SQL 基础之DDL语句创建和管理表（十四）

loyalxuan

　　数据库对象
Object描述表基本的数据存储集合，由行和列组成View从一张表或多张表中抽出的 逻辑上相关的数据集合序列生成规律的数值index 索引提高查询性能Synonym 别名给对象起的别名　　表名和列名注意事项:

• 　　必须以字母开头
  
• 　　必须在 1–30 个字符之间
  
• 　　必须只能包含 A–Z, a–z, 0–9, _, $, 和 #
  
• 　　必须不能和用户定义的其他对象重名
  
• 　　必须不能是Oracle的保留字
  
• 　　表的列的数量最多1000个
  

　　CREATE TABLE  语句
　　必须具有：
　　– CREATE TABLE 权限
　　– 存储空间
　　必须指定：
　　– 表名
　　– 字段名称，字段数据类型和字段大小
　　语法：
　　CREATE TABLE [schema.]table_name (column datatype [DEFAULT expr][, ...]);
　　引用其他用户的表

• 　　其他用户定义的表不在当前用户的方案中
  
• 　　应该使用用户名作为前缀，引用其他用户定义的对象
  

　　DEFAULT  选项
　　在插入的过程中，为列指定一个默认值。
　　... hire_date DATE DEFAULT SYSDATE, ...
　　字符串, 算数表达式, 或SQL 函数都是合法的
　　其它列的列名和 伪列 是非法的
　　默认值必须满足列的数据类型定义
　　例如：CREATE TABLE hire_dates  (id NUMBER(8), hire_date DATE DEFAULT SYSDATE);
　　创建表
　　create table dept
　　(deptno number(2),
　　dname varchar2(14),
　　loc varchar2(13),
　　create_date date default sysdate);
　　确认创建是否成功：
　　desc dept
　　Name   Null?    Type
　　----------------------------------------- -------- ----------------------------
　　DEPTNO     NUMBER(2)
　　DNAME    VARCHAR2(14)
　　LOC    VARCHAR2(13)
　　CREATE_DATE    DATE
　　数据类型
数据类型描述VARCHAR2(size)可变长字符数据CHAR(size)定长字符数据NUMBER(p,s)可变长数值数据 DATE日期和时间值LONG可变长字符数据(最大可达到 2 GB)CLOB字符数据(最大可达到 4 GB)RAW and LONG RAW二进制数据BLOB二进制数据 (最大可达到 4 GB)BFILE存储外部文件的二进制数据 (最大可达到 4 GB) ROWIDbase-64 系统编码的行唯一地址　　日期数据类型
数据类型TIMESTAMP带小数秒的日期型INTERVAL YEAR TO MONTH作为年和月的时间间隔存储INTERVAL DAY TO SECOND作为天、小时、分和秒的时间间隔存储　　包含的约束

• 　　约束为表级的强制规则。
  
• 　　如果表有依赖关系，约束能防止表的删除。
  
• 　　下面是有效的约束类型：
  

　　– NOT NULL
　　– UNIQUE
　　– PRIMARY KEY
　　– FOREIGN KEY
　　– CHECK
　　约束准则

• 　　如果不指定约束名 Oracle server 自动按照 SYS_Cn 的格式指定约束名
  
• 　　在什么时候创建约束:
  

　　– 建表的同时
　　– 建表之后

• 　　可以在表级或列级定义约束
  
• 　　可以通过数据字典视图查看约束
  

　　定义约束
　　语法：
　　create table [schema.]table
　　(column datatype [default expr]
　　[column_constraint],
　　...
　　[table_constraint][,...]);
　　列级约束语法
　　column [CONSTRAINT constraint_name] constraint_type,
　　表级约束语法：
　　column,...
　　[CONSTRAINT constraint_name] constraint_type
　　(column, ...),
　　列级约束示例
　　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)
　　constraint emp_emp_id_pk primary key,
　　first_name varchar2(20),
　　...);
　　表级约束示例：
　　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 约束
　　确保该列不允许空值：

　　UNIQUE 约束

　　可以定义在表级或者列级：
　　create table employees(
　　employee_id number(6),
　　last_name varchar2(25) not null,
　　email varchar2(25),
　　salary number(8,2),
　　commission_pct number(2,2),
　　hire_date date not null,
　　...
　　constraint emp_email_uk unique(email));
　　PRIMARY KEY  约束

　　FOREIGN KEY  约束

　　可以定义在表级或者列级：
　　create table employees(
　　employee_id number(6),
　　last_name varchar2(25) not null,
　　email varchar2(25),
　　salary number(8,2),
　　commission_pct number(2,2),
　　hire_date date not null,
　　...
　　department_id number(4),
　　constraint emp_dept_fk foreign key (department_id)
　　references departments(department_id),
　　constraint emp_email_uk unique(email));
　　FOREIGN KEY约束：关键字

• 　　FOREIGN KEY：在表级指定子表中的列
  
• 　　REFERENCES：标识在父表中的列
  
• 　　ON DELETE CASCADE：当父表中的列被删除时，子表中相对应的列也被删除
  
• 　　ON DELETE SET NULL：子表中相应的列置空
  

　　CHECK 约束
　　定义每一行必须满足的条件
　　以下的表达式是不允许的:
　　– 出现CURRVAL, NEXTVAL, LEVEL, 和ROWNUM 伪列
　　– 使用 SYSDATE, UID, USER, 和 USERENV 函数
　　– 在查询中涉及到其它行的值
　　CREATE TABLE:  示例
　　create table employees
　　( employee_id number(6)
　　constraint emp_employee_id primary key
　　, first_name varchar2(20)
　　, last_name varchar2(25)
　　constraint emp_last_name_nn not null
　　, email varchar2(25)
　　constraint emp_email_nn not null
　　constraint emp_email_uk unique
　　, phone_number varchar2(20)
　　, hire_date date
　　constraint emp_hire_date_nn not null
　　, job_id varchar2(10)
　　constraint emp_job_nn not null
　　, salary number(8,2)
　　constraint emp_salary_ck check (salary>0)
　　, commission_pct number(2,2)
　　, manager_id number(6)
　　constraint emp_manager_fk references
　　employees (employee_id)
　　, department_id number(4)
　　constraint emp_dept_fk references
　　departments (department_id));
　　违反约束
　　update employees
　　set department_id = 55
　　where department_id = 110;

　　主键行被另一张表外键引用，那么您不能删除主键行

　　使用子查询创建表
　　使用 CREATE TABLE 语句和 AS subquery 选项，将创建表和插入数据结合起来完成。
　　CREATE TABLE table  [(column, column...)]  AS subquery;
　　指定的列和子查询中的列要一一对应。
　　通过列名和默认值定义列。
　　create table dept80
　　as
　　select employee_id, last_name,
　　salary*12 annsal,
　　hire_date
　　from employees
　　where department_id = 80;
　　ALTER TABLE 语句

　　使用>

• 　　添加一个新列
  
• 　　修改现有的列定义
  
• 　　新的列定义默认值
  
• 　　删除一列
  
• 　　重命名列
  
• 　　将表更改为只读状态
  

　　Read-Only 表

　　使用>　　表修改过程中阻止DDL和DML的更改
　　将表改回到 读/写 模式
　　alter table employees read only;
　　-- perform table maintenance and then
　　-- return table back to read/write mode
　　alter table employees read write;
　　删除表

• 　　表移动到回收站
  
• 　　使用 PURGE 子句可彻底删除表
  
• 　　表上依赖的对象失效同时删除对象权限
  

　　drop table dept;