MySQL基礎知識——子查詢

2021-02-19 SQL資料庫開發

點擊關註上方「SQL資料庫開發」,

設為「置頂或星標」,第一時間送達乾貨

什麼是子查詢

子查詢(Sub Query)或者說內查詢(Inner Query),也可以稱作嵌套查詢(Nested Query),是一種嵌套在其他 SQL 查詢的 WHERE 子句中的查詢。

子查詢用於為主查詢返回其所需數據,或者對檢索數據進行進一步的限制。

子查詢可以在 SELECT、INSERT、UPDATE 和 DELETE 語句中,同 =、<、>、>=、<=、IN、BETWEEN 等運算符一起使用。

使用子查詢必須遵循以下幾個規則:

子查詢必須括在圓括號中。

子查詢的 SELECT 子句中只能有一個列,除非主查詢中有多個列,用於與子查詢選中的列相比較。

子查詢不能使用 ORDER BY,不過主查詢可以。在子查詢中,GROUP BY 可以起到同 ORDER BY 相同的作用。

返回多行數據的子查詢只能同多值操作符一起使用,比如 IN 操作符。

SELECT 列表中不能包含任何對 BLOB、ARRAY、CLOB 或者 NCLOB 類型值的引用。

子查詢不能直接用在集合函數中。

BETWEEN 操作符不能同子查詢一起使用,但是 BETWEEN 操作符可以用在子查詢中。


SELECT子查詢語句

通常情況下子查詢都與 SELECT 語句一起使用,其基本語法如下所示:

SELECT column_name [,column_name]

FROM  table1 [,table2]

WHERE column_name OPERATOR

(SELECT column_name [,column_name]      

FROM table1 [,table2 ]

[WHERE])


示例數據表

考慮 Customers表和Orders表,表中記錄如下所示:

表Customers

表Orders

子查詢實例

現在,讓我們試一下在 SELECT 語句中進行子查詢:

SELECT * FROM Customers
WHERE 客戶ID IN
(SELECT 客戶ID FROM Orders
WHERE 員工ID=9)

上述語句的執行結果如下所示:

上述語句是先獲取訂單表Orders中員工ID=9的發貨記錄,我們發現有兩條記錄,其中這兩條記錄對應的客戶ID是3和4,而在外層客戶Customers表中我們需要找到Orders表中返回的客戶ID是3和4所對應的記錄,那就是我們查詢出的結果了。

注意:在WHERE子句中使用子查詢(如這裡所示),應該保證SELECT語句具有與WHERE子句中相同數目的列。通常,子查詢將返回單個列並且與單個列匹配,但如果需要也可以使用多個列。


INSERT 子查詢語句

子查詢還可以用在 INSERT 語句中。INSERT 語句可以將子查詢返回的數據插入到其他表中。子查詢中選取的數據可以被任何字符、日期或者數值函數所修飾。

其基本語法如下所示:

INSERT INTO 

table_name [(column1 [,column2])]           

