MySQL 知识点总结

EGEGEGEGEG

已于 2024-04-06 15:01:14 修改

阅读量537

点赞数 21

文章标签： mysql

于 2024-02-03 22:38:31 首次发布

本文链接：https://blog.csdn.net/qq_37232457/article/details/136017422

版权

MySQL 知识点总结

MySQL知识点总结_malaganguo的博客-CSDN博客

mysql——cmd进入mysql及常用的mysql操作_行秋的博客-CSDN博客

0. 注意事项

避免使用select *
count（1）或count（列）代替count（*）
创建表时，尽量用char代替varchar

选择char类型时比如设置char(LEN)，那么其长度已经固定为LEN; 当存储CHAR值时，MySQL会删除字符串中的末尾空格

CHAR适合存储很短或长度近似的字符串。

对于char类型来说，最多只能存放的字符个数为255，和编码无关，任何编码最大容量都是255

–> char类型因为其长度固定，所以存储/查找时速度快，但是长度没分配好的话浪费空间

选择varchar类型时比如设置varchar(LEN)，其长度随输入数据的长度而改变;

varchar需要使用1或2个额外字节记录字符串的长度: 如果列的最大长度小于或等于255字节，则只使用1个字节表示，否则使用2个字节

只有单列字段情况下，varchar一般最多能存放(65535 - 3)个字节

–> varchar类型长度随存储数据的长度改变，所以存储/查找时速度较char类型慢，但是不浪费空间

当需求高存储查找速率并且对空间资源的浪费与否不是那么看重时当采用char类型，而对于空间资源的分配要求高当采用varchar类型
- CHAR非常适合存储密码的MD5值，因为这是一个定长的值。对于经常变更的数据，CHAR也比VARCHAR更好，因为定长的CHAR类型不容易产生碎片
- 对于非常短的列，CHAR比VARCHAR在存储空间上也更有效率。例如用CHAR(1)来存储只有Y和N的值，如果采用单字节字符集只需要一个字节，但是VARCHAR(1)却需要两个字节
索引散列值（重复少）不适合建立索引，例如：性别就不合适
表的字段顺序固定, 顺序的字段优先
组合索引代替多个单个索引（经常使用多个条件查询时）
连表时注意条件类型一致
尽量使用短索引

1. 数据库概述

客户端 --> DBMS(数据库管理系统) --> DB(数据库) --> table…

2. SQL: Structure Query Language, 结构化查询语言

SQL的分类

DDL(DataDefineLanguage)数据定义语言	用来定义数据库对象：库、表、列等。包括创建表，修改表，删除表
DML(DataManipulationLanguage)数据操作语言	用来定义数据库记录：是对表中数据的操作。包括插入，更新，删除数据
DCL(DataControlLanguage)数据控制语言	用来定义访问权限和安全级别
DQL(DataQueryLanguage)数据查询语言	用来查询记录（数据）：只有SELECT语句
SQL 语句以`;`结尾

DDL(包括: CREATE, ALTER, DROP关键字)

DDL 操作数据库

创建数据库

CREATE DATABASE db_name;  -- 创建数据库
CREATE DATABASE db_name SET GBK; -- 创建数据库并设置为gbk编码格式

查询数据库

SHOW DATABASE; -- 查看当前数据库服务器中所有数据库
SHOW CREATE DATABASE db_name; --查看创建数据库时的定义信息

在这里插入图片描述

/* 删除数据库 */
DROP DATABASE db_name; -- 删除指定数据库
/* 修改数据库 */
ALTER DATABASE db_name CHARACTER SET utf-8;  -- 将数据库的字符集改为utf-8
/* 选中数据库名 */
USE db_name; -- 使用数据库
/* 选中数据库 */
SELECT DATABASE(); -- 选中数据库

DDL操作数据表

创建数据表

语法： CREATE TABLE 表名(

字段1 字段类型，

字段2 字段类型，

…

字段n 字段类型

);

常用数据类型：

int：整型
double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；
char：固定长度字符串类型； char(10)  'abc       '
varchar：可变长度字符串类型；varchar(10) 'abc'
text：字符串类型;
blob：字节类型；
date：日期类型，格式为：yyyy-MM-dd；
time：时间类型，格式为：hh:mm:ss
timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss  会自动赋值
datetime:日期时间类型 yyyy-MM-dd hh:mm:ss

