MySQL增刪改查之【多表聯集查詢】

標籤：表資料   use   efault   其他   varchar   code   王寶強   聯合   右串連   

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

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)

 

轉自：http://www.php.cn/code/4708.html

MySQL增刪改查之【多表聯集查詢】