语法:
select 查询列表 ⑦
from 表1 别名 ①
连接类型 join 表2 ②
on 连接条件 ③
where 筛选 ④
group by 分组列表 ⑤
having 筛选 ⑥
order by排序列表 ⑧
limit 起始条目索引,条目数; ⑨
#####################案例讲解
# 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) ;
#
#一、查询每个专业的学生人数
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