整型数

类型 占用的存储空间（单位：字节） 无符号数取值范围 有符号数取值范围 含义
TINYINT 1 0 ~ 2⁸-1 -2⁷ ~ 2⁷-1 非常小的整数
SMALLINT 2 0 ~ 2¹⁶-1 -2¹⁵ ~ 2¹⁵-1 小的整数
MEDIUMINT 3 0 ~ 2²⁴-1 -2²³ ~ 2²³-1 中等大小的整数
INT（别名：INTEGER） 4 0 ~ 2³²-1 -2³¹ ~ 2³¹-1 标准的整数
BIGINT 8 0 ~ 2⁶⁴-1 -2⁶³ ~ 2⁶³-1 大整数

浮点数

类型 占用的存储空间（单位：字节） 绝对值最小非0值 绝对值最大非0值 含义
FLOAT 4 ±1.175494351E-38 ±3.402823466E+38 单精度浮点数
DOUBLE 8 ±2.2250738585072014E-308 ±1.7976931348623157E+308 双精度浮点数

规定位数

1
2
FLOAT(M, D)
DOUBLE(M, D)

其中 M(1~255) 代表该小数的总体有效位。 D(0~30) 代表小数位的有效位数。其中M 的值必须不小于 D 。如果不规定，就按默认的最大值进行存储。

虽然可以对浮点数位数进行指定，但是存储的空间都是不变的。

浮点数的结构如下，是凭借指数和尾数来进行存储的(二进制的科学计数法)，对于一些无法直接转换成二进制的数，是不精确的。

定点数

为了解决浮点数不精确的问题，定点数接将小数部分直接存储，使用时就不会出现精度损失的情况。

类型 占用的存储空间（单位：字节） 取值范围
DECIMAL(M, D) 取决于M和D 取决于M和D

定点数的存储结构如下：

其中，从小数点位置起，向前向后每九个十进制位划分为一组进行存储。

其中十进制位数对应存储字节数如下表：

组中包含的十进制位数 占用存储空间大小（单位：字节）
1或2 1
3或4 2
5或6 3
7或8或9 4

所以这个数的存储总字节数为：2 + 4 + 2 = 8 个字节。

无符号数

在原数值类型后加一个单词 UNSIGNED 就构成了无符号数(正数)。

注：无符号的整数范围比有符号数的整数的正数范围大一倍，但是受到浮点数的存储格式的影响，无符号浮点数的整数范围并不会提升。

日期与时间

类型 存储空间要求 取值范围 格式 含义
YEAR 1字节 1901~2155 YYYY 年份值
DATE 3字节 ‘1000-01-01’ ~ ‘9999-12-31’ YYYY-MM-DD 日期值
TIME 3字节 ‘-838:59:59’ ~ ‘838:59:59’ hhh:mm:ss[.uuuuuu] 时间值
DATETIME 8字节 ‘1000-01-01 00:00:00’ ～ ‘9999-12-31 23:59:59’ YYYY-MM-DD hh:mm:ss[.uuuuuu] 日期加时间值
TIMESTAMP 4字节 ‘1970-01-01 00:00:01’ ～ ‘2038-01-19 03:14:07’ YYYY-MM-DD hh:mm:ss[.uuuuuu] 时间戳

