目录
多表查询概述
表与表之间的关系:一对一,一对多,多对多
本质:把多个表通过主外键关联关系连接(join)合并成一个大表,再去查询
连接查询
数据准备
# 多表查询数据准备
# 创建hero表
CREATE TABLE hero
(
hid INT PRIMARY KEY,
hname VARCHAR(255),
kongfu_id INT
);
# 创建kongfu表
CREATE TABLE kongfu
(
kid INT PRIMARY KEY,
kname VARCHAR(255)
);
# 插入hero数据
INSERT INTO hero VALUES(1, '鸠摩智', 9),(3, '乔峰', 1),(4, '虚竹', 4),(5, '段誉', 12);
# 插入kongfu数据
INSERT INTO kongfu VALUES(1, '降龙十八掌'),(2, '乾坤大挪移'),(3, '猴子偷桃'),(4, '天山折梅手');交叉连接[慎用]
交叉连接关键字: cross join
显式交叉连接格式: select * from 左表 cross join 右表;
隐式交叉连接格式: select * from 左表,右表;
注意: 交叉连接了解即可,因为它本质就是一个错误,又叫笛卡尔积(两个表记录数的乘积),实际工作中禁止使用
注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
# 示例:
# 1.交叉连接(慎用)
# 显式
select * from hero cross join kongfu;
# 隐式
select * from hero , kongfu;内连接(常用)
内连接关键字: inner join ... in
显式内连接格式: select * from 左表 inner join 右表 on 关联条件
隐式内连接格式: select * from 左表,右表 where 关联条件
#隐式
select * from hero,kongfu where hero.kongfu_id=kongfu.kid;
#显式
select * from hero inner join kongfu on hero.kongfu_id=kongfu.kid;左外连接
左外连接关键字: left outer join ... on
左外连接格式: select * from 左表 left outer join 右表 on 关联条件
# 需求2: 获取所有的英雄名称和对应的功夫,没有功夫的null补全
# 左表所有数据展示,右表只展示和左表关联上的数据,其他用null补全
select hname,kname from hero left outer join kongfu on hero.kongfu_id=kongfu.kid右外连接
左外连接关键字: right outer join ... on
左外连接格式: select * from 左表 right outer join 右表 on 关联条件
# 需求3: 获取所有的功夫和对应的英雄,没有英雄的null补全
# 左表所有数据展示,右表只展示和左表关联上的数据,其他用null补全
select kname,hname from hero right outer join kongfu on hero.kongfu_id=kongfu.kid;连接查询练习
准备数据
# 创建表
drop table if exists products;
drop table if exists category;
# 分类表
CREATE TABLE category (
cid VARCHAR(32) PRIMARY KEY ,
cname VARCHAR(50)
);
# 商品表
CREATE TABLE products(
pid VARCHAR(32) PRIMARY KEY ,
pname VARCHAR(50),
price INT,
flag VARCHAR(2), #是否上架标记为：1表示上架、0表示下架
category_id VARCHAR(32),
CONSTRAINT products_fk FOREIGN KEY (category_id) REFERENCES category (cid)
);
# 添加数据
#分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
INSERT INTO category(cid,cname) VALUES('c004','奢侈品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p010','华为',9999,'0',null);示例
# 实战需求:
# 需求1.1:查询哪些分类的商品已经上架,要求展示分类id
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
select distinct category_id from products where flag=1;
# 需求1.2:查询哪些分类的商品已经上架,要求展示分类名称
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
select distinct category_id,cname from products inner join category on products.category_id = category.cid where flag=1;
# 需求2:查询所有分类商品的个数,要求展示分类名称和个数
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
# 注意: 此题需要利用聚合函数(字段名)忽略null值的特点
select cname,count(category_id) from products right join category on products.category_id = category.cid group by cname;
# 需求3: 查询'服饰'这个分类对应的所有商品
select * from category c inner join products p on c.cid = p.category_id where cname='服饰';
# 需求4: 查询'服饰'这个分类上架的所有商品
select * from category c inner join products p on c.cid = p.category_id where cname='服饰' and flag='1';
# 需求5: 查询'服饰'和'化妆品'这两个分类的所有商品
select * from category c inner join products p on c.cid = p.category_id where cname in('服饰','化妆品');
select *
from products p inner join category c ON p.category_id = c.cid
where cname = '服饰' or cname = '化妆品';子查询
知识点
子查询: 一个select语句作为另一个select一句的一部分(表或者条件)
注意:子查询语句作为表使用的时候需要设置别名
# 需求1: 查询'服饰'这个分类对应的所有商品
# 方式1:作为条件
select * from products where category_id=(select cid from category where cname='服饰');
# 方式2:作为表
select *
from products p
inner join (select * from category where cname = '服饰') c
on p.category_id=c.cid;
# 需求2: 查询'家电'和'化妆品'两个分类对应的所有商品
# 方式1:作为条件
select * from products where category_id in(select cid from category where cname in('服饰','化妆品'));
# 方式2:作为表
select * from products p inner join
(select * from category where cname in('服饰','化妆品')) c
on p.category_id=c.cid;自连接
自连接: 本质就是内外连接,唯一区别是左表和右表是同一张表
注意: 自连接为了作为多个表使用,必须起别名进行区分
注意: 自连接应用场景比较局限,主要是省市县三级区域表或者上下级员工表
# 需求1: 查询河北省下的所有城市
select shi.title
from areas sheng
inner join areas shi on shi.pid = sheng.id
where sheng.title = '河北省';
# 需求2: 查询邯郸市下的所有区县
select xian.title
from areas xian
inner join areas shi on xian.pid = shi.id
where shi.title = '邯郸市';
# 子查询方式
# 需求1: 查询河北省下的所有城市
select title
from areas
where pid = (select id from areas where title = '河北省');
# 需求2: 查询邯郸市下的所有区县
select title
from areas
where pid = (select id from areas where title = '邯郸市');
SQL底层执行顺序
书写顺序: select -> from -> where -> group by ->having -> order by -> limit
执行顺序: from -> where -> group by ->having -> select -> order by -> limit
SELECT
category_id AS cid,
COUNT(*) AS cnt
FROM
day03.product
WHERE
category_id IS NOT NULL
GROUP BY
cid
HAVING
cnt > 1
ORDER BY
cnt DESC
LIMIT 1;开窗函数
开窗函数:MySQL8的新功能,保证输出结果的记录数和输入的数据记录数一致
开窗函数格式: ... 开窗函数 over(partition by 分组字段 order by 排序字段 asc|desc) ...
聚合函数: sum() max() min() avg() count()
排序函数: row_number(): 巧记1234 唯一且连续
rank() : 巧记1224 并列不连续
dense_rank() : 巧记1223 并列且连续
开窗函数好处: 能够减少大量的子查询语句,方便做各种计算
开窗函数原理: 保证输出的结果和输入的结果条数一致
开窗函数特点: 计算结果在原表末尾单独一列展示
配合聚合函数
# 开窗函数和聚合函数配合使用
# 需求1: 查询每个商品和最低价格的差额
# 子查询方式
SELECT
pname,
price,
(select min(price) from products) as '最小价格',
price - (select min(price) from products) as '差额'
FROM
products;
# 开窗函数方式
SELECT
pname,
price,
min(price) over() as '最小价格',
price - min(price) over() as '差额'
FROM
products;
# 需求2: 查询每个商品和最高价格的差额
SELECT
pname,
price,
max(price) over() as '最高价格',
price - max(price) over() as '差额'
FROM
products;
# 需求3: 查询每个商品单价占总价的比例
SELECT
pname,
price,
sum(price) over() as '总价格',
price / sum(price) over() as '占比'
FROM
products;
# 需求4: 查询每个商品和平均价格的差额
SELECT
pname,
price,
avg(price) over() as '平均价格',
price - avg(price) over() as '差额'
FROM
products;
# 函数: round(数据,保留位数)
SELECT
pname,
price,
round(avg(price) over(),2) as '平均价格',
round(price - avg(price) over(),2) as '差额'
FROM
products;
# 需求5: 查询商品名称商品价格以及商品总个数
SELECT
pname,
price,
count(*) over() as '总个数'
FROM
products;配合排序函数
# 建表
create table employee (
empid int,ename varchar(20) ,deptid int ,salary decimal(10,2)
);
# 插入数据
insert into employee values(1,'刘备',10,5500.00);
insert into employee values(2,'赵云',10,4500.00);
insert into employee values(3,'张飞',10,3500.00);
insert into employee values(4,'关羽',10,4500.00);
insert into employee values(5,'曹操',20,1900.00);
insert into employee values(6,'许褚',20,4800.00);
insert into employee values(7,'张辽',20,6500.00);
insert into employee values(8,'徐晃',20,14500.00);
insert into employee values(9,'孙权',30,44500.00);
insert into employee values(10,'周瑜',30,6500.00);
insert into employee values(11,'陆逊',30,7500.00);
# 需求1: 分别使用三种排序方式,根据所有员工的工资进行降序排名
select *,row_number() over (order by salary desc) from employee ;
select *,rank() over (order by salary desc) from employee ;
select *,dense_rank() over (order by salary desc) from employee ;
# 需求2: 分别使用三种排序方式,根据每个部门员工的工资进行降序排名
select *,
row_number() over (partition by deptid order by salary desc),
rank() over (partition by deptid order by salary desc),
dense_rank() over (partition by deptid order by salary desc)
from employee;
# 综合需求: 查询每个部门工资最高的员工信息
select *
from (
select *,
rank() over (partition by deptid order by salary desc) rk
from employee
) emp
where rk=1;
扫一扫
6809
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
