主题：[推荐]关于Oracle中SQL语法的部分整理资料之一

1 集合运算
1、1 union
select * from stu where sex='F' union select * from stu where sid>1;

select * from stu where sex='F' union all select * from stu where sid>1;

1、2 minus
select * from stu minus select * from stu where sex='M';

1、3 intersect
select * from stu where sex='M' intersect select * from stu where sid>1;

2 复杂查询
2、1 数据准备
CREATE TABLE student
(
        "NUMBER" varchar2(6) NOT NULL,
        name varchar2(8) NULL,
        sex number(1),
        birthday timestamp NULL,
        height number(3,2) NULL
);

insert into student ("NUMBER",name,sex,birthday,height)  values ( '000001','黎明',1,TO_DATE('15-4月 -1980','dd-Mon-
yyyy'),1.78);
insert into student ("NUMBER",name,sex,birthday,height)  values ( '000002','赵怡春',0,TO_DATE('17-12月 -1982','dd-Mon-
yyyy'),1.77);
insert into student ("NUMBER",name,sex,birthday,height)  values ( '000003','张富平',1,TO_DATE('1-2月 -1981','dd-Mon-
yyyy'),1.80);
insert into student ("NUMBER",name,sex,birthday,height)  values ( '000004','白丽',0,TO_DATE('20-11月 -1986','dd-Mon-
yyyy'),1.73);
insert into student ("NUMBER",name,sex,birthday,height)  values ( '000005','牛玉德',1,TO_DATE('1-5月 -1983','dd-Mon-
yyyy'),1.74);
insert into student ("NUMBER",name,sex,birthday,height)  values ( '000006','姚华',0,TO_DATE('12-5月 -1983','dd-Mon-
yyyy'),1.77);

CREATE TABLE grade
(
  "NUMBER" varchar2(6) NULL,
        course varchar2(50) NULL,
        grade number(3) NULL
);

insert into grade ("NUMBER",course,grade) values ('000001','A03',56);
insert into grade ("NUMBER",course,grade) values ('000002','A02',90);
insert into grade ("NUMBER",course,grade) values ('000004','A02',77);
insert into grade ("NUMBER",course,grade) values ('000005','B01',91);
insert into grade ("NUMBER",course,grade) values ('000004','A03',76);
insert into grade ("NUMBER",course,grade) values ('000003','A03',67);
insert into grade ("NUMBER",course,grade) values ('000001','B01',78);
insert into grade ("NUMBER",course,grade) values ('000002','A03',74);
insert into grade ("NUMBER",course,grade) values ('000002','B01',86);
insert into grade ("NUMBER",course,grade) values ('000003','A02',80);
insert into grade ("NUMBER",course,grade) values ('000004','B01',90);

2、2 投影
2、2、1 简单使用
select * from student;

2、2、2 别名
select name as 姓名,height as 身高 from student

2、2、3 常数列
select name,height,' 米 ' as 单位 from student

2、2、4 计算列
select name as 姓名,round(height*100,-1) as 身高,'CM' as 单位 from student

select name as 姓名,to_char(round(height*100,0)) || '米' as 身高 from student

select name,extract(year from sysdate)-extract(year from birthday) as age from student

select distinct substr(name,1,1) as 姓 from student;

2、3 选择
2、3、1 简单使用
select * from student where height>1.76;
select * from student where name in ('黎明','姚华');
select * from student where substr(name,1,1)='黎';
select * from student where rownum=1;

2、3、2 通配符
select * from student where name like '黎%';
select * from student where name like '黎_';
select * from student where name='黎_';
select * from student where name like 'x%%x%' escape 'x';

2、3、3 NULL
insert into student("NUMBER",name,sex,birthday) values('000007','刘华',1,TO_DATE('17-2月 -1984','dd-Mon-yyyy'));

select * from student where height>1.80 or height<=1.80;
select * from student where height is not null;

2、4 排序
select * from student order by height;
select * from student order by name;
select * from student order by height desc;
select * from student order by height,name desc;

2、5 连接
2、5、1 笛卡儿乘积
select * from student,grade where student."NUMBER"=grade."NUMBER";
select * from student cross join grade;

2、5、2 内连接
select * from student inner join grade on student."NUMBER"=grade."NUMBER";

2、5、3 自连接
select A.name,B.name from student A inner join student B on A.height=B.height where A."NUMBER"<B."NUMBER";

2、5、4 外连接
select name from student left outer join grade on student."NUMBER"=grade."NUMBER" where grade is null;
等价于
select name from student,grade where student."NUMBER"=grade."NUMBER"(+) and grade is null;

2、6 聚合运算
2、6、1 count
select count(height) from student;
select count(sex) from student;
select count(distinct sex) from student;
select count(distinct height) from student;
select count(*) from student;

2、6、2 max，min，sum和avg
select max(height) from student;
select min(height) from student;
select sum(height) from student;
select avg(height) from student;

2、6、3 分组
select sex,count(*) from student group by sex;
select height,count(*) from student group by height;
select "NUMBER",round(avg(grade),1) from grade group by "NUMBER";
select min(name),round(avg(grade),1) from student inner join grade on student."NUMBER"=grade."NUMBER" group by student."NUMBER" order by avg(grade)

2、6、4 having
1）having和where的等价使用
select height,count(*) from student where height>1.75 group by height;
select height,count(*) from student group by height having height>1.75;

2）必须使用having的情况
select height,count(*) from student group by height having count(*)>=2;

3）必须使用where的情况
select height,count(*) from student where sex=1 group by height;

3 统计查询
3、1 row_number
select ename,sal,row_number() over(order by sal desc) from scott.emp;
select ename,deptno,sal,row_number() over(partition by deptno order by sal desc) from scott.emp;

3、2 rank
select ename,sal,rank() over(order by sal desc) from scott.emp;
select ename,deptno,sal,rank() over(partition by deptno order by sal desc) from scott.emp;

3、3 dense_rank
select ename,sal,dense_rank() over(order by sal desc) from scott.emp;
select ename,deptno,sal,dense_rank() over(partition by deptno order by sal desc) from scott.emp;

4 锁定
4、1 行级锁
以SYSTEM登录，执行：select * from student where sex=1 for update of name,height;
在以另外一个SYSTEM登录，执行：select * from student where sex=1 for update of name,height wait 5;

但是可以运行：select * from student where sex=0 for update of name,height wait 5;

此时会等待5秒，只有第一个SYSTEM用户提交或者回滚才能使得第二个SYSTEM用户成功执行，如第一个SYSTEM用户执行：
commit;

4、2 表级锁
可以以第一个SYSTEM用户锁定表：
lock table student in share mode;

再以第二个SYSTEM用户锁定表：
lock table student in share mode;

此时第一个SYSTEM用户不能插入记录（可以多执行几次）：
insert into student ("NUMBER",name,sex,birthday,height)  values ( '000011','黎明',1,TO_DATE('15-4月 -1980','dd-Mon-yyyy'),1.78);

但是第二个SYSTEM用户一旦提交，即可发现第一个SYSTEM用户自动插入记录，同样，一旦第一个SYSTEM用户提交，即可发现第二个SYSTEM用户也可以自动插入记录

5 表分区
表分区有四种方法，分别为范围分区、散列分区、复合分区和列表分区

create table stu
(
  name number(4)
)
partition by range(name)
(
  partition p1 values less than (2000),
  partition p2 values less than (6000),
  partition p3 values less than (MAXVALUE)
);

创建好后可以直接在企业管理器中查看

6 同义词
6、1 私有同义词
create synonym emp for scott.emp;

select * from emp;

6、2 公有同义词
create public synonym emp for scott.emp;

grant all on emp to userA;

connect userA/userA;

select * from emp;

7 序列
create sequence count start with 10 increment by 2 maxvalue 2000 nocycle cache 20;

create table stu
(
    sid integer primary key,
    name        varchar(8) not null,
    sex char(1) check(sex in('M','F'))
);

insert into stu values(count.nextval,'Tom','M');

select * from stu;

select count.currval from dual;
select count.nextval from dual;

8 视图
8、1 一般使用
create or replace view MaleStu as select * from student where sex=1;

update malestu set height=height+0.01;
select * from student;

多表连接视图：
create or replace view StuGrade as select name,course,grade from student inner join grade on student."NUMBER"=grade."NUMBER";
select * from StuGrade;

使用函数的视图：
create or replace view stuAgeInfo as select name,extract(year from sysdate)-extract(year from birthday) as age from student;
select * from stuAgeInfo;

8、2 有检验的视图
insert into malestu("NUMBER",sex) values('111111',0);
select * from malestu;

create or replace view MaleStu as select * from student where sex=1 with check option;

insert into malestu("NUMBER",sex) values('222222',0);

8、3 不可更新的视图
1）只读（with read only）
create or replace view MaleStu as select * from student where sex=1 with read only;

update malestu set height=height+0.01;

2）违反约束，如视图的字段少于表字段，同时该字段不能为空
字段定义时默认可以为空（语句的生成可以使用“显示SQL”得到）

ALTER TABLE STUDENT MODIFY("SEX" NOT NULL);

create or replace view MaleStu as select "NUMBER",name from student where sex=1;

insert into malestu values('333333','Lee');

3）具有聚合函数，伪列、计算列分组等复杂情况
4）具有连接，而且更新多张表
create or replace view StuGrade as select name,course,grade from student inner join grade on student."NUMBER"=grade."NUMBER";

select * from StuGrade;

insert into stugrade values('Lee','B304',67);

此时可以在视图中包含主键以实现键保留表，从而实现对相关表的更新，如：
ALTER TABLE GRADE ADD ("ID" NUMBER(10) NULL);

create sequence cid start with 1 increment by 1 maxvalue 1000 nocycle cache 10;
update grade set id=cid.nextval;
commit;
ALTER TABLE GRADE ADD (CONSTRAINT "GRADEPK" PRIMARY KEY("ID"));
ALTER TABLE STUDENT ADD (CONSTRAINT "STUPK" PRIMARY KEY("NUMBER"));

create or replace view StuGrade as select grade.id as id,name,course,grade from student inner join grade on student."NUMBER"=grade."NUMBER";
select * from StuGrade;

update stugrade set grade=59 where id=(select min(id) from grade);
update stugrade set grade=59 where name='黎明';
select * from StuGrade;

update stugrade set name=name || 's' where name='黎明';

select * from user_updatable_columns where table_name='STUGRADE';

8、4 创建带有错误的视图
create or replace force view newview as select * from nontable;

select * from newview;

create table nontable
(
  name varchar2(6)
);
insert into nontable values('one');

select * from newview;

alter view newview compile;

9 索引
通过建立索引，查询时通过索引文件快速搜索命中字段值，再根据ROWID快速定位表记录

9、1 一般使用
create index idxstuheight on student(height);

9、2 索引的类型
索引分为唯一索引、组合索引、反向键索引、位图索引、索引组织表和基于函数的索引

9、2、1 唯一索引
此时不能插入相同的姓名字段值
create unique index uidxnm on student(name);

9、2、2 组合索引
有助于提高选择复合字段的查询效率
create index cidxsh on student(sex,height);

9、2、3 反向键索引
有助于提高连续值字段的索引存储性能
create index ididx on grade(grade) reverse;

9、2、4 位图索引
有助于提高取值较少的字段索引效率
create bitmap index bitidx on student(sex);

9、2、5 索引组织表
将索引和表记录本身组织在一齐
CREATE TABLE studentbak
(
        "NUMBER" varchar2(6) primary key,
        name varchar2(8) NULL,
        sex number(1) NULL,
        birthday timestamp NULL,
        height number(3,2) NULL
)
organization index;

9、2、6 基于函数的索引
有助于提高利用函数表达式进行的查询效率
create index uppnameidx on student(upper(name));

9、3 索引信息查询
利用user_indexes视图可以得到很多索引信息：
select * from user_indexes where TABLE_NAME='STUDENT';