MySQL5.6.4 之后开始TIME、DATETIME、TIMESTAMP支持六位小数位秒(毫秒，微秒）。

例：DATETIME(3) 表示精确到微秒， DATETIME(5) 表示精确到 10 微秒，默认 DATETIME 为 DATETIME(0)，即精确到秒。

其中占用的存储空间与定点数占用的空间类似。需要在原本的存储空间上占用额外的空间。

保留的小数秒位数 额外需要的存储空间要
0 0字节
1或2 1字节
3或4 2字节
5或6 3字节

例：DATETIME(3) 需要占用的空间为 3 + 2 个字节。

注：DATETIME 中的时间部分表示的是一天内的时间(00:00:00 ~ 23:59:59)，而 TIME 表示的是一段时间，而且可以表示负值。

TIMESTAMP：时间戳存储的时间，可以随着时区的变化而变化。

字符串

不同编码方式的字符存储长度是不同的。

固定编码：例如 ASCII 采用 1 个字节来编码一个字符，ucs2 采用 2 个字节来编码一个字符。

变长编码：utf8 采用 1~3 个字节来编码一个字符，gb2312 采用 1~2 个字节来编码一个字符。utf8mb4 使用1~4个字节来编码一个字符(MySQL中的 utf8mb4 才是真正意义上的 utf8)。

类型 最大长度 存储空间要求 含义
CHAR(M) M个字符 M×W个字节 固定长度的字符串
VARCHAR(M) M个字符 L+1 或 L+2 个字节 可变长度的字符串
TINYTEXT 2⁸-1 个字节 L+1个字节 非常小型的字符串
TEXT 2¹⁶-1 个字节 L+2 个字节 小型的字符串
MEDIUMTEXT 2²⁴-1 个字节 L+3个字节 中等大小的字符串
LONGTEXT 2³²-1 个字节 L+4个字节 大型的字符串

M 代表该数据类型最多能存储的字符数量。

L 代表实际存储的字符数量。

W 代表在该特定字符集下，编码一个字符最多需要的字节数。

CHAR(M) 中如果占用的字符数不足 M (1~255)个，用空格补足空位，但是这样会浪费存储空间。

为了解决这个问题，我们可以使用 VARCHAR(M) ，可以存储的字符量理论上是 1~65535 ，但是由于 MySQL 中还有一个规定(表中某一行包含的所有列中存储的数据大小总共不得超过65535个字节)，所以能够容纳的字符数量是小于65535的。

VARCHAR 的组成

  • 真正的长度为L的字符串内容。

  • 记录内容占用字节数。

    假设 VARCHAR(M) 类型采用的字符集编码一个字符最多需要 W 个字节，当 M x W < 256 时需要一个字节。当 M × W >= 256 且 M × W < 65536 时，需要两个字节来表示占用的字节数。

各种 TEXT 类型也是变长类型，也是由实际内容和内容占用字节数组成的。但 MySQL 的每行至多 65535 个字节这个规定对于 TEXT 是不奏效的。

二进制类型

类型 字节数 含义
BIT(M) 近似为(M+7)/8 存储M个比特位的值

主要用于存储单个或多个比特位。

M 的取值范围为 1~64，当省略时默认为1。但是 MySQL 是以字节为单位存储数据的，若不足一个字节会补足为整数个字节。

类型 存储空间 含义
BINARY(M) M字节 存储M个字节
VARBINARY(M) L+1 / L+2 字节 存储最多M个字节

与 CHAR(M) 和 VARCHAR(M) 类似，只不过一个是用来存储字符，另一个是用来存储字节的。

另外还有 TINYBLOB 、 BLOB 、 MEDIUMBLOB 、 LONGBLOB 是针对数据量很大的二进制数据提出的，比如图片、音频、压缩文件这类数据。与 TEXT 类似，同样，一个是用来存储字符，另一个是用来存储字节的。

特殊类型

ENUM类型

枚举类型，表示在给定的字符串列表里选择一个，例如 ENUM('男', '女') ；

SET类型

集合类型，表示可以在给定的字符串列表里选择多个，例如 SET('LOL', 'DOTA2', 'CS:GO') ；

特点

EMUN 和 SET 都是两种特殊的字符串类型，在从字符串列表中单选或多选元素的时候会用得到它们。

隐式类型转换

如果数据的类型与上下文不符合，MySQL 会对数据进行转换。

例如：

  1. 把操作数类型转换为适合操作符计算的相应类型。

    1+‘2’ = 3

  2. 将函数参数转换为该函数期望的类型。

    concat(“1”, 2) -> “12”

  3. 存储数据时，把某个值转换为某个列需要的类型。

注：

  1. MySQL 会尽量把值转换为表达式中需要的类型，而不是产生错误。

  2. 在运算时会自动提升操作数的类型。

    100(TINYINT)+100(TINYINT)

    在运算过程中，MySQL 自动将操作数类型提升为 BIGINT，防止运算结果太大导致无法计算。

我们可以使用 CAST 函数对数据进行显式的转换。

内置函数

文本处理函数

名称 调用示例 示例结果 描述
LEFT LEFT('abc123', 3) abc 给定字符串从左边取指定长度的子串
RIGHT RIGHT('abc123', 3) 123 给定字符串从右边取指定长度的子串
LENGTH LENGTH('abc') 3 给定字符串的长度
LOWER LOWER('ABC') abc 给定字符串的小写格式
UPPER UPPER('abc') ABC 给定字符串的大写格式
LTRIM LTRIM(' abc') abc 给定字符串左边空格去除后的格式
RTRIM RTRIM('abc ') abc 给定字符串右边空格去除后的格式
SUBSTRING SUBSTRING('abc123', 2, 3) bc1 给定字符串从指定位置截取指定长度的子串
CONCAT CONCAT('abc', '123', 'xyz') abc123xyz 将给定的各个字符串拼接成一个新字符串

日期时间处理函数

名称 调用示例 示例结果 描述
NOW NOW() 2019-08-16 17:10:43 返回当前日期和时间
CURDATE CURDATE() 2019-08-16 返回当前日期
CURTIME CURTIME() 17:10:43 返回当前时间
DATE DATE('2019-08-16 17:10:43') 2019-08-16 将给定日期和时间值的日期提取出来
DATE_ADD DATE_ADD('2019-08-16 17:10:43', INTERVAL 2 DAY) 2019-08-18 17:10:43 将给定的日期和时间值添加指定的时间间隔
DATE_SUB DATE_SUB('2019-08-16 17:10:43', INTERVAL 2 DAY) 2019-08-14 17:10:43 将给定的日期和时间值减去指定的时间间隔
DATEDIFF DATEDIFF('2019-08-16', '2019-08-17'); -1 返回两个日期之间的天数（负数代表前一个参数代表的日期比较小）
DATE_FORMAT DATE_FORMAT(NOW(),'%m-%d-%Y') 08-16-2019 用给定的格式显示日期和时间

在 DATE_ADD 或 DATE_SUB 时，需要注意增减的单位。

时间单位 描述
MICROSECOND 毫秒
SECOND 秒
MINUTE 分钟
HOUR 小时
DAY 天
WEEK 星期
MONTH 月
QUARTER 季度
YEAR 年

DATE_FORMAT 需要记住占位符的含义：

格式符 描述
%b 简写的月份名称（Jan、Feb、…、Dec)
%D 带有英文后缀的月份中的日期（0th、1st、2nd、…、31st)）
%d 数字格式的月份中的日期(00、01、02、…、31)
%f 微秒（000000-999999）
%H 二十四小时制的小时 (00-23)
%h 十二小时制的小时 (01-12)
%i 数值格式的分钟(00-59)
%M 月份名（January、February、…、December）
%m 数值形式的月份(00-12)
%p 上午或下午（AM代表上午、PM代表下午）
%S 秒(00-59)
%s 秒(00-59)
%W 星期名（Sunday、Monday、…、Saturday）
%w 周内第几天 （0=星期日、1=星期一、 6=星期六）
%Y 4位数字形式的年（例如2019）
%y 2位数字形式的年（例如19）

