大数据开发基础入门与项目实战（二）Java Web数据可视化之2.MySQL单表、约束和事务

男娘i 2022-09-10 00:19 21阅读 0赞

### 文章目录 ###

*  前言
 *  1.DQL操作单表
 *   *  （1）DQL之排序查询
     *  （2）DQL之聚合函数
     *  （3）DQL之分组查询
     *  （4）limit关键字
 *  2.SQL约束
 *   *  （1）约束的介绍
     *  （2）主键约束
     *  （3）主键自增
     *  （4）非空约束
     *  （5）唯一约束
     *  （6）默认值
 *  3.数据库事务
 *   *  （1）事务的基本概念
     *  （2）MySQL手动提交事务
     *  （3）MySQL自动提交事务
     *  （4）MySQL事务四大特性ACID
     *  （5）MySQL事务隔离级别
     *  （6）隔离级别相关命令
     *  （7）脏读及解决
     *  （8）不可重复读及解决
     *  （9）幻读及解决
 *  总结

# 前言 #

本文主要介绍了MySQL的单表查询、数据库约束和事务，在MySQL中占有极为重要的地位。

# 1.DQL操作单表 #

## （1）DQL之排序查询 ##

SQL排序查询使用 ORDER BY 子句，可以将查询出的结果进行排序（排序只是显示效果，不会影响真实数据），语法格式如下：

select 字段名 from 表名 [where 字段名 = 值] order by 字段名称 [asc/desc];

其中，asc表示升序排序，也是默认排序方式，desc表示降序排序。

单列排序是按照某一个字段进行排序。

使用如下：

-- 使用salary字段，对emp表数据进行排序
    -- 默认升序
    mysql> select * from emp order by salary;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |   10 | 孙悟饭    | 男   |     10 | 2020-03-14 | 财务部    |
    |    6 | 玉兔精    | 女   |    200 | 2000-03-14 | 市场部    |
    |   11 | 兔八哥    | 女   |    300 | 2010-03-14 | 财务部    |
    |    8 | 黄蓉      | 女   |   3500 | 2011-09-14 | 财务部    |
    |    2 | 猪八戒    | 男   |   3600 | 2010-12-02 | 教学部    |
    |    4 | 白骨精    | 女   |   5000 | 2015-10-07 | 市场部    |
    |    5 | 蜘蛛精    | 女   |   5000 | 2011-03-14 | 市场部    |
    |    1 | 孙悟空    | 男   |   7200 | 2013-02-04 | 教学部    |
    |    3 | 唐僧      | 男   |   9000 | 2008-08-08 | 教学部    |
    |    7 | 林黛玉    | 女   |  10000 | 2019-10-07 | 财务部    |
    |    9 | 吴承恩    | 男   |  20000 | 2000-03-14 | NULL      |
    +------+-----------+------+--------+------------+-----------+
    11 rows in set (0.00 sec)
    -- 降序
    mysql> select * from emp order by salary desc;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    9 | 吴承恩    | 男   |  20000 | 2000-03-14 | NULL      |
    |    7 | 林黛玉    | 女   |  10000 | 2019-10-07 | 财务部    |
    |    3 | 唐僧      | 男   |   9000 | 2008-08-08 | 教学部    |
    |    1 | 孙悟空    | 男   |   7200 | 2013-02-04 | 教学部    |
    |    4 | 白骨精    | 女   |   5000 | 2015-10-07 | 市场部    |
    |    5 | 蜘蛛精    | 女   |   5000 | 2011-03-14 | 市场部    |
    |    2 | 猪八戒    | 男   |   3600 | 2010-12-02 | 教学部    |
    |    8 | 黄蓉      | 女   |   3500 | 2011-09-14 | 财务部    |
    |   11 | 兔八哥    | 女   |    300 | 2010-03-14 | 财务部    |
    |    6 | 玉兔精    | 女   |    200 | 2000-03-14 | 市场部    |
    |   10 | 孙悟饭    | 男   |     10 | 2020-03-14 | 财务部    |
    +------+-----------+------+--------+------------+-----------+
    11 rows in set (0.00 sec)

组合排序是同时对多个字段进行排序，如果第一个字段相同，就按照第二个字段进行排序，以此类推。

使用如下：

-- 在薪水排序的基础上，再使用id进行排序，如果薪水相同就以id降序排序
    mysql> select * from emp order by salary desc, eid desc;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    9 | 吴承恩    | 男   |  20000 | 2000-03-14 | NULL      |
    |    7 | 林黛玉    | 女   |  10000 | 2019-10-07 | 财务部    |
    |    3 | 唐僧      | 男   |   9000 | 2008-08-08 | 教学部    |
    |    1 | 孙悟空    | 男   |   7200 | 2013-02-04 | 教学部    |
    |    5 | 蜘蛛精    | 女   |   5000 | 2011-03-14 | 市场部    |
    |    4 | 白骨精    | 女   |   5000 | 2015-10-07 | 市场部    |
    |    2 | 猪八戒    | 男   |   3600 | 2010-12-02 | 教学部    |
    |    8 | 黄蓉      | 女   |   3500 | 2011-09-14 | 财务部    |
    |   11 | 兔八哥    | 女   |    300 | 2010-03-14 | 财务部    |
    |    6 | 玉兔精    | 女   |    200 | 2000-03-14 | 市场部    |
    |   10 | 孙悟饭    | 男   |     10 | 2020-03-14 | 财务部    |
    +------+-----------+------+--------+------------+-----------+
    11 rows in set (0.00 sec)

