數(shù)據(jù)庫開發(fā)技術(shù)實驗報告認識Oracle常用管理工具和DDL、DML實踐

1、福建工程學(xué)院信息科學(xué)與工程學(xué)院實驗報告 2013 2014 學(xué)年第 1 學(xué)期 任課老師： 蔣建輝 課程名稱 數(shù)據(jù)庫開發(fā)技術(shù)班級座號姓名實驗題目認識oracle常用管理工具和ddl、dml實踐實驗時間實驗開始日期： 2013.10. 12 報告提交日期： 2013.10.16 實驗?zāi)康摹⒁髮嶒災(zāi)康模? 認識并熟悉oracle常用的管理工具：sql plus、isql plus、sql developter和em。2 掌握oracle表的數(shù)據(jù)定義（ddl）語言，實現(xiàn)表的定義、刪除與修改。3 掌握創(chuàng)建oracle表各種約束的方式和方法。4 掌握利用數(shù)據(jù)字典視圖查看各種有用信息的方法。5 掌握插入、

2、刪除和更新表中數(shù)據(jù)的方法。6 掌握控制事務(wù)的方法。7 掌握where子句中條件表達式的使用方法。8 掌握訪問替換變量的方法。9 學(xué)會創(chuàng)建并執(zhí)行一個腳本文件。10 掌握case 結(jié)構(gòu)的使用方法。實驗設(shè)計內(nèi)容及實現(xiàn)步驟實驗內(nèi)容和要求1. *登錄到sql plus、isql plus、sql developter和em，認識并運用這些常用的oracle管理工具。2. ddl實踐（1） oracle表的ddl實踐。設(shè)有如下關(guān)系表s_班名座號(如：s_wg100103)： s_wg100103 (sno, sname, ssex, sage, sdept)，主關(guān)鍵字是sno。其中sno為學(xué)號，numbe

3、r(5)，學(xué)號不能為空，值是惟一的；sname為姓名，varchar2(20)；ssex為性別，char(2)；sage為年齡，number(2)； sdept為所在系名，varchar2(10)。寫出實現(xiàn)下列功能的sql語句。1) 創(chuàng)建此表并插入數(shù)據(jù)；create table s_ji3110307207( sno number(5) primary key not null, sname varchar2(20), ssex char(2), sage number(2), sdept varchar2(10)insert into s values(95001,李明勇,男,20,cs);

4、insert into s values(95002,劉晨,女,19,is);insert into s values(95003,王名,女,18,ma);insert into s values(95004,張立,男,19,cs);insert into s values(95005,張軍,男,21,ma);insert into s values(95006,王張鳳,女,19,fl);insert into s values(95011,王敬,女,18,is);insert into s values(95021,張名惠,男,19,fl);2) 查看此表的數(shù)據(jù)結(jié)構(gòu)和數(shù)據(jù);3) 根據(jù)此表再復(fù)

5、制創(chuàng)建一個只有is系學(xué)生組成的新表s_is_班名座號;create table s_is_ji3110307207as select * from s_ji3110307207where sdept=is4) 向s表添加“入學(xué)時間（comedate）”列，其數(shù)據(jù)類型為日期型（date）；alter table s_ji3110307207 add comedate date5) 將年齡的數(shù)據(jù)類型改為number(3)且默認值為19，并查看這些修改后的表結(jié)構(gòu);列無數(shù)據(jù)的情況下直接修改：alter table s_ji3110307207 modify sage munber(3),default

6、(19)列有數(shù)據(jù)的情況下：新建一列alter table s_ji3110307207 add newsage number(3) default(19)舊列數(shù)據(jù)復(fù)制新列中update s_ji3110307207 set newsage=cast (sage as number(3);刪除舊列alter table s_ji3110307207 drop column sage;重新命名新列alter table s_ji3110307207 rename column newsage to sage;6) 刪除新添加的列，查看更改情況;alter table s_ji3110307207

