MySQL第11讲--多表查询的介绍

前言

  在MySQL第10讲–约束的介绍中讲了数据库的几种约束条件：非空约束、唯一约束、主键约束、外键约束、检查约束、默认约束。下图对着几种约束做了总结：

  接下来我们将要介绍数据库多表查询

多表关系

概述：在项目开发中进行数据库表结果设计时，会根据业务需求和模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着某种关联，基本上分为三种：一对多（多对一）、一对一、多对多。
  接下来我们依次介绍一下这几种关联关系：

• 一对多
  案例：部门与员工之间的关系
  关系：一个部门对应多个员工，一个员工对应多个部门。
  实现：在多的一方建立外键，指向一的一方主键。
  
• 多对多
  案例：学生和课程之间的关系
  关系：一个学生可以选多门课程，一门课程可以被对个学生选择；
  实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键；
  
    接下来通过一个实例展现一下多对多的关系：
    首先创建一个学生列表：

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

# 给表表结构添加数据
insert into student values (null,'李元昊','1011001'),(null,'李德明','1011002'),(null,'李集钱','1011003'),(null,'李勉县','1011004');

  其次创建一个课程列表并添加数据；

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

)comment '课程表';
# 添加课程表的数据
insert into course values (null,'C++'),(null,'python'),(null,'java'),(null,'mysql');

  最后我们创建一个课程表和学生表之间的中间表来维护课程表和学生表之间的关系；
创建中间表并添加相应的数据：

create table student_coures(
    id int auto_increment primary key comment '主键',
    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_coures values (null,1,1),(null,1,2),(null,1,3),(null,2,2),(null,2,3),(null,3,4);

  创建完这三个表之后我们可以用可视化的界面展示三个表之间的关系如下图所示：

• 一对一
  案例：用户与用户详情之间的关系
  关系：一对一关系，多用于单表拆分，将一张表的基础字段放到一张表上，其他详情字段放到另一张表上，可以提升操作效率。
  实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)。
  如下图所示可以看到一张表中包含的信息比较多，操作起来不是很便利，我们可以对这张表进行拆分，
  
  如下图所示，上面的表拆分成了两张表
  
  两个表的键相互关联，并且是唯一的。
  
  接下来我们演示一下一对一的关系；
  首先创建两个表tb_user和tb_user_edu，创建两个表的语句如下：

创建tb_user

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

创建tb_user_edu

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 '用户教育信息表';

分别给这两张表添加信息：
给tb_user添加信息：

insert into tb_user(id, name, age, gender, phone) VALUES (null, '黄渤','45','男','18033575534'),
                                                         (null, '冰冰','35','女','18033575535'),
                                                         (null, '马芸','36','女','18033575536'),
                                                         (null, '艳红','35','女','18033575537');

给tb_user_edu添加信息：

insert into tb_user_edu(id, degree, major, primaryschool, middleschool, university, userid) VALUES
                                          (null,'本科','表演','静安第一小学','静安第一中学','北京电影学院','1'),
                                          (null,'本科','舞蹈','武汉第一小学','武汉第一中学','上海电影学院','2'),
                                          (null,'本科','英语','浙江第一小学','浙江第一中学','杭州电子科技','3'),
                                          (null,'本科','数学','南宫第一小学','南宫第一中学','北京工业大学','4');

打开tb_user_edu可以看到有一个外键userid，他关联的就是主键的基本id，由于我们为userid加上了约束，所以一条约束就会有一个基本信息。

多表查询概述

  概述：从多张表中查询数据
  笛卡尔积：笛卡尔积指在数学中，两个集合A集合和B集合，所有的组合情况。（在多表查询时需要消除无效的迪卡尔积）

多表查询的分类

• 多表查询的分类：连接查询和子查询；

连接查询

• 连接查询又分为内链接和外链接
  

内链接

  定义：相当于查询上图A和B交集的部分
  内链接查询语法：隐式内连接和显示内链接

• 隐式内连接语法

SELECT 字段列表 FROM 表1, 表2 WHERE 条件...;

实例1：

select emp.name,dept.name from emp,dept where emp.depth_id=dept.id;

结果：

• 显示内连接语法

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 连接条件...;

实例1：

select d.name, e.name from dept d inner join emp e on d.id = e.depth_id;

结果：

外链接

  外链接分为：左外链接和右外链接。

• 左外链接：查询左表所有数据，以及两张表交集部分数据。
  查询语法：

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...;

实例1：

select e.name, d.name from emp e left join dept d on d.id = e.depth_id;

结果：

• 右外链接：查询右表所有数据，以及两张表交集部分数据

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...;

实例1：

select e.name, d.name from emp e right join dept d on d.id = e.depth_id;

结果：

自连接

  自连接：当前表与自身的连接查询，自连接必须使用表别名。

• 自连接语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
# 自连接查询，可以是内链接查询，也可以是外链接查询

联合查询

  联合查询：关键字union或union all ；
对于联合查询就是把多次查询的结果合并起来，形成一个新的查询结果集。

• 查询语法

SELECT 查询列表 FROM 表A...
UNION [ALL]
SELECT 查询列表 FROM 表B...

注意：(1) UNION ALL ：是直接对查询结果进行合并；（2） UNION：是对查询结果进行去重；
（3 ）联合查询多张表的列数必须保持一致，字段类型也必须保持一致；

子查询

  子查询：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询。

• 查询语法

SELECT * FROM t1 WHERE column1= (SELECT column1 FROM t2);

标量子查询

   标量子查询：子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询称为标量子查询。
   标量子查询常用的操作符：= <> > < >= <=
案例1：查询 “财务部” 所有员工信息。

select * from emp where depth_id=(select id from dept where name='财务部');
# 括号内是子查询，括号内的查询结果直接当做外面查询的条件

查询结果：

案例2：查询在“张三”之后入职的所有员工

select * from emp where entrydata>(select entrydata from emp where name='张三');

查询结果：

列子查询

   列子查询：子查询返回的结果是一列（可以是多行），这种子查询称为列子查询；
   列子查询常使用的操作符：IN 、 NOT IN 、 ANY、SOME、ALL

操作符	描述
IN	在指定的集合范围之内，多选一
NOT IN	不在指定的集合范围之内
ANY	子查询返回列表中，有任意一个满足即可
SOME	与 ANY等同使用 SOME 的地方都可以使用ANY
ALL	子查询返回列表所有值都必须满足

案例1：查询 “财务部"和"测试部” 的所有员工信息。

select * from emp where depth_id in(select id from dept where name='财务部' or name='测试部');

查询结果：

案例2：查询 比"财务部"所有人工资都高的员工信息；

select * from emp where salary > all(select salary from emp where depth_id=(select id from dept where name='财务部'));

查询结果：

行子查询

   行子查询：子查询返回的结果是一行（可以是多列），这种子查询称为行子查询；
   行子查询常使用的操作符：IN 、 NOT IN 、 =、<>
案例1：查询 与“李四”薪资相同的所有员工信息。

select * from emp where salary=(select salary from emp where name='李四');

查询结果：

表子查询

   表子查询：子查询返回的结果是多行多列，这种子查询称为表查询；
   表子查询常使用的操作符：IN
案例1：查询 与“孙山”和"李白"薪资和职位相同的所有员工信息。

select * from emp where (job, salary) in(select job, salary from emp where name='孙山' or  name ='李白');

查询结果：

以上为多表查询的介绍，如有错误欢迎指正，如果帮到您请点赞加收藏吧！