devbox
weixin_40725706
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL 学习笔记-第二篇- DQL 与 DML 语句_了解基本查询语句 掌握表单查询的方法 掌握如何使用几何函数的查询

作者：weixin_40725706 | 2024-06-04 05:59:42

赞

踩

了解基本查询语句 掌握表单查询的方法 掌握如何使用几何函数的查询

目录

1 查询数据

1.1 基本查询语句

1.2 单表查询

1.3 使用聚合函数查询

1.4 连接查询 

1.5 子查询

1.6 合并查询结果

1.7 为表和字段取别名

1.8 使用正则表达式查询

1.9 MySQL 8.0 新特性 1 - GROUP BY 不再隐式排序

1.10 MySQL 8.0 新特性 2 - 通用表达式 CET

2 插入、更新与删除数据

2.1 插入数据

2.2 更新数据

2.3 删除数据

2.4 为表增加计算列

2.5 MySQL 8.0 新特性 - DDL 原子化

1 查询数据

  • 了解基本查询语句
  • 掌握表单查询的方法
  • 掌握如何使用几何函数查询
  • 掌握连接查询的方法
  • 掌握如何使用子查询
  • 熟悉合并查询结果
  • 熟悉如何为表和字段取别名
  • 掌握如何使用正则表达式查询

1.1 基本查询语句

MySQL 从数据库表中查询数据的基本语句为 SELECT 语句。基本格式为：

  1. SELECT 
  2.         {* | <字段列表>} 
  3.         [
  4.             FROM [<视图1>, <视图2>... | <表1>，<表2>...]
  5.             [WHERE <表达式>]
  6.             [GROUP BY <字段名>]
  7.             [HAVING <表达式>]
  8.             [ORDER BY <字段名>]
  9.             [LIMIT [<起始位置>,] <总数>]
  10.         ]
  • {* | <字段列表>} 可选星号通配符，表示要查询表中所有字段；或指定字段名，查询指定列。
  • FROM [<视图1>, <视图2>... | <表1>，<表2>...] 查询对象可以是一张或多张视图或表，如果是多张，采用全连接的方式连接各表。
  • [WHERE <表达式>] 过滤出满足表达式的数据行。
  • [GROUP BY <字段名>] 分组查询。
  • [HAVING <表达式>] 配合 [GROUP BY <字段名>] 使用，过滤满足表达式的分组。
  • [ORDER BY <字段名>] 按字段排序后查询，可选 (ASC)、(DESC) 排序方式。
  •  [LIMIT [<起始位置>,] <总数>] 限制查询起始位置和数量。

1.2 单表查询

  1. mysql> CREATE TABLE day_expenditure
  2.     -> (
  3.     -> id  INT AUTO_INCREMENT PRIMARY KEY,
  4.     -> item  VARCHAR(10) NOT NULL,
  5.     -> place  VARCHAR(20),
  6.     -> expend  FLOAT,
  7.     -> time  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  8.     -> );
  9. Query OK, 0 rows affected (0.03 sec)

 需要注意，TIMESTAMP 默认值有几种模式：

  1.  TIMESTAMP :  表示该字段在插入和更新时都不会自动设置当前时间。
  2.  TIMESTAMP DEFAULT CURRENT_TIMESTAMP :  表示插入时如果没有指定该字段的值，则自动设置为当前时间，可以做 create_time 之类的用途。
  3.  TIMESTAMP ON UPDATE CURRENT_TIMESTAMP :  表示更新时如果没有指定该字段的值，则设置为当前时间，可以做 update_time 之类的用途。 
  4.  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP :  表示插入和更新时如果没有指定该字段的值，都会自动设置为当前时间。
  5. 实验了一下，发现完全可以用 DATETIME 来代替 TIMESTAMP ，也可以用其它的函数类似于 NOW(), CURRENT_TIMESTAMP(), LOCALTIME(), SYSDATE() 来替换 CURRENT_TIMESTAMP 当作获取当前时间的函数，只要其它格式保持一致就行。
  6. 当然需要保证下面的全局变量是 ON 的状态： 
    1. mysql> SHOW VARIABLES LIKE 'explicit_defaults_for_timestamp';
    2. +---------------------------------+-------+
    3. | Variable_name                   | Value |
    4. +---------------------------------+-------+
    5. | explicit_defaults_for_timestamp | ON    |
    6. +---------------------------------+-------+
    7. 1 row in set, 1 warning (0.00 sec)
    8.  
    9. // 若以上值为 OFF，需要手动将其设置为 ON
    10. mysql> SET @@global.explicit_defaults_for_timestamp=ON;
    11. Query OK, 0 rows affected (0.00 sec)

创建存储过程方便批量插入数据：

  1. mysql> DELIMITER $;
  2. mysql> CREATE PROCEDURE batchInsertExpends(in args INT)
  3.     -> BEGIN
  4.     -> DECLARE i INT DEFAULT 1;
  5.     -> START TRANSACTION;
  6.     -> WHILE i <= args DO
  7.     -> INSERT INTO day_expenditure(`item`, `place`, `expend`) VALUES('吃饭', '广发银行千灯湖食堂', 20);
  8.     -> SET i = i + 1;
  9.     -> END WHILE;
  10.     -> COMMIT;
  11.     -> END
  12.     -> $;
  13. Query OK, 0 rows affected (0.02 sec)
mysql> CALL batchInsertExpends(1000);

1. 使用 * 通配符查询所有字段。

  1. mysql> SELECT * FROM day_expenditure;
  2. +------+--------+-----------------------------+--------+---------------------+
  3. | id   | item   | place                       | expend | time                |
  4. +------+--------+-----------------------------+--------+---------------------+
  5. |    1 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  6. |    2 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  7. |    3 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  8. |    4 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  9. |    5 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  10. |    6 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  11. |    7 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  12. |    8 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  13. |    9 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  14. |   10 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  15. |   11 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  16. |   12 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  17. |   13 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  18. |   14 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  19. |   15 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  20. |   16 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  21. |   17 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  22. |   18 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  23. |   19 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  24. |   20 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  25. |   21 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  26. ......

 

2. 查询指定字段

  1. mysql> SELECT item, expend FROM day_expenditure;
  2. +--------+--------+
  3. | item   | expend |
  4. +--------+--------+
  5. | 吃饭   |     20 |
  6. | 吃饭   |     20 |
  7. | 吃饭   |     20 |
  8. | 吃饭   |     20 |
  9. | 吃饭   |     20 |
  10. | 吃饭   |     20 |
  11. ......
  12. | 吃饭   |     20 |
  13. | 吃饭   |     20 |
  14. | 吃饭   |     20 |
  15. | 吃饭   |     20 |
  16. | 吃饭   |     20 |
  17. | 吃饭   |     20 |
  18. +--------+--------+
  19. 1000 rows in set (0.00 sec)