## （2）DQL之聚合函数 ##

之前我们做的查询都是**横向查询** ，它们都是根据条件一行一行的进行判断；

而使用聚合函数查询是**纵向查询** ，它是对某一列的值进行计算，然后返回一个单一的值，语法结构为：

SELECT 聚合函数(字段名) FROM 表名 [where 条件];

聚合函数会忽略null空值。

常用的5个聚合函数如下：

<table> 
 <thead> 
  <tr> 
   <th>聚合函数</th> 
   <th>作用</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>count(字段)</td> 
   <td>统计指定列不为NULL的记录行数</td> 
  </tr> 
  <tr> 
   <td>sum(字段)</td> 
   <td>计算指定列的数值和</td> 
  </tr> 
  <tr> 
   <td>max(字段)</td> 
   <td>计算指定列的最大值</td> 
  </tr> 
  <tr> 
   <td>min(字段)</td> 
   <td>计算指定列的最小值</td> 
  </tr> 
  <tr> 
   <td>avg(字段)</td> 
   <td>计算指定列的平均值</td> 
  </tr> 
 </tbody> 
</table>

使用如下：

# 查询员工的总数
    mysql> select count(*) from emp;
    +----------+
    | count(*) |
    +----------+
    |       11 |
    +----------+
    1 row in set (0.01 sec)
    
    mysql> select count(1) from emp;
    +----------+
    | count(1) |
    +----------+
    |       11 |
    +----------+
    1 row in set (0.00 sec)
    
    mysql> select count(eid) from emp;
    +------------+
    | count(eid) |
    +------------+
    |         11 |
    +------------+
    1 row in set (0.00 sec)
    # 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
    mysql> SELECT 
        SUM(salary) AS '总薪水',
        MAX(salary) '最高薪水',
        MIN(salary) '最小薪水',
        AVG(salary) '平均薪水'  
    FROM emp;
    +-----------+--------------+--------------+-------------------+
    | 总薪水    | 最高薪水     | 最小薪水     | 平均薪水          |
    +-----------+--------------+--------------+-------------------+
    |     63810 |        20000 |           10 | 5800.909090909091 |
    +-----------+--------------+--------------+-------------------+
    1 row in set (0.00 sec)
    # 查询薪水大于4000员工的个数
    mysql> select count(1) from emp where salary > 4000;
    +----------+
    | count(1) |
    +----------+
    |        6 |
    +----------+
    1 row in set (0.00 sec)
    # 查询部门为'教学部'的所有员工的个数
    mysql> select count(1) from emp where dept_name = '教学部';
    +----------+
    | count(1) |
    +----------+
    |        3 |
    +----------+
    1 row in set (0.00 sec)
    # 查询部门为'市场部'所有员工的平均薪水
    mysql> select avg(salary) '平均薪水' from emp where dept_name = '市场部';
    +--------------+
    | 平均薪水     |
    +--------------+
    |         3400 |
    +--------------+
    1 row in set (0.00 sec)

其中，`select count(*) from emp;`等价于`select count(1) from emp;`、`select count(eid) from emp;`，但是不能用`select count(dept_name) from emp;`，因为有记录的dept\_name字段值为null、不会被count函数统计，所以避免在使用count聚合函数的时候传入待统计的字段。

## （3）DQL之分组查询 ##

分组查询使用 GROUP BY 语句，对查询的信息进行分组，相同数据作为一组。

分组是将具有相同特征的数据看成一个整体，并获取它们的信息。

分组的目的就是为了做统计操作，可以查询要分组的字段，或者使用聚合函数进行统计操作，查询其他字段是没有意义的。

语法格式如下：

select 分组字段/聚合函数 from 表名 group by 分组字段

使用如下：

