多表查询

之前的SQL语句里的DQL只能进行单表查询，不能多表查询

多表关系

项目开发中，在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，表与表之间的关系大致有以下几种：

1. 一对多（或多对一）
2. 多对多
3. 多对一

• 一对多（多对一）

  在一对多（多对一）的关联关系中，比较典型的就是例如：部门与员工之间的关系

  关系：一个部门对应多个员工，一个员工对应一个部门

  实现：在多的一方建立外键，指向单个的一方的主键

  例如：在员工的一方建立外键，指向部门的主键
  

• 多对多

  在多对多的关联关系中，比较典型的就是例如：学生与课程的关系

  关系：一个学生可以选修多个课程，一门课程也可以供多个学生选择

  实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

  例如：在学生表和课程表之间创建一个中间表（学生课程关系表），这个中间表有两个外键，分别对应学生表的主键和课程表的主键
  

use onesql;
create table student(id int primary key auto_increment comment 'ID',name varchar(10) not null comment '姓名',no varchar(10) comment '学号'
)comment '学生表';create table course(id int primary key auto_increment comment 'ID',name varchar(10) comment '课程'
)comment '课程表';create table stu_cou(id int primary key auto_increment comment 'ID',stu_id int comment '学生id',cou_id int comment '课程号'
)comment '学生课程关系表';insert into course(name)values ('java'),('高数'),('大物'),('c++');
insert into student(name,no)values ('jack',001),('tom',002),
('rose',003),('jams',004);
insert into stu_cou(stu_id, cou_id) values (1,1),(1,2),(2,1),(2,3),
(3,3),(3,4),(4,1),(4,3);alter table stu_cou add constraint stuId foreign key (stu_id)
references student(id);
alter table stu_cou add constraint couId foreign key (cou_id)
references course(id);

datagrip的图形化界面
在中间表打开图

• 一对一

  在一对一的关联关系中，比较典型的就是例如：用户和用户的详细信息

  关系：一对一关系多用于单表的拆分，将一张表的基础字段放在一张表中，其他的详情字段放在另一张表中，以提升操作效率

  实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE–unique）

  例如：下图
  
  
  

create table tb_user(id int primary key auto_increment comment 'ID',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '1:nan 2:nv',phone char(11) comment '手机号'
)comment '基本信息';create table tb_user_edu(id int primary key auto_increment comment 'ID',degree varchar(20) comment '学历',major varchar(50) comment '专业',school1 varchar(50) comment '小学',school2 varchar(50) comment '中学',school3 varchar(50) comment '大学',userid int unique comment '用户id',constraint fk_userid foreign key (userid)references tb_user(id)
)comment '基本信息';-- 下面原本还应该有插入数据，但是太繁琐，所以没写

多表查询概述

指从多张表中查询数据
语法：

-- 直接这样查询就会使表1.表2的数据全部相乘
-- 例如，表1为员工表，有10个员工（10条数据），表2为部门表，有6个部门（6条数据），
-- 那么直接使用下面的方式查询出的结果就会有60条数据，也就是所谓的笛卡尔积
select 字段列表 from 表1，表2...;

笛卡尔积：笛卡尔乘积是指在数学中，两个集合A和B的所有组合情况，

所以，在进行多表查询时需要消除无效的笛卡尔积，实际的消除方法就使用where条件，使多表中关联的外键进行联系。

例如：还是表1为员工表，有10个员工（10条数据），表2为部门表，有6个部门（6条数据），在表1中有一个外键字段dept_id与部门表的主键（id）相关联，在进行多表查询时，就可以在where条件中加上 dept_id与部门表的主键（id）相等这一条件，这样就可以消除多余的笛卡尔积；

select * from 表1，表2 where 表1.dept_id=表2.id；

这样执行后就不会有多余的笛卡尔积

多表查询的分类

• 多表查询主要分为两类：

  连接查询和子查询

• 连接查询
  连接查询又分为内连接，外连接和自连接

  内连接：相当于查询A，B交集部分的数据
  外连接：

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

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

内连接

内连接查询的是两张表交集部分的数据

• 内连接查询语法：

  隐式内连接：

  SELECT 字段列表 FROM 表1，表2 WHERE 条件...;
  select 字段列表 from 表1，表2 where 条件...;
  

  显式内连接：

  SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 连接条件...;
  select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
  

演示：

