【SQL查询系列】子查询经典案例

admin管理员组

文章数量:1794759

【SQL查询系列】子查询经典案例

文章目录

• • 前言
  • • 数据库
    • 查询案例
    • • 1. 查询工资最低的员工信: last_name, salary
      • 2. 查询平均工资最低的部门信
      • 3. 查询平均工资最低的部门信和该部门的平均工资
      • 4. 查询平均工资最高的 job 信
      • 5. 查询平均工资高于公司平均工资的部门有哪些?
      • 6. 查询出公司中所有 manager 的详细信.
      • 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少
      • 8. 查询平均工资最高的部门的 manager 的详细信: last_name, department_id, email, salary
    • student数据库
    • 查询练习
    • • 一、查询每个专业的学生人数 SELECT majorid,COUNT(*)
      • 二、查询参加考试的学生中，每个学生的平均分、最高分
      • 三、查询姓张的每个学生的最低分大于60的学号、姓名
      • 四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称
      • 五、查询每个专业的男生人数和女生人数分别是多少
      • 六、查询专业和张翠山一样的学生的最低分
      • 七、查询大于60分的学生的姓名、密码、专业名
      • 八、按邮箱位数分组，查询每组的学生个数
      • 九、查询学生名、专业名、分数
      • 十、查询哪个专业没有学生，分别用左连接和右连接实现
      • 十一、查询没有成绩的学生人数
    • 【学习资料】
    • • 【01学习笔记】
      • 【02课程资料】

前言

近期在补SQL的基础，特别整理了一下SQL的一些查询练习语句，边学边笔记，持续更新~

数据库

查询案例 1. 查询工资最低的员工信: last_name, salary #①查询最低的工资 SELECT MIN(salary) FROM employees #②查询last_name,salary，要求salary=① SELECT last_name,salary FROM employees WHERE salary=( SELECT MIN(salary) FROM employees ); 2. 查询平均工资最低的部门信 #方式一： #①各部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id #②查询①结果上的最低平均工资 SELECT MIN(ag) FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep #③查询哪个部门的平均工资=② SELECT AVG(salary),department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN(ag) FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep ); #④查询部门信 SELECT d.* FROM departments d WHERE d.`department_id`=( SELECT department_id FROM employees GROUP BY department_id HAVING AVG(salary)=( SELECT MIN(ag) FROM ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ) ag_dep ) ); #方式二： #①各部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id #②求出最低平均工资的部门编号 SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1; #③查询部门信 SELECT * FROM departments WHERE department_id=( SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1 ); 3. 查询平均工资最低的部门信和该部门的平均工资 #①各部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id #②求出最低平均工资的部门编号 SELECT AVG(salary),department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1; #③查询部门信 SELECT d.*,ag FROM departments d JOIN ( SELECT AVG(salary) ag,department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) LIMIT 1 ) ag_dep ON d.`department_id`=ag_dep.department_id; 4. 查询平均工资最高的 job 信 #①查询最高的job的平均工资 SELECT AVG(salary),job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1 #②查询job信 SELECT * FROM jobs WHERE job_id=( SELECT job_id FROM employees GROUP BY job_id ORDER BY AVG(salary) DESC LIMIT 1 ); 5. 查询平均工资高于公司平均工资的部门有哪些? #①查询平均工资 SELECT AVG(salary) FROM employees #②查询每个部门的平均工资 SELECT AVG(salary),department_id FROM employees GROUP BY department_id #③筛选②结果集，满足平均工资>① SELECT AVG(salary),department_id FROM employees GROUP BY department_id HAVING AVG(salary)>( SELECT AVG(salary) FROM employees ); 6. 查询出公司中所有 manager 的详细信. #①查询所有manager的员工编号 SELECT DISTINCT manager_id FROM employees #②查询详细信，满足employee_id=① SELECT * FROM employees WHERE employee_id =ANY( SELECT DISTINCT manager_id FROM employees ); 7. 各个部门中 最高工资中最低的那个部门的 最低工资是多少 #①查询各部门的最高工资中最低的部门编号 SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1 #②查询①结果的那个部门的最低工资 SELECT MIN(salary) ,department_id FROM employees WHERE department_id=( SELECT department_id FROM employees GROUP BY department_id ORDER BY MAX(salary) LIMIT 1 ); 8. 查询平均工资最高的部门的 manager 的详细信: last_name, department_id, email, salary #①查询平均工资最高的部门编号 SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1 #②将employees和departments连接查询，筛选条件是① SELECT last_name, d.department_id, email, salary FROM employees e INNER JOIN departments d ON d.manager_id = e.employee_id WHERE d.department_id = (SELECT department_id FROM employees GROUP BY department_id ORDER BY AVG(salary) DESC LIMIT 1) ; student数据库

