学习笔记Mysql（3）

1. 多表查询

是什么？

多表查询：查询时从多张表中获取所需数据

单表查询的SQL语句：select 字段列表 from 表名;

那么要执行多表查询，只需要使用逗号分隔多张表即可，如： select 字段列表 from 表1, 表2

分类

多表查询可以分为：

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

2. 外连接

左外连接：查询左表所有数据(包括两张表交集部分数据)

右外连接：查询右表所有数据(包括两张表交集部分数据)

3. 子查询

1.1内连接

内连接查询：查询两表或多表中交集部分数据。

内连接从语法上可以分为： 隐式内连接  显式内连接

隐式内连接语法：

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

显式内连接语法：

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

案例：查询员工的姓名及所属的部门名称

隐式内连接实现

select tb_emp.name , tb_dept.name -- 分别查询两张表中的数据

from tb_emp , tb_dept -- 关联两张表

where tb_emp.dept_id = tb_dept.id; -- 消除笛卡尔积

显式内连接实现

select tb_emp.name , tb_dept.name
from tb_emp inner join tb_dept
on tb_emp.dept_id = tb_dept.id;

多表查询时给表起别名：

方式一：tableA as 别名1 , tableB as 别名2 ;

方式二：tableA 别名1 , tableB 别名2 ;

注意事项: 为表起了别名，就不能再使用表名来指定对应的字段，此时只能够使用别名来指定字段。

1.2外连接

左外连接语法结构：

select 字段列表 from 表1 left [ outer ] join 表2 on 连接条件 ... ;

左外连接相当于查询表1(左表)的所有数据，当然也包含表1和表2交集部分的数据。

右外连接语法结构：

select 字段列表 from 表1 right [ outer ] join 表2 on 连接条件 ... ;

右外连接相当于查询表2(右表)的所有数据，当然也包含表1和表2交集部分的数据。

案例：查询员工表中所有员工的姓名, 和对应的部门名称

-- 左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表
匹配的右边表中的数据
select emp.name , dept.name
from tb_emp AS emp left join tb_dept AS dept
on emp.dept_id = dept.id;

案例：查询部门表中所有部门的名称, 和对应的员工名称

-- 右外连接
select dept.name , emp.name
from tb_emp AS emp right join tb_dept AS dept
on emp.dept_id = dept.id;

1.3子查询

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

语法：SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2 ... );

根据子查询结果的不同分为：

1. 标量子查询（子查询结果为单个值[一行一列]）

2. 列子查询（子查询结果为一列，但可以是多行）

3. 行子查询（子查询结果为一行，但可以是多列）

4. 表子查询（子查询结果为多行多列[相当于子查询结果是一张表]）

子查询可以书写的位置：

1. where之后 

2. from之后

3. select之后

1. 标量子查询

子查询返回的结果是单个值(数字、字符串、日期等)，最简单的形式，这种子查询称为标量子查询。 常用的操作符： = <> > >= < <=

案例1：查询"教研部"的所有员工信息

可以将需求分解为两步：

1. 查询 "教研部" 部门ID

2. 根据 "教研部" 部门ID，查询员工信息

-- 1.查询"教研部"部门ID
select id from tb_dept where name = '教研部'; #查询结果：2
-- 2.根据"教研部"部门ID, 查询员工信息
select * from tb_emp where dept_id = 2;
-- 合并出上两条SQL语句
select * from tb_emp where dept_id = (select id from tb_dept where
name = '教研部');

案例2：查询在 "方东白" 入职之后的员工信息

1. 查询 方东白 的入职日期

2. 查询 指定入职日期之后入职的员工信息

-- 1.查询"方东白"的入职日期
select entrydate from tb_emp where name = '方东白'; #查询结果：
2012-11-01
-- 2.查询指定入职日期之后入职的员工信息
select * from tb_emp where entrydate > '2012-11-01';
-- 合并以上两条SQL语句
select * from tb_emp where entrydate > (select entrydate from tb_emp
where name = '方东白');

2. 列子查询

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

