【Oracle】曾經的Oracle學習筆記（4-7）多表聯集查詢，子查詢，動態條件查詢

標籤：

一、多表聯集查詢

二、子查詢

三、動態條件查詢

 

 

LESSON 4 Displaying Data from Multiple Tables
--------------------------------------------------------

查詢s_emp表中最大的工資數,並且顯示出這個最大工資數的員工名字

select last_name,max(salary)
from s_emp;

多表查詢

查詢多張表的時候會產生笛卡爾積

為了防止笛卡爾積的產生，我們需要使用某些條件把兩張表或多張表串連起來（一般會使用兩個表間主外鍵相等的關係）

1）等值串連
內串連
自串連，自己串連自己

2）不等值串連
三種不等值串連：
表名1 left outer join 表名2 on 串連條件
表名1 right outer join 表名2 on 串連條件
表名1 full outer join 表名2 on 串連條件
其中的outer可以省去不寫

查詢s_emp表中每一個員工的名字和部門編號

select last_name,dept_id
from s_emp

查詢s_emp表中每一個員工的名字和部門編號,並且顯示出部門的名字
select se.last_name,se.dept_id,sd.id,sd.name
from s_emp se,s_dept sd
order by se.last_name;

select se.last_name,se.dept_id,sd.name
from s_emp se,s_dept sd
where se.dept_id = sd.id
order by se.last_name;

查詢s_emp表中每一個員工的名字和部門編號,並且顯示出部門的名字以及部門所在地區的名字

select se.last_name,se.dept_id,sd.name,sd.region_id,sr.name
from s_emp se, s_dept sd, s_region sr
where se.dept_id = sd.id and sd.region_id = sr.id
order by last_name asc;

查詢s_emp表中每一個員工的id、名字、領導人的id、領導人的名字，沒有領導人的員工除外。

select s1.id,s1.last_name,s1.manager_id,s2.last_name
from s_emp s1,s_emp s2
where s1.manager_id = s2.id and s1.manager_id is not null
order by s2.id asc;

//向s_emp中插入一條測試資料
//這是一條DML語句，會產生事務
insert into s_emp(id,last_name)
values(26,‘tom‘);
//所以插入之後的資料要提交
commit;

下面都是不等值串連的例子:
---------------------------------
查詢員工的名字、部門號、部門名字
(即使某個員工還沒有部門編號也要顯示出來)

//如果還用這樣的等值串連是查詢不出來tom這個人的
select se.last_name,se.dept_id,sd.name
from s_emp se,s_dept sd
where se.dept_id = sd.id;

//哪邊資料少在那邊加上這個加號
select se.last_name,se.dept_id,sd.name
from s_emp se,s_dept sd
where se.dept_id = sd.id(+);

左串連（將左表作為主表）
select se.last_name,se.dept_id,sd.name
from s_emp se left join s_dept sd
on se.dept_id = sd.id;

右串連
select se.last_name,se.dept_id,sd.name
from s_emp se right join s_dept sd
on se.dept_id = sd.id;

insert into s_dept(id,name)values(51,‘KunShan‘);

commit;

--------------------------------

查詢員工的名字、部門號、部門名字
(沒有員工存在的部門也要顯示出來)

select se.last_name,se.dept_id,sd.name
from s_emp se,s_dept sd
where se.dept_id(+) = sd.id;

查詢員工的名字、部門號、部門名字
(即使某個員工還沒有部門編號也要顯示出來、沒有員工存在的部門也要顯示出來)

//全串連
select se.last_name,se.dept_id,sd.name
from s_emp se full outer join s_dept sd
on se.dept_id = sd.id;

select se.last_name,se.dept_id,sd.name
from s_emp se full join s_dept sd
on se.dept_id = sd.id;

操作倆個結果集的關鍵字
---------------------------------------------
(這裡說的結果集指的是一條sql語句查詢後得到的結果)

 

union

union all

minus

intersect

第一條sql:
select id,last_name
from s_emp
where id < 7;

結果集一:
ID LAST_NAME
-- ---------------
1 Velasquez
2 Ngao
3 Nagayama
4 Quick-To-See
5 Ropeburn
6 Urguhart

