WEB02_Day05(下)-sql运算符、模糊查询、排序和分页、数值计算和聚合函数、分组查询、子查询

2021/9/7 2:08:12

编程Tag： 运算符 select 查询 where sal emp deptno day05

本文主要是介绍WEB02_Day05(下)-sql运算符、模糊查询、排序和分页、数值计算和聚合函数、分组查询、子查询，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

一、sql运算符

1.1 判断null

　　使用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 实现模糊查询的通配符

  1. 通配符%: 代表0或多个任意字符

  2. 通配符_: 代表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)*20
       (2-1)*20
       (3-1)*20
       (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查询关键字的书写顺序：

  1. SELECT

  2. FROM

  3. WHERE

  4. GROUP BY

  5. HAVING

  6. ORDER BY

  7. 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);

 



这篇关于WEB02_Day05(下)-sql运算符、模糊查询、排序和分页、数值计算和聚合函数、分组查询、子查询的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程