多表查詢:
笛卡爾積: 實際上是兩張表的乘積,但是在實際開發(fā)中沒有太大意義
格式: select * from 表1,表2
select * from emp;
select * from dept;
select * from emp, dept;
select * from emp e1, dept d1 where e1.deptno = d1.deptno;
內(nèi)聯(lián)接:
隱式內(nèi)聯(lián)接:
等值內(nèi)聯(lián)接: where e1.deptno = d1.deptno;
不等值內(nèi)聯(lián)接: where e1.deptno <> d1.deptno;
自聯(lián)接: 自己連接自己
顯示內(nèi)聯(lián)接:
select * from 表1 inner join 表2 on 連接條件
inner 關鍵字可以省略
select * from emp e1, dept d1 where e1.deptno <> d1.deptno;
--查詢員工編號,員工姓名,經(jīng)理的編號,經(jīng)理的姓名
select e1.empno,e1.ename,e1.mgr,m1.ename
from emp e1, emp m1 where e1.mgr= m1.empno;
--查詢員工編號,員工姓名,員工的部門名稱,經(jīng)理的編號,經(jīng)理的姓名
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename
from emp e1, emp m1,dept d1 where e1.mgr= m1.empno and e1.deptno = d1.deptno;
--查詢員工編號,員工姓名,員工的部門名稱,經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱
select e1.empno,e1.ename,d1.dname,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno ;
--查詢員工編號,員工姓名,員工的部門名稱,員工的工資等級,經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname
from emp e1, emp m1,dept d1,dept d2,salgrade s1
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal ;
--查詢員工編號,員工姓名,員工的部門名稱,員工的工資等級,
經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱,經(jīng)理的工資等級
select e1.empno,e1.ename,d1.dname,s1.grade,e1.mgr,m1.ename,d2.dname,s2.grade
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal ;
-查詢員工編號,員工姓名,員工的部門名稱,員工的工資等級,經(jīng)理的編號,經(jīng)理的姓名,經(jīng)理的部門名稱,
經(jīng)理的工資等級
--將工資等級 1,2,3,4 顯示成 中文的 一級 二級 三級...
select e1.empno,
e1.ename,
d1.dname,
case s1.grade
when 1 then '一級'
when 2 then '二級'
when 3 then '三級'
when 4 then '四級'
else
'五級'
end "等級",
e1.mgr,
m1.ename,
d2.dname,
decode(s2.grade,1,'一級',2,'二級',3,'三級',4,'四級','五級') "等級"
from emp e1, emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where
e1.mgr= m1.empno
and e1.deptno = d1.deptno
and m1.deptno = d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal ;
--查詢員工姓名和員工部門所處的位置
select e1.ename,d1.loc from emp e1,dept d1 where e1.deptno = d1.deptno;
select * from emp e1 inner join dept d1 on e1.deptno = d1.deptno;
外連接: (標準,通用寫法)
左外連接: left outer join 左表中所有的記錄,如果右表沒有對應記錄,就顯示空
右外連接: right outer join 右表中的所有記錄,如果左表沒有對應記錄,就顯示空
outer 關鍵字可以省略
Oracle中的外連接: (+) 實際上是如果沒有對應的記錄就加上空值
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
select * from emp e1 left outer join dept d1 on e1.deptno = d1.deptno;
insert into emp(empno,ename) values(9527,'HUAAN');
select * from emp e1,dept d1 where e1.deptno = d1.deptno(+);
select * from emp e1 right outer join dept d1 on e1.deptno = d1.deptno;
select * from emp e1,dept d1 where e1.deptno(+) = d1.deptno;
子查詢: 查詢語句中嵌套查詢語句; 用來解決復雜的查詢語句
查詢最高工資的員工信息
單行子查詢: > >= = < <= <> !=
多行子查詢: in not in >any >all exists not exists
--查詢最高工資的員工信息
--1.查詢出最高工資 --5000
select max(sal) from emp;
--2. 工資等于最高工資
select * from emp where sal = (select max(sal) from emp);
--查詢出比雇員7654的工資高,同時和7788從事相同工作的員工信息
--1.雇員7654的工資 1250
select sal from emp where empno = 7654;
--2.7788從事的工作 ANALYST
select job from emp where empno = 7788;
--3.兩個條件合并
select * from emp where sal > 1250 and job = 'ANALYST';
select * from emp where sal > (select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);
--查詢每個部門最低工資的員工信息和他所在的部門信息
--1.查詢每個部門的最低工資,分組統(tǒng)計
select deptno,min(sal) minsal from emp group by deptno;
--2.員工工資等于他所處部門的最低工資
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal = t1.minsal;
--3.查詢部門相關信息
select *
from emp e1,
(select deptno,min(sal) minsal from emp group by deptno) t1,
dept d1
where e1.deptno = t1.deptno and e1.sal = t1.minsal and e1.deptno = d1.deptno;
內(nèi)聯(lián)接, 單行子查詢, 多行子查詢
in
not in
any
all
exists
通常情況下, 數(shù)據(jù)庫中不要出現(xiàn)null 最好的做法加上Not null
null值并不代表不占空間, char(100) null 100個字符
--查詢領導信息
--1.查詢所有經(jīng)理的編號
select mgr from emp;
select distinct mgr from emp;
--2.結果
select * from emp where empno in (select mgr from emp);
--查詢不是領導的信息
select * from emp where empno not in (select mgr from emp);
select * from emp where empno <>all(select mgr from emp);
--正確的寫法
select * from emp where empno not in (select mgr from emp where mgr is not null);
--查詢出比10號部門任意一個員工薪資高的員工信息 10 20 30
select * from emp where sal >any (select sal from emp where deptno = 10);
--查詢出比20號部門所有員工薪資高的員工信息 10 20 30
--1.20號最高工資 5000
select max(sal) from emp where deptno =20;
--2.員工信息
select * from emp where sal > (select max(sal) from emp where deptno =20);
使用多行子查詢完成上面這題
--20號部門所有員工薪資 (800 2975 ...)
select sal from emp where deptno = 20;
--大于集合所有的
select * from emp where sal >all(select sal from emp where deptno = 20);
exists(查詢語句) : 存在的意思,判斷一張表里面的記錄是否存在與另外一張表中
當作布爾值來處理:
當查詢語句有結果的時候, 就是返回true
否則返回的是false
數(shù)據(jù)量比較大的時候是非常高效的
select * from emp where exists(select * from emp where deptno = 1234567);
select * from emp where 3=4;
select * from emp where exists(select * from emp where deptno = 20);
--查詢有員工的部門的信息
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno);
--找到員工表中工資最高的前三名(降序排序)
select * from emp order by sal desc;----錯誤
rownum : 偽列, 系統(tǒng)自動生成的一列, 用來表示行號
rownum是Oracle中特有的用來表示行號的, 默認值/起始值是 1 ,在每查詢出結果之后,再添加1
rownum最好不能做大于號判斷,可以做小于號判斷
SQL執(zhí)行順序
from .. where ..group by..having .. select..rownum..order by
Select rownum,e1.* from emp e1;
--查詢rownum大于2的所有記錄 ,
select rownum,e1.* from emp e1 where rownum > 2; --沒有任何記錄
--查詢rownum大于等于1的所有記錄
select rownum,e1.* from emp e1 where rownum >=1;
--查詢rownum < 6 的所有記錄
select rownum,e1.* from emp e1 where rownum < 6;
--rownum 排序
Select rownum,e1.* from emp e1 order by sal;
--找到員工表中工資最高的前三名
select e1.* from emp e1 order by sal desc;
--將上面的結果當作一張表處理,再查詢
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1;
--只要顯示前三條記錄
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1
where rownum < 4;
--找到員工表中薪水大于本部門平均薪水的員工
--1.分組統(tǒng)計部門平均薪水
select deptno,avg(sal) avgsal from emp group by deptno;
--2.員工工資 > 本部門平均工資
select * from emp e1,(select deptno,avg(sal) avgsal from emp group by deptno) t1
where e1.deptno = t1.deptno and e1.sal > t1.avgsal;
關聯(lián)子查詢 , 非關聯(lián)子查詢
select * from emp e where sal > (select avg(sal) from emp e2 group by deptno having
e.deptno=e2.deptno);
統(tǒng)計每年入職的員工個數(shù)
select hiredate from emp;
--只顯示年
select to_char(hiredate,'yyyy') from emp;
--分組統(tǒng)計
select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy');
select yy
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy')) tt;
select case yy when '1987' then cc end
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy')) tt;
select case yy when '1987' then cc end "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy')) tt;
--去除行記錄中的空值
select sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy')) tt;
--統(tǒng)計員工的總數(shù)
select sum(cc) "TOTAL"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy')) tt;
--將1987 和TOTAL 合并在一起
select
sum(cc) "TOTAL",
sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy')) tt;
--顯示所有年份的結果
select
sum(cc) "TOTAL",
sum(case yy when '1980' then cc end) "1980",
sum(case yy when '1981' then cc end) "1981",
sum(case yy when '1982' then cc end) "1982",
sum(case yy when '1987' then cc end) "1987"
from
(select to_char(hiredate,'yyyy') yy,count(1) cc from emp group by
to_char(hiredate,'yyyy')) tt;
rowid : 偽列 每行記錄所存放的真實物理地址
rownum : 行號 , 每查詢出記錄之后,就會添加一個行號
select rowid,e.* from emp e;
--去除表中重復記錄
create table p(
name varchar2(10)
);
insert into p values('黃偉福');
insert into p values('趙洪');
insert into p values('楊華');
delete from p p1 where rowid > (select min(rowid) from p p2 where
p1.name = p2.name);
rownum : 分頁查詢
在oracle中只能使用子查詢來做分頁查詢
--查詢第6 - 第10 記錄
select rownum, emp.* from emp;
select rownum hanghao, emp.* from emp;
select * from (select rownum hanghao, emp.* from emp) tt where
tt.hanghao between 6 and 10;
集合運算:
并集: 將兩個查詢結果進行合并
交集
差集
所有的查詢結果可能不是來自同一張表,
emp 2000年
2017年 手機 詳細信息 emp2017
--并集運算: union union all
union : 去除重復的,并且排序
union all : 不會去除重復的
--工資大于1500,或者20號部門下的員工
select * from emp where sal > 1500 or deptno = 20;
select * from emp where sal > 1500
union
select * from emp where deptno = 20;
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;
交集運算: intersect
--工資大于1500,并且20號部門下的員工
select * from emp where sal > 1500;
select * from emp where deptno = 20;
select * from emp where sal > 1500
intersect
select * from emp where deptno = 20;
差集運算: 兩個結果相減：minus
--1981年入職員工(不包括總裁和經(jīng)理)
--1981年入職員工
select * from emp where to_char(hiredate,'yyyy')='1981';
--總裁和經(jīng)理
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job = 'PRESIDENT' or job = 'MANAGER';
集合運算中的注意事項:
1.列的類型要一致
2.按照順序寫
3.列的數(shù)量要一致,如果不足,用空值填充
select ename,sal from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
--列的類型不匹配
select ename,sal from emp where sal > 1500
union
select sal,ename from emp where deptno = 20;
--列的數(shù)量不匹配
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,null from emp where deptno = 20;
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal,66 from emp where deptno = 20;
select * from emp;
select * from dept;
