《MySQL 必知必会》是一本适合入门的书籍,非常适合CURD boy

1 基础知识

1 主键

  • 唯一标识表中每行的这个列(这组列)称为主键。
  • 应该总是定义主键,虽然并不总是需要主键。
  • 任意两行都不具有相同的主键值。
  • 每一行都必须具有一个主键值,不可为 NULL。

2 常用命令

  • SHOW DATABASES;
  • SHOW TABLES;
  • SHOW COLUMNS FROM table;
  • SHOW STATUS;
  • SHOW CREATE DATABASE database;
  • SHOW CREATE TABLE table;

2 检索数据

除非确实需要绝大部分列或全部列,否则最好不使用通配符* 来获取所有的列。检索不需要的列通常会降低搜索和应用程序的性能。

1 DISTINCT 和 GROUP BY

SELECT DISTINCT `role_id` FROM user;
SELECT `role_id` FROM user GROUP BY `role_id`;
  • 二者都可以达到去重的效果。
  • DISTINCT 把列中的全部内容存储到内存中,可以理解为一个 hash,最后的到 hash 中的 key 就可以得到结果。比较耗内存。
  • GROUP BY 先将列排序,然后去重。排序比较耗时间。

2 LIMIT

从 0 开始算

SELECT `role_id` FROM user LIMIT 5 OFFSET 3;
SELECT `role_id` FROM user LIMIT 3, 5;

以上两条命令都表示从 3 开始的 5 行。

3 排序数据

默认为升序 ASC

SELECT `role_id`, `account_id` FROM user_pay ORDER BY `account_id`, `role_id` DESC LIMIT 20;

以上语句中, account_id 默认为升序排列,也可以写上 ASC。

4 过滤数据

1 IN 操作符

SELECT * FROM user WHERE role_id IN (1000001, 1000002);

2 NOT 操作符

SELECT * FROM user WHERE role_id IN (1000001, 1000002) LIMIT 10;

3 LIKE 操作符

SELECT * FROM user WHERE name LIKE 't%';
SELECT * FROM user WHERE name LIKE 't_';    -- t1, t2
SELECT * FROM user WHERE name LIKE 't__';   -- t123, t34
  • 以上两句都是模糊匹配用户名以 t 开头。
  • %: 匹配任意 0 个或者多个字符。
  • _: 一个_匹配 1 个任意字符,且必须有一个。

5 正则表达式搜索

MYSQL 仅支持多数正则表达式实现的一个很小的子集。

1 基本字符匹配

SELECT name FROM user WHERE name REGEXP 't';    -- t, t1, t2
SELECT name FROM user WHERE name REGEXP 't.';   -- t1, t2
  • .表示匹配任意一个字符。
  • LIKE 和 REGEXP 区别:
  • LIKE 要求整个列匹配(使用通配符除外), REGEXP 只要列中某个片段匹配即可。
  • 假设有用户名为 s123。则以下例子中, LIKE 没有得到结果。
SELECT name FROM user WHERE name LIKE 's1';     -- 没有结果
SELECT name FROM user WHERE name REGEXP 's1';   -- s123

2 OR 匹配

SELECT name FROM user WHERE name REGEXP 's1|s2' ORDER BY name; -- s123, s2, s234
使用 功能上类似于 SELECT 中的 OR 语句。多个 OR 语句可以使用正则表达式替代,更简洁。

3 匹配几个字符之一

SELECT name FROM user WHERE name REGEXP 's[1238]' ORDER BY name;  -- s123, s2, s234, s89

相当于

SELECT name FROM user WHERE name REGEXP 's1|s2|s3|s8' ORDER BY name;

也可以添加^,来匹配除指定以外的内容

SELECT name FROM user WHERE name REGEXP 's[^1238]' ORDER BY name;  -- s4, s5

4 匹配范围

SELECT name FROM user WHERE name REGEXP 's[1-8]' ORDER BY name;  -- s123, s2, s89..

