oracle基础知识总结 part 1 ： select语句

关于“oracle基础知识总结”系列博客的说明：

1 本系列博客是在本人学习过程中总结的笔记，学习资料为尚学堂马士兵老师的oracle视频，大部分示例来自于该资料；

2 做为数据库初学者，本系列博客主要讲解了oracle的基础知识，高手勿入勿喷；

3 由于是学习笔记，主要作用是便于本人以后复习和备忘。可能条理不是很清晰，上下文衔接的不是很好，请读本文的读者做好心理准备；

4 本系列博客中的语句主要使用了oracle中scott用户的表结构，该用户是数据库中自带的用户。

作为一个应用程序的开发者，对数据库所要掌握的知识主要是使用，而不是管理，管理数据库是DBA的工作。所以作为一个程序员，主要是会使用数据库。而开发应用程序的过程中，select语句用的较多。select语句也是数据库中比较难，比较复杂的语句。这篇文章主要总结oracle数据库中select语句相关的知识。有些知识点比较零碎，可能让本文显得比较混乱。

1  desc 。显示表结构（都有哪些字段）。举例：

desc emp;

2  select基本用法举例：查询每个雇员的年薪

select ename ，sal*12 from emp;

3  oracle中空表dual的使用：

select 2*3 from dual;

使用空表查询日期：

select sysdate from dual;

4  给查询出的数据起别名

select ename，sal*12 anual_sal from emp;

如果名字里有空格， 要加双引号

select ename, sal*12 "annual salary" from emp;

5  任何含有空值的数学表达式的计算结果都是空值

select ename, sal*12+comm from emp;

如果津贴comm为null，计算出的年薪也为null

6  字符串连接符||

select ename || sal from emp;
select ename || 'aaa' from emp;

单引号引起来的是字符串常量，若果字符串中有一个单引号， 用两个单引号表示， 第一个单引号用于转义

select ename || 'aaabb''sss' from emp;

7  列名前边加distinct关键字， 去除重复的值， 查出来的行数和表的行数不一致

select distinct deptno from emp;

distinct会作用于多个字段， 指多个字段的组合不能重复

select distinct deptno , job from emp;

8  where过滤条件， 选择特定的行

select ename from emp where deptno=10;

where语句中字符串的比较要用单引号：

select * from emp where ename = 'CLARK';

where语句中的条件判断：

等值                =

大于 >

大于等于 >=

小于 <

小于等于 <=

不等于         <>

字符串也可以用大于和小于比较

select * from emp where ename > 'CLARK';

where语句中的bettween and关键字

select * from emp where sal between 800 and 1500;

包括800和1500, 和下面的语句一样

select * from emp where sal >= 800 and sal <= 1500;

注意where语句中的and关键字， 组合两个条件，连个条件必须同时满足

对空值的判断不能用= null， 而是用is或is not

select * from emp where comm is null;
select * from emp where comm is not null;

where语句中的 in 和 not in 关键字

select * from emp where sal in (800, 1500, 2000);
select * from emp where ename in ('CLARK', 'SMITH', 'ABC');
select * from emp where sal not in (800, 1500, 2000);

日期的处理
关于日期类型的比较， 可以使用日期函数， 也可以直接用相同格式的字符串比较

select ename from emp where hiredate > '17-12月 -81';

-81也可写成1981。日期函数在后面介绍。

where语句中的 or关键字， 两个条件满足一个就行

select * from emp where deptno = 10 or sal > 1000;

like和not like关键字实现模糊查询

%代表0个或多个字符

select * from emp where ename like '%ALL%';

_代表一个字符

select * from emp where ename like '_ALL%';

用\转义通配符

select * from emp where ename like '_A\%L%';

escape关键字指定转义字符

select * from emp where ename like '_A$%L%' escape '$';	

order by关键字实现对查询的结果排序

默认是升序

select * from emp order by sal;

升序

select * from emp order by sal asc;

降序

select * from emp order by sal desc;	

组合where语句和order by语句

select empno, ename from emp where deptno <> 10 order by empno desc;

按两个字段排序。   当deptno相同的时候， 内部按照ename排序

select * from emp order by deptno desc, ename asc;  

综合复习语句：

select ename, sal*12 annual_salary deptno 
from emp
where ename not like '_A%' and sal > 800
order by sal desc, ename asc;

9    单行函数

所谓单行函数， 是指一行记录对应一行输出。

对字符串操作的单行函数：lower，upper ，substr

select lower(ename) from emp;

查出的每一行数据中， 都把ename的值转化成小写， 再作为结果返回

select ename from emp where lower(ename) like '_a%';

在过滤条件时， 把每行的ename转成小写， 在和'_a%'进行模糊匹配， 但是结果中的ename还是大写

