数据库技术：MySQL 基础和 SQL 入门，单表、约束和事务

RendaZhang

发布于 2020-09-08 15:30:58

1.5K0

发布于 2020-09-08 15:30:58

文章被收录于专栏：RendaRenda

Introduction to MySQL and SQL

Basic Concepts

What is Database?

数据库就是存储和管理数据的仓库。
数据库是一个文件系统。它以文件的方式将数据保存在电脑上。

Why Should We Use Database?

Comparison among different ways of storing data：

内存

优点：速度快。
缺点：不能永久保存，数据是临时状态的。

文件

优点：数据是可以永久保存。
缺点：使用 IO 流操作文件，所以不方便。

数据库

优点：1. 数据可以永久保存；2. 方便存储和管理数据；3. 使用统一的方式操作数据库。
缺点：占用资源，有些数据库需要付费，如 Oracle 数据库。

Conclusion: By using Database, it greatly improves user experience for Data Manipulations, such as Adding, Deleting, or Querying data.

Common Database Software

MySQL 数据库：开源免费的数据库。操作简单，常作为中小型的项目的数据库首选，但是被 Oracle 公司收购后，MySQL 6.x 开始收费。

Oracle 数据库：收费的大型数据库，Oracle 公司的核心产品。安全性高。

DB2：收费的超大型数据库，IBM 公司的数据库产品。常在银行系统使用。

SQL Server：收费的中型数据库，微软公司的产品。常用在 C#，.net 等语言。该数据库只能运行在 Windows 机器上；扩展性，稳定性，安全性，性能都表现平平。

Why Do We Choose MySQL as the Database Software?

功能强大，可以用于 Web 应用开发。
开源，免费。

使用 MySQL

下载安装 MySQL，并配置好相应的环境变量

使用 Windows 服务或者 DOS 命令方式启动 MySQL 服务

使用命令行管理数据库

mysql -u用户名 -p密码
mysql -h主机IP -u用户名 -p密码

使用 `SQLyog` 图形化软件管理登录 MySQL 数据库

MySQL Directory Structure

MySQL Installation Directory

bin：放置一些可执行文件
docs: 文档
include: 包含（头）文件
lib: 依赖库
share: 用于存放字符集、语言等信息

MySQL Configuration File and the Directory of Databases and Data Tables

my.ini 文件：是 MySQL 的配置文件，一般不建议去修改。
Data 目录：MySQL 管理的数据库文件所在的目录，保存的就是数据库（文件夹）与数据表（文件）的信息。

Database Management System

数据库管理系统指一种操作和管理维护数据库的大型软件。

MySQL 就是一个数据库管理系统软件，安装了 MySQL 的电脑，我们叫它数据库服务器。

数据库管理系统用于建立、使用和维护数据库，对数据库进行统一的管理。

MySQL 中管理着很多数据库，在实际开发环境中一个数据库一般对应了一个的应用，数据库当中保存着多张表，每一张表对应着不同的业务，表中保存着对应业务的数据。

安装了 MySQL 软件的电脑被称为 MySQL 服务器
MySQL 中管理着多个数据库
数据库中包含多张表
表中包含多条数据
客户端（命令行或 SQLyog）通过网络访问（服务器地址 : 端口）MySQL 服务器

Database Tables

数据库中以表为组织单位存储数据

表类似 Java 中的类,每个字段都有对应的数据类型

对比 Java 程序与关系型数据库

类 = 表
类中属性 = 表中字段
对象 = 数据记录

SQL

SQL Concept

SQL - Structured Query Language，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

Functions of SQL:

是所有关系型数据库的统一查询规范，不同的关系型数据库都支持 SQL
所有的关系型数据库都可以使用 SQL
不同数据库之间的 SQL 有一些区别方言

SQL Syntax

SQL 语句可以单行或者多行书写，以分号结尾 ; （Sqlyog 中可以不用写分号，但建议加上）

可以使用空格和缩进来增加语句的可读性。

MySQL 中使用 SQL 不区分大小写，一般关键字大写，数据库名表名列名小写。

MySQL 注释方式：

# show databases;  单行注释

-- show databases; 单行注释

/*
    多行注释
    show databases;
*/

SQL 的分类

DDL - Data Deﬁnition Language：数据定义语言，用来定义数据库对象：数据库，表，列等。

DML - Data Manipulation Language：数据操作语言，用来对数据库中表的记录进行更新。

DQL - Data Query Language：数据查询语言，用来查询数据库中表的记录。

