(相關(guān)資料圖)

1.連接

1.1.笛卡兒積

1.1.1.交叉連接（cross join）

1.1.2.查詢并沒有指定兩個數(shù)據(jù)表應(yīng)該如何連接，數(shù)據(jù)庫服務(wù)器就生成了笛卡兒積

1.1.2.1.兩個數(shù)據(jù)表的所有排列組合

1.1.3.很少會用到（至少不會特意用到）

1.1.3.1.使用頻率并不高

1.1.4.mysql

-＞ SELECT c.first_name, c.last_name, a.address    -＞ FROM customer c JOIN address a;

1.1.5.為2020年的每一天生成一行，但是數(shù)據(jù)庫中沒有包含每天一行的數(shù)據(jù)表

1.1.5.1.涉及交叉連接、外連接、日期函數(shù)、分組、集合運(yùn)算（union all）和聚合函數(shù)（count()）

1.1.5.2.sql

SELECT "2020-01-01" dtUNION ALLSELECT "2020-01-02" dtUNION ALLSELECT "2020-01-03" dtUNION ALL.........SELECT "2020-12-29" dtUNION ALLSELECT "2020-12-30" dtUNION ALLSELECT "2020-12-31" dt

1.1.5.3.mysql

-＞ SELECT ones.num + tens.num + hundreds.num    -＞ FROM    -＞ (SELECT 0 num UNION ALL    -＞ SELECT 1 num UNION ALL    -＞ SELECT 2 num UNION ALL    -＞ SELECT 3 num UNION ALL    -＞ SELECT 4 num UNION ALL    -＞ SELECT 5 num UNION ALL    -＞ SELECT 6 num UNION ALL    -＞ SELECT 7 num UNION ALL    -＞ SELECT 8 num UNION ALL    -＞ SELECT 9 num) ones    -＞ CROSS JOIN    -＞ (SELECT 0 num UNION ALL    -＞ SELECT 10 num UNION ALL    -＞ SELECT 20 num UNION ALL    -＞ SELECT 30 num UNION ALL    -＞ SELECT 40 num UNION ALL    -＞ SELECT 50 num UNION ALL    -＞ SELECT 60 num UNION ALL    -＞ SELECT 70 num UNION ALL    -＞ SELECT 80 num UNION ALL    -＞ SELECT 90 num) tens    -＞ CROSS JOIN    -＞ (SELECT 0 num UNION ALL    -＞ SELECT 100 num UNION ALL    -＞ SELECT 200 num UNION ALL    -＞ SELECT 300 num) hundreds;

1.1.5.4.如果生成{0, 1, 2, 3, 4, 5, 6, 7, 8, 9}、{0, 10, 20, 30, 40, 50, 60, 70, 80, 90}和{0,100, 200, 300}這3個集合的笛卡兒積，并將這3列的值相加，就可以得到包含0～399的所有數(shù)值的400行結(jié)果集

1.1.5.5.mysql

-＞ SELECT DATE_ADD("2020-01-01",    -＞   INTERVAL (ones.num + tens.num + hundreds.num) DAY) dt    -＞ FROM    -＞  (SELECT 0 num UNION ALL    -＞   SELECT 1 num UNION ALL    -＞   SELECT 2 num UNION ALL    -＞   SELECT 3 num UNION ALL    -＞   SELECT 4 num UNION ALL    -＞   SELECT 5 num UNION ALL    -＞   SELECT 6 num UNION ALL    -＞   SELECT 7 num UNION ALL    -＞   SELECT 8 num UNION ALL    -＞   SELECT 9 num) ones    -＞   CROSS JOIN    -＞  (SELECT 0 num UNION ALL    -＞   SELECT 10 num UNION ALL    -＞   SELECT 20 num UNION ALL    -＞   SELECT 30 num UNION ALL    -＞   SELECT 40 num UNION ALL    -＞   SELECT 50 num UNION ALL    -＞   SELECT 60 num UNION ALL    -＞   SELECT 70 num UNION ALL    -＞   SELECT 80 num UNION ALL    -＞   SELECT 90 num) tens    -＞   CROSS JOIN    -＞  (SELECT 0 num UNION ALL    -＞   SELECT 100 num UNION ALL    -＞   SELECT 200 num UNION ALL    -＞   SELECT 300 num) hundreds    -＞ WHERE DATE_ADD("2020-01-01",    -＞   INTERVAL (ones.num + tens.num + hundreds.num) DAY) ＜ "2021-01-01"    -＞ ORDER BY 1;

