Oracle课程：第二课

 常用的SQL函数：
lower(字段名);
把某个字段的内容全部转换为小写的。

substring();截字符串
char();转换字符
ascii()转换成ascii码
round()四舍五入
日期的转换
to_char(字段名，‘YYYY-MM-DD HH:MI:SS')

转换日期：to_date()

字符串转换为数字：to_number()

组函数：MAX（最大值）,MIN（最小值）,AVG（平均值）,SUM（求和）,COUNT（数量）
如：select MAX(SAL)FROM EMP;

group by:分组函数

having语句：对分组进行限制

多表连接： select ename,dname from emp join dept
          on (emp.deptno=dept.deptno);

外连接:left join,right join
全外连接：full join

一些关于多表连接的实例
部门平均薪水等级

select deptno,grade from
 (select deptno,avg(sal) avg_sal from emp
group by deptno) t
 join salgrade s on(avg_sal between s.losal and s.hisal);
-------------------------------
部门的平均薪水等级

 select avg(grade) from
  (select ename,sal,grade,deptno from
  (select ename,sal,deptno from emp) s join salgrade t on(sal between losal and hisal))a
 group by deptno;
-------------------------------
哪些人是经理

 select distinct ename
 from emp e join (select mgr from emp) s on (e.empno=s.mgr);
-------------------------------
不用组函数求薪水的最高值

 select sal from emp
 where sal not in
 (select e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));
--------------------------------
平均薪水最高的部门的部门编号
select deptno,avg_sal 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 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));