简介
数据库
数据库(Database):是按照数据结构来组织、存储和管理数据的仓库。每个数据库都有一个或多个不同的 API 用于创建,访问,管理,搜索和复制所保存的数据。
我们也可以将数据存储在文件中,但是在文件中读写数据速度相对较慢。所以,现在我们使用关系型数据库管理系统来存储和管理的大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
关系数据库管理系统 RDBMS(Relational Database Management System)的特点:数据以表格的形式出现;每行为各种记录名称;每列为记录名称所对应的数据域;许多的行和列组成一张表单;若干的表单组成库。
RDBMS 术语
- 数据库:数据库是一些关联表的集合。
- 数据表:表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列:一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
- 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。
- 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
- 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键:外键用于关联两个表。
- 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
- 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
MySQL数据库
MySQL 是 数据库管理系统 DBMS(DataBase Management System),由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。它的特点有:
-
MySQL 属于关系型数据库管理系统,关系型数据库将数据保存在不同的表中,而不是将所有数据放在一个大仓库内,这样就增加了速度并提高了灵活性。
-
MySQL 是开源的,所以你不需要支付额外的费用。
-
MySQL 使用标准的 SQL 数据语言形式。
-
MySQL 可以运行于多个系统上,并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
-
MySQL 对PHP有很好的支持,PHP 是目前最流行的 Web 开发语言。
-
MySQL 支持大型数据库,支持 5000 万条记录的数据仓库,32 位系统表文件最大可支持 4GB,64 位系统支持最大的表文件为8TB。
-
MySQL 是可以定制的,采用了 GPL 协议,你可以修改源码来开发自己的 MySQL 系统。
入门
配置
- 打开 MySQL 安装的文件夹,打开在该文件夹下的 my.ini 配置文件:
# 设置mysql客户端默认字符集
default-character-set=utf8
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
# 设置 mysql数据库的数据的存放目录
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
# 允许最大连接数
max_connections=20
# 服务端使用的字符集,默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
命令行指令
启动 MySQL 服务:
net start mysql
登录 MySQL 客户端:
- -h : 客户端的主机名(登录本机 localhost / 127.0.0.1,可忽略此选项)
- -u : 用户名
- -p : 密码(密码为空, 可忽略此选项)
mysql -h 主机名 -u 用户名 -p 密码
关闭 MySQL 服务:
net stop mysql
管理
- SHOW DATABASES
- USE database
- SHOW TABLES
- SHOW COLUMNS FROM table
USE database:选择要操作的Mysql数据库,使用该命令后所有Mysql命令都只针对该数据库。
操作结果:
Database changed
SHOW DATABASES:列出 MySQL 数据库管理系统的数据库列表。
操作结果:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
SHOW TABLES:显示指定数据库的所有表,使用该命令前需要使用 use 命令来选择要操作的数据库。
操作结果:
+------------------+
| Tables_in_runoob |
+------------------+
| employee_tbl |
| runoob_tbl |
| tcount_tbl |
+------------------+
SHOW COLUMNS FROM database:显示数据表的属性,属性类型,主键信息 ,是否为 NULL,默认值等其他信息。
操作结果:
+-----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+--------------+------+-----+---------+-------+
| runoob_id | int(11) | NO | PRI | NULL | |
| runoob_title | varchar(255) | YES | | NULL | |
| runoob_author | varchar(255) | YES | | NULL | |
| submission_date | date | YES | | NULL | |
+-----------------+--------------+------+-----+---------+-------+
SQL 语法
结构化查询语言 SQL(Structured Query Language )
DDL 数据定义
数据定义语言 DDL(Data Definition Language ):用于定义数据库、表。
操作数据库
使用
# 使用数据库
USE $database;
# 查询当前正在使用的数据库名称
SELECT DATABASE();
创建
# 创建数据库
CREATE DATABASE $database;
# 创建数据库,并判断是否存在
CREATE DATABASE IF NOT EXISTS $database;
# 创建数据库,并设置字符集
CREATE DATABASE $database
CHARACTER SET utf-8;
# 创建数据库,判断是否存在,并设置字符集
CREATE DATABASE
IF NOT EXISTS $database
CHARACTER SET utf-8;
查询
# 查询所有数据库名称
SHOW DATABASES;
# 查询数据库的创建语句
SHOW CREATE DATABASE $database;
修改
# 修改数据库的字符集
ALTER DATABASE $database
CHARACTER SET gbk;
删除
# 删除数据库
DROP DATABASE $database;
# 删除数据库,判断是否存在
DROP DATABASE
IF EXISTS $database;
操作表、列
创建
# 创建表
CREATE TABLE $table(
$columnA $type,
$columnB $tyep
);
# 创建副本
CREATE TABLE $tableB
LIKE $tableA;
查询
# 查询某个数据库中的所有表
SHOW TABLES;
# 查询表结构
DESC $table;
# 查询表的创建语句
SHOW CREATE TABLE $table;
修改
# 修改表名
ALTER TABLE $tableOld
RENAME TO $tableNew
# 修改表的字符集
ALTER TABLE $table
CHARACTER SET utf-8;
# 修改列
ALTER TABLE $table
CHANGE $columnOld $columnNew $typeNew;
# 修改类型
ALTER TABLE $table
MODIFY $column $tyepNew;
# 添加列
ALTER TABLE $table
ADD $column $type;
# 删除列
ALTER TABLE $table
DROP $column;
删除
# 删除表
DROP TABLE $table;
# 删除表,判断是否存在
DROP TABLE
IF EXISTS $table;
DML 数据操作
数据操作语言 DML(Data Manipulation Language) :用于对数据库中表的数据进行增删改。
操作记录、值
创建
# 给记录中的所有字段,添加值
INSERT INTO $table
VALUES($valueA,$valueB....)
# 给记录中指定的字段,添加值
INSERT INTO $table($columnA)
VALUES($valueA)
删除
# 删除所有记录
DELETE * FROM $table;
# 删除指定记录
DELETE FROM $table
WHERE $column = $value;
# 删除所有记录,并创建一个空副本
TRUNCATE TABLE $table;
修改
# 给记录中所有的字段,修改值
UPDATE $table
SET $columnA = $valueA;
# 给记录中指定的字段,修改值
UPDATE $table
SET $columnA = $valueA
WHERE $colmnB = $valueB;
DQL 数据查询
数据查询语言 DQL (Data Query Language):用于查询数据库中表的数据。
基础查询
# 查询表中的所有列
SELECT * FROM $table;
# 查询表中指定的列
SELECT $column FROM $table;
# 查询表中指定的列,并去掉重复的字段
SELECT DISTINCT $column FROM $table;
# 查询表中指定的列,并计算
SELECT
$columnA,
$columnB,
$columnA + $columnB
FROM
$table;
# 查询表中指定的列,并计算,且排除为null的情况
SELECT
$columnA,
$columnB,
IFNULL($columnA,0) + IFNULL($columnB,0)
FROM
$table;
# 查询表中指定的列,计算,排除为null的情况,给计算结果起名
# AS可省略
SELECT
$columnA AS $nameA,
$columnB AS $nameB,
IFNULL($columnA,0)+IFNULL($columnB,0) AS $nameC
FROM
$table;
条件查询
# 大于、大于等于、等于、不等于、不等于
WHERE $column > $value;
WHERE $column >= $value;
WHERE $column = $value;
WHERE $column != $value;
WHERE $column <> $value;
# 范围
WHERE $column>=10 && $column<=100;
WHERE $column>=10 AND $column<=100;
WHERE $column BETWEEN 10 AND 1000;
# 个例
WHERE $column=10 OR $column=20;
WHERE $column IN (10,20,30,40);
#查询NULL
WHERE $column IS NULL;
WHERE $column IS NOT NULL;
模糊查询
# “_” 单个任意字符
WHERE $column = 'C_t';
# “%” 多个任意字符
WHERE $column = 'super%';
排序查询
# 按照指定列的顺序,升序排序
ORDER BY $column ASC;
# 按照指定列的顺序,降序排序
ORDER BY $column DESC;
聚合函数
- 聚合函数排除了NULL值
# 计算指定列的 字段个数
SELECT COUNT($column) FROM $table;
# 计算指定列的 字段个数,且不排除NULL值
SELECT COUNT( IFNULL($column,0) ) FROM $table;
# 计算指定列的 最大值
SELECT MAX($column) FROM $table;
# 计算指定列的 最小值
SELECT MIN($column) FROM $table;
# 计算指定列的和
SELECT SUM($column) FROM $table;
# 计算指定列的平均值
SELECT AVG($column) FROM $table;
分组查询
WHERE和HAVING的区别:
WHERE 在分组前进行限定,满足条件,则进行分组; 不可以进行聚合函数的判断。
HAVING 在分组后进行限定,满足条件,则被查询出来;可以进行聚合函数的判断。
# 查询男性和女性的平均成绩
SELECT
$sex, AVG($score), COUNT($id)
FROM
$table
GROUP BY
$sex;
# 查询男女在70分以上的成绩
SELECT
$sex, AVG($score), COUNT($id)
FROM
$table
WHERE
$score>70
GROUP BY
$sex;
# 查询男女在70分以上的成绩,且分组人数大于2
SELECT
$sex, AVG($score), COUNT($id)
FROM
$table
WHERE
$score>70
GROUP BY
$sex
HAVING
COUNT($id) > 2;
# 查询男女在70分以上的成绩,且分组人数大于2,使用别名
SELECT
$sex, AVG($score) 平均分 , COUNT($id) 人数
FROM
$table
WHERE
$score>70
GROUP BY
$sex
HAVING
平均分 > 80 AND 人数 > 2 ;
分页查询
# 分成3页,每页显示9条记录
/*第一页*/
# 开始索引 = (页码 - 1 )* 每页显示的条数 =( 1 - 1 )* 9 = 0
SELECT * FROM $table LIMIT 0,3;
/*第二页*/
# 开始索引 = (页码 - 1 )* 每页显示的条数 =( 2 - 1 )* 9 = 9
SELECT * FROM $table LIMIT 9,3;
/*第三页*/
# 开始索引 = (页码 - 1 )* 每页显示的条数 =( 3 - 1 )* 9 = 18
SELECT * FROM $table LIMIT 18,3;
约束
约束是对表中的数据进行限定,保证数据的正确性、有效性和完整性。它包括:
-
主键约束:PRIMARY KEY
-
非空约束:NOT NULL
-
唯一约束:UNIQUE
-
外键约束:FOREIGN KEY
主键约束
主键约束(PRIMARY KEY):列中的字段不能重复,也不能为NULL。
# 创建表,添加主键约束
CREATE TABLE $table(
$id INT PRIMARY KEY
);
# 删除主键约束
ALTER TABLE
$table
DROP
PRIMARY KEY;
# 添加主键约束
ALTER TABLE
$table
MODIFY
$id INT PRIMARY KEY;
# 自动增长(数值类型,且只跟上一条记录有关。)
CREATE TABLE $table(
$id INT PRIMARY KEY AUTO_INCREMENT
);
# 删除自动增长
ALTER TABLE
$table
MODIFY
$id INT;
# 添加自动增长
ALTER TABLE
$table
MODIFY
$id INT AUTO_INCREMENT;
非空约束
# 创建表,添加非空约束
CREATE TABLE $table(
id INT NOT NULL
);
# 删除非空约束
ALTER TABLE
$table
MODIFY
id INT;
# 添加非空约束
ALTER TABLE
$table
MODIFY
id INT NOT NULL;
唯一约束
# 创建表,添加唯一约束
CREATE TABLE $table(
id INT UNIQUE
);
# 删除唯一约束
ALTER TABLE
$table
DROP INDEX
$id;
# 添加唯一约束
ALTER TABLE
$table
MODIFY
$id INT UNIQUE;
外键约束
# 创建表,添加外键约束
/*主表*/
CREATE TABLE zhu(
id INT PRIMARY KEY,
cong_id INT,
# CONSTRAINT 外键名称 FOREIGN KEY(外键列) REFERENCES 主表名称(主表主键名称)
CONSTRAINT zhu_cong_fk FOREIGN KEY(cong_id) REFERENCES zhu(id)
);
/*从表*/
CREATE TABLE cong(
id INT PRIMARY KEY,
);
# 删除外键约束
ALTER TABLE
$table
DROP
PRIMARY KEY;
# 添加外键约束
ALTER TABLE
$table
ADD CONSTRAINT
emp_dep_fk FOREIGN KEY (dep_id) REFERENCES employee(id);
# 添加外键约束,设置级联更新
ALTER TABLE
$table
ADD
CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES employee(id)
ON UPDATE CASCADE ;
# 添加外键约束,设置级联删除(谨慎使用!)(删除更新可以同时设置)
ALTER TABLE
$table
ADD
CONSTRAINT emp_dep_fk FOREIGN KEY (dep_id) REFERENCES employee(id)
ON DELETE CASCADE ;
数据库设计
表和表的关系
一对一
身份证:编号
/* 身份证表 */
CREATE TABLE IDCards (
id_card INT PRIMARY KEY
) ;
/* 编号表 */
CREATE TABLE IDNums (
id_num INT PRIMARY KEY
) ;
/* 给身份证表添加外键 */
ALTER TABLE IDCards
ADD CONSTRAINT num_card_fk FOREIGN KEY (id_card) REFERENCES IDNums (id_num) ;
/* 给编号表表添加外键 */
ALTER TABLE IDNums
ADD CONSTRAINT card_num_fk FOREIGN KEY (id_num) REFERENCES IDCards (id_card) ;
一对多
顾客:订单
/* 顾客表 */
CREATE TABLE customers(
cust_id INT PRIMARY KEY
);
/* 订单表 */
CREATE TABLE orders(
orde_id INT PRIMARY KEY,
cust_id INT,
CONSTRAINT many_one_fk FOREIGN KEY(cust_id) REFERENCES customers(cust_id)
);
多对多
**订单:商品 **
/* 订单表 */
CREATE TABLE orders(
orde_id INT PRIMARY KEY
);
/* 商品表 */
CREATE TABLE products(
prod_id INT PRIMARY KEY
);
/* 订单-商品表 */
CREATE TABLE orders_products(
orde_id INT,
prod_id INT,
CONSTRAINT order_fk FOREIGN KEY(orde_id) REFERENCES orders(orde_id),
CONSTRAINT prod_fk FOREIGN KEY(prod_id) REFERENCES products(prod_id)
);
表的设计范式
数据库设计的范式:
设计关系数据库时,遵从不同的规范要求,设计出合理的关系型数据库。
不同的规范要求被称为不同的范式,各种范式呈递次规范,越高的范式数据,数据库冗余越小。
专业术语
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rXKq9F0o-1581679800119)(MySQL\属性.png)]
码:学号 --> 课程
如果在一张表中,某个属性(或属性组)被其他所有属性完全依赖,则称这个属性(或属性组)为这张表的码。简而言之,就是能完全确定每一条记录的字段,可以是一个字段,也可以是多个。在这里学号和课程就可以完全确定每一条记录。
主属性(候选码):学号,课程
码属性组中的所有属性。
非主属性:姓名,系名,系主任,分数
除开码属性组中的属性的表中的其他属性。
概念函数依赖:学号 --> 姓名,(学号,课程) --> 分数
通过 A属性或A属性组的值,可以唯一确定 B属性的值。则称B依赖于A。
完全函数依赖:(学号,课程名称)–> 分数
A是一个属性组,通过 A属性组的所有值,才可以确定唯一的B属性的值。则称B完全依赖于A。
部分函数依赖:(学号,课程名称)–> 姓名 、学号–> 姓名
A是一个属性组,通过 A属性组 的部分值,就可以确定唯一的B属性的值。则称B部分依赖于A。
传递函数依赖:学号 --> 院系 --> 系主任
通过 A属性或者A属性组 的值,可以确定唯一的B属性的值;再通过 B属性或者B属性组 的值,可以确定唯一的C属性的值,则称C传递依赖于A。
第一范式
- 表1
- 主属性:学号,课程名称
- 非主属性:系名,系主任,分数
- 部分依赖:(学号,课程名称)–> 姓名 、学号–> 姓名
- 完全依赖:(学号,课程名称)–> 分数
- 传递依赖:学号 --> 院系 --> 系主任
- 规范
- 每一列都是不可分割的原子数据项
- 缺点
- 数据冗余非常严重(相同系名和系主任在每一条记录中都会重复使用)
- 数据添加存在问题(开设新的院系没有学生,导致无法添加新院系)
- 数据删除存在问题(学生毕业删除学生,却也删除了院系)
第二范式
-
表1
- 主属性:学号,课程名称
- 非主属性:分数
- 部分依赖:0
- 完全依赖:(学号,课程名称)–> 分数
- 传递依赖:0
-
表2
- 主属性:学号
- 非主属性:姓名,系名,系主任
- 部分依赖:0
- 完全依赖:学号 --> 姓名、学号 --> 系名、学号 --> 系主任
- 传递依赖:学号 --> 院系 --> 系主任
-
规范
- 每一列都是不可分割的原子数据项
- 消除非码属性对主属性的部分依赖(非码属性必须完全依赖于主属性)
-
缺点
数据冗余非常严重- 数据添加存在问题(开设新的院系没有学生,导致无法添加新院系)
- 数据删除存在问题(学生毕业删除学生,却也删除了院系)
第三范式
-
表1
- 主属性:学号,课程名称
- 非主属性:分数
- 部分依赖:0
- 完全依赖:(学号,课程名称)–> 分数
- 传递依赖:0
-
表2
- 主属性:学号
- 非主属性:姓名,系名
- 部分依赖:0
- 完全依赖:学号 --> 姓名、学号 --> 系名
- 传递依赖:0
-
表3
- 主属性:系名
- 非主属性:系主任
- 部分依赖:0
- 完全依赖:系名 --> 系主任
- 传递依赖:0
-
规范
- 每一列都是不可分割的原子数据项
- 消除非码属性对主属性的部分依赖
- 消除传递依赖
-
缺点
数据冗余非常严重数据添加存在问题数据删除存在问题
备份和还原
备份数据库
mysqldump -uroot -proot DATABASE > 保存路径(包含备份文件)
mysqldump -uroot -p111 database01 > D://database01.sql
还原数据库
1.开启MySQL服务:net start mysql;
2.登录MySQL:mysql -uroot -proot;
3.创建数据库:CREATE DATABASE $database;
4.使用数据库:use $database;
5.还原登录数据库:source 文件路径(包含备份文件)
source D://database01.sql
多表查询
笛卡尔积
笛卡儿积(cartesian product)由没有联结条件的表关系返回的结果为笛卡儿积。
检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
SELECT * FROM vendors; // 查询出记录15条
SELECT * FROM products; // 查询出记录15条
SELECT * FROM vendors, products; // 查询出记录15*15条
从上面的输出中可以看到,相应的笛卡儿积不是我们所想要的。
这里返回的数据用每个供应商匹配了每个产品,它包括了供应商中不匹配的产品。实际上有的供应商根本就没有这样的产品。
内联结
隐式内联结
等值联结(equijoin),也称为内部联结,它基于两个表之间的相等测试。它可以解决笛卡尔积的问题:
SELECT *
FROM vendors, products
WHERE vendors.vend_id = products.vend_id;
除了两个表之外,内部联结还能联结多个表:
SELECT
products.prod_name,
vendors.vend_name,
products.prod_price,
orderitems.quantity
FROM
orderitems,
products,
vendors
WHERE products.vend_id = vendors.vend_id
AND orderitems.prod_id = products.prod_id
AND orderitems.order_num = 20005 ;
另外,使用别名能够有效的缩短SQL语句,并且我们看可以在单条SELECT语句中多次使用相同的表:
SELECT
p.prod_name,
v.vend_name,
p.prod_price,
o.quantity
FROM
orderitems AS o,
products AS p,
vendors AS v
WHERE p.vend_id = v.vend_id
AND o.prod_id = p.prod_id
AND o.order_num = 20005 ;
显示内联结
内部联结的另外一种形式,主要是FROM语句的变化:
SELECT *
FROM vendors INNER JOIN products
WHERE vendors.vend_id = products.vend_id;
自联结
首先找到生产ID为DTNTR的物品的供应商,然后找出这个供应商生产的其他物品。
此查询中需要的两个表实际上是相同的表,因此products表在FROM子句中出现了两次。
虽然这是完全合法的,但对products的引用具有二义性,因为MySQL不知道你引用的是products表中的哪个实例。
为解决此问题,使用了表别名。products的第一次出现为别名p1,第二次出现为别名p2。
SELECT
p1.prod_id,
p1.prod_name
FROM
products AS p1,
products AS p2
WHERE p1.vend_id = p2.vend_id
AND P2.prod_id = 'DTNTR';
外连接
下面的SELECT语句给出一个简单的内部联结。它检索所有客户及其订单:
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders /* 左右交集(左右关联的行) */
WHERE customers.cust_id = orders.cust_id;
外部联结语法与内部联结类似。检索所有客户,包括那些没有订单的客户:
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders /* 左(左中未关联的行) + 左右交集(左右关联的行) */
WHERE customers.cust_id = orders.cust_id;
事务处理
事务处理是一种机制,用来管理必须成批执行的MySQL操作,以保证数据库不包含不完整的操作结果。
利用事务处理,可以保证一组操作不会中途停止,它们或者作为整体执行,或者完全不执行(除非明确指示)。
如果没有错误发生,整组语句提交给(写到)数据库表。如果发生错误,则进行回退(撤销)以恢复数据库到某个已知且安全的状态。
在使用事务和事务处理时,有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语:
事务(transaction):指一组SQL语句;
回退(rollback):指撤销指定SQL语句的过程;
提交(commit):指将未存储的SQL语句结果写入数据库表;
保留点(savepoint):指事务处理中设置的临时占位符(place- holder),你可以对它发布回退(与回退整个事务处理不同)。
控制事务处理
标识事务
START TRANSACTION
回退
事务处理用来管理INSERT、UPDATE和DELETE语句。
你不能回退SELECT语句(这样做也没有什么意义)、CREATE或DROP操作。
事务处理块中可以使用这两条语句,但如果你执行回退,它们不会被撤销。
SELECT * FROM ordertotals;
START TRANSACTION; /* 开启事务 */
DELETE FROM ordertotals; /* 执行删除操作 */
SELECT * FROM ordertotals;
ROLLBACK; /* 回滚操作 */
SELECT * FROM ordertotals;
提交
一般的MySQL语句都是直接针对数据库表执行和编写的。
这就是所谓的隐含提交(implicit commit),即提交操作是自动进行的。
但是,在事务处理块中,提交不会隐含地进行。
为进行明确的提交,需要使用COMMIT语句,如下所示:
SELECT * FROM ordertotals;
START TRANSACTION; /* 开启事务 */
DELETE FROM ordertotals; /* 执行删除操作 */
SELECT * FROM ordertotals;
ROLLBACK; /* 回滚操作 */
COMMIT; /* 提交操作 */
SELECT * FROM ordertotals;
保留点
简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。
但是,只是对简单的事务处理才能这样做,更复杂的事务处理可能需要部分提交或回退。
这些占位符称为保留点:
SAVEPOINT d1;
每个保留点都取标识它的唯一名字,以便在回退时,MySQL知道要回退到何处:
ROLLBACK TO d1;
释放保留点保留点在事务处理完成(执行一条ROLLBACK或COMMIT)后自动释放。
自MySQL 5以来,也可以明确地释放保留点:
RELEASE SAVEPOINT d1;
提交行为
正如所述,默认的MySQL行为是自动提交所有更改。
换句话说,任何时候你执行一条MySQL语句,该语句实际上都是针对表执行的,而且所做的更改立即生效。
查看事务的默认提交行为:
SELECT autocommit;
# 1 自动提交
# 0 手动提交
设置autocommit为0(假),指示MySQL不自动提交更改,直到autocommit被设置为真为止:
SET autocommit = 0;
事务的四大特征
原子性:不可分割的最小操作单位,要么同时成功,要么同时失败。
持久性:当事务提交或回滚后,数据库会持久化的保存数据。
隔离性:多个事务之间,相互独立。
一致性:事务操作前后,数据总量不变。
事务存在的问题
脏读
一个事务读取到了另一个事务中没有提交的数据。
我们同时开启事务,我可以让你去查看我未提交的数据;然后你看到了之后,我再回滚到初始状态。
设置READ COMMITTED可以解决。
虚读
在同一个事务中,两次读取的数据不一样。
我们同时开启事务,我让你去查看我提交过的数据,你看到了我的这个数据;然后我提交了新数据,你还没提交而且又查看了我的数据,我的数据又变成了新提交的数据。
设置REPEATABLE READ可以解决。你手动提交之后,才可以看到我的新数据。
幻读
在一个事务中,对数据库中所有记录进行操作(DML),但是另一个事务却添加的数据,并且在这个事务中查询不到执行过的修改。
我们同时开启事务,我准备把仓库的东西都丢了,你准备再往仓库里加东西,并且你看不到我把东西丢出去的操作。
事务的隔离级别
隔离级别从小到大安全性越来越高,但是效率越来越低。
READ UNCOMMITTED 读未提交:我们同时开始事务,你可以查看我未提交的数据。
产生问题:脏读、不可重复读、幻读
READ COMMITTED(Oracle)读已提交:我们同时开启事务,你能查看我提交过的数据;但同时,我提交新数据,你也可以查看新数据。
产生问题:不可重复读、幻读
REPEATABLE READ (MySQL默认)可重复读:我们同时开启事务,你能只查看我提交过的数据;我提交新数据,你查看的也还是旧数据;只有当你手动提交事务或者回滚事务后,你才能查看我的新数据。
产生问题:幻读
SERIALIZABLE串行化:我们同时开启事务,我修改数据后,并未提交数据;此时,你无法查看我的任何数据,只有当我提交事务或者回滚事务后,你才能查看我的数据。类似于‘锁’。
串行化可以解决所有问题。
查询隔离级别语句
SELECT @@tx_isolation;
设置隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;