第七章 Transact-SQL 查詢

CREATE TABLE fruits

(

f_id    char(10)   PRIMARY KEY,        --水果id

s_id    INT             NOT NULL,      --供應商id

f_name  VARCHAR(255)      NOT NULL,    --水果名稱

f_price decimal(8,2)  NOT NULL,        --水果價格

);

INSERT INTO fruits (f_id, s_id, f_name, f_price)

VALUES('a1', 101,'apple',5.2),

  ('b1',101,'blackberry', 10.2),

  ('bs1',102,'orange', 11.2),

  ('bs2',105,'melon',8.2),

  ('t1',102,'banana', 10.3),

  ('t2',102,'grape', 5.3),

  ('o2',103,'coconut', 9.2),

  ('c0',101,'cherry', 3.2),

  ('a2',103, 'apricot',2.2),

  ('l2',104,'lemon', 6.4),

  ('b2',104,'berry', 7.6),

  ('m1',106,'mango', 15.6);

  CREATE TABLE customers

(

  c_id      int       PRIMARY KEY,

  c_name    varchar(50)  NOT NULL,

  c_address varchar(50)  NULL,

  c_city    varchar(50)  NULL,

  c_zip     varchar(10)  NULL,

  c_contact varchar(50)  NULL,

  c_email   varchar(255) NULL

);

【例7.1】從fruits表中檢索所有字段的資料，T-SQL語句如下：

SELECT * FROM fruits;

【例7.2】例如，從fruits表中擷取f_name和f_price兩列，T-SQL語句如下：

SELECT f_name, f_price FROM fruits;

【例7.3】查詢fruits表中s_id字段的值，并傳回s_id字段值不得重複，T-SQL語句如下：

SELECT DISTINCT s_id FROM fruits;

【例7.4】從fruits表中選取頭3條記錄，

SELECT TOP (3) * FROM fruits;

【例7.5】從fruits表中選取前30%的記錄，

SELECT TOP 30 PERCENT * FROM fruits;

【例7.6】查詢fruits表，為f_name取别名名稱，f_price取别名價格， T-SQL語句如下：

SELECT f_name AS fruit_name, f_price AS fruit_price

FROM fruits;

【例7.7】查詢fruits表，對表中的s_id和f_id添加說明資訊，

SELECT ‘供應商編号：’, s_id,’水果編号’,f_id FROM fruits;

【例7.8】查詢fruits表中所有水果的名稱和價格，并對價格打八折，

SELECT f_name, f_price 原價,f_price * 0.8 折扣價 

FROM fruits;

【例7.9】查詢價格為10.2元的水果的名稱，T-SQL語句如下：

SELECT f_name, f_price

FROM fruits

WHERE f_price = 10.2;

【例7.10】查找名稱為“apple”的水果的價格，T-SQL語句如下：

SELECT f_name, f_price

FROM fruits

WHERE f_name = 'apple';

【例7.11】查詢價格小于10的水果的名稱，T-SQL語句如下：

SELECT f_name, f_price

FROM fruits

WHERE f_price < 10;

【例7.12】查詢價格在2.00元到10.5元之間水果名稱和價格，T-SQL語句如下：

SELECT f_name, f_price FROM fruits WHERE f_price BETWEEN 2.00 AND 10.20;

【例7.13】查詢價格在2.00元到10.5元之外的水果名稱和價格，T-SQL語句如下：

SELECT f_name, f_price

FROM fruits 

WHERE f_price NOT BETWEEN 2.00 AND 10.20;

【例7.14】查詢s_id為101和102的記錄，T-SQL語句如下：

SELECT s_id,f_name, f_price 

FROM fruits 

WHERE s_id IN (101,102) 

【例7.15】查詢所有s_id不等于101也不等于102的記錄，T-SQL語句如下：

SELECT s_id,f_name, f_price

FROM fruits

WHERE s_id NOT IN (101,102);

【例7.16】查找所有以‘b’字母開頭的水果，T-SQL語句如下：

SELECT f_id, f_name

FROM fruits

WHERE f_name LIKE 'b%';

【例7.17】在fruits表中，查詢f_name中包含字母‘g’的記錄，T-SQL語句如下：