-- 通过性别字段进行分组，求各组的平均薪资
    mysql> select sex, avg(salary) from emp group by sex;
    +------+-------------+
    | sex  | avg(salary) |
    +------+-------------+
    | 女   |        4000 |
    | 男   |        7962 |
    +------+-------------+
    2 rows in set (0.00 sec)
    -- 查询所有部门信息
    mysql> select dept_name as '部门名' from emp group by dept_name;
    +-----------+
    | 部门名    |
    +-----------+
    | NULL      |
    | 市场部    |
    | 教学部    |
    | 财务部    |
    +-----------+
    4 rows in set (0.00 sec)
    -- 查询每个部门的平均薪资
    mysql> select dept_name, avg(salary) from emp group by dept_name;
    +-----------+-------------+
    | dept_name | avg(salary) |
    +-----------+-------------+
    | NULL      |       20000 |
    | 市场部    |        3400 |
    | 教学部    |        6600 |
    | 财务部    |      3452.5 |
    +-----------+-------------+
    4 rows in set (0.01 sec)
    -- 查询每个部门的平均薪资, 部门名称不能为null
    mysql> select dept_name, avg(salary) from emp where dept_name is not null group by dept_name;
    +-----------+-------------+
    | dept_name | avg(salary) |
    +-----------+-------------+
    | 市场部    |        3400 |
    | 教学部    |        6600 |
    | 财务部    |      3452.5 |
    +-----------+-------------+
    3 rows in set (0.00 sec)

分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。

使用如下：

-- 查询平均薪资大于6000的部门
    -- 1.分组求平均薪资
    -- 2.筛选平均薪资大于6000的部门
    mysql> select dept_name, avg(salary) from emp where dept_name is not null group by dept_name having avg(salary) > 6000;
    +-----------+-------------+
    | dept_name | avg(salary) |
    +-----------+-------------+
    | 教学部    |        6600 |
    +-----------+-------------+
    1 row in set (0.00 sec)

where和having关键字的区别如下：

*  where

- where 进行分组前的过滤

- where 后面不能写 聚合函数

*  having

- having 进行分组后的过滤

- having 后面可以写聚合函数

## （4）limit关键字 ##

limit是限制的意思，用于限制返回的查询结果的行数，可以通过limit指定查询多少行数据。  
limit 语法是 MySQL的方言，可以用来完成分页。

语法格式如下：

SELECT 字段1, 字段2, ... FROM 表名 LIMIT offset, length;

其中，limit offset , length; 关键字可以接收一个或者两个为0或者正整数的参数；  
offset表示起始行数，从0开始记数，如果省略则默认为 0；  
length表示返回的行数。

使用如下：

# 查询emp表中的前5条数据
    mysql> select * from emp limit 0, 5;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    1 | 孙悟空    | 男   |   7200 | 2013-02-04 | 教学部    |
    |    2 | 猪八戒    | 男   |   3600 | 2010-12-02 | 教学部    |
    |    3 | 唐僧      | 男   |   9000 | 2008-08-08 | 教学部    |
    |    4 | 白骨精    | 女   |   5000 | 2015-10-07 | 市场部    |
    |    5 | 蜘蛛精    | 女   |   5000 | 2011-03-14 | 市场部    |
    +------+-----------+------+--------+------------+-----------+
    5 rows in set (0.01 sec)
    
    mysql> select * from emp limit 5;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    1 | 孙悟空    | 男   |   7200 | 2013-02-04 | 教学部    |
    |    2 | 猪八戒    | 男   |   3600 | 2010-12-02 | 教学部    |
    |    3 | 唐僧      | 男   |   9000 | 2008-08-08 | 教学部    |
    |    4 | 白骨精    | 女   |   5000 | 2015-10-07 | 市场部    |
    |    5 | 蜘蛛精    | 女   |   5000 | 2011-03-14 | 市场部    |
    +------+-----------+------+--------+------------+-----------+
    5 rows in set (0.00 sec)
    # 查询emp表中从第4条开始、查询6条
    mysql> select * from emp limit 3, 6;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    4 | 白骨精    | 女   |   5000 | 2015-10-07 | 市场部    |
    |    5 | 蜘蛛精    | 女   |   5000 | 2011-03-14 | 市场部    |
    |    6 | 玉兔精    | 女   |    200 | 2000-03-14 | 市场部    |
    |    7 | 林黛玉    | 女   |  10000 | 2019-10-07 | 财务部    |
    |    8 | 黄蓉      | 女   |   3500 | 2011-09-14 | 财务部    |
    |    9 | 吴承恩    | 男   |  20000 | 2000-03-14 | NULL      |
    +------+-----------+------+--------+------------+-----------+
    6 rows in set (0.00 sec)
    # limit分页，每页3条
    # 第1页
    mysql> select * from emp limit 0, 3;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    1 | 孙悟空    | 男   |   7200 | 2013-02-04 | 教学部    |
    |    2 | 猪八戒    | 男   |   3600 | 2010-12-02 | 教学部    |
    |    3 | 唐僧      | 男   |   9000 | 2008-08-08 | 教学部    |
    +------+-----------+------+--------+------------+-----------+
    3 rows in set (0.00 sec)
    # 第2页
    mysql> select * from emp limit 3, 3;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    4 | 白骨精    | 女   |   5000 | 2015-10-07 | 市场部    |
    |    5 | 蜘蛛精    | 女   |   5000 | 2011-03-14 | 市场部    |
    |    6 | 玉兔精    | 女   |    200 | 2000-03-14 | 市场部    |
    +------+-----------+------+--------+------------+-----------+
    3 rows in set (0.00 sec)
    # 第3页
    mysql> select * from emp limit 6, 3;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |    7 | 林黛玉    | 女   |  10000 | 2019-10-07 | 财务部    |
    |    8 | 黄蓉      | 女   |   3500 | 2011-09-14 | 财务部    |
    |    9 | 吴承恩    | 男   |  20000 | 2000-03-14 | NULL      |
    +------+-----------+------+--------+------------+-----------+
    3 rows in set (0.00 sec)
    # 第4页
    mysql> select * from emp limit 9, 3;
    +------+-----------+------+--------+------------+-----------+
    | eid  | ename     | sex  | salary | hire_date  | dept_name |
    +------+-----------+------+--------+------------+-----------+
    |   10 | 孙悟饭    | 男   |     10 | 2020-03-14 | 财务部    |
    |   11 | 兔八哥    | 女   |    300 | 2010-03-14 | 财务部    |
    +------+-----------+------+--------+------------+-----------+
    2 rows in set (0.00 sec)

