當前位置：首頁 > 运维知识 > 数据库 >内容正文

数据库

MySQL之子查询

發布時間：2025/3/20 数据库 19 豆豆

生活随笔收集整理的這篇文章主要介紹了 MySQL之子查询小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

在前面介紹了數據的記錄的操作，主要包括兩類：

記錄的寫操作：增加記錄，修改記錄，刪除記錄

記錄的讀取操作：記錄的查找

首先在 test?數據庫?中創建一個數據表 goods?

root@localhost test>CREATE TABLE goods(-> goods_id SMALLINT(5) UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> goods_name varchar(150),-> goods_cate varchar(40),-> brand_name varchar(40),-> goods_price decimal(15,3) unsigned DEFAULT 0.000,-> is_show tinyint(1) DEFAULT 1,-> is_saleoff tinyint(1) DEFAULT 0-> );

使用命令查看創建情況，該命令用于查看表的結構：

root@localhost test>show columns from goods;

現在插入若干條記錄

insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('Vostr','lapPC','lennovo',3990,default,default); insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('honorX6','phone','honor',990,default,default); insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('lingyue','lapPC','HP',3990,default,default); insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('back-2','backpack','sony',690,default,default); insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('mi-9','phone','xiaomi',2990,default,default); insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('sdkf','Trolley-bag','xuan',290,default,default); insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('x-3','TV','TCL',1890,default,default); insert goods(goods_name,goods_cate,brand_name,goods_price,is_show,is_saleoff) VALUES('A-6','camera','sony',4990,default,default);

子查詢(SubQuery)是指出現在其他SQL語句內的SELECT?子句

SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2)

其中SELECT * FROM t1 稱之為 Outer Query /Outer Statement 為外層查詢，SELECT col2 FROM t2 稱之為SubQuery

子查詢是指嵌套在查詢內部，且必須始終出現在()內，子查詢可以包括多個關鍵字和條件，如DISTINCT,GROUP BY,ORDER BY,LIMIT，函數等；子查詢的外層查詢可以是：SELECT,INSERT,UPDATE,DET,DO.

子查詢的結果可以是一個標量，一行，一列或者子查詢

1、使用比較運算符的子查詢

使用比較運算符的子查詢，其中比較運算符有? =、>、 <、>=、<=、<>、！=、<=>? ，其語法結構為：

operand comparision_operator subquery

首先對表中的所有商品的價格進行平均值求解：

root@localhost test>SELECT AVG(goods_price) FROM goods;

小數點的位數太多，這里進行指定為為小數點后取兩位

root@localhost test>SELECT ROUND(AVG(goods_price),2) FROM goods;

現在查詢哪些商品的價格大于平均值呢

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price >= 2477.50;

這里使用的 2477.50?就是上一個查詢結果的值，能否將兩個語句“合并”，就利用子查詢進行實現

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price >= (SELECT ROUND(AVG(goods_price),2) FROM goods);

這樣就實現了子查詢的功能。

現在來查詢一下，分類中goods_cate為phone的有哪些商品

這里查詢的時候對表達式goods_cate=phone中沒有對phone進行使用引號報錯了，加上引號之后：

root@localhost test>SELECT goods_price FROM goods WHERE goods_cate='phone';

現在想知道哪些商品的價格大于這些 phone?

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > (SELECT goods_price FROM goods WHERE goods_cate='phone');

此時系統會提示 ERROR 1242? (21000):Subquery returns maore than 1 row?因為在上述查詢中知道滿足goods_cate='phone'的結果有兩個，子查詢返回了三條結果，所以應該指定到底是大于990還是2990,此時會用到以下語法

operand comparsion_operator ANY (subquery) operand comparsion_operator SOME (subquery) operand comparsion_operator ALL (subquery)

當子查詢返回多個結果時，可以使用以上的語法進行修飾

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ANY (SELECT goods_price FROM goods WHERE goods_cate='phone');

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price > ALL (SELECT goods_price FROM goods WHERE goods_cate='phone');

2、使用[NOT] IN的子查詢

使用IN?或者 NOT IN?的子查詢和上述的方法基本相同，其語法結構如下

operand comparsion_operator [NOT] IN (subquery)

而該語法又與ALL，ANY又一定的聯系，其中 =ANY與IN與算符等價，!=ALL或者<>ALL運算符與NOT IN運算符等價

root@localhost test>SELECT goods_id,goods_name,goods_price FROM goods WHERE goods_price NOT IN (SELECT goods_price FROM goods WHERE goods_cate='phone');

查找產品類型不為phone?的記錄

3、使用[NOT] EXISTS的子查詢

這種情況使用的較少，如果子查詢返回任何行，EXISTS將返回TRUE否則返回FALSE

4、多表更新

在數據表中可以發現，存在很多重復的字段，利用命令查看，如產品類型goods_cate中的LapPC，phone和品牌brand_name中的sony等，目前因為數據表只涉及了8條記錄，但是如果有越來越多的數據記錄，這將造成?查找時候效率降低，想著可以用外鍵來實現：

root@localhost test>select * from goods\G;

而外鍵的實現是利用兩張數據表，現在只有一張數據表，因此需要創建一張數據表來存儲分類和品牌,那如何才能得到這些商品的不同分類，以方便實現存儲呢？就是在查找的時候按照分組就可以

