SQL中的查询语句总结(实例)

首先我们使用MySQL创建两个表

– 创建人力资源管理系统数据库

drop database if exists hrs;
create database hrs default charset utf8;

– 切换数据库上下文环境

use hrs;

– 删除表

drop table if exists tb_emp;
drop table if exists tb_dept;

– 创建部门表

create table tb_dept
(
dno int comment '部门编号',	
dname varchar(10) not null comment '部门名称',	
dloc varchar(20) not null	comment '部门所在地', 
PRIMARY key (dno)
);

– 添加部门记录

insert into tb_dept values (10, '会计部', '北京'),
 (20, '研发部', '成都'),
 (30, '销售部', '重庆'),
 (40, '运维部', '深圳');

– 创建员工表

create table tb_emp
(
eno int comment '员工编号',
ename varchar(20) not null comment '员工姓名',
job varchar(20) not null comment '员工职位' ,	
mgr int comment '主管编号',	
sal int not null comment '员工工资',
comm int comment '每月补贴',
dno int	comment '所在部门编号',
primary key (eno),
foreign key (dno) REFERENCES tb_dept(dno)
);

– 添加员工记录

insert into tb_emp values 
(7800, '张三丰', '总裁', null, 9000, 1200, 20),
(2056, '乔峰', '分析师', 7800, 5000, 1500, 20),
(3088, '李莫愁', '设计师', 2056, 3500, 800, 20),
(3211, '张无忌', '程序员', 2056, 3200, null, 20),
(3233, '丘处机', '程序员', 2056, 3400, null, 20),
(3251, '张翠山', '程序员', 2056, 4000, null, 20),
(5566, '宋远桥', '会计师', 7800, 4000, 1000, 10),
(5234, '郭靖', '出纳', 5566, 2000, null, 10),
(3344, '黄蓉', '销售主管', 7800, 3000, 800, 30),
(1359, '胡一刀', '销售员', 3344, 1800, 200, 30),
(4466, '苗人凤', '销售员', 3344, 2500, null, 30),
(3244, '欧阳锋', '程序员', 3088, 3200, null, 20),
(3577, '杨过', '会计', 5566, 2200, null, 10),
(3588, '朱九真', '会计', 5566, 2500, null, 10);

下面是查询语句:

– 查询薪资最高的员工姓名和工资

这里是引用

-- select ename, sal from tb_emp order by sal desc limit 1;
select ename, sal from tb_emp 
where sal=(select max(sal) from tb_emp);

– 查询员工的姓名和年薪((工资+补贴)*12)

select ename, (sal+ifnull(comm, 0))*12 as annsal 
from tb_emp order by annsal desc;

– 查询有员工的部门的编号和人数

select dno, count(dno) as total from tb_emp group by dno;

– 查询所有部门的名称和人数
– 当列有二义性的时候需要使用完全限定名

select dname, total from tb_dept t1,
(select dno, count(dno) as total from tb_emp group by dno) t2 
where t1.dno=t2.dno;

select dname as 部门名称, ifnull(total, 0) as 人数 
from tb_dept t1 left outer join 
(select dno, count(dno) as total from tb_emp group by dno) t2 
on t1.dno=t2.dno;

– 查询薪资最高的员工(Boss除外)的姓名和工资

select ename, sal from tb_emp 
where sal=(select max(sal) from tb_emp where mgr is not null);

– 查询薪水超过平均薪水的员工的姓名和工资

select ename, sal from tb_emp 
where sal>(select avg(sal) from tb_emp);

– 查询薪水超过其所在部门平均薪水的员工的姓名、部门编号和工资

select ename, t1.dno, sal, sal-avgsal as delta
from tb_emp t1 inner join 
(select dno, avg(sal) as avgsal 
from tb_emp group by dno) t2 on t1.dno=t2.dno   
where sal>avgsal;

select ename, dno, sal, sal-avgsal as delta
from tb_emp t1 inner join 
(select dno as deptno, avg(sal) as avgsal 
from tb_emp group by dno) t2 on deptno=dno   
where sal>avgsal;

– 查询部门中薪水最高的人姓名、工资和所在部门名称

select ename, sal, dname from tb_dept t3 inner join 
(select ename, sal, t1.dno from tb_emp t1 inner join 
(select dno, max(sal) as maxsal 
from tb_emp group by dno) t2 on t1.dno=t2.dno 
where sal=maxsal) t4 on t3.dno=t4.dno;

– 查询主管的姓名和职位

select ename, job from tb_emp where eno in 
(select distinct mgr from tb_emp where mgr is not null);

– 集合运算和去重操作性能都非常糟糕应该尽量避免使用
– 可以使用exists和not exists来替代集合运算和去重

select ename, job from tb_emp t1 where exists 
(select 'x' from tb_emp t2 where t1.eno=t2.mgr);

select ename, job from tb_emp t1 where not exists 
(select 'x' from tb_emp t2 where t1.eno=t2.mgr);

– 查询薪资排名4~6名的员工姓名和工资

select ename, sal from tb_emp order by sal desc limit 3,3;
select ename, sal from tb_emp order by sal desc limit 3 offset 3;