SQL（Oracle）学习笔记2016-11-04

解锁scott账户

    alter user scott account unlock;

查看表结构

• desc ‘TableName’

    desc emp; 
    desc dept;
    desc salgrade;

查询语句 select * from

查看表中数据

    select * from salgrade;
    select * from dept;
    select * from emp;
    select sal*12 from emp;
    select ename, sal*12 from emp;

虚表dual

    desc dual; 
    select * from dual;
    select 2*3 from dual;
    select sysdate from dual;  -- sysdate - 系统当前时间函数

设置别名

    select ename, sal*12 anuual_sal from emp; 

0和空值(null)是不一样的

    select ename, comm from emp;

• 有空值(null)的表达式结果仍为空值(null)(无论什么运算)。

    select ename, sal*12+comm from emp;

字符串连接符 “||”

    select ename || sal from emp; 

• 保持大小写的字符串放在单引号‘’中，其字符串中的单引号“‘”和“’”用2个表示1个。

    select ename || 'abc' from emp;
    select ename || 'abc''de''fg' from emp;

去重 distinct

    select distinct deptno ,job from emp; 

where 条件过滤（针对from表中的数每条据）

• 等于、大于

    select * from emp where deptno = 10;
    select * from emp where sal > 1500;
    -- 不等于 <>

• and 连接2个条件

    select ename, sal, deptno from emp where sal >1000 and deptno = 10;
    select ename, sal, deptno from emp where sal >1000 or deptno = 10;

• between A and B – 介于A和B之间 等价于 >= A and <= B

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

• null不是= ，而是is

    select ename, sal, comm from emp where comm is null;
    select ename, sal, comm from emp where comm is not null;

• in(A,B,C) 只取 A,B,C中的值

    select ename, sal from emp where sal in (800, 1500, 2000);

• 日期（格式需和oracle的一致）

    select ename, sal, hiredate from emp where hiredate > '20-2月-81';

• 模糊查询（‘正则匹配’）

    select ename, sal from emp where ename like '%ALL%';
    select ename, sal from emp where ename like '_A%';
    select ename, sal from emp where ename like '%\%%';

排序 order by

    select * from dept ;
    select * from dept order by deptno desc;
    --默认（不写） - asc

• order by 和 where 、like等组合

    select empno, ename, deptno from emp where deptno <>10 order by empno ;
    select empno, ename, deptno from emp order by deptno ;
    select empno, ename, sal*12 anuual_sal from emp where ename not like '_A%' and sal > 800 order by sal desc;

SQL function 1

• lower()

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

• substr()

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

• chr()

    select chr(65) from dual; 
    -- 输出：A

• ascii()

    select ascii('A') from dual; 
    --输出65

• round()

    select round(23.654321) from dual;
    select round(23.654321,1) from dual;
    select round(23.654321,-1) from dual;

• to_char()

    select to_char(sal,'$9,999.9999') from emp; --9代表一位数字，没有时不予显示
    select to_char(sal,'L9,999.9999') from emp; --L代表当地货币符号
    select to_char(sal,'$0,000.0000') from emp; --0代表一位数字，没有时补0占位
    select to_char(hiredate, 'YYYY-MM-DD HH:MI:SS') mdate from emp;
    select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') mdate from dual;

• to_date()

    select ename, hiredate from emp where hiredate > to_date('1981-02-20 12:00:00','YYYY-MM-DD HH24:MI:SS');

• to_number()

    select sal from emp where sal > to_number('$1,250.00','$9,999.99');

• nvl() – null处理 用0替代空值

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

SQL function 2 – 组函数

• max(), min(), avg(), count(), sum() 多输入，1个输出

    select max(sal) from emp;
    select min(sal) from emp;
    select round(avg(sal),2) from emp;
    select count(*) from emp;
    select count(comm) from emp;
    -- count() 对于null不计数
    select count(distinct deptno) from emp;
    select sum(sal) from emp;
    select avg(sal) from emp;

分组group by

    select deptno, round(avg(sal),2) from emp group by deptno;
    select deptno, job, max(sal) from emp group by deptno,job;
    select deptno, max(sal) from emp group by deptno;
    select ename, sal from emp where sal = (select max(sal) from emp);
    select deptno, round(avg(sal),2) from emp group by deptno;

having 对分组后的数据过滤

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

小结

select查询语句顺序

1. select
2. where
3. group by
4. having
5. order by

  select round(avg(sal),2) from emp where sal>1200 group by deptno 
  having avg(sal) > 1500 order by avg(sal) desc;

子查询

将一个select查询结果作为一张数据表，再进行select查询操作

• 谁挣的钱最多？

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

• 工资超过平均工资之上

    select ename, sal from emp where sal > (select avg(sal) from emp);

• 每个部门工资最多的员工 (join on)

    select 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);

• 每个部门的平均薪水等级

    select deptno,avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t 
    join salgrade on (t.avg_sal between salgrade.losal and Salgrade.Hisal);

• 求每个人的经理人 (自连接)

    select e.ename ename,  t.ename mgr_name from emp e , emp t where e.mgr = t.empno;

• SQL 1999 表连接查询

    select ename, dname from emp join dept on emp.deptno = dept.deptno;
    select ename, dname from emp join dept using(deptno); --不推荐
    select ename, dname, grade from emp join dept on emp.deptno = dept.deptno join salgrade on emp.sal between salgrade.losal and salgrade.hisal where salgrade.grade > 2;

• 左外连接 左边表不能连接的数据也拿出来

    select e.ename ename,  t.ename mgr_name from emp e left join emp t on e.mgr = t.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);

练习！

• A.求部门平均薪水的等级。

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

• B.求部门平均的薪水等级

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

• C.哪些人是经理

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

• D.不准用组函数，求薪水的最高值（面试题）

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

• E.平均薪水最高的部门编号

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

• F.平均薪水最高的部门名称

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

• G.求平均薪水的等级最低的部门的部门名称

    select p.deptno, p.dname, t.avg_sal, t.grade from dept p join
    (
        select deptno, avg_sal, grade from 
        (
            select d.deptno deptno, avg_sal, s.grade grade from 
            ( 
                select deptno, avg(sal) avg_sal from emp group by deptno 
            ) d join salgrade s on (d.avg_sal between s.losal and s.hisal)

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