Mysql-多表串連的操作和用法

標籤：exists   nbsp   des   AC   style   串連   sel   table   實現   

• 一 、介紹
• 二 、多表串連查詢
• 三 、符合條件串連查詢
• 四 、子查詢

一、介紹

本節主題

• 多表串連查詢
• 複合條件串連查詢
• 子查詢

準備表

#建表create table dep(id int,name varchar(20) );create table emp(id int primary key auto_increment,name varchar(20),sex enum(‘male‘,‘female‘) not null default ‘male‘,age int,dep_id int);#插入資料insert into dep values(200,‘技術‘),(201,‘人力資源‘),(202,‘銷售‘),(203,‘運營‘);insert into emp(name,sex,age,dep_id) values(‘大娃‘,‘male‘,18,200),(‘二娃‘,‘female‘,48,201),(‘三娃‘,‘male‘,38,201),(‘四娃‘,‘female‘,28,202),(‘五娃‘,‘male‘,18,200),(‘六娃‘,‘female‘,18,204);#查看錶結構和資料mysql> desc dep;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+mysql> desc emp;+--------+-----------------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+-----------------------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | varchar(20) | YES | | NULL | || sex | enum(‘male‘,‘female‘) | NO | | male | || age | int(11) | YES | | NULL | || dep_id | int(11) | YES | | NULL | |+--------+-----------------------+------+-----+---------+----------------+mysql> select * from dep;+------+--------------+| id | name |+------+--------------+| 200 | 技術 || 201 | 人力資源 || 202 | 銷售 || 203 | 運營 |+------+--------------+mysql> select * from emp;+----+------------+--------+------+--------+| id | name | sex | age | dep_id |+----+------------+--------+------+--------+| 1 | 大娃 | male | 18 | 200 || 2 | 二娃 | female | 48 | 201 || 3 | 三娃 | male | 38 | 201 || 4 | 四娃 | female | 28 | 202 || 5 | 五娃 | male | 18 | 200 || 6 | 六娃 | female | 18 | 204 |+----+------------+--------+------+--------+

dep表和emp表二 、多表串連查詢

#重點：外連結文法

SELECT 欄位列表

FROM 表1

INNER|LEFT|RIGHT JOIN 表2

ON 表1.欄位 = 表2.欄位;

1 、交叉串連：不適用任何匹配條件。產生笛卡爾積

mysql> select * from emp,dep;+----+------------+--------+------+--------+------+--------------+| id | name       | sex    | age  | dep_id | id   | name         |+----+------------+--------+------+--------+------+--------------+|  1 | 大娃       | male   |   18 |    200 |  200 | 技術         ||  1 | 大娃       | male   |   18 |    200 |  201 | 人力資源     ||  1 | 大娃       | male   |   18 |    200 |  202 | 銷售         ||  1 | 大娃       | male   |   18 |    200 |  203 | 運營         ||  2 | 二娃       | female |   48 |    201 |  200 | 技術         ||  2 | 二娃       | female |   48 |    201 |  201 | 人力資源     ||  2 | 二娃       | female |   48 |    201 |  202 | 銷售         ||  2 | 二娃       | female |   48 |    201 |  203 | 運營         ||  3 | 三娃       | male   |   38 |    201 |  200 | 技術         ||  3 | 三娃       | male   |   38 |    201 |  201 | 人力資源     ||  3 | 三娃       | male   |   38 |    201 |  202 | 銷售         ||  3 | 三娃       | male   |   38 |    201 |  203 | 運營         ||  4 | 四娃       | female |   28 |    202 |  200 | 技術         ||  4 | 四娃       | female |   28 |    202 |  201 | 人力資源     ||  4 | 四娃       | female |   28 |    202 |  202 | 銷售         ||  4 | 四娃       | female |   28 |    202 |  203 | 運營         ||  5 | 五娃       | male   |   18 |    200 |  200 | 技術         ||  5 | 五娃       | male   |   18 |    200 |  201 | 人力資源     ||  5 | 五娃       | male   |   18 |    200 |  202 | 銷售         ||  5 | 五娃       | male   |   18 |    200 |  203 | 運營         ||  6 | 六娃       | female |   18 |    204 |  200 | 技術         ||  6 | 六娃       | female |   18 |    204 |  201 | 人力資源     ||  6 | 六娃       | female |   18 |    204 |  202 | 銷售         ||  6 | 六娃       | female |   18 |    204 |  203 | 運營         |+----+------------+--------+------+--------+------+--------------+

交叉串連示範

2 、內串連：只串連匹配的行

#找兩張表共有的部分，相當於利用條件從笛卡爾積結果中篩選出了正確的結果#dep沒有204這個部門，因而emp表中關於204這條員工資訊沒有匹配出來mysql> select emp.id,emp.name,emp.age,emp.sex,dep.name from emp inner join dep on emp.dep_id=dep.id; +----+-----------+------+--------+--------------+| id | name      | age  | sex    | name         |+----+-----------+------+--------+--------------+|  1 | 大娃     |   18 | male    | 技術         ||  2 | 二娃     |   48 | female | 人力資源     ||  3 | 三娃     |   38 | male    | 人力資源     ||  4 | 四娃     |   28 | female | 銷售         ||  5 | 五娃     |   18 | male    | 技術         |+----+-----------+------+--------+--------------+#上述sql等同於mysql> select emp.id,emp.name,emp.age,emp.sex,dep.name from emp,dep where emp.dep_id=dep.id;

