《SQL 必知必會》全解析

不要哀求，學會爭取。若是如此，終有所獲。

原文：https://mp.weixin.qq.com/s/zbOqyAtsWsocarsFIGdGgw

前言

你是否還在煩惱 SQL 該從何學起，或者學了 SQL 想找個地方練練手？好巧不巧，最近在工作之餘登上牛客，發現了牛客不知道啥時候上線了SQL 必知必會的練習題。

《SQL 必知必會》作為麻省理工學院、伊利諾伊大學等眾多大學的參考教材，由淺入深地講解了SQL的基本概念和語法。涉及數據的排序、過濾和分組，以及表、視圖、聯結、子查詢、游標、存儲過程和觸發器等內容。實例豐富，方便查閱，可以說作為一個 CRUD BOY/GIRL 必讀書目。

想著正好給它刷一遍，然後將自己刷題的一些想法總結下，於是有了今天這篇文章，希望能給需要的小伙伴一點點幫助。

SQL1 從 Customers 表中檢索所有的 ID

描述

現有表Customers如下：

問題

編寫 SQL 語句，從 Customers 表中檢索所有的 cust_id。

示例答案

返回 cust_id 列的內容

示例

DROP TABLE IF EXISTS `Customers`;

CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) DEFAULT NULL
);

INSERT `Customers` VALUES ('A'),('B'),('C');

解答

考察最簡單的查詢語句，因為 Customers 表中僅有 cust_id 一列，所以我們可以使用以下兩種解答方式。

• 第一種方式，選擇特定列進行輸出，這也是我們在工作中更加推薦使用的一種方式，將需要輸出的列名全部描述出來。

SELECT cust_id FROM Customers;

• 第二種方式，使用 * 對錶中所有列進行輸出，因為 Customers 表中僅有一列，所以可以使用該方式。但在日常工作中，就算查詢結果列中包含了資料庫表的所有欄位，也不要直接使用 *.

SELECT * FROM Customers;

SQL2 檢索併列出已訂購產品的清單

描述

表OrderItems含有非空的列prod_id代表商品id，包含了所有已訂購的商品（有些已被訂購多次）。

問題

編寫SQL 語句，檢索併列出所有已訂購商品（prod_id）的去重後的清單。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '商品id'
);
INSERT `OrderItems` VALUES ('a1'),('a2'),('a3'),('a4'),('a5'),('a6'),('a6')

解答

要對結果去重，可以使用 DISTINCT 關鍵字。使用時，在後邊跟上需要去重的欄位即可保證這些去重欄位的查詢結果不重覆。

SELECT DISTINCT prod_id FROM OrderItems;

此外還可以使用 GROUP BY 關鍵字，改關鍵字支持在去重的同時，同步返回其他欄位的信息。

SELECT prod_id FROM OrderItems GROUP BY prod_id;

SQL3 檢索所有列

描述

現在有 Customers 表（表中含有列 cust_id 代表客戶 id，cust_name 代表客戶姓名） 。

問題

需要編寫 SQL語句，檢索所有列。

示例結果

返回所有列 cust_id 和 cust_name。

示例

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客戶id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客戶姓名'
);
INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

解答

類似於第一題，最簡單的查詢語句，只不過相比第一題多了一列，所以同樣可以使用兩種方式。

• 使用 * 表示所有列。

SELECT * FROM Customers;

• 將需要列印出的列詳細列出。

SELECT cust_id, cust_name FROM Customers;

SQL4 檢索顧客名稱並且排序

描述

有表 Customers，cust_id 代表客戶 id，cust_name 代表客戶姓名。

問題

從 Customers 中檢索所有的顧客名稱（cust_name），並按從 Z 到 A 的順序顯示結果。

示例結果

返回客戶姓名 cust_name

示例

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客戶id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客戶姓名'
);
INSERT `Customers` VALUES ('a1','andy'),('a2','ben'),('a3','tony'),('a4','tom'),('a5','an'),('a6','lee'),('a7','hex');

解答

要對資料庫中的數據進行排序，需要使用關鍵字 ORDER BY。此外，預設情況下，SQL 中列的結果預設是正序排列的，即實際情況下以下語句執行結果是一樣的。

SELECT cust_name FROM Customers;

SELECT cust_name FROM Customers ORDER BY cust_name ASC;

而要對查詢結果逆序輸出，則需要用到 DESC 關鍵字，表示逆序輸出。

SELECT cust_name FROM Customers ORDER BY cust_name DESC;

SQL5 對顧客ID和日期排序

描述

