第七章 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 );