MySQL 中的 SQL 语句是不区分大小写的，但是读者应该养成一个良好的习惯，关键字大写，表名和列名小写，这样写出来的 SQL 会更容易维护一些。

3. 查询指定记录

数据库表中的数据是相当多的，一般情况下，我们都不会查出全部数据，而是需要部分满足我们条件的数据，这时就可以使用 WHERE 子句加上一些条件判断表达式对我们的数据进行过滤。

  1. mysql> SELECT item, place FROM day_expenditure WHERE id=45;
  2. +--------+-----------------------------+
  3. | item   | place                       |
  4. +--------+-----------------------------+
  5. | 吃饭   | 广发银行千灯湖食堂          |
  6. +--------+-----------------------------+
  7. 1 row in set (0.00 sec)

 

4. 关键字 IN 和 NOT IN 查询

  1. mysql> SELECT * FROM day_expenditure WHERE id IN (101, 954);
  2. +-----+--------+-----------------------------+--------+---------------------+
  3. | id  | item   | place                       | expend | time                |
  4. +-----+--------+-----------------------------+--------+---------------------+
  5. | 101 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  6. | 954 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  7. +-----+--------+-----------------------------+--------+---------------------+
  8. 2 rows in set (0.00 sec)
  1. mysql> SELECT * FROM day_expenditure WHERE id NOT IN (SELECT id FROM day_expenditure WHERE id >1);
  2. +----+--------+-----------------------------+--------+---------------------+
  3. | id | item   | place                       | expend | time                |
  4. +----+--------+-----------------------------+--------+---------------------+
  5. |  1 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  6. +----+--------+-----------------------------+--------+---------------------+
  7. 1 row in set (0.01 sec)

5. 范围查询

  1. mysql> SELECT id, item, place FROM day_expenditure WHERE id BETWEEN 996 AND 998;
  2. +-----+--------+-----------------------------+
  3. | id  | item   | place                       |
  4. +-----+--------+-----------------------------+
  5. | 996 | 吃饭   | 广发银行千灯湖食堂          |
  6. | 997 | 吃饭   | 广发银行千灯湖食堂          |
  7. | 998 | 吃饭   | 广发银行千灯湖食堂          |
  8. +-----+--------+-----------------------------+
  9. 3 rows in set (0.01 sec)
  1. mysql> SELECT * FROM day_expenditure WHERE id >= 45 AND id <= 54;
  2. +----+--------+-----------------------------+--------+---------------------+
  3. | id | item   | place                       | expend | time                |
  4. +----+--------+-----------------------------+--------+---------------------+
  5. | 45 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  6. | 46 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  7. | 47 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  8. | 48 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  9. | 49 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  10. | 50 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  11. | 51 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  12. | 52 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  13. | 53 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  14. | 54 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  15. +----+--------+-----------------------------+--------+---------------------+
  16. 10 rows in set (0.00 sec)

6. 字符匹配查询

可以使用 LIKE 关键字，也可以使用 REGEXP 关键字进行字符串匹配。

  1. mysql> INSERT INTO day_expenditure(item, place, expend, time) VALUES('烧烤', '青岛精酿', 121, NULL);
  2. Query OK, 1 row affected (0.01 sec)

 

LIKE：

  1. // 匹配以岛开头的字符串
  2. mysql> SELECT * FROM day_expenditure WHERE place LIKE '岛%';
  3. Empty set (0.00 sec)
  4.  
  5. // 匹配以青开头的字符串
  6. mysql> SELECT * FROM day_expenditure WHERE place LIKE '青%';
  7. +------+--------+--------------+--------+------+
  8. | id   | item   | place        | expend | time |
  9. +------+--------+--------------+--------+------+
  10. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  11. +------+--------+--------------+--------+------+
  12. 1 row in set (0.00 sec)
  13.  
  14. // 匹配包含岛的字符串
  15. mysql> SELECT * FROM day_expenditure WHERE place LIKE '%岛%';
  16. +------+--------+--------------+--------+------+
  17. | id   | item   | place        | expend | time |
  18. +------+--------+--------------+--------+------+
  19. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  20. +------+--------+--------------+--------+------+
  21. 1 row in set (0.00 sec)
  22.  
  23. // 匹配以酒结尾的字符串
  24. mysql> SELECT * FROM day_expenditure WHERE place LIKE '%酒';
  25. Empty set (0.00 sec)
  26.  
  27. // 匹配以酿结尾的字符串
  28. mysql> SELECT * FROM day_expenditure WHERE place LIKE '%酿';
  29. +------+--------+--------------+--------+------+
  30. | id   | item   | place        | expend | time |
  31. +------+--------+--------------+--------+------+
  32. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  33. +------+--------+--------------+--------+------+
  34. 1 row in set (0.00 sec)
  35.  
  36. // 匹配两个字符长的第二个字符是岛的字符串
  37. mysql> SELECT * FROM day_expenditure WHERE place LIKE '_岛';
  38. Empty set (0.00 sec)
  39.  
  40. // 匹配四个字符长的第二个字符是岛的字符串
  41. mysql> SELECT * FROM day_expenditure WHERE place LIKE '_岛__';
  42. +------+--------+--------------+--------+------+
  43. | id   | item   | place        | expend | time |
  44. +------+--------+--------------+--------+------+
  45. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  46. +------+--------+--------------+--------+------+
  47. 1 row in set (0.00 sec)
  48.  
  49. // 匹配四个字符长的字符串
  50. mysql> SELECT * FROM day_expenditure WHERE place LIKE '____';
  51. +------+--------+--------------+--------+------+
  52. | id   | item   | place        | expend | time |
  53. +------+--------+--------------+--------+------+
  54. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  55. +------+--------+--------------+--------+------+
  56. 1 row in set (0.00 sec)