有 Orders 表

問題

編寫 SQL 語句，從 Orders 表中檢索顧客 ID（cust_id）和訂單號（order_num），並先按顧客 ID 對結果進行排序，再按訂單日期倒序排列。

示例答案

返回 2 列，cust_id 和 order_num

示例解析

首先根據 cust_id 進行排列，andy 在 bob 和 dick 前，再根據 order_date 進行排列，訂單號 bbbb 的訂單時間是 "2021-01-01 12:00:00" 大於訂單號 aaaa 的時間 "2021-01-01 00:00:00"

示例

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders` (
  `cust_id` varchar(255) NOT NULL COMMENT '顧客 ID',
  `order_num` varchar(255) NOT NULL COMMENT '訂單號',
  `order_date` timestamp NOT NULL COMMENT '訂單時間'
);
INSERT INTO `Orders` VALUES ('andy','aaaa','2021-01-01 00:00:00'),
('andy','bbbb','2021-01-01 12:00:00'),
('bob','cccc','2021-01-10 12:00:00'),
('dick','dddd','2021-01-11 00:00:00');

解答

要對列進行排序，則需要使用 ORDER BY 關鍵字，此外就是正序和倒序輸出。

• ASC：正序輸出，也是預設輸出的情況。
• DESC：倒序輸出。

SELECT cust_id, order_num FROM Orders ORDER BY cust_id, order_date DESC;

SQL6 按照數量和價格排序

描述

假設有一個 OrderItems 表

問題

編寫 SQL 語句，顯示 OrderItems 表中的數量（quantity）和價格（item_price），並按數量由多到少、價格由高到低排序。

示例答案

返回 quantity 和 item_price

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems` (
  `quantity` INT(64) NOT NULL COMMENT '數量',
  `item_price` INT(64) NOT NULL COMMENT '訂單價格'
);
INSERT INTO `OrderItems` VALUES (1,100),
(10,1003),
(2,500);

解答

同樣是查詢語句，要對查詢結果進行排序，則需要使用 ORDER BY 關鍵字，最後則是需要註意是正序還是倒序，題目中由多到少和由高到低都是屬於倒序，所以需要使用關鍵字 DESC。

SELECT quantity, item_price FROM OrderItems ORDER BY quantity DESC, item_price DESC;

SQL7 檢查SQL語句

描述

有 Vendors 表

問題

下麵的 SQL 語句有問題嗎？嘗試將它改正確，使之能夠正確運行，並且返回結果根據 vend_name 逆序排列

SELECT vend_name, 
FROM Vendors 
ORDER vend_name DESC;

示例展示

返回 vend_name

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名稱'
);
INSERT INTO `Vendors` VALUES ('海底撈'),
('小龍坎'),
('大龍燚');

解答

SELECT vend_name, FROM Vendors ORDER vend_name DESC;

主要存在兩處錯誤，首先是選取需要展示的列 vend_name 後，後面並沒有跟上需要展示的其他列，所以此處多了一個 ,。另外，對選取列進行排序使用的關鍵字是 ORDER BY，而題目中只是給出了 ORDER，忘記了 BY，所以會導致查詢結果錯誤。針對這兩處錯誤改正後，得到的正確的語句如下所示。

SELECT vend_name FROM Vendors ORDER BY vend_name DESC;

SQL8 返回固定價格的產品

描述

有表 Products

問題

從 Products 表中檢索產品 ID（prod_id）和產品名稱（prod_name），只返回價格為 9.49 美元的產品。

示例結果

返回 prod_id 和 prod_name

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
  `prod_id` VARCHAR(255) NOT NULL COMMENT '產品 ID',
  `prod_name` VARCHAR(255) NOT NULL COMMENT '產品名稱',
  `prod_price` DOUBLE NOT NULL COMMENT '產品價格'
);
INSERT INTO `Products` VALUES ('a0018','sockets',9.49),
('a0019','iphone13',600),
('b0019','gucci t-shirts',1000);

解答

查詢符合指定條件的數據，可以使用關鍵字 WHERE 對條件進行限制，然後就是對條件的實現，這裡可以使用兩種方式。

第一種，直接利用 =，既然條件是需要價格為 9.49 美元的產品，則我們將價格列設置為 prod_price = 9.49 即可。

SELECT prod_id, prod_name FROM Products WHERE prod_price = 9.49;

第二種，除開 = 之外，我們也可以使用 in，表示價格只要在指定的數據之中，我們將價格列設置為 in (9.49) 即可，但是此時要註意不要忽略 ()，否則語句會報錯。

