Oracle知識點筆記

1.1 Oracle基本配置

1.1.1 Oracle安裝與啟動

Oracle安裝：用戶種類及初始密碼

image.png

在oracle10g\11g中默認scott被鎖定。

Oracle數(shù)據(jù)庫的啟動

a) 啟動兩個服務Listener/Service

b) “開始-》運行”-》sqlplus或 sqlplusw

1.2 Oracle用戶管理

1.2.1 基本命令

用戶連接广恢、解鎖掉伏、鎖定

c) conn/connect scott/tiger; system/orcl; sys/orcl as sysdba;  

d) 解鎖/鎖定：alter user 用戶名 account unlock/lock;

解鎖： alter user scott account unlock 用戶解鎖

鎖定： alter user scott sccount lock 用戶鎖定

 alter user scott account password expire;設置密碼過期

用戶的查找

e) 顯示當前的用戶：show user;

f) 顯示系統(tǒng)默認的用戶：select * from all_users;  所有用戶

 dba_users 管理員用戶

創(chuàng)建新用戶

g) 創(chuàng)建用戶密碼：SQL> create user xxx identified by 密碼 default tablespace xx;

h) 更改密碼：SQL> alter user xxx identified by 密碼;

i) 給用戶授系統(tǒng)權限：

i. 授予連接數(shù)據(jù)庫權限：

a) SQL> grant create session to test;

b) grant connect,resource to xxx;

ii. 授予創(chuàng)建數(shù)據(jù)庫表權限：

a) SQL> grant create table to test;

j) 給用戶授對象權限：

i. 授予查詢表修改等的權限

a) SQL> grant select on dept to test;

b) SQL> grant all on scott.dept to test;

k) 給用戶授角色：

 i. SQL>grant connect,resource to test;

 ii. 管理權限 with grant option 看一下收回權限后蚌铜，級聯(lián)授權的能否收回

grant all on emp to xxx with grant option;按照角色授予權限

l) 收回權限

 i. SQL> revoke select on dept from test;

 ii. SQL> revoke all on dept from test;

 iii. 撤銷權限 revoke xx on 表名 from 用戶

刪除用戶

m) SQL> drop user test;

n) SQL> drop user xx cascade;

1.2.2 實例

創(chuàng)建用戶：必需以dba的身份才能創(chuàng)建用戶怠晴，否則會提示權限不足

a) 例：以scott的身份創(chuàng)建用戶

image.png

b) 例：使用system來創(chuàng)建用戶

image.png

一般以dba的身份去刪除某個用戶赴捞，如果用其他用戶去刪除用戶笨忌，則需要有drop user 的權限频丘。在刪除用戶時注意办成，如果要刪除的用戶已經(jīng)創(chuàng)建了表，那么就需要在刪除時帶一個參數(shù)cascade搂漠。

a) 例1迂卢，使用scott刪除本身

image.png

給用戶創(chuàng)建權限，我們創(chuàng)建的用戶剛開始是什么權限都沒有桐汤，登錄都不可以

a) 使用剛剛創(chuàng)建的chenzhou來登錄

image.png

提示缺少權限而克，登錄被取消

b) Oracle中權限的分類

i. 系統(tǒng)權限：用戶對數(shù)據(jù)庫的相關權限

ii. 對象權限：用戶對數(shù)據(jù)庫的數(shù)據(jù)對象操作的權限（select,insert,update,delete,all,create index……）

c) Oracle中角色的分類

i. 預定義角色

ii. 自定義角色

d) 給用戶賦予connect角色

image.png

再登錄：

image.png

e) 給用戶賦予resource的角色，用戶有了該角色就能夠自己建表

image.png

f) 用戶自己建表

image.png

g) 如何使用chenzhou用戶來訪問scott的emp表

i. 首先給用戶賦權

image.png

ii. 賦權之后使用該用戶來查詢scott中的emp表怔毛，結果如下：

image.png

iii. 如果要賦予該用戶對該表修改權限员萍，則grant update……

