09_01-创建和管理表剖析

使用DDL语句创建和管理表,课程目标,通过本章学习，您将可以: 主要的数据库对象分类 查看表结构 列出列的数据类型 创建一个简单的表 创建表时如何创建约束 描述对象如何工作,Lesson Agenda,数据库对象 命名规则 CREATE TABLE 语句： 访问其他用户的表 DEFAULT 选项 数据类型 约束概述: NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK 约束 使用子查询创建表 ALTER TABLE 只读表 DROP TABLE 语句,数据库对象,从一张表或多张表中抽出的逻辑上相关的数据集合。,View,生成规律的数值,Sequence,基本的数据存储集合，由行和列组成。,Table,给对象起的别名,Synonym,提高查询性能,Index,Description,Object,命名规则,表名和列名: 必须以字母开头 必须在 130 个字符之间 必须只能包含 AZ, az, 09, _, $, 和 # 必须不能和用户定义的其他对象重名 必须 不能是Oracle 的保留字,Lesson Agenda,数据库对象 命名规则 CREATE TABLE 语句： 访问其他用户的表 DEFAULT 选项 数据类型 约束概述: NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK 约束 使用子查询创建表 ALTER TABLE 只读表 DROP TABLE 语句,CREATE TABLE 语句,您必须具有： CREATE TABLE 权限 存储空间 您必须指定： 表名 字段名称，字段数据类型和字段大小,CREATE TABLE schema.table (column datatype DEFAULT expr, .);,引用其他用户的表,其他用户定义的表不在当前用户的方案中 应该使用用户名作为前缀，引用其他用户定义的对象,USERB,USERA,SELECT * FROM userB.employees;,SELECT * FROM userA.employees;,DEFAULT 选项,在插入的过程中，为列指定一个默认值。 字符串, 算数表达式, 或SQL 函数都是合法的 其它列的列名和伪列是非法的 默认值必须满足列的数据类型定义,. hire_date DATE DEFAULT SYSDATE, .,CREATE TABLE hire_dates (id NUMBER(8), hire_date DATE DEFAULT SYSDATE);,创建表,创建表： 确认：,DESCRIBE dept,CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), create_date DATE DEFAULT SYSDATE);,Lesson Agenda,数据库对象 命名规则 CREATE TABLE 语句： 访问其他用户的表 DEFAULT 选项 数据类型 约束概述: NOT NULL, PRIMARY KEY, FOREIGN KEY, CHECK 约束 使用子查询创建表 ALTER TABLE 只读表 DROP TABLE 语句,数据类型,二进制数据,RAW and LONG RAW,二进制数据 (最大可达到 4 GB),BLOB,存储外部文件的二进制数据 (最大可达到 4 GB),BFILE,日期和时间值,DATE,可变长字符数据(最大可达到 2 GB),LONG,字符数据(最大可达到 4 GB),CLOB,base-64 系统编码的行唯一地址,ROWID,定长字符数据,CHAR(size),可变长数值数据,NUMBER(p,s),可变长字符数据,VARCHAR2(size),Description,Data Type,日期数据类型,您可以使用多种日期数据类型：,作为年和月的时间间隔存储,INTERVAL YEAR TO MONTH,作为天、小时、分和秒的时间间隔存储,INTERVAL DAY TO SECOND,带小数秒的日期型,TIMESTAMP,Description,Data Type,Lesson Agenda,数据库对象 命名规则 CREATE TABLE 语句： 访问其他用户的表 DEFAULT 选项 数据类型 约束概述: NOT NULL,PRIMARY KEY,FOREIGN KEY, CHECK 约束 使用子查询创建表 ALTER TABLE 只读表 DROP TABLE 语句,包含的约束,约束为表级的强制规则。 如果表有依赖关系，约束能防止表的删除。 下面是有效的约束类型： 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, .),column CONSTRAINT constraint_name constraint_type,定义约束,列级约束示例： 表级约束示例：,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);,1,2,NOT NULL 约束,确保该列不允许空值：,NOT NULL 约束(Primary Key 强制 NOT NULL 约束),没有NOT NULL 约束 (任何行在此列都能为空值),NOT NULL 约束,UNIQUE 约束,EMPLOYEES,UNIQUE 约束,INSERT INTO,不允许：已经存在了,允许的,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 约束,DEPARTMENTS,PRIMARY KEY,INSERT INTO,不允许(空值),不允许(50 已经存在),FOREIGN KEY 约束,DEPARTMENTS,EMPLOYEES,FOREIGN KEY,INSERT INTO,不允许(9 不存在),允许,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 函数 在查询中涉及到其它行的值,., salary NUMBER(2) CONSTRAINT emp_salary_min CHECK (salary 0),.,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 (salary0) , 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;,违反约束,Department 55 does not exist.,违反约束,主键行被另一张表外键引用，那么您不能删除主键行,DELETE FROM departments WHERE department_id = 60;,Lesson Agenda,数据库对象 命名规则 CREATE TABL