等值连接查询 与 非等值连接查询 与 嵌套查询

等值连接查询

查询11号员工的名字及2018年每个月总工资

select   e.employee_id,  name, date, basic+bonus as total 
from employees as e   inner join salary as s 
on e.employee_id=s.employee_id
where year(date)=2018 and e.employee_id=11;


查询每个员工2018年的总工资，按总工资升序排列

select employees.employee_id, sum(basic+bonus) as total  
from employees inner join salary on employees.employee_id=salary.employee_id 
where year(salary.date)=2018 group by employee_id order by total asc;

 

非等值连接查询

查询2018年12月员工基本工资级别 :

 select employee_id, date, basic, grade
 from salary as s   inner join wage_grade as g 
 on s.basic between g.low and g.high
 where year(date)=2018 and month(date)=12;

 

外连接 (左连接查询,右连接查询,全外连接查询)

左连接查询

输出没有员工的部门名

select d.dept_name,e.name from departments as d  left  join employees as e on d.dept_id=e.dept_id where e.name is null;


仅显示departments表中dept_name表头

select d.dept_name from departments as d  left  join employees as e on d.dept_id=e.dept_id where e.name is null;

右连接查询

显示没有部门的员工名

select e.name from departments as d  right  join employees as e 
on d.dept_id=e.dept_id where d.dept_name is null ;

全外连接查询

输出2018年基本工资的最大值和最小值

 ( select basic  from salary where year(date)=2018 order by  basic  desc limit 1) union 
(select basic  from salary where year(date)=2018 
order by  basic asc limit 1 );


union 去掉查询结果中重复的行

(select employee_id , name , birth_date from employees where employee_id <= 5) 
union 
(select employee_id , name , birth_date from employees 
where employee_id <= 6);

第二个查询只输出了与条件匹配的最后1行

(select employee_id , name , birth_date from employees where employee_id <= 5) 
union  
(select employee_id , name , birth_date from employees 
where employee_id <= 6);

union all 不去重显示查询结果

(select employee_id , name , birth_date from employees where employee_id <= 5) 
union all  
(select employee_id , name , birth_date from employees 
where employee_id <= 6);

嵌套查询

from之后嵌套查询,where之后嵌套查询, having之后嵌套查询,select之后嵌套查询

嵌套查询：是指在一个完整的查询语句之中，包含若干个不同功能的小查询；从而一起完成复杂查询的一种编写形式。包含的查询放在（）里 ， 包含的查询出现的位置：

• SELECT之后
• FROM之后
• WHERE
• HAVING之后

where之后嵌套查询

select dept_id from departments where dept_name="运维部";

select  *  from  employees  
where  
dept_id = (select dept_id from departments where dept_name="运维部");

查询人事部2018年12月所有员工工资

//查看人事部的部门id
select dept_id from departments where dept_name='人事部';

//查找employees表里 人事部的员工id 
select employee_id from employees  
where 
dept_id=(select dept_id from departments where dept_name='人事部');

//查询人事部2018年12月所有员工工资 
select   *   from salary where year(date)=2018 and month(date)=12 
and employee_id in (select employee_id from employees 
where dept_id=(select dept_id from departments where dept_name='人事部') );

查询人事部和财务部员工信息

//查看人事部和财务部的 部门id
select dept_id from departments  where dept_name in ('人事部', '财务部');

//查询人事部和财务部员工信息
select dept_id , name  from employees 
where dept_id in ( 
select dept_id from departments  where dept_name in ('人事部', '财务部') 
);

查询2018年12月所有比100号员工基本工资高的工资信息

//把100号员工的基本工资查出来
select basic from salary  where year(date)=2018 and 
month(date)=12 and employee_id=100; 

//查看比100号员工工资高的工资信息
select  *  from salary 
where year(date)=2018 and month(date)=12 and 
basic>(select basic from salary where year(date)=2018 and 
month(date)=12 and employee_id=100);

having之后嵌套查询

查询部门员工总人数比开发部总人数少 的 部门名称和人数

//统计开发部员工总人数
select count(name) from employees 
where 
dept_id = (select dept_id from departments where dept_name="开发部");

//统计每个部门总人数 
select   dept_id , count(name) from employees group by   dept_id;

//输出总人数比开发部总人数少的部门名及总人数 
select  dept_id ,  count(name) as total    from employees group by dept_id 
having  
total < (select count(name) from employees  
where 
dept_id=(select dept_id from departments where dept_name='开发部')

from之后嵌套查询

查询3号部门 、部门名称 及其部门内 员工的编号、名字 和 email

select dept_id, dept_name, employee_id, name, email  
from 
(select  d.dept_name, e.*  from departments as d  inner join employees as e 
on d.dept_id=e.dept_id ) as tmp_table  where dept_id=3;

select之后嵌套查询

查询每个部门的人数： dept_id dept_name 部门人数

//显示部门表中的所有列表
select d.*  from departments as d;

//查询每个部门的人数
select  d.* , ( select count(name) 
from employees as e  
where d.dept_id=e.dept_id) as 部门人数  
from departments as d;