MySQL学习笔记之多表关系

2024-08-25 11:04
文章标签 mysql 学习 关系 笔记 database 之多表

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

文章目录

  • MySQL多表关系
        • 一、创建多对多关系
          • 1. 创建学生表
          • 2. 创建课程表
          • 3. 创建学生课程中间表
        • 二、一对一关系
          • 1. 创建用户基本信息表
          • 2. 创建用户教育信息表
        • 三、内连接查询
          • 1. 查询每一个员工的姓名及其关联部门的名称（隐式内连接）
          • 2. 显式内连接实现同样的查询
        • 四、外连接查询
          • 1. 左外连接查询员工的所有数据和对应的部门信息
          • 2. 右外连接查询部门的所有数据和对应员工信息
        • 五、自连接查询
          • 1. 查询所有员工及其所属领导的名字
          • 2. 查询所有员工及其领导的名字，如果员工没有领导也要查询出来
        • 六、联合查询（UNION）
          • 1. 查询薪资低于5000的员工和年龄大于50岁的员工
        • 七、子查询
          • 1. 标量子查询：查询“销售部”的所有员工信息
          • 2. 列子查询：查询“销售部”和“市场部”的所有员工信息
          • 3. 行子查询：查询与“张无忌”的薪资及直属领导相同的员工信息
          • 4. 表子查询：查询入职日期是“2006-01-01”之后的员工信息及其部门信息
        • 八、多表查询案例
          • 1. 查询员工的姓名、年龄、职位、部门信息（隐式内连接）
          • 2. 查询拥有员工的部门ID、部门名称
          • 3. 查询“研发部”员工的平均工资
          • 4. 查询所有学生的选课情况
      • 完整代码

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学习笔记之多表关系的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！


http://www.chinasem.cn/article/1105375。

相关文章

Mysql数据库聚簇索引与非聚簇索引举例详解

Mysql数据库聚簇索引与非聚簇索引举例详解

《Mysql数据库聚簇索引与非聚簇索引举例详解》在MySQL中聚簇索引和非聚簇索引是两种常见的索引结构,它们的主要区别在于数据的存储方式和索引的组织方式,：本文主要介绍Mysql数据库聚簇索引与非... 目录前言一、核心概念与本质区别二、聚簇索引（Clustered Index）1. 实现原理（以 Inno
阅读更多...
sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符

sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符

《sqlserver、mysql、oracle、pgsql、sqlite五大关系数据库的对象名称和转义字符》：本文主要介绍sqlserver、mysql、oracle、pgsql、sqlite五大... 目录一、转义符1.1 oracle1.2 sqlserver1.3 PostgreSQL1.4 SQLi
阅读更多...
MySQL数据库双机热备的配置方法详解

MySQL数据库双机热备的配置方法详解

《MySQL数据库双机热备的配置方法详解》在企业级应用中,数据库的高可用性和数据的安全性是至关重要的,MySQL作为最流行的开源关系型数据库管理系统之一,提供了多种方式来实现高可用性,其中双机热备（M... 目录1. 环境准备1.1 安装mysql1.2 配置MySQL1.2.1 主服务器配置1.2.2 从
阅读更多...
深入理解Mysql OnlineDDL的算法

深入理解Mysql OnlineDDL的算法

《深入理解MysqlOnlineDDL的算法》本文主要介绍了讲解MysqlOnlineDDL的算法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小... 目录一、Online DDL 是什么？二、Online DDL 的三种主要算法2.1COPY（复制法）
阅读更多...
mysql8.0.43使用InnoDB Cluster配置主从复制

mysql8.0.43使用InnoDB Cluster配置主从复制

《mysql8.0.43使用InnoDBCluster配置主从复制》本文主要介绍了mysql8.0.43使用InnoDBCluster配置主从复制,文中通过示例代码介绍的非常详细,对大家的学习或者... 目录1、配置Hosts解析(所有服务器都要执行)2、安装mysql shell(所有服务器都要执行)3、
阅读更多...
k8s中实现mysql主备过程详解

k8s中实现mysql主备过程详解

《k8s中实现mysql主备过程详解》文章讲解了在K8s中使用StatefulSet部署MySQL主备架构,包含NFS安装、storageClass配置、MySQL部署及同步检查步骤,确保主备数据一致... 目录一、k8s中实现mysql主备1.1 环境信息1.2 部署nfs-provisioner1.2.
阅读更多...
MySQL中VARCHAR和TEXT的区别小结

MySQL中VARCHAR和TEXT的区别小结

《MySQL中VARCHAR和TEXT的区别小结》MySQL中VARCHAR和TEXT用于存储字符串,VARCHAR可变长度存储在行内,适合短文本；TEXT存储在溢出页,适合大文本,下面就来具体的了解... 目录一、VARCHAR 和 TEXT 基本介绍1. VARCHAR2. TEXT二、VARCHAR
阅读更多...
MySQL中C接口的实现

MySQL中C接口的实现

《MySQL中C接口的实现》本节内容介绍使用C/C++访问数据库,包括对数据库的增删查改操作,主要是学习一些接口的调用,具有一定的参考价值,感兴趣的可以了解一下... 目录准备mysql库使用mysql库编译文件官方API文档对象的创建和关闭链接数据库下达sql指令select语句前言：本节内容介绍使用C/
阅读更多...
mybatis直接执行完整sql及踩坑解决

mybatis直接执行完整sql及踩坑解决

《mybatis直接执行完整sql及踩坑解决》MyBatis可通过select标签执行动态SQL,DQL用ListLinkedHashMap接收结果,DML用int处理,注意防御SQL注入,优先使用#... 目录myBATiFBNZQs直接执行完整sql及踩坑select语句采用count、insert、u
阅读更多...
MySQL之搜索引擎使用解读

MySQL之搜索引擎使用解读

《MySQL之搜索引擎使用解读》MySQL存储引擎是数据存储和管理的核心组件,不同引擎（如InnoDB、MyISAM）采用不同机制,InnoDB支持事务与行锁,适合高并发场景；MyISAM不支持事务,... 目录mysql的存储引擎是什么MySQL存储引擎的功能MySQL的存储引擎的分类查看存储引擎1.命令
阅读更多...

Copyright China编程 23002807@qq.com

沪ICP备2023033072号-2