DCL - Date Control Language：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。

/* 
    CRUD
    C - create
    R - retrieve
    U - update
    D - delete
*/

DDL - Define Database

MySQL Built-in Database

information_schema：信息数据库，保存的是其它数据库的信息

mysql：MySQL 核心数据库，保存的是用户和权限

performance_schema：保存性能相关的数据，监控 MySQL 的性能

sys：记录了 DBA（Database Administrator）所需要的一些信息，更方便地让 DBA 快速了解数据库的运行情况。

Create Database

/*
    方式1 直接指定数据库名进行创建
    默认数据库字符集为：latin1
*/
CREATE DATABASE db1; 

/*
    方式2 指定数据库名称，指定数据库的字符集
    一般都指定为 utf8,与Java中的编码保持一致
*/
CREATE DATABASE db1_1 CHARACTER SET utf8;

create database 数据库名; -- 创建指定名称的数据库。

create database 数据库名 character set 字符集; -- 创建指定名称的数据库，并且指定字符集（一般都指定 UTF-8）。

Show Database

-- 切换数据库 从db1 切换到 db1_1
USE db1_1;

-- 查看当前正在使用的数据库
SELECT DATABASE();

-- 查看Mysql中有哪些数据库
SHOW DATABASES;

-- 查看一个数据库的定义信息
SHOW CREATE DATABASE db1_1;

use 数据库; -- 切换数据库

select database(); -- 查看当前正在使用的数据库

show databases; -- 查看 MySQL 中都有哪些数据库

show create database 数据库名; -- 查看一个数据库的定义信息

Alter Database

-- 将数据库db1 的字符集 修改为 utf8
ALTER DATABASE db1 CHARACTER SET utf8;

-- 查看当前数据库的基本信息，发现编码已更改
SHOW CREATE DATABASE db1;

alter database 数据库名 character set 字符集; -- 数据库的字符集修改操作

Drop Database

-- 删除某个数据库
DROP DATABASE db1_1;

drop database 数据库名; -- 从 MySQL 中永久的删除某个数据库

DDL - Define Table

MySQL Data Type

常用数据类型

int（整型），double（浮点型）
char（字符串型固定长度），varchar（字符串型只使用字符串长度所需的空间），text（字符串型存储文本）
date（日期类型 yy-MM-dd），datetime（日期类型 yyyy-MM-dd HH:mm:ss），timestamp（日期类型自动存储记录修改时间）

Create Table

-- 创建测试表
CREATE TABLE test1(
    tid INT,
    tname VARCHAR(20),
    tdate DATE -- 注意最后不要加逗号
);

-- 创建一个表结构与 test1 相同的 test2表
CREATE TABLE test2 LIKE test1;

-- 查看表结构
DESC test2;

Show Table

-- 查看当前数据库中的所有表名
SHOW TABLES;

-- 显示当前数据表的结构
DESC test1;

-- 查看创建表的 SQL 语句
SHOW CREATE TABLE test1;

Drop Table

-- 直接删除 test1 表
DROP TABLE test1;

-- 先判断 再删除 test2 表
DROP TABLE IF EXISTS test2;

Alter Table

--  将 category 表改为 category1
RENAME TABLE category 
TO category1;

-- 将 category 表的字符集 修改为 gbk
alter table category 
character set gbk;

-- 为分类表添加一个新的字段为分类描述 cdesc varchar(20)
ALTER TABLE category 
ADD cdesc VARCHAR(20);

-- 对分类表的描述字段进行修改，类型 varchar(50) 
ALTER TABLE category 
MODIFY cdesc VARCHAR(50);

-- 对分类表中的 desc 字段进行更换, 更换为 cdesc2 varchar(30)
ALTER TABLE category 
CHANGE cdesc cdesc2 VARCHAR(30);

--  删除分类表中 cdesc2 列
ALTER TABLE category 
DROP cdesc2;

Data Manipulation Language

# 创建学生表
CREATE TABLE student(
    sid INT, # 学员ID
    sname VARCHAR(20), # 姓名
    age INT, # 年龄
    sex CHAR(1), # 性别
    address VARCHAR(40) # 地址
);

Insert Data

-- 插入全部字段，将所有字段名都写出来
INSERT INTO student (sid,sname,age,sex,address) 
    VALUES(1,'张人大',20,'男','广州');

-- 插入全部字段，不写字段名
INSERT INTO student 
    VALUES(2,'李小明',10,'男','上海');