在这里插入图片描述

DML(DataManipulationLanguage)数据操作语言

DML是对表中的数据进行增、删、改的操作

INSERT 增操作

INSERT INTO 表名(字段1,字段2,…) VALUES(值1,值2,…);

-- 创建emp表格
CREATE TABLE emp(
id INT,
NAME VARCHAR(100),
gender VARCHAR(10),
birthday DATE,
salary FLOAT(10,2),
entry_date DATE,
RESUME TEXT
);
-- 插入一条记录
INSERT INTO emp(id,NAME,gender,birthday,salary,entry_date,RESUME)
VALUES(1,'zhangsan','female','1990-5-10',10000,'2015-5-5-','good girl');
-- 插入三条记录
INSERT INTO emp VALUES
(4,'zs','m','2015-09-01',10000,'2015-09-01',NULL),
(5,'li','m','2015-09-01',10000,'2015-09-01',NULL),
(6,'ww','m','2015-09-01',10000,'2015-09-01',NULL);

在这里插入图片描述

UPDATE 改操作

UPDATE 表名 SET 列名1=列值1,列名2=列值2,… WHERE 列名=列值;

UPDATE emp SET salary=5000; 将所有员工薪水修改为5000元
UPDATE emp SET salary=3000 WHERE name=zs; 将姓名为’zs’的员工薪水修改为3000元
UPDATE emp SET salary=4000,job=‘ccc’ WHERE name=‘aaa’; 将姓名为’aaa’的员工薪水修改为4000元,job改为ccc
UPDATE emp SET salary=salary+1000 WHERE name=‘wu’; 将wu的薪水在原有基础上增加1000元

UPDATE emp SET salary=5000;	将所有员工薪水修改为5000元
UPDATE emp SET salary=3000 WHERE name=zs;	将姓名为’zs’的员工薪水修改为3000元
UPDATE emp SET salary=4000,job=‘ccc’ WHERE name=‘aaa’;	将姓名为’aaa’的员工薪水修改为4000元,job改为ccc
UPDATE emp SET salary=salary+1000 WHERE name=‘wu’;	将wu的薪水在原有基础上增加1000元

DELETE 删操作

DELETE FROM 表名（WHERE 列名=值）;

DELETE FROM emp WHERE name=‘zs’; 删除表中名称为‘zs’的记录
DELETE FROM emp; 删除表中所有记录
TRUNCATE TABLE emp; 使用TRUNCATE彻底删除表中记录

TRUNCATE操作与DELETE的不同在于：TRUNCATE删除操作是 DROP掉数据表，然后重新创建新表，这样做的好处是执行速度快，不好的是删除的数据无法恢复;DELETE删除的是表中数据，表结构还在，数据可以找回。

DELETE FROM emp WHERE name=‘zs’;	删除表中名称为‘zs’的记录
DELETE FROM emp;	删除表中所有记录
TRUNCATE TABLE emp;	使用TRUNCATE彻底删除表中记录

DQL(Data Query Language) - 只有查询操作

SELECT 列名 FROM 表名

语法：

SELECT selection_list /*要查询的列名称*/
  FROM table_list /*要查询的表名称*/
  WHERE condition /*行条件*/
  GROUP BY grouping_columns /*对结果分组*/
  HAVING condition /*分组后的行条件*/
  ORDER BY sorting_columns /*对结果分组*/
  LIMIT offset_start, row_count /*结果限定, 第几行开始 计数, 总计row_count列*/

基础查询

SELECT * FROM emp; -- 查询表全部字段
SELECT empno,ename FROM emp ; -- 查询表指定字段

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：

=、!=、<>、<、<=、>、>=；
BETWEEN…AND； – 还可以筛选日期条件
IN(set)；
IS NULL； IS NOT NULL
AND；
OR；
NOT；

模糊查询(LIKE)

使用通配符补充模糊地方, 通配符有两个：

'_‘表示一个任意字符，’%'表示任意多个字符。使用模糊查询需要用到关键字：LIKE。
SELECT * FROM emp WHERE sname LIKE 's__0%'; -- 要找的是s开头后面两位任意，第四位为0后面任意长度的姓名