REGEXP：

  1. // 匹配以青开头的字符串
  2. mysql> SELECT * FROM day_expenditure WHERE place REGEXP '^青';
  3. +------+--------+--------------+--------+------+
  4. | id   | item   | place        | expend | time |
  5. +------+--------+--------------+--------+------+
  6. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  7. +------+--------+--------------+--------+------+
  8. 1 row in set (0.02 sec)
  9.  
  10. // 匹配以岛开头的字符串
  11. mysql> SELECT * FROM day_expenditure WHERE place REGEXP '^岛';
  12. Empty set (0.00 sec)
  13.  
  14. // 匹配以精结尾的字符串
  15. mysql> SELECT * FROM day_expenditure WHERE place REGEXP '精$';
  16. Empty set (0.00 sec)
  17.  
  18. // 匹配以酿尾的字符串
  19. mysql> SELECT * FROM day_expenditure WHERE place REGEXP '酿$';
  20. +------+--------+--------------+--------+------+
  21. | id   | item   | place        | expend | time |
  22. +------+--------+--------------+--------+------+
  23. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  24. +------+--------+--------------+--------+------+
  25. 1 row in set (0.00 sec)
  26.  
  27. // 匹配包含精的字符串
  28. mysql> SELECT * FROM day_expenditure WHERE place REGEXP '.精';
  29. +------+--------+--------------+--------+------+
  30. | id   | item   | place        | expend | time |
  31. +------+--------+--------------+--------+------+
  32. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  33. +------+--------+--------------+--------+------+
  34. 1 row in set (0.00 sec)
  35.  
  36. // 匹配包含酒的字符串
  37. mysql> SELECT * FROM day_expenditure WHERE place REGEXP '.酒';
  38. Empty set (0.00 sec)
  39.  
  40. // 匹配包含烤肉的任意一个字符的字符串
  41. mysql> SELECT * FROM day_expenditure WHERE item REGEXP '[烤肉]';
  42. +------+--------+--------------+--------+------+
  43. | id   | item   | place        | expend | time |
  44. +------+--------+--------------+--------+------+
  45. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  46. +------+--------+--------------+--------+------+
  47. 1 row in set (0.00 sec)

 

7. 空值查询 IS NULL 与 非空值查询 IS NOT NULL

  1. mysql> SELECT * FROM day_expenditure WHERE time IS NULL;
  2. +------+--------+--------------+--------+------+
  3. | id   | item   | place        | expend | time |
  4. +------+--------+--------------+--------+------+
  5. | 1001 | 烧烤   | 青岛精酿     |    121 | NULL |
  6. +------+--------+--------------+--------+------+
  7. 1 row in set (0.00 sec)
  8.  
  9.  
  10. mysql> SELECT * FROM day_expenditure WHERE time IS NOT NULL;
  11. +------+--------+-----------------------------+--------+---------------------+
  12. | id   | item   | place                       | expend | time                |
  13. +------+--------+-----------------------------+--------+---------------------+
  14. |    1 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  15. |    2 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  16. ......
  17. |  998 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  18. |  999 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  19. | 1000 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  20. +------+--------+-----------------------------+--------+---------------------+
  21. 1000 rows in set (0.00 sec)

8. 多条件查询 AND、OR 

  1. mysql> SELECT * FROM day_expenditure WHERE time IS NULL OR id=1;
  2. +------+--------+-----------------------------+--------+---------------------+
  3. | id   | item   | place                       | expend | time                |
  4. +------+--------+-----------------------------+--------+---------------------+
  5. |    1 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  6. | 1001 | 烧烤   | 青岛精酿                    |    121 | NULL                |
  7. +------+--------+-----------------------------+--------+---------------------+
  8. 2 rows in set (0.00 sec)
  9.  
  10. mysql> SELECT * FROM day_expenditure WHERE time IS NULL AND id=1;
  11. Empty set (0.01 sec)

9. 查询不重复的结果

  1. mysql> SELECT DISTINCT item, place, expend, time FROM day_expenditure;
  2. +--------+-----------------------------+--------+---------------------+
  3. | item   | place                       | expend | time                |
  4. +--------+-----------------------------+--------+---------------------+
  5. | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  6. | 烧烤   | 青岛精酿                    |    121 | NULL                |
  7. +--------+-----------------------------+--------+---------------------+
  8. 2 rows in set (0.00 sec)

10. 排序查询结果

  1. mysql> SELECT * FROM day_expenditure WHERE id > 999 ORDER BY place DESC;
  2. +------+--------+-----------------------------+--------+---------------------+
  3. | id   | item   | place                       | expend | time                |
  4. +------+--------+-----------------------------+--------+---------------------+
  5. | 1001 | 烧烤   | 青岛精酿                    |    121 | NULL                |
  6. | 1000 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  7. +------+--------+-----------------------------+--------+---------------------+
  8. 2 rows in set (0.00 sec)
  1. mysql> SELECT * FROM day_expenditure WHERE id >= 999 ORDER BY expend, item ASC;
  2. +------+--------+-----------------------------+--------+---------------------+
  3. | id   | item   | place                       | expend | time                |
  4. +------+--------+-----------------------------+--------+---------------------+
  5. |  999 | 吃面   | 广发银行千灯湖食堂          |     20 | 2022-05-25 17:11:29 |
  6. | 1000 | 吃饭   | 广发银行千灯湖食堂          |     20 | 2022-05-25 15:38:04 |
  7. | 1001 | 烧烤   | 青岛精酿                    |    121 | NULL                |
  8. +------+--------+-----------------------------+--------+---------------------+
  9. 3 rows in set (0.00 sec)

11. 分组查询

  1. mysql> SELECT item, place, SUM(expend) FROM day_expenditure GROUP BY item, place;
  2. +--------------+-----------------------------+-------------+
  3. | item         | place                       | SUM(expend) |
  4. +--------------+-----------------------------+-------------+
  5. | 吃饭         | 广发银行千灯湖食堂          |         220 |
  6. | 烧烤         | 青岛精酿                    |         121 |
  7. | 打车         | 上下班                      |          66 |
  8. | 吃饭         | 万科金色领域                |         290 |
  9. | 买波罗蜜     | 广发东门                    |          25 |
  10. +--------------+-----------------------------+-------------+
  11. 5 rows in set (0.00 sec)

注意：GROUP BY 关键字通常和分组函数 MAX()、MIN()、COUNT()、SUM()、AVG() 联合使用，并且，SELECT 关键字后面跟的字段要么是分组的字段，要么就是使用了分组函数，否则，以该字段查询出来的列数据也只是取了第一行数据的值显示，毫无意义，甚至造成歧义或问题。

  1. mysql> SELECT item, place, SUM(expend) FROM day_expenditure GROUP BY item;
  2. +--------------+-----------------------------+-------------+
  3. | item         | place                       | SUM(expend) |
  4. +--------------+-----------------------------+-------------+
  5. | 吃饭         | 广发银行千灯湖食堂          |         510 |
  6. | 烧烤         | 青岛精酿                    |         121 |
  7. | 打车         | 上下班                      |          66 |
  8. | 买波罗蜜     | 广发东门                    |          25 |
  9. +--------------+-----------------------------+-------------+
  10. 4 rows in set (0.00 sec)

 

