Mysql多表聯合查詢

很多時候在實際的業務中我們不只是查詢一張表。

在電子商務系統中，查詢哪些使用者沒有購買過產品。
銀行中可能會查詢違規記錄，同時查詢出用戶的
#查詢中獎資訊和中獎人員的基本資訊。

以上只是列的情況我們就需要把兩張表在一起進行查詢。

而上述業務中需要多表聯合在一起查詢才能有結果，而多表聯合查詢的本質是：表連接。

表格連接

當需要查詢多個表格中的欄位時，就可以使用表格連接來實現。表聯接分為內連接和外連接。

內聯結：將兩個表中存在聯結關係的欄位符合連結關係的那些記錄形成記錄集的聯結。
外連接：會選取其他不符的記錄，分成外左聯結和外右聯結。

在學習實驗前，我為大家準備了兩個模擬的資料表：

使用者表，存放使用者資訊
#訂單表，存放哪位使用者購買過哪個商品

user表建立語句

#CREATE TABLE IF NOT EXISTS user (
uid int(11) NOT NULL,
username varchar(30) NOT NULL,
password# char(32) NOTULL,

password
# char(32) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS order_goods (
oid int(11) NOT NULL,
oid int(11) NOT NULL,
uid int(11) NOT NULL,

name

varchar(50) NOT NULL,

#buytime int(11) NOT NULL# ) ENGINE=InnoDB DEFAULT CHARSET=utf8;user表資料如下：##uid#username#password1景甜1234562 王小二245667#3王強1235531#12355314井柏然123455#5范冰冰5abcwa6黃曉明abcdeef7anglebaby# caption8TFBOYS#abcdwww9安小超12tfddwd#10#高小峰3124qwqw#11李小強323fxfvdvd#12

###李小超######311aqqee############ #13######韓小平######121rcfwrfq############14######宋小康######123123tcsd###### #######15######佟小剛######3cxvdfs############

order_goods資料如下：

##23iphone 12s123121241312#雪碧1323233341534242123#53iphone 鍵盤12123413

oid	#uid	name	buytime
1	10	蘋果滑鼠	1212313

注意：在上表order_goods表中uid是指user表中的uid欄位。上表中oid為1的資料行，uid為10的使用者。為user表中uid為10的使用者：高小峰。該用戶購買了商品為蘋果滑鼠。購買時間buytime為一個unix時間戳。

內連接

基本語法一：

#類別詳細說明#基本語法

select 表1.欄位[as 別名],表n.欄位 from 表1 [別名],表n where 條件;###### #######範例######select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user,order_goods where user.uid = order_goods.uid ;############範例說明######查詢商品表中哪些使用者購買過商品，並將使用者資訊顯示出來############

註：下例中from 表使用到了表別名。

由於表名太長，每次寫的時候容易寫錯。我們可以在表後直接跟上一個簡寫英文字串。在前面拼接字段時，直接使用簡寫字串.字段即可。

mysql> select u.uid ,u.username as username,o.oid,o.uid,o.name as shopname from user u,order_goods o where u.uid = o.uid;
+-----+-----------+-----+-----+---------------+
| uid | username | oid | uid | shopname |
+-----+-----------+-----+-----+---------------+
| 10 | 高小峰 | 1 | 10 | 蘋果滑鼠 |
# | 3 | 王寶強 | 2 | 3 | iphone 12s |
# | 12 | 李小超 | 3 | 12 | 雪碧 |
| 15 | 佟小剛 | 4 | 15 | |
| 3 | 李文凱 | 5 | 3 | iphone 鍵盤 |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)

#基本語法二：

##類別詳細解示#基本語法select 表1.欄位[as 別名],表格n.欄位 from 表1 INNER JOIN 表n on 條件;範例select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner joinorder_goods on user.uid = user.uid = .uid;範例說明查詢商品表中哪些使用者購買過商品，並將使用者資訊顯示出來

結果與基本語法1中一致。

mysql> select user.uid ,user.username as username,order_goods.oid,order_goods.uid,order_goods.name as shopname from user inner join order_goods on order>user. uid;
+-----+-----------+-----+-----+---------------+
| uid | username | oid | uid | shopname |
+-----+-----------+-----+-----+---------------+
| 10 | 高小峰 | 1 | 10 | 蘋果滑鼠 |
# | 3 | 王寶強 | 2 | 3 | iphone 12s |
# | 12 | 李小超 | 3 | 12 | 雪碧 |
| 15 | 佟小剛 | 4 | 15 | |
| 3 | 王寶強 | 5 | 3 | iphone 鍵盤 |
+-----+-----------+-----+-----+---------------+
5 rows in set (0.00 sec)

外連接

說明

########## ##########基本語法######select 表1.欄位[as 別名],表格n.欄位 from 表1 LEFT JOIN 表n on 條件;######## ####範例######select * from user left join order_goods on user.uid = order_goods.uid;#############範例說明######以左為左為左為左為############」範例說明######以左為左為左為主，查詢哪些使用者未購買過商品，並將使用者資訊顯示出來############

