建公司网站要多少钱,php能开发大型网站,手机app与网站链接,单页网站产品#x1f4e2;本章节主要学习使用SQL多表查询的案例,多表查询基础概念 请点击此处。 #x1f384;数据准备
首先我们创建一个新的表也就是薪资等级表#xff0c;其余两个表(员工表和薪资表)在多表查询章节中已经创建。然后我么根据这三个表完成下面的12个需求。
create tab…本章节主要学习使用SQL多表查询的案例,多表查询基础概念 请点击此处。 数据准备
首先我们创建一个新的表也就是薪资等级表其余两个表(员工表和薪资表)在多表查询章节中已经创建。然后我么根据这三个表完成下面的12个需求。
create table salaryGrade(grade int,losal int,hisal int
)comment 薪资等级表;insert into salaryGrade 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 . 查询员工的姓名、年龄、职位、部门信息 隐式内连接 select employee.name,employee.age,employee.job,department.name from employee,department
where employee.dept_id department.id; ⭐案例2 查询年龄小于30 岁的员工的姓名、年龄、职位、部门信息显式内连接 select e.name,e.age,e.job,d.name from
employee as e join department as don (e.dept_id d.id and e.age 30); 还有一种是在on子句后面加where条件 select e.name,e.age,e.job,d.name from
employee as e join department as don e.dept_id d.id where e.age 30; ⭐案例3 查询拥有员工的部门ID 、部门名称 这里有个重点要使用distinct对查出的列进行去重操作。对于distinct来说是它后面所有的列都完全相同时才会去重。 select distinct department.id, department.name from employee,department
where employee.dept_id department.id ⭐案例4 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出 来 ( 外连接 ) select e.*,department.name from (select * from employee where employee.age 40) as eleft outer join department on e.dept_id department.id; 还有一种实现方式更为简单 select employee.*,department.name from employee left outer join departmenton employee.dept_id department.id where employee.age 40 ⭐案例5 查询所有员工的工资等级 select employee.*,salaryGrade.grade from employee,salarygradewhere employee.salary between salaryGrade.losal and salaryGrade.hisal; ⭐案例6 查询 研发部 所有员工的信息及 工资等级 首先涉及到3个表,3个表的连接条件至少有两个先确定连接条件连接条件:(employee.salary between salaryGrade.losal and salaryGrade.hisal) and department.id employee.dept_id查询条件:department.name 研发部 select employee.*,salaryGrade.grade from employee ,department,salaryGrade
where (employee.salary between salaryGrade.losal and salaryGrade.hisal) and department.id employee.dept_id
and department.name 研发部 ⭐案例7 研发部 员工的平均工资 select department.name, avg(employee.salary) as 平均工资
from employee,department
where employee.dept_id department.idand department.name 研发部;/* 每个部门平均工资 */
select department.name, avg(employee.salary) as 平均工资
from employee,department
where employee.dept_id department.id
group by employee.dept_id; ⭐案例8 查询工资比灭绝高的员工 这是一个典型的标量子查询因为返回的值只有一个值。 select name from employee where salary (
select salary from employee where name 灭绝) 当然这个也可以使用自查询只不过比子查询要复杂 select a.name from employee as a join employee as b on a.salary b.salary where b.name 灭绝 ⭐案例9 查询比平均薪资高的员工信息 select name from employee where salary (select avg(salary) from employee); ⭐案例10 查询低于本部门平均工资的员工信息 select * from employee as a where a.salary
(select avg(b.salary) from employee as b where b.dept_id a.dept_id) 还可以使用分组查询自连接 select a.* from employee as a join
(select employee.dept_id,avg(employee.salary) as avg_salary from employee group by dept_id )
as b on a.dept_id b.dept_id
where a.salary b.avg_salary ⭐案例11 查询所有的部门信息 , 并统计部门的员工人数 这里使用的子查询属于select类型。 select a.id, a.name, (select count(*) from employee as b where b.dept_id a.id) as 部门人数
from department as a; ⭐案例12 查询所有学生的选课情况 , 展示出学生名称 , 学号 , 课程名称 首先我们先把三张表的连接条件写出来。然后再去写表的查询条件。这个案例不需要写查询条件。 select student.name, student_course.studentno, student_course.courseno
from student,course,student_course
where student.no student_course.studentnoand course.name student_course.courseno