create table department(id int primary key auto_increment comment 'ID',dept_name varchar(8) comment '部门'
)comment '部门表';
insert into department(dept_name)values ('主管部'),('营销部'),('软件部'),('硬件部'),('财政部');alter table emp add constraint fk_deptTwo_id foreign key (dept_id)references department(id);drop table emp;
create table emp(id int comment '编号',workno varchar(10) comment '工号',name varchar(10) comment '姓名',gender char(1) comment '性别',age tinyint comment '年龄',idCard char(18) comment '身份证号',workAddress varchar(50) comment '工作地址',entryDate date comment '入职日期',dept_id int comment '部门号',constraint fk_dp_id foreign key (dept_id)references department(id)
)comment '员工信息表';
insert into emp values 
(1,'1','张三','男',20,'12345678901234567','北京','2000-01-01',1),
(2,'2','张四','女',25,'12345678901234567','北京南','2000-02-01',3),
(3,'3','李四','女',35,'12345678901234567','北京北','2000-03-01',5),
(4,'4','李1','女',32,'12345678901234567','北京','2000-04-01',2),
(5,'5','李92','男',21,'12345678901234567','北北','2000-05-01',1),
(6,'6','李3','女',22,'12345678901234567','京北','2000-06-01',4),
(7,'7','李4s','男',23,'12345678901234567','北','2000-07-01',4),
(8,'8','李5','女',35,'12345678901234567','北京北','2000-08-01',5),
(9,'9','李61','男',37,'12345678901234567','北京1北','2000-09-01',1),
(10,'10','李67','女',35,'12345678901234567','北2京北','2000-10-01',2),
(11,'11','李7','男',37,'12345678901234567','北京北','2000-11-01',2),
(12,'12','李12','女',25,'12345678901234567','北京是北','2000-09-01',3),
(13,'13','李21','男',15,'12345678901234567','北是京北','2000-03-01',4),
(14,'14','李123','女',25,'12345678901234567','北上述京北','2000-01-01',3),
(15,'15','李32','男',88,'12345678901234567','北京北说','2000-02-03',1),
(16,'16','李19','男',95,null,'北京北2','2000-09-12',2);//隐式内连接
select *from emp,department where department.id=emp.dept_id;
//显式内连接
select *from emp inner join department on department.id=emp.dept_id;

外连接

外连接分为左外连接和右外连接

这里on后面的条件是外键和主键之间的条件，如果是筛选的条件的话，还需要再写在where后面

• 左外连接
  查询表1的所有数据，包括表1和表2交集的数据(这里的交集就是相关联的数据，像是外键那样)

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件...WHERE...;
select 字段列表 from 表1 left [outer] join 表2 on 条件...where...;

• 右外连接
  查询表2的所有数据，包括表1和表2交集的数据(这里的交集就是相关联的数据，像是外键那样)

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件...WHERE...;
select 字段列表 from 表1 right [outer] join 表2 on 条件...where...;

例子：

-- 查询emp表的所有数据，和对应的部门信息（左外连接）
select * from emp left join department d on emp.dept_id = d.id;
-- 查询department表的所有数据，和对应的员工信息（右外连接）
select d.*,e.* from emp e right join department d on e.dept_id = d.id;

自连接

语法：

SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件...;
select 字段列表 from 表A 别名A join 表A 别名B on 条件...;

自连接查询，可以是内连接查，也可以是外连接查
即自连接也可以使用左外或右外的形式

SELECT 字段列表 FROM 表A 别名A LEFT/RIGHT JOIN 表A 别名B ON 条件...;
select 字段列表 from 表A 别名A left/right join 表A 别名B on 条件...;

自连接中表必须起别名

自连接的作用主要是作用于一张表上的关系，查询同一张表上的不同行的信息，例如，一张员工表上有两个字段，第一个字段是id，第二个字段是领导id,使用自连接就可以将领导id与对应的id连接起来

联合查询union，union all

对于联合查询，就是把多次查询的结果合并起来，形成一个新的查询结果集

语法：

SELECT 字段列表 FROM 表A ...
UNION[ALL]
SELECT 字段列表 FROM 表B...;
-- 这段程序会将上面对表A的查询结果和对表B的查询结果合并起来，形成一个新的查询结果集

案例：

-- 1.将workno小于等于10的员工和年龄大于30的员工全部查询出来
select * from emp where workno<=10
union
select *from emp where age>=30;
-- union后不跟all，重复的数据就不会显示，如果加上all，重复的数据就会显示

注意：联合查询时，多张表的列数需要保持一致，所以要求查询的字段列表是一致的。

子查询

概念：SQL语句中嵌套SELECT语句，称为嵌套查询，又称子查询
语法：

SELECT * FROM t1 WHERE column1=(SELECT column1 FROM t2);
select *from t1 where column1 =(select column1 from t2);
-- 括号内部的语句就成为子查询语句，
-- 子查询外部的语句，即=左边的语句，可以是SQL语句的任意形式
-- 例如 INSERT/UPDATE/DELETE/SELECT的任何一个

