2020-05-01 实验3.doc : Oracle数据库实验实战记录

实验二：表的管理（设计型，2课时）

一、实验目的与要求

利用SQL语句创建表；
掌握定义表的约束、添加、删除和激活约束；
掌握修改表的各种操作。
二、实验环境
计算机软件环境：Windows XP操作系统, oracle 10g
三、实验预习与准备
掌握CREATE TABLE语句（2种）创建表；
利用SQL语句对约束进行各种操作；
利用SQL语句进行各种操作。
四、实验内容和步骤

教材154页-实训题1~10题
每个步骤都需要用相关的数据字典查询其状态.
使用sppol 来存储执行的所有操作

五、实验报告要求
实验序号班级学号。doc （2_CS2_20103001.doc 包括spool的结果）
报告书提交到11 平台上.
截止日期：周日晚12点为止

六、提示
1． CREATE TABLE <表名>
(<列名><数据类型>[列级完整性约束条件]
[，<列名> <数据类型>[列级完整性约束条件]]…
[，<表级完整性约束条件>])
[参数设置];
2．CREATE TABLE <表名>
(<列名>[列级完整性约束条件]
[，<列名> [列级完整性约束条件]]…
[，<表级完整性约束条件>])
[参数设置]
AS 子查询

定义约束
（1）列级约束语法格式：
[CONSTRAINT constraint_name ]constraint_type [condition]
（2）表级约束语法格式：
[CONSTRAINT constraint_name ]constraint_type [column1_name,column2_name,…]|[condition]
4．表约束的修改
ALTER TABLE <table_name>
ADD <constraint>
MODIFY <constraint >
ENABLE <constraint >
DISABLE <constraint >
DROP <constraint >
5．字段的添加、删除、修改
ALTER TABLE <表名>
ADD <新列名><数据类型>[ <完整性约束定义]
MODIFY <列名><数据类型>
RENAME COLUMN oldname TO newname
SET UNUSED COLUMN column //single column
SET UNUSED COLUMNS（column1，column2…）
DROP COLUMN <col> //single column
DROP < col1，col2… > //multi column
DROP UNUSED COLUMNS
UPDATE <表名>
DELETE FROM <表名> WHERE <CONDITION>

INDEX_TYPE UNIQUENES

STUDENT SYS_C005435
NORMAL UNIQUE

STUDENT SYS_C005436
NORMAL UNIQUE

CLASS SYS_C005434
NORMAL UNIQUE

TABLE_NAME INDEX_NAME

INDEX_TYPE UNIQUENES

CLASS CLASS_CNAME_INDEX
NORMAL UNIQUE

SQL> create table student_range as select * from student;

表已创建。

SQL> select table_name from user_tables;

TABLE_NAME

CLASS
STUDENT
STUDENT_RANGE
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1

已选择6行。

