创建和管理表课件

1、九九.创建和管理表创建和管理表目标目标l本课结束后，应该掌握以下内容:l描述主要的数据库对象l查看表结构l列出主要的列数据类型l创建表l掌握如何在建表的同时建立约束l描述模式对象是如何工作的对象对象描述描述表表存储的基本单元，由行和列组成存储的基本单元，由行和列组成视图视图一个逻辑的数据集，数据来自一个或者多个表一个逻辑的数据集，数据来自一个或者多个表序列序列 产生主键值产生主键值索引索引目的提高查询的性能目的提高查询的性能同义词同义词 给出对象的别名给出对象的别名数据库对象数据库对象l 必须以字母开始必须以字母开始l 可以是可以是1-30个字符的长度个字符的长度l 仅能包括字符：仅能包括字符

2、： 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 TAB

3、LE 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.employee

4、s;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 服务器创建和维护的一系列表的集合服务器创

5、建和维护的一系列表的集合l包含数据库的信息包含数据库的信息OracleOracle数据库中的表数据库中的表数据类型数据类型描述描述VARCHAR2(size)变长字符数据，变长字符数据，size为最大长度为最大长度CHAR(size) 定长字符数据，定长字符数据， size为最大长度为最大长度NUMBER(p,s) 数字类型，数字类型，p为精度，为精度，s为刻度为刻度DATE 日期和时间值日期和时间值LONG 可以保存最大长度为可以保存最大长度为 2GB的字符数据的字符数据CLOB可以保存最大长度为可以保存最大长度为 4GB的单字节字符的单字节字符数据数据RAW and LONG RAW 存储

6、二进制数据存储二进制数据BLOB最大为最大为 4GB的二进制数据类型的二进制数据类型BFILE在外部文件中存储的二进制数据在外部文件中存储的二进制数据;最大为最大为4GB数据类型数据类型日期数据类型日期数据类型l 几种日期数据类型几种日期数据类型数据类型数据类型 描述描述TIMESTAMP秒带有小数的日期秒带有小数的日期INTERVAL YEAR TO MONTH存储年和月的间隔存储年和月的间隔INTERVAL DAY TO SECOND存储天，小时，分钟和秒的间隔存储天，小时，分钟和秒的间隔日期数据类型日期数据类型lTIMESTAMP 是DATE数据类型的扩展l存储DATE日期类型的年，月，

7、日数据类型加上小时，分钟，和带有小数的秒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 数据类型数据类

8、型使用天，小时，分钟，秒存储一段时间使用天，小时，分钟，秒存储一段时间INTERVAL YEAR (year_precision) TO MONTHINTERVAL DAY (day_precision) TO SECOND (fractional_seconds_precision)INTERVAL DAY TO INTERVAL DAY TO SECONDSECONDlINTERVAL DAY TO SECOND 数据类型数据类型使用天，小时，分钟，秒存储一段时间使用天，小时，分钟，秒存储一段时间INTERVAL 4 5:12:10.222 DAY TO SECOND(3)表示：4 day

9、s, 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

10、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 (

11、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,

12、CONSTRAINT emp_emp_id_pk PRIMARY KEY (EMPLOYEE_ID);12NOT NOT NULLNULL约束约束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约束I

13、NSERT 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 PRIMARY KEYKEY约束约束D

14、EPARTMENTS PRIMARY KEYINSERT INTO不允许空值不允许重复值FOREIGN FOREIGN KEYKEY约束约束DEPARTMENTS EMPLOYEESFOREIGNKEYINSERT INTO不允许9 允许PRIMARYKEYFOREIGN FOREIGN KEYKEY约束约束 在表级或者列级定义在表级或者列级定义: :CREATE TABLE employees( employee_id NUMBER(6), last_name VARCHAR2(25) NOT NULL, email VARCHAR2(25), salary NUMBER(8,2), com

15、mission_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: 删除父表中的行同时也删除子表中的行l

16、ON 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_

17、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

18、, 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 - pare

19、nt 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使用子查询创

20、建表使用子查询创建表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

21、 tableADD (column datatype DEFAULT expr , column datatype.);ALTER TABLE tableMODIFY (column datatype DEFAULT expr , column datatype.);l 使用使用 ALTER TABLE 命令来进行命令来进行:l增加一个新列增加一个新列l更改一个现存的列更改一个现存的列l为新列定义缺省的值为新列定义缺省的值l删除一列删除一列ALTER ALTER TABLETABLE语句语句 EMPNO ENAME ANNSAL HIREDATE - - - 7698 BLAKE 342000

22、1-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 3

23、4200 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);更改一个现存的列更改一个现存的列可以更改一个现存列的数据类型、

24、长度、以及缺省值可以更改一个现存列的数据类型、长度、以及缺省值.对于缺省值的修改只影响到以后会修改的列对于缺省值的修改只影响到以后会修改的列.SQL ALTER TABLEdept30 2 DROP COLUMN job ;使用使用 DROP COLUMN来丢弃一个表中将来不使用的列来丢弃一个表中将来不使用的列丢弃一个列丢弃一个列SQL DROP TABLE dept30;l表中所有的数据和结构都被删除了表中所有的数据和结构都被删除了.l所有未决的事务都被提交所有未决的事务都被提交.l此表上所有的索引全部被删除此表上所有的索引全部被删除.l此表上所有的约束全部被删除此表上所有的约束全部被删除.l这个操作是不能回滚的这个