SELECT f_id, f_name

FROM fruits

WHERE f_name LIKE '%g%';

【例7.18】查詢以‘b’開頭，并以‘y’結尾的水果的名稱，T-SQL語句如下：

SELECT f_name

FROM fruits

WHERE f_name LIKE 'b%y';

【例7.19】在fruits表中，查詢以字母‘y’結尾，且‘y’前面隻有4個字母的記錄，T-SQL語句如下：

SELECT f_id, f_name FROM fruits WHERE f_name LIKE '____y';

【例7.20】在fruits表中，查找f_name字段值中以字母‘abc’三個字母之一開頭的記錄，長度為7個字元的記錄，T-SQL語句如下：

SELECT * FROM fruits 

WHERE f_name LIKE '[abc]%'; 

【例7.21】在fruits表中，查找f_name字段值中不是以字母‘abc’三個字母之一開頭的記錄，SQL語句如下：

SELECT * FROM fruits 

WHERE f_name LIKE '[^abc]%';

【例7.22】查詢customers表中c_email字段為空的記錄的c_id、c_name和c_email字段值，SQL語句如下：

SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NULL;

【例7.23】查詢customers表中c_email不為空的記錄的c_id、c_name和c_email字段值，T-SQL語句如下：

SELECT c_id, c_name,c_email FROM customers WHERE c_email IS NOT NULL;

【例7.24】查詢表suppliers表中是否存在s_id=107的供應商，如果存在則查詢fruits表中的記錄，T-SQL語句如下：

SELECT * FROM fruits

WHERE EXISTS

(SELECT s_name FROM suppliers WHERE s_id = 107);

【例7.25】查詢表suppliers表中是否存在s_id=107的供應商，如果存在則查詢fruits表中的f_price大于10.20的記錄，T-SQL語句如下：

SELECT * FROM fruits

WHERE f_price>10.20 AND EXISTS

(SELECT s_name FROM suppliers WHERE s_id = 107);

【例7.26】查詢fruits表的f_name字段值，并對其進行排序，T-SQL語句如下：

SELECT f_name FROM fruits ORDER BY f_name;

【例7.27】查詢fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序，SQL語句如下：

SELECT f_name, f_price FROM fruits ORDER BY f_name, f_price;

【例7.28】查詢fruits表中的f_name和f_price字段，對結果按f_price降序方式排序，T-SQL語句如下：

SELECT f_name, f_price FROM fruits ORDER BY f_price DESC;

【例7.29】根據s_id對fruits表中的資料進行分組，T-SQL語句如下：

SELECT s_id, COUNT(*) AS Total FROM fruits GROUP BY s_id;

【例7.30】根據s_id和f_name字段對fruits表中的資料進行分組， T-SQL語句如下，

SELECT s_id,f_name FROM fruits group by s_id,f_name;

【例7.31】根據s_id對fruits表中的資料進行分組，并顯示水果種類大于1的分組資訊，T-SQL語句如下：

SELECT s_id, COUNT(*) AS Total FROM fruits 

GROUP BY s_id HAVING COUNT(*) > 1;

【例7.32】使用COMPUTE子句生産fruits表中price字段的總和，T-SQL語句如下：

SELECT s_id, f_price,f_name FROM fruits 

ORDER BY s_id 

COMPUTE SUM(f_price);

【例7.33】按s_id字段分組顯示并統計各個分組中f_price字段值的總和， T-SQL語句如下：

SELECT s_id, f_price,f_name FROM fruits 

ORDER BY s_id 

COMPUTE SUM(f_price) BY s_id;

【例7.34】查詢所有價格小于9的水果的資訊，查詢s_id等于101所有的水果的資訊，使用UNION ALL連接配接查詢結果，T-SQL語句如下：

SELECT s_id, f_name, f_price 

FROM fruits

WHERE f_price < 9.0

UNION ALL

SELECT s_id, f_name, f_price 

FROM fruits

WHERE s_id =101;

【例7.35】查詢所有價格小于9的水果的資訊，查詢s_id等于101所有的水果的資訊，，T-SQL語句如下：

