oracle 学习笔记(二)

1.清屏

windows：host cls;

linux:host clear;

2.对日期的处理

1)系统默认的类型：

select sysdate from  dual;-->注：sysdate 是系统的当前时间 系统默认格式为：日-月-年

2)to_char()的用法

语法：TO_CHAR(date,'format_model')

格式：

 日期格式的元素

格式	说明	举例
YYYY	Full year in numbers	2011
YEAR	Year spelled out(年的英文全称)	twenty eleven
MM	Two-digit value of month 月份（两位数字）	04
MONTH	Full name of the month（月的全称）	4月
DY	Three-letter abbreviation of the day of the week(星期几)	星期一
DAY	Full name of the day of the week	星期一
DD	Numeric day of the month	02

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

3)修改系统的默认日期类型：

查看系统默认的参数：select * from v$nls_parameters;

修改当前会话中的日期类型的默认格式(只在当前会话有效)

alter session set nls_date_format='yyyy-mm-dd'; // 只对当前session有效

4)使用日期类型函数

SQL> select * from emp where hiredate>to_date('1981-12-31', 'yyyy-MM-dd');
或
SQL> select * from emp where to_char(hiredate, 'yyyy-MM-dd')  > '1981-12-31';

语法：TO_CHAR(date, 'format_model')
语法：TO_DATE(str, 'format_model')

3.排序

语法：order by 列名 asc/desc 注：这里的列名只能是数字,字符串,日期类型

asc 默认就是升序

desc 降序

例：按照员工的姓名排序

例：按照员工的薪水从低到高排序

当order by所在的列中有null，会：
升序时，null的在下面。
降序时，null的在上面。
我们希望，不管升序还是降序，null值的始终在下面

方式一：select empno,ename,sal,comm from emp order by comm nulls last;-->使用nulls last 可以使得null放在最后面

方式二: select empno, ename, job, hiredate, sal, nvl(comm, 0) from emp  order by 6 desc

4.组函数

select 
max(sal) 最高工资, 
min(sal) 最低工资, 
avg(sal) 平均工资, 
sum(sal) 所有员工的工资和, 
count(sal) 领工资的员工数量
from emp;

例：查询emp表中的平均工资

select sum(comm)/count(*) from emp; 这里的count(*) 会查询出包括comm为null的记录数

select avg(comm) from emp;这里的avg会自动虑null 因此comm为null的行不算 这里记录数就为3

因此上述的使用avg应改为：select avg(nvl(comm,0)) from emp; 注：函数可以嵌套使用

注意：

按一个列分组
按多个列分组
参与分组的多个列有一个不相同就是不同的组。

having与where的区别：

Having，是分完组后再进行过滤，只显示符合条件的结果。
在Group by与Having中都不可以使用别名。
与Where的区别
Having是是分完组后再进行过滤。
Where是先过滤，再进行分组操作。
如果可以，尽量写Where条件，不写Having。

5.子查询

当一步不能求解时，可以使用子查询

分为：单行子查询/多行子查询

例：查询工资比Scott高的员工信息

 select ename from emp where sal>(select sal from emp where ename='SCOTT');

注意问题：
1. 子查询相对主查询往右缩进
2. 将子查询放入括号中
3. 换行
4. 可以在主查询的select, from, where, having 都可以放子查询
5. 不可以在主查询的group by 放子查询
6. 主查询和子查询可以不是用一张表，只要子查询返回的结果主查询可以使用，即可
7. 在from后面放子查询(*****)
select 后面放子查询： 该子查询必须是单行子查询
from 后面放子查询: 查询员工的编号和姓名
主查询和子查询可以不是用一张表，只要子查询返回的结果主查询可以使用，即可
例：查询部门名称为ACCOUNTING的员工信息

多行子查询
in: 在集合中
例：查询部门名称为ACCOUNTING和SALES的员工信息

select * from emp where deptno in( select deptno from dept where dname ='ACCOUNTING' or dname='SALES')
any:和子查询中任意（随便）一个值比较
查询工资比10号部门任意一个员工工资低的员工信息

select * from emp where sal<any(select sal from emp where deptno=10);或者

select * from emp where sal<(select min(sal) from emp where deptno=10);
all: 和子查询中所有值比较
查询工资比10号部门所有员工工资低的员工信息

select * from emp where sal<all(select sal from emp where deptno=10);
**
any: 小于集合中的最大值
        all: 小于集合中的最小值
        any: 大于集合中的最小值
        all: 大于集合中的最大值