第二條sql語句:
select id,last_name
from s_emp
where id < 6;

結果集二:
ID LAST_NAME
-- --------------
1 Velasquez
2 Ngao
3 Nagayama
4 Quick-To-See
5 Ropeburn

1 union 取得倆個結果集的並集

select id,last_name
from s_emp
where id < 7
union
select id,last_name
from s_emp
where id < 6;

2 union all 之間把倆個結果集放在一起,不管重複的資料

select id,last_name
from s_emp
where id < 7
union all
select id,last_name
from s_emp
where id < 6;

3 minus 去掉倆個結果集相同的部分

select id,last_name
from s_emp
where id < 7
minus
select id,last_name
from s_emp
where id < 6;

 

4 intersect 只保留倆個結果集相同部分(交集)

select id,last_name
from s_emp
where id < 7
intersect
select id,last_name
from s_emp
where id < 6;

子查詢
------------------------------------------------------
查詢Ngao所在部門的編號

select dept_id from s_emp
where last_name=‘Ngao‘;

查詢41號部門的平均工資
select avg(salary)
from s_emp
where dept_id = 41
group by dept_id;

select avg(salary)
from s_emp
where dept_id = 41;

AVG(SALARY)
-----------
1247.5

 

sql語句的基本格式:
-------------
select ...
from ..
where ...
group by ...
having ...
order by ..

**執行順序： ---> where ---> group by --->組函數 ---> having ---> order by

select/having 後面出現了組函數，那麼麼有被組函數修飾的列就一定要寫在group by 後面
-----------------------------------------------------------------------------

查詢Ngao所在部門的平均工資

select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
);

練習1：
---------------------------------------------------------------------------
查詢比41號部門平均工資高的部門裡面的員工資訊(顯示員工的 名字 部門號 工資)

 

select last_name,dept_id,salary
from s_emp
where dept_id=any(
select dept_id from s_emp
group by dept_id
having avg(salary) >
(
select avg(salary) from s_emp
where dept_id = 41
)
);

分析：
1.查詢的是員工資訊
2.員工所在部門的平均工資比41號部門高

41部門的平均工資
select avg(salary)
from s_emp
where dept_id = 41;

AVG(SALARY)
-----------
1247.5

比41部門平均工資高的部門
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5
order by dept_id;

select dept_id
from s_emp
group by dept_id
having avg(salary)>
(
select avg(salary)
from s_emp
where dept_id = 41
)
order by dept_id;

DEPT_ID
----------
10
31
32
33
35
50

 

select last_name,dept_id,salary
from s_emp
where dept_id in(10,31,32,33,35,50);

select last_name,dept_id,salary
from s_emp
where dept_id in
(
select dept_id
from s_emp
group by dept_id
having avg(salary)>
(
select avg(salary)
from s_emp
where dept_id = 41
)
);

 

練習2：
-------------------------------------------------------------------------------------------
查詢比Ngao所在部門的平均工資高的員工資訊,並且這些員工所在部門平均工資也要比Ngao所在部門的平均工資要高.

select last_name,dept_id,salary
from s_emp
where dept_id in
(
select dept_id
from s_emp
group by dept_id
having avg(salary)>
(
select avg(salary)
from s_emp
where dept_id = 41
)
)
and salary >
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
);

分析：
1.查詢的是員工資訊
2.這些員工的工資比Ngao所在部門平均工資高
3.這些員工所在部門的平均工資也要比Ngao所在部門平均工資高

Ngao所在的部門編號
select dept_id
from s_emp
where last_name = ‘Ngao‘;

DEPT_ID
--------
41

 

41號部門的平均工資
select avg(salary)
from s_emp
where dept_id = 41;

AVG(SALARY)
-----------
1247.5

比41部門平均工資高的部門
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5
order by dept_id;

DEPT_ID
----------
10
31
32
33
35
50

 

這個例子最終要執行的sql其實為:
select last_name,dept_id,salary
from s_emp
where salary > 1247.5
and dept_id in(10,31,32,33,35,50);

