MySQL 速查手册（基础）

前言： 学习MYSQL后有一段时间没使用了，又把以前的笔记翻出来整理一遍，方便自己以后遇到不会的来查一下

一、基础语法

1、DDL

• 数据库的的增删改查

# 创建
CREATE DATABASE 数据库名 [CHARACTER SET 字符集];

# 删除
DROP DATABASE 数据库名;

# 修改
ALTER DATABASE 数据库名 CHARACTER SET 字符集;

# 查看
SHOW DATABASES; # 查看有哪些数据库
USE 数据库名; # 使用数据库
SHOW CREATE DATABASE 数据库名; # 查看定义信息

• 数据表的增删改查

# 创建
CREATE TABLE 表名 like 另一个表;
CREATE TABLE 表名 (
    字段1 INT PRIMARY KEY [AUTO_INCREMENT], # 自增主键，必须为INT类型
    字段2 CHAR
    字段3 INT
	);

# 删除
DROP TABLE [IF EXISTS] 表名;

# 修改
ALTER TABLE 表名 TO 新表名;
ALTER TABLE 表名 MODIFY 字段名 字段类型; # 修改字段类型
ALTER TABLE 表名 CHANGE 旧列名 新列名  类型; # 修改字段
ALTER TABLE 表名 ADD PRIMARY KEY(eid); # 讲eid添加为主键

# 查看
SHOW TABLES; # 查看有哪些表
DESC 表名; # 查看表结构

注：除了主键约束外还有唯一约束(UNIQUE)、非空约束(NOT NULL)、外键约束(FOREIGN KEY)。主键约束同时具有唯一与非空的性质，可以用与唯一标识一条数据

2、DML

• 对数据的增删改

# 插入
INSERT INTO 表名 (字段1, 字段2) VALUE(值1, 值2);

# 删除
DELETE FROM 表名 [WHERE 条件表达式]; # 不使用WHERE 将会删除全表数据，不影响主键的自增
TRUNCATE TABLE 表名; # 删除全表数据，速度更快。主键将重新从1开始

# 修改
UPDATE 表名 SET 列名 = 值 [WHERE 条件表达式];

3、DQL

#查询
SELECT
	[DISTINCT]*, # "*"表示所有字段,DISTINCT去重
	[字段1,]# 查询某个字段
	[字段1 + 字段2 AS 型新段,] # 查询运算结果，AS 后面跟别称，可以省略
	[聚合函数,] # 使用聚合函数查询
	[窗口函数,] # 使用窗口函数
FROM
	表1[[INNER / LEFT / RIGHT] JOIN 表2 ON 表连接条件]
WHERE
	条件表达式
GROUP BY
	[分组字段 / 表达式]
HAVING
	条件表达式
ORDER BY
	[排序字段 / 表达式] [ASC / DESC] #ASC 表示升序排序(默认) DESC 表示降序排序
LIMIT
	起始行数 , 返回的行数

SELECT语句的执行顺序如下：

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

注： WHERE与HAVING都可以筛选数据，但HAVING是分组之后的过滤，可以使用聚合函数书。WHERE是分组前进行过滤，不能使用聚合函数

二、高级查询

1、运算符

• 条件运算符

运算符	说明
> < <= >= = <> !=	大于、小于、小于(大于)等于、等于、不等于
BETWEEN …AND…	显示在某一区间的值例如 2000-10000之间： Between 2000 and 10000
IN(集合)	集合表示多个值,使用逗号分隔,例如: name in (悟空，八戒)，in中的每个数据都会作为一次条件,只要满足条件就会显示
LIKE ‘%张%’	模糊查询，%可以匹配任意多个字符，-匹配 一个字符
IS NULL	查询某一列为NULL的值, 注: 不能写 = NULL

• 逻辑运算符

运算符	说明
AND	与
OR	或
NOT	非

2、聚合函数

COUNT([DISTINCT] 字段) # 计数
SUM([DISTINCT] 字段) # 求和
MAX(字段) # 最大值
MIN(字段) # 最小值
AVG(字段) #平均值

3、窗口函数

• 语法

窗口函数(字段 或 表达式) OVER(PARTITION BY 字段 ORDER BY 字段 ROWS BETWEEN 数据范围 )

• ROWS BETWEEN 示例

rows between 2 preceding and current row # 取当前行和前面两行
rows between unbounded preceding and current row # 包括本行和之前所有的行
rows between current row and unbounded following # 包括本行和之后所有的行
rows between 3 preceding and current row # 包括本行和前面三行
rows between 3 preceding and 1 following # 从前面三行和下面一行，总共五行
# 当order by后面缺少窗口从句条件，窗口规范默认是rows between unbounded
preceding and current row.
# 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding
and unbounded following

• MYSQL窗口函数

① 聚合类窗口函数

