尚硅谷MySQL学习笔记(Day_4)-DQL语言介绍：练习和作业

最新推荐文章于 2023-10-19 15:29:23 发布

原创最新推荐文章于 2023-10-19 15:29:23 发布 · 217 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

MySQL笔记专栏收录该内容

24 篇文章

订阅专栏

这篇博客主要介绍了DQL语言，包括子查询的经典案例，如查询最低工资员工、平均工资最低部门等，并详细讲解了相关作业，如查询每个专业学生人数、平均分等。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DQL语言

子查询经典案例题目

查询工资最低的员工信息: last_name, salary

SELECT last_name,salary
FROM employees
WHERE salary = (
	SELECT MIN(salary)
	FROM employees
);

在这里插入图片描述

查询平均工资最低的部门信息

#方法一
SELECT *
FROM departments 
WHERE  department_id=(
	SELECT department_id #找到id
	FROM (
			SELECT department_id,AVG(salary)平均工资
			FROM employees 
			GROUP BY department_id
	) 平均工资表
	WHERE 平均工资= (
		SELECT MIN(平均工资) 最低平均工资
		FROM(
			SELECT department_id,AVG(salary)平均工资
			FROM employees
			GROUP BY department_id
		) 平均工资表
	)
)

#方式二
SELECT *
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)

在这里插入图片描述

查询平均工资最低的部门信息和该部门的平均工资

#方式一：连接查询
SELECT d.* , ag_dep.ag
FROM departments d
INNER JOIN(
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
	ORDER BY ag
	LIMIT 1
) ag_dep
ON d.department_id = ag_dep.department_id

#方式二：子查询
SELECT *,(
	SELECT AVG(salary)
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)ag
FROM departments
WHERE department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary)
	LIMIT 1
)

在这里插入图片描述

查询平均工资最高的 job 信息

#4. 查询平均工资最高的 job 信息
SELECT *
FROM jobs
WHERE job_id = (
	SELECT job_id
	FROM employees
	GROUP BY job_id
	ORDER BY AVG(salary) DESC
	LIMIT 1
)

在这里插入图片描述

查询平均工资高于公司平均工资的部门有哪些?

SELECT department_name
FROM departments
WHERE department_id IN (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	HAVING AVG(salary) > (
		SELECT AVG(salary)
		FROM employees	
	)	
)

在这里插入图片描述

查询出公司中所有 manager 的详细信息.

SELECT *
FROM employees e
WHERE e.employee_id =ANY (
	SELECT DISTINCT manager_id
	FROM employees
)

在这里插入图片描述

各个部门中最高工资中最低的那个部门的最低工资是多少

SELECT MIN(salary)
FROM employees
WHERE department_id =(
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY MAX(salary)
	LIMIT 1
)

在这里插入图片描述

查询平均工资最高的部门的 manager 的详细信息: last_name, department_id, email,salary

#方式一：
SELECT last_name,department_id,email,salary
FROM employees
WHERE employee_id = (
	SELECT manager_id
	FROM departments
	WHERE department_id = (
		SELECT department_id
		FROM employees
		GROUP BY department_id
		ORDER BY AVG(salary) DESC
		LIMIT 1
	)
)

#方式二；
SELECT last_name,e.department_id,email,salary
FROM employees e
INNER JOIN departments d
ON e.`employee_id` = d.`manager_id`
WHERE e.department_id = (
	SELECT department_id
	FROM employees
	GROUP BY department_id
	ORDER BY AVG(salary) DESC
	LIMIT 1	
)

在这里插入图片描述

作业讲解

查询每个专业的学生人数

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