所以最終把sql語句中的條件替換為子查詢為:
select last_name,dept_id,salary
from s_emp
where salary >
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
and dept_id in
(
select dept_id
from s_emp
group by dept_id
having avg(salary)>
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
);

 

練習3：
----------------------------------------------------------------
查詢比Ngao所在部門的平均工資高員工資訊,並且這些員工所在部門平均工資也要比Ngao所在部門的平均工資要高.
(顯示 員工的名字、部門號、工資)
增加顯示這個員工所在部門名字

 

select se.last_name,se.dept_id,se.salary,sd.name,sr.name,se2.last_name
from s_emp se,s_dept sd,s_region sr,s_emp se2
where se.salary >
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
and se.dept_id in
(
select dept_id
from s_emp
group by dept_id
having avg(salary)>
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
)
and se.dept_id = sd.id
and sd.region_id = sr.id
and se.manager_id = se2.id;

 

1.查詢的是員工資訊
2.這些員工的工資比Ngao所在部門平均工資高
3.這些員工所在部門的平均工資也要比Ngao所在部門平均工資高

select se.last_name,se.dept_id,se.salary,sd.dept_name
from s_emp se join s_dept sd
on se.dept_id = sd.id;

 

select last_name,dept_id,salary
from s_emp
where salary >
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
and dept_id in
(
select dept_id
from s_emp
group by dept_id
having avg(salary)>
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
);

 

 

select se.last_name,se.dept_id,se.salary,sd.name
from s_emp se join s_dept sd
on ((se.dept_id=sd.id) and
(se.salary >
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
and se.dept_id in
(
select dept_id
from s_emp
group by dept_id
having avg(salary)>
(
select avg(salary)
from s_emp
where dept_id =
(
select dept_id
from s_emp
where last_name = ‘Ngao‘
)
)
)
)
);

 

練習4：
------------------------------------------
查詢每一個部門中比本部門平均工資高的員工資訊
顯示出員工的名字、工資、部門號、這個部門的平均工資

//這個sql語句的查詢結果將來可以當做要查詢的一張表
select dept_id,avg(salary) avgSal
from s_emp
group by dept_id;

DEPT_ID avgSal
------- -----------
42 1081.66667
43 900
34 1160
44 1050
31 1400
32 1490
35 1450
50 2025
41 1247.5
45 1089
33 1515
10 1450

 

select se.last_name,se.salary,se.dept_id,temp.avgSal
from s_emp se,(
select dept_id,avg(salary) avgSal
from s_emp
group by dept_id
) temp
where se.dept_id = temp.dept_id
and se.salary > temp.avgSal;

1.查詢的是員工資訊
2.例如要把41號部門中比41號部門(本部門)平均工資高的員工資訊查詢出來

select avg(salary),dept_id
from s_emp
group by dept_id;

select last_name,salary,dept_id
from s_emp;

select s1.last_name,s1.salary,s1.dept_id,s2.avg_sal
from s_emp s1
join (select avg(salary) avg_sal,dept_id from s_emp group by dept_id) s2
on ((s1.dept_id = s2.dept_id) and (s1.salary > s2.avg_sal))
order by s1.dept_id asc;

 

sql語句執行的時候輸入參數(運行時參數)
------------------------------------------------
select last_name,salary,dept_id
from s_emp
where last_name=&name;

select last_name,salary,dept_id
from s_emp
where id = 2;

select last_name,salary,dept_id
from s_emp
where id = &id;

//運行sql語句的時候給字串參數賦值
select last_name,salary,dept_id
from s_emp
where last_name = &last_name;

select last_name,salary,dept_id
from s_emp
where last_name = ‘&last_name‘;

select last_name,salary,dept_id
from s_emp
where id > &id or last_name = ‘&last_name‘;

//運行時再給出具體的篩選條件
select last_name,salary,dept_id
from s_emp
where &condition;

select last_name,salary,dept_id
from s_emp
&condition;

insert into student(id,name,age)
values(&id,‘&name‘,&age);

 

【Oracle】曾經的Oracle學習筆記（4-7）多表聯集查詢，子查詢，動態條件查詢