select substr(ename, 1, 3) from emp;

在查询的结果中，把每行中的ename截取字符串， 并作为结果返回。 从第一个字符开始截取， 一共截取三个字符


chr函数，将数字按ASCII码转成字符，输出为A

select chr(65) from dual;	

asci函数， 将字符转成对应的ASCII码， 输出为97

select ascii('a') from dual;	

round函数， 四舍五入

select round(23.7923) from dual;

直接四舍五入，输出为24

select round(23.7923， 2) from dual;

输出为23.79， 四舍五入到小数点后两位

select round(23.7923， -1) from dual;

输出为20， 四舍五入到十位数


to_char函数， 按一定的格式转成字符串， 可将数字转成字符串， 也可将日期类型转化成字符串

select to_char(sal, '$99,999.9999') from emp;
select to_char(sal, 'L99,999.9999') from emp;		
select to_char(sal, 'L0000.0000') from emp;

L代表本地货币，$代表美元
格式中9的含义： 在小数点前边时，若该位没有数字， 不显示
格式中0的含义： 在小数点前边时， 若该位没有数字， 补0
以上为数字转换成字符串， 下面为日期格式的转换

select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') from emp;
select to_char(sysdate, 'YYYY-MM-DD HH:MI:SS') from dual;		
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

HH显示12小时制，HH21显示24小时制

to_date函数， 将特定格式的字符串转成日期类型， 方便日期类型的运算

select * from emp where hiredate > to_date('1978-10-21 15:50:23', 'YYYY-MM-DD HH24:MI:SS');

to_number函数， 将特定的字符串按一定的格式转化为数字

select * from emp where sal > to_number('$12,765.3472', '$999,999.9999');

nvl函数， 空值替换函数。 如果comm这个字段中的值为空值（即没存有值）， 那么用0替换它

select sal*12 + nvl(comm, 0) from emp;

10  组函数

组函数，也叫多行函数。多条记录对应一条输出(一共五个, 很重要)。
最大值函数max

select max(sal) from emp;

最小值函数min

select min(sal) from emp;

平均值函数avg

select avg(sal) from emp;

select to_char(avg(sal), '99999999999,99') from emp;
select round(avg(sal), 2) from emp;

求和函数sum

select sum(sal) from emp;

计算记录数count，某个字段， 不是空值的才算一个

select count(*) from emp;
select count(*) from emp where deptno = 10;
select count(ename) from emp;
select count(comm) from emp;	

select count(distinct deptno) from emp;

11  group by语句, 实现分组
每个部门的平均薪水

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

按照两个字段分组

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

所有人里面薪水最高的人的薪水值

select ename, max(sal) from emp ;

这是错误的, 因为max(sal)只对应一个值, 而这个值对应的人可能有多个, 也就是多个人的薪水并列第一
求挣钱最多的人(可能有多个), 只能用子查询

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

每个部门中的最高薪水

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

分组查询中的规则: 出现在select后的字段名, 如果没出现在组函数中, 则必须出现在group by中

12  having 子句， 过滤分组后的记录
按照部门编号进行分组, 每个组中的平均薪水

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

再把平均薪水大于两千的组取出来

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

where语句对单条记录进行过滤, 不能过滤分组后的记录, 对分组查询后的记录进行过滤的是having


13  select语句中各子句的执行顺序

where > group by > having > order by

示例：

select avg(sal) from emp 
	where sal > 1000 
	group by deptno 
	having avg(sal) > 1500 
	order by avg(sal) desc;

14  子查询
薪水最高的人的名字

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

子查询可以出现在where中, 也可出现在from中

求哪些人的工资位于所有人的平均工资之上

selct ename from emp where sal > (selct avg(sal) from emp);

按照部门进行分组,每个部门里面挣钱最多的那个人(连表查询)

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

每个部门的平均薪水所在的薪水等级

select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t jion salgrade s on (t.avg_sal between s.losal and s.hisal); 

15  连表查询
自连接

select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;

注意, 把连接条件卸写在了where中而不是on中

sql1999的表连接
在sql1992中, 过滤条件和连接条件混在了一起

select ename, dname, grade from emp e, dept d, salgrade s where e.deptno = d.deptno and e.sal between s.losal and s.hisal and job <> 'CLERK';

在sql1999中, 连接条件不写在where 中了, 而是使用join on 或join using

select ename, dname from emp cross join dept;

非等值连接

select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

连接三张表

select ename, dname, grade from 
emp e join dept d on (e.deptno = d.deptno)
join salgrade s on (e.sal between s.losal and s.hisal)
where ename not like '_A%';

自连接1999语法

select e1.ename, e2.ename from emp e1 join emp e2 on e1.mgr = e2.empno);

外连接

左外连接, left join 和left outer join一样

