3.18学习笔记---MySQL多表查询，索引，事务

多表查询

当我们调用这句sql语句查询员工表（17行）和部门表（5行）中的数据：

select * from  tb_emp , tb_dept;

会发现查询结果很多，即17*5=85条记录。

插入知识点：

这种出现85条记录的现象叫笛卡尔积。

但我们不想要这么多数据，就需要用条件筛除一些结果：

 select * from tb_emp , tb_dept where tb_emp.dept_id = tb_dept.id ;--隐式内连接

（只有id号相同才是正确的结果）

多表查询分类：

1，连续查询---内连接

查A、B交集部分数据。

隐式内连接：select  字段列表   from   表1 , 表2   where  条件 ... ;

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

select tb_emp.name , tb_dept.name

from tb_emp inner join tb_dept

on tb_emp.dept_id = tb_dept.id;

但如果给表起了别名，比如：tb_emp->emp;tb_dept->dept,上面的语句就变成：

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

2，连续查询---外连接

左外连接：以left join关键字左边的表为主表，查询主表中所有数据，以及和主表匹配的右边表中的数据。没有匹配到数据记为null。

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

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

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

3，子查询

介绍：SQL语句中嵌套select语句（也可以是insert / update / delete的任何一个），称为嵌套查询，又称子查询。

子查询可以书写的位置：where之后；from之后；select之后

子查询分为：

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

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

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

 常用的操作符：IN（在指定的集合范围之内，多选一）；NOT IN（不在指定的集合范围之内）

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

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

（4）表子查询：返回的结果是多行多列，常作为临时表；

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

分两步执行：

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

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 ;

事务

目的：为了保证数据的一致性。即保证在一个事务中多次操作数据库表中数据时，要么全都成功,要么全都失败。

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

事务的操作：

1，自动提交事务（默认）：执行一条sql语句提交一次事务。

2，手动提交事务：先开启，再提交。

 手动提交事务使用步骤：
第1种情况：开启事务  =>  执行SQL语句   =>  成功  =>  提交事务

start transaction ;---sql语句---成功---commit ;
第2种情况：开启事务  =>  执行SQL语句   =>  失败  =>  回滚事务

start transaction ;---sql语句---失败---rollback ;

事务的四大特性ACID（面试题）

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

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

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

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

索引

介绍：是帮助数据库高效获取数据的数据结构

创建：CREATE INDEX 索引名 ON 表名 (列名[(length)]);

length是可选项，如果忽略 length 的值，则使用整个列的值作为索引。

按照索引查询：select * from 表名 where 索引名 = '条件';

删除：drop  index  索引名  on  表名;

优点：

1. 提高数据查询的效率，降低数据库的IO成本。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低CPU消耗。

缺点：

1. 索引会占用存储空间。
2. 索引大大提高了查询效率，同时却也降低了insert、update、delete的效率。

索引结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。

平常所说的索引，默认的 B+Tree（平衡二叉树） 结构组织的索引。

思考：采用二叉搜索树或者是红黑树来作为索引的结构有什么问题？
    最大的问题就是在数据量大的情况下，树的层级比较深，会影响检索速度。因为不管是二叉搜索数还是红黑数，一个节点下面只能有两个子节点。此时在数据量大的情况下，就会造成数的高度比较高，树的高度一旦高了，检索速度就会降低。

B+Tree的优点：

B+Tree可以减少树的高度，增加树的宽度。千万条数据，B+Tree可以控制在小于等于3的高度

（例如根节点中可以存储1170个元素，每个子节点也会存储1170个元素，第三层由key+数据组成，假设key+数据总大小是1KB，而每个节点一共能存储16KB，所以一个第三层一个节点大概可以存储16个元素(即16条记录)，那么总计能从查到1170*1170*16个元素），

非叶子节点都是由key+指针域组成的，一个key占8字节，一个指针占6字节，而一个节点总共容量是16KB，那么可以计算出一个节点可以存储的元素个数：16*1024字节 / (8+6)=1170个元素。

所有的数据都存储在叶子节点上，并且底层已经实现了按照索引进行排序，还可以支持范围查询，叶子节点是一个双向链表，支持从小到大或者从大到小查找。

注意点：

主键字段，在建表时，会自动创建主键索引
添加唯一约束时，数据库实际上会添加唯一索引

以上来源于黑马程序员