子查询和高级子查询
博客专区 > bharals 的博客 > 博客详情
子查询和高级子查询
bharals 发表于7个月前
子查询和高级子查询
  • 发表于 7个月前
  • 阅读 1
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

--  谁的工资比 Abel 高
SELECT salary FROM employees WHERE last_name='Abel';
SELECT last_name, salary FROM employees WHERE salary>=11000;
SELECT last_name, salary FROM employees WHERE salary>=(SELECT salary FROM employees WHERE last_name='Abel');
--  子查询一般是哪个条件未知
--  子查询要包含在括号内。
--  将子查询放在比较条件的右侧。
--  单行操作符对应单行子查询,多行操作符对应多行子查询。

--  返回job_id与141号员工相同,salary比143号员工多的员工姓名,job_id 和工资
SELECT last_name, job_id, salary
            FROM   employees
            WHERE  job_id =  
                            (SELECT job_id
                             FROM   employees
                             WHERE  employee_id = 141)
            AND    salary >
                            (SELECT salary
                             FROM   employees
                             WHERE  employee_id = 143);
--  在子查询中使用组函数    题目:返回公司工资最少的员工的last_name,job_id和salary
SELECT last_name, job_id, salary
            FROM   employees
            WHERE  salary = 
                            (SELECT MIN(salary)
                             FROM   employees);
--  子查询中的 HAVING 子句     首先执行子查询。向主查询中的HAVING 子句返回结果。
--  题目:查询最低工资大于50号部门最低工资的部门id和其最低工资
SELECT   department_id, MIN(salary)
FROM     employees
GROUP BY department_id
HAVING   MIN(salary) >
                       (SELECT MIN(salary)
                        FROM   employees
                        WHERE  department_id = 50);


--  多行子查询   IN: 等于列表中任意一个   ANY:  和子查询返回的某个值比较  ALL:和子查询返回的所有值比较
--  1、在多行子查询中使用 ALL 操作符  
--  题目:返回其它部门中比job_id为‘IT_PROG’部门所有工资都低的员工的员工号、姓名、job_id 以及salary
SELECT employee_id, last_name, job_id, salary
                        FROM   employees
                        WHERE  salary < ALL
                                            (SELECT salary
                                             FROM   employees
                                             WHERE  job_id = 'IT_PROG')
                        AND    job_id <> 'IT_PROG';
--  2、子查询中的空值问题
SELECT emp.last_name
                        FROM   employees emp
                        WHERE  emp.employee_id NOT IN
                                                     (SELECT mgr.manager_id
                                                      FROM   employees mgr);

--  NO ROWS selected




--  高级子查询
SELECT last_name
FROM   employees
WHERE  salary > 
                (SELECT salary
                 FROM   employees
                 WHERE  employee_id = 149) ;

--查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id, manager_id, department_id  
--成对比较
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 (141,174))
AND	employee_id NOT IN (141,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 子句中使用子查询
--问题:返回比本部门平均工资高的员工的last_name, department_id, salary及平均工资
-- 方法一:
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
          );
--方法二:
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;

--单列子查询应用举例
--在 CASE 表达式中使用单列子查询
--问题:显式员工的employee_id,last_name和location。其中,
--若员工department_id与location_id为1800的department_id相同,则location为’Canada’,其余则为’USA’。
SELECT employee_id, last_name,
       (CASE
        WHEN department_id =
                
                 
        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);


--相关子查询举例
--问题:查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
SELECT last_name, salary, department_id
FROM   employees outer
WHERE  salary >(SELECT AVG(salary)
                   FROM   employees
                   WHERE  department_id =  
                          OUTER.department_id) ;
--问题:若employees表中employee_id与job_history表中employee_id相同的数目不小于2,输出这些相同id的员工的employee_id,last_name和其job_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 操作符检查在子查询中是否存在满足条件的行
--如果在子查询中存在满足条件的行:
--不在子查询中继续查找
--条件返回 TRUE
--如果在子查询中不存在满足条件的行:
--条件返回 FALSE
--继续在子查询中查找

--问题:查询公司管理者的employee_id,last_name,job_id,department_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 department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
                  FROM   employees
                  WHERE  department_id 
                         = d.department_id);

--相关更新应用举例
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);
--问题:删除表employees中,其与emp_history表皆有的数据
DELETE FROM employees E
WHERE employee_id =  
           (SELECT employee_id
            FROM   emp_history 
            WHERE  employee_id = E.employee_id);


--使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
--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;

共有 人打赏支持
粉丝 0
博文 26
码字总数 46307
×
bharals
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: