MySql基础四-多表查询

一、多表关系

多表关系

一对多

多对多

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

总结

多表关系

最后编辑于：2023.07.28 17:50:23

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 220,192评论 6赞 511
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 93,858评论 3赞 396
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 166,517评论 0赞 357
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 59,148评论 1赞 295
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 68,162评论 6赞 397
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,905评论 1赞 308
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,537评论 3赞 420
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 39,439评论 0赞 276
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,956评论 1赞 319
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 38,083评论 3赞 340
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 40,218评论 1赞 352
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,899评论 5赞 347
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 41,565评论 3赞 331
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 32,093评论 0赞 23
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 33,201评论 1赞 272
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 48,539评论 3赞 375
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 45,215评论 2赞 358

MySql基础四-多表查询

一、多表关系

二、多表查询概述

三、内连接（39）

四、外连接

五、自连接

联合查询-union，union all

六、子查询

6.2 列子查询

6.3 行子查询

6.4 表子查询

七、多表查询案例

总结

推荐阅读更多精彩内容