SQL高级一部分一(SET运算符 && 高级子查询)_SQL

SQL高级部分一(SET运算符 && 高级子查询)
一、SET运算符

将多个查询用 SET 操作符连接组成一个新的查询

select employee_id , department_idfrom emp01union all --相当于两个集合相加， union A并B ，intersect ，A交B，MINUS 差集，A-Bselect employee_id , department_idfrom emp02

SET操作的注意事项

在SELECT 列表中的列名和表达式在数量和数据类型上要相对应
括号可以改变执行的顺序
ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置

注意：
除 UNION ALL之外，系统会自动将重复的记录删除
系统将第一个查询的列名显示在输出中
除 UNION ALL之外，系统自动按照第一个查询中的第一个列的升序排列

SELECT department_id, TO_NUMBER(null) location, hire_dateFROM   employeesUNIONSELECT department_id, location_id,  TO_DATE(null)FROM   departments;

所查询的列一定要一一对应，没有的可以用null来代替

SELECT employee_id, job_id,salaryFROM   employeesUNIONSELECT 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_nuFROM dualUNIONSELECT 'I`d like to teach',1FROM dualUNION SELECT 'the world to',1FROM dualorder 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_idfrom employeeswhere (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_idFROM    employeesWHERE   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_salaryfrom employees e1where 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_salfrom employees e1 , (select department_id , avg(salary) avg_sal                    from employees                    group by department_id                    ) e2 --以子查询的形式，形成了一个新表where e1.department_id = e2.department_idand 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                                 ) locationfrom employees*/SELECT employee_id, last_name,       (CASE        WHEN department_id =                (select department_id                 from departments                where location_id = 1800                 )        THEN 'Canada' ELSE 'USA' END) locationFROM   employees

在 ORDER BY 子句中使用单列子查询
问题：查询员工的employee_id,last_name,要求按照员工的department_name排序

SELECT   employee_id, last_nameFROM     employees eORDER BY (SELECT department_name 	FROM departments d 	WHERE e.department_id = d.department_id	);

三、相关子查询
相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询
外层的表在内层中使用，就是相关子查询

SELECT column1, column2, ...FROM   table1 outerWHERE  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 ewhere 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 , salaryfrom employees e1where e1.employee_id in(                   select manager_id                   from employees e2                   )*//*select distinct e1.employee_id , e1.last_name , e1.salaryfrom employees e1,employees e2where e1.employee_id = e2.manager_id*/SELECT employee_id, last_name, job_id, department_idFROM   employees outerWHERE  EXISTS ( SELECT 'X'                 FROM   employees                 WHERE  manager_id = outer.employee_id     		);

问题：查询departments表中，不存在于employees表中的部门的department_id和department_name

/*select d1.department_idfrom departments d1minusSELECT department_id FROM   employees*/SELECT department_id, department_nameFROM departments dWHERE NOT EXISTS (		SELECT 'X'                  FROM   employees                  WHERE  department_id  = d.department_id		);