SQL（Oracle）进阶学习

SQL语法学习

• "自链接":其实时一种概念,某个table和自己本身链接。

select worker.last_name || 'works for'|| manager.last_name
FROM   employees worker, employees manager
      WHERE worker.manager_id = manager.employee_id ;

多表关联查询

• 交叉链接:相当于没有条件的多表关联查询，结果是个笛卡尔乘积。
• 全外链接

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;

分组计算函数和group by字句注意点

• SELECT 查询语句中同时选择分组计算函数表达式和其他独立字段时 ，其他字段必须出现在Group By 子 句中，否则不合法。
• 不能在Where 条件中使用分组计算函数表达式，当出现这样的需求的时候，使用Having 子句。

SELECT department_id, AVG(salary) FROM employees
GROUP BY department_id
HAVING AVG(salary) > 8000;

• 分组计算函数也可嵌套使用。

SELECT MAX(AVG(salary))
FROM employees
GROUP BY department_id;

子查询

• 注意点 单行比较必须对应单行子查询 多行比较必须对应多行子查询（返回一个数据集合的查询）；比如 IN , > ANY, > ALL
• 单行

select employee_id,last_name
from employees
where salary=(
  select min(dalary)
  from employees
);

• 多行

select employee_id,last_name
from employees
where salary<ANY
(
  select salary
  from employees
  where job_id = 'IT_PROG'
)
and job_id <> 'IT_PROG';

DML语句

• DML:数据操纵语言，简单的说就是SQL中的增删改等语句。
• Insert语句：1.写出表名+列名 2.仅写表名 第二种方式：在value中必须对应写出每个列的值，即是允许null 的字段，也必须显式的给出null值。 3.把查询结果作为插入的数据 4.把子查询作为插入目标

Insert into sales_reps(id, name, salary, commission_pct)
    SELECT employee_id, last_name, salary, commission_pct
    FROM employees
    where job_id like '%rep%';

• update语句，可以使用子查询的结果作为更新后的值

update employees
set job_id = (
  sales_reps(id, name, salary, commission_pct)
  SELECT employee_id, last_name, salary, commission_pct
  FROM employees
),salary = (SELECT salary
  FROM employees
  WHERE employee_id = 205)
  WHERE employee_id = 114;

• delete语句：注意使用truncate，这个语句无法回滚。

子查询进阶

• 非相关子查询当作一张表来用

select a.last_name,a.salary,a.department_id,b.salavg
from employees a,(select department_id,avg(salary) salavg
from employees group by department_id) b
where a.department_id = b.department_id
and a.salary > b.salavg

sql中exists,not exists的用法

• exists : 强调的是是否返回结果集，不要求知道返回什么, 比如： select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要 exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么， 而是在乎是不是有结果集返回。

• 而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如： select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)
  ，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回， 在1，2，3中随便去了两个字段即可。

• Show the employee number, last name, salary, department number, and the average salary in their department for all employees.

select e.employee_id,e.last_name,e.department_id,d.avg from employee e,(select department_id,avg(salary) avg from employees group 
by department_id) id
where e.department_id = d.department_id;

SQL进阶

• 分析函数提供一系列比较高级的sql功能。分析函数时建立在数据窗口（over在一定的数据范围进行数据分析），在一定的数据范围进行排序，汇总等。

• 如查看当前员工所在部门的平均工资，最高工资和最低工资，即可通过分析函数来实现

SELECT e.last_name,
e.salary,
d.department_name,
AVG(e.salary) over(PARTITION BY d.department_name) department_avg_salary,
MAX(e.salary) over(PARTITION BY d.department_name) department_max_salary,
MIN(e.salary) over(PARTITION BY d.department_name) department_min_salary
FROM employees e, departments d
WHERE 1 = 1
AND e.department_id = d.department_id;

• 将一个部门内的员工的工资从高到低排序，

select d.department_name,e.last_name,e.salary,
rank() over(partition by department_name order by e.salary desc) dept_salary_rank1,
dense_rank() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank2,
row_number() over(PARTITION BY d.department_name ORDER BY e.salary DESC) dept_salary_rank3
FROM employees e, departments d
WHERE 1 = 1
AND e.department_id = d.department_id;

• 综合案列：显示部门编号、名称、员工人数和平均工资 部门，以及在每个部门工作的员工的姓名、薪水和工作 部门

SELECT CASE
         WHEN RANK()
          OVER(PARTITION BY DEP.DEPARTMENT_ID ORDER BY EMP.SALARY ASC) = 1 THEN
          DEP.DEPARTMENT_ID
         ELSE
          NULL
       END "DEPARTMENT_ID",
       CASE
         WHEN RANK()
          OVER(PARTITION BY DEP.DEPARTMENT_ID ORDER BY EMP.SALARY ASC) = 1 THEN
          DEP.DEPARTMENT_NAME
         ELSE
          NULL
       END "DEPARTMENT_NAME",
       CASE
         WHEN RANK()
          OVER(PARTITION BY DEP.DEPARTMENT_ID ORDER BY EMP.SALARY ASC) = 1 THEN
          COUNT(EMP.EMPLOYEE_ID) OVER(PARTITION BY DEP.DEPARTMENT_ID)
         ELSE
          NULL
       END "EMPLOYEES",
       CASE
         WHEN EMP.SALARY IS NULL THEN
          'No average'
         WHEN RANK()
          OVER(PARTITION BY DEP.DEPARTMENT_ID ORDER BY EMP.SALARY ASC) = 1 THEN
          TO_CHAR(AVG(EMP.SALARY) OVER(PARTITION BY DEP.DEPARTMENT_ID),
                  '999999.99')
         ELSE
          NULL
       END "AVG_SAL",
       EMP.LAST_NAME,
       EMP.SALARY,
       EMP.JOB_ID
  FROM EMPLOYEES EMP, DEPARTMENTS DEP
 WHERE EMP.DEPARTMENT_ID(+) = DEP.DEPARTMENT_ID;

关于exists的优化

• 看代码

select d.department_id
from departments d
where d.department_id !=(
select distinct department_id from employees
where job_id = 'SA_REP' and department_id is not null
)

select d.department_id
from departments d
where d.department_id not in(
select  department_id from employees
where job_id = 'SA_REP' and department_id is not null
)

select d.department_id
from departments d
where  not exists(
select  department_id from employees
where job_id = 'SA_REP' and department_id is not null
)