【十一，MySQL】基础篇--多表查询 练习、总结

练习：

#=================================================练习===============================================
# 1.查询员工的姓名、年龄、职位、部门信息。
select a.name as 姓名,a.age as 年龄,a.job as 职位,b.name as 部门 from emp a
left join dept b on a.dept_id = b.id;

# 2.查询年龄小于30岁的员工姓名、年龄、职位、部门信息。
select a.name as 姓名,a.age as 年龄,a.job as 职位,b.name as 部门 from emp a
left join dept b on a.dept_id = b.id
where a.age < 30;

# 3.查询拥有员工的部门ID、部门名称。
select distinct b.id as ID,b.name as 部门 from emp a
right join dept b on a.dept_id = b.id;

# 4．查询所有年龄大于20岁的员工,及其归属的部门名称;如果员工没有分配部门,也需要展示出来。
select a.*,b.name as 部门 from emp a
left join dept b on a.dept_id = b.id
where a.age > 20;

# 5.查询所有员工的工资等级。（隐式内连接）
# 表：emp salgrade
# 连接条件：emp.salary between salgrede.losal and salgrede.hisal;
select a.*,b.grade as 薪资等级 ,b.losal ,b.hisal from emp a,salgrede b where a.salary between b.losal and b.hisal;

# 6．查询"研发部"所有员工的信息及工资等级。
# 连接条件：emp.salary between salgrede.losal and salgrede.hisal , emp.dept_id = dept.id
# 查询条件：dept.name = '研发部'
select a.* , b.name ,c.grade from emp a
left join dept b on a.dept_id = b.id
left join salgrede c on a.salary between c.losal and c.hisal
where b.name = '研发部';

# 7．查询"研发部"员工的平均工资。
select round(avg(a.salary),2) as 平均薪资 from emp a
left join dept b on a.dept_id = b.id
where b.name = '研发部';

# 8．查询工资比"王五"高的员工信息。
select * from emp where salary > (select salary from emp where name = '王五');

# 9.查询比平均薪资高的员工信息。
select * from emp where salary > (select avg(salary) from emp);

# 10.查询低于本部门平均工资的员工信息。
# ①查询指定部门的平均薪资
# select avg(a.salary) from emp a where a.dept_id = '2';
# ②查询比本部门平均工资低的员工信息
# select * from emp where salary < (当前部门平均薪资)
select b.*,(select avg(a.salary) from emp a where a.dept_id = b.dept_id) as 当前部门平均薪资
from emp b where b.salary < (select avg(a.salary) from emp a where a.dept_id = b.dept_id);

# 11.查询所有的部门信息,并统计部门的员工人数。
# ①查询某个部门的员工人数
select count(*) from emp b where b.dept_id = '5';
# ②查询所有部门信息
select a.*,(select count(*) from emp b where b.dept_id = a.id) as 人数 from dept a;

# 12.查询所有学生的选课情况,展示出学生名称,学号,课程名称
# 表结构：student student_score score
# ①查询某个学生的选课课程id
select a.* , b.score_id as 课程id from student a
left join student_score b on a.id = b.student_id;
# ②再关联课程表
select a.name as 姓名 ,a.no as 学号, b.score_id as 课程编号,c.name as 课程 from student a
left join student_score b on a.id = b.student_id
left join score c on b.score_id = c.id;