*注:此笔记为个人在学习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)] ;