數(shù)據(jù)庫原理實驗報告電子和麗

1、數(shù)據(jù)庫原理與技術數(shù)據(jù)庫原理與技術DATABASEPrinciples,Technology and Application實驗報告實驗報告電子信息電子信息 1203 班班0909123003和麗和麗中南大學信息科學與工程學院中南大學信息科學與工程學院1有關說明有關說明1參考教材參考教材11 張祖平張祖平, ,孫星明等孫星明等. .數(shù)據(jù)庫原理及應用數(shù)據(jù)庫原理及應用, ,中南大學出版社中南大學出版社,2010.11,2010.11 22 oracle10goracle10g 教程從入門到精通，百度文庫教程從入門到精通，百度文庫2 2最終實驗結果呈現(xiàn)最終實驗結果呈現(xiàn)一個文本文件，包括所有實驗內(nèi)容語句

2、、調(diào)試數(shù)據(jù)、思考題等.3 3對象命名對象命名（1）文件名取*_?,其中為現(xiàn)在班號+學號后 4 位，如 oraexp_10104.txt(sql),表示電子信息 1 班,學號后四位為 0104,總學號為 0909120107，最后源文件名可取.txt 或*.sql 等（2）其它對象名如用戶、表、視圖、過程、觸發(fā)器等按規(guī)則$_*_?,其中$為對象類型標識碼，如用戶取名 u,表取 t,視圖 V，過程取 P，觸發(fā)器取 TG；?為班級+學號后四位，與文件名的命名規(guī)則類同。*可自己命名，不作強制要求，可以用英文簡寫法，也可以用中文拼音首字母。如 T_STUD_20807, p_sumscore_11517

3、,tg_STUD_ST_12101。目目 錄錄實驗一、熟悉實驗一、熟悉 ORALCE 環(huán)境并練習環(huán)境并練習 SQL 的定義的定義.1一、目的與要求.1二、操作環(huán)境.1三、實驗內(nèi)容.1四、思考題.3五、實驗報告要求.3實驗二、數(shù)據(jù)更新操作實驗二、數(shù)據(jù)更新操作.4一、目的與要求.4二、操作環(huán)境.4三、實驗內(nèi)容.4四、思考題.4五、實驗報告要求.4實驗三、視圖及權限控制實驗三、視圖及權限控制.5一、目的與要求.5二、操作環(huán)境.5三、實驗內(nèi)容（綜合設計與實現(xiàn)）.5四、實驗報告要求.5實驗四、數(shù)據(jù)庫觸發(fā)器與過程實驗四、數(shù)據(jù)庫觸發(fā)器與過程.6一、目的與要求.6二、操作環(huán)境.6三、實驗內(nèi)容.6四、實驗報告要

4、求.6附錄：附錄：ORACLE 有關命令及內(nèi)部字典有關命令及內(nèi)部字典.7ORACLE 的 SQL*PLUS 基本命令.7ORACLE的啞表與數(shù)據(jù)字典 .7例句中設有一定的錯誤，請注意調(diào)試。.70實驗一、熟悉實驗一、熟悉 ORALCE 環(huán)境并練環(huán)境并練習習 SQL 的定義的定義一、目的與要求一、目的與要求本實驗主要是熟悉 ORACLE 的運行環(huán)境，在 CLIENT 端進行聯(lián)接設置與服務器聯(lián)通，進入 ORACLE 的 SQL*PLUS 的操作環(huán)境，進一步建立表格，并考慮主鍵,外部鍵,值約束。二、操作環(huán)境二、操作環(huán)境硬件：主頻 2GHz 以上服務器（內(nèi)存 2GB 以上、硬件空閑 2.2GB 以上）

5、，主頻 1GHz以上微機，內(nèi)存 1GB 以上。軟件：WINDOWS XP/2000/2003/或 win7/2008 等，如操作系統(tǒng)是 SERVER 版 可安裝ORACLE/9i/10g/11g/12C FOR NT/WINDOWS(注意在 32 位與 64 位的區(qū)別，可選企業(yè)版) 如果 Windows 非 server 如 XP/win7 等，安裝時請選擇個人版(PERSONAL)三、實驗內(nèi)容三、實驗內(nèi)容1.1.上機步驟上機步驟在實驗室上機時,一般安裝時管理員已設置好.先找到 windows 中的 oracle 服務（一般為 OracleServiceORCL，如下圖 1）,啟動此服務,再找

