MySQL-6.查询语句

本文地址：https://www.jianshu.com/p/8341e77f34e2

一、简介

在MySQL中，查询语句SELECT应该是使用频率最高的语句了，在一般的应用之中，数据库的读写比例大概能达到10：1。

由于其子句较多，且功能繁杂，所以语法相对较为复杂。

二、SELECT语法

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      SQL_NO_CACHE [SQL_CALC_FOUND_ROWS]

    select_expr [, select_expr ...]

    [FROM table_references
      [PARTITION partition_list]
      [WHERE where_condition]
      [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]

    [HAVING where_condition]
    [WINDOW window_name AS (window_spec)
        [, window_name AS (window_spec)] ...]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR {UPDATE | SHARE} [OF tbl_name [, tbl_name] ...] [NOWAIT | SKIP LOCKED]
      | LOCK IN SHARE MODE]]

常用基础关键词

• select_expr [, select_expr ...]

  每一条select_expr语句代表你想要从表中取出的字段值，这是必备的关键词，也就是你至少要写一条select_expr，如果要写多条select_expr的话，应该用,进行分割。

  并且select_expr可以使用*通配符来进行匹配，例如以下语句将会取出表中的所有字段。

  SELECT * FROM tb_name;
  

• FROM table_references

  table_references指的是你想要提取的一张或多张表的表名。

  SELECT col_name1, col_name2 FROM tb_name;
  

  如果对table_references指定了多个表名，那么意味着你在使用JOIN连接。

  • [PARTITION partition_list]

    在FROM语句中，可以使用PARTITION子句来指定查询的分区表。指定之后，查询将只从列出的分区表中查询数据。

  • [WHERE where_condition]

    FROM语句中，还有WHERE子句，可以指定查找符合where_condition表达式的数据。如果没有指定WHERE子句，那么将会把表中所有行的数据查询出来。

    例子：

    SELECT col_name1 FROM tb_name WHERE col_name2=1;
    

    在WHERE的where_condition表达式中，可以使用任何MySQL支持的函数和运算符，聚合算法除外。

  • [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]

    FROM中还有一个GROUP BY语句，用以分组。

• [ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]

  排序子句，用于将查询到的数据按一定顺序进行排列，ORDER BY可以通过列名或者列的别名来进行排序。例子如下所示：

  SELECT college, region, seed FROM tournament
    ORDER BY region, seed;
  
  SELECT college, region AS r, seed AS s FROM tournament
    ORDER BY r, s;
  

  如果需要改变排序方式为降序的话，那么可以在ORDER BY语句后加上DESC(descending)，默认的排序方式是ASC(ascending)。

  SELECT college, region, seed FROM tournament
    ORDER BY region, seed DESC;
  

• [LIMIT {[offset,] row_count | row_count OFFSET offset}]

  LIMIT子句主要用来限制查询结果的数量。LIMIT一般需要一个或两个非负整数参数来决定限制的范围和位置。

  • 当只有一个参数时，参数表示的是从查询结果的第一行开始返回的行数。例：

    SELECT * FROM tb_name LIMIT 5;     # 返回前五行
    

  • 当传入两个参数时，第一个参数表示相对于结果第一行的偏移行数，第二个参数表示的是返回的行数。例：

    SELECT * FROM tb_name LIMIT 5,10;  # 返回第6-15行
    

  • 如果你希望从某一行开始，返回这一行之后所有的结果，那么你可以把第二个参数设置的非常大。例：

    SELECT * FROM tbl LIMIT 95,18446744073709551615;   #返回从96行开始所有的结果
    

修饰符

跟在SELECT后有许多可以影响查询结果的修饰符可以使用，例如HIGH_PRIORITY、STRAIGHT_JOIN等。

• ALL、DISTINCT

  这个修饰符指的是是否返回重复的查询结果。

  • ALL(默认)指的是，只要符合查询结果，就算有数据是重复的也全部返回。

  • DISTINCT指的是，如果查询结果中有重复的行，那么那些重复的数据将会被删除。DISTINCTROW是DISTINCT的同义词。