SELECT prod_id, prod_name FROM Products WHERE prod_price in (9.49);

SQL9 返回更高價格的產品

描述

Products 表

問題

編寫 SQL 語句，從 Products 表中檢索產品 ID（prod_id）和產品名稱（prod_name），只返回價格為 9 美元或更高的產品。

示例答案

返回 prod_id 商品 id 和 prod_name 商品名稱

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '產品名稱',
`prod_price` DOUBLE NOT NULL COMMENT '產品價格'
);
INSERT INTO `Products` VALUES ('a0011','usb',9.49),
('a0019','iphone13',600),
('b0019','gucci t-shirts',1000);

解答

條件查詢語句，查詢格式：

SELECT [列名] FROM [表名] WHERE 判斷條件 …

既然判斷條件是要產品價格不低於 9 美元，則產品需要大於等於 9 美元，可以將產品價格設置為 prod_price >= 9 即可。

SELECT prod_id, prod_name FROM Products WHERE prod_price >= 9;

此外，我們還可以將 >= 拆分為兩個條件，一個是 prod_price > 9，而另一個則是 prod_price = 9，然後滿足任一條件即可，則利用關鍵字 OR 將兩個條件進行連接。

SELECT prod_id, prod_name FROM Products WHERE prod_price > 9 OR prod_price = 9;

SQL10 返回產品並且按照價格排序

描述

有 Products 表

問題

編寫 SQL 語句，返回 Products 表中所有價格在 3 美元到 6 美元之間的產品的名稱（prod_name）和價格（prod_price），然後按價格對結果進行排序

示例結果

返回商品名稱 prod_name 和商品價格 prod_price

註：不需要考慮商品價格相同的情況

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '產品名稱',
`prod_price` DOUBLE NOT NULL COMMENT '產品價格'
);
INSERT INTO `Products` VALUES ('a0011','egg',3),
('a0019','sockets',4),
('b0019','coffee',15);

解答

條件查詢，通過將條件拆分為 prod_price >= 3 和 prod_price <= 6 兩個條件，然後利用關鍵字 AND 將兩個條件串聯起來，同時滿足兩個條件的數據則進行輸出。然後要對價格進行排序，則可以使用關鍵字 ORDER BY。

SELECT prod_name, prod_price FROM Products WHERE prod_price >= 3 AND prod_price <=6 ORDER BY prod_price;

除開上述將兩個條件拆分開然後利用關鍵字 AND 串聯起來之外，其實 SQL 還提供了關鍵字 BETWEEN … AND …，可以將滿足介於兩者之間的數進行篩選輸出。

SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 3 AND 6 ORDER BY prod_price;

SQL11 返回更多的產品

描述

OrderItems 表含有：訂單號 order_num，quantity 產品數量

問題

從 OrderItems 表中檢索出所有不同且不重覆的訂單號（order_num），其中每個訂單都要包含 100 個或更多的產品。

示例結果

返回訂單號列 order_num

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品訂單號',
	quantity VARCHAR(255) NOT NULL COMMENT '商品數量'
);
INSERT `OrderItems` VALUES ('a1','105'),('a2','1100'),('a2','200'),('a4','1121'),('a5','10'),('a2','19'),('a7','5')

解答

要篩選指定列中不重覆的數據，則需要使用關鍵字 DISTINCT。此外，要篩選出滿足指定條件的數據，則需要關鍵字 WHERE，然後根據題意，可以將條件寫到一起，也可以將條件進行拆分後用關鍵字 OR 並聯。

SELECT DISTINCT order_num FROM OrderItems WHERE quantity >= 100;

SELECT DISTINCT order_num FROM OrderItems WHERE quantity > 100 OR quantity = 100;

SQL12 檢索供應商名稱

描述

Vendors 表有欄位供應商名稱（vend_name）、供應商國家（vend_country）、供應商州（vend_state）

問題

編寫 SQL 語句，從 Vendors 表中檢索供應商名稱（vend_name），僅返回加利福尼亞州的供應商（這需要按國家[USA]和州[CA]進行過濾，沒準其他國家也存在一個 CA）

示例答案

返回供應商名稱 vend_name

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名稱',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend國家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);
INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

解答

既然要篩選出加利福利亞州的提供商，則首要條件是滿足 vend_state = 'CA'。另外，除開美國有加利福利亞州之外，有可能其他國家也有加利福利亞州，所以需要對國家也進行限定，設置條件為 vend_country = 'USA'。然後用關鍵字 AND 將條件串聯，滿足這兩個條件的數據則進行輸出。

SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA';

SQL13 檢索併列出已訂購產品的清單

描述

OrderItems 表包含了所有已訂購的產品（有些已被訂購多次）。

問題

編寫 SQL 語句，查找所有訂購了數量至少 100 個的 BR01、BR02 或 BR03 的訂單。你需要返回 OrderItems 表的訂單號（order_num）、產品 ID（prod_id）和數量（quantity），並按產品 ID 和數量進行過濾。

示例答案

返回商品 id prod_id、訂單 order_num、數量 quantity。

示例解析

返回的結果中，數量滿足大於等於 100，且滿足 prod_id 是 "BR01"，“BR02”，“BR03" 中的任意一個。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(

	prod_id VARCHAR(255) NOT NULL COMMENT '商品號',
	order_num VARCHAR(255) NOT NULL COMMENT '商品訂單號',
	quantity INT(255) NOT NULL COMMENT '商品數量'
);
INSERT `OrderItems` VALUES ('BR01','a1','105'),('BR02','a2','1100'),('BR02','a2','200'),('BR03','a4','1121'),('BR017','a5','10'),('BR02','a2','19'),('BR017','a7','5')

解答

多條件查詢，將兩個條件進行拆分，然後將兩個查詢條件進行串聯即可。

數量滿足大於等於 100，則設置為 quantyty >= 100。

產品 id 滿足 "BR01"，“BR02”，“BR03” 中的任意一個，則使用關鍵字 in。

SELECT order_num, prod_id, quantity FROM OrderItems WHERE quantity >= 100 AND prod_id IN ('BR01', 'BR02', 'BR03');

SQL14 返回所有價格在 3 美元到 6 美元之間的產品的名稱和價格

描述

有表 Products

問題

編寫 SQL 語句，返回所有價格在 3 美元到 6 美元之間的產品的名稱（prod_name）和價格（prod_price），使用 AND 操作符，然後按價格對結果進行升序排序

示例結果

返回商品名稱 prod_name 和商品價格 prod_price

註：不需要考慮價格相同時的排序問題

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_name` VARCHAR(255) NOT NULL COMMENT '產品名稱',
`prod_price` INT(255) NOT NULL COMMENT '產品價格'
);
INSERT INTO `Products` VALUES ('a0011','egg',3),
('a0019','sockets',4),
('b0019','coffee',15);

解答

兩個條件，首先是價格介於 3 到 6 美元，則可以使用關鍵字 BETWEEN … AND …。另外，按價格升序排序，則使用關鍵字 ORDER BY。

SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 3 AND 6 ORDER BY prod_price;

題目中指定需要使用關鍵字 AND，則需要將價格條件替換成 prod_price >= 3 和 prod_price <= 6 兩個條件，然後將其串聯。

SELECT prod_name, prod_price FROM Products WHERE prod_price >= 3 AND prod_price <= 6 ORDER BY prod_price;

SQL15 糾錯2

描述

供應商表 Vendors 有欄位供應商名稱 vend_name、供應商國家 vend_country、供應商省份 vend_state

問題

修改正確下麵 sql，使之正確返回

SELECT vend_name 
FROM Vendors 
ORDER BY vend_name 
WHERE vend_country = 'USA' AND vend_state = 'CA';

示例結果

結果返回 vend_name

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_name` VARCHAR(255) NOT NULL COMMENT 'vend名稱',
  `vend_country` VARCHAR(255) NOT NULL COMMENT 'vend國家',
  `vend_state` VARCHAR(255) NOT NULL COMMENT 'vend州'
);
INSERT INTO `Vendors` VALUES ('apple','USA','CA'),
('beef noodle king','USA','CA'),
('vivo','CNA','shenzhen'),
('huawei','CNA','xian');

解答

主要是一處錯誤，搞錯了關鍵字 WHERE 和 ORDER BY 的先後順序，正確的 SQL 語句格式為：

SELECT [列名] FOMR [表名] WHERE [條件] ORDER BY [列名];

SELECT vend_name FROM Vendors WHERE vend_country = 'USA' AND vend_state = 'CA' ORDER BY vend_name;

SQL16 檢索產品名稱和描述（一）

描述

Products表

問題

編寫 SQL 語句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅返回描述中包含 toy 一詞的產品名稱

示例結果

返回產品名稱和產品描述

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '產品名稱'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

解答

模糊查詢，主要產品描述中含有 toy 一詞，就需要返回這時候需要用到模糊查詢關鍵字 LIKE 然後是通配符，題目中並沒有明確說明 toy 前後包含多少個字元，所以用 %。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%'