6、到 sql*plus 所在的位置（一般桌面上有如下圖 2 的程序組有）,運行即可進行登錄界面.圖 1 Oracle 在計算機系統(tǒng)中的服務圖 2 稱 Sql*plus 在系統(tǒng)程序組中的位置1實驗室現(xiàn)有環(huán)境操作步驟：實驗室現(xiàn)有環(huán)境操作步驟：通過桌面我的電腦找到管理服務的入口，如圖 3，或者通過資源管理器找到管理。圖 3 通過桌面我的電腦找到管理服務的入口然后找到服務，如圖 4。圖 4 系統(tǒng)中的服務再找啟動 oracle 的服務如圖 1：（1）從桌面或在程序中（圖 2）執(zhí)行 SQL*PLUS（2）輸入：system/manager（3）在 SQL狀態(tài)輸入建立用戶命令:create user user

7、name identified by password;其中 username 與 password 不要數(shù)字開頭，用戶名 username 中按規(guī)則加班級學號后綴如：U_11221 表示 1 班學號尾數(shù)為 1221 號的用戶。（4）給用戶授權：grant resource,connect to username;(5) 連接用戶：connect username/password在 SQL就可以建表等操作了2.根據(jù)要求建立如下表根據(jù)要求建立如下表定義基本表格 dept_?（部門）與 emp_?(員工） ，關系模式如下：2Dept_?(DEPTNO char(4) Primary key,NA

8、ME VARCHAR2(32),LOC VARCHAR2(32) in(長沙，深圳),tel varchar2(13) 以 0731，0755 開頭）Emp_?(EMPNO char(10) primary key, ENAME VARCHAR2(32), JOB VARCHAR2(12),MGR char(10) foreign key,HIREDATE DATE 19920101, SAL NUMBER(7,2),COMM NUMBER(7,2), DEPTNO char(4) foreign key,sal+commto_date(19920101,yyyymmdd),sal numbe

9、r(7,2),comm number(7,2),deptno char(4) references dept_33003(deptno),check(sal+comm=5000);alter table emp_33003 add (empyear char(50);update emp_33003 set empyear=(ceil(sysdate-hiredate)/365);3 insert into dept_33003 values(4,accounting,深圳,07314646745); insert into dept_33003 values(4,would,長沙,07312

10、7485); insert into dept_33003 values(4,dtre,深圳,0731436547); insert into dept_33003values(3,aaa,深圳,07553254365); insert into dept_33003 values(3,eee,深圳,07553246547); insert into dept_33003 values(3,sansan,長沙,07312435654); insert into dept_33003 values(3,huahua,長沙,0731334654); insert into dept_33003va

11、lues(3,zhang,長沙,075523436); insert into dept_33003 values(3,research,長沙,075525678);insertinto dept_33003values(2,xiaoniu,深圳,0731545666);insertinto dept_33003values(1,和麗,深圳,0731545123);insertinto dept_33003values(1,李四,深圳,0731545456);4insertinto emp_33003values(0101,李四,董事長,null,11-5 月-14,3000,1000,1);

12、insert into emp_33003 values(0111,zhang,工程師,0102,19-7 月-14,2000,500,3);insert into emp_33003 values(0112,research,工程師,0102,19-7 月-14,2000,500,3);insert into emp_33003 values(0110,huahua,工程師,0102,19-7 月-14,2000,500,3);insert into emp_33003 values(0109,sansan,工程師,0102,19-7 月-14,2000,500,3);insert into

13、 emp_33003 values(0107,aaa,工程師,0102,14-7 月-14,2000,500,3);insert into emp_33003 values(0108,eee,工程師,0102,19-7 月-14,2000,500,3);insert into emp_33003 values(0105,would,財務,0103,21-7 月-14,2000,500,4);insert into emp_33003 values(0106,dire,財務,0103,29-7 月-14,2000,500,4);insert into emp_33003 values(0104,

14、accounting,財務,0103,26-7 月-14,2000,500,4);insert into emp_33003 values(0102,和麗,總經(jīng)理, 0101, 01-5 月-14, 2000,1000,1);insert5 into emp_33003 values(0103,xiaoniu,部門經(jīng)理,0102,16-4 月-14,2000,500,2);四、思考題四、思考題1能否任意改變表的名稱。答：能。ALTER TABLE 當前表名 RENAME TO。2能否先刪除 dept 表？答：不能，為了約束完整性不應該被首先刪除。但是可以用 cascade 關鍵字實現(xiàn)級聯(lián)刪除。

15、3能否改變 dept 表中 DEPTNO 類型與長度，或改變 emp 表中 empno 的類型與長度。答：可以通過 ALTER TABLE DEPT_? MODIFY(DNO CHAR(2)修改，表中主鍵值不允許為 null；屬性定義中聲明為 not null 也不允許空值。五、實驗報告要求五、實驗報告要求1實驗內(nèi)容2源程序清單3回答思考題4寫出在實驗過程中遇到的問題及解決方法5要求字跡端正、條理清晰、概念正確6實驗二、實驗二、數(shù)據(jù)查詢與更新操作數(shù)據(jù)查詢與更新操作一、目的與要求一、目的與要求本實驗主要是熟悉在 SQL*PLUS 環(huán)境下進行數(shù)據(jù)記錄查詢、更新與刪除，其中查詢考慮簡單查詢,復雜查詢

16、,嵌套查詢。二、操作環(huán)境二、操作環(huán)境同實驗一。三、實驗內(nèi)容三、實驗內(nèi)容1查詢所有職工的記錄 答：select * from emp_33003;2查詢所有部門的記錄，并按部門號升序排列 答：select * from dept_33003 order by deptno;3.查詢有自己姓名的記錄 答：Select*from emp_33003 where ename=和麗;4.查詢李四的 EMPNO，ENAME，JOB 答：select empno,ename,job from emp_33003 where ename=李四;5.查詢 RESEARCHRESEARCH 部門部門所有的職工記錄

17、 答：select * from dept_33003 where name=research;6.查詢 RESEARCHRESEARCH 部門部門各職工的入司年數(shù)（CeilCeil（sysdate-HIREDATE)/365)）的人數(shù)，如 10 年的為 3 人，20 年的有 10 人等 答 select round(ceil(sysdate-hiredate)/365),count(round(ceil(sysdate-hiredate)/365) from emp_33003 where deptno in(select name=research from dept_33003);7.查

18、詢李四的 EMPNO，ENAME，JOB， DEPTNO，DNAME，LOC 答：Select empno,ename,job,deptno,name,loc from emp_33003,dept_33003 where ename=李四 emp_33003.deptno=dept_33003.deptno;8.在 emp 中增加入司年數(shù) years 并求出各人的具體數(shù)值放到表中（alter 與 update) 答：alter table emp_33003 add (empyear char(50);update emp_33003 set empyear=(ceil(sysdate-hi

19、redate)/365);9.刪除日期 2000 年 01 月 01 日前的所有員工信息 答：Delete from emp_33003 where hiredate2000-01-01; 7四、思考題四、思考題1查詢學生的記錄數(shù)。2測試表中的完整性約束，并分析不成功的原因3查詢平均入司年數(shù)最高與最低的部門五、實驗報告要求五、實驗報告要求1實驗內(nèi)容2調(diào)試后的各命令清單3寫出思考題的語句4寫出在實驗過程中遇到的問題及解決方法5要求字跡端正、條理清晰、概念正確8實驗三、視圖及權限控制實驗三、視圖及權限控制一、目的與要求一、目的與要求本實驗主要是熟悉在 SQL*PLUS 環(huán)境下進行數(shù)據(jù)視圖及權限控制

20、的操作。二、操作環(huán)境二、操作環(huán)境同實驗一。三、實驗內(nèi)容三、實驗內(nèi)容（綜合設計與實現(xiàn)）（綜合設計與實現(xiàn)）批處理實現(xiàn)為每個員工建立用戶，并授予 connect 角色，建立視圖并給相應查詢權限，讓每個員工用自己的用戶登錄能查詢到自己的整個信息。如果是 manager 則可以查詢到本部門的所有員工信息，如果是 president 則可以查詢所有員工信息。select create user u|empno| identified by p|empno|; from emp_33003;connect system/managercreate user u0101 identified by p0102

21、;create user u0102 identified by p0102;create user u0103 identified by p0103;create user u0104 identified by p0104;create user u0105 identified by p0105;create user u0106 identified by p0106;create user u0107 identified by p0107;create user u0108 identified by p0108;create user u0109 identified by p

22、0109;create user u0110 identified by p0110;create user u0111 identified by p0111;create user u0112 identified by p0112;connect emp_33003/heliselect grant connect,resource to u|empno|; from emp_33003; connect system/managergrant resource,connect to uu0101;9grant resource,connect to uu0102;grant resou

23、rce,connect to uu0103;grant resource,connect to uu0104;grant resource,connect to uu0105;grant resource,connect to uu0106;grant resource,connect to uu0107;grant resource,connect to uu0108;grant resource,connect to uu0109;grant resource,connect to uu0110;grant resource,connect to uu0111;grant resource

24、,connect to uu0112;grant create view to emp_33003;connect emp_33003/heli;create view v1 as select dept_33003.deptno,name,loc,tel,emp_33003,empno,ename,job,mgr,hiredate,sal,comm from dept_33003,emp_33003where dept_33003.deptno=emp_33003.deptno;create view v2 from v1 where deptno=manager.deptno;grant

25、select on v2 to manager;grant select on v1 to president;select * from U_33003; 10實驗四、數(shù)據(jù)庫觸發(fā)器與過程實驗四、數(shù)據(jù)庫觸發(fā)器與過程一、目的與要求一、目的與要求本實驗主要是熟悉在 SQL*PLUS 環(huán)境下數(shù)據(jù)庫觸發(fā)器的建立及作用，了解大型數(shù)據(jù)庫編程。二、操作環(huán)境二、操作環(huán)境同實驗一。三、實驗內(nèi)容三、實驗內(nèi)容1先建立表 salgrad_?(job,maxsal,minsal)2. 刪除 emp_?中關于 sal+comm=5000 的檢查約束3建立觸發(fā)器滿足如下需求：插入或修改 emp_?表中的記錄時，判定并提示某

26、一 job 的 sal 滿足不超過 maxsal 不低于 minsal,如提示， “數(shù)據(jù)輸入有誤，job 為 manager 的最高工資應為 6000,最低應為 4000，你輸入的工資 8000 超過了最高 6000 的限制，請確認輸入數(shù)據(jù)是否正確”4.測試與調(diào)試觸發(fā)器create table salgrad_33003(job varchar2(12),maxsal number(7,2),minsal number(7,2);select insert into salgrad_33003 values(|deptno|,maxsal number(7,2),minsal number(7

27、,2);from emp_33003;insert into salgrad_33003 values(1,4500,3000);insert into salgrad_33003 values(2,3500,2000);insert into salgrad_33003 values(3,3000,2000);insert into salgrad_33003 values(4,3000,2000);alter table emp_33003 drop constraintcheck(sal + commmaxsal or salminsal then raise_application_e

28、rror( -20000, 數(shù)據(jù)輸入有誤，請確認輸入數(shù)據(jù)是否正確); end if;end;/*建立觸發(fā)器滿足如下需求：插入或修改 emp_32719 表中的記錄時，判定并提示某一 job 的 sal 滿足不超過 maxsal 不低于 minsal,如提示， “數(shù)據(jù)輸入有誤，job 為 manager 的最高工資應為6000,最低應為 4000，你輸入的工資 8000 超過了最高 6000 的限制，請確認輸入數(shù)據(jù)是否正確”5設計并調(diào)試過程（1）分部門統(tǒng)計工資與津貼的總和，如建立 sumsal_?過程，/*創(chuàng)建 sumsal_33003 表*/create table sumsal_33003(

29、deptno char(4) references dept_33003.deptno,sumsal number(7,2),sumcomm number(7,2);/*創(chuàng)建過程*/create or replace procedure Pro_sumsal_33003(s_sal in varchar)isvin_sal char(10);v_sal char(10);v_sumsal number;begin vin_sal :=0|s_rno; select count(emp_33003.sal) into v_sumsal From emp_33003DBMS_OUTPUT.put_line(部門 0||在員工表中的工資與津貼的總額為|v_sumsal|); /*顯示輸出*/exception