一、等值連接
1.查詢員工的名字、部門編號、部門名字
select emp.last_name,dept.id,dept.name
from s_emp emp,s_dept dept
where emp.dept_id =dept.id;
2.查詢部門的 id,名稱以及所在區(qū)域的名稱
select dept.id,dept.name,re.name
from s_region re,s_dept dept
where dept.region_id =re.id;
3.查詢每個員工所在的區(qū)域
select emp.last_name,re.name
from s_emp emp,s_region re,s_dept dept
where emp.dept_id=dept.id
and dept.region_id=re.id;
4.查詢Ngao所在的部門名稱以及相對應(yīng)區(qū)域名稱
select emp.last_name,dept.name dept_name,re.name region_name
from s_emp emp,s_dept dept,s_region re
where emp.dept_id =dept.id
????? and
????? dept.region_id = re.id
????? and
????? emp.last_name ='Ngao';
5.查詢工資大于1200的員工所在部門區(qū)域
select emp.last_name,emp.salary,re.name
from s_emp emp,s_dept dept,s_region re
where emp.dept_id =dept.id
????? and
????? dept.region_id = re.id
????? and
????? emp.salary >1200;
二、不等值連接
1.查詢員工的工資等級名稱
select emp.last_name,grade.gradeName
from s_emp emp,s_grade grade
where emp.salary>=grade.losal
and
emp.salary<=grade.hisal;
或者
select emp.last_name,grade.gradename
from s_emp emp,s_grade grade
where emp.salary between grade.losal andgrade.hisal;
三、外連接
1.左外連接
查詢員工所在部門，沒有部門的員工也要查詢出來
select emp.last_name,dept.id
from s_emp emp, s_dept dept
where emp.dept_id =dept.id(+);
或者
select emp.last_name,dept.id
from s_emp emp left joins_dept dept
on emp.dept_id =dept.id(+);
2.右外連接
查詢每個部門所對應(yīng)的員工，沒有員工的部門也要查詢出來
select emp.last_name,dept.id
from s_emp emp,s_dept dept
where emp.dept_id(+) =dept.id;
或者
select emp.last_name,dept.id
from s_emp emp right joins_dept dept
on emp.dept_id(+) =dept.id;
3.全連接
查詢所有的員工，以及對應(yīng)的部門的名字，沒有任何員工的部門也要顯示出來，沒有部門的員工也要顯示出來
select emp.last_name,dept.id,dept.name
from s_emp emp full joins_dept dept
on emp.dept_id =dept.id;
1.? 自連接
select emp1.last_name,emp2.last_name
from s_emp emp1,s_emp emp2
where emp1.id =emp2.manager_id;
四、結(jié)果集
◆union? 獲得倆個結(jié)果集的【并集】
select emp.last_name,dept.id
from s_emp emp, s_dept dept
where emp.dept_id =dept.id(+)
union
select emp.last_name,dept.id
from s_emp emp,s_dept dept
where emp.dept_id(+) =dept.id;
◆union all?把倆個結(jié)果集合在一起顯示出來
select emp.last_name,dept.id
from s_emp emp, s_dept dept
where emp.dept_id =dept.id(+)
union all
select emp.last_name,dept.id
from s_emp emp,s_dept dept
where emp.dept_id(+) =dept.id;??
◆minus 第一個結(jié)果集除去第二個結(jié)果集和它相同的部分【差集】
select emp.last_name,dept.id
from s_emp emp, s_dept dept
where emp.dept_id =dept.id(+)
minus
select emp.last_name,dept.id
from s_emp emp,s_dept dept
where emp.dept_id(+) =dept.id;
◆intersect?求倆個結(jié)果集的【交集】(公共部分)
select emp.last_name,dept.id
from s_emp emp, s_dept dept
where emp.dept_id =dept.id(+)
intersect
select emp.last_name,dept.id
from s_emp emp,s_dept dept
where emp.dept_id(+) =dept.id;
五、偽列rownum
（1）rowmun 能等于1
查詢第一列數(shù)據(jù)
select*
from s_emp
where rownum = 1
（2）rownum 能大于0
select*
from s_emp
where rownum >0
rownum >=1 也是可以的
select*
from s_emp
where rownum >=1
（3）rownum小于任何數(shù)
select*
from s_emp
where rownum <=5
六、分頁
（1） 查詢第4條到第6條的數(shù)據(jù)
select*
from s_emp
where rownum <=6
minus
select*
from s_emp
where rownum <=3
（2）查詢第2條數(shù)據(jù)
select emp.*
from s_emp emp
where rownum <= 2
minus
select emp.*
from s_emp emp
where rownum <= 1;
七、組函數(shù)
1.不使用groupby
select max(last_name)
from s_emp;
select min(last_name)
from s_emp;
select avg(last_name)
from s_emp;
select sum(last_name)
from s_emp;
計(jì)算有多少條數(shù)據(jù)
select count(last_name)
from s_emp;
標(biāo)準(zhǔn)差
select stddev(salary)
from s_emp;
?方差
select variance(salary)
from s_emp;
2.使用group by
（1）查詢員工表中每個部門的平均工資
select dept_id,avg(salary)
from s_emp
group by dept_id;
（2）查詢員工表中每個部門員工的人數(shù)
select dept_id,count(*)
from s_emp
group by dept_id;
3.Having
（1）查詢區(qū)域id以及名字，同時顯示該區(qū)域所有員工工資總和
select r.id,r.name,sum(salary)
from s_region r,s_emp e,s_dept p
where r.id=p.region_id ande.dept_id = p.id
group by r.id,r.name;
（2）查詢s_emp表中部門的平均工資大于等于1400的部門
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary) >=1400;
（3）求部門平均工資大于1000的信息，并按照部門|平均工資排序輸出
select e.dept_id,avg(salary)
froms_dept d,s_emp e
where d.id = e.dept_id
group bye.dept_id
having avg(salary)>1000
order by avg(salary);
having/where的區(qū)別
????? where:
????????????? 不能夠單獨(dú)使用
????????????? where緊跟在from
????????????? where在分組之前去執(zhí)行，不能夠出現(xiàn)組函數(shù)
?????? having:
????????????? 不能夠單獨(dú)使用
????????????? 緊跟在group by
????????????? 可以出現(xiàn)組函數(shù)
?????? where為什么不能夠出現(xiàn)組函數(shù)：
????????????? 組函數(shù)是在分組以后執(zhí)行
????????????? 分組：
???????????????????? 默認(rèn)分組：
?????????????????????????? 將整列認(rèn)為是一個大組
???????????????????? group by:
?????????????????????????? where 在這個語句執(zhí)行之前執(zhí)行
select執(zhí)行順序
??????? select
????????????? from
????????????? where
????????????? group by
????????????? having
????????????? order by
????????????? 1.from
????????????? 2.where
????????????? 3.group by
????????????? 4.having
????????????? 5.select
????????????? 6.order by
組函數(shù)可以出現(xiàn)的位置：
group by執(zhí)行的語句都可以出現(xiàn)
having:
對分組后的數(shù)據(jù)進(jìn)一步篩選