案例：查询"教研部"和"咨询部"的所有员工信息

1. 查询 "销售部" 和 "市场部" 的部门ID

2. 根据部门ID, 查询员工信息

-- 1.查询"销售部"和"市场部"的部门ID
select id from tb_dept where name = '教研部' or name = '咨询部'; #查
询结果：3,2
-- 2.根据部门ID, 查询员工信息
select * from tb_emp where dept_id in (3,2);
-- 合并以上两条SQL语句
select * from tb_emp where dept_id in (select id from tb_dept where
name = '教研部' or name = '咨询部');

3. 行子查询

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

常用的操作符：= 、<> 、IN 、NOT IN

案例：查询与"小王"的入职日期及职位都相同的员工信息

可以拆解为两步进行：

1. 查询 "小王" 的入职日期 及 职位

2. 查询与"小王""的入职日期及职位相同的员工信息

-- 查询"小王"的入职日期 及 职位
select entrydate , job from tb_emp where name = '小王'; #查询结果：
2007-01-01 , 2
-- 查询与"小王"的入职日期及职位相同的员工信息
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
-- 合并以上两条SQL语句
select * from tb_emp where (entrydate,job) = (select entrydate , job
from tb_emp where name = '小王');

4. 表子查询

子查询返回的结果是多行多列，常作为临时表，这种子查询称为表子查询。

案例：查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息

分解为两步执行：

1. 查询入职日期是 "2006-01-01" 之后的员工信息

2. 基于查询到的员工信息，在查询对应的部门信息

select * from emp where entrydate > '2006-01-01';
select e.*, d.* from (select * from emp where entrydate > '2006-01-
01') e left join dept d on e.dept_id = d.id ;

2. 事务

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

事务作用：保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

场景：学工部整个部门解散了，该部门及部门下的员工都需要删除了。

事务操作有关的SQL语句：

手动提交事务使用步骤：

第1种情况：开启事务 => 执行SQL语句 => 成功 => 提交事务

第2种情况：开启事务 => 执行SQL语句 => 失败 => 回滚事务

使用事务控制删除部门和删除该部门下的员工的操作：

-- 开启事务
start transaction ;
-- 删除学工部
delete from tb_dept where id = 1;
-- 删除学工部的员工
delete from tb_emp where dept_id = 1;

上述的这组SQL语句，如果如果执行成功，则提交事务

-- 提交事务 (成功时执行)
commit ;

上述的这组SQL语句，如果如果执行失败，则回滚事务

-- 回滚事务 (出错时执行)
rollback ;

事务的特性

原子性（Atomicity） ：原子性是指事务包装的一组sql是一个不可分割的工作单元，事务中的 操作要么全部成功，要么全部失败。

一致性（Consistency）：一个事务完成之后数据都必须处于一致性状态。 如果事务成功的完成，那么数据库的所有变化将生效。 如果事务执行出现错误，那么数据库的所有变化将会被回滚(撤销)，返回到原始状态。

隔离性（Isolation）：多个用户并发的访问数据库时，一个用户的事务不能被其他用户的事务 干扰，多个并发的事务之间要相互隔离。 一个事务的成功或者失败对于其他的事务是没有影响。

持久性（Durability）：一个事务一旦被提交或回滚，它对数据库的改变将是永久性的，哪怕数 据库发生异常，重启之后数据亦然存在。

3. 索引

索引(index)：是帮助数据库高效获取数据的数据结构 ，就是使用索引可以提高查询的效率。

语法

创建索引

create [ unique ] index 索引名 on 表名 (字段名,... ) ;

案例：为tb_emp表的name字段建立一个索引

create index idx_emp_name on tb_emp(name);

查看索引

show index from 表名;

案例：查询 tb_emp 表的索引信息

show index from tb_emp;

删除索引

drop index 索引名 on 表名;

案例：删除 tb_emp 表中name字段的索引

 drop index idx_emp_name on tb_emp;

注意事项：

主键字段，在建表时，会自动创建主键索引

添加唯一约束时，数据库实际上会添加唯一索引