數(shù)據(jù)庫(kù)技術(shù)培訓(xùn)課程講義課件

1、數(shù)據(jù)庫(kù)技術(shù)培訓(xùn)課程講義趙正道主要內(nèi)容數(shù)據(jù)庫(kù)基本管理SQL語(yǔ)句編寫(xiě)數(shù)據(jù)庫(kù)查詢及優(yōu)化PL/SQL開(kāi)發(fā)數(shù)據(jù)庫(kù)基本管理用戶管理創(chuàng)建用戶修改用戶屬性刪除用戶權(quán)限管理 在數(shù)據(jù)庫(kù)中，權(quán)限是執(zhí)行某種特定數(shù)據(jù)庫(kù)操作的權(quán)利或許可，比如建立數(shù)據(jù)庫(kù)連接、創(chuàng)建表或視圖等數(shù)據(jù)庫(kù)對(duì)象、插入、修改和刪除數(shù)據(jù)等操作。用戶在進(jìn)行數(shù)據(jù)庫(kù)操作時(shí)，數(shù)據(jù)庫(kù)將會(huì)首先驗(yàn)證該用戶是否擁有執(zhí)行該操作的權(quán)限。數(shù)據(jù)庫(kù)基本管理權(quán)限名稱權(quán)限描述create table在用戶自己的方案中創(chuàng)建表create any table在任何方案中創(chuàng)建表alter any table在任何方案中更改表drop any table在任何方案中刪除表insert any

2、 table在任何方案中執(zhí)行插入記錄操作update any table在任何方案中執(zhí)行更新操作delete any table在任何方案中執(zhí)行刪除操作select any table在任何方案中執(zhí)行查詢操作lock any table在任何方案中鎖定任何表數(shù)據(jù)庫(kù)基本管理權(quán)限管理授予權(quán)限(grant)查看權(quán)限收回權(quán)限(revoke)select * from system_privilege_map查詢當(dāng)前數(shù)據(jù)庫(kù)系統(tǒng)所有系統(tǒng)權(quán)限信息select *from dba_sys_privs where grantee=SCOTT查詢所有用戶各自所擁有的系統(tǒng)權(quán)限信息select * from sess

3、ion_privs查詢當(dāng)前用戶所擁有的系統(tǒng)權(quán)限數(shù)據(jù)庫(kù)基本管理角色管理創(chuàng)建角色角色授權(quán)授予角色收回角色刪除角色SQL語(yǔ)句編寫(xiě)數(shù)據(jù)定義語(yǔ)言(DDL)語(yǔ)句名稱功能/含義create創(chuàng)建表alter修改表drop刪除表rename重命名truncate刪除表中所有數(shù)據(jù)SQL語(yǔ)句編寫(xiě)數(shù)據(jù)操作語(yǔ)言(DML)語(yǔ)句名稱功能/含義insert向表中插入記錄update修改記錄delete刪除記錄select檢索數(shù)據(jù)SQL語(yǔ)句編寫(xiě)數(shù)據(jù)控制語(yǔ)言(DCL)語(yǔ)句名稱功能/含義grant授予權(quán)限及角色revoke收回權(quán)限及角色事物控制語(yǔ)句(TCS)語(yǔ)句名稱功能/含義commit對(duì)數(shù)據(jù)的修改永久保存到數(shù)據(jù)庫(kù)rollback

