（十五）MySQL函数

目录

01  字符串函数

举个栗子

02  数值函数

举个栗子

03  日期函数

举个栗子

04  流程函数

举个栗子

不总结=白学

---

我们在数据库中常常会遇到各种字符串函数、数值函数、日期时间函数、流程函数等。

本篇将会对 mysql中的各种字符串函数、数值函数、日期时间函数以及流程函数进行说明，希望通过本文的学习，能够让大家对 mysql中各种函数有一个系统的了解。

---

01  字符串函数

MySQL常用的字符串函数

函数	说明
CONCAT(字符串1, 字符串2, …)	将字符串1，字符串2，……拼接成一个字符串
LOWER(字符串)	将字符串全部小写
UPPER(字符串)	将字符串全部大写
LPAD(字符串1, n, 字符串A)	左填充，用字符串A对字符串1的左边进行填充，达到n个长度
RPAD(字符串1, n, 字符串A)	右填充，用字符串A对字符串1的右边进行填充，达到n个长度
TRIM(字符串)	去掉字符串首尾的空格（中间的不变）
SUBSTRING(字符串, 起始位置, 长度n)	返回返回字符串从起始位置开始的长度为n的字符串

举个栗子

数据库：world（MySQL自带），数据表：city（MySQL自带）。需要回答以下问题：

1. 将name与countrycode以“-”连接起来；
2. 将countrycode全部小写，name全部大写；
3. 将id规范显示为3个数，countrycode右侧以*填充为4个字符；
4. 截取distinct从第2个字符开始，长度为3的字符串；
5. 去掉字符串“  mo jing  ”的首位空格；

use world;
desc city;
select * from city;

-- 1. 将name与countrycode以“-”连接起来；
-- 2. 将countrycode全部小写，name全部大写；
-- 3. 将id规范显示为3个数，countrycode右侧以*填充为4个字符
-- 4. 截取district从第2个字符开始，长度为3的字符串
select 
	concat(name, '-', countrycode) as ncc  -- 将name与countrycode以“-”连接起来；
    , lower(countrycode) as cc_lower  -- countrycode全部小写
    , upper(name) as n_upper  -- name全部大写
    , lpad(id, 3, 0) as id_pad  -- id规范显示为3个数
    , rpad(countrycode, 4, '*') as cc_pad  -- countrycode右侧以*填充为4个字符
    , substr(district, 2, 3) as dt_substr  -- 截取district从第2个字符开始，长度为3的字符串
from city
order by id_pad;

-- 5. 去掉字符串“  mo jing  ”的首位空格 
select trim('  mo jing  ');

 

1~4题结果

 

5题结果

 

02  数值函数

MySQL常用的数值函数

函数	说明
CEIL(x)	向上取整。如3.14向上取整：4
FLOOR(x)	向下取整。如3.16向下取整：3
MOD(x, y)	返回x/y的余数（模）
RAND()	返回0~1之间的随机数
ROUND(x, y)	x四舍五入，保留y位小数

举个栗子

数据库：world（MySQL自带），数据表：country（MySQL自带）。需要回答以下问题：

1. 对LifeExpectancy分别向上取整、向下取整、四舍五入取整数；
2. 取GNP/population的余数；
3. 对每个城市生成一个6位随机数的随机验证码；

select * from country;

select 
	ceil(lifeexpectancy) as le_ceil  -- 向上取整 
    , floor(lifeexpectancy) as le_floor  -- 向下取整 
    , round(lifeexpectancy) as le_round  -- 四舍五入 
    , mod(gnp, population) as gp_mod  -- 取余数 
    , lpad(round(rand()*1000000, 0), 6, 0) as num_rand  -- 6位随机数，注意先四舍五入再左填充，以此保证6位数 
from country
where ceil(lifeexpectancy) is not null;  -- 去除空行

country原表

 

输出结果

 

03  日期函数

