MySql基础四-多表查询

作者: Matt_Z_ | 来源:发表于2023-07-27 17:05 被阅读0次

一、多表关系

多表关系

一对多

多对多

# 多对多
# 学生表
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);

总结

多表关系

MySql基础四-多表查询

一、多表关系

二、多表查询概述

三、内连接（39）

四、外连接

五、自连接

联合查询-union，union all

六、子查询

6.2 列子查询

6.3 行子查询

6.4 表子查询

七、多表查询案例

总结

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读