SQL必知必会刷题总结

在这里插入代码片

《SQL必知必会（第5版）》独家授权

作为一名靠代码吃饭的技术人员，怎么能一天不敲代码呢（doge）
mysql是最基础且必须掌握的一项技能，sql语句刷起来很快，就大概花了一周的时间将mysql必知必会，以及mysql快速入门，刷完了
在多表查询处还是有一些不熟练的地方
sql执行顺序
from
where
group by
having
聚合函数
select
order by

1.检索数据（select的使用）

1. 从 Customers 表中检索所有的 ID ：select cust_id from Customers

2. SQL61 检索并列出已订购产品的清单 ：select distinct prod_id from OrderItems;
   使用distinct(关键词distinct用于返回唯一不同的值)
   3.SQL62 检索所有列： SELECT * FROM Customers;

2. 排序检索数据 (关于order by的使用）

1.SQL63 检索顾客名称并且排序：
select cust_name from Customers
order by cust_name DESC;
（order by 列名 ）默认按照某列升序排列，（order by 列名 desc ）则是降序排列

2.SQL64 对顾客ID和日期排序:
select cust_id,order_num from Orders
order by cust_id ,order_date DESC;
3.SQL65 按照数量和价格排序:
select quantity,item_price
from OrderItems
order by quantity DESC,item_price DESC;

4.SQL66 检查SQL语句:
select vend_name
from Vendors
ORDER BY vend_name DESC;

3.过滤数据 （使用where对数据进行过滤）

1.SQL67 返回固定价格的产品：
SELECT prod_id,prod_name
from Products
where prod_price=9.49;

2.SQL68 返回更高价格的产品：
select prod_id,prod_name
from Products
where prod_price >=9;

3.SQL69 返回产品并且按照价格排序:(between的使用）
select prod_name,prod_price
from Products
where prod_price between 3 and 6
order by prod_price

4.高级数据过滤

1.SQL71 检索供应商名称：
select vend_name from Vendors
where vend_country=‘USA’ and vend_state=‘CA’;

2.SQL72 检索并列出已订购产品的清单：
select order_num,prod_id,quantity from OrderItems
where quantity>=100 and prod_id in (‘BR01’,‘BR02’,‘BR03’);

3.SQL73 返回所有价格在 3美元到 6美元之间的产品的名称和价格：
select prod_name,prod_price from Products
where prod_price between 3 and 6
order by prod_price ASC;

4.SQL74 纠错2：
select vend_name from Vendors
where
vend_country = ‘USA’
AND vend_state = ‘CA’
order by vend_name;

5.用通配符进行过滤

like用法：
%表示任何字符出现任意次数
_表示单个字符
[]表示一个字符集
1.SQL75 检索产品名称和描述（一）:
select prod_name,prod_desc
from Products
where prod_desc like ‘%toy%’;

2.SQL76 检索产品名称和描述（二）:
select prod_name,prod_desc
from Products
where prod_desc not like ‘%toy’
order by prod_name ;

3.SQL77 检索产品名称和描述（三）:
select prod_name ,prod_desc from Products
#where prod_desc like ‘%carrots%toy%’;
where prod_desc like ‘%carrot%’ and prod_desc like ‘%toy%’;

4.SQL78 检索产品名称和描述（四）
select prod_name,prod_desc
from Products
where prod_desc like ‘%toy%carrots%’;

6.创建计算字段

1.SQL79 别名：as 可以省略，直接写别名
select vend_id,vend_name as vname,
vend_address as vaddress,
vend_city as vcity
from Vendors
order by vname ASC;

2.SQL80 打折：
*select prod_id,prod_price,
prod_price 0.9 as sale_price
from Products;

7.使用函数处理数据

1.SQL81 顾客登录名：
/
字符串的截取：substring(字符串，起始位置，截取字符数）
字符串的拼接：concat(字符串1，字符串2，字符串3,…)
字母大写：upper(字符串）
/
select
cust_id,
cust_name,
upper(concat(substring(cust_name,1,2),substring(cust_city,1,3))) as user_login
from
Customers;
2.SQL82 返回 2020 年 1 月的所有订单的订单号和订单日期
select order_num,order_date
from Orders
where order_date like ‘2020-01%’
order by order_date;

8.汇总数据

1.SQL86 返回每个订单号各有多少行数:
select order_num ,
count(order_num) as order_lines
from OrderItems
group by order_num
order by order_lines;
知识点：
1、count(*),count(列名)都可以，区别在于，count(列名)是统计非NULL的行数
2、order by最后执行，所以可以使用列别名
3、分组聚合一定不要忘记加上 group by ,不然只会有一行结果

2.SQL84 确定已售出产品项 BR01 的总数:
select sum(quantity) as items_ordered
from OrderItems
where prod_id=‘BR01’;

3.SQL85 确定 Products 表中价格不超过 10 美元的最贵产品的价格:
SELECT max(prod_price) as max_price
from Products
where prod_price <=10;
用法：
最大值—max()
最小值—min()
平均值—avg()
总值 —sum()
总数 —count()

9.分组数据

1.SQL86 返回每个订单号各有多少行数：
知识点：1.sum函数是计算数字的求和，然而count是计算的这一列的总和
2.group by 和order by连用的时候要注意，order by要在后面
sql中关键字出现的顺序是：select/from/where/group by/having/order by/limit
select
order_num,
count(order_num) order_lines
from OrderItems
group by order_num
order by order_lines asc ;
2.SQL87 每个供应商成本最低的产品：
select vend_id, min(prod_price) as cheapest_item
from Products
group by vend_id
order by cheapest_item;
3.SQL88 返回订单数量总和不小于100的所有订单的订单号:
用法：
where—过滤过滤指定的行
having–过滤分组，与group by连用
select order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num;
4.SQL89 计算总和:*
注意：
where后面不能加聚合函数！！！！！
having必须和group by联合使用
select order_num,sum(item_pricequantity) total_price
from OrderItems
group by order_num
having sum(item_pricequantity)>=1000
order by order_num;

10.使用子查询

从这之后可二刷
1.SQL91 返回购买价格为 10 美元或以上产品的顾客列表:
select cust_id
from Orders
where order_num in (
select order_num
from OrderItems
where item_price>=10
)
2.SQL92 确定哪些订单购买了 prod_id 为 BR01 的产品（一）
select cust_id,order_date
from Orders
where order_num in (
select order_num
from OrderItems
where prod_id =‘BR01’
)
order by order_date;
3.SQL93 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（一）:
select cust_email
from Customers
where cust_id in(
select cust_id
from Orders
where order_num in(
select order_num
from OrderItems
where prod_id=‘BR01’
)
)
4.SQL94 返回每个顾客不同订单的总金额:
将聚合的字段放在select后,用where+关联条件关联即可
select cust_id,(
select sum(item_price*quantity)
from OrderItems
where OrderItems.order_num=Orders.order_num
) as total_ordered
from Orders
order by total_ordered desc;
5.SQL95 从 Products 表中检索所有的产品名称以及对应的销售总数:
select prod_name, sum(quantity) as quant_sold
from Products,OrderItems
where Products.prod_id=OrderItems.prod_id
group by prod_name

11.链结表

1.SQL96 返回顾客名称和相关订单号SQL96 返回顾客名称和相关订单号：
**/select cust_name,order_num
from Customers,Orders
where Customers.cust_id=Orders.cust_id
order by cust_name;
/
select cust_name,order_num
from Customers
inner join Orders on Customers.cust_id=Orders.cust_id
order by cust_name;

2.SQL97 返回顾客名称和相关订单号以及每个订单的总价：
/有一个聚类sum，其他的必须也使用聚类
select cust_name,t2.order_num,sum(item_pricequantity) as OrderTotal
from Customers t1
inner join Orders t2 on t1.cust_id=t2.cust_id
inner join OrderItems t3 on t2.order_num=t3.order_num
group by cust_name,t2.order_num
order by cust_name,t2.order_num;
/
#使用where
select t1.cust_name,t2.order_num,sum(quantityitem_price)
from Customers t1,Orders t2,OrderItems t3
where t1.cust_id=t2.cust_id and t2.order_num=t3.order_num
group by t1.cust_name,t2.order_num
order by t1.cust_name,t2.order_num;

3.SQL98 确定哪些订单购买了 prod_id 为 BR01 的产品（二）：
select cust_id,order_date
from Orders
where order_num in (
select order_num from OrderItems
where prod_id =‘BR01’
)
order by order_date;

4.SQL99 返回购买 prod_id 为 BR01 的产品的所有顾客的电子邮件（二）
select cust_email
from Orders
inner join OrderItems on Orders.order_num=OrderItems.order_num
inner join Customers on Orders.cust_id=Customers.cust_id
where prod_id=‘BR01’

5.SQL100 确定最佳顾客的另一种方式（二）:
用两个 inner join把三张表联结起来，再根据 cust_name分组，之后用having过滤不大于1000的顾客
出现聚合函数sum，要用group by
having–过滤分组，与group by连用
select t3.cust_name,sum(item_price*quantity) as total_price
from Customers t3
inner join Orders t2 using(cust_id)
inner join OrderItems t1 using(order_num)
group by cust_name
having total_price>=1000
order by total_price;

12.创建高级联结

1.SQL101 检索每个顾客的名称和所有的订单号（一）：
/
select t1.cust_name,t2.order_num
from Customers t1,Orders t2
where t1.cust_id=t2.cust_id
order by cust_name;
/
select cust_name,t2.order_num
from Customers t1
inner join Orders t2 on t1.cust_id=t2.cust_id
order by cust_name;
2.SQL102 检索每个顾客的名称和所有的订单号（二）：
/
内联结： inner join ：取两列的交集
外连接：
left join :左连接，以左边列表为主，取俩列的交集，对于不在右边列的名称取null
right join：右连接，以右边表的列为主，取两列的交集，对于不在左边列存在的名称取null。
/
select t1.cust_name,t2.order_num
from Customers t1
left join Orders t2 using(cust_id)
order by t1.cust_name;
3.SQL103 返回产品名称和与之相关的订单号：
select prod_name,order_num
from Products
left join OrderItems using(prod_id)
order by prod_name;

4.SQL104 返回产品名称和每一项产品的总订单数：
#outer join :外连接包括left join ,right join
select prod_name,count(order_num) as orders
from Products
left join OrderItems using(prod_id)
group by prod_name
order by prod_name;

5.SQL105 列出供应商及其可供产品的数量：
select vend_id,count(prod_id) as prod_id
from Vendors
left join Products using(vend_id)
group by vend_id
order by vend_id;

13.组合查询

1.SQL106 将两个 SELECT 语句结合起来（一）:
**/select * from OrderItems
where quantity=100
union
select * from OrderItems
where prod_id like ‘BNBG%’
order by prod_id;
/
select * from OrderItems
where quantity in (
select quantity from OrderItems
where quantity = 100
) or prod_id like ‘BNBG%’
order by prod_id;

2.SQL107 将两个 SELECT 语句结合起来（二）
#将两个 SELECT 语句结合起来:1.union 2.子查询
#注意：这次仅使用单个 SELECT 语句。
select * from OrderItems
where quantity =100 or prod_id like ‘BNBG%’
order by prod_id;

3.SQL108 组合 Products 表中的产品名称和 Customers 表中的顾客名称:
select prod_name from Products
union
select cust_name as prod_name from Customers
order by prod_name;

4.SQL109 纠错4:
#使用union组合查询时，只能使用一条order by字句，他必须位于最后一条select语句之后，
#因为对于结果集不存在对于一部分数据进行排序，而另一部分用另一种排序规则的情况
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = ‘MI’
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state = 'IL’ORDER BY cust_name;