/*

以下代码是对emp表/dept表/salgrade表进行显示宽度设置

*/

col empno for 9999;

col ename for a10;

col job for a10;

col mgr for 9999;

col hiredate for a12;

col sal for 9999;

col comm for 9999;

col deptno for 99;

col dname for a14;

col loc for a14;

col grade for 9999;

col tname for a12;

set pagesize 30;

//------------------------------------------------------------------------------------------------------

查询当前日期

select sysdate from dual;

select to_char(sysdate,'YYYY-MM-DD DAY') from dual;

select to_char(sysdate,'yyyy-mm-dd day') from dual;

'格式'中的字符串，大小写不敏感

日期隐式转换，读字符串，判断是否符合日期格式要求，再隐式转换，效率低

select ename,hiredate from emp where hiredate='17-12月-80';

日期显式转换，效率高，项目中提倡使用

select ename,hiredate from emp where hiredate=to_date('1980-12-17','yyyy-mm-dd');

使用to_char(日期，'格式')函数将日期转成字符串，显示如下格式：2013-01-25 今天是 星期五 20:18:24

select to_char(sysdate,'yyyy-mm-dd "今天是" day hh24:mi:ss') from dual;

使用to_char(数值，'格式')函数，显示员工工资，加上$或￥符号和千位符

select to_char(1234,'L9,999') from dual;

select ename,to_char(sal,'$9,999') "美元",to_char(sal,'L9,999') "人民币" from emp;

使用to_number(字符串)函数将字符串"123"转成数字

select to_number('123')+321 from dual;

select to_number('A123')+321 from dual;//错误

使用to_date(字符串，'格式')函数将字符串"1980-12-17"转成日期

select * from emp where hiredate = to_date('1980-12-17','yyyy-mm-dd');

使用nvl(a,b)函数，统计员工年收入

select ename,job,nvl(comm,0) from emp;

使用nul2(a,b,c)函数，统计员工年收入

select ename,job,nvl2(comm,comm,0) from emp;

使用nullif(a,b)函数，比较10和10.0是否相同

select nullif(10,10) from dual;相同返回null

select nullif(10,11) from dual;不相同返回第一个值

select nullif(10,'10') from dual;比较时，二类型必须相同

使用case表达式，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400

select ename "姓名",job "职位",sal "涨前薪水",

case job

when 'ANALYST' then sal+1000

when 'MANAGER' then sal+800

else sal+400

end "涨后薪水"

from emp;

使用decode函数，职位是分析员的，工资+1000；职位是经理的，工资+800；职位是其它的，工资+400【oracle专用】

select ename "姓名",job "职位",sal "涨前薪水",

decode(job,'ANALYST',sal+1000,'MANAGER',sal+800,sal+400) "涨后薪水"

from emp;

从今天开始算，下下一个星期三是多少号？【中文平台/星期一，星期二，星期三，星期四，星期五，星期六，星期日】

select next_day(next_day(sysdate,'星期三'),'星期三') from dual;

函数可嵌套，由内向外执行

//------------------------------------------------------------------------------------------------------

员工总工资，平均工资，四舍五入，保留小数点后2位

select sum(sal) "总工资",round(avg(sal),2) "平均工资"

from emp;

员工最高工资，最低工资分别是多少

select max(sal) "最高工资",min(sal) "最低工资"

from emp;

求员工总人数

select count(*) "总人数" from emp;//11

select count(comm) "总人数" from emp;//4

以上这些函数都不会统计null值

入职最早员工，入职最晚员工

select max(hiredate) "入职最晚员工",min(hiredate) "入职最早员工"

from emp;

以上这些函数都用于数值型，max和min也可用于日期型

统计有佣金的员工人数

select count(comm) from emp;

统计公司有多少个部门，部门不能重复

select distinct deptno from emp;

按部门求出部门平均工资，且平均工资取整数

select deptno "部门号",trunc(avg(sal),0) "部门平均工资"

from emp

group by deptno;

查询部门平均工资大于2000元的部门

select deptno "部门号",trunc(avg(sal),0) "部门平均工资"

from emp

group by deptno

having trunc(avg(sal),0) > 2000;

使用having前提是，必须分组，而且只能出现在group by之后

