60 Oracle初级表表练习.docx

1、60 Oracle初级表表练习七、Oracle数据库的对象定义操作(DDL语句)1.表(TABLE)1.1 Oracle 常用的数据类型 重点Varchar2:表示的是可变的字符串类型 varchar(n) =4000Char:表示是定长的字符串 char(n) =2000Number:表示整数和浮点数 number(n),number(n,m)Date:表示日期格式，oracle默认的日期格式是”DD-MM-YY”Timestamp:时间戳,精确度比较高的日期类型Time:时间格式Blob:存储大的二进制多媒体文件。=4GBClob:存储大的二进制文本文件。 使用ALTER命令，其语法为：A

2、LTER TABLE表名 ADD(字段名 类型 DEFAULT 默认值,字段名 类型 DEFAULT 默认值,.)说明： 这种向表中添加字段的方式比较常见，对数据的破坏较小，一般在系统的二次开发用的比较多。Eg : 要向student表中再添加QQ字段，则可以如下操作：ALTER TABLE student ADD(qq number(10);2 修改数据类型其语法为：ALTER TABLE表名 MODIFY(字段名 类型 DEFAULT 默认值字段名 类型 DEFAULT 默认值.)说明： 通常只涉及到数据长度的修改，类型之间的修改限制比较多 在将长度的改小的过程中一定要考试以前的数据是否还

3、能装下。Eg:将student表的sname字段的字符长度改为30ALTER TABLE student MODIFY(name varchar2(30)3 删除表的列基语法是：ALTER TABLE 表名 DROP(字段名,字段名.) 说明： 删除列的情况在实际项目比较少见。Eg:删除student表中的QQ字段 ALTER TABLEstudent DROP(qq)4 修改表的名称其语法是：语法一、ALTER TABLE表名 RENAME TO 新表名语法二、RENAME 表名 TO 新表名Eg:将student表名改成imit_studentALTER TABLE student REN

4、AME TO imti_student或：RENAME student TO imti_student说明： 修改表的名称在实际项目中也比较少见，影响太大。1.4截断表 了解其语法是：TRUNCATE TABLE表名Eg: 删除student表中的所有记录：DROP TABLE student或：DELETE FROM student或：TRUNC TABLE student1.5 DELETE DROP TRUNCATE的比较 重点相同点：三个关键字都能删除表的所记录不同点：1. delete都只是删除数据，不删除表的结构，而drop会删除表的结构，以及依赖它的约束，触发器，索引，但是依赖它

5、的存储过程与函数会保留。2. delete是DML语句，它的操作会放到回滚段中，可以执行回滚操作，能够触发触发器。而drop,truncate都是DDL语句，它的操作不会放到回滚段中，无法回滚,不会触发触发器。3. delete删除数据不会回收该空间。drop会将表所占用的所有空间都释放。truncate默认情况下是要释放空间，但可以设置不释放空间。4. 速度不一样，一般情况：droptruncatedelete5. 安全性，小心使用drop和truncate总结： 想删除部分数据建议使用delete 想删除表只能drop 想删除记录但保留表的结构时，如果操作与事务无关，不需要触发触发器可以使

6、用truncate,如果操作与事务有关，需要触发器可以使用delete 如果想整理数据内部碎片，可以使用truncate跟上reuse stroage.然后重新导入数据。2.约束(CONSTRAINT) 重点在数据库的开发，约束是必不可少，约束可以比较好控制数据的完整性。2.1 约束的分类在实际中，约束可以分为以下几种： 主键约束：主键表示唯一标识，本身不能空且不能重复。（PK） 唯一约束：只表此列的值是唯一，但是可以为空。（UK） 检查约束：表示一个列的值是否是合法数据。（CK） 非空约束：表示此列不能出现空值。 主-外键约束：表示两张表之间约束。（FK）2.2 主键约束（PRIMARY K

7、EY）给某字段加主键约束可以使用关键字PRIMARY KEYCREATE TABLEstudent( Sid number(5) PRIMARY KEY, Sname varchar2(20), Sage number(3), Sgender char(2) DEFAULT 男, Address varchar2(100), Telpone varchar2(12), email varchar2(30)说明： 主键约束本身就有非空的约束和唯一约束，而且一个表只能有一个主键。 将约束直接定义在相应的字段之后的约束称为列级约束。 将约束定义所有字段之后的约束我们称为表级约束。 给约束定义名称最好

8、使用表级约束。如：CREATE TABLEstudent( Sid number(5),-学员编号 Sname varchar2(20), Sage number(3), Sgender char(2) DEFAULT 男, Address varchar2(100), Telpone varchar2(12), email varchar2(30), CONSTRAINT student_sid_pk PRIMARY KEY(sid) 通常一张表的主键会设置成与实体本身无关一个字段。也就是说通常会虚构出一个无业务无关ID字段作为表的主键。CREATE TABLEstudent( Id num

9、ber(8) ,-虚构一个与业务无关的字来作为表的主键 Sid number(5),-学员编号 Sname varchar2(20), Sage number(3), Sgender char(2) DEFAULT 男, Address varchar2(100), Telpone varchar2(12), email varchar2(30), CONSTRAINT student_sid_pk PRIMARY KEY(id)Eg:向表student中添加记录INSERT INTO student VALUES(1001,zhangsan,20,男,襄樊胜利街,138458888,abc)

10、;INSERT INTO student VALUES(1001,zhangsan,20,男,襄樊胜利街,138458888,abc);说明： 当使用了主键约束后，则不允许插入两个完全相同的记录。并且主键所在列不允许为空值。2.3 唯一约束(UNIQUE)保证数据的某项内容不重复。可以使用唯一约束，关键字是UNIQUEEg.将student表的sname列定义成唯一列.CREATE TABLEstudent( id number(5),-主键 sno varchar2(8) UNIQUE,-代表学员编号 Sname varchar2(20) UNIQUE, Sage number(3), Sg

11、ender char(2) DEFAULT 男, Address varchar2(100), Telpone varchar2(12), email varchar2(30), CONSTRAINT student_sid_pk PRIMARY KEY(id)说明： 同样可以用表级约束使用CONSTRAINT关键字来定义唯一性约束. 如:CREATE TABLEstudent( id number(5),-主键 sno varchar2(8) UNIQUE,-代表学员编号 Sname varchar2(20), Sage number(3), Sgender char(2) DEFAULT

12、男, Address varchar2(100), Telpone varchar2(12), email varchar2(30), CONSTRAINT student_sid_pk PRIMARY KEY(id), CONSTRAINT student_sname_uk UNIQUE(sname)要查看当前用户的所有约束，可查看ORACLE数据字典，USER_CONSTRAINTselect constraint_name from user_constraints where owner=SCOTT2.4 检查约束用来检查数据的合法性的约束，可以使用CHECK关键字.Eg:将表stud

13、ent中的年龄范围定义在0-150,性别必须是男或者女CREATE TABLEstudent( id number(5),-主键 sno varchar2(8) UNIQUE,-代表学员编号 Sname varchar2(20), Sage number(3) CHECK(sage BETWEEN 0 AND 150), Sgender char(2) DEFAULT 男 CHECK(sgender IN(男,女), Address varchar2(100), Telpone varchar2(12), email varchar2(30), CONSTRAINT student_sid_p

14、k PRIMARY KEY(id), CONSTRAINT student_sname_uk UNIQUE(sname)同样也可以用表级约束来定义检查约束CREATE TABLEstudent( id number(5),-主键 sno varchar2(8) UNIQUE,-代表学员编号 Sname varchar2(20), Sage number(3), Sgender char(2) DEFAULT 男 CHECK(sgender IN(男,女), Address varchar2(100), Telpone varchar2(12), email varchar2(30), CONS

15、TRAINT student_sid_pk PRIMARY KEY(id),CONSTRAINT student_sname_uk UNIQUE(sname),CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150)2.5非空约束（NOT NULL）可以对那此必填的字段进行约束，可以使用关键字NOT NULLEg:使student表的sno必填的内容CREATE TABLEstudent( id number(5),-主键 sno varchar2(8) UNIQUE NOT NULL,-代表学员编号 Sname varchar2(20)

16、, Sage number(3), Sgender char(2) DEFAULT 男 CHECK(sgender IN(男,女), Address varchar2(100), Telpone varchar2(12), email varchar2(30), CONSTRAINT student_sid_pk PRIMARY KEY(id),CONSTRAINT student_sname_uk UNIQUE(sname),CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150)说明： 如果使用列级约束给同一字段加多个约束，用空格隔开

17、即可。 非空约束不可以使用表级约束来定义，只能使用列级约束。2.6 主-外键约束（FOREIGN KEY）之前讲的四种的约束全部都针对单表的约束，而主-外键约束是两张表的约束，使用关键字FOREIGN KEY。外键主要用来维护两实体之间的联系。对于1：N的关系建立在在多一方。对于1：1的关系建立在从表的一方。对于多对多的关系是需要中间表来维护，而且中间表使用的是联合主键。CREATE TABLEstudent(-子表 id number(5),-主键 sno varchar2(9) UNIQUE NOT NULL,-代表学员编号 Sname varchar2(20), Sage number(

18、3), Sgender char(2) DEFAULT 男 CHECK(sgender IN(男,女), Address varchar2(100), Telpone varchar2(12), email varchar2(30),classid number(3), CONSTRAINT student_sid_pk PRIMARY KEY(id), CONSTRAINT student_sname_uk UNIQUE(sname), CONSTRAINT student_sage_ck CHECK(sage BETWEEN 0 AND 150) CONSTRAINT student_cl

19、ass_classid_fk FOREIGN KEY(classid) REFERENCES class(id)CREATE TABLE class(-父表 id number(3) PRIMARY KEY, cno varchar2(6) not null unique, cname varchar2(20) not null unique, classroom varchar2(10)说明： 外键约束可以保证数据的完整性，不会出现不合理的数据,它可以为空可以重复，但数据都是来源于关联表的主键值。 外键可以进行列级定义或或者表级定义如：CONSTRAINT student_class_cla

20、ssid_fk FOREIGN KEY(classid) REFERENCES class(id)（表级定义）classid number(3) references class(id)（列级定义） 主外键约束引用的必须是父表的主键。 在添加数据的时候必须先添加父表的数据。 删除的时候必须先删除子表中数据再删除父表中的数据实际也可以进级级联操作,要进行级联操作的时候需要在建立外键的关系的时候指定此级联属性。A. ON DELETECASCADE:删除父表中的数据时，会级联删除子表中关联的数据。B. ON DELETE SET NULL:删除父表中数据时，会将子表的关联列的数据全部置为NULLC

21、. ON DELETE NO ACTION:不做任何级联,默认设置 删除表的时候也需要先删除子表再删除父表。但是可以加上CASCADE CONSTRAINT来进行级联删除(意义不大)。如：drop table class CASCADE CONSTRAINT;2.7约束的修改约束的修改比较不常见。特别是已经有数据的表，再修改其约束的时候，限制会比较多。通常会用在建表时，特别是建立主外约束时用的比较多。1 添加约束其语法是：ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型(约束的字段) Eg：将class表的classroom字段加上唯一性约束 ALTER TABLE

22、 class ADD CONSTRAINT class_classroom_uk UNIQUE(classroom) 可以使用上面语法添加的约束类型有： PRIMARY KEY，UNIQUE CHECK FOREIGN KEY 对于NOT 类型的约束可以使用下面的语法：ALTER TABLE class MODIFY(classroom varchar(30) NOT null); 删除约束 其约束的语法: ALTER TABLE 表名 DROP CONSTRAINT 约束名 Eg:去掉STUDENT表的sname字段的唯一约束 ALTER TABLE student DROP CONSTRAINT student_sname_uk;3约束的禁用和启用其语法：ALTER TABLE 表名 DISABLE CONSTRAINT 约束名ALTER TABLE 表名 ENABLE CONSTRAINT 约束名