-- 插入指定字段的值，必须要写上列名
INSERT INTO student (sid, sname) 
    VALUES(3,'周小红');

-- 如果插入空值，可以忽略不写或者写 null
INSERT INTO student (sid,sname) 
    VALUES(4,'赵四');
INSERT INTO student (sid,sname,age,sex,address) 
    VALUES(5,'钱五',NULL,NULL,NULL);

Update Data

-- 不带条件修改，将所有的性别改为女，慎用
UPDATE student SET sex = '女';

-- 带条件的修改，将 sid 为 3 的学生，性别改为女
UPDATE student SET sex = '女' 
WHERE sid = 3;

-- 一次修改多个列， 将 sid 为 2 的学员，年龄改为 18，地址改为北京
UPDATE student 
    SET age = 18, address = '北京' 
WHERE sid = 2;

Delete Data

-- 删除 sid 为 1 的数据
DELETE FROM student WHERE sid = 1;

-- 删除所有数据方法一：有多少条记录，就执行多少次删除操作，效率低
DELETE FROM student;

-- 删除所有数据方法二：先删除整张表，然后再重新创建一张一模一样的表，效率高
truncate table student;

Data Query Language

-- 创建员工表
CREATE TABLE emp(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1),
    salary DOUBLE,
    hire_date DATE,
    dept_name VARCHAR(20)
);

-- 添加数据
...

Simple Query

-- 查询 emp 中的所有数据，使用 * 表示所有列
SELECT * FROM emp;

-- 查询 emp 表中的所有记录，仅显示 id 和 name 字段
SELECT eid, ename FROM emp;

-- 别名查询，使用关键字 as 为列起别名
SELECT 
    eid AS '编号',
    ename AS '姓名' ,
    sex AS '性别',
    salary AS '薪资',
    hire_date '入职时间', -- AS 可以省略
    dept_name '部门名称'
FROM emp;

-- 使用distinct 关键字,去掉重复部门信息
SELECT DISTINCT dept_name 
FROM emp;

-- 运算查询，查询结果参与运算
SELECT ename, salary + 1000 
FROM emp;
SELECT 
    ename AS '姓名', 
    salary + 1000 AS '薪资' 
FROM emp;

Conditional Query

比较运算符

> < <= >= = 大于、小于、小于等于、大于等于、等于
<>，!= 不等于
BETWEEN ... AND ... 显示在某一区间的值
IN (集合) 集合表示多个值，使用逗号分隔，in 中的每个数据都会作为一次条件，只要满足条件就会显示
LIKE '%张%' 模糊查询
IS NULL 查询某一列为 NULL 的值，注：不能写 = NULL

逻辑运算符

And && 多个条件同时成立
Or || 多个条件任一成立
Not 不成立，取反

模糊查询

% 表示匹配任意多个字符串
_ 表示匹配一个字符

-- 查询员工姓名为'张人大'的员工信息
SELECT * 
FROM emp 
WHERE ename = '张人大';

-- 查询薪水价格为 3000 的员工信息
SELECT * 
FROM emp 
WHERE salary = 3000;

-- 查询薪水价格不是 5000 的所有员工信息
SELECT * 
FROM emp 
WHERE salary != 5000;
SELECT * 
FROM emp 
WHERE salary <> 5000;

-- 查询薪水价格大于 6000 元的所有员工信息
SELECT * 
FROM emp 
WHERE salary > 6000;

-- 查询薪水价格在 5000 到 10000 之间所有员工信息
SELECT * 
FROM emp 
WHERE salary 
    BETWEEN 5000 AND 10000;

-- 查询薪水价格是 3000 或 8000 或者 20000 的所有员工信息
# 方法 1: or
SELECT * FROM emp 
WHERE salary = 3000 
    OR salary = 8000 
    OR salary = 20000;
# 方法 2: in() 匹配括号中指定的参数
SELECT * 
FROM emp 
WHERE salary IN(3000, 8000, 20000);

-- 查询含有'人'字的所有员工信息
SELECT * 
FROM emp 
WHERE ename LIKE '%人%';

-- 查询以'张'开头的所有员工信息
SELECT * 
FROM emp 
WHERE ename LIKE '张%';

-- 查询第二个字为'莱'的所有员工信息
SELECT * 
FROM emp 
WHERE ename LIKE '_莱%';

