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

作者: YMeng_Zhang | 来源:发表于2021-08-17 20:38 被阅读0次

什么是数据库

数据库(DataBase) 就是存储和管理数据的仓库
其本质是一个文件系统, 还是以文件的方式,将数据保存在电脑上

为什么使用数据库

存储方式	优点	缺点
内存	速度快	不能够永久保存,数据是临时状态的
文件	数据是可以永久保存的	使用IO流操作文件, 不方便
数据库	1.数据可以永久保存 2.方便存储和管理数据 3.使用统一的方式操作数据库(SQL)	占用资源,有些数据库需要付费(比如Oracle数据库)

常见的数据库：

MySQL 数据库：开源免费的数据库。操作简单，常作为中小型的项目的数据库首选，但是被 Oracle 公司收购后，MySQL 6.x 开始收费。
Oracle 数据库：收费的大型数据库，Oracle 公司的核心产品。安全性高。
DB2：收费的超大型数据库，IBM 公司的数据库产品。常在银行系统使用。
SQL Server：收费的中型数据库，微软公司的产品。常用在 C#，.net 等语言。该数据库只能运行在 Windows 机器上；扩展性，稳定性，安全性，性能都表现平平。

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

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

使用命令行管理数据库

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

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

MySql的目录结构

  bin --- 放置一些可执行文件
  docs --- 文档
  &nbspinclude --- 包含(头)文件
  lib --- 依赖库
  share --- 用于存放字符集、语言等信息。

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

数据库管理系统

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

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

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

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

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

数据库表

  数据库中以表为组织单位存储数据
  表类似 Java 中的类,每个字段都有对应的数据类型
  对比 Java 程序与关系型数据库

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

SQL

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

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

  SQL 语句可以单行或者多行书写，以分号结尾 ; （Sqlyog 中可以不用写分号，但建议加上）
  可以使用空格和缩进来增加语句的可读性。
  MySQL 中使用 SQL 不区分大小写，一般关键字大写，数据库名表名列名小写。
MySQL 注释方式：

# show databases;  单行注释(MySql特有的单行注释)
-- show databases; 单行注释
/*
    多行注释
    show databases;
*/

SQL 的分类

DDL - Data Deﬁnition Language：数据定义语言，用来定义数据库对象：数据库，表，列等。
DML - Data Manipulation Language：数据操作语言，用来对数据库中表的记录进行更新。
DQL - Data Query Language：数据查询语言，用来查询数据库中表的记录。
DCL - Date Control Language：数据控制语言，用来定义数据库的访问权限和安全级别，及创建用户。

DDL操作数据库

information_schema：信息数据库，保存的是其它数据库的信息
mysql：MySQL 核心数据库，保存的是用户和权限
performance_schema：保存性能相关的数据，监控 MySQL 的性能
sys：记录了 DBA（Database Administrator）所需要的一些信息，更方便地让 DBA 快速了解数据库的运行情况。

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

/* 方式1 直接指定数据库名进行创建 默认数据库字符集为：latin1 */
CREATE DATABASE db1; 
/* 方式2 指定数据库名称，指定数据库的字符集 一般都指定为 utf8,与Java中的编码保持一致 */
CREATE DATABASE db1_1 CHARACTER SET utf8;

查看/选择数据库

-- 切换数据库 从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 数据库名; -- 查看一个数据库的定义信息

修改数据库

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

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

删除数据库

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

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

DDL 操作数据表

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

创建表

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

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

-- 查看表结构
DESC test2;

查看表

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

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

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

删除表

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

drop table 表名 --- 删除表（从数据库中永久删除某一张表）
drop table if exists 表名 --- 判断表是否存在，存在的话就删除,不存在就不执行删除

修改表

--  将 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;

DML 操作表中数据

SQL中的DML 用于对表中的数据进行增删改操作

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

插入数据

-- 插入全部字段，将所有字段名都写出来
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);

