本文地址：http://www.reibang.com/p/8341e77f34e2

一哮内、簡(jiǎn)介

在MySQL中盗棵，查詢語(yǔ)句SELECT應(yīng)該是使用頻率最高的語(yǔ)句了，在一般的應(yīng)用之中牍蜂，數(shù)據(jù)庫(kù)的讀寫(xiě)比例大概能達(dá)到10：1漾根。

由于其子句較多，且功能繁雜鲫竞，所以語(yǔ)法相對(duì)較為復(fù)雜辐怕。

二、SELECT語(yǔ)法

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]]

常用基礎(chǔ)關(guān)鍵詞

• select_expr [, select_expr ...]

  每一條select_expr語(yǔ)句代表你想要從表中取出的字段值从绘，這是必備的關(guān)鍵詞寄疏，也就是你至少要寫(xiě)一條select_expr，如果要寫(xiě)多條select_expr的話僵井，應(yīng)該用,進(jìn)行分割陕截。

  并且select_expr可以使用*通配符來(lái)進(jìn)行匹配，例如以下語(yǔ)句將會(huì)取出表中的所有字段批什。

  SELECT * FROM tb_name;
  

• FROM table_references

  table_references指的是你想要提取的一張或多張表的表名农曲。

  SELECT col_name1, col_name2 FROM tb_name;
  

  如果對(duì)table_references指定了多個(gè)表名，那么意味著你在使用JOIN連接驻债。

  • [PARTITION partition_list]

    在FROM語(yǔ)句中乳规，可以使用PARTITION子句來(lái)指定查詢的分區(qū)表。指定之后合呐，查詢將只從列出的分區(qū)表中查詢數(shù)據(jù)暮的。

  • [WHERE where_condition]

    FROM語(yǔ)句中，還有WHERE子句淌实，可以指定查找符合where_condition表達(dá)式的數(shù)據(jù)冻辩。如果沒(méi)有指定WHERE子句猖腕，那么將會(huì)把表中所有行的數(shù)據(jù)查詢出來(lái)。

    例子：

    SELECT col_name1 FROM tb_name WHERE col_name2=1;
    

    在WHERE的where_condition表達(dá)式中恨闪，可以使用任何MySQL支持的函數(shù)和運(yùn)算符倘感，聚合算法除外。

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

    FROM中還有一個(gè)GROUP BY語(yǔ)句咙咽，用以分組侠仇。

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

  排序子句，用于將查詢到的數(shù)據(jù)按一定順序進(jìn)行排列犁珠，ORDER BY可以通過(guò)列名或者列的別名來(lái)進(jìn)行排序。例子如下所示：

  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語(yǔ)句后加上DESC(descending)犁享，默認(rèn)的排序方式是ASC(ascending)。

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

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

  LIMIT子句主要用來(lái)限制查詢結(jié)果的數(shù)量豹休。LIMIT一般需要一個(gè)或兩個(gè)非負(fù)整數(shù)參數(shù)來(lái)決定限制的范圍和位置炊昆。

  • 當(dāng)只有一個(gè)參數(shù)時(shí)，參數(shù)表示的是從查詢結(jié)果的第一行開(kāi)始返回的行數(shù)威根。例：

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

  • 當(dāng)傳入兩個(gè)參數(shù)時(shí)凤巨，第一個(gè)參數(shù)表示相對(duì)于結(jié)果第一行的偏移行數(shù)，第二個(gè)參數(shù)表示的是返回的行數(shù)洛搀。例：

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

  • 如果你希望從某一行開(kāi)始敢茁，返回這一行之后所有的結(jié)果，那么你可以把第二個(gè)參數(shù)設(shè)置的非常大留美。例：

    SELECT * FROM tbl LIMIT 95,18446744073709551615;   #返回從96行開(kāi)始所有的結(jié)果
    

修飾符

跟在SELECT后有許多可以影響查詢結(jié)果的修飾符可以使用彰檬，例如HIGH_PRIORITY、STRAIGHT_JOIN等谎砾。