-- 查询没有部门的员工信息
SELECT * 
FROM emp 
WHERE dept_name IS NULL;
-- 错误方式，不可以用 = 来判断是否为空
# SELECT * FROM emp WHERE dept_name = NULL; 

-- 查询有部门的员工信息
SELECT * 
FROM emp 
WHERE dept_name IS NOT NULL;

MySQL: Single Table，Constraint and Transaction

Single Table Operation

Sorting

单列排序

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

-- 使用 salary 字段，对 emp 表数据进行排序 (升序/降序)
# 默认升序排序 ASC
SELECT * FROM emp 
ORDER BY salary;
# 降序排序
SELECT * FROM emp 
ORDER BY salary DESC;

组合排序

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

-- 在薪水排序的基础上, 再使用 eid 进行排序, 如果薪水相同就以 eid 做降序排序
# 组合排序
SELECT * FROM emp 
ORDER BY salary DESC, eid DESC;

Aggregate Function

`count` (字段) -- 统计指定列不为 `NULL` 的记录行数

-- 查询员工的总数
# 统计表中的记录条数
SELECT COUNT(eid) FROM emp;
SELECT COUNT(*) FROM emp;
# 使用 1，与 * 效果一样
SELECT COUNT(1) FROM emp;

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

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

`sum` (字段) -- 计算指定列的数值和

`max` (字段) -- 计算指定列的最大值

`min` (字段) -- 计算指定列的最小值

`avg` (字段) -- 计算指定列的平均值

-- 查看员工总薪水、最高薪水、最小薪水、薪水的平均值
SELECT 
    SUM(salary) AS '总薪水',
    MAX(salary) AS '最高薪水',
    MIN(salary) AS '最低薪水',
    AVG(salary) AS '平均薪水'   
FROM emp;

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

Query by Group

分组查询指的是使用 GROUP BY 语句，对查询的信息进行分组，相同数据作为一组。分组时可以查询要分组的字段，或者使用聚合函数进行统计操作。

-- 按照性别进行分组操作。
# 使用 * 会让所有字段显示
SELECT * 
FROM emp 
GROUP BY sex;

-- 通过性别字段进行分组，求各组的平均薪资
SELECT sex, AVG(salary) 
FROM emp 
GROUP BY sex;

-- 查询有几个部门
SELECT dept_name AS '部门名称' 
FROM emp 
GROUP BY dept_name;

-- 查询每个部门的平均薪资
SELECT 
dept_name AS '部门名称',
AVG(salary) AS '平均薪资' 
FROM emp 
GROUP BY dept_name;

-- 查询每个部门的平均薪资, 且部门名称不为 null
SELECT 
    dept_name AS '部门名称',
    AVG(salary) AS '平均薪资' 
FROM emp 
WHERE dept_name IS NOT NULL 
GROUP BY dept_name;

分组操作中的 having 子语句，是用于在分组后对数据进行过滤的，作用类似于 where 条件。where 与 having 的区别：1. where 进行分组前的过滤，having 是分组后的过滤；2. where 后面不能写聚合函数，having 后面可以写。

-- 查询平均薪资大于6000的部门
# 需要在分组后再次进行过滤，使用 having
SELECT 
    dept_name,
    AVG(salary)
FROM emp  
WHERE dept_name IS NOT NULL 
GROUP BY dept_name 
HAVING AVG(salary) > 6000 ;

Limit

Limit 关键字用于限制返回的查询结果的行数 (可以通过 limit 指定查询多少行数据)，是 MySQL 的方言，用来完成分页。分页公式：起始索引 = (当前页 - 1) * 每页条数。

-- 查询 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 条数据
# 第 1 页
SELECT * FROM emp LIMIT 0, 3;
# 第 2 页 (2-1)*3=3
SELECT * FROM emp LIMIT 3, 3;
# 第 3 页
SELECT * FROM emp LIMIT 6, 3;

Constraint

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

`Primary Key` -- 主键约束

主键约束是不可重复的、唯一的、非空的，用来表示数据库中的每一条记录。通常针对业务去设计主键，每张表都设计一个主键 id。主键是给数据库和程序使用的，与客户的要求无关，所以只要能够保证主键不重复即可。

-- 方式 1：直接设置主键
CREATE TABLE emp1(
    # 
    eid INT PRIMARY KEY,
    ename VARCHAR(20),
    sex CHAR(1)
);

-- 方式 2：指定主键为 eid 字段
CREATE TABLE emp2(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1),
    PRIMARY KEY(eid)
);