其中，`select * from emp limit 0, 5;`等价于`select * from emp limit 5;`；

分页公式为 起 始 索 引 ( o f f s e t ) = ( 当 前 页 码 − 1 ) ∗ 每 页 显 示 条 数 起始索引(offset) = (当前页码 - 1) \* 每页显示条数 起始索引(offset)=(当前页码−1)∗每页显示条数。

# 2.SQL约束 #

## （1）约束的介绍 ##

约束是对表中的数据进行进一步的限制，从而保证数据的正确性、有效性、完整性。  
违反约束的不正确数据，将无法插入到表中。

常见的约束如下：

<table> 
 <thead> 
  <tr> 
   <th>约束名</th> 
   <th>约束关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>主键</td> 
   <td>primary key</td> 
  </tr> 
  <tr> 
   <td>唯一</td> 
   <td>unique</td> 
  </tr> 
  <tr> 
   <td>非空</td> 
   <td>not null</td> 
  </tr> 
  <tr> 
   <td>外键</td> 
   <td>foreign key</td> 
  </tr> 
 </tbody> 
</table>

## （2）主键约束 ##

主键约束的特点是不可重复、唯一、非空，作用是用来表示数据库中的每一条记录。

语法格式如下：

字段名 字段类型 primary key

添加主键有3种方式：

*  创建表时，在声明字段时即指定主键
 *  创建表时，最后指定主键
 *  通过DDL语句给已经创建好的表添加主键约束

使用如下：

可以看到，通过3种方式对表设置主键都成功。

现在进行测试如下：

# 正常插入一条数据
    mysql> INSERT INTO emp2 VALUES(1,'宋江','男');
    Query OK, 1 row affected (0.00 sec)
    # 插入一条数据,主键为空
    -- 主键不能为空
    mysql> INSERT INTO emp2 VALUES(NULL,'李逵','男');
    ERROR 1048 (23000): Column 'eid' cannot be null
    # 插入一条数据,主键为1
    -- 主键不能重复
    mysql> INSERT INTO emp2 VALUES(1,'孙二娘','女');
    ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

删除主键也是使用DDL语句，语法格式为`alter table 表名drop primary key;`。

使用如下：

可以看到，在删除主键后，eid能插入重复的值。

设置主键的原则：

通常针对业务去设计主键，每张表都设计一个主键id；

主键是给数据库和程序使用的，跟最终的客户无关，所以主键没有意义没有关系，只要能够保证不重复就好，比如身份证就可以作为主键。

## （3）主键自增 ##

主键如果让我们自己添加很有可能重复，我们通常希望在每次插入新记录时，数据库自动生成主键字段的值，即主键自增。

可以通过关键字`AUTO_INCREMENT` 设置字段自动增长，需要保证字段类型必须是整数类型。

现在创建表并添加数据测试自增，如下：

-- 创建主键自增的表
    mysql> CREATE TABLE emp4(
        eid INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(20),
        sex CHAR(1)
    );
    Query OK, 0 rows affected (0.02 sec)
    -- 插入数据
    mysql> INSERT INTO emp4(ename,sex) VALUES('张三','男');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from emp4;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    |   1 | 张三   | 男   |
    +-----+--------+------+
    1 row in set (0.00 sec)
    
    mysql> INSERT INTO emp4(ename,sex) VALUES('李四','男');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from emp4;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    |   1 | 张三   | 男   |
    |   2 | 李四   | 男   |
    +-----+--------+------+
    2 rows in set (0.00 sec)
    
    mysql> INSERT INTO emp4 VALUES(NULL,'翠花','女');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from emp4;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    |   1 | 张三   | 男   |
    |   2 | 李四   | 男   |
    |   3 | 翠花   | 女   |
    +-----+--------+------+
    3 rows in set (0.00 sec)
    
    mysql> INSERT INTO emp4 VALUES(NULL,'艳秋','女');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from emp4;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    |   1 | 张三   | 男   |
    |   2 | 李四   | 男   |
    |   3 | 翠花   | 女   |
    |   4 | 艳秋   | 女   |
    +-----+--------+------+
    4 rows in set (0.00 sec)

