Oracle系列《三》：表、（约束）索引、序列、视图的使用

一、创建、删除、修改表

建立表：Oracle中主要数据类型VARCHAR2、NUMBER、DATE、CLOB(大量文本)、BLOB(图片、音乐、电影)

如果只能复制一张表的结构，但不复制内容，则加上一个不可能成立的条件即可，例如

SQL>CREATETABLEtmpAS(SELECT*FROMempWHERE1>2)

例如创建表Person如下：

CREATETABLEperson(

pidVARCHAR2(18),

nameVARCHAR2(30),

ageNUMBER(3),

birthdayDATE,

sexVARCHAR(2)DEFAULT'M'

);

如果发现创建表后需要添加特定的列，例如address列，则可以使用ALTERTABLE命令

SQL>ALTERTABLEpersonADD(addressVARCHAR2(20));

修改表中的列属性

SQL>ALTERTABLEpersonMODIFY(addressVARCHAR2(30));

在数据库程序开发中，很少去修改表结构，在IBMDB2中就没有提供ALTERTABLE命令

在Oracle中提供RENAME命令对表进行重命名

SQL>RENAMEpersontopersoner;

在Oracle中要清空一张表的数据，但又不能回滚，立即释放资源

SQL>TRUNCATETABLEpersoner;

(与DELETE区别：前者不能回滚，后者能回滚)

（与DROPTABLE区别：前者删除内容，后者删除表）

二、表的约束

约束分类：主键约束、唯一约束、检查约束、非空约束、外键约束

语法：CONSTRAINT 约束名 约束类型（字段名）

添加约束如下：

CREATETABLEperson(

pidVARCHAR2(18),

nameVARCHAR2(30)NOTNULL,

ageNUMBER(3)CHECK(ageBETWEEN0AND150),

birthdayDATE,

sexVARCHAR(2)DEFAULT'M',

CONSTRAINTperson_pid_pkPRIMARYKEY(pid),

CONSTRAINTperson_name_ukUNIQUE(name),

CONSTRAINTperson_sex_ckCHECK(sexIN('M','F'))

);

以上约束可以采取自动命名和手动命名

现在要再添加一张表，使用主-外键约束

CREATETABLEbook(

bidNUMBERPRIMARYKEY,

bnameVARCHAR(20),

bpriceNUMBER(5,2),

pidVARCHAR2(18),

CONSTRAINTperson_book_pid_fkFOREIGNKEY(pid)REFERENCESperson(pid)

);

这时候如果要删除掉person表的话，就会出现错误，此时可以使用强制性的删除手段

DROPTABLEpersonCASCADECONSTRAINT;但是这种做法一般不用

如果在person和book表中添加记录，而在person表中进行删除一条记录时，假设该记录的pid被

book表引用，那么会出现删除错误。如果希望一个表中的数据在删除时，能自动删除对应字表的记录，可以使用级联删除的操作

（总结：主表数据被删除，从表数据跟着删除，在有主外约束的条件下）

CREATETABLEbook(

bidNUMBERPRIMARYKEY,

bnameVARCHAR(20),

bpriceNUMBER(5,2),

pidVARCHAR2(18),

CONSTRAINTperson_book_pid_fkFOREIGNKEY(pid)REFERENCESperson(pid)ONDELETECASCADE

);

添加约束语法如下：

ALTERTABLE表名称ADDCONSTRAINT约束名称约束类型(约束字段);

约束类型命名规则：

PRIMARYKEY:主键字段_PK

UNIQUE：字段_UK

CHECK：字段_CK

FOREIGNKEY：父子段_子字段_FK

SQL>ALTERTABLEpersonADDCONSTRAINTperson_pid_PKPRIMARYKEY(pid);

SQL>ALTERTABLEpersonADDCONSTRAINTperson_name_UKUNIQUE(name);

SQL>ALTERTABLEpersonADDCONSTRAINTperson_age_CKCHECK(ageBETWEEN0AND150);

SQL>ALTERTABLEbookADDCONSTRAINTperson_book_pid_fkFOREIGNKEY(pid)REFERENCESperson(pid)ONDELETECASCADE;

删除约束语法如下：

ALTERTABLE表名称DROPCONSTRAINT约束名称;

SQL>ALTERTABLEpersonDROPCONSTRAINTperson_age_CK;

