数据库系统管理.doc

1.知识目标通过综合实训进一步巩固、深化和扩展大家的Oracle 11g数据库管理和开发的基本知识和技能。（1）熟练掌握Oracle 11g数据库的操作；（2）熟练掌握Oracle 11g表的操作；（3）熟练掌握Oracle 11g视图的操作和应用；（4）掌握Oracle 11g索引的操作；（5）熟练掌握PL/SQL变成技术和Oracle 11g储存过程的操作和使用；（6）熟练掌握Oracle 11g触发器的操作和应用；（7）掌握Oracle 11g数据安全性操作；（8）熟练掌握Oracle 11g数据管理操作；（9）了解Oracle 11g数据库程序开发技术。2.能力目标培养学生运用所学的知识和技能解决Oracle 11g数据库管理和开发过程中所遇到的实际问题的能力、掌握基本的SQL脚本编写规范、养成良好的数据库操作习惯。（1）培养学生通过各种媒体搜集资料、阅读资料和利用资料的能力；（2）培养学生基本的数据库应用能力；（3）培养学生基本的编程逻辑思想；（4）培养学生通过各种媒体进行自主学习的能力。（一） 数据库对象的管理1. 数据库数据库实例名称：Student.2. 表创建Student数据库中的所有表。添加样本数据到所创建的表中。（1） 学生信息表create table scott.Student-学生信息表(x_id char(12) NOT NULL PRIMARY KEY,x_name varchar2(30) not null UNIQUE,x_sex char(2) check (x_sex='男' or x_sex='女'),x_cardid varchar2(18) not null,b_id char(50) not null REFERENCES scott.banji(b_id),jiguan varchar2(10) not null,xueji varchar2(10) not null,x_brith date not null,m_id char(6) not null);-插入三条记录insert into scott.STUDENT values('1','袁洁芳','女','','','湖南省','在籍',to_date('1982-05-18','yyyy-mm-dd'),'04');insert into scott.STUDENT values('2','王华宝','男','','','江苏省','在籍',to_date('1993-05-18','yyyy-mm-dd'),'03');insert into scott.STUDENT values('3','王浩','男','','','江苏省','在籍',to_date('1992-05-18','yyyy-mm-dd'),'02');commit;（2） 课程信息表create table scott.Class1-课程信息表(c_id char(6) NOT NULL PRIMARY KEY,c_name varchar2(30) UNIQUE not null,d_id char(5) not null REFERENCES scott.Discipline(d_id),xuefen char(2) not null,xueshi char(5) not null,c_style char(2) not null,bianhao char(5) not null);-插入三条记录insert into scott.Class1 values('03107','软件工程','0310','4','60','01','01');insert into scott.Class1 values('03106','软件工程1','0311','5','70','02','02')；insert into scott.Class1 values('03108','软件工程2','0312','6','80','03','03')；commit;（3） 专业信息表create table Discipline-专业信息表(d_id char(5) NOT NULL PRIMARY KEY,d_name varchar2(20) UNIQUE not null,d_fuzeren varchar(30) not null,phone varchar2(15) not null,xuezhi char(2) not null,s_id char(5) not null REFERENCES scott.Section(s_id),d_year char(10) not null);-插入三条记录insert into Scott.Discipline values('0310','软件技术','刘志成','','3','03','2003'); insert into Scott.Discipline values('0311','软件技术1','王浩','','4','01','2004'); insert into Scott.Discipline values('0312','软件技术2','王化宝','','2','02','2005');（4）部门信息表create table scott.Section -部门表(s_id char(5) NOT NULL PRIMARY KEY,s_name varchar2(20) not null UNIQUE,s_fuzeren varchar2(30) not null,phone varchar2(15) not null);-插入三条记录insert into Scott.SECTION VALUES('03','信息工程系','彭勇','');insert into Scott.SECTION VALUES('01','信息系','王化宝','');insert into Scott.SECTION VALUES('02','信息管理系','王浩','');commit;（5） 班级信息表create table Banji-班级信息表(b_id char(50) NOT NULL PRIMARY KEY,b_name varchar2(15) UNIQUE not null,s_id char(5) not null REFERENCES scott.Section(s_id),d_id char(5) not null REFERENCES scott.Discipline(d_id);-插入三条记录insert into Scott.BANJI VALUES('','软件051','03','0310');insert into Scott.BANJI VALUES('','软件111','01','0311');insert into Scott.BANJI VALUES('','软件112','02','0312');commit;（6）学生成绩表create table XueSheng1-学生成绩表(x_id char(12) NOT NULL REFERENCES scott.Student(x_id),c_id char(6) NOT NULL REFERENCES scott.Class1(c_id) ,zhengkao char(5),bukao char(5),chongxiu char(5);-插入三条记录insert into scott.XUESHENG1 values('1','03107','87','0','0');insert into scott.XUESHENG1 values('2','03106','88','1','1');insert into scott.XUESHENG1 values('3','03108','89','2','2');（7）管理员信息表create table Admins-管理员信息表(a_id char(5) NOT NULL PRIMARY KEY,c_name varchar2(30) UNIQUE,mima varchar2(15),a_style varchar2(30),a_date date not null);-插入三条记录insert into Scott.ADMINS values('1','admin','123','系统管理员',to_date('2007-02-10','yyyy-mm-dd');insert into Scott.ADMINS values('2','A类用户','123','超级用户',to_date('2007-02-10','yyyy-mm-dd');insert into Scott.ADMINS values('3','B类用户','123','超级用户',to_date('2007-02-10','yyyy-mm-dd');commit;3. 视图（1） 创建指定部门的专业信息的视图vw_Majorcreate view scott.vw_MajorasSELECT d_name,d_fuzeren,Discipline.phone,s_name,s_fuzeren,Section.phone,d_year FROM scott.Discipline ,scott.Section WHERE scott.Discipline.s_id=scott.Section.s_idwith read only; (2)创建学生成绩的视图vw_Scorecreate view scott.vw_ScoreasSELECT scott.student.x_id,student.x_name,class1.c_id,class1.c_name,xuesheng1.zhengkao FROM SCOTT.studentJOIN SCOTT.xuesheng1 ON xuesheng1.x_id=student.x_id JOIN SCOTT.class1 ON xuesheng1.c_id=class1.c_idwith read only;4.索引-创建"学生名称"为关键字的唯一索引-create index scott.is_studenton scott.student(s_name);-创建"课程名称"为关键字的唯一索引-create UNIQUE INDEX scott.is_classon scott.class1(c_name);-创建"专业名称"为关键字的唯一索引-create UNIQUE INDEX scott.is_disciplineon scott.Discipline(d_name);-创建"班级名称"为关键字的唯一索引-create UNIQUE INDEX scott.is_banjion scott.banji(b_name);5.存储过程（1）-创建根据指定的学号查询学生所有课程成绩信息的存储过程up_MyScore，并执行该存储过程查询学号为“1”的学生的成绩信息CREATE OR REPLACEPROCEDURE UP_MYSCORE( st IN VARCHAR2,kid out xuesheng1.c_id%type,x_zhengkao out xuesheng1.zhengkao%type,x_bukao out xuesheng1.bukao%type,x_chongxiu out xuesheng1.chongxiu%type) ASBEGINselect c_id,zhengkao,bukao,chongxiu into kid,x_zhengkao,x_bukao,x_chongxiufrom scott.xuesheng1where x_id=st;END UP_MYSCORE;（2）-创建根据指定的管理员信息实现添加管理员的储存过程up_AddAdmin，并执行该储存过程，将管理员添加到管理员列表中create or replace procedure up_AddAdmin(aid in char,aname in varchar2,mima1 in varchar2,aleixing in varchar2,ayear in date)asbegininsert into scott.admins values(aid,aname,mima1,aleixing,ayear);end up_AddAdmin;-将一条管理员记录插入到管理员表中exec up_AddAdmin('8','A用户','888','超级用户',to_date('2007-10-10','yyyy-mm-dd');select * from scott.admins（3）-创建统计每门课程总成绩和平均成绩的存储过程，并将课程总成绩和平均成绩以输出参数形式输出-create or replaceprocedure up_xuesheng(kid in char)astotal number;total2 number;beginselect sum(zhengkao ) into total from scott.xuesheng1 where c_id=kid;select avg(zhengkao ) into total2 from scott.xuesheng1 where c_id=kid;dbms_output.put_line(total);dbms_output.put_line(total2);end;exec up_xuesheng('03107');6.触发器（1） -创建在删除“学生信息表”中的学生信息时，删除“学生成绩表”中该学生信息的触发器tr_DeleteStudent，并设置删除语句验证该触发器工作-create or replace trigger scott.tr_DeleteStudentafter deleteon scott.studentfor each rowbegindelete from scott.xuesheng1 where x_id=:old.x_id;dbms_output.put_line('成功删除学生信息及其成绩信息');end tr_DeleteStudent;-删除语句delete from scott.xuesheng1where x_id='3'commit;（2）-创建在修改"部门信息表"中的部门标号时，修改"班级信息表"和"专业信息表"的触发器tr_UpdateDeptNo，并设置修改语句验证该触发器的工作create or replacetrigger scott.tr_UpdateDeptNoafter updateon scott.section for each row begin update banjiset s_id=:new.s_idwhere s_id=:old.s_id;update disciplineset s_id=:new.s_idwhere s_id=:old.s_id;end tr_UpdateDeptNo;（二） 数据库安全策略（1）-创建用户MyLogin，并将该用户添加到CONNECT角色中create user MyLoginidentified by student;grant connect to MyLogin;（2） -查看用户MyLogin的基本信息-select * from all_userswhere username = 'MyLogin'（3） -创建数据库角色MyRole-create role MyRoleidentified by student;（4） -为角色MyRole添加系统权限-grant create session to MyRole;（5） -为角色MyRole添加Student数据库中表的相关权限-grant select on scott.STUDENT to MyRole;（查询权限）grant insert on scott.STUDENT to MyRole；（插入权限）grant update on scott.STUDENT to MyRole;（更新权限）grant delete on scott.STUDENT to MyRole;（删除权限）（6） -查看角色MyRole的基本信息-select * from session_roleswhere role = 'MyRole'（三） 数据查询（1） -查询学生信息表中的所有数据-select * from scott.STUDENT;(2)-查询部门编号“03”，专业负责人为“刘志成”的专业信息并显示汉字标题-select d_id 专业编号,d_name 专业名称,d_fuzeren 专业负责人,discipline.phone 联系电话,xuezhi 学制,d_year 开设年份,s_id 部门编号from scott.disciplinewhere d_fuzeren='刘志成'and s_id='07'(3)-查询所有年龄在20岁以下的学生的名称、籍贯和年龄-select x_name 姓名,jiguan 籍贯,to_char(sysdate,'yyyy')-to_char(x_brith,'yyyy') nlfrom scott.STUDENTwhere to_char(sysdate,'yyyy')-to_char(x_brith,'yyyy')<20;(4)-查询学生名字中包含“芳”的学生信息的详细信息，并要求按年龄升序排列-select * from scott.STUDENTwhere x_name like '%芳%'order by x_brith desc;(5) -查询所有“软件技术”专业的专业编号、专业名称、所属部门名称和部门负责任-SELECT discipline.d_NAME,discipline.d_fuzeren,section.s_name,section.s_fuzerenFROM scott.disciplinejoin scott.sectionon scott.discipline.s_ID=scott.section.s_IDwhere discipline.d_name='软件技术'（6）-查询每一门课程的平均成绩，并根据平均成绩进行降序排列-select avg(zhengkao)平均成绩from scott.xuesheng1group by c_idorder by avg(zhengkao) desc;(7) -查询学生“苑俊芳”的所有课程的成绩信息-select xuesheng1.x_id 学号,c_id 课程编号,zhengkao 正考成绩,bukao 补考成绩,chongxiu 重修成绩from scott.xuesheng1,scott.STUDENTwhere student.x_ID=xuesheng1.x_IDand student.x_name='袁洁芳'（8）-查询不比“范俊芳”小的学生的详细信息-select *from scott.STUDENTwhere to_char(sysdate,'yyyy')-to_char(x_BRITH,'yyyy')>=(select to_char(sysdate,'yyyy')-to_char(x_BRITH,'yyyy')from scott.STUDENTwhere student.x_NAME='袁洁芳');（9）-查询每个班级男女学生的平均年龄，并将结果保存到“t_Age”表中-create table t_age(x_name varchar2(30),b_avg float);insert into t_age(x_name,b_avg)select x_name,avg(to_char(sysdate,'yyyy')-to_char(x_BRITH,'yyyy')from scott.STUDENT group by x_name;（10）-查询年龄到20岁以上以及班级编号为“”的学生信息（使用联合查询）-select x_id,x_name,x_sex,x_cardid,b_id,jiguan,xueji,to_char(sysdate,'yyyy')-to_char(x_BRITH,'yyyy') nl,m_idfrom scott.STUDENTwhere to_char(sysdate,'yyyy')-to_char(x_BRITH,'yyyy')>=19unionselect x_id,x_name,x_sex,x_cardid,b_id,jiguan,xueji,to_char(sysdate,'yyyy')-to_char(x_BRITH,'yyyy') nl,m_idfrom scott.STUDENTwhere b_id=''（四） 数据管理（1）将Student数据库中的“学生信息表”的信息导出到stu.dmp文件中（2）删除Student数据库中的“学生信息表”，并通过stu.dmp文件导入“学生信息表”的信息（五） 数据库程序开发（1） 编写根据输入学生的学号查询学生信息的Win Form程序。（2） 编写显示所有课程信息的JAVA程序。import java.sql.*;public class ebuy public static void main(String args)String strQuery="SELECT c_ID,c_Name,d_ID,xuefen,xueshi,c_style,bianhao FROM SCOTT.CLASS1"Connection conn;Statement stat;ResultSet rs;String cid,zid,cname,cxuefen,czks,ctype,csk;try/注册驱动程序Class.forName("oracle.jdbc.driver.OracleDriver");/获得和Oracle数据库的连接conn=DriverManager.getConnection("jdbc:oracle:thin:localhost:1521:ORCL", "SCOTT", "");/获得和Oracle数据库的连接stat=conn.createStatement();/向Oracle数据库发送SQL请求 rs=stat.executeQuery(strQuery);/操作结果集对象while(rs.next()cid=rs.getString(1);zid=rs.getString(2);cname=rs.getString(3);cxuefen=rs.getString(4);czks=rs.getString(5);ctype=rs.getString(6);csk=rs.getString(7);System.out.println(cid+","+zid+","+cname+","+cxuefen+","+czks+","+ctype+","+csk);/关闭相关对象rs.close();stat.close();conn.close();catch(Exception err)err.printStackTrace();(六) 总结