• 根据子查询的结果不同，可以将子查询分为：
  1.标量子查询（子查询结果为单个值）

  2.列子查询（子查询结果为一列）

  3.行子查询（子查询结果为一行）

  4.表子查询（子查询结果为多行多列）

根据子查询出现的位置，可以将子查询分为：WHERE（where）之后，FROM（from）之后，SELECT（select）之后的子查询

标量子查询

概念：
标量子查询返回的结果是单个值（数字，字符串，日期等），最简单的子查询形式

• 标量子查询常用的操作符：
  = ,<> ,>,>=,<,<=

练习：

-- 1.查询软件部的所有员工信息
-- 拆解成两步，先查询软件部的部门id，再通过部门id查询详细的员工信息
select id from department where dept_name='软件部';-- 3
select * from emp where dept_id=3;
-- 合并成一步就是
-- 这里子查询的结果是3，只有一条记录，所以是标量子查询
select *from emp where dept_id=(select id from department where dept_name='软件部');

列子查询

列子查询返回的结果是一列（可以是多行）；

• 列子查询常用的操作符
  IN(in),NOT IN(not in),ANY(any),SOME(some),ALL(all)

• 操作符的作用：
  
  案例：

-- 1.查询营销部和硬件部的所有员工信息
-- 还是分成两步，第一步就是查询'硬件部','营销部'对应的部门id，通过部门id去寻找相应的员工
-- 由于这里子查询的结果是一列数据，所以是列子查询
select *from emp where dept_id in 
(select id from department where dept_name in('硬件部','营销部'));-- 查询大于所有财政部年龄的员工的信息
select *from emp where age>all(select age from emp where dept_id=(select id from department where dept_name='财政部'));select *from emp where age>any(select age from emp where dept_id=(select id from department where dept_name='主管部'));

行子查询

行子查询返回的结果是一行（可以是多列）

语法：

select 字段列表 from 表名 where（条件字段1，条件字段2，。。。）=（子查询）

• 行子查询常用的操作符：
  =,<,>,IN(in),NOT IN(not in)

-- 查询与张三年龄和住址相同的员工信息
select *from emp where 
(age,workAddress)=(select age,workAddress from emp where name='张三');

表子查询

表子查询返回的结果是多行多列

• 常用的操作符
  IN(in)

案例：

-- 查询与张三和李四年龄和住址相同的员工信息
select *from emp where 
(age,workAddress)
in
(select age,workAddress from emp where name ='张三'or name='李四');

综合练习

-- 1.
select name,age,d.dept_name from emp e,department d where e.dept_id=d.id;
-- 2.
select name,age,d.dept_name from emp e,department d where  (e.dept_id=d.id)and e.age<30;
-- 3.(内连接)
select distinct d.id,d.dept_name from emp e,department d where e.dept_id =d.id;
-- 4.
select e.*,d.dept_name from emp e left join department d on e.dept_id=d.id where age>30;
-- 5.
select e.name,s.grade from salgrade s,emp e where e.gongzi>=s.low and e.gongzi<s.highest;
-- 6.
select e.*,s.grade from salgrade s,emp ewhere e.dept_id=(select id from department where dept_name='研发部')and(e.gongzi>=s.low and e.gongzi<s.highest);
-- 7.
select avg(e.gongzi) from salgrade s,emp ewhere e.dept_id=(select id from department where dept_name='研发部')and(e.gongzi>=s.low and e.gongzi<s.highest);
-- 8.
select *from emp where gongzi>(select gongzi from emp where name='李7');
-- 9.
select *from emp where gongzi>(select avg(gongzi) from emp);
-- 10.
select *from emp e2 where e2.gongzi<(select avg(e1.gongzi)from emp e1 where e2.dept_id=e1.dept_id);
-- 11.
select d.id,d.dept_name,(select count(*)from emp e where d.id=e.dept_id) '数量' from department d;

小结

事务

事务简介

• 事务：

  是一组操作的集合，它是一个不可分割的工作单位，事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败

  例如：银行的转账，中间不能有任何异常出现，一旦出现异常就必须要赊销之前的所有操作
  
  在MySQL中的事务是自动提交的，也就是说，当执行一条sql语句，MySQL会立即隐式的提交事务，不用自已手动提交

事务的操作

修改事务的提交方式

• 查看/设置事务的提交方式

  -- 查看事务的自动提交方式。如果为1，就是自动提交，如果为0，就是手动提	交
  SELECT @@autocommit;
  -- 可以通过set将系统的自动提交方式改为手动
  SET @@autocommit=0;-- select @@autocommit;
  set @@autocommit=0;
  

• 提交事务

  COMMIT;commit;
  

