Les09-创建和管理表

1、九.创建和管理表.目的本课终了后，应该掌握以下内容:描画主要的数据库对象查看表构造列出主要的列数据类型创建表掌握如何在建表的同时建立约束描画方式对象是如何任务的.对象描画表存储的根本单元，由行和列组成视图一个逻辑的数据集，数据来自一个或者多个表序列 产生主键值索引目的提高查询的性能同义词 给出对象的别名数据库对象.必需以字母开场可以是1-30个字符的长度仅能包括字符： AZ, az, 09, _, $, 和#不能与本用户拥有的其他对象重名不能是一个 Oracle效力器的保管字命名习惯.CREATE GLOBAL TEMPORARY TABLE schema.table (column data

2、type 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)确认创建的结果创建表.援用其他用户的表属于其他用户的

3、表，不在当前用户的方式下运用这些表的属主名作为前缀USERBUSERASELECT * 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.用户表由用户创建和维护的一

4、系列表的集合包含用户的信息数据字典由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数据类型.日期数据类型几种日期数据类型数

5、据类型 描述TIMESTAMP秒带有小数的日期INTERVAL YEAR TO MONTH存储年和月的间隔INTERVAL DAY TO SECOND存储天，小时，分钟和秒的间隔.日期数据类型TIMESTAMP 是DATE数据类型的扩展存储DATE日期类型的年，月，日数据类型加上小时，分钟，和带有小数的秒可以在TIMESTAMP上选择运用和不运用时区TIMESTAMP(fractional_seconds_precision)TIMESTAMP(fractional_seconds_precision)WITH TIME ZONETIMESTAMP(fractional_seconds_pre

6、cision)WITH LOCAL TIME ZONE.日期数据类型INTERVAL YEAR TO MONTH 数据类型运用年和月日期字段存储一段时间INTERVAL DAY TO SECOND 数据类型运用天，小时，分钟，秒存储一段时间INTERVAL YEAR (year_precision) TO MONTHINTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision).INTERVAL DAY TO SECONDINTERVAL DAY TO SECOND 数据类型运用天，小时，分钟，秒存储一段时间INTER

7、VAL 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.约束约束是强加到表中的列上的

8、规那么提供了以下五种约束:NOT NULLUNIQUE PRIMARY KEYFOREIGN KEYCHECK.约束规那么创建约束时，可以对约束进展命名，也可以运用SYS_Cn的格式自定义命名可以在以下时候创建约束:在表创建时同时创建约束在表创建以后创建约束在表或者列级别创建约束在数据字典中查看约束.定义约束语法:列级别约束:表级别约束:CREATE TABLE schema.table (column datatype DEFAULT expr column_constraint, . table_constraint,.);column,. CONSTRAINT constraint_na

9、me 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、(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);12.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不允许

11、，曾经存在允许.UNIQUE约束定义unique约束或者在表级别或者在列级别: CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,. CONSTRAINT emp_email_uk UNIQUE();.PRIMARY KEY约束DEPARTMENTS PRIMARY KEYINSERT INTO不允许空值不允许反复值

12、.FOREIGN KEY约束DEPARTMENTS EMPLOYEESFOREIGNKEYINSERT INTO不允许9 允许PRIMARYKEY.FOREIGN KEY约束在表级或者列级定义:CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, VARCHAR2(25), salary NUMBER(8,2), commission_pct NUMBER(2,2), hire_date DATE NOT NULL,. department_id NUMBER(4), CONSTRAINT

13、 emp_dept_fk FOREIGN KEY (department_id) REFERENCES departments(department_id), CONSTRAINT emp_email_uk UNIQUE();.FOREIGN KEYFOREIGN KEY: 定义在子表的列上REFERENCES: 援用父表的主键或者独一键ON DELETE CASCADE: 删除父表中的行同时也删除子表中的行ON DELETE SET NULL: 将外键设为空值.CHECK约束每行必需满足定义的条件以下的表达式不允许:CURRVAL, NEXTVAL, LEVEL和ROWNUM伪列调用以下函数

14、：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 , VARCHAR2(25) CONSTR

15、AINT 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 NUM

16、BER(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部门不存在.违反约束不能删除外键所援用的

17、值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.运用子查询创建表经过运用CREATE TABLE语句和AS SUBQUERY子句创建表同时插入数据创建表的列的个数要与子查询中的列的个数匹配CREATE TABLE table (column, column.)AS subquery;.CREATE TABLE

18、 dept80 AS SELECT employee_id, last_name, salary*12 ANNSAL, hire_date FROM employees WHERE department_id = 80;Table created.运用子查询创建表DESCRIBE dept80.ALTER TABLE tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);运用 ALTE

19、R 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 19

20、20020-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来丢弃一个表中未来不运用的列丢弃一个