MySQL学习笔记

自己学习MySQL时整理的笔记，包括实操中遇到的问题，不同版本之间的差异。后续也会继续完善，有PDF文档版。
学习视频：https://www.bilibili.com/video/BV1Kr4y1i7ru?t=1.9

怎么学，收获？

基础篇：（初级工程师）

• MySQL概述
• SQL
• 函数
• 约束
• 多表查询
• 事务

进阶篇：（中级工程师）

• 存储引擎
• 索引
• SQL优化
• 视图/存储过程/触发器
• 锁
• InnoDB核心
• MySQL管理

运维篇：（高级工程师）

• 日志
• 主从复制
• 分库分表
• 读写分离

MySQL初级篇

MySQL概述

1. 数据库（DB）
   • 数据存储的仓库
2. 数据库管理系统（DBMS）
   • 操作和管理数据库的大型软件
3. SQL
   • 操作关系型数据库的编程语言，定义了操作关系型数据库的统一标准

关系型数据库(RDBMS)

建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

1. 使用表存储数据，格式统一，便于维护
2. 使用SQL语言操作，标准统一，使用方便

MySQL数据库 数据模型

客户端–连接–DBMS–使用sql语句通过DBMS创建数据库/表/存储数据

SQL

• sql通用语法
• sql分类
• DDL
• DML
• DQL
• DCL

SQL通用语法

1. SQL语句可以单行或多行书写，以分号结尾
2. SQL语句可以使用空格/缩进来增强可读性
3. MySQL数据库的SQL语句不区分大小写，关键字建议使用大写
4. 注释：
   • 单行注释：-- 注释内容 或 #注释内容（MySQL特有）
   • 多行注释：/*注释内容*/

SQL分类

分类	说明
DDL	数据库定义语言，用来定义数据库对象（数据库，表，字段）
DML	数据库操作语言，用来对数据库表中的数据进行增删改
DQL	数据库查询语言，用来查询数据库中表的记录
DCL	数据库控制语言，用来创建数据库用户、控制数据库的访问权限

DDL

DDL-数据库操作

查询、创建、删除、使用

# 查询
show databases;			-- 查询所有数据库
select database();		-- 查询当前数据库
# 创建
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
# 删除 
drop databse [if exists] 数据库名;
# 使用
use 数据库名;

DDL-表操作

查询、创建、修改、删除

查询

# 查询
show tables;		-- 查询当前数据库所有表
desc 表名;		-- 查询表结构
show create table 表名;		-- 查询指定表的建表语句

创建

create table 表名(
	字段1	字段1类型 [comment 字段1注释],
    字段2	字段2类型 [comment 字段2注释],
    字段3	字段3类型 [comment 字段3注释],
    ......
    字段n	字段n类型 [comment 字段n注释]
)[comment 表注释];

注意：[…]为可选参数，最后一个字段后没有逗号

DDL-表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值、字符串、日期时间

数值类型

• age tinyint unsigned	-- 表示没负数
  

字符串类型

二进制数据（视频/音频/安装包）开发当中一般不怎么做，性能不高，一般采用专门的文件服务器存储

定长字符串char(10) 和 变长字符串varchar(10)

相同点：10代表最多只能存储10个字符，一旦超过就报错

不同：

• char(10)—>性能好：即使存储一个字符，也会占用10个字符空间，其它空间用空格补位
• varchar(10)—>性能较差：存储一个字符就占用一个字符空间，根据存储内容去计算占用的空间是多少

日期时间类型

修改

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

alter table test add email varchar(20) comment '邮件';

desc test; -- 查看表信息，进行验证

修改数据类型

alter table 表名 modify 字段名 新数据类型(长度);

修改字段名和字段类型

alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释][约束];

删除字段

alter table 表名 drop 字段名;

修改表名

alter table 表名 rename to 新表名;

删除

删除表

drop table [if exists] 表名;

删除指定表，并重新创建该表

truncate table 表名;	-- 原表的结构并不会变

DML

数据操作语言：对数据中表的数据进行增、删、改

• 添加数据：insert
• 修改数据：update
• 删除数据：delete

DML-添加数据

1. 给指定字段添加数据

   insert into 表名(字段1,字段2,...)values(值1,值2);
   

2. 给全部字段添加数据

   insert into 表名 values(值1,值2,...);
   

3. 批量添加数据

   insert into 表名(字段1,字段2,...) values(值1,值2,...),(值1,值2,...),(值1,值2,...);
   insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...);
   

注意：

• 插入数据时，指定的字段顺序需要与值的顺序是一一对应的
• 字符串和日期型数据应该包含在引号中
• 插入的数据大小，应该在字段的规定范围内

DML-修改数据

update 表名 set 字段1=值1, 字段2=值2, ... [where 条件];

注意：修改语句的条件可以有，也可以没有，没有条件则会修改整张表的所有数据

DML-删除数据

delete from 表名 [where 条件];

注意：

• delete语句的条件可以有，也可以没有，没条件则表示删除整张表的所有数据
• delete语句不能删除某一个字段的值（可以使用update）

DQL

数据库查询语言，查询数据库中表的记录

关键字：select

DQL-语法

-- 编写顺序
select 
	字段列表
from
	表名列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

• 基本查询
• 条件查询（where）
• 聚合函数（count、max、min、avg、sum）
• 分组查询（order by）
• 分页查询（limit）

DQL-基本查询

1. 查询多个字段

   select 字段1,字段2,字段3... from 表名;
   select * from 表名;
   

2. 设置别名

   select 字段1[as 别名1], 字段2[as 别名2] ... from 表名;	# as可以省略不写
   

3. 去除重复记录

   select distinct 字段列表 from 表名;
   