7、drop column comedate7) 將表s改名為student;alter table s_ji3110307207 rename to student_31103072078) 為表s添加注釋學(xué)生信息表comment on table student_3110307207 is 學(xué)生信息表讀取注釋:select * from user_tab_comments where comments is not null9) 截斷s表;truncate table s_ji311030720710) 刪除生成的新表s_is_班名座號；truncate和delete只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)(

8、定義)drop語句將刪除表的結(jié)構(gòu)被依賴的約束(constrain),觸發(fā)器(trigger),索引(index);依賴于該表的存儲過程drop table s_is_ji3110307207(2) 創(chuàng)建約束要求按普遍采用的約定為這些約束命名，約束創(chuàng)建后要進行約束測試。（參見所附的表結(jié)構(gòu)和數(shù)據(jù)，表名要加上班名座號）create table course_ji3110307207( cno number(4) , cname varchar2(30), cpno number(4), ccredit number(2), climit number(4)1) 為course表添加一個主鍵約束;al

9、ter table course_ji3110307207 add constraint pk_cno primary key(cno)查看此表的約束：select * from user_constraints where table_name=course_ji31103072072) 創(chuàng)建sc表的主鍵約束和外鍵約束（一個列級和一個表級），表級的外鍵約束要求允許使用級聯(lián)刪除選項;alter table course_ji3110307207 add constraint pk_cno primary key(cno)select * from user_constraints where

10、table_name=scalter table sc add constraint sno_cno_fk foreign key(sno,cno) references s_ji3110307207(sno) and course_ji3110307207(cno)ora-02256: 要引用的列數(shù)必須與已引用列數(shù)匹配?3) 為s表的ssex創(chuàng)建check約束;alter table s_ji3110307207 add constraint ssex_chk check(ssex in(男,女)select * from user_constraints where table_name=

11、s_ji31103072074）為s表創(chuàng)建一個合適的not null和unique惟一性約束;姓名為非空:select * from user_constraints where table_name = s_ji3110307207學(xué)號是唯一的: alter table s_ji3110307207 add constraint sno_unique unique(sno)建表時已經(jīng)將sno設(shè)為主鍵了，然后就添加不進去了;ora-02261: 表中已存在這樣的唯一關(guān)鍵字或主鍵;然后查約束條件的時候又沒有看到？5） 在上題基礎(chǔ)上，刪除其中一個約束，啟用/禁用其中一個約束，并測試之。 alter

12、 table s_ji3110307207 drop constraint ssex_chk alter table s_ji3110307207 disable constraint sys_c005176alter table s_ji3110307207 enable constraint sys_c005176(3) 使用數(shù)據(jù)字典視圖利用任意3種數(shù)據(jù)字典視圖查看數(shù)據(jù)庫對象的信息，如查看表、表空間信息和約束信息等。查看用戶下所有的表：select * from user_tables顯示用戶信息所屬的表空間：select default_tablespace,temporary_tabl

13、espace from dba_users查詢約束信息：select * from user_constraints 3. dml實踐(1) oracle表的dml操作。1) 7369號雇員加薪10%。update empset sal=sal*1.1where empno=73692) *從部門表（dept）中刪除部門30，如果刪除不成功，那么寫出你自己的建議，說明應(yīng)該怎么做。應(yīng)該把30號部門的員工信息刪除，因為部門表的部門編號作為了員工表的外鍵3) 向emp表中插入一個新雇員。insert into emp values(7950,qsl,manager ,7839,to_date(21-

14、08-2003,dd-mm-yyyy), 4000.00, null, 10)(2) 數(shù)據(jù)檢索。1) *顯示emp表中月薪$2850的雇員的姓名和薪資。select ename,sal from empwhere sal28502) *顯示雇員號為7566的員工的姓名和部門號。select ename,deptno from empwhere empno=75663) 顯示受雇日期在20-2月-1981與1-5月-1981之間的員工的姓名、工種和受雇日期，要求以受雇日期升序排列。select ename ,job,hiredate from empwhere hiredate between

15、20-2月-1981 and 1-5月-1981order by hiredate4) *顯示部門號為10、30的員工的姓名和部門號，要求以姓名序排列。select ename,deptno from empwhere deptno in (10,30)order by ename5) 顯示emp表中所有崗位（job）的類型。select distinct job from emp6) 顯示沒有上司員工的姓名和工種。select ename ,job from empwhere mgr is null7) 顯示部門號為10、30且月薪$1500的所有員工的姓名和月薪，要求顯示列名為 “empl

