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

前言

本文主要介绍了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个聚合函数如下：

聚合函数	作用
count(字段)	统计指定列不为NULL的记录行数
sum(字段)	计算指定列的数值和
max(字段)	计算指定列的最大值
min(字段)	计算指定列的最小值
avg(字段)	计算指定列的平均值

使用如下：

# 查询员工的总数
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;；

分页公式为$起始索引(offset)=(当前页码-1)\ast 每页显示条数$。

2.SQL约束

（1）约束的介绍

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

常见的约束如下：

约束名	约束关键字
主键	primary key
唯一	unique
非空	not null
外键	foreign key

（2）主键约束

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

语法格式如下：

字段名 字段类型 primary key

添加主键有3种方式：

• 创建表时，在声明字段时即指定主键

• 创建表时，最后指定主键

• 通过DDL语句给已经创建好的表添加主键约束

使用如下：

-- 方式一：创建一个带有主键的表，声明字段时指定
mysql> CREATE TABLE emp2(
    eid INT PRIMARY KEY,
    ename VARCHAR(20),
    sex CHAR(1)
);
Query OK, 0 rows affected (0.05 sec)

mysql> desc emp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid   | int(11)     | NO   | PRI | NULL    |       |
| ename | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
-- 方式二：创建一个带有主键的表，最后指定
mysql> CREATE TABLE emp3(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1),
    PRIMARY KEY(eid)
);
Query OK, 0 rows affected (0.03 sec)

mysql> desc emp3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid   | int(11)     | NO   | PRI | NULL    |       |
| ename | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
-- 方式三：创建表之后，再设置主键，即通过DDL语句添加主键约束
mysql> alter table emp add primary key(eid);                    
Query OK, 0 rows affected (0.07 sec)                            
Records: 0  Duplicates: 0  Warnings: 0                          
                                                                
mysql> desc emp;                                                
+-----------+-------------+------+-----+---------+-------+      
| Field     | Type        | Null | Key | Default | Extra |      
+-----------+-------------+------+-----+---------+-------+      
| eid       | int(11)     | NO   | PRI | NULL    |       |      
| ename     | varchar(20) | YES  |     | NULL    |       |      
| sex       | char(1)     | YES  |     | NULL    |       |      
| salary    | double      | YES  |     | NULL    |       |      
| hire_date | date        | YES  |     | NULL    |       |      
| dept_name | varchar(20) | YES  |     | NULL    |       |      
+-----------+-------------+------+-----+---------+-------+      
6 rows in set (0.01 sec)                                        
                                                                

可以看到，通过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;。

使用如下：

mysql> alter table emp2 drop primary key;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> desc emp2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| eid   | int(11)     | NO   |     | NULL    |       |
| ename | varchar(20) | YES  |     | NULL    |       |
| sex   | char(1)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> INSERT INTO emp2 VALUES(1,'孙二娘','女');
Query OK, 1 row affected (0.00 sec)

可以看到，在删除主键后，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删除表中所有数据对自增长的影响：

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

现在验证如下：

- 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 中可以有两种方式进行事务的操作：

• 手动提交事务

• 自动提交事务

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

功能	语句
开启事务	start transaction;或者BEGIN;
提交事务	commit;
回滚事务	rollback;

其中：

• START TRANSACTION

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

• COMMIT
  表示提交事务，即提交事务的所有操作，具体地说，就是将事务中所有对数据库的更新都写到磁盘上的物理数据库中，事务正常结束。

• ROLLBACK
  表示撤销事务，即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，回滚到事务开始时的状态

手动提交事务流程：

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

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

如下图：

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

-- 恢复数据
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的四大特性如下：

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

（5）MySQL事务隔离级别

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

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

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

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

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

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

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

（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）幻读及解决

幻读是select 某记录是否存在，不存在时插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。

解决幻读可以将事务隔离级别设置到SERIALIZABLE（最高），以防止幻读的发生。

如果一个事务，使用了SERIALIZABLE可串行化隔离级别时，在这个事务没有被提交之前 , 其他的线程只能等到当前操作完成之后，才能进行操作，这样会非常耗时，影响数据库的性能，数据库一般不会使用这种隔离级别。

总结

单表查询是关联查询和子查询的基础；数据库约束在保证数据的完整性方面发挥了极大的作用；事务也保证了数据的ACID特性。