SELECT name FROM user WHERE name REGEXP '[a-z][1-8]' ORDER BY name;  -- a1, b2, c3

5 匹配特殊字符

为了匹配特殊字符,必须用\\为前导。

SELECT name FROM user WHERE name REGEXP 's\\-' ORDER BY name;  -- s-5

\\也用来引用具有特殊含义的字符

特殊字符 含义
\\f 换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表

多数正则表达式使用\转义特殊字符,以便能使用这些字符本身。但 MySQL 要求用\\。 MySQL 解释一个,正则表达式解释另外一个。

6 匹配字符类

为了方便工作,可以使用预定义的字符集

说明
[:alnum:] 任意字母和数字 ([a-zA-Z0-9])
[:alpha:] 任意字符 ([a-zA-Z])
[:blank:] 空格和指标 (\\t)
[:cntrl:] ASCII 控制字符 (ASCII 0~31, 127)
[:digit:] 任意数字 ([0-9])
[:graph:] 与[:print:]相同,但不包括空格
[:lower:] 任意小写字母 ([a-z])
[:print:] 任意可打印的数字
[:punct:] 同时不在[:alnum:][:cntrl:]中的任意字符
[:space:] 包括空格在内的任意空白字符 ([\\t\\n\\r\\t\\v])
[:upper:] 任意大写字母 ([A-Z])
[:xdigit:] 任意十六进制数字 ([a-fA-F0-9])

示例:

SELECT name FROM user WHERE name REGEXP '[[:alpha:]]1' ORDER BY name; -- h1, m1, s123

7 匹配多个实例

字符 说明
* 0 个或多个匹配
+ 1 个或多个匹配 ({1, })
? 0 个或 1 个匹配 ({0, 1})
{n} 指定数目的匹配
{n, } 不少于指定数目的匹配
{n, m} 匹配数目范围, m 不超过 255
SELECT name FROM user WHERE name REGEXP '[[:digit:]]{4}' ORDER BY name; -- s4444, 21111

8 定位符

元字符 说明
^ 文本的开始
$ 文本的结尾
[[:<:]] 词的开始
[[:>:]] 词的结尾
  • 示例 1 假设要找到以字母开头的用户名
SELECT  name FROM user WHERE name REGEXP '[a-zA-Z]';

以上语句将会在文本任意位置进行查找匹配,并不符合以字母开头这依规定. 这里可以使用^

SELECT  name FROM user WHERE name REGEXP '^[a-zA-Z]';

6 创建计算字段

存储在表中的数据不一定是应用程序所需要的。我们可以直接从数据库中检索出转换,计算或格式化过的数据。而不是检索出原始数据然后在应用程序中重新格式化。

1 拼接 CONCAT

表中含有 role_id, name 字段,应用程序需要这样的格式 role_name(role_id)

SELECT CONCAT(name, '(', role_id, ')') FROM user LIMIT 1;   -- s123 (1000001)
SELECT CONCAT(RTRIM(name), '(', role_id, ')') FROM user LIMIT 1;   -- s123(1000001)
  • RTRIM()函数去掉了值右边的所有空格。其余有 LTRIM(), TRIM()

2 别名 AS

拼接处的结果没有名字,应用程序没法引用。可以使用别名解决这个问题。

SELECT CONCAT(name, '(', role_id, ')') AS info FROM user LIMIT 1;

这样,应用程序就可以使用 info 这个列,就像它本来就存在于表中一样。

3 执行算术计算

假设用户充值了 money(元),每元可以换成 10 个代币,这里通过计算直接得出获得的总代币。

SELECT role_id, money, money * 10 AS total_gold FROM user LIMIT 10;

7 使用数据处理函数

1 字符串函数