字段控制查询(DISTINCT)

DISTINCT : 去重

SELECT DISTINCT sal FROM emp; -- 查询某个字段并去重

IFNULL(a,b): 字段a为NULL, 用的时候变成默认值 b

SELECT *,sal+IFNULL(comm,0) AS total FROM emp ; -- 查询表, 将sal+comm 一并输出, comm为NULL时默认为0

排序(ORDER BY)

ASC : 升序; DESC : 降序

SELECT * FROM emp ORDER BY sal DESC,empno; -- sal按照降序(指定)排序，sal相同empno按照升序(默认)排序

在这里插入图片描述

分组查询(GROUP BY)

子句 GROUP BY

SELECT deptno,COUNT(*) num,SUM(sal) SUM FROM emp GROUP BY deptno; -- 按deptno分组, 查询deptno字段, 统计数量, 累计sal, 查询每个部门的编号，人数以及每个部门的工资和：

在这里插入图片描述

子句: HANVING 作用时分组后对数据进行过滤, 区别于WHERE
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal)>9000; 查询工资总和大于9000的部门编号以及工资和

聚合函数 – 做纵向运算的函数

COUNT(): 统计指定列不为NULL的记录
MAX() : 指定列最大值, 可以比较字符串
MIN()
SUM() : 指定列数值和; 非数值类型, 结果为0
AVG() : 指定列数值平均值; 非数值类型, 结果为0

Mysql 方言 – LIMIT

LIMIT 用来限定查询结果的起始行以及总行数, 起始行索引也是从0开始的
SELECT * FROM emp LIMIT 0,5;  从0行开始查询5行记录

查询代码书写顺序和执行顺序

查询语句的书写顺序:

SELECT-FROM-WHERE-GROUP BY-HAVING-ORDER BY-LIMIT

查询语句的执行顺序:

FROM-WHERE-GROUP BY-HAVING-SELECT-ORDER BY-LIMIT

DCL(DataControlLanguage): 数据控制语言

…

Mysql的物理删除和逻辑删除

物理删除: 真实删除。将对应数据从数据库中删除. 在数据库中直接使用delete、drop删除数据表属于物理删除

逻辑删除: 假删除。逻辑上数据被删除，但数据本身依然存在。设置is_deleted字段，设置其数据类型为bit,只有两个值0、1。0表示已经删除，1表示没有被删除

3. 数据完整性

保证用户输入的数据保存到数据库是正确的. 完整性分类: ①实体完整性 ②域完整性 ③引用完整性

实体完整性

实体（entity）：表中的一行（一条记录）。

主键的值不能为空或部分为空的约束条件; 要求每一个表中的主键字段都不能为空或者重复的值.
要求表中的所有行都有唯一的标识符，称为主键, 主键是否可以修改，或整个列是否可以被删除，取决于主关键字与其他表之间要求的完整性

PRIMARY KEY主键约束：数据唯一（数据不能重复），且不能为null

一个表中只能有一个PK约束，联合PK约束算是一个PK约束。

-- 方式一
CREATE TABLE student(
sid INT PRIMARY KEY,
sname VARCHAR(20)
);
-- 方式二: 可以创建联合主键——同时参照所有主键，都完全相同视为数据不唯一，所有键联合为一个主键。
-- 创建单主键表
CREATE TABLE student1(
sid INT,
sname VARCHAR(20),
PRIMARY KEY(sid)
);
-- 创建联合主键表
CREATE TABLE student1_1(
sid INT,
sname VARCHAR(20),
score DOUBLE,
PRIMARY KEY(sid,score)
);
-- 方式三: 可以给已经存在的表添加主键且可以添加联合主键
-- 在表格外部给表添加单主键
CREATE TABLE student2(
sid INT,
classid INT,
sname VARCHAR(20)
);
ALTER TABLE student2 ADD CONSTRAINT PRIMARY KEY(sid);
-- 在表格外部给表格添加联合主键
CREATE TABLE student2_1(
sid INT,
classid INT,
sname VARCHAR(20)
);
ALTER TABLE student2_1 ADD PRIMARY KEY(sid,classid);

