•单行函数语法
–语法:
函数名[(参数1,参数2,…)]
–其中的参数可以是以下之一:
•变量
•列名
•表达式
•单行函数特征
–单行函数对单行操作
–每行返回一个结果
–有可能返回值与原参数数据类型不一致
–单行函数可以写在SELECT、WHERE、ORDER
BY子句中
–有些函数没有参数,有些函数包括一个或多个参数
–函数可以嵌套
•常用函数分类
–数学函数
–字符串函数
–日期和时间函数
–流程控制函数
–其他函数
数学函数(线上)
•CEIL(x)、CEILING(x):(天花板)向上到整
select CEIL(-3.5),ceil(3.5)
from dual;
select CEIL(-3.5),ceil(3.5)
•FLOOR(x):(地板)向下取整
•ROUND(x,y):四舍五入到第y位
select round(-1.23),round(-1.523),round(1.23),round(1.51)
select round(-1.234,2),round(-1.235,2),round(1.234,2)
round函数如果只输入一个参数,四舍五入保留整数部分
round(x,y)如果是两个参数,表示四舍五入精确到小数点后第y位
两种方式都可以,mysql这个函数可以省略from,oracle不可省略
•TRUNCATE(x,y):截取到小数点后y位
select TRUNCATE(-1.234,2),TRUNCATE(-1.235,2)
## 练习1 ##
-- •1.写一个查询,分别计算100.456四舍五入到小数点后第2位,第1位,整数位的值。
SELECT ROUND(100.456,2)
,ROUND(100.456,1)
,ROUND(100.456)
-- •2.写一个查询,分别计算100.456从小数点后第2位,第1位,整数位截断的值。
SELECT TRUNCATE(100.456,2),
TRUNCATE(100.456,1),
TRUNCATE(100.456,0);
注意:ROUND(x,y)如果保留整数时,y可以省略,TRUNCATE(x,y)保留整数时,y不可省略,写成(x,0)的形式
•SIGN(x):返回参数的符号;
x>0 返回1
x=0 返回0
x<0 返回-1
select sign(-2.3333),sign(222222),sign(0);分别返回-1,1,0
-- 生成≥a且≤b的随机数
-- x=a
-- y=(b-a)+1
select floor(x+rand()*y);
1
-- 例如生成≥2且≤10的随机数
select rand()*9;
select floor(2+rand()*9);
字符串函数(线上)
•CHAR_LENGTH(str):返回字符个数
无论何种编码方式,1个汉字=1个字符
•LENGTH(str):返回字节个数
英文1=1 中文1=2,3,4
常用中文字符用utf-8编码占用3个字节(大约2万多字),但超大字符集中的更大多数汉字要占4个字节(在unicode编码体系中,U+20000开始有5万多汉字)。
GBK、GB2312收编的汉字占2个字节,严格地用iso8859-1无法表示汉字,只能转为问号。
•CONCAT简单字符串拼接
select CONCAT("我是","彭一峰")
•CONCAT_WS(x,s1,s2,…sn):用x做分隔符,把s1到sn拼接起来
select CONCAT_WS(";","彭一峰","王振生","张兴业","寒") ---彭一峰;王振生;张兴业;寒
• INSERT(str,pos,len,newstr) 替换字符串的函数:str从第pos位置开始的len个字符,用newstr代替
select INSERT("我是王秋菊",3,3,"王振生")
-- 我是王振生
select INSERT("我是王秋菊",3,1,"王振生")
-- 我是王振生秋菊
•left从左侧截取字符串到n个长度
select LEFT("我是王秋菊",2) --我是
select LEFT("ent",2) --en
•right从右侧截取字符串到n个长度
select right("我是王秋菊",2)
select right("ent",2)
• LPAD(str,len,padstr)
-- 把字符串str扩充到len长度,不足的部分用padstr代替
select LPAD(str,len,padstr)
select LPAD("mysql",10,"*") *****mysql
select lpad(ename,20,"*") from emp;
• TRIM去掉前后空格
select TRIM(" fgagag gggggg ")
• lTRIM去掉左侧空格
select lTRIM(" fgagag gggggg ")
• rTRIM去掉右侧空格
select rTRIM(" fgagag gggggg ")
• 比较字符串是否相等 相等返回0
select STRCMP("ab","ab")
•如果str1>str2,返回1
select STRCMP("abc","ab")
•如果str1
select STRCMP("ab","abc")
•SUBSTR(s,n,len) 截取到s中从第n个字符开始的len长度的字符串,如果省略了len,就截取到末尾
select SUBSTR("abcdefagagag",3,3)
select SUBSTR("abcdefagagag",3)
## 练习2 ##
1.显示所有员工姓名的前三个字符
select substring(ename,1,3)
from emp;
select left(ename,3) from emp;
2.显示姓名正好为5个字符的员工的姓名,工资,部门号
select ename, sal, deptno
from emp
where char_length(ename)=5;
日期和时间函数
以下为重点
TIMEDIFF
•TIMEDIFF(expr1, expr2):返回两个日期相减相差的时间数;
注意事项:时间可以写成18:32:00 形式也可写成183200形式
如select TIMEDIFF('183200', '60000') from dual;
select TIMEDIFF("18:32:59","06:00:00")
返回结果为:12:32:00
DATEDIFF
•DATEDIFF(expr1, expr2):返回两个日期相减相差的天数;
注意:
实际与时间无太大关系,如果写上时间,要注意写法
以下几种都为合法
select empno,ename,hiredate,DATEDIFF(hiredate,"2019-07-09") from emp;
select DATEDIFF('2018-09-14 11:30:20', '2018-09-01') from dual;
select DATEDIFF('2018-09-14', '2018-09-01') from dual;
select DATEDIFF('2018-09-14','20180901') from dual;
select DATEDIFF('2018-09-14 11:30:20', '20180901') from dual;
DATE_ADD*重点
•DATE_ADD(date,INTERVAL expr unit):日期加上一个时间间隔值;
注意:其中的INTERVAL 为关键字,固定写法
expr是一个表达式,对应后面的类型
unit是时间间隔的单位(间隔类型),如下:
注意:YEAR_MONTH中的year和month值用点分隔
select empno,ename,HIREDATE,DATE_ADD(hiredate,INTERVAL 1.2 YEAR_MONTH) from emp;
如:
假设员工入职半年后转正,请查询出员工转正日期:
select DATE_ADD(hiredate,INTERVAL 6 month) from emp;
入职一天后的日期
select DATE_ADD(hiredate,INTERVAL 1 day) from emp;
DATE_SUB(略)
•DATE_SUB(date,INTERVAL expr unit):日期减去一个时间间隔值;
与加类似,不同处是做减运算
•日期加减第二种方式
不使用函数,也可以写表达式进行日期的加减:
date + INTERVAL expr unit
date - INTERVAL expr unit
假设员工入职半年后转正,请查询出员工转正日期:
select hiredate+INTERVAL 6 month from emp;
入职一天后的日期
select hiredate,hiredate+interval 1 DAY 一天后
from emp
前一天
select hiredate-INTERVAL 1 day from emp;
•DATE_FORMAT(date,format):格式化日期;
–date 参数是合法的日期
–format 规定日期/时间的输出格式
•TIME_FORMATE(time,formate):格式化时间;
-- H24小时制 h12小时制
-- Y 4位年 y 2位年
-- M是英文月份 m是数字月份
-- %d来定义日时,不足两位的补0 %e来定义日时,不足两位的不补0
-- H补0 k不补0
-- h补0 l不补0
24小时制:
select DATE_FORMAT(now(),'%Y-%m-%d %H:%i:%S') FROM DUAL;
12小时制:
select DATE_FORMAT(now(),'%Y-%m-%d %h:%i:%S') FROM DUAL;
select DATE_FORMAT(now(),"%Y-%M-%D %H:%I:%S") 当前时间
-- 2019-July-9th 11:11:50
select DATE_FORMAT(now(),"%Y-%m-%d %H:%I:%S") 当前时间
-- 2019-07-09 11:11:38
select DATE_FORMAT(now(),"%Y-%m-%d %H:%i:%s") 当前时间
-- 2019-07-09 11:37:04
简单日期时间函数,按课件上的例子试一下
•CURDATE()和CURRENT_DATE():获取当前日期函数;
•NOW():返回服务器的当前日期和时间;
•CURTIME():返回当前时间,只包含时分秒;
•UTC_DATE():返回世界标准时间日期函数;
•UTC_TIME():返回世界标准时间函数;
•DATE(date)、TIME(date)、YEAR(date):选取日期时间的各个部分:
select DATE(now()),TIME(now()),
YEAR(now()),MONTH(now()),DAY(now()),HOUR(now()),MINUTE(now()),SECOND(now())
from dual
•DAYOFWEEK(date) 、DAYOFMONTH(date) 、DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天
select DAYOFWEEK(now()) 星期日为1 。。。周五返回6
•DAYNAME、MONTHNAME:返回日期的星期和月份名称;
•EXTRACT(unit FROM date):从日期中抽取出某个单独的部分或组合;
SELECT now(),extract(YEAR FROM now()); -- 年
SELECT now(),extract(QUARTER FROM now()); -- 季度
SELECT now(),extract(MONTH FROM now()); -- 月
SELECT now(),extract(WEEK FROM now()); -- 周
SELECT now(),extract(DAY FROM now()); -- 日
SELECT now(),extract(HOUR FROM now()); -- 小时
SELECT now(),extract(MINUTE FROM now()); -- 分钟
SELECT now(),extract(SECOND FROM now()); -- 秒
SELECT now(),extract(YEAR_MONTH FROM now()); -- 年月
SELECT now(),extract(HOUR_MINUTE FROM now()); -- 时分
•个性化显示时间日期
–dayofweek(date)
–dayofmonth(date)
–dayofyear(date)
流程控制函数
## casewhen ##
•CASE
value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
•CASE
WHEN [condition] THEN result
[WHEN [condition] THEN result ...]
[ELSE result]
END
例子:
1)SELECT ename,CASE deptno WHEN 10 THEN '10号部门'
when 20 then '20号部门'
ELSE '其他部门'
END as 部门
from emp;
2)select case when sal<1500 then 'low' else 'high' end
from emp;
3)select empno,sal,
case when sal<1500 then 'low'
when sal BETWEEN 1500 and 2000 then 'middle'
ELSE 'high'
end as 工资水平
from emp
select ename,sal,
case
when sal<1000 then '工资低需要涨一下'
when sal BETWEEN 1000 and 1500 then '工资中等,讨论一下'
when sal>1500 then '工资高,不用涨'
END as 是否需要涨工资
from emp;
ifnull
详见第4章null部分
ifnull(expr1,expr2) 如果expr1为null,则返回expr2的值,否则返回expr1的值
if
•IF(expr1,expr2,expr3)
–如果expr1 是TRUE 则 IF()的返回值为expr2;否则返回值则为 expr3。
–IF()的返回值为数字值或字符串值,具体情况视其所在语境而定。
•SELECT IF(1>2,2,3);
•SELECT IF(1<2,'yes ','no');
select empno,comm,if(comm is null,0,comm) as 奖金
from emp;
-- 判断该员工是不是大boss
-- 其实是判断员工有没有上级领导 mgr?null
-- mgr=null 我是大boss,否则我不是
select ename,mgr,if(mgr is null,'我是大boss','我不是')
from emp;
练习题答案
作业:
## 练习3,4,课后作业##
练习3
•1.写一个查询,用首字母大写,其它字母小写显示雇员的 ename,显示名字的长度,并给每列一个适当的标签,条件是满足所有雇员名字的开始字母是J、A 或 M 的雇员,并对查询结果按雇员的ename升序排序。(提示:使用length、substr)
select concat(upper(left(ename,1)),lower(right(ename,length(ename)-1))) 名称,length(ename) 长度
from emp
where upper(left(ename,1)) in ('J','A','M')
order by ename
•2.查询员工姓名中中包含大写或小写字母A的员工姓名。
select ename
from emp
where locate('A',upper(ename))>0
•3.显示所有员工的姓名,用a替换所有"A"
select replace(ename,'A','a')
from emp
•4.查询部门编号为10或20,入职日期在81年5月1日之后,并且姓名中包含字母A的员工姓名,员工姓名长度
select ename,length(ename)
from emp
where deptno in(10,20)
and hiredate>'1981-05-01'
and locate('A',upper(ename))>0
•5.查询每个职工的编号,姓名,工资
–要求将查询到的数据按照一定的格式合并成一个字符串.
–前10位:编号,不足部分用*填充,左对齐
–中间10位:姓名,不足部分用*填充,左对齐
–后10位:工资,不足部分用*填充,右对齐
select concat(rpad(empno,10,'*'),rpad(ename,10,'*'),lpad(sal,10,'*'))
from emp
练习4
1.查询服务器当前时间
SELECT now()
FROM DUAL
2.查询部门10,20的员工截止到2000年1月1日,工作了多少周,入职的月份。
select ceil(DATEDIFF('2000-1-1',hiredate)/7),
monthname(hiredate)
from emp
3.如果员工试用期6个月,查询职位不是MANAGER的员工姓名,入职日期,转正日期,入职日期是第多少月,第多少周
select ename,
hiredate,
date_format(date_add(hiredate,interval 6 month),'%y-%m-%d')转正日期,
month(hiredate),
WEEKOFYEAR(hiredate)
from emp
where job <> 'MANAGER'
3.如果员工试用期6个月,查询职位不是MANAGER的员工姓名,入职日期,转正日期,入职日期是第多少月,第多少周,星期几
week一周的开始默认是从周日开始算的,可以通过第二个参数来指定,比如周一为第一天,则第二个参数为1
SELECT ename 员工姓名 ,hiredate 入职日期, hiredate+INTERVAL 6 MONTH 转正日期,
month(hiredate) 月,week(hiredate,1) 周,dayofweek(hiredate) 星期
FROM emp
WHERE job != 'MANAGER';
WEEKOFYEAR一周的开始是从周一开始算的
SELECT ename 员工姓名 ,hiredate 入职日期, hiredate+INTERVAL 6 MONTH 转正日期,
month(hiredate) 月,WEEKOFYEAR(hiredate) 周,dayofweek(hiredate) 星期
FROM emp
WHERE job != 'MANAGER';
课后作业
1.计算2000年1月1日到现在有多少周(四舍五入)。
select round(datediff(curdate(),'2000-01-01')/7)
from dual;
2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。
select *
from emp
where ename like '__A%'
select *
from emp
where substring(ename,3,1) = 'A'
3.使用trim函数将字符串‘hello’、‘ Hello ’、‘bllb’、‘ hello ’
分别处理得到下列字符串ello、Hello、ll、hello。
-- SELECT RIGHT('hello',4),TRIM(' Hello '),SUBSTRING('bllb',2,2),RTRIM('hello ')
-- FROM DUAL
select trim('听' from '听说你要请我吃锅包肉')
from dual
select trim('h' from 'hello'),trim(' Hello '),trim('b' from 'bllb'),trim(' hello ')
from dual
4.查询员工的姓名及其经理编号,要求对于没有经理的显示“No Manager”字符串。
select ename,ifnull(mgr,'no manager')
from emp
5.将员工的参加工作日期按如下格式显示:月份/年份。
select date_format(hiredate,'%m/%Y')
from emp
6.在员工表中查询出员工的工资,并计算应交税款:如果工资小于1000,税率为0,如果工资大于等于1000并小于2000,
税率为10%,如果工资大于等于2000并小于3000,税率为15%,如果工资大于等于3000,税率为20%。
select sal,case when sal<1000 then 0*sal
when sal<2000 then 0.1*sal
when sal<3000 then 0.15*sal
else 0.2*sal end
from emp
SELECT sal,
CASE
WHEN sal<1000 THEN sal*0
WHEN 1000<=sal<2000 then sal*0.1
WHEN 2000<=sal<3000 THEN sal*0.15
WHEN sal>=3000 THEN sal*0.2
END
FROM emp
7.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度,用 $ 左填充,列标签 SALARY。
select ename,lpad(sal,15,'$') SALARY
from emp
14人点赞
作者:wqjcarnation
链接:https://www.jianshu.com/p/f2eadb54585f
来源:简书
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。