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)
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 175,490评论 5赞 419
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 74,060评论 2赞 335
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 124,407评论 0赞 291
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 47,741评论 0赞 248
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 56,543评论 3赞 329
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 43,040评论 1赞 246
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 34,107评论 3赞 358
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 32,646评论 0赞 229
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 36,694评论 1赞 271
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 32,398评论 2赞 279
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 33,987评论 1赞 288
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 30,097评论 3赞 285
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 35,298评论 3赞 282
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 27,278评论 0赞 14
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 28,413评论 1赞 232
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 38,397评论 2赞 309
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 38,099评论 2赞 314