Oracle查询学习心得（一）

数据控制语言（DCL）：系统权限授权、回收。Grant,revoke

数据定义语言（DDL）：创建、删除、修改。Create Table、Alter Index..

数据操作语言（DML）：操作内容、查询。Select、insert、update、delete、lock table等语句。

数据事务处理（）：commit、rollback、save point、set transaction

Ⅰ、查询：

①：SELECT字段名列表FROM表名WHERE条件;

SELECT为查询语句的关键字，后跟要查询的字段名列表，字段名列表用来指定检索特定的字段，该关键字不能省略。

EG：SELECT * FROM emp WHERE deptno=10;

②：基本查询和排序

Student模式下查询Scott用户的表

EG：SELECT * FROM SCOTT.EMP;

③：虚列Rownum(显示的是结果的行号，数据库中并没有，必须是详细的列明或emp.*，就是*会出错)

SELECT rownum,ename FROM emp;

④：别名（在列名和别名之间要用AS分隔，AS可以省略。歧义别名用双引号将它们引起来。只能是双引号，不能是单引号）

SELECT ename“Name “, sal *12+5000 AS "工资"FROM emp;

⑤：连接运算符||或者连接函数Concat

SELECTename||’的职务是’||job AS "雇员职务表" FROM emp;

⑥：消除重复行（DISTINCT）

Select distinct job from emp;

⑦：查询结果排序（order by）

SELECT字段列表FROM表名WHERE条件

ORDER BY字段名1 [ASC|DESC][,字段名2 [ASC|DESC]...];

ASC、DESC默认升序

EG：SELECT ename, sal FROM emp ORDER BY sal ,ename;

Ⅱ、条件查询

①：简单条件查询

比较运算符

SELECT ename,job,sal FROM emp WHEREjob='SALESMAN';

②：日期格式（缺省中文日期格式为DD-MM月-YY）

如2003年1月10日应该表示为“10-1月-03”。

SELECT hiredate FROM emp WHERE

hiredate>='1-1月-82';

③：复合条件查询

运算的优先顺序是NOT，AND，OR

SELECT ename, job,sal FROM emp WHEREsal>1000 AND sal<2000;

④：特殊运算符

⑤：LIKE的用法

%：代表0个或多个任意字符。

_：代表一个任意字符。

SELECT * FROM emp WHERE ename LIKE '_A%';

⑥：判断空值（IS[NOT]NULL）

EG：SELECTename, mgr FROM emp WHERE mgr IS NOT NULL;

Ⅲ、函数

①、数值型函数

②、字符型函数

③、日期函数（sysdate--系统日期和时间的虚拟函数）

如：假定当前的系统日期是2003年2月6日，求再过1000天的日期

SELECT sysdate+1000 AS "NEW DATE"

FROM dual;----04-11月-05

④、转换函数

TO_CHAR、TO_DATE、TO_NUMBER.

⑤、日期类型转换

SELECT TO_CHAR(sysdate,'YYYY-MM-DDHH24:MI:SS AM DY') FROM dual;

2017-10-10 15:44:48下午星期二

SELECT TO_CHAR(sysdate,'YYYY"年"MM"月"DD"日"')FROM dual;

2003年11月18日

⑥、数字类型转换

SELECT TO_CHAR(123.45,'0000.00'),TO_CHAR(12345,'L9.9EEEE') FROM dual;

0123.45RMB1.2E+04

⑦、其它函数

Decode函数：

SELECTename,decode(job,'MANAGER', '经理', 'CLERK','职员', 'SALESMAN','推销员', 'ANALYST','系统分析员','未知') FROM emp;

Ⅳ、高级查询

①、多表联合查询

两个表的连接方式：相等连接、不等连接、外连接、自连接

②、相等连接（通过两个表具有相同意义的列，可以建立相等连接条件）

SELECT emp.ename,emp.deptno,dept.dname FROMemp,dept

WHERE emp.deptno=dept.deptno;

③、外连接（相等连接有一个问题：如果某个雇员的部门还没有填写，即保留为空，那么该雇员在查询中就不会出现）

不满足连接条件的行将显示在最后。外连操作符为(+)

右外连接：

SELECT ename,sal,dname FROM emp,dept WHEREemp.deptno(+)=dept.deptno;

或：

SELECT ename,sal,dname FROM emp

right outer join dept on emp.deptno =dept.deptno;

左外连接：