数值操作

名称 调用示例 示例结果 描述
ABS ABS(-1) 1 取绝对值
Pi PI() 3.141593 返回圆周率
COS COS(PI()) -1 返回一个角度的余弦
EXP EXP(1) 2.718281828459045 返回e的指定次方
MOD MOD(5,2) 1 返回除法的余数
RAND RAND() 0.7537623539136372 返回一个随机数
SIN SIN(PI()/2) 1 返回一个角度的正弦
SQRT SQRT(9) 3 返回一个数的平方根
TAN TAN(0) 0 返回一个角度的正切

聚集函数

函数名 描述
COUNT 返回某列的行数
MAX 返回某列的最大值
MIN 返回某列的最小值
SUM 返回某列值之和
AVG 返回某列的平均值

聚集函数，一般是用来统计信息的。并且聚集函数统计的都是符合条件语句查询的。

COUNT 有两种用法：

  • COUNT(*)：对表中行的数目进行计数，不管列的值是不是 NULL。
  • COUNT(列名)：对特定的列进行计数，会忽略掉该列为 NULL 的行。
  • COUNT(1)：每一行记一个数字 1 , 然后统计 1 的个数。

使用 DISTINCT 过滤重复数据。

SELECT (DISTINCT <col_name>) FROM <table_name>;

数据库操作

1
2
3
4
5
6
SHOW DATABASES;							-- 显示所有数据库。
CREATE DATABASE <db_name>; 				-- 创建数据库，若数据库已存在就会报错。
CREATE DATABASE <db_name> IF NOT EXISTS;-- 一般我们会使用这个语句，防止报错。
USE <db_name>; 							-- 切换数据库。
DROP DATABASE <db_name>;				-- 删除数据库，若数据库不存在则报错。
DROP DATABASE <db_name> IF EXISTS;		-- 一般我们会用这个语句防止报错。

表操作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- 可以使用 db_name.table_name 操作其他库中的表。
SHOW TABLES;	-- 显示所有表
-- 创建表
CREATE TABLE <table_name> (
	col_name1 col_type [col_attrs],
	……
	col_namen col_type [col_attrs]
);
-- 创建表时添加注释
CREATE TABLE <table_name> (
	……
) COMMENT 'SOME_COMMITS';
-- 建表时，如果表已经存在，会报错，可以使用 IF NOT EXISTS 判断
CREATE TABLE IF NOT EXISTS <table_name> (
   ……
);
DROP TABLE [IF EXISTS] <table_name>;	-- 删除表
-- 查看表结构(以下几种方式等效)
DESCRIBE <table_name>;
DESC <table_name>;
EXPLAIN <table_name>;
SHOW COLUMNS FROM <table_name>;
SHOW FIELDS FROM <table_name>;
-- 查看建表信息
SHOW CREATE TABLE <table_name>;
-- 对表内结构的修改一般使用 ALTER，并且多条语句可以合并成一条语句，从而提升效率。
-- 修改表名
ALTER TABLE [db1.]table_1 RENAME TO [db2.]table_2;
RENAME TABLE [db1.]table_1 TO [db2.]table_2, [db3.]table_3 TO [db4.]table_4, ……; 
-- 增加列，FIRST 和 AFTER 指定增加列的位置，若不指定，默认在最后添加。
ALTER TABLE <tablr_name> ADD COLUMN <col_name> <col_type> [col_attrs] [{FIRST | AFTER <col_name>}];
-- 删除列
ALTER TABLE <table_nmae> DROP COLUMN <col_name>;
-- 修改列信息
ALTER TABLE <table_nmae> MODIFY <col_name> <col_type> [<col_attrs>];
ALTER TABLE <table_name> CHANGE <old_col_name> <new_col_name> <col_type> [<col_attrs>];
-- 修改列的排列
ALTER TABLE <table_name> MODIFY <col_name> <col_type> <col_attrs> { FIRST | AFTER <col_name> };

