MySQL入门知识：常用函数

@TOC

字符函数

#（1）length获取字符个数
#查询国家表，按照国家的本地名称字母个数降序排序
select name,LocalName,length(localname) from country order by length(localname) desc;
#(2)concat 链接字符串，如有任何一个参数为null，则返回值为null
#查询国家表，输出每行记录的格式为：国家名称 was founded in 建国年份。alter
select code,concat(name,'was founded in',IndepYear) from country;
#(3)substr(str,开始索引,[截取的字符长度])截取子串
#注意：开始索引从1开始
select substr('abcd',3);       #结果为cd
select substr('abcd',1,3);     #结果为abc
#（4）instr（str,'字符'）获取字符第一次出现的索引
#查询国家表，显示国家名称中第一次出现字母a的索引
select name,instr(name,'a') from country;
#(5)trim去前后指定的字符，默认是去空格
select trim('   今晚   月色  真美   ')；
select concat('--',trim('   今晚   月色  真美   '),'--');
select trim("--" from '--今晚   月色  真美--');
#（6）lpad/rpad 左填充/右填充 中间的数值是设定总的字符长度，剩余的用第三个参数内容填充
select lpad("今晚月色真美",10,"+"),rpad("今晚月色真美",10,"+");  #左边补充+号补充到10个字符
#（7）upper/lower 变大写/变小写
#查询国家表，将所有国家编码编程小写，国家名称变大写
select code,lower(code),name,(name) from country;

数字函数

#(1)abs 获取绝对值
select abs(-3.14),abs(3.14);
#查询国家表，GNP变化的绝对值，降序排列
select name,gnp,gnpold,gnp-gnpold,abs(gnp-gnpold) GNP变化值 from country order by GNP变化值 desc;
#(2)ceil向上取整， 返回该参数的最小整数
select ceil(-1.10),ceil(0.09),ceil(2.3);
#(3)floor 向下取整，返回该参数的最大整数
select floor(-1.10),floor(0.09),floor(2.3);
#(4)round四舍五入
select round(3.1415926),round(3.1415926,2);
#(5)truncate截取
select truncate(3.1415926,2);
#(6)mod取余
select -10%3,10%3,-10%3,10%-3;       #符号跟被除数相同

日期函数

#（1）now显示当前时间
select now();
#（2）curdate显示当前日期
select curdate();
#（3）datediff 显示两个日期之间的差值
select datediff('2019-1-1','2020-12-31');
#（4）date_format 将日期类型按照指定格式显示
select date_format('2020-12-25 13:45:30','%Y年%m月%d日 %H小时 %i分钟 %s秒') 圣诞节;
#查找sakila数据库中的address表，以“2014年09月25日 22:30:40”的形式显示租赁时间
select address,last_update,date_format(last_update,'%Y年%m月%d日 %H:%I:%s') 上次租赁时间 from sakila.address;
#（5）str_to_date 按照指定格式解析字符串中的日期
select str_to_date('12/25 2020','%m/%d %Y');
#查找sakila数据库里的address表，在“2014年09月25日 22:34:10”以后租赁的记录信息
select * from sakila.address where last_update > str_to_date("2014年09月25日 22:34:10",'%Y年%m月%d日 %H:%I:%s');

流程控制函数

#(1)ifnull 当给定的字段内容为空的时候，显示指定的默认值
select name,ifnull(indepyear,"没有独立时间") from world.country;
#查询国家表，输出每行记录的格式为：
#有年份，显示"Aruba was founded in XXXX"
#没有年份时，显示"Aruba was founded in ----"
select concat(name,"was founder in",ifnull(indepyear,"----")) 国家建立时间 from world.country;
#查询国家表，输出国家名称及建国时间，有年份则显示"建国时间为：XXXX"，没有年份则显示"没有建国时间"
select name,ifnull(concat("建国时间为：",indepyear),"没有建国时间") 国家建立时间 from world.country;
#convert 将指定字符串转换为对应的字符集进行输出
select convert("建国时间"using gbk);
#查询国家表，输出每行记录的格式为：
#有年份时，显示"Aruba 的建国时间为 XXXX"；没有年份，显示"Aruba 的建国时间为 ----"
select concat(convert(name using utf8),"的建国时间为",ifnull(indepyear,"----")) from world.country;
#（2）if(表达式1,表达式2,表达式3)如果表示式1成立，那么显示表达式2，否则表达式3）
select if(10>5,"是的","不是的") "10>5" ;
#查询国家表，独立年份为null则显示"没有建国年份",否则显示“建国年份为XXXX年”
select name,if(IndepYear is null,"没有建国年份",concat("建国时间为：",indepyear,"年")) 建国年份 from world.country;
#（3）case when
#按照数值进行判断
#查询国家表，将所在的大洲翻译为中文
select name,Continent,
case Continent
when 'Asia' then '亚洲'
when 'Africa' then '非洲'
when 'North America' then '北美洲'
else '暂不做翻译'
end 中文名称
from world.country;
#按照条件进行判断
#查询国家表，根据预期寿命的年龄，分阶段显示评价信息；
#大于等于80岁，显示"长寿";大于等于60岁，显示"一般";大于等于40岁，显示"悲观";低于40岁，显示"预期寿命太短了";没有数据的，显示"没有统计数据"。
select name,LifeExpectancy,
case
	when LifeExpectancy >=80 then '长寿'
    when LifeExpectancy >=60 then '一般'
    when LifeExpectancy >=40 then '悲观'
	when LifeExpectancy < 40 then '长寿'
    else '预期寿命太短了'
end '预期寿命评价'
from world.country;