创建和管理表课件

九.创立和管理表目标本课结束后，应该掌握以下内容:描述主要的数据库对象查看表结构列出主要的列数据类型创立表掌握如何在建表的同时建立约束描述模式对象是如何工作的对象 描述表 存储的根本单元，由行和列组成 视图 一个逻辑的数据集，数据来自一个或者多个表序列 产生主键值索引 目的提高查询的性能同义词 给出对象的别名数据库对象必须以字母开始可以是1-30个字符的长度仅能包括字符：A–Z,a–z,0–9,_,$,和#不能与本用户拥有的其他对象重名不能是一个Oracle效劳器的保存字命名习惯CREATE[GLOBALTEMPORARY]TABLE[schema.]table (column

datatype[DEFAULTexpr][,...]);CREATETABLE语句必须有:CREATETABLE的权限拥有存储空间你可以指定表名称列名称，列长度，列类型

SQL>CREATETABLEdept

2 (deptno NUMBER(2),3 dname VARCHAR2(14),4 loc VARCHAR2(13));Tablecreated.

SQL>DESCRIBEdeptNameNull?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日期数据类型INTERVAL

YEAR

TO

MONTH

数据类型使用年和月日期字段存储一段时间INTERVALDAYTOSECOND

数据类型使用天，小时，分钟，秒存储一段时间INTERVALYEAR[(year_precision)]TOMONTHINTERVALDAY[(day_precision)]TOSECOND[(fractional_seconds_precision)]INTERVALDAYTOSECONDINTERVALDAYTOSECOND

数据类型使用天，小时，分钟，秒存储一段时间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.约束约束是强加到表中的列上的规那么提供了以下五种约束:NOTNULLUNIQUEPRIMARYKEYFOREIGNKEYCHECK约束规那么创立约束时，可以对约束进行命名，也可以使用SYS_Cn的格式自定义命名可以在以下时候创立约束:在表创立时同时创立约束在表创立以后创立约束在表或者列级别创立约束在数据字典中查看约束定义约束语法:列级别约束:表级别约束:CREATETABLE[schema.]table(column

datatype[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(Norowcancontain

anullvaluefor

thiscolumn.)AbsenceofNOTNULLconstraint

(Anyrowcancontainanullvalueforthiscolumn.)NOTNULL

constraint…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约束DEPARTMENTS

PRIMARYKEYINSERTINTO不允许空值不允许重复值…FOREIGNKEY约束DEPARTMENTS

EMPLOYEESFOREIGN

KEYINSERTINTO不允许9允许PRIMARY

KEY……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));FOREIGNKEYFOREIGNKEY:定义在子表的列上REFERENCES:引用父表的主键或者唯一键ONDELETECASCADE:删除父表中的行同时也删除子表中的行ONDELETESETNULL:将外键设为空值CHECK约束每行必须满足定义的条件以下的表达式不允许:CURRVAL,NEXTVAL,LEVEL和ROWNUM伪列调用以下函数：SYSDATE,UID,USER,USERENV...,salary NUMBER(2)CONSTRAINTemp_salary_minCHECK(salary>0),...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(salary>0),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;违反约束55部门不存在违反约束不能删除外键所引用的值DELETEFROMdepartmentsWHEREdepartment_id=60;DELETEFROMdepartments*ERRORatline1:ORA-02292:integrityconstraint(HR.EMP_DEPT_FK)violated-childrecordfound使用子查询创立表通过使用CREATETABLE语句和ASSUBQUERY子句创立表同时插入数据创立表的列的个数要与子查询中的列的个数匹配CREATETABLEtable [(column,column...)]ASsubquery;CREATETABLE dept80AS

SELECTemployee_id,last_name,salary*12ANNSAL,hire_date

FROMemployees

WHEREdepartment_id=80;Tablecreated.使用子查询创立表DESCRIBEdept80ALTERTABLEtableADD (columndatatype[DEFAULTexpr] [,columndatatype]...);ALTERTABLEtableMODIFY (columndatatype[DEFAULTexpr] [,columndatatype]...);使用ALTERTABLE命令来进行:增加一个新列更改一个现存的列为新列定义缺省的值删除一列ALTERTABLE语句