SELECT s_id, f_name, f_price 

FROM fruits

WHERE f_price < 9.0

UNION 

SELECT s_id, f_name, f_price 

FROM fruits

WHERE s_id =101;

【例7.36】在orderitems表中查詢30005号訂單一共購買的水果總量，T-SQL語句如下：

SELECT SUM(quantity) AS items_total

FROM orderitems

WHERE o_num = 30005;

【例7.37】在orderitems表中，使用SUM()函數統計不同訂單号中訂購的水果總量，T-SQL語句如下：

SELECT o_num, SUM(quantity) AS items_total

FROM orderitems

GROUP BY o_num;

【例7.38】在fruits表中，查詢s_id=103的供應商的水果價格的平均值，T-SQL語句如下：

SELECT AVG(f_price) AS avg_price

FROM fruits

WHERE s_id = 103;

【例7.39】在fruits表中，查詢每一個供應商的水果價格的平均值，T-SQL語句如下：

SELECT s_id,AVG(f_price) AS avg_price

FROM fruits

GROUP BY s_id;

【例7.40】在fruits表中查找市場上價格最高的水果，T-SQL語句如下：

SELECT MAX(f_price) AS max_price FROM fruits;

【例7.41】在fruits表中查找不同供應商提供的價格最高的水果，T-SQL語句如下：

SELECT s_id, MAX(f_price) AS max_price

FROM fruits

GROUP BY s_id;

【例7.42】在fruits表中查找f_name的最大值，T-SQL語句如下：

SELECT MAX(f_name) FROM fruits;

【例7.43】在fruits表中查找市場上水果的最低價格，T-SQL語句如下：

SELECT MIN(f_price) AS min_price FROM fruits;

【例7.44】在fruits表中查找不同供應商提供的價格最低的水果，T-SQL語句如下：

SELECT s_id, MIN(f_price) AS min_price

FROM fruits

GROUP BY s_id;

【例7.45】查詢customers表中總的行數，T-SQL語句如下：

SELECT COUNT(*) AS 客戶總數 

FROM customers;

【例7.46】查詢customers表中有電子郵箱的客戶的總數，T-SQL語句如下：

SELECT COUNT(c_email) AS email_num

FROM customers;

【例7.47】在orderitems表中，使用COUNT()函數統計不同訂單号中訂購的水果種類，T-SQL語句如下：

SELECT o_num '訂單号', COUNT(f_id) '訂購數量' 

FROM orderitems 

GROUP BY o_num;

【例7.48】在suppliers表中查詢s_city等于“Tianjin”的供應商s_id，然後在fruits表中查詢所有該供應商提供的水果的種類，T-SQL語句如下：

SELECT s_id, f_name FROM fruits

WHERE s_id =

(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

【例7.49】在suppliers表中查詢s_city等于“Tianjin”的供應商s_id，然後在fruits表中查詢所有非該供應商提供的水果的種類，T-SQL語句如下：

SELECT s_id, f_name FROM fruits

WHERE s_id <>

(SELECT s1.s_id FROM suppliers AS s1 WHERE s1.s_city = 'Tianjin');

【例7.50】在orderitems表中查詢訂購f_id為c0的訂單号，并根據訂單号查詢具有訂單号的客戶c_id，T-SQL語句如下：

SELECT c_id FROM orders WHERE o_num IN

(SELECT o_num  FROM orderitems WHERE f_id = 'c0');

【例7.51】與前一個例子語句類似，但是在SELECT語句中使用NOT IN操作符，T-SQL語句如下：

SELECT c_id FROM orders WHERE o_num NOT IN

(SELECT o_num  FROM orderitems WHERE f_id = 'c0');

CREATE table tbl1 ( num1 INT NOT NULL);

CREATE table tbl2 ( num2 INT NOT NULL);

INSERT INTO tbl1 values(1), (5), (13), (27);

INSERT INTO tbl2 values(6), (14), (11), (20);

【例7.52】傳回tbl2表的所有num2列，然後将tbl1中的num1的值與之進行比較，隻要大于num2的任何值為符合查詢條件的結果。

SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);

