MySQL数据库 - 单表&约束&事务

1. MySQL单表

1.1 DQL_排序查询

SELECT 字段名 FROM 表名 [WHERE 字段 = 值] ORDER BY 字段名 [ASC / DESC]

• ASC 表示升序排序(默认)
• DESC 表示降序排序

1.1.1 单列排序

• 只按照某一个字段进行排序, 就是单列排序

-- 使用 salary 字段,对emp 表数据进行排序 (升序/降序)

-- 默认升序排序 ASC
SELECT * FROM emp ORDER BY salary;
-- 降序排序
SELECT * FROM emp ORDER BY salary DESC;

1.1.2 组合排序

• 同时对多个字段进行排序, 如果第一个字段相同 就按照第二个字段进行排序,以此类推

-- 在薪水排序的基础上,再使用id进行排序, 如果薪水相同就以id 做降序排序

-- 组合排序
SELECT * FROM emp ORDER BY salary DESC, eid DESC;

1.2 DQL_聚合函数

格式：

SELECT 聚合函数(字段名) FROM 表名

1.2.1 count 查询总数

-- 统计表中的记录条数 使用 count()

SELECT COUNT(eid) FROM emp; -- 使用某一个字段
SELECT COUNT(*) FROM emp; -- 使用 *
SELECT COUNT(1) FROM emp; -- 使用 1,与 * 效果一样

1.2.2 Sum,Max,Min,Avg

-- sum函数求和, max函数求最大, min函数求最小, avg函数求平均值

SELECT
SUM(salary) AS ‘总薪水‘,
MAX(salary) AS ‘最高薪水‘,
MIN(salary) AS ‘最低薪水‘,
AVG(salary) AS ‘平均薪水‘
FROM emp;

1.2.3 条件查询+聚合函数(count)

# 查询薪水大于4000员工的个数
SELECT COUNT(*) FROM emp WHERE salary > 4000;

# 查询部门为‘教学部‘的所有员工的个数
SELECT COUNT(*) FROM emp WHERE dept_name = ‘教学部‘;

# 查询部门为‘市场部‘所有员工的平均薪水
SELECT
AVG(salary) AS ‘市场部平均薪资‘
FROM emp
WHERE dept_name = ‘市场部‘;

1.3 DQL_分组查询

• 分组查询指的是使用 GROUP BY 语句,对查询的信息进行分组,相同数据作为一组

SELECT 分组字段/聚合函数 FROM 表名 GROUP BY 分组字段 [HAVING 条件];

• 分组时可以查询要分组的字段, 或者使用聚合函数进行统计操作.
• *查询其他字段没有意义
• where 和 having的区别

1.4 DQL_limit

limit关键字的作用：

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

语法结构：

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

• 关键字(Limit offset, length)可以接受一个或者两个为0 或者正整数的参数
• offset 起始行数, 从0开始记数, 如果省略 则默认为 0
• length 返回的行数

示例：

# 查询emp表中的前 5条数据

-- 参数1 起始值,默认是0 , 参数2 要查询的条数
SELECT * FROM emp LIMIT 5;
SELECT * FROM emp LIMIT 0 , 5;

# 查询emp表中 从第4条开始,查询6条

-- 起始值默认是从0开始的.
SELECT * FROM emp LIMIT 3 , 6;

分页操作：

-- 分页操作 每页显示3条数据
SELECT * FROM emp LIMIT 0,3; -- 第1页
SELECT * FROM emp LIMIT 3,3; -- 第2页 2-1=1 1*3=3
SELECT * FROM emp LIMIT 6,3; -- 第三页

-- 分页公式 起始索引 = (当前页 - 1) * 每页条数

2 约束

约束的作用：

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

常见约束：

2.1 主键约束

2.1.1 特点：

• 不可重复
• 唯一
• 非空

2.1.2 作用：

• 用来表示数据库中的每一条记录

2.1.3 语法格式：

字段名 字段类型 primary key

2.1.4 创建主键的三种方式：

# 方式1 创建一个带主键的表
CREATE TABLE emp2(
-- 设置主键 唯一 非空
eid INT PRIMARY KEY,
ename VARCHAR(20),
sex CHAR(1)
);

-- 方式2 创建一个带主键的表
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1),
-- 指定主键为 eid字段
PRIMARY KEY(eid)
);

-- 方式3 创建一个带主键的表
CREATE TABLE emp2(
eid INT ,
ename VARCHAR(20),
sex CHAR(1)
)
-- 创建的时候不指定主键,然后通过 DDL语句进行设置
ALTER TABLE emp2 ADD PRIMARY KEY(eid);

2.1.5 可以做主键的字段

• 通常针对业务去设计主键,每张表都设计一个主键id
• 主键是给数据库和程序使用的,跟最终的客户无关,所以主键没有意义没有关系,只要能够保证不重复 就好,比如 身份证就可以作为主键

2.1.6 删除主键约束

-- 使用DDL语句 删除表中的主键
ALTER TABLE emp2 DROP PRIMARY KEY;

2.1.7 主键的自增

