mysql-----多表查詢

標籤：mysq   一個   包含   class   查詢   存在   卡爾   優先   any   

一、多表串連查詢

#重點  ：   外連結文法select 欄位列表  from 表1 inner | left | right join  表2  on 表1.欄位=表2.欄位；

1、交叉連結：不適用任何匹配條件。產生笛卡爾積select * from employee,department;2、內串連：只串連匹配的行select * from employee,department where employee.dep_id=department.id;  #不用這種形式select * from employee inner join department on employee.dep_id=department.id;3、外連結之左串連：優先顯示左表全部記錄select * from employee left join department on employee.dep_id=department.id;4、外連結之右串連：優先顯示右表全部記錄select * from employee right join department on employee.dep_id=department.id;5、全外連結：顯示左右兩個表全部記錄select * from employee left join department on employee.dep_id=department.idunionselect * from employee right join department on employee.dep_id=department.id;

二、符合條件串連查詢

 

#樣本1：以內串連的方式查詢employee和department表，並且employee表中的age欄位值必須大於25,即找出公司所有部門中年齡大於25歲的員工select employee.name,employee.age from employee,department where employee.dep_id=department.id and age>25;select  t1.name,t1.age,t2.name from employee as t1 left join department as t2 on t1.dep_id=t2.id and 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：還可以包含比較子：= 、 !=、> 、<等

1、帶in關鍵字的子查詢

#查詢employee表，但dep_id必須在department表中出現過select * from employee                             where dep_id in                             (select id from department);   

2、帶比較子的子查詢

#比較子：=、!=、>、>=、<、<=、<>#查詢平均年齡在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人的部門名select name from department where id in (select dep.id from department dep left join employee emp on dep.id = emp.dep_id    group by dep.id    having count(emp.id) < 1);select * from department where id not in (select dep_id from employee group by dep_id having count(id) >= 1);

3、帶exists關鍵字的子查詢

exists關鍵字表示存在。在使用exists關鍵字時，內層查詢語句不返回查詢結果。而是返回一個真假值。True或False

當返回True時，外層查詢語句將進行查詢；當傳回值為False時，外層查詢語句不進行查詢

#deparment表中存在dep_id=200時，Trueselect * from employee where exists (select id from department where id=200);因為括弧中為TRUE，返回的值為select * from employee語句的結果select * from employee where exists (select id from department where id=204);因為括弧中為Flase,外層不查詢，返回空集Empty set

 

mysql-----多表查詢