這篇文章是SQL最重要的部分,在未來的面試中很有可能會用到。所以,掌握起來也相對不容易,但是細細分析,會發現,資料庫多表查詢是最讓人心情爽快的一件事,讓複雜的問題變得簡單,在分析需求的同時,可以思考人生,也可以理解到SQL語句的強大的魅力之處,試想如果沒有SQL 語句,我們的公司以及生活又會是怎樣呢?
多表查詢-SQL語言的利器。
四- 多表查詢
1 什麼是多表查詢
多表查詢:
當查詢的資料並不是來源一個表時,需要使用 多表連結操作完成查詢。根據
不同表中的資料之間的關係查詢相關聯的資料。
多表連結方式:
內連線:
連線兩個表,透過相等或不等判斷連結列,稱為內連線。在內連線中典型
的聯接運算有 = 或 <> 之類的比較運算子。包括等值聯接和自然聯接
等值連線
非等值連線
自連線
SQL99:交叉連結(CROSS JOIN)
SQL99:內連線(INNER JOIN)
SQL99:自然連結(NATURAL JOIN)
外連線:
在兩個表之間的連線,返回內連線的結果,同時還返回不匹配行的左(或右)表的連線,稱為左(或右)連線。返回內連線的結果, 同時還返回左和右連線,稱為全連線。
左外連結
右外連結
全外連結
子查詢:
當一個查詢是另一個查詢的條件時,稱之為子查詢。
2 笛卡爾乘積
2。1 什麼是笛卡爾乘積
笛卡爾乘積是指在數學中,兩個集合 X 和 Y 的笛卡尓積(Cartesian product),又稱直
積,表示為 X * Y,第一個物件是 X 的成員而第二個物件是 Y 的所有可能有序對的其中一
個成員。
2。2 如何避免出現笛卡爾乘積
當一個連線條件無效或被遺漏時,其結果是一個笛卡爾乘積 (Cartesian product),其中
所有行的組合都被顯示。第一個表中的所有行連線到第二個表中的所有行。一個笛卡爾乘積
會產生大量的行,其結果沒有什麼用。應該在 WHERE 子句中始終包含一個有效的連線條
件。
2.3 示例
select * from employees,departments;
3 多表 連線 語法
3.1 語法結構
使用一個連線從多個表中查詢資料。
• 在 WHERE 子句中寫連線條件。
• 當多個表中有相同的列名時,將表名或者表的別名作為列名的字首。
3。2 定義連線
當資料從多表中查詢時,要使用連線 (join) 條件。一個表中的行按照存在於相應列中的值被連
接到另一個表中的行。
3。3 原則
• 在寫一個連線表的 SELECT 語句時,在列名前面用表名或者表別名可以使語義清楚,
並且加快資料庫訪問。
• 為了連線 n 個表在一起,你最少需要 n-1 個連線條件。例如,為了連線 4 個表,
最少需要 3 個連線條件。
4 等值連線
4。1 什麼等值連線
等值連線
也被稱為簡單連線 (simple joins) 或內連線 (inner joins)。是透過等號來判斷連
接條件中的資料值是否相匹配。
4。2 抉擇矩陣 (decision matrix)
是透過行與列來分析一個查詢的方式。
例如,如果你想顯示同一個部門中所有名字為 Taylor 的僱員的名字和部門名稱,可以
寫出下面的決策矩陣:
投影列 源表 條件
last_name employees last_name = ‘Taylor’
department_name departments employees。department_id =
departments。department_id;
4。2。1 示例一
查詢所有僱員名字以及他們所在的部門名稱。
select last_name,department_name from employees , departments where employees。department_id = departments。department_id;
4.3 使用 AND 運算子附加搜尋條件
除連線之外,還可以要求用 WHERE 子句在連線中限制一個或多個表中的行。
4。3。1 示例二
顯示同一個部門中所有名字為 Taylor 的僱員的名字和部門號。
select last_name,department_name from
employees , departments where
employees。department_id =
departments。department_id and last_name =
‘Taylor’;
4。4 使用表別名
使用表別名簡化查詢語句的長度。
4。4。1 表別名
可以使用表別名代替表名。就象列別名給列另一個名字一樣。表別名有助於保持 SQL
程式碼較小,因此使用的儲存器也少。
4。4。2 使用表別名 原則
• 表別名最多可以有 30 個字元,但短一些更好。
• 如果在 FROM 子句中表別名被用於指定的表,那麼在整個 SELECT 語句中都可以
使用表別名。
• 表別名應該是有意義的。
• 表別名只對當前的 SELECT 語句有效。
4。4。3 示例
使用表別名方式改寫顯示同一個部門中所有名字為 Taylor 的僱員的名字和部門號。
select em。last_name,de。department_name
from employees em,departments de where
em。department_id = de。department_id and
em。last_name = ‘Taylor’;
4。5 多於兩個表的連線
為了連線 n 個表,你最少需要 n-1 個連線條件。例如,為了連線 3 個表,最少需要
兩個連線。
4。5。1 示例一
查詢每個僱員的 last name、departmentname 和 city(city 來源於 locations 表)。
select
em。last_name,de。department_name ,lo。city
from employees em ,departments de,locations lo
where em。department_id = de。department_id and
de。location_id = lo。location_id;
4。5。2 示例二
查詢 Taylor 的僱員 ID、部門名稱、和工作的城市。
select em。employee_id,
em。last_name,de。department_name ,lo。city
from employees em ,departments de,locations lo
where em。department_id = de。department_id and
de。location_id = lo。location_id and
em。last_name = ‘Taylor’;
5 非等值連線
5。1 非等值連線
一個非等值連線是一種不使用相等(=)作為連線條件的查詢。如!=、>、<、>=、<=、
BETWEEN AND 等都是非等值連結的條件判斷。
5.2 建立案例表
create table JOB_GRADES
(
lowest_sal NUMBER,
highest_sal NUMBER,
gra VARCHAR2(10)
)
5。3 示例
查詢所有僱員的薪水級別。
select em。last_name,em。salary,gr。gra
from employees em ,job_grades gr where
em。salary between gr。lowest_sal and
gr。highest_sal;
6 自連線
6。1 什麼是自連線
使用一個表連線它自身的操作。
6。2 示例
查詢每個僱員的經理的名字以及僱員的名字。
select
worker。last_name,manager。last_name from
employees worker,employees manager where
worker。manager_id = manager。employee_id;
7 外連線(OUTER JOIN)
7。1 什麼是外連線
外連線是指查詢出符合連線條件的資料同時還包含孤兒資料。左外連結包含左表的孤兒
資料,右外連線包含右表的孤兒資料,全外連線包含兩個表中的孤兒資料。
7。2 孤兒資料(Orphan Data)
孤兒資料是指被連線的列的值為空的資料。
7。3 外連線型別
左外(LEFT OUTER JOIN):包含左表的孤兒資料。
右外(RIGHT OUTER JOIN):包含右表的孤兒資料。
全外(FULL OUTER JOIN):包含兩個表中的孤兒資料。
7。4 SQL99 中的外連線
SQL99 外連線語法格式:
用 LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN 定義連線型別,用
ON 子句建立連線條件。
7.4.1 左外連結(LEFT OUTER JOIN)
7.4.1.1 示例
用左外連結查詢僱員名字以及他們所在的部門名稱,包含那些沒有部門的僱員。
select e。last_name,d。department_name from
employees e left outer join departments d on
e。department_id = d。department_id;
7.4.2 右外連結(RIGHT OUTER JOIN)
7.4.2.1 示例
用右外連結查詢僱員名字以及他們所在的部門名稱,包含那些沒有僱員的部門。
select e。last_name,d。department_name from
employees e right outer join departments d on
e。department_id = d。department_id;
7。4。3 全外連結(FULLOUTER JOIN)
7.4.3.1 示例
查詢所有僱員和部門,包含那些沒有僱員的部門以及沒有部門的僱員。
select e。last_name,d。department_name from
employees e full outer join departments d on
e。department_id = d。department_id;
7。5 Oracle 擴充套件的外連線
在 Oracle 資料庫中對外連線中的左外與右外連線做了擴充套件,可以簡化外連線的語法。
透過在連線條件的後側使用(+)來表示是否顯示孤兒資料,有(+)表示不顯示孤兒資料而另一
側則顯示孤兒資料。 但是該種寫法僅能在 Oracle 資料庫中使用。
7。5。1 示例一
查詢僱員名字以及他們所在的部門名稱,包含那些沒有僱員的部門。
select e。last_name,d。department_name from
employees e ,departments d where
e。department_id(+) = d。department_id;
7。5。2 示例二
查詢僱員名字以及他們所在的部門名稱,包含那些沒有部門的僱員。
select e。last_name,d。department_name from
employees e ,departments d where
e。department_id = d。department_id(+);
8 SQL99 中的交叉連線
• CROSS JOIN 子句導致兩個表的交叉乘積
• 該連線和兩個表之間的笛卡爾乘積是一樣的
8。1 示例
查詢 Employees 表與 Departments 表的笛卡爾乘積。
select * from employees cross join
departments;
9 SQL99 中的自然連線(NATURALJOIN)
• NATURAL JOIN 子句基於兩個表之間有相同名字的所有列。
• 它從兩個表中選擇在所有的匹配列中有相等值的行。
• 如果有相同名字的列的資料型別不同,返回一個錯誤。
9。1 使用自然連線需要注意
1。如果做自然連線的兩個表的有多個欄位都滿足有相同名稱個型別,那麼他們會被作為
自然連線的條件。
2。如果自然連線的兩個表僅是欄位名稱相同,但資料型別不同,那麼將會返回一個錯誤。
3。由於 oracle 中可以進行這種非常簡單的 natural join,我們在設計表時對具有相同含
義的欄位需要考慮到使用相同的名字和資料型別。
9。2 示例
查詢部門 ID,部門名稱以及他們所在的城市。
select d。department_id,d。department_name,LOST&FOUND
from departments d natural join locations l;
9。3 用 USING 子句建立連線
• 當有多個列匹配時,用 USING 子句匹配唯一的列。
• 如果某列在 USING 中使用,那麼在引用該列時不要使用表名或者別名。
• NATURAL JOIN 和 USING 子句是相互排斥的。
9。3。1 示例
查詢 location_id 為 1800 的部門名稱以及他們所在的城市名稱,指定 location_id 為連線
列。
select d。department_name,LOST&FOUND from
departments d join locations l
using(location_id) where location_id = 1800;
10 SQL99 中的內連線(INNER JOIN)
內連線(INNER JOIN): 內連線透過 INNER JOIN 來建立兩個表的連線。在內連線中使
用 INNER JOIN 作為表的連線,用 ON 子句給定連線條件。INNER JOIN 語句在效能上其他
語句沒有效能優勢。
10。1 示例
查詢僱員 id 為 202 的僱員名字,部門名稱,以及工作的城市。
等值連線:
select e。last_name,d。department_name,LOST&FOUND
from employees e,departments d ,locations l
where e。department_id = d。department_id and
d。location_id = l。location_id and
e。employee_id = 202;
內連線:
select e。last_name,d。department_name,LOST&FOUND from employees e inner join departments d on
e。department_id = d。department_id inner join
locations l on d。location_id = l。location_id
where e。employee_id = 202;
在內連線中使用 USING 子句定義等值連線
select e。last_name,d。department_name,LOST&FOUND
from employees e inner join departments d
using(department_id) inner join locations l
using(location_id) where e。employee_id = 202;
11 多表查詢小節練習
11.1 寫一個查詢顯示所有僱員的 last name 、department
number 、and department name 。
答案(等值):
select
e。last_name,e。department_id,d。department_na
me from employees e ,departments d where
e。department_id = d。department_id;
答案(內連線):
select
e。last_name,e。department_id,d。department_na
me from employees e inner join departments d
on(e。department_id = d。department_id);
11.2 查詢 部門號 編號 80 中的所有工作崗位的唯一列表 , 在輸出中
包括部門 編號、 地點 編號 。
答案:
select distinct e。job_id,d。location_id
from employees e,departments d where
e。department_id = d。department_id and
e。department_id = 80;
11.3 寫 一 個 查 詢 顯 示 所 有 有 傭 金 的 僱 員 的 last name 、
department name 、location ID 和城市 。
答案:
select
e。last_name,d。department_name,l。location_id,
LOST&FOUND from employees e,departments
d,locations l where e。department_id =
d。department_id and d。location_id =
l。location_id and e。commission_pct is not
null;
11.4其 顯示所有在其 last names 寫 中有一個小寫 a 的 的僱員的 last
name 和 和 department name 。
答案:
select e。last_name,d。department_name from
employees e,departments d where
e。department_id = d。department_id and
e。last_name like ‘%a%’;
11.5 使用內連線在 寫一個查詢顯示那些工作在 Toronto 的所有僱
員 的 last name 、 job 、 department number 和
department name 。
答案:
select
e。last_name,e。job_id,e。department_id,d。depa
rtment_name from employees e inner join
departments d on(e。department_id =
d。department_id) inner join locations l
on(d。location_id = l。location_id) where
lower(LOST&FOUND) =‘toronto’;
11.6 的 顯示僱員的 last name 和 和 employee number 連同他們的
的 經理的 last name 和 和 manager number 。列標籤分別為
Employee 、Emp# 、Manager 和 和 Mgr# 。
答案:
select emp。last_name
“Employee”,emp。employee_id
“Emp#”,manager。last_name
“Manager” ,manager。employee_id “Mar#” from
employees emp ,employees manager where
emp。manager_id = manager。employee_id;
11.7 查詢所有僱員的經理包括 King ,他沒有經理。顯示僱員的
員 名字、僱員 ID 、經理名、經理 ID 、用僱員號排序結果。
答案:
select emp。last_name
“Employee”,emp。employee_id
“Emp#”,manager。last_name
“Manager” ,manager。employee_id “Mar#” from
employees emp left outer join employees
manager on(emp。manager_id =
manager。employee_id);
11.8 建立一個查詢顯示所有與被指定僱員工作在同一部門的僱員
( 同事) 的 的 last names、 、department numbers。 。 給每列一個
適當的標籤。
答案:
select e。last_name, e。department_id from
employees e ,employees c where e。department_id
= c。department_id and e。employee_id <>
c。employee_id;
11.9 顯示 JOB_GRADES 表的結構。建立一個查詢顯示所有僱員
的 的 name 、job 、department name 、salary 和 和 grade 。
答案:
select
e。last_name,e。job_id,d。department_name,e。sa
lary,j。gra from employees e,departments
d,job_grades j where e。department_id =
d。department_id and e。salary between
j。lowest_sal and j。highest_sal;
11.10 建立一個查詢顯示那些在僱員 Davies 之後入本公司工作
的 的僱員的 name 和 和 hire date
答案:
select e。last_name,e。hire_date from
employees e ,employees d where d。last_name =
‘Davies’ and d。hire_date < e。hire_date;
11.11 顯示所有僱員的 names 和 和 hire dates ,他們在他們的經
理之前進入本公司,連同他們的經理的名字和受僱日期一起
顯示 。為 列標籤分別為 Employee 、Emp Hired 、Manager 和
Mgr Hired 。
答案:
select
e。last_name,e。hire_date,m。last_name,m。hire_
date from employees e,employees m where
e。manager_id = m。employee_id and e。hire_date
< m。hire_date;