SQL17 檢索產品名稱和描述（二）

描述

Products 表

問題

編寫 SQL 語句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅返回描述中未出現 toy 一詞的產品，最後按”產品名稱“對結果進行排序。

示例結果

返回產品名稱和產品描述

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '產品名稱'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy');

解答

這題就是 16 題的反面，那我們只要對 16 的查詢語句進行取反即可，此時添加一個關鍵字 NOT 即可。此外，還需要按“產品名稱”對結果排序，使用關鍵字 ORDER BY 即可。但是需要註意 WHERE 和 ORDER BY 關鍵字的先後順序。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc NOT LIKE '%toy%' ORDER BY prod_name;

SQL18 檢索產品名稱和描述（三）

描述

Products 表

問題

編寫 SQL 語句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅返回描述中同時出現 toy 和 carrots 的產品。有好幾種方法可以執行此操作，但對於這個挑戰題，請使用 AND 和兩個 LIKE 比較。

示例結果

返回產品名稱和產品描述

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '產品名稱'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego carrots toy');

解答

題目中明確要求使用 LIKE 和 AND 關鍵字，那麼可以將條件拆分為兩個模糊查詢，一個是描述中含有 toy 的產品，一個是描述中含有 carrots 的產品，然後利用關鍵字 AND 將兩個條件串聯就可以了。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%' AND prod_desc LIKE '%carrots%';

SQL19 檢索產品名稱和描述（四）

描述

Products 表

問題

編寫 SQL 語句，從 Products 表中檢索產品名稱（prod_name）和描述（prod_desc），僅返回在描述中以先後順序同時出現 toy 和 carrots 的產品。提示：只需要用帶有三個 % 符號的 LIKE 即可。

示例結果

返回產品名稱和產品描述

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_desc` VARCHAR(255) NOT NULL COMMENT '產品名稱'
);
INSERT INTO `Products` VALUES ('a0011','usb'),
('a0019','iphone13'),
('b0019','gucci t-shirts'),
('c0019','gucci toy'),
('d0019','lego toy carrots ');

解答

題目中已經進行了提示，要查詢產品描述中以先後順序同時出現 toy 和 carrots 的產品，但沒有說他們兩者之間存在的字元以及 toy 前面和 carrots 後邊所包含的字元，那麼需要使用通配符 % ，得到最終的結果 %toy%carrots%。

SELECT prod_name, prod_desc FROM Products WHERE prod_desc LIKE '%toy%carrots%';

SQL20 別名

描述

別名的常見用法是在檢索出的結果中重命名錶的列欄位（為了符合特定的報表要求或客戶需求）。有表 Vendors 代表供應商信息，vend_id 供應商 id、vend_name 供應商名稱、vend_address 供應商地址、vend_city 供應商城市。

問題

編寫 SQL 語句，從 Vendors 表中檢索 vend_id、vend_name、vend_address 和 vend_city，將 vend_name 重命名為 vname，將 vend_city 重命名為 vcity，將 vend_address 重命名為 vaddress，按供應商名稱對結果進行升序排序。

示例結果

返回 vend_id 供應商 id、vname 供應商名稱、vaddress 供應商地址、vcity 供應商城市。

示例

DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT '供應商id',
  `vend_name` VARCHAR(255) NOT NULL COMMENT '供應商名稱',
  `vend_address` VARCHAR(255) NOT NULL COMMENT '供應商地址',
  `vend_city` VARCHAR(255) NOT NULL COMMENT '供應商城市'
);
INSERT INTO `Vendors` VALUES ('a001','tencent cloud','address1','shenzhen'),
('a002','huawei cloud','address2','dongguan'),
('a003','aliyun cloud','address3','alibaba');

解答

SQL 中，要對列取別名，需要用到關鍵字 AS，使用格式如下：

列名 AS 別名

通過對題目中的三個欄位取別名，然後使用 ORDER BY 關鍵字，按照供應商名稱列對結果進行升序排序。

SELECT vend_id, vend_name AS vname, vend_address AS vaddress, vend_city AS vcity FROM Vendors ORDER BY vend_name;

此外，AS 其實可要可不要，你也可以寫成以下的方式，在列名之後跟上別名即可，實現的效果和上面語句一致。

SELECT vend_id, vend_name vname, vend_address vaddress, vend_city vcity FROM Vendors ORDER BY vend_name;

SQL21 打折

描述

我們的示例商店正在進行打折促銷，所有產品均降價 10%。Products 表包含 prod_id產品 id、prod_price 產品價格

問題

編寫 SQL語句，從 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一個包含促銷價格的計算欄位。提示：可以乘以 0.9，得到原價的 90%（即 10%的折扣）

示例結果

返回產品 id prod_id、產品價格 prod_price、銷售價格 sale_price

示例解析

sale_price 的價格是 prod_price 的 90%

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_id` VARCHAR(255) NOT NULL COMMENT '產品 ID',
`prod_price` DOUBLE NOT NULL COMMENT '產品價格'
);
INSERT INTO `Products` VALUES ('a0011',9.49),
('a0019',600),
('b0019',1000);

