数据表对象

前言：

一：数据表概述

二：创建数据表

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表

  1 SCOTT@orcl> flashback table STUDENTS_5 TO before drop ;
  2 
  3 Flashback complete.
  4 
  5 SCOTT@orcl>

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
  4

  1 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：删除约束

———————————————————————————————————————————————————————————————————————————————————————————————————————

posted @ 2018-03-12 23:33 一品堂.技术学习笔记阅读(721) 评论(0) 编辑收藏举报

刷新页面返回顶部

✧*꧁一品堂.技术学习笔记꧂*✧.

数据表对象

前言：

一：数据表概述

二：创建数据表

1：数据表的逻辑结构

1：字符类型

2：数值类型

3：日期时间类型

4:LOB类型

5：rowid 数据类型