-- 列的属性，一般会规定列的属性，对列中的数据进行规约，列的属性会有冲突，设定时需要避免
-- 列的默认值
<col_name> <col_type> DEAFULT <default_value>;
-- 列值非空
<col_name> <col_type> NOT NULL;
-- UNIQUE 属性，UNIQUE 属性可以为 NULL，且 NULL 值可以多次出现在列中
<col_name> <col_type> UNIQUE;	-- 列声明
UNIQUE [constraint_name] (列名1, 列名2,……);	-- 表声明
UNIQUE KEY [constraint_name] (列名1, 列名2,……);
-- 主键，主键值默认有 NOT NULL 和 UNIQUE 属性
<col_name> <col_type> PRIMARY KEY;	-- 列声明，这种方法能且仅能规定一个主键
PRIMARY KEY(col_name1, col_name2, ……);	-- 表声明，这种方法可以规定多个主键
-- 外键
CONSISTENT [foreign_key_name] FOREIGN KEY(col_name1, col_name2, ……) REFERENCES father_table(father_col1,father_col2, ……);
-- 列值自增
	-- 一个表中最多有一个具有AUTO_INCREMENT属性的列
	-- 具有AUTO_INCREMENT属性的列必须建立索引。
	-- 不能设定默认值
	-- 一般用来生成一个唯一的主键值
<col_name> <col_type> AUTO_INCREMENT;
-- 列注释
<col_name> <col_type> [col_attrs] COMMENT "comments";
-- ZEROFILL， 在小于规定显示宽度的具有 USINGNED ZEROFILL 属性的整数列上 才会显示补全的效果，并不会影响存储的具体值。若建表时，只声明了ZEROFILL属性，MySQL会为该列自动生成UNSIGNED属性。
	-- 各种 INT 有默认的显示宽度，如果列声明了 UNSIGNED 属性，默认宽度减一。
		TINYINT: 4
		INT: 11
<col_name> INT(10) UNSIGNED ZEROFILL;	-- 其中的10就是显示宽度

