【MySQL学习|黑马笔记|Day3】多表查询（多表关系、内连接、外连接、自连接、联合查询、子查询），事务（简介、操作、四大体系、并发事务问题、事务隔离级别）

今天看的是MySQL的36 ~ 57集，笔记包含多表查询和事务相关知识

【DAY3】

一.多表查询

1）多表关系

进行数据库表结构设计时会根据业务需求以及业务模块之间的关系分为

• 一对多（多对一）

  • 案例：部门与员工的关系

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

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

• 多对多

  • 案例：学生与课程的关系

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

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

    

    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,'zs','2000100101'),(null,'ls','2000100101'),(null,'ww','2000100103'),(null,'zl','2000100104');create table course(id int auto_increment primary key comment'主键id',name varchar(10) comment'课程名称'
    )comment'课程表';
    insert into course value(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);
    

    （可视化表的关系：右键中间表–>Diagrams–>show Daigrams）

• 一对一

  • 案例：用户与用户的关系

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

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

2）多表查询

• 概述：从多张表中查询数据

• 笛卡尔积：笛卡尔积是指在数学中，两个集合A和B的所有组合情况（在多表查询时需要消除无用的笛卡尔积）

  select * from emp,dept where emp.dept_id = dept.id;

• 分类

  • 连接查询

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

    外连接：左外连接：查询作弊哦啊所有数据以及两张表交集部分数据

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

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

  • 子查询

2.1）内连接

• 隐式内连接

  select 字段列表 from 表1,表2 where 条件...;

• 显式内连接

  select 字段列表 from 表1(inner)join 表2 on 连接条件...;

  (inner可省略)

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

eg

-- 1.查询每一个员工的姓名以及关联部门的名称（隐式内连接）
select emp.name,dept.name from emp,dept where emp.dept_id = dept.id;-- 2.查询每一个员工的姓名以及关联部门的名称（显式内连接）
select emp.name,dept.name from emp inner join dept on emp.dept_id = dept.id;

二者区别在于显式内连接提供了更好的可读性和维护性

2.2）外连接

• 左外连接

  select 字段列表 from 表1 left(outer)join 表2 on 条件...;

  相当于查询左表所有数据包含两表交集部分数据

• 右外连接

  select 字段列表 from 表1 right(outer)join 表2 on 条件...;

  相当于查询右表所有数据包含两表交集部分数据

eg

-- 1.查询emp表所有数据和对应部门信息
select emp.*,dept.name from emp left outer join dept on emp.dept_id = dept.id;-- 2.查询dept表所有数据和对应员工信息
select dept.*,emp.* from emp right outer join dept on emp.dept_id = dept.id;

2.3）自连接

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

自连接查询，可以是内连接查询也可以是外连接查询

eg

-- 1.查询员工及其所属领导名字（内连接）
select a.name,b.name from emp a,emp b where a.managerid = b.id;-- 2.查询所有员工emp及其领导的名字emp 如果没有领导也要查询出来（外连接）
select a.name,b.name from emp a left join emp b on a.managerid = b.id;

2.4）联合查询-union，union all

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

select 字段列表 from 表A...
union[all] -- 不加all就去重
select 字段列表 from 表B...;

对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

eg

-- 1.将薪资低于5000的员工和年龄大于50的员工全部查询出来
select * from emp where salary < 5000
union all -- 没去重
select * from emp where age < 50;

2.5）子查询

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

  select * from t1 where column1 =(select column1 from t2);

  子查询外部的语句可以是insert/update/delete/select的任何一个

• 根据子查询结果不同分为

  • 标量子查询（子查询结果为单个值）
  • 列子查询（子查询结果为一列）
  • 行子查询（子查询结果）
  • 表子查询（子查询结果为多行多列）

• 根据子查询位置，分为：where之后、from之后、select之后

2.5.1）标量子查询

子查询返回的结果是单个值（数字、字符串、日期等），最简单的形式，这种子查询成为标量子查询

常用操作符：= <> > >= < <=

eg

-- 1.查询销售部的所有员工信息
-- a.查询销售部的ID
select id from dept where name = '销售部';
-- b.根据销售部部门ID，查询员工信息
select * from emp where dept_id = (select id from dept where name = '销售部');

-- 2.查询在zs入职之后的员工信息
-- a.查询zs的入职日期
select entrydate from emp where name = 'zs';
-- b.查询指定入职日期之后的员工信息
select * from emp where entrydate > (select entrydate from emp where name = 'zs');

2.5.2）列子查询

子查询返回的结果是一列（可以是多行），这种子查询称为列子查询

