一、事务

1. 概念

1. 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。
2. 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行，因此事务是一个不可分割的工作逻辑单元。
3. 在数据库系统上执行并发操作时，事务是作为最小的控制单元来使用的，特别适用于多用户同时操作的数据库系统。例如，航空公司的订票系统、银行、保险公司以及证券交易系统等。

2. ACID特性

事务具有4个特性，即原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability），这4个特性称为ACID。
事务的 ACID 原则保证了一个事务要么成功提交，要么失败回滚，二者必居其一。因此，它对事务的修改具有可恢复性。即当事务失败时，它对数据的修改都会恢复到该事务执行前的状态。

1）原子性

概念：原子性表示事务中的一系列操作是不可分割的，要么全部成功，要么全部失败。如果事务执行中途出现意外，已经执行的修改会被撤销， 回滚到事务开始前的状态。

示例：以银行转账（A给B转账）为例，如果该事务提交了，则A、B这两个账户的数据将会更新，但如果由于某种原因，事务在更新这两个账户之前终止了，则不会更新这两个账户的余额，并且会撤销对A、B账户余额的修改

begin transaction;
    update account set money = money-100 where name = 'A';
    update account set money = money+100 where name = 'B';
commit transaction;

2）一致性

概念：事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态

示例：转账前后A、B两个账户的金额总和应该保持不变
在事务开始之前，所有账户余额的总额处于一致状态。在事务进行的过程中，账户A余额减少了，而账户B余额尚未修改。因此，所有账户余额的总额处于不一致状态。事务完成以后，账户余额的总额再次恢复到一致状态。

3）隔离性

概念：多个并发事务之间是隔离的，并发执行的事务不会相互影响，其对数据库的影响和它们串行执行时一样。

示例：比如多个用户同时往一个账户转账，最后账户的结果应该和他们按先后次序转账的结果一样
事务T1是A给B转账，如果事务没有提交的话，那么在事务T2中并不能查看T1 未提交的数据

4）持久性

概念：持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

5）总结

在事务的ACID特性中，C即一致性是事务的根本追求，而对数据一致性的破坏主要来自两个方面：

• 事务的并发执行
• 事务故障或系统故障

数据库系统是通过并发控制技术和日志恢复技术来避免这种情况发生的。

并发控制技术：保证了事务的隔离性,使数据库的一致性状态不会因为并发执行的操作被破坏。
日志恢复技术：保证了事务的原子性,使一致性状态不会因事务或系统故障被破坏。同时使已提交的对数据库的修改不会因系统崩溃而丢失，保证了事务的持久性。

事务ACID

3. 事务的四种隔离级别

数据事务的隔离性其实就是数据库对多事务并发执行的一个控制，描述了多个事务并发执行时，事务与事务之间操作数据的可见性
SQL 标准中定义了四个隔离级别：

隔离级别

1）读未提交

读未提交，也就是会读取其他事务还未提交的数据，也就是脏读。

2）读已提交

也就是只读取其他事务已经提交的数据。
但是会出现的问题是不可重复读，也就是在一个事务里，读取数据 a，原来数据 a=1，但是另一个事务同时修改了数据 a，改成了 2，那么第一个事务再读取一下这个数据 a，这个数据 a=2。

3）可重复读

也就是在一个事务里，不管读这个数据几次，无论这个数据是否被其他事务修改，读取都是返回一样的值。
但是会出现的问题是幻读，幻读则是数据行数可能会变更，例如我读取字段 a=1 的值，一开始读到两行数据，如果这个时候另一个事务插入了一行，那么可能再次读取时就会读到 3 行数据，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读。

4）可串行话

也就是可以看作这些事务是串行执行的，不会出现上面的任何问题，当然性能也是最差的。

5）快照隔离（Snapshot Isolation）