函数 说明 示例 结果
CHAR_LENGTH(S) 返回字符串 s 字符数 SELECT CHAR_LENGTH(‘abc 你好’); 6
LENGTH(S) 返回字符串 s 的长度 SELECT LENGTH(‘abc 你好’); 10
CONCAT(S1,S2,…) 合并为一个字符串 SELECT CONCAT(‘hello’, ‘ abc’); hello abc
CONCAT_WS(x, s1, s2,…) 同 CONCAT,但会加上 x SELECT CONCAT_WS(‘+’, ‘1’, ‘2’, ‘3’); 1+2+3
INSERT(s1, x, length, s2) 将字符串 s2 替换 s1 的 x 位置开始长度为 length 的字符串 SELECT INSERT(‘abcdefg’, 2, 3, ‘123’); a123efg
UPPER(s) 将字符串 s 的所有字母变成大写字母 SELECT UPPER(‘abcd’); ABCD
LOWER(s) 将字符串 s 的所有字母变成小写字母 SELECT LOWER(‘ABCD’); abcd
LEFT(s, n) 返回字符串 s 的前 n 个字符 SELECT LEFT(‘abcdef’, 3); abc
RIGHT(s, n) 返回字符串 s 的后 n 个字符 SELECT RIGHT(‘abcdef’, 3); def
LPAD(s1, length, s2) 字符串 s2 来填充 s1 的开始处,使字符串长度达到 length SELECT LPAD(‘abc’, 8, ‘123’); 12312abc
RPAD(s1, length, s2) 字符串 s2 来填充 s1 的结尾处,使字符串的长度达到 length SELECT RPAD(‘abc’, 8, ‘123’); abc12312
LTRIM(s) 去掉字符串 s 开始处的空格 SELECT LTRIM(‘ abc ‘); ‘abc ‘
RTRIM(s) 去掉字符串 s 结尾处的空格 SELECT RTRIM(‘ abc ‘); ’ abc’
TRIM(s) 去掉字符串 s 开始和结尾处的空格 SELECT TRIM(‘ abc ‘); ‘abc’
TRIM(s1 FROM s) 去掉字符串 s 中开始处和结尾处的字符串 s1 SELECT TRIM(‘-‘ FROM ‘—hello–’); hello
REPEAT(s, n) 将字符串 s 重复 n 次 SELECT REPEAT(‘abc’, 3); abcabcabc
SPACE(n) 返回 n 个空格 SELECT SPACE(3); ‘   ’
REPLACE(s, s1, s2) 将字符串 s2 替代字符串 s 中的字符串 s1 SELECT REPLACE(‘abcdef’, ‘abc’, ‘12’); 12def
STRCMP(s1, s2) 比较字符串 s1 和 s2 SELECT STRCMP(‘abc’, ‘abc’); 0
STRCMP(s1, s2) 比较字符串 s1 和 s2 SELECT STRCMP(‘abc’, ‘abcd’); -1
STRCMP(s1, s2) 比较字符串 s1 和 s2 SELECT STRCMP(‘abc’, ‘ab’); 1
SUBSTRING(s, n, length) 获取从字符串 s 中的第 n 个位置开始长度为 length 的字符串 SELECT SUBSTRING(‘abcdefg’, 2, 3); bcd
MID(s, n, length) 同 SUBSTRING SELECT MID(‘abcdefg’, 3, 2); cd
LOCATE(s1, s) 从字符串 s 中获取 s1 的开始位置 SELECT LOCATE(‘de’, ‘abcdefg’); 4
POSITION(s1, s) 从字符串 s 中获取 s1 的开始位置 SELECT POSITION(‘de’ IN ‘abcdefg’); 4
INSTR(s, s1) 从字符串 s 中获取 s1 的开始位置 SELECT INSTR(‘abcdefg’, ‘de’); 4
REVERSE(s) 将字符串 s 的顺序反过来 SELECT REVERSE(‘a,b,c,d,e,f’); f,e,d,c,b,a
ELT(n, s1, s2, …) 返回第 n 个字符串 SELECT ELT(3, ‘abc’, ‘def’, ‘ghi’, ‘jkl’); ghi
EXPORT_SET(…) 见示例 SELECT EXPORTSET(6, ‘y’, ‘n’, ‘’, 3); n_y_y
FIELD(s, s1, s2, …) 返回第一个与字符串 s 匹配的字符串位置 SELECT FIELD(‘b’, ‘a’, ‘b’, ‘c’); 2
FIND_IN_SET(str, str_list) 见示例 SELECT FIND_IN_SET(‘4’, ‘6,5,4,3,2,1’); 3