iv. 如果想把對該表的所有權限夠賦給該用戶則 grant all on emp to chenzhou

給用戶賦權使用grant，收回權限使用revoke

a) 例子：收回chenzhou用戶對emp表的查詢權限

image.png

再次查詢scott中的emp表時：

image.png

對權限進行維護

a) 希望chenzhou用戶可以去查詢scott中的emp表拣度，還希望他可以把這個權限交給別的對象

i. 如果是對象權限碎绎，就加入with grant option

例：grant select on emp to chenzhou with grant option

image.png

ii. 如果是系統(tǒng)權限更耻，就加入with admin option

iii. 注：如果A對象把權限賦給B,然后B再賦給C凄杯，當A把B的權限r(nóng)evoke后，C擁有的權限也會被回收掉恶阴。

使用profile管理用戶口令

a) 賬戶鎖定

i. 概述：指定該賬戶登陸時最多可以輸入密碼的次數(shù)窖张，也可以指定用戶鎖定的時間（天）幕随，一般用dba的身份去執(zhí)行該命令

ii. 例子：指定scott這個用戶最多只能嘗試3次登陸，鎖定時間為2天

create profile lock_account limit failed_login_attempts 3 password_lock_time 2;

alter user scott profile lock_account;

b) 給賬戶解鎖（dba操作）

 i. alter user scott account unlock;

c) 終止口令

i. 概述：為了讓用戶定期修改密碼可以使用終止口令的指令來完成（dba操作）

ii. 例子：給chenzhou用戶創(chuàng)建一個profile文件宿接，要求該用戶每隔10天要修改自家的登錄密碼赘淮，寬限期為2天。

 iii. create profile profilename limit password_life_time 10 password_grace_time 2;

alter user chenzhou profile profilename;

d) 口令歷史

i. 概述：如果希望用戶在修改密碼時睦霎，不能使用以前使用過的密碼梢卸，可以使用口令歷史，這樣oracle就會將口令修改的信息存放到數(shù)據(jù)字典中副女，這樣當用戶修改密碼時蛤高，oracle就會對新舊密碼進行比較，當發(fā)現(xiàn)新舊密碼一樣時碑幅，就提示用戶重新輸入密碼戴陡。

ii. 例子：

建立profile

create profile password_history limit password_life_time 10 password_grace_time 3 password_reuse_time 10;

psssword_reuse_time //指定口令可以重用時間，即10天后就可以重用

分配給某個用戶：

e) 刪除profile

i. 概述：當不需要某個profile文件時沟涨，可以刪除該文件恤批。

 ii. drop profile password_history[cascade]

1.3 基本數(shù)據(jù)類型及其sql語法

1.3.1 表管理

o) 查詢數(shù)據(jù)庫中有哪些表：select * from tab;

p) 查詢某張表的結構：desc 表名;

image.png

q)      create table users(

 userId number primary key,

 userName varchar2(10),

 password varchar2(20),

 addr varchar2(20));

r) 添加字段

alter table users add(pubdate date);

desc users;

alter table users add(age number)

s) 修改字段

alter table users modify(userName varchar2(20));

insert into users values(32,'aa','aa','aa',sysdate,10);

t) 刪除字段

alter table users drop(password);

或者

alter table users drop column password

desc users;

u) 給表添加約束

alter table users add constraint ck check(age>10 and age<60)

alter table users drop primary key;

alter table users add primary key(userId)

v) 重新命名

rename users to test;//將users表重新命名為test

drop table users;//刪除表結構

truncate table users //刪除記錄，記錄不可恢復裹赴，不寫日志（快）

delete from emp //刪除記錄喜庞，但可以恢復诀浪，寫日志

w) 注意：

mysql中一條INSERT語句插入批量數(shù)據(jù)的寫法：INSERT INTO 表名 VALUES ([列值],[列值])), ([列值],[列值])), ([列值],[列值])); Oracle中不支持這種寫法，不過可以采用下面的方式：

