說明
最近看了 Sams Teach Yourself 系列的《SQL必知必會》躲惰。里面的內(nèi)容很基礎(chǔ)周霉，使用SQL上手簡單翰苫。我將其中典型的題目及答案總結(jié)如下，包含數(shù)據(jù)庫所有的基礎(chǔ)操作温技，其中所使用的數(shù)據(jù)庫樣例表見文章末尾。
如果大家有什么意見和建議扭粱，歡迎留言或私信指出~
練習(xí)
（Products）檢索列vend_id的所有值和「不同值」
SELECT DISTINCT vend_id FROM Products;
檢索列prod_name的前5行數(shù)據(jù)舵鳞、第6到10行數(shù)據(jù)
SELECT prod_name FROM Products LIMIT 5;
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
檢索列prod_id, prod_price, prod_name，并按后兩個(gè)升序排列（位置）
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name;
檢索列prod_price琢蛤，并按prod_price降序排列
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC;
檢索列prod_name, prod_price蜓堕，同時(shí)prod_price為3.49、小于10博其、在5至10之間套才、空值
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10;
檢索列prod_name, prod_price，其中vend_id為DLL01或BRS01（兩種方式）
SELECT prod_name , prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';
檢索列prod_id, prod_name慕淡，其中prod_name以詞Fish起頭霜旧、包含"bean bag"、以F開頭y結(jié)尾
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%';
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'F%y';
Vendors表包含列vend_name和vend_country儡率，用括號將vend_country括起來挂据，組合兩列返回（使用別名vend_title）
SELECT Concat(vend_name, '(', vend_country, ')') AS vend_title FROM Vendors ORDER BY vend_name;
在OrderItems中，檢索order_num為20008的所有物品（+ 匯總物品的價(jià)格為expanded_price = quantity * item_price）
SELECT *, quantity * item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
在Vendors中儿普，將vend_name顯示為小寫和大寫（vend_name_upcase）
SELECT vend_name, UPPER(vend_name) AS vend_upcase FROM Vendors ORDER BY vend_name;
在Orders中崎逃，輸出order_num，條件是order_date在2012年
SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;
在Products中眉孩，將prod_price的平均值作為avg_price輸出（6.8233）（+ vend_id為DLL01的平均值）
SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01';
在Customers中个绍，計(jì)算顧客的總數(shù)作為num_cust輸出（+ cust_email列）
SELECT COUNT(*) AS num_cast FROM Customers;
在Products中勒葱，返回prod_price的最大值，作為max_price輸出（11.99）
SELECT MAX(prod_price) AS max_price FROM Products;
在OrderItems中巴柿，計(jì)算order_num為20005的quantity的總和凛虽，作為items_ordered輸出
SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE order_num = 20005;
在Products中，列出vend_id及每個(gè)出現(xiàn)的次數(shù)（num_prods）
SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;
在Orders中广恢，按照cust_id分組凯旋，列出cust_id及其行數(shù)（orders），打印出行數(shù)>=2的
SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;
在Products中钉迷，對vend_id分組至非，找出prod_price>=4并且行數(shù)>=2的vend_id，并打印對應(yīng)行數(shù)（num_prods）
SELECT vend_id, COUNT(*) AS num_prods FROM Products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;
在OrderItems中糠聪，選取prod_id為RGAN01的列order_num荒椭，并將結(jié)果作為order_num的范圍選取條件，在Orders中選取cust_id
SELECT cust_id FROM Orders WHERE order_num IN (SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01');
在Vendors和Products中舰蟆，選取列vend_name, prod_name, prod_price（vend_id相同趣惠，等值聯(lián)結(jié)）（+使用內(nèi)聯(lián)結(jié)）
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;
在Customers中，插入一行數(shù)據(jù)身害，cust_id=10006, cust_name=yano(+并刪除)
INSERT INTO Customers (cust_id, cust_name) VALUES(10006, 'yano');
DELETE from Customers where cust_name = 'yano';
將表Customers的內(nèi)容全部復(fù)制到CustCopy表中
CREATE TABLE CustCopy AS SELECT * FROM Customers;
在Customers中信卡，更新cust_id為1000000005的用戶，將cust_email改為'yano_nankai'
UPDATE Customers SET cust_email = 'yano_nankai' WHERE cust_id = '1000000005';
創(chuàng)建表yano题造，id為整數(shù)非空傍菇，name為10個(gè)字符非空（默認(rèn)為hello），并更新表結(jié)構(gòu)（增加和刪除phone列界赔，20個(gè)字符非空）丢习，將id增加約束為主鍵，刪除表yano
CREATE TABLE YANO( id INTEGER NOT NULL, name CHAR(10) NOT NULL DEFAULT 'HELLO');
ALTER TABLE YANO ADD phone CHAR(20);
ALTER TABLE YANO DROP COLUMN phone;
查看Orders的索引
SHOW INDEX FROM Orders;
