2024Mysql And Redis基础与进阶操作系列（6）作者——LJS[含MySQL 多表之一对一/多；多对多；多表联合查询等详解步骤及常见报错问题所对应的解决方法]

MySQL多表操作

1多表关系

简介

1.1 一对一关系

比如

1.2 一对多/多对一关系

比如：

实现规则：

1.3 多对多关系

举例：

规则：

2. 多表联合查询

简介

多表查询有以下分类

知识补充——笛卡尔积（了解即可）

交叉连接查询 [产生笛卡尔积]

内连接查询(使用的关键字 inner join -- inner可以省略)

select * from A inner join B on 条件;

子查询        select的嵌套表自关联： 将一张表当成多张表来用

2.1 数据准备——详解步骤如下

准备查询数据

创建部门表

创建员工表

给dept3表添加数据

给emp表添加数据

2.2 交叉连接查询

格式

示例

2.3 内连接查询

格式

隐式内连接

显示内连接

示例

查询每个部门的所属员工

查询研发部和销售部的所属员工

查询每个部门的员工数,并升序排序

查询人数大于等于3的部门，并按照人数降序排序

2.4 外连接查询

简介

注意：

格式

左外连接

右外连接：

满外连接:

外连接查询——举例  

2.5 子查询

简介

特点

举例

查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄

查询年研发部和销售部的员工信息，包含员工号、员工名字

查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字

子查询关键字

1.ALL关键字格式

特点

示例

查询年龄大于‘1003’部门所有年龄的员工信息

查询不属于任何一个部门的员工信息

2.ANY关键字

3.SOME关键字格式——格式

特点

4.IN关键字

格式

示例

查询研发部和销售部的员工信息，包含员工号、员工名字

5.EXISTS关键字

格式

特点

示例

2.6 自连接查询

简介

格式

示例

创建表,并建立自关联约束

添加数据

进行关联查询

MySQL多表操作

1多表关系

简介

：一个商城项目就需要分类表(category)、商品表(products)、订单表(orders)等多张表。且这些表 的数据之间存在一定的关系，如下图：

MySQL多表之间的关系可以概括为：一对一、一对多/多对一关系，多对多  

 1.1 一对一关系

比如

一个学生只有一张身份证；

一张身份证只能对应一学生。 在任一表中添加唯一外键，指向另一方主键，确保一对一关系。

一般一对一关系很少见，遇到一对一关系的表最好是合并表。

1.2 一对多/多对一关系

比如：

部门和员工关系：一个部门有多个员工，一个员工只能对应一个部门

实现规则：

在多的一方建立外键，指向一的一方的主键

 1.3 多对多关系

举例：

学生和课程关系：一个学生可以选择很多门课程，一个课程也可以被很多学生选择

规则：

多对多关系实现需要借助第三张中间表。

中间表至少包含两个字段，将多对多的关系，拆成一对多的关系，中间表至少要有两个外键，这两个外键分别指向原来的那两张表的主键

 2. 多表联合查询

简介

多表查询就是同时查询两个或两个以上的表，因为有的时候在查看数据的时候,需要显示的数据来自多张表.

多表查询有以下分类

知识补充——笛卡尔积（了解即可）

笛卡尔积是指两个集合之间所有可能的有序对的集合。

在数据库中，如果你对两个表进行交叉连接查询（CROSS JOIN），结果会是这两个表的笛卡尔积，即每一行的组合会和另一个表的每一行组合在一起。这通常会产生大量的数据，因为行数是两个表行数的乘积。

交叉连接查询 [产生笛卡尔积]

select * from A,B;

内连接查询(使用的关键字 inner join -- inner可以省略)

#隐式内连接

select * from A,B where 条件; 

显示内连接

select * from A inner join B on 条件;

select * from A inner join B on 条件;

#左外连接：left outer join 

select * from A left outer join B on 条件; 

#右外连接：right outer join

select * from A right outer join B on 条件; 

