很多時(shí)候在實(shí)際的業(yè)務(wù)中我們不只是查詢一張表。

1. 在電子商務(wù)系統(tǒng)中，查詢哪些用戶沒有購買過產(chǎn)品。

2. 銀行中可能查詢違規(guī)記錄，同時(shí)查詢出用戶的

3. 查詢中獎(jiǎng)信息和中獎(jiǎng)人員的基本信息。

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

而上述業(yè)務(wù)中需要多表聯(lián)合在一起查詢才能有結(jié)果，而多表聯(lián)合查詢的本質(zhì)是：表連接。

表連接

當(dāng)需要查詢多個(gè)表中的字段時(shí)，就可以使用表連接來實(shí)現(xiàn)。表聯(lián)接分為內(nèi)連接和外連接。

1. 內(nèi)聯(lián)結(jié)：將兩個(gè)表中存在聯(lián)結(jié)關(guān)系的字段符合聯(lián)結(jié)關(guān)系的那些記錄形成記錄集的聯(lián)結(jié)。

2. 外連接：會選出其他不匹配的記錄，分為外左聯(lián)結(jié)和外右聯(lián)結(jié)。

在學(xué)習(xí)實(shí)驗(yàn)前，我為大家準(zhǔn)備了兩個(gè)模擬的數(shù)據(jù)表：

1. 用戶表，存放用戶信息
2. 訂單表，存放哪個(gè)用戶購買過哪個(gè)商品

user表創(chuàng)建語句

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 (order_goods (
   oid int(11) NOT NULL,
   uid int(11) NOT NULL,
   name varchar(50) NOT NULL,
   buytime ? ?oid int(11) NOT NULL,
? ?uid int(11) NOT NULL,

? ?name varchar(50) NOT NULL,

?) ENGINE=InnoDB DEFAULT CHARSET=utf8;

order_goods數(shù)據(jù)如下：

注意：在上表order_goods表中uid是指user表中的uid字段。上表中oid為1的數(shù)據(jù)行，uid為10的用戶。為user表中uid為10的用戶：高小峰。該用戶購買了商品為蘋果鼠標(biāo)。購買時(shí)間buytime為一個(gè)unix時(shí)間戳。

內(nèi)連接

基本語法一：

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

由于表名太長，每次寫的時(shí)候容易寫錯(cuò)。我們可以在表后直接跟上一個(gè)簡寫英文字符串。在前面拼接字段時(shí)，直接使用簡寫字符串.字段即可。

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 | 蘋果鼠標(biāo) ? ? ?|
?| ? 3 | 王寶強(qiáng) ? ?| ? 2 | ? 3 | iphone 12s ? ?|
?| ?12 | 李小超 ? ?| ? 3 | ?12 | 雪碧 ? ? ? ? ?|
?| ?15 | 佟小剛 ? ?| ? 4 | ?15 | ? ? ? ? ? ? ? |
?| ? 3 | 李文凱 ? ?| ? 5 | ? 3 | iphone 鍵盤 ? |
?+-----+-----------+-----+-----+---------------+
?5 rows in set (0.00 sec)

基本語法二：

結(jié)果與基本語法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 | 蘋果鼠標(biāo) ? ? ?|
?| ? 3 | 王寶強(qiáng) ? ?| ? 2 | ? 3 | iphone 12s ? ?|
?| ?12 | 李小超 ? ?| ? 3 | ?12 | 雪碧 ? ? ? ? ?|
?| ?15 | 佟小剛 ? ?| ? 4 | ?15 | ? ? ? ? ? ? ? |
?| ? 3 | 王寶強(qiáng) ? ?| ? 5 | ? 3 | iphone 鍵盤 ? |
?+-----+-----------+-----+-----+---------------+
?5 rows in set (0.00 sec)

外連接

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

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

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 | 蘋果鼠標(biāo) ? ? ?| ? 1212313 |
| ? 3 | 王寶強(qiáng) ? ?| 1235531 ? ?| ? ?2 | ? ?3 | iphone 12s ? ?| 123121241 |
| ?12 | 李小超 ? ?| 311aqqee ? | ? ?3 | ? 12 | 雪碧 ? ? ? ? ?| ?13232333 |
| ?15 | 佟小剛 ? ?| 3cxvdfs ? ?| ? ?4 | ? 15 | ? ? ? ? ? ? ? | ?34242123 |
| ? 3 | 王寶強(qiáng) ? ?| 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 | 李小強(qiáng) ? ?| 323fxfvdvd | NULL | NULL | NULL ? ? ? ? ?| ? ? ?NULL |
| ?13 | 韓小平 ? ?| 121rcfwrfq | NULL | NULL | NULL ? ? ? ? ?| ? ? ?NULL |
| ?14 | 宋小康 ? ?| 123123tcsd | NULL | NULL | NULL ? ? ? ? ?| ? ? ?NULL |
+-----+-----------+------------+------+------+---------------+-----------+
16 rows in set (0.00 sec)

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

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 | 蘋果鼠標(biāo) ? ? ?| ? 1212313 |
| ? ?3 | 王寶強(qiáng) ? ?| 1235531 ?| ? 2 | ? 3 | iphone 12s ? ?| 123121241 |
| ? 12 | 李小超 ? ?| 311aqqee | ? 3 | ?12 | 雪碧 ? ? ? ? ?| ?13232333 |
| ? 15 | 佟小剛 ? ?| 3cxvdfs ?| ? 4 | ?15 | ? ? ? ? ? ? ? | ?34242123 |
| ? ?3 | 王寶強(qiáng) ? ?| 1235531 ?| ? 5 | ? 3 | iphone 鍵盤 ? | ?12123413 |
+------+-----------+----------+-----+-----+---------------+-----------+
5 rows in set (0.00 sec)

子查詢

有時(shí)候，當(dāng)我們查詢的時(shí)候，需要的條件是另外一個(gè)select語句的結(jié)果，這時(shí)就需要使用子查詢。用于子查詢的關(guān)鍵字包括in、not in、=、!=、exists、not exists等。

示例1：

mysql> select * ?from user where uid in (1,3,4);
?+-----+-----------+----------+
?| uid | username ?| password |
?+-----+-----------+----------+
?| ? 1 | 景甜 ? ? ?| 123456 ? |
?| ? 3 | 王寶強(qiáng) ? ?| 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 | 王寶強(qiáng) ? ?| 1235531 ?|
?| ?12 | 李小超 ? ?| 311aqqee |
?| ?15 | 佟小剛 ? ?| 3cxvdfs ?|
?+-----+-----------+----------+
?4 rows in set (0.00 sec)

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

記錄聯(lián)合

使用 union 和 union all 關(guān)鍵字，將兩個(gè)表的數(shù)據(jù)按照一定的查詢條件查詢出來后，將結(jié)果合并到一起顯示。兩者主要的區(qū)別是把結(jié)果直接合并在一起，而 union 是將 union all 后的結(jié)果進(jìn)行一次distinct，去除重復(fù)記錄后的結(jié)果。

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)