select e1.ename, e2.ename from emp e1 left join emp e2 on e1.mgr = e2.empno); 

右外连接  

select ename , dname from emp e right join dept d on (e.deptno = d.deptno);

全外连接

select ename , dname from emp e full join dept d on (e.deptno = d.deptno);

16  rownum字段，实现分段查询

rownum是一个伪字段，并且只能用于< 和<= 而不能用于> , = 和>=

下面语句是可以的：

select ename, sal from emp where rownum <= 5;

而下面语句是不可以的：

select ename, sal from emp where rownum > 5;

把rownum取出来形成一张新的子表,然后用这个子表查询， 就可以用大于号了

select rownum r, ename from emp;
select ename from (select rownum r, ename from emp ) where r > 10; 

oracle中没有limit子句，实现分段查询只能使用rownum，而rownum的使用真的很恶心

求薪水最高的前五名员工， 以下是一些容易迷惑的语句：

select ename, sal from emp where rownum <= 5 order by sal desc ;

上面语句把当前表中的前五个取出来然后再排序，是错误的。

select ename, sal from emp order by sal desc where rownum <= 5 ;

上面语句也是是错误的, 语法错误

select ename, sal, rownum r from emp order by sal desc;

上面语句虽然sal排序了, 但r是乱序的, 并不是从1开始, 而还是排序前的行号，所以不能使用下面的子查询，因为子查询中行号是乱序的：

select ename, sal from (select ename, sal , rownum r from emp order by sal desc ) where r <= 5;

上面语句也是错误的。

select ename, sal from (select ename, sal from emp order by sal desc ) where rownum <= 5;

上面是可以的, 因为子查询中的rownum是原生的

rownum练习：求薪水最高的第六到第十名的员工

select ename, sal from
(
	select ename, sal, rownum r from
	(
		select ename, sal from emp order by sal desc
	) 
) where r >= 6 and r <= 10;

求最后入职的5名员工：

17  select语句练习

1）求部门平均的薪水等级

select deptno , avg(grade) from 
(
    select ename, deptno, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal)
) t group by deptno;

2） 雇员中有哪些人是经理人?

select ename from emp where empno in (select distinct mgr from emp);

3） 不准用组函数, 求薪水的最高值

使用组函数的话很简单：

select max(salary) from emp;

如果不用组函数，用自连接中的非等值连接, 左边表的最大值, 在右边的表中找不到比他大的值

select distinct sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));

4） 求平均薪水最高的部门的部门编号

select deptno from 
(select avg(sal) avg_sal, deptno from emp group by deptno) where avg_sal = 
select max(avg_sal) from (select avg(sal) avg_sal, deptno from emp group by deptno);

5） 求平均薪水最高的部门的部门名称

select dname from dept where deptno = 
(select deptno from 
	(select avg(sal) avg_sal, deptno from emp group by deptno) 
		where avg_sal = 
			(select max(avg_sal) from 
				(select avg(sal) avg_sal, deptno from emp group by deptno
			)
	)
);

其中下面语句

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

可以用组函数嵌套解决, 等价的语句如下:

select max(avg(sal)) from emp group by deptno;

组函数可以互相嵌套, 但只能嵌套两层, 因为组函数多个输入产出一条输出

6） 求平均薪水的等级最低的部门的部门名称

select dname from dept where deptno = 
	(
	    select deptno from 
	    (    
		select deptno , grade from salgrade join 
		(
			select avg(sal) avg_sal, deptno from emp group by deptno
		) t
		on (t.avg_sal between salgrade.losal and hisal)
	    )
	    where grade = 
	    (
		select min(grade) min_grade from
		(
			select deptno , grade from salgrade join 
			(
				select avg(sal) avg_sal, deptno from emp group by deptno
			) t
			on (t.avg_sal between salgrade.losal and salgrade.hisal)
		)
	    )
	);

另一种解法:

select dname from dept join 
(select avg_sal, deptno, grade from salgrade join 
	(select avg(sal) avg_sal , deptno from emp group by deptno) t on 
	(t.avg_sal between salgrade.losal and salgrade.hisal)
) t on (dept.deptno = t. deptno) where t.grade = 
(select min(grade) from 
	( select avg_sal, deptno, grade from salgrade join 
		(select avg(sal) avg_sal , deptno from emp group by deptno) t on 
		(t.avg_sal between salgrade.losal and salgrade.hisal)
	 )
 );

7） 求部门经理人中平均薪水最低的部门名称:

8） 求比普通员工的最高薪水还要高的经理人的名称:

select ename from emp where empno in (select distinct mgr from emp where mgr is not null)
and sal > 
(
	select max(sal) from emp where empno not in 
		(select distinct mgr from emp where mgr is not null)
);