可以看到，插入数据时，主键eid实现了自增；

此时，在插入数据时，主键字段的值可以是null或0，在插入的数据中会被自动替换为对应的自增值。

默认的 AUTO\_INCREMENT 的起始值是 1，也可以修改主键自增的起始值，语法为`AUTO_INCREMENT=自定义初始值`。

使用如下：

-- 创建主键自增的表，自定义自增的起始值
    mysql> CREATE TABLE emp5(
        eid INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(20),
        sex CHAR(1)
    )AUTO_INCREMENT=100;
    Query OK, 0 rows affected (0.01 sec)
    -- 插入值
    mysql> INSERT INTO emp5 VALUES(NULL,'翠花','女');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> INSERT INTO emp5 VALUES(NULL,'艳秋','女');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from emp5;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    | 100 | 翠花   | 女   |
    | 101 | 艳秋   | 女   |
    +-----+--------+------+
    2 rows in set (0.00 sec)

可以看到，此时插入的数据是从自定义的100开始的。

DELETE和TRUNCATE删除表中所有数据对自增长的影响：

<table> 
 <thead> 
  <tr> 
   <th>清空表数据的方式</th> 
   <th>特点</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>DELETE</td> 
   <td>只是删除表中所有数据，对自增没有影响</td> 
  </tr> 
  <tr> 
   <td>TRUNCATE</td> 
   <td>是将整个表删除掉，然后创建一个新的表，相当于是直接释放，自增的主键重新从1开始</td> 
  </tr> 
 </tbody> 
</table>

现在验证如下：

- delete方式删除所有数据
    mysql> delete from emp4;
    Query OK, 4 rows affected (0.00 sec)
    
    mysql> INSERT INTO emp4 VALUES(NULL,'艳秋','女');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from emp4;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    |   5 | 艳秋   | 女   |
    +-----+--------+------+
    1 row in set (0.00 sec)
    -- truncate方式删除所有数据
    mysql> truncate table emp5;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> INSERT INTO emp5 VALUES(NULL,'艳秋','女');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from emp5;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    |   1 | 艳秋   | 女   |
    +-----+--------+------+
    1 row in set (0.00 sec)

可以看到，使用delete删除表中的所有数据后，再插入数据，主键是从接着之前的主键向上增加的；

而使用truncate删除表并重新建表后，会保留了表结构及其约束、索引等，设置了自起始值都会重置成1，而不管原表是否自定义了初始值，可以在看作表创建以后又执行了一条设置自增起始值为1的命令。

## （4）非空约束 ##

非空约束的特点是**某一列不予许为空** 。

语法格式是：

字段名 字段类型 not null

使用如下：

-- 创建数据表
    -- 将ename字段设置为非空
    mysql> CREATE TABLE emp6(
        eid INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(20) NOT NULL,
        sex CHAR(1)
    )AUTO_INCREMENT=100;
    Query OK, 0 rows affected (0.02 sec)
    -- 查看表结构
    mysql> desc emp6;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | eid   | int(11)     | NO   | PRI | NULL    | auto_increment |
    | ename | varchar(20) | NO   |     | NULL    |                |
    | sex   | char(1)     | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    
    -- 插入正常数据
    mysql> INSERT INTO emp6 VALUES(NULL,'艳秋','女');
    Query OK, 1 row affected (0.00 sec)
    
    mysql> select * from emp6;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    | 100 | 艳秋   | 女   |
    +-----+--------+------+
    1 row in set (0.00 sec)
    -- 插入ename字段为null的数据
    mysql> INSERT INTO emp6 VALUES(NULL, NULL, '女');
    ERROR 1048 (23000): Column 'ename' cannot be null

可以看到，此时不能插入ename字段为空的数据。

## （5）唯一约束 ##

唯一约束的特点是表中的某一列的值不能重复，但是对null不做唯一的判断。

语法格式为：

字段名 字段值 unique

使用如下：

-- 创建表，为ename字段添加唯一约束
    mysql> CREATE TABLE emp7(
        eid INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(20) UNIQUE,
        sex CHAR(1)
    );
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> desc emp7;
    +-------+-------------+------+-----+---------+----------------+
    | Field | Type        | Null | Key | Default | Extra          |
    +-------+-------------+------+-----+---------+----------------+
    | eid   | int(11)     | NO   | PRI | NULL    | auto_increment |
    | ename | varchar(20) | YES  | UNI | NULL    |                |
    | sex   | char(1)     | YES  |     | NULL    |                |
    +-------+-------------+------+-----+---------+----------------+
    3 rows in set (0.00 sec)
    -- 插入正常数据
    mysql> INSERT INTO emp7 VALUES(NULL,'艳秋','女');
    Query OK, 1 row affected (0.00 sec)
    -- 插入重复ename的值
    mysql> INSERT INTO emp7 VALUES(NULL,'艳秋','女');
    ERROR 1062 (23000): Duplicate entry '艳秋' for key 'ename'

