null高级子查询高级子查询 目标目标通过本章学习,您将可以:
书写多列子查询
子查询对空值的处理
在 FROM 子句中使用子查询
在SQL中使用单列子查询
关联子查询
书写关联子查询
使用子查询更新和删除数据
使用 EXISTS 和 NOT EXISTS 操作符
使用 WITH 子句子查询子查询子查询是嵌套在 SQL 语句中的另一个SELECT 语句SELECT ...
FROM ...
WHERE ...(SELECT ...
FROM ...
WHERE ...)主查询子查询子查询子查询子查询 (内查询) 在主查询执行之前执行
主查询使用子查询的结果 (外查询)
SELECT select_list
FROM table
WHERE expr operator (SELECT select_list
FROM table);子查询应用举例子查询应用举例SELECT last_name
FROM employees
WHERE salary >
(SELECT salary
FROM employees
WHERE employee_id = 149) ;
多列子查询多列子查询主查询WHERE (MANAGER_ID, DEPARTMENT_ID) INSubquery100 90 102 60 124 50主查询与子查询返回的多行、多列进行比较列比较列比较多列子查询中的比较分为两种:
成对比较
不成对比较成对比较举例成对比较举例
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178,174))
AND employee_id NOT IN (178,174);不成对比较举例不成对比较举例
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 子句中使用子查询在 FROM 子句中使用子查询
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;单列子查询表达式单列子查询表达式单列子查询表达式是在一行中只返回一列的子查询
Oracle8i 只在下列情况下可以使用, 例如:
SELECT 语句 (FROM 和 WHERE 子句)
INSERT 语句中的VALUES列表中
Oracle9i中单列子查询表达式可在下列情况下使用:
DECODE 和 CASE
SELECT 中除 GROUP BY 子句以外的所有子句中单列子查询应用举例单列子查询应用举例在 CASE 表达式中使用单列子查询SELECT employee_id, last_name,
(CASE
WHEN department_id =
THEN 'Canada' ELSE 'USA' END) location
FROM employees; (SELECT department_id FROM departments
WHERE location_id = 1800)在 ORDER BY 子句中使用单列子查询
SELECT employee_id, last_name
FROM employees e
ORDER BY
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);关联子查询关联子查询 关联子查询按照一行接一行的顺序循环执行,主查询的每一行都执行一次子查询GET
从主查询中获取候选列EXECUTE
子查询使用主查询的数据USE
如果满足子查询的条件则返回该行关联子查询关联子查询
SELECT column1, column2, ...
FROM table1
WHERE column1 operator
(SELECT colum1, column2
FROM table2
WHERE expr1 = .expr2);
子查询中使用主查询中的列outerouter关联子查询举例关联子查询举例SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE department_id =
outer.department_id) ;关联子查询举例关联子查询举例SELECT e.employee_id, last_name,e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);EXISTS 操作符EXISTS 操作符EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回 TRUE
如果在子查询中不存在满足条件的行:
条件返回 FALSE
继续在子查询中查找
与关联子查询结合使用EXISTS 操作符应用举例EXISTS 操作符应用举例SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id =
outer.employee_id);NOT EXISTS 操作符应用举例NOT EXISTS 操作符应用举例
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id
= d.department_id);关联更新关联更新
使用关联子查询依据一个表中的数据更新另一个表的数据
UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);关联更新应用举例关联更新应用举例ALTER TABLE employees
ADD(department_name VARCHAR2(14));UPDATE employees e
SET department_name =
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);关联删除关联删除 DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);使用关联子查询依据一个表中的数据删除另一个表的数据
关联删除应用举例关联删除应用举例DELETE FROM employees E
WHERE employee_id =
(SELECT employee_id
FROM emp_history
WHERE employee_id = E.employee_id);WITH 子句WITH 子句使用 WITH 子句, 可以避免在复杂的SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率WITH 子句应用举例WITH 子句应用举例
WITH dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
总结
初级经济法重点总结下载党员个人总结TXt高中句型全总结.doc高中句型全总结.doc理论力学知识点总结pdf
总结通过本章学习,您已经可以:
使用多列子查询
多列子查询的成对和非成对比较
单列子查询
关联子查询
EXISTS 和 NOT EXISTS操作符
关联更新和关联删除
WITH子句