牛客网SQL训练3—SQL必知必会

毛媛媛

已于 2023-12-28 11:02:19 修改

阅读量986

点赞数 15

分类专栏：数据分析四大工具之二：SQL 文章标签： sql 数据库数据挖掘学习笔记

于 2023-12-15 14:15:04 首次发布

本文链接：https://blog.csdn.net/myy12456789/article/details/135013184

版权

数据分析四大工具之二：SQL 专栏收录该内容

10 篇文章 1 订阅

订阅专栏

文章目录

一、检索数据
二、排序检索数据
三、过滤数据
四、高级数据过滤
五、用通配符进行过滤
六、创建计算字段
七、使用函数处理数据
八、汇总数据
九、分组数据
十、使用子查询
十一、联结表
十二、创建高级联结
十三、组合查询

一、检索数据

【题目1：从 Customers 表中检索所有的 ID】

--输入：
DROP TABLE IF EXISTS `Customers`;

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

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

在这里插入图片描述

select cust_id from Customers;

在这里插入图片描述

【题目2：检索并列出已订购产品的清单】

--输入：
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')

在这里插入图片描述

select 
	prod_id 
from OrderItems
group by prod_id
;

在这里插入图片描述

【题目3：检索所有列】

--输入：
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;

在这里插入图片描述

二、排序检索数据

【题目1：检索顾客名称并且排序】

--输入：
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 
	cust_name 
from Customers
order by cust_name desc
;

在这里插入图片描述

【题目2：对顾客ID和日期排序】

--输入：
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');

在这里插入图片描述

select 
	cust_id
	,order_num
from Orders
order by cust_id asc,order_date desc
;

在这里插入图片描述

【题目3：按照数量和价格排序】

--输入：
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);

在这里插入图片描述

select
	quantity
	,item_price
from OrderItems
order by quantity desc,item_price desc
;

在这里插入图片描述

【题目4：检查SQL语句】

--输入：
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;

#-- 修改后
SELECT vend_name
FROM Vendors 
ORDER BY vend_name DESC;

在这里插入图片描述

三、过滤数据

【题目1：返回固定价格的产品】

--输入：
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);

在这里插入图片描述

select 
	prod_id
	,prod_name
from Products
where prod_price='9.49'
;

在这里插入图片描述

【题目2：返回更高价格的产品】

--输入：
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 
	prod_id
	,prod_name
from Products
where prod_price>='9'
;

在这里插入图片描述

【题目3：返回产品并且按照价格排序】

--输入：
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);

在这里插入图片描述

select 
	prod_name
	,prod_price
from Products
where prod_price between '3' and '6'
order by prod_price
;

在这里插入图片描述

【题目4：返回更多的产品】

--输入：
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')

在这里插入图片描述

select 
	order_num
from OrderItems
group by order_num 
having sum(quantity)>=100
;

在这里插入图片描述

四、高级数据过滤

【题目1：检索供应商名称】

--输入：
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');

在这里插入图片描述

select 
	vend_name
from Vendors
where vend_country='USA' and vend_state='CA'
;

在这里插入图片描述

【题目2：检索并列出已订购产品的清单】

--输入：
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')

在这里插入图片描述

select 
	order_num
	,prod_id
	,quantity
from OrderItems
where prod_id in ('BR01','BR02','BR03')
and quantity>100
;

在这里插入图片描述

【题目3：返回所有价格在 3美元到 6美元之间的产品的名称和价格】

--输入：
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);

在这里插入图片描述

select
	prod_name
	,prod_price
from Products
where prod_price between 3 and 6
order by prod_price
;

在这里插入图片描述

【题目4：纠错2】

--输入：
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');

在这里插入图片描述

#-- 修改前
SELECT vend_name 
FROM Vendors 
ORDER BY vend_name 
WHERE vend_country = 'USA' AND vend_state = 'CA';

#-- 修改后
SELECT vend_name 
FROM Vendors 
WHERE vend_country = 'USA' AND vend_state = 'CA'
ORDER BY vend_name;

在这里插入图片描述

五、用通配符进行过滤

【题目1：检索产品名称和描述 (一)】

--输入：
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');

在这里插入图片描述

select 
	prod_name
	,prod_desc
from Products
where prod_desc like '%toy%'
;

在这里插入图片描述

【题目2：检索产品名称和描述(二)】

--输入：
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');

在这里插入图片描述

select 
	prod_name
	,prod_desc
from Products
where prod_desc not like '%toy%'
order by prod_name
;

在这里插入图片描述

