oracle学习笔记(二)

1.转义字符，可以使用escape自定义转义字符。

select ename from emp where ename like '%$%%' escape '$';

表示查出含有%的，其中$是转义字符(自己指定的)，默认的转义字符是\

2.排序用order by，去重用distinct。如

select distinct deptno from emp;//去重
select * from emp order by deptno desc;//desc降序排列,asc升序排列，默认的

3.统计用count(唯一键)，一般是主键，若没有唯一键，则用count(*)来统计。

select count(*) from emp;//表示查询有多少条(不为空的)记录,结果为14
select count(*) from emp from where deptno = 10;//结果为3
select count(comm) from emp;//结果为4，因为只有4个有值，其他的都为null
select count(deptno) from emp;//结果为14，只要不为null，就会被count
select count(distinct deptno) from emp;//结果为3

4.日期的比较，注意日期格式为dd-m月-yy
查询雇佣年份为1981年的雇员姓名

select ename from emp where hiredate like '%81%';
//注意年份为两位数字，不是四位，否则查询不到任何结果

查询雇佣日期为1981以后的雇员信息

select * from emp where hiredate >= '01-1月-81';
//注意年份仍为两位数

//查询EMP表显示在1981年2月1日到1981年5月1日之间雇佣的雇员名、岗位及雇佣日期，并以雇佣日期进行排序。
select ename,job,hiredate from emp 
where hiredate between '1-2月-81' and '1-5月-81'
order by hiredate;

select ename,job,hiredate from emp 
where hiredate between to_date('1981-2-1','yyyy-mm-dd') and to_date('1981-5-1','yyyy-mm-dd')
order by hiredate;

这两种方法均可。

5.建表时若有not null 和default，则default应放在前边

6.分组函数是多行输入，只有一行输出。所以相求领工资最高的人的姓名不能用

select ename,max(sal) from emp;

这样会提示错误，因为有可能有很多人工资都一样高，而分组函数只能有一行输出，所以正确的写法为：

select ename from emp where sal = (select max(sal) from emp);

7.求每一个部门里边工资最高的人的名字
错误写法为：

select ename from emp where sal = (select max(sal) from emp group by deptno);

正确的应为

select emp.deptno,ename,sal 
from emp 
join (select deptno,max(sal) max_sal from emp group by deptno) t 
on (emp.sal = t.max_sal and emp.deptno=t.deptno);

这使用了表的连接。

8.where,group by,having,order by 执行顺序为where,group by,having,order by
而having是对分好的组（group by之后的）再进行限制，即having是来修饰group by的。如

select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

顺序也得保持固定

select * from emp
where sal > 2000 //对数据进行过滤
group by deptno  //对数据分组
having           //对分组进行限制
order by ;	 //对产生的最后结果进行排序
//最后再排序，顺序不能调换，因为也是按照这种顺序解析

9.事物表示一些列操作完整进行后才算成功，如果一项成功，另一项失败，则整个事物操作不成功，需要回滚。只有两项都成功了，才会调用commit()来提交，否则调用rollback()执行回滚。

10.若查的字段里有单引号，如a'ssssd,则sql语句应写为

select 'a''ssssd' from table_test;

注意此时a与s之间用 两个单引号，而不是双引号，更不是一个单引号，这样才能正确查询。

11.处理空值，用nvl(原值,新值)。如nvl(comm,0)表示若comm不为空值，则使用comm的本来值，若comm为null，则用0来代替。

12.分组函数是多行输入，只有一行输出。
group by 有一个原则,group by 后面的字段在select后面一定要存在，否则编译不过去.[出现在select中的字段，如果没有出现在组函数(就是max，min，avg,sum,count这五个函数)中，就必须出现在 group by 子句内]

select deptno,max(sal) from emp group by deptno;//可运行

而像这样的

select ename,max(sal) from emp group by deptno;

就会出错，因为ename既没有出现在组函数max内，也没有出现在group by 之内，所以出错。

select deptno,max(sal) from emp group by deptno;//deptno在group by内出现，sal在组函数max()出现。能得到结果

//查找每个部门中最高薪水人的姓名
select ename,max(sal) from emp group by deptno;//会报错。max(sal)只有一个值，但有最高工资的可能有多个人，所以会报错。应该使用子查询。


select ename,sal,deptno from emp where sal = (select max(sal) from emp group by deptno);
//错误写法，因为括号内有三个值。会爆出单行子查询返回多个值

select ename,sal,deptno from emp where sal in (select max(sal) from emp group by deptno);
//将“=”换成in，虽然结果正确，但sql语句不严谨。意思就是只要sal的值与括号内子查询的值相同就列出结果。括号内能得到3个值，因此只要等于其中之一就会列出。因此就可能出现2组

的最高值为1000,3的最高值为2000.本来3组只应列出2000的信息，但这样写也会把3组sal为1000的人也列出来。数据一多，就会出错。

select ename,sal from emp
join(select max(sal) max_sal,deptno from emp group by deptno) t
on (emp.sal=t.max_sal and emp.deptno=t.deptno);
//这使用了表连接。最严谨的语句，t为新建表的别名

13.分组即可按照一个字段，也可按照多个字段。

group by deptno;//按照deptno分组，deptno相同的分为一组，这是按照一个字段进行分组
group by dpptno,job;//按照两个字段分组，这两个字段的值都相同的才分为一组

14.in和exists的区别
in相当于“=”，例如若有where id in(1,2,3)则相当于id=1 or id=2 or id=3

//查询部门编号为20或30的员工名称、工资、佣金
select ename,sal,comm from emp where deptno =20 or deptno=30;
select ename,sal,comm from emp where deptno in (20,30);

这两个查询语句等价。
而exists语句返回一个bool值，true或false

15.有时可以对统一张表赋予不同的别名，当表内值有关联时。

列出入职日期早于其直接上级的所有雇员。

select e1.* from emp e1,emp e2
where e1.hiredate < e2.hiredate and e1.mgr = e2.empno;
//这其中e1表示下级表，e2为上级表

这是emp表中的全部字段。

16.mysql和oracle的分页

①mysql分页

select * from t_user where …… limit ?,?;//第一个参数返回第一行在所有数据中的位置，从0开始。第二个参数指定返回行数
select * from t_user where … limit 10;//返回前10行
select * from t_user where……limit 0,10;//返回前10行，这两行等价
//第二个参数为-1可返回至从开始查询的那行到结束的所有数据
select * from t_user where……limit 1,-1;//返回第2行到结束的所有行

②oracle分页

oracle中没有limit关键字，但有 rownum字段。rownum是一个伪列，是oracle自动为查询返回结果的每行分配的编号，第一行为1，第二行为2。如

select * from 
	(select A.*,rownum rn
	from(select * from t_user) A
	where rownum<=40
	)
where rn>=21;

最内层的查询select * from t_user表示不进行分页的原始查询语句，rownum<=40和rn>=21控制分页查询的页数范围。