SQL进阶（多表查询+窗口函数简单使用）
SQL基础语句之多表查询
外键约束
外键约束的添加方法
假设现在我们有两张表“分类表” 和“商品表”
— category —
cid VARCHAR(32) PRIMARY KEY, cname VARCHAR(100) #分类名称1
2
3
4
5
6
7
8
- — products—
- ```sql
pid VARCHAR(32) PRIMARY KEY,
name VARCHAR(40),
price DOUBLE,
category_id VARCHAR(32),
在创表语句后添加：
CONSTRAINT FOREIGN KEY (外键字段) REFERENCES 主表名(主键)category分类表，为唯一方，也就是主表，必须提供主键cid
products商品表，为多方，也就是从表，必须提供外键category_id
外键约束检测
外键约束的优点
- ==在插入数据时，保证了数据的准确性==
1 | INSERT INTO category (cid ,cname) VALUES('c001','服装'); |
- ==在删除数据时，保证了数据的完整性==
1 | -- 将分类（category）表中的’c001‘数据进行删除会直接报错 |
结论：==如果要删除主表中的记录,需要先将从表中所有引用该数据的记录删除或者修改为引用其他记录==。
从表中引用了主表中的数据，主表中数据不可被删除。
主表中没有数据，从表外键也无法被插入。
- 从表外键的值是对主表主键的引用。
- 从表外键类型，必须与主表主键类型一致。
多表查询（连接查询）
连接查询可以分为:
- 内连接查询： 查询两个表中符合条件的共有记录
- 左连接查询： 以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
- 右连接查询： 以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
- 自连接查询： 左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
- 先建立表，以便后面操作
1 | USE test01; |
内连接查询
查询两个表中符合条件的共有记录
内连接查询语法格式:
1 | SELECT 字段 FROM 左表 INNER JOIN 右表 ON 左表和右表的连接规则 |
- 查询所有英雄所对应的功夫名称,如果没有则不显示(求交集):
1 | SELECT * FROM hero INNER JOIN kongfu ON hero.kongfu_id = kongfu.kid; |
- 结论：==内连接中,只保存左侧和右侧匹配成功的记录,其余记录都删除，也就是取其交集==
左连接查询
以左表为主根据条件查询右表数据，如果根据条件查询右表数据不存在使用null值填充
左连接查询语法格式:
1 | SELECT 字段列表 FROM 左表 LEFT OUTER JOIN 右表 ON 左表和右表的连接规则 |
- 查询所有英雄对应的武功,没有武功的的应用也需要展示出来(差集):
1 | SELECT * FROM hero LEFT OUTER JOIN kongfu ON hero.kongfu_id = kongfu.kid; |
- 结论：==左连接时，会将所有的左表内容以及右表中可以匹配到左表中的数据保留，其余右表数据将被忽略。==
右连接查询
以右表为主根据条件查询左表数据，如果根据条件查询左表数据不存在使用null值填充
右连接查询语法格式:
1 | SELECT 字段列表 FROM 左表 RIGHT OUTER JOIN 右表 ON 左表和右表的连接规则 |
- 查询所有武功对应的英雄,保留所有的武功,没有武功的英雄不展示:
1 | SELECT * FROM hero RIGHT OUTER JOIN kongfu ON hero.kongfu_id = kongfu.kid; |
- 结论：==右连接时会将所有的右表内容以及左表中可以匹配到右表中的数据保留，其余左表数据被忽略。==
自连接查询
- 左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
子查询
1. 子查询的介绍
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.
- 主查询和子查询的关系:
- 子查询是嵌入到主查询中
- 子查询是辅助主查询的,要么充当条件,要么充当数据源
- 子查询是可以独立存在的语句,是一条完整的 select 语句
2. 子查询的使用
准备数据
1 | # 创建分类表 |
查询当前商品中大于平均价格的商品:
- *第一步：**获取平均价格 (得到的是一个值)
1 | SELECT AVG(price) FROM products; |
- 第二步：查询所有商品
1 | SELECT * FROM products; |
- 第三步：将第一步结果作为第二步的查询条件，这种方式即为子查询
1 | SELECT * |
获取所有商品中,平均价格大于1000的分类的全部商品:
第一步：获取平均价格大于1000的分类
1 | SELECT |
第二步：查询所有商品
1 | SELECT * FROM products; |
第三步：第一步查询出来的分类,作为第二步的条件进行筛选
1 | SELECT * |
窗口函数
窗口函数是什么,用法
MYSQL 8.0 之后，加入了窗口函数功能，简化了数据分析工作中查询语句的书写
在没有窗口函数之前，我们需要通过定义临时变量和大量的子查询才能完成的工作，使用窗口函数实现起来更加简洁高效
窗口函数一般和聚合函数一起使用(sum,avg,min,max,count,rank,dense_rank,roll_number)
使用方法
SELECT SUM() OVER(PARTITION BY ___ ORDER BY___) FROM Table数据准备
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80create database test3 charset=utf8;
use test3;
-- employee员工信息表
create table employee(
id int unsigned primary key not null,
first_name varchar(20) not null,
last_name varchar(30) not null,
department_id tinyint not null,
salary int not null,
years_worked tinyint not null
);
insert into employee values
(1, 'Diane', 'Turner', 1, 5330, 4),
(2, 'Clarence', 'Robinson', 1, 3617, 2),
(3, 'Eugene', 'Phillips', 1, 4877, 2),
(4, 'Philip', 'Mitchell', 1, 5259, 3),
(5, 'Ann', 'Wright', 2, 2094, 5),
(6, 'Charles', 'Wilson', 2, 5167, 5),
(7, 'Russell', 'Johnson', 2, 3762, 4),
(8, 'Jacqueline', 'Cook', 2, 6923, 3),
(9, 'Larry', 'Lee', 3, 2796, 4),
(10, 'Willie', 'Patterson', 3, 4771, 5),
(11, 'Janet', 'Ramirez', 3, 3782, 2),
(12, 'Doris', 'Bryant', 3, 6419, 1),
(13, 'Amy', 'Williams', 3, 6261, 1),
(14, 'Keith', 'Scott', 3, 4928, 8),
(15, 'Karen', 'Morris', 4, 6347, 6),
(16, 'Kathy', 'Sanders', 4, 6286, 1),
(17, 'Joe', 'Thompson', 5, 5639, 3),
(18, 'Barbara', 'Clark', 5, 3232, 1),
(19, 'Todd', 'Bell', 5, 4653, 1),
(20, 'Ronald', 'Butler', 5, 2076, 5)
;
-- department部门信息表
create table department(
id int unsigned primary key not null,
name varchar(30) not null
);
insert into department values
(1, 'IT'),
(2, 'Management'),
(3, 'Human Resources'),
(4, 'Accounting'),
(5, 'Help Desk')
;
-- purchase采购信息表
create table purchase(
id int unsigned primary key not null,
department_id tinyint not null,
item varchar(30) not null,
price int not null
);
insert into purchase values
(1, 4, 'monitor', 531),
(2, 1, 'printer', 315),
(3, 3, 'whiteboard', 170),
(4, 5, 'training', 117),
(5, 3, 'computer', 2190),
(6, 1, 'monitor', 418),
(7, 3, 'whiteboard', 120),
(8, 3, 'monitor', 388),
(9, 5, 'paper', 37),
(10, 1, 'paper', 695),
(11, 3, 'projector', 407),
(12, 4, 'garden party', 986),
(13, 5, 'projector', 481),
(14, 2, 'chair', 180),
(15, 2, 'desk', 854),
(16, 2, 'post-it', 15),
(17, 3, 'paper', 60),
(18, 2, 'tv', 943),
(19, 2, 'desk', 478),
(20, 5, 'keyboard', 214)
;具体案例
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42# 需求：创建报表，除了查询每个人的工资之外，还要统计出公司每月的工资总支出
select first_name,last_name,salary,avg(salary) over() as '统计' from employee;
# 需求：统计采购表中的总体平均采购价格，并与明细一起显示（每件物品名称，价格）
select
item,price,
avg(price) over() as avg_price
from
purchase;
# 计算出员工的薪水和总体平均薪水之间的差
select
first_name,last_name,salary-avg(salary) over() as '差值'
from employee;
# 创建报表统计每个员工的工龄和总体平均工龄之间的差值
select
first_name,last_name,years_worked,years_worked-avg(years_worked) over() as '差值'
from employee;
# 查询了id为2的部门所采购的所有商品，并计算每项支出占总采购金额的占比
select
item,price,price/sum(price) over() as '占比'
from
purchase
where department_id=2;
# 统计人力资源部（部门ID为3）的员工薪资，并将每名员工的薪资与部门总体平均薪资进行比较
select
first_name,last_name,salary,salary-avg(salary) over() as '比较'
from
employee
where department_id=3;
-- 查询月薪超过4000的员工，并统计所有月薪超过4000的员工数量
select count(*) from employee where salary>4000;
# 假设四人，三人分数一样，另外一个不一样
# 对比 RANK(), 1，1，1，4
# DENSE_RANK(), 1，1，1，2
# ROW_NUMBER() 1，2，3，4
Demo
1 | -- 获取所有商品中价格最高的商品(最高价值商品不只有一个) |
All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
微信
支付宝
