mysql子查询(重点难点)

于 2024-11-27 12:38:33 发布
阅读量663 收藏 9
点赞数 7
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/u013172533/article/details/144080316
版权

#子查询
/*
含义：
出现在其他语句中的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) ;

Admin-user
关注
2022-09-09 MySQL查询优化器
悟世之路
09-09 231
在生成逻辑查询执行计划过程中，根据关系代数的原理，把语法分析树变为关系代数语法树的样式，原先SQL语义中的一些谓词变化为逻辑代数的操作符等样式，这些样式是一个临时的中间状态，经过进一步的逻辑查询优化，如执行常量传递、选择下推等(如一些节点下移，一些节点上移)，从而生成逻辑查询执行计划。优化器在物理优化阶段，需要对单表读取开销，两表连接开销，多表连接顺序开销等进行比较，比较基于的就是一些基础数据的值，这些数据通常不会被实时更新，所以优化器有时做出的计划未必是最合适的。语法分析器是查询优化器的输入。
MySQL笔记4——高级查询
OYXL2000的博客
07-14 665
学习目的：理解MySQL数据库中分组查询的含义，掌握常用分组函数的使用，掌握GROUP BY子句的使用规则，掌握分组后数据结果的条件过滤，掌握SELECT语句执行过程，理解子查询的含义，掌握单行子查询和多行子查询的使用。重点： 难点：分组函数 ● MIN函数和MAX函数 ● SUM函数和AVG函数 ● COUNT函数 ● 组函数中空值处理 ● 通过GROUP BY子句进行分组汇总 ● HAVING子句的使用 ● 单行子查询 ● 多行子查询 ● 子查询中空值问题......
mysql中子查询注意事项_MySQL使用子查询注意事项及使用技巧
weixin_30488835的博客
02-18 1111
使用子查询注意事项:1.MySQL 4.1引入了对子查询的支持.必须使用MySQL 4.1或更高级的版本. 2.能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。3.列必须匹配 在WHERE子句中使用子查询(如这里所示)，应该保证SELECT语句具有与WHERE子句中相同数目的列。通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列.WHERE ...
子查询注意事项&semi-join(2)—mysql基于规则优化（四十五)
ke1ying的博客
09-22 682
前面说了mysql会吧一些冗余的sql语句查询优化重写，比如多于的括号，比如有的外连接其实跟内连接类似，可以优化查询表的顺序。子查询又分为相关和不相关子查询，如果子查询过滤条件里有外层查询的参数，则是相关子查询，反之则是不相关子查询。Any函数就代表只要有一个就行，最小的，all代表必须所有的都满足这个条件，所以必须最大的也满足。当我们判断子查询里是否存在的时候，则用exists判断，有则返回true。 条件简化&子查询（1）--Mysql基于规则优化（四十四) 子查询注意事项 1、子查询
【数据库】子查询注意的几个点
m0_49428126的博客
10-01 773
子查询是一个 SELECT 查询，它嵌套在 SELECT、INSERT、UPDATE、DELETE 语句或其它子查询中。任何允许使用表达 式的地方都可以使用子查询。
MySQL子查询和MySQL子查询注意事项
m0_37449634的博客
12-22 1173
前面我们介绍了如何使用 SELECT、INSERT、UPDATE 和 DELETE 语句对 MySQL 进行简单访问和操作。下面在此基础上开始学习子查询。子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。
mysql中子查询注意事项_MySQL 子查询用法和注意事项
weixin_42554408的博客
01-25 338
有时候一条sql语句解决不了什么问题，需要嵌套sql语句来实现目标，但是会影响执行的效率，这里简单说说其用法和注意事项。MySQL子查询是嵌套在另一个查询(如SELECT，INSERT，UPDATE或DELETE)中的查询。1，MySQL子查询在WHERE子句中 =，>，< WHERE子句中使用IN或NOT IN运算符SELECTcustomerNumber, checkNumbe...
09mysql子查询(重点难点)
01-16
这是我自己学习mysql时的学习笔记，每一个知识点都是自己动手写的，涵盖了mysql中的几乎全部的基础知识点，查询，子查询，分组，排序，常用函数，多表连接，视图，变量，存储过程，函数，分支结构，循环结构等等
MySQL进阶：分组查询、子查询与表连接详解
4. **子查询**（难点）： - 子查询是将一个查询嵌套在另一个查询中，可以用来作为条件、枚举限制，甚至创建临时表格进行更复杂的查询。 - - 子查询作为条件：`WHERE`子句中使用子查询的结果。 - - 子查询作为枚举...
SQL之分组统计和子查询专题
11-03
本课程涵盖了分组统计的重点和难点、子查询的概念和应用场景，以及在实际开发中需要注意的细节。 分组统计 在SQL中，分组统计是对数据进行分组和统计的操作。常用的聚合函数有COUNT、SUM、AVG、MAX、MIN等。其中，...
第17章 子查询
最新发布
weixin_44554794的博客
11-20 1336
介绍子查询的使用、分类和注意事项，以及SELECT结构中哪些位置可以使用子查询。
MySQL数据库中子查询注意事项
Advancing Swift 的博客
01-01 1050
SELECT PASSWORD FROM sw_user WHERE username = ( SELECT username FROM sw_user WHERE id = 2 );  子查询中的完整语句不要用“；”结束
MySQL 子查询用法和注意事项
weixin_34331102的博客
09-24 616
有时候一条sql语句解决不了什么问题，需要嵌套sql语句来实现目标，但是会影响执行的效率，这里简单说说其用法和注意事项。 MySQL子查询是嵌套在另一个查询(如SELECT，INSERT，UPDATE或DELETE)中的查询。 1，MySQL子查询在WHERE子句中 =，>，< WHERE子句中使用IN或NOT IN运算符 SELECT cu...
SQL编程之高级查询（子查询）以及注意事项
anju3120的博客
05-03 399
1.什么是子查询？ 当一个查询是另一个查询的条件时，称之为子查询。子查询可以使用几个简单命令构造功能强大的复合命令。子查询最常用于SELECT-SQL命令的WHERE子句中。子查询是一个 SELECT 语句，它嵌套在一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。 语法：selec...
子查询的注意事项
weixin_30492601的博客
05-13 479
任何允许适用表达式的地方都可以适用子查询 嵌套在select语句中的子查询 语法：select(子查询)from表名 嵌套在from语句中的子查询 语法：select...from (子查询) as 表的别名 例子 use E_Marketgo--[1]子查询作为列值来使用select * from CommoditySort where SortId=1--查询类别编号为1的商品的名...
嵌套子查询时需要注意的问题
weixin_34144848的博客
11-07 905
嵌套子查询时需要注意的： (1)、一个子查询必须放在圆括号中 (2)、将子查询放在比较条件的右边以增加可读性 (3)、子查询不包含order by子句。对一个select语句只能用一个order by子句。 (4)、order by子句可以使用，并且在进行top-n分析时是必须的，注意，如果指定了它就必须放在主select语句的最后。 (5)、在写嵌套子查询的时候，...
mysql相关子查询+in_SQL编程之高级查询（子查询）以及注意事项
weixin_29337389的博客
02-19 646
1.什么是子查询？当一个查询是另一个查询的条件时，称之为子查询。子查询可以使用几个简单命令构造功能强大的复合命令。子查询最常用于SELECT-SQL命令的WHERE子句中。子查询是一个 SELECT 语句，它嵌套在一个 SELECT、SELECT...INTO 语句、INSERT...INTO 语句、DELETE 语句、或 UPDATE 语句或嵌套在另一子查询中。语法：select ....fro...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值