前言：
一：数据表概述
二：创建数据表
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> create table students( 7 2 stuno number(10) not null, --学号 8 3 stuname varchar2(8), --姓名 9 4 sex char(2), --性别 10 5 age int, --年龄 11 6 departno varchar2(2) not null, --系别编号 12 7 classno varchar2(4) not null, --班级编号 13 8 regdate date default sysdate --建档日期 14 9 ); 15 16 Table created. 17 18 SCOTT@orcl> 19
1 SCOTT@orcl> desc students; 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 STUNO NOT NULL NUMBER(10) 5 STUNAME VARCHAR2(8) 6 SEX CHAR(2) 7 AGE NUMBER(38) 8 DEPARTNO NOT NULL VARCHAR2(2) 9 CLASSNO NOT NULL VARCHAR2(4) 10 REGDATE DATE 11 12 SCOTT@orcl>
1 [oracle@localhost ~]$ sqlplus / as sysdba; 2 3 SQL*Plus: Release 11.2.0.3.0 Production on Mon Mar 12 23:53:26 2018 4 5 Copyright (c) 1982, 2011, Oracle. All rights reserved. 6 7 8 Connected to: 9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options 11 12 SYS@orcl> create table scott.students_demo(id number,name varchar2(30)); 13 14 Table created. 15 16 SYS@orcl>
1 SYS@orcl> create table scott.students_demo1 as select * from scott.students ; 2 3 Table created. 4 5 SYS@orcl>
3 ： 数据表的特性
1 2 SYS@orcl> create table scott.students_3( 3 2 stuno number(10) not null, --学号 4 3 stuname varchar2(8), --姓名 5 4 sex char(2), --性别 6 5 age int, --年龄 7 6 departno varchar2(2) not null, --系别编号 8 7 classno varchar2(4) not null, --班级编号 9 8 regdate date default sysdate --建档日期 10 9 )tablespace TBS_TEST1 11 10 storage(initial 256k); 12 13 Table created. 14 15 SYS@orcl>
1 SYS@orcl> conn scott/scott 2 Connected. 3 SCOTT@orcl> select initial_extent from user_tables where table_name ='STUDENTS_3'; 4 5 INITIAL_EXTENT 6 -------------- 7 262144 8 9 SCOTT@orcl>
2: 数据块管理参数
- pctfree 和 pctused
1 SCOTT@orcl> 2 SCOTT@orcl> create table students_4( 3 2 stuno number(10) not null, --学号 4 3 stuname varchar2(8), --姓名 5 4 sex char(2), --性别 6 5 age int, --年龄 7 6 departno varchar2(2) not null, --系别编号 8 7 classno varchar2(4) not null, --班级编号 9 8 regdate date default sysdate --建档日期 10 9 )tablespace TBS_TEST1 11 10 storage(initial 256k) 12 11 pctfree 20 13 12 pctused 40; 14 15 16 17 Table created. 18 19 SCOTT@orcl>
- initrans 参数
1 SCOTT@orcl> create table students_5( 2 2 stuno number(10) not null, --学号 3 3 stuname varchar2(8), --姓名 4 4 sex char(2), --性别 5 5 age int, --年龄 6 6 departno varchar2(2) not null, --系别编号 7 7 classno varchar2(4) not null, --班级编号 8 8 regdate date default 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 Table created. 17 18 SCOTT@orcl>
1 SCOTT@orcl> select ini_trans , max_trans from user_tables where table_name ='STUDENTS_5'; 2 3 INI_TRANS MAX_TRANS 4 ---------- ---------- 5 10 255 6 7 SCOTT@orcl>
3： 重做日志参数
1 SCOTT@orcl> create table students_6( 2 2 stuno number(10) not null, --学号 3 3 stuname varchar2(8), --姓名 4 4 sex char(2), --性别 5 5 age int, --年龄 6 6 departno varchar2(2) not null, --系别编号 7 7 classno varchar2(4) not null, --班级编号 8 8 regdate date default 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 Table created. 17 18 SCOTT@orcl>
4： 缓存参数
1 SCOTT@orcl> select table_name ,cache from user_tables where 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> alter table students_6 rename to students_7; 3 4 Table altered. 5 6 SCOTT@orcl> select * from students_6; 7 select * from students_6 8 * 9 ERROR at line 1: 10 ORA-00942: table or view does not exist 11 12 13 SCOTT@orcl>
4：改变表空间和储存参数
1：修改表空间
1 2 Copyright (c) 1982, 2011, Oracle. All rights reserved. 3 4 5 Connected to: 6 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 7 With the Partitioning, OLAP, Data Mining and Real Application Testing options 8 9 SYS@orcl> ho clear 10 11 ---用 sys 用户 查询有多少表空间 12 SYS@orcl> select a.tablespace_name from 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 rows selected. 28 --切换数据库用户 29 SYS@orcl> conn scott/scott 30 Connected. 31 32 ---查询该数据用户下 所有的表与表空间的对应关系 33 SCOTT@orcl> SCOTT@orcl> select table_name,tablespace_name from 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 rows selected. 58 ---把 表 STUDENTS 从 表空间 TBS_TEST1 移动到 表空间 TBS_TEST_3 中 59 60 61 SCOTT@orcl> alter table students_7 move tablespace TBS_TEST_3; 62 63 Table altered. 64 ---查询最终 数据移动结果 65 SCOTT@orcl> select table_name,tablespace_name from 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 rows selected. 90 91 SCOTT@orcl> SCOTT@orcl>
2：修改 储存参数
1 SCOTT@orcl> select t.table_name ,t.pct_free,t.pct_used ,t.pct_increase from user_tables t where 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> ALTER TABLE STUDENTS_7 PCTFREE 25 PCTUSED 45; 9 10 Table altered. 11 12 SCOTT@orcl> select t.table_name ,t.pct_free,t.pct_used ,t.pct_increase from user_tables t where 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 drop table table_name [casecade constraints];
1 SCOTT@orcl> 2 SCOTT@orcl> select t.table_name ,t.pct_free,t.pct_used ,t.pct_increase from user_tables t where 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> drop table students_5 cascade constraints; 10 11 Table dropped. 12 13 SCOTT@orcl> select t.table_name ,t.pct_free,t.pct_used ,t.pct_increase from user_tables t where t.table_name='STUDENTS_5'; 14 15 no rows selected 16 17 SCOTT@orcl> 18 SCOTT@orcl>
示例：利用闪回功能快速恢复被删除的表 students_5 ；
1：实现 确认students_5 表是否已经被删除了
1 SCOTT@orcl> select * from students_5 ; 2 select * from students_5 3 * 4 ERROR at line 1: 5 ORA-00942: table or view does not exist 6 7 8 SCOTT@orcl>
2：从上面的查询结果看出，该表已经被删除。那么用户就要通过查询数据字典视图 recyclebin 来观察该表是否已经进入回收站
1 SCOTT@orcl> select object_name ,original_name from recyclebin where 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表
4：确认 表是否已经被恢复了
1 SCOTT@orcl> select object_name ,original_name from recyclebin where original_name ='STUDENTS_5'; 2 3 no rows selected 4 5 SCOTT@orcl> select * from students_5 ; 6 7 no rows selected 8 9 SCOTT@orcl>
1 ---如果在删除表的时候使用了 purge 则 该表是直接删除掉了而不会进入回收站。表无法被闪回 2 SCOTT@orcl> drop table students_5 cascade constraints purge; 3 4 Table dropped. 5 6 SCOTT@orcl> select object_name ,original_name from recyclebin where original_name ='STUDENTS_5'; 7 8 no rows selected 9 10 SCOTT@orcl> flashback table STUDENTS_5 TO before drop ; 11 flashback table STUDENTS_5 TO before drop 12 * 13 ERROR at line 1: 14 ORA-38305: object not in RECYCLE BIN 15 16 17 SCOTT@orcl>
6：修改表的状态
1 2 3 ----查看现在数据库表该 scott用户下的所有表的情况 4 SCOTT@orcl> select table_name,tablespace_name from 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 rows selected. 28 29 ---创建 表 students_5 ；从students_4 复制并创建students_5 30 31 SCOTT@orcl> create table students_5 as select * from students_3; 32 33 Table created. 34 35 ---查看表 students_5 的状态 36 SCOTT@orcl> select table_name,read_only from user_tables where table_name ='STUDENTS_5'; 37 38 TABLE_NAME REA 39 ------------------------------ --- 40 STUDENTS_5 NO 41 ---设置表 students_5 为只读状态 42 SCOTT@orcl> alter table students_5 read only ; 43 44 Table altered. 45 ---- 46 SCOTT@orcl> select table_name,read_only from user_tables where table_name ='STUDENTS_5'; 47 48 TABLE_NAME REA 49 ------------------------------ --- 50 STUDENTS_5 YES 51 52 SCOTT@orcl>
1 SCOTT@orcl> update students_5 set sex='男' where stuno >5000; 2 update students_5 set sex='男' where stuno >5000 3 * 4 ERROR at line 1: 5 ORA-12081: update operation not allowed on table "SCOTT"."STUDENTS_5" 6 7 8 SCOTT@orcl>
对于 ora_12081:不允许对表"SCOTT"."STUDENTS_5" 进行更新操作。其原因为该表是只读状态。不能进行数据的更新 插入 删除等操作。
1 [oracle@localhost ~]$ sqlplus / as sysdba; 2 3 SQL*Plus: Release 11.2.0.3.0 Production on Sat Mar 17 23:24:05 2018 4 5 Copyright (c) 1982, 2011, Oracle. All rights reserved. 6 7 8 Connected to: 9 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production 10 With the Partitioning, OLAP, Data Mining and Real Application Testing options 11 ----查询 所有的表空间名称 12 SYS@orcl> select tablespace_name from 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 rows selected. 28 --切换到scott用户 29 SYS@orcl> conn scott/scott; 30 Connected. 31 --查询 scott 用户下的所有表与表空间的对应关系 32 SCOTT@orcl> select table_name ,tablespace_name from 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 rows selected. 56 --把 表 students_5 的 从 tbs_test1 移动到 tbs_test3中去。 57 SCOTT@orcl> alter table students_5 move tablespace tbs_test_3; 58 59 Table altered. 60 61 SCOTT@orcl> select table_name ,tablespace_name from 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 rows selected. 85 86 ---查看其表的状态是否发生改变。而后变没有发生变化 87 SCOTT@orcl> select table_name ,read_only from user_tables where table_name ='STUDENTS_5'; 88 89 TABLE_NAME REA 90 ------------------------------ --- 91 STUDENTS_5 YES 92 93 SCOTT@orcl>
1 SCOTT@orcl> select table_name ,read_only from user_tables where table_name ='STUDENTS_5'; 2 3 TABLE_NAME REA 4 ------------------------------ --- 5 STUDENTS_5 YES 6 7 SCOTT@orcl> alter table students_5 read write; 8 9 Table altered. 10 11 SCOTT@orcl> select table_name ,read_only from user_tables where table_name ='STUDENTS_5'; 12 13 TABLE_NAME REA 14 ------------------------------ --- 15 STUDENTS_5 NO 16 17 SCOTT@orcl>
四：数据完整性和约束性
1： 非空约束
1 SCOTT@orcl> create table Books 2 2 ( 3 3 BookNo number(4) not null,--图书编号 4 4 BookName varchar2(20),--图书名称 5 5 Author varchar2(10),--作者 6 6 SalePrice number(9,2),--定价 7 7 PublisherNo varchar2(4) not null,--出版社编号 8 8 PublishDate date,--出版日期 9 9 ISBN varchar2(20) not null--ISBN 10 10 ); 11 12 Table created. 13 14 SCOTT@orcl> select table_name ,tablespace_name from 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 rows selected. 39 40 SCOTT@orcl>
1 2 SCOTT@orcl> select table_name ,tablespace_name from 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 rows selected. 27 28 SCOTT@orcl> desc books; 29 Name Null? Type 30 ----------------------------------------- -------- ---------------------------- 31 BOOKNO NOT NULL NUMBER(4) 32 BOOKNAME VARCHAR2(20) 33 AUTHOR VARCHAR2(10) 34 SALEPRICE NUMBER(9,2) 35 PUBLISHERNO NOT NULL VARCHAR2(4) 36 PUBLISHDATE DATE 37 ISBN NOT NULL VARCHAR2(20) 38 39 40 41 SCOTT@orcl> alter table books modify bookname not null; 42 43 Table altered. 44
1 SCOTT@orcl> desc books; 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 BOOKNO NOT NULL NUMBER(4) 5 BOOKNAME NOT NULL VARCHAR2(20) 6 AUTHOR VARCHAR2(10) 7 SALEPRICE NUMBER(9,2) 8 PUBLISHERNO NOT NULL VARCHAR2(4) 9 PUBLISHDATE DATE 10 ISBN NOT NULL VARCHAR2(20) 11 12 SCOTT@orcl> alter table books modify bookname null; 13 14 Table altered. 15 16 17 18 SCOTT@orcl> desc books; 19 Name Null? Type 20 ----------------------------------------- -------- ---------------------------- 21 BOOKNO NOT NULL NUMBER(4) 22 BOOKNAME VARCHAR2(20) 23 AUTHOR VARCHAR2(10) 24 SALEPRICE NUMBER(9,2) 25 PUBLISHERNO NOT NULL VARCHAR2(4) 26 PUBLISHDATE DATE 27 ISBN NOT NULL VARCHAR2(20) 28 29 SCOTT@orcl>
2：主键约束
1 2 SCOTT@orcl> select table_name ,tablespace_name from 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 rows selected. 27 28 SCOTT@orcl> create table Books_1 29 2 ( 30 3 BookNo number(4) not null,--图书编号 31 4 BookName varchar2(20),--图书名称 32 5 Author varchar2(10),--作者 33 6 SalePrice number(9,2),--定价 34 7 PublisherNo varchar2(4) not null,--出版社编号 35 8 PublishDate date,--出版日期 36 9 ISBN varchar2(20) not null,--ISBN 37 10 constraint BOOK_PK primary key (BookNo) 38 11 ); 39 40 Table created. 41 42 SCOTT@orcl> select table_name ,tablespace_name from 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 rows selected. 68 69 SCOTT@orcl>
1 SCOTT@orcl> desc books 2 Name Null? Type 3 ----------------------------------------- -------- ---------------------------- 4 BOOKNO NOT NULL NUMBER(4) 5 BOOKNAME VARCHAR2(20) 6 AUTHOR VARCHAR2(10) 7 SALEPRICE NUMBER(9,2) 8 PUBLISHERNO NOT NULL VARCHAR2(4) 9 PUBLISHDATE DATE 10 ISBN NOT NULL VARCHAR2(20) 11 --------------------- 使用 alter table.. . add 语句 为books 表添加主键约束。12 SCOTT@orcl> alter table books add constraint Books_PK primary key (bookno); 13 14 Table altered. 15 16 SCOTT@orcl> desc books; 17 Name Null? Type 18 ----------------------------------------- -------- ---------------------------- 19 BOOKNO NOT NULL NUMBER(4) 20 BOOKNAME VARCHAR2(20) 21 AUTHOR VARCHAR2(10) 22 SALEPRICE NUMBER(9,2) 23 PUBLISHERNO NOT NULL VARCHAR2(4) 24 PUBLISHDATE DATE 25 ISBN NOT NULL VARCHAR2(20) 26 ---------------------查找表的唯一性约束（包括名称，构成列）：27 SCOTT@orcl> select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name ='BOOKS' ; 28 29 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION 30 ------------ -------------- ---------------- -------------- ------------- 31 SCOTT BOOKS_PK BOOKS BOOKNO 1 32 33 34 SCOTT@orcl>
1 SCOTT@orcl> select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name ='BOOKS_1'; 2 3 OWNER CONSTRAINT_NAME TABLE_NAME COLUMN_NAME POSITION 4 ------------------------------------------------------------------------------------------------------------------ 5 SCOTT BOOK_PK BOOKS_1 BOOKNO 1 6 7 8 9 SCOTT@orcl> alter table books_1 drop constraint BOOK_PK; 10 11 Table altered. 12 13 SCOTT@orcl> select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name ='BOOKS_1'; 14 15 no rows selected 16 17 SCOTT@orcl>
3：唯一性约束
查询表的唯一性约束
1 HR@orcl> select column_name from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'U' and au.table_name = 'members'; 2 3 no rows selected
1 SCOTT@orcl> 2 SCOTT@orcl> select table_name,tablespace_name from 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 rows selected. 28 29 SCOTT@orcl> create table Members 30 2 ( 31 3 MemNo number(4) not null, --会员编号 32 4 MemName varchar2(20) not null, --会员名称 33 5 Phone varchar2(20), --联系电话 34 6 Email varchar2(30), --电子邮件地址 35 7 QQ varchar2(20) Constraint QQ_UK unique, --QQ号，并设置为UNIQUE约束 36 8 ProvCode varchar2(2) not null, --省份代码 37 9 OccuCode varchar2(2) not null, --职业代码 38 10 InDate date default sysdate, --入会日期 39 11 Constraint Mem_PK primary key (MemNo) --主键约束列为MemNo 40 12 ); 41 42 43 Table created. 44 45 SCOTT@orcl> SCOTT@orcl> select table_name,tablespace_name from 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 rows selected. 72 73 SCOTT@orcl>
1 SCOTT@orcl> insert into members(memno,memname,phone,email,qq,provcode,occucode) values('001','111','asfsd','afdaweew@WQRW.COM',null,'01','02'); 2 3 1 row created. 4 5 SCOTT@orcl> insert into members(memno,memname,phone,email,qq,provcode,occucode) values('001','111','asfsd','afdaweew@WQRW.COM',null,'01','02'); 6 insert into members(memno,memname,phone,email,qq,provcode,occucode) values('001','111','asfsd','afdaweew@WQRW.COM',null,'01','02') 7 * 8 ERROR at line 1: 9 ORA-00001: unique constraint (SCOTT.MEM_PK) violated 10 11 12 SCOTT@orcl>
1 2 SCOTT@orcl> alter table members add constraint Email_UK unique(email); 3 4 Table altered. 5 6 SCOTT@orcl> 7
1 SCOTT@orcl> alter table members drop constraint Email_UK; 2 3 Table altered. 4 5 SCOTT@orcl> 6
4： 外键约束
查看表的所有外键约束
1 HR@orcl> select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 'employess_temp'; 2 3 no rows selected 41 4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）： 2 select * from user_constraints c where c.constraint_type = 'R' and c.table_name = 表名 3 外键约束的列名： 4 select * from user_cons_columns cl where cl.constraint_name = 外键名称 5 引用表的键的列名： 6 select * from user_cons_columns cl where cl.constraint_name = 外键引用表的键名
1 SCOTT@orcl> conn hr/hr 2 Connected. 3 HR@orcl> create table employees_temp 4 2 as select * from employees 5 3 where department_id=30; 6 7 alter table employees_temp 8 add constraint temp_departid_fk 9 foreign key(department_id) 10 references departments(department_id); 11 12 13 14 Table created. 15
1 alter table employees_temp add constraint temp_departid_fk foreign key(department_id) references departments;
1 HR@orcl> insert into employees_temp(employee_id,last_name,email,job_id,hire_date,department_id) values(9527,'aaa','aadfae@adfa.com','astew',sysdate,9999); 2 insert into employees_temp(employee_id,last_name,email,job_id,hire_date,department_id) values(9527,'aaa','aadfae@adfa.com','astew',sysdate,9999) 3 * 4 ERROR at line 1: 5 ORA-02291: integrity constraint (HR.TEMP_DEPARTID_FK) violated - parent key not 6 found 7 8 9 HR@orcl>
1 HR@orcl> create table departments_temp as select * from departments where department_id=30; 2 3 Table created. 4 5 HR@orcl> alter table departments_temp add primary key(department_id) ; 6 7 Table altered. 8 9 HR@orcl>
1 alter table employees_temp add consrtraint temp_departid_fk2 foregin key(department_id) references departments_temp on delete casade;
1 2 HR@orcl> select count(*) from employees_temp where department_id=30; 3 4 COUNT(*) 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> delete from departments_temp where department_id=30; 7 8 1 row deleted. 9 10 HR@orcl> select count(*) from departments_temp; 11 12 COUNT(*) 13 ---------- 14 0 15 16 HR@orcl>
1 alter table employees_temp drop constraint temp_departid_fk2;
5：禁用与激活约束
1：在定义约束时被禁用
1 HR@orcl> create table Student 2 2 ( 3 3 StuCode varchar2(4) not null, 4 4 StuName varchar2(10) not null, 5 5 Age int constraint Age_CK check (age > 0 and age <120) disable, 6 6 Province varchar2(20), 7 7 SchoolName varchar2(50) 8 8 ); 9 10 Table created. 11 12 HR@orcl>2：禁用已经存在的约束
1 HR@orcl> select t.*,c.COMMENTS from user_tab_columns t,user_col_comments c where t.table_name = c.table_name and t.column_name = c.column_name and t.table_name ='employees_temp'; 2 3 no rows selected 4 5 HR@orcl> alter table employees_temp disable constraint temp_departid_fk; 6 7 Table altered. 8 9 HR@orcl>
1 SCOTT@orcl> alter table books disable constraint BOOKS_PK; 2 3 Table altered. 4 5 SCOTT@orcl> 6
1 SCOTT@orcl> select * from books; 2 3 no rows selected 4 5 SCOTT@orcl> desc books; 6 Name Null? Type 7 ----------------------------------------- -------- ---------------------------- 8 BOOKNO NOT NULL NUMBER(4) 9 BOOKNAME VARCHAR2(20) 10 AUTHOR VARCHAR2(10) 11 SALEPRICE NUMBER(9,2) 12 PUBLISHERNO NOT NULL VARCHAR2(4) 13 PUBLISHDATE DATE 14 ISBN NOT NULL VARCHAR2(20) 15 16 SCOTT@orcl> insert into books (bookno,PUBLISHERNO,ISBN) values(1,'asdf','2134234'); 17 18 SCOTT@orcl> insert into books (bookno,PUBLISHERNO,ISBN) values(1,'asdf','2134234'); 19 20 1 row created. 21 22 SCOTT@orcl> insert into books (bookno,PUBLISHERNO,ISBN) values(1,'asdf','2134234'); 23 24 1 row created. 25 26 SCOTT@orcl> insert into books (bookno,PUBLISHERNO,ISBN) values(1,'asdf','2134234'); 27 28 1 row created. 29 30 SCOTT@orcl>
1 SCOTT@orcl> alter table books enable constraint BOOKS_PK; 2 alter table books enable constraint BOOKS_PK 3 * 4 ERROR at line 1: 5 ORA-02437: cannot validate (SCOTT.BOOKS_PK) - primary key violated 6 7 8 SCOTT@orcl> delete from books; 9 10 3 rows deleted. 11 12 SCOTT@orcl> commit; 13 14 Commit complete. 15 16 SCOTT@orcl> alter table books enable constraint BOOKS_PK; 17 18 Table altered. 19 20 SCOTT@orcl> insert into books (bookno,PUBLISHERNO,ISBN) values(1,'asdf','2134234'); 21 22 1 row created. 23 24 SCOTT@orcl> insert into books (bookno,PUBLISHERNO,ISBN) values(1,'asdf','2134234'); 25 insert into books (bookno,PUBLISHERNO,ISBN) values(1,'asdf','2134234') 26 * 27 ERROR at line 1: 28 ORA-00001: unique constraint (SCOTT.BOOKS_PK) violated 29 30 31 SCOTT@orcl>
6：删除约束
———————————————————————————————————————————————————————————————————————————————————————————————————————
485
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