• HIGH_PRIORITY

  HIGH_PRIORITY能让SELECT语句拥有比UPDATE更高的优先级。

• STARTGHT_JOIN

  STARTGHT_JOIN会强制让优化器按照FROM字句中列出的顺序进行连接。

• SQL_SMALL_RESULT或SQL_BIG_RESULT

  这个修饰符可以与GROUP BY或者DISTINCT一起使用，来告诉优化器搜索的结果非常多还是非常少。如果使用SQL_BIG_RESULT，那么MySQL会直接使用基于磁盘的临时表来存储搜索结果，如果使用的是SQL_SMALL_RESULT，那么MySQL会使用基于内存的临时表来存储搜索结果。

注：需要注意的是，SELECT子句的顺序都需要按照语法中给定的顺序来进行使用，INTO子句例外，它可以跟在select_expr列表后方。

---

三、聚合分组

聚合函数

在MySQL中有一些对值的集合进行操作的函数可以使用，这被称为聚合函数，以下是常见的聚合函数：

聚合函数	描述
AVG()	求平均值函数
COUNT()	返回参数的行数
COUNT(DISTINCT)	返回去重之后的行数
GROUP_CONCAT()	返回所有值拼接成的字符串
MAX()	求最大值
MIN()	求最小值
STD()	求标准差
SUM()	求和函数

除非专门说明，否则这些聚合函数都会忽略列表中的NULL。

• AVG([DISTINCT] expr) [over_clause]

  返回expr的平均值，可以使用DISTINCT将结果先去重后再求平均值。例子：

  mysql> SELECT student_name, AVG(test_score)
         FROM student
         GROUP BY student_name;
  

• COUNT(expr) [over_clause]

  COUNT会返回expr中值不为NULL的行数。但是COUNT(*)有一点特殊，其返回的是提取出来的总行数，不管其是否为NULL。

  mysql> SELECT student.student_name,COUNT(*)
         FROM student,course
         WHERE student.student_id=course.student_id
         GROUP BY student_name;
  

• GROUP_CONCAT(expr)

  这个聚合函数会将expr中所有非NULL值拼接成一个字符串并返回。

  mysql> SELECT student_name,
         GROUP_CONCAT(test_score)
         FROM student
         GROUP BY student_name;
  

分组查询(GROUP BY)

GROUP BY是SELECT语句中的子句，其可以指定字段，将这个字段中值相同的行分为一组，所以这个字段基本上都与聚合函数一起使用 ，能够根据不同条件统计数据。例如：

mysql> SELECT class, COUNT(name)
       FROM student
       GROUP BY class;

此例子会将学生通过班级分组，并统计出每个班级的人数。

需要注意的是，在使用了GROUP BY分组之后，那么前面SELECT查询的字段就只能使用分组的字段和聚合函数了，使用其他字段将会报错。

---

四、连接查询(JOIN)

在MySQL中，支持在SELECT、DELETE和UPDATE的多表中使用JOIN语法。JOIN也被称为连接表达式，而连结又主要分为内连接以及外连接。

JOIN语法可以把两张不同的表按一定的条件进行拼接。

我将会使用以下两张表作为例子进行演示：

mysql> SELECT * FROM a;
+------+-------+
| id   | name  |
+------+-------+
|    1 | Emma  |
|    2 | Jason |
+------+-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM b;
+------+------+
| id   | sex  |
+------+------+
|    1 | F    |
|    3 | M    |
+------+------+
2 rows in set (0.00 sec)

内连接

MySQL中的JOIN，CROSS JOIN，INNER JOIN三者是等价的，都被视为内连接。在直接使用的时候，又被称为无条件内连接或笛卡尔连接，其会把两张表里的数据完全相互连接，形成M * N条数据。

例子：

