实验4：表的创建及修改
实验人： 学号 班级：计算机
实验目的：
1. 掌握建表的SQL语法。
2. 掌握修改表的SQL语法。
实验平台：
1. Oracle 11g/12c
实验过程记录及分析：
1. 以scott帐号登陆数据库，创建表test，包含三个字段：a－整数类型，b－最长10个字符的可变长度字符串，c－日期型，然后给此表添加一条记录，注意to_date()函数的用法。
SQL> create table test
2 (
1 / 28
3 a int,
4 b varchar2(10),
5 c date
6 )
7 /
表已创建。
SQL> insert into test values
2 (1,'MIKE',to_char(to_date('14/10/23','yy/mm/dd')));
已创建 1 行。
SQL> select * from test;
A B C
---------- ---------- --------------
1 MIKE 23-10月-14
2. 删除上面的test表，重新创建test，依然包含上面三个字段，并且建表时字段a附加主键约束，b附加unique约束，c附加非空约束，用列级指定名称来实现。表建好后，验证每个约束的作用。
删除表并创建
SQL> drop table test
2 /
表已删除。
SQL> ed
已写入 file afiedt.buf
1 create table test
2 (
3 a int constraint pk_test primary key,
3 / 28
4 b varchar2(10) constraint uq_test unique,
5 c date constraint nn_test not null
6* )
SQL> /
表已创建。
验证主键约束唯一性
SQL> insert into test values
2 (1,'MIKE',to_char(to_date('14/10/23','yy/mm/dd')));
已创建 1 行。
SQL> insert into test values
2 (1,'MIKE',to_char(to_date('14/10/23','yy/mm/dd')));
insert into test values
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.PK_TEST)
验证主键非空约束
SQL> insert into test values
2 (null,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')));
(null,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 (\"C##SCOTT\".\"TEST\".\"A\")
验证唯一约束
SQL> ed
5 / 28
已写入 file afiedt.buf
1 insert into test values
2* (2,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
SQL> /
insert into test values
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.PK_TEST)
验证非空约束
SQL> insert into test values
2 (3,'Mike',to_char(null));
(3,'Mike',to_char(null))
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 (\"C##SCOTT\".\"TEST\".\"C\")
3. 不指定名称，用列级约束来实现第二题内容
SQL> create table test
2 (
3 a int primary key,
4 b varchar2(10) unique,
5 c date not null
6 )
7 /
表已创建。
7 / 28
验证主键约束唯一性
SQL> insert into test values
2 (1,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')));
已创建 1 行。
SQL> insert into test values
2 (1,'ldf',to_char(to_date('2014-10-23','yyyy-mm-dd')));
insert into test values
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.SYS_C009891)
验证主键约束非空
SQL> insert into test values
2 (null,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')));
(null,'Mike',to_char(to_date('2014-10-23,'yyyy-mm-dd')))
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 (\"C##SCOTT\".\"TEST\".\"A\")
验证唯一约束
SQL> insert into test values
2 (2,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')));
insert into test values
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.SYS_C009892)
9 / 28
验证非空约束
SQL> insert into test values
2 (3,'Mike',to_char(null));
(3,'Mike',to_char(null))
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 (\"C##SCOTT\".\"TEST\".\"C\")
4. 用表级不指定名称来实现第二题要求。
SQL> create table test
2 (
3 a int,
4 b varchar2(10),
5 c date,
6 primary key(a),
7 unique(b)
8 )
9 /
表已创建。
验证主键约束唯一性
SQL> insert into test values
2 (1,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')));
已创建 1 行。
SQL> ed
已写入 file afiedt.buf
11 / 28
1 insert into test values
2* (1,'ldf',to_char(to_date('2014-10-23','yyyy-mm-dd')))
SQL> /
insert into test values
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.SYS_C009893)
验证主键约束非空性
SQL> ed
已写入 file afiedt.buf
1 insert into test values
2* (null,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
SQL> /
(null,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 (\"C##SCOTT\".\"TEST\".\"A\")
验证唯一约束
SQL> ed
已写入 file afiedt.buf
1 insert into test values
2* (2,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
SQL> /
insert into test values
13 / 28
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.SYS_C009894)
5. 用表级指定名称来实现第二题要求。
SQL> ed
已写入 file afiedt.buf
1 create table test
2 (
3 a int,
4 b varchar2(10),
5 c date,
6 constraint pk_test primary key(a),
7 constraint uq_test unique(b)
8* )
SQL> /
表已创建。
验证主键约束唯一性
SQL> insert into test values
2 (1,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')));
已创建 1 行。
SQL> insert into test values
2 (1,'ldf',to_char(to_date('2014-10-23','yyyy-mm-dd')));
insert into test values
*
15 / 28
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.PK_TEST)
验证主键约束非空
SQL> ed
已写入 file afiedt.buf
1 insert into test values
2* (null,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
SQL> /
(null,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
*
第 2 行出现错误:
ORA-01400: 无法将 NULL 插入 (\"C##SCOTT\".\"TEST\".\"A\")
验证唯一约束
SQL> ed
已写入 file afiedt.buf
1 insert into test values
2* (2,'Mike',to_char(to_date('2014-10-23','yyyy-mm-dd')))
SQL> /
insert into test values
*
第 1 行出现错误:
ORA-00001: 违反唯一约束条件 (C##SCOTT.UQ_TEST)
6. 创建两个表，名称分别为A、B，分别满足下列要求：A中包含字段a、b，B中包含字段c、d，都是int类型，其中a为表A的主键，c为表B的主键，d为指向表A中字段a的外键。
17 / 28
SQL> create table A
2 (
3 a int constraint pk_A primary key,
4 b int
5 )
6 /
表已创建。
SQL> create table B
2 (
3 c int constraint pk_B primary key,
4 d int constraint fk_B references A(a)
5 )
6 /
表已创建。
7. 向第六题中的表A、B添加记录，验证外键约束所起的作用，即验证引用完整性的内容。
SQL> insert into A values(1,2);
已创建 1 行。
SQL> insert into B values(1,1);
已创建 1 行。
SQL> insert into B values(2,2);
insert into B values(2,2)
*
第 1 行出现错误:
19 / 28
ORA-02291: 违反完整约束条件 (C##SCOTT.FK_B) - 未找到父项关键字
8. 修改上面test表的名称为test1，然后验证结果。
SQL> select * from test;
A B C
---------- ---------- --------------
1 Mike 14-11月-12
SQL> alter table test rename to test1;
表已更改。
SQL> select * from test1;
A B C
---------- ---------- --------------
1 Mike 14-11月-12
9. 删除test1表中的b字段然后验证结果。
SQL> alter table test1 drop column b;
表已更改。
SQL> select * from test1;
A C
---------- --------------
1 14-11月-12
10. 把test1表中的c字段名称修改为d，然后验证结果。
SQL> alter table test1 rename column c to d;
表已更改。
SQL> select * from test1;
A D
21 / 28
---------- --------------
1 14-11月-12
11．删除test1表中的主键约束，然后验证结果。
删除主键约束：
SQL> alter table test1 drop constraint pk_test;
表已更改。
验证主键唯一性无效：
SQL> insert into test1 values
2 (1,to_char(to_date('14/10/23','yy/mm/dd')))
3 /
已创建 1 行。
验证主键非空性无效：
SQL> insert into test1 values
2 (null,to_char(to_date('14/10/23','yy/mm/dd')))
3 /
已创建 1 行。
SQL> select * from test1;
A D
---------- --------------
1 14-11月-12
1 14-11月-12
14-11月-12
12以system用户登录数据库，利用dba_tables数据字典表查询scott用户有哪些表。
SQL> select table_name
23 / 28
2 from dba_tables
3 where owner='SCOTT'
4 /
TABLE_NAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
YAMA
A
B
TEST1
已选择8行。
13．以system用户登录数据库，利用dba_constraints与dba_cons_columns数据字典表查询第六题中的约束名称及其所在的列。
SQL> select table_name,constraint_name,column_name
2 from dba_cons_columns
3 where table_name='A'
4 /
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
25 / 28
A PK_A
A
SQL> ed
已写入 file afiedt.buf
1 select table_name,constraint_name,column_name
2 from dba_cons_columns
3* where table_name='B'
SQL> /
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
COLUMN_NAME
--------------------------------------------------------------
B FK_B
D
B PK_B
C
14．如果不使用desc命令，利用dba_columns数据字典表查询scott的emp表的结构(即emp表的所有列及其类型)。
SQL> select column_name,data_type from dba_tab_columns
2 where owner='SCOTT'
3 and table_name='EMP'
4 /
COLUMN_NAME DATA_TYPE
------------------------------ ------------------------------------
27 / 28
EMPNO NUMBER
ENAME VARCHAR2
JOB VARCHAR2
MGR NUMBER
HIREDATE DATE
SAL NUMBER
COMM NUMBER
DEPTNO NUMBER
已选择8行。
15．用一个命令查询emp表上所有的约束名称，约束类型及其所在列名称，若是外键请给出其指向的表及列，若是检查约束，请给出检查条件。
[文档可能无法思考全面，请浏览后下载，另外祝您生活愉快，工作顺利，万事如意!]
因篇幅问题不能全部显示，请点此查看更多更全内容