MySQL实战：4.数据的操作

1. 数据排序

1.1 单一字段排序

按照薪水由小到大排序（系统默认由小到大）

select ename,sal from emp order by sal;

取得 job 为 MANAGER 的员工，按照薪水由小到大排序（系统默
认由小到大）

 select ename,job,sal from emp where job = 'MANAGER' order by sal;

注意：如果存在 where 子句，那么 order by 必须放到where 询句后面。

按照薪水由大到小排序（降序兲键字desc）

select ename, sal from emp order by sal desc;

升序关键字（asc）
降序关键字（desc）

1.2多个字段排序

按照 job 和薪水倒序排序

select ename, job, sal from emp order by job desc, sal desc;

注意：如果采用多个字段排序，如果根据第一个字段排序重复了，会根据第二个字段排序；

1.3使用字段位置排序（不推荐使用）

按照薪水升序排序
select * from emp order by 6;

不建议采用此方法，采用数字含义不明确，可读性不强，程序不健壮

2. 处理函数

2.1 单行处理函数

函数名称	含义
Lower	转换小写
upper	转换大写
substr	取子串（substr（被截取的字符串，起始下标，截取的长度））
length	取长度
trim	去空格
str_to_date	将字符串转换成日期
date_format	格式化日期
format	设置千分位
round	四舍五入
rand()	生成随机数
ifnull	可以将null转换成一个具体值

2.11 lower(字段名)函数

用法：lower(要转换字段名称)

1. 查询员工姓名，将员工姓名全部转换成小写

select lower(ename) as lowName from emp;

2.12 upper(字段名)函数

用法：upper(要转换字段名称)

1）查询员工姓名，将员工姓名全部转换为大写

 select upper(ename) as upperName from emp;

2.13 substr(字段名,起始下标,截取长度)函数

查询并显示所员工姓名的第二个字母
select substr(ename, 2, 1) from emp;

查询员工姓名中第二个字母为A的所有员工
select ename from emp where substr(ename, 2, 1) = 'A';

2.1.4 length(字段名)函数

取得员工姓名长度
select ename, length(ename) as lenName from emp;

2.1.4 ifnull(字段名,替换值)函数

查询员工姓名及补助，如果补助为Null设置为0；
select ename, ifnull(comm,0) from emp;

查询员工薪水与补助的和
select ename, (sal + ifnull(comm,0)) as sumSal from emp;

错误写法
select sal+comm from emp;
因为null与任何数字相加都为null

注意：在数据库中，有Null参不数学运算的结果一定为Null；为了防止计算结果出现Null，建议先使用ifnull函数预先处理。

2.1.5 case…when…then…else…end

匹配工作岗位，当为MANAGER时，薪水上调10%，当为SALESMAN时，薪水上调50%，其它岗位薪水不变

case job
	when ‘MANAGER’then sal * 1.1
	when ‘SALESMAN’then sal * 1.5
	else sal
end
注意：使用在DQL语句中

匹配工作岗位，当为MANAGER时，薪水上调10%，当为SALESMAN时，薪水上调50%，其它岗位薪水不变
select ename, job,
(case job
	when 'MANAGER' then sal *1.1
	when 'SALESMAN' then sal * 1.5
	else sal
end) as newSal
from 
	emp;

2.1.6 trim(‘字符串’)函数

作用：trim函数去除首尾空格，不会去除中间空格

取得工作岗位为manager的所有员工
select * from emp where job = trim(" manager ");
注意：MySQL默认去除字段后面的空格，原因：MySQL语法松散

2.1.7 round(数字,保留小数位数)函数

用法：round(要四舍五入的数字,四舍五入到哪一位)，默认保留整数位。

select round(125.18)

保留1位小数
select round(125.18,1);

保留2位小数
select round(125.18,2);

个数位四舍五入
select round(125.18,-1);

2.1.8 rand()函数

生成一个 0≤ v ≤ 1.0的随机数;

select rand();

生成一个0-100的随机数
select round(rand()*100);

2.1.9 str_to_date函数

作用：将‘日期字符串’转换为‘日期类型’数据
执行结果：DATE类型

用法：str_to_date(‘日期字符串’,‘日期格式’)
1） 日期字符串：日期格式的字符串
2） 日期格式：见下表

MySQL的日期格式

序号	格式符	功能		格式符	功能
1	%Y	代表四位的年份		%y	代表两位的年份
2	%m	代表月，格式（01…12）		%c	代表月，格式（1…12）
3	%d	代表日
4	%H	代表24小时制		%h	代表12小时制
5	%i	代表分钟，格式（00…59）
6	%S或%s	代表秒，格式（00…59）

查询出1981-12-03入职的员工
select ename,hiredate from emp where hiredate = '1981-12-03';

输入的日期字符串格式与MySQL默认日期格式相同，MySQL默认日期格式：%y-%m-%d

查询出02-20-1981入职的员工
错误执行：select ename,hiredate from emp where hiredate = ‟02-20-1981‟;
错误原因：‘02-20-1981’是一个字符串varchar类型，与MySQL中DATE默认类型不匹配

正确写法
select ename,hiredate from emp where hiredate = str_to_date('02-20-1981','%m-%d-%Y');

