2,619
次編輯
「SQL雜記」修訂間的差異
無編輯摘要
Tankianting（討論 | 貢獻） |
Tankianting（討論 | 貢獻） |
||
| 行 55： | 行 55： | ||
如果子查詢和外部表格欄位關聯，則應用join，以提升效能。 | 如果子查詢和外部表格欄位關聯，則應用join，以提升效能。 | ||
select id, name from 學生清單; | |||
select * from student; | |||
select name, round(age * 0.9) from student; | |||
select name, age as foo from tab.col 點號.避免重名 | |||
select o.id, o.name, | select o.id, o.name, | ||
| 行 62： | 行 72： | ||
group by o.id, o.name | group by o.id, o.name | ||
select all x,y from ... where... | |||
select | select distinct o.type ... 不重複資料 | ||
select count(distinct tyoe)as unique | |||
with o as (SUBQUERY) select ... from ... | |||
為什麼使用subquery？ | |||
# 分析子問題 | |||
# 提升速度 | |||
select x from y limit 10; | |||
使用predicate謂詞 | |||
where name not like "%fall%" | |||
owner.id is null | |||
owner.id in (select ...) | |||
having : aggeration聚合的過濾 | |||
having 要搭配group by | |||
例如 having count(*) = 6; | |||
where vs having | |||
where：過濾列 | |||
having：過濾having的集合 | |||
order by x DESC = order by x 遞增排序 | |||
order by x ASC 排序遞減 | |||
不能用於subquery | |||
limit 10 出現多少的資料。SQLite、MySQL、etc. | |||
group by t.name; 重複的會消除，將資料以AAA BBB CCCC排列 | |||
=== Ch5. 增刪更檔案=== | === Ch5. 增刪更檔案=== | ||
*下列語法以PostgreSQL為準 | *下列語法以PostgreSQL為準 | ||