-- 方式 3：通过数据定义语言进行主键设置
CREATE TABLE emp3(
    eid INT,
    ename VARCHAR(20),
    sex CHAR(1)
)
ALTER TABLE emp3 ADD PRIMARY KEY(eid);
DESC emp3;

-- 测试主键的唯一性和非空性
# 正常插入一条数据
INSERT INTO emp3 VALUES(1,'张人大','男');
# 错误：主键不能为空
INSERT INTO emp3 VALUES(NULL,'布莱尔','女');
# 错误：主键不能重复
INSERT INTO emp3 VALUES(1,'李小明','男');

-- 使用数据定义语言语句删除表中的主键
ALTER TABLE emp3 DROP PRIMARY KEY;
DESC emp3;

-- 创建主键自增的表
# 关键字 AUTO_INCREMENT，类型必须是整数类型
CREATE TABLE emp4(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
);
#  添加数据观察主键的自增，默认开始值为 1。
INSERT INTO emp4(ename,sex) VALUES('张人大','男');
INSERT INTO emp4(ename,sex) VALUES('李小明','男');
INSERT INTO emp4 VALUES(NULL,'布莱尔','女');
INSERT INTO emp4 VALUES(NULL,'周小红','女');

-- 自定义自增其实值
CREATE TABLE emp5(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20),
    sex CHAR(1)
) AUTO_INCREMENT = 100;
# 插入数据,观察主键的起始值
INSERT INTO emp5(ename,sex) VALUES('张人大','男');
INSERT INTO emp5(ename,sex) VALUES('布莱尔','女');

-- 使用 delete 删除表中所有数据
# 目前最后的主键值是 101
SELECT * FROM emp5;
# delete 删除表中数据，对自增没有影响
DELETE FROM emp5;
# 插入数据，发现主键从 102 继续自增
INSERT INTO emp5(ename,sex) VALUES('张人大','男');
INSERT INTO emp5(ename,sex) VALUES('布莱尔','女');

-- 使用 truncate 删除表中所有数据,
TRUNCATE TABLE emp5;
-- 插入数据，发现主键从 1 重新自增
INSERT INTO emp5(ename,sex) VALUES('张人大','男');
INSERT INTO emp5(ename,sex) VALUES('布莱尔','女');

`Not Null` -- 非空约束

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

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

`Unique` -- 唯一约束

唯一约束的特点：表中的某一列的值不能重复（对 null 不做唯一的判断）。

主键约束与唯一约束的区别：1. 主键约束是唯一的且不能够为空，唯一约束是唯一的但可以为空；2. 一个表中只能有一个主键，但是可以有多个唯一约束。

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

-- 测试唯一约束
# 添加一条数据
INSERT INTO emp7(ename,sex) VALUES('布莱尔','男');
# 错误：ename 不能重复
INSERT INTO emp7(ename,sex) VALUES('布莱尔','女');

`Foreign Key` -- 外键约束

外键约束将在多表中介绍。

`Default` -- 默认值约束

默认值约束用来指定某列的默认值

-- 为 ename 字段添加默认值
CREATE TABLE emp8(
    eid INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCHAR(20) DEFAULT '无名氏',
    sex CHAR(1)
);

-- 测试默认值
INSERT INTO emp8(ename,sex) VALUES(DEFAULT,'男');
INSERT INTO emp8(sex) VALUES('女');
# 不使用默认值，则覆盖默认值。
INSERT INTO emp8(ename,sex) VALUES('布莱尔','女');

Database Transaction

What is Database Transaction?

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

Database Rollback

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

Account Transfer Example

-- 创建账户表
CREATE TABLE account(
    # 主键
    id INT PRIMARY KEY AUTO_INCREMENT,
    # 姓名
    NAME VARCHAR(10),
    # 余额
    money DOUBLE
);

-- 添加两个用户
INSERT INTO account(NAME, money) 
VALUES ('张人大', 1000), ('布莱尔', 1000);

-- 模拟张人大给布莱尔转 500 元钱
# 张人大账户减去 500 元
UPDATE account SET money = money - 500 WHERE NAME = '张人大';
# 布莱尔账户增加 500 元
UPDATE account SET money = money + 500 WHERE NAME = '布莱尔';

-- 假设当张人大减去了钱，然后服务器崩溃。
-- 布莱尔的账号并没有收到钱，数据就出现问题。
-- 所以要保证整个事务执行的完整性，要么都成功，要么都失败。

MySQL Transaction Operations