增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
-- 查询(*代表所有列)
SELECT <col_name1 as alias_name1,col_name2 as alias_name2,……> FROM <table_name>;
	-- 去重
	SELECT DISTINCT <col_names> FROM <table_name>;
	-- 限制，如果起始行大于结果集中的总个数，那么结果集为空。起始行默认为0；
	SELECT <col_names> FROM <table_name> LIMIT [<start_col>.]<select_col_number>;
	-- 排序，默认为升序，可以规定按多个列进行排序，会先按在前的列排序，在按照在后的列排序。
	SELECT <col_names1> FROM <table_name> ORDER BY <col_names2> [ ASC | DESC ];
	-- ORDER 语句必须在 LIMIT 语句前。
    -- 条件语句 WHERE, WHERE 语句中不能出现聚合函数。
        -- 比较操作符
            =, !=/(<>), >, <, <=, >=, BETWEEN, NOT BETWEEN
            -- 匹配列表元素
                IN/NOT IN (ele1, ele2, ……);
            -- 匹配NULL
                IS NULL, IS NOT NULL
            -- 模糊查询
                LIKE , NOT LIKE
                -- %: 任意一个字符串
                -- _：任意一个字符
                -- 转义字符: \% -> '%', \_ -> '_'
    	-- 其他操作符
    		+, -, *, /, DIV(取商), %, -(负号)
    	-- 逻辑操作符
     		AND, OR, XOR, ()	-- 优先级	() > AND > OR
     -- 分组查询，将非分组查询的数据放到分组查询的结果集中会报错，因为把非分组列放到查询列表中会引起争议，导致结果不确定。
     -- 关闭 ONLY_FULL_GROUP_BY 这个模式后就可以将将非分组查询的数据放到分组查询的结果集中。
     -- 分组查询中，会根据 WHERE 语句先过滤数据，再通过 GROUP 语句进行分组。
     		GROUP BY <col_name>;
     		-- 分组条件，就是针对分组的条件
     			1. 作用于分组列
     				GROUP BY <col_name> HAVING <col_name>="???";
     			2. 作用于分组的聚合函数
     				GROUP BY <col_name1> HAVING MAX(<col_name2>)>?;
     		-- 分组排序
     			GROUP BY <col_name1> ORDER BY(MAX(<col_name2>)>) [{ ASC | DESC }];
     		-- 嵌套分组，将一个大的分组，再将其按照其中的字段分为更详细的分组。只需要在 GROUP BY 子句中把各个分组列依次写上，用逗号','分隔开就好了，在嵌套分组中，聚合函数作用于最细粒度的分组。
     			GROUP BY <col_name_wide>, <col_name_detail>;
     		-- 注：
     			1. 分组列中含有 NULL 值，那么 NULL 也会作为一个独立的分组存在。
     			2. 嵌套分组，聚集函数将作用在最后的那个分组列上。
     			3. 如果查询语句中存在 WHERE 子句和 ORDER BY 子句，那么 GROUP BY 子句必须出现在 WHERE 子句之后， ORDER BY 子句之前。
     			4. 非分组列不能单独出现在检索列表中(可以被放到聚集函数中)。
     			5. GROUP BY子句后也可以跟随表达式(但不能是聚集函数)。MySQL会根据这个表达式的值来对记录进行分组，并且查询列表中的表达式和GROUP BY子句中的表达式必须完全一样。
					SELECT CONCAT(<col_name1>, "1"), COUNT(*) FROM <table_name> GROUP BY CONCAT(<col_name1>, "1");
				6. WHERE 语句在分组前对数据过滤， HAVING 语句在分组后对分组数据进行过滤。
			-- 语句顺序
				SELECT [DISTINCT] <col_names>
                [FROM table_name]
                [WHERE <conditions>]
                [GROUP BY <col_names> ]
                [HAVING <conditions>]
                [ORDER BY <col_name>]
                [LIMIT start_line, lines]
	-- 子查询,子查询语句必须用小括号。
		-- 标量子查询
			SELECT <col_names> FROM <table_name> WHERE <col_name = > (<a_select_statement_to_get_a_value>);
		-- 列子查询
			SELECT <col_names> FROM <table_name> WHERE <col_name IN > (<a_select_statement_to_get_a_col_values>);
		-- 行子查询
			SELECT <col_names1> FROM <table_name> WHERE <col_names2 = >(<a_select_statement_to_get_a_row_of_col_name2>)
		-- 表子查询
			SELECT <col_names1> FROM <table_name> WHERE <col_names2 IN >(<a_select_statement_to_get_rows_of_col_name2>)
		-- EXIST 子查询, 根据 WHERE 后的查询结果集是否存在判断是否执行前面的查询语句。
			SELECT <col_names> FROM <table_name> WHERE [NOT] EXIST (<a_select_statement_to_get_some_value>);
		-- 不相关子查询，相关子查询的区别
			-- 不相关子查询，子查询语句可以独立运行并获取一个值。
			-- 相关子查询语句，子查询语句需要依赖外层查询语句查询的值。
		-- 同一个表的子查询
			-- 在 WHERE 条件语句中不可以出现聚合函数， 但是我们可以通过子查询先获取聚合函数的值。
	-- 连接查询(本质是对几个表的记录进行笛卡尔积)
		SELECT  [<table_name1>.]<col_names>, [<table_name2>].<col_names> …… FROM <table_name1>,<table_name2>;	-- 如果两个表中有字段名重复的字段，重复字段需要指定表名。我们可以通过 WHERE 子句过滤出我们的想要的结果。
		-- 内连接： 驱动表中的记录若在被驱动表中找不到相应的记录，该记录不会加入最后的结果集。
			SELECT <col_names> FROM <table_name1> [ INNER | CROSS] JOIN <table_name2> ON <conditions> [WHERE conditions]
        -- 外连接： 驱动表中的记即使在被驱动表中未找到相应的记录，该记录仍会加入最后的结果集。
        	-- 左外连接： 左侧为驱动表。
        	-- 右外连接： 右侧为驱动表。
        		SELECT <col_names> FROM <table_name1> { LEFT | RIGHT } [OUTER] JOIN <table_name2> ON <conditions> [WHERE conditions]
        -- 过滤子句
        	-- WHERE： 就是常见的 WHERE 子句，凡是不符合WHERE子句中的过滤条件的记录都不会被加入最后的结果集。
        	-- ON： 对于外连接，若在被驱动表中没有满足 ON 子句中的过滤条件的记录，该记录仍会被加入结果集中，对应的被驱动表中的记录使用 NULL 进行填充。但是对于内连接 WHERE 和 ON 是等价的。
        -- 多表连接(不管是多少个表连接，本质上就是各个表的记录在符合过滤条件下的自由组合)
        	Ex. 
        		SELECT t1.col1, t2..col2, t3.col3 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.col1 = t2.col2 AND t2.col2 = t3.col3;·
        		或
        		SELECT t1.col1, t2..col2, t3.col3 FROM t1 INNER JOIN t2 ON t2.col2 = t1.col1 INNER JOIN t3 ON t2.col2 = t2.col2；
        -- 自连接，一个表中的一个字段和和这个表中的另外一个字段相关时，需要用到自连接。对两个自连表可以使用别名来区分。
        -- 连接查询可以使用子查询解决。(MySQL 内部有时会使用连接来处理子查询)
    -- 合并查询(UNION)： 将多个结果集联合成一个结果集
    	(SELECT <t1_col_1>, <t1_col_2> FROM t1 <where_statement>) UNION [ALL] (SELECT <t2_col_1>, <t2_col_2> FROM t2 <where_statement>) ORDER BY <col_of_result> [ASC | DESC] LIMIT <col_number>;
    	-- 使用UNION连接起来的各个查询语句的查询列表中位置相同的表达式的类型(结果集数据类型)应该是相同的。不匹配的话 MySQL 会自动进行数据类型转换，但是并不建议这么做。
    	-- 一般结果集中显示的列名以第一个语句的结果集的列名为准。
    	-- UNION 会自动对两个结果集中重复的元素执行去重操作，如果不想去重，可以使用 UNION ALL。
    	-- 最后可以对结果集进行排序，但是对其中一个结果集的排序是无意义的，MySQL 规定： 组合查询并不保证最后汇总起来的大结果集中的顺序是按照各个小查询的结果集中的顺序排序的。