1.1.5.6.無須人為介入，結(jié)果集會自動包含額外的閏日（2月29日），這是由數(shù)據(jù)庫服務(wù)器通過將2020年1月1日加上59天計(jì)算得出

1.2.內(nèi)連接

1.2.1.增加了連接類型（注意關(guān)鍵字inner）

1.2.2.mysql

-＞ SELECT c.first_name, c.last_name, a.address    -＞ FROM customer c JOIN address a    -＞   ON c.address_id = a.address_id;

1.2.3.sql

SELECT c.first_name, c.last_name, a.addressFROM customer c INNER JOIN address a  ON c.address_id = a.address_id;

1.2.4.如果用于連接兩個數(shù)據(jù)表的列名相同，則可以使用using子句替代on

1.2.4.1.sql

SELECT c.first_name, c.last_name, a.addressFROM customer c INNER JOIN address a  USING (address_id);

1.2.4.2.using是一種只能在某些特定情況下使用的簡寫表示法

1.3.外連接（outer join）

1.3.1.如果要將一個數(shù)據(jù)表中的所有行全部納入結(jié)果集，不管其在另一個數(shù)據(jù)表中是否存在匹配

1.3.2.mysql

-＞ SELECT f.film_id, f.title, count(i.inventory_id) num_copies    -＞ FROM film f    -＞   LEFT OUTER JOIN inventory i    -＞   ON f.film_id = i.film_id    -＞ GROUP BY f.film_id, f.title;

1.3.3.關(guān)鍵字left和right只是告知服務(wù)器哪個數(shù)據(jù)表的數(shù)據(jù)可以不足

1.3.4.outer關(guān)鍵字是可選的，可以使用A left join B來代替

1.4.自然連接（natural join）

1.4.1.依靠多個數(shù)據(jù)表之間相同的列名來推斷適合的連接條件

1.4.2.mysql

-＞ SELECT c.first_name, c.last_name, date(r.rental_date)    -＞ FROM customer c    -＞   NATURAL JOIN rental r;Empty set (0.04 sec)

1.4.3.數(shù)據(jù)庫服務(wù)器檢查數(shù)據(jù)表定義并添加了連接條件r.customer_id = c.customer_id

1.4.4.應(yīng)該避免使用這種連接類型，而使用帶有顯式連接條件的內(nèi)連接

1.5.連接的舊方法

1.5.1.mysql

-＞ SELECT c.first_name, c.last_name, a.address    -＞ FROM customer c, address a    -＞ WHERE c.address_id = a.address_id;

1.5.2.不需要on子句

1.5.3.from子句中的數(shù)據(jù)表名以逗號分隔

1.5.4.連接條件出現(xiàn)在where子句中

1.6.ANSI連接語法

1.6.1.連接條件和過濾條件被分隔在兩個不同的子句中（on子句和where子句），使得查詢語句更易于理解

1.6.2.兩個數(shù)據(jù)表的連接條件出現(xiàn)在其各自單獨(dú)的on子句中，這樣就不太可能錯誤地忽略連接條件

1.6.3.使用SQL92連接語法的查詢語句可以在各種數(shù)據(jù)庫服務(wù)器間移植，而舊語法在不同服務(wù)器上的表現(xiàn)略有不同

1.7.連接順序

1.7.1.各數(shù)據(jù)表在from子句中出現(xiàn)的順序并不重要

1.7.1.1.服務(wù)器使用從數(shù)據(jù)庫對象收集的統(tǒng)計(jì)信息，在3個數(shù)據(jù)表中選擇一個作為起點(diǎn)（所選擇的數(shù)據(jù)表被稱為驅(qū)動表），然后確定其他數(shù)據(jù)表的連接順

1.7.2.如果認(rèn)為查詢語句中的數(shù)據(jù)表應(yīng)該始終以特定的順序連接，可以將數(shù)據(jù)表按照需要的順序排列

1.7.2.1.在MySQL中指定straight_join關(guān)鍵字

1.7.2.1.1.sql