【题目3：检索产品名称和描述(三)】

--输入：
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');

在这里插入图片描述

select 
	prod_name
	,prod_desc
from Products
where prod_desc like '%toy%' and prod_desc like '%carrots%' 
;

在这里插入图片描述

【题目4：检索产品名称和描述 (四)】

--输入：
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 ');

在这里插入图片描述

select 
	prod_name
	,prod_desc
from Products
where prod_desc like '%toy%carrots%'  
;

在这里插入图片描述

六、创建计算字段

【题目1：别名】

--输入：
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');

在这里插入图片描述

select 
	vend_id
	,vend_name 		vname
	,vend_address  	vaddress	
	,vend_city      vcity
from Vendors
order by vend_name
;

在这里插入图片描述

【题目2：打折】

--输入：
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);

在这里插入图片描述

select 
	prod_id	
	,prod_price
	,prod_price*0.9 sale_price
from Products
;

在这里插入图片描述

七、使用函数处理数据

【题目1：顾客登录名】

--输入：
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');

在这里插入图片描述

select
	cust_id	
	,cust_name	
	,upper(concat(substr(cust_contact,1,2),substr(cust_city,1,3))) user_login
from Customers
;

在这里插入图片描述

【题目2：返回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');

在这里插入图片描述

select
	order_num	
	,order_date
from Orders
where substr(order_date,1,7)='2020-01'
order by order_date
;

在这里插入图片描述

八、汇总数据

【题目1：确定已售出产品的总数】

--输入：
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);

在这里插入图片描述

select 
	sum(quantity) items_ordered
from OrderItems
;

在这里插入图片描述

【题目2：确定已售出产品项 BRO1 的总数】

--输入：
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');

在这里插入图片描述

select 
	sum(quantity) items_ordered
from OrderItems
where prod_id='BR01'
;

在这里插入图片描述

【题目3：确定Products表中价格不超过10美元的最贵产品的价格】

--输入：
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);

在这里插入图片描述

select 
	max(prod_price) max_price
from Products
where prod_price<=10
;

在这里插入图片描述

九、分组数据

【题目1：返回每个订单号各有多少行数】

--输入：
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');

在这里插入图片描述

select 
	order_num
	,count(order_num) order_lines
from OrderItems
group by order_num
order by order_lines
;

在这里插入图片描述

【题目2：每个供应商成本最低的产品】

--输入：
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);

在这里插入图片描述

select
	vend_id
	,min(prod_price) cheapest_item
from Products
group by vend_id
order by cheapest_item
;

在这里插入图片描述

【题目3：确定最佳顾客】

--输入：
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);

在这里插入图片描述

select 
	order_num
from OrderItems
group by order_num
having sum(quantity)>=100
order by order_num
;

在这里插入图片描述

【题目4：确定最佳顾客的另一种方式 (一)】

--输入：
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);

在这里插入图片描述

select 
	order_num
	,sum(item_price*quantity) total_price
from OrderItems
group by order_num
having sum(item_price*quantity)>=1000
order by order_num
;

在这里插入图片描述

【题目5：纠错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');

在这里插入图片描述

#-- 修改前
SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY items 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

#-- 修改后
SELECT order_num, COUNT(*) AS items 
FROM OrderItems 
GROUP BY order_num 
HAVING COUNT(*) >= 3 
ORDER BY items, order_num;

在这里插入图片描述

十、使用子查询

【题目1：返回购买价格为10美元或以上产品的顾客列表】

--输入：
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 '售出价格'
  );
  INSERT `OrderItems` VALUES ('a1',10),('a2',1),('a2',1),('a4',2),('a5',5),('a2',1),('a7',7);

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
  );
  INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a2','cust1'),('a4','cust2'),('a5','cust5'),('a2','cust1'),('a7','cust7');

在这里插入图片描述

select 
	cust_id
from Orders
where order_num in (
						select 
							order_num
						from OrderItems
						where item_price>=10
					)
;

在这里插入图片描述

【题目2：确定哪些订单购买了 prod id 为 BRO1 的产品 (一)】

--输入：
DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  );
  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

在这里插入图片描述

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：返回购买 prod id 为 BRO1 的产品的所有顾客的电子邮件 (一)】

--输入：
DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  );
  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');

在这里插入图片描述

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：返回每个顾客不同订单的总金额】

--输入：
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 ('a0001',10,105),('a0002',1,1100),('a0002',1,200),('a0013',2,1121),('a0003',5,10),('a0003',1,19),('a0003',7,5);

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a0001','cust10'),('a0003','cust1'),('a0013','cust2');

