MySql基础四-多表查询
2023-07-27 本文已影响0人
Matt_Z_
一、多表关系
多表关系
一对多
多对多
# 多对多
# 学生表
create table student(
id int primary key auto_increment comment '主键ID',
name varchar(10) comment '姓名',
no varchar(10) comment '学号'
) comment '学生表';
insert into student
values
(null, '黛绮丝','2000100101'),
(null, '谢逊','2000100102'),
(null, '殷天正','2000100103'),
(null, '韦一笑','2000100104');
# 课程表
create table course(
id int primary key auto_increment comment '主键ID',
name varchar(10) comment '课程名称'
) comment '课程表';
insert into course
values
(null, 'Java'),
(null, 'PHP'),
(null, 'MySQL'),
(null, 'Hadoop');
# 学生课程关系表
create table student_course(
id int primary key auto_increment comment 'ID',
studentid int not null comment '学生ID',
courseid int not null comment '课程id',
constraint fk_courseid foreign key (courseid) references course(id),
constraint fk_studentid foreign key (studentid) references student(id)
) comment '学生课程关系表';
insert into student_course
values
(null, 1, 1),
(null, 1, 2),
(null, 1, 3),
(null, 2, 1),
(null, 2, 4);
外键关系表：
外键关系表
一对一
一对一
# 一对一
create table tb_user(
id int primary key auto_increment comment 'ID',
name varchar(10) comment '姓名',
age int comment '年龄',
gender char(1) comment '性别：1.男 2.女',
phone char(11) comment '手机号'
) comment '用户基本信息表';
create table tb_user_edu(
id int primary key auto_increment comment 'ID',
degree varchar(20) comment '学历',
major varchar(50) comment '专业',
primaryschool varchar(50) comment '小学',
middleschool varchar(50) comment '中学',
university varchar(50) comment '大学',
userid int unique comment '用户id',
constraint fk_userid foreign key (userid) references tb_user(id)
) comment '用户教育信息表';
insert into tb_user
values
(null, '黄渤', 45, 1, 18800001111),
(null, '冰冰', 35, 2, 18800002222),
(null, '码云', 55, 1, 18800008888),
(null, '李彦宏', 50, 1, 18800009999);
insert into tb_user_edu
values
(null, '本科','舞蹈','静安区第一小学','静安区第一中学','北京舞蹈学院',1),
(null, '硕士','表演','朝阳区第一小学','朝阳区第一中学','北京电影学院',2),
(null, '本科','英语','杭州市第一小学','杭州市第一中学','杭州师范大学',3),
(null, '本科','应用数学','阳泉第一小学','阳泉区第一中学','清华大学',4);
二、多表查询概述
# 准备数据
create table dept(
id int primary key auto_increment comment 'ID',
name varchar(50) not null comment '部门名称'
) comment '部门表';
insert into dept(id, name)
values
(1, '研发部'),
(2, '市场部'),
(3, '财务部'),
(4, '销售部'),
(5, '总经办');
create table emp (
id int primary key auto_increment comment 'ID',
name varchar(50) not null comment '名称',
age int check ( age > 0 && age < 120 ) comment '年龄',
job varchar(20) comment '职位',
salary int comment '薪资',
entrydate date comment '入职时间',
managerid int comment '直属领导id',
dept_id int comment '部门id'
) comment '员工表';
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id)
values
(1, '金庸',66,'总裁', 20000, '2000-01-01', null, 5),
(2, '张无忌',20,'项目经理', 12500, '2005-12-05', 1, 1),
(3, '杨逍',33,'开发', 8400, '2000-11-03', 2, 1),
(4, '韦一笑',48,'开发', 11000, '2002-02-05', 2, 1),
(5, '常遇春',43,'开发', 10500, '2004-09-07', 3, 1),
(6, '小昭',19,'程序员鼓励师', 6600, '2004-10-12', 2, 1);
insert into emp(id, name, age, job, salary, entrydate, managerid, dept_id)
values
(7, '灭绝',60,'财务总监', 8500, '2002-09-12', 1, 3),
(8, '周芷若',19,'会计', 4800, '2006-06-02', 7, 3),
(9, '丁敏君',23,'出纳', 5250, '2009-05-13', 7, 3),
(10, '赵敏',20,'市场部总监', 12500, '2004-10-12', 1, 2),
(11, '鹿杖客',56,'职员', 3750, '2006-10-03', 10, 2),
(12, '鹤笔翁',19,'职员', 3750, '2007-05-09', 10, 2),
(13, '房东白',19,'职员', 5500, '2009-02-12', 10, 2),
(14, '张三丰',88,'销售总监', 14000, '2004-10-12', 1, 4),
(15, '俞莲舟',38,'销售', 4600, '2004-10-12', 14, 4),
(16, '宋远桥',40,'销售', 4600, '2004-10-12', 14, 4),
(17, '陈友谅',42,null, 2000, '2011-10-12', 1, null);
多表查询-笛卡尔积
消除笛卡尔积
# 多表查询 -- 笛卡尔积
select * from emp, dept where emp.dept_id = dept.id;
多表查询分类
三、内连接（39）
内连接
- 隐式内连接
select 字段列表 from 表1, 表2 where 条件...;
- 显式内连接
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
内连接查询的是两张表交集的部分
演示：
- 查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
- 查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
# 1. 查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
# -- 表结构：emp, dept
# -- 连接条件: emp.dept_id = dept.id;
select emp.name,dept.name from emp, dept where emp.dept_id = dept.id;
select e.name, d.name from emp e, dept d where e.dept_id = d.id;
# 2. 查询每一个员工的姓名，及关联的部门的名称（显式内连接实现）
# -- 表结构：emp, dept
# -- 连接条件: emp.dept_id = dept.id;
select e.name, d.name from emp e inner join dept d on e.dept_id = d.id;
select e.name, d.name from emp e join dept d on e.dept_id = d.id;
四、外连接
外连接
- 左外连接
select 字段列表 from 表1 left [outer] join 表2 on 条件...;
- 右外连接
select 字段列表 from 表1 right [outer] join 表2 on 条件...;
演示：
- 查询emp表的所有数据，和对应的部门信息（左外连接）
2.查询dept表的所有数据，和对应的员工信息（右外连接）
# 演示：
# 1.查询emp表的所有数据，和对应的部门信息（左外连接）
select e.*,d.name from emp e left outer join dept d on e.dept_id = d.id;
select e.*,d.name from emp e left join dept d on e.dept_id = d.id;
# 2.查询dept表的所有数据，和对应的员工信息（右外连接）
select d.*, e.* from emp e right outer join dept d on e.dept_id=d.id;
select d.*, e.* from dept d left outer join emp e on e.dept_id = d.id;
五、自连接
自连接
演示：
- 查询员工 及其 所属领导的名字
- 查询所有员工 emp 及其领导的名字 emp，如果员工没有领导，也需要查询出来
# 演示：
# 1. 查询员工 及其 所属领导的名字
# -- 表结构： emp a, emp b
# -- 条件：e1.managerid = e2.id
select a.name, b.name from emp a, emp b where a.managerid = b.id;
# 2. 查询所有员工 emp 及其领导的名字 emp，如果员工没有领导，也需要查询出来
# -- 表结构： emp a, emp b
select a.name '员工', b.name '领导' from emp a left join emp b on a.managerid = b.id;
联合查询-union，union all
联合查询
select 字段列表 form 表A ...
union [all]
select 字段列表 form 表B ...;
演示：
- 将薪资低于 5000 的员工, 和 年龄大于 50 岁的员工全部查询出来。
# 演示：
# 1. 将薪资低于 5000 的员工, 和 年龄大于 50 岁的员工全部查询出来。
select * from emp where salary < 5000
union
select * from emp where age > 50;
六、子查询
子查询
标量子查询
演示：
- 查询“销售部” 的所有员工信息
- 查询在 “方东白” 入职后的员工信息
# 演示：
# 1. 查询“销售部” 的所有员工信息
# -- a. 查询 "销售部" 部门id
select id from dept where name = '销售部';
# -- b. 根据销售部id，查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');
# 2. 查询在 “方东白” 入职后的员工信息
# -- a. 查询 ‘东方白’ 入职时间
select entrydate from emp where name = '房东白';
# -- b. 根据‘房东白’的入职时间，查询入职时间以后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = '房东白');
6.2 列子查询
列子查询
练习：
- 查询“销售部” 和 “市场部” 的所有员工信息
- 查询比财务部所有人工资都高的员工信息
- 查询比研发部其中任意一人工资高的员工信息
# 1. 查询“销售部” 和 “市场部” 的所有员工信息
# -- a. 查询 “销售部” 和 “市场部” 的部门id
select id from dept where name = '销售部' or name = '市场部';
# -- b. 根据id 查询所有的员工信息
select * from emp where dept_id in (select id from dept where name = '销售部' or name = '市场部');
# 2. 查询比财务部所有人工资都高的员工信息
# -- a. 查询财务部的所有人工资
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
# -- b. 查询比这个工资高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = '财务部'));
# 3. 查询比研发部其中任意一人工资高的员工信息
# -- a. 查询'研发部'的所有人的工资
select salary from emp where dept_id = (select id from dept where name = '研发部');
# -- b. 查询 比 研发部任意一个人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = '研发部'));
select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where name = '研发部'));
6.3 行子查询
行子查询
作业：
- 查询与 “张无忌” 的薪资及直属领导相同的员工信息；
# 1. 查询与 “张无忌” 的薪资及直属领导相同的员工信息；
# -- a. 查询张无忌的 薪资 及直属领导
select salary, managerid from emp where name = '张无忌';
# -- b. 查询与‘张无忌’的薪资及直属领导相同员工信息
select * from emp where (salary, managerid) = (select salary, managerid from emp where name = '张无忌');
6.4 表子查询
表子查询
练习：
- 查询与“鹿杖客”, “宋远桥” 的职位和薪资相同的员工信息
- 查询入职日期是 “2006-01-01” 之后的员工信息，及其部门信息
# 1. 查询与“鹿杖客”, “宋远桥” 的职位和薪资相同的员工信息
# -- a. 查询与“鹿杖客”, “宋远桥” 的职位和薪资
select job, salary from emp where name = '鹿杖客' or name = '宋远桥';
# -- b. 查询与“鹿杖客”, “宋远桥” 的职位和薪资相同的员工信息
select * from emp where (job, salary) in (select job, salary from emp where name = '鹿杖客' or name = '宋远桥');
# 2. 查询入职日期是 “2006-01-01” 之后的员工信息，及其部门信息
# -- a. 查询入职日期是 “2006-01-01” 之后的员工信息
select * from emp where entrydate > '2006-01-01';
# -- b. 查询入职日期是 “2006-01-01” 之后的员工信息，及其部门信息
select e.*, d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on d.id = e.dept_id
七、多表查询案例
练习：根据需求，完成SQL语句的编写
- 查询员工的姓名、年龄、职位、部门信息。
- 查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
- 查询拥有员工的部门ID、部门名称。
- 查询所有年龄大于40岁的员工，及其归属的部门名称；如果员工没有分配部门，也需要展示出来。
- 查询所有员工的工资等级。
- 查询“研发部”所有员工的信息及工资等级。
- 查询“研发部”员工的平均工资。
- 查询工资比“灭绝”高的员工信息。
- 查询比平均薪资高的员工信息。
- 查询低于本部门平均工资的员工人数。
- 查询所有的部门信息，并统计部门的员工人数。
- 查询所有学生的选课情况，展示出学生名称，学号，课程名称。
# 准备数据
create table salgrade(
grade int,
losal int,
hisal int
) comment '薪资等级表';
insert into salgrade values (1,0,3000);
insert into salgrade values (2,3001,5000);
insert into salgrade values (3,5001,8000);
insert into salgrade values (4,8001,10000);
insert into salgrade values (5,10001,15000);
insert into salgrade values (6,15001,20000);
insert into salgrade values (7,20001,25000);
insert into salgrade values (8,25001,30000);
总结
多表关系