后来其实有人提出了另一种隔离级别：快照隔离（Snapshot Isolation）
快照隔离级别不会出现脏读、不可重复读、幻读的问题，并且读操作不会被阻塞。
每个数据库厂商对于这个隔离级别有着不同的命名，例如 oracle 称之为可串行化，而 postgresql 和 mysql 则是可重复读，而这个命名混淆的原因则是在 sql 标准定义时，还并没有出现快照隔离个别，标准定义的是可重复读，这个可重复读较为接近于快照隔离级别，所以 postgresql 和 mysql 称他们的快照隔离级别为 “可重复读 "。不过 mysql 的可重复读没有完全解决幻读的问题，所以称不上严格意义上的快照隔离级别。postgresql 的可重复读确实是完全解决了幻读的问题。

二、索引

1. 概念

索引是对数据库表中一列或多列的值进行排序的一种数据结构，使用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜索所有的行相比，索引有助于更快地获取信息。

2. 目的

索引的一个主要目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构

3. 示例

例如这样一个查询：select * from table1 where id=10000。如果没有索引，必须遍历整个表，直到ID等于10000的这一行被找到为止；有了索引之后(必须是在ID这一列上建立的索引)，即可在索引中查找。由于索引是经过某种算法优化过的，因而查找次数要少的多。可见，索引是用来定位的。

4. 索引种类

数据库索引好比是一本书前面的目录，能加快数据库的查询速度。
索引分为聚簇索引和非聚簇索引两种，聚簇索引 是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

根据数据库的功能，可以在数据库设计器中创建三种索引：唯一索引、主键索引和聚集索引。

提示：尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束

1）唯一索引

唯一索引是不允许其中任何两行具有相同索引值的索引。
当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。数据库还可能防止添加将在表中创建重复键值的新数据。例如，如果在employee表中职员的姓(lname)上创建了唯一索引，则任何两个员工都不能同姓。

2）主键索引

数据库表经常有一列或多列组合，其值唯一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将自动创建主键索引，主键索引是唯一索引的特定类型。该索引要求主键中的每个值都唯一。当在查询中使用主键索引时，它还允许对数据的快速访问。

3）聚集索引

在聚集索引中，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能包含一个聚集索引。如果某索引不是聚集索引，则表中行的物理顺序与键值的逻辑顺序不匹配。与非聚集索引相比，聚集索引通常提供更快的数据访问速度。
聚集索引和非聚集索引的区别：
聚集索引：如字典默认按字母顺序排序，读者如知道某个字的读音可根据字母顺序快速定位。因此聚集索引和表的内容是在一起的。
非聚集索引：如读者需查询某个生僻字，则需按字典前面的索引，举例按偏旁进行定位，找到该字对应的页数，再打开对应页数找到该字。这种通过两个地方而查询到某个字的方式就如非聚集索引。

4）组合列

可以基于数据库表中的单列或多列创建索引。多列索引可以区分其中一列可能有相同值的行。如果经常同时搜索两列或多列或按两列或多列排序时，索引也很有帮助。例如，如果经常在同一查询中为姓和名两列设置判据，那么在这两列上创建多列索引将很有意义。

5. 索引原理-平衡树

首先我们要对一个查询的基本流程简单了解，当我们输入一个 SQL 进行查询的时候，那么数据库管理系统（DBMS）将经过如下流程：

sql查询

数据库索引是用于提高数据库表的数据访问速度的，因此其实是优化查询算法

1）全表扫描

对表中每行数据进行读取，IO次数多，时间长，时间复杂度O(n)

2）索引扫描

索引原理就是B+树结构，即 平衡树，其特点：

• B + 树不是二叉树，m 阶的 B+ 树其子树中最多分为 m 叉。
• 所有的叶子节点都位于同一层 —— 稳定性，不会出现又是查找1层就找到，有时需要查找n层
• 所有的中间节点只保存索引信息，不保存数据信息 —— 即想找到数据，必须从根节点找到叶子节点，因为数据都在叶子节点上
• 所有的叶子节点包含了全部的元素信息，即含有指向这些元素的的指针（也就是地址信息）。
• 所有中间节点的元素都存在于子节点，在子节点元素中是最大（或最小的）元素