使用  HAVING 过滤分组：

  1. mysql> SELECT item, place, SUM(expend) FROM day_expenditure GROUP BY item, place HAVING SUM(expend) > 100;
  2. +--------+-----------------------------+-------------+
  3. | item   | place                       | SUM(expend) |
  4. +--------+-----------------------------+-------------+
  5. | 吃饭   | 广发银行千灯湖食堂          |         220 |
  6. | 烧烤   | 青岛精酿                    |         121 |
  7. | 吃饭   | 万科金色领域                |         290 |
  8. +--------+-----------------------------+-------------+
  9. 3 rows in set (0.00 sec)
  1. mysql> SELECT item, place, SUM(expend) FROM day_expenditure GROUP BY item, place HAVING item REGEXP '.饭';
  2. +--------+-----------------------------+-------------+
  3. | item   | place                       | SUM(expend) |
  4. +--------+-----------------------------+-------------+
  5. | 吃饭   | 广发银行千灯湖食堂          |         220 |
  6. | 吃饭   | 万科金色领域                |         290 |
  7. +--------+-----------------------------+-------------+
  8. 2 rows in set (0.00 sec)

HAVING 与 WHERE 的区别：

HAVING 在分组之后过滤数据，WHERE 在分组之前过滤数据，被 WHERE 过滤掉的数据不包含在分组之中。

  1. mysql> SELECT item, place, SUM(expend) FROM day_expenditure WHERE item REGEXP '.饭' GROUP BY item, place HAVING SUM(expend) > 100;
  2. +--------+-----------------------------+-------------+
  3. | item   | place                       | SUM(expend) |
  4. +--------+-----------------------------+-------------+
  5. | 吃饭   | 广发银行千灯湖食堂          |         220 |
  6. | 吃饭   | 万科金色领域                |         290 |
  7. +--------+-----------------------------+-------------+
  8. 2 rows in set (0.00 sec)

使用 ORDER BY 排序分组：

  1. mysql> SELECT item, place, SUM(expend) AS sumexpend  FROM day_expenditure WHERE item REGEXP '.饭' GROUP BY item, place HAVING SUM(expend) > 100 ORDER BY sumexpend DESC;
  2. +--------+-----------------------------+-----------+
  3. | item   | place                       | sumexpend |
  4. +--------+-----------------------------+-----------+
  5. | 吃饭   | 万科金色领域                |       290 |
  6. | 吃饭   | 广发银行千灯湖食堂          |       220 |
  7. +--------+-----------------------------+-----------+
  8. 2 rows in set (0.00 sec)

使用 WITH ROLLUP 统计分组函数总量：

  1. mysql> SELECT item, place, SUM(expend) AS sumexpend  FROM day_expenditure GROUP BY item, place WITH ROLLUP;
  2. +--------------+-----------------------------+-----------+
  3. | item         | place                       | sumexpend |
  4. +--------------+-----------------------------+-----------+
  5. | 买波罗蜜     | 广发东门                    |        25 |
  6. | 买波罗蜜     | NULL                        |        25 |
  7. | 吃饭         | 万科金色领域                |       290 |
  8. | 吃饭         | 广发银行千灯湖食堂          |       220 |
  9. | 吃饭         | NULL                        |       510 |
  10. | 打车         | 上下班                      |        66 |
  11. | 打车         | NULL                        |        66 |
  12. | 烧烤         | 青岛精酿                    |       121 |
  13. | 烧烤         | NULL                        |       121 |
  14. | NULL         | NULL                        |       722 |
  15. +--------------+-----------------------------+-----------+
  16. 10 rows in set (0.00 sec)

WITH ROLLUP 会对每一列分组字段进行一次分组函数列的总量求和，然后对所有列分组字段进行一次分组函数的总量求和。

1.3 使用聚合函数查询

表1.1 MySQL 聚合函数
函数作用
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值的和

1. COUNT() 函数

统计数据表中包含的记录行的总数，有两种用法：

  • COUNT(*) 计算表中总的行数，不管某列是否有值或为空值。
  1. mysql> SELECT item, place, COUNT(*) FROM day_expenditure GROUP BY item, place HAVING SUM(expend) > 100;
  2. +--------+-----------------------------+----------+
  3. | item   | place                       | COUNT(*) |
  4. +--------+-----------------------------+----------+
  5. | 吃饭   | 广发银行千灯湖食堂          |       11 |
  6. | 烧烤   | 青岛精酿                    |        1 |
  7. | 吃饭   | 万科金色领域                |        5 |
  8. +--------+-----------------------------+----------+
  9. 3 rows in set (0.00 sec)
  • COUNT(字段名) 计算表指定列下总的行数，忽略空值的行。
  1. mysql> SELECT item, place, COUNT(time) AS count FROM day_expenditure GROUP BY item, place HAVING SUM(expend) > 100;
  2. +--------+-----------------------------+-------+
  3. | item   | place                       | count |
  4. +--------+-----------------------------+-------+
  5. | 吃饭   | 广发银行千灯湖食堂          |    11 |
  6. | 烧烤   | 青岛精酿                    |     0 |
  7. | 吃饭   | 万科金色领域                |     5 |
  8. +--------+-----------------------------+-------+
  9. 3 rows in set (0.00 sec)

2. SUM() 函数是一个求总和的函数，可以与 GROUP BY 组合使用计算每个分组的某几列值总和。

  1. mysql> SELECT item, place, SUM(expend) AS sum FROM day_expenditure GROUP BY item, place HAVING SUM(expend) > 100;
  2. +--------+-----------------------------+------+
  3. | item   | place                       | sum  |
  4. +--------+-----------------------------+------+
  5. | 吃饭   | 广发银行千灯湖食堂          |  220 |
  6. | 烧烤   | 青岛精酿                    |  121 |
  7. | 吃饭   | 万科金色领域                |  290 |
  8. +--------+-----------------------------+------+
  9. 3 rows in set (0.00 sec)
  10.  
  11. mysql> SELECT SUM(expend) AS sum FROM day_expenditure;
  12. +------+
  13. | sum  |
  14. +------+
  15. |  722 |
  16. +------+
  17. 1 row in set (0.01 sec)

3. AVG() 计算平均值的函数，可以与 GROUP BY 组合使用计算每个分组的某几列的平均值。

  1. mysql> SELECT item, place, AVG(expend) AS avg FROM day_expenditure GROUP BY item, place;
  2. +--------------+-----------------------------+------+
  3. | item         | place                       | avg  |
  4. +--------------+-----------------------------+------+
  5. | 吃饭         | 广发银行千灯湖食堂          |   20 |
  6. | 烧烤         | 青岛精酿                    |  121 |
  7. | 打车         | 上下班                      |   11 |
  8. | 吃饭         | 万科金色领域                |   58 |
  9. | 买波罗蜜     | 广发东门                    | 12.5 |
  10. +--------------+-----------------------------+------+
  11. 5 rows in set (0.00 sec)

