mysql数据库之子查询练习

矫情吗；* 2023-10-11 20:56 8阅读 0赞

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;