Les09-创建和管理表

九.创建和管理表目标本课结束后，应该掌握以下内容:描述主要的数据库对象查看表结构列出主要的列数据类型创建表掌握如何在建表的同时建立约束描述模式对象是如何工作的对象描述表存储的基本单元，由行和列组成视图一个逻辑的数据集，数据来自一个或者多个表序列产生主键值索引目的提高查询的性能同义词给出对象的别名数据库对象必须以字母开始可以是1-30个字符的长度仅能包括字符：A–Z,a–z,0–9,_,$,和#不能与本用户拥有的其他对象重名不能是一个Oracle服务器的保留字命名习惯CREATE[GLOBALTEMPORARY]TABLE[schema.]table(columndatatype[DEFAULTexpr][,...]);CREATETABLE语句必须有:CREATETABLE的权限拥有存储空间你可以指定表名称列名称，列长度，列类型SQLCREATETABLEdept2(deptnoNUMBER(2),3dnameVARCHAR2(14),4locVARCHAR2(13));Tablecreated.SQLDESCRIBEdeptNameNull?Type--------------------------------------------DEPTNONUMBER(2)DNAMEVARCHAR2(14)LOCVARCHAR2(13)确认创建的结果创建表引用其他用户的表属于其他用户的表，不在当前用户的模式下使用这些表的属主名作为前缀USERBUSERASELECT*FROMuserB.employees;SELECT*FROMuserA.employees;在插入期间可以指定默认值文字值，表达式，或者SQL函数是合法值其他列的名或者伪列是非法的默认的数据类型必须匹配列的数据类型DEFAULT选项...hire_dateDATEDEFAULTSYSDATE,...CREATETABLEhire_dates(idNUMBER(8),hire_dateDATEDEFAULTSYSDATE);Tablecreated.用户表由用户创建和维护的一系列表的集合包含用户的信息数据字典由Oracle服务器创建和维护的一系列表的集合包含数据库的信息Oracle数据库中的表数据类型描述VARCHAR2(size)变长字符数据，size为最大长度CHAR(size)定长字符数据，size为最大长度NUMBER(p,s)数字类型，p为精度，s为刻度DATE日期和时间值LONG可以保存最大长度为2GB的字符数据CLOB可以保存最大长度为4GB的单字节字符数据RAWandLONGRAW存储二进制数据BLOB最大为4GB的二进制数据类型BFILE在外部文件中存储的二进制数据;最大为4GB数据类型日期数据类型几种日期数据类型数据类型描述TIMESTAMP秒带有小数的日期INTERVALYEARTOMONTH存储年和月的间隔INTERVALDAYTOSECOND存储天，小时，分钟和秒的间隔日期数据类型TIMESTAMP是DATE数据类型的扩展存储DATE日期类型的年，月，日数据类型加上小时，分钟，和带有小数的秒可以在TIMESTAMP上选择使用和不使用时区TIMESTAMP[(fractional_seconds_precision)]TIMESTAMP[(fractional_seconds_precision)]WITHTIMEZONETIMESTAMP[(fractional_seconds_precision)]WITHLOCALTIMEZONE日期数据类型INTERVALYEARTOMONTH数据类型使用年和月日期字段存储一段时间INTERVALDAYTOSECOND数据类型使用天，小时，分钟，秒存储一段时间INTERVALYEAR[(year_precision)]TOMONTHINTERVALDAY[(day_precision)]TOSECOND[(fractional_seconds_precision)]INTERVALDAYTOSECONDINTERVALDAYTOSECOND数据类型使用天，小时，分钟，秒存储一段时间INTERVAL'45:12:10.222'DAYTOSECOND(3)表示：4days,5hours,12minutes,10.222seconds,INTERVAL'45:12'DAYTOMINUTE表示：4days,5hoursand12minutes.INTERVAL'4005'DAY(3)TOHOUR表示：400days5hours.INTERVAL'11:12:10.2222222'HOURTOSECOND(7)表示：11hours,12minutes,and10.2222222seconds.约束约束是强加到表中的列上的规则提供了以下五种约束:NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECK约束规则创建约束时，可以对约束进行命名，也可以使用SYS_Cn的格式自定义命名可以在以下时候创建约束:在表创建时同时创建约束在表创建以后创建约束在表或者列级别创建约束在数据字典中查看约束定义约束语法:列级别约束:表级别约束:CREATETABLE[schema.]table(columndatatype[DEFAULTexpr][column_constraint],...[table_constraint][,...]);column,...[CONSTRAINTconstraint_name]constraint_type(column,...),column[CONSTRAINTconstraint_name]constraint_type,定义约束列级别约束:表级别约束：CREATETABLEemployees(employee_idNUMBER(6)CONSTRAINTemp_emp_id_pkPRIMARYKEY,first_nameVARCHAR2(20),...);CREATETABLEemployees(employee_idNUMBER(6),first_nameVARCHAR2(20),...job_idVARCHAR2(10)NOTNULL,CONSTRAINTemp_emp_id_pkPRIMARYKEY(EMPLOYEE_ID));12NOTNULL约束非空约束的列不能有空值:NOTNULLconstraint(Norowcancontainanullvalueforthiscolumn.)AbsenceofNOTNULLconstraint(Anyrowcancontainanullvalueforthiscolumn.)NOTNULLconstraint…UNIQUE约束EMPLOYEESUNIQUE约束INSERTINTO不允许，已经存在允许…UNIQUE约束•定义unique约束或者在表级别或者在列级别:CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...CONSTRAINTemp_email_ukUNIQUE(email));PRIMARYKEY约束DEPARTMENTSPRIMARYKEYINSERTINTO不允许空值不允许重复值…FOREIGNKEY约束DEPARTMENTSEMPLOYEESFOREIGNKEYINSERTINTO不允许9允许PRIMARYKEY……FOREIGNKEY约束•在表级或者列级定义:CREATETABLEemployees(employee_idNUMBER(6),last_nameVARCHAR2(25)NOTNULL,emailVARCHAR2(25),salaryNUMBER(8,2),commission_pctNUMBER(2,2),hire_dateDATENOTNULL,...department_idNUMBER(4),CONSTRAINTemp_dept_fkFOREIGNKEY(department_id)REFERENCESdepartments(department_id),CONSTRAINTemp_email_ukUNIQUE(email));FOREIGNKEYFOREIGNKEY:定义在子表的列上REFERENCES:引用父表的主键或者唯一键ONDELETECASCADE:删除父表中的行同时也删除子表中的行ONDELETESETNULL:将外键设为空值CHECK约束每行必须满足定义的条件以下的表达式不允许:CURRVAL,NEXTVAL,LEVEL和ROWNUM伪列调用以下函数：SYSDATE,UID,USER,USERENV...,salaryNUMBER(2)CONSTRAINTemp_salary_minCHECK(salary0),...CREATETABLE:示例CREATETABLEemployees(employee_idNUMBER(6)CONSTRAINTemp_employee_idPRIMARYKEY,first_nameVARCHAR2(20),last_nameVARCHAR2(25)CONSTRAINTemp_last_name_nnNOTNULL,emailVARCHAR2(25)CONSTRAINTemp_email_nnNOTNULLCONSTRAINTemp_email_ukUNIQUE,phone_numberVARCHAR2(20),hire_dateDATECONSTRAINTemp_hire_date_nnNOTNULL,job_idVARCHAR2(10)CONSTRAINTemp_job_nnNOTNULL,salaryNUMBER(8,2)CONSTRAINTemp_salary_ckCHECK(salary0),commission_pctNUMBER(2,2),manager_idNUMBER(6),department_idNUMBER(4)CONSTRAINTemp_dept_fkREFERENCESdepartments(department_id));UPDATEemployees*ERRORatline1:ORA-02291:integrityconstraint(HR.EMP_DEPT_FK)violated-parentkeynotfoundUPDATEemployeesSETdepartment_id=55WHEREdepartment_id=110;违反约束违反约束不能删除外键所引用的值DELETEFROMdepartmentsWHEREdepartment_id=60;DELETEFROMdepartments*ERRORatline1:ORA-02292:integrityconstraint(HR.EMP_DEPT_FK)violated-childrecordfound使用子查询创建表通过使用CREATETABLE语句和ASSUBQUERY子句创建表同时插入数据创建表的列的个数要与子查询中的列的个数匹配CREATETABLEtable[(column,column...)]ASsubquery;CREATETABLEdept80ASSELECTemployee_id,last_name,salary*12ANNSAL,hire_dateFROMemployeesWHEREdepartment_id=80;Tablecreated.使用子查询创建表DESCRIBEdept80ALTERTABLEtableAD