SELECT STRAIGHT_JOIN c.first_name, c.last_name, ct.cityFROM city ct  INNER JOIN address a  ON a.city_id = ct.city_id  INNER JOIN customer c  ON c.address_id = a.address_id

1.7.2.2.在SQL Server中請求force order選項(xiàng)

1.7.2.3.在Oracle Database中使用ordered或leading優(yōu)化器

1.7.3.三路外連接

1.7.3.1.mysql

-＞ SELECT f.film_id, f.title, i.inventory_id, r.rental_date    -＞ FROM film f    -＞   LEFT OUTER JOIN inventory i    -＞   ON f.film_id = i.film_id    -＞   LEFT OUTER JOIN rental r    -＞   ON i.inventory_id = r.inventory_id    -＞ WHERE f.film_id BETWEEN 13 AND 15;

1.8.自連接

1.8.1.mysql

-＞ SELECT f.title, f_prnt.title prequel    -＞ FROM film f    -＞   INNER JOIN film f_prnt    -＞   ON f_prnt.film_id = f.prequel_film_id    -＞ WHERE f.prequel_film_id IS NOT NULL;

2.集合

2.1.在對兩個數(shù)據(jù)集執(zhí)行集合運(yùn)算時

2.1.1.兩個數(shù)據(jù)集的列數(shù)必須相等

2.1.2.兩個數(shù)據(jù)集各列的數(shù)據(jù)類型必須相同

2.1.2.1.數(shù)據(jù)庫服務(wù)器必須能夠?qū)⒁环N數(shù)據(jù)類型轉(zhuǎn)換成另一種數(shù)據(jù)類型

2.2.union運(yùn)算符

2.2.1.mysql

-＞ SELECT 1 num, "abc" str    -＞ UNION    -＞ SELECT 9 num, "xyz" str;

2.2.2.union會對組合后的集合進(jìn)行排序并去除重復(fù)項(xiàng)

2.2.3.union all則不然

2.3.intersect運(yùn)算符

2.3.1.ANSI SQL規(guī)范中定義了用于執(zhí)行交集運(yùn)算的intersect運(yùn)算符

2.3.2.ANSI SQL規(guī)范還提供了不去除重復(fù)行的intersect all運(yùn)算符

2.3.2.1.唯一實(shí)現(xiàn)了intersect all運(yùn)算符的數(shù)據(jù)庫服務(wù)器是IBM DB2 Universal Server

2.3.3.MySQL 8.0版還未實(shí)現(xiàn)intersect運(yùn)算符

2.3.4.Oracle或SQL Server 2008中可以使用

2.4.except運(yùn)算符

2.4.1.ANSI SQL規(guī)范提供了執(zhí)行差集運(yùn)算的except運(yùn)算符

2.4.2.ANSI SQL規(guī)范還提供了except all運(yùn)算符

2.4.2.1.只有IBM的 DB2 Universal Server實(shí)現(xiàn)了該運(yùn)算符

2.4.3.MySQL 8.0版也沒有實(shí)現(xiàn)except運(yùn)算符

2.4.4.Oracle Database需要使用非ANSI兼容的minus運(yùn)算符替代except運(yùn)算符

2.5.對符合查詢結(jié)果排序

2.5.1.mysql

-＞ SELECT a.first_name fname, a.last_name lname    -＞ FROM actor a    -＞ WHERE a.first_name LIKE "J%" AND a.last_name LIKE "D%"    -＞ UNION ALL    -＞ SELECT c.first_name, c.last_name    -＞ FROM customer c    -＞ WHERE c.first_name LIKE "J%" AND c.last_name LIKE "D%"    -＞ ORDER BY lname, fname;

2.6.集合運(yùn)算的優(yōu)先級

2.6.1.包含3個或以上查詢語句的復(fù)合查詢，是以自頂向下的順序來評估查詢的

2.6.2.根據(jù)ANSI SQL規(guī)范，intersect運(yùn)算符擁有比其他集合運(yùn)算符更高的優(yōu)先級

2.6.3.可以將查詢放入括號內(nèi)，以明確指定查詢的執(zhí)行順序

2.6.3.1.MySQL目前還不允許在復(fù)合查詢中使用括號

2.6.3.2.將相鄰查詢放入括號中，以覆蓋復(fù)合查詢默認(rèn)的自頂向下的處理方式