4. MAX() 求某列或分别某几列的最大值，可以与 GROUP BY 组合使用。

  1. mysql> SELECT item, MAX(expend) AS max FROM day_expenditure GROUP BY item;
  2. +--------------+------+
  3. | item         | max  |
  4. +--------------+------+
  5. | 吃饭         |   58 |
  6. | 烧烤         |  121 |
  7. | 打车         |   11 |
  8. | 买波罗蜜     |   15 |
  9. +--------------+------+
  10. 4 rows in set (0.00 sec)
  11.  
  12. mysql> SELECT MAX(expend) AS max FROM day_expenditure;
  13. +------+
  14. | max  |
  15. +------+
  16. |  121 |
  17. +------+
  18. 1 row in set (0.00 sec)

5. MIN() 求某几列的最小值，可以与 GROUP BY 组合使用。

  1. mysql> SELECT item, MIN(expend) AS max FROM day_expenditure GROUP BY item;
  2. +--------------+------+
  3. | item         | max  |
  4. +--------------+------+
  5. | 吃饭         |   20 |
  6. | 烧烤         |  121 |
  7. | 打车         |   11 |
  8. | 买波罗蜜     |   10 |
  9. +--------------+------+
  10. 4 rows in set (0.01 sec)

单独使用聚合函数的时候，SELECT 后面跟的字段也必须是分组函数的参数或是被分组的列，否则就会出现像下面这样的混乱。

  1. mysql> SELECT item, MIN(expend) AS max FROM day_expenditure;
  2. +--------+------+
  3. | item   | max  |
  4. +--------+------+
  5. | 吃饭   |   10 |
  6. +--------+------+
  7. 1 row in set (0.00 sec)

客官可以采用另外的方式达到你的目的，如：

  1. mysql> SELECT item, MIN(expend) AS max FROM day_expenditure GROUP BY item HAVING max <= 10;
  2. +--------------+------+
  3. | item         | max  |
  4. +--------------+------+
  5. | 买波罗蜜     |   10 |
  6. +--------------+------+

1.4 连接查询 

连接是关系型数据库的主要特点。连接查询是关系型数据库中最主要的查询，主要包括内连接、外连接等。通常连接运算符可以实现多个表查询。

1. 内连接查询

