前言：
一：数据表概述
二：创建数据表
1：数据表的逻辑结构
1：字符类型
2：数值类型
3：日期时间类型
4:LOB类型
5：rowid 数据类型
2：创建1个数据表
创建一个 学生档案信息表 students 该表包括 学号、姓名、性别、系别编号、班级编号、建档日期
1 @> conn scott/scott 2 Connected. 3 SCOTT@orcl> ho clear 4 5 6 SCOTT@orcl> students( 7 2 stuno number(10) , --学号 8 3 stuname 2(8), --姓名 9 4 sex (2), --性别 10 5 age , --年龄 11 6 departno 2(2) , --系别编号 12 7 classno 2(4) , --班级编号 13 8 regdate sysdate --建档日期 14 9 ); 15 16 created. 17 18 SCOTT@orcl> 191 SCOTT@orcl> students; 2 Name ? Type 3 ----------------------------------------- -------- ---------------------------- 4 STUNO NUMBER(10) 5 STUNAME 2(8) 6 SEX (2) 7 AGE NUMBER(38) 8 DEPARTNO 2(2) 9 CLASSNO 2(4) 10 REGDATE 11 12 SCOTT@orcl>1 [oracle@localhost ~]$ sqlplus / sysdba; 2 3 *Plus: Release 11.2.0.3.0 Production Mon Mar 12 23:53:26 2018 4 5 Copyright (c) 1982, 2011, Oracle. rights reserved. 6 7 8 Connected : 9 Oracle 11g Enterprise Edition Release 11.2.0.3.0 - Production 10 the Partitioning, OLAP, Data Mining Application Testing options 11 12 SYS@orcl> scott.students_demo(id number,name 2(30)); 13 14 created. 15 16 SYS@orcl>1 SYS@orcl> scott.students_demo1 * scott.students ; 2 3 created. 4 5 SYS@orcl>3 ： 数据表的特性
1 2 SYS@orcl> scott.students_3( 3 2 stuno number(10) , --学号 4 3 stuname 2(8), --姓名 5 4 sex (2), --性别 6 5 age , --年龄 7 6 departno 2(2) , --系别编号 8 7 classno 2(4) , --班级编号 9 8 regdate sysdate --建档日期 10 9 )tablespace TBS_TEST1 11 10 storage(initial 256k); 12 13 created. 14 15 SYS@orcl>1 SYS@orcl> conn scott/scott 2 Connected. 3 SCOTT@orcl> initial_extent user_tables table_name ='STUDENTS_3'; 4 5 INITIAL_EXTENT 6 -------------- 7 262144 8 9 SCOTT@orcl>2: 数据块管理参数
- pctfree 和 pctused
1 SCOTT@orcl> 2 SCOTT@orcl> students_4( 3 2 stuno number(10) , --学号 4 3 stuname 2(8), --姓名 5 4 sex (2), --性别 6 5 age , --年龄 7 6 departno 2(2) , --系别编号 8 7 classno 2(4) , --班级编号 9 8 regdate sysdate --建档日期 10 9 )tablespace TBS_TEST1 11 10 storage(initial 256k) 12 11 pctfree 20 13 12 pctused 40; 14 15 16 17 created. 18 19 SCOTT@orcl>
- initrans 参数
1 SCOTT@orcl> students_5( 2 2 stuno number(10) , --学号 3 3 stuname 2(8), --姓名 4 4 sex (2), --性别 5 5 age , --年龄 6 6 departno 2(2) , --系别编号 7 7 classno 2(4) , --班级编号 8 8 regdate sysdate --建档日期 9 9 )tablespace TBS_TEST1 10 10 storage(initial 256k) 11 11 pctfree 20 12 12 pctused 40 13 13 initrans 10; --数据块管理参数 10个事务条目 14 15 16 created. 17 18 SCOTT@orcl>1 SCOTT@orcl> ini_trans , max_trans user_tables table_name ='STUDENTS_5'; 2 3 INI_TRANS MAX_TRANS 4 ---------- ---------- 5 10 255 6 7 SCOTT@orcl>3： 重做日志参数
1 SCOTT@orcl> students_6( 2 2 stuno number(10) , --学号 3 3 stuname 2(8), --姓名 4 4 sex (2), --性别 5 5 age , --年龄 6 6 departno 2(2) , --系别编号 7 7 classno 2(4) , --班级编号 8 8 regdate sysdate --建档日期 9 9 )tablespace TBS_TEST1 10 10 storage(initial 256k) 11 11 pctfree 20 12 12 pctused 40 13 13 initrans 10 14 14 nologging; 15 16 created. 17 18 SCOTT@orcl>4： 缓存参数
1 SCOTT@orcl> table_name ,cache user_tables table_name ='STUDENTS_6'; 2 3 TABLE_NAME CACHE 4 ------------------------------ ---------- 5 STUDENTS_6 N 6 7 SCOTT@orcl>
三：维护数据表
1： 增加和删除字段
1 SCOTT@orcl> select * from students_6; 2 3 no rows selected 4 5 SCOTT@orcl> alter table students_6 add (province varchar2(10)); 6 7 Table altered. 8 9 SCOTT@orcl> desc students_6; 10 Name Null? Type 11 ----------------------------------------- -------- ---------------------------- 12 STUNO NOT NULL NUMBER(10) 13 STUNAME VARCHAR2(8) 14 SEX CHAR(2) 15 AGE NUMBER(38) 16 DEPARTNO NOT NULL VARCHAR2(2) 17 CLASSNO NOT NULL VARCHAR2(4) 18 REGDATE DATE 19 PROVINCE VARCHAR2(10) 20 21 SCOTT@orcl>1 SCOTT@orcl> alter table students_6 drop column PROVINCE ; 2 3 Table altered. 4 SCOTT@orcl> desc students_6; 5 Name Null? Type 6 ----------------------------------------- -------- ---------------------------- 7 STUNO NOT NULL NUMBER(10) 8 STUNAME VARCHAR2(8) 9 SEX CHAR(2) 10 AGE NUMBER(38) 11 DEPARTNO NOT NULL VARCHAR2(2) 12 CLASSNO NOT NULL VARCHAR2(4) 13 REGDATE DATE 14 15 SCOTT@orcl>1 2 SCOTT@orcl> desc students_6; 3 Name Null? Type 4 ----------------------------------------- -------- ---------------------------- 5 STUNO NOT NULL NUMBER(10) 6 STUNAME VARCHAR2(8) 7 SEX CHAR(2) 8 AGE NUMBER(38) 9 DEPARTNO NOT NULL VARCHAR2(2) 10 CLASSNO NOT NULL VARCHAR2(4) 11 REGDATE DATE 12 13 SCOTT@orcl> alter table students_6 drop (sex,age); 14 15 Table altered. 16 17 SCOTT@orcl> desc students_6; 18 Name Null? Type 19 ----------------------------------------- -------- ---------------------------- 20 STUNO NOT NULL NUMBER(10) 21 STUNAME VARCHAR2(8) 22 DEPARTNO NOT NULL VARCHAR2(2) 23 CLASSNO NOT NULL VARCHAR2(4) 24 REGDATE DATE 25 26 SCOTT@orcl>
2: 修改字段
1 2 SCOTT@orcl> desc students_6; 3 Name Null? Type 4 ----------------------------------------- -------- ---------------------------- 5 STUNO NOT NULL NUMBER(10) 6 STUNAME VARCHAR2(8) 7 DEPARTNO NOT NULL VARCHAR2(2) 8 CLASSNO NOT NULL VARCHAR2(4) 9 REGDATE DATE 10 11 SCOTT@orcl> alter table students_6 modify departno varchar2(4); 12 13 Table altered. 14 15 SCOTT@orcl> desc students_6; 16 Name Null? Type 17 ----------------------------------------- -------- ---------------------------- 18 STUNO NOT NULL NUMBER(10) 19 STUNAME VARCHAR2(8) 20 DEPARTNO NOT NULL VARCHAR2(4) 21 CLASSNO NOT NULL VARCHAR2(4) 22 REGDATE DATE 23 24 SCOTT@orcl>
3：重命名表名称
1 2 SCOTT@orcl> students_6 rename students_7; 3 4 altered. 5 6 SCOTT@orcl> * students_6; 7 * students_6 8 * 9 ERROR line 1: 10 ORA-00942: does exist 11 12 13 SCOTT@orcl>
4：改变表空间和储存参数
1：修改表空间
1 2 Copyright (c) 1982, 2011, Oracle. rights reserved. 3 4 5 Connected : 6 Oracle 11g Enterprise Edition Release 11.2.0.3.0 - Production 7 the Partitioning, OLAP, Data Mining Application Testing options 8 9 SYS@orcl> ho clear 10 11 ---用 sys 用户 查询有多少表空间 12 SYS@orcl> a.tablespace_name dba_data_files a; 13 14 TABLESPACE_NAME 15 ------------------------------ 16 USERS 17 UNDOTBS1 18 SYSAUX 19 SYSTEM 20 EXAMPLE 21 TBS_TEST1 22 TBS_TEST_3 23 TBS_TEST4 24 TBS_TEST5 25 TBS_BIG_1 26 27 10 selected. 28 --切换数据库用户 29 SYS@orcl> conn scott/scott 30 Connected. 31 32 ---查询该数据用户下 所有的表与表空间的对应关系 33 SCOTT@orcl> SCOTT@orcl> table_name,tablespace_name user_tables t ; 34 35 36 TABLE_NAME TABLESPACE_NAME 37 ------------------------------ ------------------------------ 38 DEPT USERS 39 EMP USERS 40 BONUS USERS 41 SALGRADE USERS 42 DEPT_LOG USERS 43 GOODS USERS 44 DDL_OPER_LOG USERS 45 TB_TEST USERS 46 STUDENTS TBS_TEST1 47 STUDENTS_DEMO TBS_TEST1 48 STUDENTS_DEMO1 TBS_TEST1 49 50 TABLE_NAME TABLESPACE_NAME 51 ------------------------------ ------------------------------ 52 STUDENTS_3 TBS_TEST1 53 STUDENTS_4 TBS_TEST1 54 STUDENTS_5 TBS_TEST1 55 STUDENTS_7 TBS_TEST1 56 57 15 selected. 58 ---把 表 STUDENTS 从 表空间 TBS_TEST1 移动到 表空间 TBS_TEST_3 中 59 60 61 SCOTT@orcl> students_7 move tablespace TBS_TEST_3; 62 63 altered. 64 ---查询最终 数据移动结果 65 SCOTT@orcl> table_name,tablespace_name user_tables t ; 66 67 68 TABLE_NAME TABLESPACE_NAME 69 ------------------------------ ------------------------------ 70 DEPT USERS 71 EMP USERS 72 BONUS USERS 73 SALGRADE USERS 74 DEPT_LOG USERS 75 GOODS USERS 76 DDL_OPER_LOG USERS 77 TB_TEST USERS 78 STUDENTS TBS_TEST1 79 STUDENTS_DEMO TBS_TEST1 80 STUDENTS_DEMO1 TBS_TEST1 81 82 TABLE_NAME TABLESPACE_NAME 83 ------------------------------ ------------------------------ 84 STUDENTS_3 TBS_TEST1 85 STUDENTS_4 TBS_TEST1 86 STUDENTS_5 TBS_TEST1 87 STUDENTS_7 TBS_TEST_3 88 89 15 selected. 90 91 SCOTT@orcl> SCOTT@orcl>2：修改 储存参数
1 > t.table_name ,t.pct_free,t.pct_used ,t.pct_increase user_tables t t.table_name='STUDENTS_7'; 2 3 4 TABLE_NAME PCT_FREE PCT_USED PCT_INCREASE 5 ------------------------------ ---------- ---------- ------------ 6 STUDENTS_7 20 40 7 8 SCOTT@orcl> SCOTT@orcl> STUDENTS_7 PCTFREE 25 PCTUSED 45; 9 10 altered. 11 12 SCOTT@orcl> t.table_name ,t.pct_free,t.pct_used ,t.pct_increase user_tables t t.table_name='STUDENTS_7'; 13 14 TABLE_NAME PCT_FREE PCT_USED PCT_INCREASE 15 ------------------------------ ---------- ---------- ------------ 16 STUDENTS_7 25 45 17 18 SCOTT@orcl>
5: 删除表
1 table_name [casecade ];1 SCOTT@orcl> 2 SCOTT@orcl> t.table_name ,t.pct_free,t.pct_used ,t.pct_increase user_tables t t.table_name='STUDENTS_5'; 3 4 5 TABLE_NAME PCT_FREE PCT_USED PCT_INCREASE 6 ------------------------------ ---------- ---------- ------------ 7 STUDENTS_5 20 8 9 SCOTT@orcl> SCOTT@orcl> students_5 ; 10 11 dropped. 12 13 SCOTT@orcl> t.table_name ,t.pct_free,t.pct_used ,t.pct_increase user_tables t t.table_name='STUDENTS_5'; 14 15 selected 16 17 SCOTT@orcl> 18 SCOTT@orcl>示例：利用闪回功能快速恢复被删除的表 students_5 ；
1：实现 确认students_5 表是否已经被删除了
1 SCOTT@orcl> * students_5 ; 2 * students_5 3 * 4 ERROR line 1: 5 ORA-00942: does exist 6 7 8 SCOTT@orcl>2：从上面的查询结果看出，该表已经被删除。那么用户就要通过查询数据字典视图 recyclebin 来观察该表是否已经进入回收站
1 SCOTT@orcl> object_name ,original_name recyclebin original_name ='STUDENTS_5'; 2 3 OBJECT_NAME ORIGINAL_NAME 4 ------------------------------ -------------------------------- 5 BIN$Z5z6vK4HbbHgUwEAAH/rpg==$0 STUDENTS_5 6 7 SCOTT@orcl>3: 使用 flashback table 语句恢复被删除的students_5表
1 SCOTT@orcl> flashback STUDENTS_5 before ; 2 3 Flashback complete. 4 5 SCOTT@orcl>4：确认 表是否已经被恢复了
1 SCOTT@orcl> object_name ,original_name recyclebin original_name ='STUDENTS_5'; 2 3 selected 4 5 SCOTT@orcl> * students_5 ; 6 7 selected 8 9 SCOTT@orcl>1 ---如果在删除表的时候使用了 purge 则 该表是直接删除掉了而不会进入回收站。表无法被闪回 2 SCOTT@orcl> students_5 purge; 3 4 dropped. 5 6 SCOTT@orcl> object_name ,original_name recyclebin original_name ='STUDENTS_5'; 7 8 selected 9 10 SCOTT@orcl> flashback STUDENTS_5 before ; 11 flashback STUDENTS_5 before 12 * 13 ERROR line 1: 14 ORA-38305: object RECYCLE BIN 15 16 17 SCOTT@orcl>
6：修改表的状态
1 2 3 ----查看现在数据库表该 scott用户下的所有表的情况 4 SCOTT@orcl> table_name,tablespace_name user_tables t ; 5 6 7 TABLE_NAME TABLESPACE_NAME 8 ------------------------------ ------------------------------ 9 DEPT USERS 10 EMP USERS 11 BONUS USERS 12 SALGRADE USERS 13 DEPT_LOG USERS 14 GOODS USERS 15 DDL_OPER_LOG USERS 16 TB_TEST USERS 17 STUDENTS TBS_TEST1 18 STUDENTS_DEMO TBS_TEST1 19 STUDENTS_DEMO1 TBS_TEST1 20 21 TABLE_NAME TABLESPACE_NAME 22 ------------------------------ ------------------------------ 23 STUDENTS_3 TBS_TEST1 24 STUDENTS_4 TBS_TEST1 25 STUDENTS_7 TBS_TEST_3 26 27 14 selected. 28 29 ---创建 表 students_5 ；从students_4 复制并创建students_5 30 31 SCOTT@orcl> students_5 * students_3; 32 33 created. 34 35 ---查看表 students_5 的状态 36 SCOTT@orcl> table_name,read_only user_tables table_name ='STUDENTS_5'; 37 38 TABLE_NAME REA 39 ------------------------------ --- 40 STUDENTS_5 41 ---设置表 students_5 为只读状态 42 SCOTT@orcl> students_5 ; 43 44 altered. 45 ---- 46 SCOTT@orcl> table_name,read_only user_tables table_name ='STUDENTS_5'; 47 48 TABLE_NAME REA 49 ------------------------------ --- 50 STUDENTS_5 YES 51 52 SCOTT@orcl>1 SCOTT@orcl> students_5 sex='男' stuno >5000; 2 students_5 sex='男' stuno >5000 3 * 4 ERROR line 1: 5 ORA-12081: operation allowed "SCOTT"."STUDENTS_5" 6 7 8 SCOTT@orcl>对于 ora_12081:不允许对表"SCOTT"."STUDENTS_5" 进行更新操作。其原因为该表是只读状态。不能进行数据的更新 插入 删除等操作。
1 [oracle@localhost ~]$ sqlplus / sysdba; 2 3 *Plus: Release 11.2.0.3.0 Production Sat Mar 17 23:24:05 2018 4 5 Copyright (c) 1982, 2011, Oracle. rights reserved. 6 7 8 Connected : 9 Oracle 11g Enterprise Edition Release 11.2.0.3.0 - Production 10 the Partitioning, OLAP, Data Mining Application Testing options 11 ----查询 所有的表空间名称 12 SYS@orcl> tablespace_name dba_data_files; 13 14 TABLESPACE_NAME 15 ------------------------------ 16 USERS 17 UNDOTBS1 18 SYSAUX 19 SYSTEM 20 EXAMPLE 21 TBS_TEST1 22 TBS_TEST_3 23 TBS_TEST4 24 TBS_TEST5 25 TBS_BIG_1 26 27 10 selected. 28 --切换到scott用户 29 SYS@orcl> conn scott/scott; 30 Connected. 31 --查询 scott 用户下的所有表与表空间的对应关系 32 SCOTT@orcl> table_name ,tablespace_name user_tables; 33 34 TABLE_NAME TABLESPACE_NAME 35 ------------------------------ ------------------------------ 36 DEPT USERS 37 EMP USERS 38 BONUS USERS 39 SALGRADE USERS 40 DEPT_LOG USERS 41 GOODS USERS 42 DDL_OPER_LOG USERS 43 TB_TEST USERS 44 STUDENTS TBS_TEST1 45 STUDENTS_DEMO TBS_TEST1 46 STUDENTS_DEMO1 TBS_TEST1 47 48 TABLE_NAME TABLESPACE_NAME 49 ------------------------------ ------------------------------ 50 STUDENTS_3 TBS_TEST1 51 STUDENTS_4 TBS_TEST1 52 STUDENTS_5 TBS_TEST1 53 STUDENTS_7 TBS_TEST_3 54 55 15 selected. 56 --把 表 students_5 的 从 tbs_test1 移动到 tbs_test3中去。 57 SCOTT@orcl> students_5 move tablespace tbs_test_3; 58 59 altered. 60 61 SCOTT@orcl> table_name ,tablespace_name user_tables; 62 63 TABLE_NAME TABLESPACE_NAME 64 ------------------------------ ------------------------------ 65 DEPT USERS 66 EMP USERS 67 BONUS USERS 68 SALGRADE USERS 69 DEPT_LOG USERS 70 GOODS USERS 71 DDL_OPER_LOG USERS 72 TB_TEST USERS 73 STUDENTS TBS_TEST1 74 STUDENTS_DEMO TBS_TEST1 75 STUDENTS_DEMO1 TBS_TEST1 76 77 TABLE_NAME TABLESPACE_NAME 78 ------------------------------ ------------------------------ 79 STUDENTS_3 TBS_TEST1 80 STUDENTS_4 TBS_TEST1 81 STUDENTS_7 TBS_TEST_3 82 STUDENTS_5 TBS_TEST_3 83 84 15 selected. 85 86 ---查看其表的状态是否发生改变。而后变没有发生变化 87 SCOTT@orcl> table_name ,read_only user_tables table_name ='STUDENTS_5'; 88 89 TABLE_NAME REA 90 ------------------------------ --- 91 STUDENTS_5 YES 92 93 SCOTT@orcl>1 SCOTT@orcl> table_name ,read_only user_tables table_name ='STUDENTS_5'; 2 3 TABLE_NAME REA 4 ------------------------------ --- 5 STUDENTS_5 YES 6 7 SCOTT@orcl> students_5 ; 8 9 altered. 10 11 SCOTT@orcl> table_name ,read_only user_tables table_name ='STUDENTS_5'; 12 13 TABLE_NAME REA 14 ------------------------------ --- 15 STUDENTS_5 16 17 SCOTT@orcl>
四：数据完整性和约束性
1： 非空约束
1 SCOTT@orcl> Books 2 2 ( 3 3 BookNo number(4) ,--图书编号 4 4 BookName 2(20),--图书名称 5 5 Author 2(10),--作者 6 6 SalePrice number(9,2),--定价 7 7 PublisherNo 2(4) ,--出版社编号 8 8 PublishDate ,--出版日期 9 9 ISBN 2(20) --ISBN 10 10 ); 11 12 created. 13 14 SCOTT@orcl> table_name ,tablespace_name user_tables; 15 16 TABLE_NAME TABLESPACE_NAME 17 ------------------------------ ------------------------------ 18 DEPT USERS 19 EMP USERS 20 BONUS USERS 21 SALGRADE USERS 22 DEPT_LOG USERS 23 GOODS USERS 24 DDL_OPER_LOG USERS 25 TB_TEST USERS 26 STUDENTS TBS_TEST1 27 STUDENTS_DEMO TBS_TEST1 28 STUDENTS_DEMO1 TBS_TEST1 29 30 TABLE_NAME TABLESPACE_NAME 31 ------------------------------ ------------------------------ 32 STUDENTS_3 TBS_TEST1 33 STUDENTS_4 TBS_TEST1 34 BOOKS TBS_TEST1 35 STUDENTS_7 TBS_TEST_3 36 STUDENTS_5 TBS_TEST_3 37 38 16 selected. 39 40 SCOTT@orcl>1 2 SCOTT@orcl> table_name ,tablespace_name user_tables; 3 4 TABLE_NAME TABLESPACE_NAME 5 ------------------------------ ------------------------------ 6 DEPT USERS 7 EMP USERS 8 BONUS USERS 9 SALGRADE USERS 10 DEPT_LOG USERS 11 GOODS USERS 12 DDL_OPER_LOG USERS 13 TB_TEST USERS 14 STUDENTS TBS_TEST1 15 STUDENTS_DEMO TBS_TEST1 16 STUDENTS_DEMO1 TBS_TEST1 17 18 TABLE_NAME TABLESPACE_NAME 19 ------------------------------ ------------------------------ 20 STUDENTS_3 TBS_TEST1 21 STUDENTS_4 TBS_TEST1 22 BOOKS TBS_TEST1 23 STUDENTS_7 TBS_TEST_3 24 STUDENTS_5 TBS_TEST_3 25 26 16 selected. 27 28 SCOTT@orcl> books; 29 Name ? Type 30 ----------------------------------------- -------- ---------------------------- 31 BOOKNO NUMBER(4) 32 BOOKNAME 2(20) 33 AUTHOR 2(10) 34 SALEPRICE NUMBER(9,2) 35 PUBLISHERNO 2(4) 36 PUBLISHDATE 37 ISBN 2(20) 38 39 40 41 SCOTT@orcl> books modify bookname ; 42 43 altered. 441 SCOTT@orcl> books; 2 Name ? Type 3 ----------------------------------------- -------- ---------------------------- 4 BOOKNO NUMBER(4) 5 BOOKNAME 2(20) 6 AUTHOR 2(10) 7 SALEPRICE NUMBER(9,2) 8 PUBLISHERNO 2(4) 9 PUBLISHDATE 10 ISBN 2(20) 11 12 SCOTT@orcl> books modify bookname ; 13 14 altered. 15 16 17 18 SCOTT@orcl> books; 19 Name ? Type 20 ----------------------------------------- -------- ---------------------------- 21 BOOKNO NUMBER(4) 22 BOOKNAME 2(20) 23 AUTHOR 2(10) 24 SALEPRICE NUMBER(9,2) 25 PUBLISHERNO 2(4) 26 PUBLISHDATE 27 ISBN 2(20) 28 29 SCOTT@orcl>
2：主键约束
1 2 SCOTT@orcl> table_name ,tablespace_name user_tables; 3 4 TABLE_NAME TABLESPACE_NAME 5 ------------------------------ ------------------------------ 6 DEPT USERS 7 EMP USERS 8 BONUS USERS 9 SALGRADE USERS 10 DEPT_LOG USERS 11 GOODS USERS 12 DDL_OPER_LOG USERS 13 TB_TEST USERS 14 STUDENTS TBS_TEST1 15 STUDENTS_DEMO TBS_TEST1 16 STUDENTS_DEMO1 TBS_TEST1 17 18 TABLE_NAME TABLESPACE_NAME 19 ------------------------------ ------------------------------ 20 STUDENTS_3 TBS_TEST1 21 STUDENTS_4 TBS_TEST1 22 BOOKS TBS_TEST1 23 STUDENTS_7 TBS_TEST_3 24 STUDENTS_5 TBS_TEST_3 25 26 16 selected. 27 28 SCOTT@orcl> Books_1 29 2 ( 30 3 BookNo number(4) ,--图书编号 31 4 BookName 2(20),--图书名称 32 5 Author 2(10),--作者 33 6 SalePrice number(9,2),--定价 34 7 PublisherNo 2(4) ,--出版社编号 35 8 PublishDate ,--出版日期 36 9 ISBN 2(20) ,--ISBN 37 10 BOOK_PK (BookNo) 38 11 ); 39 40 created. 41 42 SCOTT@orcl> table_name ,tablespace_name user_tables; 43 44 TABLE_NAME TABLESPACE_NAME 45 ------------------------------ ------------------------------ 46 DEPT USERS 47 EMP USERS 48 BONUS USERS 49 SALGRADE USERS 50 DEPT_LOG USERS 51 GOODS USERS 52 DDL_OPER_LOG USERS 53 TB_TEST USERS 54 STUDENTS TBS_TEST1 55 STUDENTS_DEMO TBS_TEST1 56 STUDENTS_DEMO1 TBS_TEST1 57 58 TABLE_NAME TABLESPACE_NAME 59 ------------------------------ ------------------------------ 60 STUDENTS_3 TBS_TEST1 61 STUDENTS_4 TBS_TEST1 62 BOOKS TBS_TEST1 63 BOOKS_1 TBS_TEST1 64 STUDENTS_7 TBS_TEST_3 65 STUDENTS_5 TBS_TEST_3 66 67 17 selected. 68 69 SCOTT@orcl>1 SCOTT@orcl> books 2 Name ? Type 3 ----------------------------------------- -------- ---------------------------- 4 BOOKNO NUMBER(4) 5 BOOKNAME 2(20) 6 AUTHOR 2(10) 7 SALEPRICE NUMBER(9,2) 8 PUBLISHERNO 2(4) 9 PUBLISHDATE 10 ISBN 2(20) 11 --------------------- 使用 alter table.. . add 语句 为books 表添加主键约束。12 SCOTT@orcl> books Books_PK (bookno); 13 14 altered. 15 16 SCOTT@orcl> books; 17 Name ? Type 18 ----------------------------------------- -------- ---------------------------- 19 BOOKNO NUMBER(4) 20 BOOKNAME 2(20) 21 AUTHOR 2(10) 22 SALEPRICE NUMBER(9,2) 23 PUBLISHERNO 2(4) 24 PUBLISHDATE 25 ISBN 2(20) 26 ---------------------查找表的唯一性约束（包括名称，构成列）：27 SCOTT@orcl> cu.* user_cons_columns cu, user_constraints au cu.constraint_name = au.constraint_name au.constraint_type = 'P' au.table_name ='BOOKS' ; 28 29 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME 30 ------------ -------------- ---------------- -------------- ------------- 31 SCOTT BOOKS_PK BOOKS BOOKNO 1 32 33 34 SCOTT@orcl>1 SCOTT@orcl> cu.* user_cons_columns cu, user_constraints au cu.constraint_name = au.constraint_name au.constraint_type = 'P' au.table_name ='BOOKS_1'; 2 3 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME 4 ------------------------------------------------------------------------------------------------------------------ 5 SCOTT BOOK_PK BOOKS_1 BOOKNO 1 6 7 8 9 SCOTT@orcl> books_1 BOOK_PK; 10 11 altered. 12 13 SCOTT@orcl> cu.* user_cons_columns cu, user_constraints au cu.constraint_name = au.constraint_name au.constraint_type = 'P' au.table_name ='BOOKS_1'; 14 15 selected 16 17 SCOTT@orcl>
3：唯一性约束
查询表的唯一性约束
1 HR@orcl> column_name user_cons_columns cu, user_constraints au cu.constraint_name = au.constraint_name au.constraint_type = 'U' au.table_name = 'members'; 2 3 selected1 SCOTT@orcl> 2 SCOTT@orcl> table_name,tablespace_name user_tables; 3 4 TABLE_NAME TABLESPACE_NAME 5 ------------------------------ ------------------------------ 6 DEPT USERS 7 EMP USERS 8 BONUS USERS 9 SALGRADE USERS 10 DEPT_LOG USERS 11 GOODS USERS 12 DDL_OPER_LOG USERS 13 TB_TEST USERS 14 STUDENTS TBS_TEST1 15 STUDENTS_DEMO TBS_TEST1 16 STUDENTS_DEMO1 TBS_TEST1 17 18 TABLE_NAME TABLESPACE_NAME 19 ------------------------------ ------------------------------ 20 STUDENTS_3 TBS_TEST1 21 STUDENTS_4 TBS_TEST1 22 BOOKS TBS_TEST1 23 BOOKS_1 TBS_TEST1 24 STUDENTS_7 TBS_TEST_3 25 STUDENTS_5 TBS_TEST_3 26 27 17 selected. 28 29 SCOTT@orcl> Members 30 2 ( 31 3 MemNo number(4) , --会员编号 32 4 MemName 2(20) , --会员名称 33 5 Phone 2(20), --联系电话 34 6 Email 2(30), --电子邮件地址 35 7 QQ 2(20) QQ_UK , --QQ号，并设置为UNIQUE约束 36 8 ProvCode 2(2) , --省份代码 37 9 OccuCode 2(2) , --职业代码 38 10 InDate sysdate, --入会日期 39 11 Mem_PK (MemNo) --主键约束列为MemNo 40 12 ); 41 42 43 created. 44 45 SCOTT@orcl> SCOTT@orcl> table_name,tablespace_name user_tables; 46 47 TABLE_NAME TABLESPACE_NAME 48 ------------------------------ ------------------------------ 49 DEPT USERS 50 EMP USERS 51 BONUS USERS 52 SALGRADE USERS 53 DEPT_LOG USERS 54 GOODS USERS 55 DDL_OPER_LOG USERS 56 TB_TEST USERS 57 STUDENTS TBS_TEST1 58 STUDENTS_DEMO TBS_TEST1 59 STUDENTS_DEMO1 TBS_TEST1 60 61 TABLE_NAME TABLESPACE_NAME 62 ------------------------------ ------------------------------ 63 STUDENTS_3 TBS_TEST1 64 STUDENTS_4 TBS_TEST1 65 BOOKS TBS_TEST1 66 BOOKS_1 TBS_TEST1 67 MEMBERS TBS_TEST1 68 STUDENTS_7 TBS_TEST_3 69 STUDENTS_5 TBS_TEST_3 70 71 18 selected. 72 73 SCOTT@orcl>1 SCOTT@orcl> members(memno,memname,phone,email,qq,provcode,occucode) ('001','111','asfsd','afdaweew@WQRW.COM',,'01','02'); 2 3 1 row created. 4 5 SCOTT@orcl> members(memno,memname,phone,email,qq,provcode,occucode) ('001','111','asfsd','afdaweew@WQRW.COM',,'01','02'); 6 members(memno,memname,phone,email,qq,provcode,occucode) ('001','111','asfsd','afdaweew@WQRW.COM',,'01','02') 7 * 8 ERROR line 1: 9 ORA-00001: (SCOTT.MEM_PK) violated 10 11 12 SCOTT@orcl>1 2 SCOTT@orcl> members Email_UK (email); 3 4 altered. 5 6 SCOTT@orcl> 71 SCOTT@orcl> members Email_UK; 2 3 altered. 4 5 SCOTT@orcl> 6
4： 外键约束
查看表的所有外键约束
1 HR@orcl> * user_constraints c c.constraint_type = 'R' c.table_name = 'employess_temp'; 2 3 selected 41 4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）： 2 * user_constraints c c.constraint_type = 'R' c.table_name = 表名 3 外键约束的列名： 4 * user_cons_columns cl cl.constraint_name = 外键名称 5 引用表的键的列名： 6 * user_cons_columns cl cl.constraint_name = 外键引用表的键名1 SCOTT@orcl> conn hr/hr 2 Connected. 3 HR@orcl> employees_temp 4 2 * employees 5 3 department_id=30; 6 7 employees_temp 8 temp_departid_fk 9 (department_id) 10 departments(department_id); 11 12 13 14 created. 151 employees_temp temp_departid_fk (department_id) departments;1 HR@orcl> employees_temp(employee_id,last_name,email,job_id,hire_date,department_id) (9527,'aaa','aadfae@adfa.com','astew',sysdate,9999); 2 employees_temp(employee_id,last_name,email,job_id,hire_date,department_id) (9527,'aaa','aadfae@adfa.com','astew',sysdate,9999) 3 * 4 ERROR line 1: 5 ORA-02291: integrity (HR.TEMP_DEPARTID_FK) violated - parent 6 7 8 9 HR@orcl>1 HR@orcl> departments_temp * departments department_id=30; 2 3 created. 4 5 HR@orcl> departments_temp (department_id) ; 6 7 altered. 8 9 HR@orcl>1 employees_temp consrtraint temp_departid_fk2 foregin (department_id) departments_temp casade;1 2 HR@orcl> (*) employees_temp department_id=30; 3 4 (*) 5 ---------- 6 6 7 8 HR@orcl>1 2 DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID 3 ------------- ------------------------------ ---------- ----------- 4 30 Purchasing 114 1700 5 6 HR@orcl> departments_temp department_id=30; 7 8 1 row deleted. 9 10 HR@orcl> (*) departments_temp; 11 12 (*) 13 ---------- 14 0 15 16 HR@orcl>1 employees_temp temp_departid_fk2;
5：禁用与激活约束
1：在定义约束时被禁用
1 HR@orcl> Student 2 2 ( 3 3 StuCode 2(4) , 4 4 StuName 2(10) , 5 5 Age Age_CK (age > 0 age <120) disable, 6 6 Province 2(20), 7 7 SchoolName 2(50) 8 8 ); 9 10 created. 11 12 HR@orcl>2：禁用已经存在的约束
1 HR@orcl> t.*,c.COMMENTS user_tab_columns t,user_col_comments c t.table_name = c.table_name t.column_name = c.column_name t.table_name ='employees_temp'; 2 3 selected 4 5 HR@orcl> employees_temp disable temp_departid_fk; 6 7 altered. 8 9 HR@orcl>1 SCOTT@orcl> books disable BOOKS_PK; 2 3 altered. 4 5 SCOTT@orcl> 61 SCOTT@orcl> * books; 2 3 selected 4 5 SCOTT@orcl> books; 6 Name ? Type 7 ----------------------------------------- -------- ---------------------------- 8 BOOKNO NUMBER(4) 9 BOOKNAME 2(20) 10 AUTHOR 2(10) 11 SALEPRICE NUMBER(9,2) 12 PUBLISHERNO 2(4) 13 PUBLISHDATE 14 ISBN 2(20) 15 16 SCOTT@orcl> books (bookno,PUBLISHERNO,ISBN) (1,'asdf','2134234'); 17 18 SCOTT@orcl> books (bookno,PUBLISHERNO,ISBN) (1,'asdf','2134234'); 19 20 1 row created. 21 22 SCOTT@orcl> books (bookno,PUBLISHERNO,ISBN) (1,'asdf','2134234'); 23 24 1 row created. 25 26 SCOTT@orcl> books (bookno,PUBLISHERNO,ISBN) (1,'asdf','2134234'); 27 28 1 row created. 29 30 SCOTT@orcl>1 SCOTT@orcl> books enable BOOKS_PK; 2 books enable BOOKS_PK 3 * 4 ERROR line 1: 5 ORA-02437: cannot validate (SCOTT.BOOKS_PK) - violated 6 7 8 SCOTT@orcl> books; 9 10 3 deleted. 11 12 SCOTT@orcl> ; 13 14 complete. 15 16 SCOTT@orcl> books enable BOOKS_PK; 17 18 altered. 19 20 SCOTT@orcl> books (bookno,PUBLISHERNO,ISBN) (1,'asdf','2134234'); 21 22 1 row created. 23 24 SCOTT@orcl> books (bookno,PUBLISHERNO,ISBN) (1,'asdf','2134234'); 25 books (bookno,PUBLISHERNO,ISBN) (1,'asdf','2134234') 26 * 27 ERROR line 1: 28 ORA-00001: (SCOTT.BOOKS_PK) violated 29 30 31 SCOTT@orcl>
6：删除约束
———————————————————————————————————————————————————————————————————————————————————————————————————————