• ALL逢倍、DISTINCT

  這個(gè)修飾符指的是是否返回重復(fù)的查詢結(jié)果。

  • ALL(默認(rèn))指的是景图，只要符合查詢結(jié)果较雕，就算有數(shù)據(jù)是重復(fù)的也全部返回。

  • DISTINCT指的是挚币，如果查詢結(jié)果中有重復(fù)的行亮蒋，那么那些重復(fù)的數(shù)據(jù)將會(huì)被刪除。DISTINCTROW是DISTINCT的同義詞忘晤。

• HIGH_PRIORITY

  HIGH_PRIORITY能讓SELECT語(yǔ)句擁有比UPDATE更高的優(yōu)先級(jí)宛蚓。

• STARTGHT_JOIN

  STARTGHT_JOIN會(huì)強(qiáng)制讓優(yōu)化器按照FROM字句中列出的順序進(jìn)行連接。

• SQL_SMALL_RESULT或SQL_BIG_RESULT

  這個(gè)修飾符可以與GROUP BY或者DISTINCT一起使用设塔，來(lái)告訴優(yōu)化器搜索的結(jié)果非常多還是非常少凄吏。如果使用SQL_BIG_RESULT远舅，那么MySQL會(huì)直接使用基于磁盤的臨時(shí)表來(lái)存儲(chǔ)搜索結(jié)果，如果使用的是SQL_SMALL_RESULT痕钢，那么MySQL會(huì)使用基于內(nèi)存的臨時(shí)表來(lái)存儲(chǔ)搜索結(jié)果图柏。

注：需要注意的是，SELECT子句的順序都需要按照語(yǔ)法中給定的順序來(lái)進(jìn)行使用任连，INTO子句例外蚤吹，它可以跟在select_expr列表后方。

三随抠、聚合分組

聚合函數(shù)

在MySQL中有一些對(duì)值的集合進(jìn)行操作的函數(shù)可以使用裁着，這被稱為聚合函數(shù)，以下是常見(jiàn)的聚合函數(shù)：

除非專門說(shuō)明拱她，否則這些聚合函數(shù)都會(huì)忽略列表中的NULL二驰。

• AVG([DISTINCT] expr) [over_clause]

  返回expr的平均值，可以使用DISTINCT將結(jié)果先去重后再求平均值秉沼。例子：

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

• COUNT(expr) [over_clause]

  COUNT會(huì)返回expr中值不為NULL的行數(shù)桶雀。但是COUNT(*)有一點(diǎn)特殊，其返回的是提取出來(lái)的總行數(shù)唬复，不管其是否為NULL矗积。

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

• GROUP_CONCAT(expr)

  這個(gè)聚合函數(shù)會(huì)將expr中所有非NULL值拼接成一個(gè)字符串并返回。

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

分組查詢(GROUP BY)

GROUP BY是SELECT語(yǔ)句中的子句敞咧，其可以指定字段棘捣，將這個(gè)字段中值相同的行分為一組，所以這個(gè)字段基本上都與聚合函數(shù)一起使用 休建，能夠根據(jù)不同條件統(tǒng)計(jì)數(shù)據(jù)柱锹。例如：

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

此例子會(huì)將學(xué)生通過(guò)班級(jí)分組，并統(tǒng)計(jì)出每個(gè)班級(jí)的人數(shù)丰包。

需要注意的是禁熏，在使用了GROUP BY分組之后，那么前面SELECT查詢的字段就只能使用分組的字段和聚合函數(shù)了邑彪，使用其他字段將會(huì)報(bào)錯(cuò)瞧毙。

四、連接查詢(JOIN)

在MySQL中寄症，支持在SELECT宙彪、DELETE和UPDATE的多表中使用JOIN語(yǔ)法。JOIN也被稱為連接表達(dá)式有巧，而連結(jié)又主要分為內(nèi)連接以及外連接释漆。

JOIN語(yǔ)法可以把兩張不同的表按一定的條件進(jìn)行拼接。

