MySQL数据库---Day 03

一、连接查询

        交叉连接（cross join ） : 不带 on 子句，返回连接表中所有数据行的笛卡尔积。

        内连接（inner join ） ：返回连接表中符合连接条件及查询条件的数据行。

        外连接：分为左外连接（ left outer join ）、右外连接（ right outer join ），书写时可以省略outer。与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。

-- 笛卡儿积
/*{'张三','李四','王五'}
{1,2,3,4,5}
{'张三',1}{'张三',2}{'张三',3}{'张三',4}{'张三',5}
{'李四',1}{'李四',2}{'李四',3}{'李四',4}{'李四',5}
{'王五',1}{'王五',2}{'王五',3}{'王五',4}{'王五',5}*/
-- 交叉连接 cross join
select s.name,t.t_name from student s cross join teacher t
-- 内连接 inner join
select * from student s inner join teacher t on s.tno = t.tno;
-- 外连接
-- 左外 left outer join
select * from student s left join teacher t on s.tno = t.tno;
-- 右外连接 right outer join
select * from student s right join teacher t on s.tno = t.tno;
select * from teacher t right join student s on s.tno = t.tno;

二、事务

       （一）事务 transaction ：指的是逻辑上的一组操作，组成这组操作的各个逻辑单元，要么都成功，要么都失败

       （二）事务特点：

       1. 原子性（Atomicity ）：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况

        2.一致性（Consistency ）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举一个例子，李二给王五转账50 元，其事务就是让李二账户上减去 50 元，王五账户上加上 50 元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50 元转账。而对于李二少了50 元，王五还没加上 50 元这个中间状态是不可见的。

        3.隔离性（Isolation ）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

        4.持久性（Durability ）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响。

三、数据安全性问题：

        脏读

        脏读指的是读到了其他事务未提交的数据，未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。读到了不一定最终存在的数据，这就是脏读。

        不可重复读

        不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了。通常针对数据更新（UPDATE ）操作。

        幻读

        幻读是针对数据插入（INSERT ）操作来说的。假设事务 A 对某些行的内容作了更改，但是还未提交，此时事务B 插入了与事务 A 更改前的记录相同的记录行，并且在事务 A 提交之前先提交了，而这时，在事务A 中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务 B 刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。

        解决这些数据安全性问题

        SQL 标准定义了四种隔离级别， MySQL 全都支持。这四种隔离级别分别是：

        读未提交（READ UNCOMMITTED ）

        读提交 （READ COMMITTED ）

        可重复读 （REPEATABLE READ ）

        串行化 （SERIALIZABLE ）

        从上往下，隔离强度逐渐增强，性能逐渐变差。采用哪种隔离级别要根据系统需求权衡决定，其中，可重复读是 MySQL 的默认级别。

        事务隔离其实就是为了解决上面提到的脏读、不可重复读、幻读这几个问题，下面展示了 4 种隔离级别对这三个问题的解决程度。

隔离级别	脏读	不可重复读	幻读
读未提交	可能	看年	可能
读提交	不可能	可能	可能
可重复读（mysql默认）	不可能	不可能	可能
串行化	不可能	不可能	不可能

-- 事务的流程
begin;
sql语句;
commit;||rollback;
-- 查看当前的事务隔离级别
select @@transaction_isolation;
select @@tx_isolation;
-- 修改事务隔离级别
set 作用域 transaction isolation level 事务隔离级别
作用域：
global 全局
session 当前会话

 

四、索引

        索引（ index|key ）是帮助 MySql 高效获取数据的数据结构。

        1、索引的优势

     （1）提高查询效率（降低 IO 使用率）

     （2）降低 CPU 使用率

        2、索引的弊端

     （1）索引本身很大，可以存放在内存或硬盘上，通常存储在硬盘上。

     （2）索引不是所有情况都使用，比如①少量数据②频繁变化的字段③很少使用的字段

     （3）索引会降低增删改的效率

        索引的分类（以应用层次进行分类）

        1.普通索引

        2.唯一索引

        3.主键索引

        4.联合索引

        5.全文索引（了解）

-- 普通索引
-- 创建
alter table 表名 add index 索引名(所作用的字段)
-- 查看查询语句所用到的索引
explain select 语句;
-- 删除
alter table 表名 drop index 索引名;
-- 查看当前表的索引
show index from 表名

-- 唯一索引
alter table 表名 add unique index 索引名(索引所作用的字段)
-- 注意：如果值在表中不存在，则不会用到唯一索引和主键索引

-- 联合索引
alter table 表名 add index 索引名(所作用的字段1，所作用的字段2)
-- 最左原则（给两个字段添加联合索引，如左边值不存在，还是可以查询到索引；相反右边查不到）

-- 全文索引（了解）
alter table 表名 add fulltext index 索引名(所用的字段)

        索引的使用原则

        1.对于频繁增删改的字段，不建议使用索引

        2.对数据量较少的，不建议使用索引

        3.对于满足唯一索引条件的，不使用普通索引