mysql-多表查詢

標籤：結構   資料   company   nbsp   char   去掉   dep   資訊   val   

一 介紹

本節主題

多表串連查詢

複合條件串連查詢

子查詢

 

準備表

company.employee
company.department

#建表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 |+----+------------+--------+------+--------+

 

二 多表串連查詢

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

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

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 | 運營         |+----+------------+--------+------+--------+------+--------------+

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

#找兩張表共有的部分，相當於利用條件從笛卡爾積結果中篩選出了正確的結果#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;

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

#以左表為準，即找出所有員工資訊，當然包括沒有部門的員工#本質就是：在內串連的基礎上增加左邊有右邊沒有的結果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         |+----+------------+--------------+

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

#以右表為準，即找出所有部門資訊，包括沒有員工的部門#本質就是：在內串連的基礎上增加右邊有左邊沒有的結果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      | 運營         |+------+-----------+--------------+

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

全外串連：在內串連的基礎上增加左邊有右邊沒有的和右邊有左邊沒有的結果#注意：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,employee.age from employee,department    where employee.dep_id = department.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 employee group by dep_id having count(id) <=1);

3 帶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)

 五 綜合練習 init.sql

#準備表、記錄mysql> create database db1;mysql> use db1;mysql> source /root/init.sql

 

！！！重中之重：練習之前務必搞清楚sql邏輯查詢語句的執行順序

連結：http://www.cnblogs.com/liluning/p/7490116.html

題目

參考答案：http://www.cnblogs.com/wupeiqi/articles/5748496.html

mysql-多表查詢