【MySQL系列笔记】- 第二章 - 条件查询语句

条件查询

条件查询是MySQL的基本操作，在不改变数据本身的情况下对符合条件的数据进行筛选，而不是将所有数据都查出来

基础语法：

select
	字段1, 字段2, 字段3 // select 后跟需要显示的字段
from
	表名
where
	条件; // 即具体范围，如工资区间、年龄、性别等

假设有以下名称为emp的表，其示例为：

1. 条件中的：= 等于；<> 和 != 都是不等于，MySQL可以使用between and判断中间值

   # 介于二者之间（包含两侧的值）
   # 这里是筛选薪资介于3000到6000的员工编号，姓名和薪资
   select 
   	empno, ename, sal 
   from 
   	emp
   where
   	sal between 3000 and 6000;
   

2. null 的用法（需要用is null，不能用等号衡量），

   # 查询那些员工的补助为null
   select empno, enmae, sal from emp where comm is null;
   # 查询那些员工的补助不为null
   select empno, enmae, sal from emp where comm is not null;
   

3. and 并且；or 或者；not 非（用于 is 或 in中）

   # or的示例，选择职位是经历或销售的员工编号、姓名和职位
   select 
   		empno,ename,job
   from
   		emp
   where 
   		job = 'MANAGER' or job = 'SALESMAN';
   

   and 和 or 同时出现的情况下，and 的优先级更高，先执行 and 再执行 or

4. in 和 not in 相当于多个 or （不是区间，后面跟具体的值）

   # 上面的语句等同于
   select empno,ename,job
   from
   		emp
   where 
   		job in('MANAGER', 'SALESMAN')
   

   后面也可以跟数字，作用类似于between and

    select empno,ename,job from emp where sal in(4000, 5000)
   

   not in 则意义相反

5. like 模糊查询，可以用于匹配文本，支持 % 或 _ 匹配，需要加单引号

   % 匹配任意个字符，如db%，查询所有以db开头的字符串

   _ 一个下划线匹配一个字符

   # 查询emp表中名字里含有o的
   select ename from emp where ename like '%o%';
   # 如果包含特殊字符，使用\转义
   select ename from emp where ename like '%\_%';
   

6. 排序，使用 order by

   # 查询所有员工薪资并排序
   select empno,sal
   from
   	emp
   order by
   	sal;
   # 默认升序，降序则在sal后加上desc，升序sal后加上asc（默认）
   

   多个字段排序

   # 查询所有员工薪资排序并按照名字排序
   select empno,sal
   from
   	emp
   order by
   	sal asc, ename asc;
   

   还可以使用像 order by 2，对第二列进行排序（开发时不建议）

7. 使用顺序

   select
   	...
   from
   	...
   where
   	...
   order by
   	...;
   

数据处理函数/单行处理函数

单行处理函数的特点：一个输入对应一个输出，例如四舍五入、大小写转换这类操作

多行处理函数的特点：多个输入对应一个输出，例如求和、求平均这类操作（输入的是一类或几类的数据）

更改的是视图，数据本身没有改变

函数	效果
lower	转换小写
upper	转换大写
substr	取子串，subset(name,start,len)
length	取长度
trim	去空格
str_to_date	字符串转日期
date_format	格式化
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null设置为数字
concat	字符串拼接

1. lower 转小写，upper 用法类似，转为大写

   # select后跟想要展示的数据
   select lower(ename) as ename from emp;
   +--------+
   | ename  |
   +--------+
   | smith  |
   | allen  |
   +--------+
   

2. substr 取子串，subset(name,start,len)

   select substr(ename, 1 ,1) as ename from emp;
   

   如果不用 as 则命名为as前的处理函数

   注意：起始下标从1开始

3. length 取长度

   select length(ename) from emp;
   

4. trim 去空格，查询时用

   select * from emp where ename = trim(' KING');
   

5. round 四舍五入

   # 四舍五入并保留一位小数，负数则是保留小数点前
   select round(sal, 1) as result from emp
   