UNIQUE唯一约束：数据唯一，可以有null

CREATE TABLE student3_1(
sid INT ,
sname VARCHAR(20) UNIQUE,
sage VARCHAR(5) UNIQUE
);
-- 方式二:
CREATE TABLE student3_2(
sid INT ,
sname VARCHAR(20),
sage VARCHAR(5)
);
ALTER TABLE student3_2 ADD UNIQUE (sname);
-- 删除唯一约束的方法:
ALTER TABLE student3_2 DROP INDEX sname; -- 这样可以删除sname作为的唯一约束

AUTO-INCREMENT自动增长列

自动增长列的约束是用来给==键==添加自动增加（只针对整数）的。如果没有定义键使用AUTO-INCREMENT，会报错

-- 配合 UNIQUE
CREATE TABLE student4(
sid INT UNIQUE AUTO_INCREMENT,
sname VARCHAR(20)
);
-- 配合 PRIMARY KEY
CREATE TABLE student4_1(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20)
);

自增字段的数值不会随着删除记录退回重加

域完整性

针对某一具体关系数据库的约束条件，它保证表中某些列不能输入无效的值。域完整性指列的值域的完整性。如数据类型、格式、值域范围、是否允许空值等。

域完整性约束：数据类型非空约束（NOT NULL）默认约束（DEFAULT）检查约束（CHECK这个MySQL不支持）

数据类型: 数据类型,日期类型,字符串类型

非空约束NOT NULL: 约束添加在一个字段的定义中，约束这个字段红不能有null

默认值约束DEFAULT: 这个字段的默认填充为DEFAULT 后的内容

ALTER TABLE USER ADD COLUMN gender CHAR(3) DEFAULT '男';
INSERT INTO USER 
VALUES(5,'高七','012','gaoqi@outlook.com','2000-5-1',DEFAULT); -- 插入的值与默认值相同

INSERT INTO USER 
VALUES(5,'杨八','012','gaoqi@outlook.com','2000-5-1','女'); -- 插入的值与默认值不同

在这里插入图片描述

引用完整性（参照完整性）——外键约束

外键约束关键字： FOREIGN KEY

外键约用来在两个表之间建立连接，它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性，一个表的外键可以为空值，若不为空值，则每个外键值必须等于另一个表中主键的某个值。

外键：首先外键是表中的一个字段，可以不是本表的主键，但对应另外一个表的主键。外键的主要作用是保证数据引用完整性，定义外键后，不允许删除在另外一个表种的具有关联关系的行，外键的作用是保持数据的一致性，完整性。

-- 创建学生表
CREATE TABLE connStudent(
sid INT PRIMARY KEY,
sname VARCHAR(20),
sbirthday DATE
);
-- 创建分数表, 分数表中的sid是外键, 与学生表关联, 对应为学生表的sid
CREATE TABLE connScore(
id INT,
sid INT,
sscore DOUBLE,
CONSTRAINT fk_connScore_connStudent FOREIGN KEY(sid) REFERENCES connStudent(sid)
);
-- 第二种增加外键方式: 
ALTER TABLE connScore ADD CONSTRAINT fk_connStudent_connScore FOREIGN KEY(sid) REFERENCES connStudent(sid);

表与表的关系

表与表的关系有三种：一对一，一对多（多对一），多对多。在确定表与表的关系的时候需要确定主表和从表，依赖性更强的是从表，必须存在的是主表，这样就可以知道外键该设置在哪个表中了。

一对多：一个部门可以有多个员工，一个员工只能属于一个部门；

一对多建表原则：在多的一方创建外键对应一的一方的主键。

多对多：一个学生可以选择多门课程，一门课程可以被多个学生选择；

多对多建表原则：创建中间表，中间表中至少有两个字段分别作为外键指向多对多双方的主键。（主键对应）

一对一：一般都建一张表。(垂直切分, 分表, 减小数据量, 提高部分数据查询速度)

一对一建表原则：唯一外键对应/主键对应。（不常用，且可合并）

4. 多表查询

合并结果集

合并结果集是将两个或多个列类型、列数对应的表进行拼接查询。关键字：UNION,UNION ALL

