一、SET运算符
将多个查询用 SET 操作符连接组成一个新的查询
SET操作的注意事项
在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置
注意:
除 UNION ALL之外,系统会自动将重复的记录删除
系统将第一个查询的列名显示在输出中
除 UNION ALL之外,系统自动按照第一个查询中的第一个列的升序排列
I'd like to teach
the world to
sing
显示 的SQL语句
二、高级子查询
多列子查询
主查询与子查询返回的多个列进行比较
举例:
查询与141号或174号员工的manager_id和department_id相同的其他员工
的employee_id, manager_id, department_id
1)成对比较举例
在 FROM 子句中使用子查询
问题:返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
1)已学知识解决:
2)from字句
在 CASE 表达式中使用单列子查询
问题:显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
问题:查询员工的employee_id,last_name,要求按照员工的department_name排序
三、相关子查询
相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询
外层的表在内层中使用,就是相关子查询
问题:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,
输出这些相同id的员工的employee_id,last_name和其job_id
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找,条件返回 TRUE
如果在子查询中不存在满足条件的行:
条件返回 FALSE,继续在子查询中查找
问题:查询公司管理者的employee_id,last_name,job_id,department_id信息
将多个查询用 SET 操作符连接组成一个新的查询
select employee_id , department_id
from emp01
union all --相当于两个集合相加, union A并B ,intersect ,A交B,MINUS 差集,A-B
select employee_id , department_id
from emp02
SET操作的注意事项
在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置
注意:
除 UNION ALL之外,系统会自动将重复的记录删除
系统将第一个查询的列名显示在输出中
除 UNION ALL之外,系统自动按照第一个查询中的第一个列的升序排列
SELECT department_id, TO_NUMBER(null) location, hire_date
FROM employees
UNION
SELECT department_id, location_id, TO_DATE(null)
FROM departments;
所查询的列一定要一一对应,没有的可以用null来代替
SELECT employee_id, job_id,salary
FROM employees
UNION
SELECT employee_id, job_id,0 --salary可以用0代替
FROM job_history;
指定列不显示column 指定列的名字 noprint;
I'd like to teach
the world to
sing
显示 的SQL语句
SELECT 'sing' "My dream", 3 a_nu
FROM dual
UNION
SELECT 'I`d like to teach',1
FROM dual
UNION
SELECT 'the world to',1
FROM dual
order by 2
SQL> column a_nu noprint;--指定a_nu列不显示
二、高级子查询
多列子查询
主查询与子查询返回的多个列进行比较
举例:
查询与141号或174号员工的manager_id和department_id相同的其他员工
的employee_id, manager_id, department_id
1)成对比较举例
select manager_id , employee_id,department_id
from employees
where (manager_id,department_id) in (
select manager_id,department_id
from employees
where employee_id in (141,174)
)
and employee_id not in(141,174)
2)不成对比较
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (
SELECT manager_id
FROM employees
WHERE employee_id IN (174,141)
)
AND department_id IN (
SELECT department_id
FROM employees
WHERE employee_id IN (174,141)
)
AND employee_id NOT IN(174,141);
注意主查询的列 和 内查询的列,一定要一一对应
在 FROM 子句中使用子查询
问题:返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
1)已学知识解决:
select last_name,department_id,salary,
(select avg(salary)from employees e3
where e1.department_id = e3.department_id
group by department_id) avg_salary
from employees e1
where salary >
(select avg(salary)
from employees e2
where e1.department_id = e2.department_id
group by department_id
)
此方法进行了重复性操作
2)from字句
select employee_id , e1.department_id , salary ,e2.avg_sal
from employees e1 , (select department_id , avg(salary) avg_sal
from employees
group by department_id
) e2 --以子查询的形式,形成了一个新表
where e1.department_id = e2.department_id
and e1.salary > e2.avg_sal
单列子查询应用举例
在 CASE 表达式中使用单列子查询
问题:显式员工的employee_id,last_name和location。其中,若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
/*select employee_id , last_name , (
case department_id
when (select department_id
from departments
where location_id = 1800
)
then 'Canada' else 'Usa'
end
) location
from employees
*/
SELECT employee_id, last_name,
(CASE
WHEN department_id =
(select department_id
from departments
where location_id = 1800
)
THEN 'Canada' ELSE 'USA' END) location
FROM employees
在 ORDER BY 子句中使用单列子查询
问题:查询员工的employee_id,last_name,要求按照员工的department_name排序
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id
);
三、相关子查询
相关子查询按照一行接一行的顺序执行,主查询的每一行都执行一次子查询
外层的表在内层中使用,就是相关子查询
SELECT column1, column2, ...
FROM table1
outer
WHERE column1 operator
(SELECT colum1, column2
FROM table2
WHERE expr1 = outer.expr2);
子查询中使用主查询中的列
问题:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,
输出这些相同id的员工的employee_id,last_name和其job_id
select employee_id , last_name , job_id
from employees e
where 2 <= (
select count(*)
from job_history j
where e.employee_id = j.employee_id
)
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找,条件返回 TRUE
如果在子查询中不存在满足条件的行:
条件返回 FALSE,继续在子查询中查找
问题:查询公司管理者的employee_id,last_name,job_id,department_id信息
/*
select employee_id , last_name , salary
from employees e1
where e1.employee_id in(
select manager_id
from employees e2
)
*/
/*
select distinct e1.employee_id , e1.last_name , e1.salary
from employees e1,employees e2
where e1.employee_id = e2.manager_id
*/
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id = outer.employee_id
);
问题:查询departments表中,不存在于employees表中的部门的department_id和department_name
/*
select d1.department_id
from departments d1
minus
SELECT department_id
FROM employees
*/
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
SELECT 'X'
FROM employees
WHERE department_id = d.department_id
);