2 数学函数

函数 说明 示例 结果
ABS(x) 返回 x 的绝对值    
CEIL(x) 返回大于或等于 x 的最小整数    
CEILING(x) 返回大于或等于 x 的最小整数    
FLOOR(x) 返回小于或等于 x 的最大整数    
RAND() 返回 0->1 的随机数    
RAND(x) 返回 0->1 的随机数,x 值相同时返回的随机数相同    
SIGN(x) 返回 x 的符号,x 是负数、0、正数分别返回-1、0 和 1    
PI() 返回圆周率(3.141593)    
TRUNCATE(x, y) 返回数值 x 保留到小数点后 y 位的值(不会四舍五入)    
ROUND(x) 返回离 x 最近的整数    
ROUND(x, y) 保留 x 小数点后 y 位的值(四舍五入)    
POW(x, y) 返回 x 的 y 次方    
POWER(x, y) 返回 x 的 y 次方    
SQRT(x) 返回 x 的平方根    
EXP(x) 返回 e 的 x 次方    
MOD(x, y) 返回 x 除以 y 以后的余数    
LOG(x) 返回自然对数(以 e 为底的对数)    
LOG10(x) 返回以 10 为底的对数    
RADIANS(x) 将角度转换为弧度    
DEGREES(x) 将弧度转换为角度    
SIN(x) 求正弦值(参数是弧度)    
ASIN(x) 求反正弦值(参数是弧度)    
COS(x) 求余弦值(参数是弧度)    
ACOS(x) 求反余弦值(参数是弧度)    
TAN(x) 求正切值(参数是弧度)    
ATAN(), ATAN2() 求反正切值(参数是弧度)    
COT() 求余切值(参数是弧度)    

3 日期时间函数