解答

此題考察了取別名以及如何在 SQL 中直接使用算數運算，sale_price 不是表中的數據，而是通過 prod_price 而來，所以需要通過關鍵字 AS 來進行取別名，最終的實現語句如下。

SELECT prod_id, prod_price, prod_price * 0.9 AS sale_price FROM Products;

SQL22 顧客登錄名

描述

我們的商店已經上線了，正在創建顧客賬戶。所有用戶都需要登錄名，預設登錄名是其名稱和所在城市的組合。

給出 Customers 表 如下：

問題

編寫 SQL 語句，返回顧客 ID（cust_id）、顧客名稱（cust_name）和登錄名（user_login），其中登錄名全部為大寫字母，並由顧客聯繫人的前兩個字元（cust_contact）和其所在城市的前三個字元（cust_city）組成。提示：需要使用函數、拼接和別名。

示例結果

返回顧客 id cust_id，顧客名稱 cust_name，顧客登錄名 user_login

示例解析

例如，登錄名是 ANOAK（Andy Li，居住在 Oak Park）

示例

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_id VARCHAR(255) NOT NULL COMMENT '客戶id',
	cust_name VARCHAR(255) NOT NULL COMMENT '客戶姓名',
	cust_contact VARCHAR(255) NOT NULL COMMENT '客戶聯繫人',
	cust_city VARCHAR(255) NOT NULL COMMENT '客戶城市'
);
INSERT `Customers` VALUES ('a1','Andy Li','Andy Li','Oak Park'),('a2','Ben Liu','Ben Liu','Oak Park'),('a3','Tony Dai','Tony Dai','Oak Park'),('a4','Tom Chen','Tom Chen','Oak Park'),('a5','An Li','An Li','Oak Park'),('a6','Lee Chen','Lee Chen','Oak Park'),('a7','Hex Liu','Hex Liu','Oak Park');

解答

根據題意以及示例結果，可以看到登錄名全是大寫，所以需要用到函數 upper()，然後需要將顧客聯繫人前兩個字元和所在城市的前三個字元相拼接，則需要用到函數 concat()，再接著就是需要從 cust_name 和 cust_city 中截取字元串，則需要用到 substring()，最後則是通過將字元串拼接轉換之後取別名為 user_login，需要用到關鍵字 AS。以上提到的三個函數用法如下：

• upper(字元串)：將字元串中所有字元轉換為大寫。
• substring(字元串, 起始位置, 截取的字元數)，需要註意的是起始位置是從 1 開始的。
• concat(字元串 1, 字元串 2, 字元串 3, …)

SELECT cust_id, cust_name, upper(concat(substring(cust_name, 1, 2), substring(cust_city, 1, 3))) AS user_login FROM Customers;

SQL23 返回 2020 年 1 月的所有訂單的訂單號和訂單日期

描述

Orders 訂單表

問題

編寫 SQL 語句，返回 2020 年 1 月的所有訂單的訂單號（order_num）和訂單日期（order_date），並按訂單日期升序排序

【示例結果】

返回訂單號 order_num，和 order_date 訂單時間

示例解析

a0001、a0002、a0003 時間屬於 2020 年 1 月

示例

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
	order_num VARCHAR(255) NOT NULL COMMENT '訂單號',
	order_date TIMESTAMP NOT NULL COMMENT '訂單日期'
);
INSERT `Orders` VALUES ('a0001','2020-01-01 00:00:00'),
('a0002','2020-01-02 00:00:00'),
('a0003','2020-01-01 12:00:00'),
('a0004','2020-02-01 00:00:00'),
('a0005','2020-03-01 00:00:00');

解答

其實我們依然可以將本題看做模糊查詢，只要訂單日期滿足對應條件即可，可以使用關鍵字 WHERE … LIKE … 來實現。

SELECT order_num, order_date FROM Orders WHERE order_date LIKE '2020-01%' ORDER BY order_date;

