很多時候在實際的業務中我們不只是查詢一張表。
在電子商務系統中,查詢哪些使用者沒有購買過產品。
銀行中可能會查詢違規記錄,同時查詢出用戶的
#查詢中獎資訊和中獎人員的基本資訊。
以上只是列的情況我們就需要把兩張表在一起進行查詢。
而上述業務中需要多表聯合在一起查詢才能有結果,而多表聯合查詢的本質是:表連接。
當需要查詢多個表格中的欄位時,就可以使用表格連接來實現。表聯接分為內連接和外連接。
內聯結:將兩個表中存在聯結關係的欄位符合連結關係的那些記錄形成記錄集的聯結。
外連接:會選取其他不符的記錄,分成外左聯結和外右聯結。
在學習實驗前,我為大家準備了兩個模擬的資料表:
user表建立語句
varchar(50) NOT NULL,#CREATE TABLE IF NOT EXISTS
passworduser
(
uid
int(11) NOT NULL,
username
varchar(30) NOT NULL,
password
# char(32) NOTULL,
# char(32) NOT NULL
name) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTSorder_goods
(
oid
int(11) NOT NULL,
oid
int(11) NOT NULL,
uid
int(11) NOT NULL,
#buytime
user表資料如下: | ##uid | |
---|---|---|
#password | ||
1 | 景甜 | 123456 |
2 | 王小二 | 245667 |
#3 | 王強 | 1235531 |
#1235531 | ||
井柏然 | 123455 | |
范冰冰 | 5abcwa | |
黃曉明 | abcdeef | |
anglebaby | # caption | |
TFBOYS | #abcdwww | |
安小超 | 12tfddwd | |
#高小峰 | 3124qwqw | |
李小強 | 323fxfvdvd |
order_goods資料如下:
oid | #uid | name | buytime |
---|---|---|---|
1 | 10 | 蘋果滑鼠 | 1212313 |
3 | iphone 12s | 123121241 | |
12 | #雪碧 | 13232333 | |
15 | |||
3 | iphone 鍵盤 | 12123413 |
注意:在上表order_goods表中uid是指user表中的uid欄位。上表中oid為1的資料行,uid為10的使用者。為user表中uid為10的使用者:高小峰。該用戶購買了商品為蘋果滑鼠。購買時間buytime為一個unix時間戳。
內連接基本語法一:詳細說明 | |
---|---|
註:下例中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; | |
查詢商品表中哪些使用者購買過商品,並將使用者資訊顯示出來 |
說明 | |
---|---|
外連接又分為左連接和右鏈接,具體定義如下。
左連接:包含所有的左邊表中的記錄甚至是右邊表中沒有和它匹配的記錄
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水平
範例說明
查詢商品表中哪些使用者曾經購買過商品,並將使用者資訊顯示出來
+------+-----------+----------+-----+-----+-- -------------+-----------+
| 10 | 高小峰 | 3124qwqw | 1 | 10 | 蘋果滑鼠 | 1212313 || 3 | 王寶強 | 1235531 | 5 | 3 | iphone 鍵盤 | 12123413 | | +------+-----------+-------- --+-----+-----+---------------+-----------+5 rows in set (0.00 sec) |
---|---|
類別 | |
#select 欄位 from 表where 欄位in(條件) | |
select * from user where uid in (1,3,4); | |
依照id 查詢指定使用者 |
範例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) ;mysql> select * from emp where deptno in (select deptno from dept);#記錄聯合#使用union 和union all 關鍵字,將兩個表的資料依照一定的查詢條件查詢出來後,將結果合併成一起顯示。兩者主要的差異是把結果直接合併在一起,而 union 是將 union all 後的結果進行一次distinct,去除重複記錄後的結果。+-----+-----------+----------+
# | uid | username | password |
+-----+-----------+----------+
# | 10 | 高小峰 | 3124qwqw |
| 3 | 王寶強 | 1235531 |
| 12 | 李小超 | 311aqqee |
| 15 | 佟小剛 | 3cxvdfs |
+-----+-----------+----------+
# 4 rows in set (0.00 sec)
詳細解示 | |
---|---|
#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)