mysql数据库之子查询练习

1、查询员工的姓名、年龄、职位、部门信息（隐式内连接）。

#emp、dept
#连接条件：emp.dept_id=dept.id
select e.name,e.age,e.job,d.name from emp e，dept d where e.dept_id=d.id;

2、查询年龄小于30岁的员工姓名、年龄、职位、部门信息（显示内连接）。

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

3、查询拥有员工的部门id、部门名称（内连接）。

#emp、dept
#连接条件：emp.dept_id=dept.id
select distinct d.id,d.name from emp e,dept d where e.dept_id=d.id;

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

#emp、dept
#连接条件：emp.dept_id=dept.id
#左外连接
select e.*,d.name from emp e left join dept d on e.dept_id=d.id where e.age > 40;

5、查询所有员工的工资等级。

写法1

#emp、salgrade
#连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.*,s.grade,s.losal,s.hisal from emp e,salgrade s where e.salary >= s.losal and e.salary <= s.hisal;

写法2（between）

#emp、salgrade
#连接条件：emp.salary >= salgrade.losal and emp.salary <= salgrade.hisal
select e.*,s.grade,s.losal,s.hisal from emp e,salgrade s where e.salary between s.losal and s.hisal;

6、查询研发部所有员工的信息及工资等级。

#表emp，salgrade，dept
#连接条件：emp.salary between salgrade.losal and salgrade.hisal，emp.dept_id=dept.id;
#查询条件：dept.name = '研发部';
select e.*,s.grade from emp e，dept d,salgrade s where e.dept_id= d.id and (e.salary between s.losal and s.hisal) and d.name='研发部';#美化后的sql
SELECT #之后是查询返回的字段e.*,s.grade 
FROM #之后是所查询的表emp e，dept d,salgrade s 
WHERE #之后是查询条件e.dept_id = d.id AND ( e.salary BETWEEN s.losal AND s.hisal ) AND d.NAME = '研发部';

7、查询研发部员工的平均工资。

#表：emp、dept
#连接条件：emp.dept_id=dept.id
select avg(e.salary) from emp e,dept d where e.dept_id = d.id and d.name = '研发部';

8、查询工资比灭绝高的员工信息。

#查询灭绝的薪资
select salary from emp where name = '灭绝';
#查询比灭绝工资高的员工信息
select * from emp where salary > 8500;#优化后的子查询（只返回一个值，标量子查询）
select * from emp where salary > (select salary from emp where name = '灭绝');

9、查询比平均薪资高的员工信息。

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

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

#查询指定部门平均薪资
select avg(salary) from emp e1 where e1.dept_id = 1;
select avg(salary) from emp e1 where e1.dept_id = 2;
#查询低于本部门平均工资的员工信息。
select *,(select avg(salary) from emp e1 where e1.dept_id = e2.dept_id) '平均薪资' from emp e2 where e2.salary < (select avg(salary) from emp e1 where e1.dept_id = e2.dept_id);

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

#查询部门的员工信息。
select d.id,d.name,(select count(*) from emp e where e.dept_id=d.id) '人数' from dept d;
#查询人数。
select count(*) from emp e where dept_id=1;

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

#表：student，course，student_course
#连接条件：student.id=student_course.studentid,course.id=student_course.courseid;
select s.name,s.no,c.name from student s,student_course sc,course c where s.id=sc.studentid and sc.courseid=c.id;