Oracle11g学习笔记三

*注：此笔记为个人在学习Oracle时从教学视频、参考书上摘录整理而成，纯手打完成，如需转载麻烦表明出处，附上连接（http://blog.csdn.net/sherkyoung/article/details/25988931），谢谢！

SQL语法

DML（数据管理语言）

数据伪列（了解）

数据伪列值得是用户不需要处理的列，而是由Oracle自行维护的数据列有两个数据伪列：ROWNUM、ROWID;

 

一、ROWNUM

ROWNUM为每一个显示的记录都会自动随着查询生成行号，例如：

SELSECT ROWNUM,empno,ename,job,hiredate,sal FROM emp ;

此时的ROWNUM行号并不是固定的，而是每次显示记录时生成的。那么有了这个ROWNUM就可以实现数据的部分显示：

范例：查询前5条记录；

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp 

WHERE ROWNUM<6 ;

范例：查询6-10条记录

SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp 

WHERE ROWNUM BETWEEnN 6 AND 10 ;

这个时候并没有返回任何数据，因为ROWNUM并不是真实的列，而想要实现这种查询就必须先查询前10条记录，显示后5条记录，要依靠子查询：

SELECT * FROM(

SELECT ROWNUM rn,empno,ename,job,hiredate,sal

FROM emp

WHERE ROWNUM<=10) temp

WHERE temp.rn>5 ;

如果现在按照这个思路，下面就可以给出日后程序所需要的分页功能的实现：

范例：显示当前5条记录；

当前所在页（currentPage）为1；

每页显示数量为5：

第一页：

SELECT *FROM (

SELECT ROWNUM rn,empno,ename,job,hiredate,sal FROM emp

WHERE ROWNUM<=5) temp

WHERE temp.rn>0 ;

范例：显示中间的5条记录

当前所在页（currentPage）为2；

每页显示的记录长度（linesize）为5 ；

第二页：

SELECT *FROM (

SELECT ROWNUM rn,empno,ename,job,hiredate,sal FROM emp

WHERE ROWNUM<=10) temp 

WHERE temp.rn>5 ;

范例：显示第3页的内容

当前所在页（currentPage）为3 ；

每页显示的记录长度（linesize）为5；

第三页：

SELECT *FROM (

SELECT ROWNUM rn,empno,ename,job,hiredate,sal FROM emp

WHERE ROWNUM<=15) temp 

WHERE temp.rn>10 ;

以上的程序就是分页显示的核心代码。

 

 

二、ROWID（了解）

ROWID表示的是每一行数据保存的无物理地址的编号，例如：

SELECT ROWID,deptno,dname,loc FROM dept ;

此时返回了很多数据：

 

每一条记录的ROWID都不会重复，以一个ROWID为例，说明组成：

AAAL+XAAEAAAAANAAA

|-数据对象：AAAL+X

|-相对文件号：AAE

|-数据块号：AAAAAN

|-数据行号：AAA

 

面试题：请删除表中的重复记录；

现在项目中由于管理不善出现了很多重复信息，现在要求删除所有的重复信息，保留最早的记录信息；

 

先按照查询的方式做，首先找到所有的重复数据，可以采用按照部门分组，统计个数大于1则表示重复。

SELECT dname,loc,MIN(ROWID)

FROM dept

GROUP BY dname,loc

HAVING COUNT(deptno)>1 ;

以上即是查询出所有的存在重复的数据记录。

SELECT dname,loc ,MIN(RAWID)

FROM dept

GROUP BY dname,loc ;

此时返回的ROWID既是最早记录的ROWID，也就是不能删除的最早记录的ROWID。

这是可以开始用DELETE删除重复数据。

DELETE FROM dept 

WHERE ROWID NOT IN(

SELECT MIN(ROWID)

FROM dept

GROUP BY dname,loc);

COMMIT ;

 

在所有伪列之中只有ROWNUM是最为重要的部分，一定要掌握，对于ROWID了解就可以了不用太过深入。

 

 

复杂查询（重点）

1、列出至少有一个员工的所有部门的编号、名称、并统计出这些部门的【平均工资、最低工资、最高工资。

a) 确定所需要的数据表

