前言： 学习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 | 非 |
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 条件表达式