mysql> SELECT * FROM a JOIN b;
+------+-------+------+------+
| id   | name  | id   | sex  |
+------+-------+------+------+
|    1 | Emma  |    1 | F    |
|    2 | Jason |    1 | F    |
|    1 | Emma  |    3 | M    |
|    2 | Jason |    3 | M    |
+------+-------+------+------+
4 rows in set (0.00 sec)

可以看到数据的连接是完全没有任何联系的，所以如果我们需要通过指定条件来限定连接的方式，就可以使用ON子句来设定连接条件。

这一种有条件的内连接是使用的最多的连接方式。

mysql> SELECT * FROM a JOIN b ON a.id=b.id;
+------+------+------+------+
| id   | name | id   | sex  |
+------+------+------+------+
|    1 | Emma |    1 | F    |
+------+------+------+------+
1 row in set (0.00 sec)

外连接

在内连接中，可以看到如果没有符合ON字句的匹配条件，那么不符合的这些行将会被舍去。但是有一些情况下，我们希望保留下那些没有符合匹配条件的行，这个时候就可以使用外连接。

左外连接

既然是要保留没有符合匹配条件的行，那么肯定是需要一个标准的，也就是保留哪张表的不匹配行。那么左外连接也就意味着将会以JOIN的左表为基准进行保留。

也就是说，在左外连接的过程中，左表中不符合匹配条件的行将会被保存下来，这些行的右表字段将使用NULL来填充。而右表中不匹配的字段将会被舍去。

mysql> SELECT * FROM a LEFT JOIN b ON a.id=b.id;
+------+-------+------+------+
| id   | name  | id   | sex  |
+------+-------+------+------+
|    1 | Emma  |    1 | F    |
|    2 | Jason | NULL | NULL |
+------+-------+------+------+
2 rows in set (0.00 sec)

右外连接

与左外连接相对应，将右表作为基准进行连接。

mysql> SELECT * FROM a RIGHT JOIN b ON a.id=b.id;
+------+------+------+------+
| id   | name | id   | sex  |
+------+------+------+------+
|    1 | Emma |    1 | F    |
| NULL | NULL |    3 | M    |
+------+------+------+------+
2 rows in set (0.00 sec)

---

五、子查询

子查询也就是说把一个查询嵌套在另一个查询中，子查询也被称为内部查询，包含内部查询的则被称为外部查询。

外部查询需要是这些语句之一：SELECT、INSERT、UPDATE、DELETE或DO。

子查询的位置一般会在SELECT中、FROM后、WHERE中。

子查询分类

子查询一般会被分为以下几类：

1. 标量子查询：返回单一值的标量，最简单的形式。

   是指子查询返回的是单一值的标量，如一个数字或一个字符串，也是子查询中最简单的返回形式。 可以使用 = > < >= <= <> 这些操作符对子查询的标量结果进行比较，通常子查询的位置在比较式的右侧。

   SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)
   SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)
   SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)
   

2. 列子查询：返回的结果集是N行一列。

   指子查询返回的结果集是 N 行一列，该结果通常来自对表的某个字段查询返回。可以使用IN、ANY、SOME和ALL操作符，不能直接使用= > < >= <= <> 这些比较标量结果的操作符。

   SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)
   SELECT s1 FROM table1 WHERE s1 > ANY (SELECT s2 FROM table2)
   SELECT s1 FROM table1 WHERE s1 > ALL (SELECT s2 FROM table2)
   

3. 行子查询：返回的结果集是一行N列。

   指子查询返回的结果集是一行N列，该子查询的结果通常是对表的某行数据进行查询而返回的结果集。

   SELECT * FROM table1 WHERE (1,2) = (SELECT column1, column2 FROM table2)
   SELECT * FROM article WHERE (title,content,uid) = (SELECT title,content,uid FROM blog WHERE bid=2)
   

4. 表子查询：返回的结果集是N行N列。

   指子查询返回的结果集是N行N列的一个表数据。

   SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)