SQL老黄牛

事务 transaction

数据库事务(Database Transaction) 是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行
简单来说：
事务就是将一堆的SQL语句（通常是增删改）绑定在一起执行，要么都执行成功，要么都执行失败，即都执行成功才算成功，否则这些SQL语句就会恢复到执行之前的状态。

特性ACID

• 原子性 Atomicity
  一个事务中的所有操作，要么全部成功，要么全部不成功，不会结束在中间某个环节。
  事务在执行过程中如果发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
• 一致性 Consistency
  在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
• 隔离性 Isolation
  数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行导致数据的不一致。
• 持久性 Durability
  事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

隔离级别

• 读未提交 Read Uncommitted
  安全性最差，可能发生并发数据问题，性能最好
• 读提交 Read Committed
  Oracle默认的隔离级别
• 可重复读 Repeatable Read
  MySQL默认的隔离级别，安全性较好，性能一般
• 串行化 Serializable
  表级锁，读写都加锁，效率低下，安全性高，不能并发在

事务处理

• 在MySQL 中只有使用了 innodb 数据库引擎的数据库或表 才支持事务
• 事务处理可以用来维护数据的完整性，保证成批的SQL 语句要么全部执行，要么全部不执行
• 事务用来管理 insert、update、delete 语句，因为这些操作才会破坏数据，select 语句是不会的
• MySQL 默认数据库的事务是开启的，执行SQL后自动提价
• MySQL的事务也可以改成手动提交，那就有两个步骤：
  先开启，写完SQL 后，再手动提交。

开启事务

start transaction;
begin;

结束事务

commit; -- 提交事务
rollback; -- 回滚事务

约束 constaints

• 非空约束 not null
• 唯一约束 unique
• 主键约束 primary key
• 外键约束 foreign key
• 默认约束 default
• 检查约束 check

表关联 association

表的关系分为四种

• 一对一
  QQ和QQ邮箱，员工和员工编号
• 一对多
  部门和员工，用户和订单
• 多对一
  员工和部门，订单和用户
• 多对多
  老师和学生，老师和课程

创建表

• 表都以s结束，标识复数
• 字段多以表的首字母作为开头，在多表联查时，方便标识出哪个表的字段

多表联查 join

笛卡尔积

select * from dept,emp;

多表查询都是先生成笛卡尔积，再进行数据的筛选过滤

join

• 内连接 inner join
• 左外连接 left join
• 右外连接 right join

多表联查案例

案例： 列出research 部门下的所有员工的信息

select * from emp where 
deptno=(select deptno from dept where dname="research");

使用 inner join 实现上面的案例

select d.dname,e.ename,e.job 
from emp e inner join dept d 
on e.deptno = d.deptno 
where d.dname = "research";

select d.dname,e.ename,e.job
from emp e inner join dept d
on e.deptno = d.deptno
where d.dname = “research”;

• inner join 两边都对应有记录的才展示，其他去掉
• left join 左边表中的数据都出现，右边没有数据以null填充
• right join 右边表中的数据都出现，左边没有数据以null填充

子查询（嵌套查询）

单行子查询 =

select * from emp where deptno = 
(select deptno from emp where ename="tony"); --查询tony所在的部门的所有人信息

多行子查询 in

select * from emp where job in("经理","员工");
select * from emp where job in(select job from emp where ename="leo");除了leo谁还从事该工作
select * from emp where job in(select distinct job from emp);

索引 index

定义
索引是一种排好序的快速查找的数据结构，它帮助数据库高效的进行数据的检索。在数据之外，数据库系统还维护着特定查找算法的数据结构（额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效的查找算法。这种数据结构就叫做索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中。目前大多索引都采用BTree树方式构建。

索引分类

• 单值索引：
  一个索引只包括一个列，一个表可以有多个列
• 唯一索引：
  索引列的值必须是唯一，但允许有空值；主键会自动创建唯一索引
• 复合索引：
  一个索引同时包含多列

查看索引

主键会自动创建索引

show index from dept

创建普通索引

create index loc_index on dept(loc);
alter table dept add index loc_index(loc);

创建唯一索引

create unique index loc_unique_index on dept(loc);
alter table dept add unique index loc_unique_index(loc);

创建复合索引

create index emp_composite_index on emp(empno,ename);
alter table emp add index emp_composite_index(empno,ename);

删除索引

alter table dept drop index loc_index

为何索引快

首先，索引表是排序了，可以类似二分查找，非常有效的提高了查询的速度。
先找到事先排序好的索引表中检索查询，找到其主键后，就直接定位到记录所在位置，然后直接返回这条数据。

• 排序、tree结构、类似二分查找
• 索引表小

优点

• 索引是数据库优化
• 表的主键会默认自动创建索引
• 每个字段都可以被索引
• 大量降低数据库的IO磁盘读写成本，极大提高了检索速度
• 索引事先对数据库进行了排序，大大提高了查询效率

缺点

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