除30号部门外，查询部门平均工资大于1500元的部门，方式一【having deptno!=30】

select deptno,trunc(avg(sal),0) "部门平均工资"

from emp

group by deptno

having trunc(avg(sal),0)>1500 and deptno<>30;

分析：分组->组过滤器->组过滤器【效率低】

除30号部门外，查询部门平均工资大于1500元的部门，方式二【where deptno!=30】

select deptno,trunc(avg(sal),0) "部门平均工资"

from emp

where deptno<>30

group by deptno

having trunc(avg(sal),0)>1500;

分析：行过滤器->分组->组过滤器【效率高】

where行过滤器【优先】

having组过滤器

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

select max(avg(sal)) "部门平均工资的最大值"

from emp

group by deptno;

//------------------------------------------------------------------------------------------------------

员工表和部门表的笛卡尔集（笛卡尔集表=列数之和，行数之积，笛卡尔集表内中数据是无意义的）

select * from

emp,dept;

(1)使用等值连接，显示员工的编号，姓名，部门名，使用表别名简化【=号】

select e.empno "编号",e.ename "姓名",d.dname "部门名"

from emp e,dept d

where e.deptno=d.deptno;

(2)使用非等值连接（就是不能使用=号），显示员工的编号，姓名，月薪，工资级别

select e.empno "编号",e.ename "姓名",e.sal "月薪",s.grade "级别"

from emp e,salgrade s

where e.sal between s.losal and s.hisal;

(3)使用外连接，按部门10,20,30,40号，统计各部门员工人数，显示部门号，部门名，人数

select e.deptno "部门号",count(*) "员工人数"

from emp e,dept d

where e.deptno=d.deptno

group by e.deptno;

以上代码只将emp表中满足条件的记录查询出来，【但不满足条件的记录却没有查询出来】

目的：就是要将【满足条件的记录】和【不满足条件的记录】也要查询出来。

解决方案：使用外连接

外连接类种：

A）左外连接：（+）符号在=号右边，叫左外连接

B）右外连接：（+）符号在=号左边，叫右外连接

(+)只能出现在where中

select d.deptno "部门号",d.dname "部门名",count(e.empno) "员工人数"

from emp e,dept d

where e.deptno(+)=d.deptno

group by d.deptno,d.dname;

//右外连接

select d.deptno "部门号",d.dname "部门名",count(e.empno) "员工人数"

from emp e,dept d

where d.deptno=e.deptno(+)

group by d.deptno,d.dname;

//左外连接

 

(4)使用自连接，显示"SMITH的老细是FORD"这种格式，表示层次关联的数据，用自连接

select emp.ename || '的老板是' || boss.ename

from emp emp , emp boss

where emp.mgr = boss.empno;

总结：

等值：用的最广，连接表的基础。

*非等值：只要不使用=号就行。

***外连接：主要解决满足条件和不满足条件的记录，在少的一方加上(+)即可

**自连接：主要解决具有层次关系的需求，将一张表看成多次张，

通过等值连接将多张表连接在一起。

注意：这是oracle专用语法

select中出现的非组函数，都必须出现在group by中

这里的组函数就是:count()，max()，min()，avg()，sum()

//------------------------------------------------------------------------------------------------------

子查询解决查询【条件未知】。

查询工资比WARD高的员工信息

步一：查询WARD的工资

select sal from emp where ename='WARD';

步二：查询工资大于1250的员工信息

select * from emp where sal>1250;

子查询：

select *

from emp

where sal>(

select sal

from emp

where ename='WARD'

);

主、子查询都是争对同一张表

查询部门名为'SALES'的员工信息（方式一：子查询）

（内/子）步一：查询"SALES"的部门号

select deptno from dept where dname='SALES';

（外/主查询）步二：查询30号部门的员工信息

select * from emp where deptno=30;

子查询：

select * from emp where deptno=(

select deptno from dept where dname='SALES'

);

主、子查询都是争对不同张表

主、子查询的连接点类型和数量要相同，（前提是使用=号）

查询部门名为'SALES'的员工信息（方式二：多表查询）

select emp.*

from emp,dept

where (emp.deptno = dept.deptno) and (dept.dname = 'SALES');

在oracle11G内部优化后，从查表次数来看，优先选择