SELECT [*|column1 [,column2]           

FROM table1 [,table2]

[WHERE VALUE OPERATOR]


示例:

考慮與 Customers表擁有相似結構的 Customers_bak表。現在要將 上面查詢的結果插入到Customers_bak表中

INSERT INTO Customers_bak
SELECT * FROM Customers
WHERE 客戶ID IN
(SELECT 客戶ID FROM Orders
WHERE 員工ID=9)

查詢一下Customers_bak的結果,會發現和上面的結果一致。


UPDATE子查詢語句

子查詢可以用在 UPDATE 語句中。當子查詢同 UPDATE 一起使用的時候,既可以更新單個列,也可更新多個列。

其基本語法如下:

UPDATE table

SET column_name = new_value

[WHERE OPERATOR [VALUE]

(SELECT COLUMN_NAME   

FROM TABLE_NAME)

[WHERE)]

示例:

下面的示例將 Customers表中在Orders表裡有發貨的客戶,他們所在的城市要加一個「市」字。

UPDATE Customers
SET 城市=城市+'市'
WHERE 客戶ID IN
(SELECT 客戶ID FROM Orders)

這將影響4行數據,隨後 Customers表中的記錄將如下所示:


DELETE 子查詢語句

如同前面提到的其他語句一樣,子查詢還可以同 DELETE 語句一起使用。

其基本語法如下所示:

DELETE FROM TABLE_NAME

[ WHERE OPERATOR [VALUE]

(SELECT COLUMN_NAME   

FROM TABLE_NAME)

[WHERE)]

示例:

假設我們想刪除在Orders表中有購買記錄的客戶信息,可以這樣寫

DELETE FROM Customers
WHERE 客戶ID IN
(SELECT 客戶ID FROM Orders)

這將影響4行數據,隨後 Customers表中的記錄將如下所示:


SELECT 
`姓名`,
`客戶ID`,
(
SELECT COUNT(*)
FROM orders o
WHERE o.`客戶ID`=c.`客戶ID` 
) AS '數量'
FROM customers c

結果為:

這裡使用SELECT COUNT(*)對表中的行進行計數,並且通過提供一條WHERE子句來匹配某個特定的客戶ID 。其中相關就是通過WHERE裡面的匹配條件來確定聯繫的。

點擊「閱讀原文」了解SQL訓練營

相關焦點

  • MySQL 這該死的 「IN (子查詢)」
    一、什麼是子查詢簡而言之,子查詢就是嵌套在主查詢裡的查詢,如:SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);SELECT * FROM t1 JOIN (SELECT * FROM t2 WHERE id%2 = 0) tmp ON t1.id = tmp.ref_id
  • 玩轉Mysql系列 - 第12篇:子查詢(非常重要,高手必備)
    環境:mysql5.7.25,cmd命令中進行演示。本章節非常重要。子查詢 出現在select語句中的select語句,稱為子查詢或內查詢。外部的select查詢語句,稱為主查詢或外查詢。all:和子查詢返回的「所有值」比較,比如a>all(10,20,30),a大於子查詢中所有值,換句話說,a大於子查詢中最大值即可滿足查詢條件,等同於a>max(10,20,30);子查詢的執行優先於主查詢執行,因為主查詢的條件用到了子查詢的結果。mysql中的in、any、some、allin,any,some,all分別是子查詢關鍵詞之一。
  • mysql基礎-mysql中的DQL-排序查詢
    每天和小潭一起快樂的學習~    你好,我是在學mysql鑑於篇幅原因,小潭將mysql查詢部分的知識點分成同期不同篇的方式進行學習和介紹。該篇中我們將了解mysql中的排序查詢。sql語句選中使用F9可快速提交查詢,使用F12可以格式化sql語句。
  • mysql基礎-mysql中的DQL-常見函數
    每天和小潭一起快樂的學習~    你好,我是在學mysql鑑於篇幅原因,小潭將mysql查詢部分的知識點分成同期不同篇的方式進行學習和介紹。該篇中我們將了解mysql中的常見函數。sql語句選中使用F9可快速提交查詢,使用F12可以格式化sql語句。
  • MySQL最常用分組聚合函數
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]分組SELECT的基本格式:  select [聚合函數] 欄位名 from 表名    [where 查詢條件]    [group by 欄位名]    [having 過濾條件]1、
  • 小林求職記(二):說好的問基礎,為啥我感覺一點也不基礎呢?
    面試官:嗯嗯,那我先來問你一些基礎問題吧。小林:嗯嗯,好的。面試官:你了解arraylist吧,請說下內部的一些特性。> commit;Query OK, 0 rows affected (0.00 sec)當a事務提交結束了,此時b事務開始執行select 查詢校驗的操作,判斷不存在id為100的數據,此時打算執行插入數據的操作:mysql> select * from
  • Mysql 8 重要新特性 - CTE 通用表表達式
    派生表是 FROM 中的子查詢,例如:SELECT ... FROM (subquery) AS derived, t1 ...CTE 就像派生表,但它的聲明是在查詢塊兒之前,而不是在 FROM 中,例如:WITH derived AS (subquery) SELECT ... FROM derived, t1 ...
  • 由MySQL字符串函數考慮到的繞過利用
    ','f1fhgb');-> 0mysql> select locate('fh','f1fhgb');-> 3mysql> select position('bc'in'abc');-> 2mysql> select instr('aabca','abc');-> 2
  • MySQL的SQL語句 - 數據操作語句(7)- INSERT SELECT 語句
    ● INSERT 語句的目標表可以出現在查詢的 SELECT 部分的 FROM 子句中,也可以是 TABLE 指定的表。但是,不能在子查詢中插入一個表並從同一表中進行選擇。當從同一個表中進行選擇和插入時,MySQL 會創建一個內部臨時表來保存 SELECT 中的行,然後將這些行插入到目標表中。但是,當t是臨時表時,不能使用 INSERT INTO t ... SELECT ...
  • 技術分享 | MySQL:count(*)、count(欄位) 實現上區別
    文章末尾有他著作的《深入理解 MySQL 主從原理 32 講》,深入透徹理解 MySQL 主從,GTID 相關技術知識。
  • MYSQL 常用函數
    字符串函數函數功能CONCAT(str1, str2,…,strn)連接字符串str1,…,strn為一個完整字符串INSERT(str, x, y, instr)將字符串str從第x位置開始,y個字符長的子串替換為字符串
  • MySQL基礎語法
    FROM:指定要查詢的表。WHERE:設定查詢結果的過濾條件。LIMIT:設定返回記錄的行數。OFFSET:指定開始查詢的數據偏移量,默認為0。limit N,M: 相當於limit M offset N, 從第 N+1 條記錄開始, 返回 M 條記錄。SELECT和FROM語句是必須的,其他部分是可選的。
  • MySQL的四種GROUP BY用法
    原文出處:https://www.percona.com/blog/2018/02/05/four-ways-to-execute-mysql-group-by/原文作者:Peter Zaitsev 在本文中,我將介紹MySQL
  • 說說MySQL ORDER BY
    測試表:[yejr]@[imysql.com]>show create table t1\G*************************** 1. row ***************************       Table: t1Create Table: CREATE TABLE `t1` (  `c1` int(10) unsigned NOT
  • 分組查詢時,select的欄位是否一定要都在group by中?
    分組查詢關鍵字group by通常和集合函數(MAX、MIN、COUNT、SUM、AVG)一起使用,它可以對一列或者多列結果集進行分組。
  • 開發者課程 | 資料庫知識-SQL基礎
    FROM emp e WHERE e.job LIKE '%技術%';排序,ORDER BY語句,DESC降序,ASC升序,不寫默認為升序,此例按照工資降序排序SELECT * FROM emp e ORDER BY e.sal DESC;分組 GROUP BY 語句根據一個或多個列對結果集進行分組,在分組的列上我們可以使用 COUNT,
  • MySQL mysqlbinlog 解析出的 SQL 語句被注釋是怎麼回事
    我們通過實驗來了解一下具體細節情況,如下所示,實驗環境為5.6.20-enterprise-commercial-advanced-log# whereis mysqlbinlogmysqlbinlog: /usr/bin/mysqlbinlog /usr/share/man/man1/mysqlbinlog.1.gz我們先在參數文件my.cnf裡面設置binlog_format
  • Mysql 中 exists 和 in 的區別
    下面將主查詢的表稱為外表;子查詢的表稱為內表。exists 與 in 的區別如下:子查詢使用 exists,會先進行主查詢,將查詢到的每行數據循環帶入子查詢校驗是否存在,過濾出整體的返回數據;子查詢使用 in,會先進行子查詢獲取結果集,然後主查詢匹配子查詢的結果集,返回數據外表內表相對大小情況不一樣時,查詢效率不一樣:兩表大小相當,in 和 exists 差別不大;內表大,用 exists 效率較高;內表小,用 in