此外，SQL 中也提供了對於日期的操作函數 date_format()，用於返回日期的一部分。

SELECT order_num, order_date FROM Orders WHERE date_format(order_date, '%Y-%m') = '2020-01' ORDER BY order_date;

另外，我們還可以單獨提取出日期中的年份和月份，然後將兩個條件串聯，篩選出同時滿足兩個條件的數據。

SELECT order_num, order_date FROM Orders WHERE year(order_date) = 2020 AND month(order_date) = 1 ORDER BY order_date;

SQL24 確定已售出產品的總數

描述

OrderItems 表代表售出的產品，quantity 代表售出商品數量。

問題

編寫 SQL 語句，確定已售出產品的總數。

示例結果

返回 items_ordered 列名，表示已售出商品的總數。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	quantity INT(16) NOT NULL COMMENT '商品數量'
);
INSERT `OrderItems` VALUES (10),(100),(1000),(10001),(2),(15);

解答

考察求和函數以及取別名兩個知識點，取別名需要關鍵字 AS，而且可要可不要。

而求和函數則是 SUM(列名) ，它會統計列中所有記錄的綜合。

SELECT SUM(quantity) items_ordered FROM OrderItems;

SQL25 確定已售出產品項 BR01 的總數

描述

OrderItems 表代表售出的產品，quantity 代表售出商品數量，產品項為 prod_id。

問題

修改創建的語句，確定已售出產品項（prod_id）為 "BR01" 的總數。

示例結果

返回商品項已訂購訂單數

示例解析

已訂購商品 BR01 的數量 quantity 為 1000。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	quantity INT(16) NOT NULL COMMENT '商品數量',
	prod_id VARCHAR(255) NOT NULL COMMENT '商品項'
);
INSERT `OrderItems` VALUES (10,'AR01'),(100,'AR10'),(1000,'BR01'),(10001,'BR010');

解答

主要考察的知識點：

• 求和函數：SUM()
• 取別名：AS
• 條件查詢：WHERE

需要註意的是 SQL 語句中關鍵字的先後順序，否則可能會導致語句出錯。

SELECT SUM(quantity) AS items_ordered FROM OrderItems WHERE prod_id = 'BR01';

SQL26 確定 Products 表中價格不超過 10 美元的最貴產品的價格

描述

Products 表

問題

編寫 SQL 語句，確定 Products 表中價格不超過 10 美元的最貴產品的價格（prod_price）。將計算所得的欄位命名為 max_price。

示例結果

返回 max_price

示例解析