SUM(x) , COUNT(x), AVG(x), MAX(X), MIN(X)

② 排名函数

RANK() OVER() # 可以有多个相同名称，排名跳跃，如：1,1,3,4,5,5,5,8
DENSE_RANK() OVER() # 可以有多个相同名称，排名不跳跃，如：1,1,1,2,2,3,3,4
ROW_NUMBER() OVER() # 每行一个编号如：1,2,3,4,5,6,7,8
NTILE(n) OVER((partition by a order by b ) 
# 用于将分组数据按照顺序切分成n片，返回当前切片 NTILE不支持ROWS BETWEEN

③ 偏移分析函数

LAG(字段, 偏移量, 默认值) OVER() # 向上(后)偏移，没有默认值时为NULL
LEAD(字段, 偏移量, 默认值) OVER() # 向下(前)偏移

4、多表查询

# 隐式内连接
SELECT * FROM 表1 t1, 表2 t2 ON t1.字段 = t2.字段
# 显式内连接
SELECT * FROM 表1 t1 INNER JOIN 表2 t2 ON t1.字段 = t2.字段
# 左连接，保留左表数据
SELECT * FROM 表1 t1 LEFT JOIN 表2 t2 ON t1.字段 = t2.字段
# 右连接，保留左表数据
SELECT * FROM 表1 t1 RIGHT JOIN 表2 t2 ON t1.字段 = t2.字段

三、子查询与视图

1、子查询

子查询就是一条SELECT 的结果，作为另一条SELECT 的一部分。子查询必须使用小括号括起来

# WHERE 型子查询
SELECT 查询字段 FROM WHERE 字段 = (子查询);

# 子查询结果作为一张表
SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;

# IN 判断子查询，子查询的结果必须是单列多行
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

当多个子查询中都需要使用同一个子查询的结果可以使用WITH AS

WITH 子查询 AS(SELECT 查询字段 FROM 表)
SELECT 查询字段 FROM 子查询;

2、合并查询

UNION 可以合并多个SELECT语句的结果集，并消除重复行。

SELECT 查询字段 FROM 表1
UNION
SELECT 查询字段 FROM 表2;

要求合并的结果有相同的列数与数据类型

**注：**若可以确认合并的两个结果集中不包含重复数据，那么就可以使用UNION ALL，这要比UNION快很多，因为UNION ALL只是简单的将两个结果合并，不会去重

3、视图

视图是一种虚拟表，建立在已有表之上，可以像表一样使用。可以看作储存起来的SELECT语句。主要用于简化复杂的查询以及权限控制

CREATE VIEW 视图名 AS SELECT语句;

四、MySQl函数

1、数学函数

ABS(X) # 绝对值
FLOOR(X) # 向下取整
CEIL(X) #向上取整
RAND(X) # 返回0~1的随机数
PI() # 返回圆周率
MOD(X,Y) # 返回X除以Y的余数

2、字符串函数

CONCAT(s1,s2,....) #拼接字符串
LEFT(s, n) # 返回从字符串s开始的n最左字符
TRIM(s) # 移除掉字串中s的字头或字尾处空格
REPLACE(s,s1,s2) # 用字符串s2替代字符串s重的字符串s1
SUBSTRING(s,n,len) # 截取字符串s中第n个位置开始，长度为len的字符串
MID(s,n,len) # 同SUBSTRING(s,n,len)
REVERSE(s) # 将字符串s的顺序翻转过来

3、时间与日期函数

CURDATE() # 返回当前日期
CURTIME() # 返回当前时间
NOW() # 返回当前日期和时间
MONTH(d) # 返回月份
YEAR(d) # 返回年份
DATEDIFF(date1,date2) # 函数返回两个日期之间的天数

4、条件表达式

IF(表达式,v1,v1) # 如果表达式成立，则执行v1，否则执行v2
CASE WHEN # 用于计算条件列表并返回多个可能结果表达式之一

5、系统信息函数

VERSION() # 返回数据库的版本号
DATABASES() # 返回当前数据库名
USER() # 返回当前用户名

五、局部变量与用户变量

**参考：**https://www.cnblogs.com/Brambling/p/9259375.html

1、局部变量

能用在begin/end语句块中，比如存储过程中的begin/end语句块。其作用域仅限于该语句块

# declare语句专门用于定义局部变量，可以使用default来说明默认值
declare 变量名 INT default 0;

# SET 赋值
SET 变量名=100;

# SELECT INTO 赋值
SELECT 字段 INTO 变量
FROM 表
WHERE 条件表达式

2、用户变量

用户变量不用提前声明，使用时直接@变量名即可，作用域为当前连接

# SET 赋值
SET @变量名 = 100
SET @变量名 := 100

#SELECT 赋值
SELECT @变量名 := 字段
FROM 表
WHERE 条件表达式