本文中数据表中数据均为假设
表名：t_emp, 各字段名和部分值如下图所示:
表:t_dept,各字段名和部分值如下图所示:
表名：t_salgrade, 各字段名和部分值如下图所示:
本文章通过举例整理相关常用的查询语句：
一、单表查询
1. 直接查询：
select * from t_emp;
2. 排序查询：
按工资升序降序查询信息
select empno,ename,job,sal from t_emp order by sal asc;(升序）
select empno,ename,job,sal from t_emp order by sal desc;(降序)
3. 分页查询:
从0开始显示5条分页查询信息
select ename as "员工",sal as "工资" from t_emp limit 0,5;
select ename as "员工",sal as "工资" from t_emp order by sal desc limit 0,5;(综合)
4. 条件查询:
工资大于2000的员工:
select empno,ename,job,sal from t_emp where (sal>=2000);
年薪大于20000,部门编号为10:
select empno,ename,job,sal from t_emp where (sal*12>20000 and deptno=10);
名字和A有关的员工:
select empno,ename,job,sal from t_emp where ename like "%A%";(包含A的员工)select empno,ename,job,sal from t_emp where ename like "A%";(A开头的员工)select empno,ename,job,sal from t_emp where ename like "%A";(A结尾的员工)
查询年薪超过15000并且工龄超过20年的员工:
select empno,ename,hiredate from t_emp where (sal+ifnull(comm,0)*12>=15000 and datediff(now(),hiredate)/365>=20);
综合查询:
select distinct deptno from t_emp order by deptno asc where sal+ifnull(comm,0)*12>=10000 and datediff(now(),hiredate)/365>=10 limit 0,2;
5.聚合函数:
求平均值函数AVG()
select avg(sal) from t_emp where deptno=10;(10号单位平均工资)
求和函数SUM()
select sum(sal) from t_emp where deptno=10;(10号单位总工资)
求最大值函数MAX(),最小值函数MIN()
select max(sal) from t_emp where deptno=10;(10号单位最大工资数)
select min(sal) from t_emp where deptno=10;(10号单位最小工资数)
求总数函数COUNT()
select count(*) from t_emp where deptno=10;(10号单位员工总数)
6.分组查询:
查询每部门的平均工资(取整):
select deptno,round(avg(sal)) from t_emp group by deptno;
查询每个部门的人数,平均工资,最大工资数,最小工资数,并汇总:
select deptno,count(*),max(sal),min(sal) from t_emp group by deptno with rollup;
查询每个部门中工资大于2000的员工的姓名和总数:
select deptno,group_concat(ename),count(*) from t_emp where sal>=2000 group by deptno;
7.HAVING用法:
HAVING是与GROUP BY一起使用的,如果条件包含聚合函数,不能放在where中,要放在HAVING中.
查询平均工资超过2000的部门:
select deptno from t_emp group by deptno having avg(sal)>2000;
查询1982年以后入职的人数超过2人的部门:
select deptno from t_emp where hiredate group by deptno having count(*)>=2 order by deptno;
二、多表查询
1.内连接查询:
查询部门表中每个员工的员工编号,姓名,职务,部门编号
select e.empno,e.ename,e.job,d.deptno from t_emp e,t_dept d where e.deptno=d.deptno;
查询与SCOTT相同部门的员工
select e2.ename from t_emp e1,t_emp e2 where e1.deptno=e2.deptno and e1.ename="SCOTT" and e2.ename!="SCOTT";
查询每种职业的最高工资,最低工资,平均工资,最高工资等级和最低工资等级
select e.job,max(e.sal+ifnull(comm,0)) as max,min(e.sal+ifnull(comm,0)) as min,floor(avg(e.sal+ifnull(comm,0))) as avg,max(s.grade),min(s.grade) from t_emp e,t_salgrade s where e.sal+ifnull(comm,0) between s.losal and hisal group by job;
查询每个部门超过部门平均底薪的员工信息
select e.empno,e.ename,e.job,e.sal from t_emp e,(select deptno,avg(sal) as avg from t_emp group by deptno) t where e.deptno=t.deptno and e.sal>=t.avg;
2.外连接查询:
查询每个部门的部门名称和人数
select d.dname,count(e.deptno) from t_dept d left join t_emp e on e.deptno=d.deptno group by d.deptno;
查询每个员工的员工编号,姓名,部门编号,部门名称,工资,工资等级,工龄和上司编号
select e.empno,e.ename,e.deptno,d.dname,e.sal+ifnull(comm,0) as sal,s.grade,floor(datediff(now(),e.hiredate)/365) as time,e.mgr from t_emp e left join t_dept d on e.deptno=d.deptno left join t_salgrade s on e.sal+ifnull(comm,0) between s.losal and hisal;
3.子查询:
查询与FORD和MARTIN相同部门的员工
select ename from t_emp where deptno in (select deptno from t_emp where ename in ("FORD","MARTIN")) and ename not in("FORD","MARTIN");
查询工资比FORD和MARTIN都高的员工
select ename from t_emp where sal>=all(select sal from t_emp where ename in ("FORD","MARTIN")) and ename not in("FORD","MARTIN");
