MySQL必知必会

本文链接
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

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

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

6 匹配字符类

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

示例:

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

7 匹配多个实例

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 字符串函数

2 数学函数

3 日期时间函数

* 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 系统信息函数

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的链接，改变其值不会影响其它链接。