MySQL常用的时间日期函数

函数	说明
CURDATE()、CURTIME()、NOW()	返回当前日期、时间、日期和时间
YEAR(date)、MONTH(date)、DAY(date)、HOUR(date)、MINUTE(date)、SECOND(date)	获取指定date的年、月、日、时、分、秒
DATE_ADD(date, INTERVAL 时间间隔SECOND/MINUTE/HOUR/DAY/MONTH/YEAR)	返回date加上一个时间间隔后的日期/时间
DATEDIFF(date1, date2)	返回起始date1和结束date2之间的天数

举个栗子

数据库：sakila（MySQL自带），数据表：customer（MySQL自带）。需要回答以下问题：

1. 查询当前的日期、时间、日期和时间；

2. 查询create_date的年、月、日；

3. 查询create_date间隔5天、5月、5年后的日期；

4. 查询create_date和last_update之间的天数；

-- 1. 查询当前的日期、时间、日期和时间；
select curdate(), curtime(), now();

-- 2. 查询create_date的年、月、日；
-- 3. 查询create_date间隔5天、5月、5年后的日期；
-- 4. 查询create_date和last_update之间的天数；
select 
	customer_id, create_date, last_update  -- 原始数据 
	, year(create_date) as cy  -- 返回年 
    , month(create_date) as cm  -- 返回月 
    , day(create_date) as cd  -- 返回日 
    , date_add(create_date, interval 5 day) as c5d  -- 返回5天后
    , date_add(create_date, interval 5 month) as c5m  -- 返回5月后
    , date_add(create_date, interval 5 year) as c5y  -- 返回5年后（时、分、秒类似）
    , datediff(create_date, last_update) as date_diff  -- 时间间隔
from customer;

第1题结果

 

2~4题结果

 

 

04  流程函数

MySQL常用的流程函数

函数	说明
IF(值, t, f)	如果值为TRUE，则返回t，否则返回f
IFNULL(值1, 值2)	如果值1不为NULL，则返回值1，否则返回值2
CASE WHEN 值1 THEN 结果1 … ELSE 结果 END	如果值1为TRUE，则返回结果1，…，否则返回结果
CASE 值 WHEN 值1 THEN 结果1 … ELSE 结果 END	如果值等于值1，则返回结果1，…，否则返回结果

举个栗子

数据库：world（MySQL自带），数据表：countrylanguage（MySQL自带）。需要回答以下问题：

1. 如果percentage>50，则返回1，否则返回0；

2. 如果countrycode不为null，则返回原值，否则返回0；

3. 如果language为english，则返回“英语”，如果language为german，则返回“德语”, 否则返回“其他语言”（两种方法）；

use world;
show tables;
select * from countrylanguage;

-- 1. 如果percentage>50，则返回1，否则返回0；
-- 2. 如果countrycode不为null，则返回原值，否则返回0；
-- 3. 如果language为english，则返回“英语”，如果language为german，则返回“德语”, 否则返回“其他语言”（两种方法）；
select 
	*  -- 原表
    , if(percentage > 50, 1, 0) as p_50 -- 如果percentage>50，则返回1，否则返回0；
    , ifnull(countrycode, 0) as c_null -- 如果countrycode不为null，则返回原值，否则返回0；
    , (case 
		  when language = 'english' then '英语'
          when language = 'german' then '德语'
		  else '其他语言'
	  end) as l_cw_1  -- 方法一
	, (case language
		  when 'english' then '英语'
          when 'german' then '德语'
          else '其他语言'
	  end) as l_cw_2  -- 方法二
from countrylanguage;

 

注意IFNULL(值1, 值2)中的值1：

select 
	ifnull(123, '说明前面的值为null') as a
	, ifnull('', '说明前面的值为null') as b
    , ifnull(null, '说明前面的值为null') as c;  -- 123、’’都不为null，只有null才是null

 

不总结=白学

---

THE END

---