INSERT ALL INTO a表 VALUES(各個值1) INTO a表 VALUES (其它值2) INTO a表 VALUES(其它值3) SELECT * FROM b表延都，如果SELECT * FROM b表會查詢出n條數(shù)據(jù)的話雷猪，就會先往a表插入值1對應的各個字段插入n條記錄，然后插入值2各個對應的字段n條記錄晰房，然后插入值3對應的各個字段n條記錄春宣。注意后邊跟的SELECT語句可以隨意，不過不是把它SELECT出來的內容插入前邊的表里嫉你，而是起到前邊的多個數(shù)據(jù)每次插入多少行的作用，這個多少行是和后邊跟的SELECT語句查出來幾條而定的

1.3.2 數(shù)據(jù)字典

維護系統(tǒng)對象的一套特殊表和視圖

user_xxx 用戶擁有的 all_xx 用戶有權查看 dba_xxx(sys) 所有的信息

1躏惋、查看所有數(shù)據(jù)字典對象的名稱和用途

select * from dictionary;

2幽污、查看oracle數(shù)據(jù)庫中所有用戶

select username from dba_users;

3、查看scott用戶下所有的表

select table_name from user_tables;

4簿姨、查看scott用戶所有的視圖

select * from user_views

5距误、查詢scott用戶有權查看的表

select table_name,owner from all_tables;

4、創(chuàng)建表扁位，講解數(shù)據(jù)類型

Number(m,n) char(n) varchar2(n) date

a>創(chuàng)建新表

b>利用現(xiàn)有的表創(chuàng)建表

 create table test2 as select * from usersinfo;

5准潭、事務控制語言（TCL）可以放SQL后面

 Insert into dept values(11,null,null);

 Insert into dept values(12,null,null);

 Savepoint p1;

 Insert into dept values(13,null,null);

 Insert into dept values(14,null,null);

 Savepoint p2;

 Insert into dept values(15,null,null);

 Insert into dept values(16,null,null);

 rollback to p2;

 Select * from dept;

 Rollback to p1;

 Select * from dept;

 Rollabck;

 Select * from dept;

image.png

Select distinct * from emp where job=’CLERK’;

Select distinct job,ename from emp where job=’CLERK’;

Select distinct job from emp;

image.png

Select (empno||’,’||ename||’,’||job) as OUT_PUT from emp;

image.png

1.4 基本SQL查詢

1.4.1 簡單查詢

1 、select 簡單查詢

select sid,sname from t_student

select * from t_course

select * from t_score

--2班女生

select * from t_student where sclass = 2 and SSEX='f'

--查詢所有班級

select sclass from t_student

2域仇、 select distinct 查詢

select distinct sclass from t_student

3刑然、 給列或表達式取別名

select * from t_student;

--從學生表中檢索出2班的性別為女性的學生的信息

select * from t_student where SCLASS=2 and SSEX='f';

--從學生表中檢索出所有班級

select SCLASS from t_student;

select DISTINCT SCLASS from t_student;

--學號為‘10001’的學生參加了哪些課程的考試

select CID from t_score where SID='10001'

--給學員編號為10002的學生的各門課成績進行升序和降序的排列

select SCORE from t_score where SID='10002' order by SCORE asc

select SCORE from t_score where SID='10002' order by SCORE desc

--查詢所有姓張的學生信息

select * from t_student where SNAME like '張%';

insert into t_student values('10009','張三','m','01-3月-1985'，'13563921205','2')

select * from t_student where SNAME like '張_'

--利用已有的t_student表生成新表

create table t_student_bak1 as select * from t_student

create table t_student_bak2 as select * from t_student where sclass=2

create table t_student_bak3 as select * from t_student where 1=2

1.4.2 連接查詢和子查詢—重點

1暇务、查詢員工以及他所在的部門

 select a.*,b.* from emp a,dept b where a.deptno=b.deptno----（內連接）

2泼掠、查詢所有部門以及員工姓名

 select a.ename,b.deptno,b.dname from emp a,dept b where a.deptno(+)=b.deptno