DEPT30EMPNOENAME ANNSAL HIREDATE---------------- --------7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81...

DEPT30EMPNOENAME ANNSAL HIREDATE---------------- --------7698 BLAKE 34200 01-MAY-817654 MARTIN 15000 28-SEP-817499 ALLEN 19200 20-FEB-817844 TURNER 18000 08-SEP-81...

JOB

JOB新列增加一列“…为表DEPT30增加一个新列…〞EMPNOENAMEANNSALHIREDATEJOB-----------------------------------------7698BLAKE3420001-MAY-817654MARTIN1500028-SEP-817499ALLEN1920020-FEB-817844TURNER1800008-SEP-81...6rowsselected.SQL>ALTERTABLEdept302ADD (jobVARCHAR2(9));Tablealtered.新列已经增加，成为最后一个列使用ADD子句来增加一个列增加一列SQL>ALTERTABLE dept302MODIFY (enameVARCHAR2(15));Tablealtered.更改一个现存的列可以更改一个现存列的数据类型、长度、以及缺省值.对于缺省值的修改只影响到以后会修改的列.SQL>ALTERTABLE dept302DROPCOLUMNjob;Tablealtered.使用DROPCOLUMN来丢弃一个表中将来不使用的列丢弃一个列SQL>DROPTABLEdept30;Tabledropped.表中所有的数据和结构都被删除了.所有未决的事务都被提交.此表上所有的索引全部被删除.此表上所有的约束全部被删除.这个操作是不能回滚的.丢弃一个表SQL>RENAMEdeptTOdepartment;Tablerenamed.改变一个对象的名称更改表、视图、同义词、序列等对象的名称,执行RENAME命令就可以了操作用户必须是对象的拥有者SQL>TRUNCATETABLEdepartment;Tabletruncated.截断一个表TRUNCATETABLE语句功能:移除表中所有的数据行释放表中的存储空间在执行TRUNCATE语句后，不能对该操作回滚。可以用DELETE语句来做相同的操作，不过DELETE并不立即释放空间，因而可以回滚SQL>COMMENTONTABLEemp2IS'EmployeeInformation';Commentcreated.为一个表增加注释可以使用COMMENT语句为一个表，或者表中的某一列增加注释注释可以通过数据字典视图进行查询.ALL_COL_COMMENTSUSER_COL_COMMENTSALL_TAB_COMMENTSUSER_TAB_COMMENTS

语句 描述CREATETABLE 创立一个表ALTERTABLE 更改表结构DROPTABLE 废弃一个表，包括它的结构和数据行RENAME 更改对象的名称 TRUNCATE 从表中移除所有的行，并将这些空间释放掉 COMMENT 为一个表或者视图增加注释总结9、静夜四无邻，荒居旧业贫。。4月-224月-22Thursday,April21,202210、雨中黄叶树，灯下白头人。。15:46:4515:46:4515:464/21/20223:46:45PM11、以我独沈久，愧君相见频。。4月-2215:46:4515:46Apr-2221-Apr-2212、故人江海别，几度隔山川。。15:46:4515:46:4515:46Thursday,April21,202213、乍见翻疑梦，相悲各问年。。4月-224月-2215:46:4515:46:45April21,202214、他乡生白发，旧国见青山。。21四月20223:46:45下午15:46:454月-2215、比不了得就不比，得不到的就不要。。。四月223:46下午4月-2215:46April21,202216、行动出成果，工作出财富。。2022/4/2115:46:4515:46:4521April202217、做前，能够环视四周；做时，你只能或者最好沿着以脚为起点的射线向前。。3:46:45下午3:46下午15:46:454月-229、没有失败，只有暂时停止成功！。4月-224月-22Thursday,April21,202210、很多事情努力了未必有结果，但是不努力却什么改变也没有。。15:46:4515:46:4515:464/21/20223:46:45PM11、成功就是日复一日那一点点小小努力的积累。。4月-2215:46:4515:46Apr-2221-Apr-2212、世间成事，不求其绝对圆满，留一份缺乏，可得无限