MySQL学习笔记之多表关系

本文主要是介绍MySQL学习笔记之多表关系，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

MySQL多表关系

一、创建多对多关系

在多对多关系中，一般需要借助第三张表来管理两个表之间的关联。示例中，我们创建了学生表 (student)、课程表 (course) 以及学生课程中间表 (student_course)。

1. 创建学生表

create table student
(id   int auto_increment primary key comment '主键 ID',name varchar(10) comment '姓名 ',no   varchar(10) comment '学号 '
) comment '学生表 ';

2. 创建课程表

create table course
(id   int auto_increment primary key comment '主键 ID',name varchar(10) comment '课程名称 '
) comment '课程表 ';

3. 创建学生课程中间表

create table student_course
(id        int auto_increment comment '主键' primary key,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 '学生课程中间表 ';

二、一对一关系

在一对一关系中，一般通过在一个表中创建唯一外键来实现。

1. 创建用户基本信息表

create table tb_user
(id     int auto_increment primary key comment '主键 ID',name   varchar(10) comment '姓名',age    int comment '年龄',gender char(1) comment '1 : 男 , 2 : 女',phone  char(11) comment '手机号'
) comment '用户基本信息表';

2. 创建用户教育信息表

create table tb_user_edu
(id            int auto_increment primary key 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 '用户教育信息表';

三、内连接查询

内连接返回两个表中匹配的行。分为隐式和显式两种写法。

1. 查询每一个员工的姓名及其关联部门的名称（隐式内连接）

select e.name, d.name
from empl e, dept d
where e.dept_id = d.id;

2. 显式内连接实现同样的查询

select e.name, d.name
from empl e
join dept d on e.dept_id = d.id;

四、外连接查询

外连接分为左外连接和右外连接。左外连接返回左表的所有行，右外连接返回右表的所有行。

1. 左外连接查询员工的所有数据和对应的部门信息

select e.*, d.name
from empl e
left join dept d on e.dept_id = d.id;

2. 右外连接查询部门的所有数据和对应员工信息

select d.*, e.*
from dept d
right join empl e on d.id = e.dept_id;

五、自连接查询

自连接是一种特殊的内连接，表与自己进行连接。

1. 查询所有员工及其所属领导的名字

select a.name, b.name
from empl a, empl b
where a.managerid = b.id;

2. 查询所有员工及其领导的名字，如果员工没有领导也要查询出来

select a.name '员工', b.name '领导'
from empl a
left join empl b on a.managerid = b.id;

六、联合查询（UNION）

UNION 用于合并两个或多个 SELECT 语句的结果集，UNION ALL 不会去重。

1. 查询薪资低于5000的员工和年龄大于50岁的员工

select *
from empl
where salary < 5000
union
select *
from empl
where age > 50;

七、子查询

子查询是一种查询中嵌套另一条查询的方式，分为标量子查询、列子查询、行子查询和表子查询。

1. 标量子查询：查询“销售部”的所有员工信息

select *
from empl
where dept_id = (select id from dept where name = '销售部');

2. 列子查询：查询“销售部”和“市场部”的所有员工信息

select *
from empl
where dept_id in (select id from dept where name = '销售部' or name = '市场部');

3. 行子查询：查询与“张无忌”的薪资及直属领导相同的员工信息

select *
from empl
where (salary, managerid) = (select salary, managerid from empl where name = '张无忌');

4. 表子查询：查询入职日期是“2006-01-01”之后的员工信息及其部门信息

select e.*, d.*
from (select * from empl where entrydate > '2006-01-01') e
left join dept d on e.dept_id = d.id;

八、多表查询案例

1. 查询员工的姓名、年龄、职位、部门信息（隐式内连接）

select e.name, e.age, e.job, d.name
from empl e, dept d
where e.dept_id = d.id;

2. 查询拥有员工的部门ID、部门名称

select distinct d.id, d.name
from empl e, dept d
where e.dept_id = d.id;

3. 查询“研发部”员工的平均工资

select avg(salary)
from empl e
join dept d on d.id = e.dept_id
where d.name = '研发部';

4. 查询所有学生的选课情况

select s.name, s.no, c.name
from student s
join student_course sc on s.id = sc.studentid
join course c on sc.courseid = c.id;

完整代码

-- ------------------------------------------- 多表关系演示 -----------------------------------------------------
-- 多对多
create table student
(id   int auto_increment primary key 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 auto_increment primary key 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 auto_increment comment '主键' primary key,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, 2),(null, 2, 3),(null, 3, 4);-- 一对一
create table tb_user
(id     int auto_increment primary key 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 auto_increment primary key 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(id, name, age, gender, phone)
values (null, '黄渤', 45, '1', '18800001111'),(null, '冰冰', 35, '2', '18800002222'),(null, '码云', 55, '1', '18800008888'),(null, '李彦宏', 50, '1', '18800009999');
insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid)
values (null, '本科', '舞蹈', '静安区第一小学', '静安区第一中学', '北京舞蹈学院', 1),(null, '硕士', '表演', '朝阳区第一小学', '朝阳区第一中学', '北京电影学院', 2),(null, '本科', '英语', '杭州市第一小学', '杭州市第一中学', '杭州师范大学', 3),(null, '本科', '应用数学', '阳泉第一小学', '阳泉区第一中学', '清华大学', 4);-- 多表查询
select *
from empl,dept
where empl.dept_id = dept.id;
-- 消除笛卡尔积-- 内连接
-- 隐式内连接
--   select 字段列表 from 表1, 表2 where 条件 ...;
-- 显式内连接
--   select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;-- 内连接演示
-- 1.查询每一个员工的姓名，及关联的部门的名称（隐式内连接实现）
-- 表结构：empl, dept
-- 连接条件：emp.dept_id = dept.id
select empl.name, dept.name
from empl,dept
where empl.dept_id = dept.id;
select e.name, d.name
from empl e,dept d
where e.dept_id = d.id;-- 2.查询每一个员工的姓名，及关联部门的名称（显式内连接实现）
select empl.name, dept.name
from empljoin dept on empl.dept_id = dept.id;
select e.name, d.name
from empl ejoin dept d on d.id = e.dept_id;-- 外连接
-- 左外连接
--   select 字段列表 from 表1 left [outere] join 表2 on 条件 ...;
--   相当于查询表1（左表）的所有数据包含表1和表2交集部分的数据
-- 右外连接
--   select 字段列表 from 表1 right [outer] join 表2 on 条件 ...;
--   相当于查询表2（右表）的所有数据包含表1和表2交集部分的数据-- 外连接演示
-- 1.查询empl表的所有数据和对应的部门信息（左外连接）
-- 表结构：empl, dept
-- 连接条件：emp.dept_id = dept.id
select e.*, d.name
from empl eleft join dept d on e.dept_id = d.id;-- 2.查询dept表的所有数据和对应员工信息（右外连接）
select d.*, e.*
from dept dright join empl e on d.id = e.dept_id;
select d.*, e.*
from empl eleft join dept d on e.dept_id = d.id;-- 自连接
-- select 字段列表 from 表A 别名A join 表A 别名B on 条件 ...;
-- 自连接查询，可以是内连接查询，也可以是外连接查询。-- 自连接演示
-- 1.查询所有员工以及所属领导的名字
-- 表结构：empl
-- 连接条件：a.managerid = b.id
select a.name, b.name
from empl a,empl b
where a.managerid = b.id;-- 2.查询所有员工empl及其领导的名字empl，如果员工没有领导也要查询出来
select a.name '员工', b.name '领导'
from empl aleft join empl b on a.managerid = b.id;-- 联合查询-union，union all
--   select 字段列表 from 表A ...
--   union [all]
--   select 字段列表 from 表B ...;
-- 对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致。
-- union all 会将全部的数据直接合并在一起，union 会对合并之后的数据去重。-- 联合查询演示
-- 1.将薪资低于5000的员工和年龄大于50岁的员工全部查询出来。
select *
from empl
where salary < 5000
union
select *
from empl
where age > 50;-- 子查询
-- 概念:SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。
-- select * from t1 where column1 = (select column1 from t2);
-- 子查询外部的语句可以是INSERT / UPDATE / DELETE / SELECT 的任何一个。-- 根据子查询结果不同，分为:
--   标量子查询(子查询结果为单个值)
--   列子查询(子查询结果为一列)
--   行子查询(子查询结果为一行)
--   表子查询(子查询结果为多行多列)-- 根据子查询位置，分为:WHERE之后、FROM之后、SELECT 之后。-- 标量子查询
--   子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询成为标量子查询
--   常用的操作符:= <> > >= < <=-- 1.查询”销售部“的所有员工信息
-- a.查询”销售部“部门ID
select id
from dept
where name = '销售部';
-- b.根据”销售部“部门ID，查询员工信息
select *
from empl
where dept_id = 4;
select *
from empl
where dept_id = (select id from dept where name = '销售部');-- 2.查询在”水电费“入职之后的所有员工信息
-- a.查询水电费的入职日期
select entrydate
from empl
where name = '水电费';
-- b.查询指定入职日期之后的员工信息
select *
from empl
where entrydate > '2006-01-01';
select *
from empl
where entrydate > (select entrydate from empl where name = '水电费');-- 列子查询
--   子查询返回的结果是一列(可以是多行)，这种子查询称为列子查询
--   常用的操作符:IN 、NOTIN、ANY 、SOME、ALL
--     操作符    描述
--     IN      在指定的集合范围之内，多选一
--     NOT IN  不在指定的集合范围之内
--     ANY     子查询返回列表中，有任意一个满足即可
--     SOME    与ANY等同，使用SOME的地方都可以使用ANY
--     ALL     子查询返回列表的所有值都必须满足-- 1. 查询“销售部”和 “市场部” 的所有员工信息
-- a.查询 “销售部” 和 “市场部”  的部门ID
select id
from dept
where name = '销售部'or name = '市场部';
-- b.根据部门ID，查询员工信息
select *
from empl
where dept_id in (2, 4);
select *
from empl
where dept_id in (select id from dept where name = '销售部' or name = '市场部');-- 2. 查询比财务部所有人工资都高的员工信息
-- a. 查询所有 财务部 人员工资
select id
from dept
where name = '财务部';
select salary
from empl
where dept_id = (select id from dept where name = '财务部');
-- b. 比 财务部 所有人工资都高的员工信息
select *
from empl
where salary > all (select salary from empl where dept_id = (select id from dept where name = '财务部'));-- 3. 查询比研发部其中任意一人工资高的员工信息
-- a.查询研发部所有人工资
select salary
from empl
where dept_id = (select id from dept where name = '研发部');
-- b.比研发部其中任意一人工资高的员工信息
select *
from empl
where salary > any (select salary from empl where dept_id = (select id from dept where name = '研发部'));-- 行子查询
--   子查询返回的结果是一行(可以是多列)，这种子查询称为行子查询
--   常用的操作符:= 、<>、IN 、NOT IN-- 1.查询与”张无忌“的薪资及直属领导相同的员工信息
-- a.查询“张无忌” 的薪资及直属领导
select salary, managerid
from empl
where name = '张无忌';
-- b.查询与“张无忌” 的薪资及直属领导相同的员工信息
select *
from empl
where (salary, managerid) = (12500, 1);
select *
from empl
where (salary, managerid) = (select salary, managerid from empl where name = ' 张无忌 ');-- 表子查询
--   子查询返回的结果是多行多列，这种子查询称为表子查询
--   常用的操作符:IN-- 1. 查询与“杨逍”,"韦一笑”的职位和薪资相同的员工信息
-- a.查询 "杨逍","韦一笑”的职位和薪资
select job, salary
from empl
where name = '杨逍'or name = '韦一笑';
-- b.查询与"杨逍”,"韦一笑” 的职位和薪资相同的员工信息
select *
from empl
where (job, salary) in (select job, salary from empl where name = '杨逍' or name = '韦一笑');-- 2. 查询入职日期是 “2006-01-01” 之后的员工信息，及其部门信息
-- a.入职日期是 “2006-01-01” 之后的员工信息
select *
from empl
where entrydate > '2006-01-01';
-- b.查询这部分员工， 对应的部门信息
select e.*, d.*
from (select * from empl where entrydate > '2006-01-01') eleft join dept d on e.dept_id = d.id;-- ------------------------------------> 多表查询案例 <-------------------------------------
create table salgrade
(grade int,losal int,hisal int
) comment '薪资等级表';insert into salgrade
values (1, 0, 300);
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);-- 1.查询员工的姓名、年龄、职位、部门信息。（隐式内连接）
-- 表：empl, dept
-- 连接条件：empl.dept_id = dept.id
select e.name, e.age, e.job, d.name
from empl e,dept d
where e.dept_id = d.id;-- 2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。（显式内连接）
-- 表：empl, dept
-- 连接条件：empl.dept_id = dept.id
select e.name, e.age, e.job, d.name
from empl einner join dept d on e.dept_id = d.id
where e.age < 30;-- 3.查询拥有员工的部门ID、部门名称。
-- 表：empl, dept
-- 连接条件：empl.dept_id = dept.id
select distinct d.id, d.name
from empl e,dept d
where e.dept_id = d.id;-- 4.查询所有年龄大于40岁的员工,及其归属的部门名称; 如果员工没有分配部门,也需要展示出来。
-- 表：empl, dept
-- 连接条件：empl.dept_id = dept.id
-- 外连接
select e.*, d.name
from empl eleft join dept d on e.dept_id = d.id
where e.age > 40;-- 5.查询所有员工的工资等级。
-- 表：empl,salgrade
-- 连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.name, s.grade
from empl e,salgrade s
where e.salary >= s.losaland e.salary <= s.hisal;
select e.name, s.grade
from empl e,salgrade s
where e.salary between s.losal and s.hisal;-- 6.查询“研发部”所有员工的信息及工资等级
-- 表：empl， dept， salgade
-- 连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal, empl.dept_id = dept.id
-- 查询条件：dept.name = '研发部'
select distinct e.*, s.grade
from empl e,dept d,salgrade s
where e.dept_id = (select id from dept where name = '研发部')and e.salary between s.losal and s.hisal;
select e.*, s.grade
from empl e,dept d,salgrade s
where e.dept_id = d.idand (e.salary between s.losal and s.hisal)and d.name = '研发部';-- 7.查询“研发部”员工的平均工资
-- 表：empl， dept
-- 连接条件：empl.dept_id = dept.id
select avg(salary)
from empl ejoin dept d on d.id = e.dept_id
where d.name = '研发部';-- 8.查询工资比“开会吧”高的员工信息。
-- 表：empl
-- 查询条件：salary > (select salary from empl where empl.name = '开会吧');
select *
from empl
where salary > (select salary from empl where name = '开会吧');-- 9.查询比平均薪资高的员工信息。
select *
from empl
where salary > (select avg(salary) from empl);-- 10.查询低于本部门平均工资的员工信息。
select e1.*
from empl e1
where e1.salary < (select avg(salary)from empl e,dept dwhere e.dept_id = e1.dept_id);-- 11.查询所有的部门信息，并统计部门的员工人数。
select d.id, d.name, (select count(*) from empl e where e.dept_id = d.id) '人数'
from dept d;-- 12.查询所有学生的选课情况，展示出学生名称,学号,课程名称
-- 表: student ，course , student_course
-- 连接条件: student.id = student_course.studentid, course.id = student_course.courseid
select s.name, s.no, c.name
from student s,student_course sc,course c
where s.id = sc.studentidand sc.courseid = c.id;

这篇关于MySQL学习笔记之多表关系的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---