mysql子查询(重点、难点)

最新推荐文章于 2022-01-18 22:55:30 发布

程宇寒

最新推荐文章于 2022-01-18 22:55:30 发布

阅读量830

点赞数 3

文章标签： mysql 子查询

本文链接：https://blog.csdn.net/czh500/article/details/85250075

版权

mysql子查询(重点、难点)

这是表结构

#子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
   select后面：
       仅仅支持标量子查询

   from后面：
       支持表子查询
   where或having后面：★重点
       标量子查询（单行子查询） √重点
       列子查询（多行子查询） √重点

       行子查询(用的较少)

   exists后面（相关子查询）
       表子查询

exists (sql 返回结果集,为真)
not exists (sql 不返回结果集,为真)

按结果集的行列数不同：
   标量子查询（结果集只有一行一列）又叫做单行子查询
   列子查询（结果集只有一列多行）又叫做多行子查询
   行子查询（结果集有一行多列、多行多列）
   表子查询（结果集一般为多行多列[一行一列、一行多列等等都可以，无所谓，只要是一个查询结果就行]）

#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

IN/NOT IN 等于列表中的任意一个
ANY|SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
any表示有任何一个满足就返回true，all表示全部都满足才返回true

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

下面直接po代码和案例，代码中有注释

#子查询
/*
含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询

分类：
按子查询出现的位置：
	select后面：
		仅仅支持标量子查询
	
	from后面：
		支持表子查询
	where或having后面：★重点
		标量子查询（单行子查询） √重点
		列子查询  （多行子查询） √重点
		
		行子查询(用的较少)
		
	exists后面（相关子查询）
		表子查询
		
exists (sql 返回结果集,为真)
not exists (sql 不返回结果集,为真)
		
按结果集的行列数不同：
	标量子查询（结果集只有一行一列）又叫做单行子查询
	列子查询（结果集只有一列多行）又叫做多行子查询
	行子查询（结果集有一行多列、多行多列）
	表子查询（结果集一般为多行多列[一行一列、一行多列等等都可以，无所谓，只要是一个查询结果就行]）



*/
#########################################
#select emp.* from (select last_name,salary from employees) emp;
#SELECT last_name,salary FROM (SELECT last_name,salary FROM employees) emp;
#SELECT last_name FROM (SELECT last_name,salary FROM employees) emp;
#select (select count(*) from employees);
#select * from employees where exists (select * from employees where salary > 23000) and department_id = 90;
#SELECT * FROM employees WHERE department_id = 90;
#########################################
#一、where或having后面
/*
1、标量子查询（单行子查询）
2、列子查询（多行子查询）

3、行子查询（多列多行）

特点：
①子查询放在小括号内
②子查询一般放在条件的右侧
③标量子查询，一般搭配着单行操作符使用
> < >= <= = <>

列子查询，一般搭配着多行操作符使用
in、any/some、all

IN/NOT IN 等于列表中的任意一个
ANY|SOME 和子查询返回的某一个值比较
ALL 和子查询返回的所有值比较
any表示有任何一个满足就返回true，all表示全部都满足才返回true

④子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果

*/
SELECT DATEDIFF('2019/06/06', NOW());
SELECT DATEDIFF('2019-06-06', NOW());
#1.标量子查询★(结果集只有一行一列)
#案例1：谁的工资比 Abel 高?
SELECT 
  * 
FROM
  employees 
WHERE salary > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel') ;

#案例2：返回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) ;

#案例3：返回公司工资最少的员工的last_name,job_id和salary
SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

#案例4：查询最低工资大于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) ;
  
#非法使用标量子查询
SELECT 
  MIN(salary),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING MIN(salary) > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE department_id = 250) ;

#非法使用标量子查询
SELECT 
  MIN(salary),
  department_id 
FROM
  employees 
GROUP BY department_id 
HAVING MIN(salary) > 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE department_id = 50) ;
  
#列子查询（结果集只有一列多行）又叫做多行子查询
#2.列子查询（多行子查询）★
#案例1：返回location_id是1400或1700的部门中的所有员工姓名
SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;
  
#或者使用ANY(把in换成ANY)，= ANY和上面的IN效果一样
SELECT 
  last_name 
FROM
  employees 
WHERE department_id = ANY 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;
  
#NOT IN 
SELECT 
  last_name 
FROM
  employees 
WHERE department_id NOT IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;  

#<> ALL和上面的NOT IN 效果一样
SELECT 
  last_name 
FROM
  employees 
WHERE department_id <> ALL 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700)) ;   

