? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? 數(shù)據(jù)庫(kù)2

函數(shù)

內(nèi)置函數(shù)和自定義函數(shù)

單行函數(shù) : 一條記錄返回一個(gè)結(jié)果的

多行函數(shù)|組函數(shù)|聚合函數(shù) : 多條記錄返回一條結(jié)果的

當(dāng)前時(shí)間

select distinct? sysdate from emp;

select sysdate from dual;

select current_date from dual;

加減日期

2天以后十幾號(hào)

select sysdate+2 from dual;

所有員工入職的3天前是幾號(hào)

select hiredate,hiredate-3 from emp;

add months(日期對(duì)象，月份數(shù))

查詢所有員工的試用期到期后? 3個(gè)月試用期

select hiredate,add_months(hiredate,3) from emp;

months between(大月份，小月份)

查詢所有員工到目前為止一共工作了幾個(gè)月

select hiredate,months_between(sysdate,hiredate) from emp;

last_day()

查詢當(dāng)前月的最后一天

select hiredate,last_day(hiredate) from emp;

next_day('星期日')

?下一個(gè)星期三是幾號(hào)(即將要過(guò)的星期日)

select next_day(sysdate,'星期日') from dual;

to_date(數(shù)據(jù),格式)

to_char(數(shù)據(jù),格式)

設(shè)定一個(gè)特定的時(shí)間（用一個(gè)特定的時(shí)間字符串轉(zhuǎn)換為日期）

?設(shè)定一個(gè)時(shí)間? 就是今天 '2020-02-05 16:18:25'

select to_date('2020/02/05 16:18:25','yyyy/mm//dd/ hh24:mi:ss')+3 from dual;

?將日期轉(zhuǎn)為特定格式的字符串

select to_char(sysdate,'yyyy"年"mm"月"dd"日" hh24:mi:ss')from dual;

判定函數(shù)? decode(判定字段,值1,結(jié)果1,值2,結(jié)果2,值3,結(jié)果3....(,默認(rèn)結(jié)果))

給每個(gè)部門(mén)后后面添加一個(gè)偽列,如果10部門(mén),偽列顯示為十,二十,三十...

select deptno,dname,loc,decode(deptno,10,'十',20,'二十',30,'三十','四十') from dept;

?給20部門(mén)的所有員工都漲薪10%，顯示出員工的名稱， 原來(lái)的薪水， 所屬部門(mén)編號(hào)， 漲薪后的薪水

select ename,sal,deptno,decode(deptno,20,sal*1.1,sal)from emp;

10部門(mén)漲薪10%， 20漲薪20%，30降薪1% ， 40部門(mén)翻倍3倍

select sal,deptno, decode(deptno,10,sal*1.1,20,sal*1.2,30,sal*(1-0.01),40,sal*3) from emp;

組函數(shù)|聚合函數(shù)|多行函數(shù) : 對(duì)結(jié)果集進(jìn)行組函數(shù)計(jì)算

多行記錄返回一個(gè)結(jié)果

count(條件) sum(條件) max() min() avg()

注意: 組函數(shù)不能和非分組字段一起使用

?統(tǒng)計(jì)一下一共有多少個(gè)員工

select count(empno) from emp;

select count(deptno) from emp;

select count(*) from emp;

select count(1) from emp; --偽列? 相當(dāng)于為每條數(shù)據(jù)的后面添加一個(gè)偽列字段 1

?統(tǒng)計(jì)一共有幾個(gè)部門(mén)

select count(1) from dept;

?統(tǒng)計(jì)有員工存在的部門(mén)總數(shù)

查詢有員工存在的部門(mén)編號(hào)的結(jié)果集,對(duì)這個(gè)結(jié)果集求個(gè)數(shù)

select count(distinct deptno) from emp;

select count(1)

? from dept

where deptno in (select distinct deptno from emp);

?統(tǒng)計(jì)20部門(mén)一共有多少人

select count(deptno) from emp where deptno =20;

?計(jì)算本公司每個(gè)月一共要在工資上花費(fèi)多少錢(qián)

select sum(sal) from emp;

?計(jì)算20部門(mén)每個(gè)月的工資花銷

select sum (sal) from emp where deptno=20;

查詢本公司的最高工資和最低工資

select max(sal) from emp;

select min(sal) from emp;

查看30部門(mén)的最高工資和最低工資

select max(sal),min(sal) from emp where deptno=30;

?avg 平均工資

select avg(sal) from emp;

?請(qǐng)查詢出 20部門(mén)的平均工資

select avg(sal) from emp where deptno=20;

?計(jì)算出所有員工的獎(jiǎng)金總和? null 不參與運(yùn)算

select sum(comm) from emp where comm is not null;

?統(tǒng)計(jì)一共有多少個(gè)員工 null 不參與運(yùn)算

統(tǒng)計(jì)有獎(jiǎng)金的員工有幾個(gè)

select count(comm) from emp;

查詢 最高薪水的員工姓名， 及薪水

select max(sal) from emp;

select ename from emp where sal =(select max(sal) from emp);

?查詢工資低于平均工資的員工編號(hào)，姓名及工資

select empno,ename,sal from emp where sal<(select avg(sal) from emp);

查詢比SMITH薪資高并且與SMITH同部門(mén)的員工信息

