day~~23数据库中的事务、子查询、表关联、索引的使用

一、事务transaction

1.事务的四个条件ACID

1.原子性(Atomicity):

一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间的某个环节.事务在执行期间发生错误会直接回滚(rollback)到事务开始前的状态,就像从来没有执行过一样.

2.一致性(Consistency):

在事务开始之前和事务结束以后,数据库的完整性没有被破坏,表示写入的资料完全符合预设的规则.

3.隔离性(Isolation):

数据库允许多个并发事务同时对其数据进行读取或修改的能力,隔离可以防止事务并发所造成的数据不一致问题.

4.持久性(Durability):

事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失.

2.特点

1.MySql支持事务,非关系型数据库不支持事务如:rides、HBase等

2.事务可以用来维护数据的完整性,保证成批SQL数据可以全部执行,要么全部不执行

3.事务用来管理insert、update、delete语句,因为这些操作可能会破坏数据,select查询时不会破坏的

4.默认MySql数据库的事务是开启,执行SQL语句后会自动提交(commit)

3.测试

1.提交(commit)

2.回滚(rollback)

3.保存点(savepoint)

#先设置手动提交，默认是自动提交
set @@autocommit = 0;
#开启事务
begin;
#设置检查点
savepoint chen;
update emp set sal = sal-100 where empno = 100;
update emp set sal = sal+100 where empno = 200;
#提交事务就结束了
#commit;
rollback;#回滚
rollback to chen;#错误时才使用,回滚到检查点

set @@autocommit = 0;
begin;
savepoint chen;
delete from dept;
#commit; 提交就删了
rollback to chen;

二、子查询(subquery)

1.概念:

子查询是嵌入在其它select语句中的select语句,也叫嵌套语句.子查询效率低,数据少时不影响,多时尽量用别的方法代替

2.单行子查询

#列出tony所在部门的所有人员
select deptno from emp where ename='tony';
select * from emp where deptno = (select deptno from emp where ename='tony');

deptno = 使用等号，后面的查询结果只能为一个值

3.多行子查询(用in)

select * from emp where job in ('经理','员工');
select * from emp where job in (select distinct job from emp);

三、表关联关系association

1.表的关系分为四种：

一对一,一对多,多对一,多对多

2.多表联查join

1.概念

笛卡尔积(Cartesianproduct)，又称直积。一般笛卡尔积没有实际的业务意义，但作为概念必须了解，多表查询都是先生成笛卡尔积，再进行数据的筛选过滤。

2.内连接(inner join)

#查看员工表和部门表所对应的dname,ename,sal,job
select dname,ename,sal,job 
from dept d inner join emp e
on d.deptno = e.deptno;

3.左连接(left join)

select dname,ename,sal,job 
from dept d left join emp e
on d.deptno = e.deptno;

4.右连接(right join)

select dname,ename,sal,job 
from dept d right join emp e
on d.deptno = e.deptno;

5.基本语法

第一步：左边查询括号写别名
第二步：右边查询括号写别名
第三步：left join横中间，后面加on是条件
第四步：select挑字段，别名后面写字段

四、索引index

1.概述

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。

2.分类

1.单值索引

一个索引只包括一个列,一个表可以有多个列

2.唯一索引

索引列的值必须唯一,但允许有空值

3.复合索引

一个索引同时包括多列

3.优点:

索引是数据库优化
表的主键会默认自动创建索引
大量降低数据库的IO磁盘读写成本，极大提高了检索速度
索引事先对数据进行了排序，降低查询是数据排序的成本，降低CPU的消耗

4.缺点:

索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间
索引表中的内容，在业务表中都有，数据是重复的，空间是“浪费的”
虽然索引大大提高了查询的速度，但反向影响了增、删、改操作的效率。如表中数据变化之后，会造成索引内容不正确，需要更新索引表信息，如果数据量非常巨大，重新创建索引的时间就大大增加
随着业务的不断变化，之前建立的索引可能不能满足查询需求，需要消耗我们的时间去更新索引

5.怎么判断是否创建索引？

1.创建索引:

1.主键约束默认建立唯一索引
2.频繁出现在where查询条件的字段
3.多表查询中与其它表进行on关联的字段，外键关系
4.单列索引/复合索引的选择? 高并发下倾向于创建复合索引
5.查询中经常用来排序的字段

2.不创建索引:

1.频繁更新的字段: 每次更新都会影响索引树
2.where条件查询中用不到的字段
3.表记录太少
4.经常增删改的表: 更新了表，索引也得更新才行

3.注意:

如果一张表中重复的记录非常多，为它建立索引就没有太大意义

6.为什么索引快？

1.自动排序
2.有树型结构,类似二分查找
比如:3层的BTree可以表示上百万的数据，如果上百万条数据，查找只需要三次IO，性能提高将是巨大的，如果没有索引每次查找都要发生一次IO，那么总共就需要百万次的IO,显然成本是非常高的。