#案例2：返回其它工种中比job_id为‘IT_PROG’工种任一工资低的员工的员工号、姓名、job_id 以及salary
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary < ANY 
  (SELECT DISTINCT
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;

#使用SOME关键字
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary < SOME 
  (SELECT DISTINCT
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;  
#或者
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary <  
  (SELECT 
    MAX(salary)
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;
  
#案例3：返回其它工种中比job_id为‘IT_PROG’工种所有工资都低的员工的员工号、姓名、job_id 以及salary

SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary < ALL  
  (SELECT DISTINCT
    salary
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;
#或者
SELECT 
  employee_id,
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE job_id <> 'IT_PROG' 
  AND salary <   
  (SELECT 
    MIN(salary)
  FROM
    employees 
  WHERE job_id = 'IT_PROG') ;

#3、行子查询（结果集一行多列或多行多列）用的比较少
#案例：查询员工编号最小并且工资最高的员工信息
#这种用的比较少
SELECT * FROM employees WHERE (employee_id, salary) = (
SELECT MIN(employee_id), MAX(salary) FROM employees
);

#或者
SELECT 
  * 
FROM
  employees 
WHERE employee_id = 
  (SELECT 
    MIN(employee_id) 
  FROM
    employees) 
  AND salary = 
  (SELECT 
    MAX(salary) 
  FROM
    employees);
    
#二、select后面
/*
仅仅支持标量子查询
*/
#案例：查询每个部门的员工个数
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    employees emp 
  WHERE emp.`department_id` = d.`department_id`) AS 个数 
FROM
  departments d ;
  
#用连接也可以
SELECT 
	dep.*,
	(SELECT COUNT(*) FROM employees WHERE employees.`department_id` = dep.`department_id`) AS 个数
FROM 
	departments dep 
LEFT OUTER JOIN 
	employees emp 
ON dep.`department_id` = emp.`department_id`;

#用连接也可以
SELECT 
	DISTINCT dep.department_id, department_name, dep.manager_id, location_id, 	
	(SELECT COUNT(*) FROM employees WHERE employees.`department_id` = dep.`department_id`) AS 个数
FROM 
	departments dep 
LEFT OUTER JOIN 
	employees emp 
ON dep.`department_id` = emp.`department_id`;

#案例2：查询员工号=102的部门名
SELECT 
  department_name 部门名
FROM
  departments 
WHERE department_id = 
  (SELECT 
    department_id 
  FROM
    employees 
  WHERE employee_id = 102) ;
#或者
SELECT 
  (SELECT 
    department_name 
  FROM
    departments dep 
  WHERE dep.department_id = emp.`department_id`) AS 部门名
FROM
  employees emp 
WHERE emp.`employee_id` = 102 ;

##select后面：仅仅支持标量子查询(放在select后面的子查询结果只能是一行一列)
SELECT 
  (SELECT 
    department_name
  FROM
    departments d 
    INNER JOIN employees e 
      ON d.department_id = e.department_id 
  WHERE e.employee_id = 102) 部门名 ;
		  
#
SELECT 
    department_name AS 部门名
  FROM
    departments d 
    INNER JOIN employees e 
      ON d.department_id = e.department_id 
  WHERE e.employee_id = 102;
 
 
#有NULL的情况(子查询中的空值问题)
#能查到数据
SELECT * FROM employees WHERE department_id IN (90); 
#能查到数据
SELECT * FROM employees WHERE department_id IN (90, NULL);  
#能查到数据
SELECT * FROM employees WHERE department_id IN ('90');
#能查到数据
SELECT * FROM employees WHERE department_id IN ('90', NULL);
#能查到数据(子查询的结果中包含了null值，但是外查询把null忽略掉了)
SELECT * FROM employees WHERE commission_pct IN (
SELECT DISTINCT commission_pct FROM employees);
#查不到数据
SELECT * FROM employees WHERE commission_pct IN (NULL);
#只能查到commission_pct等于0.15的记录，不能查到commission_pct为null的记录
SELECT * FROM employees WHERE commission_pct IN (0.15, NULL);
#查不到数据
SELECT * FROM employees WHERE commission_pct NOT IN (0.15, NULL);
#能查到数据
SELECT * FROM employees WHERE commission_pct NOT IN (0.15);
#查不到数据(因为子查询中,commission_pct字段有null值)
SELECT * FROM employees WHERE commission_pct NOT IN (
SELECT commission_pct FROM employees WHERE employee_id > 100 AND employee_id < 160
);
#能查到数据(因为子查询中,把commission_pct字段为null的筛选掉了)
SELECT * FROM employees WHERE commission_pct NOT IN (
SELECT commission_pct FROM employees WHERE employee_id > 100 AND employee_id < 160 AND commission_pct IS NOT NULL
);
#可以查到数据(子查询的结果中有null值)
SELECT * FROM employees WHERE  commission_pct > ALL(
SELECT commission_pct FROM employees WHERE employee_id = 100 OR employee_id = 155);
#可以查到数据(子查询的结果中有null值)
SELECT * FROM employees WHERE EXISTS (
SELECT commission_pct FROM employees WHERE employee_id = 100 OR employee_id = 155);
#
#三、from后面
/*
特别注意：将子查询结果充当一张表，要求必须起别名
*/
#
SELECT * FROM job_grades gra;

#案例：查询每个部门的平均工资的工资等级
#特别注意：将子查询结果充当一张表，要求必须起别名
SELECT 
  avg_dep.*,
  gra.`grade_level` 
FROM
  (SELECT 
    AVG(salary) avg_sal,
    department_id 
  FROM
    employees 
  GROUP BY department_id) avg_dep 
  INNER JOIN job_grades gra 
    ON avg_dep.avg_sal BETWEEN gra.`lowest_sal` 
    AND gra.`highest_sal` ;
    
    
#四、exists后面（相关子查询）

/*
语法：
exists(完整的查询语句)
结果：
1或0

*/
#查询结果为1
SELECT EXISTS (SELECT employee_id FROM employees) AS 结果;
#查询结果为1
SELECT EXISTS (SELECT * FROM employees) AS 结果;
#查询结果为1
SELECT EXISTS (SELECT last_name, salary FROM employees) AS 结果;
#查询结果为0
SELECT EXISTS (SELECT employee_id FROM employees WHERE salary = 80000) AS 结果;
#查询结果为0
SELECT EXISTS (SELECT * FROM employees WHERE salary = 80000) AS 结果;
#查询结果为1
SELECT NOT EXISTS (SELECT * FROM employees WHERE salary = 80000) AS 结果;
/*
exists (sql 返回结果集,为真)
not exists (sql 不返回结果集,为真)
*/

#案例1：查询有员工的部门名
#使用EXISTS
SELECT 
  department_name 
FROM
  departments dep 
WHERE EXISTS 
  (SELECT 
    * 
  FROM
    employees emp 
  WHERE dep.`department_id` = emp.`department_id`) ;
 
#使用in
SELECT 
  department_name 
FROM
  departments dep 
WHERE dep.`department_id` IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    employees);
#    
USE girls;    
#案例2：查询没有女朋友的男神信息
SELECT * FROM boys;
SELECT * FROM beauty;
INSERT INTO boys VALUES (5, '乔峰', 200);

#使用NOT EXISTS
SELECT 
  * 
FROM
  boys bo 
WHERE NOT EXISTS 
  (SELECT 
    * 
  FROM
    beauty b 
  WHERE bo.`id` = b.`boyfriend_id`) ;
  
#使用in
SELECT 
  * 
FROM
  boys bo 
WHERE bo.`id` NOT IN 
  (SELECT DISTINCT 
    b.`boyfriend_id` 
  FROM
    beauty b) ;
    
    

#题目
#1.查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name, salary FROM employees emp
WHERE emp.`department_id` = (
SELECT `department_id` FROM employees WHERE last_name = 'Zlotkey');

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
SELECT employee_id, last_name, salary FROM employees emp
WHERE emp.`salary` > (
SELECT AVG(salary) FROM employees);

#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#######先查一下各部门的平均工资
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
#######
#
SELECT employee_id, last_name, salary, emp01.`department_id` FROM employees emp01
WHERE emp01.`salary` > (
SELECT AVG(salary) FROM employees emp02 GROUP BY department_id
HAVING emp01.`department_id` = emp02.`department_id`);

#或者
SELECT employee_id, last_name, salary, emp01.`department_id` FROM employees emp01
WHERE emp01.`salary` > (
SELECT AVG(salary) FROM employees emp02 
WHERE emp01.`department_id` = emp02.`department_id`
GROUP BY department_id);
#或者
SELECT employee_id,last_name,salary,e.department_id
FROM employees e
INNER JOIN (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;

#4.查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
SELECT employee_id, last_name FROM employees WHERE department_id IN (
SELECT DISTINCT department_id FROM employees WHERE last_name LIKE '%u%');

#5. 查询在部门的location_id为1700的部门工作的员工的员工号
SELECT employee_id FROM employees WHERE department_id IN (
SELECT DISTINCT department_id FROM departments WHERE location_id = 1700);
#或者
SELECT employee_id FROM employees WHERE department_id = ANY (
SELECT DISTINCT department_id FROM departments WHERE location_id = 1700);

#6.查询管理者是K_ing的员工姓名和工资
SELECT last_name, salary FROM employees WHERE manager_id IN (
SELECT employee_id FROM employees WHERE last_name = 'K_ing');

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
SELECT 
  CONCAT(first_name, last_name) AS "姓.名" 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;
#用IN也可以
SELECT 
  CONCAT(first_name, last_name) AS "姓.名" 
FROM
  employees 
WHERE salary IN 
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;