SELECT ename,sal,dname FROM emp,dept

WHERE emp.deptno=dept.deptno(+);

或：

SELECT ename,sal,dname FROM emp

leftouter join dept on emp.deptno = dept.deptno;

④、不等连接

SELECT e.ename, e.sal, s.grade FROM empe,salgrade s

WHEREe.salBETWEEN s.losal AND s.hisal；

⑤、自连接

对于自连接可以想像存在两个相同的表(表和表的副本)，可以通过不同的别名区别两个相同的表

SELECT worker.ename||'的经理是'||manager.ename AS雇员经理FROM emp worker, emp manager WHEREworker.mgr = manager.empno;

Ⅴ、统计查询

①、组函数

Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。

常用组函数

②、统计函数

EG1：求部门10的雇员的平均工资

SELECT AVG(sal) FROM emp WHERE deptno=10;

EG2：求最晚和最早雇佣的雇员的雇佣日期

SELECT MAX(hiredate),MIN(hiredate) FROMemp;

EG3：求雇员表中不同职务的个数

SELECT COUNT( DISTINCT job) FROM emp;

③、分组统计

EG1：按职务统计工资总和

SELECT job,SUM(sal) FROM emp GROUP BY job;

注意1：在查询列中，不能使用分组列以外的其他列，否则会产生错误信息。

注意2：对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，而WHERE从句要出现在GROUP BY从句之前。

EG2：统计各部门的最高工资，排除最高工资小于3000的部门

SELECTdeptno, max(sal) FROM emp GROUP BY deptno HAVINGmax(sal)>=3000；

注意3：HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行

EG3：统计人数小于4的部门的平均工资

select avg(sal) from emp group by deptnohaving count(*) <4;

④、组函数的嵌套使用

EG1：求各部门平均工资的最高值

SELECT max(avg(sal)) FROM emp GROUP BYdeptno;

说明：该查询先统计各部门的平均工资，然后求得其中的最大值。

注意：虽然在查询中有分组列，但在查询字段中不能出现分组列。如下的查询是错误的：

SELECT deptno,max(avg(sal)) FROM emp GROUPBY deptno;

因为各部门平均工资的最高值不应该属于某个部门

⑤、子查询

子查询一般出现在SELECT语句的WHERE子句中，Oracle也支持在FROM或HAVING子句中出现子查询。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号扩起来，并放在比较运算符的右侧。

一：单行子查询

EG1：查询比SCOTT工资高的雇员名字和工资

SELECT ename,sal FROM emp WHEREsal>(SELECT sal FROM emp WHERE empno=7788);

二：多行子查询

EG2：查询工资低于任何一个“CLERK”的工资的雇员信息

SELECTempno, ename, job,sal FROM emp

WHEREsal < ANY (SELECT sal FROM emp WHERE job = 'CLERK')AND job <> 'CLERK';

三：多列子查询

EG3：查询职务和部门与SCOTT相同的雇员的信息。

SELECTempno, ename, sal FROM emp

WHERE (job,deptno) =(SELECT job,deptnoFROM emp WHERE empno=7788);

四：在FROM从句中使用子查询

EG4：查询雇员表中排在第6～9位置上的雇员

SELECT ename,sal FROM (SELECT rownum asnum,ename,sal FROM emp WHERE rownum<=9 )

WHERE num>=6;

Ⅵ、集合运算

①、多个查询语句的结果可以做集合运算

EG1：查询部门10和部门20的所有职务

SELECTjob FROM emp WHERE deptno=10

UNION

SELECTjob FROM emp WHERE deptno=20;

Ⅶ、阶段训练

EG1：显示人数最多的部门名称

SELECT DECODE(dname,'SALES','销售部',

'ACCOUNTING','财务部',

'RESEARCH','研发部',

'未知')部门名

FROM emp,dept WHEREemp.deptno=dept.deptno

GROUP BY dname HAVING COUNT(*)=

(SELECT MAX(COUNT(*)) FROM emp GROUP BYdeptno);

EG2：显示各部门的平均工资、最高工资、最低工资和总工资列表，并按平均工资高低顺序排序

SELECT dname部门,AVG(sal)平均工资,MAX(sal)最高工资,MIN(sal)最低工资,SUM(sal)总工资

FROMemp,dept

WHERE emp.deptno=dept.deptno

GROUP BY dname

ORDER BY AVG(sal) DESC;