显然，第二次插入时就会报错、不能正常插入。

主键约束与唯一约束的区别：

*  主键约束唯一，且不能够为空
 *  唯一约束唯一，但是可以为空
 *  一个表中只能有一个主键，但是可以有多个唯一约束

## （6）默认值 ##

默认值约束用来指定某列的默认值，语法格式为：

字段名 字段类型 DEFAULT 默认值

使用如下：

-- 创建表，指定sex字段默认值为女
    mysql> CREATE TABLE emp8(
        eid INT PRIMARY KEY AUTO_INCREMENT,
        ename VARCHAR(20) UNIQUE,
        sex CHAR(1) DEFAULT '女'
    );
    Query OK, 0 rows affected (0.03 sec)
    -- 插入数据，使用默认值
    mysql> INSERT INTO emp8(eid, ename) VALUES(NULL,'艳秋');
    Query OK, 1 row affected (0.01 sec)
    -- 插入数据，不使用默认值
    mysql> INSERT INTO emp8 VALUES(0,'吴用','男');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> select * from emp8;
    +-----+--------+------+
    | eid | ename  | sex  |
    +-----+--------+------+
    |   1 | 艳秋   | 女   |
    |   2 | 吴用   | 男   |
    +-----+--------+------+
    2 rows in set (0.00 sec)

可以看到，在插入数据不指定有默认值字段的值时，插入的就是设置的默认值。

# 3.数据库事务 #

## （1）事务的基本概念 ##

事务是由一条或者多条SQL 语句组成的一个整体，这些SQL语句要么都执行成功，要么都执行失败。

只要有一条SQL出现异常，整个操作就会回滚，整个业务执行失败。

其中，回滚是指在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态。回滚是在提交之前执行的。

比如: 银行的转账业务，张三给李四转账500元，至少要操作两次数据库，张三-500、李四+500，这中间任何一步出现问题，整个操作就必须全部回滚，这样才能保证用户和银行都没有损失。

现在演示如下：

-- 创建账户表
    mysql> CREATE TABLE account(
    -- 主键
    id INT PRIMARY KEY AUTO_INCREMENT,
    -- 姓名
    NAME VARCHAR(10),
    -- 余额
    money DOUBLE
    );
    Query OK, 0 rows affected (0.01 sec)
    -- 添加两个用户
    mysql> INSERT INTO account (NAME, money) VALUES ('tom', 1000), ('jack', 1000);
    Query OK, 2 rows affected (0.01 sec)
    Records: 2  Duplicates: 0  Warnings: 0
    -- 模拟tom 给 jack 转 500 元钱，一个转账的业务操作最少要执行2步操作
    -- tom账户 -500元
    mysql> UPDATE account SET money = money - 500 WHERE NAME = 'tom';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    -- jack账户 + 500元
    mysql> UPDATE account SET money = money + 500 WHERE NAME = 'jack';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0

这是正常操作结束，假设当tom 账号上 -500 元完成后服务器崩溃或者发生其他异常，jack 的账号并没有+500 元，数据就出现问题了。

所以我们要保证整个事务执行的完整性，即要么都成功、要么都失败，这个时候我们就需要通过操作事务来保证完整性。

## （2）MySQL手动提交事务 ##

MYSQL 中可以有两种方式进行事务的操作：

*  手动提交事务
 *  自动提交事务

手动提交事务的步骤如下：

<table> 
 <thead> 
  <tr> 
   <th>功能</th> 
   <th>语句</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>开启事务</td> 
   <td><code>start transaction;</code>或者<code>BEGIN;</code></td> 
  </tr> 
  <tr> 
   <td>提交事务</td> 
   <td><code>commit;</code></td> 
  </tr> 
  <tr> 
   <td>回滚事务</td> 
   <td><code>rollback;</code></td> 
  </tr> 
 </tbody> 
</table>

其中：

*  START TRANSACTION

这个语句显式地标记一个事务的起始点。

*  COMMIT  
    表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。
 *  ROLLBACK  
    表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

手动提交事务流程：

执行成功的情况： 开启事务 -> 执行多条 SQL 语句 -> 成功提交事务；

执行失败的情况： 开启事务 -> 执行多条 SQL 语句 -> 事务的回滚。

如下图：

![mysql transaction][]

先进行成功案例示意，如下：

-- 恢复数据
    mysql> UPDATE account SET money = money + 500 WHERE NAME = 'tom';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> UPDATE account SET money = money - 500 WHERE NAME = 'jack';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    -- 当前命令行名为窗口1，开启事务
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    -- 执行转账操作
    -- tom账户 -500
    mysql> UPDATE account SET money = money - 500 WHERE NAME = 'tom';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    -- jack账户 +500
    mysql> UPDATE account SET money = money + 500 WHERE NAME = 'jack';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    -- 另开一个命令行名为窗口2，连接数据库并查询
    -- 数据未改变
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |  1000 |
    |  2 | jack |  1000 |
    +----+------+-------+
    2 rows in set (0.00 sec)
    -- 回到窗口1，提交事务
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    -- 窗口2，再次查询
    -- 数据改变
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |   500 |
    |  2 | jack |  1500 |
    +----+------+-------+
    2 rows in set (0.00 sec)

