- 檢索多個(gè)列（SELECT）
SELECT prod_id, prod_name, prod_price
FROM Products;
- 檢索某列厅贪，并排序（ORDER BY）
- ORDER BY語(yǔ)句必須是SELECT語(yǔ)句的最后一句羞福，否則會(huì)報(bào)錯(cuò)幻碱。也可以用沒有選擇的列來(lái)排序答渔。
SELECT prod_id
FROM Products
ORDER BY prod_name;
- 檢索多個(gè)列关带，按多個(gè)列進(jìn)行排序（ORDER BY）
- 首先會(huì)按照價(jià)格進(jìn)行排序，對(duì)于價(jià)格一樣的行沼撕，再按照名稱排序宋雏。
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
- 指定排序方向（ORDER BY DESC）
- 先對(duì)prod_price按照降序，再對(duì)prod_name進(jìn)行升序（默認(rèn)為升序）务豺。
SELECT prod_id, prod_name, prod_price
FROM Products
ORDER BY prod_price DESC, prod_name;
- 檢索多個(gè)列磨总，并使用WHERE過(guò)濾（WHERE）
- < >或者!= 為不等于
- BETWEEN 為在指定的兩個(gè)數(shù)值之間
- IS NULL 為NULL值
- 單引號(hào)用來(lái)限制字符串，如果將值和字符串類型的列進(jìn)行比較笼沥，則需要限定引號(hào)蚪燕。用來(lái)和數(shù)值進(jìn)行比較的值則不需要引號(hào)。
- 可以使用 AND奔浅、OR馆纳、NOT
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;
SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ('DLL01' , 'BRS01');
- 模糊查詢（LIKE）
- '%'是一種通配符，%表示任何字符出現(xiàn)任意次數(shù)（包括匹配0個(gè)字符）
- ‘_’總是匹配一個(gè)字符乘凸，不能多也不能少
- ‘[ ]’可以用來(lái)指定一個(gè)字符集
//找出所有以詞Fish起頭的產(chǎn)品
//若改為L(zhǎng)IKE ‘%Fish%’ 則表示匹配包含F(xiàn)ish
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';
//匹配以F起頭厕诡，以y結(jié)尾的所有產(chǎn)品
//有些DBMS會(huì)用空格來(lái)填補(bǔ)字段的內(nèi)容累榜，所以結(jié)尾就會(huì)是空格营勤，將會(huì)匹配不出來(lái)灵嫌，解決辦法：LIKE ‘F%y%’
SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y';
//找出所有以J或者M(jìn)起頭的聯(lián)系人
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
- 計(jì)算字段
- 連接字符串是，不同DBMS不一樣葛作，有的是+寿羞，有的是||，MySQL是CONCAT
- RTRIM可以去掉字段的空格
- 可以對(duì)字段使用*等操作符赂蠢，進(jìn)行數(shù)學(xué)運(yùn)算
SELECT CONCAT(vend_name, '(', vend_country, ')')
FROM Vendors
ORDER BY vend_name;
- 函數(shù)
- UPPER將文本轉(zhuǎn)換為大寫绪穆，LTRIM去掉串左邊的空格，RTRIM去掉串右邊的空格
- 有一些日期相關(guān)的函數(shù)
- ABS虱岂、COS玖院、EXP等數(shù)值函數(shù)
- 匯總數(shù)據(jù)（AVG、COUNT第岖、MAX难菌、MIN、SUM）
- AVG忽略列值為NULL的行
- COUNT（*）對(duì)表中行的數(shù)目進(jìn)行計(jì)數(shù)蔑滓，不管表列中包含的是空值（NULL）還是非空值郊酒。使用COUNT（column）對(duì)特定列中具有值的行進(jìn)行計(jì)數(shù)，忽略NULL值
- MAX键袱、MIN燎窘、SUM
- 如果指定列名，則DISTINCT只能用于COUNT（）蹄咖。DISTINCT不能用于COUNT（*）褐健。
SELECT AVG（prod_price） AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
//加了DISTINCT之后，平均值只考慮各個(gè)不同的價(jià)格
SELECT AVG（DISTINCT prod_price） AS avg_price
FROM Products
WHERE vend_id = 'DLL01';
SELECT SUM(item_price * quantity) AS total price
FROM OrderItems
WHERE order_num = 20005;
- 分組數(shù)據(jù)（GROUP BY澜汤、HAVING）
- WHERE 用來(lái)過(guò)濾行铝量，HAVING用來(lái)過(guò)濾分組。WHERE在數(shù)據(jù)分組前進(jìn)行過(guò)濾银亲，HAVING在數(shù)據(jù)分組之后進(jìn)行過(guò)濾慢叨，所以WHERE排除的行不包括在分組中，這可能會(huì)改變計(jì)算值务蝠，從而影響HAVING子句中基于這些值過(guò)濾掉的分組
//先用WHERE子句過(guò)濾所有prod_price至少為4的行拍谐，然后按照vend_id分組數(shù)據(jù)，HAVING子句過(guò)濾計(jì)數(shù)為2或2以上的分組馏段。
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 2;
- 使用子查詢
- 作為子查詢的SELECT語(yǔ)句只能查詢單個(gè)列轩拨。企圖檢索多個(gè)列將會(huì)返回錯(cuò)誤
SELECT cust_id
FROM Orders
WHERE order_num IN (SELCT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01');
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id = Customers.cust_id) AS
orders
FROM Customers
ORDER BY cust_name;
- 聯(lián)結(jié)表（JOIN）
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01'))
也可以使用下面的查詢，得到同樣的結(jié)果
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num
AND prod_id = 'RGAN01';
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
//檢索所有客戶以及每個(gè)客戶所下的訂單數(shù)
SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord
FROM Customers INNER JOIN Orders
ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;