内连接(INNER JOIN) 使用比较运算符进行表间某(些)列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组成新的记录，也就是说，在内连接查询中，只有满足条件的记录才能出现在查询结果之中。

  1. mysql> CREATE TABLE place_star
  2.     -> (
  3.     -> id  INT AUTO_INCREMENT PRIMARY KEY,
  4.     -> place  VARCHAR(24) NOT NULL,
  5.     -> star  VARCHAR(6)
  6.     -> );
  7. Query OK, 0 rows affected (0.03 sec)
  8.  
  9. mysql> INSERT INTO place_star(place, star) VALUES('广发银行千灯湖食堂', '※※※'), ('青岛精酿', '※※'), ('万科金色领域, '※'), ('蒲二炭烤肉', '※※※※※'), ('胡老大', '※※※※');
  10. Query OK, 5 rows affected (0.01 sec)
  11. Records: 5  Duplicates: 0  Warnings: 0
  13. mysql> SELECT * FROM place_star;
  14. +----+-----------------------------+-----------------+
  15. | id | place                       | star            |
  16. +----+-----------------------------+-----------------+
  17. |  1 | 广发银行千灯湖食堂          | ※※※             |
  18. |  2 | 青岛精酿                    | ※※              |
  19. |  3 | 万科金色领域                | ※               |
  20. |  4 | 蒲二炭烤肉                  | ※※※※※           |
  21. |  5 | 胡老大                      | ※※※※            |
  22. +----+-----------------------------+-----------------+
  23. 5 rows in set (0.00 sec)
  1. mysql> SELECT item, a.place AS place, star, expend FROM day_expenditure AS a, place_star AS b WHERE a.place = b.place;
  2. +--------+-----------------------------+-----------+--------+
  3. | item   | place                       | star      | expend |
  4. +--------+-----------------------------+-----------+--------+
  5. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  6. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  7. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  8. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  9. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  10. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  11. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  12. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  13. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  14. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  15. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  16. | 烧烤   | 青岛精酿                    | ※※        |    121 |
  17. | 吃饭   | 万科金色领域                | ※         |     58 |
  18. | 吃饭   | 万科金色领域                | ※         |     58 |
  19. | 吃饭   | 万科金色领域                | ※         |     58 |
  20. | 吃饭   | 万科金色领域                | ※         |     58 |
  21. | 吃饭   | 万科金色领域                | ※         |     58 |
  22. +--------+-----------------------------+-----------+--------+
  23. 17 rows in set (0.00 sec)

两个表中如果存在相同的字段名，需要指明格式为表名.字段名，否则，MySQL 不知道指的是哪个表中的该字段，将会报错。

使用 INNER JOIN 语法时，要用 ON 关键字指定连接条件。

  1. mysql> SELECT item, a.place AS place, star, expend FROM day_expenditure AS a INNER JOIN place_star AS b ON a.place = b.place;
  2. +--------+-----------------------------+-----------+--------+
  3. | item   | place                       | star      | expend |
  4. +--------+-----------------------------+-----------+--------+
  5. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  6. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  7. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  8. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  9. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  10. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  11. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  12. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  13. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  14. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  15. | 吃饭   | 广发银行千灯湖食堂          | ※※※       |     20 |
  16. | 烧烤   | 青岛精酿                    | ※※        |    121 |
  17. | 吃饭   | 万科金色领域                | ※         |     58 |
  18. | 吃饭   | 万科金色领域                | ※         |     58 |
  19. | 吃饭   | 万科金色领域                | ※         |     58 |
  20. | 吃饭   | 万科金色领域                | ※         |     58 |
  21. | 吃饭   | 万科金色领域                | ※         |     58 |
  22. +--------+-----------------------------+-----------+--------+
  23. 17 rows in set (0.00 sec)

如果进行内连接的两张表物理上是同一张表，则称为自连接。

这个暂时先不举例了，之后有空再补充吧。

2. 外连接查询

连接查询时有时候需要包含没有满足关联条件的行，这时就要用外连接，分为如下几种：

  • LEFT [OUTER] JOIN: 返回包括左表中所有记录和右表中满足关联条件的记录的组合。
  • RIGHT [OUTER] JOIN: 返回包括右表中所有记录和左表中满足关联条件的记录的组合。

LEFT [OUTER] JOIN，未满足匹配条件的右表数据将置为 NULL。

  1. mysql> SELECT item, a.place AS place, star, expend FROM day_expenditure AS a LEFT JOIN place_star AS b ON a.place = b.place;
  2. +--------------+-----------------------------+-----------+--------+
  3. | item         | place                       | star      | expend |
  4. +--------------+-----------------------------+-----------+--------+
  5. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  6. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  7. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  8. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  9. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  10. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  11. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  12. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  13. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  14. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  15. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  16. | 烧烤         | 青岛精酿                    | ※※        |    121 |
  17. | 打车         | 上下班                      | NULL      |     11 |
  18. | 打车         | 上下班                      | NULL      |     11 |
  19. | 打车         | 上下班                      | NULL      |     11 |
  20. | 打车         | 上下班                      | NULL      |     11 |
  21. | 打车         | 上下班                      | NULL      |     11 |
  22. | 打车         | 上下班                      | NULL      |     11 |
  23. | 吃饭         | 万科金色领域                | ※         |     58 |
  24. | 吃饭         | 万科金色领域                | ※         |     58 |
  25. | 吃饭         | 万科金色领域                | ※         |     58 |
  26. | 吃饭         | 万科金色领域                | ※         |     58 |
  27. | 吃饭         | 万科金色领域                | ※         |     58 |
  28. | 买波罗蜜     | 广发东门                    | NULL      |     10 |
  29. | 买波罗蜜     | 广发东门                    | NULL      |     15 |
  30. +--------------+-----------------------------+-----------+--------+
  31. 25 rows in set (0.00 sec)
  32.  
  33. mysql> SELECT item, a.place AS place, star, expend FROM day_expenditure AS a LEFT OUTER JOIN place_star AS b ON a.place = b.place;
  34. +--------------+-----------------------------+-----------+--------+
  35. | item         | place                       | star      | expend |
  36. +--------------+-----------------------------+-----------+--------+
  37. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  38. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  39. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  40. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  41. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  42. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  43. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  44. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  45. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  46. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  47. | 吃饭         | 广发银行千灯湖食堂          | ※※※       |     20 |
  48. | 烧烤         | 青岛精酿                    | ※※        |    121 |
  49. | 打车         | 上下班                      | NULL      |     11 |
  50. | 打车         | 上下班                      | NULL      |     11 |
  51. | 打车         | 上下班                      | NULL      |     11 |
  52. | 打车         | 上下班                      | NULL      |     11 |
  53. | 打车         | 上下班                      | NULL      |     11 |
  54. | 打车         | 上下班                      | NULL      |     11 |
  55. | 吃饭         | 万科金色领域                | ※         |     58 |
  56. | 吃饭         | 万科金色领域                | ※         |     58 |
  57. | 吃饭         | 万科金色领域                | ※         |     58 |
  58. | 吃饭         | 万科金色领域                | ※         |     58 |
  59. | 吃饭         | 万科金色领域                | ※         |     58 |
  60. | 买波罗蜜     | 广发东门                    | NULL      |     10 |
  61. | 买波罗蜜     | 广发东门                    | NULL      |     15 |
  62. +--------------+-----------------------------+-----------+--------+
  63. 25 rows in set (0.00 sec)

RIGHT [OUTER] JOIN，未满足匹配条件的左表数据将置为 NULL。

  1. mysql> SELECT item, b.place AS place, star, expend FROM day_expenditure AS a RIGHT JOIN place_star AS b ON a.place = b.place;
  2. +--------+-----------------------------+-----------------+--------+
  3. | item   | place                       | star            | expend |
  4. +--------+-----------------------------+-----------------+--------+
  5. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  6. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  7. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  8. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  9. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  10. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  11. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  12. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  13. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  14. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  15. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  16. | 烧烤   | 青岛精酿                    | ※※              |    121 |
  17. | 吃饭   | 万科金色领域                | ※               |     58 |
  18. | 吃饭   | 万科金色领域                | ※               |     58 |
  19. | 吃饭   | 万科金色领域                | ※               |     58 |
  20. | 吃饭   | 万科金色领域                | ※               |     58 |
  21. | 吃饭   | 万科金色领域                | ※               |     58 |
  22. | NULL   | 蒲二炭烤肉                  | ※※※※※           |   NULL |
  23. | NULL   | 胡老大                      | ※※※※            |   NULL |
  24. +--------+-----------------------------+-----------------+--------+
  25. 19 rows in set (0.00 sec)
  26.  
  27. mysql> SELECT item, b.place AS place, star, expend FROM day_expenditure AS a RIGHT OUTER JOIN place_star AS b ON a.place = b.place;
  28. +--------+-----------------------------+-----------------+--------+
  29. | item   | place                       | star            | expend |
  30. +--------+-----------------------------+-----------------+--------+
  31. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  32. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  33. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  34. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  35. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  36. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  37. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  38. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  39. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  40. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  41. | 吃饭   | 广发银行千灯湖食堂          | ※※※             |     20 |
  42. | 烧烤   | 青岛精酿                    | ※※              |    121 |
  43. | 吃饭   | 万科金色领域                | ※               |     58 |
  44. | 吃饭   | 万科金色领域                | ※               |     58 |
  45. | 吃饭   | 万科金色领域                | ※               |     58 |
  46. | 吃饭   | 万科金色领域                | ※               |     58 |
  47. | 吃饭   | 万科金色领域                | ※               |     58 |
  48. | NULL   | 蒲二炭烤肉                  | ※※※※※           |   NULL |
  49. | NULL   | 胡老大                      | ※※※※            |   NULL |
  50. +--------+-----------------------------+-----------------+--------+
  51. 19 rows in set (0.00 sec)

1.5 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。在 SELECT 子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或多个表。子查询中常用的操作符有 ANY(SOME)、ALL、IN、EXISTS。子查询可以添加岛 SELECT、UPDATE、DELETE 语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如 ”<“, ">", "<=", ">=", "!=" 等。

1. 带 ANY、SOME 关键字的子查询

ANY、SOME 关键字表示只要满足任意一个就返回 TRUE。

  1. mysql> CREATE TABLE tb1 (nums1 INT NOT NULL);
  2. Query OK, 0 rows affected (0.03 sec)
  3.  
  4. mysql> CREATE TABLE tb2 (nums2 INT NOT NULL);
  5. Query OK, 0 rows affected (0.03 sec)
  6.  
  7. mysql> INSERT INTO tb1 VALUES(1), (5), (13), (27);
  8. Query OK, 4 rows affected (0.01 sec)
  9. Records: 4  Duplicates: 0  Warnings: 0
  10.  
  11. mysql> INSERT INTO tb2 VALUES(6), (14), (11), (10);
  12. Query OK, 4 rows affected (0.01 sec)
  13. Records: 4  Duplicates: 0  Warnings: 0
  14.  
  15. mysql> SELECT nums1 FROM tb1;
  16. +-------+
  17. | nums1 |
  18. +-------+
  19. |     1 |
  20. |     5 |
  21. |    13 |
  22. |    27 |
  23. +-------+
  24. 4 rows in set (0.00 sec)
  25.  
  26. mysql> SELECT nums2 FROM tb2;
  27. +-------+
  28. | nums2 |
  29. +-------+
  30. |     6 |
  31. |    14 |
  32. |    11 |
  33. |    10 |
  34. +-------+
  35. 4 rows in set (0.00 sec)
  1. mysql> SELECT nums1 FROM tb1 WHERE nums1 > ANY (SELECT nums2 FROM tb2);
  2. +-------+
  3. | nums1 |
  4. +-------+
  5. |    13 |
  6. |    27 |
  7. +-------+
  8. 2 rows in set (0.00 sec)
  9.  
  10. mysql> SELECT nums1 FROM tb1 WHERE nums1 > SOME (SELECT nums2 FROM tb2);
  11. +-------+
  12. | nums1 |
  13. +-------+
  14. |    13 |
  15. |    27 |
  16. +-------+
  17. 2 rows in set (0.00 sec)

2. 带 ALL 关键字的子查询

ALL 关键字表示外层需要满足内层结果集中的所有结果才能通过过滤。

  1. mysql> SELECT nums1 FROM tb1 WHERE nums1 > ALL (SELECT nums2 FROM tb2);
  2. +-------+
  3. | nums1 |
  4. +-------+
  5. |    27 |
  6. +-------+
  7. 1 row in set (0.00 sec)

3. 带 EXISTS、NOT EXISTS 关键字的子查询

EXISTS 关键字后面的参数可以是任意一个子查询，子查询的结果集值本身没有太大意义，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，则 EXISTS 返回 TRUE ，此时外层查询才进行查询；若子查询没有返回任何行，那么 EXISTS 结果为 FALSE ，此时外层不进行查询。

  1. mysql> SELECT nums1 FROM tb1 WHERE EXISTS (SELECT nums2 FROM tb2 WHERE nums2 > 10);
  2. +-------+
  3. | nums1 |
  4. +-------+
  5. |     1 |
  6. |     5 |
  7. |    13 |
  8. |    27 |
  9. +-------+
  10. 4 rows in set (0.00 sec)
  11.  
  12. mysql> SELECT nums1 FROM tb1 WHERE EXISTS (SELECT nums2 FROM tb2 WHERE nums2 > 14);
  13. Empty set (0.00 sec)

NOT EXISTS 关键字与 EXISTS 正好相反，若子查询没有返回则外层进行查询，否则哪怕返回一行外层查询也会进行。

  1. mysql> SELECT nums1 FROM tb1 WHERE NOT EXISTS (SELECT nums2 FROM tb2 WHERE nums2 > 14);
  2. +-------+
  3. | nums1 |
  4. +-------+
  5. |     1 |
  6. |     5 |
  7. |    13 |
  8. |    27 |
  9. +-------+
  10. 4 rows in set (0.00 sec)
  11.  
  12. mysql> SELECT nums1 FROM tb1 WHERE NOT EXISTS (SELECT nums2 FROM tb2 WHERE nums2 > 11);
  13. Empty set (0.00 sec)

4.  使用带 IN 的关键子查询

IN 关键字在子查询查出的数据列表中进行过滤，看外层查询的值是否在子查询的结果集中， NOT IN 与此相反。

  1. mysql> SELECT nums1 FROM tb1 WHERE nums1 IN (SELECT nums2 FROM tb2);
  2. Empty set (0.00 sec)
  3.  
  4. mysql> SELECT nums1 FROM tb1 WHERE nums1 NOT IN (SELECT nums2 FROM tb2);
  5. +-------+
  6. | nums1 |
  7. +-------+
  8. |     1 |
  9. |     5 |
  10. |    13 |
  11. |    27 |
  12. +-------+
  13. 4 rows in set (0.00 sec)

5.  带比较运算符的子查询

  1. mysql> SELECT nums1 FROM tb1 WHERE nums1 <> (SELECT nums2 FROM tb2 WHERE nums2=11);
  2. +-------+
  3. | nums1 |
  4. +-------+
  5. |     1 |
  6. |     5 |
  7. |    13 |
  8. |    27 |
  9. +-------+
  10. 4 rows in set (0.00 sec)

1.6 合并查询结果

利用 UNION 关键字合并多条 SELECT 语句的查询结果，合并的前提是查询出的结果集的列数和数据类型必须相同。各个 SELECT 语句使用 UNION 或 UNION ALL 连接，两者的区别是 UNION 会去重，而 UNION ALL 不会。

  1. mysql> INSERT INTO tb1 VALUES(66);
  2. Query OK, 1 row affected (0.01 sec)
  3.  
  4. mysql> INSERT INTO tb2 VALUES(66);
  5. Query OK, 1 row affected (0.00 sec)
  6.  
  7. mysql> SELECT * FROM tb1;
  8. +-------+
  9. | nums1 |
  10. +-------+
  11. |     1 |
  12. |     5 |
  13. |    13 |
  14. |    27 |
  15. |    66 |
  16. +-------+
  17. 5 rows in set (0.00 sec)
  18.  
  19. mysql> SELECT * FROM tb2;
  20. +-------+
  21. | nums2 |
  22. +-------+
  23. |     6 |
  24. |    14 |
  25. |    11 |
  26. |    10 |
  27. |    66 |
  28. +-------+
  29. 5 rows in set (0.00 sec)
  1. mysql> SELECT nums1 FROM tb1 UNION SELECT nums2 FROM tb2;
  2. +-------+
  3. | nums1 |
  4. +-------+
  5. |     1 |
  6. |     5 |
  7. |    13 |
  8. |    27 |
  9. |    66 |
  10. |     6 |
  11. |    14 |
  12. |    11 |
  13. |    10 |
  14. +-------+
  15. 9 rows in set (0.00 sec)
  16.  
  17. mysql> SELECT nums1 FROM tb1 UNION ALL SELECT nums2 FROM tb2;
  18. +-------+
  19. | nums1 |
  20. +-------+
  21. |     1 |
  22. |     5 |
  23. |    13 |
  24. |    27 |
  25. |    66 |
  26. |     6 |
  27. |    14 |
  28. |    11 |
  29. |    10 |
  30. |    66 |
  31. +-------+
  32. 10 rows in set (0.00 sec)

1.7 为表和字段取别名

当表名过长或字段过长时，可以取别名，注意不能与其它表名或字段名冲突。

表名 [AS] 表别名
字段 [AS] 字段别名

1.8 使用正则表达式查询

MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式。

表1.2 MySQL 正则表达式常用字符匹配列表
选项说明例子匹配值实例
^匹配文本的开始字符'^b' 匹配以 b 开头的字符串book、backup
$匹配文本的结束字符'a$' 匹配以 b 结束的字符串NBA、cba
.匹配任何单个字符'b.t' 匹配任何 b 和 t 之间有一个字符的字符串bit、but
*匹配零个或多个在它前面的字符't*x' 匹配字符 x 前面有任意个字符 t 的字符串x、tx、ttx
+匹配一个或多个在它前面的字符'tx+'匹配以 t 开头后面至少跟一个字符 x 的字符串txt、txx
str匹配包含str字符串的文本'tx' 匹配任何包含 tx 的字符串ltx、ltx666
[字符集合]匹配字符集合中任意一个字符'[tx]' 匹配任何包含 t 或 x 的字符串lt、xx
[^]匹配不在字符集合中的文本'[^abc]' 匹配任何不包含 a、b 或 c 的字符串def、ghi
{n,}匹配前面的字符串至少 n 次b{2} 匹配两个或更多的 bbbb、bbbbbx
{n, m}匹配前面的字符串至少 n 次，至多 m 次b{2, 4} 匹配含最少两个、最多四个 b 的字符串bxbxbx

 

1.9 MySQL 8.0 新特性 1 - GROUP BY 不再隐式排序

这个很好理解。

1.10 MySQL 8.0 新特性 2 - 通用表达式 CET

这个还搞不懂。

2 插入、更新与删除数据

2.1 插入数据

MySQL 使用 INSERT 语句向数据库表中插入数据，可以插入记录的一部分、插入完整的数据、插入多条记录、插入另一个查询的结果等。

2.1.1 为表的所有字段插入数据

INSERT INTO table_name (column_List) VALUES (value_List)

注意：字段列和数据列的数量必须相同，允许字段列表 column_List 为空，此时，值列表value_List 需要为表的每一个字段指定值，并且值的顺序必须和数据表中字段定义时的顺序一致。

2.1.2 为表的指定字段插入数据

指定部分字段值插入，未指定字段将取表定义时的默认值，如果未定义默认值，则为 NULL，但前提是未指定字段没有 NOT NULL 约束。

2.1.3 同时插入多条记录

INSERT INTO table_name (column_List) VALUES (value_List1), (value_List2), ..., (value_Listn)

使用 INSERT 同时插入多条记录时，MySQL 会返回一些在执行单行插入时没有的信息：

  • Records：表明插入的记录数。
  • Duplicates：表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键。
  • Warnings：表明有问题的数据值，例如发生数据类型转换。 

2.1.4 将查询结果插入到表中

INSERT INTO table_name1 (column_List1) SELECT (column_List2) FROM table_name2 WHERE (condition)

注意：column_List1 和 column_List2 的字段个数和数据类型必须相同。

2.2 更新数据

MySQL 使用 UPDATE 语句更新数据库表中的数据。

UPDATE table_name SET column_name1 = value1, column_name2 = value2, ..., column_namen = valuen WHERE (condition)

注意：保证 UPDATE 语句以 WHERE 子句结束，通过 WHERE 子句指定被更新的记录所需满足的条件，如果忽略 WHERE 子句，MySQL 将更新表中所有的行。

2.3 删除数据

从数据表中删除数据使用 DELETE 语句，DELETE 语句允许 WHERE 子句指定删除条件。

DELETE FROM table_name [WHERE <condition>]

注意：[WHERE <condition>] 为可选条件，如果没有 WHERE 子句，MySQL 将删除表中所有的行。如果想删除表中所有记录，还可以使用 TRUNCATE TABLE 语句，TRUNCATE 将直接删除原来的表，并重建一个表。其速度比 DELETE 快。

TRUNCATE TABLE table_name

2.4 为表增加计算列

计算列的值总是通过其它列或其它数据的值计算而来。

col_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT comment] [NOT NULL | NULL] [[PRIMARY] KEY]