-- 创建a, b 表, 并插入部分数据
CREATE TABLE a(
  NAME VARCHAR(20),
  score INT
);
CREATE TABLE b(
  NAME VARCHAR(20),
  score INT
);
INSERT INTO A VALUES('a',10),('b',20),('c',30);
INSERT INTO B VALUES('a',10),('b',20),('d',40);

-- 合并结果集去重
SELECT * FROM a 
UNION 
SELECT * FROM b;


-- 只合并不去重
SELECT * FROM a
UNION ALL
SELECT * FROM b;

在这里插入图片描述

连接查询

在这里插入图片描述

连接查询会产生笛卡尔积，假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

-- 可以使用主外键作为条件来去除无用信息。
SELECT * FROM emp,dept WHERE emp.deptno=dept.deptno ;

内连接(inner join)

上面的连接语句就是内连接，但它不是SQL标准中的查询方式，将上面内连接查询的语句使用SQL标准写为：

SELECT e.ename,e.sal,e.comm,d.dname FROM emp e INNER  JOIN dept d ON  e.deptno=d.deptno;

有筛选条件下: 取出两张表中匹配到的数据，匹配不到的不保留

在这里插入图片描述

外连接(outer join)

有筛选条件下: 取出连接表中匹配到的数据，匹配不到的也会保留，其值为NULL

外连接可以分为左连接和右连接，两种连接方式大同小异，只是左连接按照条件查询时参照左边的表的值，右连接按照参照条件参照右边表的值。

SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

因为左表中没有40这个部门，所以没有查出deptno=40这条记录：

在这里插入图片描述

SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

因为右表中没有50这个部门，所以没有查出deptno=50这条记录：

在这里插入图片描述

连接查询可以是两张表, 三张表, N张表的连接查询. 通常查询需要使用条件来取出不需要的记录. 通常条件都是主外键筛选

两张表的连接查询一定有一个主外键关系, 三张表连接查询就一定有两个主外键关系.

全连接（full join）

全连接（full join）结合的左，右外连接的结果。连接表将包含的所有记录来自两个表，并使用NULL值作为两侧缺失匹配结果

SELECT * FROM A FULL JOIN B on A.id=B.id

MySQL不支持FULL JOIN

交叉连接(cross join)

用于生成两张表的笛卡尔结果集，结果集为左表中的每一行与右表中的所有行组合。

select * from A , B

select * from A cross join B

select * from A , B语句就是返回笛卡尔结果集，等同于 select * from A cross join B

自连接

连接自身表, 需配合别名使用, 自己一个表当作两个表来用

SELECT * FROM A a1, A a2 WHERE a1.id=a2.id

自然连接（natural join）

自然连接(Natural join)是一种特殊的等值连接，它要求两个关系中进行比较的分量必须是相同的属性组，并且在结果中把重复的属性列去掉。而等值连接并不去掉重复的属性列。

如：A中a,b,c字段，B中有c,d字段，则select * from A natural join B 相当于select A.a,A.b,A.c,B.d from A.c = B.c。

select * from A natural join B
select A.a,A.b,A.c,B.d from A.c = B.c

子查询

子查询: 一个select语句中包含另一个完整的select语句, 子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了

子查询出现的位置

WHERE 后, 作为被查询的条件的一部分
FROM 后, 作表

当子查询出现再WHERE后作为条件是, 还可以使用如下关键字: any , all
子查询结果集的形式:

单行单列(用作条件)
单行多列(用作条件)
多行单列(用作条件)
多行多列(用作表)

-- 查询JONES的工资
SELECT sal FROM emp WHERE ename="JONES";
-- 查询工资高于JONES的员工
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename="JONES");
-- 查询与SCOTT同部门的员工
SELECT * FROM emp WHERE job IN (SELECT job FROM emp WHERE ename="SCOTT");
-- 工资高于30号部门的所有人的员工的信息
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
-- 也可以这样写
SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno=30);
-- 查询工作和工资与MARTIN（马丁）完全相同的员工信息
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename="MARTIN");
-- 有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);

-- 查询 工号为7788的员工姓名, 薪水, 部门, 部门地址
SELECT e.ename,e.sal,d.dname,d.loc 
FROM emp e,(SELECT dname,loc,deptno FROM dept) d
WHERE e.deptno=d.deptno AND e.empno=7788;

