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

1、數(shù)據(jù)庫技術(shù)培訓(xùn)課程講義趙正道主要內(nèi)容數(shù)據(jù)庫基本管理SQL語句編寫數(shù)據(jù)庫查詢及優(yōu)化PL/SQL開發(fā)數(shù)據(jù)庫基本管理用戶管理創(chuàng)建用戶修改用戶屬性刪除用戶權(quán)限管理 在數(shù)據(jù)庫中，權(quán)限是執(zhí)行某種特定數(shù)據(jù)庫操作的權(quán)利或許可，比如建立數(shù)據(jù)庫連接、創(chuàng)建表或視圖等數(shù)據(jù)庫對象、插入、修改和刪除數(shù)據(jù)等操作。用戶在進行數(shù)據(jù)庫操作時，數(shù)據(jù)庫將會首先驗證該用戶是否擁有執(zhí)行該操作的權(quán)限。數(shù)據(jù)庫基本管理權(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ù)庫基本管理權(quán)限管理授予權(quán)限(grant)查看權(quán)限收回權(quán)限(revoke)select * from system_privilege_map查詢當(dāng)前數(shù)據(jù)庫系統(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ù)庫基本管理角色管理創(chuàng)建角色角色授權(quán)授予角色收回角色刪除角色SQL語句編寫數(shù)據(jù)定義語言(DDL)語句名稱功能/含義create創(chuàng)建表alter修改表drop刪除表rename重命名truncate刪除表中所有數(shù)據(jù)SQL語句編寫數(shù)據(jù)操作語言(DML)語句名稱功能/含義insert向表中插入記錄update修改記錄delete刪除記錄select檢索數(shù)據(jù)SQL語句編寫數(shù)據(jù)控制語言(DCL)語句名稱功能/含義grant授予權(quán)限及角色revoke收回權(quán)限及角色事物控制語句(TCS)語句名稱功能/含義commit對數(shù)據(jù)的修改永久保存到數(shù)據(jù)庫rollback

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

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

6、眾,奧迪100,440000,1999-11-17c006,一汽大眾,奧迪A6,540000,2006-8-9c039,東風(fēng)悅達(dá)起亞,千里馬,13200,2003-8-23c095,北京奔馳,奔馳280,420000,2008-12-24SQL語句編寫-插入數(shù)據(jù)實例create table student( sno number(8), sname varchar2(10), sex char(4);insert into student values(1001,zhangqiang,女);SQL語句編寫-修改和刪除數(shù)據(jù)實例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語句編寫-修改表結(jié)構(gòu)1、增加字段2、改變類型和寬度3、字段重命名4、刪除字段5、刪除表中所有數(shù)據(jù)6、刪除表SQL語句編寫-約束 在插入、更新行或者從表中刪除行的時候強制表中的數(shù)據(jù)遵循規(guī)則。 在Oracle數(shù)據(jù)庫中，可以為表設(shè)置約束（Constraint）,來防止無效數(shù)據(jù)輸入到表中。 當(dāng)為某個表定義了約束后，對該表做的所有操作都必須滿足約束的要求，否則操作將失

8、敗。SQL語句編寫-約束約束類型約束類型說明not null指定列不能包含空值unique指定列的值或者列的組合的值對于表中所有的行必須是唯一的primary key表的每行的唯一性標(biāo)識foreign key在列和引用表的一個列之間建立并且強制一個外鍵關(guān)系check指定一個必須為真的條件SQL語句編寫-約束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語句編寫-約束列級約束create table deptment( deptno number(2), dname varchar2(14) constraint dept_dname_uk unique, loc varchar2(13) );UNIQUE約束SQL語句編寫-約束表級約束create table deptment( deptno number(2), dname varchar2(14), loc varchar2(13), constraint dept_dname_uk unique(dnam

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

11、Primary key約束SQL語句編寫-約束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語句編寫-約束on delete cascade選項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語句編寫-約束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ù)庫

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

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

15、r(hello,2,2)ellength()求串長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ù)庫查詢及優(yōu)化-基本查詢常用數(shù)值函數(shù)函數(shù)功能用法返回結(jié)果abs()求絕對值abs(-4)4sqrt()開平方sqrt(81)9mod()取余數(shù)mod(17,3)2r

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

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

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

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

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

21、薪金，條件是他們的薪金高于部門30中所有雇員的薪金數(shù)據(jù)庫查詢及優(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ù)庫查詢及優(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ù)庫查詢及優(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ù)庫查詢及優(yōu)化-查詢優(yōu)化4、用wh

23、ere替換havingselect語句執(zhí)行順序是：先where子句，再group by子句，然后select查詢，再后having子句，最后是order by子句，因此在進行分組查詢時，如果過濾條件不涉及分組計算，則應(yīng)使用where替換having子句指定過濾條件。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開發(fā)-PL/SQL塊PL/SQL基本結(jié)構(gòu)d

24、eclarebeginexceptionend;PL/SQL開發(fā)-PL/SQL塊實例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開發(fā)-PL/SQL塊create table exception_log( aris_time date default sysdate, msg varchar2(200); PL/SQL開發(fā)-程序流程控制分支結(jié)構(gòu)if(條件表達(dá)式1) then語句序列1elsif(條件表達(dá)式2) then語句序列2.else語句序列nend if; PL/SQL開發(fā)-程序流程控制分支結(jié)構(gòu) 實例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(需要交納個人所得稅);elsedbms_output.put_line(不需繳稅);end if;end;PL/SQL開發(fā)-程序流程控制循環(huán)結(jié)構(gòu)-for循環(huán)for in . loop語句序列.end loop; PL/SQL開發(fā)-程序流程控制for循環(huán)結(jié)構(gòu) 實例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開發(fā)-程序流程控制循環(huán)結(jié)構(gòu)-while循環(huán)while 條件 loop語句序列.end loop; PL/SQL開發(fā)-程序流程控制while循環(huán)結(jié)構(gòu) 實例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開發(fā)-程序流程控制練習(xí)1、打印乘法口訣表2、打印水仙花數(shù)3、編寫程序，向表中輸入10000條記錄PL/SQL開發(fā)-存儲過程創(chuàng)建存儲過程create or replace procedure (形式參數(shù))is|asbeginexceptionend;PL/SQL開發(fā)-存儲過程創(chuàng)建存儲過程-實例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開發(fā)-存儲過程執(zhí)行存儲過程execute set serveroutput on;exec change_salary(7369,8888);刪除存儲過程drop procedure change_salary;PL/SQL開發(fā)-存儲過程存儲過程練習(xí) 編寫一個給特殊雇員加薪10%的存儲過程，同時檢查如果已經(jīng)雇傭該雇員超過60個月，則給他額外加薪3000。PL/SQL開發(fā)-存儲

30、過程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開發(fā)-觸發(fā)器 觸發(fā)器是與存儲過程類似的另外一種保存在數(shù)據(jù)庫服務(wù)器端的PL/SQL程序單元，但運行機制存在明顯差別，觸發(fā)器不能由用戶或應(yīng)用程序顯示調(diào)用，而是與特定的數(shù)據(jù)表、視圖操作或數(shù)據(jù)庫事件聯(lián)系到一起，當(dāng)進行特定操作或發(fā)生特定事件時系統(tǒng)將自動觸發(fā)并執(zhí)行相關(guān)的觸發(fā)器程序代碼。PL/SQL開發(fā)-觸發(fā)器實例1create or replace trigger secure_deptbefore i