12.8.3 多表聯合查詢 · 3天入門MySQL

很多時候在實際的業務中我們不只是查詢一張表。 1. 在電子商務系統中，查詢哪些用戶沒有購買過產品。 2. 銀行中可能查詢違規記錄，同時查詢出用戶的 3. 查詢中獎信息和中獎人員的基本信息。以上只是列的情況我們就需要把兩張表在一起進行查詢。而上述業務中需要多表聯合在一起查詢才能有結果，而多表聯合查詢的本質是：表連接。 ##表連接當需要查詢多個表中的字段時，就可以使用表連接來實現。表聯接分為內連接和外連接。 1. 內聯結：將兩個表中存在聯結關系的字段符合聯結關系的那些記錄形成記錄集的聯結。 2. 外連接：會選出其他不匹配的記錄，分為外左聯結和外右聯結。在學習實驗前，我為大家準備了兩個模擬的數據表： 1. 用戶表，存放用戶信息 2. 訂單表，存放哪個用戶購買過哪個商品 **user表創建語句** > CREATE TABLE IF NOT EXISTS `user` ( `uid` int(11) NOT NULL, `username` varchar(30) NOT NULL, `password` char(32) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; > CREATE TABLE IF NOT EXISTS `order_goods` ( `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 | password | | -- | -- | | |1|景甜|123456| |2|王小二|245667| |3|李文凱|1235531| |4|井柏然|123455| |5|范冰冰|5abcwa| |6|黃曉明|abcdeef| |7|anglebaby|caption| |8|TFBOYS|abcdwww| |9|安小超|12tfddwd| |10|高小峰|3124qwqw| |11|李小強|323fxfvdvd| |12|李小超|311aqqee| |13|韓小平|121rcfwrfq| |14|宋小康|123123tcsd| |15|佟小剛|3cxvdfs| order_goods數據如下： |oid|uid|name|buytime| | -- | -- |-- |-- | |1|10|蘋果鼠標|1212313| |2|3|iphone 12s|123121241| |3|12|雪碧|13232333| |4|15||34242123| |5|3|iphone 鍵盤|12123413| **注意：** 在上表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 join order_goods on user.uid = order_goods.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 user.uid = order_goods.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 | 李小超 | 311aqqee | 3 | 12 | 雪碧 | 13232333 | | 15 | 佟小剛 | 3cxvdfs | 4 | 15 | | 34242123 | | 3 | 李文凱 | 1235531 | 5 | 3 | iphone 鍵盤 | 12123413 | | 1 | 景甜 | 123456 | NULL | NULL | NULL | NULL | | 2 | 王小二 | 245667 | NULL | NULL | NULL | NULL | | 4 | 井柏然 | 123455 | NULL | NULL | NULL | NULL | | 5 | 范冰冰 | 5abcwa | NULL | NULL | NULL | NULL | | 6 | 黃曉明 | abcdeef | NULL | NULL | NULL | NULL | | 7 | anglebaby | caption | NULL | NULL | NULL | NULL | | 8 | TFBOYS | abcdwww | NULL | NULL | NULL | NULL | | 9 | 安小超 | 12tfddwd | NULL | NULL | NULL | NULL | | 11 | 李小強 | 323fxfvdvd | NULL | NULL | NULL | NULL | | 13 | 韓小平 | 121rcfwrfq | NULL | NULL | NULL | NULL | | 14 | 宋小康 | 123123tcsd | NULL | NULL | NULL | 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; | | 示例說明 |查詢商品表中哪些用戶購買過商品，并將用戶信息顯示出來 | 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 | 李小超 | 311aqqee | 3 | 12 | 雪碧 | 13232333 | | 15 | 佟小剛 | 3cxvdfs | 4 | 15 | | 34242123 | | 3 | 李文凱 | 1235531 | 5 | 3 | iphone 鍵盤 | 12123413 | +------+-----------+----------+-----+-----+---------------+-----------+ 5 rows in set (0.00 sec) ##子查詢有時候，當我們查詢的時候，需要的條件是另外一個select語句的結果，這時就需要使用子查詢。用于子查詢的關鍵字包括in、not in、=、!=、exists、not exists等。 | 類別 | 詳細解示 | | -- | -- | | 基本語法 | select 字段 from 表 where 字段 in(條件) | | 示例1 | select * from user where uid in (1,3,4);| | 示例1說明 |按照id 查詢指定用戶 | | 示例2 |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)