或者

 select a.ename,b.deptno,b.dname from dept b left outer join emp a on b.deptno=a.deptno---（左外連接）

 select a.ename,b.deptno,b.dname from emp a right outer join dept b on a.deptno=b.deptno----（右外連接）

3、顯示員工以及其直接上級

 select a.ename 員工,b.ename 經(jīng)理 from emp a,emp b where a.mgr=b.empno(+) ----(自連接)

4垦细、查詢工資高于平均工資的員工

 select * from emp where sal>(select avg(sal) from emp) ------（非關聯(lián)子查詢）

先執(zhí)行子查詢择镇，后執(zhí)行主查詢，叫做非關聯(lián)子查詢

5括改、查詢每個部門最高工資的員工

 select * from emp a where (select count(*) from emp where deptno=a.deptno and sal>a.sal)=0 ---(關聯(lián)子查詢)

先執(zhí)行主查詢腻豌，后執(zhí)行子查詢，將主查詢的當作已經(jīng)查詢出來的結果

也可非關聯(lián)實現(xiàn)

 select * from emp where (deptno,sal) in (select deptno,min(sal) from emp group by deptno)

實例：

--內連接（join on/inner join on）：查詢學生總體學習情況：學生姓名嘱能，課程名吝梅，成績

select SNAME,CNAME,SCORE from t_student a

join t_score b on a.sid = b.sid

join t_course c on b.cid = c.cid

select SNAME,CNAME,SCORE from t_student,t_score,t_course

where t_student.sid = t_score.sid

and t_score.cid = t_course.cid

--查詢及格的學生的學習情況：學生姓名，課程名焰檩，成績

select SNAME,CNAME,SCORE from t_student a

join t_score b on a.sid = b.sid

join t_course c on b.cid = c.cid

where SCORE >= 60

--查詢有課程的教師的信息

select * from t_teacher

inner join t_teachercourse

on t_teacher.tid = t_teachercourse.tid

--查詢所有教師的工作分配情況憔涉，有課程的和沒課程的老師都要出現(xiàn)在查詢結果中（左外連接）

select * from t_teacher --主表

left outer join t_teachercourse --從表   

on t_teacher.tid = t_teachercourse.tid

select * from t_teachercourse --從表 

right join t_teacher --主表   

on t_teacher.tid = t_teachercourse.tid

--全聯(lián)接(full join 結果集中除了滿足聯(lián)接條件的記錄外,還有左、右表中不滿足條件的記錄) 左連接和外連接的一個組合析苫，先執(zhí)行左連接兜叨，再執(zhí)行右連接穿扳，刪掉重復記錄

select * from t_teacher

full join t_teachercourse  

on t_teacher.tid = t_teachercourse.tid

--學生和課程有多少可能的組合------------

--交叉連接（cross join ）-------------

select * from t_student

cross join t_course  

select * from t_student，t_course

----------子查詢----------

--查詢比張老師年齡大的教師信息

select * from t_teacher where tage > （select tage from t_teacher where TNAME='張老師'）

--查詢參加過課程編號為1的考試的學生信息

select * from t_student where sid = (select sid from t_score where cid=1)---失敗

select * from t_student where sid in (select sid from t_score where cid=1)

--查詢沒有參加過課程編號為1的考試的學生信息

select * from t_student where sid not in (select sid from t_score where cid=1)

--查詢所有已經(jīng)安排教師上課的課程信息

select * from t_course tc where cid in (select cid from t_teachercourse )

select * from t_course tc where exists (select * from t_teachercourse ttc where ttc.cid = tc.cid)

--查詢所有沒安排教師上課的課程信息

select * from t_course tc where not exists (select * from t_teachercourse ttc where ttc.cid = tc.cid)

--在成績表中查詢出所有學生的最高平均分(from后面的子查詢)

select max(avgscore) from (select avg(SCORE) as avgscore from t_score group by sid)

--經(jīng)典應用分頁查詢（rownum）

--查詢成績表的前5條記錄

select * from t_score where rownum>=1 and rownum<=5

select * from (select rownum as num,SID,CID,SCORE from t_score) where num>=1 and num<=5

