多表聯(lián)合查詢

表連接

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

1.??? 內(nèi)連結(jié)：將兩個(gè)表格中存在連結(jié)關(guān)係的欄位符合連結(jié)關(guān)係的那些記錄形成記錄集的連結(jié)。

2.??? 外連接：會(huì)選出其他不符的記錄，分為外左連接和外右連接。

在學(xué)習(xí)實(shí)驗(yàn)前，我為大家準(zhǔn)備了兩張模擬的資料表：

1.??? 使用者表，存放使用者資訊

2.??? 訂單表，存放哪一個(gè)使用者購(gòu)買過哪個(gè)商品

user表建立語(yǔ)句

     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表資料如下：

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

內(nèi)部連接

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

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

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 | 李文凱 | 2 | 3 | iphone 12s |
 | 12 | 李小超 | 3 | 12 | 雪碧 |
 | 15 | 佟小剛 | 4 | 15 | |
 | 3 | 李文凱 | 5 | 3 | iphone 鍵盤 |
 +-----+-----------+-----+-----+---------------+
 5 rows in set (0.00 sec)

基本語(yǔ)法二：

結(jié)果與基本語(yǔ)法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 | 李文凱 | 2 | 3 | iphone 12s |
 | 12 | 李小超 | 3 | 12 | 雪碧 |
 | 15 | 佟小剛 | 4 | 15 | |
 | 3 | 李文凱 | 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 | 李文凱 | 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 | 李小強(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 | 李文凱 | 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)

子查詢

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

範(fàn)例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)

範(fàn)例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);

記錄聯(lián)合

使用union 和union all 關(guān)鍵字，將兩個(gè)表格的資料依照一定的查詢條件查詢出來後，將結(jié)果合併到一起顯示。兩者主要的差異是把結(jié)果直接合併在一起，而 union 是將 union all 後的結(jié)果進(jìn)行一次distinct，去除重複記錄後的結(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)

?