函数 说明 示例 结果
CURDATE(), CURRENT_DATE() 返回当前日期 SELECT CURRENT_DATE(); 2017-05-11
CURTIME(), CURRENT_TIME 返回当前时间 SELECT CURRENT_TIME(); 19:01:11
NOW() 返回当前日期和时间 SELECT NOW(); 2017-05-11 19:01:30
CURRENT_TIMESTAMP() 返回当前日期和时间 同上  
LOCALTIME() 返回当前日期和时间 同上  
SYSDATE() 返回当前日期和时间 同上  
LOCALTIMESTAMP() 返回当前日期和时间 同上  
UNIX_TIMESTAMP() 以 UNIX 时间戳的形式返回当前时间 SELECT UNIX_TIMESTAMP(); 1494500521
UNIX_TIMESTAMP(d) 将时间 d 以 UNIX 时间戳的形式返回 SELECT UNIX_TIMESTAMP(‘2017-05-11 19:02:01’); 1494500521
FROM_UNIXTIME(d) 将 UNIX 时间戳的时间转换为普通格式的时间 SELECT FROM_UNIXTIME(1494500521); 2017-05-11 19:02:01
UTC_DATE() 返回 UTC 日期 SELECT UTC_DATE(); 2017-05-11
UTC_TIME() 返回 UTC 时间 SELECT UTC_TIME(); 11:06:13
MONTH(d) 返回日期 d 中的月份值,1->12 SELECT MONTH(‘2017-05-11’); 5
MONTHNAME(d) 返回日期当中的月份名称 SELECT MONTHNAME(‘2017-05-11’); May
DAYNAME(d) 返回日期 d 是星期几 SELECT DAYNAME(‘2017-05-11 19:07:12’); Thursday
DAYOFWEEK(d) 日期 d 今天是星期几,1 星期日,2 星期一 SELECT DAYOFWEEK(‘2017-05-11’); 5
WEEKDAY(d) 日期 d 今天是星期几,0 表示星期一,1 表示星期二 SELECT WEEKDAY(‘2017-05-11’); 3
WEEK(d),WEEKOFYEAR(d) 计算日期 d 是本年的第几个星期,范围是 0->53 SELECT WEEK(‘2017-05-11’); 19
DAYOFYEAR(d) 计算日期 d 是本年的第几天 SELECT DAYOFYEAR(‘2017-05-11’); 131
DAYOFMONTH(d) 计算日期 d 是本月的第几天 SELECT DAYOFMONTH(‘2017-05-11’); 11
QUARTER(d) 返回日期 d 是第几季节,返回 1->4 SELECT QUARTER(‘2017-05-11’); 2
HOUR(t) 返回 t 中的小时值 SELECT HOUR(‘2017-05-11 19:11:23’); 19
MINUTE(t) 返回 t 中的分钟值 SELECT MINUTE(‘2017-05-11 19:11:23’); 11
SECOND(t) 返回 t 中的秒钟值 SELECT SECOND(‘2017-05-11 19:11:23’); 23
EXTRACT(type FROM d) 从日期 d 中获取指定的值,type 指定返回的值(见下文) SELECT EXTRACT(WEEK FROM ‘2017-05-11 19:11:23’); 19
TIME_TO_SEC(t) 将时间 t 转换为秒 SELECT TIME_TO_SEC(‘19:11:23’); 69083
SEC_TO_TIME(s) 将以秒为单位的时间 s 转换为时分秒的格式 SELECT SEC_TO_TIME(69083); 19:11:23
TO_DAYS(d) 计算日期 d 距离 0000 年 1 月 1 日的天数 SELECT TO_DAYS(‘2017-05-11 19:11:23’); 736825
FROM_DAYS(n) 计算从 0000 年 1 月 1 日开始 n 天后的日期 SELECT FROM_DAYS(736825); 2017-05-11
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数 SELECT DATEDIFF(‘2017-05-11’, ‘2017-05-12’); -1
ADDDATE(d,n) 计算其实日期 d 加上 n 天的日期 SELECT ADDDATE(‘2017-05-11 19:11:23’, 3); 2017-05-14 19:11:23
ADDDATE(d,INTERVAL expr type) 计算起始日期 d 加上一个时间段后的日期 SELECT ADDDATE(‘2017-05-11 19:11:23’, INTERVAL 3 HOUR); 2017-05-11 22:11:23
DATE_ADD(d,INTERVAL expr type) 同上 SELECT DATE_ADD(‘2017-05-11 19:11:23’, INTERVAL 10 HOUR); 2017-05-12 05:11:23
SUBDATE(d,n) 日期 d 减去 n 天后的日期 SELECT SUBDATE(‘2017-05-12 05:11:23’, 13); 2017-04-29 05:11:23
SUBDATE(d,INTERVAL expr type) 日期 d 减去一个时间段后的日期 SELECT SUBDATE(‘2017-04-29 05:11:23’, INTERVAL 10 MINUTE); 2017-04-29 05:01:23
ADDTIME(t,n) 时间 t 加上 n 秒的时间 SELECT ADDTIME(‘2017-04-29 05:01:23’, 30); 2017-04-29 05:01:53
SUBTIME(t,n) 时间 t 减去 n 秒的时间 SELECT SUBTIME(‘2017-04-29 05:01:53’, 30); 2017-04-29 05:01:23
DATE_FORMAT(d,f) 按表达式 f 的要求显示日期 d SELECT DATE_FORMAT(‘2017-04-29 05:01:23’, ‘%Y-%m-%d’); 2017-04-29
TIME_FORMAT(t,f) 按表达式 f 的要求显示时间 t SELECT TIME_FORMAT(‘2017-04-29 05:01:23’, ‘%r’); 05:01:23 AM
  • type 的值可以为:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