5. Mysql函数大全

MySQL函数大全及用法示例 - 雨林少爷 - 博客园 (cnblogs.com)

-- 求绝对值
select ABC (-1);
-- 求ASCLL
select ASCLL ('a');
-- 时间相关
select NOW(),current_date(),current_time();

在这里插入图片描述

自定义函数

delimiter //
CREATE FUNCTION f2(
	i1 int,
	i2 int
)
returns int;
BEGIN
-- DECLARE 用于声明变量
	declare num int default 0;   
	set num = i1+i2;
	return(num);
END//
delimiter;
--执行函数
select f2(100,90);

数据的加密和解密方式

**双向加密: **

encode / decode

-- 传入两个值，一个是要加密的记录，一个是加密和解密的key
-- 加密
SELECT ENCODE('sname','myname');
-- 解密
SELECT DECODE(ENCODE('sname','myname'),'myname');

在这里插入图片描述

AES_ENCRYPT/AES_DECRYPT

-- 这种加密算法使用AES（高级加密标准），使用key_str加密，key_str的长度可以达到256位，加密的结果是一个二进制的字符串
-- 加密
SELECT AES_ENCRYPT('kkll','clean');
-- 解密
SELECT AES_DECRYPT(AES_ENCRYPT('kkll','clean'),'clean');

在这里插入图片描述

DES_ENCRYPT/DES_DECRYPT

-- 这种加密方法使用了3DES（三重加密数据算法），加密时可以选择使用key_num还是key_str
-- 加密
SELECT DES_ENCRYPT('kkll',6),DES_ENCRYPT('kkll','pwd');
-- 解密
SELECT DES_DECRYPT('kkll',6),DES_DECRYPT('kkll','pwd');

在这里插入图片描述

单向加密

MD5加密

SELECT MD5('kkklll');

在这里插入图片描述

时间戳与日期互相转换

时间戳转换成日期 FROM_UNIXTIME

SELECT FROM_UNIXTIME(1429063399,'%Y年%m月%d日');

日期转化为时间戳 UNIX_TIMESTAMP

SELECT UNIX_TIMESTAMP('2015-04-15');

MySQL常用时间格式

在这里插入图片描述

6. Mysql 事务

什么是事务

数据库中的事务就是指对数据库执行一系列操作, 在同一个事务中, 这些操作最终要么全部执行成功, 要么全部失败, 不存在部分成功的情况.

事务的特性原则–ACID

原子性(Atomicity)

事务的整个过程就和一个原子操作一样, 不可分割, 最终要么全部成功，或者全部失败. 原子性是从最终结果来看的，从最终结果来看这个过程是不可分割的。
一致性(Consistency)

事务必须保证数据库从一个一致性状态转换到另一个一致性的状态. 一致性: 指的就是数据处于一种有意义的状态, 这种状态是语义上的不是语法上的.

以银行转账举例, 一个账户向另一个账户转账, 一个账户的钱少了, 另一个账户的钱就必须增加; 不能有一个账户的钱平白无故的少了

所谓一致性，即，从实际的业务逻辑上来说，最终结果是对的、是跟程序员的所期望的结果完全符合的
隔离性(Isolation)

不同事务之间的执行互不干扰. 一个事务内部的操作相对于其他事务来说是隔离的. 事务中的不同的隔离级别
- 读未提交
- 读已提交
- 可重复读
- 串行行
持久性(Durability)

事务一旦提交, 数据库的修改就是永久性的. 数据会持久化到磁盘

事务的分类

你能想象到吗？就这么个破事务还会分以下这么多种：

扁平事务

最简单的一种, 实际开发中使用最多的一种事务. 在这种事务中, 所有操作都处于同一层次, 不能提交或回滚事务的某一部分
带有保存点的扁平事务

除了支持扁平事务支持的操作外, 允许事务在执行过程中回滚到同一事务中较早的一个状态. 这种操做是通过增加保存点来实现的
链事务

指回滚时,只能回复到最近一个保存点; 而带有保存点的扁平事务可以回滚到任意正确的保存点
嵌套事务