【例7.53】傳回tbl1表的中比tbl2表num2 列所有值都大的值，T-SQL語句如下：

SELECT num1 FROM tbl1 WHERE num1 > ALL (SELECT num2 FROM tbl2);

【例7.54】查詢表suppliers表中是否存在s_id=107的供應商，如果存在則查詢fruits表中的記錄，T-SQL語句如下：

SELECT * FROM fruits

WHERE EXISTS

(SELECT s_name FROM suppliers WHERE s_id = 107);

【例7.55】查詢表suppliers表中是否存在s_id=107的供應商，如果存在則查詢fruits表中的f_price大于10.20的記錄，T-SQL語句如下：

SELECT * FROM fruits

WHERE f_price>10.20 AND EXISTS

(SELECT s_name FROM suppliers WHERE s_id = 107);

【例7.56】查詢表suppliers表中是否存在s_id=107的供應商，如果不存在則查詢fruits表中的記錄，T-SQL語句如下：

SELECT * FROM fruits

WHERE NOT EXISTS

(SELECT s_name FROM suppliers WHERE s_id = 107);

【例7.57】在fruits表和suppliers表之間使用INNER JOIN文法進行内連接配接查詢，T-SQL語句如下：

SELECT suppliers.s_id, s_name,f_name, f_price

FROM fruits INNER JOIN suppliers

ON fruits.s_id = suppliers.s_id;

【例7.58】在fruits表和suppliers表之間使用INNER JOIN文法進行内連接配接查詢，T-SQL語句如下：

SELECT suppliers.s_id, s_name,f_name, f_price

FROM fruits INNER JOIN suppliers

ON fruits.s_id <> suppliers.s_id;

【例7.59】在customers表和orders表中，使用INNER JOIN文法查詢customers表中ID為10001的客戶的訂單資訊，T-SQL語句如下：

SELECT customers.c_id, orders.o_num

FROM customers INNER JOIN orders

ON customers.c_id = orders.c_id AND customers.c_id = 10001;

【例7.60】查詢f_id=’a1’的水果供應商提供的其他水果種類，T-SQL語句如下：

SELECT f1.f_id, f1.f_name

FROM fruits AS f1, fruits AS f2

WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

【例7.61】在customers表和orders表中，查詢所有客戶，包括沒有訂單的客戶，T-SQL語句如下：

SELECT customers.c_id, orders.o_num

FROM customers LEFT OUTER JOIN orders

ON customers.c_id = orders.c_id;

【例7.62】在customers表和orders表中，查詢所有訂單，包括沒有客戶的訂單，T-SQL語句如下：

SELECT customers.c_id, orders.o_num

FROM customers RIGHT OUTER JOIN orders

ON customers.c_id = orders.c_id;

【例7.63】在customers表和orders表中，使用全外連接配接查詢，T-SQL語句如下。

SELECT customers.c_id, orders.o_num

FROM customers FULL OUTER JOIN orders

ON customers.c_id = orders.c_id;

【例7.64】使用ROW_NUMBER函數查詢的結果進行分組排序，

SELECT ROW_NUMBER() OVER (ORDER BY s_id ASC) AS ROWID,s_id,f_name

FROM fruits;

【例7.65】使用RANK函數對根據s_id字段查詢的結果進行分組排序，

SELECT RANK() OVER (ORDER BY s_id ASC) AS RankID,s_id,f_name

FROM fruits;

【例7.66】使用DENSE_RANK函數對根據s_id字段查詢的結果進行分組排序，

SELECT DENSE_RANK() OVER (ORDER BY s_id ASC) AS DENSEID,s_id,f_name

FROM fruits;

【例7.67】根據使用NTILE函數對根據s_id字段查詢的結果進行分組排序，

SELECT NTILE(5) OVER (ORDER BY s_id ASC) AS NTILEID,s_id,f_name

FROM fruits;

【例7.68】使用動态生成的SQL語句完成對fruits表的查詢，T-SQL語句如下。

DECLARE @id INT;

declare @sql varchar(8000)

SELECT @id=101;

SELECT @sql ='SELECT f_name, f_price

FROM fruits

WHERE s_id = ';

exec(@sql + @id );