4 条件判断函数

  • IF (expr, v1, v2);
SELECT IF (1 > 0, 'Y', 'N');	-- Y
  • IFNULL(v1, v2); 如果 v1 不为 NULL, 返回 v1,否则返回 v2
SELECT IFNULL('a', 'b');	-- a

5 系统信息函数

函数 说明 示例 结果
VERSION() 返回数据库的版本号 SELECT VERSION(); 5.7.11
CONNECTION_ID() 返回服务器的连接数 SELECT CONNECTION_ID(); 13
DATABASE() 返回当前数据库名 SELECT DATABASE(); database-learn
       
USER() 返回当前用户 SELECT USER(); root@localhost
CHARSET(s) 返回字符串 s 的字符集 SELECT CHARSET(“123”); utf8
COLLATION(s) 返回字符串 s 的字符排列方式 SELECT COLLATION(“a123”); utf8_general_ci
LAST_INSERT_ID() 返回最近生成的 AUTO_INCREMENT 值 SELECT LAST_INSERT_ID(); 0

8 分组数据

1 数据分组

假设要获取用户的充值次数,最低充值额度,最高充值额度,平均充值额度,可以用以下命令:

SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay;

以上得出的是总的信息,如果要获取每个用户的这些信息,就可以使用分组了。

SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id ORDER BY num;

以上按照每个用户来计算结果。

  • 需要注意的是,GROUP BY 必须出现在 WHERE 之后,ORDER BY 之前
  • 可以使用 WITH ROLLUP 得到汇总的值
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id WITH ROLLUP;

以上在在结果的最后,会附上总的结果。

2 分组过滤

假设只需要得到充值 2 次(包含)以上用户的数据,则需要使用 HAVING 来过滤。

SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay GROUP BY role_id HAVING num >= 2 ORDER BY num;
  • 注意 HAVING 跟 GROUP BY 后面。
  • 也可以同时使用 WHERE 和 HAVING。
SELECT role_id, COUNT(*) AS num, MIN(money) as min_money, MAX(money) as max_money, AVG(money) AS avg_money FROM user_pay WHERE time >= 1483200000 GROUP BY role_id HAVING num >= 2 ORDER BY num;

以上通过 WHERE 新增了条件,2017 年以来充值的。

  • 当 sql_mode 为 ONLY_FULL_GROUP_BY 需要注意
  • 查看 sql_mode 值
SELECT @@sql_mode;

结果:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
  • 在 sql_mode=ONLY_FULL_GROUP_BY 的模式下,以下句子报错
SELECT role_id, money FROM user_pay GROUP BY role_id;

错误: SELECT list is not in GROUP BY clause and contains nonaggregated column ... 表中的列,出现在 SELECT 中时,也得出现在 GROUP BY 中。

SELECT role_id, money FROM user_pay GROUP BY role_id, money;

同样,ORDER BY 也需要注意这个问题。

3 SELECT 字句顺序

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

9 子查询

1 子查询过滤

假设要得出充值用户的用户信息

SELECT role_id, name FROM user WHERE role_id in (SELECT role_id FROM user_pay);
  • 在 SELECT 语句中,子查询总是从内向外处理。
  • 需要保证 WHERE 语句中需要和子 SELECT 语句中有相同数目的列。二者名称可以不相同。
... WHERE role_id in (SELECT role_id ...)
... WHERE role_id in (SELECT r_id ...)

2 做为计算字段使用子查询

假设要得出用户的充值次数(user_pay)以及用户信息(user)

SELECT  role_id,
name,
(SELECT COUNT(*)
FROM user_pay
WHERE user_pay.role_id = user.role_id) AS recharge_count
FROM user
LIMIT 10;

10 联结

1 内联结

同 9.2 假设要得出用户的充值次数(user_pay)以及用户信息(user), 以下两种方法都可以获得结果。

使用 WHERE 子句

SELECT role_id, COUNT(money)
FROM user, user_pay
WHERE user.role_id = user_pay.rid
GROUP BY role_id;

