Les09-创建和管理表

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,九,.,创建和管理表,目标,本课结束后，应该掌握以下内容,:,描述主要的数据库对象,查看表结构,列出主要的列数据类型,创建表,掌握如何在建表的同时建立约束,描述模式对象是如何工作的,对象描述,表存储的基本单元，由行和列组成,视图一个逻辑的数据集，数据来自一个或者多个表,序列 产生主键值,索引目的提高查询的性能,同义词 给出对象的别名,数据库对象,必须以字母开始,可以是,1-30,个字符的长度,仅能包括字符：,AZ, az, 09, _, $,和,#,不能与本用户拥有的其他对象重名,不能是一个,Oracle,服务器的保留字,命名习惯,CREATE ,GLOBAL TEMPORARY, TABLE ,schema,.,table,(,column,datatype,DEFAULT,expr, .);,CREATE TABLE,语句,必须有,:,CREATE TABLE,的权限,拥有存储空间,你可以指定,表名称,列名称，列长度，列类型,SQL CREATE TABLE dept 2(deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13);,Table created.,SQL DESCRIBE dept,Name Null? Type,- - -,DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13),确认创建的结果,创建表,引用其他用户的表,属于其他用户的表，不在当前用户的模式下,使用这些表的属主名作为前缀,USERB,USERA,SELECT *,FROM userB.employees;,SELECT *,FROM userA.employees;,在插入期间可以指定默认值,文字值，表达式，或者,SQL,函数是合法值,其他列的名或者伪列是非法的,默认的数据类型必须匹配列的数据类型,DEFAULT,选项,. hire_date DATE DEFAULT SYSDATE, .,CREATE TABLE hire_dates (id NUMBER(8),hire_date DATE DEFAULT SYSDATE,);,Table created.,用户表,由用户创建和维护的一系列表的集合,包含用户的信息,数据字典,由,Oracle,服务器创建和维护的一系列表的集合,包含数据库的信息,Oracle,数据库中的表,数据类型描述,VARCHAR2(,size,),变长字符数据，,size,为最大长度,CHAR(,size,),定长字符数据，,size,为最大长度,NUMBER(,p,s),数字类型，,p,为精度，,s,为刻度,DATE,日期和时间值,LONG,可以保存最大长度为,2GB,的字符数据,CLOB,可以保存最大长度为,4GB,的单字节字符数据,RAW and LONG RAW,存储二进制数据,BLOB,最大为,4GB,的二进制数据类型,BFILE,在外部文件中存储的二进制数据,;,最大为,4GB,数据类型,日期数据类型,几种日期数据类型,数据类型,描述,TIMESTAMP,秒带有小数的日期,INTERVAL YEAR TO MONTH,存储年和月的间隔,INTERVAL DAY TO SECOND,存储天，小时，分钟和秒的间隔,日期数据类型,TIMESTAMP,是,DATE,数据类型的扩展,存储,DATE,日期类型的年，月，日数据类型加上小时，分钟，和带有小数的秒,可以在,TIMESTAMP,上选择使用和不使用时区,TIMESTAMP(fractional_seconds_precision),TIMESTAMP(fractional_seconds_precision),WITH TIME ZONE,TIMESTAMP(fractional_seconds_precision),WITH LOCAL TIME ZONE,日期数据类型,INTERVAL,YEAR,TO,MONTH,数据类型使用年和月日期字段存储一段时间,INTERVAL DAY TO SECOND,数据类型使用天，小时，分钟，秒存储一段时间,INTERVAL YEAR (year_precision) TO MONTH,INTERVAL DAY (day_precision),TO SECOND (fractional_seconds_precision),INTERVAL DAY TO SECOND,INTERVAL DAY TO SECOND,数据类型使用天，小时，分钟，秒存储一段时间,INTERVAL 4 5:12:10.222 DAY TO SECOND(3),表示：,4 days, 5 hours, 12 minutes, 10. 222 seconds,INTERVAL 4 5:12 DAY TO MINUTE,表示：,4 days, 5 hours and 12 minutes.,INTERVAL 400 5 DAY(3) TO HOUR,表示：,400 days 5 hours.,INTERVAL 11:12:10.2222222 HOUR TO SECOND(7),表示：,11 hours, 12 minutes, and 10.2222222 seconds.,约束,约束是强加到表中的列上的规则,提供了以下五种约束,:,NOT NULL,UNIQUE,PRIMARY KEY,FOREIGN KEY,CHECK,约束规则,创建约束时，可以对约束进行命名，也可以使用,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,constraint,(No row can containa null value forthis column.),Absence of,NOT NULL,constraint (Any row can contain a null value for this column.),NOT NULL,constraint,UNIQUE,约束,EMPLOYEES,UNIQUE,约束,INSERT INTO,不允许，已经存在,允许,UNIQUE,约束,定义,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,不允许空值,不允许重复值,FOREIGN KEY,约束,DEPARTMENTS,EMPLOYEES,FOREIGNKEY,INSERT INTO,不允许,9,允许,PRIMARYKEY,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,., salaryNUMBER(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), department_id NUMBER(4),CONSTRAINT emp_dept_fk REFERENCES,departments (department_id);,UPDATE employees,*,ERROR at line 1:,ORA-02291: integrity constraint (HR.EMP_DEPT_FK) violated - parent key not found,UPDATE employees,SET department_id = 55,WHERE department_id = 110;,违反约束,55,部门不存在,违反约束,不能删除外键所引用的值,DELETE FROM departments,WHERE department_id = 60;,DELETE FROM departments,*,ERROR at line 1:,ORA-02292: integrity constraint (HR.EMP_DEPT_FK) violated - child record found,使用子查询创建表,通过使用,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;,Table created.,使用子查询创建表,DESCRIBE dept80,ALTER TABLE,table,ADD (,column datatype,DEFAULT,expr,column datatype,.);,ALTER TABLE,table,MODIFY (,column datatype,DEFAULT,expr,column datatype,.);,使用,ALTER TABLE,命令来进行,:,增加一个新列,更改一个现存的列,为新列定义缺省的值,删除一列,ALTER TABLE,语句,DEPT30,EMPNO ENAME ANNSAL HIREDATE,- -,7698BLAKE 3420001-MAY-81,7654MARTIN 1500028-SEP-81,7499ALLEN 1920020-FEB-81,7844TURNER 1800008-SEP-81,.,DEPT30,EMPNO ENAME ANNSAL HIREDATE,- -,7698BLAKE 3420001-MAY-81,7654MARTIN 1500028-SEP-81,7499ALLEN 1920020-FEB-81,7844TURNER 1800008-SEP-81,.,JOB,JOB,新列,增加一列,“,为表,DEPT30,增加一个新列,”,EMPNO ENAME ANNSAL HIREDATE JOB,- - - - -,7698 BLAKE 34200 01-MAY-81,7654 MARTIN 15000 28-SEP-81,7499 ALLEN 19200 20-FEB-81,7844 TURNER 18000 08-SEP-81,.,6 rows selected.,SQL ALTER TABLE dept30,2 ADD (job VARCHAR2(9);,Table altered.,新列已经增加，成为最后一个列,使用,ADD,子句来增加一个列,增加一列,SQL ALTER TABLEdept30,2 MODIFY(ename VARCHAR2(15);,Table altered.,更改一个现存的列,可以更改一个现存列的数据类型、长度、以及缺省值,.,对于缺省值的修改只影响到以后会修改的列,.,SQL ALTER TABLEdept30,2 DROP COLUMN job ;,Table altered.,使用,DROP COLUMN,来丢弃一个表中将来不使用的列,丢弃一个列,SQL DROP TABLE dept30;,Table dropped.,表中所有的数据和结构都被删除了,.,所有未决的事务都被提交,.,此表上所有的索引全部被删除,.,此表上所有的约束全部被删除,.,这个操作是不能回滚的,.,丢弃一个表,SQL RENAME dept TO department;,Table renamed.,改变一个对象的名称,更改表、视图、同义词、序列等对象的名称,执行,RENAME,命令就可以了,操作用户必须是对象的拥有者,SQL TRUNCATE TABLE department;,Table truncated.,截断一个表,TRUNCATE TABLE,语句功能,:,移除表中所有的数据行,释放表中的存储空间,在执行,TRUNCATE,语句后，不能对该操作回滚。,可以用,DELETE,语句来做相同的操作，不过,DELETE,并不立即释放空间，因而可以回滚,SQL COMMENT ON TABLE emp,2 IS Employee Information;,Comment created.,为一个表增加注释,可以使用,COMMENT,语句为一个表，或者表中的某一列增加注释,注释可以通过数据字典视图进行查询,.,ALL_COL_COMMENTS,USER_COL_COMMENTS,ALL_TAB_COMMENTS,USER_TAB_COMMENTS,语句描述,CREATE TABLE,创建一个表,ALTER TABLE,更改表结构,DROP TABLE,废弃一个表，包括它的结构和数据行,RENAME,更改对象的名称,TRUNCATE,从表中移除所有的行，并将这些空间释放掉,COMMENT,为一个表或者视图增加注释,总结,