SQL语言笔记——高阶查询

注意：

• SQL语句大小写不敏感
• 关键词不能缩写和换行
• 空值运算也为空

单表查询

1. 运用算术符和列别名

# annual salary自动大写，如果含有特殊字母需要“ ”
select last_name,12*(salary+bonus) annual salary
from employees
where lower(last_name) = 'shelly'

2. 字符串拼接 | |：

#日期和字母串常量需要‘’
select last_name ||' is a '||job_id
       as "Employee Detail"
from employees;

3. 比较符

常用比较符

select employee_id,last_name,salary,manager_id
from employees
where manager_id in (100,101,201)
and salary >= 2500 
and last_name like ‘_0%’；

注意：

• 在like语法中：%：>= 0 ; _: = 1
• 先做 and 运算再做or运算，想改变优先级用（）。

4. 排序

#先按照部门id升序排列，再按照工资降列排序：
select from last_name,department_id,salary
from employees
order by department_id,salary DESC

5. 转换函数

select last_name,to_char(hire_date,'DD-Mon-YYYY')
from employees
where hire_date < to_date('01-Jan-90','DD-Mon-RR')

6. 嵌套函数

• 一般函数：NULLIF、NULL、ISNULL、COALESCE、IIF
• 条件表达：执行 IF-THEN-ELSE ：(1) CASE expression 、(2) DECODE expression

（1）case: 表示某一个情况

# 如果job_id是“IT_PROG”, salary * 1.10...如果不是，salary不变。这一行叫Revised_Salary：
select last_name,job_id,salary
       case job_id when 'IT_PROG' then 1.10 * salary
                   when 'ST_CLERK' then 1.15 * salary
                   when 'SA_PRP' then 1.20 * salary 
       else salary end  "Revised_Salary "
from employees

（2）decode函数

select last_name,job_id,salary
       decode (job_id,  'IT_PROG' then 1.10 * salary
                        'ST_CLERK' then 1.15 * salary
                        'SA_PRP' then 1.20 * salary 
               salary)
        Revised_Salary
from employees 

7. 组函数

#要用having语句来限制组别，而不能用where:
select department_id,count(distinct(last_name)),avg(salary)
from employees
group by department_id
having avg(salary)> 8000
order by avg(salary);

8. 子查询

（1）单值查询: 可以与组函数、having语句嵌套使用

select department_id,min(salary)
from employees
group by department_id
having min(salary) > 
                    ( select min(salary)
                      from employees
                      where department_id  = 50 );

（2）多值查询

OPERATOR	IN 、ANY、 ALL

select last_name
from employees
where employee_id not in 
                        (select manager_id
                         from employees
                         where manager_id is not null);  #没有这一行代码会显示no row selected,因为空值无法做比较

多表查询（oracle）

原理：SQL的多表查询(笛卡尔积)

1. 等值链接：外键=主键
2. 非等值链接：范围

select e.last_name,e.salary,j.grade_level
from employees e,job_grade j
where e.salary
      between j.lowest_sal and j.higest_sal;

3. 外链接：不满足条件但仍会显示出来，操作符是加一个（+）

select e.last_name,e.department_id,d.department_name
from employees e,departments d
where e.departmemt_id (+) = d.department_id; #(+)允许空值出现

4. 自链接：| |

select worker.last_name || ' works for ' || manager.last_name
from employees worker,employees manager
where worker.manager_id = manager.employee_id;

PS: Using SQL : 内链接、外链接、cross join