MySQL函数概述

MySQL提供了很多功能强大、方便易用的函数，在进行数据库管理以及数据的查询和操作时，帮助我们提高对数据库的管理效率

•单行函数语法

–语法:

函数名[(参数1，参数2,…)]

–其中的参数可以是以下之一：

•变量

•列名

•表达式

•单行函数特征

–单行函数对单行操作

–每行返回一个结果

–有可能返回值与原参数数据类型不一致

–单行函数可以写在SELECT、WHERE、ORDER

BY子句中

–有些函数没有参数，有些函数包括一个或多个参数

–函数可以嵌套

•常用函数分类

–数学函数

–字符串函数

–日期和时间函数

–流程控制函数

–其他函数

数学函数（线上）

字符串函数（线上）

日期和时间函数

以下为重点

TIMEDIFF

•TIMEDIFF(expr1, expr2)：返回两个日期相减相差的时间数；

注意事项：时间可以写成18:32:00 形式也可写成183200形式

如select TIMEDIFF('183200', '60000') from dual;

返回结果为:12:32:00

DATEDIFF

•DATEDIFF(expr1, expr2)：返回两个日期相减相差的天数；

注意：

实际与时间无太大关系，如果写上时间，要注意写法

以下三种都为合法

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是时间间隔的单位(间隔类型)，如下：

如：

假设员工入职半年后转正，请查询出员工转正日期：

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-INTERVAL 1 day from emp;

•DATE_FORMAT(date,format)：格式化日期；

–date 参数是合法的日期

–format 规定日期/时间的输出格式

•TIME_FORMATE(time,formate）：格式化时间；

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;

简单日期时间函数，按课件上的例子试一下

•CURDATE()和CURRENT_DATE()：获取当前日期函数;

•NOW()：返回服务器的当前日期和时间；

•CURTIME(）：返回当前时间，只包含时分秒；

•UTC_DATE(）：返回世界标准时间日期函数；

•UTC_TIME()：返回世界标准时间函数；

•DATE(date)、TIME(date)、YEAR（date）：选取日期时间的各个部分：

•DAYOFWEEK(date) 、DAYOFMONTH(date) 、DAYOFYEAR(date):返回日期在一周、一月、一年中是第几天

•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)

流程控制函数

ifnull

详见第4章null部分

if

•IF(expr1,expr2,expr3)

–如果expr1 是TRUE 则 IF()的返回值为expr2;否则返回值则为 expr3。

–IF()的返回值为数字值或字符串值，具体情况视其所在语境而定。

•SELECT IF(1>2,2,3);

•SELECT IF(1<2,'yes ','no');

练习题答案

练习1

1.写一个查询，分别计算100.456 四舍五入到小数点后第2位，第1位，整数位的值。

select round(100.456,2),round(100.456,1),round(100.456)

from dual

select round(100.456,1)

from dual

select round(100.456)

from dual

2.写一个查询，分别计算100.456 从小数点后第2位，第1位，整数位截断的值。

select truncate(100.456,2),truncate(100.456,1),truncate(100.456,0)

from dual

select truncate(100.456,1)

from dual

select truncate(100.456,0)

from dual

练习2

1.显示所有员工姓名的前三个字符

select substring(ename,1,3)

from emp

2.显示姓名正好为5个字符的员工的姓名，工资，部门号

select ename,sal,deptno

from emp

where length(ename)=5

练习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'

课后作业

1.计算2000年1月1日到现在有多少周（四舍五入）。

select round(datediff(curdate(),'2000-01-01')/7)

from dual;

2.查询员工ENAME的第三个字母是A的员工的信息(使用2个函数)。

select *

from emp

where locate('A',ename) = 3

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

7.创建一个查询显示所有雇员的 ename和 sal。格式化sal为 15 个字符长度，用 $ 左填充，列标签 SALARY。

select ename,lpad(sal,15,'$') SALARY

from emp