mysql笔记
五,数据处理函数
数据处理函数/单行处理函数
Lower 转换小写
upper 转换大写
substr 取子串(substr(被截取的字符串,起始下标,截取的长度))
length 取长度
trim 去空格
str_to_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位
round 四舍五入
rand() 生成随机数
Ifnull 可以将null转换成一个具体值
1,lower
查询员工将员工的名字转换为小写
select lower(ename) from emp;
2,upper
select *from emp where job=upper('salesman');
3,substr
select * from emp where substr(ename, 1, 1)=upper('m');
4,length
查询员工姓名长度为5的员工
select length(ename),ename from emp where length(ename)=5;
5,trim
会去除首位空格,不会去除中间的空格
岗位为salesman 的所有员工
select *from emp where job=trim(upper('salesman '));
5,str_to_date
将字符串转换为日期
查询1981-02-20入职的员工
select *from emp where hiredate='1981-02-20';(系统默认格式)
将字符串改为日期格式
select *from emp where hiredate=str_to_date('1981-02-20','%Y-%m-%d');
select *from emp where hiredate=str_to_date('20-02-1981','%d-%m-%Y');
6,date_format
select empno,ename,date_format(hiredate,'%Y-%m-%d %H:%i:%s') as hiredate from emp;
7,now() 查询当前时期
select date_format(now(),'%Y-%m-%d %H:%i:%s');
日期格式的说明
%Y:代表4位的年份
%y:代表2位的年份
%m:代表月, 格式为(01……12)
%c:代表月, 格式为(1……12)
%H:代表小时,格式为(00……23)
%h: 代表小时,格式为(01……12)
%i: 代表分钟, 格式为(00……59)
%r:代表 时间,格式为12 小时(hh:mm:ss [AP]M)
%T:代表 时间,格式为24 小时(hh:mm:ss)
%S:代表 秒,格式为(00……59)
%s:代表 秒,格式为(00……59)
8,format
查询员工的薪水加入千分位
select empno,ename,format(sal,0) from emp;
保留小数点的两位
select empno,ename,format(sal,2) from emp;
9,round(四舍五入)
select round(123.45);
10,rand()(生成随机数)
select rand();
生成一到一百的随机数
select round(rand()*100+1);
11,case … when … then …..else …end
如果job为MANAGERG薪水上涨10%,如果job为SALESMAN工资上涨50%
select empno, ename, job, sal, case job when 'MANAGER' then sal*1.1 when 'SALESMAN'
then sal*1.5 end as newsal from emp;
12,ifnull
select ifnull(comm,0) from emp;
如果comm为null 就替换为 0
在SQL语句当中若有NULL值参与数学运算,计算结果一定是NULL
为了防止计算结果出现NULL,建议先使用ifnull空值处理函数预先处理。
以下SQL是计算年薪的:
select empno,ename,sal,(sal+ifnull(comm,0))*12 as yearsal from emp;
六,分组函数/聚合函数/多行处理函数
count 取得记录数
sum 求和
avg 取平均
max 取最大的数
min 取最小的数
注意:分组函数自动忽略空值,不需要手动的加where条件排除空值。
select count(*) from emp where xxx; 符合条件的所有记录总数。
select count(comm) from emp; comm这个字段中不为空的元素总数。
注意:分组函数不能直接使用在where关键字后面。
mysql> select ename,sal from emp where sal > avg(sal);
1,count
取得所有员工数
select count(*) from emp;
取得津贴不为null的员工
select count(comm) from emp;
2,sum
sum可以取得某一列的和,null会被忽略
取得薪水的合计
select sum(sal) from emp;
取得津贴的合计
select sum(comm) from emp;
取得薪水的合计
select sum(ifnull(comm,0)+sal) from emp;
3,avg
取得某一列的平均值
select avg(sal) from emp;
4,max
取得某一列的最大值
select max(sal) from emp;
取得最晚入职的员工
select ename,max(str_to_date(hiredate,'%Y-%m-%d')) from emp;
5,min 取得某一列的最小值
select min(sal) from emp;
取得最早入职的员工
select ename,min(str_to_date(hiredate,'%Y-%m-%d')) from emp;
6,组合聚合函数
select count(*),sum(sal),avg(sal),max(sal),min(sal) from emp;
六,分组查询
取得每个工作岗位的工资合计,要求显示岗位名称和工资合计;
select job,sum(sal) from emp group by job;
select job,deptno,sum(sal) from emp group by job;
七,having
如果想对分组数据再进行过滤需要使用having子句
取得每个岗位的平均工资大于2000的
select job,avg(sal) from emp group by job having avg(sal)>2000;
select 语句总结
select 字段
from 表名
where …….
group by ……..
having ……. (就是为了过滤分组后的数据而存在的—不可以单独的出现)
order by ……..
以上语句的执行顺序
1.首先执行where语句过滤原始数据
2.执行group by进行分组
3.执行having对分组数据进行操作
4.执行select选出数据
5.执行order by排序
原则:能在where中过滤的数据,尽量在where中过滤,
效率较高。having的过滤是专门对分组之后的数据进行过滤的。