select ename ,sal from emp where sal>(select sal from emp where ename='SMITH');

查看高于本部門(mén)平均薪水員工姓名

select avg(sal) from emp where deptno=20;

select ename? from emp e1 where sal >(select avg(sal) from emp e2 where e2.deptno = e1.deptno);

分組: group by 分組字段

查詢公式:select 數(shù)據(jù) from 數(shù)據(jù)來(lái)源 where 行過(guò)濾條件 group by 分組字段1,.. having 過(guò)濾組信息(以組為單位過(guò)濾) order by 排序字段..;

執(zhí)行流程: from -- where --group by --having --select? -- order by

注意:

? 1)select 后如果出現(xiàn)了組函數(shù)|分了組,組函數(shù)不能與非分組字段,可以與其他組函數(shù)或分組字段一起使用??

2)where 后不能使用組函數(shù)? 因?yàn)檫€沒(méi)有組,執(zhí)行流程問(wèn)題? ? ?

--求出所有有員工存在的部門(mén)編號(hào)

select deptno from emp group by deptno;

-- 找出20部門(mén)和30部門(mén)的最高工資

--20部門(mén)和30部門(mén)中的所有員工中的最高工資

select max(sal) from emp where deptno in(30,20);

--找出20部門(mén)和30部門(mén)中每個(gè)部門(mén)的最高工資

? select max(sal),deptno from emp where deptno in (30,20) group by deptno;? --先過(guò)濾 后分組

select max(sal),deptno from emp group by deptno having deptno in(30,20);--先分組再過(guò)濾

-- 求出每個(gè)部門(mén)的平均工資

--數(shù)據(jù): 每組的平均薪資

--來(lái)源: 員工表

--條件: 一個(gè)部門(mén)一個(gè)部門(mén)求平均薪資? ,一個(gè)部門(mén)一個(gè)值? 以部門(mén)為單位 如果不分組組函數(shù)對(duì)所有滿足條件的數(shù)據(jù)進(jìn)行計(jì)算,如果分組了,以組為單位

select avg(sal),deptno from emp group by deptno;

-- 求出每個(gè)部門(mén)員工工資高于1000的的部門(mén)平均工資

--數(shù)據(jù): 部門(mén)平均工資

--來(lái)源: 員工表

--條件: sal>1000 以部門(mén)為單位:按照部門(mén)進(jìn)行分組

select avg(sal),deptno from emp where sal>1000 group by deptno;

-- 求出10和20部門(mén)部門(mén)的哪些工資高于1000的員工的平均工資

select avg(sal),depton from emp where sal>1000 group by deptno having deptno in(10,20);

--不推薦使用,效率相對(duì)較低

select * from (select avg(sal),deptno from emp where sal>1000 group by deptno) where deptno in(10,20);

-- 找出每個(gè)部門(mén)的最高工資

select max(sal) from emp group by deptno;

-- 求出每個(gè)部門(mén)的平均工資高于2000的部門(mén)編號(hào)和平均工資

select avg(sal),deptno from emp? group by deptno having avg(sal)>2000;

--按 部門(mén)崗位(job) 查詢 平均工資和工種

select avg(sal),job from emp group by job;

--按 崗位查詢 平均工資，且平均工資大于2000的崗位

select avg(sal),job from emp where sal>2000 group by job;

select avg(sal),job from emp group by job having avg(sal)>2000;

--查詢出所有部門(mén)的平均薪資和部門(mén)編號(hào)

select deptno,avg(sal) from emp group by deptno;

--所有部門(mén)的平均薪資中最低薪資

select min(avg(sal)) from emp group by deptno;

--查詢 最低平均工資的部門(mén)編號(hào)

select deptno

? from (select deptno, avg(sal) avg_sal from emp group by deptno)

where avg_sal = (select min(avg(sal)) from emp group by deptno);

-- 統(tǒng)計(jì)每個(gè)部門(mén)的員工數(shù)，和部門(mén)編號(hào),按照員工個(gè)數(shù)升序排序

select count(1),deptno from emp group by deptno order by count(1);

-- 查詢平均工資在1500到2000之間的部門(mén)平均工資和部門(mén)編號(hào)

select avg(sal),deptno from emp group by deptno having avg(sal) between 1500 and 2000;

--查詢20部門(mén)的平均工資的員工

select avg(sal),deptno from emp group by deptno having deptno=20;

-- 查詢工資高于20部門(mén)平均工資的員工

select * from emp where sal>(select avg(sal) from emp group by deptno having deptno=20);

--rowid 和 rownum 都是偽列

--rowid理解為記錄在插入到數(shù)據(jù)庫(kù)的表中時(shí)候就存在的數(shù)據(jù)的地址(對(duì)象的地址),其實(shí)不是地址,根據(jù)地址得到的值

--如果一個(gè)表中沒(méi)有主鍵,沒(méi)有不可重復(fù)的字段,可能會(huì)出現(xiàn)多條一模一樣的數(shù)據(jù),無(wú)法區(qū)分重復(fù)數(shù)據(jù),可以根據(jù)rowid進(jìn)行區(qū)分

每一頁(yè)要顯示的數(shù)據(jù)的rownum 第一個(gè): rownum>=num*(i-1)+1 最后一個(gè)為: row<=num*i