溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
下文主要給大家帶來MySQL子查詢和連接有何區別，希望這些內容能夠帶給大家實際用處，這也是我編輯MySQL子查詢和連接有何區別這篇文章的主要目的。好了，廢話不多說，大家直接看下文吧。
使客戶端進入gbk編碼方式顯示：
mysql> SET NAMES gbk;
1.子查詢
子查詢的定義：
子查詢（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 , SET 或 DO
子查詢的返回值：
子查詢可以返回標量、一行、一列或子查詢
2.使用比較運算符的子查詢
使用比較運算符的子查詢：
=、>、<、>=、<=、<>、！=、 <=>
語法結構：
operand（操作數）、comparison_operator （比較運算符）、subquery（子查詢）
用ANY 、SOME 或ALL修飾的比較運算符
operand comparison_operator ANY (subquery)
operand comparison_operator SOME (subquery)
operand comparison_operator ALL (subquery)
1.語法結構 操作數 比較運算符 any（子查詢）
操作數 比較運算符 some（子查詢）
操作數 比較運算符 all（子查詢）
2.適合于子查詢有多個結果
3.any 和some結果一致 all與any、some 相反
例，從tdb_goods表中 查詢平均價格，小數點保留2位，：
mysql> SELECT ROUND(avg(goods_price),2) AS avg_price FROM tdb_goods;+-----------+| avg_price |+-----------+| 5391.30 |+-----------+
查詢平均價格以上的商品：
mysql> SELECT * FROM tdb_goods WHERE goods_price > 5391.30;
mysql> SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);
查詢超極本的列表，按價格升序排列：
mysql> SELECT * FROM tdb_goods WHERE goods_cate = '超級本' ORDER BY goods_price ASC;
查詢比超極本的最低價格高的列表，按價格降序的方式排列：
mysql> SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超級本') ORDER BY goods_price DESC;
查詢比超極本的最高價格高的列表，按價格降序的方式排列：
mysql> SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超級本') ORDER BY goods_price DESC;
3.使用[NOT]IN/EXISTS引發的子查詢
子查詢形式：
1.使用IN的子查詢
2.使用[NOT]IN的子查詢
語法結構：operand comparison_operator [NOT] IN (subquery)
=ANY 運算符與IN 等效，!=ALL或<>ALL運算符與NOT IN等效
3.使用[NOT]EXISTS的子查詢(用的相對較少)
如果子查詢返回任何行，EXISTS將返回TRUE，否則為FALSE
例，查找不是超極本的商品列表：
mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE goods_cate = '超級本');
4.使用INSERT...SELECT插入記錄
例：在tdb_goods_cates表中插入tdb_goods表中的goods_cate分類；
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate; mysql> SELECT * FROM tdb_goods_cates;+---------+---------------------+| cate_id | cate_name |+---------+---------------------+| 1 | 臺式機 || 2 | 平板電腦 || 3 | 服務器/工作站 || 4 | 游戲本 || 5 | 筆記本 || 6 | 筆記本配件 || 7 | 超級本 |+---------+---------------------+
5.多表更新
多表更新：
UPDATE table_references SET col_name1={expr1|DEFAULT}[,col_name2={expr2|DEFAULT}]...[WHERE where_condition] 其中，table_references即連接的語法結構
連接類型：
INNER JOIN，內連接
在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等價的
LEFT [OUTER] JOIN，左外連接
RIGHT [OUTER] JOIN，右外連接
連接—語法結構
table_reference
{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}
table_referenceON conditional_expr 例，將tdb_goods中的goods_cate更新為tdb_goods_cate表中的對應cate_id。
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;
6.多表更新之一步到位
CREATE...SELECT
創建數據表同時將查詢結果寫入到數據表（合并了CREATE 和 INSERT...SELECT兩個操作步驟）
CREATE TABLE [IF NOT EXISTS] tbl_name[(create_definition,...)]select_statement
例：創建品牌分類數據表tdb_goods_brand，并將tdb_goods表中的brand_name寫入
mysql> CREATE TABLE tdb_goods_brand( -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, -> brand_name VARCHAR(40) NOT NULL -> ) -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;
例，將tdb_goods中的brand_name更新為tdb_goods_brand表中的對應brand_id。錯誤寫法（程序分別不出哪個brand_name屬于哪個表）：
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id; ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous
解決方法是給表起別名：
mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;
查詢tdb_goods的數據表結構
mysql> DESC tdb_goods;+-------------+------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+------------------------+------+-----+---------+----------------+| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || goods_name | varchar(150) | NO | | NULL | || goods_cate | varchar(40) | NO | | NULL | || brand_name | varchar(40) | NO | | NULL | || goods_price | decimal(15,3) unsigned | NO | | 0.000 | || is_show | tinyint(1) | NO | | 1 | || is_saleoff | tinyint(1) | NO | | 0 | |+-------------+------------------------+------+-----+---------+----------------+
goods_cate和brand_name任然是varchar，現在我們修改字段名goods_cate為cate_id，brand_name修改為brand_id, 為了節省空間，我們修改數據類型為smallant
mysql> ALTER TABLE tdb_goods； -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL, -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL; mysql> DESC tdb_goods； +-------------+------------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------------+------------------------+------+-----+---------+----------------+| goods_id | smallint(5) unsigned | NO | PRI | NULL | auto_increment || goods_name | varchar(150) | NO | | NULL | || cate_id | smallint(5) unsigned | NO | | NULL | || brand_id | smallint(5) unsigned | NO | | NULL | || goods_price | decimal(15,3) unsigned | NO | | 0.000 | || is_show | tinyint(1) | NO | | 1 | || is_saleoff | tinyint(1) | NO | | 0 | |+-------------+------------------------+------+-----+---------+----------------+
7.連接的語法結構
連接在MySQL在SELECT語句、多表更新、多表刪除語句中支持JOIN操作。
table_reference
{[INNER|CROSS] JOIN|{LEFT|RIGHT}[OUTER]JOIN}
table_referenceON conditional_expr數據表參照
table_reference
tbl_name [[AS] alias] | table_subquery [AS] alias
數據表可以使用tbl_name AS alias_name
或tbl_name alias_name 賦予別名
table_subquery可以作為子查詢使用FROM子句中，
這樣的子查詢必須賦予別名。
8.內連接INNER JOIN
INNER JOIN，內連接
在MySQL中，JOIN，CROSS JOIN 和 INNER JOIN 是等價的
LEFT [OUTER] JOIN，左外連接
RIGHT [OUTER] JOIN，右外連接
連接條件：
使用ON關鍵字來設定連接條件，也可以使用WHERE來代替
通常使用ON關鍵字來設定連接條件
使用WHERE關鍵字進行結果集記錄的過濾
內連接和外連接的區別
內連接 ，顯示左表及右表符合連接條件的記錄，即交集
例如 插入幾條記錄
-- 分別在tdb_goods_cates和tdb_goods_brands表插入記錄
INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交換機'),('網卡');INSERT tdb_goods_brands(brand_name) VALUES('海爾'),('清華同方'),('神舟'); -- 在tdb_goods數據表寫入任意記
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光打印機','12','4','1849');例，查看符合內連接的記錄，表中只出現商品表和品牌表都有的記錄，這就是內連接（這里商品表為左表，分類表為右表）：
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods-> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
9.外連接OUTER JOIN
左外連接
顯示左表的全部記錄及右表符合連接條件的記錄
例，查看符合左外連接的記錄，表中出現商品表的全部和2表表都有的記錄，這就是左外連接（這里商品表為左表，分類表為右表）
mysql> SELECT goods_id,cate_name,goods_price FROM tdb_goods LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
右外連接
顯示右表的全部記錄及左表符合連接條件的記錄
例，查看符合右外連接的記錄，表中出現品牌表的全部和2表表都有的記錄，這就是右外連接（這里商品表為左表，分類表為右表）
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;
10.多表連接
/**
多表的連接跟兩張表的連接一樣
表的連接實質就是外鍵的逆向約束
*/
例，查看符合內連接的記錄，表中只出現商品表和品牌表都有的記錄，這就是內連接的多表連接（這里商品表為左表，品牌表和分類表為右表）：
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id -> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id;
11.無限級分類表設計
自身連接
例，查找所有分類及其父類：
mysql> SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS p ON s.parent_id = p.type_id;+---------+-----------------+-----------------+| type_id | type_name | type_name |+---------+-----------------+-----------------+| 1 | 家用電器 | NULL || 2 | 電腦、辦公 | NULL || 3 | 大家電 | 家用電器 || 4 | 生活電器 | 家用電器 || 5 | 平板電視 | 大家電 || 6 | 空調 | 大家電 || 7 | 電風扇 | 生活電器 || 8 | 飲水機 | 生活電器 || 9 | 電腦整機 | 電腦、辦公 || 10 | 電腦配件 | 電腦、辦公 || 11 | 筆記本 | 電腦整機 || 12 | 超級本 | 電腦整機 || 13 | 游戲本 | 電腦整機 || 14 | CPU | 電腦配件 || 15 | 主機 | 電腦配件 |+---------+-----------------+-----------------+
例，查找所有分類及其子類：
mysql> SELECT p.type_id,p.type_name,s.type_name FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id; +---------+-----------------+--------------+| type_id | type_name | type_name |+---------+-----------------+--------------+| 1 | 家用電器 | 大家電 || 1 | 家用電器 | 生活電器 || 2 | 電腦、辦公 | 電腦整機 || 2 | 電腦、辦公 | 電腦配件 || 3 | 大家電 | 平板電視 || 3 | 大家電 | 空調 || 4 | 生活電器 | 電風扇 || 4 | 生活電器 | 飲水機 || 5 | 平板電視 | NULL || 6 | 空調 | NULL || 7 | 電風扇 | NULL || 8 | 飲水機 | NULL || 9 | 電腦整機 | 筆記本 || 9 | 電腦整機 | 超級本 || 9 | 電腦整機 | 游戲本 || 10 | 電腦配件 | CPU || 10 | 電腦配件 | 主機 || 11 | 筆記本 | NULL || 12 | 超級本 | NULL || 13 | 游戲本 | NULL || 14 | CPU | NULL || 15 | 主機 | NULL |+---------+-----------------+--------------+
例，查找所有分類及其子類的數目
mysql> SELECT p.type_id,p.type_name,COUNT(s.type_name) FROM tdb_goods_types AS p LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id GROUP BY p.type_name ORDER BY p.type_id;+---------+-----------------+--------------------+| type_id | type_name | COUNT(s.type_name) |+---------+-----------------+--------------------+| 1 | 家用電器 | 2 || 2 | 電腦、辦公 | 2 || 3 | 大家電 | 2 || 4 | 生活電器 | 2 || 5 | 平板電視 | 0 || 6 | 空調 | 0 || 7 | 電風扇 | 0 || 8 | 飲水機 | 0 || 9 | 電腦整機 | 3 || 10 | 電腦配件 | 2 || 11 | 筆記本 | 0 || 12 | 超級本 | 0 || 13 | 游戲本 | 0 || 14 | CPU | 0 || 15 | 主機 | 0 |+---------+-----------------+--------------------+
為tdb_goods_types添加child_count字段
mysql> UPDATE tdb_goods_types AS t1 INNER JOIN -> (SELECT p.type_id,p.type_name,count(s.type_name) AS child_count FROM tdb_goods_types AS p -> LEFT JOIN tdb_goods_types AS s ON s.parent_id = p.type_id -> GROUP BY p.type_name -> ORDER BY p.type_id)AS t2 -> ON t1.type_id = t2.type_id -> SET t1.child_count = t2.child_count； mysql> SELECT * FROM tdb_goods_types;+---------+-----------------+-----------+-------------+| type_id | type_name | parent_id | child_count |+---------+-----------------+-----------+-------------+| 1 | 家用電器 | 0 | 2 || 2 | 電腦、辦公 | 0 | 2 || 3 | 大家電 | 1 | 2 || 4 | 生活電器 | 1 | 2 || 5 | 平板電視 | 3 | 0 || 6 | 空調 | 3 | 0 || 7 | 電風扇 | 4 | 0 || 8 | 飲水機 | 4 | 0 || 9 | 電腦整機 | 2 | 3 || 10 | 電腦配件 | 2 | 2 || 11 | 筆記本 | 9 | 0 || 12 | 超級本 | 9 | 0 || 13 | 游戲本 | 9 | 0 || 14 | CPU | 10 | 0 || 15 | 主機 | 10 | 0 |+---------+-----------------+-----------+-------------+
11.多表刪除
DELETE tbl_name[.*][,tbl_name[.*]]...FROM table_references[WHERE where_condition]
例，查找重復記錄：
mysql> SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name) >= 2; +----------+-----------------------------+| goods_id | goods_name |+----------+-----------------------------+| 18 | HMZ-T3W 頭戴顯示設備 || 19 | 商務雙肩背包 |+----------+-----------------------------+
刪除重復記錄
mysql> DELETE t1 FROM tdb_goods AS t1 -> LEFT JOIN (SELECT goods_id,goods_name FROM tdb_goods GROUP BY goods_name having count(goods_name) >= 2) AS t2 -> ON t1.goods_name = t2.goods_name -> WHERE t1.goods_id > t2.goods_id;
12.復制記錄
復制編號為19,20的兩條記錄
mysql> SELECT goods_id,goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN (19,20);+----------+------------------------------------+---------+----------+| goods_id | goods_name | cate_id | brand_id |+----------+------------------------------------+---------+----------+| 19 | 商務雙肩背包 | 6 | 7 || 20 | X3250 M4機架式服務器 2583i14 | 3 | 1 |+----------+------------------------------------+---------+----------+
mysql> INSERT tdb_goods(goods_name,cate_id,brand_id) SELECT goods_name,cate_id,brand_id FROM tdb_goods WHERE goods_id IN(19,20);
對于以上關于MySQL子查詢和連接有何區別，大家是不是覺得非常有幫助。如果需要了解更多內容，請繼續關注我們的行業資訊，相信你會喜歡上這些內容的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