-- 插入
	 INSERT INTO <table_name> VALUES(col1_val1,col2_val1...), (col1_val2,col2_val2...)...;	-- 这种方式需要将每个列的值都写上，不能缺少任何一个列的值。
	 INSERT INTO <table_name>(col_names) VALUES(col_vals1), (col_vals2)...;	-- col_vals 中的值与col_names 中的值是一一对应的。但是只有满足以下两种条件的条件的列可以省略：
	 	-- 1. 该列允许 NULL 值。
	 	-- 2. 该列有默认值或是自增列。
	 -- 在规定了 UNIQUE 的列中，若插入的数据已存在，则MySQL就会报错。
	 	-- 我们可以使用 INSERT IGNORE 语句来进行插入，已经存在的行，不会被插入表中。
	 	-- 也可以使用 INSERT ON DUPLICATE KEY UPDATE 对重复记录进行更新。
	 	-- 这两条语句在插入多条记录时，如果不希望有报错信息很有用。
-- 删除数据
	DELETE FROM <table_name> [where_statement] [order_statement] [limit_statement];	-- 可以通过 LIMIT 和 ORDER 语句进行限制。
-- 更新数据
	UPDATE <table_name> SET <col_name1>="col_val1", <col_name2>="col_val2"... [where_statement] [order_statement] [limit_statement];	-- 也可以通过 LIMIT 和 ORDER 语句进行限制。

视图

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 通俗的理解，视图就是查询语句的别名。
-- 创建视图
	CREATE VIEW <view_name> AS <select_statement>;
	CREATE VIEW <view_name>(view_col1, view_col2...) AS SELECT <tb_col1>, <tb_col2>... FROM tb;	-- 这种方法需要注意视图中的字段应该与查询表达式一一对应。
-- 查询视图
	-- 视图就是一个虚拟表，对视图的所有操作都会被映射到底层的表上。所有的视图并非维护在存储器中，查询视图时会转换为对底层表的查询语句，然后再执行。
	-- 我们完全可以将视图当做表使用。并且视图可以简化 SQL 语句的书写，对视图的操作更加直观，不必考虑底层实现细节。
	SELECT * FROM <view_name>;
-- 查看视图
	SHOW TABLES;	-- 查看数据库中的视图。因为视图是一张虚拟表，不能与其他表名相同。
	SHOW CREATE VIEW <view_name>;	-- 查看视图创建信息，与查看表结构类似。
-- 操作视图(不建议直接操作)
	-- 有些视图不光是可以查询的，还是可以更新的。对其进行 INSERT、 DELETE、 UPDATE 就是对底层表的增、删、改。
	-- 含有以下几种语句的视图是不可以进行增删改的。
		-- 1. 聚集函数
		-- 2. DISTINCT
		-- 3. GROUP BY
		-- 4. HAVING
		-- 5. UNION
		-- 6. 子查询
		-- 7. 连接查询
-- 删除视图
	DROP VIEW <view_name>;

变量与语句结束分隔符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 变量， MySQL 规定在自定义变量前必须加一个 @
	-- 查看变量
		SELECT @var;
	-- 变量赋值
		SET @var = "str";	-- 可以将常量赋值给变量，当然也可以将变量的值赋值给变量。
		SET @var = (select_statement);	-- 可以将查询结果的值(结果仅有一个值)赋值给变量。
		<select_statement> into @var;	-- 与上面的语句效果相同
		SELECT <col_name1>,<col_name2> FROM <table_name> LIMIT 1 INTO @var1, @var2;	-- 将多个结果赋值给多个变量。