查询练习 一、查询每个专业的学生人数 SELECT majorid,COUNT(*) FROM student GROUP BY majorid; 二、查询参加考试的学生中，每个学生的平均分、最高分 SELECT AVG(score),MAX(score),studentno FROM result GROUP BY studentno; 三、查询姓张的每个学生的最低分大于60的学号、姓名 SELECT s.studentno,s.`studentname`,MIN(score) FROM student s JOIN result r ON s.`studentno`=r.`studentno` WHERE s.`studentname` LIKE '张%' GROUP BY s.`studentno` HAVING MIN(score)>60; 四、查询每个专业生日在“1988-1-1”后的学生姓名、专业名称 SELECT m.`majorname`,s.`studentname` FROM student s JOIN major m ON m.`majorid`=s.`majorid` WHERE DATEDIFF(borndate,'1988-1-1')>0 GROUP BY m.`majorid`; 五、查询每个专业的男生人数和女生人数分别是多少 SELECT COUNT(*),sex,majorid FROM student GROUP BY sex,majorid; 六、查询专业和张翠山一样的学生的最低分 #①查询张翠山的专业编号 SELECT majorid FROM student WHERE studentname = '张翠山' #②查询编号=①的所有学生编号 SELECT studentno FROM student WHERE majorid=( SELECT majorid FROM student WHERE studentname = '张翠山' ) #②查询最低分 SELECT MIN(score) FROM result WHERE studentno IN( SELECT studentno FROM student WHERE majorid=( SELECT majorid FROM student WHERE studentname = '张翠山' ) ) 七、查询大于60分的学生的姓名、密码、专业名 SELECT studentname,loginpwd,majorname FROM student s JOIN major m ON s.majorid= m.majorid JOIN result r ON s.studentno=r.studentno WHERE r.score>60; 八、按邮箱位数分组，查询每组的学生个数 SELECT COUNT(*),LENGTH(email) FROM student GROUP BY LENGTH(email); 九、查询学生名、专业名、分数 SELECT studentname,score,majorname FROM student s JOIN major m ON s.majorid= m.majorid LEFT JOIN result r ON s.studentno=r.studentno 十、查询哪个专业没有学生，分别用左连接和右连接实现 #左 SELECT m.`majorid`,m.`majorname`,s.`studentno` FROM major m LEFT JOIN student s ON m.`majorid` = s.`majorid` WHERE s.`studentno` IS NULL; #右 SELECT m.`majorid`,m.`majorname`,s.`studentno` FROM student s RIGHT JOIN major m ON m.`majorid` = s.`majorid` WHERE s.`studentno` IS NULL; 十一、查询没有成绩的学生人数 SELECT COUNT(*) FROM student s LEFT JOIN result r ON s.`studentno` = r.`studentno` WHERE r.`id` IS NULL 【学习资料】 【01学习笔记】

• 【SQL数据库基础01】数据库和SQL概述
• 【SQL数据库基础02】MySQL最新版安装使用教程–（安装、卸载、登录、常用命令、语法规范、图形化界面SQLyog）
• 【SQL数据库基础03】数据处理之查询 (•过滤和排序数据 •分组函数 •分组查询 •多表查询 •分页查询 •联合查询)
• 【SQL查询系列】子查询经典案例
• 【SQL数据库基础04】数据操纵语言DML，即SQL的增删改查
• 【SQL数据库基础05】数据定义语言DDL-库表的管理、数据类型、常见的约束
• 【SQL数据库基础05-1】常见的数据类型–• 数值类型• 字符类型• 日期类型
• 【SQL数据库基础05-2】常见约束与标识列
• 【SQL数据库基础06】事务控制语言TCL–事务（概念、特性、隔离级别、案例演示）
• 【SQL数据库基础07】视图–定义、增删改查视图
• 【SQL数据库基础08】变量–系统变量：全局、会话、自定义变量
• 【SQL数据库基础09】存储过程
• 【SQL数据库基础10】函数
• 【SQL数据库基础11】流程控制结构

【02课程资料】

• 《数据库系统概论（第5版）》
• 计院学网盘复习资料–数据库
• 尚硅谷MySQL核心技术-婷姐

本文标签： 案例系列经典SQL