嵌套事务，就是在事务中再嵌套事务. 位于根节点的事务称为顶层事务, 事务的前驱称为父事务，事务的下一层称为子事务

子事务既可以提交也可以回滚，但是它的提交操作并不马上生效，直到其父事务提交. 因此就可以确定，任何子事务都在顶层事务提交后才真正的被提交了。同理，任意一个事务的回滚都会引起它的所有子事务一同回滚。
分布式事务

分布式事务通常是指在一个分布式环境下运行的扁平事务，因此需要根据数据所在位置访问网络中的不同节点

Mysql中事务的操作

事务分隐形事务和显性事务, 是否开启隐形事务可由 修改变量autocommot控制

Mysql 默认是隐形事务, 执行 insert, update, delete 操作时, 数据库会自动开启事务, 提交, 回滚操作

隐形事务: 执行 insert, update, delete 操作时, 数据库会自动开启事务, 提交, 回滚操作

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit   | ON   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

**显性事务: **事务需要手动开启, 提交或回滚, 由开发者自己控制

方式1: 设置不自动提交事务, 执行修改操作后, 手动commit/rollback

//设置不自动提交事务
set autocommit=0;
//事务中的增删改操作
...
//提交/回滚
commit|rollback;

// 提交事务
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

// 回滚事务
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

方式2: 开启事务, 执行修改操作, 最后提交/回滚

start transaction;//开启事务
//执行事务操作
commit|rollback;

`savepoint` 关键字

在事务中我们执行了一大批操作，可能我们只想回滚部分数据，怎么做呢？

我们可以将一大批操作分为几个部分，然后指定回滚某个部分。可以使用savepoin来实现

start transaction;  //开启事务
...;  //执行操作1
savepoint p1; //设置一个保存点p1
...;  //修改操作2
savepoint p2; //设置一个保存点p2
rollback to p1;  // 回滚到 保存点p1
commit

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> savepoint part1;//设置一个保存点
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test1 values (2);
Query OK, 1 row affected (0.00 sec)

mysql> rollback to part1;//将savepint = part1的语句到当前语句之间所有的操作回滚
Query OK, 0 rows affected (0.00 sec)

mysql> commit;//提交事务
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test1;
+------+
| a   |
+------+
|   1 |
+------+
1 row in set (0.00 sec)

savepoint需要结合rollback to sp1一起使用，可以将保存点sp1到rollback to之间的操作回滚掉

只读事务

start transaction read only;
SELECT ...; // 读操作
...; // 增删改操作, 会报错
commit;

事务中会出现的问题

脏写(多个事务更新同一条数据, 最后提交导致前边事务提交不生效)
脏读(当前事务读取到其他事务未提交的数据)
不可重复读(当前事务重复读,多次结果不一致)
幻读(当前事务重复查询, 多次查询结果数量不一致; 查询不存在, 却不能插入的问题)

事务的隔离级别

当多个事务同时进行的时候, 要保证当前事务中数据的正确性, 需要依靠事务的隔离级别来保证, 不同隔离级别中产生的效果不一样

事务隔离级别主要是解决了上面多个事务之间数据可见性及数据正确性的问题。

数据库定义了四种事务的隔离级别

读未提交: READ-UNCOMMITED

读未提交情况下，可以读取到其他事务还未提交的数据，多次读取结果不一样，出现了脏读、不可重复读、幻读
读已提交: READ-COMMITED

读已提交情况下，无法读取到其他事务还未提交的数据，可以读取到其他事务已经提交的数据，多次读取结果不一样，不会出现脏读，出现了不可重复读、幻读
可重复读: REPEATABLE-READ

可重复读情况下，不能读到其他事务提交修改的数据, 未出现脏读，未读取到其他事务已提交的数据，多次读取结果一致，即可重复读
串行: SERIALIZABLE

SERIALIZABLE会让并发的事务串行执行（多个事务之间读写、写读、写写会产生互斥，效果就是串行执行，多个事务之间的读读不会产生互斥）。

以上四种隔离级别的隔离性越来越强, 会导致数据库的并发性也越来越低

查看, 设置隔离级别

mysql> show variables like 'transaction_isolation';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit   | ON   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