#满外连接: full outer join 

select * from A full outer join B on 条件;

子查询        select的嵌套表自关联： 将一张表当成多张表来用

2.1 数据准备——详解步骤如下

准备查询数据

use mydb3;

创建部门表

create table if not exists dept3(

    deptno varchar(20) primary key ,  #部门号
 
    name varchar(20) # 部门名字
);

创建员工表

create table if not exists emp3(
 
    eid varchar(20) primary key , #员工编号
 
    ename varchar(20), # 员工名字
 
    age int, # 员工年龄
 
    dept_id varchar(20)  #员工所属部门
);

给dept3表添加数据

insert into dept3 values('1001','研发部');

insert into dept3 values('1002','销售部');

insert into dept3 values('1003','财务部');

insert into dept3 values('1004','人事部');

给emp表添加数据

insert into emp3 values('1','乔峰',20, '1001');

insert into emp3 values('2','段誉',21, '1001');

insert into emp3 values('3','虚竹',23, '1001');

insert into emp3 values('4','阿紫',18, '1001');

insert into emp3 values('5','扫地僧',85, '1002');

insert into emp3 values('6','李秋水',33, '1002');

insert into emp3 values('7','鸠摩智',50, '1002');
 
insert into emp3 values('8','天山童姥',60, '1003');

insert into emp3 values('9','慕容博',58, '1003');

insert into emp3 values('10','丁春秋',71, '1005');

2.2 交叉连接查询

交叉连接查询

交叉连接查询返回被连接的两个表所有数据行的笛卡尔积笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配假如A表有m行数据，B表有n行数据，则返回m*n行数据笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

格式

select * from 表1,表2,表3….;

示例

select * from dept3,emp3;

2.3 内连接查询

格式

隐式内连接

select * from A,B where 条件;

显示内连接

select * from A inner join B on 条件;

示例

查询每个部门的所属员工

select * from dept3,emp3 where dept3.deptno = emp3.dept_id;

select * from dept3 inner join emp3 on dept3.deptno = emp3.dept_id;

查询研发部和销售部的所属员工

