- GROUP BY 分組允許把數(shù)據(jù)分為多個(gè)分組，以便能夠?qū)γ總€(gè)分組進(jìn)行聚集計(jì)算。（獲取每個(gè)供應(yīng)商所提供的商品數(shù)量）
SELECT vend_id , count(*) as vend_num FROM products GROUP BY vend_id
- HAVING 過(guò)濾分組，（獲取訂單表中，訂單次數(shù)大于等于2次的）
SELECT cust_id,count(*) AS count_num FROM orders GROUP BY cust_id HAVING count_num>=2
- 子查詢 Mysql在4.1中引入了對(duì)子查詢的支持，所以需要進(jìn)行子查詢的話，Mysql版本必須高于4.1。(獲取訂單表中購(gòu)買物品是TNT2的客戶信息)
SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id ='TNT2')
- 相關(guān)子查詢 （獲取每個(gè)客戶的下單次數(shù)和客戶信息）
SELECT cust_name,cust_address,(SELECT COUNT(*) FROM orders WHERE orders.cust_id=customers.cust_id )AS user_orders FROM customers
- 聯(lián)表查詢（在一條SELECT語(yǔ)句中聯(lián)結(jié)幾個(gè)表時(shí)，相應(yīng)的關(guān)系是在運(yùn)行時(shí)構(gòu)造的。在數(shù)據(jù)庫(kù)的定義中不存在能指示MYSQL對(duì)表進(jìn)行聯(lián)結(jié)的東西。在聯(lián)結(jié)兩個(gè)表的時(shí)候，你實(shí)際上做的是，將第一個(gè)表中的每一行數(shù)據(jù)和第二個(gè)表中的每一行數(shù)據(jù)進(jìn)行匹配，WHERE子句作為過(guò)濾的條件。倘若沒(méi)有聯(lián)結(jié)條件的話，就是返回笛卡爾積 （第一個(gè)行數(shù)*第二表的行數(shù)））。
SELECT * FROM products,vendors WHERE products.vend_id=vendors.vend_id
SELECT * FROM products INNER JOIN vendors ON products.vend_id=vendors.vend_id
- 自聯(lián)結(jié) （自聯(lián)結(jié)通常作為外部語(yǔ)句來(lái)替代相同表中檢索數(shù)據(jù)時(shí)使用的子查詢。有的時(shí)候處理聯(lián)結(jié)遠(yuǎn)比子查詢的速度快，所以需要多嘗試一下，確定哪種的性能更好點(diǎn)）
SELECT COUNT(*) FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id='DTNTR')
SELECT p1.prod_id,p1.prod_price FROM products as p1,products as p2 WHERE p1.vend_id=p2.vend_id AND p2.prod_id='DTNTR'
- 外部聯(lián)結(jié) （在使用 OUTER JOIN 語(yǔ)法的時(shí)候，必須使用LEFT 或者RIGHT關(guān)鍵字來(lái)指定所有的行。RIGHT是指OUTER JOIN右邊的表）
SELECT orders.cust_id,orders.order_num AS 訂單總數(shù) FROM orders,customers WHERE orders.cust_id=customers.cust_id GROUP BY orders.cust_id
SELECT customers.cust_id,orders.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id
SELECT customers.cust_id,orders.order_num FROM orders RIGHT OUTER JOIN customers ON customers.cust_id=orders.cust_id
- 組合查詢 （UNION必須有兩條或者兩條以上的SELECT語(yǔ)句組合成的。UNION在查詢結(jié)果集中會(huì)自動(dòng)去除重復(fù)行，假設(shè)不想去除重復(fù)行的話可以使用UNION ALL）
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id in (1001,1002) #5
SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price<=5 #4
SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id in (1001,1002) UNION SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price<=5
注意HAVING和WHERE之間的區(qū)別：
WHERE在數(shù)據(jù)分組前進(jìn)行過(guò)濾，HAVING在數(shù)據(jù)分組后進(jìn)行過(guò)濾。WHERE排除的行不包括在分組中，這可能會(huì)影響結(jié)果值。（綜合WHERE和HAVING的查詢，產(chǎn)品表中，價(jià)格大于等于10且產(chǎn)品數(shù)量大于2）
SELECT vend_id, COUNT(*) AS num FROM products WHERE prod_price>=10 GROUP BY vend_id HAVING num >=2
