Oracle数据库对象小测试总结复习题【表空间、建表、约束、权限、用户、角色、事务】（附带Oracle思维导图）

1.创建以自己姓名命名的表空间，创建数据表customer，结构如下

cust_id number(4),
cname varchar2(10),
sex char(2),
age number(2),
birthday date,
account number(5,2)

将customer表放到自己名字的表空间中。

建立以下约束：

（一）客户编码为主键

（二）姓名不能为空

（三）性别默认为"男"

（四）建立检查约束，要求男性年龄在18到60岁之间，女性年龄在18到55岁之间（要求为表级约束，必须有约束名称）

1、答案：


创建表空间：create tablespace 自己姓名 datafile ‘地址’ size 10m;

创建表空间：create tablespace 张三 datafile ‘d:\zhangsan.def’ size 10M;

创建表：
create table customer(cust_id number(4),cname varchar2(20),sex char(2),age number(2),
birthday date,account number(5,2)) tablespace 张三;

客户编码为主键
alter table customer add constraint cust_id_pk primary key(cust_id);

姓名不能为空
alter table customer modify cname not null;

性别默认为"男"
alter table customer modify sex default ‘男’;

建立检查约束，要求男性年龄在18到60岁之间，女性年龄在18到55岁之间（要求为表级约束，必须有约束名称）
alter table customer add constraint check_pk check((sex=‘男’ and age between 18 and 60) or
(sex=‘女’ and age between 18 and 55));

---

2.在一题的表中增加一列 dno number,并在dno字段上创建唯一性约束

2、答案：


方法一：
alter table customer add dno number;
alter table customer add constraint un_dno unique(dno);

方法二：
alter table customer add dno number unique;

---

3.创建序列stu_seq,增长量是2，最小值是1.最大值是100，不循环，设置缓存为30.然后在customer表中插入三条记录，使用该序列生成主键值。

3、答案：


create sequence stu_seq increment by 2 minvalue 1 maxvalue 100 nocycle cache 30;

insert into customer values(stu_seq.nextval,‘李四’,‘男’,18,to_date(‘1999-09-09’,‘yyyy-mm-dd’),200);
insert into customer values(stu_seq.nextval,‘刘五’,‘男’,23,to_date(‘2019-03-02’,‘yyyy-mm-dd’),300);
insert into customer values(stu_seq.nextval,‘王六’,‘男’,43,to_date(‘1978-11-04’,‘yyyy-mm-dd’),500)

---

4.针对scott用户的emp表和dept表做以下练习

（一）创建一个视图v_emp_salesman,内容是所有职位是SALESMAN的员工
（二）需要经常在emp表的ename列上执行小写名字搜索，在此列上建立一个基于函数的索引。
（三）在emp表的job和sal列上建立一个组合索引
（四）查看scott用户下的表信息
（五）禁用dept表中pk_dno约束
（六）为dept表定义同义词id
（七）修改（三）中的索引名，为edjs。

4、答案：


(一)：
create view v_emp_salesman as select * from c##scott.emp where job=‘SALESMAN’;

(二)：
create index ename_index on c##scott.emp(lower(ename));

(三)：
create index emp_index on c##scott.emp(job,sal);

(四)：
select * from all_tables where owner=‘SCOTT’;

(五)：
alter table c##scott.dept disable constraint pk_dno;

(六)：
create synonym d for c##scott.dept;

(七)：
alter index emp_index rename to edjs;

---

5.已知学生表student，该表两个字段学号（id）、姓名（name）,其中id列为主键列。有课程表course，该表有两个字段编号(id)、课程名称(name)，其中id列为主键列。有选课表sc，该表有三个字段学号（sid）、课程编号（cid）、成绩（score）。要求建立下列外键关联关系：

1).sc表的学号列（sid），外键关联学生表（student）的学号列(id).

2).sc表的课程编号列（cid），外键关联课程表(course)的编号列(id).

请写出建立上述关联关系的SQL语句。

5、答案：


create table student(id number(10) primary key,name varchar2(10));
create table course(id number(10) primary key,name varchar2(10));
create table sc(sid number(10),cid number(10),score number(10));

alter table sc add constraint foreign_pk foreign key(sid) references student(id);
alter table sc add constraint foreign_pk2 foreign key(cid) references course(id);

---

6.对scott方案下的表dept，完成以下内容:

(1)使用system身份连接数据库。创建用户test_user,其口令为oracle

(2)向用户test_user授予连接数据库的系统权限

(3)向用户test_user授予对象scott.dept的select权限

(4)向用户test_user授予对象scott.dept的insert、delete权限，对loc字段有更新权力

(5)用户test_user具有对dept表的所有权利，并具有给其他用户授权的权力

(6)撤销用test_user对dept表的所有权限

(7)用户test_user只有查看10号部门的权力，不能查看其他部门信息

(8)建立角色role1，具有连接数据库、创建表的权力

(9)将role1角色的权力授予给用户test_user

(10)删除角色role1

6、答案：


–1：
create user c##test_user identified by oracle ;
–2：
grant create session to c##test_user;
–3：
grant select on c##scott.dept to c##test_user;
–4：
grant insert,delete,update(loc) on c##scott.dept to c##test_user;
–5：
grant all on c##scott.dept to c##test_user with grant option;
–6：
revoke all on c##scott.dept from c##test_user;
–7：
create view c##scott.view3 as select * from c##scott.dept where deptno=10;
grant select on c##scott.view3 to c##test_user;
–8：
create role c##role1;
grant create session,create table to c##role1;
–9：
grant c##role1 to c##test_user;
–10：
drop role c##role1;

---

7.完成以下操作：

(1) 复制表dept，生成新表dept1

(2) 删除dept1中部门编号为10的记录，并提交事务

(3) 将dept1表中部门编号为20的记录地址更改为BEIJING。然后回滚该事务以取消对dept1表的修改。

(4)在事务中建立一个保存点，并使用回滚语句回滚到保存点。事务操作可自定。最后要有验证语句。

7、答案：

–1
create table dept1 as select * from c##scott.dept;
–2
delete from dept1 where deptno=10;
commit;
–3
update dept1 set loc=‘BEIJING’ where deptno=20;
rollback;

–4
savepoint abc;
insert into dept1 values(50,‘abc’,‘abc’);
select * from dept1;
rollback to abc;
select * from dept1;

---

8、思维导图