在这里插入图片描述

select 
	a.cust_id
	,sum(item_price*quantity) total_ordered
from Orders a 
left join OrderItems b 
on a.order_num=b.order_num
group by a.cust_id
order by total_ordered desc
;

在这里插入图片描述

【题目5：从 Products 表中检索所有的产品名称以及对应的销售总数】

--输入：
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 '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity INT(16) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',1100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5);

在这里插入图片描述

select 
	prod_name	
	,sum(quantity) quant_sold
from Products a 
left join OrderItems b 
on a.prod_id=b.prod_id
group by prod_name
;

在这里插入图片描述

十一、联结表

【题目1：返回顾客名称和相关订单号】

--输入：
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

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 ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

在这里插入图片描述

select 
	cust_name	
	,order_num
from Customers a
join Orders b 
on a.cust_id=b.cust_id
order by cust_name,order_num
;

在这里插入图片描述

【题目2：返回顾客名称和相关订单号以及每个订单的总价】

--输入：
DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

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 ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  quantity INT(16) NOT NULL COMMENT '商品数量',
  item_price INT(16) NOT NULL COMMENT '商品价格'
);
INSERT `OrderItems` VALUES ('a1',1000,10),('a2',200,10),('a3',10,15),('a4',25,50),('a5',15,25),('a7',7,7);

在这里插入图片描述

select 
	cust_name	
	,b.order_num	
	,sum(quantity*item_price) OrderTotal
from Customers a 
join Orders b on a.cust_id=b.cust_id
join OrderItems c on b.order_num=c.order_num
group by cust_name,b.order_num	
order by cust_name,b.order_num
;

在这里插入图片描述

【题目3：确定哪些订单购买了 prod id 为 BRO1 的产品 (二)】

--输入：
DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  );
  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

在这里插入图片描述

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：返回购买 prod id 为 BRO1 的产品的所有顾客的电子邮件 (二)】

--输入：
DROP TABLE IF EXISTS `OrderItems`;
  CREATE TABLE IF NOT EXISTS `OrderItems`(
    prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号'
  );
  INSERT `OrderItems` VALUES ('BR01','a0001'),('BR01','a0002'),('BR02','a0003'),('BR02','a0013');

  DROP TABLE IF EXISTS `Orders`;
  CREATE TABLE IF NOT EXISTS `Orders`(
    order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    order_date TIMESTAMP NOT NULL COMMENT '下单时间'
  );
  INSERT `Orders` VALUES ('a0001','cust10','2022-01-01 00:00:00'),('a0002','cust1','2022-01-01 00:01:00'),('a0003','cust1','2022-01-02 00:00:00'),('a0013','cust2','2022-01-01 00:20:00');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_id VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','cust10@cust.com'),('cust1','cust1@cust.com'),('cust2','cust2@cust.com');

在这里插入图片描述

select 
	cust_email
from OrderItems a
join Orders b on a.order_num=b.order_num
join Customers c on b.cust_id=c.cust_id
where a.prod_id='BR01'
;

在这里插入图片描述

【题目5：确定最佳顾客的另一种方式 (二)】

--输入：
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);


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 ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

在这里插入图片描述

select 
	cust_name
	,sum(item_price*quantity) total_price
from Customers a 
join Orders b on a.cust_id=b.cust_id
join OrderItems c on b.order_num=c.order_num
group by cust_name
having sum(item_price*quantity)>1000
order by total_price
;

在这里插入图片描述

十二、创建高级联结

【题目1：检索每个顾客的名称和所有的订单号 (一)】

--输入：
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 ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

在这里插入图片描述

select 
	cust_name	
	,order_num
from Orders a 
join Customers b on a.cust_id=b.cust_id
order by cust_name
;

在这里插入图片描述

【题目2：检索每个顾客的名称和所有的订单号 (二)】

--输入：
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 ('cust10','andy'),('cust1','ben'),('cust2','tony'),('cust22','tom'),('cust221','an'),('cust2217','hex'),('cust40','ace');

DROP TABLE IF EXISTS `Orders`;
CREATE TABLE IF NOT EXISTS `Orders`(
  order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
  cust_id VARCHAR(255) NOT NULL COMMENT '顾客id'
);
INSERT `Orders` VALUES ('a1','cust10'),('a2','cust1'),('a3','cust2'),('a4','cust22'),('a5','cust221'),('a7','cust2217');

在这里插入图片描述

select 
	cust_name	
	,order_num
from Customers a 
left join Orders b on a.cust_id=b.cust_id
order by cust_name
;

在这里插入图片描述

【题目3：返回产品名称和与之相关的订单号】

--输入：
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 '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola'),
('a0023','soda');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	order_num VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001','a105'),('a0002','a1100'),('a0002','a200'),('a0013','a1121'),('a0003','a10'),('a0003','a19'),('a0003','a5');

在这里插入图片描述

select 
	prod_name	
	,order_num
from Products a 
left join OrderItems b on a.prod_id=b.prod_id
order by prod_name
;

在这里插入图片描述

【题目4：返回产品名称和每一项产品的总订单数】

--输入：
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 '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','sockets'),
('a0013','coffee'),
('a0003','cola'),
('a0023','soda');

DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	order_num VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001','a105'),('a0002','a1100'),('a0002','a200'),('a0013','a1121'),('a0003','a10'),('a0003','a19'),('a0003','a5');

在这里插入图片描述

select 
	prod_name	
	,count(order_num) order_num
from Products a 
left join OrderItems b on a.prod_id=b.prod_id
group by prod_name
order by prod_name
;

在这里插入图片描述

【题目5：列出供应商及其可供产品的数量】

--输入：
DROP TABLE IF EXISTS `Vendors`;
CREATE TABLE IF NOT EXISTS `Vendors` (
  `vend_id` VARCHAR(255) NOT NULL COMMENT 'vend名称'
);
INSERT INTO `Vendors` VALUES ('a0002'),
('a0013'),
('a0003'),
('a0010');

DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`vend_id` VARCHAR(255) NOT NULL COMMENT '产品 ID',
`prod_id` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('a0001','egg'),
('a0002','prod_id_iphone'),
('a00113','prod_id_tea'),
('a0003','prod_id_vivo phone'),
('a0010','prod_id_huawei phone');

在这里插入图片描述

select 
	a.vend_id	
	,count(prod_id) prod_id
from Vendors a 
left join Products b on a.vend_id=b.vend_id
group by a.vend_id
order by a.vend_id
;

在这里插入图片描述

十三、组合查询

【题目1：将两个 SELECT 语结合起来 (一)】

--输入：
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

在这里插入图片描述

select 
	prod_id	
	,quantity
from OrderItems
where quantity=100
union all
select 
	prod_id	
	,quantity
from OrderItems
where prod_id like 'BNBG%'
order by prod_id
;

在这里插入图片描述

【题目2：将两个 SELECT 语结合起来 (二)】

--输入：
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
	prod_id VARCHAR(255) NOT NULL COMMENT '产品id',
	quantity VARCHAR(255) NOT NULL COMMENT '商品数量'
);
INSERT `OrderItems` VALUES ('a0001',105),('a0002',100),('a0002',200),('a0013',1121),('a0003',10),('a0003',19),('a0003',5),('BNBG',10002);

在这里插入图片描述

select 
	prod_id	
	,quantity
from OrderItems
where quantity=100 or prod_id like 'BNBG%'
;

在这里插入图片描述

【题目3：组合 Products 表中的产品名称和 Customers 表中的顾客名称】

--输入：
DROP TABLE IF EXISTS `Products`;
CREATE TABLE IF NOT EXISTS `Products` (
`prod_name` VARCHAR(255) NOT NULL COMMENT '产品名称'
);
INSERT INTO `Products` VALUES ('flower'),
('rice'),
('ring'),
('umbrella');

DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
	cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名'
);
INSERT `Customers` VALUES ('andy'),('ben'),('tony'),('tom'),('an'),('lee'),('hex');

在这里插入图片描述

select 
	prod_name prod_name
from Products
union all
select 
	cust_name prod_name
from Customers
order by prod_name
;

在这里插入图片描述

【题目4：纠错4】

--输入：
DROP TABLE IF EXISTS `Customers`;
CREATE TABLE IF NOT EXISTS `Customers`(
    cust_name VARCHAR(255) NOT NULL COMMENT '顾客id',
    cust_contact VARCHAR(255) NOT NULL COMMENT '顾客联系方式',
    cust_state VARCHAR(255) NOT NULL COMMENT '顾客州',
    cust_email VARCHAR(255) NOT NULL COMMENT '顾客email'
  );
INSERT `Customers` VALUES ('cust10','8695192','MI','cust10@cust.com'),('cust1','8695193','MI','cust1@cust.com'),('cust2','8695194','IL','cust2@cust.com');

在这里插入图片描述

#-- 修改前
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
ORDER BY cust_name; 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'ORDER BY cust_name;

#-- 修改后
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;