二、scott用户、单行函数、多行函数、分组查询、多表查询、子查询、分页查询

oracle自带scott用户，密码tiger，但是刚开始是锁定状态，需要解锁。

--解锁
alter user scott account unlock;
--设置密码
alter user scott identified by 19950909;

然后切换到scott用户下面。
scott用户下有三张主要的表。第一张emp表。

第二张dept表。

第三张salgrade表。

下面是根据这几张表，进行简单的数据库语言学习。

1.单行函数：作用于一行，返回一个值

 -----------------------------------------字符函数
select upper('yes')from dual;--小写变大写
select lower('YES')from dual;--大写变小写
--------------------------------------------数值函数
select round(26.18)from dual;--四合五入取整
select round(26.16,1)from dual;--1是保留的位数
select round(26.16,-1)from dual;
select trunc(26.18,1)from dual;--直接截取
select mod(10,3)from dual;--求余数
--------------------------------------------日期函数
---查询出emp表中所有员工入职距离现在几天
select sysdate-e.hiredate from emp e;
-- sysdate是系统时间减去现在时间e.hiredata
--算出明天此刻
select sysdate+1 from dual;
--查询出emp表中所有员工入职距离现在几月
select months_between(sysdate,e.hiredate) from emp e;
--几年
select months_between(sysdate,e.hiredate)/12 from emp e;
--几个礼拜
select (sysdate-e.hiredate)/7 from emp e;
select round((sysdate-e.hiredate)/7) from emp e;
--------------------------------------------转换函数
--日期转字符串(字符串类型)
select to_char(sysdate,'yyyy-mm-dd hh:mi:ss')from dual;
select to_char(sysdate,'fm yyyy-mm-dd hh:mi:ss')from dual;
select to_char(sysdate,'fm yyyy-mm-dd hh24:mi:ss')from dual;
--字符串转日期（日期类型）
select to_date('2021-07-14 09:37:57','fm yyyy-mm-dd hh24:mi:ss')from dual;
--------------------------------------------通用函数
--算出emp表中所有员工的年薪
select e.sal*12 from emp e;
--奖金里面有null，如果null值和任意数字做算术，结果都是null；
select e.sal*12+nvl(e.comm,0) from emp e;
--如果有null则使用0，无则直接加奖金

2.条件表达式

--给EMP表中员工起中文名，等值判断
select e.ename from emp e;
select e.ename,
       case  e.ename
         when 'SMITH' then '小白兔'
           when 'ALLEN' then '白又白'
             when 'WARD' then  '两只耳朵'
               else'竖起来'--这一行可去掉
               end
 from emp e;
--判断员工工资，高于3000,1500-3000；低于3000；范围判断
select e.sal,
       case  
         when e.sal>3000 then '高'
           when e.sal<1500 then '中'
               else'低'
               end
 from emp e;
---orcal专用条件表达式，但是为了保持代码兼容性还是用通用表达式。
select e.ename,
        decode( e.ename,
         'SMITH' , '小白兔',
           'ALLEN' ,'白又白',
             'WARD',   '两只耳朵',
               '竖起来')
       from emp e;
       
 --orcal中除了起别名，都用单引号。
 select e.ename,
        decode( e.ename,
         'SMITH' , '小白兔',
           'ALLEN' ,'白又白',
             'WARD',   '两只耳朵',
               '竖起来')中文名
 from emp e;

select e.ename,
        decode( e.ename,
         'SMITH' , '小白兔',
           'ALLEN' ,'白又白',
             'WARD',   '两只耳朵',
               '竖起来')"中文名"
            
 from emp e;

3.多行函数：作用于多行，返回一个值。

