《数据库原理及应用》实验指导书.docx
- 文档编号:7912630
- 上传时间:2023-01-27
- 格式:DOCX
- 页数:27
- 大小:31.58KB
《数据库原理及应用》实验指导书.docx
《《数据库原理及应用》实验指导书.docx》由会员分享,可在线阅读,更多相关《《数据库原理及应用》实验指导书.docx(27页珍藏版)》请在冰豆网上搜索。
《数据库原理及应用》实验指导书
《数据库原理及应用-ORACLE》
实
验
指
导
书
信息科学与工程学院2014年9月
作者:
何小卫
实验一ORACLE平台下基本SQL语言的应用
实验二ORACLE基本操作与用户权限基本管理
实验三数据字典视图
实验四PL-SQL语言设计
实验五存储过程
实验六游标
实验七数据库触发器
实验一:
ORACLE平台下基本SQL语言的应用
实验目的
在oracle下熟练运用SQL语言来完成基本表的管理、索引的建立和删除、数据查询、数据更新、视图建立和删除等等操作。
实验要求
1、件基本配置:
IntelPentiumIII以上级别的CPU,大于512MB的内存。
2、软件要求:
WindowXP操作系统,ORACLE9i
3、实验学时:
4学时。
4、实验报告。
实验准备
ORACLE9i
实验内容
上机题1:
利用CreateTable创建scott用户下的3个表,包括每个表的码和外码的定义。
EmpC员工表字段内容如下:
empno员工号(主码),ename员工姓名,job工作,mgr上级编号(外码),hiredate受雇日期,sal薪金,comm佣金,deptno部门编号(外码);
createtableEmpc(empnonumber,enamevarchar(10),jobvarchar(20),mgrnumber,hiredatedate,salnumber,commnumber,deptnonumber,primarykey(empno),foreignkey(mgr)referencesEmpc(empno));
altertableempcaddconstraintdeptnoforeignkey(deptno)referencesdeptc(deptno);
DeptC部门表字段内容如下:
deptno部门号(主码),dname部门名称,loc办公地方
createtableDeptc(deptnonumber,enamevarchar(10),locvarchar(10),primarykey(deptno));
BonusC奖金表字段内容如下:
ename员工姓名,job工作名称,sal薪金,comm佣金
createtablebounsc(enamevarchar(10),jobvarchar(10),salnumber,commnumber);
上机题2:
用AlterTable向表中BonusC增加comm的约束为0-3000元之间。
altertablebounscaddconstraintcommcheck(commbetween0and3000);
上机题3:
将一个员工记录插入到Emp中(每个属性值自定);
insertintoempvalues(102,'zhangsan','manager',102,to_date('2010-05-06','yyyy-mm-dd'),null,null,10);
上机题4:
删除所有张三职工记录;
Deletefromempwhereename=’zhangsan’;
上机题5:
创建如下的视图:
创建“research”部门的职工情况,包含如下字段的视图view_research:
包括empno、ename、sal、comm。
createviewresearchasselectempno,ename,sal,commfromemp;
上机题6:
使用SELECT语句创建查询:
1、列出至少有一个员工的全部部门及人数。
selectcount(empno),deptnofromempgroupbydeptnohavingcount(ename)>1;
2、列出薪金比“SMITH”多的全部员工。
selectename,salfromempwheresal>(selectsalfromempwhereename='SMITH');
3、列出全部员工的姓名及其直接上级的姓名
selecttable1.ename,table2.enamefromemptable1,emptable2wheretable1.mgr=table2.empno;
4、列出受雇日期早于其直接上级的全部员工。
selecta.enamefromempa,empbwherea.mgr=b.empnoanda.hiredate 5、列出部门名称和这些部分的员工信息,同时列出那些没有员工的部门。 selectemp.*,dept.dnamefromemprightouterjoindeptondept.deptno=emp.deptno; 6、列出全部“CLERK”(做事员)的姓名及其部分名称。 selectename,dnamefromemp,deptwhereemp.deptno=dept.deptnoandemp.job='CLERK'; 7、列出最低薪金大于1500的种种工作。 selectjobfromempgroupbyjobhavingmin(sal)>1500; 8、列出在部分门“SALES”(贩卖部)做事的员工的姓名,假定不知道贩卖部的部门编号。 selectenamefrom(selectdeptnofromdeptwheredname='SALES')a,empwherea.deptno=emp.deptno; 9、列出薪金高于公司平均薪金的全部员工。 selectemp.*fromempwheresal>(selectavg(sal)fromemp); 10、列出与“SCOTT”从事相同工作的全部员工。 selectemp.*fromempwherejobin(selectjobfromempwhereename='SCOTT'); 11、列出薪金是部门30中员工的薪金的全部员工的姓名和薪金。 selectename,salfromempwheresalin(selectsalfromempwheredeptno=30); 12、列出薪金高于在部门30做事的全部员工的薪金的员工姓名和薪金。 selectename,salfromempwheresal>(selectmax(sal)fromempwheredeptno=30); 13、列出在每个部门做事的员工数量、均匀收入、平均做事限期。 selectcount(ename),avg(sal),floor(sysdate-hiredate/365)fromempgroupby(deptno); 14、列出全部员工的姓名、部门名称和收入。 selectename,dname,salfromemp,deptwhereemp.deptno=dept.deptno; 15、列出从事同一种工作但属于不同部门的员工。 selecta.enamefromempa,empbwherea.job=b.jobanda.deptno<>b.deptno; 16、列出全部部门的具体信息和部门人数。 select*fromdeptaleftjoin(selectdeptno.count(*)fromempgroupbydept(no)bona.deptno=b.deptno; 17、列出种种工作的最低收入。 selectjob,min(sal)fromempgroupbyjob; 18、列出各个部门的MANAGER(司理)的最低薪金。 selectmin(sal)fromempwherejob='MANAGER'groupbydeptno; 19、列出全部员工的年收入,按年薪从低到高排序。 selectename,sal*12afromemporderbyaasc; 20、找出佣金高于薪金的60%的员工. selectenamefromempwherecomm>sal*0.6; 21、找出部门10中所有经理(MANAGER),部门20中所有办事员(CLERK),既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料. Select*nameempwhere(deptno=’10’andjob=’MANAGER’)or(deptno=’20’andjob=’CLERK’)or(jobnotin(‘MANAGER’,’CLERK’)andsal>=2000) 22、找出不收取佣金或收取的佣金低于100的员工. select*fromempwherecomm<100orcomm=0; 23、找出各月倒数第3天受雇的所有员工. select*fromempwherehiredate=lastday(hiredate)-2; 24、显示正好为5个字符的员工的姓名. selectenamefromempwhereenamelike''; 25、显示不带有"R"的员工的姓名 selectenamefromempwhereenamenotlike'%R%';. 26、显示所有员工姓名的前三个字符. selectsubstr(ename,1,3)fromemp; 27、显示所有员工的姓名、工作和薪金,按工作的降序排序,若工作相同则按薪金排序. selectename,sal,jobfromemporderbyjobdesc,sal; 28、对于每个员工,显示其加入公司的天数. selectename,round(sysdate-hiredate)emp_datefromemp; 29、显示姓名字段的任何位置包含"A"的所 有员工的姓名. selectenamefromempwhereenamelike'%A%'; 30、查询和“李建国”是同一部门的职工姓名。 (使用子查询) selectenamefromempwheredeptno=(selectdeptnofromempwhereename='李建国'); 31、查询“Research”和”Manager”部门的职工姓名(UNION) selectenamefromempwherejob='RESEARCH'orjob='MANAGER'; 实验二ORACLE基本操作与用户权限基本管理 实验目的 熟悉ORACLE环境下基本操作和基本用户权限管理 实验要求 1、硬件基本配置: IntelPentiumIII以上级别的CPU,大于512MB的内存。 2、软件要求: WindowXP操作系统,ORACLE9i 3、实验学时: 2学时 4、实验报告 实验内容 上机题1: 连接命令 connectusername/password@服务名[assysdba,assysoper],当用特权用户连接时候,必须带上assysdba或assysoper。 Sys均可以;connSystem/manager disconnect: 断开与当前数据库的连接;SQL/plus中运用OK password 说明: 该命令用于修改用户的密码,如果要想修改其它用户的密码,需要用sys/system登录。 showuser说明: 显示当前用户名 exit: 该命令会断开与数据库的连接,同时会退出sql*plus disconnect; connectsystem/sysasdydsba; showuser; 上机题2: 创建用户和系统权限 createuserxiaozhangidentifiedbyxiao; grantcreatesessiontoxiaozhang; grantcreatetabletoxiaozhang; grantunlimitedtablespacetoxiaozhang; select*fromuser_sys_privs; 上机题3: 限制用户和密码修改 用户加锁: alteruser用户名accountlock 用户解锁: alteruser用户名accountunlock 用户口令即刻失效: alteruser用户名passwordexpire 删除用户: dropuser用户名[cascade] cascade用在当被删除的用户下还有未删除的对象(如一些表)时,强制级联删除。 它表示删除用户所有对象。 密码修改: 用SYS(或SYSTEM)用户登录: CONNSYS/PASS_WORDASSYSDBA; 使用如下语句修改用户的密码: ALTERUSERuser_nameIDENTIFIEDBY"newpass"; alteruserxiaozhangaccountlock; alteruserxiaozhangaccountunlock; alteruserxiaozhangpasswordexpire; alteruserxiaozhangidentifiedbyzhang; 上机题4: 对象权力的管理: Grant,Revoke运用 grantselectonmytabtoxiaozhang; grantupdate,select,deleteonmytabtoxiaozhang; grantallonmytabtoxiaozhang; connectscott/tiger; grantselectonemptoxiaozhangwithgrantoption; connectxiaozhang/zhang; select*fromscott.emp; grantselectonscott.emptolisi; connectlisi/lisi; select*fromscott.emp; 上机题5: 权限的传递 sys用户把一些系统权限授权给xiaozhang用户. grantalteranytabletoxiaozhangwithadminoption 查看当前用户的对象权限: select*fromuser_tab_privs grantunlimitedtablespacetoxiaozhangwithadminoption; createuserlisiidentifiedbylisi; grantcreatesessiontolisi; grantunlimitedtablespacetolisi; select*fromuser_tab_privs; 上机题6: 角色管理 在sys下创建角色: createrolemyrole; 给角色添加权限: grantcreatesessiontomyrole; grantcreatetabletomyrole; 创建用户: createuserzhangsan; grantmyroletozhangsan;//赋予以上的两个权限给zhangsan createrolemyrole; grantcreatesessiontomyrole; grantcreatetabletomyrole; createuserzhangsanidentifiedbyzhang; grantmyroletozhangsan; 实验三: 数据字典视图 实验目的 在oracle下熟练掌握数据字典视图的应用。 实验要求 1、基本配置: IntelPentiumIII以上级别的CPU,大于512MB的内存。 2、软件要求: WindowXP操作系统,ORACLE9i 3、实验学时: 2学时。 4、实验报告。 实验准备 数据字典是oracle数据库中最重要的组成部分,它提供了数据库的一些系统信息。 数据字典记录了数据库的系统信息,它是只读表和视图的集合,数据字典的所有者为sys用户。 用户只能在数据字典上执行查询操作(select语句),而其维护和修改是由系统自动完成的。 实验内容 上机题1user_tables user_tables; 用于显示当前用户所拥有的所有表,它只返回用户所对应方案的所有表 比如: selecttable_namefromuser_tables; 上机题2all_tables 用于显示当前用户可以访问的所有表,它不仅会返回当前用户方案的所有表,还 会返回当前用户可以访问的其它方案的表: 比如: selecttable_namefromall_tables; 上机题3dba_tables 它会显示所有方案拥有的数据库表。 但是查询这种数据库字典视图,要求用户必 须是dba角色或是有selectanytable系统权限。 例如: 当用system用户查询数据字典视图dba_tables时,会返回system,sys, scott方案所对应的数据库表,selecttable_namefromdba_tables; 上机题4 通过查询dba_users可以显示所有数据库用户的详细信息; select*fromdba_users; 通过查询数据字典视图dba_sys_privs,可以显示用户所具有的系统权限; select*fromdba_sys_privswheregrantee='LISI'; 通过查询数据字典视图dba_tab_privs,可以显示用户具有的对象权限; connectsystem/sys; select*fromdba_tab_privswheregrantee='LISI'; 通过查询数据字典dba_col_privs可以显示用户具有的列权限; select*fromdba_col_privswheregrantee='LISI'; 通过查询数据库字典视图dba_role_privs可以显示用户所具有的角色。 select*fromdba_role_privswheregrantee='ZHANGSAN'; //要查看scott具有的角色,可查询dba_role_privs; select*fromdba_role_privswheregrantee='SCOTT'; //查询orale中所有的系统权限,一般是dba select*fromsystem_privilege_maporderbyname; //查询oracle中所有对象权限,一般是dba selectdistinctprivilegefromdba_tab_privs; //查询oracle中所有的角色,一般是dba select*fromdba_roles; //查询数据库的表空间 selecttablespace_namefromdba_tablespaces; 上机题5 1、如何查询一个角色包括的权限? a.一个角色包含的系统权限 select*fromdba_sys_privswheregrantee='角色名' select*fromdba_sys_privswheregrantee='MYROLE'; select*fromrole_sys_privswhererole='角色名' b.一个角色包含的对象权限 select*fromdba_tab_privswheregrantee='角色名'; select*fromrole_tab_privswhererole='角色名'; select*fromdba_tab_privswheregrantee='MYROLE'; 2、oracle究竟有多少种系统角色? select*fromdba_roles; 3、如何查看某个用户,具有什么样的角色? select*fromdba_role_privswheregrantee='用户名'; select*fromdba_role_privswheregrantee='ZHANGSAN'; 4、显示当前用户可以访问的所有数据字典视图。 select*fromdictwherecommentslike'%grant%'; 5、显示当前数据库的全称n select*fromglobal_name; 实验四PL-SQL语言设计 实验目的 在oracle下熟练掌握PL-SQL语言的使用,包括语言语法、例外处理、函数,包等。 实验要求 1、基本配置: IntelPentiumIII以上级别的CPU,大于512MB的内存。 2、软件要求: WindowXP操作系统,ORACLE9i 3、实验学时: 4学时。 4、实验报告。 实验准备 编写规范ν 1.注释 单行注释-- select*fromempwhereempno=7788;--取得员工信息 多行注释/*...*/来划分 2.标志符号的命名规范 1).当定义变量时,建议用v_作为前缀v_sal 2).当定义常量时,建议用c_作为前缀c_rate 3).当定义游标时,建议用_cursor作为后缀emp_cursor 4).当定义例外时,建议用e_作为前缀e_error 3、pl/sql块: 块(block)是pl/sql的基本程序单元,编写pl/sql程序实际上就是编写pl/sql块,要完成相对简单的应用功能,可能只需要编写一个pl/sql块,但是如果想要实现复杂的功能,可能需要在一个pl/sql块中嵌套其它的pl/sql块。 pl/sql块由三个部分构成: 定义部分,执行部分,例外处理部分: declare /*定义部分——定义常量、变量、游标、例外、复杂数据类型*/ begin /*执行部分——要执行的pl/sql语句和sql语句*/ exception /*例外处理部分——处理运行的各种错误*/ end; pl/sql块的实例 (1) 实例1-只包括执行部分的pl/sql块ν Sql代码 setserveroutputon--打开输出选项 begin dbms_output.put_line('hello'); end; 相关说明: dbms_output是oracle所提供的包(类似java的开发包),该包包含一些过程, put_line就是dbms_output包的一个过程。 pl/sql块的实例 (2) 实例2-包含定义部分和执行部分的
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库原理及应用 数据库 原理 应用 实验 指导书
![提示](https://static.bdocx.com/images/bang_tan.gif)