SQL> alter table student_range partition by range(sage) (partition p1 values les
s than (20) tablespace example,partition p2 values between (20) and (30) tablesp
ace orcltbs1,partition p3 values less than (maxvalue) tablespace orcltbs2);
alter table student_range partition by range(sage) (partition p1 values less tha
n (20) tablespace example,partition p2 values between (20) and (30) tablespace o
rcltbs1,partition p3 values less than (maxvalue) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项

SQL> alter table student_range partition by range(sage) (partition p1 values les
s than (20) tablespace example,partition p2 values between (20) and (30) tablesp
ace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
alter table student_range partition by range(sage) (partition p1 values less tha
n (20) tablespace example,partition p2 values between (20) and (30) tablespace o
rcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项

SQL> alter table student_range partition by range(sage) (partition p1 values les
s than (20) tablespace example,partition p2 values between (20) and (30) tablesp
ace orcltbs1);
alter table student_range partition by range(sage) (partition p1 values less tha
n (20) tablespace example,partition p2 values between (20) and (30) tablespace o
rcltbs1)
*
第 1 行出现错误:
ORA-01735: 无效的 ALTER TABLE 选项

SQL> CREATE table student_range partition by range(sage) (partition p1 values le
ss than (20) tablespace example,partition p2 values between (20) and (30) tables
pace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
CREATE table student_range partition by range(sage) (partition p1 values less th
an (20) tablespace example,partition p2 values between (20) and (30) tablespace
orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)

第 1 行出现错误:
ORA-14007: 缺失 LESS 关键字

SQL> CREATE table student_range partition by range(sage) (partition p1 values le
ss than (20) tablespace example,partition p2 values LESS THAN (30) tablespace or
cltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
CREATE table student_range partition by range(sage) (partition p1 values less th
an (20) tablespace example,partition p2 values LESS THAN (30) tablespace orcltbs
1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

SQL> CREATE table student_range partition by range(sage)(partition p1 values les
s than (20) tablespace example,partition p2 values LESS THAN (30) tablespace orc
ltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
CREATE table student_range partition by range(sage)(partition p1 values less tha
n (20) tablespace example,partition p2 values LESS THAN (30) tablespace orcltbs1
,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

SQL> CREATE table student_range2 AS SELECT * FROM student partition by range(sag
e)(partition p1 values less than (20) tablespace example,partition p2 values LES
S THAN (30) tablespace orcltbs1,partition p3 values less than (MAXVALUE) tablesp
ace orcltbs2);
CREATE table student_range2 AS SELECT * FROM student partition by range(sage)(pa
rtition p1 values less than (20) tablespace example,partition p2 values LESS THA
N (30) tablespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace o
rcltbs2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

SQL> CREATE table student_range partition by range(sage)(partition p1 values les
s than (20) tablespace example,partition p2 values LESS THAN (30) tablespace orc
ltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
CREATE table student_range partition by range(sage)(partition p1 values less tha
n (20) tablespace example,partition p2 values LESS THAN (30) tablespace orcltbs1
,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

SQL> CREATE table student_range partition by range(sage) (partition p1 values l
ess than (20) tablespace example,partition p2 values LESS THAN (30) tablespace o
rcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
CREATE table student_range partition by range(sage) (partition p1 values less t
han (20) tablespace example,partition p2 values LESS THAN (30) tablespace orcltb
s1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

SQL> edit
已写入 file afiedt.buf

1* alter table student_range partition by range(sage) (partition p1 values le
ss than (20) tablespace example,partition p2 values LESS THAN (30) tablespace or
cltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
SQL> CREATE table student_range partition by range(sage) (partition p1 values l
ess than (20) tablespace example,partition p2 values LESS THAN (30) tablespace o
rcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2);
CREATE table student_range partition by range(sage) (partition p1 values less t
han (20) tablespace example,partition p2 values LESS THAN (30) tablespace orcltb
s1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
*
第 1 行出现错误:
ORA-00906: 缺失左括号

SQL> edit
已写入 file afiedt.buf

1* CREATE table student_range2(sno number(4) primary key,sname varchar2(10) un
ique,sage number,sex char(2),cno number(2)) partition by range(sage)(partition p
1 values less than (20) tablespace example,partition p2 values LESS THAN (30) ta
blespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
SQL>
SQL> CREATE table student_range2(sno number(4) primary key,sname varchar2(10) un
ique,sage number,sex char(2),cno number(2)) partition by range(sage)(partition p
1 values less than (20) tablespace example,partition p2 values LESS THAN (30) ta
blespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
2 /
CREATE table student_range2(sno number(4) primary key,sname varchar2(10) unique,
sage number,sex char(2),cno number(2)) partition by range(sage)(partition p1 val
ues less than (20) tablespace example,partition p2 values LESS THAN (30) tablesp
ace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)

第 1 行出现错误:
ORA-00959: 表空间 'ORCLTBS1' 不存在

SQL> create tablespace orcltbs1 datafile 'D:\oracle\product\10.2.0\oradata\orcl
orcltbsd1_1.dbf'size 50M;

表空间已创建。

SQL> create tablespace orcltbs2 datafile 'D:\oracle\product\10.2.0\oradata\orcl
orcltbsd2_1.dbf'size 50M;

表空间已创建。

SQL> CREATE table student_range2(sno number(4) primary key,sname varchar2(10) un
ique,sage number,sex char(2),cno number(2)) partition by range(sage)(partition p
1 values less than (20) tablespace example,partition p2 values LESS THAN (30) ta
blespace orcltbs1,partition p3 values less than (MAXVALUE) tablespace orcltbs2)
2 /

表已创建。

SQL> select table_name from user_tables;

TABLE_NAME

CLASS
STUDENT
STUDENT_RANGE
STUDENT_RANGE2
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1

已选择7行。

SQL> desc student_range2;
名称是否为空? 类型

SNO NOT NULL NUMBER(4)
SNAME VARCHAR2(10)
SAGE NUMBER
SEX CHAR(2)
CNO NUMBER(2)

SQL> CREATE table student_list(sno number(4) primary key,sname varchar2(10) uniq
ue,sage number,sex char(2),cno number(2)) partition by list(sex)(partition p1sM
tablespace orcltbs1,partition p2sF tablespace orcltbs2);
CREATE table student_list(sno number(4) primary key,sname varchar2(10) unique,sa
ge number,sex char(2),cno number(2)) partition by list(sex)(partition p1sM tabl
espace orcltbs1,partition p2sF tablespace orcltbs2)

第 1 行出现错误:
ORA-00926: 缺失 VALUES 关键字

SQL> CREATE table student_list(sno number(4) primary key,sname varchar2(10) uniq
ue,sage number,sex char(2),cno number(2)) partition by list(sex)(partition p1sM
values('M') tablespace orcltbs1,partition p2sF values('F') tablespace orcltbs2);

表已创建。

SQL> select table_name from user_tables;

TABLE_NAME

CLASS
STUDENT
STUDENT_RANGE
STUDENT_LIST
STUDENT_RANGE2
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1

已选择8行。

SQL> create sequence wy0411 increment by 2 start with 10000 maxvalue 100000 nocy
cle;

序列已创建。

SQL> select table_name from user_tables;

TABLE_NAME

CLASS
STUDENT
STUDENT_RANGE
STUDENT_LIST
STUDENT_RANGE2
STUDENT_TMP3
STUDENT_TMP2
STUDENT_TMP1

已选择8行。

SQL> spool off;
当前未假脱机
SQL> save c:a.txt
已创建 file c:\a.txt
SQL> list
1* select table_name from user_tables
SQL>