select count(1) from emp;--查询总数量，也可以用count(*)
select sum(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select avg(sal) from emp;

4.分组查询
–查询出每个部门的平均工资
–分组查询，出现在group by后面的原始列，才能出现在select后面。
–如果想在select后面出先，必须加上聚合函数。
–聚合函数可以把多行记录变成一个值

select  e.deptno,avg(e.sal)--,e.namee
from emp e
group by  e.deptno;

--查询出平均工资高于2000的部门信息
select  e.deptno,avg(e.sal)
from emp e
group by  e.deptno
having avg(e.sal)>2000;

select  e.deptno,avg(e.sal) 别名
from emp e
group by  e.deptno
having avg(e.sal)>2000;

---所有条件都不能使用别名来判断。
select *from emp where sal>1500;
select ename,sal from emp where sal>1500;
select ename,sal 别名 from emp where sal>1500;
--错误select ename,sal 别名 from emp where 别名>1500;

--查询每个部门高于800的员工的平均工资
select  e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by  e.deptno；

---where是过滤分组group by前的数据，haxing是过滤分组group by之后的数据。
--查询出每个部门工资高于800的平均工资，然后查询平均工资高于2000的部门。
select  e.deptno,avg(e.sal)
from emp e
where e.sal>800
group by  e.deptno
having  avg(e.sal)>2000;

5.多表查询
5.1 笛卡尔积-两表相乘–一般无意义

select *from emp e,dept d;

5.2 等值连接

select*
from emp e,dept d
where e.deptno=d.deptno;

5.3 内连接
—内连接和等值连接一样，一般使用等值连接（无人部门查不出来）

select*
from emp e inner join dept d
on e.deptno=d.deptno;

5.4外连接
又分为左连接和右连接。
–查询出所有部门，以及部门下的员工信息（可以查出无人部门）

select *
from emp e right join dept d--主要查询的是部门，所以右链接，以部门为主
on e.deptno=d.deptno;

–以员工为主
–左连接是查询所有员工，以及所属的部门。(无人的部门就查不出来。)

select *
from emp e left join dept d
on e.deptno=d.deptno;

—oracle中专用外连接，但是尽量使用上面的通用方法

select*
from emp e,dept d
where e.deptno(+)=d.deptno;--右连接

select*
from emp e,dept d
where e.deptno=d.deptno(+);--左连接

5.5自连接
–自连接：在不同角度把一张表看几张表，得到条件下的数据
–查询员工empno姓名(哪张表)，员工领导mgr姓名(哪张表),都在一张表呢

select e1.ename,e2.ename
from emp e1,emp e2
where e1.mgr=e2.empno;---e1表中的领导是e2表中的员工
--所以e1是员工表，e2是领导表。(没有领导的会被过滤)

—查询员工姓名，员工领导姓名，员工部门名称，员工领导部门名称。

select e1.ename,e2.ename,d1.dname,d2.dname
from emp e1,emp e2,dept d1,dept d2
where e1.mgr=e2.empno
and e1.deptno=d1.deptno
and e2.deptno=d2.deptno;

—在上面基础上查询员工领导在同一个部门，名称

select e1.ename,e2.ename,d.dname
from emp e1,emp e2,dept d
where e1.mgr=e2.empno
and e1.deptno=d.deptno
and e2.deptno=d.deptno;

—查询员工姓名，员工领导姓名，员工部门名称，员工领导部门名称。
–员工薪资等级和领导薪资等级

select 
  e.empno,
  e.ename, 
  decode(s.grade,
    1,'一级',
    2,'二级',
    3,'三级',
    4,'四级',
    5,'五级') grade, 
  d.dname,
  e1.empno, 
  e1.ename, 
  decode(s1.grade,
    1,'一级',
    2,'二级',
    3,'三级',
    4,'四级',
    5,'五级') grade
  from emp e, emp e1, dept d, salgrade s, salgrade s1 
where 
  e.mgr = e1.empno
  and e.deptno = d.deptno
  and e.sal between s.losal and s.hisal
  and e1.sal between s1.losal and s1.hisal;
  

6.子查询
6.1子查询返回一个值
–查询工资和SMITH一样的员工信息

select*from emp where sal=    --写等号有点隐患
  (select sal from emp where ename='SMITH');--一个值
  

6.2子查询返回一个集合
–查询出工资和10号部门任一员工一样的工资信息
条件例如叫SMITH的可能重名，所以不用=要用in ,是主键不会重复

select*from emp where sal in        --用in
  (select sal from emp where deptno =10);--一个集合
 

6.3子查询返回一个表
–查询每个部门最低工资和最低工资的员工姓名，该员工所在的部门。
–查询每个部门最低工资

select deptno ,min(sal) 最低工资
  from emp
  group by deptno;
  

6.4三表连查

select t.deptno,t.最低工资,d.dname
  
  from (select deptno ,min(sal) 最低工资
  from emp
  group by deptno) t, emp e,dept d
  
  where t.deptno=e.deptno
  and t.最低工资=e.sal
  and e.deptno=d.deptno;

7.分页查询
–rownum行号：当我们做select操作的时候，每查询一次就会在该行增加一个行号
–行号从1开始，依次递增，不能跳着走。

--倒序
select *from emp e order by e.sal desc;
--倒序前三
--排序操作会影响rownum的顺序
select rownum ,e.*from emp e  order by e.sal desc;
--如果涉及到排序，可以嵌套查询。
select rownum ,t.*from
(select*from emp  order by sal desc) t;

--emp表工资倒叙排列后，每页5条记录，查询第二页。
select rownum ,t.*from
(select*from emp  order by sal desc) t
where rownum<11;--and rownum>5
--where先执行，在执行select.
--rownum不能跳着走，rownum行号在where后不能大于正数rownum>11.

所以标准正确的分页格式为：

select*from(
select rownum a,t.*from
(select*from emp  order by sal desc
) t where rownum<11)
where a>5;