• 所有叶子节点（即数据节点）之间是一种链式环结构，依靠关键字的大小从小到大顺序链接。

  
  
  B + 树

6. 索引使用场景

适用场景：精确查找、模糊查找-最左匹配（a*）、组合索引-最左匹配
不适用场景：除了模糊查找-最左匹配外、组合索引-最左匹配外的模糊查找
组合索引：例如对a、b、c这3列建立了组合索引，顺序是abc

• 查找a=1、b=2、c=3：走索引
• 查找a=1、b=2：走索引
• 查找a=1：走索引
• 查找b=2、c=3：不走索引
• 查找a=1、c=3：不走索引

三、SQL常用语句

image.png

• SELECT - 从数据库中提取数据
• UPDATE - 更新数据库中的数据
• DELETE - 从数据库中删除数据
• INSERT INTO - 向数据库中插入新数据
• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

1. select查询语句

SELECT 语句是最常用来识别、检索和操作表中数据，使用子句可以设定查询条件，常用子句及其固定顺序：

• select 变量 / 列名 from 来源数据集 / 表名
• where 选择条件
• group by 分组标志（变量）
• having 针对组的选择条件
• order by 排序标志（变量）

1）where 子句

选择条件：where + 条件表达式

where

• 比较算符和逻辑算符还有一些等价形式。
• 所有算符前面加一个 not 算符，可以得到相反的选择条件。
• 条件较复杂时，可以将表达式用括号括起来，以突出逻辑关系、增强可读性。
  几个重要条件运算符