使用 INNER JOIN

SELECT  role_id,
COUNT(money)
FROM user
INNER JOIN user_pay
ON user_pay.role_id = user.role_id
GROUP BY role_id;
  • ANSI SQL 规范首选 INNER JOIN。

2 外联结

外联结使用 OUTER JOIN 来表示。 必须在 OUTER 前加上 LEFT 或 RIGHT 关键字。OUTER 可以省略不写。 LEFT: 表示选中OUTER左侧表的所有行。 RIGHT: 表示选中OUTER右侧表的所有行。

SELECT a.role_id, SUM(b.money) AS total_recharge
FROM user a
LEFT JOIN user_pay b
ON a.role_id = b.role_id
GROUP BY a.role_id;

以上信息获取用户的充值信息,如果有用户没有充值,则 total_recharge=NULL。 如果使用 RIGHT JOIN,如果 user_pay 中有用户数据在 user 表中找不到,则 role_id=NULL。

11 组合查询

1 UNION

假设需要获取充值额度为 30 的用户, 以及渠道为 1001 的用户,使用组合查询:

SELECT role_id, money FROM user_pay WHERE money = 30
UNION
SELECT role_id, money FROM user_pay WHERE channel_id = 1001;
  • 组合使用 UNION 将独立的 SELECT 相连。
  • 每个 SELECT 查询都必须包含相同的列,表达式或函数。但次序不必相同。

2 UNION ALL

UNION 从查询结果中自动去除了重复的行。比如渠道 1001 也有人充值 30 的。 如果不想被去除重复的行,可以使用 UNION ALL。

3 组合查询结果排序

可以在最后一条的 SELECT 后添加 ORDER BY 语句对结果进行排序。

12 全文本搜索

1 引擎支持

  • MyISAM 和 InnoDB(5.6)都支持全文本搜索。 TODO

13 视图

视图可以简化操作,保护数据。

1 创建视图

  • 使用 CREATE VIEW 创建视图。
  • 使用 DROP VIEW 删除视图。
  • 这边使用 CREATE OR REPLACE VIEW
  • 创建一个视图,该视图从用户表(user), 用户充值表(user_pay)获取用户基本信息,总充值额度。
CREATE OR REPLACE VIEW user_pay_info AS
SELECT b.role_id, b.name, SUM(a.money) AS total_money
FROM user_pay a
RIGHT JOIN user b
ON a.role_id = b.role_id
GROUP BY b.role_id;

使用 SHOW TABLES 可以发现多了一个表,user_pay_info。

2 使用视图 SELECT

  • 创建好视图后,再想获得用户充值信息,可以通过以下语句:
SELECT * FROM user_pay_info;

十分便捷。

  • 虽然表面看是从 user_pay_info 中获取数据,但实际上仍然是从 user, user_pay 中获取数据。

3 更新视图 UPDATE

视图中存在以下操作,则不可更新:

  • 分组 (GROUP BY, HAVING)
  • 联结
  • 子查询
  • 聚集函数 (MIN, COUNT, SUM)
  • DISTINCT

但凡 MySQL 不能确定能够正确更新到实际表(user, user_pay),则不允许进行视图更新。 一般,应该将视图用于检索,而不用于更新。

14 存储过程

相当于调用预编译好的 sql 集合。

1 创建存储过程 CREATE PROCEDURE

假设要知道每个用户的充值总额

CREATE PROCEDURE user_pay_total()
BEGIN
SELECT role_id, SUM(money) AS total_recharge
FROM user_pay
GROUP BY role_id;
END

以上就创建好了。 需要注意的是,如果在命令行工具中直接用以上语句创建,会报错。 因为命令行工具也用;做为分隔符,sql 语句中也是用;做为分隔符,存在冲突。 是用 DELIMITER 可以自定义命令行工具的分隔符