“多表查询”-->“子查询”

提前时：

A)二种方式做能完成任务

B)oracle对笛卡尔集做了优化

查询工资最低的员工信息（单行子查询，=号）

步一：查询最低工资的员工

select min(sal) from emp;

步二：查询工资是880的员工信息

select * from emp where sal = 880;

子查询：

select * from emp where sal = (

select min(sal) from emp

);

查询部门名为'ACCOUNTING'或'SALES'的员工（多行子查询，in关键字）

步一：查询部门名是"ACCOUNTING"或"SALES"的部门编号

select deptno from dept where dname in ('ACCOUNTING','SALES');//10,30

步二：查询部门号为10号或30号的员工信息

select * from emp where deptno in(10,30);

子查询：

select * from emp where deptno in(

select deptno from dept where dname in ('ACCOUNTING','SALES')

);

查询工资比20号部门【任意any】一个员工【低<】的员工信息（多行子查询，any关键字）

步一：查询20号部门员的工资

select sal from emp where deptno=20;

步二：查询工资低于"880/2975/3000/1100/3000"任何一个工资的员工信息

select * from emp where sal < any (数组);

子查询：

select * from emp where sal < any (

select sal from emp where deptno=20

);

<any 等价于 小于最高工资

查询工资比30号部门【所有all】员工【低<】的员工信息（多行子查询，all关键字）

步一：查询30号部门员的工资

select sal from emp where deptno=30;

步二：查询工资低于"1600/1250/1250/2850/1500/950"任何一个工资的员工信息

select * from emp where sal < all (数组);

子查询：

select * from emp where sal < all (

select sal from emp where deptno=30

);

<all 等价于 小于最低工资

思考：

1）>any ？

2）>all ?

//------------------------------------------------------------------------------------------------------

使用并集运算，查询20号部门和30号部门的员工信息

步一：查询20号部门的员工

select * from emp where deptno=20;

步二：查询30号部门的员工

select * from emp where deptno=30;

并集运算：

select * from emp where deptno=20

union

select * from emp where deptno=30;

打开时间的开关

set time on;//在提示符中显示当前时间

set time off;//关闭时间

关闭时间的开关

set timing on;//在最后，显示SQL语句执行的时间

set timing off;//关闭SQL执行的时间

使用交集运算，查询工资在1000-2000和1500-2500之间的员工信息（方式一）

select * from emp where sal between 1000 and 2000

intersect

select * from emp where sal between 1500 and 2500;

使用where行过滤，查询工资在1000-2000和1500-2500之间的员工信息（方式二）

select *

from emp

where (sal between 1000 and 2000) and (sal between 1500 and 2500);

使用差集运算，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式一）

select * from emp where sal between 1000 and 2000

minus

select * from emp where sal between 1500 and 2500;

在做集合运算时，

二个集合的列数必须一样

二个集合如果列名不一样，结果取决于第一个集合

使用where行过滤，查询工资在1000-2000，但不在1500-2500之间的员工信息（方式二）

select *

from emp

where (sal between 1000 and 2000) and (sal not between 1500 and 2500);

当多表查询，子查询，集合查询都能完成任务时，按如下方案选择

多表查询->子查询->集合查询

前提：... ...

表查得越少越快

表查字段越少越快

表查记录越少越快

//------------------------------------------------------------------------------------------------------

rowid：通过desc看不见该列，但又存在，与文件系统引用建立关系

【重点】rownum：通过desc看不见该列，但又存在，该值有如下特点

A）永远连续

B) rownum依然可以参与运算

C）rownum可以参与<或<=运算

D）rownum值不能完成=，>，>=运算

E）rownum值<>与<是一样的结果

思考：

1）获取前五条记录

select * from emp where rownum<=5

2）获取第六条记录到第十条记录

启动Oracle11自带的一个SQLDeveloper图形化工具，使用JDK5/6

//------------------------------------------------------------------------------------------------------

动手练习：

将今天讲过的OracleSQL，删除答案，自已练习一遍

//------------------------------------------------------------------------------------------------------

单引号:

A)to_char(a,'格式');

双引号：

B)to_char(a,'格式')该格式中有固定文本时，可以使用双引号“”定界