MySQL 中可以有两种方式进行事务的操作：手动或自动提交事务。

手动提交事务

开启事务 - start transaction; 或 BEGIN;

提交事务 - commit;

回滚事务 - rollback;

-- 执行成功的情况：开启事务 -> 执行多条 SQL 语句 -> 成功提交事务
# 开启事务
start transaction;
# 张人大账户减去 500 元
UPDATE account SET money = money - 500 WHERE NAME = '张人大';
# 布莱尔账户增加 500 元，此时使用 SQLyog 查看表，发现数据并没有改变
UPDATE account SET money = money + 500 WHERE NAME = '布莱尔';
# 执行 commit 提交事务，此时发现数据发生改变
commit;

-- 执行失败的情况：开启事务 -> 执行多条 SQL 语句 -> 事务的回滚
start transaction;
INSERT INTO account VALUES(NULL,'张人大',3000);
INSERT INTO account VALUES(NULL,'张人大',3500);
# 此时不去提交事务直接退出程序，发生回滚操作，数据没有改变
rollback;

自动提交事务

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

-- 查看 autocommit 状态
SHOW VARIABLES LIKE 'autocommit';

-- 取消自动提交，再次修改，则需要提交之后才生效 
SET @@autocommit = off;
# 修改数据
update account set money = money - 500 where name = '张人大';
# 手动提交
commit;

-- 开启自动提交
SET @@autocommit = on;

The Four ACID Attributes of Database Transaction

Atomicity -- 原子性。每个事务都是一个整体，不可再拆分，事务中所有的 SQL 语句要么都执行成功，要么都失败。

Consistency -- 一致性。事务在执行前数据库的状态与执行后数据库的状态保持一致。如：转账前2个人的总金额是 2000，转账后 2 个人总金额也是 2000。

Isolation -- 隔离性。事务与事务之间不应该相互影响，执行时保持隔离的状态。

Durability -- 持久性。一旦事务执行成功，对数据库的修改是持久的。就算关机，数据也是要保存下来的。

Transaction Isolation Level

Data Concurrency

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

Data Concurrency Problems

Dirty Reads -- 脏读：一个事务读取到了另一个事务中尚未提交的数据

Unrepeatable Reads -- 不可重复读：一个事务中多次读取的数据内容不一致。这是进行 update 操作时引发的问题。注意这里的重点是同一个事务中的多次读取，比如开启事务后，第一次读取有 1000 块钱，在事务还未结束时，第二次读取就可能变成 500 块钱了。又比如银行系统的程序在一个事务中要读取两次数据然后发到文件和电脑屏幕上，这时候就需要保证不可重复读的问题不会发生，否则会导致文件和电脑屏幕的数据不一致。

Phantom Reads -- 幻读：一个事务中，某一次的 select 操作得到的结果所表征的数据状态，无法支撑后续的业务操作。查询得到的数据状态不准，导致幻读。为什么会出现幻读？首先，为了解决不可重复读问题，只能底层设置一个缓存机制来保证一个事务中每次读取到的值会一样；基于解决了不可重复读问题的前提下，假设一个事务 A 查询了数据 x 是否存在并基于查询结果进行数据 x 的增加，但同时有另一个事务 B 提交了数据 x 的增加，那么事务 A 的增加就会因为重复而报错；这时候，在事务 A 中就会出现报错信息提示数据 x 已经存在，但是查询结果提示数据 x 不存在，就像出现幻觉一样。

The Four Isolation Level

Read Uncommitted -- 读未提交：没有解决问题。只要数据被修改，就立即持久化修改后的值。

Read Committed -- 读已提交：解决脏读问题。如果数据修改没有被提交，就不会持久化修改后的值。这是 Oracle 和 SQL 的默认隔离级别。

Repeatable Read -- 可重复读：解决不可重复读问题。底层设置了缓存保证一个事务内的每次读取都是一样的。这是 MySQL 的默认隔离级别。

Serializable -- 串行化：解决幻读问题。解决幻读问题只能通过取消数据并发访问，变成串行访问。但是串行化非常耗时，而且，影响数据库的性能，所以，数据库不会使用这种隔离级别。

-- 查看隔离级别
select @@tx_isolation;

-- 修改隔离级别为读未提交
set global transaction isolation level read uncommitted;

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2020-07-06，如有侵权请联系 cloudcommunity@tencent.com 删除

云数据库 SQL Server

本文分享自 Renda 微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体同步曝光计划，欢迎热爱写作的你一起参与！