外連接又分為左連接和右鏈接，具體定義如下。

左連接：包含所有的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄

mysql> select * from user left join order_goods on user.uid = order_goods.uid;
+-----+-----------+------------+------+ ------+---------------+-----------+
| uid | username | password | oid | uid | name | buytime |
+-----+-----------+------------+------+----- -+---------------+-----------+
| 10 | 高小峰 | 3124qwqw | 1 | 10 | 蘋果滑鼠 | 1212313 |
| 3 | 手寶強 | 1235531 | 2 | 3 | iphone 12s | 123121241 |
| 12 |碧 | 13232333 |
| 15 | 佟小剛 | 3cxvdfs |   4 | 15 | | 34242123 |
| 3 | 王寶強 | 1235531 鍵盤 |   5 |   35531 鍵盤 |   5 |   31一樣 | | 123456 | NULL | NULL | NULL | NULL |
| 2 | 小王二 | 245667 | NULL | NULL | NULL | NULL |
| 4 | 井下  NULL |
| 5 | 範冰冰 | 5abcwa | NULL | NULL | NULL | NULL |
#| 6 | 黃曉明 | abcdeef | NULL | NULL | NULL | NULL |
|    NULL |
| 8 | TFBOYS | abcdwww | NULL | NULL | NULL | NULL |
| 9 | 安小超 | 12tfddwd | NULL | NULL | NULL | NULL |
| 11 | 李小魯| NULL |
| 13 | 韓小平 | 121rcfwrfq | NULL | NULL | NULL | NULL |
| 14 | 宋小康 | 123123tcsd + NULL ------------+------+------+---------------+------- ----+
16 rows in set (0.00 sec)

右連接：包含所有的右邊表格中的記錄甚至是右邊表格中沒有和它相符的記錄

類別	詳細解示
#基本語法	select 表1.欄位[as 別名],表格n.欄位 from 表1 right JOIN 表n on 條件;
範例	select * from user right join order_goods on user.uid = order_goods.uid;

H水平
範例說明

查詢商品表中哪些使用者曾經購買過商品，並將使用者資訊顯示出來

mysql> select * from user right join order_goods on user.uid = order_goods.uid;

+------+-----------+----------+-----+ -----+---------------+-----------+

| uid | username | password | oid | uid | name | buytime |

+------+-----------+----------+-----+-----+-- -------------+-----------+

| 10 | 高小峰 | 3124qwqw | 1 | 10 | 蘋果滑鼠 | 1212313 || 3 | 王寶強 | 1235531 | 2 | 3 | iphone 12s | 123121241 || 12 | 李小超 | 311aq | 13232333 || 15 | 佟小剛 | 3cxvdfs | 4 | 15 | | 34242123 | +------+-----------+-------- --+-----+-----+---------------+-----------+子查詢有時候，當我們查詢的時候，需要的條件是另一個select語句的結果，這時就需要使用子查詢。用於子查詢的關鍵字包括in、not in、=、!=、exists、not exists等。類別詳細解示基本語法 #select 欄位 from 表where 欄位in(條件)#範例1select * from user where uid in (1,3,4);#範例1說明依照id 查詢指定使用者#範例2

\| 3 \| 王寶強 \| 1235531 \| 5 \| 3 \| iphone 鍵盤 \| 12123413 \|	5 rows in set (0.00 sec)

###select * from user where uid in ( select uid from order_goods);############範例2說明######將購買過商品的使用者資訊顯示出來############

範例1：

mysql> select * from user where uid in (1,3,4);
+-----+-----------+----------+
# | uid | username | password |
+-----+-----------+----------+
# | 1 | 景甜 | 123456 |
| 3 | 王寶強 | 1235531 |
| 4 | 井柏然 | 123455 |
+-----+-----------+----------+
# 3 rows in set (0.00 sec)

範例2：

##mysql> select * from user where uid in (select uid from order_goods) ;
+-----+-----------+----------+
# | uid | username | password |
+-----+-----------+----------+
# | 10 | 高小峰 | 3124qwqw |
| 3 | 王寶強 | 1235531 |
| 12 | 李小超 | 311aqqee |
| 15 | 佟小剛 | 3cxvdfs |
+-----+-----------+----------+
# 4 rows in set (0.00 sec)

mysql> select * from emp where deptno in (select deptno from dept);

#記錄聯合

#使用union 和union all 關鍵字，將兩個表的資料依照一定的查詢條件查詢出來後，將結果合併成一起顯示。兩者主要的差異是把結果直接合併在一起，而 union 是將 union all 後的結果進行一次distinct，去除重複記錄後的結果。

類別詳細解示基本語法 #select語句1 union[all] select語句2範例select * from user where uid in (1,3,4);範例說明將商品表中的使用者資訊和使用者資料表中的使用者資訊的結果組合在一起

mysql> select uid from user union select uid from order_goods;
+-----+
| uid |
+-----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
+-----+
15 rows in set (0.00 sec)