常用的操作符：in、not in、any、some、all

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

eg

-- 1.查询销售部和市场部的所有员工信息
-- a.查询销售部和市场部的部门ID
select id from dept where name = '销售部' or name = '市场部';
-- b.根据部门ID查询员工信息
select * from emp where dept_id in(select id from dept where name = '销售部' or name = '市场部');-- 2.查询比财务所有人工资都高的员工信息
-- a.查询所有财务人员工作
select id from dept where name = '财务部';
select salary from emp where dept_id = (select id from dept where name = '财务部');
-- b.比财务部所有人工资都高的人员信息
select * from emp where salary > all(select salary from emp where dept_id = (select id from dept where name = '财务部'));-- 3.查询比开发部任意一人工资高的员工信息
-- a.查询所有开发人员工作
select id from dept where name = '开发部';
select salary from emp where dept_id = (select id from dept where name = '开发部');
-- b.比开发部任意一人工资高的人员信息
select * from emp where salary > any(select salary from emp where dept_id = (select id from dept where name = '开发部'));

2.5.3）行子查询

子查询返回的结果是一行（可以是多列），这种子查询称为行子查询

常用的操作符：=、<>、in、not in

eg

-- 1.查询与zs的薪资及直属领导相同的员工信息
-- a.查询zs的薪资和直属领导
select salary,managerid from emp where name = 'zs';
-- b.查询与zs薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = 'zs');

2.5.4）表子查询

子查询返回的结果是多行多列），这种子查询称为表子查询

常用的操作符：in

eg

-- 1.查询与zs，ls的职位和薪资相同的员工信息
-- a.查询zs，ls的职位和薪资
select job,salary from emp where name = 'zs' or name = 'ls';
-- b.查询与zs，ls的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name = 'zs' or name = 'ls');-- 2.查询入职日期是2006-01-01之后的员工信息以及部门信息
-- a.入职信息是2006-01-01的员工信息
select * from emp where entrydate > '2006-01-01';
-- b.查询这部分员工的部门信息
select e.*,d.* from (select * from emp where entrydate > '2006-01-01') e left join dept d on e.dept_id = d.id;

二.事务

1）简介

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

默认MySQL的事务是自动提交的，当执行一条DML语句，MySQL会立即隐式的提交事务

2）操作演示

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

  select @@autocommit;
  set @@autocommit = 0;
  

• 提交事务

  commit;
  

• 回滚事务

  rollback;
  

法一：设置为手动提交—>执行完事务后若成功提交事务—>若失败回滚操作

select @@autocommit;-- 默认值为1 自动提交set @@autocommit = 0;-- 设置为手动提交-- 转账操作
-- 1.查询zs账户余额
select * from account where name = 'zs';-- 2.将zs余额-1000
update account set money = money - 1000 where name = 'zs';程序执行报错...-- 3.将ls账户余额+1000
update account set money = money + 1000 where name = 'ls';-- 提交事务
commit;-- 回滚事务
rollback;

---

• 开启事务

  start transaction 或 begin;
  

• 提交事务

  commit;
  

• 回滚事务

  rollback;
  

法二：不修改提交方式

先开启事务—>再执行事务—>若成功再提交事务—>否则回滚事务

-- 转账操作
-- 1.查询zs账户余额
-- 开启事务
start transaction;select * from account where name = 'zs';-- 2.将zs余额-1000
update account set money = money - 1000 where name = 'zs';程序执行报错...-- 3.将ls账户余额+1000
update account set money = money + 1000 where name = 'ls';-- 提交事务
commit;-- 回滚事务
rollback;

3）事务四大特性（ACID）

• 原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败
• 一致性：事务完成时，必须使所有的数据都保持一致
• 隔离性：数据库系统提供的隔离机制保证事务再不受外部并发操作影响的独立环境下运行
• 持久性：事务一旦提交或回滚，它对数据库的改变就是永久的

4）并发事务问题

问题	描述
脏读	一个事务读到另外一个事务还没有提交的数据
不可重复读	一个事务先后读取同一条记录，但两次读取的数据不同
幻读	一个事务安装条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了’幻影‘

5）事务隔离级别

是否会发生并发事务问题

隔离级别	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read（默认）	×	×	√
serializable	×	×	×

-- 查看事务隔离级别6
select @@transaction_isolation;-- 设置事务隔离级别
set [session|global] transaction isolation level {read uncommitted|read committed|repeatable read|serializable};

（笔记内容主要基于黑马程序员的课程讲解，旨在加深理解和便于日后复习）

希望这篇笔记能对大家的学习有所帮助，有啥不对的地方欢迎大佬们在评论区