总结：
1、日期是数据库本身的特色，也是数据库本身机制中的一个重要内容，所以仍需掌握；
2、每一个数据库处理日期时采用的机制都不一样，都有自己的一套处理机制，所以在实际开发中将日期字段定义为DATE类型的情况很少；
3、如果使用日期类型，java程序将丌能通用。实际开发中，一般会使用“日期字符串”来
表示日期；
select substr(ename, 1, 4) as ename from emp;
select substr(ename, 3, 3) as ename from emp;

结论：str_to_date函数通常使用在插入操作中；字段DATA类型，不接收varchar类型，需要先通过该函数将varchar变成data再插入数据。

2.1.10 date_format函数

作用：将‘日期类型’转换为特定格式的‘日期字符串’类型
用法： date_format(日期类型数据,‘日期格式‟)

查询员工的入职日期，以‘10-12-1980’的格式显示到窗口中；
select ename,date_format(hiredate, '%m-%d-%Y') as hiredate from emp;

结论：data_format函数主要用在数据库查询操作中。实际工作中，客户需要日期以特定格式展示的时候，需要使用该函数

2.2多行处理函数

函数	含义
sum	求和
avg	取平均
max	取最大值
min	取最小值
count	取得记录值

什么是单行处理函数，什么是多行处理函数？
单行函数都是一行输入对应一行输出
多行处理函数都是多行输入对应一行输出

2.2.1 sum函数

作用：求某一列的和，null会自动被忽略；
用法： sum(字段名称)

取得薪水的合计
select sum(sal) as sumSal from emp;

取得总共薪水（工资+补助）合计
错误写法：select sum(sal + comm) from emp;
原因：comm字段有 null 值，在多列进行运算时，只要有 null 参与的数学运算结果都为 null

正确结果
select sum(sal + ifnull(comm, 0)) from emp;
sum函数会自动忽略掉null值，正确的做法是将comm的null值转换为0

2.2.2 avg函数

作用：求某一列的平均值，null会被自动忽略
用法：avg(字段名称)

取得平均薪水
select avg(sal) as avgSal from emp;

2.2.3 max函数

作用：取得某一列的最大值
用法：max(字段名称)

取得最高薪水
select max(sal) as maxSal from emp;

取得最晚入职的员工
select max(hiredate) from emp; 
说明：日期类型也可以进行大小比较

2.2.4 min函数

作用：取得某一列最小值
用法：min(字段名称)

取得最低薪水
select min(sal) from emp;

2.2.5 count函数

作用：取得某字段值不为null的记录总数
用法：count(字段名称) 或 count(*)

注意： 1. count(*)表示取得当前查诟表所有记录
2. count(字段名称)，不会统计为 null 的记录

取得所有员工数
select count(*) from emp;

取得补助不为空的所有员工数
select count(comm) from emp;
select count(*) from emp where comm is not null;

2.3 distinct函数

作用：将查询结果中某一字段的的重复记录去除掉
用法：distinct 字段名或 distinct 字段名1, 字段名2 … …
distinct 字段名A：去除与字段名A相同的记录
distinct 字段名A,字段名B：去除与字段名A和字段名B同时相同的记录
注意：distinct只能出现在所有字段最前面，后面如果有多个字段及为多字段联合去重

查询该公司有哪些工作岗位
select distinct job from emp;

去除部门编号deptno和工作岗位job重复的记录
select distinct deptno,job from emp;

2.4 group by函数

作用：通过哪个戒哪些字段迚行分组
用法：group by 字段名称

找出每个工作岗位的最高薪水
思路分析：按照工作岗位分组，然后对每一组求最大值。
select job, max(sal) from emp group by job;

计算每个工作岗位的最高薪水，并且按照由低到高进行排序
select job, max(sal) as maxSal from emp group by job order by maxSal;

2.5 having

作用：如果想对分组的数据再进行过滤，需要使用having子句；

找出每个工作岗位的平均薪水，要求显示平均薪水大于2000的；
思路分析：先对工作岗位 job 分组，然后对每一组薪水求平均值，最后再对每一组平均薪水的值进行大于2000的条件过滤
错误写法：select job,avg(sal) from emp where avg(avg) > 2000 group by job;
原因：where关键字后面不能直接使用分组函数，这与SQL语句的执行顺序有关系，它会先执行from emp，然后再进行 where 条件过滤，where条件过滤结束之后再执行 group by 分组，之后才会显示出查询结果。

正确结果
select job,avg(sal) from emp group by job having avg(sal) > 2000;

where 不 having 区别：

1） where 和 having 都是为了完成数据的过滤，它们后面都是添加条件；
2） where 是在 group by之前完成过滤；
3） having 是在 group by 之后完成过滤；

3 select语句总结(重要)

一个完成的SQL询句如下：
select 
	xxxx
from
	xxxx
where 
	xxxx
group by
	xxxx
having
	xxxx
order by
	xxxx
以上关键字的顺序不能变，严格遵守

以上语句的执行顺序：
1） from 将硬盘上的表文件加载到内存
2） where:将符合条件的数据行摘取出来。生成一张新的临时表
3） group by :根据列中的数据种类，将当前临时表划分成若干个新的临时表
4） having : 可以过滤掉group by生成的不符合条件的临时表
5） select : 对当前临时表进行整列读取
6） order by : 对select生成的临时表，迚行重新排序，生成新的临时表
7)limit : 对最终生成的临时表的数据行，迚行截叏。