## 前言本文是基于mysql 8.0。接着 - [【前端运维】打通任督二脉！（上篇）](https://juejin.cn/post/7197043415145840698 "https://juejin.cn/post/7197043415145840698") - [【前端运维】打通任督二脉！（nginx篇和nodejs + go）](https://juejin.cn/post/7197424757216968759) ## ORM 有些不了解ORM的同学可以理解为，相当于用低代码搭界面，低代码是更抽象的工具去做js，html和css的事。在这里，意思是更抽象的工具去做mysql原生的事。有人会说，我用nodejs都会使用ORM去写SQL，不用学原生SQL，这个就大错特错了，ORM工具你去它的github上看，有多少issue它是没有解决的，这种东西你不会原生SQL，出了问题首先排查非常困难，然后你发现了你难道去等人家把issue修复了才上线？还有就是稍微复杂点的联表，ORM写出的SQL性能差 ## SOL语句的分类和特点刚开始我也不重视这个分类和特点，后来学sql就学混了，感觉有了分类后更好记忆sql语句一些。 SQL语言共分为四大类：数据查询语言（DQL），数据操纵语言（DML），数据定义语言（DDL）和数据控制语言（DCL）大家看，这些都是DxL，只有中间的x不一样，D指的是Data，数据的意思，L是指Language，语言。 ### DQL 所以我们先看DQL，Q指的是查询，Query 我们常见的什么SELECT，FORM，WHERE都是查询表的，我们可以简单理解为就是DQL语句 ``` SELECT select_list [ INTOnew_table ] FROM table_source [ WHERE search_condition ] [ GROUPBY group_by_expression ] ``` ### DDL 中间的D指的是Definition,定义的意思，我们简单理解为就是创建库创建表的语言 ``` CREATE 创建 ALTER 修改 DROP 删除 COMMENT 注释 ``` ### DML 　M是manipulation，操纵的意思，我们简单理解为对数据表里的每一条数据：添加更新和删除操作 ``` INSERT 添加 UPDATE 更新 DELETE 删除 ``` ### DCL 　　C是control的意思，简单理解就是mysql里的事务。 ``` COMMIT 提交 ROLLBACK 回滚 SET TRANSACTION 设置当前事务的特性，它对后面的事务没有影响 ``` ## mysql登录举个例子：在命令行界面输入以下命令登录MySQL： ``` mysql -u username -p ``` 其中，"username"是你的MySQL用户名，"-p"表示需要输入密码。接下来，你会被提示输入密码。输入密码并回车后，如果登录成功，你将看到MySQL提示符，表示你已经成功登录到MySQL服务器了。如果你登录的不是本机，那么需要 -h参数。 "-h"是MySQL命令行登录中的一个选项，用于指定MySQL数据库服务器的主机名或IP地址。 ## DDL ### 创建数据库 #### 直接创建数据库语法: 注意中括号里的语句是可选的 ``` CREATE DATABASE [IF NOT EXISTS] 数据库名 ``` 虽然是可选，但是我建议加上去，要不数据不存在会报错。 >对比js 其实就是面向对象 new 一个新的数据库 #### 指定字符集的方式创建数据库 5.7的时候，mysql默认字符集简单来说只支持英文，所以一般都要把字符集设置为utf8，支持所有的语言。mysql 8是默认utf8mb4，我们肯定用8啊，5.7人家官方都放弃维护了。这里简单说一下 utf8和utf8mb4的区别： UTF-8和UTF8MB4是MySQL中的两种字符集。 - UTF-8：这是一种常见的Unicode字符集，可以表示大多数字符，但不能表示某些特殊的字符（如Emoji字符）。 - UTF8MB4：这是一种扩展的UTF-8字符集，可以表示所有的Unicode字符，包括Emoji字符。当您需要存储更多字符，特别是Emoji字符时，建议使用UTF8MB4字符集。在使用UTF8MB4字符集时，您需要使用4个字节来存储一个字符，而使用UTF-8字符集则只需要使用3个字节。因此，如果您的数据存储需求中需要存储更多字符，则使用UTF8MB4字符集可能会带来更多的空间开销。语法： ``` CREATE DATABASE [IF NOT EXISTS] 数据库名 CHARACTER 字符集 ``` >对比js 其实就是面向对象 new 一个新的数据库，然后传参字符集是啥 #### 排序规则我们拿utf8字符集指定的utf8_general_ci和utf8_bin对数据排序的规则来说明： - utf8_general_ci是指对大小写不明感，a和A会在字符集里判断一致。 - utf8_bin则要区分语法： ``` CREATE DATABASE [IF NOT EXISTS] 数据库名 CHARACTER 字符集 COLLATE utf8_bin ``` >对比js 这个就是传参传了字符集和排序规则 ### 查看数据库 #### 查看所有数据库 ``` SHOW databases ``` #### 查看指定数据库可以看到建表语句和编码方式等信息 ``` SHOW CREATE DATABASE 数据库名 ``` #### 修改数据库这个很少用到啊，感觉改这个是个大事情。只能修改数据库的字符集和排序规则，不能修改数据库的名字语法： ``` ALTER DATABASE 数据库名 CHARACTER SET 字符集 [COLLATE 排序规则] ``` >对比js，就是你把对象的值给改了，就是一个setter方法改内部的值 #### 删除数据库语法： ``` DROP DATABASE 数据库名 ``` >对比js，就是把你创建的实例对象让系统垃圾回收 ### 使用数据库 #### 查看正在使用的数据库语法 ``` SELECT DARAVASE() ``` #### 切换数据库 ``` USE 数据库名 ``` ### 操作表 #### 创建表 ``` CREATE TABLE 表名（字段名1 数据类型，字段名2 数据类型，....） ``` > 对比js, 也是类似面向对象创建一个表的实例，传入实例的参数就是字段名和数据类型例如: ``` CREATE TABLE student { id INT, name VARCHAR(20), birthday date } ``` 这里我们把所有数据类型整理一下: **整型** 先从最基本的数据类型整型说起，首先用一张表格归纳一下： | 数据类型 | 字节数 | 带符号最小值 | 带符号最大值 | 不带符号最小值 | 不带符号最大值 | | --------- | --- | -------------------- | ------------------- | ------- | -------------------- | | TINYINT | 1 | -128 | 127 | 0 | 255 | | SMALLINT | 2 | -32768 | 32767 | 0 | 65535 | | MEDIUMINT | 3 | -8388608 | 8388607 | 0 | 16777215 | | INT | 4 | -2147483648 | 2147483647 | 0 | 4294967295 | | BIGINT | 8 | -9223372036854775808 | 9223372036854775807 | 0 | 18446744073709551616 默认整数是有正负的，如果需要纯正数，要加一个unsigned关键字。 **浮点型** **数据类型** | **字节数** | **备注** | | -------- | ------- | ------ | | float | 4 | 单精度浮点型 | | double | 8 | 双精度浮点型这里注意，浮点型可以说明到底精确到几位 ``` CREATE TABLE test_float ( num float(5, 2) ) engine=innodb charset=utf8; ``` 从这个结果我们总结一下float(M,D)、double(M、D)的用法规则： - D表示浮点型数据小数点之后的精度，假如超过D位则四舍五入，即1.233四舍五入为1.23，1.237四舍五入为1.24 - M表示浮点型数据总共的位数，D=2则表示总共支持五位，即小数点前只支持三位数 **定点数类型** 定点类型是decimal，用法和上面的浮点数一样 - decimal(M,D)的规则和float/double相同，但区别在float/double在不指定M、D时默认按照实际精度来处理而decimal在不指定M、D时默认为decimal(10, 0) **日期类型** 接着我们看一下MySQL中的日期类型，MySQL支持五种形式的日期类型：date、time、year、datetime、timestamp，用一张表格总结一下这五种日期类型： | 数据类型 | 字节数 | 格式 | 备注 | | --------- | --- | ------------------- | ------------- | | date | 3 | yyyy-MM-dd | 存储日期值 | | time | 3 | HH:mm:ss | 存储时分秒 | | year | 1 | yyyy | 存储年 | | datetime | 8 | yyyy-MM-dd HH:mm:ss | 存储日期+时间 | | timestamp | 4 | yyyy-MM-dd HH:mm:ss | 存储日期+时间，可作时间戳 | **字符类型：char和varchar类型** char很坑，存储的是字节，但是我们一个汉字并不对应一个字节。varchar是按字符来的，所以我们通常都是用varchar 1. char是固定长度字符串，其长度范围为0-255且与编码方式无关，无论字符实际长度是多少，都会按照指定长度存储，不够的用空格补足；varchar为可变长度字符串，在utf8编码的数据库中其长度范围为0-21844 3. char实际占用的字节数即存储的字符所占用的字节数，varchar实际占用的字节数为存储的字符+1或+2或+3 4. MySQL处理char类型数据时会将结尾的所有空格处理掉而varchar类型数据则不会 ### 查看表 #### 查看所有表 ``` show tables ``` #### 查看表结构 ``` DESC 表名 ``` #### 查看建表语句 ``` SHOW CREATE TABLE 表名; ``` 建表默认的引擎是innodb，默认数据是null填充，默认排序是对大小写不敏感的。 #### 复制表结构语法： ``` CREATE TABLE 新表名 LIKE 旧表名 ``` #### 删除表 ``` DROP TABLE 表名 ``` ## DML 插入记录 ``` INSERT INTO 表名 (字段名1，字段名2...) VALUES (值1，值2...) ``` 注意，除了数值类型外，其它的字段类型的值必须使用引号引起来。 ## 蠕虫复制在已有数据基础之上，将原来的数据进行复制，插入到对应表中。注意，需要建立一张跟源表一样结构的表进行复制。 ``` INSERT INTO 表名1 SELECT * FROM 表名2 ``` ### 更新表记录 #### 不带条件的更新 ``` UPDATE 表名 SET 字段1=值1[字段2=值2...] ``` 整个列的值都会变 #### 带条件更新 ``` UPDATE 表名 SET 字段1=值1[字段2=值2...][WHERE 条件] ``` ### 删除记录 #### 带条件删除删除某些行的数据： ``` DELETE FROM 表名 WHERE 条件 ``` #### 不带条件删除语法 ``` DELETE FROM 表名 ``` 会删除表中所有数据 ## DQL 数据查询语言 ### 单表查询 ### 简单查询 #### 查询表中全部数据语法 ``` SELECT * FROM 表名 ``` 对比js,这个就是for循环语句 ``` const result = []; for(let i = 0; i < 学生表数组.length; i++) { result.push(学生表数组[i]) } return result; ``` #### 查询表中具体的列的全部数据当然，你可以查询具体字段： ``` SELECT 字段名1，字段名2，...字段名n FROM 表名 ``` 对比js,这个就是for循环语句 ``` // 下面的pick函数，就是从一个对象里取对应属性名的函数 // 很多工具库都有，比如loadash，自己写一个也很简单 const result = []; for(let i = 0; i < 学生表数组.length; i++) { result.push(pick(学生表数组[i], ...所有字段)) } return result; ``` #### 别名查询别名就是给列的名字起一个自定义的名字，我们通常使用AS关键字来实现语句 ``` SELECT 字段名1 AS 别名，字段2 AS 别名... FROM 表名 AS 表的别名 ``` #### 去除重复值在查询指定列时，如果指定的列中有重复的数据，默认都会显示出来 ``` SELECT DISTINCT 字段名 FROM 表名 ``` 比如你查询的数据： ``` SELECT DISTINCT name FROM student ``` 就会把所有name字段同名的行筛选掉 #### 查询结果参与运算在查询语句中，查询的列可以和其他值做数学运算（加减乘除）。运算结果只会影响展示，不会影响表中的数据。语法 ``` SELECT 列名1 + 固定值 FROM 表名 SELECT 列表1 + 列名2 FROM 表名 ``` 上面这个只限于数字相加，比如我们有个表是 ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/46ba854cba8c432a8440bdb58381d247~tplv-k3u1fbpfcp-watermark.image?) 然后我们在age列 + 5岁，那么就显示 ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/50cf87895a1c4c1da7200341f49e6b41~tplv-k3u1fbpfcp-watermark.image?) 当然，也可以通过别名让列名字好看一些。如下，顺便列与列相加也在其中 ![image.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a5dbcd3795fa4a6fb81ca2975e5bee24~tplv-k3u1fbpfcp-watermark.image?) #### 比较运算符在查询条件中，可以使用多种比较运算符来表示查询条件。比如有： - = 等于 -> 大于 - < 小于 - <= 小于等于 ->= 大于等于 - <> 或 - != 不等于 #### 逻辑运算符 - and 或者 && 多个条件同时满足 - or 或者 || 多个条件其中一个满足 - not 或者 ! 不满足 #### 指定范围内查询 in 语法 ``` SELECT 字段名 FROM 表名 WHERE 字段 [not] in (数据1，数据2...) ``` #### 模糊查询语法 ``` SELECT * FROM 表名 WHERE 字段名 like “通配符字符串” ``` - % 表示 0 个或多个字符（任意个字符） - _ 表示一个字符 #### 为空查询 is null 查询某个字段为空(null)的数据，不是使用 = null（null和任何值都不相等），而是使用is null 例如： ``` SELECT * FROM student WHERE english IS null ``` ### 排序排序语句是在SELECT语句后执行语法 ``` SELECT 字段名 FROM 别名 [WHERE 条件] ORDER BY 字段名 [ASC|DESC] ``` - ASC 默认升序 - DESC 降序 ## 单行函数单行函数是指对每一行数据进行计算后得到一行输出结果。 ### 数值函数 #### abs(x) 求绝对值例如 ``` SELECT ABS(-1) -- 返回1 ``` #### ceil(x) 向上取整例如 ``` SELECT CEIL(1.5) --返回2 ``` #### floor(x) 向下取整 #### round(x) 四舍五入取整 ### 字符串函数 #### concat(s1, s2,...sn) 字符串s1,s2等多个字符串合并为一个字符串 #### locate(s1, s) 从s字符串中获取s1的开始位置 #### lower(s) 将s字符串所有字母变为小写字母 #### upper(s) 将字符串转换为大写 #### replace(s, s1, s2) 将字符串s2替代字符串s找那个的字符串s1 #### substr(s, start, length) 从字符串s的start位置截取长度为length的子字符串 #### trim(s) 去掉首尾空格 ### 聚合函数聚合函数查询是纵向查询，它是对一列的值进行计算，饭后返回一个结果值，聚合函数忽略空值 #### count 统计指定列记录数，记录为NULL的不统计 #### SUM 计算指定列的数值和，如果不是数值类型，那么计算结果为0 #### MAX 计算指定列的最大值 #### MIN 计算指定列的最小值 #### AVG 计算指定列的平均值，如果不是数值类型，那么计算结果为0 ### 分组查询分组查询是指使用GROUP BY语句对查询信息进行分组，相同数据作为一组。 GROUP BY语句执行顺序在WHERE语句之后，SELECT语句之前语法 ``` SELECT 字段1，字段2...FROM 表名 GROUP BY 分组字符安 [HAVING 条件] ``` GROUP BY将分组字段结果中相同内容作为一组示例 ``` SELECT * FROM student3 GROUP BY age ``` 将age相同分为一组上面的用法是错误的，分组的目的是为了统计，一般会跟聚合函数一起使用。例如： ``` SELECT SUM(math), sex FROM student GROUP BY sex; ``` ### WHERE和HAVING的对比 - having是在分组后对数据进行过滤，where是在分组前 - having语句中可以使用聚合函数，where后面不可以使用聚合函数 ### limit LIMIT是限制查询记录的条数，并且要放在SQL语句的最后语法： ``` LIMIT offset, length;或者 limit length ``` offset是偏移量，可以认为是跳过的记录数量 ## 总结查询语句的书写顺序和执行顺序 ### 书写顺序 SELECT 字段 FROM 表名 WHERE 条件 GROUP BY 字段 HAVING 条件 ORDER BY 字段 LIMIT offset，length ### 执行顺序 - from表名 - where条件 - group by 字段 - having 条件 - select 字段 - order by 字段 - limit ## 约束和策略 ### 主键约束 #### 主键的作用用来唯一标识一条记录，比如一个人身份证号就算。每个表都应该有一个主键，并且每个表只能有一个主键。通常不用业务字段作为主键，单独给每张表设计一个id的字段，把id作为主键，主键是给数据库和程序使用的，不是给最终的客户使用的，所以主键没有含义没有关系，主要不重复，非空即可。 #### 建表时添加主键 ``` create table stu1 ( id int primary key, name varchar(20) ) ``` ``` create table stu1 { id int, name varchar(20), primary key(id) } ``` #### 主键自增策略 ``` create table stu1 ( id int primary key auto_increment, name varchar(20) ) ``` ### 非空约束 NOT NULL，表示添加数据时，此列必填 ``` mysql> CREATE TABLE tb_dept4 -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(22) NOT NULL, -> location VARCHAR(50) -> ); ``` ### 唯一约束添加唯一约束的列不能重复，但可以为null ``` CREATE TABLE Employees ( employee_name CHAR(50) NOT NULL, social_num INTEGER, phone INTEGER, UNIQUE (social_num) ) ``` ### 缺省约束语法为default ``` mysql> CREATE TABLE tb_dept3 -> ( -> id INT(11) PRIMARY KEY, -> name VARCHAR(22), -> location VARCHAR(50) DEFAULT 'Beijing' -> ); ``` ## 表关系现实生活中，实体与实体之间是存在关系的，比如：老公和老婆，部门和员工，老师和学生等，我们设计的表是对现实中实体的描述，那么我们在设计表的时候，就应该体现出来这种关系。表与表之间的关系主要包括：一对多（多对一），一对一，多对多三种。。 ### 一对多（多对一）例如：班级和学生，部门和员工，客户和订单，分类和商品等建表原则： ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/71935d8c388344548398bd33ed52cb07~tplv-k3u1fbpfcp-watermark.image?) 不一定是外键，就是a表的一列对应b表的主键即可构成这个关系 ### 多对多例如：老师和学生，学生和课程，用户和角色多对多关系，建表原则：需要创建第三张表，中间表中至少两个字段 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/7b691ba83ba1424d95529792a47ddef7~tplv-k3u1fbpfcp-watermark.image?) ## 多表查询 ### 笛卡尔乘积 MySQL中的笛卡尔乘积指的是在执行多个表之间没有指定条件的JOIN操作时，返回的结果是这些表中所有可能的组合。例如，假设我们有两个表A和B，其中A包含两个行（1和2），B包含三个行（A，B和C）， ``` SELECT * FROM A, B; ``` 则返回的结果集将包含6行，即： ``` 1 A 1 B 1 C 2 A 2 B 2 C ``` >转化为javascript就是两个表for循环 ``` const result = []; for(let i = 0; i < A表.length; i++){ for(let j = 0; j < B表.length; j++){ result.push(A[i], B[j]) } } ``` ### 多表查询要清除笛卡尔乘积就需要使用连接查询，连接查询分为内连接和外连接。 ![image.png](https://p9-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/4ecc2a0a55e9479d9d6158d961943785~tplv-k3u1fbpfcp-watermark.image?) ### JOIN语句介绍 ![image.png](https://p3-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/1df253ed6fc24774b6146f7e74bb1f99~tplv-k3u1fbpfcp-watermark.image?) 1. INNER JOIN INNER JOIN是最常用的JOIN类型，它只返回两个表中共有的行。它会查找两个表中都存在的值，并将这些值组合成一个结果集。例如，假设有两个表，一个表是"orders"，另一个表是"customers"。这两个表中都有"customer_id"列，可以使用INNER JOIN将这两个表中的数据合并在一起： ``` SELECT * FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id; ``` 这个其实就是在上面笛卡尔乘积的基础上做了筛选 >转化为javascript就是两个表for循环 ```javascript const result = []; for(let i = 0; i < orders.length; i++){ for(let j = 0; j < customers.length; j++){ if(orders[i].customer_id === customers[j].customer_id){ result.push(orders[i], customers[j]) } } } ``` 2. LEFT JOIN LEFT JOIN返回左边表中的所有行，即使在右边的表中没有匹配的行。如果没有匹配的行，右侧表的列将显示为NULL。例如，假设有两个表，一个表是"orders"，另一个表是"customers"。使用LEFT JOIN将所有订单以及订单所属客户的信息一起列出： ``` SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id; ``` >转化为javascript就是两个表for循环 ```javascript const result = []; for(let i = 0; i < orders.length; i++){ for(let j = 0; j < customers.length; j++){ if(orders[i].customer_id === customers[i].customer_id) { result.push(A[i], B[j]) } } } ``` 3. RIGHT JOIN RIGHT JOIN与LEFT JOIN相似，但是它返回右边表中的所有行，即使在左边的表中没有匹配的行。如果没有匹配的行，左侧表的列将显示为NULL。例如，假设有两个表，一个表是"orders"，另一个表是"customers"。使用RIGHT JOIN将所有客户的信息以及他们的订单一起列出： ``` SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id; ``` 4. FULL OUTER JOIN MySQL不支持FULL OUTER JOIN，但是可以使用UNION操作符将LEFT JOIN和RIGHT JOIN合并起来，以获得相同的结果。例如，假设有两个表，一个表是"orders"，另一个表是"customers"。使用UNION和LEFT JOIN、RIGHT JOIN来实现FULL OUTER JOIN： ``` SELECT * FROM orders LEFT JOIN customers ON orders.customer_id = customers.customer_id UNION SELECT * FROM orders RIGHT JOIN customers ON orders.customer_id = customers.customer_id; ``` ### UNION `UNION` 是 MySQL 数据库中用于合并两个或多个 SELECT 语句的操作符，它可以将两个或多个 SELECT 语句的结果集合并为一个结果集。`UNION` 会自动去除重复行，如果需要包含重复行可以使用 `UNION ALL`。例如 ``` SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; ``` - `SELECT column_name(s) FROM table1` 和 `SELECT column_name(s) FROM table2` 分别代表要合并的两个 SELECT 语句。 - `UNION` 操作符用于合并结果集，它返回两个结果集的并集，自动去除重复行。 - 如果希望保留重复行，可以使用 `UNION ALL` 操作符。 ### 子查询详细的练习请看这篇文章：https://juejin.cn/post/7083685848637505567 一条SELECT语句结果作为另一条SELECT语法的一部分语法 ``` SELECT 字段 ...FROM 表 WHERE 字段运算符 (SELECT 字段... FROM 表) ``` 单行子查询的结果是一个值。 ![image.png](https://p6-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/05061216236346b7a70a291bf888cb08~tplv-k3u1fbpfcp-watermark.image?) 单行运算符 =、>、>=、<、<=, != 多行运算符 in、any、all ### 多列子查询子查询结果是单列，在WHERE后面作为条件子查询的结果子查询的结果是多列，肯定在FROM后面作为表语法 ``` SELECT 查询字段 FROM （子查询）表别名 WHERE 条件 ``` ## 数据备份和还原我们在服务器上进行数据传输，数据存储和数据交换，就有可能产生数据故障，比如发生意外停机或存储介质损坏，如果没有CIA去数据备份和数据恢复手段的措施，就会导致数据的丢失。 ``` mysqldump -u 用户名 -p 密码数据库 > 文件的路径/文件名.sql ``` 注意，这个备份是表级别的备份，你还原的时候需要把库先创建出来。 ## 事务 ### 什么是数据库事务数据库事务是访问并可能操作数据项的一个数据库操作（增删改），这些操作要么全部执行，要么全部不执行，是一个不可分割的工作单位。 ### 事务的目的 - 为数据库操作提供一个从失败中恢复到正常状态 - 当多个应用程序在并发访问数据库时，可以在这些应用程序之间提供一个隔离方法，以防止彼此操作互相干扰 ### 操作事务 mysql中可以有两种方式进行事务的操作： - 自动提交事务 - mysql的每条增删改语句都是一个单独的事务，mysql默认情况下，在执行每条语句时都会自动开启一个事务，执行完毕自动提交事务， - 手动提交事务 - 事务有关的SQL语句 - start transaction; 开启事务 - commit; 提交事务 - rollback; 回滚事务示例： ``` start transaction; update xxx -- 因为这里没有提交事务，所以数据库的数据不会发生变化 ``` ### 事务的原理事务开启之后，所有的操作都会临时保存到事务日志，事务日志只有在得到commit命令之后才会同步到数据表中，其他情况都会清空事务日志（rollback，断开连接） ![image.png](https://p1-juejin.byteimg.com/tos-cn-i-k3u1fbpfcp/a5fceab754604ba68e701db0496be498~tplv-k3u1fbpfcp-watermark.image?) ### 事务的并发异常在事务并发操作时，可能出现的问题 - 回滚丢失（并发事务的问题） "回滚丢失"是指在MySQL事务中执行回滚操作时，如果在回滚之前已经提交了一些修改，那么这些修改将会丢失，也就是说回滚操作无法撤销这些已经提交的修改。例如，假设在一个MySQL事务中，首先执行了一些插入或更新操作，但在提交事务之前执行了回滚操作。如果在回滚之前已经有其他的连接或事务提交了一些修改，那么这些修改将不会被回滚，并且最终数据库中的数据将包含这些已经提交的修改。 - 覆盖丢失 "覆盖丢失"是指在MySQL中执行更新操作时，如果两个或多个并发的连接或事务同时修改同一行数据，其中一个修改会覆盖掉其他修改的结果，从而导致其他修改的结果丢失。例如，假设有两个并发的连接或事务同时尝试更新同一行数据，如果这两个连接或事务都执行了更新操作，那么最后一个更新操作的结果将会覆盖之前的更新操作的结果。这样一来，之前的更新操作的结果就被覆盖了，也就是发生了"覆盖丢失"。 - 脏读一个事务读取了另一个事务尚未提交的数据 - 不可重复读 "不可重复读"是指在MySQL中，在同一个事务中多次读取同一行数据，但是在此期间有其他事务修改了该行数据，导致前后两次读取得到的结果不同的情况。例如，假设在一个事务中执行了如下操作： 1. 读取某一行数据； 1. 另一个事务修改了该行数据； 1. 再次读取同一行数据。如果在第二次读取之前，有另一个事务修改了该行数据，则第二次读取得到的数据将与第一次读取的数据不同，从而导致不可重复读。 - 幻读 "幻读"是指在MySQL中，在同一个事务中多次执行相同的查询语句，但是在此期间有其他事务插入或删除了符合查询条件的行，导致前后两次查询结果不同的情况。例如，假设在一个事务中执行了如下操作： 1. 执行某个查询语句，返回一组符合条件的行； 1. 另一个事务插入了一些新的符合条件的行； 1. 再次执行相同的查询语句。如果在第二次执行查询语句之前，有另一个事务插入了新的符合条件的行，则第二次查询将会返回比第一次查询更多的行，从而导致幻读。 ### 隔离级别以为Mysql中数据库InnoDb存储引擎默认的隔离界别的Repeatable Read，所以我们特地看一下这个隔离级别能解决哪些并发异常 - 回滚丢失 - ok - 脏读 - ok - 不可重复读 - ok - 覆盖丢失 - ok - 幻读 - 按道理来说是不ok的，但是mysql也搞定了 ### 隔离级别的查看和修改查看 ``` SELECT @@transacation_isolation ``` 修改 ``` SELECT @@session.tx_isolation='隔离级别' ``` ## 创建用户语法 ``` CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码' ``` 修改密码 ``` ALTER USER USER() IDENTIFIED BY `new_password` ``` - 用户名：将创建的用户名 - 主机名：指定改用户在哪个主机上可以登录，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登录，可以使用通配符% - 密码：该用户的登录密码，密码可以为空，如果为空则该用户可以不需要密码登录服务器 ## 索引简单来说，索引就是目录，能快速帮我们查找到对应的数据。一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上虽然说索引大大提高了查询速度，同时也降低了表更新的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，Mysql不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的的键值变化后的索引信息 ### 普通索引使用起来没有限制，允许在所有数据类型上创建，可以有重复值和空值。一个索引只包含单个列，一个表可以有多个单列索引。 #### 建表时创建语法 ``` INDEX 索引名 (列名) ``` 示例 ``` create table student ( id int primary key, name varchar(20), age int, index student_name_index(name) ) ``` 直接创建 ``` CREATE INDEX 索引名 ON 表名(列名) ``` 测试是否创建成功 ``` show index from student2 ``` ### 唯一索引与普通索引类似，不同的是，索引列的值必须唯一，但允许有，空值。如果是组合索引，则列值的组合必须是唯一。语法 ``` UNIQUE INDEX 索引名（列名） ``` 参考 - 《mysql8.0从入门到精通》 - 作者：老刘大数据