6. rand 生成0~1之间的随机数

   # 生成100以内的随机数
   select round(rand()*100, 0) from emp;
   

7. ifnull 空处理函数，用法：ifnull(数据, 被当做哪个值)

   在所有数据库中，只要有NULL参与的数学运算，结果都是null

   select ename, (sal + ifnull(comm, 0))*12 from emp;
   

8. case..when..then..when..then..else..end 条件判断语句

   # 当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50%,其它正常。
   select 
   	ename,
   	job, 
   	sal as oldsal,
   	(case job when 'MANAGER' then sal*1.1 
   	when 'SALESMAN' then sal*1.5 
     else sal end) as newsal 
   from 
   	emp;
   +--------+-----------+---------+---------+
   | ename  | job       | oldsal  | newsal  |
   +--------+-----------+---------+---------+
   | SMITH  | CLERK     |  800.00 |  800.00 |
   | ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
   +--------+-----------+---------+---------+
   

分组函数/多行处理函数

函数	效果
count	计数
sum	求和
avg	平均值
max	最大值
min	最小值

必须先分组，再使用函数，否则整张表默认为一组

# 计算最大工资
select max(sal) from emp;
# 计算最低工资
select min(sal) from emp;
# 计算工资和
select sum(sal) from emp;
# 计算工资的平均值
select avg(sal) from emp;
# 计算工资列对象数量（将会排除null）
select count(sal) from emp;

1. 分组函数自动忽略null，无需提前处理

2. count(*) 和 count(具体字段) 的区别

   # 相对于整张表所有列元素不为NULL的元素的总数（即总行数）
   select count(*) from emp;
   # 相对于该字段下所有不为NULL的元素的总数（即所有元素都不含null）
   select count(comm) from emp;
   

3. 【注意】分组函数不能直接使用在 where 子句中

   错误的用法：select ename, sal from emp where sal > avg(sal);

4. 多个分组函数可以同时使用

5. 补充知识点：distinct 去除查询结果的重复记录

   select distinct job from emp;
   

   错误：select ename, distinct job from emp;

   distinct 只能出现在所有字段的最前方，作用为后方所有查询字段去除重复记录

   # 查询工作的种类
   select count(distinct job) from emp;
   

分组查询

实际需求中，可能需要先进行分组，然后对每一组数据进行操作

select // 5
	...
from // 1
	...
where // 2
	...
group by // 3
	...
having // 4
	...
order by // 6
	...

select 在 group by 之后执行

几个示例

1. 示例：找出每个工作岗位的工资和

   select
   	job, sum(sal)
   from 
   	emp
   group by 
   	job;
   

   在一条 select 语句中，如果有 group by 语句的话， select 后面只能跟：参加分组的字段、以及分组函数，其他的一律不能跟。

2. 示例：找出具有每个部门的最高薪资

   select deptno, max(sal) from emp group by deptno;
   

3. 示例：找出每个部门，不同岗位的最高薪资

   select 
   	deptno, job, max(sal) 
   from
   	emp 
   group by
   	deptno,job;
   

4. 使用 having 可以对分组完的数据进一步过滤，但是必须和 group by 连用，因此不能代替 where

    # 找出每个部门的最高薪资，且显示最高薪资大于3000的
    select 
    	job, max(sal) 
    from 
    	emp 
    group by 
    	job
    having
    	max(sal)>3000;
   # 等同于
   select job, max(sal) from emp where max(sal)>3000 group by job;
   
   +-----------+----------+
   | job       | max(sal) |
   +-----------+----------+
   | PRESIDENT |  5000.00 |
   +-----------+----------+
   

5. 示例：找出每个部门平均薪资，要求显示平均工资高于2500的

   select
   	deptno, avg(sal)
   from 
   	emp
   group by 
   	deptno 
   having
   	avg(sal)>2500;
   

6. 示例：求每个岗位的平均薪资，要求显示平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排

   select 
   	job,avg(sal) as avgsal
   from 
   	emp 
   where 
   	job <> 'MANAGER' 
   group by 
   	job 
   having 
   	avg(sal)>1500 
   order by 
   	avgsal,