-- 语句分隔符：表示一个语句的的结束，当敲下回车时，会检测是否含有';'、'\g'或'\G'，如果存在，会将内容发送至服务端。但是这样我们在同时执行多个语句时，就需要在一行中书写多条语句。为此，我们可以使用语句结束分隔符，使客户端在遇到这几个符号并回车时，不会直接发送到服务器，只有在遇到我们定义的语句结束分隔符时才会发送所有的语句。
	delimiter EOF	-- 定义与语句结束符
	SELECT * FROM t1 LIMIT 1;
    SELECT * FROM t2 LIMIT 1;
    SELECT * FROM t3 LIMIT 1;
    EOF	-- 遇到EOF后，将上面三条语句同时发送给服务器。

存储例程

存储函数与存储过程

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
-- 存储程序(Stored Programe)：用来封装一些语句，然后给用户提供一种简单的方式来调用这个存储程序，从而间接的执行这些语句。可以分为以下几种类型：
	1. 存储例程(Stored Routine)
		(1) 存储函数(Stored Function)
		(2) 存储过程(Stored Procedure)
	2. 触发器(Trigger)
	3. 事件(Event)
-- 存储例程
	-- 存储函数：本质就是函数
		-- 创建存储函数
			CREATE FUNCTION <function_name>)(param_name param_type, ...)
			RETURN <return_value_type>
			BEGIN
				<functoion_body>
			END
			-- 参数
				-- 函数参数不可以指定默认值，我们在调用函数的时候，必须显式的指定所有的参数，并且参数类型也一定要匹配
			-- 函数体
				-- 变量：在函数体中使用使用局部变量需要先声明。但是函数体中的局部变量不是用@标识。同样，也可以使用自定义变量，使用方法和之前的自定义变量一致，这些自定义变量可以在函数体外使用。
					DECLARE var1, var2... <data_type> [DEFAULT <val>]; --局部变量
				-- 判断语句
					IF <bool_statement> THEN
						statements...
					[ELSEIF <bool_statement> THEN
						statements...]
					...
					[ELSE
                     	statemants]
                     END IF;
                 -- 循环语句
                 	-- WHIEL 语句
                        WHILE <bool_statements> DO
                            statements...
                        END WHIEL;
                    -- REPEAT 语句(相当于其他语言中的do...while语句，至少会执行一次循环语句)
                    	REPEAT
                    		statements...
                    	UNTIL <bool_statement> END REPEAT;
                   	-- LOOP 语句
                   		LOOP
                   			statements...
                   		END LOOP;
                   		-- LOOP 没有终止条件，我们可以在循环体中增减判断条件，满足某种条件是直接调用 RETURN 语句结束循环。
                   		-- 如果仅仅想结束循环，我们可以使用 LEAVE，但是使用前。需要在 LOOP 前放置一个标记。
                   		-- flag：相当于为这个循环打了一个名叫flag的标记，然后在对应的END LOOP语句后边也把这个标记名flag给写上了。在存储函数的函数体中使用LEAVE flag语句来结束flag这个标记所代表的循环。
                   		-- 其实也可以在BEGIN ... END、REPEAT和WHILE这些语句上打标记，标记主要是为了在这些语句发生嵌套时可以跳到指定的语句中使用的。
                   			flag:LOOP
                   				IF <bool_statement> THEN
                   					LEAVE flag;
                   				END IF;
                   				statements...
                   			END LOOP;
		-- 调用存储函数
			SELECT <function_name>(params...);
		-- 查看存储函数
			SHOW FUNCTION STATUS [like_statement];	-- 查看已定义定义的存储函数
			SHOW CREATE FUNCTION <function_name>;	-- 查看函数的创建语句
		-- 删除函数
			DROP FUNCTION <function_name>;
	-- 存储过程：相较于存储函数会返回一个值，存储过程更倾向于单纯的执行这些语句，所以不需要声明一个返回值。存储过程更偏向于执行某些过程，并不能用在表达式中。并且存储过程中所有 SELECT 语句产生的结果集将会被显示到客户端中。
		-- 创建存储过程
			CREATE PROCEDURE <procedure_name>([[IN | OUT | INOUT]param_name param_type,...])
			BEGIN
				statements...
			END;
			-- 参数：
				-- IN，实参可以不是变量，用于向存储过程传递数据，如果参数在存储过程中被修改，调用者不可见。存储函数仅支持 IN 函数。
				-- OUT，实参必须是变量，用于将存储过程中的数据赋值给 OUT 参数，存储过程结束后，调用者可以访问到 OUT 参数。如果在存储过程未执行过程中还未对参数赋值时，读取参数的值，值为 NULL
				-- INOUT，实参必须是变量，结合了 IN 与 OUT 的特点既可以向存储过程中传递数，也可以存放存储过程中产生的数据，方便调用者使用。
		-- 调用存储过程
			CALL <procedure_name>([param_name param_type,...]);
		-- 查看存储过程
            SHOW PROCEDURE STATUS [like_statement];	-- 查看定义的存储过程
            SHOW CREATE PROCEDURE <procedure_name>;	-- 查看存储过程创建语句
		-- 删除存储过程
			DROP PROCEDURE <procedure_name>;
	-- 存储函数与存储过程差别
		-- 存储函数需要显式使用 RETURNS 表明返回值类型。函数体中也必须使用 RETURN 语句显式指定返回值。存储过程可以通过 IN、INOUT 来返回多个结果。
		-- 存储函数仅支持 IN 类型的参数。
		-- 存储函数执行过程中的 SELECT 语句的结果集不会显示到客户端，但是存储过程会。
		-- 存储函数可以在表达式中调用，但是存储过程只能使用 CALL 语句调用。
	-- 触发器：在一些操作过后，MySQL 会自动调用一些语句进行自动化处理。
		-- 创建触发器
			CREATE TRIGGER <triggrt_name>
			{ BEFORE | AFTER }	-- 在具体语句之前还是之后
			{ INSERT | DELETE | UPDATE }	-- MySQL仅支持这三种触发器。
			ON <table_name>
			FOR EACH ROW	-- 代指要操作的记录，INSERT 中代指要插入的记录，DELETE 和 UPDATE 中代指符合条件的记录。
			BEGIN
				statements
			END
			-- 通过 NEW/OLD 读取操作的记录
				-- INSERT 中 NEW 有效，代指准备插入的记录。
				-- DELETE 中 OLD 有效，代指准备删除的记录。
				-- UPDATE 中 NEW/OLD 都有效，NEW 代指要更新的记录，OLD 代指更新前的记录。
			-- 注： 
				-- 触发器中不能有输出结果集的语句。
				-- NEW代表记录的列的值可以被更改，OLD代表记录的列的值无法更改。
				-- 在 AFTER 语句中，因为数据已经被插入了，所以无法对 NEW 记录中的数据赋值。
				-- 如果 BEFORE 触发器执行过程中报错，对应的语句也就无法执行。如果对应的语句执行时报错，对应的 AFTER 语句将无法执行。
		-- 查看触发器
			SHOW TRIGGERS;
			SHOW CREATE TRIGGER <trigger_name>;
		-- 删除触发器
			DROP TRIGGER <trigger_name>;
	-- 事件：每隔一段时间自动执行的任务。但是默认情况下，MySQL 并不会自动执行事件，需要我们手动开启 SET GLOBAL event_scheduler = ON; ，并且事件在执行完之后会自动删除，我们可以设置系统参数进行保留。
		-- 创建事件
			CREATE EVENT <event_name>
            ON SCHEDULE
            {
                AT <a_concrete_time>| 
                EVERY <time_interval> [STARTS datetime][END datetime]
            }
            DO
            BEGIN
                statements;
            END
            -- time_interval 可以设置为 EVERY 1 YEAR， 有HOUR、YEAR、QUARTER、MONTH、DAY、HOUR、 MINUTE、WEEK、SECOND、YEAR_MONTH、DAY_HOUR、DAY_MINUTE、DAY_SECOND、HOUR_MINUTE、HOUR_SECOND、MINUTE_SECOND等时间间隔单位。
        -- 查看事件
        	SHOW EVENTS;
        	SHOW CREATE EVENT <event_name>;
        -- 删除事件
        	DROP EVENT <event_name>;

