# Mysql_notes **Repository Path**: lihaowen2017/Mysql_notes ## Basic Information - **Project Name**: Mysql_notes - **Description**: Mysql工具笔记包含mysql数据库设计及操作入门知识，8.0新特性，(公共表达式，窗口函数)，sql优化，以及数据库逻辑物理备份 - **Primary Language**: Unknown - **License**: Not specified - **Default Branch**: master - **Homepage**: None - **GVP Project**: No ## Statistics - **Stars**: 0 - **Forks**: 0 - **Created**: 2020-02-11 - **Last Updated**: 2021-11-02 ## Categories & Tags **Categories**: Uncategorized **Tags**: None ## README ## MySQL8.0基础 [toc] ### 数据库选型 #### SQL和NoSQL ##### 关系型数据库的特点 数据结构化存储在二维表中 知识事务的原子性A，一致性C，隔离性I，持久性D特性 支持使用SQL语言对存储在其中的数据进行操作。 ##### 关系型数据库的适用场景 数据存在着一定的关系，需要关联查询数据的场景。 需要事务支持的业务场景。(账户类) 需要使用SQL语言灵活操作数据的场景。 ##### 非关系型数据库的特点 存储结构灵活，没有固定的结构。 对事务的支持比较弱，但对数据的并发处理性能高。 大多不使用SQL语言操作数据 ##### 非关系型数据库的适用场景 数据结构不固定的场景(商品特征属性) 对事务要求不高，但读写并发比较大的场景。(日志交互，用户行为类) 对数据的处理操作比较简单的场景。 #### 关系型数据库选型原则 1. 数据库使用的广泛性 2. 数据库的可扩展性 支持基于二进制日志的逻辑复制 存在多种第三方数据库中间层，支持读写分离及分库分表。 3. 数据库的安全性和稳定性 MySQL主从复制集群，配合主从复制高可用架构，支持对存储在MySQL的数据进行分级安全控制。 4. 数据库所支持的系统 5. 数据库的使用成本 #### 关系型数据库结构设计 业务分析，逻辑设计，数据类型，对象命名，建立库表 ### mooc网示例 #### 业务分析： 课程的属性：{主标题，副标题，方向，分类，难度，最新，最热，时长，简介，人数，需知，收获，讲师名，讲师职位，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰} 课程列表的属性：{章名，小节名，说明，小节时长，章节url，视频格式}。 讲师属性：{讲师昵称，说明，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数} 问答评论属性：{类型，标题，内容，关联章节，浏览量，发布时间，用户昵称} 笔记的属性：{用户昵称，关联章节，笔记标题，笔记内容，发布时间} 用户的属性：{用户昵称，密码，说明，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数} 评价属性：{用户，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间} #### 宽表模式（对象属性全部存在一张表中） ##### 宽表模式存在的问题： 1. 数据冗余，相同数据在一个表中出现了多次，比如课程属性中讲师与课程是多对一关系，即课程表中会出现大量重复的讲师名。维护成本高。 2. 数据更新异常：修改一行中某列的值时，同时修改了多行数据。 3. 数据插入异常：部分数据由于缺失主键信息而无法写入表中。 4. 数据删除异常：删除某一数据时不得不删除另一数据。 修改列属性的时候造成问题。 ##### 宽表模式的应用场景 > 配合列存储的数据报表应用 #### 逻辑设计 ##### 数据库设计范式 第一范式：表中的所有字段都是不可再分的(不能有复合属性) 第二范式：表中必须存在业务主键，并且非主键依赖于全部业务主键。 业务主键：能唯一标识出每一行业务数据的列或列的组合 组合主键：当单一列无法唯一标识出一行的业务属性时，可以使用组合属性当主键： > 笔记表 > 用户 章节 标题 内容 用户积分 时间 > 张三 1-1 test1 内容1 300 2018-12-24 用户可能有多条笔记，标题可能重复，章节可能有多人写笔记，都无法唯一标识，即可以使用用户，章节，标题为组合主键。 用户积分与组合主键无依赖关系，则分为两张表 > 笔记表 > 用户 章节 标题 内容 时间 > 张三 1-1 test1 内容1 2018-12-24 > 用户表 > 用户 用户积分 > 张三 300 只有一个列作为主键列的表一定符合第二范式 第三范式：表中的非主键列之间不能相互依赖 >课程表 >课程名 方向 所属学院 讲师名 讲师职位 其中以课程名为唯一约束主键的话，讲师职位与讲师名存在依赖关系不符合第三范式则应新建表。 ##### 示例： 课程对象 课程的属性：{主标题，副标题，方向，分类，难度，最新，最热，时长，简介，人数，需知，收获，讲师名，讲师职位，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰} 按照三范式设计课程表 课程表：{主标题(PK)，副标题，方向(记录其它表的业务主键），分类(记录其它表的业务主键），难度(记录其它表的业务主键），上线时间，学习人数，时长，简介，需知，收获，讲师名，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰} >最新可以由上线时间计算获得 >最热可以由学习人数计算获得 讲师表(简)：{讲师名，讲师职位} 课程方向表：{课程方向名称（pk），添加时间} >不放入主表的原因，假设添加新的课程方向但却还未有新开设的课程会导致（宽表模式的问题3） 课程分类表：{分类名称（pk），添加时间} >同上 课程难度分级表：{难度名称（pk），添加时间} >同上 ##### 课程列表对象 课程列表的属性：{章节名，小节名，说明，小节时长，章节url，视频格式} > 组合主键章节名+小节名，因为章节下有很多小节，每章节的小节名称有可能相同都叫第一节，所需要使用组合主键。才能保证主键唯一性 说明只与章节名有关 小节时长，章节url，视频格式依赖于小节名 课程章节表：{章节名，说明，章节编号（pk）} 课程同章节的联系表：{课程主标题，课程章节名} // 为什么不用章节编号 课程小节表：{小节名，小节时长，章节url，视频格式，小节编号（pk）} 课程章节同小节关系表：{课程主标题，课程章节名，小节名称} // 为什么不用章节编号＋小节编号（小节名） ##### 讲师对象 讲师的属性:{讲师昵称(业务主键)，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数} 讲师表:{讲师昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数} ##### 用户对象 用户的属性:{用户昵称(业务主键)，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数} 用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数} ##### 用户对象 > 用户表和讲师表进行合并，否则会数据冗余 用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数，讲师标识} ##### 问答评论对象 问答评论属性:{类型，标题，内容，关联章节，浏览量，发布时间，用户昵称} > 问答评论对象的业务主键为组合主键: 标题+关联章节+用户昵称 > > 关联章节使用课程得列表对象的业务主键，课程名+章节名+小节名 问答评论表:{标题，课程主标题，课程章名，小节名称，用户昵称，父评论标题，内容，类型，浏览量，发布时间} > 父评论标题，评论回复的标题 ##### 笔记对象 笔记的属性:{用户昵称，关联章节，笔记标题，笔记内容，发布时间} > 用户昵称，关联章节，笔记标题作为组合业务主键来唯一标识笔记 > > 关联章节: 课程名+章节名+小节名 笔记表: {笔记标题，课程主标题，课程章名，小节名称，用户昵称，笔记内容，发布时间} ##### 评价对象 评价的属性:{用户昵称，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间} > 用户昵称，课程主标题作为业务主键 评价表: {用户昵称，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间} 用户选课表:{用户昵称，课程主标题，选课时间，累积听课时长} > 联合主键: 用户昵称，课程主标题 ##### 逻辑设计总结 课程表：{主标题(PK)，副标题，方向(记录其它表的业务主键），分类(记录其它表的业务主键），难度(记录其它表的业务主键），上线时间，学习人数，时长，简介，需知，收获，讲师名，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰} 课程章节表：{章节名，说明，章节编号（pk）} 课程同章节的联系表：{课程主标题，课程章节名} 课程小节表：{小节名，小节时长，章节url，视频格式，小节编号（pk）} 课程章节同小节关系表：{课程主标题，课程章节名，小节名称} 课程方向表：{课程方向名称（pk），添加时间} 课程分类表：{分类名称（pk），添加时间} 课程难度分级表：{难度名称（pk），添加时间} 用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数，讲师标识} 问答评论表:{标题，课程主标题，课程章名，小节名称，用户昵称，父评论标题，内容，类型，浏览量，发布时间} 笔记表: {笔记标题，课程主标题，课程章名，小节名称，用户昵称，笔记内容，发布时间} 评价表: {用户昵称，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间} 用户选课表:{用户昵称，课程主标题，选课时间，累积听课时长} #### 思考 如何获取出一门课程包括所有章节和小节的信息 > 课程表 --> 章节关联表 --> 章节表 --> 章节同小节关联表-->小节表 > > 在范式设计后我们发现 > > 查询一门课程包括所有章节和小节的信息需要查询五章表，会导致系统性能降低，此时需要进行反范式设计 ##### 反范式化设计 > 空间换时间，增加数据冗余来提高查询性能 例: 课程表和章节表(1--N) > 课程章节表:{课程章节名，说明，章节编号} > > 课程同章节的联系表:{课程主标题，课程章名} > > 课程章节表:{课程主标题，课程章名，章节说明，章节编号} ##### 课程小节表 例: 课程章节表和课程小节表(1--N) > 课程小节表:{小节名称，小节视频url，视频格式，小节时长，小节编号} > > 课程章同小节关系表:{课程主标题，课程章名，小节名称} > > 课程小节表:{课程主标题，课程章名，小节名称，小节视频url，视频格式，小节时长，小节编号} >此时获取出一门课程包括所有章节和小节的信息只需要查询三张表 > >课程表 / 课程章节表 / 课程小节表 ##### 反范式化设计总结 课程表：{主标题(PK)，副标题，方向(记录其它表的业务主键），分类(记录其它表的业务主键），难度(记录其它表的业务主键），上线时间，学习人数，时长，简介，需知，收获，讲师名，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰} 课程章节表:{课程主标题，课程章名，章节说明，章节编号} > 联合业务主键: 课程主标题，章节名称 课程小节表:{课程主标题，课程章名，小节名称，小节视频url，视频格式，小节时长，小节编号} > 联合业务主键: 课程主标题，课程章名，小节名称 课程方向表：{课程方向名称（pk），添加时间} 课程分类表：{分类名称（pk），添加时间} 课程难度分级表：{难度名称（pk），添加时间} 用户表:{用户昵称，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数，讲师标识} 问答评论表:{标题，课程主标题，课程章名，小节名称，用户昵称，父评论标题，内容，类型，浏览量，发布时间} 笔记表: {笔记标题，课程主标题，课程章名，小节名称，用户昵称，笔记内容，发布时间} 评价表: {用户昵称，课程主标题，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间} 用户选课表:{用户昵称，课程主标题，选课时间，累积听课时长} #### 物理设计 > 表中每列使用的数据类型，库，表命名 ##### MySQL常见的存储引擎 MYISAM 不支持事务 MySQL5.6之前的默认引擎，最常用的非事务型存储引擎。 CSV 不支持事务 以CSV格式存储的非事务型存储引擎，读写时会对整个表加锁；使用场景: 不同系统间的数据交换，不建议存储核心业务数据 Archive 不支持事务 只允许查询和新增数据而不允许修改的非事务型存储引擎；使用场景: 只支持select，insert操作；记录归档文件，日志文件，使用该存储引擎记录的数据所占物理内存比较小 Memory 不支持事务 是一种易失性非事务型存储引擎，有很强的IO性能，但mysql实例重启，则数据会消失，该存储引擎是mySQL内部存储临时数据的存储引擎 ###### INNODB 支持事务 是最常用的事务型存储引擎 Innodb存储引擎的特点 事务型存储引擎支持ACID，不能同时使用事务型存储引擎及非事务型存储引擎，由于某些原因事务型存储引擎发生回滚，而非事务引擎无法进行回滚，破坏了数据一致性要求 数据按主键聚集存储，建议使用自增ID作为主键，之前的业务主键不适合作为该引擎的主键 支持行级锁及MVCC，只在数据修改的所在行上进行上锁，而非MYISAM进行全表上锁，增加了数据处理的并发性 MVCC多版本并发控制，进一步避免读写操作的互相阻塞，适合高并发读写混合的业务场景 支持Btree和自适应Hash索引 支持全文索引和空间索引 ##### 根据Innodb特性优化后的表逻辑结构 课程表：{课程ID(Pk自增ID)，主标题，副标题，方向ID，分类ID，难度ID，上线时间，学习人数，时长，简介，需知，收获，讲师ID，课程图片，综合评分，内容实用，简洁易懂，逻辑清晰} 课程章节表:{章节ID，课程ID，章节名称，章节说明，章节编号} 课程小节表:{小节ID，课程ID，章节ID，小节名称，小节视频url，视频格式，小节时长，小节编号} 课程方向表：{课程方向ID，课程方向名称，添加时间} 课程分类表：{课程分类ID，分类名称，添加时间} 课程难度分级表：{课程难度ID，难度名称，添加时间} 用户表:{用户ID，用户昵称(非空唯一索引)，密码，性别，省，市，职位，说明，经验，积分，关注人数，粉丝人数，讲师标识} 问答评论表:{评论ID，父评论ID，课程ID，章节ID，小节ID，评论标题，用户ID，内容，类型，浏览量，发布时间} 笔记表: {笔记ID，课程ID，章节ID，小节ID，笔记标题，用户昵称，笔记内容，发布时间} 评价表: {评价ID，用户ID，课程ID，内容，综合评分，内容实用，简洁易懂，逻辑清晰，发布时间} 用户选课表:{用户选课ID，用户ID，课程ID，选课时间，累积听课时长} ###### 常用的整数类型 tinyint 1字节 -2^8(字节大小)-1 ~ 2^8-1 -1(有符号)，无符号大一倍 smallint 2字节 mediumint 3字节 int 4字节 bigint 8字节 ###### 常用的浮点类型 float 4个字节 非精确类型 double 8个字节 非精确类型 decimal 每4个字节存9个数字，小数点占一个字节 是精确类型 123456789.987654321 占9个字节4+1+4，最多允许存储65个数字，使用场景，财务类型 ###### 常用的时间类型 DATE 3字节 YYYY-MM-DD 从“1000-01-01”到“9999-12-31" TIME 3-6字节 HH: MM: SS [.微秒值] 从"-838:59:59"到“838:59:59” YEAR 1字节 YYYY 从1901到2155 DATETIME 5-8字节 YYYY-MM-DD HH:MM:SS[.微秒值] 从‘1000-01-01 00:00:00'到’9999-12-31 23:59:59' TIMESTAMP(包括时区) 4-7字节 YYYY-MM-DD HH:MM:SS[.微秒值] 从'1970-01-01 00:00:01'UTC到‘2038-01-19 03:14:07’UTC ###### 常用的字符串类型 Char(M) M=1~255个字符 固定长度 VarChar(M) 一行中所有varchar类型的列所占用的字节数不能超过65535个字节 存储可变长度的M个字符 存储可变长度的M个字符 TinyText 最大长度255个字节 可变长度 Text 最大长度65535个字节 可变长度 MediumText 最大长度16777215个字节 可变长度 LongText 最大长度4294967295个字节 可变长度 Enum 集合最大数目为65535 只能插入列表中的值 ##### 如何为数据选择合适的数据类型 优先选择符合存储数据需求的最小数据类型 > INET_ATON('255.255.255.255') = 4294967295 ip字符串转整数 > > INET_NTOA(4294967295) = '255.255.255.255' 整数转为ip类型字符串 谨慎使用ENUM,TEXT字符串类型 > 此类数据的使用SQL的性能较差 同财务相关的数值型数据，必须使用decimal类型 ##### 如何为表和列选择适合的名字 > 所有数据库对象名称必须使用小写字母可选用下划线分割。 > > 所有数据库对象名称定义禁止使用MySQL保留关键字。 > > 数据库对象的命名要能做到见名识义，并且最好不要超过32个字。 > > 临时库表必须以tmp为前缀并以日期为后缀。 > > 用于备份的库，表必须以bak为前缀并以日期为后缀。 > > 所有存储相同数据的列名和列类型必须一致 ##### 本章知识点 > 数据库的逻辑设计规范(符合范式的数据库设计可以，减少数据冗余，并且可以避免数据的更新插入删除异常)(范式化数据库设计与反范式化数据库设计需要掌握尺度) > > MySQL的常用存储引擎及其选择方法 > > MySQL的常用数据类型及其选择方法 > > 如何为表选择合适的存储类型 > > 如何为表起名 #### 数据库访问异常处理 > 1045 异常 > > 确认密码是否正确 > > 确认是否有对IP的授权，%不包括localhost，如果想要本地访问，必须对127.0.0.1进行授权 > > 网络是否畅通 > 1153异常 > > 输入的SQL语句的大小超过了max_allowed_packet的大小 > > max_allowed_packet 是MySQL接受最大数据包大小的 > > 增加max_allowed_packet配置的大小 > > SET PERSIST max_allowed_packet=100\*1024\*1024(100M) #### SQL语言开发 > 分类 > > DCL(数据库管理类语句，例如授权访问的) > > DDL(数据定义类语句，建立数据库对象的，比如建立表的) > > DML(操作数据库，增删改查) > > TCL(事务类语句) ##### 访问控制账号 DCL(Data Control Language) > 建立数据库账号: create user > > 对用户授权: grant > > 收回用户权限: revoke ###### 实操访问控制 建立程序使用的数据库账号 > ```sql > CREATE USER mc_class@'192.168.1.%' IDENTIFIED WITH 'mysql_native_password' by '123456'; > ``` > > mc_class@'192.168.1.%' 指定可访问ip列表 > 如果使用MHA, MMM等建议使用mysql_native_password来作为认证方式 > 资源限制，限制用户只能建立一个连接 > > ```sql > CREATE USER mc_test@'192.168.1.%' IDENTIFIED BY '123456' WITH MAX_USER_CONNECTIONS 1; > ``` > > 修改名称与可访问ip列表 > > ```sql > RENAME USER 'mc_test'@'192.168.1.%' TO 'mc_test'@'localhost'; > ``` ##### 给账号授权 ###### MySQL的常用权限 | 权限名称 | 说明 | | :------- | :------------------- | | Insert | 向表中插入数据的权限 | | Delete | 删除表中数据的权限 | | Update | 修改表中数据的权限 | | Select | 查询表中数据的权限 | | Execute | 执行存储过程的权限 | ###### 给账号授权 > 查看当前支持的权限列表 > > ```sql > Show privileges; > ``` > > 对某些用户隐藏某些列的信息 > > ```sql > GRANT SELECT(user, host) ON mysql.user TO mc_class@'192.168.1.%'; -- 给用户mc_class 授予mysql.user表上的user和host列的查询权限 DELECT等直接修改SELECT为其它即可 > ``` > > 查询表中所有信息 > > ```sql > GRANT SELECT ON mysql.user TO mc_class@'192.168.1.%'; -- 给mc_class 授予mysql.user表上所有列的查询权限 > ``` > > 查询某数据库下的所有表 > > ```sql > GRANT SELECT,DELETE,UPDATE ON mysql.* TO mc_class@'192.168.1.%'; -- 给mc_class 授予mysql上所有表对象的权限列表 > ``` > > ##### GRANT命令的注意事项 > > 1. mysql 8.0后使用grant授权的数据库账户必须存在不能自动创建账户了；使用grant授权的数据库账户必须存在 > 2. 用户使用grant命令授权必须具有grant option的权限 > 3. 获取命令帮助 \h grant ###### 回收用户权限 EX: > ```sql > GRANT SELECT,DELETE,UPDATE,UPDATE ON mysql.* TO mc_class@'192.168.1.%'; > REVOKE DELETE,UPDATE,UPDATE ON mysql.* FRPM mc_class@'192.168.1.%'; > ``` ##### 创建数据库对象 ##### DDL(Data Definition language) > 建立/修改/删除数据库: create/alter/drop database > > 建立/修改/删除表: create/alter/drop table > > 建立/删除索引: create/drop index > > 清空表: truncate table(删除表的数据仅保留表的结构，没有记录日志，无法通过日志对表进行恢复) > > 重命名表: rename table > > 建立/修改/删除视图: create/alter/drop view ###### EX: ###### 创建数据库imc_db > ```sql > CREATE DATABASE imc_db DEFAULT CHARSET UTF8MB4; > ``` ###### 建立数据库imc_db中的表 > ```sql > CREATE TABLE imc_course ( > course_id INT UNSIGNED auto_increment NOT NULL COMMENT '课程ID', -- 自增ID列，必须为主键或唯一索引的一部分，一张表中只能有一列 > title VARCHAR(20) NOT NULL DEFAULT '' COMMENT '课程主标题', > title_desc VARCHAR(50) NOT NULL DEFAULT '' COMMENT '课程副标题', > type_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程方向ID', > class_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程分类ID', > level_id SMALLINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程难度ID', > online_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '课程上线时间', > study_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '学习人数', > course_time time NOT NULL DEFAULT '0:00' COMMENT '课程时长', > intro VARCHAR(200) NOT NULL DEFAULT '' COMMENT '课程简介', > info VARCHAR(200) NOT NULL DEFAULT '' COMMENT '课程须知', > harvest VARCHAR(200) NOT NULL DEFAULT '' COMMENT '课程收获', > user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '讲师ID', > main_pic VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '课程主图片', > content_score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '内容评分', > level_score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '简单易懂', > logic_score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '逻辑清晰', > score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '综合评分', > PRIMARY KEY (course_id), > UNIQUE KEY udx_title (title) -- 唯一索引，课程主标题作为业务主键不能重复，最后一行不加逗号 > ) COMMENT '课程主表'; > ``` > 所有列设置为NOT NULL为了优化查询性能，因为B+树索引不能对空值进行索引。 > ```sql > CREATE TABLE imc_chapter ( > chapter_id INT UNSIGNED auto_increment NOT NULL COMMENT '章节ID', > course_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程ID', > chapter_name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '章节名称', > chapter_info VARCHAR(200) NOT NULL DEFAULT '' COMMENT '章节说明', > chapter_no TINYINT(2) UNSIGNED ZEROFILL NOT NULL DEFAULT 0 COMMENT '章节编号', -- 长度小于定义长度时数字前补0 > PRIMARY KEY (chapter_id), > UNIQUE KEY udx_courseid_chaptername (course_id, charpter_name) -- 联合唯一索引，一门课程下只能有一个章节名称 > ) COMMENT '课程章节表'; > ``` > > ```sql > CREATE TABLE imc_subsection ( > sub_id INT UNSIGNED auto_increment NOT NULL COMMENT '小节ID', > chapter_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '章节ID', > course_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程ID', > sub_name VARCHAR(50) NOT NULL DEFAULT '' COMMENT '小节名称', > sub_url VARCHAR(1000) NOT NULL DEFAULT '' COMMENT '小节URL', > video_type enum ('avi','mp4','flv') NOT NULL DEFAULT 'mp4' COMMENT '视频格式', > sub_time time NOT NULL DEFAULT '0:00' COMMENT '小节时长', > sub_no TINYINT(2) UNSIGNED ZEROFILL NOT NULL DEFAULT 0 COMMENT '小节编号', -- 长度小于定义长度时数字前补0 > PRIMARY KEY (sub_id), > UNIQUE KEY udx_charpterid_courseid_subname (chapter_id,course_id, sub_name) > ) COMMENT '课程小节表'; > ``` > > ```sql > CREATE TABLE imc_class( > class_id SMALLINT UNSIGNED auto_increment NOT NULL COMMENT '课程分类ID', > class_name VARCHAR(10) NOT NULL DEFAULT '' COMMENT '分类名称', > add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', -- 默认值为当前时间戳 > PRIMARY KEY (class_id) > ) COMMENT '课程分类表'; > ``` > > ```sql > CREATE TABLE imc_type( > type_id SMALLINT UNSIGNED auto_increment NOT NULL COMMENT '课程方向ID', > type_name VARCHAR(10) NOT NULL DEFAULT '' COMMENT '课程方向名称', > add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', -- 默认值为当前时间戳 > PRIMARY KEY (type_id) > ) COMMENT '课程方向表'; > ``` > > ```sql > CREATE TABLE imc_level( > level_id SMALLINT UNSIGNED auto_increment NOT NULL COMMENT '课程难度ID', > level_name VARCHAR(10) NOT NULL DEFAULT '' COMMENT '课程难度名称', > add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '添加时间', -- 默认值为当前时间戳 > PRIMARY KEY (level_id) > ) COMMENT '课程难度表'; > ``` > > ```sql > CREATE TABLE imc_user( > user_id INT UNSIGNED auto_increment NOT NULL COMMENT '用户ID', > user_nick VARCHAR(20) NOT NULL DEFAULT '慕课网' COMMENT '用户昵称', > user_pwd CHAR(32) NOT NULL DEFAULT '' COMMENT '密码', > sex CHAR(2) NOT NULL DEFAULT '未知' COMMENT '性别', > phone CHAR(11) NOT NULL DEFAULT '00000000000' COMMENT '电话', -- 与老师不一致新加 > province VARCHAR(20) NOT NULL DEFAULT '' COMMENT '省', > city VARCHAR(20) NOT NULL DEFAULT '' COMMENT '市', > position VARCHAR(10) NOT NULL DEFAULT '未知' COMMENT '职位', > mem VARCHAR(100) NOT NULL DEFAULT '' COMMENT '说明', > exp_cnt MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '经验值', > score INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '积分', > follow_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '关注人数', > fans_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '粉丝人数', > is_teacher TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '讲师标识,0:普通用户,1:讲师用户', > reg_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间', > user_status TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户状态 1:正常 0:冻结', > PRIMARY KEY (user_id), > UNIQUE KEY udx_usernick (user_nick), -- 插入其他用户时必须插入 > UNIQUE KEY udx_phone (phone) > ) COMMENT '用户表'; > ``` > > ```sql > CREATE TABLE imc_question ( > quest_id INT UNSIGNED auto_increment NOT NULL COMMENT '评论ID', > user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID', > course_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程ID', > chapter_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '章节ID', > sub_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '小节ID', > replyid INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父评论ID', > quest_title VARCHAR(50) NOT NULL DEFAULT '' COMMENT '评论标题', > quest_content text COMMENT '评论内容', > quest_type enum('问答','评论') NOT NULL DEFAULT '评论' COMMENT '评论类型', > view_cnt INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '浏览量', > add_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间', > PRIMARY KEY (quest_id) > ) COMMENT '问答评论表'; > ``` > > ```sql > CREATE TABLE imc_note ( > note_id INT UNSIGNED auto_increment NOT NULL COMMENT '评论ID', > user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID', > course_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程ID', > chapter_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '章节ID', > sub_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '小节ID', > note_title VARCHAR(50) NOT NULL DEFAULT '' COMMENT '评论标题', > note_content text COMMENT '评论内容', > add_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间', > PRIMARY KEY (note_id) > ) COMMENT '笔记表'; > ``` > > ```sql > CREATE TABLE imc_classvalue ( > value_id INT UNSIGNED auto_increment NOT NULL COMMENT '评价ID', > user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID', > course_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程ID', > content_score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '内容评分', > level_score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '简单易懂', > logic_score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '逻辑清晰', > score DECIMAL(3,1) NOT NULL DEFAULT 0.0 COMMENT '综合评分', > add_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '发布时间', > PRIMARY KEY (value_id) > ) COMMENT '课程评价表'; > ``` > > ```sql > CREATE TABLE imc_selectcourse ( > select_id INT UNSIGNED auto_increment NOT NULL COMMENT '选课ID', > user_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户ID', > course_id INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '课程ID', > select_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '选课时间', > study_time TIME NOT NULL DEFAULT '0:00' COMMENT '累积听课时间', > PRIMARY KEY (select_id) > ) COMMENT '用户选课表'; > ``` ###### 维护表中的索引 > ```sql > CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name,...) > index_col_name: > col_name [(length)][ASC|DESC] > ``` > > ```sql > DROP INDEX index_name ON tbl_name > ``` ##### 其它DDL语句 > 清空表(数据drop+create,不记日志): TRUNCATE TABLE imc_note; > > 重命名表: RENAME TABLE imc_note TO bak_imc_note; ##### 操作表 ##### DML(Data Manipulation language) > 新增表中的数据: insert into > > 删除表中的数据: delete > > 修改表中的数据: update > > 查询表中的数据: select ###### 向表中写入数据 > ###### 编写INSERT语句的思路 > > 确定要把数据插入到哪张表中 (imc_class) > > 确认表的数据结构，哪些列不能为NULL，哪些列可以为NULL，对于不能为NULL的列是否有默认值。(class_name) > > ```sql > -- 查看表结构 > SHOW CREATE TABLE imc_class; > ``` > > 确认对应插入列的插入值的清单 > > 插入数据 > > ```sql > INSERT INTO imc_class(class_name) VALUES('MySQL'),('Redis'),('MongoDB'),('安全测试'),('Oracle'),('SQL Server'),('Hbase'); > ``` > > 建立唯一索引 > > ```sql > CREATE UNIQUE INDEX uqx_classname ON imc_class(class_name); > ``` > > 当出现主键或唯一索引冲突时，覆盖前者数据将时间改为新插入时的时间 > > ```sql > INSERT INTO imc_class(class_name) VALUES('MySQL')ON DUPLICATE KEY UPDATE add_time=CURRENT_TIME; > ``` ###### 查询表中数据 > SELECT 语句语法 > > ```sql > SELECT > [ALL | DISTINCT | DISTINCTROW] > select_expr [, select_expr ...] > [FROM table_references] > [WHERE where_condition] > [GROUP BY {col_name | expr | position}] > [HAVING where_condition] > [ORDER BY {col_name | expr | position} > [ASC | DESC],...] > [LIMIT {[offset,] row_count | row_count OFFSET offset}] > ``` > > EX: > > ```sql > SELECT class_id,class_name FROM imc_db.imc_class; > select * FROM imc_db.imc_class; -- 不推荐使用，在查询特殊类型的数据如text时影响查询性能。 > ``` > > ```sql > -- 查询出所有课程名中包括MYSQL的课程的课程名称 > SELECT title -- 根据表结构查询数据所在的列 > FROM imc_db.imc_course -- 先写数据所存在的表(FROM) > WHERE title LIKE '%MYSQL%'; -- LIKE 比较运算符，%为通配符 > ``` ###### 编写查询语句的思路 > 首先确定我们要获取的数据存在在哪些表中(确定FROM子句) > > 其次是确定我们要取现表中的哪些列(确定SELECT子句) > > 确认是否需要对表中的数据进行过滤(确定WHERE子句) > > ###### WHERE 子句支持的过滤方法 > > 查询出课程表中课程标题含有MySQL的课程标题 > > title LIKE ' %mysql%' ##### MySQL的比较运算符 > | 比较运算符 | 说明 | > | -------------------- | --------------------------------------- | > | = > < >= <= <> != | <> 和 != 都表示不等于 | > | BETWEEN min AND max | 列的值大于等于最小值，小于等于最大值 | > | IS NULL, IS NOT NULL | 判断列的值是否为NULL | > | LIKE, NOT LIKE | % 代表任何数量的字符 _ 代表任何一个字符 | > | IN, NOT IN | 判断列的值是否在指定的范围内 | > > ```sql > -- 学习人数等于1000人的课程都有哪些？ > -- 列出他们的课程标题和学习人数 > SELECT title,study_cnt > FROM imc_db.imc_course > WHERE study_cnt=1000; > ``` > > ```sql > -- 学习人数大于等于1000人小于等于2000人的课程都有哪些？ > -- 列出他们的课程标题和学习人数 > SELECT title,study_cnt > FROM imc_db.imc_course > WHERE study_cnt BETWEEN 1000 AND 2000; > ``` > > ```sql > -- 查询是否为空或不为空 > SELECT * FROM imc_db.imc_note WHERE note_content IS NULL; -- = 无法对NULL进行比较 > ``` > > ```sql > -- 查询课程编号为1,3,5,7,9 的课程名 > SELECT title > FROM imc_course > WHERE course_id IN (1,3,5,7,9); > ``` ###### 如何合并WHERE子句中的多个过滤条件 ##### MySQL的逻辑运算符 | 逻辑运算符 | 说明 | | ---------- | ------------------------------------------------------------ | | AND, && | AND运算符两边表达式都为真时, 返回的结果才为真, 同时满足AND两边的条件 | | OR, \|\| | OR运算符两边的表达式有一条为真, 返回的结果就是真，多个条件中满足一个条件的 | | XOR | XOR 异或运算符，两边表达式一真一假时返回真，两真两假时返回假 | > ```sql > -- 查询出课程表中课程标题含有MySQL的并且学习人数大于5000人的课程标题 > SELECT title,study_cnt > FROM imc_db.imc_course > WHERE title LIKE '%mysql%' > AND study_cnt > 5000; > ``` > > ```sql > -- 查询出课程表中课程标题含有MySQL的并且学习人数小于5000人的课程标题同时查询出课程表中课程标题不含MySQL的并且学习人数大于5000人的课程标题 > SELECT title,study_cnt > FROM imc_db.imc_course > WHERE title LIKE '%mysql%' > XOR study_cnt > 5000; > ``` > ###### 使用JOIN关联多个表 > INNER JOIN 内关联，取出来两个表集合的交集 > > ```sql > SELECT FROM TABLEA A INNER JOIN TABLEB B ON A.key = B.key > ``` > > ```sql > -- 查询出每一门课程的课程ID, 课程名称和章节名称 > SELECT a.course_id,a.title,b.chapter_name > FROM imc_course a > JOIN imc_chapter b ON b.course_id=a.course_id; > ``` > > OUTER JOIN : > > LEFT JOIN > > 左外关联，将左表中数据全部取出哪怕没有满足ON条件的 > > ```sql > SELECT FROM TABLEA A LEFT JOIN TABLEB B ON A.Key=B.Key > -- 查询A表中所有数据和B表中满足条件的数据 > SELECT FROM TABLEA A LEFT JOIN TABLEB B ON A.Key=B.Key WHERE B.Key IS NULL > -- 一般应用于查询不存在某表中的数据 查询补集 增加WHERE过滤条件 > ``` > > ```sql > -- 查询出只存在于课程表中，但是不存在于章节表中的课程的课程名称和课程ID信息 > SELECT a.course_id, a.title > FROM imc_course a > WHERE course_id NOT IN (SELECT b.course_id FROM imc_chapter b); > > -- 外关联方式性能更高 > SELECT a.course_id, a.title > FROM imc_course a > LEFT JOIN imc_chapter b ON b.course_id=a.course_id > WHERE b.course_id IS NULL > ``` > > RIGHT JOIN > > 右外关联，同左外关联相反 ###### GROUP BY ... HAVING 子句的作用 > 把结果集按某些列分成不同的组，并对分组后的数据进行聚合操作 > > ```sql > -- 统计每个分类下不同难度的课程的数量 > SELECT level_name,class_name,count(*) -- 分组键和聚合函数,除了聚合函数，分组键必须都要写到GROUP BY子句中 > FROM imc_course a > JOIN imc_class c ON a.class_id=c.class_id > JOIN imc_level b ON b.level_id=a.level_id > GROUP BY level_name,class_name; -- 分组键 > ``` > > 可以通过可选的HAVING子句对聚合后的数据进行过滤 > > ```sql > -- 统计每个分类下课程大于三门的难度有哪些 > SELECT class_name,level_name,count(*) -- WHERE子句中无法使用聚合函数必须使用HAVING > FROM imc_course a > JOIN imc_class b ON b.class_id=a.class_id > JOIN imc_level c ON c.level_id=a.level_id > GROUP BY class_name,level_name HAVING count(*)>3; > ``` ##### 常用的聚合函数 | 聚合函数 | 说明 | | ------------------- | -------------------------------------- | | COUNT(*)/COUNT(col) | 计算符合条件的数据行数 | | SUM(col_name) | 计算表中符合条件的数值列的合计值 | | AVG(col_name) | 计算表中符合条件的数值列的平均值 | | MAX(col_name) | 计算表中符合条件的任意列中数据的最大值 | | MIN(col_name) | 计算表中符合条件的任意列中数据的最小值 | > ```sql > -- 统计课程表的总课程数 > SELECT COUNT(*), > COUNT(DISTINCT user_id) -- 去重，统计有多少个不同的讲师 > FROM imc_course; > ``` > > ```sql > -- 统计出不同难度所有课程总的学习人数 > SELECT level_name,SUM(study_cnt) > FROM imc_course a > JOIN imc_level b ON b.level_id=a.level_id > GROUP BY level_name; > ``` > > ```sql > -- 统计出没门课程的平均学习人数 > SELECT SUM(study_cnt)/COUNT(study_cnt),AVG(study_cnt) > FROM imc_course; > ``` > > ```sql > -- 利用课程评价表中的评分，来更新课程表中的课程评分 > UPDATE imc_course a > JOIN > (SELECT a.course_id,title, > AVG(a.content_score) AS avg_content, > AVG(a.level_score) AS avg_level, > AVG(a.logic_score) AS avg_logic, > AVG(a.score) AS avg_score > FROM imc_classvalue a > JOIN imc_course b ON a.course_id = b.course_id > GROUP BY a.course_id) b ON a.course_id = b.course_id > SET a.content_score=b.avg_content, > a.level_score=b.avg_level, > a.logic_score=b.avg_logic, > a.score=b.avg_score > ; > ``` > > ```sql > -- 查询出学习人数最多的课程 > SELECT title,study_cnt > FROM imc_course > WHERE study_cnt = ( -- 子查询(可以看做是一个临时表) > SELECT MAX(study_cnt) > FROM imc_course > ); > ``` ###### 使用ORDER BY 子句对查询结果进行排序 > 使用ORDER BY 子句是对查询结果进行排序的最安全的方法 > > 列名后增加ASC关键字指定按该列的升序进行排序，或是指定DESC关键字指定按该列的降序进行排序 > > ORDER BY 子句也可以使用SELECT子句中未出现的列或是函数 > > ```sql > -- 查询出每门课程的学习人数并按学习人数从高到低排列 > > SELECT title,study_cnt > FROM imc_course > ORDER BY study_cnt DESC; > ``` ###### 使用LIMIT 子句限制返回结果集的行数 > 常用于数据列表分页 > > 一定要和ORDER BY 子句配合使用 > > limit 起始偏移量，结果集的行数 > > ```sql > -- 分页返回课程ID和课程名称，每页返回10行记录 > SELECT course_id,title > FROM imc_course > ORDER BY course_id ASC > LIMIT 0,10; > ``` ##### 视图 > 视图是一个逻辑的存储结构，也可以看做是一个虚拟的表。 ###### 创建视图 > ```sql > CREATE VIEW view_name > AS > SELECT 查询语句(定义了视图的结构和数据) > ``` > > ```sql > -- 定义一个包括课程ID，课程名称，课程分类，课程方向以及课程难度的视图 > CREATE VIEW vm_course > AS > SELECT a.course_id,a.title,b.class_name,c.type_name,d.level_name > FROM imc_course a > JOIN imc_class b ON b.class_id=a.class_id > JOIN imc_type c ON c.type_id=a.type_id > JOIN imc_level d ON d.level_id=a.level_id; > > -- 查询视图可以达到查询四表关联的效果 > SELECT * FROM vm_course; > ``` ##### 按条件删除表中数据 > ```sql > DELETE > FROM table_name > [WHERE where_condition] > [ORDER BY ...] > [LIMIT row_count] > ``` ###### 编写DELETE语句的思路 > 确定要删除的数据存储在哪张表中 FROM子句，如果是多张表可以用JOIN子句 > > 确认删除数据的过滤条件 WHERE子句 > > 确认是否只删除有限条数据 ORDER BY ... LIMIT 子句 LIMIT子句只有一个参数，删除数据的最大行数 > > ```sql > -- 删除课程表中没有章节信息的课程 > DELECT a -- 多张表关联时指定删除哪张表 > FROM imc_course a > LEFT JOIN imc_chapter b ON b.course_id=a.course_id > WHERE b.course_id IS NULL > ``` > > ```sql > -- 删除课程方向标中重复的课程方向，保留方向ID最小的一条，并在方向名称上增加唯一索引 > -- 要保证建立唯一索引必须先要保证该列的数据都是不重复的 > DELETE a > FROM imc_type a > JOIN ( -- 作为子查询虚拟表 > SELECT type_name,MIN(type_id) AS min_type_id,COUNT(*) -- 分组后找到最小ID > FROM imc_type > GROUP BY type_name HAVING count(*)>1; -- 超过一条说明方向重复 > ) b ON a.type_name=b.type_name AND a.type_id>min_type_id; -- 删除 方向名相同且id大于最小id的数据 > CREATE UNIQUE INDEX uqx_typename ON imc_type(type_name); -- 在imc_type的type_name列上建立唯一索引 > ``` ##### 修改表中符合条件的数据 > ```sql > UPDATE table_name > SET col_name1={expr1|DEFAULT} > [, col_name2={expr2|DEFAULT}] ... > [WHERE where_condition] > [ORDER BY ...] > [LIMIT row_count] -- 限制更新数量，避免主从延迟，大面积阻塞 > ``` ###### 编写UPDATE语句的思路 > 确定要更新的数据存储在哪张表中 UPDATE 子句 > > 确定要更新的列的值 SET子句 > > 确认更新数据的条件 WHERE 子句 > > 一般先写SELECT语句，将要更新的数据查出后再修改为UPDATE语句 > > ```sql > -- 冻结用户"沙占"的账号 > -- 先写SELECT语句查询要修改的数据 > SELECT user_nick,user_status > FROM imc_user > WHERE user_nick='沙占'; > -- 修改为UPDATE语句 > UPDATE imc_user > SET user_status=0 > WHERE user_nick='沙占'; > ``` ###### 修改表结构 >```sql >-- 修改列名 >ALTER TABLE testalter_tbl CHANGE i j 字段信息(TINYINT DEFAULT 0 COMMENT '是否推荐,0不推荐,1推荐'); >``` > ```sql > -- 随机推荐10门课程 > -- 增加推荐标识列 > ALTER TABLE imc_course > ADD is_recommand TINYINT DEFAULT 0 COMMENT '是否推荐,0不推荐,1推荐'; > ``` > ```sql > -- 随机推荐10门课程 > SELECT course_id > FROM imc_course > ORDER BY RAND() -- 随机函数 > LIMIT 10; > -- 修改为UPDATE语句 > UPDATE imc_course > SET is_recommand=1 > ORDER BY RAND() -- 随机函数 > LIMIT 10; > ``` > > ```sql > -- 利用课程评价表中的评分，来更新课程表中的课程评分 > UPDATE imc_course a > JOIN > (SELECT a.course_id,title, > AVG(a.content_score) AS avg_content, > AVG(a.level_score) AS avg_level, > AVG(a.logic_score) AS avg_logic, > AVG(a.score) AS avg_score > FROM imc_classvalue a > JOIN imc_course b ON a.course_id = b.course_id > GROUP BY a.course_id) b ON a.course_id = b.course_id > SET a.content_score=b.avg_content, > a.level_score=b.avg_level, > a.logic_score=b.avg_logic, > a.score=b.avg_score > ; > ``` #### 系统函数 ##### 常用的时间函数(前端时间可能存在不一致，往往使用数据库时间) | 函数名 | 说明 | | ---------------------------------- | ------------------------------------------------------------ | | CURDATE()/CURTIME() | 返回当前日期/返回当前时间 | | NOW() | 返回当期的日期和时间 | | DATE_FORMAT(date,fmt) | 按照fmt的格式,对日期date进行格式化; fmt: (%Y:四位的年 %m:月份(00..12) %d:天(00..31) %H:小时(00..24) %i:分钟(00..59) %s:秒(00..59)) | | SEC_TO_TIME(seconds) | 把秒数转换为(小时:分:秒) | | TIME_TO_SEC(time) | 把时间(小时:分:秒)转换为秒数 | | DATEDIFF(date1, date2) | 返回date1和date2两个日期相差的天数 | | DATE_ADD(date,INTER VAL expr unit) | 对给定的日期增加或减少指定的时间单元(unit:DAY天/HOUR小时/MINUTES分钟/SECOND秒) | | EXTRACT(unit FROM date) | 返回日期date的指定部分 | | UNIX_TIMESTAMP() | 将日期格式返回unix时间戳，unix时间戳是自1970年1月1日0点0分0秒到当前时间经过的秒数 | | FROM_UNIXTIME() | 把Unix时间戳转换为日期时间 | > ```sql > -- 按照指定格式输出时间 > SELECT DATE_FORMAT(NOW(),'%Y%m%d %H:%i:%S'); > ``` > > ```sql > -- 时间秒之间相互转换 > SELECT SEC_TO_TIME(119),TIME_TO_SEC('1:00:00'); > ``` > > ```sql > -- 计算每门课程，上线时间距当前时间的天数,按上线时间由前至后排序 > SELECT title,DATEDIFF(NOW(),online_time) > FROM imc_course > ORDER BY 2 DESC; > ``` > > ```sql > SELECT NOW() > ,DATE_ADD(NOW(),INTERVAL 1 DAY) -- 当前时间加1天 > ,DATE_ADD(NOW(),INTERVAL 1 YEAR) -- 当前时间加1年 > ,DATE_ADD(NOW(),INTERVAL -1 DAY) -- 当前时间减1天 > ,DATE_ADD(NOW(),INTERVAL '-1:30' HOUR_MINUTE); -- 当前时间减1小时30分 > ``` > > ```sql > SELECT NOW() > ,EXTRACT(YEAR_MONTH FROM NOW()) -- 提取出当前时间的年月 > ,EXTRACT(MONTH FROM NOW()) -- 提取出当前时间的月份 > ,EXTRACT(DAY FROM NOW()); -- 提取出当前时间的日期 > ``` ##### 常用的字符串函数 | 函数名 | 说明 | | -------------------------------- | ---------------------------------------------- | | CONCAT(str1,str2,...) | 把字符串str1,str2连接成一个字符串 | | CONCAT_WS(sep,str1,str2,...) | 用指定的分割符sep连接字符串 | | CHAR_LENGTH(str) | 返回字符串str的字符个数 | | LENGTH(str) | 返回字符串str的字节个数 | | FORMAT(X,D[,locale]) | 将数字N格式化，如“#,###,###.##"并舍入到D为小数 | | LEFT(str,len)/RIGHT(str,len) | 从字符串的左/右边起返回len长度的子字符串 | | SUBSTRING(str,pos,[len]) | 从字符串str的pos位置起返回长度为len的子串 | | SUBSTRING_INDEX(str,delim,count) | 返回字符串str按delim分割的前count个子字符串 | | LOCATE(substr,str) | 在字符串str中返回子串substr第一次出现的位置 | | TRIM([remstr FROM] str) | 从字符串str两端删除不需要的字符remstr | > ```sql > -- 处于SEO优化的目的，我们需要合并显示课程分类名称和课程标题 > SELECT CONCAT_WS('||',class_name,title) > FROM imc_course a > JOIN imc_class b ON b.class_id=a.class_id; > ``` > > ```sql > -- 中文字符占3个字节，英文字符占1个字节 > SELECT class_name, LENGTH(class_name),CHAR_LENGTH(class_name) > FROM imc_class; > ``` > > ```sql > -- 保留4位小数 > SELECT FORMAT(123456.789,4) > ``` > > ```sql > -- 截取指定长度的字符串 > SELECT LEFT('www.imooc.com',3),RIGHT('www.imooc.com',3); > SELECT SUBSTRING('www.imooc.com',5,5); > ``` > > ```sql > -- 从右边截取第二个ip地址段至后 > SELECT SUBSTRING_INDEX('192.168.0.1','.',-2); > ``` > > ```sql > -- 截取课程标题里中横线之前的部分 > SELECT title > ,LOCATE('-',title) -- 获取中横线字符所在位置 > ,SUBSTRING(title,1,LOCATE('-',title)-1) -- 起始位置和终止位置 > FROM imc_course; > -- 方法二 > SELECT SUBSTRING_INDEX(title,'-',1) > FROM imc_course; > ``` > > ```sql > -- 删除字符串两边的空格或指定字符 > SELECT TRIM(' imooc '),TRIM('x' FROM 'ximoocx'); > ``` ##### 其它常用函数 | 函数名 | 说明 | | -------------------------------------- | -------------------------------------------------- | | ROUND(X,D) | 对数值X进行四舍五入保留D位小数 | | RAND() | 返回一个在0到1之间的随机数 | | CASE WHEN [condition] THEN result END | 用于实现其它语言中的case..when功能，提供数据流控制 | | MD5(str) | 返回str的MD5值 | > ```sql > -- 显示每个用户的昵称和性别 > SELECT user_nick, > CASE WHEN sex=1 THEN '男' > WHEN sex=0 THEN '女' > ELSE '未知' > END AS '性别' > FROM imc_user > WHERE CASE WHEN sex=1 THEN '男' -- where条件中使用case when 筛选只有男性的 > WHEN sex=0 THEN '女' > ELSE '未知' > END = '男'; > ``` > #### SQL8.0高级特性 > 相较于子查询 ##### 公用表表达式CTE(Common Table Expressions) > MySQL8.0之后的版本才可以使用 > > CTE生成一个命名临时表,并且旨在查询期间有效 > > CTE临时表在一个查询中可以多次引用及自引用 ###### CTE基础语法 > ```sql > WITH [RECURSIVE] -- 自引用关键字 > cte_name [(colum_list)] AS ( -- 临时表表名 > query -- 生成公共表查询(select查询语句) > ) > [,cte_name[(colum_list)] AS ( -- 定义多个公共表表达式逗号分隔 > query > )] > SELECT * FROM cte_name; > ``` > > ```sql > -- 公共表表达式例子 > WITH cte AS ( > SELECT title,study_cnt,class_id > FROM imc_course > WHERE study_cnt>2000 > ) > SELECT * FROM cte > UNION ALL -- 自引用 定义后可以多次引用，子查询生成的临时表则不行 > SELECT * FROM cte > ORDER BY title; -- 查询了两次 > ``` > > ```sql > -- CTE 递归生成自增序列 > WITH RECURSIVE test AS( > SELECT 1 AS n > UNION ALL > SELECT 1+n FROM test WHERE n<10 > ) > SELECT * FROM test; > ``` > > ```sql > -- 递归查询课程评论信息 > WITH RECURSIVE replay(quest_id,quest_title,user_id,replyid,path) AS ( > SELECT quest_id,quest_title,user_id,replyid,CAST(quest_id AS CHAR(200)) AS path > FROM imc_question > WHERE course_id=59 AND replyid = 0 > UNION ALL > SELECT a.quest_id,a.quest_title,a.user_id,a.replyid,CONCAT(b.path,'>>',a.quest_id) AS path > FROM imc_question a > JOIN replay b ON a.replyid=b.quest_id -- 递归终止条件 > ) > SELECT * FROM replay; > ``` ##### 窗口函数 > 一组记录所执行的函数，符合条件的记录执行的函数 > > 窗口函数不会改变记录集的行数 > > ```sql > function_name([exp]) > OVER( -- 指定了窗口的范围 > [PARTITION BY exp [, ...]] -- 查询返回的查询集分组 > [ORDER BY exp [ASC|DESC][,...]] -- 按照哪些字段来排序 > ) > ``` | 函数名 | 说明 | | ------------ | ------------------------------------------------------------ | | 聚合函数 | 集聚和函数都可以作为窗口函数使用 | | ROW_NUMBER() | 返回窗口分区内数据的行号 | | RANK() | 类似于row_number,只是对于相同数据会产生重复的行号, 之后的数据行号会产生间隔 | | DENSE_RANK() | 类似于rank区别在于当组内某行数据重复时,虽然行号会重复,但后续的行号不会产生间隔。(两个数据并列第一，第三个数据行号为2) | > ```sql > -- row_number,rank,dense_rank之间的区别 排名 > WITH test(study_name,class_name,score) AS ( > SELECT 'sqlercn','MySQL',95 > UNION ALL > SELECT 'tom','MySQL',99 > UNION ALL > SELECT 'Jerry','MySQL',99 > UNION ALL > SELECT 'Gavin','MySQL',98 > UNION ALL > SELECT 'sqlercn','PostGreSQL',99 > UNION ALL > SELECT 'tom','PostGreSQL',99 > UNION ALL > SELECT 'Jerry','PostGreSQL',98 > UNION ALL > ) > SELECT study_name,class_name,score > ,ROW_NUMBER() OVER(PARTITION BY class_name ORDER BY score DESC) AS rw > ,RANK() OVER(PARTITION BY class_name ORDER BY score DESC) AS rk > ,DENSE_RANK() OVER(PARTITION BY class_name ORDER BY score DESC) AS drk > FROM test > ORDER BY class_name, rw; > ``` > > ```sql > -- 按学习人数对课程进行排名，并列出每类课程学习人数排名前3的课程名称，学习人数以及名次。 > WITH tmp AS ( > SELECT class_name,title,score > ,RANK() OVER(PARTITION BY class_name ORDER BY score DESC) > FROM imc_course a > JOIN imc_class b ON b.class_id=a.class_id > ) > SELECT * > FROM tmp WHERE cnt <= 3; > ``` > > ```sql > -- 每门课程的学习人数占本类课程总学习人数的百分比 > WITH tmp AS ( > SELECT class_name,title,study_cnt > ,SUM(study_cnt) OVER(PARTITION BY class_name) AS class_total -- 窗口中符合条件的记录会执行SUM窗口函数 > FROM imc_course a > JOIN imc_class b ON b.class_id=a.class_id > ) > SELECT class_name,title,CONCAT(study_cnt/class_total*100,'%') > FROM tmp; > ``` #### 在SQL开发中易犯的错误 > ###### 使用COUNT(*)判断是否存在符合条件的数据 > > 由于COUNT(*)将全表数据扫描后返回结果，查询效率很低 > > 应该使用SELECT...LIMIT1 > > ###### 在执行一个更新语句后使用查询方式判断此更新语句是否有执行成功 > > 使用ROW_COUNT()函数判断修改行数 > > ###### 试图在ON条件中过滤不满足条件的记录 > > 在外关联时可能会失效 > > 使用WHERE条件中进行过滤 > > ###### 使用In进行子查询的判断时，在列中未指定正确的表名。如SELECT A1 FROM A WHERE A1 IN (SELECT A1 FROM B)这是尽管B中并不存在A1列，数据库也不会报错，而是会列出A表中的所有数据 > > EX: > > ```sql > SELECT * > FROM imc_course > WHERE title IN (SELECT title FROM imc_class); > -- imc_class中没有title列然而不会报错并会列出所有imc_course的数据 > ``` > > 方案1: 使用表名.列名 > > ```sql > SELECT * > FROM imc_course > WHERE title IN (SELECT imc_class.title FROM imc_class); > -- imc_class中没有title列然而不会报错并会列出所有imc_course的数据 > ``` > > 方案2:使用JOIN关联代替子查询 > > ###### 对于表中定义的具有NOT NULL和DEFAULT值的列，在插入数据时直接插入NULL值。 ### SQL优化 #### SQL优化的一般步骤 > 发现问题 -> 分析执行计划 -> 优化索引 -> 改写SQL > > 数据库垂直切分 -> 数据库水平切分 ##### 常见问题发现渠道 > 用户主动上报应用性能问题 > > 分析慢查询日志发现存在问题的SQL > > 数据库实时监控长时间运行的SQL ##### 通过慢查询日志发现问题 ###### 配置MySQL慢查询日志 > ```sql > set global slow_query_log=[ON|OFF] -- 慢查询日志存储在默认位置 > set global slow_query_log_file=/sql_log/slowlog.log -- 指定慢查询日志存放位置 > set global long_query_time=xx.xxx秒 -- 执行时间超过该值就存储在慢查询日志 > set global log_queries_not_using_indexes=[ON|OFF] -- 所有没有使用到索引的SQL记录到日志中 > ``` ###### 分析MySQl慢查询日志 > mysqldumpslow > > pt-query-digest (更好用)(percona工具集中的) ###### linux 安装 percona工具集 > https://www.percona.com/downloads/ > wget https://www.percona.com/downloads/percona-toolkit/3.1.0/binary/redhat/7/x86_64/percona-toolkit-3.1.0-2.el7.x86_64.rpm > > 补包 > > yum install perl-DBI perl-DBD-MySQL perl-Digest-MD5 perl-IO-Socket-SSL perl-TermReadKey > > rpm -ivh percona-toolkit-3.1.0-2.el7.x86_64.rpm ###### 使用慢查询日志 > 启动mysql > > ```sql > show variables like 'long_query_time'; -- 查询慢日志记录阈值 > set global long_query_time=0.001; > show variables like 'slow_query_log'; -- 查看启动慢查询日志 > show variables like 'slow_query_log_file'; -- 查看慢查询日志记录位置 > set global slow_query_log=on; --启动慢查询日志 > ``` > > more 日志文件 # 查看慢查询日志内容 > > 使用mysql自带的命令行工具分析慢查询日志 > > ```shell > mysqldumpslow 日志文件 > ``` > > ```sql > pt-query-digest 日志文件 > ``` > > 需要优化的SQL的特征 > > > 1. 执行次数多 > > 2. 扫描行数远远多于返回结果行数 ##### 通过实时监控发现问题 > 监控长时间运行的SQL > > ```sql > SELECT id,user,host,DB,command,time,state,info > FROM information_schema.PROCESSLIST > WHERE TIME>=6; > -- 如果登录的mysql的linux账号没有搜索权限则只能看到该账户的SQL而不能看到其他账户的执行的SQL > ``` ##### 分析执行计划 ###### 作用 > 了解SQL如何访问表中数据(全表扫描;索引扫描) > > 了解SQL如何使用表中的索引(使用哪个索引，是否正确使用索引) > > 了解SQL所使用的查询类型(是否使用到子查询，是否使用到关联查询)等信息 ##### 如何获取执行计划 > ```sql > EXPLAIN > {explainable_stmt|FOR CONNECTION connection_id} > explainable_stmt:{ > SELECT statement | > DELETE statement | > INSERT statement | > REPLACE statement | > UPDATE statement > } > ``` > > ```sql > REPLACE -- 语句和INSERT语句类似只是当遇见插入时主键冲突时会替换为新数据 > ``` ##### 执行计划内容分析 > ```sql > EXPLAIN > SELECT course_id,title,study_cnt > FROM imc_course > WHERE study_cnt > 3000; > ``` > > ```sql > id:1 > select_type:SIMPLE > table:imc_course > partitions:NULL > type:ALL > possible_keys:NULL > key:NULL > key_len:NULL > ref:NULL > rows:100 > filtered:33.33 > Extra:Using where > ``` ###### id的含义 > 一组数字(ID表示查询执行的顺序;ID相同时由上到下执行;ID不同时，由大到小执行)或是NULL(数据是由另外两个查询UNION操作后所产生的结果集) ###### select_type的含义 > | 值 | 含义 | > | ------------------ | ----------------------------------------------------------- | > | SIMPLE | 不包含子查询或是UNION操作的查询 | > | PRIMARY | 查询中如果包含任何子查询，那么做外层的查询则被标记为PRIMARY | > | SUBQUERY | SELECT列表中的子查询 | > | DEPENDENT SUBQUERY | 外部依赖结果的子查询 | > | UNION | union操作的第二个或是之后的查询的值为union | > | DEPENDENT | 当UNION做为子查询时，第二或是第二个后的查询的select_type值 | > | UNION RESULT | UNION产生的结果集 | > | DERIVED | 出现在FROM子句中的子查询 | ###### table的含义 > 指明是从哪个表中获取数据 > > 由ID为M,N查询union产生的结果集 > > / 由ID为N ###### partitions的含义 > 对于分区表，显示查询的分区ID > > 对于非分区表，显示NULL ###### type的含义 > 体现了mysql访问数据的方式 | 性能 | 值 | 含义 | | ---- | ----------- | ------------------------------------------------------------ | | 高 | system | 这是const连接类型的一个特例，当查询的表只有一行时使用 | | | const | 表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式 | | | eq_ref | 唯一索引或主键索引查找，对于每个索引键，表中只有一条记录与之匹配 | | | ref | 非唯一索引查找，返回匹配某个单独值的所有行。 | | | ref_or_null | 类似于ref类型的查询，但是附加了对NULL值列的查询 | | | index_merge | 该联接类型表示使用了索引合并优化方法。 | | | range | 索引范围扫描，常见between,>,<这样的查询条件 | | | index | FULL index Scan 全索引扫描，同ALL的区别是,遍历的是索引树 | | 低 | ALL | FULL TABLE Scan 全表扫描，这是效率最差的联接方式 | ###### possible_keys的含义 > 指出查询中可能会用到的索引 > > 指出查询时实际用到的索引 ###### key_len的含义 > 实际使用索引的最大长度 ###### ref的含义 > 指出哪些列或常量被用于索引查找 ###### rows的含义 > 根据统计信息预估扫描的行数 > > 关联查询中表示内嵌循环的次数 ###### filtered的含义 > 显示mysql预估的返回的数据行数占结果行数的百分比 ###### extra的含义 > 不适合在其它列现实的信息 | 值 | 含义 | | ---------------------------- | ------------------------------------------------------------ | | Distinct | 优化distinct操作,在找到第一匹配的元组后即停止找同样值的动作 | | Not exists | 使用not exists来优化查询 | | Using filesort | 使用文件(外部索引)来进行排序，通常会出现扎起order by 或group by查询中 | | Using index | 使用了覆盖索引进行查询 | | Using temporary | MySQL需要使用临时表来处理查询，常用于排序，子查询，和分组查询 | | Using where | 需要在MySQL服务器层使用WHERE条件来过滤 | | select tables optimized away | 直接通过索引来获得数据，不用访问表 | ### SQL优化的手段 > 优化SQL查询所涉及到的表中的索引 > > 改写SQL以达到更好的利用索引的目的 ##### 索引的作用 > 告诉存储引擎如何快速的查找到所需要的数据 ##### 索引优化 ###### Innodb支持的索引类型 > Btree索引 > > 自适应HASH索引 > > 全文索引(字符串，对中文支持不好，不如ElasticSearch) > > 空间索引 ###### Btree索引的特点 > 以B+树的结构存储索引数据 > > B+树是平衡二叉树，每一叶子节点到根节点距离相同，每一叶子节点根据键值大小顺序放在同一层叶子节点上，每一叶子节点是指针连接 > > Innodb: 叶子节点指向数据行主键的位置 > > MySam: 叶子节点存储数据的物理磁盘位置 > > Btree索引适用于全值匹配的查询 > > ```sql > class_name='mysql' -- 完全匹配，一个值的完全匹配 > class_name in ('mysql','postgreSQL') -- 多个值的完全匹配 in 的列数过多时会采用全表查询 > ``` > > 适合处理范围查找 > > ```sql > -- 范围查询能用到study_cnt上的Btree索引 > study_cnt between 1000 and 3000; > study_cnt > 3000; > ``` > > Btree索引从索引的最左侧列开始匹配查找列 > > ```sql > create index idx_title_studyCnt on imc_course(title,study_cnt); > A) study_cnt>3000; -- 不能使用到索引，符合索引最左侧不为study_cnt > B) study_cnt>3000 and title='Mysql'; -- 可以使用到索引，mysql优化器可以调整查询过滤条件的顺序以适应索引 > C) title='Mysql' -- 可以使用到索引 > ``` ##### 应该在什么列上建立索引 > * WHERE 子句中的列(在筛选性好的列上建立索引，例如主键列，一列的数据重复度高说明其筛选性不佳) > > ```sql > -- 查询出2019年1月1号以后注册的男性会员的昵称 > EXPLAIN > SELECT user_nick > FROM imc_user > WHERE sex=1 AND reg_time>'2019-01-01' > ``` > > ```sql > -- 比较性别和注册年月日的筛选率 > -- 计算区分度 > SELECT COUNT(DISTINCT sex),COUNT(DISTINCT DATE_FORMAT(reg_time,'%Y-%m-%d')) > ,COUNT(*) > ,COUNT(DISTINCT sex)/COUNT(*) > ,COUNT(DISTINCT DATE_FORMAT(reg_time,'%Y-%m-%d'))/COUNT(*) > FROM imc_user; > -- 在筛选率好的列上建立索引 > CREATE INDEX ind_regtime ON imc_user(reg_time); > -- 查询率提升到10%，扫描576行优于之前的3533行 > ``` > > * 包含在ORDER BY、GROUP BY、DISTINCT中的中字段 > > > 索引列的顺序和ORDER BY的顺序要完全一致；索引列的方向(升序或降序)也要与ORDER BY的顺序完全一致 > > * 多表JOIN的关联列上建立索引 > > ```sql > -- 查询高级分类为MySQL的课程信息 > EXPLAIN > SELECT course_id,b.class_name,d.type_name,c.level_name,title,score > FROM imc_course a > JOIN imc_class b ON b.class_id=a.class_id > JOIN imc_level c ON c.level_id=a.level_id > JOIN imc_type d ON d.type_id=a.type_id > WHERE c.level_name='高级' > AND b.class_name='MySQL'; > > -- 由于方向筛选性高于分类高于难度所以联合索引依次建立 > CREATE INDEX idx_classid_typeid_levelid ON imc_course(class_id,type_id,level_id); > > -- level_id是c表的主键，所以只需在level_name上建立二级索引，Btree索引非主键索引会指向主键，所以在非主键上建立索引会带上主键信息 > CREATE INDEX idx_levelname ON imc_level(level_name); > ``` ##### 如何选自符合索引的顺序 > * 区分度最高的列放在联合索引的最左侧 > > > Btree索引是从左侧键值开始过滤数据的，所以最左侧键值区分度越高越好 > > * 使用最频繁的列放到联合索引的最左侧 > * 尽量把字段长度小的列放在联合索引列的最左侧 ##### 使用索引的限制 > 当索引的数据列行数与全表数据行数近似时，mysql搜索引擎会认为全表查询效率更高 ##### Btree索引的限制 > * 只能从最左侧开始按索引键的顺序使用索引，不能跳过索引键 > > ex： ABC三列的联合索引，查询只包含AC两列，索引只能使用到A列；因为查询中跳过了B列 > > * NOT IN 和 <> 操作无法使用索引 > * 索引列上不能使用表达式或是函数 ##### 索引使用的误区 > * 索引越多越好(索引会降低插入更新的效率)；多个索引可以进行查询，mysql优化器会生成最优计划从而降低查询效率 > * 使用IN列表查询不能用到索引(OR不能使用到索引) > * 查询过滤顺序必须同索引键顺序相同才可以使用到索引(WHERE AND),MySQL索引优化器会自动调整键值顺序以适应索引 #### SQL改写优化 ##### SQL改写原则 > * 使用outer join代替 not in > > ```sql > -- 需求: 查询出不存在课程的分类名称。 > EXPLAIN > SELECT class_name > FROM imc_class > WHERE class_id NOT IN (SELECT class_id FROM imc_course); > > EXPLAIN > SELECT class_name > FROM imc_class a > LEFT JOIN imc_course b ON a.class_id=b.class_id > WHERE b.class_id IS NULL; > -- MySql8.0 可以自动对上述NOT IN SQL 语句进行优化，优化为下面语句 8.0之前版本需要手动完成优化 > ``` > > * 使用CTE代替子查询 > > * 拆分复杂的大SQL为多个简单的小SQL(以使用并发) > * 巧用计算列优化查询 > > ```sql > -- 需求:查询对于内容，逻辑和难度三项评分之后大于28的用户评分 > SELECT * > FROM imc_classvalue > WHERE (content_score+level_score+logic_score)>28; -- 无法使用到索引 > > -- 使用计算列(新增列) > ALTER TABLE imc_classvalue ADD COLUMN total_score DECIMAL(3,1) AS (content_score+level_score+logic_score); > -- 在计算列上增加索引 > CREATE INDEX idx_totalScore ON imc_classvalue(total_score); > > SELECT * > FROM imc_classvalue > WHERE total_score>28; > ``` #### 事务与并发控制 ##### 事务 ###### 什么是事务 > * 事务时数据库执行操作的最小逻辑单元 > * 事务可以由一个SQL组成也可以有多个SQL组成 > * 组成事务的SQL要么全执行成功要么全执行失败 > > ```sql > -- 事务中只能执行DML操作不能执行DDL操作 > START TRANSATION / BEGIN -- 开启事务 > SELECT ... > UPDATE ... > INSERT ... > COMMIT / ROLLBACK -- 提交事务或回滚事务 > ``` > ###### 事务的特性 | 特征 | 说明 | | --------- | ------------------------------------------------------------ | | 原子性(A) | 一个事务中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节 | | 一致性(C) | 在事务开始之前和事务结束之后，数据库的完整性没有被破坏 | | 隔离性(I) | 事务的隔离性要求每个读写事务的对象与其它事务的操作对象能相互分离，即该事务提交前对其它事务都不可见 | | 持久性(D) | 事务一旦提交了，其结果就是永久性的，就算发生了宕机等事故，数据库也能将数据回复 | ##### 并发带来的问题(隔离性的解读) ###### 事务按不守控制的顺序来修改数据 > 脏读 > > > 一个读取到了另一个事务未提交的数据(例如事务二回滚了操作，事务一就读到了错误的数据) > > | 事务1 | 事务2 | > | ------------------------------------------------------------ | --------------------------------------------------- | > | start transaction; | | > | select score from imc_course where course_id=59;/* score = 9.2 */ | | > | | start transaction; | > | | Update imc_course set score=9.6 where course_id=59; | > | select score from imc_course where course_id=59;/* score=9.6 */ | | > | | rollback; | > > > 事务一得到的分值是错误的 > > > > 事务1读取的数据是无效的因为事务2进行了回滚 ###### 不可重复读 > 一个事务前后两次读取的同一数据不一致 > > | 事务1 | 事务2 | > | ------------------------------------------------------------ | ---------------------------------------------------------- | > | start transaction; | | > | select course_id, score from imc_course where course_id=56/* 56,9.6 */ | | > | | start transaction; | > | | update imc_course set score=9.7 where course_id=56;commit; | > | select course_id, score from imc_course where course_id=56 /* 56, 9.7 */ | | > | commit | | > > ###### 不可重复读和脏读差别 > > > 事务二提交了数据修改和回滚了数据修改 > > > > 不可重复读事务1第二次读到数据是有效的 ###### 幻读(不可重复读的一种形式) > 指一个事务两次查询的结果集记录数不一致 > > | 事务1 | 事务2 | > | ------------------------------------------------------------ | ---------------------------------------------------------- | > | start transaction; | | > | select course_id,score from imc_course where score > 9.5 and score < 9.8 /* 56,9.6 \| 73,9.7 */ | | > | | start transaction; | > | | update imc_course set score=9.7 where course_id=43;commit; | > | select course_id, score from imc_course where score > 9.5 and score < 9.8 / * 56,9.6\|73,9.7\|43,9.7 */ | | > | commit; | | > > 幻读读取到的数据也是有效的 ##### 事务的隔离性 | 隔离级别 | 脏读 | 不可重复读 | 幻读 | 隔离性 | 并发性 | | ------------------------------ | ---- | ---------- | ---- | ------ | ------ | | 顺序读(SERIALIZABLE)（串行化） | N | N | N | 最高 | 最低 | | 可重复读(REPEATABLE READ) | N | N | (N) | | | | 读以提交(READ COMMITTED) | N | Y | Y | | | | 读未提交(READ UNCOMMITTED) | Y | Y | Y | 最低 | 最高 | > 顺序读:不存在并发 > > 可重复读:保证在同一事务中多次读取统一数据时读取结果是相同的，在SQL标准中幻读问题无法避免，在INNODB中使用了NEXTKEY下一个键锁避免了可重复读隔离级别幻读的情况(INNODB默认的事务隔离级别) > > 读以提交: 其它数据库默认的事务隔离级别(如SQLserver) 保证了事务只能看到已经被提交事务关联数据的修改 > > 读未提交: 一个事务可以读到另一个事务未提交的事务 > > 隔离级别越高，数据完整性越好，并发性越差 ##### 设置事务的隔离级别 > ```sql > SET [PERSIST|GLOBAL|SESSION] -- PERSIST 重启服务后还有效 GLOBAL 重启服务后失效 SESSION 当前连接有效 > TRANSACTION ISOLATION LEVEL > { > READ UNCOMMITTED > | READ COMMITTED > | REPEATABLE READ > | SERIALIZABLE > } > ``` ###### 顺序读事务(串行化) > ```sql > -- serializable 级别两个事物并发的情况 > -- 事务1 > SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE; > -- 查看当前事务的隔离级别 > SHOW VARIABLES LIKE '%iso%'; > > BEGIN; -- 启动事务 > SELECT course_id, title > FROM imc_course > WHERE study_cnt>5000; > ROLLBACK; > ``` > > ```sql > -- 事务2 > BEGIN; > UPDATE imc_course > SET study_cnt=6000 > WHERE course_id=1; > ROLLBACK; > ``` > > 由于第二个事务修改后的值满足事务1的查询条件，所以事务2只能等事务1提交或回滚后才能够继续执行(rollback/commit) ###### 可重复读 > ```sql > -- REPEATABLE READ 级别两个事物并发的情况 > -- 事务1 > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; > -- 查看当前事务的隔离级别 > SHOW VARIABLES LIKE '%iso%'; > > BEGIN; -- 启动事务 > SELECT course_id, title > FROM imc_course > WHERE study_cnt>5000; > ROLLBACK; > ``` > > ```sql > -- 事务2 > BEGIN; > UPDATE imc_course > SET study_cnt=6000 > WHERE course_id=1; > COMMIT; > ``` > > 启动事务1后启动并提交事务2，再次在事务1中查找相关数据发现，数据并未发生变化，隔离是有效的且未堵塞 ###### 读已提交 > 执行上述SQL后 > > 启动事务1后启动并提交事务2，再次在事务1中查找相关数据发现，数据发生变化(出现不可重复读和幻读) ###### 读未提交 > 执行上述SQL后 > > 启动事务1后启动并不提交事务2，再次在事务1中查找相关数据发现，数据发生变化，此时将事务2进行回滚，此时事务1查询的数据为错误的(出现脏读), 隔离性很差； ##### 可重复读级别下的阻塞(REPEATABLE) > ```sql > -- REPEATABLE READ 级别下的阻塞 > -- 事务1 > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; > -- 查看当前事务的隔离级别 > SHOW VARIABLES LIKE '%iso%'; > > BEGIN; -- 启动事务 > UPDATE imc_course > SET study_cnt=study_cnt+10 > WHERE study_cnt>5000; > > COMMIT; -- commit后事务二才没有阻塞 > ``` > > ```sql > -- 事务2 > BEGIN; > UPDATE imc_course > SET study_cnt=study_cnt-10 > WHERE study_cnt>5000; > COMMIT; > ``` ##### INNODB中的锁 > 锁的作用是保证一个事务不能被另一个事务修改的数据进行修改 > * 查询需要对资源加共享锁(s) > > 被加锁的对象只能被持有锁的事务读取但不能修改，其它事务无法对该对象进行修改，但能够读取 > > * 数据修改需要对资源加排它锁(x) > > 被加锁的对象只能被持有锁的事务读取和修改，其它事务无法读取和修改 > > | | 排它锁 | 共享锁 | > | ------ | ------ | ------ | > | 排它锁 | 不兼容 | 不兼容 | > | 共享锁 | 不兼容 | 兼容 | ##### 阻塞和死锁 ###### 什么是阻塞 > 由于不同锁之间的兼容关系，造成的一事务需要等待另一个事务释放其所占用的资源的现象。 ###### 如何发现数据库存在阻塞 > ```sql > SELECT waiting_pid AS '被阻塞的线程', > waiting_query AS '被阻塞的SQL', > blocking_pid AS '阻塞线程', > blocking_query AS '阻塞SQL' > wait_age AS '阻塞时间', > sql_kill_blocking_query AS '建议操作' > FROM sys.innodb_lock_waits -- 记录了所有Innodb等待的事件 > WHERE > (UNIX_TIMESTAMP()-UNIX_TIMESTAMP(wait_started))>30; -- 阻塞时间超过30s > ``` ##### 查询连接ID > ```sql > SELECT CONNECTION_ID(); -- 用于查看阻塞的进程id > ``` ##### 杀掉连接 > ```sql > KILL id(阻塞的进程id) > ``` ##### 如何处理阻塞 > * 终止占用资源的事务 > * 优化占用资源事务的SQL，使其尽快释放资源 ##### 什么是死锁 > * 并行执行的多个事务相互之间占有了对方所需要的资源 ###### 监控死锁 > 将死锁记录到错误日志中 > > ```sql > SET global innodb_print_all_deadlocks=ON; > ``` > > 然后通过日志查询和程序查询判断死锁的位置 ###### 如何处理死锁 > * 数据库自行回滚占用资源少的事务 > * 并发事务按相同顺序占有资源 ## MySQL经验集锦 ### MySQL备份恢复 #### 如何对数据库进行备份 ##### 备份方式 > 逻辑备份和物理备份 > > 逻辑备份:备份为SQL文件(恢复速度慢)(移至性较好) > > 物理备份: 数据库数据目录的拷贝(恢复速度快)，离线备份和在线备份(移至性较差，环境完全一致) > 全量备份和增量备份以及差异备份 > > 差异备份:比较与上次全量备份的差异进行备份 ##### 常用的备份工具 | 名称 | 特点 | | ---------- | ------------------------------------------------------------ | | mysqldump | 最常用的逻辑备份工具，支持全量备份及条件备份 | | mysqlpump | 多线程逻辑备份工具，mysqldump的增强版本可以对备份数据集进行压缩，备份速度更快 | | xtrabackup | Innodb在线物理备份工具，支持多线程和增量备份 | #### 逻辑备份 #### mysqldump ##### mysqldump的优点 > * 备份结果为可读的SQL文件，可用于跨版本跨平台恢复数据。 > * 备份文件的尺寸小于物理备份，便于长时间保存 > * MySQL发行版自带工具，无序安装第三方插件 #####mysqldump的缺点 > * 只能单线程执行备份恢复任务，备份恢复速度较慢 > * 为完成一致性备份需要对备份表加锁，容易造成阻塞 > * 逻辑备份的思路是把数据从表读出写到备份文件中，会使原本不需要存储在Innodb缓存池中的冷数据放到Innodb缓冲池中；将Innodb缓存池中热数据刷出缓存池，造成Innodb Buffer Pool的污染。 ##### mysqldump使用说明 > ```shell > mysqldump --help > ``` > > ```shell > mysqldump [OPTIONS] database [tables] > mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] > mysqldump [OPTIONS] --all databases [OPTIONS] > # 重要的可选参数 > -E ,-- events # 备份mysql的计划任务，时间 > -F ,--flush-logs # 备份刷新二进制日志 > --max-allowed-packet # 指定最大的SQL执行包大小 > --single-transaction # 启动一个事务来备份 > --triggers # 备份触发器 > ``` > > ```shell > # 进行全数据库备份 在备份路径下输入 > mysqldump -uroot -p --databases imc_db > imcdb.sql > ``` > > ```shell > # 进行表备份 > mysqldump -uroot -p [数据库名] [表名] > [文件名].sql > mysqldump -uroot -p imc_db imc_level > imclevel.sql > ``` > > ```shell > # 记录当前二进制日志信息 --maste-data 2 备份所有数据库 > mysqldump -uroot -p --maste-data 2 --alldatabases > all.sql > ``` > > ```shell > # 恢复数据库 > mysql -uroot -p [数据库名] < sql文件 > mysql -uroot -p imc_db < imcdb.sql > # 如果使用了集群存在GTID阻止恢复就先将GTID置为空 > # 进入mysql > > reset master; > ``` > > ```shell > # 有条件的备份 > # 备份学生人数大于5000的课程信息的表 > mysqldump -uroot -p --where ["条件"] [数据库名] [表名] > [文件名].sql > mysqldump -uroot -p --where "study_cnt>5000" imc_db imc_course > imc_course5000_stu.sql > ``` #### mysqlpump ##### mysqlpump的优点 > * 语法同mysqldump高度兼容 > * 支持基于库和表的并行备份，可以提高逻辑备份的性能 > * 支持使用ZLIB和Lz4算法对备份进行压缩 ##### mysqlpump的缺点 > * 基于表进行并行备份，对于大表来说性能较差，一个表只能使用一个线程 > * 5.7.11之前版本不支持一致性并行备份 > * 会对Innodb Buffer Pool造成污染 ##### mysqlpump使用说明(与mysqldump的区别) > ```shell > # 相较于mysqldump新增的参数 > # 压缩备份的SQL文件 > -C, --compress, --compress-output=name > # 指定默认的并行线程数 > -default-parallelism=# > # 指定并行备份的数据库，如果是多个库可以使用逗号分隔,N是指定多少个线程来备份 > --parallel-schemas=name[N] > # 备份用户信息 > --users > ``` > > ```shell > # mysqlpump压缩算法有LZ4和ZLIB > # 指定压缩算法 取消输出的gtid > mysqlpump -uroot -p --compress-output zlib --set-gtid-purged off --databases imc_db > imc_db_pump.zlib > # 使用zlib文件 > zlib_decompress imc_db_pump.zlib imc_db_pump.sql > ``` > > ```shell > # 备份所有数据库账号 > mysqlpump --users --exclude-databases [数据库名(逗号分隔)] --set-gtid-purged off -uroot -p > ``` #### 物理备份 #### xtrabackup ##### xtrabackup的优点 > * 支持Innodb存储引擎的在线热备份，对Innodb缓冲没有影响。 > * 支持并行对数据库的全备和增量备份。 > * 备份和恢复效率比逻辑备份高。 #####xtrabackup的缺点 > * 做单表恢复时比较复杂 > * 完整的数据文件拷贝，故备份文件比逻辑备份大 > * 对跨平台的数据库版本的备份恢复支持度不如逻辑备份 ##### xtrabackup 的安装 > 8版本不支持5.7 > > wget https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.18/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm > > 依赖包安装 > > yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL > > yum -y install rsync perl l perl-Digest-MD5 > > wget ftp://ftp.pbone.net/mirror/apt.sw.be/redhat/el6/en/x86_64/rpmforge/RPMS/libev-4.15-1.el6.rf.x86_64.rpm > > rpm -ivh libev-4.15-1.el6.rf.x86_64.rpm > > rpm -ivh percona-xtrabackup-24-2.4.18-1.el7.x86_64.rpm > > xtrabackup --help # 检查是否安装完毕 ##### xtrabackup 的使用 > xtrabackup只能对Innodb的表进行备份 > > innobackupex --help > > Innobackupex 相当于对xtrabackup进行封装可以对其它引擎的数据库进行备份 > > ```shell > # 建立备份目录 > mkdir -p /root/db_backup > # 进行备份 > innobackupex --user=root --password=123456 /root/db_backup > # 指定并行备份的线程数和目录进行备份 > innobackupex --user=root --password=123456 --parallel=2 /root/db_backup/20200208 --no-timestamp > ``` > > ```shell > # 恢复 > innobackupex --apply-log /root/db_backup/20200208 > ``` > > 暂停mysql实例，将恢复的日志文件拷贝至原目录进行恢复