mysql資料操作之多表查詢

標籤：def   max   表示   creat   連表   order   art   運營   none   

一：介紹

主題：

　　多表串連查詢

　　符合條件串連查詢

　　子查詢

準備表

#建表create table department(id int,name varchar(20) );create table employee(id int primary key auto_increment,name varchar(20),sex enum(‘male‘,‘female‘) not null default ‘male‘,age int,dep_id int);#插入資料insert into department values(200,‘技術‘),(201,‘人力資源‘),(202,‘銷售‘),(203,‘運營‘);insert into employee(name,sex,age,dep_id) values(‘egon‘,‘male‘,18,200),(‘alex‘,‘female‘,48,201),(‘wupeiqi‘,‘male‘,38,201),(‘yuanhao‘,‘female‘,28,202),(‘liwenzhou‘,‘male‘,18,200),(‘jingliyang‘,‘female‘,18,204);#查看錶結構和資料mysql> desc department;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| id | int(11) | YES | | NULL | || name | varchar(20) | YES | | NULL | |+-------+-------------+------+-----+---------+-------+mysql> desc employee;+--------+-----------------------+------+-----+---------+----------------+| 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 department;+------+--------------+| id | name |+------+--------------+| 200 | 技術 || 201 | 人力資源 || 202 | 銷售 || 203 | 運營 |+------+--------------+mysql> select * from employee;+----+------------+--------+------+--------+| 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 |+----+------------+--------+------+--------+

View Code

二 多表串連查詢

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

1、內串連：把兩張表有對應關係的記錄串連成一張虛擬表

select * from emp inner join dep on emp.dep_id = dep.id;

#應用：
select * from emp,dep where emp.dep_id = dep.id and dep.name = "技術"; # 不要用where做連表的活

select * from emp inner join dep on emp.dep_id = dep.id
where dep.name = "技術"
;

2、左串連：在內串連的基礎上，保留左邊沒有對應關係的記錄
select * from emp left join dep on emp.dep_id = dep.id;

3、右串連：在內串連的基礎上，保留右邊沒有對應關係的記錄
select * from emp right join dep on emp.dep_id = dep.id;

4、全串連：在內串連的基礎上，保留左、右邊沒有對應關係的記錄
select * from emp left join dep on emp.dep_id = dep.id
union
select * from emp right join dep on emp.dep_id = dep.id;

 

#補充：多表串連可以不斷地與虛擬表串連

尋找各部門最高工資select t1.* from emp as t1inner join(select post,max(salary) as ms from emp group by post) as t2on t1.post = t2.postwhere t1.salary = t2.ms;

三：符合條件串連查詢

#樣本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、還可以包含比較子：=、！=、    >、<等

1、帶IN關鍵詞的子查詢

#子查詢：把一個查詢語句用括弧括起來，當做另一條查詢語句的條件去用，稱之為子查詢select emp.name from emp inner join dep on emp.dep_id =dep.id where dep.name=‘技術‘;select name from emp where dep_id =(select id from department where name =‘技術‘);

#查詢平均年齡在25歲以上的部門名字select 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 dep where name =‘技術‘);#查看不足1人的部門名（子查詢得到的是有人的部門id）select name from dep where id not in （select distinct dep_id from employee);#查看每個部門最新入職的那位員工select t1.id,t1.name,t1.post,t1.hire_date,t2.max_date from emp as t1 inner join(select post, max(hire_date) as max_date from emp group by post) as t2on t1.post = t2.postwhere t1.hire_date = t2.max_date;

2 帶比較子的子查詢

EXISTS關鍵詞表示存在。在使用EXISTS關鍵詞時，內層查詢語句不返回查詢的記錄。

而是返回一個真假值。TRUE或FALSE

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

判斷department表中存在dep_id=203，Trueselect * from employeewhere 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,Falseselect * from employee    where exists     (select id from department where id=204);

 

mysql資料操作之多表查詢