Les09-创建和管理表

九九.创立和管理表创立和管理表目标目标l本课结束后，应该掌握以下内容:l描述主要的数据库对象l查看表结构l列出主要的列数据类型l创立表l掌握如何在建表的同时建立约束l描述模式对象是如何工作的对象对象描述描述表表存储的根本单元，由行和列组成存储的根本单元，由行和列组成视图视图一个逻辑的数据集，数据来自一个或者多个表一个逻辑的数据集，数据来自一个或者多个表序列序列 产生主键值产生主键值索引索引目的提高查询的性能目的提高查询的性能同义词同义词 给出对象的别名给出对象的别名数据库对象数据库对象l 必须以字母开始必须以字母开始l 可以是可以是1-30个字符的长度个字符的长度l 仅能包括字符：仅能包括字符：AZ,az,09,_,$,和和#l 不能与本用户拥有的其他对象重名不能与本用户拥有的其他对象重名l 不能是一个不能是一个 Oracle效劳器的保存字效劳器的保存字命名习惯命名习惯CREATE GLOBAL TEMPORARY TABLE schema.table (column datatype DEFAULT expr,.);CREATE TABLECREATE TABLE语句语句l 必须有必须有:lCREATE TABLE 的权限的权限l拥有存储空间拥有存储空间l你可以指定你可以指定l表名称表名称l列名称，列长度，列类型列名称，列长度，列类型 SQL CREATE TABLE dept 2(deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13);SQL DESCRIBE dept Name Null?Type-DEPTNO NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)确认创立的结果确认创立的结果创立表创立表引用其他用户的表引用其他用户的表l属于其他用户的表，不在当前用户的模式下l使用这些表的属主名作为前缀USERBUSERASELECT*FROM userB.employees;SELECT*FROM userA.employees;l在插入期间可以指定默认值l文字值，表达式，或者SQL函数是合法值l其他列的名或者伪列是非法的l默认的数据类型必须匹配列的数据类型DEFAULTDEFAULT选项选项.hire_date DATE DEFAULT SYSDATE,.CREATE TABLE hire_dates (id NUMBER(8),hire_date DATE DEFAULT SYSDATE);Table created.l用户表用户表l由用户创立和维护的一系列表的集合由用户创立和维护的一系列表的集合l包含用户的信息包含用户的信息l数据字典数据字典l由由Oracle 效劳器创立和维护的一系列表的集合效劳器创立和维护的一系列表的集合l包含数据库的信息包含数据库的信息OracleOracle数据库中的表数据库中的表数据类型数据类型描述描述VARCHAR2(size)变长字符数据，变长字符数据，size为最大长度为最大长度CHAR(size)定长字符数据，定长字符数据，size为最大长度为最大长度NUMBER(p,s)数字类型，数字类型，p为精度，为精度，s为刻度为刻度DATE 日期和时间值日期和时间值LONG 可以保存最大长度为可以保存最大长度为 2GB的字符数据的字符数据CLOB可以保存最大长度为可以保存最大长度为 4GB的单字节字符的单字节字符数据数据RAW and LONG RAW 存储二进制数据存储二进制数据BLOB最大为最大为 4GB的二进制数据类型的二进制数据类型BFILE在外部文件中存储的二进制数据在外部文件中存储的二进制数据;最大为最大为4GB数据类型数据类型日期数据类型日期数据类型l 几种日期数据类型几种日期数据类型数据类型数据类型 描述描述TIMESTAMP秒带有小数的日期秒带有小数的日期INTERVAL YEAR TO MONTH存储年和月的间隔存储年和月的间隔INTERVAL DAY TO SECOND存储天，小时，分钟和秒的间隔存储天，小时，分钟和秒的间隔日期数据类型日期数据类型lTIMESTAMP 是DATE数据类型的扩展l存储DATE日期类型的年，月，日数据类型加上小时，分钟，和带有小数的秒l可以在TIMESTAMP上选择使用和不使用时区TIMESTAMP(fractional_seconds_precision)TIMESTAMP(fractional_seconds_precision)WITH TIME ZONETIMESTAMP(fractional_seconds_precision)WITH LOCAL TIME ZONE日期数据类型日期数据类型lINTERVAL YEAR TO MONTH 数据类型使数据类型使用年和月日期字段存储一段时间用年和月日期字段存储一段时间lINTERVAL DAY TO SECOND 数据类型数据类型使用天，小时，分钟，秒存储一段时间使用天，小时，分钟，秒存储一段时间INTERVAL YEAR(year_precision)TO MONTHINTERVAL DAY(day_precision)TO SECOND(fractional_seconds_precision)INTERVAL DAY TO SECONDINTERVAL DAY TO SECONDlINTERVAL 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.约束约束l约束是强加到表中的列上的规那么l提供了以下五种约束:lNOT NULLlUNIQUE lPRIMARY KEYlFOREIGN KEYlCHECK约束规那么约束规那么l创立约束时，可以对约束进行命名，也可以使用SYS_Cn的格式自定义命名l可以在以下时候创立约束:l在表创立时同时创立约束l在表创立以后创立约束l在表或者列级别创立约束l在数据字典中查看约束定义约束定义约束l语法:l列级别约束:l表级别约束: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,定义约束定义约束l列级别约束:l表级别约束：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);12NOT NULLNOT NULL约束约束l非空约束的列不能有空值非空约束的列不能有空值: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 constraintUNIQUEUNIQUE约束约束EMPLOYEES UNIQUE约束INSERT INTO不允许，已经存在允许UNIQUEUNIQUE约束约束 定义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 KEYPRIMARY KEY约束约束DEPARTMENTS PRIMARY KEYINSERT INTO不允许空值不允许重复值FOREIGN KEYFOREIGN KEY约束约束DEPARTMENTS EMPLOYEESFOREIGNKEYINSERT INTO不允许9 允许PRIMARYKEYFOREIGN KEYFOREIGN 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 KEYlFOREIGN KEY:定义在子表的列上lREFERENCES:引用父表的主键或者唯一键lON DELETE CASCADE:删除父表中的行同时也删除子表中的行lON DELETE SET NULL:将外键设为空值CHECK约束约束l每行必须满足定义的条件l以下的表达式不允许:lCURRVAL,NEXTVAL,LEVEL和ROWNUM伪列l调用以下函数：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 foundUPDATE employeesSET department_id=55WHERE department_id=110;违反约束违反约束55部门不存在违反约束违反约束l 不能删除外键所引用的值不能删除外键所引用的值DELETE FROM departmentsWHERE department_id=60;DELETE FROM departments *ERROR at line 1:ORA-02292:integrity constraint(HR.EMP_DEPT_FK)violated-child record found使用子查询创立表使用子查询创立表l通过使用CREATE TABLE语句和AS SUBQUERY子句创立表同时插入数据l创立表的列的个数要与子查询中的列的个数匹配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 dept80ALTER TABLE tableADD (column datatype DEFAULT expr ,column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr ,column datatype.);l 使用使用 ALTER TABLE 命令来进行命令来进行:l增加一个新列增加一个新列l更改一个现存的列更改一个现存的列l为新列定义缺省的值为新列定义缺省的值l删除一列删除一列ALTER TABLEALTER TABLE语句语句 EMPNO ENAME ANNSAL HIREDATE -7698 BLAKE 3420001-MAY-81 7654 MARTIN 1500028-SEP-81 7499 ALLEN 1920020-FEB-81 7844 TURNER 1800008-SEP-81.EMPNO ENAME ANNSAL HIREDATE -7698 BLAKE 3420001-MAY-81 7654 MARTIN 1500028-SEP-81 7499 ALLEN 1920020-FEB-81 7844 TURNER 1800008-SEP-81.JOB JOB增加一列增加一列 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);新列已经增加，成为最后一个列新列已经增加，成为最后一个列使用使用ADD子句来增加一个列子句来增加一个列增加一列增加一列SQL ALTER TABLEdept30 2 MODIFY(ename VARCHAR2(15);更改一个现存的列更改一个现存的列可以更改一个现存列的数据类型、长度、以及缺省值可以更改一个现存列的数据类型、长度、以及缺省值.对于缺省值的修改只影响到以后会修改的列对于缺省值的修改只影响到以后会修改的列.SQL ALTER TABLEdept30 2 DROP COLUMN job;使用使用 DROP COLUMN来丢弃一个表中将来不使用的列来丢弃一个表中将来不使用的列丢弃一个列丢弃一个列SQL DROP TABLE dept30;l表中所有的数据和结构都被删除了表中所有的数据和结构都被删除了.l所有未决的事务都被提交所有未决的事务都被提交.l此表上所有的索引全部被删除此表上所有的索引全部被删除.l此表上所有的约束全部被删除此表上所有的约束全部被删除.l这个操作是不能回滚的这个操作是不能回滚的.丢弃一个表丢弃一个表SQL RENAME dept TO department;改变一个对象的名称改变一个对象的名称更改表、视图、同义词、序列等对象的名称更改表、视图、同义词、序列等对象的名称,执行执行RENAME 命令就可以了命令就可以了操作用户必须是对象的拥有者操作用户必须是对象的拥有者SQL TRUNCATE TABLE department;截断一个表截断一个表lTRUNCATE TABLE 语句功能语句功能:l移除表中所有的数据行移除表中所有的数据行l释放表中的存储空间释放表中的存储空间l在执行在执行 TRUNCATE语句后，不能对该操作回滚。语句后，不能对该操作回滚。l可以用可以用DELETE语句来做相同的操作，不过语句来做相同的操作，不过DELETE并不并不立即释放空间，因而可以回滚立即释放空间，因而可以回滚SQL COMMENT ON TABLE emp 2 IS Employee Information;为一个表增加注释为一个表增加注释l可以使用可以使用 COMMENT语句为一个表，或者表中语句为一个表，或者表中的某一列增加注释的某一列增加注释l注释可以通过数据字典视图进行查询注释可以通过数据字典视图进行查询.lALL_COL_COMMENTSlUSER_COL_COMMENTSlALL_TAB_COMMENTSlUSER_TAB_COMMENTS语句语句描述描述CREATE TABLE 创立一个表创立一个表ALTER TABLE 更改表结构更改表结构DROP TABLE 废弃一个表，包括它的结构和数据行废弃一个表，包括它的结构和数据行RENAME 更改对象的名称更改对象的名称TRUNCATE 从表中移除所有的行，并将这些空间释放掉从表中移除所有的行，并将这些空间释放掉COMMENT 为一个表或者视图增加注释为一个表或者视图增加注释总结总结