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

一、SET运算符将多个查询用 SET 操作符连接组成一个新的查询select employee_id , department_idfrom emp01union all –相当于两个集合相加, union A并B ,intersect ,A交B,MINUS 差集,A-Bselect employee_id , department_idfrom emp02SET操作的注意事项在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 teachthe world tosing显示 的SQL语句SELECT 'sing' "My dream", 3 a_nuFROM dualUNIONSELECT 'I`d like to teach',1FROM dualUNION SELECT 'the world to',1FROM dualorder by 2SQL> 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_idfrom employeeswhere 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_idFROM employeesWHERE employee_id IN (174,141))AND department_id IN (SELECT department_idFROM employeesWHERE 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 e2where e1.department_id = e2.department_idgroup by department_id)此方法进行了重复性操作2)from字句select employee_id , e1.department_id , salary ,e2.avg_salfrom employees e1 , (select department_id , avg(salary) avg_salfrom employeesgroup 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_idwhen (select department_idfrom departmentswhere location_id = 1800)then 'Canada' else 'Usa'end) locationfrom employees*/SELECT employee_id, last_name,(CASEWHEN department_id =(select department_idfrom departmentswhere location_id = 1800)THEN 'Canada' ELSE 'USA' END) locationFROM employees在 ORDER BY 子句中使用单列子查询问题:查询员工的employee_id,last_name,要求按照员工的department_name排序SELECT employee_id, last_nameFROMemployees 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, column2FROM table2WHERE expr1 = outer.expr2);子查询中使用主查询中的列问题:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_idselect employee_id , last_name , job_id from employees ewhere 2 <= (select count(*)from job_history jwhere 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_idfrom 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 employeesWHERE manager_id = outer.employee_id);问题:查询departments表中,,不存在于employees表中的部门的department_id和department_name一旦有了意志,脚步也会轻松起来。

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

相关文章:

你感兴趣的文章:

标签云: