网上学习资料一大堆,但如果学到的知识不成体系,遇到问题时只是浅尝辄止,不再深入研究,那么很难做到真正的技术提升。
一个人可以走的很快,但一群人才能走的更远!不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人,都欢迎加入我们的的圈子(技术交流、学习资源、职场吐槽、大厂内推、面试辅导),让我们一起学习成长!
);
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 c.cust_name, o.order_num
from Customers c join Orders o on c.cust_id = o.cust_id
order by c.cust_name, o.order_num;
*/
mysql> select c.cust_name, o.order_num
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> order by c.cust_name, o.order_num;
±----------±----------+
| cust_name | order_num |
±----------±----------+
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
±----------±----------+
6 rows in set (0.00 sec)
72、连接查询(2)
数据表:Customers,包含顾客名称:cust\_name,顾客id:cust\_id,表中数据如下:
| cust\_id | cust\_name |
| --- | --- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
数据表:Orders(订单信息表),包含订单号:order\_num,顾客id:cust\_id,表中数据如下:
| order\_num | cust\_id |
| --- | --- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
数据表:OrderItems,包含商品订单号:order\_num,订购数量:quantity,单价:item\_price,表中数据如下:
| order\_num | quantity | item\_price |
| --- | --- | --- |
| a1 | 1000 | 10 |
| a2 | 200 | 10 |
| a3 | 10 | 15 |
| a4 | 25 | 50 |
| a5 | 15 | 25 |
| a7 | 7 | 7 |
【问题】查询顾客名称、订单号以及每个订单的总价(OrderTotal),并按顾客名称再按订单号对结果进行升序排序。查询结果如下:
| cust\_name | order\_num | OrderTotal |
| --- | --- | --- |
| an | a5 | 375 |
| andy | a1 | 10000 |
| ben | a2 | 2000 |
| hex | a7 | 49 |
| tom | a4 | 1250 |
| tony | a3 | 150 |
表结构及数据如下:
/*
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, o.order_num,
sum(oi.quantity * oi.item_price) OrderTotal
from Customers c join Orders o on c.cust_id = o.cust_id
join OrderItems oi on o.order_num = oi.order_num
group by cust_name, o.order_num
order by cust_name, o.order_num;
*/
mysql> select cust_name, o.order_num,
-> sum(oi.quantity * oi.item_price) OrderTotal
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> join OrderItems oi on o.order_num = oi.order_num
-> group by cust_name, o.order_num
-> order by cust_name, o.order_num;
±----------±----------±-----------+
| cust_name | order_num | OrderTotal |
±----------±----------±-----------+
| an | a5 | 375 |
| andy | a1 | 10000 |
| ben | a2 | 2000 |
| hex | a7 | 49 |
| tom | a4 | 1250 |
| tony | a3 | 150 |
±----------±----------±-----------+
6 rows in set (0.00 sec)
73、连接查询(3)
数据表:OrderItems,表中数据如下:
| prod\_id | order\_num |
| --- | --- |
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
数据表:Orders,表中数据如下:
| order\_num | cust\_id | order\_date |
| --- | --- | --- |
| 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 |
【问题】编写 SQL 语句,查询哪些订单购买了 prod\_id 为 “BR01” 的产品,显示每个产品对应的顾客 ID(cust\_id)和订单日期(order\_date),按订购日期对结果进行升序排序。查询结果如下:
| cust\_id | order\_date |
| --- | --- |
| cust10 | 2022-01-01 00:00:00 |
| cust1 | 2022-01-01 00:01:00 |
表结构及数据如下:
/*
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;
– 使用连接查询
select o.cust_id, o.order_date
from Orders o join OrderItems oi on o.order_num = oi.order_num
where oi.prod_id = ‘BR01’
order by o.order_date;
*/
– 使用子查询
mysql> select cust_id, order_date
-> from Orders where order_num in
-> (select order_num from OrderItems where prod_id = ‘BR01’)
-> order by order_date;
±--------±--------------------+
| cust_id | order_date |
±--------±--------------------+
| cust10 | 2022-01-01 00:00:00 |
| cust1 | 2022-01-01 00:01:00 |
±--------±--------------------+
2 rows in set (0.00 sec)
– 使用连接查询
mysql> select o.cust_id, o.order_date
-> from Orders o join OrderItems oi on o.order_num = oi.order_num
-> where oi.prod_id = ‘BR01’
-> order by o.order_date;
±--------±--------------------+
| cust_id | order_date |
±--------±--------------------+
| cust10 | 2022-01-01 00:00:00 |
| cust1 | 2022-01-01 00:01:00 |
±--------±--------------------+
2 rows in set (0.00 sec)
74、连接查询(4)
数据表:OrderItems,表中数据如下:
| prod\_id | order\_num |
| --- | --- |
| BR01 | a0001 |
| BR01 | a0002 |
| BR02 | a0003 |
| BR02 | a0013 |
数据表:Orders,表中数据如下:
| order\_num | cust\_id | order\_date |
| --- | --- | --- |
| 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 |
数据表:Customers,表中数据如下:
| cust\_id | cust\_email |
| --- | --- |
| cust10 | cust10@cust.com |
| cust1 | cust1@cust.com |
| cust2 | cust2@cust.com |
【问题】查询购买 prod\_id 为 “BR01” 的产品的所有顾客的电子邮件。查询结果如下:
| cust\_email |
| --- |
| cust10@cust.com |
| cust1@cust.com |
表结构及数据如下:
/*
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’)
);
– 使用连接查询
select c.cust_email
from Customers c join Orders o on c.cust_id = o.cust_id
join OrderItems oi on o.order_num = oi.order_num
where prod_id = ‘BR01’;
*/
– 使用子查询
mysql> 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’)
-> );
±----------------+
| cust_email |
±----------------+
| cust10@cust.com |
| cust1@cust.com |
±----------------+
2 rows in set (0.00 sec)
– 使用连接查询
mysql> select c.cust_email
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> join OrderItems oi on o.order_num = oi.order_num
-> where prod_id = ‘BR01’;
±----------------+
| cust_email |
±----------------+
| cust10@cust.com |
| cust1@cust.com |
±----------------+
2 rows in set (0.00 sec)
75、连接查询(5)
数据表:OrderItems,表中数据如下:
| order\_num | item\_price | quantity |
| --- | --- | --- |
| a1 | 10 | 105 |
| a2 | 1 | 1100 |
| a2 | 1 | 200 |
| a4 | 2 | 1121 |
| a5 | 5 | 10 |
| a2 | 1 | 19 |
| a7 | 7 | 5 |
数据表:Orders,表中数据如下:
| order\_num | cust\_id |
| --- | --- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
数据表:Customers,表中数据如下:
| cust\_id | cust\_name |
| --- | --- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
【问题】编写 SQL 语句,查询订单总价不小于 1000 的客户名称和总额(item\_price \* quantity),按总额对结果进行排序。查询结果如下:
| cust\_name | total\_price |
| --- | --- |
| andy | 1050 |
| ben | 1319 |
| tom | 2242 |
表结构及数据如下:
/*
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 c.cust_name,
sum(oi.item_price * oi.quantity) total_price
from OrderItems oi join Orders o on oi.order_num = o.order_num
join Customers c on o.cust_id = c.cust_id
group by c.cust_name
having total_price >= 1000
order by total_price;
*/
mysql> select c.cust_name,
-> sum(oi.item_price * oi.quantity) total_price
-> from OrderItems oi join Orders o on oi.order_num = o.order_num
-> join Customers c on o.cust_id = c.cust_id
-> group by c.cust_name
-> having total_price >= 1000
-> order by total_price;
±----------±------------+
| cust_name | total_price |
±----------±------------+
| andy | 1050 |
| ben | 1319 |
| tom | 2242 |
±----------±------------+
3 rows in set (0.00 sec)
76、连接查询(6)
数据表:Customers,表中数据如下:
| cust\_id | cust\_name |
| --- | --- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
数据表:Orders,表中数据如下:
| order\_num | cust\_id |
| --- | --- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
【问题】编写 SQL语句,查询每个顾客的名称和所有的订单号,根据顾客姓名 cust\_name 升序排序。查询结果如下:
| cust\_name | order\_num |
| --- | --- |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
表结构及数据如下:
/*
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 c.cust_name, o.order_num
from Customers c join Orders o on c.cust_id = o.cust_id
order by c.cust_name;
*/
mysql> select c.cust_name, o.order_num
-> from Customers c join Orders o on c.cust_id = o.cust_id
-> order by c.cust_name;
±----------±----------+
| cust_name | order_num |
±----------±----------+
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
±----------±----------+
6 rows in set (0.00 sec)
77、连接查询(7)
数据表:Orders,表中数据如下:
| order\_num | cust\_id |
| --- | --- |
| a1 | cust10 |
| a2 | cust1 |
| a3 | cust2 |
| a4 | cust22 |
| a5 | cust221 |
| a7 | cust2217 |
数据表:Customers,表中数据如下:
| cust\_id | cust\_name |
| --- | --- |
| cust10 | andy |
| cust1 | ben |
| cust2 | tony |
| cust22 | tom |
| cust221 | an |
| cust2217 | hex |
| cust40 | ace |
【问题】检索每个顾客的名称和所有的订单号,列出所有的顾客,即使他们没有下过订单。查询结果如下:
| cust\_name | order\_num |
| --- | --- |
| ace | NULL |
| an | a5 |
| andy | a1 |
| ben | a2 |
| hex | a7 |
| tom | a4 |
| tony | a3 |
表结构及数据如下:
/*
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’);
*/
解答:
![img](https://img-blog.csdnimg.cn/img_convert/932b6437f5cb4941ad2ef93f43271141.png)
![img](https://img-blog.csdnimg.cn/img_convert/6581277c2391f038cfb4caa6d311efc1.png)
**既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上C C++开发知识点,真正体系化!**
**由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新**
**[如果你需要这些资料,可以戳这里获取](https://bbs.csdn.net/topics/618668825)**
('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');
\*/
解答:
[外链图片转存中…(img-34dA6sDG-1715808850941)]
[外链图片转存中…(img-QQrGofy9-1715808850941)]
既有适合小白学习的零基础资料,也有适合3年以上经验的小伙伴深入学习提升的进阶课程,涵盖了95%以上C C++开发知识点,真正体系化!
由于文件比较多,这里只是将部分目录截图出来,全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频,并且后续会持续更新