16、oyee”和“monthly salary”。select ename employee,sal monthlysalary from empwhere deptno in (10,30) andsal1500 8) 顯示哪些姓名中含有字母”a”并且部門號為30的員工或者上司號為7782的員工姓名。select ename from emp where ename like %a% and deptno=30 or mgr=77829) *在查詢emp表中使用連接操作符和原義字符串。查詢結(jié)果類同以下：select ename | is a |jobfrom emp雇員和職務(wù)表-smith is

17、 a clerkallen is a salesmanward is a salesmanjones is a managermartin is a salesman.10) *顯示哪些掙了傭金（comm）的員工的姓名、月薪和傭金，要求顯示結(jié)果按月薪降序，月薪相同再按傭金降序。降序：select sal from emp order by sal desc升序：select sal from emp order by sal select ename,sal,comm from empwhere comm is not nullorder by sal desc,comm desc(3) 事務(wù)

18、控制、替代變量、case 結(jié)構(gòu)1) *請為用戶創(chuàng)建一個定制提示符，提示用戶為deptno列輸入5099之間的一個值2) *學(xué)習(xí)使用commit、rollback和savepoint等事務(wù)控制語句。3) *學(xué)會創(chuàng)建并執(zhí)行一個腳本文件。4) *觀察數(shù)據(jù)的讀一致性。(提示：用兩個不同的連接來觀察，另一個連接比如可以用scott的身份連接)5) 顯示符合任意條件的雇員的編號以及其它任意的列6) 將case結(jié)構(gòu)用于update語句?；诠蛦T的job值來更新job值。 manager更新為管理人員，president更新為總經(jīng)理，其余的更新為普通人員。update empset job=case when

19、 job=manager then管理人員when job=president then總經(jīng)理else 普通人員 end empno ename job mgr hiredate sal comm deptno- - - - - - - - 7369 smith 普通人員 7902 17-12月-80 800 20 7499 allen 普通人員 7698 20-2月 -81 1600 300 30 7521 ward 普通人員 7698 22-2月 -81 1250 500 30 7566 jones 管理人員 7839 02-4月 -81 2975 20 7654 martin 普通人員

20、7698 28-9月 -81 1250 1400 30 7698 blake 管理人員 7839 01-5月 -81 2850 30 7782 clark 管理人員 7839 09-6月 -81 2450 10 7788 scott 普通人員 7566 13-7月 -87 3000 20 7839 king 總經(jīng)理 17-11月-81 5000 10 已選擇14行。一、 *附加題查詢emp表中10號部門的員工平均工資，如果參加平均的員工工資低于2000就以2000作為最低可能的工資參加平均。指出你使用的case結(jié)構(gòu)是簡單case表達式(simple case expression )還是搜索式

21、case表達式(searched case expression)。二、 實驗報告根據(jù)以上實驗內(nèi)容的要求認真填寫實驗報告，記錄所有的實現(xiàn)方法和運行結(jié)果，并記錄實驗過程中遇到的困難和解決問題的方法。(實驗報告中不要求寫加*題目的實驗過程和內(nèi)容)三、 附錄：create table course(cnonumber(4) cname varchar2(30),cpno number(4),ccreditnumber(2),climitnumber(4);create table sc(sno number(5),cnonumber(4), gradenumber(3,1);insert into

22、course values(1,數(shù)據(jù)庫,5,5,10);insert into course values(2,數(shù)學(xué),null,3.10);insert into course values(3,信息系統(tǒng),1,4,12);insert into course values(4,操作系統(tǒng),6,4,12);insert into course values(5,數(shù)據(jù)結(jié)構(gòu),7,5,16);insert into course values(6,數(shù)據(jù)處理,null,3,15);insert into course values(7,pascal語言,6,2,null);commit;insert into sc values(95001,5,92);insert into sc values(95002,3,80);insert into sc values(95001,1,58);insert i