• in: 符合选项之一； in+(选项 1，选项 2，…）
• between-and： 选择一定范围
• like：部分或全部匹配； % 可代替任意数量的字符； _可代替一个字符

   select * from class where age in (12 14 16);
   select * from class where age between 12 and 15;
   select * from class where (sex='F' and weight>100) or (sex='M' and weight<=90);
   select * from class where name like '%h%';
   select * from class where name like '_h%';

2）GROUP BY 和 ORDER BY 子句

Group by 一般与汇总函数结合使用，汇总函数按照 GROUP BY 子句进行分组汇总：

• 如果没有 GROUP BY，就默认整个表为一组；

• 如果没有汇总函数，则 GROUP BY 单纯分组。

  
  
  image.png

Order by对结果排序

 select name, sex, count(sex) as n, avg(height) as avgh from class
 group by sex
 order by name desc

3）HAVING 子句

HAVING 筛选组数据

• HAVING 对组设定条件，进行检索筛选；
• 在 GROUP BY 和汇总函数后运行，可以引用汇总函数结果。

与 WHERE 子句的区别：

• WHERE 对列设定条件，进行检索筛选；
• 在 GROUP BY 和汇总函数前运行，不可以引用汇总函数结果。

 select age, avg(height) as avgh  from class
 group by age
 having avgh>60

4）SELECT TOP, LIMIT 子句

取前5行

select column_name(s) from table_name limit 5

select top 5 from table_name
order by age desc/asc

5）SELECT 语句综合练习

• 选择 class 表中，名字首字母不是 B 的学生数据。

select * from class where name not like 'B%'

• 按年龄分组统计平均体重（avgw）、每组人数（n）

select age, avg(weight) as avgw, count(*) as n from class
group by age

• 输出平均体重大于 100 的组，列示年龄、平均体重、每组人数，按照年龄倒序排列

select age, avg(weight) as avgw, count(*) as n  from class
group by age
having avgw > 100
order by age desc

2. 多表查询——连接

检索数据通常会涉及多个表，一般使用连接或子查询

• 连接查询： 从多个表中选取数据 数据来自不同的表

• 子查询： 通过表与表之间的联系选取数据 选择一个表的数据时， 需要参照其他表的信息。

  
  
  image.png

1）JOIN 连接——交叉连接

交叉连接（CROSS JOIN）又叫 简单连接，是最基本的连接，以笛卡尔积形式，组成的所有可能组合。

  select * from china cross join usa;
  select * from china , usa;

如果观测行数很多，这种连接会产生巨大的数据结果，且绝大部分没有意义，可用其他 JOIN 连接选择相应子集。

2）JOIN 连接——内部连接

返回两表中匹配连接条件的行：表a inner join 表b on 连接条件

• 查询时经常遇到两个表有相同列，为了引用清楚，在列前加表名；
• 为了书写和阅读方便，可在 from 子句中设定表的别名；
• 关键词 inner, as 可选；
• 仍然可以使用 where、order by 等子句。

示例

  select * from china as a inner join usa as b 
  on a.level=b.level;
等价于：
  select * from china a , usa b 
  where a.level=b.level;

3）JOIN 连接——外部连接

返回内部连接的行以及部分不匹配连接条件的行；

• 左外部连接 (LEFT JOIN)：内部连接行 + 左边表中不符合匹配条件的行；
• 右外部连接 (RIGHT JOIN)：内部连接行 + 右边表中不符合匹配条件的行；
• 完全外部连接 (FULL JOIN)：内部连接行 + 左右两表中不符合匹配条件的行。

 select * from resdat.china a left join resdat.usa b on a.level=b.level;
 select * from resdat.china a right join resdat.usa b on a.level=b.level;
 select * from resdat.china a full join resdat.usa b on a.level=b.level;

4）JOIN 连接综合练习

按照level相同的原则匹配中美两队队员，分别选择：

• 中国队所有队员的匹配情况：select * from china left join usa on china.level=usa.level
• 美国队所有队员的匹配情况：select * from china right join usa on china.level=usa.level
• 中美两队成功匹配的队员：select * from china inner join usa on china.level=usa.level
• 中美两队所有队员的匹配情况：select * from china full join usa on china.level=usa.level
• 美国队内部按照level相同原则匹配：select * from usa a innei join usa b on a.level=b.level
• 按照美国队员比中国队员level低的原则匹配：select * from china inner join usa on china.level>usa.level

3. 多表查询——子查询

1）简单子查询

简单子查询是在一个查询语句中，用到了另一个查询的结果

• 子查询独立于外部查询；
• 子查询返回的结果要与该查询外的运算符相对应：
  • 如果是比较运算符如 >、= 等，子查询只能返回一个值；
  • 如果是条件运算符如 in、any、all 等，子查询可以返回多个值。

# 查询usa表中level小于中国队最高level的数据
 select * from usa 
   where level<(select max(level) from china);
# 查询usa表中level与中国队level相同的数据
 select * from usa 
   where level in (select distinct level from china);

2）混合子查询

混合子查询：子查询用到外部查询传输进来的值，该子查询运行后再给外部查询返回结果；可以返回单值或多值。

# 查询usa表中年龄为18岁，且level与中国队一样，且体重在100～150之间的队员信息
 select * from usa a
 where (select age from china b 
            where a.level=b.level)=18
 and 100<=weight<=150

4. 创建与更新表

1）创建表

• 创建表：CREATE TABLE + 表名
• 列定义方式：
  CREATE TABLE 表名称
  (
  列名称1 数据类型,
  列名称2 数据类型,
  ....
  )

 create table class 
(id int, 
 name char(20)
 )

• 从查询结果创建表： CREATE TABLE + 表名 + AS + SELECT 语句
  • 新表包括 SELECT 语句所选择的列，且保持原来的列属性

 create table class1 as select * from class;

2）更新表update

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

3）在表中插入行

insert into + 表名

• 用 SET 子句插入行：insert into + 表名 + set + 根据列名赋值
  insert into class1 set name='Lily', age1=13, sex='F', height=60.5
• 用 VALUES 子句插入行： insert into + 表名 + VALUES +(对应列的位置赋值)
  insert into class1 values ('Lucy', 'F', 14, ., 89)

4）删除行delete

delete from table_name where some_column=some_value
如果省略了 WHERE 子句，所有的记录都将被删除，但是表结构、属性、索引将保持不变。