MySQL 8.0 中，CREATE TABLE 和 ALTER TABLE 中都支持增加计算列。

  1. mysql> CREATE TABLE tb54
  2.     -> (
  3.     -> id  INT(9) NOT NULL AUTO_INCREMENT,
  4.     -> a  INT(9) DEFAULT NULL,
  5.     -> b  INT(9) DEFAULT NULL,
  6.     -> c  INT(9) GENERATED ALWAYS AS (a+b) VIRTUAL,
  7.     -> PRIMARY KEY(`id`)
  8.     -> );
  9. Query OK, 0 rows affected, 4 warnings (0.03 sec)
  10.  
  11. mysql> SHOW WARNINGS;
  12. +---------+------+------------------------------------------------------------------------------+
  13. | Level   | Code | Message                                                                      |
  14. +---------+------+------------------------------------------------------------------------------+
  15. | Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
  16. | Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
  17. | Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
  18. | Warning | 1681 | Integer display width is deprecated and will be removed in a future release. |
  19. +---------+------+------------------------------------------------------------------------------+
  20. 4 rows in set (0.00 sec)

四个警告是说我们声明的数据类型只是做展示用，具体存储是多少位是不受此限制且不可预测的，但必然受到该数据类型的范围限制，这里不用管。

  1. mysql> INSERT INTO tb54(a, b) VALUES(11, 16), (21, 24);
  2. Query OK, 2 rows affected (0.01 sec)
  3. Records: 2  Duplicates: 0  Warnings: 0
  4.  
  5. mysql> SELECT * FROM tb54;
  6. +----+------+------+------+
  7. | id | a    | b    | c    |
  8. +----+------+------+------+
  9. |  1 |   11 |   16 |   27 |
  10. |  2 |   21 |   24 |   45 |
  11. +----+------+------+------+
  12. 2 rows in set (0.00 sec)
  1. mysql> UPDATE tb54 SET b = 17 WHERE id = 1;
  2. Query OK, 1 row affected (0.01 sec)
  3. Rows matched: 1  Changed: 1  Warnings: 0
  4.  
  5. mysql> SELECT * FROM tb54;
  6. +----+------+------+------+
  7. | id | a    | b    | c    |
  8. +----+------+------+------+
  9. |  1 |   11 |   17 |   28 |
  10. |  2 |   21 |   24 |   45 |
  11. +----+------+------+------+
  12. 2 rows in set (0.00 sec)

2.5 MySQL 8.0 新特性 - DDL 原子化

在 MySQL 8.0 中， InnoDB 表的 DDL 支持事务的完整性，即 DDL 要么成功要么失败回滚。DDL 操作的回滚日志写入到 data dictionary 数据字典表 mysql.innodb_ddl_log (该表是隐藏的表，通过SHOW TABLES 无法看到) 中，用于回滚操作。

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/weixin_40725706/article/detail/670671
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号