DELIMITER //
CREATE PROCEDURE user_pay_total()
BEGIN
SELECT role_id, SUM(money) AS total_recharge
FROM user_pay
GROUP BY role_id;
END //
DELIMITER ;

以上 DELIMITER 告诉命令行工具,使用//做为分隔符。最后一句恢复回;做为分隔符。

2 使用存储过程 CALL

CALL user_pay_total();

以上语句会执行刚才创建的存储过程。

3 删除存储过程 DROP

可以直接使用 DROP 删除

DROP PROCEDURE user_pay_total;

但是,如果不存在 user_pay_total(),就会报错。 所以,建议用以下命令:

DROP PROCEDURE IF EXISTS user_pay_total;

4 使用参数

参数可以用 IN, OUT, INOUT 修饰。 TODO

5 检测存储过程

以下语句可以显示创建存储过程的鳄鱼局

SHOW CREATE PROCEDURE user_pay_info;

15 触发器

1 创建触发器

  • MySQL 触发器只响应以下语句: INSERT, UPDATE, DELETE
  • 保持每个数据库触发器名称唯一。
  • 只有表才支持触发器,视图,临时表不支持。

创建 tb1, tb2 同 tb1

CREATE TABLE `tb1` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '索引值',
`value` int(11) NOT NULL COMMENT '数据',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

创建触发器,命令行下别忘了 DELIMITER

DELIMITER //

CREATE TRIGGER tb1_cp_tb2
AFTER INSERT ON tb1
FOR EACH ROW
BEGIN
INSERT INTO tb2(id, value) VALUES (NEW.id, NEW.value);
END //

DELIMITER ;

以上触发器在 tb1 执行 INSERT 操作时触发,会给 tb2 插入相同的数据。

2 删除触发器

DROP TRIGGER IF EXISTS tb1_cp_tb2;

3 触发说明

  • INSERT
  1. INSERT 触发器可在 INSERT 执行之前或之后触发。
  2. 在触发器代码内,可以使用一个名为 NEW 的虚拟表,访问被插入的行。
  3. 对于 AUTO_INCREMENT 列,NEW 在 INSERT 之前为 0,在 INSERT 执行之后为自动生成的值。
  • UPDATE 同 INSERT
  • DELETE
  1. DELETE 触发器可在 DELETE 执行之前或之后触发。
  2. 在触发器代码内,可以使用一个名为 OLD 的虚拟表,访问被插入的行。
DELIMITER //

CREATE TRIGGER tb1_cp_tb2
AFTER DELETE ON tb1
FOR EACH ROW
BEGIN
INSERT INTO tb2(id, value) VALUES (OLD.id, OLD.value);
END //

DELIMITER ;

从 tb1 删除的数据会被复制到 tb2 中。

16 事务处理

事务处理可以用来维护数据库的完整性,保证多个 SQL 命令要么完全执行,要么完全不执行。

1 事务处理示例

SELECT * FROM tb1;
START TRANSACTION;
DELETE FROM tb1;
SELECT * FROM tb1;
ROLLBACK;
SELECT * FROM tb1;
  • 以上语句中,当删除 tb1 后,再次查询,没有内容。当回滚后,数据又出现了。
  • 可以使用 COMMIT 将事务提交上去执行。
  • 不能回退 SELECT, CREATE, DROP 操作。
  • 当执行 COMMIT 或 ROLLBACK 后,事务会自动关闭。

2 保留点

复杂的事务处理中,可能存在需要部分回退或者部分提交的情况。 可以使用保留点来处理。

SAVEPOINT d1; -- 创建了保留点
...
ROLLBACK TO d1;  -- 回滚到保留点

当事务关闭后,保留点会自动释放。

3 autocommit

InnoDB 默认 autocommit=on,即每一条 sql 语句都是当成一个事务,执行后就提交。 当写下 START TRANSACTION 时,autocommit 的设置就无效了。需要等待 COMMIT 或 ROLLBACK 来结束事务。 autocommit 针对的是每个与 MySQL 的链接,改变其值不会影响其它链接。