我將會(huì)使用以下兩張表作為例子進(jìn)行演示：

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)

內(nèi)連接

MySQL中的JOIN篮迎，CROSS JOIN男图，INNER JOIN三者是等價(jià)的示姿，都被視為內(nèi)連接。在直接使用的時(shí)候逊笆，又被稱為無(wú)條件內(nèi)連接或笛卡爾連接栈戳，其會(huì)把兩張表里的數(shù)據(jù)完全相互連接，形成M * N條數(shù)據(jù)难裆。

例子：

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)

可以看到數(shù)據(jù)的連接是完全沒(méi)有任何聯(lián)系的子檀，所以如果我們需要通過(guò)指定條件來(lái)限定連接的方式，就可以使用ON子句來(lái)設(shè)定連接條件乃戈。

這一種有條件的內(nèi)連接是使用的最多的連接方式褂痰。

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)

外連接

在內(nèi)連接中，可以看到如果沒(méi)有符合ON字句的匹配條件症虑，那么不符合的這些行將會(huì)被舍去脐恩。但是有一些情況下，我們希望保留下那些沒(méi)有符合匹配條件的行侦讨，這個(gè)時(shí)候就可以使用外連接。

左外連接

既然是要保留沒(méi)有符合匹配條件的行苟翻，那么肯定是需要一個(gè)標(biāo)準(zhǔn)的韵卤，也就是保留哪張表的不匹配行。那么左外連接也就意味著將會(huì)以JOIN的左表為基準(zhǔn)進(jìn)行保留崇猫。

也就是說(shuō)沈条，在左外連接的過(guò)程中，左表中不符合匹配條件的行將會(huì)被保存下來(lái)诅炉，這些行的右表字段將使用NULL來(lái)填充蜡歹。而右表中不匹配的字段將會(huì)被舍去。

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)

右外連接

與左外連接相對(duì)應(yīng)涕烧，將右表作為基準(zhǔn)進(jìn)行連接月而。

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)

五、子查詢

子查詢也就是說(shuō)把一個(gè)查詢嵌套在另一個(gè)查詢中议纯，子查詢也被稱為內(nèi)部查詢父款，包含內(nèi)部查詢的則被稱為外部查詢。

外部查詢需要是這些語(yǔ)句之一：SELECT瞻凤、INSERT憨攒、UPDATE、DELETE或DO阀参。

子查詢的位置一般會(huì)在SELECT中肝集、FROM后、WHERE中蛛壳。

子查詢分類

子查詢一般會(huì)被分為以下幾類：

1. 標(biāo)量子查詢：返回單一值的標(biāo)量杏瞻，最簡(jiǎn)單的形式所刀。

   是指子查詢返回的是單一值的標(biāo)量，如一個(gè)數(shù)字或一個(gè)字符串伐憾，也是子查詢中最簡(jiǎn)單的返回形式勉痴。 可以使用 = > < >= <= <> 這些操作符對(duì)子查詢的標(biāo)量結(jié)果進(jìn)行比較，通常子查詢的位置在比較式的右側(cè)树肃。

   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. 列子查詢：返回的結(jié)果集是N行一列蒸矛。

   指子查詢返回的結(jié)果集是 N 行一列，該結(jié)果通常來(lái)自對(duì)表的某個(gè)字段查詢返回胸嘴〕樱可以使用IN、ANY劣像、SOME和ALL操作符乡话，不能直接使用= > < >= <= <> 這些比較標(biāo)量結(jié)果的操作符。

   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. 行子查詢：返回的結(jié)果集是一行N列耳奕。

   指子查詢返回的結(jié)果集是一行N列绑青，該子查詢的結(jié)果通常是對(duì)表的某行數(shù)據(jù)進(jìn)行查詢而返回的結(jié)果集。

   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. 表子查詢：返回的結(jié)果集是N行N列屋群。

   指子查詢返回的結(jié)果集是N行N列的一個(gè)表數(shù)據(jù)闸婴。

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