游标

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- 游标：在存储过程中，用来标记结果集中我们正在访问的一条记录的位置。
	-- 我们可以根据游标的位置取出对应的记录，取出后，游标会自动移动至下一条记录。
	-- 游标的使用步骤
		-- 1. 创建游标
		-- 2. 打开游标
		-- 3. 通过游标访问记录
		-- 4. 关闭游标
	-- 创建游标，局部变量的声明要放在游标之前
		DECLARE <cursor_name> CURSOR FOR <select_statement>;
	-- 打开和关闭游标
		{ OPEN | CLOSE } <cursor_name>;	-- 关闭游标将会释放相关资源，使用完就可以将其关闭，如果不关闭，在 END 语句结束后也会释放资源。
	-- 使用游标获取记录
		FETCH <cousor_name> INTO var1, var2, var3;	-- 获取记录中的值，并将其按列的顺序赋值给变量，变量的类型和数量和列应该相同
	CREATE PROCEDURE proc1()
    BEGIN
        DECLARE <vars var_type>;
        DECLARE <cursor_name> CURSOR FOR <select_statement>;
        OPEN <cursor_name>;
        FETCH <cursor_name> INTO <vars>;	-- 游标会自动移动到下一个
        SELECT <vars>;
    END;
    -- 遍历策略
    	-- 先 count() 查询语句结果集中的记录数量。按照数量进行 WHIEL 循环。
    	-- 使用 FETCH 结束触发事件。
    		DECLARE CONTINUE HANDLER FOR NOT FOUND 
    		[BEGIN]
    			<handle_statement> -- 可以是一条或多条语句
    		[END]