--查詢成績表的6-10條記錄

select * from t_score where rownum>=6 and rownum<=10--錯誤国旷，沒有數(shù)據(jù)

select * from (select rownum as num,SID,CID,SCORE from t_score) where num>=6 and num<=10

1.4.3 層次查詢

1矛物、顯示員工領導關系

 select lpad(ename,5*level,'+') from emp connect by prior empno=mgr start with ename='KING'

或者start with mgr is null

connect by 用于執(zhí)行記錄之間的父子關系，start with 用于指定從哪個節(jié)點記錄開始遍歷訪問 Level 在整個查詢記錄中的層次

2跪但、查詢BLAKE所領導團隊工資總額

 select sum(sal) from emp connect by prior empno=mgr start with ename='BLAKE'

1.5 操作符及其SQL函數(shù)

1.5.1 操作符

--算術操作符（加(+)履羞、減(-)、乘(*)屡久、除(/)）：檢索出課程號是’2’的成績+10分后的結果

select sid,cid,score+10 as "lastScore" from t_score where cid=2

----------邏輯操作符：and or not

----------檢索班級是1班或2班的學生信息

select * from t_student where SCLASS=1 or SCLASS=2

----------檢索班級是1班的80后學生信息

select * from t_student where SCLASS=1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989'

比較操作符（包括 =忆首、<>、<被环、>糙及、<=、>=筛欢、BETWEEN…AND浸锨、IN、LIKE 和 IS NULL）：

----------檢索班級是1班或2班的學生信息

select * from t_student where SCLASS in(1,2)

--查詢 1班的 80 后的學生信息

select * from t_student where SCLASS=1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989'

----------檢索班級不是1班的80后學生信息

select * from t_student where SCLASS<>1 and SBIRTHDAY between '1-1月-1980' and '31-12月-1989'

-- 檢索1986 年出生的學生信息

select * from t_student where SBIRTHDAY between '1-1月-1986' and '31-12月-1986'

-- 檢索1980 年以前出生的學生信息

select * from t_student where SBIRTHDAY < '1-1月-1980'

--連接運算符:用于將多個字符串或數(shù)據(jù)值合并成一個字符串

--查詢學生信息（把學號和姓名合并成一個列）

select (sid || sname) as "學生信息" from t_student

select ('學號為' || sid || '的學生的姓名是'|| sname) as "學生信息" from t_student

------集合操作符：將兩個查詢的結果組合成一個結果 ppt9

-------union版姑、union all柱搜、intersect、minus

------統(tǒng)計學習操作系統(tǒng)(1)或數(shù)據(jù)結構(2)的同學學號（union：返回兩個查詢的不重復的所有行)

select sid from t_score where CID='1'

union

select sid from t_score where CID='2'

-------統(tǒng)計java web(5) 和java框架(6)都為及格的同學學號

-------intersect：相當于對2個查詢結果集取交集剥险，也就是只返回兩個查詢結果集的公共行

select sid from t_score where cid='5' and score >= 60

intersect

select sid from t_score where cid='6' and score >= 60

select * from t_score where cid='5'

update t_score set score='65' where sid='10002' and cid='6'

--統(tǒng)計操作系統(tǒng)(1)70 分及以上但數(shù)據(jù)結構(2)未達到 65 的同學學號 --10004

select sid from t_score where cid='1' and score > 70

minus

select sid from t_score where cid='2' and score >= 65

select * from t_score where sid='10003'

1.5.2 集合運算

A集合 (1聪蘸、2、3)

B集合 (2表制、3宇姚、4)

交集：(2、3)

并集：(1夫凸、2浑劳、3、2夭拌、3魔熏、4)或(1、2鸽扁、3蒜绽、4)

差集：A-B(1) B-A(4)

 create table a(id number);//插入1，2桶现，3

 create table b(id number);//插入2躲雅，3,4

1、交集

 select * from a intersect select * from b

2骡和、并集

 select * from a union all select * from b(去掉重復的記錄則去掉all)

