Oracle数据库应用(伪列、伪表、分页、查询)

1.伪列

创建表时，没有定义过的列，不在表结构中，为存储在表中，查询后，自动附加的列，查询时，自动生成值，只能查询，不能update、delete、insert。常见的伪列分为以下几种。详细介绍

1.1 RowID:

储存表中每一行在记录的物理地址,唯一可以标识表中的一行,插入记录产生的。使用RowID可以快速的定位表中的某一行。(是一种数据类型,不可更改的,索引都是基于伪列的,使用RowID是查询效率最高的一种方式)。一旦一行数据插入数据库，则rowid在该行的生命周期内是唯一的，即使该行产生行迁移，行的rowid也不会改变

例如: select  rowid as 伪列 ,  e.job from  emp e

1.2 RowNum:

是查询数据生成的,临时的,通常分页的时候用到。

在对表的查询中，每返回一条记录，rownum伪列就返回一个数字，代表查询返回的行的编号

2.伪表

 2.1 DUAL

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

SELECT  SYSDATE  from  dual

3.分页

rownum

--方法一

--第一页

select * from (select rownum 序号 ,emp.* from emp) e where 序号 between 1 and 4;

--第二页    假设emp表中有1000W 行数据  首先在子查询中将1000W 行数据查询出来

--然后根据外层查询的条件再查询一次

select * from (select rownum 序号 ,emp.* from emp) e where 序号 between 5 and 8;

 --方法二  查询效率比第一种 高50%

根据oracle 查询的优化  在内层查询的执行时候它会把外层查询的条件自动加进去

 --最内层查询的时候并没有查询所有数据 而是 根据外层查询的限制 只查询了9条

 --最外层查询 再对9 条数据有进行筛选查了

  select * from (  select * from (select rownum 序号 ,emp.* from emp) e where 序号<9) where 序号>4

4. 查询

列名使用了关键字

在Oracle 中只能用双引号 " 包围关键字。select "JOB" from  emp;

5. 字符串

• 字符串可以是 SELECT 列表中的一个字符,数字,日期。
• 日期和字符只能在单引号中出现。
• 每当返回一行时，字符串被输出一次。

select ename || ' is  a ' || job  from emp;

6.连接符

把列与列，列与字符连接在一起。

用 ‘||’表示。

可以用来‘合成’列

select ename||job from emp; SELECT   ename || ' 的岗位是 ' || job  as 数据  from emp

7.联合主键

--联合主键 CREATE  table result(   studntno  CHAR(5)  ,    subjectid number  ,   score  number(5,2),  primary key(列1,列2) ) --方法2  alter table 表名  add constraint 主键约束名 primary key(列1,列2)

8. 重复行

默认情况下，查询会返回全部行，包括重复行。

select   job from  emp

9. 删除重复行

在 SELECT 子句中使用关键字 ‘DISTINCT’ 删除重复行

Select  DISTINCT  job from  emp

10. 过滤和排序数据

过滤 ：WHERE 子句

SELECT  * from emp  where deptno=10  ORDER BY sal desc

11. 字符和日期

字符和日期要包含在单引号中。

字符大小写敏感，日期格式敏感。

默认的日期格式是 DD-MON月-RR

BETWEEN

使用 BETWEEN 运算来显示在一个区间内的值

SELECT  * from emp  where sal BETWEEN 5000 and 10000

IN

使用 IN运算显示列表中的值

SELECT  * from emp  where deptno in(10,20)

LIKE

• 使用 LIKE 运算选择类似的值
• 选择条件可以包含字符或数字:
  • % 代表零个或多个字符(任意个字符)。
  • _ 代表一个字符。

--查询员工姓名中包含 k的员工信息 SELECT  * from emp where ename   like '%K%' --查询员工姓名中倒数第二个字母是 K的员工信息 SELECT  * from emp where ename   like

NULL

• 使用 IS (NOT) NULL 判断空值

SELECT  * from emp where  comm is  not null

ORDER BY子句

使用 ORDER BY 子句排序

ASC（ascend）: 升序

DESC（descend）: 降序

ORDER BY 子句在SELECT语句的结尾

分组函数

分组函数作用于一组数据，并对一组数据返回一个值

AVG, COUNT,MAX,MIN,SUM

COUNT（计数）函数

COUNT(*) 返回表中记录总数,适用于任意数据类型

COUNT(expr) 返回expr不为空的记录总数

SELECT count(*)  from emp   select  count(comm)  from emp

DISTINCT 关键字

• COUNT(DISTINCT expr)返回expr非空且不重复的记录总数

GROUP BY

过滤分组： HAVING 子句

嵌套组函数

显示各部门平均工资的最大值

SELECT   MAX(AVG(salary)) FROM     employees GROUP BY department_id;

注意:

• 不能在 WHERE 子句中使用组函数。
• 可以在 HAVING 子句中使用组函数。
• 子查询

• 多行子查询

•