文章目录

• • 伪表（DUAL 表）
  • 伪列（RowID）
  • 行号（Rownum）
  • 联合关键字：
  • • union：结果唯一
    • union all：结果不唯一
    • intersect：求交集
    • minus：从第一个查询结果集中减去第二个查询结果中重复的数据
    • in：表示符合查询结果中的任意一个值
    • not in：表示条件不能是查询结果中的任意一个值
    • some/any：这个和in用法相同
    • all：表示比所有值都大，比所有值都小
  • 序列：sequence
  • • 查看序列的下一个值：
    • 查看序列的当前值：
    • 查看当前用户下有哪些序列：
    • 如何修改序列：
    • 如何删除序列：

伪表（DUAL 表）

该表主要目的是为了保证在使用SELECT语句中的语句的完整性而提供的。

一般用于验证函数。例如：

select sysdate,to_char(sysdate,'yyyy-mm-dd HH24:mm:ss') from dual

伪列（RowID）

rowid = 是映射数据库中每一条数据物理地址的唯一标识

适用于删除完全重复的数据

delete from lyric where rowid not in (select min(rowid) from lyric group by content);

行号（Rownum）

这是Oracle根据查询结果动态的编号，用来实现分页查询
1）有序的整数列，每多一条自动加1
2）不能和order by在同一个查询语句中，order by会打乱查询的顺序
3）不能使用表名.rownum的写法
4）如果rownum用在where之后，rownum >= 1 rownum<= 任意值

1.实现第一页数据，编号1-10

SELECTrownum,substr( content, 1, 10 ) || '...' 
FROMlyric 
WHERErownum BETWEEN 1 AND 10;

select rownum,content from lyric where rownum between 1 and 10;

2.实现第二页数据，编号11-20

SELECT* 
FROM( SELECT rownum rn, content FROM lyric WHERE rownum <= 20 ) a 
WHEREa.rn >= 11;

3.实现第三页数据，编号21-30

SELECT* 
FROM( SELECT rownum rn, content FROM lyric WHERE rownum <= 30 ) a 
WHEREa.rn >= 21;

4.实现带有排序效果的分页

SELECT* 
FROM( SELECT rownum rn, content FROM ( SELECT * FROM lyric ORDER BY content ) WHERE rownum <= 20 ) a 
WHEREa.rn >= 11;

5.我们常见的分页是如何实现的：
curpage：当前页 2 3
pagesize：每页行数 10 25
当前页的开始数：(curpage -1) * pagesize +1 11 51
当前页的截止数：curpage * pagesize 20 75

联合关键字：

union：结果唯一

select name from school where name like '%大'
union
select name from school where name like '山%';

union all：结果不唯一

select name from school where name like '%大'
union all
select name from school where name like '山%';

intersect：求交集

select * from school where name like '%大'
intersect
select * from school where name like '山%';

minus：从第一个查询结果集中减去第二个查询结果中重复的数据

select * from school where name like '%大'
minus
select * from school where name like '山%';

in：表示符合查询结果中的任意一个值

条件 in (值1，值2…)
相当于：条件 = 值1 or 条件 = 值2 or …

select * from school where id in (select sid from student);

not in：表示条件不能是查询结果中的任意一个值

条件 not in (值1，值2…)
相当于：条件 <> 值1 and 条件 <> 值2 and …

select * from school where id not in (select sid from student);

some/any：这个和in用法相同

in用在无符号的情况
some/any用在有符号的情况

 select * from school where id = some (select sid from student);select * from school where id < any (select sid from student);

all：表示比所有值都大，比所有值都小

select * from school where id > all (select sid from student);

***：

any >min

练习
查询工资高于20号部门某个员工工资的员工的信息

*：因为in的效率较低，所以用exists代替
exists：存在

select * from school where exists (select sid from student where student.sid = school.id);

not exists：不存在

select * from school where not exists (select sid from student where student.sid = school.id);

序列：sequence

一个单独的数据对象，每多一条自动加一
*：Oracle通过调用序列来实现主键自增
*：在一个新的会话中，必须调用下一个值才能查看当前值
*：序列调用下一个值，会作为下次调用的初始值

创建序列：

create sequence seq_test – 序列名
increment by 1 – 一次增长1
start with 1 – 从1开始
minvalue 1 – 最小值
maxvalue 100 – 最大值
cycle – 循环 默认不循环
nocache; --不缓存 默认缓存20

查看序列的下一个值：

select seq_test.nextval from dual;

查看序列的当前值：

select seq_test.currval from dual;

1.消除延迟段创建特性

alter system set deferred_segment_creation = false;

2.创建表

create table test2(id number(3),name varchar2(20));

3.创建序列

create sequence seq_test2;

4.新增数据

insert into test2 values(seq_test2.nextval,'橘子');
insert into test2 values(seq_test2.nextval,'苹果');

5.查询数据

select * from test2;

查看当前用户下有哪些序列：

select * from user_sequences;

如何修改序列：

alter sequence seq_test increment by 50;

如何删除序列：

drop sequence seq_test;