• 回滚事务（将事务返回到初始状态）

  ROLLBACK;rollback;
  

  如果设置为手动提交，当sql语句执行完成后，数据库里的数据不会改变，必须手动执行提交指令（commit）后才会改变；

  当sql语句出现异常时，就不用执行提交事务的指令了，而是执行回滚指令（rollback），这样数据就不会丢失了

  案例：

  create table account(
  id int auto_increment primary key comment 'ID',
  name varchar(10) comment '姓名',
  money int comment '储蓄'
  )comment '账户表';
  insert into account (name, money) values ('zhangsan',1000),('lisi',2000);
  -- 恢复数据
  update account set money=2000 where name='zhangsan' or name='lisi';-- 转账
  -- 1.查询zhangsan余额
  -- 2.将zhangsan余额-1000
  -- 3.给lisi余额+1000set @@autocommit=0;-- 现在下面的三条sql语句分别是三条单独的事务
  select money from account where name='zhangsan';
  update account set money  =money-1000 where name='zhangsan';
  update account set money  =money+1000 where name='lisi';
  commit ;
  rollback ;
  

不修改事务的提交方式

• 开启事务（在想要将一段sql语句缩成一个事务时，将开启事务放在这段sql语句的上面）

  START TRANSACTION 或 BEGIN;
  start transaction 或 begin;
  

• 提交事务

  COMMIT;commit;
  

• 回滚事务（将事务返回到初始状态）

  ROLLBACK;rollback;
  

  start transaction ;-- 开启事务,将开启事务与下面三条程序一起运行
  select money from account where name='zhangsan';
  update account set money  =money-1000 where name='zhangsan';
  update account set money  =money+1000 where name='lisi';
  -- 当上面开启事务后，如果不提交事务，数据库是不会有任何变动的
  commit ;
  rollback ;
  

四大特性ACID

• 事务有四大特性：

  1.原子性（Atomicity）

  2.一致性（Consistency）

  3.隔离性（Isolation）

  4.持久性（Durability）

并发事务问题

并发事务问题指的是：A事务和B事务同时操作一个数据库或一张表的时候，所引发的一些问题

• 常见的并发事务问题
  
  脏读：一个事务读到另一个事务还没有提交的数据
  例如：在事务隔离级别不够的情况下，现在有两个并发的事务同时操作同一张表，B事务修改表的数据，在B事务还没有提交的时候，A事务又读取这个表，此时A事务就会读到被修改后的数据，就称之为脏读

  不可重复读：一个事务先后读取同一条记录，但两次读取的数据不同，称之为不可重复读
  例如：在Read committed的事务隔离级别下，有两个并发的事务同时操作同一张表，A事务先读取数据1，此时数据1为1000，然后B事务修改数据1为0，在B事务提交后，A事务再次进行读取数据1，此时读取出来的数据1是0，事务A读取两次，两次的数据不同，这就是不可重复读

  幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”。
  例如：在Repeatable Read的事务隔离级别下，有两个并发的事务同时操作同一张表，事务A先读取一个表中不存在的值（比如说id=3的一个数据），然后事务B执行插入SQL，将id=3的数据插入表中，B事务提交后，A事务再将插入id=3数据插入表中就会报错，显示数据在表中已经存在，此时事务A再进行读取id=3的数据，还是显示数据不存在，这就是幻读

事务的隔离级别

事务的隔离级别就是来解决事务的并发问题的

数据库中的事务隔离级别

四种事务隔离级别：（从上到下事务的隔离级别越来越高，性能越来越差）
1.Read uncommitted：在这个隔离级别下，上面三个并发的事务问题都有可能会出现（性能高，数据安全性差）

2.Read committed（oracle数据库的默认事务隔离级别）：解决了脏读问题，但是不可重复读和幻读还是会出现

3.Repeatable Read（MySqL的默认的事务隔离级别）：幻读问题会出现

4.Serializable：解决了所有的并发事务问题（性能低，数据安全性强）

• 事务隔离级别的语法：

  -- 查看事务隔离级别
  SELECT @@TRANSACTION_ISOLATION;select @@transaction_isolation;-- 设置事务隔离级别
  SET [SESSION | GLOBAL]TRANSACTION ISOLATION LEVEL 
  {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}set [session(只对当前客户端窗口有效) | global（对所有的客户端窗口有效）]transaction isolation level
  {read uncommitted | read committed | repeatable read | serializable}
  

练习：

-- 查看事务的隔离级别
select @@transaction_isolation;
-- 设置事务的隔离级别
set session transaction isolation level  read committed ;
set session transaction isolation level  repeatable read ;

注意:事务的隔离级别越高，数据越安全，但是数据库的性能越低

小结