3相赁、差集

 select * from a minus select * from b (A-B)

 select * from b minus select * from a (B-A)

1相寇、查詢部門10和部門20都有的工作類型

 select job from emp where deptno=10 intersect select job from emp where deptno=20;

2、查詢部門10的辦事員和部門20的經(jīng)理

 select * from emp where deptno=10 and job='CLERK' union all select * from emp where deptno=20 and job='MANAGER'

3钮科、查詢部門30中有唤衫，而部門10中沒有的工作類型

 select job from emp where deptno=30 minus select job from emp where deptno=10;

1.5.3 常用函數(shù)

1 、聚合函數(shù)

count() sum() avg() max() min()

實例：

--------------------------------------分組聚合查詢--------------------------------------------

--Min ：最小值

--Max ：最大值

--Sum ：求和

--Avg ：求平均值

--Count：計數(shù)

--查詢課程表中最多和最少的課時數(shù)

select MAX(CHOURS) as 最多的課時數(shù),MIN(CHOURS)as 最少的課時數(shù) from t_course

select * from t_course

--查詢當前的學生數(shù)

select count(*) from t_student

--查詢成績表中每位同學的最高分绵脯，最低分佳励，總分，平均分

select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid

--統(tǒng)計平均成績超過75分的學生的最高分蛆挫，最低分赃承，總分，平均分

select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid having avg(SCORE) >75

--group by :select后面的這些字段悴侵，要么是在聚合函數(shù)里面楣导，要么是在group by字句中，否則回報錯

select sid,max(SCORE),min(SCORE),sum(SCORE),avg(SCORE) from t_score group by sid having avg(SCORE) >75

2 畜挨、字符函數(shù) （參考PPT演示）

ltrim() 左側截取

rtrim() 右側截取

translate('jack','a' ,'b') 結果back

lpad(char1,n[,char2]) 使用char2字符補充在char1字符的左邊，最終補足n個字符噩凹，如果沒有char2字符巴元，則用空格補足n個字符

 length () select length(‘a(chǎn)bcdefg’) from dual

 DECODE(input_value,value,result[,value,result…][,default_result]);

實例：

----------字符函數(shù)

select lower('FUN') from dual;--轉換為小寫

select upper('fun') from dual; --轉換為大寫

select ltrim(' abcd ') from dual;--abcd  

select rtrim(' abcd ') from dual;-- abcd

select trim(' abcd ') from dual;--abcd

select replace ('jack and jue','j','bl') from dual;

3 、數(shù)學函數(shù)

round[number,[decimal_places]] 四舍五入函數(shù)驮宴，將number按照指定小數(shù)位數(shù)進行四舍五入運算的結果

 select round(3.567) from dual;四舍五入取整

 select round(123.456, 0) from dual; 回傳 123

select round(123.456, 1) from dual; 回傳 123.5

select round(123.456, 2) from dual; 回傳 123.46

select round(123.456, 3) from dual; 回傳 123.456

select round(-123.456, 2) from dual; 回傳 -123.46

4 逮刨、轉換函數(shù)

to_char(d[,fmt]) 也可number轉換將date數(shù)據(jù)類型的d轉換成字符串類型數(shù)據(jù)

to_date(字符串，格式)

其他函數(shù)

nvl(a,b) 如果a不為null 則返回a,如果a為null則返回b堵泽，注意兩者的類型要一致

nvl2(a,b,c) ,如果a不為null 則返回b,如果a為null則返回c; b和c類型不同的話修己，c會轉換為b的類型

NULLIF (expr1, expr2) ->相等返回NULL，不等返回expr1

COALESCE (expr1, expr2, ..., exprn) ->返回第一個不為NULL的表達式迎罗，各個表達式類型一致

1.5.4 日期函數(shù)

系統(tǒng)時間sysdate

add_months(d,no_of_month) 為日期d加上no_of_month月份

 select add_months(sysdate,2) from dual;

months_between(d1,d2)返回日期d1和d2之間的月份數(shù)

例子：當前時間跟當前時間加上兩個月的差額