select * from dept3,emp3 where dept3.deptno = emp3.dept_id and name in( '研发
部','销售部');

select * from dept3 join emp3 on dept3.deptno = emp3.dept_id and name in( '研发
部','销售部');

查询每个部门的员工数,并升序排序

select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno order by total_cnt;


select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno order by total_cnt;

查询人数大于等于3的部门，并按照人数降序排序

select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;
 
select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno = emp3.dept_id group by deptno having total_cnt >= 3 order by total_cnt desc;

2.4 外连接查询

简介

外连接分为左外连接（left outer join）、右外连接(right outer join)，满外连接(full outer join)。

外连接（Outer Join）是一种 SQL JOIN 操作，它允许从一个表中选择所有的记录，而无论是否在另一个表中有匹配的记录。

如果记录在另一个表中没有匹配，那么结果集中的值将为 NULL。

注意：

oracle里面有full join,可是在mysql对full join支持的不好。我们可以使用union来达到目的。

 格式

左外连接

left outer join  select * from A left outer join B on 条件;

右外连接：

right outer join  select * from A right outer join B on 条件;

满外连接:

full outer join  select * from A full outer join B on 条件;

外连接查询——举例 

#查询哪些部门有员工，哪些部门没有员工

use mydb3;

select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id; 

#查询哪些员工有对应的部门，哪些没有

select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

#使用union关键字实现左外连接和右外连接的并集

select * from dept3 left outer join emp3 on dept3.deptno = emp3.dept_id union select * from dept3 right outer join emp3 on dept3.deptno = emp3.dept_id;

2.5 子查询

简介

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写形式，通俗一点就是包含select嵌套的查询。

特点

子查询可以返回的数据类型一共分为四种 

子查询回显的数据类型

单行单列

返回的是一个具体列的内容，可以理解为一个单值数据；

单行多列

返回一行数据中多个列的内容；

多行单列

返回多行记录之中同一列的内容，相当于给出了一个操作范围；

多行多列

查询返回的结果是一张临时表

 举例

 查询年龄最大的员工信息，显示信息包含员工号、员工名字，员工年龄

select eid,ename,age from emp3 where age = (select max(age) from emp3);

查询年研发部和销售部的员工信息，包含员工号、员工名字

select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name = '研发部' or name = '销售部') ;

查询研发部20岁以下的员工信息,包括员工号、员工名字，部门名字

select eid,age,ename,name from (select * from dept where name = '研发部 ')t1,(select * from emp3 where age <20)t2

子查询关键字

1.ALL关键字格式

select …from …where c > all(查询语句)

#相当于：

select ...from ... where c > result1 and c > result2 and c > result3

特点

ALL关键字特点

ALL: 与子查询返回的所有值比较为true 则返回trueALL可以与=、>、>=、、、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的所有数据。

ALL表示指定列中的值必须要大于子查询集的每一个值，即必须要大于子查询集的最大值；

如果是小于号即小于子查询集的最小值。同理可以推出其它的比较运算符的情况。

示例

查询年龄大于‘1003’部门所有年龄的员工信息

select * from emp3 where age > all(select age from emp3 where dept_id = '1003’);

查询不属于任何一个部门的员工信息

select * from emp3 where dept_id != all(select deptno from dept3);

2.ANY关键字

3.SOME关键字格式——格式

select …from …where c > any(查询语句)

#等价于

select ...from ... where c > result1 or c > result2 or c > result3

特点

4.IN关键字

格式

select …from …where c in(查询语句)

#等价于：

select ...from ... where c = result1 or c = result2 or c = result3

示例

查询研发部和销售部的员工信息，包含员工号、员工名字

select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 
where name = '研发部' or name = '销售部') ;

5.EXISTS关键字

格式

select …from …where exists(查询语句)

特点

EXISTS关键字特点

该子查询如果“有数据结果”(至少返回一行数据)， 则该EXISTS() 的结果为“true”，外层查询执行该子查询如果“没有数据结果”（没有任何数据返回），则该EXISTS()的结果为“false”，外层查询不执行EXISTS后面的子查询不返回任何实际数据，只返回真或假，当返回真时 where条件成立注意，EXISTS关键字，比IN关键字的运算效率高，因此，对于大数据量时，推荐使用EXISTS关键字

示例

#查询公司是否有大于60岁的员工，有则输出

select * from emp3 a where exists(select * from emp3 b where a.age > 60);
 
# 查询有所属部门的员工信息

select * from emp3 a where exists(select * from dept3 b where a.dept_id = b.deptno);

2.6 自连接查询

简介

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用。

注意自关联时表必须给表起别名。

格式

select 字段列表 from 表1 a , 表1 b where 条件;

#或者: 

select 字段列表 from 表1 a [left] join 表1 b on 条件;

示例

创建表,并建立自关联约束

create table t_sanguo(
    
    eid int primary key ,
   
    ename varchar(20),
   
    manager_id int,
 
    foreign key (manager_id) references t_sanguo (eid)   # 添加自关联约束
);

添加数据

insert into t_sanguo values(1,'刘协',NULL);

insert into t_sanguo values(2,'刘备',1);

insert into t_sanguo values(3,'关羽',2);

insert into t_sanguo values(4,'张飞',2);

insert into t_sanguo values(5,'曹操',1);

insert into t_sanguo values(6,'许褚',5);

insert into t_sanguo values(7,'典韦',5);

insert into t_sanguo values(8,'孙权',1);

insert into t_sanguo values(9,'周瑜',8);

insert into t_sanguo values(10,'鲁肃',8);

进行关联查询

#查询每个三国人物及他的上级信息，如: 关羽 刘备
 
select * from t_sanguo a, t_sanguo b where a.manager_id = b.eid;

...