root@localhost test> CREATE TABLE IF NOT EXISTS goods_cates(-> cate_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> cate_name VARCHAR(40) NOT NULL-> );

注意這里的數據表的名稱為goods_cates和數據表goods中的記錄goods_cate不要混淆

root@localhost test>SELECT goods_cate FROM goods GROUP BY goods_cate;

利用分類進行查看，這里共有6類，8條記錄共有6類，隨著商品記錄不斷增加，在類別上可能會大大的減少

將查找的類別寫入到數據表中，可以一條一條的寫入，當然這種方法面對很多類別的時候，操作相當麻煩，因此可以是如下語句

INSERT [INFO] table_name [(col_name，...)] SELECT...

將查詢結果寫入到數據表中

root@localhost test>select * from goods_cates;

數據表goods_cates為空，數據表的結結構為：

root@localhost test>DESC goods_cates;

這張數據表有兩個字段，現在寫入記錄

root@localhost test>INSERT goods_cates(cate_name) SELECT goods_cate FROM goods GROUP BY goods_cate;

這里在INSERT的時候省略了VALUES，要注意

這6條記錄已經成功寫入到數據表中了，但是沒有使用外鍵實現，而在數據表goods中依然存儲的是lapPC,phone，而根據設計目的，在goods_cates數據表中應該將lapPC存儲為3，將phone存儲為 4等等，因此現在需要參照分類表goods_cates來更新商品表goods，就是參照別的表來更新當前的表，因此需要用到多表更新。其語法結構為

UPDATE table_references SET col_name1={expr1|DEFAULT} [,col_name2=expr2|DEFAULT}]...[WHERE where_condition] table_reference {[INNER|CROSS] JOIN | {LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr root@localhost test>UPDATE goods INNER JOIN goods_cates ON goods_cate = cate_name-> SET goods_cate = cate_id;

這里需要更新的是表goods中的goods_cate使用內連接的方式,將goods表中的goods_cate修改為表goods_cates表中的cate_id,(如將goods中的goods_cate 為lapPC修為為3，因為goods_cates表的lapPC為3，goods_cate和cate_name中的值是相等的，)因此上述語句中連接條件為ON goods_cate = cate_name,條件有了，那么更新的值是什么呢，那就是SET? goods_cate = cate_id

因此多表更新可以總結為：

1、創建新表，這里是goods_cates

2、插入記錄，這里是將goods_cate中的值插入到表goods_cates中的cate_name

3、多表的更新

發現上述步驟較為繁瑣，可以實行CREATE...SELECT來實現

即是創建數據表同時將查詢結果寫入到數據表中，其語法結構為：

CREATE TABLE [IF NOT EXISTS] table_name [(create_defination,...)] select_statement

將上述的中1、2步驟合成一個命令,首先查看一下有多少種品牌

root@localhost test>SELECT brand_name FROM goods GROUP BY brand_name;
root@localhost test>CREATE TABLE table_goods_brands(-> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,-> brand_name VARCHAR(40) NOT NULL-> )-> SELECT brand_name FROM goods GROUP BY brand_name;注意這里在創建數據結束的時候沒有加";"，而是在SELECT語句之后加上的“；”

此時需要參照品牌表table_goods_brands更新商品表goods,分別查詢兩個表中的結構情況

root@localhost test>UPDATE goods INNER JOIN table_goods_brands ON brand_name = brand_name-> SET brand_name = brand_id;

提示錯誤！因為兩個表中均有brand_name，此時利用起別名進行區分是哪個表的grand_name

root@localhost test>UPDATE goods AS A INNER JOIN table_goods_brands AS B ON A.brand_name = B.brand_name-> SET A.brand_name = B.brand_id;

再查詢一下結構

發現goods_cate和brand_name依然是字符型，因此需要盡量的去修改結構

root@localhost test>alter table goods-> change goods_cate cate_id smallint unsigned not null,-> change brand_name brand_id smallint unsigned not null;

再次查找發現現在才更改為smallint unsigned進行了“瘦身”操作。目前將數據進行分開存儲了，那要不要做外鍵呢？其實外鍵不一定非要物理的外鍵,就是非要有FOREIGN KEY ，也可以是使用上述形式的外鍵，這種外鍵往往稱之為事實的外鍵，也是常用的一種形式，而真正的物理的外鍵就是有 FOREIGN KEY用的不是特別多.

使用命令 select * from goods\G 進行查看

數字所占的字節數要遠遠小于字節占的字節數。現在再在goods和table_goods_brand? 插入記錄

root@localhost test>INSERT goods_cates VALUES (default,'route'),(default,'switch'),(default,'ethnet');

root@localhost test>INSERT table_goods_brands VALUES (default,'Huawei'),(default,'Huawei'),(default,'H3C');

再在goods數據表中寫入一條記錄

root@localhost test>INSERT goods(goods_name,cate_id,brand_id,goods_price) VALUES ('Laserjet Pro P16','12','4','2998');

注意這里的cate_id為12，用于并不存在為12的分類，但是有外鍵存在，所以也是成功的

總結

以上是生活随笔為你收集整理的MySQL之子查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

MySQL
之子