|-emp表可以查询出员工的数量

|-dept表：部门信息

|-emp表：统计信息

b) 确定已知的关联字段

emp.deptno=dept-deptno

第一步：查询出所有的雇员数量大于1的部门编号

SELECT deptno,COUNT(empno)

FROM emp

GROUP BY(deptno);

第二步：找到部门名称，肯定使用到部门表，因为数据量较小，所以可以采用将emp和dept两个表进行连接，统一采用非分组字段的方式进行查询；

SELECT d.deptno,d.dname,COUNT(e.empno)

FROM emp e,dept d

WHERE d.deptno=e.deptno(+)

GROUP BY d.deptno,d.dname

HAVING COUNT(e.empno)>1 ;

第三步：继续统计工资

SELECT d.deptno,d.dname,COUNT(e.empno),AVG(e.sal),MIN(e.sal),MAX(e.sal)

FROM emp e,dept d

WHERE d.deptno=e.deptno(+)

GROUP BY d.deptno,d.dname

HAVING COUNT(e.empno)>1 ;

 

2、列出薪金比SMINTH或ALLEN多的所有员工的编号、姓名、部门名称、领导姓名

a) 确定所需要的数据表

|-emp表：查询出SMITH和ALLEN的工资

|-emp表：最终的显示需要姓名、编号

|-emp表：领导的姓名，自身关联

|-dept表：部门名称

b) 确定已知的关联字段

|-雇员和领导：emp.mgr=temp.empno;

|-雇员和部门：emp.deptno=dept.emptno

第一步：找出SMITH、ALLEN的工资：

SELECT sal,comm

FROM emp

WHERE ename IN('SMITH','ALLEN');

第二步：以上的查询返回的是多行多列记录，按照子查询的要求在WHERE子句中比较合适，继续查询符合此要求的员工的编号、姓名。

SELECT empno,ename

FROM emp

WHERE sal>ALL(

SELECT sal

FROM emp

WHERE ename IN('SMITH','ALLEN'));

第三步：查询出部门名称，引入部门表和消除笛卡尔积的条件；

SELECT e.empno,e.ename,d.dname

FROM emp e,dept d

WHERE sal>ALL(

SELECT sal

FROM emp

WHERE ename IN('SMITH','ALLEN'))

AND d.deptno=e.deptno;

第四步：领导的信息需要emp与自身关联

SELECT e.empno,e.ename,d.dname,e.ename

FROM emp e,dept d,emp m

WHERE e.sal>ALL(

SELECT sal

FROM emp

WHERE ename IN('SMITH','ALLEN'))

AND d.deptno=e.deptno

AND e.mgr=m.deptno(+);

 

 

总结

1、多表查询：在进行查询语句的编写的时候，一定要确定所需要关联的数据表，而且只要是表的关联查询，就一定会存在笛卡尔即的问题，使用关联字段消除此类问题；

在使用多表查询的时候要考虑到左右连接的问日，Oracle之外的数据可可以使用SQL:1999语法进行左右连接控制。

 

2、所有的统计函数是用于进行数据统计操作的，而统计主要在分组中进行（或者单独使用），分组使用GROUP BY子句，是在某一列上存在重复数据的时候才会用分组操作，而进行分组操作之后的过滤使用HAVING子句完成，所有的分组函数可以嵌套，但是嵌套之后的分组函数之中不能再有其他的查询字段，包括分组字段；

 

3、子查询：结合限定查询、多表查询、分组统计查询完成各个复杂查询的操作，子查询一般在WHERE和FROM子句之后出现比较多；

 

4、数据库的更新操作一定还要收到事务控制，事务的两个命令ROLLBACK、COMMIT，每一个连接到数据上的用户都单独使用一个SESSION表示；

 

5、数据表的分页查询显示依靠ROWNUM实现，这个在以后开发中十分重要。

 

6、数据库的更新操作：

a) 增加：INSERT INTO 表名称(字段1，字段2...) VALUES(值1，值2...)

b) 修改：UPDATE 表名称 SET 字段1=值1，字段2=值2，...[WHERE 更新条件]

c) 删除：DELETE 表名称 [WHERE 删除条件(s)] ;