60 Oracle初级表表练习.docx
《60 Oracle初级表表练习.docx》由会员分享，可在线阅读，更多相关《60 Oracle初级表表练习.docx（15页珍藏版）》请在冰豆网上搜索。
60Oracle初级表表练习
七、Oracle数据库的对象定义操作（DDL语句）
1.表（TABLE）
1.1Oracle常用的数据类型[重点]
Varchar2:
表示的是可变的字符串类型varchar（n）<=4000
Char:
表示是定长的字符串char（n）<=2000
Number:
表示整数和浮点数number（n）,number（n,m）
Date:
表示日期格式，oracle默认的日期格式是”DD-MM-YY”
Timestamp:
时间戳,精确度比较高的日期类型
Time:
时间格式
Blob:
存储大的二进制多媒体文件。
<=4GB
Clob:
存储大的二进制文本文件。
<=4GB
1.2建立表[重点]
1．2．1实例演示数据建模：
学员管理系统：
学员（student）：
学号（sid）,姓名（sname）,年龄（sage）,性别（sgender）,地址（address）,电话（telphone）
班级（class）：
班号（cid）班级名称（cname）,所在教室（classroom）….
课程（course）：
课程编号（courseId）,课程名称（cousename）,学分（coursescore）,课时（coursetime）
教师（teacher）：
教师编号（tid）,教师姓名（tname）……
学员与班级是:
n:
1
学员与课程是:
n:
m
课程与教师是:
n:
m
将些关系转换成E-R图，如下：
1．2．2将上面的E-R图转换成数据对象（在这之前应该有形成数据字典）
建立数据库的基本讲法是：
CREATETABLE表名（
字段名类型[DEFAULT默认值],
字段名类型[DEFAULT默认值],
……
字段名类型[DEFAULT默认值]
）
注意：
1．建表语句的最后一个字段的定义结束不需要使用逗号。
2．字段名称尽量用小写，而且使用字母，下划线，数字组成
3．字段名称尽量缩写，（加注释）
4．数据库中的命名，多个单词之间用下划线相连
Eg:
建立Student表
CREATETABLE student（
idnumber（5）,
namevarchar2（20）,
agenumber（3）,
genderchar
（2）DEFAULT‘男’,
addressvarchar2（100）,
telponevachar2（12）
）
练习：
将图中其它实体通过建表语句建立起来。
1.3删除表[重点]
删除表使用的语法：
DROPTABLE表名
Eg:
删除学生表student
DROPTABLEstudent;
说明：
删除表会删除表的定义以及其中的所有数据，并会将其占用的空间释放。
1.4修改表[重点]
1．添加字段
1>使用ALTER命令，其语法为：
ALTERTABLE 表名ADD（字段名类型[DEFAULT默认值,
字段名类型DEFAULT默认值,…..]）
说明：
⏹这种向表中添加字段的方式比较常见，对数据的破坏较小，一般在系统的二次开发用的比较多。
Eg:
要向student表中再添加QQ字段，则可以如下操作：
ALTERTABLEstudentADD（qqnumber（10））;
2．修改数据类型
其语法为：
ALTERTABLE 表名MODIFY（字段名类型[DEFAULT默认值
字段名类型DEFAULT默认值]….）
说明：
⏹通常只涉及到数据长度的修改，类型之间的修改限制比较多
⏹在将长度的改小的过程中一定要考试以前的数据是否还能装下。
Eg:
将student表的sname字段的字符长度改为30
ALTERTABLEstudentMODIFY（namevarchar2（30））
3．删除表的列
基语法是：
ALTERTABLE表名DROP（字段名,字段名…..）
说明：
⏹删除列的情况在实际项目比较少见。
Eg:
删除student表中的QQ字段
ALTERTABLE studentDROP（qq）
4．修改表的名称
其语法是：
语法一、
ALTERTABLE 表名RENAMETO新表名
语法二、
RENAME表名TO新表名
Eg:
将student表名改成imit_student
ALTERTABLEstudentRENAMETOimti_student
或：
RENAMEstudentTOimti_student
说明：
⏹修改表的名称在实际项目中也比较少见，影响太大。
1.4截断表[了解]
其语法是：
TRUNCATETABLE 表名
Eg:
删除student表中的所有记录：
DROPTABLEstudent
或：
DELETEFROMstudent
或：
TRUNCTABLEstudent
1.5DELETEDROPTRUNCATE的比较[重点]
相同点：
三个关键字都能删除表的所记录
不同点：
1.delete都只是删除数据，不删除表的结构，而drop会删除表的结构，以及依赖它的约束，触发器，索引，但是依赖它的存储过程与函数会保留。
2.delete是DML语句，它的操作会放到回滚段中，可以执行回滚操作，能够触发触发器。
而drop,truncate都是DDL语句，它的操作不会放到回滚段中，无法回滚,不会触发触发器。
3.delete删除数据不会回收该空间。
drop会将表所占用的所有空间都释放。
truncate默认情况下是要释放空间，但可以设置不释放空间。
4.速度不一样，一般情况：
drop>truncate>delete
5.安全性 ，小心使用drop和truncate
总结：
✓想删除部分数据建议使用delete
✓想删除表只能drop
✓想删除记录但保留表的结构时，如果操作与事务无关，不需要触发触发器可以使用truncate,如果操作与事务有关，需要触发器可以使用delete
✓如果想整理数据内部碎片，可以使用truncate跟上reusestroage.然后重新导入数据。
2.约束（CONSTRAINT）[重点]
在数据库的开发，约束是必不可少，约束可以比较好控制数据的完整性。
2.1约束的分类
在实际中，约束可以分为以下几种：
◆主键约束：
主键表示唯一标识，本身不能空且不能重复。
（PK）
◆唯一约束：
只表此列的值是唯一，但是可以为空。
（UK）
◆检查约束：
表示一个列的值是否是合法数据。
（CK）
◆非空约束：
表示此列不能出现空值。
◆主-外键约束：
表示两张表之间约束。
（FK）
2.2主键约束（PRIMARYKEY）
给某字段加主键约束可以使用关键字PRIMARYKEY
CREATETABLE student（
Sidnumber（5）PRIMARYKEY,
Snamevarchar2（20）,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男',
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）
）
说明：
⏹主键约束本身就有非空的约束和唯一约束，而且一个表只能有一个主键。
⏹将约束直接定义在相应的字段之后的约束称为列级约束。
⏹将约束定义所有字段之后的约束我们称为表级约束。
⏹给约束定义名称最好使用表级约束。
如：
CREATETABLE student（
Sidnumber（5）,--学员编号
Snamevarchar2（20）,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男',
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（sid）
）
⏹通常一张表的主键会设置成与实体本身无关一个字段。
也就是说通常会虚构出一个无业务无关ID字段作为表的主键。
CREATETABLE student（
Idnumber（8）,--虚构一个与业务无关的字来作为表的主键
Sidnumber（5）,--学员编号
Snamevarchar2（20）,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男',
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（id）
）
Eg:
向表student中添加记录
INSERTINTOstudentVALUES
（1001,'zhangsan',20,'男','襄樊胜利街','138458888','abc@'）;
INSERTINTOstudentVALUES
（1001,'zhangsan',20,'男','襄樊胜利街','138458888','abc@'）;
说明：
⏹当使用了主键约束后，则不允许插入两个完全相同的记录。
并且主键所在列不允许为空值。
2.3唯一约束（UNIQUE）
保证数据的某项内容不重复。
可以使用唯一约束，关键字是UNIQUE
Eg.将student表的sname列定义成唯一列.
CREATETABLE student（
idnumber（5）,--主键
snovarchar2（8）UNIQUE,--代表学员编号
Snamevarchar2（20）UNIQUE,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男',
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（id）
）
说明：
⏹同样可以用表级约束使用CONSTRAINT关键字来定义唯一性约束.
如:
CREATETABLE student（
idnumber（5）,--主键
snovarchar2（8）UNIQUE,--代表学员编号
Snamevarchar2（20）,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男',
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（id）,
CONSTRAINTstudent_sname_ukUNIQUE（sname）
）
要查看当前用户的所有约束，可查看ORACLE数据字典，USER_CONSTRAINT
selectconstraint_namefromuser_constraintswhereowner='SCOTT'
2.4检查约束
用来检查数据的合法性的约束，可以使用CHECK关键字.
Eg:
将表student中的年龄范围定义在0-150,性别必须是’男’或者’女’
CREATETABLE student（
idnumber（5）,--主键
snovarchar2（8）UNIQUE,--代表学员编号
Snamevarchar2（20）,
Sagenumber（3）CHECK（sageBETWEEN0AND150）,
Sgenderchar
（2）DEFAULT'男'CHECK（sgenderIN（‘男’,’女’））,
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（id）,
CONSTRAINTstudent_sname_ukUNIQUE（sname）
）
同样也可以用表级约束来定义检查约束
CREATETABLE student（
idnumber（5）,--主键
snovarchar2（8）UNIQUE,--代表学员编号
Snamevarchar2（20）,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男'CHECK（sgenderIN（'男','女'））,
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（id）,
CONSTRAINTstudent_sname_ukUNIQUE（sname）,
CONSTRAINTstudent_sage_ckCHECK（sageBETWEEN0AND150）
）
2.5非空约束（NOTNULL）
可以对那此必填的字段进行约束，可以使用关键字NOTNULL
Eg:
使student表的sno必填的内容
CREATETABLE student（
idnumber（5）,--主键
snovarchar2（8）UNIQUENOTNULL,--代表学员编号
Snamevarchar2（20）,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男'CHECK（sgenderIN（'男','女'））,
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（id）,
CONSTRAINTstudent_sname_ukUNIQUE（sname）,
CONSTRAINTstudent_sage_ckCHECK（sageBETWEEN0AND150）
）
说明：
⏹如果使用列级约束给同一字段加多个约束，用空格隔开即可。
⏹非空约束不可以使用表级约束来定义，只能使用列级约束。
2.6主-外键约束（FOREIGNKEY）
之前讲的四种的约束全部都针对单表的约束，而主-外键约束是两张表的约束，使用关键字FOREIGNKEY。
外键主要用来维护两实体之间的联系。
对于1：
N的关系建立在在多一方。
对于1：
1的关系建立在从表的一方。
对于多对多的关系是需要中间表来维护，而且中间表使用的是联合主键。
CREATETABLE student（--子表
idnumber（5）,--主键
snovarchar2（9）UNIQUENOTNULL,--代表学员编号
Snamevarchar2（20）,
Sagenumber（3）,
Sgenderchar
（2）DEFAULT'男'CHECK（sgenderIN（'男','女'））,
Addressvarchar2（100）,
Telponevarchar2（12）,
emailvarchar2（30）,
classidnumber（3）,
CONSTRAINTstudent_sid_pkPRIMARYKEY（id）,
CONSTRAINTstudent_sname_ukUNIQUE（sname）,
CONSTRAINTstudent_sage_ckCHECK（sageBETWEEN0AND150）
CONSTRAINTstudent_class_classid_fkFOREIGNKEY（classid）REFERENCESclass（id）
）
CREATETABLEclass（--父表
idnumber（3）PRIMARYKEY,
cnovarchar2（6）notnullunique,
cnamevarchar2（20）notnullunique,
classroomvarchar2（10）
）
说明：
⏹外键约束可以保证数据的完整性，不会出现不合理的数据,它可以为空可以重复，但数据都是来源于关联表的主键值。
⏹外键可以进行列级定义或或者表级定义
如：
CONSTRAINTstudent_class_classid_fkFOREIGNKEY（classid）REFERENCESclass（id）（表级定义）
classidnumber（3）referencesclass（id）（列级定义）
⏹主外键约束引用的必须是父表的主键。
⏹在添加数据的时候必须先添加父表的数据。
⏹删除的时候必须先删除子表中数据再删除父表中的数据实际也可以进级级联操作,要进行级联操作的时候需要在建立外键的关系的时候指定此级联属性。
A.ONDELETE CASCADE:
删除父表中的数据时，会级联删除子表中关联的数据。
B.ONDELETESETNULL:
删除父表中数据时，会将子表的关联列的数据全部置为NULL
C.ONDELETENOACTION:
不做任何级联,默认设置
⏹删除表的时候也需要先删除子表再删除父表。
但是可以加上CASCADECONSTRAINT来进行级联删除（意义不大）。
如：
droptableclassCASCADECONSTRAINT;
2.7约束的修改
约束的修改比较不常见。
特别是已经有数据的表，再修改其约束的时候，限制会比较多。
通常会用在建表时，特别是建立主外约束时用的比较多。
1>添加约束
其语法是：
ALTERTABLE表名ADDCONSTRAINT约束名约束类型（约束的字段）
Eg：
将class表的classroom字段加上唯一性约束
ALTERTABLEclassADDCONSTRAINTclass_classroom_ukUNIQUE（classroom）
可以使用上面语法添加的约束类型有：
PRIMARYKEY，UNIQUECHECKFOREIGNKEY
对于NOTＮＵＬＬ类型的约束可以使用下面的语法：
ALTERTABLEclassMODIFY（classroomvarchar（30）NOTnull）;
２>删除约束
其约束的语法:
ALTERTABLE表名DROPCONSTRAINT约束名
Eg:
去掉STUDENT表的sname字段的唯一约束
ALTERTABLEstudentDROPCONSTRAINTstudent_sname_uk;
3>约束的禁用和启用
其语法：
ALTERTABLE表名DISABLECONSTRAINT约束名
ALTERTABLE表名ENABLECONSTRAINT约束名
升级会员 