編程之路：MySql系列之多表查詢

標籤：匹配   nio   --   必須   any   間接   文法   優先   full join   

多表串連查詢

#重點：外連結文法SELECT 欄位列表    FROM 表1 INNER|LEFT|RIGHT JOIN 表2    ON 表1.欄位 = 表2.欄位;

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

mysql> select * from employee,department;+----+------------+--------+------+--------+------+--------------+| id | name       | sex    | age  | dep_id | id   | name         |+----+------------+--------+------+--------+------+--------------+|  1 | egon       | male   |   18 |    200 |  200 | 技術         ||  1 | egon       | male   |   18 |    200 |  201 | 人力資源     ||  1 | egon       | male   |   18 |    200 |  202 | 銷售         ||  1 | egon       | male   |   18 |    200 |  203 | 運營         ||  2 | alex       | female |   48 |    201 |  200 | 技術         ||  2 | alex       | female |   48 |    201 |  201 | 人力資源     ||  2 | alex       | female |   48 |    201 |  202 | 銷售         ||  2 | alex       | female |   48 |    201 |  203 | 運營         ||  3 | wupeiqi    | male   |   38 |    201 |  200 | 技術         ||  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力資源     ||  3 | wupeiqi    | male   |   38 |    201 |  202 | 銷售         ||  3 | wupeiqi    | male   |   38 |    201 |  203 | 運營         ||  4 | yuanhao    | female |   28 |    202 |  200 | 技術         ||  4 | yuanhao    | female |   28 |    202 |  201 | 人力資源     ||  4 | yuanhao    | female |   28 |    202 |  202 | 銷售         ||  4 | yuanhao    | female |   28 |    202 |  203 | 運營         ||  5 | liwenzhou  | male   |   18 |    200 |  200 | 技術         ||  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力資源     ||  5 | liwenzhou  | male   |   18 |    200 |  202 | 銷售         ||  5 | liwenzhou  | male   |   18 |    200 |  203 | 運營         ||  6 | jingliyang | female |   18 |    204 |  200 | 技術         ||  6 | jingliyang | female |   18 |    204 |  201 | 人力資源     ||  6 | jingliyang | female |   18 |    204 |  202 | 銷售         ||  6 | jingliyang | female |   18 |    204 |  203 | 運營         |+----+------------+--------+------+--------+------+--------------+

 

內串連：只串連匹配的行

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

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

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

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

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

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

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

符合條件串連查詢

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

子查詢

#1：子查詢是將一個查詢語句嵌套在另一個查詢語句中。#2：內層查詢語句的查詢結果，可以為外層查詢語句提供查詢條件。#3：子查詢中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等關鍵字#4：還可以包含比較子：= 、 !=、> 、<等

帶IN關鍵字的子查詢

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

帶比較子的子查詢

#比較子：=、!=、>、>=、<、<=、<>#查詢大於所有人平均年齡的員工名與年齡mysql> select name,age from emp where age > (select avg(age) from emp);+---------+------+| name | age |+---------+------+| alex | 48 || wupeiqi | 38 |+---------+------+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;

帶EXISTS關鍵字的子查詢

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

#department表中存在dept_id=203，Turemysql> select * from employee    ->     where exists    ->         (select id from department where id=200);+----+------------+--------+------+--------+| id | name       | sex    | age  | dep_id |+----+------------+--------+------+--------+|  1 | egon       | male   |   18 |    200 ||  2 | alex       | female |   48 |    201 ||  3 | wupeiqi    | male   |   38 |    201 ||  4 | yuanhao    | female |   28 |    202 ||  5 | liwenzhou  | male   |   18 |    200 ||  6 | jingliyang | female |   18 |    204 |+----+------------+--------+------+--------+#department表中存在dept_id=205，Falsemysql> select * from employee    ->     where exists    ->         (select id from department where id=204);Empty set (0.00 sec)

 

編程之路：MySql系列之多表查詢