欢迎各位彦祖与热巴畅游本人专栏与博客

你的三连是我最大的动力

以下图片仅代表专栏特色 [点击箭头指向的专栏名即可闪现]

专栏跑道一

➡️网络空间安全——全栈前沿技术持续深入学习

专栏跑道二

➡️ 24 Network Security -LJS

专栏跑道三

➡️ MYSQL REDIS Advance operation

专栏跑道四

➡️HCIP；H3C-SE;CCIP——LJS[华为、华三、思科高级网络]

专栏跑道五

➡️RHCE-LJS[Linux高端骚操作实战篇]

专栏跑道六

➡️数据结构与算法[考研+实际工作应用+C程序设计]

专栏跑道七

➡️RHCSA-LJS[Linux初级及进阶骚技能]

上节回顾

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 交叉连接查询

• 交叉连接查询

• 格式
  

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关键字特点

• 示例
  
• 查询年龄大于‘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关键字特点

• 示例
  

#查询公司是否有大于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;