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-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查询关键字的书写顺序:

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

 

posted @ 2021-09-06 20:18  Coder_Cui  阅读(126)  评论(0编辑  收藏  举报