修改设置

修改mysql 中的my.init文件, 我们奖隔离级别设置为: READ-UNCOMMITED, 如下

transaction_isolation=READ-UNCOMMITED

以管理员身份打开cmd窗口, 重启mysql

net stop mysql

net start mysql

各种隔离级别会出现的问题

隔离级别	脏读可能性	不可重读可能性	幻读可能性
READ-UNCOMMITED	有	有	有
READ-COMMITED	无	有	有
REPEATABLE-READ	无	无	有
SERIALIZABLE	无	无	无

关于隔离级别的选择

需要对各种隔离级别产生的现象非常了解，然后选择的时候才能游刃有余
隔离级别越高，并发性也低，比如最高级别SERIALIZABLE会让事物串行执行，并发操作变成串行了，会导致系统性能直接降低。
具体选择哪种需要结合具体的业务来选择。
读已提交（READ-COMMITTED）通常用的比较多。
默认隔离级别：如果没有指定隔离级别，数据库就会使用默认的隔离级别。在MySQL中，如果使用 InnoDB，默认的隔离级别是Repeatable Read。

7. Mysql存储引擎

存储引擎是数据库的核心，对于mysql来说，存储引擎是以插件的形式运行的。数据库管理系统使用存储引擎进行创建、查询、更新和删除数据。

-- 查看MySQL使用的引擎
show engines;
-- 查看数据库默认使用的引擎
show variables like '%storage_engine%';
-- 查看所有表使用的引擎
show table status;

Mysql各种存储引擎对比总结 (baidu.com)

8. MySQL循环语句

mysql常见的三种循环方式：while、repeat和loop循环。还有一种goto，不推荐使用。

1. while … end while循环

delimiter //                            #定义标识符为双斜杠
drop procedure if exists test;          #如果存在test存储过程则删除
create procedure test()                 #创建无参存储过程,名称为test
begin
    declare i int;                      #申明变量
    set i = 0;                          #变量赋值
    while i < 10 do                     #结束循环的条件: 当i大于10时跳出while循环
        insert into test values (i);    #往test表添加数据
        set i = i + 1;                  #循环一次,i加一
    end while;                          #结束while循环
    select * from test;                 #查看test表数据
end
//                                      #结束定义语句
call test();                            #调用存储过程

2. repeat … until …end repeat循环

delimiter //                            #定义标识符为双斜杠
drop procedure if exists test;          #如果存在test存储过程则删除
create procedure test()                 #创建无参存储过程,名称为test
begin
    declare i int;                      #申明变量
    set i = 0;                          #变量赋值
    repeat
        insert into test values (i);    #往test表添加数据
        set i = i + 1;                  #循环一次,i加一
    until i > 10 end repeat;            #结束循环的条件: 当i大于10时跳出repeat循环
    select * from test;                 #查看test表数据
end
//                                      #结束定义语句
call test();                            #调用存储过程

3. loop循环

lp:loop
	...
	if 条件 then
		leave lp;
    end if;
end loop;

delimiter //                            #定义标识符为双斜杠
drop procedure if exists test;          #如果存在test存储过程则删除
create procedure test()                 #创建无参存储过程,名称为test
begin
    declare i int;                      #申明变量
    set i = 0;                          #变量赋值
    lp : loop                           #lp为循环体名,可随意 loop为关键字
        insert into test values (i);    #往test表添加数据
        set i = i + 1;                  #循环一次,i加一
        if i > 10 then                  #结束循环的条件: 当i大于10时跳出loop循环
            leave lp;
        end if; 
    end loop;
    select * from test;                 #查看test表数据
end
//                                      #结束定义语句
call test();                            #调用存储过程

EGEGEGEGEG

关注

21
点赞
踩
20

收藏

觉得还不错? 一键收藏
0
评论
MySQL 知识点总结

fill:#333;color:#333;color:#333;fill:none;ClientMySQLDB1DB2表1表2表3表4客户端 --> DBMS(数据库管理系统) --> DB(数据库) --> table…数据库中的事务就是指对数据库执行一系列操作, 在同一个事务中, 这些操作最终要么全部执行成功, 要么全部失败, 不存在部分成功的情况.
复制链接

扫一扫