• 主键如果让我们自己添加很有可能重复,我们通常希望在每次插入新记录时,数据库自动生成主键字段的值
• 关键字AUTO_INCREMENT 表示自动增长(字段类型必须是整数类型)

2.1.8 修改主键自增的起始值

• 默认地 AUTO_INCREMENT 的开始值是 1

-- 创建主键自增的表,自定义自增起始值
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20),
sex CHAR(1)
)AUTO_INCREMENT=100;

2.1.9 DELET和TRUNCAT对自增的影响

• 假设目前最后的主键值为101
• delete删除表中数据，对自增没影响，插入数据主键自增为102
• truncate删除表中数据，插入数据主键从1开始自增

2.2 非空约束

语法格式：某一列不允许为空

字段名 字段类型 not null

示例：

# 非空约束
CREATE TABLE emp2(
eid INT PRIMARY KEY AUTO_INCREMENT,
-- 添加非空约束, ename字段不能为空
ename VARCHAR(20) NOT NULL,
sex CHAR(1)
);

2.3 唯一约束

语法格式：表中某一列的值不能重复（对null不做唯一的判断）

字段名 字段值 unique

示例：

#创建emp3表 为ename 字段添加唯一约束
CREATE TABLE emp3(
eid INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(20) UNIQUE,
sex CHAR(1)
);

2.3.1 主键约束和唯一约束的区别

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

2.4 外键约束

2.5 默认值

语法格式：用来指定某列的默认值

字段名 字段类型 DEFAULT 默认值

插入数据是可以不声明或者使用Default

3 数据库事务

3.1 事务基本概念

事务的概念：

• 事务是一个整体,由一条或者多条SQL 语句组成,这些SQL语句要么都执行成功,要么都执行失败, 只要有 一条SQL出现异常,整个操作就会回滚,整个业务执行失败

回滚：

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

3.2 MySQL事务操作

2种方式进行事务的提交：

• 手动提交事务
• 自动提交事务

3.2.1 手动提交事务

3.2.1.1 语法格式：

START TRANSACTION 

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

COMMIT

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

ROLLBACK

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

3.2.1.2 手动提交事务的流程

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

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

• 如果事务中 SQL 语句没有问题，commit 提交事务，会对数据库数据的数据进行改变。 如果事务 中 SQL 语句有问题，rollback 回滚事务，会回退到开启事务时的状态

3.2.2 自动提交事务

3.2.2.1 概念

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

3.2.2.2 取消自动提交

• 查看autocommit状态

SHOW VARIABLES LIKE ‘autocommit‘;

• 把 autocommit 改成 of

SET @@autocommit=off;

3.3 事务的四大特性 （ACID）

3.4 MySQL事务隔离级别

3.4.1 数据并发访问

• 一个数据库可能拥有多个访问客户端,这些客户端都可以并发方式访问数据库. 数据库的相同数据可能 被多个事务同时访问,如果不采取隔离措施,就会导致各种问题, 破坏数据的完整性

3.4.2 并发访问会产生的问题

• 事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。
• 因为并发操作，多个用户同时访问 同一个 数据。可能引发并发访问的问题

3.4.3 并发访问会产生的问题

3.4.4 四种隔离级别

• 通过设置隔离级别,可以防止上面的三种并发问题.
• MySQL数据库有四种隔离级别 上面的级别最低，下面的级别最高
• ? 不会出现问题
• ? 会出现问题

• 隔离级别从小到大(1~4)，安全性是越来越高的，但是效率是越来越低的

3.4.5 隔离级别的相关命令

3.4.5.1 查看隔离级别

select @@tx_isolation;

3.4.5.2 设置事务隔离级别

set global transaction isolation level 级别名称;
read uncommitted 读未提交
read committed 读已提交
repeatable read 可重复读
serializable 串行化

• 需退出MySQL重新登录

示例：修改隔离级别为 读未提交

set global transaction isolation level read uncommitted;

3.4.6 隔离性问题

3.4.6.1 脏读

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

解决方案：

• 将全局的隔离级别进行提升为: read committed

3.4.6.2 不可重复读

• 不可重复读: 同一个事务中,进行查询操作,但是每次读取的数据内容是不一样的
• 两次查询输出的结果不同，不知道以哪次为准
• 比如银行程序需要将查询结果分别输出到电脑屏幕和发短信给客 户，结果在一个事务 中针对不同的输出目的地进行的两次查询不一致，导致文件和屏幕中的结果不一致，银行工作人员就不知道以哪个为准了

解决方案：

• 将全局的隔离级别进行提升为： repeatable read

3.4.6.3 幻读

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

解决方案：

• 如果一个事务，使用了SERIALIZABLE——可串行化隔离级别时，在这个事务没有被提交之前 , 其 他的线程，只能等到当前操作完成之后，才能进行操作，这样会非常耗时，而且，影响数据库的 性能，数据库不会使用这种隔离级别
• serializable 串行化可以彻底解决幻读,但是 事务只能排队执行,严重影响效率, 数据库不会使用这种隔离级别

MySQL数据库 - 单表&约束&事务