多表查询
本质:把多个表通过外键关联关系连接(join)合成一个大表,再去查询
外键
外键概念:在从表(多方)创建一个字段,引用主表(一方)的主键,对应的这个字段就是外键.
外键的特点:
1.从表外键的值是对主表主键的引用.
2.从表外键类型,必须与主表主键类型一致.
外键约束
外键约束关键字: foreign key
外键约束作用:
限制从表插入:如果从表插入的外键值,在主表中不存在,旧插入失败
限制主表删除:如果主表的主键值已经被从表引用,在主表删除数据的时候,就删除失败
注意:如果想要使用外键约束,储存引擎需要是InnoDB
建表时添加外键约束:create table 从表名(...[CONSTRAINT] foreign key(外键名) reference 主表名(主键名));
建表后添加外键约束: alter table 从表名 add [CONSTRAINT] foreign key(外键名) references 主表名(主键名);
修改存储引擎
# 分类表
CREATE TABLE category
(
cid VARCHAR(32) PRIMARY KEY,
cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
pid varchar(32) PRIMARY KEY,
pname VARCHAR(40),
price DOUBLE,
category_id varchar(32)
);
# 演示没有外键约束出现的问题
# 演示往从表中插入主表不存在的数据
insert into products values('p001','联想笔记本',4999,'c001'); # 插入成功,因为没有约束
insert into products values('p002','华为笔记本',4999,'c001'); # 插入成功,因为没有约束
insert into products values('p003','小米笔记本',4999,'c001'); # 插入成功,因为没有约束
# 往主表中插入数据
insert into category values('c001','电脑');
# 演示主表删除从表已经引用的数据
delete from category where cid = 'c001'; # 删除成功,因为没有约束
# 问题1:以上问题如何解决? 添加外键约束
# 问题2:如何添加外键约束? 前提是innodb存储引擎,myisam存储引擎不支持外键约束
# 注意: 如果修改了存储引擎,需要重新建表才能生效,否则还是原来的存储引擎
# 查看之前建表语句
show create table category; -- ENGINE=MyISAM ...
show create table products; -- ENGINE=MyISAM ...
# 添加显示成功,但是实际无效,因为原来建的表还是MyISAM引擎
alter table products add foreign key(category_id) references category(cid);
# 注意: 修改完存储引擎后,原来建的表都不会生效,除非重新建表
# 删除重建
drop table category;
drop table products;
# 建表
# 分类表
CREATE TABLE category
(
cid VARCHAR(32) PRIMARY KEY,
cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
pid varchar(32) PRIMARY KEY,
pname VARCHAR(40),
price DOUBLE,
category_id varchar(32)
);
# 添加约束 成功
alter table products add foreign key(category_id) references category(cid);
演示外键约束
# 多表查询准备工作
create database day04;
use day04;
# 分类表
CREATE TABLE category
(
cid VARCHAR(32) PRIMARY KEY,
cname VARCHAR(100) #分类名称
);
# 商品表
CREATE TABLE products
(
pid varchar(32) PRIMARY KEY,
pname VARCHAR(40),
price DOUBLE,
category_id varchar(32),
foreign key(category_id) references category(cid)
);
/*外键约束关键字: foreign key
外键约束作用:
限制从表插入: 如果从表插入的外键值,在主表主键中不存在,就插入失败
限制主表删除: 如果主表的主键值已经被从表引用,在主表删除该数据的时候,就删除失败
*/
# 演示外键约束限制从表插入数据
insert into products values ('p01','联想笔记本',9999,'c001'); # 插入失败
# 如何让从表插入数据成功? 方式1: 主表提前插入从表要引用的c001数据, 方式2: 从表插入的时候外键保留位空
# 方式1: 主表提前插入从表要引用的c001数据
insert into category values('c001','电脑');
# 再次插入数据
insert into products values ('p01','联想笔记本',9999,'c001'); # 插入成功
insert into products values ('p03','小米笔记本',5999,'c001'); # 插入成功
# 方式2: 从表插入的时候外键保留位空
insert into products values ('p02','联想笔记本',9999,null); # 插入成功
# 演示外键约束限制主表删除数据
delete from category where cid = 'c001';
# 如何让主表删除数据成功? 方式1: 直接把引用主表c001的从表数据删除, 方式2: 把引用主表c001d从表外键值改为空
# 方式1: 直接把引用主表c001的从表数据删除
delete from products where pid = 'p01';
# 方式2: 把引用主表c001d从表外键值改为空
update products set category_id = null where pid = 'p03';
# 主表再次删除
delete from category where cid = 'c001';
连接查询
本质:把多个表通过主外键关联关系连接(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 ... on
显式内连接格式: select * from 左表 inner join 右表 on 关联条件;
隐式内连接格式: select * from 左表 , 右表 where 关联条件;
注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
# 示例:
# 2.内连接(常用)
# 隐式
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 关联条件;
注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
# 示例:
# 3.左外连接
# 需求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 关联条件;
注意: 左表和右表没有特殊含义,只是在前面是左表,在后面的是右表
# 示例:
# 4.右外连接
# 需求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:查询哪些分类的商品已经上架,要求展示分类名称
需求2:查询所有分类商品的个数,要求展示分类名称和个
# 实战需求:
# 需求1.1:查询哪些分类的商品已经上架,要求展示分类id
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
select DISTINCT category_id
from products
where flag = '1';
# 需求1.2:查询哪些分类的商品已经上架,要求展示分类名称
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
select DISTINCT cname
from products as p inner join category as c ON p.category_id = c.cid
where flag = '1';
# 需求2:查询所有分类商品的个数,要求展示分类名称和个数
# 分析: 完成需求需要的数据来源几个表,如果是一个表直接查询,如果是多个表需要先合成一个表再去查
# 注意: 此题需要利用聚合函数(字段名)忽略null值的特点
select cid,count(category_id)
from products p right outer join category as c ON p.category_id = c.cid
group by cid;
# 需求3: 查询'服饰'这个分类对应的所有商品
select *
from products p inner join category c ON p.category_id = c.cid
where cname = '服饰';
# 需求4: 查询'服饰'这个分类上架的所有商品
select *
from products p inner join category c ON p.category_id = c.cid
where cname = '服饰' and flag = 1;
# 需求5: 查询'服饰'和'化妆品'这两个分类的所有商品
select *
from products p inner join category c ON p.category_id = c.cid
where cname = '服饰' or cname = '化妆品';
select *
from products p inner join category c ON p.category_id = c.cid
where cname in('服饰' ,'化妆品') ;
子查询
子查询: 一个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;
拓展知识
SQL底层执行顺序
# sql底层执行顺序
# 需求: 查询非空的各个分类商品个数大于1,只保留个数最多的分类和个数
# 步骤1: 查询整个表数据
select * from day03.product;
# 步骤2: 查询分类id非空的所有数据
select * from day03.product where category_id is not null;
# 步骤3: 根据分类id进行分组
# 注意: where后不能跟别名,group by 后可以跟别名
select category_id as cid from day03.product where category_id is not null group by cid;
# 步骤4: 每个分组内统计个数
select category_id as cid,count(*) as cnt from day03.product where category_id is not null group by cid;
# 步骤5: 筛选个数大于1的分组
# 注意: having后也可以跟别名
select category_id as cid,count(*) as cnt from day03.product where category_id is not null group by cid having cnt > 1;
# 步骤6: 按照统计后的个数降序排序
# 注意: order by后也可以跟别名
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;
# 步骤7: 取地条数据
# 注意: order by后也可以跟别名
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;
# 执行顺序: from -> where -> group by ->having -> select -> order by -> limit
自连接
自连接: 本质就是内外连接,唯一区别是左表和右表是同一张表
注意: 自连接为了作为多个表使用,必须起别名进行区分
注意: 自连接应用场景比较局限,主要是省市县三级区域表或者上下级员工表
# 自连接
# 需求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 = '邯郸市');
开窗函数
开窗函数: mysql8的新功能,保证输出结果的记录数和输入的数据记录数一致
开窗函数格式:... 开窗函数 over(partition by 分组字段 order by 排序字段 asc|desc) ...
聚合函数: sum() max() min() avg() count()
排序函数: row_number() rank() dense_rank()
row_number(): 巧记1234 唯一且连续
rank() : 巧记1224 并列不连续
dense_rank(): 巧记1223 并列且连续
# over() 配合聚合函数
/*
开窗函数好处: 能够减少大量的子查询语句,方便做各种计算
开窗函数原理: 保证输出的结果和输入的结果条数一致
开窗函数特点: 计算结果在原表末尾单独一列展示
*/
# 开窗函数和聚合函数配合使用
# 需求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) as rn,
rank() OVER (ORDER BY salary desc) rk,
dense_rank() OVER (ORDER BY salary desc) dr
from employee ;
# 需求2: 分别使用三种排序方式,根据每个部门员工的工资进行降序排名
select *,
row_number() OVER (PARTITION by deptid ORDER BY salary desc ) as rn,
rank() OVER (PARTITION by deptid ORDER BY salary desc) rk,
dense_rank() OVER (PARTITION by deptid ORDER BY salary desc) dr
from employee ;
# 综合需求: 查询每个部门工资最高的员工信息
select *
from (
select *, rank() OVER (PARTITION by deptid ORDER BY salary desc) rk from employee
) tmp
where rk=1;