oracle练习题scott实例

Oracle系列《一》：简单SQL与单行函数使用scott/密码用户下的emp表和dept表完成下列练习，表的结构说明如下

emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号)

dept部门表(deptno部门编号/dname部门名称/loc地点)

工资 ＝ 薪金 ＋ 佣金

如果发现创建表后需要添加特定的列，例如address列，则可以使用ALTER TABLE命令ALTER TABLE person ADD(address VARCHAR2(20));修改表中的列属性ALTER TABLE person MODIFY(address VARCHAR2(30));在数据库程序开发中，很少去修改表结构，在IBM DB2中就没有提供ALTER TABLE命令在Oracle中提供RENAME命令对表进行重命名RENAME person to personer;在Oracle中要清空一张表的数据，但又不需要回滚，需要立即释放资源(与DELETE区别)TRUNCATE TABLE personer;（与DROP TABLE区别：前者删除内容，后者删除表）二、表的约束约束分类：主键约束、唯一约束、检查约束、非空约束、外键约束添加约束如下：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' ,CONSTRAINTperson_pid_pk PRIMARY KEY(pid),CONSTRAINT person_name_ukUNIQUE(name),CONSTRAINT person_sex_ck CHECK(sexIN('M','F')));以上约束可以采取自动命名和手动命名现在要再添加一张表，使用主-外键约束CREATE TABLEbook(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 TABLEperson CASCADE CONSTRAINT;但是这种做法一般不用如果在person 和 book 表中添加记录，而在person表中进行删除一条记录时，假设该记录的pid被book表引用，那么会出现删除错误。如果希望一个表中的数据在删除时，能自动删除对应字表的记录，可以使用级联删除的操作CREATE TABLEbook(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:主键字段_PKUNIQUE：字段_UKCHECK：字段_CKFOREIGN KEY：父子段_子字段_FKALTERTABLE person ADD CONSTRAINT person_pid_PK PRIMARY KEY(pid);ALTERTABLE person ADD CONSTRAINT person_name_UK UNIQUE(name);ALTERTABLE person ADD CONSTRAINT person_age_CK CHECK(age BETWEEN 0 AND 150);ALTERTABLE book ADD CONSTRAINT person_book_pid_fk FOREIGN KEY(pid) REFERENCESperson(pid)ON DELETECASCADE;删除约束语法如下：ALTER TABLE 表名称 DROP CONSTRAINT 约束名称;ALTERTABLE person DROP CONSTRAINT person_age_CK;ALTERTABLE book DROP CONSTRAINT person_book_pid_fk;ROWNUM伪列SELECT ROWNUM,empno,ename FROM emp;ROWNUM采用自动编号的形式出现加入只想显示前5条记录，那么ROWNUM<=5SELECT ROWNUM,empno,ename FROM emp;WHEREROWNUM<=5;但是如果要查询5-10条的记录的话，则查询不出，只能采用子查询的方式SELECT * FROM (SELECT ROWNUM m,empno,enameFROM empWHERE ROWNUM<=10) tmpWHEREtmp.m>5;不好的查询思路：select ROWNUM,empno,job,mgr from empt where rownum<=10MINUSselect ROWNUM,empno,job,mgr from empt where rownum<=5集合操作：在Oracle中提供了3类集合操作:并、交、差UNION：将多个查询结果组合到一个查询结果之中，没有重复值UNION ALL：与UNICON不同的是包含重复值INTERSECT：返回多个查询结果中相同的部分MINUS：返回两个查询结果的差集验证UNION和UNION ALLCREATE TABLE emp20 AS SELECT * FROM emp WHERE deptno=20; (5条记录)SELECT * FROM emp UNION SELECT * FROM emp20; (14条记录)SELECT * FROM emp UNION ALL SELECT * FROM emp20; (19条记录)验证MINUS 和 INTERSECTSELECT * FROM emp MINUS SELECT * FROM emp20; (9条记录)SELECT * FROM emp INTERSECT SELECT * FROM emp20; (5条记录)案例：主键约束添加删除1、创建表的同时创建主键约束create table accounts ( accounts_number number primary key, accounts_balance number );

2、删除表中已有的主键约束SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME='accounts';找出主键名 ALTER TABLE ACCOUNTS DROP CONSTRAINT SYS_C003063;3、向表中添加主键约束 ALTER TABLE ACCOUNTS ADD CONSTRAINT PK_ACCOUNTS PRIMARY KEY(ACCOUNTS_NUMBER);

Oracle中视图的操作1、创建视图CREATE VIEW 视图名称 AS 子查询这条子查询是非常复杂的语句CREATE VIEW empv20 ASSELECT empno,ename,job,hiredateFROM empWHERE deptno=20;2、查询视图SELECT* FROM empv20;