一、子查询
子查询就是在一个查询之中嵌套了其他若干查询,子查询是简单查询,限定查询,多表查询,统计查询的综合体。理论上子查询可以出现在查询语句的任意位置上,但是子查询出现在WHERE和FROM语句较多,子查询语法格式如下:子查询使用"()"括起来
SELECT [DISTINCT] *| 分组字段1 [别名][,分组字段2 [别名]...]|统计函数,(
SELECT [DISTINCT] *| 分组字段1 [别名][,分组字段2 [别名]...]|统计函数
FROM 表名称 [别名][,表名称 [别名]...]
[WHERE 条件(s)]
[GROUP BY 分组字段1,[分组字段2],...]
[ORDER BY 排序字段 ASC|DESC [,排序字段2 ASC|DESC]] )
FROM 表名称 [别名][,表名称 [别名]...],(
SELECT [DISTINCT] *| 分组字段1 [别名][,分组字段2 [别名]...]|统计函数
FROM 表名称 [别名][,表名称 [别名]...]
[WHERE 条件(s)]
[GROUP BY 分组字段1,[分组字段2],...]
[ORDER BY 排序字段 ASC|DESC [,排序字段2 ASC|DESC]] )
[WHERE 条件(s),(
SELECT [DISTINCT] *| 分组字段1 [别名][,分组字段2 [别名]...]|统计函数
FROM 表名称 [别名][,表名称 [别名]...]
[WHERE 条件(s)]
[GROUP BY 分组字段1,[分组字段2],...]
[ORDER BY 排序字段 ASC|DESC [,排序字段2 ASC|DESC]] )]
[GROUP BY 分组字段1,[分组字段2],...]
[ORDER BY 排序字段 ASC|DESC [,排序字段2 ASC|DESC]]
子查询主要存在于WHERE和FROM语句中:
WHERE:子查询返回单行单列,单行多列或者多行单列的语句
FROM: 子查询返回多行多列的数据作为一张临时表
1、子查询在WHERE字句中
a. 返回单行单列 例:查询比SMITH工资还高的全部雇员信息
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='SMITH') ;
例:查询高于公司平均工资的雇员信息
SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp) ;
b. 返回单行多列
例:SELECT * FROM emp WHERE (job,sal)=(SELECT job,sal FROM emp WHERE ename='ALLEN') ;
c. 返回多行单列 此时需要使用三种判断符:IN ANY ALL
1)IN操作符:用于指定一个子查询的判断范围
例:SELECT * FROM emp WHERE sal IN(SELECT sal FROM emp WHERE job='MANAGER') ;
2)ANY操作,分为=ANY >ANY <ANY 三种操作
=ANY:作用和IN操作一样
例:SELECT * FROM emp WHERE sal =ANY(SELECT sal FROM emp WHERE job='MANAGER') ;
>ANY:比子查询中数据最小值大的数据
例:SELECT * FROM emp WHERE sal >ANYSELECT sal FROM emp WHERE job='MANAGER') ;
>ANY:比子查询中数据最大值小的数据
例:SELECT * FROM emp WHERE sal <ANY(SELECT sal FROM emp WHERE job='MANAGER') ;
3)ALL:与每一个内容相匹配,分为>ALL 和<ALL 两种操作
>ALL:比字查询返回记录最大值还要大的数据
例:SELECT * FROM emp WHERE sal >ALL(SELECT sal FROM emp WHERE job='MANAGER') ;
<ALL:比字查询返回记录最小值还要小的数据
例:SELECT * FROM emp WHERE sal <ALL(SELECT sal FROM emp WHERE job='MANAGER') ;
综合示例:查询部门编号,部门名称,位置,部门人数,平均工资。
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d
WHERE e.deptno(+)=d.deptno GROUP BY d.deptno,d.dname,d.loc ;
如在FROM字句中使用子查询形式如下:
SELECT d.deptno,d.dname,d.loc,temp.count,temp.avg
FROM dept d,(SELECT deptno dno,COUNT(empno),AVG(sal) avg FROM emp GROUP BY deptno) temp
WHERE d.deptno=temp.dno(+);
子查询要比多表查询更加节省性能,有复杂统计的地方大多需要子查询。
二、数据更新操作
数据更新操作主要包括增加,修改,删除操作,首先复制一张emp表,用于增加、删除、修改操作
复制emp表:CREATE TABLE myemp AS SELECT * FROM emp ; 建立了一myemp表
1、数据增加,数据增加有两种操作:完整型和简便型
语法:INSERT INTO 表名称 [(字段1,字段2,...)] VALUES(值1,值2,...) ;
增加数据分为如下几种类型:
增加数字:直接编写,如123 ; 增加字符串:使用“‘’”单引号
增加DATE型数据:参照已有DATE格式写成字符串,例如‘17-08月-80’;利用TO_DATE函数;使用SYSDATE
完整型:
INSERT INTO myemp (empno,ename,hiredate,sal,mgr,job,comm)VALUES(8899,'张三',TO_CHAR('1998-08-24','YYYY-MM-DD'),5000,7369,'清洁工',1000) ;
简便型:可以不写字段名称,但必须依照表中顺序给每个字段输入内容
INSERT INTO myemp VALUES(8899,'李四','操作工',7369,SYSDATE,3000,null,30) ;
2、数据修改:
语法:UPDATE 表名称 SET 更新字段1=更新值1,更新字段2=更新值2,...[WHERE 更新条件(s)]
例:更新雇员编号7369的sal为3500,comm为2000,job为MANAGER
UPDATE myemp SET sal=3500,comm=2000,job='MANAGER' WHERE empno=7369 ;
注意:更新操作如果不加更新条件意味着更新全部数据,但是这种做法在大数据时会影响性能。
3、删除操作:
语法:DELETE FROM 表名称 [WHERE 删除条件(s)]
例:删除所有在1987年雇佣的员工
DELETE FROM myemp WHERE TO_CHAR(hiredate,'yyyy')=1987 ;
注意:如果删除没有匹配条件,意味着删除全部数据,如果在大数据量是如此操作会影响性能。
一般而言,删除操作可能的少使用,包括在进行系统开发时,对于所用可能用到的删除操作一定要增加提 示信息,以防止用户误操作。
三、事务处理
对于数据表的处理,查询比更新操作更加安全,因为更新操作可能会出错。
在Oracle中所有事务操作都是在session中完成的,每一个session之间彼此独立没有关联,不会有任何通讯,而每一个session独享自己的事务控制,事务控制主要使用以下两个命令:
事务的回滚操作:ROLLBACK 更新操作回到原点
事务的提交操作:COMMIT 真正发出更新操作,一旦提交后无法回滚。
注意:在一个session进行事务更新操作还没有提交时,其他session是无法更新的,必须等待之前的session提交后才可以,称为死锁。
所有的数据更新一定会受到事务的控制。
四、数据伪列
数据伪列指的是用户不需要处理的列,而是Oracle自行维护的数据列,在Oracle中有两个数据伪列:ROWNUM和ROWID 。
(1)ROWUNM:行号 为每一个显示的记录动态分配一个行号,不固定
例:查询emp表中前5条记录
SELECT ROWNUM,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=5 ;
例:查询6-10条记录
由于ROWNUM不是真实的列,而要真正实现这种查询,思路是:先查询前10条记录之后再显示后5条,此时要使用子查询完成
SELECT * FROM (SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=10) temp WHERE temp.m>5 ;
ROWNUM主要用于分页显示功能的实现:
例如:显示前5条记录,CurrentPage:1,LineSize:5
SELECT * FROM (SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=5) temp WHERE temp.m>0 ;
显示前5条记录,CurrentPage:2,LineSize:5
SELECT * FROM (SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=510) temp WHERE temp.m>5 ;
显示前5条记录,CurrentPage:3,LineSize:5
SELECT * FROM (SELECT ROWNUM m,empno,ename,job,hiredate,sal FROM emp WHERE ROWNUM<=15) temp WHERE temp.m>10 ;
(2)ROWID:表示每一行数据保存的物理地址编号。
SELECT ROWID,empno,ename FROM emp ;
每一条记录有一个ROWID,不会重复,如AAAL+XAAEAAAAANAAA ;
数据对象号:AAAL+X
相对文件号:AAE
数据块号:AAAAAN
数据行号:AAA
*************by jixiangrurui 转载请注明出处*************