# MySQL
# 参考文档
# 图形化界面
- DBeaver，开源免费，Linux 首选。
- HeidiSQL，一款精简版的，特别好用。
- Navicat。
- SQLyog。
# 数据类型
MySQL 中的数据类型有很多，主要分为三类：
- 数值类型。
- 字符串类型。
- 日期时间类型。
# 数值类型
每种类型都分为有符号（SIGNED）和无符号（UNSIGNED）类型，比如定义一个无符号的小整数 age TINYINT UNSIGNED。
| 类型 | 大小 | 描述 |
|---|---|---|
| TINYINT | 1 byte | 小整数 |
| SMALLINT | 2 b | 大整数 |
| MEDIUMINT | 3 b | 大整数 |
| INT or INTEGER | 4 b | 大整数 |
| BIGINT | 8 b | 极大整数 |
| FLOAT | 4b | 单精度浮点数值 |
| DOUBLE | 8b | 双精度浮点数值 |
| DECIMAL | 小数值（精确定点数） |
# 字符串类型
| 类型 | 大小 | 描述 |
|---|---|---|
| CHAR | 0-255 bytes | 定长字符串 |
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制数据 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16777215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUM TEXT | 0-16777215 bytes | 中等长度长文本数据 |
| LONGBLOB | 0-4294967295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4294967295 bytes | 极大文本数据 |
注意： 正常使用中，需要指定字符串的长度，比如 chart(10) 表示最多只能存储 10 个字符，超过报错。假如 char(10) 中只存储了一个字符，依然会占用 10 个空间，其他的用空格补位。varchar(10) 中如果如果存储小于 10 个字符的内容，存储的多大就是多大。
# 日期时间类型
| 类型 | 大小 | 范围 | 格式 | 描述 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期 |
| TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | 混合日期和时间值，时间戳 |
# SQL
# 语法
- SQL 语句以分号结尾，可以单行也可多行。
- SQL 语句可以使用空格/缩进来增强语句的可读性。
- MySQL 数据库的 SQL 语句
不区分大小写，关键字建议大写。 - 注释：
- 单行注释：
-- xxx或者# xxx(MySQL 独有)。 - 多行内容：
/* xxx */
- 单行注释：
# 分类
DDl，Data Definition Language，数据定义语言，用来定义数据库对象（数据库、表、字段）。DML，Data Manipulation Language，数据操作语言，用来对数据库表中的数据进行增删改。DQL，Data Query Language，数据查询语言，用来查询数据库中表的记录。DCL，Data Control Language，数据控制语言，用来创建数据库用户、控制数据库的访问权限。
# DDL
Data Definition Language，数据
定义语言，用来定义数据库对象（数据库、表、字段）。
# 操作数据库
查询所有数据库：SHOW DATABASES;。
查询当前数据库：SELECT DATABASES();。
创建数据库：CREATE DATEABASE [IF NOT EXISTS] db_name [DEFAULT CHARSET 字符集] [COLLATE 排序规则];。
- 例如：
CREATE DATABASE if not exists user;。
删除数据库：DROP DATABASE [IF EXIST] db_name;。
使用数据库：USE db_name;。
# 操作表
查询所有表：SHOW TABLES;。
创建表结构：
CREATE TABLE tb_name (
字段1 字段1类型[COMMENT 字段1注释]，
字段2 字段2类型[COMMENT 字段2注释]，
...
)[COMMENT 表注释];
# 示例
create table m_user(
-> id int comment "no",
-> age int comment "age"
-> ) comment 'user table';
2
3
4
5
6
7
8
9
10
查询表结构：DESC tb_name;。
查询指定表的建表语句：SHOW CREATE TABLE tb_name;。
修改表：
- 添加字段（ADD）：
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];。 - 修改字段名和类型（CHANGE）：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];。 - 修改字段数据类型（MODIFY）：
ALTER TABLE 表名 MODIFY 字段名 新类型(长度);。 - 删除字段（DROP）：
ALTER TABLE 表名 DROP 字段名;。 - 修改表名（RENAME）：
ALTER TABLE 表名 RENAME TO 新表名;。 // TO 可省
删除表：DROP TABLE [IF EXISTS] 表名;。
删除表，然后重建该表（即清除表数据，但保留表结构）：TRUNCATE TABLE 表名;。
# DML
Data Manipulation Language，数据
操作语言，用来对数据库表中的数据进行增删改。
- 添加数据
INSERT。 - 修改数据
UPDATE。 - 删除数据
DELETE。
# 添加数据/INSERT
- 给
所有字段添加数据：INSERT INTO 表名 VALUES (值1，值2，...);。 - 给
指定字段添加数据：INSERT INTO 表名 (字段名1，字段名2，...) VALUES (值1，值2，...);。 - 批量添加数据：
INSETR INTO 表名 (字段1，字段2，...) VALUES (值1，值2，...), (值1，值2，...);INSETR INTO 表名 VALUES (值1，值2，...),(值1，值2，...);
注意： 插入的字符串或者时间格式的值需要加在引号里面。
# 修改数据/UPDATE
UPDATE 表名 SET 字段1=值1，字段2=值2，... [WHERE 条件];，若无修改条件，默认改全表。
# 删除数据/DELETE
DELETE FROM 表名 [WHERE 条件];，若无条件，默认改全表。
- 只能删除整条记录，不能删除一条记录中的某个字段的值，如果想要删除某个字段的值，可以使用 update 某个字段为 null。
# DQL
Data Query Language，数据
查询语言，用来查询数据库中表的记录。
查询用关键字 SELECT，查询语法为：
// 数字代表实际执行顺序，也就说返回字段是在分组过滤后才做的。
SELECT 字段列表 // 4
FROM 表名列表 // 1
WHERE 条件列表 // 2
GROUP BY 分组字段列表 // 3
HAVING 分组后条件列表 //
ORDER BY 排序字段列表 // 5
LIMIT 分页参数 // 6
2
3
4
5
6
7
8
# 基本查询/别名/去重
SELECT * FROM 表名;SELECT 字段1, 字段2, ... FROM 表名;
设置别名：
SELECT 字段1 [AS 别名1]，字段2 [AS 别名2] ... FROM 表名;// AS 可省
去重：
SELECT DISTINCT 字段列表 FROM 表名;
# 条件查询
使用关键字 WHERE，语法为：SELECT 字段列表 FROM 表名 WHERE 条件列表;。
可用条件有：
| 符号 | 功能 |
|---|---|
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| = | 等于 |
| <> 或 != | 不等于 |
| BETWEEN...AND... | 在某个范围内（含最小最大值） |
| IN(...) | 在 in 之后的列表中的值，多选一 |
| LIKE 占位符 | 模糊匹配（ _ 匹配单个字符，% 匹配多个字符） |
| IS NULL | 是 NULL |
| AND 或 && | 并条件 |
OR 或 || | 或条件 |
| NOT 或 ! | 非条件，取反 |
BETWEEN 小值 AND 大值<==>>= 小值 && <= 大值- 查询名字为两个字的用户：
SELECT * FROM user WHERE name LIKE '__'。
# 聚合函数
将一列数据作为一个整体，进行纵向计算。
语法： SELECT 聚合函数(字段列表) FROM 表名;。
常用的聚合函数有：
| 函数 | 作用 |
|---|---|
| count | 统计数量 |
| max | 最大值 |
| min | 最小值 |
| avg | 平均值 |
| sum | 求和 |
注意： NULL 值不参与聚合运算。
# 示例
- 统计用户总数：
SELECT COUNT(*) FROM user;。 - 统计用户总数：
SELECT AVG(age) FROM user;。
# 分组查询
关键字 GROUP BY：SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段 [HAVING 分组后过滤条件];。
注意： WHERE > 聚合函数 > HAVING。
# 排序查询
关键字 ORDER BY：SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1，字段2 排序方式2;。
排序方式分为：
ASC升序（默认）。DESC降序。
多个字段排序规则：先按照第一个字段排序，第一个字段值相同时，再按照第二个字段排序。
# 示例
按照年龄升序排序：SELECT * FROM user ORDER BY age ASC;。
# 分页查询
分页查询的实现每个数据库是不一样的，在 MySQL 中使用的是关键字 LIMIT：
SELECT * FROM 表名 LIMIT 起始索引，要查询条数。
- 起始索引 0 开始，
# DQL-多表查询
多表查询主要分为 连接查询（交集）、联合查询（并集）和 嵌套查询(子查询)。
# 连接查询
连接查询共分为：
内连接，只取两表交集外连接- 左外连接，查询左表所有数据
- 右外连接，查询右表所有数据
自连接？？
连接查询需要指定两个表的关联条件，用来消除笛卡尔积。笛卡尔积：集合 A 和 集合 B 的所有排列组合值。
# 内连接
内连接只返回集合 A 和集合 B 的交集，分为 2 种：
- 隐式内连接
SELECT * FROM t1, t2 WHERE t1.xx = t2.yy - 显式内连接
SELECT * FROM t1 [INNER] JOIN t2 ON t1.xx = t2.yy
-- 隐式内连接
select user.*, ur.name from user, user_role ur where user.role_id = ur.id;
-- 显式内连接，inner 可省略
select * from user [inner] join user_role ur on user.role_id = ur.id;
2
3
4
# 外连接
外连接分为 2 种：
- 左外连接，
SELECT * FROM t1 LEFT [OUTER] JOIN t2 ON t1.xx = t2.yy - 右外连接，
SELECT * FROM t1 RIGHT [OUTER] JOIN t2 ON t1.xx = t2.yy
左连接返回左表所有数据和交集数据，右连接亦然。
# 自连接
自连接是一种特殊的外连接，自己连接自己
语法：SELECT * FROM t1 alias1 JOIN t1 alias2 ON alias1.learder_id = alias2.id
自连接可以是内连接也可以是外连接。
# 联合查询
联合查询就是将多次查询合并，返回一个新的结果集，关键字 union 或 union all。
union，查询到的结果去重处理。union all，查询到的结果只是简单的拼接在一起，不会去重处理。
联合查询的多张表的列数和字段类型必须保持一致。
SELECT * DROM t1
UNION [ALL]
SELECT * FROM t2;
2
3
# 嵌套查询/子查询
嵌套查询也叫子查询。用
()括起来。
语法：SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
子查询的外部语句可以是 SELECT、INSERT、UPDATE、DELETE 种任意一种。
--> 根据查询结果不同，可以分为以下几种：
标量子查询，子查询返回的结果是一个单个的值列子查询，返回结果是一列（可以多行）行子查询，返回结果是一行（可以多列）表子查询，返回结果是多行多列的
--> 根据子查询位置，可以分为：
WHERE之后FROM之后SELECT之后
# 标量子查询
子查询返回的结果是一个单个的值
# 列子查询
子查询返回的结果是一列（可以多行）
select * from user where roleInfo in (select ...)
# 行子查询
子查询返回的结果是一行（可以多列）
select * from user where id = 1 and age = 2;
-- 等同于
select * from user where (id, age) = (1, 2);
select * from user where (id, age) = (select ...)
2
3
4
5
# 表子查询
子查询返回的结果是多行多列
示例：查询和张三、李四的职位和工资相同的员工信息。
-- 1. 查询张三、李四的职位和工资
select job, salary from emp where name = '张三' or name = '李四';
-- 返回结果是一个多行多列的
-- job salary
-- 开发 1800
-- 项目经理 3500
-- 2. 查询员工信息
select * from emp where (job, salary) in (select ...)
2
3
4
5
6
7
8
查询结果可以当做临时表进行操作，查询的结果不仅还可以放在 from 后，作为一张表进行查询。
# DCL
Data Control Language，数据
控制语言，用来创建数据库用户、控制数据库的访问权限。
# 用户管理
MySQL 数据库的用户存放在系统数据库 mysql 中的 user 表中。
# 查询用户
USE mysql;
SELECT * FROM user;
2
# 创建用户
CREATE USER '用户名@主机名' INDENTIFIED BY '密码';
# 修改用户密码
ALTER USER '用户名@主机名' INDENTIFIED WITH mysql_native_password BY '新密码';
# 删除用户
DROP USER '用户名@主机名';
# 权限控制
MySQL 中定义了很多权限，常用的权限如下：
| 权限 | 说明 |
|---|---|
| ALL，ALL PRIVILEGES | 所有权限 |
| SELECT | 查询数据 |
| INSERT | 插入数据 |
| UPDATE | 更新数据 |
| DELETE | 删除数据 |
| ALTER | 修改表 |
| DROP | 删除数据库/表/视图 |
| CREATE | 创建数据库/表 |
# 查询权限
SHOW GRANTS FOR '用户名@主机名';
# 授予权限
GRANTS 权限列表 ON 数据库.表名 TO '用户名@主机名';
# 撤销权限
REVOKE 权限列表 ON 数据库.表名 FROM '用户名@主机名';
# 函数
# 字符串函数
CONCAT(S1, S2, S3...)，拼接字符串。LOWER(str)UPPER(str)LPAD(str, n, pad)，左填充，用 pad 对 str 的左边进行填充，达到 n 个字符串长度。RPAD(str, n, pad)TRIM(str)，去除字符串头尾的空格。SUBSTRING(str, start, length)，从 start 开始 length 长度。
# 数值函数
CEIL(x)，向上取整。FLOOR(x)，向下取整。MOD(x，y)，返回 x/y 的模（取余数）。RAND(x)，返回 0~1 内的随机数。ROUND(x, y)，求参数 x 的四舍五入的值，保留 y 位小数。
生成 6 位验证码：SELECT LPAD( CEIL (RAND()*1000000),6,0)。
# 日期函数
CURDATE(), current date。CURTIME(), current time。NOW(), current datetime。YEAR(date)MONTH(date)DAY(date)DATE_ADD(date, INTERVAL expr type)，返回一个日期/时间值加上一个时间间隔 expr 后的时间值。- 70 天后的时间：
SELECT DATE_ADD(NOW(), INTERVAL 70 DAY);。
- 70 天后的时间：
DATEDIFF(date1, date2)，date1 - date2，返回起止时间的天数。
# 流程函数
IF(boolValue, t, f)IFNULL(value1, value2)，如果不为空返回 val1，否则返回 value 2，类似 getIfBlank() 方法。CASE WHEN [val1] THEN [res1] ... ELSE [defaultValue] END，如果 val1 == true，则返回 res1，否则返回 default 默认值。CASE [expr] WHEN [val1] THEN [res1] ... ELSE [defaultValue] END，如果 expr 的值等于 val1，则返回 res1，否则返回 default 默认值。
示例1：
SELECT
nickname ,
mobile ,
(CASE
address when '北京' then '一线'
when '上海' THEN '一线'
ELSE '二线'
end) as 地址
FROM
member
2
3
4
5
6
7
8
9
10
# 约束
约束是作用与表中字段上的规则，用于限制存储在表中的数据。目的是为了保证数据库中数据的正确、有效性和完整性。
| 约束 | 关键字 | 描述 |
|---|---|---|
| 非空约束 | NOT NULL | 限制该字段的数据不能为 NULL |
| 唯一约束 | UNIQUE | 保证该字段的所有数据都是唯一、不重复的 |
| 主键约束 | PRIMARY KEY | 主键是一行数据的唯一标识，要求非空且唯一 |
| 默认约束 | DEFAULE | 保存数据时，如果未指定该字段的值，则采用默认值 |
| 外键约束 | FOREIGN KEY | 用来让两张表的数据之间建立连接，保证数据的一致性和完整性 |
| 检查约束（v8.0.16 之后） | CHECK | 保证字段值满足某个条件 |
注意：约束作用于表中的字段上，可以在创建/修改表的时候添加约束。
# 事务
事务就是一组操作的集合，它是一个不可分割的单位，事务会把所有操作当做一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。
MySQL 的事务默认是自动提交的，也就说，当执行一条 DML 语句，MySQL 会立即隐式的提交事务。
# 四大特性
MySQL 的事务满足 ACID 特性：
- 原子性
Atomicity，事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 - 一致性
Consistency，事务完成时，必须使所有的数据都保持一致状态。 - 隔离性
Isolation，数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。 - 持久性
Durability，事务一旦提交或回滚，它对数据库中数据的改变就是永久的。
# 操作
- 查看事务提交方式
SELECT @@autocommit; - 设置事务提交方式
SELECT @@autocommit = 0; - 开启事务
START TRANSACTION或BEGIN - 提交事务
COMMIT; - 回滚事务
ROLLBACK;
# 隔离级别
MySQL 中事务的隔离级别一共分为 4 种，隔离级别由低到高依次为：
- 读未提交
READ UNCOMMITED，所有事务都可以看到其它事务未提交的数据。- 可能发生 脏读，很少使用。
- 因为可以读取到其他事务中未提交的数据，而未提交的数据可能会发生回滚，因此我们把该级别读取到的数据称之为脏数据，把这个问题称之为脏读。
- 读已提交
READ COMMITED，一个事务提交后，它修改的数据才会被其它事务看到。- 可能发生 不可重复读。
- 可重复读
REPEATABLE READ，一个事务执行过程中看到的数据，总是和这个事务开启时看到的数据一致。// MySQL default- 可能发生 幻读。InnoDB 通过 MVCC 解决了幻读。
- 可串行化
SERIALIZABLE，按照顺序执行事务- 解决 幻读。最高隔离级别，效率最低。
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交 | Y | Y | Y |
| 读已提交 | - | Y | Y |
| 可重复读 | - | - | Y |
| 可串行化 | - | - | - |
注：Y 表示可能发生，- 表示不会发生。
# 事务问题
主要有 3 种问题：
脏读，一个事务读取到其它事务还未提交的数据（未提交的数据可能会发生回滚）。不可重复读，当一个事务 A 开启后，查询 user1 后，事务 B 修改了 user1 的数据并提交了，然后事务 A 的第 2 步有查询了 user1，这时候对于事务 A 来说，读取到的 user1 数据变化了，这种现象就是不可重复读。幻读，事务 A 中第一步查询 user1 不存在，事务 B 创建出来 user1，事务 A 第一步中查询 user1 不存在则执行插入操作，但是插入失败，查询 user1 也是不存在（解决不可重复读后，事务读取的数据总和事务开启前一致，所以事务 B 插入后，事务 A 也读取不到的），这种现象就是幻读。即，我查询没有啊，怎么插入的时候，又有了？
# 讨论区
由于评论过多会影响页面最下方的导航，故将评论区做默认折叠处理。