內串連示範

3 、外連結之左串連：優先顯示左表全部記錄

#以左表為準，即找出所有員工資訊，當然包括沒有部門的員工#本質就是：在內串連的基礎上增加左邊有右邊沒有的結果mysql> select emp.id,emp.name,dep.name as depart_name from emp left join dep on emp.dep_id=dep.id;+----+------------+--------------+| id | name       | depart_name  |+----+------------+--------------+|  1 | 大娃       | 技術         ||  5 | 五娃       | 技術         ||  2 | 二娃       | 人力資源     ||  3 | 三娃       | 人力資源     ||  4 | 四娃       | 銷售         ||  6 | 六娃       | NULL         |+----+------------+--------------+

左串連示範

4 、外連結之右串連：優先顯示右表全部記錄

#以右表為準，即找出所有部門資訊，包括沒有員工的部門#本質就是：在內串連的基礎上增加右邊有左邊沒有的結果mysql> select emp.id,emp.name,dep.name as depart_name from emp right join dep on emp.dep_id=dep.id;+------+-----------+--------------+| id   | name      | depart_name  |+------+-----------+--------------+|    1 | 大娃       | 技術         ||    2 | 二娃       | 人力資源     ||    3 | 三娃       | 人力資源     ||    4 | 四娃       | 銷售         ||    5 | 五娃       | 技術         || NULL | NULL       | 運營         |+------+-----------+--------------+

右串連示範

5 全外串連：顯示左右兩個表全部記錄

全外串連：在內串連的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果#注意：mysql不支援全外串連 full JOIN#強調：mysql可以使用此種方式間接實現全外串連select * from emp left join dep on emp.dep_id = dep.idunionselect * from emp right join dep on emp.dep_id = dep.id;#查看結果+------+------------+--------+------+--------+------+--------------+| id   | name       | sex    | age  | dep_id | id   | name         |+------+------------+--------+------+--------+------+--------------+|    1 | 大娃       | male   |   18 |    200 |  200 | 技術         ||    5 | 五娃       | male   |   18 |    200 |  200 | 技術         ||    2 | 二娃       | female |   48 |    201 |  201 | 人力資源     ||    3 | 三娃       | male   |   38 |    201 |  201 | 人力資源     ||    4 | 四娃       | female |   28 |    202 |  202 | 銷售         ||    6 | 六娃       | female |   18 |    204 | NULL | NULL         || NULL | NULL       | NULL   | NULL |   NULL |  203 | 運營         |+------+------------+--------+------+--------+------+--------------+#注意 union與union all的區別：union會去掉相同的紀錄

全外接示範三 、符合條件串連查詢

#樣本1：以內串連的方式查詢emp和dep表，並且emp表中的age欄位值必須大於25,即找出年齡大於25歲的員工以及員工所在的部門select emp.name,dep.name from emp inner join dep    on emp.dep_id = dep.id    where age > 25;#樣本2：以內串連的方式查詢emp和dep表，並且以age欄位的升序方式顯示select emp.id,emp.name,emp.age,dep.name from emp,dep    where emp.dep_id = dep.id    and age > 25    order by age asc;

具體串連示範四 、子查詢

#1：子查詢是將一個查詢語句嵌套在另一個查詢語句中。

#2：內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。

#3：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關鍵字

#4：還可以包含比較子：= 、 !=、> 、<等

1 、帶IN關鍵字的子查詢

#查詢平均年齡在25歲以上的部門名select id,name from dep    where id in         (select dep_id from emp group by dep_id 、having avg(age) > 25);#查看技術部員工姓名select name from emp    where dep_id in         (select id from dept where name=‘技術‘);#查看不足1人的部門名(子查詢得到的是有人的部門id)
select name from dep where id not in (select distinct dep_id from emp);

2 帶比較子的子查詢

#比較子：=、!=、>、>=、<、<=、<>#查詢大於所有人平均年齡的員工名與年齡mysql> select name,age from emp where age > (select avg(age) from emp);+---------+------+| name | age |+---------+------+| 大娃 | 48 || 三娃 | 38 |+---------+------+2 rows in set (0.00 sec)#查詢大於部門內平均年齡的員工名、年齡select t1.name,t1.age from emp t1inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2on t1.dep_id = t2.dep_idwhere t1.age > t2.avg_age; 

3 、帶EXISTS關鍵字的子查詢

EXISTS關字鍵字表示存在。在使用EXISTS關鍵字時，內層查詢語句不返回查詢的記錄。
而是返回一個真假值。True或False
當返回True時，外層查詢語句將進行查詢；當傳回值為False時，外層查詢語句不進行查詢

#dept表中存在dept_id=203，Turemysql> select * from emp    ->     where exists    ->         (select id from dep where id=200);+----+------------+--------+------+--------+| id | name       | sex    | age  | dep_id |+----+------------+--------+------+--------+|  1 | 大娃       | male   |   18 |    200 ||  2 | 二娃       | female |   48 |    201 ||  3 | 三娃       | male   |   38 |    201 ||  4 | 四娃       | female |   28 |    202 ||  5 | 五娃       | male   |   18 |    200 ||  6 | 六娃       | female |   18 |    204 |+----+------------+--------+------+--------+#dep表中存在dept_id=205，Falsemysql> select * from emp    ->     where exists    ->         (select id from dep where id=204);Empty set (0.00 sec)

 

Mysql-多表串連的操作和用法