相关子查询

查询工资高于所在系的平均工资的教师信息，输出工号、姓名和工资

相关子查询：

select tno,tname,sal

from teacher t1

where sal>(select avg(sal) from teacher where dno=t1.dno);

查询从未被选修的课程，输出课序号和课程名

子查询：

SELECT cno,cname

FROM course

WHERE NOT EXISTS(SELECT * FROM sc WHERE cno=course.cno);

视图

视图是被存储的查询，视图是虚表，数据库只存储视图的定义，不存储数据。

单一表创建视图：

create or replace view v_teacher

as select tno,tname

from teacher;

多表视图：

create or replace view student_d01

as select * from student

where dno=(select dno from dep

where dname='物理系');

表和视图创建视图：

create or replace view student_sc

as select sc.sno,sname,cno,grade from sc,student_d01

where sc.sno=student_d01.sno;

通过视图查询物理系学生的信息

select * from student_d01;

通过视图将物理系学生陈国民的名字修改为陈国

update student_d01

set sname='陈国'

where sname='陈国民';

通过视图删除数据

delete from student_d01

where sname='陈国';

通过视图插入数据（不建议）

insert into student_d01 values(2015125101,'王坤','女','15-8月-2000','d01');

insert into student_d01 values(2015125102,'王丽坤','女','10-7月-2000','d02');

从系代号可以知道王丽坤不是物理系的学生，但是这条数据仍然可以被添加，查询student_d01不会显示该条数据，通过student_d01也无法删除该条数据。

带check option的视图

create or replace view student_d01

as select * from student

where dno='d01'

with check option constraint v_1; --如果视图多了with check option则上面插入的两条记录中，第一条记录可以添加，第二条记录无法添加。

只读视图

create or replace view student_sex

as select sno,sname from student

where sex='男'

with read only;

视图创建语句中出现distinct、聚合操作、分组统计、排序输出、树查询和集合运算等关键字，或者视图涉及多张表，则视图不能被更新。

删除视图

drop view student_d01;

索引

索引的目的就是为了能够快速地在文件中定位要访问的记录，为了实现这种访问数据的方式，需要一些附加结构——索引

索引有两种：

B+树索引

create index in_sname

on student(sname);

或者

create index in_dno_birth on student(dno,birth);

已经被定义为主码，则不能再建索引

create index in_sno

on student(sno); //出错

或者

create index in_sno_cno

on sc(sno,cno); //出错

建立索引的原则：

记录有一定规模，检索<10％内容

select * from student where sex=‘男’;

建立索引的列在where子句中频繁使用

如果查询过程中列需要先进行运算再进行比对，则索引不起作用

select * from teacher where upper(tname)=`JONES`;

位图索引

create bitmap index bidx_sex on student(sex);

create bitmap index bidx_dno on student(dno);

建立位图索引的原则：

位图索引适用于列的值取值不多的情况

位图索引不适合经常插入或更新数据的表

删除索引

drop index in_sname;

drop index bidx_sex; --注意不能写成bitmap index

聚集

除了通过索引来加快查询速度，对于多表连接，可以通过聚集（簇）来加快查询速度

比如teacher表中的dno和dep表中的dno，可以在建立这两张表之前先创建一个聚集

create cluster teacher_dep

(depno char(3)); --depno也可以令名为dno，只要跟teacher表和dep表的dno数据类型和长度相同就可以

在聚集键列上创建聚集索引

create index t_dep_inx on cluster teacher_dep;

然后在创建teacher表和dep表的语句中加入

cluster teacher_dep(dno);

从而将teacher表和dep表创建成聚集表，dno变成聚集键列，对于已经存在的表，不能通过修改表的结构来将表改成聚集表

teacher表dno列和dep表dno列保存在同一数据块中

teacher表中dno列出现的重复值将只保存一个，teacher表dno列和dep表dno列相同的值将只存储一个，这样不仅加快了连接查询而且节省了存储空间

聚集的删除必须先删除聚集表，然后再删除聚集

drop table emp;

drop table dept;

drop cluster emp_dept;

或者：

drop cluster emp_dept including tables;

序列

执行下面语句两次

select s1.nextval,s1.currval /* 在一个会话周期内，第一次引用currval之前必须先引用一次nextval，第一次nextval显示序列的初始值，currval显示最近一次nextval的值 */

from dual;

修改序列

alter sequence s1

increment by 2;

alter sequence s1

maxvalue 2000; --只能修改序列的增量、最大和最小值，不能修改序列的初始值

序列的使用

insert into student values(s1.nextval,'陈赫','男','12-7月-1980','d02');

下列情况不能使用序列

不能在where子句中使用

不能在带distinct运算符的查询语句中使用

不能在集合运算中使用

不能在分组和排序的查询语句中使用

不能在子查询中使用

不能在建立视图和快照的查询子句中使用

不能在建立或者修改表的结构中用序列作为默认值

不能在check约束条件中使用