可以看到，在一个session（即命令行窗口）未提交事务时，其他session看不到未提交的事务，在提交之后才能看到，

再进行事务回滚演示：

-- 恢复数据
    mysql> UPDATE account SET money = money + 500 WHERE NAME = 'tom';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> UPDATE account SET money = money - 500 WHERE NAME = 'jack';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    -- 窗口1开启事务
    mysql> start transaction;
    Query OK, 0 rows affected (0.00 sec)
    -- 执行转账操作
    -- tom账户 -500成功
    mysql> UPDATE account SET money = money - 500 WHERE NAME = 'tom';
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    -- jack账户 +500失败
    mysql> UPDATE account SET money = money + 500 WHERE NAME1 = 'jack';
    ERROR 1054 (42S22): Unknown column 'NAME1' in 'where clause'
    -- 窗口2查询
    -- 数据未改变
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |  1000 |
    |  2 | jack |  1000 |
    +----+------+-------+
    2 rows in set (0.00 sec)
    -- 窗口1回滚
    mysql> rollback;
    Query OK, 0 rows affected (0.01 sec)
    -- 窗口1查询
    -- 数据未保存
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |  1000 |
    |  2 | jack |  1000 |
    +----+------+-------+
    2 rows in set (0.00 sec)
    -- 窗口2查询
    -- 数据未保存
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |  1000 |
    |  2 | jack |  1000 |
    +----+------+-------+
    2 rows in set (0.00 sec)

可以看到，在开启事务之后，如果执行过程中发生异常，执行回滚操作，数据就不会被保存；

如果事务中，有某条SQL语句执行时报错，没有手动commit，整个事务也会自动回滚。

可以看到：

如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变；

如果事务中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态。

## （3）MySQL自动提交事务 ##

MySQL 默认每一条 DML（增删改）语句都是一个单独的事务，每条语句都会自动开启一个事务，语句执行完毕自动提交事务。

MySQL 默认开启自动提交事务。

其中`SHOW VARIABLES LIKE 'autocommit';`语句可以查看`autocommit`的状态，其中on表示开启自动提交，off表示关闭自动提交、需要手动提交，可以使用`SET @@autocommit=on/off;`修改自动提交状态。

现在演示如下：

