MYSQL天花板函数和地板函数_2020-08-04常用函数

•单行函数语法

–语法:

函数名[(参数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

来源：简书

著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。