WEB02_Day05(下)-sql运算符、模糊查询、排序和分页、数值计算和聚合函数、分组查询、子查询
一、sql运算符
使用is null 和 is not null判断数据是否为空
-
查询没有上级领导的员工信息
select *from emp where mgr is null;
-
查询有上级领导的员工姓名
select ename from emp where mgr is not null;
1.2 关系运算符
sql的比较运算符:> < >= <= = !=和<>
-
查询员工工资小于等3000的姓名和工资
select ename,sal from emp where sal<=3000;
-
查询工作不是程序员的员工姓名和工作
select ename,job from emp where job!='程序员';
select ename,job from emp where job<>'程序员';//标准
1.3 逻辑运算符
sql中的逻辑运算符and 和 or 类似java 中的 && 和 ||
-
查询1号部门工资高于2000的员工信息
select * from emp where deptno = 1 and sal>2000;
-
查询工作是人事或工资大于3000的员工姓名,工资,工作
select ename,sal,job from emp where job='人事' or sal>3000;
-
查询工资高于1000~3000的员工信息
select *from emp where sal>1000 and sal<3000;
1.4 between关键字
语法: between x and y查询x和y之间 包含x和y
-
查询工资在2000到3000之间的员工姓名和工资
select ename,sal from emp where sal>=2000 and sal<=3000;
select ename,sal from emp where sal between 2000 and 3000;
1.5 in关键字
语法: [列名] in (x,y,z)
-
查询员工工资为3000,1500,5000的员工姓名和工资
select ename,sal from emp where sal=3000 or sal=1500 or sal=5000;
select ename,sal from emp where sal in(3000,1500,5000);
1.6 练习
-
查询有上级领导并且是3号部门的员工信息
select * from emp where mgr is not null and deptno=3;
-
查询2号部门工资在1000到2000之间的员工姓名 工资和部门编号
select ename,sal,deptno from emp where deptno=2 and sal between 1000 and 2000;
-
查询1号部门工资为800和1600的员工信息
select * from emp where deptno = 1 and sal in(800,1600);
二、模糊查询
当要进行查询的数据只是部分记忆,并没有完全记住数据的名称,那么我们需要通过部分数据内容进行模糊匹配相关的数据,使用模糊查询需要匹配数据的类型为字符串类型(char/varchar/text)
2.1like关键字
使用like关键字可以替换sql语句中的关系运算符,进而通过部分数据做数据的模糊查询操作
2.2 实现模糊查询的通配符
-
通配符%: 代表0或多个任意字符
-
通配符_: 代表1个任意字符 _
举例:
1.以x开头 'x%'
eg:李
2.以x结尾 '%x'
eg:末尾詹姆斯
3.包含x
'%x%'
4.第二个字符是x
'_x%'
5.倒数第三个字符是x
'%x__'
6.以x开头倒数第二个是y
'x%y_'
-
查询姓孙的员工信息
select * from emp where ename like '孙%';
-
查询工作中包含’销售‘字样的员工姓名和工作
select ename,job from emp where job like '%销售%';
-
查询名字中以‘精’结尾的员工姓名
select ename from emp where ename like '%精';
三、排序和分页
3.1 排序
MySQL中提供了order by关键字,可以进行实现对数据的升序或者降序,比如工作中HR需要对入职员工的在职时间做排序统计,那么可以对员工的入职时间字段进行升序或者降序处理。desc关键字是对数据进行降序排列,asc是对数据进行升序排列(默认排序)
-
排序SQL书写格式
select 字段 from 表名 如果有查询条件就写没有就省略 order by hiredate;
-
查询员工姓名和工资,按照员工的工资降序排列
select ename,sal from emp order by sal desc;
-
查询2号部门的员工信息,按照工资进行升序排列
select * from emp where deptno=2 order by sal asc;
假设当前需要对多个字段进行排序,此时MySQL也是支持的,需要按照员工的部门进行升序排列,然后如果员工部门一致时,按照员工的工资进行降序排列,查询所有员工信息。
select * from emp order by deptno asc,sal desc;
3.2 分页查询
在开发中,当数据结果较多时,往往不会进行一次性完全展示,原因如下:
-
所有数据都在一页中进行展示,用户浏览的兴趣会越来越低,用户体验感不好
-
所有数据都在一页中进行展示,当用户请求该页数据资源时,用户所消耗的流量较多,并不是后面所有的数据都需要进行访问查看,通过分页,可以减少用户流量的使用,只对当先访问的数据页面做流量消耗
MySQL中提供了limit关键字进行分页查询
-
格式:
select * from 表名 limit 查询跳过的数据条数,每页显示的数据条数;
-
跳过数据条数的公式推算
页数 跳过的数据条数 规定每页显示20条数据
1 (1-1)*20
2 (2-1)*20
3 (3-1)*20
4 (4-1)*20
n (n-1)*每页显示数据条数
-
查询员工表中第一页数据,每页显示8条数据,并且按照员工工资进行升序排列
select *from emp order by sal limit 0,8;
-
查询员工表中第二页数据,每页显示8条数据,并且按照员工工资进行升序排列
select *from emp order by sal limit 8,8;
-
查询员工表中工资最高的员工信息
select * from emp order by sal desc limit 0,1;
-
查询工资在2000~5000之间的第二页员工信息(每页显示2条)
select *from emp where sal between 2000 and 5000 limit 2,2;
四、数值计算和聚合函数
http://www.hechaku.com/sqlfunction/SQL_BIT_COUNT__hanshu.html
4.1 数值计算
在sql语句中,我们可以在出现数据的位置进行计算,包括且不限于+ - * /等运算符。
-
查询员工表中每位员工的年薪和姓名
select ename,sal*12 as 年薪 from emp;
as关键字可以用于给数据表,和字段起别名,也可以省略不写
4.2 聚合函数
sql语句允许对查询出的多行数据中的某些列的值进行统计
可以统计这些数据的:求和\计数\平均值\最大值\最小值
-
查询3号部门的工资总和
select sum(sal) from emp where deptno=3;
当使用聚合函数进行查询数据时,不建议添加其他的字段,如果添加了其他字段,最终查询的数据是不符合需求的。分组查询这里可以用聚合函数和其他字段搭配。
-
统计员工工资大于1000元的员工数量
select count(*) from emp where sal>1000;
-
统计1号部门的平均工资
select avg(sal) from emp where deptno=1;
-
统计2号部门的最高工资
select max(sal) from emp where deptno=2;
-
统计3号部门的最低工资
select min(sal) from emp where deptno=3;
五、分组查询
定义:
分组查询是以当前表中某一列字段进行分组,然后根据业务需求进行按组查询。
应用场景:
如果查询出多行的聚合结果时,因为聚合函数查询出来的数据只能显示出一行,那么就可以使用分组查询,让当前的查询数据的结果可以在多行进行显示。
指定部门编号一致的这列数值为一组,然后将一组的数据工资总和计算出来,当前有三个部门编号,总共可以分为三组,然后通过聚合函数求出每个部门的工资总和即可。
5.1 group by
-
按照部门编号进行分组,查询每个部门的工资总和
select deptno,sum(sal) from emp group by deptno;
-
查询每个部门工资高于2000的员工个数
select deptno,count(*) from emp where sal>2000 group by deptno;
-
查询每个工种的在职人数
select job,count(*) from emp group by job;
-
查询每个部门的最高工资
select deptno,max(sal) from emp group by deptno;
-
查询每个部门的平均工资
select deptno,avg(sal) from emp group by deptno;
-
查询1号部门和2号部门的最高工资
select deptno,max(sal) from emp where deptno=1 or deptno=2 group by deptno;
select deptno,max(sal) from emp where deptno in(1,2) group by deptno;
5.2 having
group by是可以对表中的字段进行分组,而having子句可以对分组后的结果进行二次的筛选。如果没有进行分组(没有使用group by) ,那么就不能使用having。如果没有having子句,也是可以有group by分组查询的。
-
where后面不能带有聚合函数的条件
-
having后面专门可以附带聚合函数的条件
案例:
-
查询工资在1000以上的部门和人数,部门的员工数量需要在2人以上。
select deptno,count(*) from emp where sal>1000 group by deptno having count(*)>2;
-
查询每种工作人数为1的数据
select job,count(*) from emp group by job having count(*)=1;
-
查询每个部门平均工资,只查询平均工资大于2000的
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
select deptno,avg(sal) a from emp group by deptno having a>2000;
5.3 练习
最后练习案例分析:
查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于5400,
最后按照工资总和降序排列,只查询结果中的第一条数据
sql技术点:聚合函数、分组查询、having子句、排序、分页
第一步骤:查询每个部门的工资总和
SELECT deptno,sum(sal) FROM emp GROUP BY deptno;
第二步骤:查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于5400
SELECT deptno,sum(sal) s FROM emp WHERE mgr IS NOT NULL GROUP BY deptno HAVING s>5400;
第三步骤:查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于5400,最后按照工资总和降序排列
SELECT deptno,sum(sal) s FROM emp WHERE mgr IS NOT NULL GROUP BY deptno HAVING s>5400 ORDER BY s DESC;
第四步骤:查询每个部门的工资总和,只查询有上级领导的员工并且要求工资总和大于5400,最后按照工资总和降序排列,只查询结果中的第一条数据
SELECT deptno,sum(sal) s FROM emp WHERE mgr IS NOT NULL GROUP BY deptno HAVING s>5400 ORDER BY s DESC LIMIT 0,1;
sql查询关键字的书写顺序:
-
SELECT
-
FROM
-
WHERE
-
GROUP BY
-
HAVING
-
ORDER BY
-
LIMIT
六、子查询
定义:子查询也可以称之为是嵌套查询,也就是一条SQL语句中,包含了另一条SQL,目的就是使用SQL语句进行查询时,需要依赖(借助)于另一条SQL语句查询的结果进行查询。
应用:使用子查询适用于较为复杂的查询需求,子查询不光可以进行单表查询,多表之间的查询也可以进行使用子查询。
-
查询工资高于1号部门平均工资的员工信息(一张数据表)
-- 查询1号部门的平均工资
select avg(sal) from emp where deptno=1;
-- 查询工资高于1号部门平均工资的员工信息
select * from emp where sal>(select avg(sal) from emp where deptno=1);
-
查询工资高于3号部门最低工资的员工信息(一张表)
-- 查询3号部门最低工资 1250
SELECT MIN(sal) FROM emp WHERE deptno=3;
-- 查询工资高于3号部门最低工资的员工信息
SELECT *FROM emp WHERE sal>(SELECT MIN(sal) FROM emp WHERE deptno=3);
-
查询和孙悟空相同工种的其他员工信息(一张表)
-- 查询孙悟空员工的工种
SELECT job FROM emp WHERE ename='孙悟空';
-- 查询和孙悟空相同工种的其他员工信息
SELECT * FROM EMP WHERE job=(SELECT job FROM emp WHERE ename='孙悟空') AND ename!='孙悟空';
-
查询白骨精部门的信息(多表查询)
原理:通过员工表中查询的数据,去部门表中进行使用查询
-- 查询白骨精这位员工的部门编号
SELECT deptno FROM emp WHERE ename='白骨精';
-- 根据白骨精的部门编号在部门表查询部门信息
SELECT * FROM dept WHERE deptno=(SELECT deptno FROM emp WHERE ename='白骨精');
-
查询有员工的部门信息(多表查询)
-- 查询员工表中所有的部门
SELECT deptno FROM emp;
-- 去除重复的查询信息distinct
SELECT DISTINCT deptno FROM emp;
-- 查询上面结果得出部门的所有部门信息
SELECT * FROM dept WHERE deptno in(SELECT DISTINCT deptno FROM emp);