返回十元以下最高價格 max_price。

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_price` DOUBLE NOT NULL COMMENT '產品價格'
);
INSERT INTO `Products` VALUES (9.49),
(600),
(1000);

解答

考察知識點：

• 條件查詢：使用關鍵字 WHERE，將產品價格不超過 10 美元的產品篩選出來。
• MAX(列名)：找出列中的最大值。
• 取別名：通過關鍵字 AS 將不超過 10 美元的產品中價格最高的記錄篩選出來後重命名。

SELECT MAX(prod_price) AS max_price FROM Products WHERE prod_price <= 10;

SQL27 返回每個訂單號各有多少行數

描述

OrderItems 表包含每個訂單的每個產品

問題

編寫 SQL 語句，返回每個訂單號（order_num）各有多少行數（order_lines），並按 order_lines 對結果進行升序排序。

示例結果

返回訂單號 order_num 和對應訂單號的行數 order_lines

示例解析

訂單號 a002 有 3 行訂單記錄也是最多的訂單號故排在最後一位返回，相同訂單行數的訂單無需過多處理。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品訂單號'
);
INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');

解答

考察知識點：

• COUNT(列名)：返回指定列的值的數目。
• AS：取別名。
• GROUP BY：根據指定列或者表達式的值將行進行分組。
• ORDER BY：根據尾隨的列名進行排序，ASC 表示正序，也是預設排序，DESC 表示倒序。

SELECT order_num, COUNT(order_num) AS order_lines FROM OrderItems GROUP BY order_num ORDER BY order_lines;

SQL28 每個供應商成本最低的產品

描述

有Products表，含有欄位prod_price代表產品價格，vend_id代表供應商id

問題

編寫 SQL 語句，返回名為 cheapest_item 的欄位，該欄位包含每個供應商成本最低的產品（使用 Products 表中的 prod_price），然後從最低成本到最高成本對結果進行升序排序。

示例結果

返回供應商 id vend_id 和對應供應商成本最低的產品 cheapest_item。

示例解析

例如 b0019 成本最低的價格是 20，且最後根據成本價格排序返回依次是 a0019、b0019、a0011。

示例

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '供應商ID',
`prod_price` DOUBLE NOT NULL COMMENT '產品價格'
);
INSERT INTO `Products` VALUES ('a0011',100),
('a0019',0.1),
('b0019',1000),
('b0019',6980),
('b0019',20);

解答

要找出各個供應商中成本最低的產品，則需要通過關鍵字 GROUP BY 來進行分組，然後藉助函數 MIN() 找出 prod_price 中最小的值，接著取別名為 cheapest_item，最後則是按照找出的各供應商中的成本最低產品 cheapest_item 利用關鍵字 ORDER BY 進行升序排序。

SELECT vend_id, MIN(prod_price) AS cheapest_item FROM Products GROUP BY vend_id ORDER BY cheapest_item;

SQL29 返回訂單數量總和不小於100的所有訂單的訂單號

描述

OrderItems 代表訂單商品表，包括：訂單號order_num 和訂單數量 quantity。

問題

請編寫 SQL 語句，返回訂單數量總和不小於 100 的所有訂單號，最後結果按照訂單號升序排序。

示例結果

返回 order_num 訂單號。

示例解析

訂單號 a1、a2、a4 的 quantity 總和都大於等於 100，按順序為 a1、a2、a4。

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品訂單號',
	quantity INT(255) NOT NULL COMMENT '商品數量'
);
INSERT `OrderItems` VALUES ('a1',105),('a2',200),('a4',1121),('a5',10),('a7',5);

解答

條件查詢，只是此時不再是過濾指定的行，而是需要過濾分組，所以這個時候不能再使用關鍵字 WHERE，而是需要使用到關鍵字 HAVING，它通常是和關鍵字 GROUP BY 連用。另外需要註意的是各個關鍵字之間的先後順序，先是 GROUP BY，緊接著是 HAVING，最後才是 ORDER BY。

SELECT order_num FROM OrderItems GROUP BY order_num HAVING SUM(quantity) >= 100 ORDER BY order_num;

SQL30 計算總和

描述

OrderItems表代表訂單信息，包括欄位：訂單號 order_num 和 item_price 商品售出價格、quantity 商品數量。

問題

編寫 SQL 語句，根據訂單號聚合，返回訂單總價不小於 1000 的所有訂單號，最後的結果按訂單號進行升序排序。

提示：總價 = item_price 乘以 quantity

示例結果

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品訂單號',
	item_price INT(16) NOT NULL COMMENT '售出價格',
	quantity INT(16) NOT NULL COMMENT '商品數量'
);
INSERT `OrderItems` VALUES ('a1',10,105),('a2',1,1100),('a2',1,200),('a4',2,1121),('a5',5,10),('a2',1,19),('a7',7,5);

解答

以上幾題都已經將知識點講過了，這題只是綜合運用，主要涉及如下：

• SUM()：對同一產品的總價求和。
• AS：取別名。
• GROUP BY：按照列進行分組。
• HAVING：與 GROUP BY 聯合使用從而實現條件過濾。
• ORDER BY：按列進行排序。

SELECT order_num, SUM(item_price * quantity) AS total_price FROM OrderItems GROUP BY order_num HAVING total_price >= 1000 ORDER BY order_num;

SQL31 糾錯3

描述

OrderItems 表含有 order_num 訂單號

問題

將下麵代碼修改正確後執行

SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

示例結果

返回訂單號 order_num 和出現的次數 items

示例解析

由於訂單號 a002 出現了三次，所以返回3

示例

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	order_num VARCHAR(255) NOT NULL COMMENT '商品訂單號'
);
INSERT `OrderItems` VALUES ('a002'),('a002'),('a002'),('a004'),('a007');

解答

最主要的錯誤在於 GROUP BY 後邊跟著的是統計結果，其次在於 HAVING 後邊的 COUNT() 其實是可以不用再次計算的，可以直接利用已經統計出的結果。第二處不算錯誤，但是改了之後能提高 SQL 語句所執行的效率。

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items, order_num;

SELECT order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING items >= 3 ORDER BY items, order_num;

SQL32 返回購買價格為 10 美元或以上產品的顧客列表

描述

OrderItems 表示訂單商品表，含有欄位訂單號：order_num、訂單價格：item_price；Orders 表代表訂單信息表，含有顧客 id：cust_id 和訂單號：order_num

OrderItems 表

Orders表

您的分享是我們最大的動力!

-Advertisement-