MySQL-6.查詢語句

本文地址：http://www.itdecent.cn/p/8341e77f34e2

一、簡介

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

由于其子句較多，且功能繁雜，所以語法相對較為復(fù)雜。

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

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

  • select_expr [, select_expr ...]

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

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

    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子句來指定查詢的分區(qū)表。指定之后，查詢將只從列出的分區(qū)表中查詢數(shù)據(jù)。

    • [WHERE where_condition]

      FROM語句中，還有WHERE子句，可以指定查找符合where_condition表達(dá)式的數(shù)據(jù)。如果沒有指定WHERE子句，那么將會把表中所有行的數(shù)據(jù)查詢出來。

      例子：

      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中還有一個GROUP BY語句，用以分組。

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

    排序子句，用于將查詢到的數(shù)據(jù)按一定順序進(jìn)行排列，ORDER BY可以通過列名或者列的別名來進(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語句后加上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子句主要用來限制查詢結(jié)果的數(shù)量。LIMIT一般需要一個或兩個非負(fù)整數(shù)參數(shù)來決定限制的范圍和位置。

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

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

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

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

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

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

修飾符

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

  • ALL、DISTINCT

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

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

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

  • HIGH_PRIORITY

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

  • STARTGHT_JOIN

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

  • SQL_SMALL_RESULT或SQL_BIG_RESULT

    這個修飾符可以與GROUP BY或者DISTINCT一起使用，來告訴優(yōu)化器搜索的結(jié)果非常多還是非常少。如果使用SQL_BIG_RESULT，那么MySQL會直接使用基于磁盤的臨時表來存儲搜索結(jié)果，如果使用的是SQL_SMALL_RESULT，那么MySQL會使用基于內(nèi)存的臨時表來存儲搜索結(jié)果。

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

三、聚合分組

聚合函數(shù)

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

聚合函數(shù) 描述
AVG() 求平均值函數(shù)
COUNT() 返回參數(shù)的行數(shù)
COUNT(DISTINCT) 返回去重之后的行數(shù)
GROUP_CONCAT() 返回所有值拼接成的字符串
MAX() 求最大值
MIN() 求最小值
STD() 求標(biāo)準(zhǔn)差
SUM() 求和函數(shù)

除非專門說明，否則這些聚合函數(shù)都會忽略列表中的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會返回expr中值不為NULL的行數(shù)。但是COUNT(*)有一點(diǎn)特殊，其返回的是提取出來的總行數(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)

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

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

分組查詢(GROUP BY)

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

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

此例子會將學(xué)生通過班級分組，并統(tǒng)計出每個班級的人數(shù)。

需要注意的是，在使用了GROUP BY分組之后，那么前面SELECT查詢的字段就只能使用分組的字段和聚合函數(shù)了，使用其他字段將會報錯。

四、連接查詢(JOIN)

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

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

我將會使用以下兩張表作為例子進(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三者是等價的，都被視為內(nèi)連接。在直接使用的時候，又被稱為無條件內(nèi)連接或笛卡爾連接，其會把兩張表里的數(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ù)的連接是完全沒有任何聯(lián)系的，所以如果我們需要通過指定條件來限定連接的方式，就可以使用ON子句來設(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)連接中，可以看到如果沒有符合ON字句的匹配條件，那么不符合的這些行將會被舍去。但是有一些情況下，我們希望保留下那些沒有符合匹配條件的行，這個時候就可以使用外連接。

左外連接

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

也就是說，在左外連接的過程中，左表中不符合匹配條件的行將會被保存下來，這些行的右表字段將使用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)

右外連接

與左外連接相對應(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)

五、子查詢

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

外部查詢需要是這些語句之一：SELECT、INSERT、UPDATE、DELETE或DO。

子查詢的位置一般會在SELECT中、FROM后、WHERE中。

子查詢分類

子查詢一般會被分為以下幾類：

  1. 標(biāo)量子查詢：返回單一值的標(biāo)量，最簡單的形式。

    是指子查詢返回的是單一值的標(biāo)量，如一個數(shù)字或一個字符串，也是子查詢中最簡單的返回形式。 可以使用 = > < >= <= <> 這些操作符對子查詢的標(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é)果通常來自對表的某個字段查詢返回?？梢允褂?code>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é)果通常是對表的某行數(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列的一個表數(shù)據(jù)。

    SELECT * FROM article WHERE (title,content,uid) IN (SELECT title,content,uid FROM blog)
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容