注意：
  1. 值与字段必须要对应，个数相同&数据类型相同
  2.值的数据大小，必须在字段指定的长度范围内
  3.varchar char date类型的值必须使用单引号，或者双引号包裹。
  4.如果要插入空值，可以忽略不写，或者插入null
  5.如果插入指定字段的值，必须要上写列名

更改数据

-- 不带条件修改，将所有的性别改为女，慎用
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;

删除数据

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

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

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

DQL 查询表中数据

-- 创建员工表
CREATE TABLE emp(
    eid INT,  --员工id
    ename VARCHAR(20), --姓名
    sex CHAR(1), --性别
    salary DOUBLE, --薪资
    hire_date DATE, -- 入职时间
    dept_name VARCHAR(20) --部门名称
);
-- 插入数据
.....

简单查询

-- 查询 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;）

条件查询
-- 比较运算符

运算符	说明
> < <= >= =	大于、小于、小于等于、大于等于、等于
<>，!=	不等于
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单表&约束&事务

DQL操作单表

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

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

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

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

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

聚合函数

聚合函数会忽略null空值。

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

-- 1. 查询员工的总数
-- # 统计表中的记录条数
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 = '教学部';

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

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

分组

分组查询指的是使用 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,AS '部门名称',
    AVG(salary)AS '平均薪资' 
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 页 (1-1)*3=0
SELECT * FROM emp LIMIT 0, 3;
 -- 第 2 页 (2-1)*3=3
SELECT * FROM emp LIMIT 3, 3;
-- 第 3 页 (3-1)*3=6
SELECT * FROM emp LIMIT 6, 3;

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

SQL约束

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;
--  插入数据,观察主键的起始值（从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('布莱尔','女');

注意：删除表中所有数据有两种方式
-- DELETE 只是删除表中所有数据,对自增没有影响
TRUNCATE
-- truncate 是将整个表删除掉,然后创建一个新的表
自增的主键,重新从 1开始

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

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

Unique -- 唯一约束
唯一约束的特点：表中的某一列的值不能重复（对 null 不做唯一的判断）。
主键约束与唯一约束的区别：

主键约束是唯一的且不能够为空，唯一约束是唯一的但可以为空；
一个表中只能有一个主键，但是可以有多个唯一约束。

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

-- 测试唯一约束
-- 添加一条数据
INSERT INTO emp7(ename,sex) VALUES('张三','男');
-- 错误：Duplicate entry '张三' for key 'ename' 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('布莱尔','女');

数据库事务

事务
数据库事务：是一个整体，由一条或者多条 SQL 语句组成，这些 SQL 语句要么都执行成功，要么都执行失败，只要有一条 SQL 出现异常，整个操作就会回滚，整个业务执行失败。
回滚
数据库回滚：即在事务运行的过程中发生了某种故障，事务不能继续执行，系统将事务中对数据库的所有已完成的操作全部撤销，滚回到事务开始时的状态（在提交之前执行）。

-- 模拟转账操作

-- 创建账户表
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事务操作

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;

事务的四大特性 ACID

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

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

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

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

MySQL 事务隔离级别

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

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

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

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

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

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

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

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

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

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

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

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

什么是数据库

为什么使用数据库

MySql的目录结构

数据库管理系统

数据库表

SQL

DDL操作数据库

DDL 操作数据表

DML 操作表中数据

DQL 查询表中数据

MySQL单表&约束&事务

DQL操作单表

排序

聚合函数

分组

limit关键字

SQL约束

数据库事务

MySQL事务操作

事务的四大特性 ACID

MySQL 事务隔离级别

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

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

什么是数据库

为什么使用数据库

MySql的目录结构

数据库管理系统

数据库表

SQL

DDL操作 数据库

DDL 操作 数据表

DML 操作表中数据

DQL 查询表中数据

MySQL单表&约束&事务

DQL操作单表

排序

聚合函数

分组

limit关键字

SQL约束

数据库事务

MySQL事务操作

事务的四大特性 ACID

MySQL 事务隔离级别

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

DDL操作数据库

DDL 操作数据表