-- 自动提交事务
    -- 窗口1执行语句前自动开启事务，执行语句结束后自动提交事务
    mysql> UPDATE account SET money = money - 500 WHERE NAME = 'tom';
    Query OK, 1 row affected (0.01 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    -- 窗口2查询
    -- 数据已改变
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |   500 |
    |  2 | jack |  1000 |
    +----+------+-------+
    2 rows in set (0.00 sec)
    -- 窗口1查询autocommit状态
    mysql> SHOW VARIABLES LIKE 'autocommit';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | ON    |
    +---------------+-------+
    1 row in set, 1 warning (0.01 sec)
    -- 窗口1关闭autocommit
    mysql> set @@autocommit=off;
    Query OK, 0 rows affected (0.01 sec)
    
    mysql> SHOW VARIABLES LIKE 'autocommit';
    +---------------+-------+
    | Variable_name | Value |
    +---------------+-------+
    | autocommit    | OFF   |
    +---------------+-------+
    1 row in set, 1 warning (0.00 sec)
    -- 再次修改，将jack 账户金额 +500元
    mysql> UPDATE account SET money = money + 500 WHERE NAME = 
    jack';                                                     
    Query OK, 1 row affected (0.00 sec)                        
    Rows matched: 1  Changed: 1  Warnings: 0                   
    -- 窗口2查询
    -- 数据未改变
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |   500 |
    |  2 | jack |  1000 |
    +----+------+-------+
    2 rows in set (0.00 sec)
    -- 手动提交
    mysql> commit;
    Query OK, 0 rows affected (0.01 sec)
    -- 窗口2查询
    -- 数据已改变
    mysql> select * from account;
    +----+------+-------+
    | id | NAME | money |
    +----+------+-------+
    |  1 | tom  |   500 |
    |  2 | jack |  1500 |
    +----+------+-------+
    2 rows in set (0.00 sec)

可以看到，在窗口1关闭自动提交后，需要手动提交数据改变才会生效，

## （4）MySQL事务四大特性ACID ##

MySQL的四大特性如下：

<table> 
 <thead> 
  <tr> 
   <th>特性</th> 
   <th>含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>原子性（Atomicity）</td> 
   <td>每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功， 要么都失败。</td> 
  </tr> 
  <tr> 
   <td>一致性（Consistency）</td> 
   <td>事务在执行前数据库的状态与执行后数据库的状态保持一致。<br>如转账前2个人的总金额是2000，转账后2个人总金额也是2000。</td> 
  </tr> 
  <tr> 
   <td>隔离性（Isolation）</td> 
   <td>事务与事务之间不应该相互影响，执行时保持隔离的状态。</td> 
  </tr> 
  <tr> 
   <td>持久性（Durability）</td> 
   <td>一旦事务执行成功，对数据库的修改是持久的，就算关机，数据也会保存下来。</td> 
  </tr> 
 </tbody> 
</table>

## （5）MySQL事务隔离级别 ##

事务在操作时的理想状态： 所有的事务之间保持隔离，相互独立、互不影响。

一个数据库可能拥有多个访问客户端，这些客户端都可以以并发方式访问数据库，数据库的相同数据可能被多个事务同时访问，可能会导致一些问题，破坏数据的完整性。此时可以通过设置不同的隔离级别来解决对应的问题。

因为并发操作，多个用户同时访问同一个数据，可能引发并发访问的问题，如下：

<table> 
 <thead> 
  <tr> 
   <th>并发访问的问题</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>脏读</td> 
   <td>一个事务读取到了另一个事务中尚未提交的数据</td> 
  </tr> 
  <tr> 
   <td>不可重复读</td> 
   <td>一个事务中两次读取的数据内容不一致，要求的是在一个事务中多次读取时数据是一致的，这是进行 update 操作时引发的问题</td> 
  </tr> 
  <tr> 
   <td>幻读</td> 
   <td>一个事务中，某一次的 select 操作得到的结果所表征的数据状态，无法支撑后续的业务操作，查询得到的数据状态不准确导致幻读</td> 
  </tr> 
 </tbody> 
</table>

通过设置隔离级别，可以防止上面的三种并发问题。  
MySQL数据库有四种隔离级别，从上到下隔离级别逐渐增大：

<table> 
 <thead> 
  <tr> 
   <th>级别</th> 
   <th>名字</th> 
   <th>隔离级别</th> 
   <th>是否出现脏读</th> 
   <th>是否出现不可重复读</th> 
   <th>是否出现幻读</th> 
   <th>数据库默认隔离级别</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>读未提交</td> 
   <td>read uncommited</td> 
   <td>是</td> 
   <td>是</td> 
   <td>是</td> 
   <td></td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>读已提交</td> 
   <td>read commited</td> 
   <td>否</td> 
   <td>是</td> 
   <td>是</td> 
   <td>Oracle和SQL Server</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>可重复读</td> 
   <td>repeatable read</td> 
   <td>否</td> 
   <td>否</td> 
   <td>是</td> 
   <td>MySQL</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>串行化</td> 
   <td>serializable</td> 
   <td>否</td> 
   <td>否</td> 
   <td>否</td> 
   <td></td> 
  </tr> 
 </tbody> 
</table>

需要注意，隔离级别从小到大安全性越来越高，但是效率越来越低，根据不同的情况选择对应的隔离级别。

## （6）隔离级别相关命令 ##

查看隔离级别命令为`select @@tx_isolation;`。

示意如下：

mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set, 1 warning (0.00 sec)

设置隔离级别的命令为：

set global transaction isolation level 级别名称;

其中，级别名称如下：

*  read uncommitted 读未提交
 *  read committed 读已提交
 *  repeatable read 可重复读
 *  serializable 串行化

示意如下：

-- 设置隔离级别
    mysql> set global transaction isolation level read uncommitted;
    Query OK, 0 rows affected (0.01 sec)
    -- 本窗口查看
    -- 未改变
    mysql> select @@tx_isolation;
    +-----------------+
    | @@tx_isolation  |
    +-----------------+
    | REPEATABLE-READ |
    +-----------------+
    1 row in set, 1 warning (0.00 sec)
    -- 新开窗口查看
    -- 已改变
    mysql> select @@tx_isolation;
    +------------------+
    | @@tx_isolation   |
    +------------------+
    | READ-UNCOMMITTED |
    +------------------+
    1 row in set, 1 warning (0.00 sec)

可以看到，设置事务隔离级别后，需要退出MySQL再重新登录或者新登录一个窗口才能生效。

## （7）脏读及解决 ##

脏读是一个事务读取到了另一个事务中尚未提交的数据。

要想实现脏读的效果，需要设置隔离级别为读未提交（最低）。

脏读非常危险的，比如张三向李四购买商品，张三开启事务，向李四账号转入 500 块，然后打电话给李四说钱 已经转了。李四一查询钱到账了，发货给张三。张三收到货后回滚事务，李四的再查看钱没了。

所以必须解决脏读，将全局的隔离级别进行提升为`read committed`即可。

## （8）不可重复读及解决 ##

不可重复读是同一个事务中，进行查询操作，但是每次读取的数据内容是不一样的。

要想实现脏读的效果，需要设置隔离级别为读已提交。

不可重复读也存在较严重的问题，比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户，结果在一个事务中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了。

解决不可重复读可以将全局的隔离级别提升为`repeatable read`，保证同一个事务中多次查询数据一致。

## （9）幻读及解决 ##