last_day(d)d所在月份最后一天

例子：查詢當前時間所在月份最后一天日期

to_date(‘字符串’,’日期格式化’)

  例子：select to_date(‘20100302’,’yyyymmdd’) from dual;

next_day(d,day) 返回d后工作日日期 day取值范圍 1—7（周日睬愤、周一、周二……周六）

select next_day(sysdate,3) from dual; 返回下周二

例子：

1纹安、查詢系統(tǒng)時間(sysdate)

 select sysdate from dual

2尤辱、查詢在12年前參加工作的員工(months_between(d1,d2)返回日期d1和d2之間的月份數(shù))

 select empno,ename,hiredate from emp where months_between(sysdate,hiredate)>144

3、查詢在當月倒數(shù)第三天參加工作的員工（last_day(d)d所在月份最后一天）

 select empno,ename,hiredate from emp where last_day(hiredate)-2=hiredate

4厢岂、查詢每個員工的工作天數(shù)（trunc(n[,m])返回截尾取整到小數(shù)點后m位的數(shù)）

 select empno,ename,hiredate,trunc(sysdate-hiredate) from emp

5光督、顯示系統(tǒng)時間為xxxx年xx月xx日，是一年中第幾天（DDD）塔粒，是星期幾(DAY)(to_char(d结借，str)將d顯示為str指定的格式)

 select to_char(sysdate,'yyyy"年"月"dd"日"DDD DAY') from dual

6、計算每個員工已經(jīng)工作了多少個月卒茬，忽略小數(shù)部分(months_between)

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

7船老、查詢在1987年2月到1987年5月參加工作的員工(to_date(str,formatstr)將str格式字符串日期轉換成formatetr指定的格式日期)

 select ename,hiredate from emp where hiredate>=to_date('19870201','yyyymmdd') and

 hiredate<to_date('19870601','yyyymmdd')

實例：

----------日期函數(shù)

select sysdate from dual

select sysdate+1 from dual --不建議

select add_months(sysdate,1) from dual -- 加一月

select add_months(sysdate,12) from dual -- 加一年

--to_char

1.5.5 統(tǒng)計函數(shù)

1咖熟、統(tǒng)計部門最低工資大于900的部門和最低工資

 select deptno,min(sal) from emp group by deptno having min(sal)>900

2、統(tǒng)計每個部門工資在1400元以上的所有員工的工資總額

 select deptno,sum(sal) from emp where sal>1400 group by deptno

3努隙、統(tǒng)計不同工作的個數(shù)

 select count(distinct job) from emp

1.6 數(shù)據(jù)庫對象

1.6.1 序列

1球恤、創(chuàng)建

create sequence seqa start with 1000 increment by 2;

2、得到序列自增的數(shù)字

select seqa.CURRVAL from dual;(得到當前序列數(shù)值)

select seqa.NEXTVAL from dual;（得到序列下一個數(shù)值）

3荸镊、插入某表

insert into test values(seqa.NEXTVAL,’aa’);

要插入開始是字母的可以

insert into test values(‘c’||trim(to_char(seqa.nextval,’0000’)),’aa’);

4咽斧、刪除序列

drop sequence seqa;

1.6.2 偽列

rowId,rownum偽列

完成分頁

1.7 PL/SQL

declare

 cursor cemp is select empno,sal from emp order by sal;

 pempno emp.empno%type;

 psal emp.sal%type;

 --漲工資的人數(shù):

 countEmp number := 0;

 --漲后的工資總額:

 salTotal number;

begin

 --得到初始的工資總額

 select sum(sal) into salTotal from emp;

 open cemp;

 loop

 --1\. 總額 > 5w

 exit when salTotal > 50000;

 --取一個員工

 fetch cemp into pempno,psal;

 --2\. notfound

 exit when cemp%notfound;

 --漲工資

 update emp set sal=sal*1.1 where empno=pempno;

 --人數(shù)+1

 countEmp := countEmp + 1;

 --2\. 漲后=漲前 + sal * 0.1

 salTotal := salTotal + psal * 0.1;

 end loop;

 close cemp;

 commit;

 dbms_output.put_line('人數(shù):'||countEmp||' 總額:'||salTotal);

