南审2017orcal期末实验参考答案
Oracle数据库基础
实验一
表空间和用户权限
专业：
班级：
姓名：
学号：
实验内容
一、修改Oracle配置文件并重启服务
Tnsnames.ora, listener.ora
二、创建表空间（将每一步的脚本和将执行结果截图，并黏贴到文
件中）
1.在D盘创建data目录
2.使用系统管理员登陆数据库。创建数据表空间tbs_学号（如
tbs_14010101），数据文件存储在d:\data\，文件名称:data_学
号.dbf，文件大小50m，可以自动扩展空间，每次扩展10m，最大100m。
Create tablespace tbs_001 datafile ‘d:\data\data_01.dbf’size
50m autoextend on next 10m maxsize 100m
三、用系统管理员创建用户（将每一步的脚本和将执行结果截图，
并黏贴到文件中）
1、创建用户
用户名：usr_学号(如usr_14010101)
密码：admin
默认表空间在第二步所创建的表空间上(tbs_学号,如tbs_14010101)。
Create user usr_001 identified by admin default tablespace tbs_001;
2、赋予权限:
将connect,resource 角色赋予该用户。
Grant connect,resource to usr_001;
四、使用第三步创建的用户登录到数据库中（将执行结果截图，并
黏贴到文件中）
1、登录用户
Conn usr_001/admin
2、使用create table 命令，创建表t_test，字段只用一个col1,
数据类型int.
Create table t_test( col1 int);
3、向表t_test中写入数据1，2，3，4，5，6
Insert into t_test(col1) values(1);
Insert into t_test(col1) values(2);
Insert into t_test(col1) values(3);
Insert into t_test(col1) values(4);
4、查询t_test表,显示结果
Select * from t_test;
5、查询系统视图dba_users，查看结果并截图。
Select * from dba_users;(会提示表或视图不存在，原因是没有访问权限)
五、对usr_学号(如usr_14010101)进行对象授权(将每一步的脚本
和执行结果截图，并黏贴到文件中)
1.使用sys用户登录数据库
Conn sys/admin as sysdba
2.对usr_学号赋予查询（select）dba_users表的对象权限。
Grant select on dba_users to usr_001;
3.再次用usr_学号登录，查询dba_users，查看结果并截图
Select * from dba_users;
Oracle数据库基础（实验二）
表、约束管理、数据管理
专业：班级：姓名：学号：
实验内容
六、修改Oracle配置文件并重启服务
Tnsnames.ora, listener.ora
七、用系统管理员创建用户（将每一步的脚本和将执行结果截图，
并黏贴到文件中）
1、创建用户
用户名：usr_学号(如usr_14010101)
密码：admin
Create user usr_001 identified by admin;
2、赋予权限:
将connect,resource 角色赋予该用户。
Grant connect,resource to usr_001;
八、在所创建的用户(usr_学号)下创建表：
1.表：department
Create table department
(
Deptid char(6) constraint pk_department primary key,
Deptname varchar2(40) not null unique,
Address varchar2(60),
Contacts varchar2(30),
Phone varchar2(20) check(length(phone) between 8 and 11), Memo varchar2(2000)
);
2.表：employee
Create table employee
(
Employeeid char(6),
Name varchar2(30),
Sexid char(1) check(sexid in (0,1)),
Phone varchar2(20),
Birthday varchar2(8),
Deptid char(6)
);
九、数据管理：（进行数据管理的时候，不要忘记提交commit）
1.录入数据：
向表department中录入以下数据：
向表employee表中录入以下数据：
2.修改数据：
修改部门编号为“100002”的部门地址为“广州”
Update department set address='广州'where deptid='100002';
Commit;
3.查询数据：
（1）、查询性别为女（sexid=1）的员工信息。
Select * from employee where sexed=1;
（2）、统计各个部门的员工数量。
要求输出：部门编号，部门名称，人数
Select a.deptid,a.deptname,count(*)
from department a ,employee b
Where a.deptid=b.deptid
Group by a.deptid,a.deptname;
（3）、统计各个部门的员工按性别统计的人数
要求输出：部门编号，部门名称，性别，人数
Select a.deptid,a.deptname,sexid,count(*)
from department a ,employee b
Where a.deptid=b.deptid
Group by a.deptid,a.deptname,sexid;
（4）、查询部门编号在100001，100002的员工信息（使用in关键字）
Select*from employee where deptid in('100002','100001');
（5）、查询员工姓名为“用”开头的员工信息（使用like 关键字）
Select*from employee wherenamelike'用%';
4.用查询创建表：
使用create table … as …命令创建备份表：department_bak，要
求将数据也要一并创建。
Create table department_bak as select * from department;
使用create table … as …命令创建备份表：employee_bak，要求只创建表结构。
Create table employee_bak as select * from employee where 1=2;
十、约束管理：
1.修改表department
i.增加字段crtdate（创建日期）数据类型：char（8），并修
改department的数据，将所有记录的crtdate修改为当前
日期（20170418）。
Alter table department add crdate char(8);
ii.修改字段address，将其长度修改为80。
Alter table department modify address varchar2(80);
iii.重命名字段deptname，修改为dname。
Alter table department rename column deptname to dname;
2.修改表employee
i.增加主键约束：将字段employeeid作为主键，主键名称为
pk_employee。
Alter table employee add constraint pk_employee primary key(employeeid);
ii.增加外键约束：将deptid作为外键，并参照department
表中的deptid。
Alter table employee add constraint fk_dept foreign key(deptid)
references department(deptid);
十一、数据字典管理：
i.查看用户表的数据字典user_tables
Select * from user_Tables;
ii.查看约束的数据字典user_constraints
Select * from user_constraints;
iii.查询定义了约束的列user_cons_columns
Select * from user_cons_ columns;
十二、外键约束的补充说明
1.外键有三种级联方式：
在定义和添加FOREIGN KEY约束时，也能够通过ON关键字指定引用行为的类型。当父表中的一条记录被删除时，需要通过引用行为来确定如何处理子表中的外键列。
ON DELETE CASCADE:级联删除（当删除父表的数据时，同步删除子表相关的数据）。
ON DELETE SET NULL:当删除父表的数据时，将子表相关记录的外键列的值设为NULL。
ON DELETE NO ACTION: 当删除父表的数据时，如果对应的子表存在关联数据时，不允许删除，必须先将子表的关联数据删除或修改为非关联数据才能删除主表数据。
举例：
ON DELETE SET NULL 级联
Alter Table emp2 Add Constraint new_fk_emp2 Foreign Key (deptno) References dept2(deptno) On Delete Set Null;
ON DELETE CASCADE 级联
Alter Table emp1 Add Constraint new_fk_emp1 Foreign Key (deptno) References dept1(deptno) On Delete cascade;
ON DELETE NO ACTION 级联
Alter Table emp3 Add Constraint new_fk_emp3 Foreign Key (deptno) References dept3(deptno) On Delete NO ACTION; Oracle数据库基础
（实验三）
视图、索引、序列
专业：
班级：姓名：学号：
实验内容
十三、修改Oracle配置文件并重启服务
Tnsnames.ora, listener.ora
十四、创建表空间
用系统管理员登录，在d:\data目录下创建表空间tbs_scott,文件名：tbs_data01.dbf，大小100m，自动增长，每次增长10m。
Create tablespace tbs_scott datafile ‘d:\data\tbs_data01.dbf’ size 100m autoextend on next 10m;
十五、用系统管理员登录，修改scott用户的账户状态为unlock;
参考SQL：
Alter user scott identified by tiger account unlock;
scott下几个表的数据字典
1、部门表：dept
2、雇员表：emp
3、工资等级表：salgrade
4、工资表：bonus
注意：以下对象的创建和管理都要在scott用户下进行。十六、创建视图：赋予：create view
1.创建视图v_emp
1.1视图要求：
视图的内容为输出empno,ename,deptno,sal。
先用系统管理员对scott授予create view 权限：
Grant create view to scott;
用scott用户登录：
注意：连接选项要用normal
createview v_Emp asselect empno,ename,deptno,sal from emp;
1.2 使用desc命令查看视图v_emp的结构
Desc v_emp;
1.3 使用insert命令向该视图中插入一条记录，看看效果。
数据内容为：
Insert into v_emp values(7777,'张三',10,3000);
2.创建视图v_empsal
2.1视图要求：
视图的内容为输出empno,ename,dname（部门名称）,ann_salary （年薪，算法：(sal+comm)*12）。
createview v_empsal as
select empno,ename,dname,(sal+comm)*12as ann_salary
from emp,dept
where emp.deptno=dept.deptno;
2.2 使用desc命令查看视图v_empsal的结构
Desc v_empsal;
2.3 使用insert命令向该视图中插入一条记录，看看效果。
Insert into v_empsal values(8888,'张三',10,3000);
3.创建实体视图mv_emp 赋予: CREATE MATERIALIZED VIEW
先用系统管理员对scott授予CREATE MATERIALIZED VIEW权限：
Grant CREATE MATERIALIZED VIEW to scott;
3.1视图要求：
视图参考脚本如下：
CREATE MATERIALIZED VIEW MV_EMP
REFRESH FORCE
ON DEMAND
AS
SELECT e.empno, e.eName, dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;
3.2 查询视图mv_emp
Select * from mv_emp;
3.3 向emp表添加记录，然后再次查询mv_emp，看看结果
3.3 手工刷新视图MV_EMP，再次查询，查看结果
提示：使用dbms_mview.refresh方法进行刷新。
exec dbms_mview.refresh('mv_Emp')
十七、创建索引：
1、为部门表（dept）的dname字段创建唯一索引，索引名称为
idx_dname。并将该索引存储到表空间tbs_scott中。
Create unique index idx_dname on dept(dname) tablespace tbs_scott;
2、为员工表（emp）的job字段创建位图索引，索引名称为idx_job。
并将该索引存储到表空间tbs_scott中。
Create bitmap index idx_job on emp(job) tablespace tbs_scott;
3、为员工表（emp）的创建函数索引，索引名称为idx_totalsal，
索引列为：年薪（计算公式为:(sal+comm)*12）。并将该索引存储到表空间tbs_scott中。
Create index idx_totalsal on emp((sal+comm)*12) tablespace tbs_scott;
十八、创建序列：
1、创建序列seq_num
要求：序列名称为：seq_num
起始值:1，没有最大值，每次增长1。
Create sequence seq_num
Start with 1
Nomaxvalue
Increment by 1;
2、初始化序列：currval
提示：
select seq_num.nextval from dual;
3、执行seq_num.nextval三次，查看输出。
select seq_num.nextval from dual;
4、执行seq_num.currval三次，查看输出。
select seq_num.currval from dual;
5、使用insert命令向该emp中插入三条记录，看看效果。
数据内容为：
注意：员工编号使用序列seq_num来自动生成（提示使用序列的nextval方法生成编号）。
Insertinto emp(empno,ename,deptno,sal)values(seq_num.nextval,'test1',10,3000);
Insertinto emp(empno,ename,deptno,sal)values(seq_num.nextval,'test2',20,2000);
Insertinto emp(empno,ename,deptno,sal)values(seq_num.nextval,'test3',30,1000);
6、手工刷新视图MV_EMP，并查看结果。
提示：使用dbms_mview.refresh方法进行刷新。
exec dbms_mview.refresh('mv_Emp')
索引、视图、序列、实体化视图在plsqldeveloper的位置。