oracle11gdatabasesql语言基础

oracle11gdatabasesql语言基础内容摘要：

多行子查询匹配 SELECT select_list FROM table WHERE expr operator (SELECT select_list FROM table)。 Exists • EXISTS 测试子查询是否具有相关的数据行 • 如果找到相关数据行 : 子查询停止运行 返回结果为 TRUE • 如果没有找到相关数据行 : 返回结果为 FALSE 继续子查询，直到子查询结束 NOT Exists 和 NOT in • 如果子查询返回 NULL值，则 NOT IN 返回假 • 当没有 null值时 NOT in 与 not exists效果相同 SELECT department_id, department_name FROM departments d WHERE NOT EXISTS (SELECT 39。 X39。 FROM employees WHERE department_id = )。 查找哪个部门没有员工 (表 employees中有一行数据的 department_id为 null) SELECT department_id, department_name FROM departments d WHERE department_id NOT IN (SELECT department_id FROM employees)。 No rows selected. IN 、 ANY 、 ALL • ANY 意谓着小于子查询返回结果的最大值 • ANY意谓着大于子查询结果的最小值 • =ANY 和 IN的效果相同 • ANY中的子查询如果返回 0行，则 ANY ， ANY ， =ANY， !=ANY ， =ANY等操作都判断为假，主查询不返回数据 SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary ANY (SELECT salary FROM employees WHERE job_id = 39。 IT_PROG39。 ) AND job_id 39。 IT_PROG39。 IN 、 ANY 、 ALL • ALL 意谓着小于子查询返回结果的最小值 • ALL意谓着大于子查询结果的最大值 • ALL中的子查询如果返回 0行，则 ALL ， ALL ， =ALL， !=ALL ， =ALL等操作都判断为真，主查询返回符合其他条件的数据 SELECT employee_id, last_name, job_id, salary FROM employees WHERE salary ALL (SELECT salary FROM employees WHERE job_id = 39。 IT_PROG39。 ) AND job_id 39。 IT_PROG39。 集合操作符 Set Operators UNION/UNION ALL A B A B A B INTERSECT A B MINUS 集合操作符说明 • 两个查询语句中引用的列数量要一致 • 第二个查询的列的数据类型须要与第一个查询的列一一对应 • 可以使用括号改变执行顺序 • 除 UNION ALL外，重复行只显示一次 • 列名的显示以第一个查询为准 • 默认情况下，除 UNION ALL外，查询结果以升序排序 UNION SELECT employee_id, job_id FROM employees UNION SELECT employee_id, job_id FROM job_history。 … … UNION ALL SELECT employee_id, job_id, department_id FROM employees UNION ALL SELECT employee_id, job_id, department_id FROM job_history ORDER BY employee_id。 … … INTERSECT SELECT employee_id, job_id FROM employees INTERSECT SELECT employee_id, job_id FROM job_history。 MINUS SELECT employee_id FROM employees MINUS SELECT employee_id FROM job_history。 … 列数量与类型匹配 SELECT location_id, department_name Department, TO_CHAR(NULL) Warehouse location FROM departments UNION SELECT location_id, TO_CHAR(NULL) Department, state_province FROM locations。 集合操作符与 ORDER BY • ORDER BY 只能在整个查询的最后面出现一次 • ORDER BY 中指定的列，必须在第一个查询语句中指定的列 • 默认情况下，第一个查询语句中的第一个列作为返回结果的排序依据，顺序为升序 多列子查询 MultipleColumn Subqueries 主查询 WHERE (MANAGER_ID, DEPARTMENT_ID) IN 子查询结果 100 90 102 60 124 50 多列子查询 MultipleColumn Subqueries • 不成对比较 • 成对比较 成对比较子查询 返回与名字为 John的员工在同一个部门，且同一个领导的员工 SELECT employee_id, manager_id, department_id FROM empl_demo WHERE (manager_id, department_id) IN (SELECT manager_id, department_id FROM empl_demo WHERE first_name = 39。 John39。 ) AND first_name 39。 John39。 不成对比较子查询 返回与名字为 John的员工中任意一个领导相符和任意一个部门相符的员工 当名字为 John多于一人时，与成对比较子查询的返回结果不同。 SELECT employee_id, manager_id, department_id FROM empl_demo WHERE manager_id IN (SELECT manager_id FROM empl_demo WHERE first_name = 39。 John39。 ) AND department_id IN (SELECT department_id FROM empl_demo WHERE first_name = 39。 John39。 ) AND first_name 39。 John39。 关联子查询 关联子查询用于行与行的处理，关联子查询对每个数据行都要进行 一次。 GET 主查询 fetch到一行数据 EXECUTE 子查询根据这行数据的值进行运算 USE 根据子查询的结果，决定是否返回这行数据 关联子查询 vs 嵌套子查询 嵌套子查询： 子查询首先执行，且执行一次，返回相关结果 主查询使用子查询返回的结果进行一次运算 关联子查询： 主查询 fetch到一行数据 根据这行数据，进行子查询运算 根据子查询的结果，决定是否返回这行数据 重复以上过程，直到主查询结束 使用关联子查询 SELECT column1, column2, ... FROM table1 WHERE column1 operator (SELECT column1, column2 FROM table2 WHERE expr1 = .expr2)。 outer outer SELECT , last_name, FROM employees e WHERE 2 = (SELECT COUNT(*) FROM job_history WHERE employee_id = )。 WITH • 使用 WITH，可以在查询中多次引用相同的子查询 • 使用 WITH 返回的结果存放在临时空间 • 在复杂的查询中适当使用 WITH，可以提高性能 WITH示例 WITH dept_costs AS ( SELECT , SUM() AS dept_total FROM employees e JOIN departments d ON = GROUP BY ), 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。 处理数据 DML Data Manipulation Language • DML 的作用 : 添加新的数据到表中 修改表中已有的数据 删除表中已有的数据 • 事务：由一系列的 DML语句组成，成为一个逻辑工作单元。 事务中的语句必须全部成功，否则必须全部回退。 INSERT INSERT INTO departments VALUES (100, 39。 Finance39。 , NULL, NULL)。 INSERT INTO departments (department_id, department_name) VALUES (30, 39。 Purchasing39。 )。 INSERT INTO sales_reps(id, name, salary, mission_pct) SELECT employee_id, last_name, salary, mission_pct FROM employees INSERT INTO employees (employee_id,hire_date) VALUES (113,SYSDATE)。 多表 INSERT 多表插入有以下种类 : • 无条件 INSERT • 有条件 INSERT ALL • 旋转 (Pivoting) INSERT • 条件 INSERT FIRST Unconditional INSERT ALL INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL) SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR FROM employees WHERE employee_id 200。 Unconditional INSERT ALL INSERT ALL IN。