大数据开发基础入门与项目实战(二)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;

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

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 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的四大特性如下:

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

(5)MySQL事务隔离级别

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

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

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

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

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

级别名字隔离级别是否出现脏读是否出现不可重复读是否出现幻读数据库默认隔离级别
1读未提交read uncommited
2读已提交read commitedOracle和SQL Server
3可重复读repeatable readMySQL
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特性。

评论 4
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

东哥说AI

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值