ORACLE百例试炼三

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

一、创建、删除、修改表

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

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

SQL> CREATE TABLE tmp AS (SELECT * FROM emp WHERE 1>2)  

例如创建表Person如下： 

 CREATE TABLE person(

  pid  VARCHAR2(18),  

  name  VARCHAR2(30),

  age  NUMBER(3),

  birthday DATE, 

 sex  VARCHAR(2)  DEFAULT 'M'

 );

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

  SQL> ALTER TABLE person ADD(address VARCHAR2(20));   

修改表中的列属性  

  SQL> ALTER TABLE person MODIFY(address VARCHAR2(30)); 

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

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

  SQL> RENAME person to personer;

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

SQL> TRUNCATE TABLE personer; 

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

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

二、表的约束

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

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

添加约束如下： 

CREATE TABLE person(  

  pid  VARCHAR2(18), 

 name  VARCHAR2(30) NOT NULL, 

 age  NUMBER(3) CHECK(age BETWEEN 0 AND 150),  

 birthday DATE, 

 sex  VARCHAR(2) DEFAULT 'M' , 

 CONSTRAINT person_pid_pk PRIMARY KEY(pid),  

 CONSTRAINT person_name_uk UNIQUE(name), 

 CONSTRAINT person_sex_ck CHECK(sex IN('M','F')) 

);  

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

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

CREATE TABLE book( 

  bid   NUMBER PRIMARY KEY,

  bname  VARCHAR(20),

  bprice  NUMBER(5,2),

  pid  VARCHAR2(18), 

 CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) 

);

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

DROP TABLE person CASCADE CONSTRAINT; 但是这种做法一般不用

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

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

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

CREATE TABLE book( 

  bid   NUMBER PRIMARY KEY,

  bname  VARCHAR(20),  

  bprice  NUMBER(5,2),  

  pid  VARCHAR2(18), 

 CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE 

);

添加约束语法如下： 

ALTER TABLE 表名称 ADD CONSTRAINT 约束名称 约束类型(约束字段); 

约束类型命名规则： 

PRIMARY KEY:主键字段_PK 

UNIQUE：字段_UK 

CHECK：字段_CK 

FOREIGN KEY：父子段_子字段_FK

SQL> ALTER TABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid); 

SQL> ALTER TABLE person ADD CONSTRAINT person_name_UK UNIQUE(name); 

SQL> ALTER TABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);  

SQL> ALTER TABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCES person(pid) ON DELETE CASCADE;

删除约束语法如下： 

ALTER TABLE 表名称 DROP CONSTRAINT 约束名称; 

 SQL> ALTER TABLE person DROP CONSTRAINT person_age_CK;

 SQL> ALTER TABLE book DROP CONSTRAINT person_book_pid_fk;

案例： 

主键约束添加删除 

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

一、无命名 create table accounts ( accounts_number number primary key, accounts_balance number );  

二、有命名 create table accounts ( accounts_number , accounts_balance number, constraint yy primary key(accounts_number) ); 

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

一、无命名 SELECT * FROM USER_CONS_COLUMNS WHERE TALBE_NAME='ACCOUNTS'; 

找出主键名 ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063; 

二、有命名 ALTER TABLE ACCOUNTS DROP CONTRAINT yy; 

3、向表中添加主键约束 ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);

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

SQL> SELECT ROWNUM,empno,ename FROM emp; 

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

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

SQL> SELECT ROWNUM,empno,ename FROM emp  WHERE ROWNUM<=5; 

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

SQL> SELECT * FROM (

      SELECT ROWNUM m,empno,ename   FROM emp 

      WHERE ROWNUM<=10)     

tmp WHERE tmp.m>=5

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

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

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

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

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

验证UNION和UNION ALL 

SQL> CREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5条记录)

SQL> SELECT * FROM emp UNION SELECT * FROM emp20; (14条记录) 

SQL> SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19条记录)  

验证MINUS 和 INTERSECT 

SQL> SELECT * FROM emp MINUS SELECT * FROM emp20; (9条记录) 

SQL> SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5条记录)

Oracle中视图的操作 

1、创建视图 

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

SQL> CREATE VIEW empv20 AS 

 SELECT empno,ename,job,hiredate  FROM emp 

 WHERE deptno=20; 

2、查询视图 

SQL> SELECT * FROM empv20; 

3、删除视图 

SQL> DROP VIEW empv20;  

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

CREATE OR REPLACE 视图名称 AS 子查询

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

SQL>  CREATE OR REPLACE VIEW empv20 AS 

 SELECT d.dname,ed.c,ed.a,e.ename FROM dept d,( 

 SELECT deptno,COUNT(empno) c, AVG(sal) a,MIN(sal) min  FROM emp 

 GROUP BY deptno) ed,emp e 

WHERE d.deptno=ed.deptno AND e.sal=ed.min; 

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

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

SQL> UPDATE empv20 SET deptno=30 WHERE empno=7369; 

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

SQL提供了两个重要的参数  

WITH CHECK OPTION：不能更新视图的创建条件 

SQL> CREATE OR REPLACE VIEW empv20 AS  SELECT * FROM emp WHERE deptno=20  WITH CHECK OPTION;

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

SQL> UPDATE empv20 SET ename='wilson' WHERE empno=7369;

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

SQL> CREATE OR REPLACE VIEW empv20 AS  SELECT * FROM emp WHERE deptno=20  WITH READ ONLY;