欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

MySQL必知必会

程序员文章站 2022-07-12 17:57:14
...

本文链接
http://alex-my.xyz/books/database/MySQL必知必会

http://blog.csdn.net/alex_my/article/details/72357498

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 EXPORT_SET(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(); [email protected]
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的链接，改变其值不会影响其它链接。

上一篇： MyBatis学习3之CRUD

下一篇： tomcat无法shutdown

推荐阅读