DQL-条件查询

1. 语法

   select 字段列表 from 表名 where 条件列表;
   

2. 条件

   比较运算符	功能	逻辑运算符	功能
   >	大于	and 或 &&	并且（多个条件同时成立）
   >=	大于等于	or 或 ||	或者（多个条件任意一个成立）
   <	小于	not 或 ！	非，不是
   <=	小于等于
   =	等于
   <> 或 !=	不等于
   between…and …	在某个范围之内（含最小、最大值）
   in(…)	在in之后的列表中的值，多选一
   like 占位符	模糊匹配（_匹配单个字符，%匹配任意个字符）
   is NULL	为空

DQL-聚合函数

通常进行分组查询的时候，会配合着聚合函数来操作

1. 介绍

   将一列数据作为一个整体，进行纵向计算。

2. 常见的聚合函数

   函数	功能
   count	统计数量
   max	最大值
   min	最小值
   avg	平均值
   sum	求和

3. 语法

   select 聚合函数(字段列表) from 表名;
   select avg(age) from user;
   

   注意：NULL值不参与聚合函数运算。

DQL-分组查询

1. 语法

   select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];
   

2. where与having区别

   • 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
   • 判断条件不同：where不能对聚合函数进行判断，而having可以。

3. 注意

   • 执行顺序：where > 聚合函数 > having

   • 分组之后，查询的字段一般为聚合函数和分组字段，查询其它字段无任何意义。

     -- 根据性别分组之后，统计人数【gender,count(*)】
     select name, gender, count(*) from emp group by gender;
     
     -- name字段查出来的是，每组第一个人的姓名，没有任何意义
     

DQL-排序查询

1. 语法

   select 字段列表 from 表名 order by 字段1 排序方式1, 字段2 排序方式2;
   

2. 排序方式

   • ASC：升序（默认值）
   • DESC：降序

3. 注意：如果是多字段排序，当第一个字段值相同时，才会根据第二字段进行排序。

DQL-分页查询

1. 语法

   select 字段列表 from 表名 limit 起始索引, 查询记录数;
   

2. 注意

   • 起始索引从0开始，起始索引 =（查询页码 -1）* 每页显示记录数。
   • 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中的是limit。
   • 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

DQL-执行顺序

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mOD84083-1687944484121)(D:\0-视频#-笔记\MySQL\笔记配图\DQL-执行顺序.png)]

DCL

数据库控制语言，用来管理数据库用户、控制数据库的访问权限。

DCL-管理用户

1. 查询用户

   use mysql;
   select * from user;
   

2. 创建用户

   create user '用户名'@'主机名' identified by '密码';
   

   主机名：localhost 只能本机访问，改为 % 表示任意主机访问

   用户名和主机地址才能定位一个MySQL用户

3. 修改用户密码

   set password for '用户名'@'主机名' = password('新密码');
   # 新密码必须使用 PASSWORD() 函数来加密，若不使用 PASSWORD() 加密，也会执行成功，但是用户会无法登录。
   
   alter user '用户名'@'主机名' identified by '新密码';	# 好像只有5.7以上的版本支持alter修改
   
   alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';	# 修改身份验证插件
   

   注意版本：

   • mysql5.7版本之前，修改用户密码使用set、update
   • mysql5.7版本之后，password属性已经取消（使用SELECT Password(‘123’) 会报错），修改用户密码的语句已经改变
   • 在MySQL 5.7中，默认的身份验证插件还是为 mysql_native_password。
   • 在MySQL 8.0中，caching_sha2_password是默认的身份验证插件，此方式并不兼容常规的老的web服务接口。
     • ​ 像在使用MySQL的可视化工具Navicat时，又或者Django和MySQL的配合使用，由于Django不支持新版本MySQL的加密方式，会报错，所以需要将默认的连接方式恢复为旧的mysql_native_password方式。

4. 删除用户

   drop user '用户名'@'主机名';
   

注意：

• 主机名可以使用通配符 %
• 这类SQL开发人员操作的比较少，主要是DBA（Database Administrator 数据库管理员）使用。

DCL-权限控制

注意：MySQL中 test 数据库比较特别，所有能连接到MySQL的用户，几乎都拥有test库的所有权限。

详情：mysql的test 库的相关特性-renwd-ChinaUnix博客

MySQL中定义了很多种权限，但是常用的就以下几种：

权限	说明
all, all privileges	所有权限
select	查询数据
insert	插入数据
update	修改数据
delete	删除数据
alter	修改表
drop	删除数据库/表/视图
create	创建数据库/表

1. 查询权限

   show grants for '用户名'@'主机名';
   

2. 授予权限

   grant 授权列表 on 数据库名.表名 to '用户名'@'主机名';
   

3. 撤销权限

   revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
   

注意：

• 多个权限之间，使用逗号分隔
• 授权时，数据库名和表名可以使用 * 进行通配，代表所有。

函数

函数是一段可以直接被另一段程序调用的程序或代码。

• 字符串函数
• 数值函数
• 日期函数
• 流程函数

字符串函数

MySQL中内置了很多字符串函数，常用的几个如下：

函数	功能
concat(S1, S2, …Sn)	字符串拼接，将S1, S2, …Sn拼接成一个字符串
lower(str)	将字符串 str 全部转为小写
upper(str)	将字符串 str 全部转为大写
lpad(str, n, pad)	左填充，用字符串 pad 对 str 的左边进行填充，达到n个字符串长度
rpad(str, n, pad)	又填充，用字符串 pad 对 str 的右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str, start, len)	返回从字符串 str 从 start 位置起的 len 个长度的字符串

数值函数

常见的数值函数如下：

函数	功能
cell(