4、還原對(duì)數(shù)據(jù)的修改，可還原至開(kāi)始或任意保存點(diǎn)savepoint設(shè)置保存點(diǎn)以標(biāo)識(shí)回滾位置SQL語(yǔ)句編寫(xiě)-創(chuàng)建表實(shí)例數(shù)據(jù)類型create table student( sno number(8), sname varchar2(10), sex char(4);類型描述varchar2變長(zhǎng)字符串char定長(zhǎng)字符串number數(shù)值型date日期和時(shí)間型SQL語(yǔ)句編寫(xiě)-創(chuàng)建表樣本數(shù)據(jù)customers表結(jié)構(gòu)字段名 數(shù)據(jù)類型和寬度 字段含義cid number(10) 顧客編號(hào)name varchar2(10) 姓名phone varchar2(12) 電話car_id varchar2(10) 所購(gòu)車(chē)編

5、號(hào)數(shù)據(jù)1,悠悠c0392,毛毛c0063,燦燦c0024,葉子null5,冉冉nullSQL語(yǔ)句編寫(xiě)-創(chuàng)建表cars表結(jié)構(gòu)字段名 數(shù)據(jù)類型和寬度 字段含義car_id varchar2(10) 汽車(chē)編號(hào)producer varchar2(12) 制造商model varchar2(10) 品牌型號(hào)price number(8) 所購(gòu)車(chē)編號(hào)produce_date date 出廠日期數(shù)據(jù)c021,上海大眾,途觀,175000,1996-5-28c002,德國(guó)大

6、眾,奧迪100,440000,1999-11-17c006,一汽大眾,奧迪A6,540000,2006-8-9c039,東風(fēng)悅達(dá)起亞,千里馬,13200,2003-8-23c095,北京奔馳,奔馳280,420000,2008-12-24SQL語(yǔ)句編寫(xiě)-插入數(shù)據(jù)實(shí)例create table student( sno number(8), sname varchar2(10), sex char(4);insert into student values(1001,zhangqiang,女);SQL語(yǔ)句編寫(xiě)-修改和刪除數(shù)據(jù)實(shí)例create table student( sno number(8)

7、, sname varchar2(10), sex char(4);update student set sex=男 where sno=1001;delete from student where sname=zhangqiang;SQL語(yǔ)句編寫(xiě)-修改表結(jié)構(gòu)1、增加字段2、改變類型和寬度3、字段重命名4、刪除字段5、刪除表中所有數(shù)據(jù)6、刪除表SQL語(yǔ)句編寫(xiě)-約束 在插入、更新行或者從表中刪除行的時(shí)候強(qiáng)制表中的數(shù)據(jù)遵循規(guī)則。 在Oracle數(shù)據(jù)庫(kù)中，可以為表設(shè)置約束（Constraint）,來(lái)防止無(wú)效數(shù)據(jù)輸入到表中。 當(dāng)為某個(gè)表定義了約束后，對(duì)該表做的所有操作都必須滿足約束的要求，否則操作將失

8、敗。SQL語(yǔ)句編寫(xiě)-約束約束類型約束類型說(shuō)明not null指定列不能包含空值unique指定列的值或者列的組合的值對(duì)于表中所有的行必須是唯一的primary key表的每行的唯一性標(biāo)識(shí)foreign key在列和引用表的一個(gè)列之間建立并且強(qiáng)制一個(gè)外鍵關(guān)系check指定一個(gè)必須為真的條件SQL語(yǔ)句編寫(xiě)-約束create table emp( empno number(4), ename varchar2(10) not null, job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2),

9、deptno mumber(4) not null);NOT NULL約束SQL語(yǔ)句編寫(xiě)-約束列級(jí)約束create table deptment( deptno number(2), dname varchar2(14) constraint dept_dname_uk unique, loc varchar2(13) );UNIQUE約束SQL語(yǔ)句編寫(xiě)-約束表級(jí)約束create table deptment( deptno number(2), dname varchar2(14), loc varchar2(13), constraint dept_dname_uk unique(dnam

10、e) );UNIQUE約束SQL語(yǔ)句編寫(xiě)-約束列級(jí)約束create table deptment( deptno number(2) constraint dept_deptno_pk primary key, dname varchar2(14) , loc varchar2(13) );Primary key約束SQL語(yǔ)句編寫(xiě)-約束表級(jí)約束create table deptment( deptno number(2), dname varchar2(14), loc varchar2(13), constraint dept_deptno_pk primary key(deptno) );

11、Primary key約束SQL語(yǔ)句編寫(xiě)-約束create table emp( empno number(4), ename varchar2(10), job varchar2(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno mumber(4) constraint emp_deptno_fkforeign key references dept(deptno);Foreign key約束SQL語(yǔ)句編寫(xiě)-約束on delete cascade選項(xiàng)create table test( em

12、pno number(4), ename varchar2(10) not null, deptno number(2) not null constraint emp_deptno_fk references dept(deptno) on delete cascade );Foreign key約束SQL語(yǔ)句編寫(xiě)-約束create table student( sno number(8), sname varchar2(10), sex char(4), age number(3), constraint student_age_ck check(age=0 and age2000;數(shù)據(jù)庫(kù)

13、查詢及優(yōu)化-基本查詢SELECT語(yǔ)句select 字段列表from 表名where 條件實(shí)例1、查詢出10部門(mén)的雇員的姓名和工作職務(wù)2、查詢工資在15005000之間的所有員工數(shù)據(jù)庫(kù)查詢及優(yōu)化-基本查詢列的別名空值null消除重復(fù)行(distinct)between運(yùn)算符In運(yùn)算符like運(yùn)算符數(shù)據(jù)庫(kù)查詢及優(yōu)化-基本查詢實(shí)例1、列出職位為（MANAGER）的員工的編號(hào)，姓名2、找出每個(gè)員工獎(jiǎng)金和工資的總和3、找出獎(jiǎng)金高于工資的員工4、找出有獎(jiǎng)金的員工的不同工作5、列出各種類別工作的最低工資6、找出沒(méi)有獎(jiǎng)金或者獎(jiǎng)金低于500的員工7、列出按年薪降序排序的所有雇員的年薪數(shù)據(jù)庫(kù)查詢及優(yōu)化-基本查詢查

14、詢練習(xí)1、查詢emp表,顯示薪水大于2000,并且工作類別是MANAGER的雇員信息。2、查詢emp表,顯示年年薪大于30000,工作類別不是MANAGER的雇員信息。3、查詢emp表,顯示薪水在1500到3000之間,工作類別以M開(kāi)頭的雇員信息4、查詢emp表,顯示傭金為空的并且部門(mén)號(hào)為20或30的雇員信息。數(shù)據(jù)庫(kù)查詢及優(yōu)化-基本查詢常用字符函數(shù)函數(shù)功能用法返回結(jié)果lower()轉(zhuǎn)換為小寫(xiě)lower(Hello)helloupper()轉(zhuǎn)換為大寫(xiě)upper(hello)HELLOinitcap()首字母大寫(xiě)initcap(ha er bin)Ha Er Binsubstr()取子串subst

15、r(hello,2,2)ellength()求串長(zhǎng)length(hello)5concat()字符串連接concat(ab,cd)abcdinstr()定位子串instr(abcd,bc)2replace()替換replace(acd,c,mm)ammdlpad()左側(cè)填充lpad(Smith,8,*)*Smithrpad()右側(cè)填充rpad(Smith,8,*)Smith*trim()去掉首尾空格trim( ab c )ab c數(shù)據(jù)庫(kù)查詢及優(yōu)化-基本查詢常用數(shù)值函數(shù)函數(shù)功能用法返回結(jié)果abs()求絕對(duì)值abs(-4)4sqrt()開(kāi)平方sqrt(81)9mod()取余數(shù)mod(17,3)2r

16、ound()四舍五入round(3,1415,3)3.142ceil()向上取整ceil(3.14)4floor()向下取整floor(3.14)3trunc()截?cái)鄑runc(3.1415,3)3.141sign()判斷正負(fù)sign(12)1數(shù)據(jù)庫(kù)查詢及優(yōu)化-基本查詢常用日期函數(shù)函數(shù)功能add_months(x,y)在x日期基礎(chǔ)上增加y個(gè)月后的日期last_day(x)返回日期x當(dāng)月最后一天的日期months_between()返回日期x和y之間相差的月數(shù)round()將日期x四舍五入到y(tǒng)所指定的單位的第一天trunc()將日期x截?cái)嗟統(tǒng)所指定的單位的第一天next_day()計(jì)算指定日期x

17、后的第一個(gè)星期幾對(duì)應(yīng)的日期數(shù)據(jù)庫(kù)查詢及優(yōu)化-基本查詢常用分組函數(shù)函數(shù)功能sum()求和avg()求平均max()求最大值min()求最小值count()查詢記錄個(gè)數(shù)1、列出各種工作類別的最低薪金，顯示最低薪金大于1500的記錄2、列出各個(gè)部門(mén)的MANAGER（經(jīng)理）的最低薪金數(shù)據(jù)庫(kù)查詢及優(yōu)化-復(fù)雜查詢連接查詢等值連接非等值連接自連接外連接子查詢數(shù)據(jù)庫(kù)查詢及優(yōu)化-連接查詢等值連接 連接查詢是通過(guò)多個(gè)表之間數(shù)據(jù)的關(guān)聯(lián)性來(lái)進(jìn)行表間記錄連接，進(jìn)而查詢數(shù)據(jù)的，如果表間記錄的連接是通過(guò)相等的字段值為條件建立起來(lái)的，這種連接稱為等值連接。語(yǔ)法格式：select table1.column,table2.co

18、lumnfrom table1,table2where table1.column1=table2.column2查詢所有雇員的編號(hào)、姓名、職位、工資、部門(mén)號(hào)和部門(mén)名select empno,ename,job,sal,emp.deptno,dnamefrom emp,deptwhere emp.deptno=dept.deptno實(shí)例數(shù)據(jù)庫(kù)查詢及優(yōu)化-連接查詢非等值連接 如果表間連接條件是使用除等號(hào)以外的其他比較運(yùn)算符相連，這種連接稱為非等值連接。1、列出所有雇員的雇員名稱、部門(mén)名稱和薪金2、列出所有“CLERK”（辦事員）的姓名及其部門(mén)名稱3、列出在“SALES”（銷售）部門(mén)工作的雇員的姓

19、名練習(xí)數(shù)據(jù)庫(kù)查詢及優(yōu)化-連接查詢非等值連接 如果表間連接條件是使用除等號(hào)以外的其他比較運(yùn)算符相連，這種連接稱為非等值連接。select empno,ename,sal,gradefrom emp,salgradewhere sallosal and sal(select sal from emp where ename=CLARK)實(shí)例2、查詢部門(mén)平均工資高于單位整體工資的部門(mén)編號(hào)select deptno 部門(mén)編號(hào),avg(sal) 部門(mén)平均工資from empgroup by deptnohaving avg(sal)(select avg(sal) from emp);數(shù)據(jù)庫(kù)查詢及優(yōu)化-

20、子查詢3、查詢與MARTIN或SMITH職位相同的所有員工信息select *from empwhere job in(select job from emp where ename=MARTIN or ename=SMITH)實(shí)例4、查詢工資高于所有部門(mén)的平均工資的員工信息select *from empwhere salall(select avg(sal) from emp group by deptno);數(shù)據(jù)庫(kù)查詢及優(yōu)化-子查詢子查詢練習(xí)1、列出與“SCOTT”從事相同工作的所有雇員2、列出某些雇員的姓名和薪金，條件是他們的薪金等于部門(mén)30中任何一個(gè)雇員的薪金3、列出某些雇員的姓名和

21、薪金，條件是他們的薪金高于部門(mén)30中所有雇員的薪金數(shù)據(jù)庫(kù)查詢及優(yōu)化-查詢優(yōu)化1、查詢條件合理排序select empno,ename,job,sal,dept.deptno,dnamefrom emp,deptwhere emp.deptno=dept.deptno and emp.deptno=20;select empno,ename,job,sal,dept.deptno,dnamefrom emp,deptwhere emp.deptno=20 and emp.deptno=dept.deptno;數(shù)據(jù)庫(kù)查詢及優(yōu)化-查詢優(yōu)化2、連接中使用表的別名select e.empno,e.ena

22、me,e.job,e.sal,e.deptno,d.dnamefrom emp e,dept dwhere e.deptno=d.deptno and e.deptno=20;數(shù)據(jù)庫(kù)查詢及優(yōu)化-查詢優(yōu)化3、用exists替換distinctselect deptno,dname from dept d;where exists (select y from emp e where e.deptno=d.deptno);select distinct d.deptno,dnamefrom dept d,emp ewhere d.deptno=e.deptno;數(shù)據(jù)庫(kù)查詢及優(yōu)化-查詢優(yōu)化4、用wh

23、ere替換havingselect語(yǔ)句執(zhí)行順序是：先where子句，再group by子句，然后select查詢，再后having子句，最后是order by子句，因此在進(jìn)行分組查詢時(shí)，如果過(guò)濾條件不涉及分組計(jì)算，則應(yīng)使用where替換having子句指定過(guò)濾條件。select deptno,avg(sal) from empwhere deptno in(10,20) group by deptno;select deptno,avg(sal) from empgroup by deptno having deptno in(10,20)PL/SQL開(kāi)發(fā)-PL/SQL塊PL/SQL基本結(jié)構(gòu)d

24、eclarebeginexceptionend;PL/SQL開(kāi)發(fā)-PL/SQL塊實(shí)例declare dn scott.emp.deptno%type;begin select deptno into dn from emp where empno=7566; update emp set sal=sal+33 where deptno=dn; commit;exception when NO_DATA_FOUND then insert into exception_log(msg) values(未查到指定員工); commit;when others then rollback; inse

25、rt into exception_log(msg) values(修改工資出現(xiàn)異常); commit;end;PL/SQL開(kāi)發(fā)-PL/SQL塊create table exception_log( aris_time date default sysdate, msg varchar2(200); PL/SQL開(kāi)發(fā)-程序流程控制分支結(jié)構(gòu)if(條件表達(dá)式1) then語(yǔ)句序列1elsif(條件表達(dá)式2) then語(yǔ)句序列2.else語(yǔ)句序列nend if; PL/SQL開(kāi)發(fā)-程序流程控制分支結(jié)構(gòu) 實(shí)例set serveroutput on;declarevsal emp.sal%type;be

26、ginselect sal into vsal from emp where empno=7369;if(vsal=3000) thendbms_output.put_line(需要交納個(gè)人所得稅);elsedbms_output.put_line(不需繳稅);end if;end;PL/SQL開(kāi)發(fā)-程序流程控制循環(huán)結(jié)構(gòu)-for循環(huán)for in . loop語(yǔ)句序列.end loop; PL/SQL開(kāi)發(fā)-程序流程控制for循環(huán)結(jié)構(gòu) 實(shí)例set serveroutput on;declaretotal number(5):=0;beginfor i in 1.100 looptotal:=tot

27、al+i;end loop;dbms_output.put_line(1-100之間的和：|total);end;PL/SQL開(kāi)發(fā)-程序流程控制循環(huán)結(jié)構(gòu)-while循環(huán)while 條件 loop語(yǔ)句序列.end loop; PL/SQL開(kāi)發(fā)-程序流程控制while循環(huán)結(jié)構(gòu) 實(shí)例set serveroutput on;declarei number(5):=1;total number(5):=0;beginwhile i=100 looptotal:=total+i;i:=i+1;end loop;dbms_output.put_line(1-100之間的和：|total);end;PL/SQ

28、L開(kāi)發(fā)-程序流程控制練習(xí)1、打印乘法口訣表2、打印水仙花數(shù)3、編寫(xiě)程序，向表中輸入10000條記錄PL/SQL開(kāi)發(fā)-存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程create or replace procedure (形式參數(shù))is|asbeginexceptionend;PL/SQL開(kāi)發(fā)-存儲(chǔ)過(guò)程創(chuàng)建存儲(chǔ)過(guò)程-實(shí)例create or replace procedurechange_salary(v_empno in number,v_new_sal in number)isv_name emp.ename%type;beginselect ename into v_name from emp where empno

29、=v_empno;update emp set sal=v_new_sal where empno=v_empno;dbms_output.put_line(v_name|工資修改完畢);commit;end;PL/SQL開(kāi)發(fā)-存儲(chǔ)過(guò)程執(zhí)行存儲(chǔ)過(guò)程execute set serveroutput on;exec change_salary(7369,8888);刪除存儲(chǔ)過(guò)程drop procedure change_salary;PL/SQL開(kāi)發(fā)-存儲(chǔ)過(guò)程存儲(chǔ)過(guò)程練習(xí) 編寫(xiě)一個(gè)給特殊雇員加薪10%的存儲(chǔ)過(guò)程，同時(shí)檢查如果已經(jīng)雇傭該雇員超過(guò)60個(gè)月，則給他額外加薪3000。PL/SQL開(kāi)發(fā)-存儲(chǔ)

30、過(guò)程crete or replace procedure raise_sal(no in number) asvhiredate date;vsal emp.sal%type;beginselect hiredte,sal into vhiredate,vsal from empwhere empno=no;if months_between(sysdate,vhiredate)60 thenvsal:=nvl(sal,0)*1.1+3000;elsevsal:=nvl(sal,0)*1.1;end if;update emp set sal=vsal where empno=no;end;PL/SQL開(kāi)發(fā)-觸發(fā)器 觸發(fā)器是與存儲(chǔ)過(guò)程類似的另外一種保存在數(shù)據(jù)庫(kù)服務(wù)器端的PL/SQL程序單元，但運(yùn)行機(jī)制存在明顯差別，觸發(fā)器不能由用戶或應(yīng)用程序顯示調(diào)用，而是與特定的數(shù)據(jù)表、視圖操作或數(shù)據(jù)庫(kù)事件聯(lián)系到一起，當(dāng)進(jìn)行特定操作或發(fā)生特定事件時(shí)系統(tǒng)將自動(dòng)觸發(fā)并執(zhí)行相關(guān)的觸發(fā)器程序代碼。PL/SQL開(kāi)發(fā)-觸發(fā)器實(shí)例1create or replace trigger secure_deptbefore i