Lesson10 Using DDL Statements to Create and Manage Tables

Lesson 10
1. 命名规则
1）表名和列名必须：
- 以字母开头
- 1~30个字符长度
- 仅包含A-Z,a-z,0-9，_,$,#
- 不能与同一个用户的其他对象重复
- 不能是oracle server保留字
2）准则
- 表名是大小写不敏感的，用双引号括起来的表名敏感


2.CREATE TABLE语句
前提
1）必须有CREATE TABLE权限
2）存储区域
CREATE TABLE [schema.]table
              (column datatype [DEFAULT expr][,...]);
              
3.引用其他用户的表
4.DEFAULT选项
1）文字值，表达式，SQL函数是合法值
2）其他列的列名，伪劣是非法值
3）DEFAULT的数据类型必须匹配列的类型


5.数据类型
VARCHAR2(size) size：最大值size必须被定义，1~4000；
CHAR [(size)]：可不定义size，默认值1，最大值2000
NUMBER [(p,s)]：精度p，1~38；范围s，-84~127；
DATE
LONG: 可变长度字符串数据，最大2GB；
             当表由子查询创建时，LONG列无法复制
             无法用在GROUP BY或ORDER BY子句中
             每个表只能用一个LONG
             LONG上不能定义约束
             你可能想用使用CLOB而不是LONG
CLOB: 字符串数据，最大4GB
BLOB: binary date，最大4GB


6.日期时间数据类型(f2)
TIMESTAMP: 日期与小数秒
INTERVAL YEAR TO MONTH:存储的年和月的时间间隔
INTERVAL DAY TO SECOND:存储天，小时，分，秒的时间间隔


7.约束准则
1）你可以给约束起名或系统以SYS_Cn形式定义
2）如下情况需要创建约束：
    - 在创建表时
    - 在创建表后
3）在列级别或表级别定义约束。功能上相同。
CREATE TABLE [schema.]table
(column datatype [DEFAULT expr] 
[column_constraint],
...
[table_constraint][,...]);
列级别约束：
column [CONSTRAINT constraint_name] constraint_type,


CREATE TABLE employees(
  employee_id  NUMBER(6) CONSTRAINT emp_emp_id_pk PRIMARY KEY,
  first_name VARCHAR2(20),
...);
表级别约束：
column,...
[CONSTRAINT constraint_name] constraint_type (column, ...),


CREATE TABLE employees
           ( employee_id NUMBER(6), 
             first_name VARCHAR2(20),
             ...
             job_id VARCHAR2(10) NOT NULL,
             CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID));
【注意】
- NOT NULL约束必须定义在列级别
- 适用于一个以上的列必须在表级别进行定义。


8.UNIQUE约束
1）如果约束不止一列组成，这组列被称为组合唯一键
2）UNIQUE约束允许输入为NULL，除非显式指定NOT NULL。NULL值不被认为等于任何值，所以NULL值满足UNIQUE
3）由于搜索机制，对于超过一列的UNIQUE约束，即使有一列为NULL，如果另一列有重复值，也是会被系统认为违反约束。
4）oracle强制隐式创建一个UNIQUE INDEX在唯一键上。


9.主键约束
1）NOT NULL
2)  UNIQUE ,且自动隐式创建唯一索引


10.外键约束
1）外键值必须匹配父表中已经存在的值或为NULL
2）外键纯粹基于逻辑上得数据值，而不是物理上得指针
3）外键约束可以被定义在列级别或表级别上。组合外键必须创建在表级别上。
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id) references departments(department_id)
4）外键约束关键字：
- FOREIGN KEY 在表级别约束上定义子表的列
- REFERENCES 识别父表的名字和列
- ON DELETE CASCADE 当父表中的一行被删除，在子表的依赖行也被删除
- ON DELETE SET NULL 当父表中的一行被删除，在子表的依赖行改为NULL
没有如上两个关键字，父表中的行不允许被删除，如果他们还存在于子表中的话。


10.CHECK约束
1）定义每行必须满足的条件
2）下列表达式不允许：
   -  引用CURRVAL,NEXTVAL,LEVEL,ROWNUM
   -  调用SYSDATE,UID,USER,USERENV函数
   -  查询调用了其他行的其他值
3）单列可以有多个CHECK
4）CHECK约束可以定义在列级别或表级别


11.违反约束
1）如果在子表中插入父表没有的值，则会报错02291
2）如果在父表中删除子表还存在的值，则会报错02292


12.使用子查询创建表
1）准则
   -  表是由指定的列名和查询返回的行创建的
   -  列定义仅包含列名和默认值
   -  如果给定列规范，列数必须等于在子查询的SELECT列表中的列数。
   -  如果给定列没有规范，列名必须与子查询中SELECT列表的列名相同。
   -  列的数据类型和NOT NULL约束会传递给新表。
   【注意】仅仅显式的NOT NULL约束会被继承。
                 主键列不会传递NOT NULL特征给新表。
                 任何其他的约束规则不会传递给新表。
   -  如果子查询中有表达式，必须给表达式起一个别名。才能传递给新表。
SYS@VERO> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where TABLE_NAME='X1';


CONSTRAINT_NAME       C TABLE_NAME
------------------------------ - ------------------------------
SYS_C0011203       C X1


SYS@VERO> select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME from dba_constraints where TABLE_NAME='X';


CONSTRAINT_NAME       C TABLE_NAME
------------------------------ - ------------------------------
SYS_C0011198       P X
UK       U X
SYS_C0011196       C X
FK       R X


13. READ-ONLY
ALTER TABLE XX READ ONLY;
如果改为READ ONLY模式，则：
1）不能在上面做DML语句操作
2）不能做select 。。。for uupdate操作
3）允许操作表上面的索引
4）可以执行DROP TABLE操作（因为drop table只操作数据字典）


14. DROP TABLE
1）所有的数据会被删除到回收站，除非加purge参数
2）任何视图和同义词会保留，但无效
3）任何pending的事务会commit
4）只有表创建者或拥有DROP ANY TABLE权限的用户才能操作