MySql-多表查询（非常重要）

一：多表关系

二：概述/分类：

在SQL语句中，如何来去除无效的笛卡尔积呢？ 我们可以给多表查询加上连接查询的条件即可。

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

分类：

三:内连接-消除笛卡尔积

四：外连接

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

五：自连接

select e1.name ,e2.name  from emp e1 join emp e2 on e1.managerid=e2.id;

B.查询所有员工 emp 及其领导的名字 emp , 如果员工没有领导, 也需要查询出来
表结构: emp a , emp b

select e1.name ,e2.name  from emp e1 left join emp e2 on e1.managerid=e2.id;

联合查询：

select * from emp where salary < 5000 or age > 50;
select * from emp where salary < 5000 union all select * from emp where age > 50;

六：子查询

1.标量子查询

select * from emp where entrydate > (select entrydate from emp where name = "方东白");

2.列子查询

select * from emp where salary > all ( select salary from emp where dept_id =
(select id from dept where name = '财务部') );

C. 查询比研发部其中任意一人工资高的员工信息

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

3.行子查询

4.表子查询

 案例：

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

-- 查询员工的姓名、年龄、职位、部门信息 （隐式内连接）
select e.name,e.age,e.job,d.name from emp e,dept d where e.dept_id = d.id;

-- 查询年龄小于30岁的员工的姓名、年龄、职位、部门信息（显式内连接）
select e.name,e.age,e.job,d.name from emp e join dept d on e.dept_id = d.id and e.age < 30 ;

-- 查询拥有员工的部门ID、部门名称
select e.* ,d.name from emp e left join dept d on e.dept_id = d.id;


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

-- 查询所有年龄大于40岁的员工, 及其归属的部门名称; 如果员工没有分配部门, 也需要展示出来(外连接)

select e.*,d.name from emp e left join dept d on e.dept_id = d.id where e.age > 40;

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

select e.*,s.* from salgrade s join  emp e on e.salary >= s.losal and e.salary <= s.hisal;

select e.* , s.grade , s.losal, s.hisal from emp e , salgrade s where e.salary >=
s.losal and e.salary <= s.hisal;

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

-- 查询 "研发部" 所有员工的信息及 工资等级
select e.*,s.grade from emp e,salgrade s,dept d where d.id=e.dept_id and d.name = '研发部' and e.salary between s.losal and s.hisal;

-- 查询 "研发部" 员工的平均工资
select avg(e.salary) from emp e ,dept d where e.dept_id = d.id and d.name= '研发部';

-- 查询工资比 "灭绝" 高的员工信息
select * from emp e where salary > (select emp.salary from emp where emp.name = '灭绝');

-- 查询比平均薪资高的员工信息

select * from emp e where e.salary > (select avg(salary) from emp );

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

select * from emp e2 where e2.salary < (select avg(e1.salary) from emp e1 where e1.dept_id = e2.dept_id );


-- 查询所有的部门信息, 并统计部门的员工人数 **
select d.id, d.name ,(select count(*) from emp e where e.dept_id = d.id) from dept d;

-- 查询所有学生的选课情况, 展示出学生名称, 学号, 课程名称
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;