语法

SELECT 的官方文档：https://dev.mysql.com/doc/refman/5.7/en/select.html

  1. SELECT
  2.     [ALL | DISTINCT | DISTINCTROW ]
  3.     [HIGH_PRIORITY]
  4.     [STRAIGHT_JOIN]
  5.     [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
  6.     [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
  7.     select_expr [, select_expr] ...
  8.     [into_option]
  9.     [FROM table_references
  10.       [PARTITION partition_list]]
  11.     [WHERE where_condition]
  12.     [GROUP BY {col_name | expr | position}
  13.       [ASC | DESC], ... [WITH ROLLUP]]
  14.     [HAVING where_condition]
  15.     [ORDER BY {col_name | expr | position}
  16.       [ASC | DESC], ...]
  17.     [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  18.     [PROCEDURE procedure_name(argument_list)]
  19.     [into_option]
  20.     [FOR UPDATE | LOCK IN SHARE MODE]
  22. into_option: {
  23.     INTO OUTFILE 'file_name'
  24.         [CHARACTER SET charset_name]
  25.         export_options
  26.   | INTO DUMPFILE 'file_name'
  27.   | INTO var_name [, var_name] ...
  28. }

WHERE

WHERE 是将查询出来的结果，通过 WHERE 后面的条件（condition），对结果进行过滤。

  1. mysql> select * from employees  where emp_no > 30000  emp_no limit 4; -- 不加order by的limit是不确定的SQL
  2. +--------+------------+------------+-----------+--------+------------+
  3. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  4. +--------+------------+------------+-----------+--------+------------+
  5. |  30001 | 1953-03-27 | Izaskun    | Morton    | M      | 1988-05-21 |
  6. |  30002 | 1960-08-23 | Branimir   | Snedden   | M      | 1998-09-24 |
  7. |  30003 | 1952-11-25 | Takahito   | Vilarrasa | M      | 1990-08-22 |
  8. |  30004 | 1957-11-26 | Lucian     | Penttonen | F      | 1992-10-08 |
  9. +--------+------------+------------+-----------+--------+------------+
  10. 4 rows in set (0.00 sec)
  12. mysql> select * from employees  where emp_no > 40000  order by emp_no limit 4; 
  13. +--------+------------+------------+-----------+--------+------------+
  14. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  15. +--------+------------+------------+-----------+--------+------------+
  16. |  40001 | 1956-03-28 | Akemi      | Maliniak  | F      | 1987-08-06 |
  17. |  40002 | 1960-03-15 | Nakhoon    | Badr      | M      | 1990-02-13 |
  18. |  40003 | 1960-01-26 | Jacopo     | Marshall  | F      | 1991-09-30 |
  19. |  40004 | 1955-09-09 | Anneke     | Stiles    | F      | 1986-03-05 |
  20. +--------+------------+------------+-----------+--------+------------+
  21. 4 rows in set (0.02 sec)
  23. mysql> select * from employees  
  24.     -> where emp_no > 40000
  25.     -> and hire_date > '1991-01-01'   -- 可以用 and 进行 逻辑与 
  26.     -> order by emp_no limit 4;
  27. +--------+------------+------------+------------+--------+------------+
  28. | emp_no | birth_date | first_name | last_name  | gender | hire_date  |
  29. +--------+------------+------------+------------+--------+------------+
  30. |  40003 | 1960-01-26 | Jacopo     | Marshall   | F      | 1991-09-30 |
  31. |  40005 | 1961-02-27 | Zsolt      | Fairtlough | F      | 1991-07-08 |
  32. |  40012 | 1955-02-07 | Chinhyun   | Ozeri      | F      | 1995-08-12 |
  33. |  40015 | 1964-10-08 | Ioana      | Lemarechal | M      | 1997-08-07 |
  34. +--------+------------+------------+------------+--------+------------+
  35. 4 rows in set (0.00 sec)
  37. mysql> select * from employees
  38.     -> where (emp_no > 40000 and birth_date > '1961-01-01') -- 使用()明确条件的逻辑规则
  39.     ->    or (emp_no > 40000 and hire_date > '1991-01-01')  -- 可以使用 or 做 逻辑或
  40.     -> order by emp_no limit 5;
  41. +--------+------------+------------+------------+--------+------------+
  42. | emp_no | birth_date | first_name | last_name  | gender | hire_date  |
  43. +--------+------------+------------+------------+--------+------------+
  44. |  40003 | 1960-01-26 | Jacopo     | Marshall   | F      | 1991-09-30 |
  45. |  40005 | 1961-02-27 | Zsolt      | Fairtlough | F      | 1991-07-08 |
  46. |  40006 | 1962-11-07 | Basim      | Panienski  | F      | 1986-12-27 |
  47. |  40012 | 1955-02-07 | Chinhyun   | Ozeri      | F      | 1995-08-12 |
  48. |  40015 | 1964-10-08 | Ioana      | Lemarechal | M      | 1997-08-07 |
  49. +--------+------------+------------+------------+--------+------------+
  50. 5 rows in set (0.00 sec)

JOIN

image.png

INNER JOIN

  1. --
  2. -- ANSI SQL 89
  3. -- 关联employees表和titles表
  4. -- 要求是 employees的emp_no 等于 titles的emp_no
  5. --
  6. mysql> select * from employees,titles where employees.emp_no = titles.emp_no limit 5;
  7. +--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+
  8. | emp_no | birth_date | first_name | last_name | gender | hire_date  | emp_no | title           | from_date  | to_date    |
  9. +--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+
  10. |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |  10001 | Senior Engineer | 1986-06-26 | 9999-01-01 |
  11. |  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |  10002 | Staff           | 1996-08-03 | 9999-01-01 |
  12. |  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |  10003 | Senior Engineer | 1995-12-03 | 9999-01-01 |
  13. |  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |  10004 | Engineer        | 1986-12-01 | 1995-12-01 |
  14. |  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |  10004 | Senior Engineer | 1995-12-01 | 9999-01-01 |
  15. +--------+------------+------------+-----------+--------+------------+--------+-----------------+------------+------------+
  16. 5 rows in set (0.00 sec)
  18. --
  19. -- 在上面的基础上只显示emp_no，名字，性别和职位名称
  20. --
  21. mysql> select emp_no, concat(last_name,' ', first_name), gender, title 
  22.     -> from employees,titles
  23.     -> where employees.emp_no = titles.emp_no limit 5;
  24. ERROR 1052 (23000): Column 'emp_no' in field list is ambiguous  -- 报错了，原因是emp_no两个表都有
  26. mysql> select employees.emp_no, -- 指定了employees
  27.     -> concat(last_name,' ', first_name), gender, title
  28.     -> from employees,titles
  29.     -> where employees.emp_no = titles.emp_no limit 5;
  30. +--------+-----------------------------------+--------+-----------------+
  31. | emp_no | concat(last_name,' ', first_name) | gender | title           |
  32. +--------+-----------------------------------+--------+-----------------+
  33. |  10001 | Facello Georgi                    | M      | Senior Engineer |
  34. |  10002 | Simmel Bezalel                    | F      | Staff           |
  35. |  10003 | Bamford Parto                     | M      | Senior Engineer |
  36. |  10004 | Koblick Chirstian                 | M      | Engineer        |
  37. |  10004 | Koblick Chirstian                 | M      | Senior Engineer |
  38. +--------+-----------------------------------+--------+-----------------+    
  40. mysql> select employees.emp_no,
  41.     -> concat(last_name,' ', first_name) as emp_name, gender, title  -- 对名字的列取一个别名叫emp_name
  42.     -> from employees,titles
  43.     -> where employees.emp_no = titles.emp_no limit 5;
  44. +--------+-------------------+--------+-----------------+
  45. | emp_no | emp_name          | gender | title           |  -- 这里就显示了emp_name
  46. +--------+-------------------+--------+-----------------+
  47. |  10001 | Facello Georgi    | M      | Senior Engineer |
  48. |  10002 | Simmel Bezalel    | F      | Staff           |
  49. |  10003 | Bamford Parto     | M      | Senior Engineer |
  50. |  10004 | Koblick Chirstian | M      | Engineer        |
  51. |  10004 | Koblick Chirstian | M      | Senior Engineer |
  52. +--------+-------------------+--------+-----------------+
  53. 5 rows in set (0.00 sec)
  55. mysql> select e.emp_no,  -- 使用表的别名
  56.     -> concat(last_name,' ', first_name) as emp_name, gender, title
  57.     -> from employees as e,titles as t      -- 对表做别名
  58.     -> where e.emp_no = t.emp_no limit 5;   -- 使用报表的别名
  59. +--------+-------------------+--------+-----------------+
  60. | emp_no | emp_name          | gender | title           |
  61. +--------+-------------------+--------+-----------------+
  62. |  10001 | Facello Georgi    | M      | Senior Engineer |
  63. |  10002 | Simmel Bezalel    | F      | Staff           |
  64. |  10003 | Bamford Parto     | M      | Senior Engineer |
  65. |  10004 | Koblick Chirstian | M      | Engineer        |
  66. |  10004 | Koblick Chirstian | M      | Senior Engineer |
  67. +--------+-------------------+--------+-----------------+
  68. 5 rows in set (0.00 sec)
  70. --
  71. -- ANSI SQL 92
  72. -- inner join ... on ...语法
  73. --
  74. mysql> select e.emp_no,
  75.     -> concat(last_name,' ', first_name) as emp_name, gender, title
  76.     -> from employees as e inner join titles as t  -- inner join 可以省略inner关键字
  77.     -> on e.emp_no = t.emp_no limit 5;             -- 配合join使用on
  78. +--------+-------------------+--------+-----------------+
  79. | emp_no | emp_name          | gender | title           |
  80. +--------+-------------------+--------+-----------------+
  81. |  10001 | Facello Georgi    | M      | Senior Engineer |
  82. |  10002 | Simmel Bezalel    | F      | Staff           |
  83. |  10003 | Bamford Parto     | M      | Senior Engineer |
  84. |  10004 | Koblick Chirstian | M      | Engineer        |
  85. |  10004 | Koblick Chirstian | M      | Senior Engineer |
  86. +--------+-------------------+--------+-----------------+
  87. 5 rows in set (0.00 sec)
  89. --
  90. -- 上面两种语句在效率上其实是一样的，只是语法上的区别
  91. --
  92. --- 第一种
  93. mysql> explain select e.emp_no,
  94.     -> concat(last_name,' ', first_name) as emp_name, gender, title
  95.     -> from employees as e,titles as t
  96.     -> where e.emp_no = t.emp_no limit 5;
  97. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
  98. | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |
  99. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
  100. |  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL               | 298124 |   100.00 | NULL        |
  101. |  1 | SIMPLE      | t     | NULL       | ref  | PRIMARY       | PRIMARY | 4       | employees.e.emp_no |      1 |   100.00 | Using index |
  102. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
  103. 2 rows in set, 1 warning (0.00 sec)
  105. --- 第二种
  106. mysql> explain select e.emp_no,
  107.     -> concat(last_name,' ', first_name) as emp_name, gender, title
  108.     -> from employees as e inner join titles as t
  109.     -> on e.emp_no = t.emp_no limit 5;
  110. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
  111. | id | select_type | table | partitions | type | possible_keys | key     | key_len | ref                | rows   | filtered | Extra       |
  112. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
  113. |  1 | SIMPLE      | e     | NULL       | ALL  | PRIMARY       | NULL    | NULL    | NULL               | 298124 |   100.00 | NULL        |
  114. |  1 | SIMPLE      | t     | NULL       | ref  | PRIMARY       | PRIMARY | 4       | employees.e.emp_no |      1 |   100.00 | Using index |
  115. +----+-------------+-------+------------+------+---------------+---------+---------+--------------------+--------+----------+-------------+
  116. 2 rows in set, 1 warning (0.00 sec)
  118. -- 通过explain看两条语句的执行计划，发现是一样的，所以性能上是一样的，只是语法的不同

OUTER JOIN

  1. --
  2. -- 左连接 left join
  3. --
  4. mysql> use burn_test
  5. Reading table information for completion of table and column names
  6. You can turn off this feature to get a quicker startup with -A
  8. Database changed
  9. mysql> create table test_left_join_1(a int);
  10. Query OK, 0 rows affected (0.16 sec)
  12. mysql> create table test_left_join_2(b int); 
  13. Query OK, 0 rows affected (0.14 sec)
  15. mysql> insert into test_left_join_1 values (1);
  16. Query OK, 1 row affected (0.03 sec)
  18. mysql> insert into test_left_join_1 values (2);
  19. Query OK, 1 row affected (0.03 sec)
  21. mysql> insert into test_left_join_2 values (1); 
  22. Query OK, 1 row affected (0.03 sec)
  24. mysql> select * from test_left_join_1;
  25. +------+
  26. | a    |
  27. +------+
  28. |    1 |
  29. |    2 |
  30. +------+
  31. 2 rows in set (0.00 sec)
  33. mysql> select * from test_left_join_2;
  34. +------+
  35. | b    |
  36. +------+
  37. |    1 |
  38. +------+
  39. 1 row in set (0.00 sec)
  41. mysql> select * from 
  42.     -> test_left_join_1 as t1 
  43.     -> left join   -- 使用left join
  44.     -> test_left_join_2 as t2 
  45.     -> on t1.a = t2.b;
  46. +------+------+
  47. | a    | b    |
  48. +------+------+
  49. |    1 |    1 |  -- 满足条件的，显示t2中该条记录的值
  50. |    2 | NULL |  -- 不满足条件的，用NULL填充
  51. +------+------+
  52. 2 rows in set (0.00 sec)
  53. -- left join ： 左表 left join 右表 on 条件；
  54. --              左表全部显示，右表是匹配表，
  55. --              如果右表的某条记录满足 [on 条件] 匹配，则该记录显示
  56. --              如果右表的某条记录 不 满足 匹配，则该记录显示NULL
  58. --
  59. -- 右连接 right join （继续使用test_left_join_1和2两张表）
  60. --
  61. mysql> select * from
  62.     -> test_left_join_1 as t1
  63.     -> right join   -- 使用right join
  64.     -> test_left_join_2 as t2
  65.     -> on t1.a = t2.b;
  66. +------+------+
  67. | a    | b    |
  68. +------+------+
  69. |    1 |    1 |   -- 右表（t2）全部显示
  70. +------+------+
  71. 1 row in set (0.00 sec)
  72. -- right join ： 左表 right join 右表 on 条件
  73. --               右表全部显示，左边是匹配表
  74. --               同样和left join，满足则显示，不满足且右表中有值，则填充NULL
  76. mysql> insert into test_left_join_2 values (3);  -- t2 中再增加一条记录
  77. Query OK, 1 row affected (0.03 sec)
  79. mysql> select * from
  80.     -> test_left_join_1 as t1
  81.     -> right join
  82.     -> test_left_join_2 as t2
  83.     -> on t1.a = t2.b;
  84. +------+------+
  85. | a    | b    |
  86. +------+------+
  87. |    1 |    1 |
  88. | NULL |    3 | -- 右表存在，左表没有，用NULL填充
  89. +------+------+
  90. 2 rows in set (0.00 sec)
  92. --
  93. -- 查找在t1表，而不在t2表的数据
  94. --
  95. mysql> select * from 
  96.     -> test_left_join_1 as t1 
  97.     -> left join
  98.     -> test_left_join_2 as t2 
  99.     -> on t1.a = t2.b where t2.b is null;
  100. +------+------+
  101. | a    | b    |
  102. +------+------+
  103. |    2 | NULL |  -- 数据1 在t1和t2中都有，所以不显示
  104. +------+------+
  105. 1 row in set (0.00 sec)
  108. -- left join ： left outer join , outer关键字可以省略
  109. -- right join： right outer join , outer 关键字可以省略
  111. -- join无论inner还是outer，列名不需要一样，甚至列的类型也可以不一样，会进行转换。
  112. -- 一般情况下，表设计合理，需要关联的字段类型应该是一样的
  114. --
  115. -- 查找哪些员工不是经理
  116. --
  117. mysql> select e.emp_no,
  118.     -> concat(last_name,' ', first_name) as emp_name, gender, d.dept_no
  119.     -> from employees as e left join dept_manager as d
  120.     -> on e.emp_no = d.emp_no
  121.     -> where d.emp_no is null limit 5;
  122. +--------+-------------------+--------+---------+
  123. | emp_no | emp_name          | gender | dept_no | -- dept_no是dept_manager的字段
  124. +--------+-------------------+--------+---------+
  125. |  10001 | Facello Georgi    | M      | NULL    |
  126. |  10002 | Simmel Bezalel    | F      | NULL    |
  127. |  10003 | Bamford Parto     | M      | NULL    |
  128. |  10004 | Koblick Chirstian | M      | NULL    |
  129. |  10005 | Maliniak Kyoichi  | M      | NULL    |
  130. +--------+-------------------+--------+---------+
  131. 5 rows in set (0.00 sec)
  133. -- 在 inner join中，过滤条件放在where或者on中都是可以的
  134. -- 在 outer join中 条件放在where和on中是不一样的
  135. mysql> select * from 
  136.     -> test_left_join_1 as t1
  137.     -> left join
  138.     -> test_left_join_2 as t2
  139.     -> on t1.a = t2.b
  140.     -> where t2.b is null;
  141. +------+------+
  142. | a    | b    |
  143. +------+------+
  144. |    2 | NULL |
  145. +------+------+
  146. 1 row in set (0.00 sec)
  148. mysql> select * from 
  149.     -> test_left_join_1 as t1
  150.     -> left join
  151.     -> test_left_join_2 as t2
  152.     -> on t1.a = t2.b
  153.     -> and t2.b is null;  -- 除了a=b, 还要找到b=null的，但是b里面没有null，所有a全部显示，b全为null
  154. +------+------+
  155. | a    | b    |
  156. +------+------+
  157. |    1 | NULL |
  158. |    2 | NULL |
  159. +------+------+
  160. 2 rows in set (0.00 sec)
  162. -- ON 参与outer join的结果的生成，而where只是对结果的一个过滤

GROUP BY

  1. --
  2. -- 找出同一个部门的员工数量
  3. --
  4. mysql> select dept_no, count(dept_no)  -- count是得到数量，这里就是分组函数
  5.     -> from dept_emp
  6.     -> group by dept_no;  -- 通过 dept_no 分组
  7. +---------+----------------+
  8. | dept_no | count(dept_no) |
  9. +---------+----------------+
  10. | d001    |          20211 |
  11. | d002    |          17346 |
  12. | d003    |          17786 |
  13. | d004    |          73485 |
  14. | d005    |          85707 |
  15. | d006    |          20117 |
  16. | d007    |          52245 |
  17. | d008    |          21126 |
  18. | d009    |          23580 |
  19. +---------+----------------+
  20. 9 rows in set (0.10 sec)
  22. --
  23. -- 选出部门人数 > 50000 
  24. -- 
  25. mysql> select dept_no, count(dept_no)
  26.     -> from dept_emp
  27.     -> group by dept_no
  28.     -> having count(dept_no) > 50000;  -- 如果是对分组的聚合函数做过滤，使用having，用where报语法错误
  29. +---------+----------------+
  30. | dept_no | count(dept_no) |
  31. +---------+----------------+
  32. | d004    |          73485 |
  33. | d005    |          85707 |
  34. | d007    |          52245 |
  35. +---------+----------------+
  36. 3 rows in set (0.09 sec)
  38. --
  39. -- 每个用户每个月产生的订单数目
  40. --
  41. mysql> desc orders;
  42. +-----------------+-------------+------+-----+---------+-------+
  43. | Field           | Type        | Null | Key | Default | Extra |
  44. +-----------------+-------------+------+-----+---------+-------+
  45. | o_orderkey      | int(11)     | NO   | PRI | NULL    |       |  -- 订单ID
  46. | o_custkey       | int(11)     | YES  | MUL | NULL    |       |  -- 客户ID
  47. | o_orderstatus   | char(1)     | YES  |     | NULL    |       |
  48. | o_totalprice    | double      | YES  |     | NULL    |       |
  49. | o_orderDATE     | date        | YES  | MUL | NULL    |       |  -- 订单日期
  50. | o_orderpriority | char(15)    | YES  |     | NULL    |       |
  51. | o_clerk         | char(15)    | YES  |     | NULL    |       |
  52. | o_shippriority  | int(11)     | YES  |     | NULL    |       |
  53. | o_comment       | varchar(79) | YES  |     | NULL    |       |
  54. +-----------------+-------------+------+-----+---------+-------+
  55. 9 rows in set (0.00 sec)
  57. mysql> select  o_orderkey, o_custkey, o_orderDATE from orders limit 3; 
  58. +------------+-----------+-------------+
  59. | o_orderkey | o_custkey | o_orderDATE |
  60. +------------+-----------+-------------+
  61. |          1 |     36901 | 1996-01-02  |
  62. |          2 |     78002 | 1996-12-01  |
  63. |          3 |    123314 | 1993-10-14  |
  64. +------------+-----------+-------------+
  65. 3 rows in set (0.00 sec)
  67. --
  68. -- 查找客户每年每月产生的订单数
  69. --
  70. mysql> select o_custkey, count(o_orderkey),                                                                  -> year(o_orderDATE), month(o_orderDATE)
  71.     -> from orders
  72.     -> group by o_custkey, year(o_orderDATE), month(o_orderDATE)
  73.     -> limit 10;
  74. +-----------+-------------------+-------------------+--------------------+
  75. | o_custkey | count(o_orderkey) | year(o_orderDATE) | month(o_orderDATE) |
  76. +-----------+-------------------+-------------------+--------------------+
  77. |         1 |                 1 |              1992 |                  4 |
  78. |         1 |                 1 |              1992 |                  8 |
  79. |         1 |                 1 |              1996 |                  6 |
  80. |         1 |                 1 |              1996 |                  7 |
  81. |         1 |                 1 |              1996 |                 12 |
  82. |         1 |                 1 |              1997 |                  3 |
  83. |         2 |                 1 |              1992 |                  4 |
  84. |         2 |                 1 |              1994 |                  5 |
  85. |         2 |                 1 |              1994 |                  8 |
  86. |         2 |                 1 |              1994 |                 12 |
  87. +-----------+-------------------+-------------------+--------------------+
  88. 10 rows in set (8.97 sec)
  90. -- 使用 date_format 函数
  91. mysql> select o_custkey, count(o_orderkey),
  92.     -> date_format(o_orderDATE, '%Y-%m') as date
  93.     -> from orders
  94.     -> group by o_custkey, date_format(o_orderDATE, '%Y-%m')
  95.     -> limit 10;
  96. +-----------+-------------------+---------+
  97. | o_custkey | count(o_orderkey) | date    |
  98. +-----------+-------------------+---------+
  99. |         1 |                 1 | 1992-04 |
  100. |         1 |                 1 | 1992-08 |
  101. |         1 |                 1 | 1996-06 |
  102. |         1 |                 1 | 1996-07 |
  103. |         1 |                 1 | 1996-12 |
  104. |         1 |                 1 | 1997-03 |
  105. |         2 |                 1 | 1992-04 |
  106. |         2 |                 1 | 1994-05 |
  107. |         2 |                 1 | 1994-08 |
  108. |         2 |                 1 | 1994-12 |
  109. +-----------+-------------------+---------+
  110. 10 rows in set (11.46 sec)

LIMIT & ORDER BY

  1. mysql> select * from employees limit 1;  -- 从employees中 随机 取出一条数据，结果是不确定的
  2. +--------+------------+------------+-----------+--------+------------+
  3. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  4. +--------+------------+------------+-----------+--------+------------+
  5. |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
  6. +--------+------------+------------+-----------+--------+------------+
  7. 1 row in set (0.00 sec)
  9. --
  10. -- order by col_name 根据某列的值进行排序
  11. -- asc ： 升序(default)
  12. -- desc： 降序
  13. --
  14. mysql> select * from employees order by emp_no asc limit 1;   -- 使用order by col_name asc进行升序排序
  15. +--------+------------+------------+-----------+--------+------------+
  16. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  17. +--------+------------+------------+-----------+--------+------------+
  18. |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
  19. +--------+------------+------------+-----------+--------+------------+
  20. 1 row in set (0.00 sec)
  22. mysql> select * from employees order by  emp_no limit 1;       -- 默认就是升序的
  23. +--------+------------+------------+-----------+--------+------------+
  24. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  25. +--------+------------+------------+-----------+--------+------------+
  26. |  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |  -- 结果和上面一致
  27. +--------+------------+------------+-----------+--------+------------+
  28. 1 row in set (0.00 sec)
  30. mysql> select * from employees order by emp_no desc limit 1;    -- desc表示降序
  31. +--------+------------+------------+-----------+--------+------------+
  32. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  33. +--------+------------+------------+-----------+--------+------------+
  34. | 499999 | 1958-05-01 | Sachin     | Tsukuda   | M      | 1997-11-30 |  -- 降序显示
  35. +--------+------------+------------+-----------+--------+------------+
  36. -- 通过order by排序后 limit 1 才是确定的
  37. 1 row in set (0.00 sec)
  39. mysql> show create table employees\G
  40. *************************** 1. row ***************************
  41.        Table: employees
  42. Create Table: CREATE TABLE `employees` (
  43.   `emp_no` int(11) NOT NULL,
  44.   `birth_date` date NOT NULL,
  45.   `first_name` varchar(14) NOT NULL,
  46.   `last_name` varchar(16) NOT NULL,
  47.   `gender` enum('M','F') NOT NULL,
  48.   `hire_date` date NOT NULL,
  49.   PRIMARY KEY (`emp_no`)    -- emp_no 是主键，order by 主键 不会创建临时表的，主键(索引)本身有序
  50. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
  51. 1 row in set (0.00 sec)
  53. mysql> select * from employees order by emp_no asc limit 5,5;  -- limit start, offset
  54.                                                                -- 从第5条 开始取，取5条出来
  55. +--------+------------+------------+-----------+--------+------------+
  56. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  57. +--------+------------+------------+-----------+--------+------------+
  58. |  10006 | 1953-04-20 | Anneke     | Preusig   | F      | 1989-06-02 |
  59. |  10007 | 1957-05-23 | Tzvetan    | Zielinski | F      | 1989-02-10 |
  60. |  10008 | 1958-02-19 | Saniya     | Kalloufi  | M      | 1994-09-15 |
  61. |  10009 | 1952-04-19 | Sumant     | Peac      | F      | 1985-02-18 |
  62. |  10010 | 1963-06-01 | Duangkaew  | Piveteau  | F      | 1989-08-24 |
  63. +--------+------------+------------+-----------+--------+------------+
  64. 5 rows in set (0.00 sec)
  66. -- 以上这个语法有一种分页的效果，但是会随着start的增加，性能会下降，因为会扫描表(从 1 到 start)
  68. -- 相对比较推荐的方法
  69. mysql> select * from employees where emp_no > 20000 order by emp_no limit 5;
  70. +--------+------------+------------+-----------+--------+------------+
  71. | emp_no | birth_date | first_name | last_name | gender | hire_date  |
  72. +--------+------------+------------+-----------+--------+------------+
  73. |  20001 | 1962-05-16 | Atreye     | Eppinger  | M      | 1990-04-18 |
  74. |  20002 | 1955-12-25 | Jaber      | Brender   | M      | 1988-01-26 |
  75. |  20003 | 1953-04-11 | Munehiko   | Coors     | F      | 1991-02-07 |
  76. |  20004 | 1952-03-07 | Radoslaw   | Pfau      | M      | 1995-11-24 |
  77. |  20005 | 1956-02-20 | Licheng    | Przulj    | M      | 1992-07-17 |
  78. +--------+------------+------------+-----------+--------+------------+
  79. 5 rows in set (0.00 sec)
  80. -- （当然推荐把热数据放cache里,比如Redis）

ORDER BY 是把已经查询好的结果集进行排序。

UNION

官方文档：https://dev.mysql.com/doc/refman/5.7/en/union.html

UNION 的作用是将两个查询的结果集进行合并。
UNION 必须由两条或两条以上的 SELECT 语句组成，语句之间用关键字 UNION 分隔。
UNION 中的每个查询必须包含相同的列（类型相同或可以隐式转换）、表达式或聚集函数。

  1. mysql> create table test_union_1(a int, b int);
  2. Query OK, 0 rows affected (0.18 sec)
  4. mysql> create table test_union_2(a int, c int);  
  5. Query OK, 0 rows affected (0.15 sec)
  7. mysql> insert into test_union_1 values(1, 2), (3, 4), (5, 6), (10, 20);
  8. Query OK, 4 rows affected (0.06 sec)
  9. Records: 4  Duplicates: 0  Warnings: 0
  11. mysql> insert into test_union_2 values(10, 20), (30, 40), (50, 60);  
  12. Query OK, 3 rows affected (0.03 sec)
  13. Records: 3  Duplicates: 0  Warnings: 0
  15. mysql> select * from test_union_1;
  16. +------+------+
  17. | a    | b    |
  18. +------+------+
  19. |    1 |    2 |
  20. |    3 |    4 |
  21. |    5 |    6 |
  22. |   10 |   20 |  -- test_union_1 中的10, 20
  23. +------+------+
  24. 4 rows in set (0.00 sec)
  26. mysql> select * from test_union_2;
  27. +------+------+
  28. | a    | c    |
  29. +------+------+
  30. |   10 |   20 | -- test_union_2 中的10, 20
  31. |   30 |   40 |
  32. |   50 |   60 |  
  33. +------+------+
  34. 3 rows in set (0.00 sec)
  36. mysql> select a, b as t from test_union_1
  37.     -> union
  38.     -> select * from test_union_2;
  39. +------+------+
  40. | a    | t    |
  41. +------+------+
  42. |    1 |    2 |
  43. |    3 |    4 |
  44. |    5 |    6 |
  45. |   10 |   20 | -- 只出现了一次 10, 20，union会去重
  46. |   30 |   40 |
  47. |   50 |   60 |
  48. +------+------+
  49. 6 rows in set (0.00 sec)
  51. mysql> select a, b as t from test_union_1
  52.     -> union all   -- 使用 union all 可以不去重
  53.     -> select * from test_union_2;
  54. +------+------+
  55. | a    | t    |
  56. +------+------+
  57. |    1 |    2 |
  58. |    3 |    4 |
  59. |    5 |    6 |
  60. |   10 |   20 | -- test_union_1 中的10, 20
  61. |   10 |   20 | -- test_union_2 中的10, 20
  62. |   30 |   40 |
  63. |   50 |   60 |
  64. +------+------+
  65. 7 rows in set (0.00 sec)
  67. mysql> select a, b as t from test_union_1 where a > 2 
  68.     -> union
  69.     -> select * from test_union_2 where c > 50;  -- 使用where过滤也可以 
  70. +------+------+
  71. | a    | t    |
  72. +------+------+
  73. |    3 |    4 |
  74. |    5 |    6 |
  75. |   10 |   20 |
  76. |   50 |   60 |
  77. +------+------+
  78. 4 rows in set (0.00 sec)

:::warning 如果知道数据本身具有唯一性，没有重复，则建议使用 union all，因为 union 会做去重操作，性能会比 union all 要低。 :::

作者：殷建卫 链接：https://www.yuque.com/yinjianwei/vyrvkf/vs0y6s 来源：殷建卫 - 架构笔记 著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。