mysql多表查询练习

多表查询语法详解：mysql多表查询-CSDN博客

准备工作，新增了一个工资等级表,其他表在上面地址中已经创建好了。

create table salgrade(grade int,losal int,hisal int)comment '工资等级表';
insert into salgrade
values (1, 0, 3000),(2, 3001, 5000),(3, 5001, 8000),(4, 8001, 10000),(5, 10001, 15000),(6, 15001, 20000),(7, 20001, 25000),(8, 25001, 30000);

练习1：

# 查询员工的姓名、年龄、职位、部门信息。

表：emp ，dept

链接条件：emp.dept_id=dept.id

隐式内连接

select emp.name,emp.age,emp.job,dept.name from emp,dept where emp.dept_id=dept.id;

显示内连接

select e.name,e.age,e.job,d.name from emp e join dept d on d.id = e.dept_id;

结果：

练习2：

查询年龄小于30岁的员工的姓名、年龄、职位、部门信息。

表：emp ，dept

链接条件：emp.dept_id=dept.id

查询条件：emp.age<30

select e.name,e.age,e.job,e.name
from emp e join dept d on e.dept_id=d.id where age<30;

结果：

 

练习3：

查询拥有员工的部门id，部门名称。获取emp和dept两张表的交集

表：emp ，dept

链接条件：emp.dept_id=dept.id

    select distinct d.* from dept d ,emp e where e.dept_id=d.id;

结果：

练习4：

查询年龄大于40岁的员工，及其归属的部门名称，如果员工没有分配部门，也需要展示出来。

表：emp ，dept

链接条件：emp.dept_id=dept.id

查询条件：age>40

左外连接：获取emp所有数据和emp与dept交集部分。

    select e.name,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;

结果：

练习5

查询所有员工的工资等级.

表：emp ,salgrade

链接条件：e.salary between s.losal and s.hisal

select e.name,s.grade from emp e,salgrade s where e.salary between s.losal and s.hisal;

 结果：

练习6

查询“研发部”所有员工的信息及平均工资

表：emp ，dept

链接条件：emp.dept_id=dept.id查

select emp.*,dept.name,(select avg(salary) from emp where dept_id=dept.id ) as '研发平均薪资' from emp,dept where emp.dept_id=dept.id and dept.name='研发部';

结果：

练习7

查询“研发部”所有员工信息及其员工等级。

表：emp ，dept，salgrade

链接条件：emp.dept_id=dept.id

    select * from emp e,salgrade s,dept d where (e.salary between s.losal and s.hisal ) and e.dept_id=d.id and d.name='研发部';

结果：

练习8

查询“研发部”员工的平均工资。

表：emp ，dept

标量子查询

    select avg(salary) from emp where dept_id=(select id from dept where name='研发部');

结果：

练习9

查询比“灭绝”工资高得员工信息。

表：emp

标量子查询

    select * from emp where salary>(select salary from emp where name='灭绝');

 结果：

练习11

查询低于本部门平均工资的员工信息。

表：emp，dept

# 第一步：查询单个部门的平局工资
select avg(salary) from emp where dept_id=1;
# 第二步：查询低于本部门平均工资的员工信息
select * from emp e where salary<(select avg(salary) from emp where dept_id=e.dept_id);

练习12

查询所有部门部门信息，并统计部门的员工数量。

表：emp，dept

select d.*,(select count(*) from emp where dept_id=d.id) '部门人数' from dept d ;

 结果：

练习13

查询所有学生的选课情况，展示出学生的名称、学号、课程名称。

表：student，course，student_course

连接条件：student.id=student_course.studentid and course.id=student_course.courseid

select student.name,course.name from course,student,student_course where student.id=student_course.studentid and course.id=student_course.courseid;

结果：