SQL>ALTERTABLEbookDROPCONSTRAINTperson_book_pid_fk;

案例：

主键约束添加删除

1、创建表的同时创建主键约束

一、无命名createtableaccounts(accounts_numbernumberprimarykey,accounts_balancenumber);

二、有命名createtableaccounts(accounts_number ,accounts_balancenumber,constraintyyprimarykey(accounts_number));

2、删除表中已有的主键约束

一、无命名SELECT*FROMUSER_CONS_COLUMNSWHERETALBE_NAME='ACCOUNTS';

找出主键名ALTERTABLEACCOUNTSDROPCONSTRAINTSYS_C003063;

二、有命名ALTERTABLEACCOUNTSDROPCONTRAINTyy;

3、向表中添加主键约束ALTERTABLEACCOUNTSADDCONSTRAINTPK_ACCOUNTSPRIMARYKEY(ACCOUNTS_NUMBER);

******ROWNUM为例*******

SQL>SELECTROWNUM,empno,enameFROMemp;

ROWNUM采用自动编号的形式出现

假如只想显示前5条记录，那么ROWNUM<=5

SQL>SELECTROWNUM,empno,enameFROMemp WHEREROWNUM<=5;

但是如果要查询5-10条的记录的话，则查询不出，只能采用子查询的方式

SQL>SELECT*FROM(

SELECTROWNUMm,empno,ename FROMemp

WHEREROWNUM<=10)

tmpWHEREtmp.m>=5

集合操作：在Oracle中提供了3类集合操作:并、交、差

UNION：将多个查询结果组合到一个查询结果之中，没有重复值

UNIONALL：与UNICON不同的是包含重复值

INTERSECT：返回多个查询结果中相同的部分

MINUS：返回两个查询结果的差集（不相同的部分）

验证UNION和UNIONALL

SQL>CREATETABLEemp20ASSELECT*FROMempWHEREdeptno=20;(5条记录)

SQL>SELECT*FROMempUNIONSELECT*FROMemp20;(14条记录)

SQL>SELECT*FROMempUNIONALLSELECT*FROMemp20;(19条记录)

验证MINUS和INTERSECT

SQL>SELECT*FROMempMINUSSELECT*FROMemp20;(9条记录)

SQL>SELECT*FROMempINTERSECTSELECT*FROMemp20;(5条记录)

Oracle中视图的操作

1、创建视图

CREATEVIEW视图名称AS子查询这条子查询是非常复杂的语句

SQL>CREATEVIEWempv20AS

SELECTempno,ename,job,hiredateFROMemp

WHEREdeptno=20;

2、查询视图

SQL>SELECT*FROMempv20;

3、删除视图

SQL>DROPVIEWempv20;

如果要修改视图，则要先删除视图，在Oracle为了方便用户修改视图，提供了一个替换的命令

CREATEORREPLACE视图名称AS子查询

视图可以封装复杂的查询，例如查询部门名称，部门的人数，平均工资以及最低工资的雇员

SQL>CREATEORREPLACEVIEWempv20AS

SELECTd.dname,ed.c,ed.a,e.enameFROMdeptd,(

SELECTdeptno,COUNT(empno)c,AVG(sal)a,MIN(sal)minFROMemp

GROUPBYdeptno)ed,empe

WHEREd.deptno=ed.deptnoANDe.sal=ed.min;

在开中发每次都写这么长的SQL语句不方便，可以将其建立成视图，以上红色部门

4.如果对视图进行更新操作，在视图中不应该包含真实数据，按以下命令进行操作

SQL>UPDATEempv20SETdeptno=30WHEREempno=7369;

发现视图已经正常更新，因为emp表中7369编号已经修改为30了，所以在创建视图是有条件的

SQL提供了两个重要的参数

WITHCHECKOPTION：不能更新视图的创建条件

SQL>CREATEORREPLACEVIEWempv20ASSELECT*FROMempWHEREdeptno=20WITHCHECKOPTION;

创建条件不能进行更新了，但其他字段仍然可以更新

SQL>UPDATEempv20SETename='wilson'WHEREempno=7369;

所以这时可以使用视图的第2个条件：创建只读视图

SQL>CREATEORREPLACEVIEWempv20ASSELECT*FROMempWHEREdeptno=20WITHREADONLY;