end;

select empno,sal from scott.emp order by sal;

1.8 游標的使用

1 、游標：

%notfound 沒有找到數(shù)據(jù)為真躬存，找到為假

%found 相反

%rowcount 統(tǒng)計影響的行數(shù)

2 张惹、顯示游標 sql；

更新指定員工工資加500

declare

eno emp.empno%type;

jr emp%rowtype;

begin

eno:=&員工編號;

update emp set sal=sal+500

where empno=eno;

if sql%notfound

then

dbms_output.putline('沒有發(fā)現(xiàn)你要找的數(shù)據(jù)');

else

dbms_output.putline(‘更新完畢’);

end if;

end;

3 岭洲、隱式游標

游標聲明宛逗、打開、檢索盾剩、關閉

聲明

cursor 游標名{(形參聲明)}

is/as

select 語句;

打開

open 游標名{(實參)};

檢索

fatch 游標名 into 變量;

關閉

close 游標名;

例如

每個部門平均工資

declare

dno number;

avgsal number;

cursor cur_1

is

select deptno,avg(sal) avgsal from emp

group by deptno;

begin

open cur_1;

loop

fatch cur_1 into dno,avgsal;

exit when cur_1%notfound;

dbms_output.putline('編號:'||dno||'平均工資：'||avgsal);

end loop;

close cur_1;--關閉

end;

顯示指定工作的員工姓名和工資

declare

empjob emp.job%type;

empname varchar2(20);

salary number;

cursor cur2(work varchar2)

is

select ename,sal from emp

where job=work;

begin

empjob:='&輸入一個工作種類';

open cur2(empjob);

loop

fatch cur2 into empname,salary;

exit when cur2%notfound;

dbms_output.putline('姓名:'||empname||'工資：'||salary);

end loop;

close cur2;

end;

1.9 觸發(fā)器

觸發(fā)器

tnew insert udate

told update dalete

create (or replace) trigger 觸發(fā)器名

before/after insert/upodate(of 列名）/delete

on 表名/視圖名

[for each row]--行級觸發(fā)器

begin

sql 語句

end;

級聯(lián)刪除

create or replace trigger tri_1

after delete from emp

where deptno=:old.deptno;

end;

delete from dept

where deptno=10;

級聯(lián)更新

create or replace trigger tri_update

after update of deptno on dept

for each row

begin

update emp set deptno=:new deptno

where deptno=:old deptno;

end;

update dept set deptno=70

where deptno=30;

函數(shù)

create {or replace} function 函數(shù)名{（形參）}

return 返回值類型

is

聲明部分

begin

語句1

end雷激；

例子

create or replace function fun

return vachar2

is

begin

return ‘hello world!’;

end;

select fun from duil;

求工資稅函數(shù)

create or replace function tax(money number)

return number

is

sal_rate number;

begin

if money<=3500 then

sal_rate:=0;

else if money<=5000 then

sal_rate:=(money-3500)*0.03;

else if money<=8000 then

sal_rate:=(5000-3500)*0.03+(money-5000)*0.1;

else .....

end if;

end if;

end if;

return sal_rate;

end;

1.10 存儲過程

存儲過程

create （or replace) procedure 存儲過程名（形參）

is/as

（聲明部分）

begin

語句

（異常處理部分）

end;

用存儲過程實現(xiàn)某個工作地點的員工姓名及工資

create or replace procedure p_emp_dept(loca varchar2)

is

empname varchar2(20);

salary number;

cursor c_sal(location varchar2)

is

select ename,sal from emp

where deptno in(select deptno from dept where loc=location);

begin

open c_sal(loca);

dbms_output.putline('姓名:'||'工資：');

loop

fatch c_sal into empname,salary;

exit when c_sal%notfound;

dbms_output.putline(empname||salary);

end loop;

close c_sal;

end;

begin

p_emp_dept(‘紐約’);

end;