数据库03—DQL数据查询函数之常见函数

常见函数

一、概述

功能：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：提高重用性和隐藏实现细节
调用：select 函数名(实参列表)【from表】;
特点：
①叫什么（函数名）
②干什么（函数功能）
分类：单行函数、分组函数（做统计使用，又称统计函数、聚合函数、组函数

二、单行函数

1.字符函数

① length：获取参数值的字节个数（utf-8一个汉字代表3个字节，gbk为2个字节）
—char_length ( )：按字符统计长度，不管汉字还是数字或者是字母都算是一个字符
例如： 一个包含5个字符且每个字符占两个字节的字符串而言，LENGTH ()返回长度10，CHAR_LENGTH ()返回长度是5；如果对于单字节的字符，则两者返回结果相同

② concat：拼接字符，如有任何一个参数为NULL ，则返回值为 NULL
—concat.ws()：使用分隔符将两个或多个字符串连接成一个字符串。CONCAT_WS(separator,str1,str2,…)，第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以是一个字符串，也可以是其它参数。如果分隔符为 NULL，则结果为 NULL。但是CONCAT_WS()不会忽略任何空字符串。

+----------------------------------------------+
| CONCAT_WS(',','Last_name',NULL,'First_Name') |
+----------------------------------------------+
| Last_name,First_Name                         |
+----------------------------------------------+

—group_concat( 【distinct】 要连接的字段 【order by 排序字段 asc/desc 】 【separator ‘分隔符’】 )
通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，省略则默认为一个逗号。（用在select后）

③ upper：变大写 select upper('John');
④ lower：变小写 select lower('John');
⑤ substr（substring）：截取子串
索引从1开始 select substr('一段字符串',开始位数，【截取长度】);
substring_index()：根据关键字截取子串，substring_index（被截取字段，关键字，n），从关键字出现第n次截取子串，如果 n > 0，从则左边数起，且返回位置前的子串；如果 n< 0，从则右边数起，且返回位置后的子串
⑥left：从提供的字符串的左侧提取给定数量的字符select left('字符串',提取长度)
right：从提供的字符串的右侧提取给定数量的字符select right('字符串',提取长度)
⑦instr：获取子串第一次出现的索引 select instr('一段字符串','子串');
⑧trim：去掉字符串中前后的空格select trim（' 字符串 '); 去掉字符串前后指定的字符select trim('指定字符' from '指定字符和字符串和指定字符');
⑨Lpad：左填充，用指定字符实现左填充
Rpad：右填充，用指定字符实现右填充

select Lpad('字符串',填充后的长度,'填充字符');

⑩replace：替换

select replace('字符串','被替换字符','用来替换的字符');

mid：用于从文本字段中提取字符。

select mid(要提取字符的字段, start(, 返回字符数（省略，则返回剩余文本） )) 

#start：规定开始位置（起始值是1）。

coalesce：

cast ：CAST (expression as data_type)，CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。
expression：任何有效的SQServer表达式；
as：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型;
data_type：目标系统所提供的数据类型

2.数学函数

①round：四舍五入 select round('数字',保留小数位数);
②ceil：向上取整，返回大于等于该参数的最小整数 select ceil(1.2);
③floor：向下取整，返回小于等于该参数的最大整数
④truncate：截断，小数点后保留几位select truncate(参数,保留位数);
⑤mod：取余 mod(a,b)：a-a/b*b

3.日期/时间函数

函数	返回值
now	返回当前系统日期和时间select now();
curdate	返回当前系统日期，不包括时间select curdate();
curtime	返回当前时间不包含日期select curtime();
str_to_date	将字符通过指定的格式转换成日期
datediff	返回两个日期相差的天数
timestampdiff	timestampdiff(minute,开始时间,结束时间)，返回以minute为时间单位的时间差
monthname	以英文形式返回月
CURRENT_TIMESTAMP	返回当前日期和时间
CURRENT_DATE	返回当前日期
CURRENT_TIME	返回当时时间
week(date,mode)	返回日期为那年第几周；mode是一个可选参数，用于确定周数计算的逻辑。默认为0（周从星期日开始）
date_sub(日期,interval n 单位)	从日期减去指定的时间间隔，n+单位代表减去多少时间间隔，date_sub(日期,interval 5 day)代表减去五天

4.其他函数

①version 当前数据库服务器的版本
②database 当前打开的数据库
③user当前用户
④password(‘字符’)：返回该字符的密码形式
⑤md5(‘字符’):返回该字符的md5加密形式
⑥offset n：跳过n个数据,limit x offset n跳过n个数据取x个数据

5.流程控制函数

①if函数：if else的效果
select if(条件表达式,条件表达式成立返回的值,不成立返回的值);
select if(10<5,'大’,'小');

②case函数
使用1：switch case的效果，比较适合等值判断

case 要判断的字段或表达式
when 常量1 then 要显示的值1或【语句1;】
when 常量2 then 要显示的值2或【语句2;】
…
else 要显示的值n或【语句n;】
end

/*案例1：查询员工的工资，要求：
部门号=30，显示的工资为1.1倍
部门号=40，显示的工资为1.2倍
部门号=50，显示的工资为1.3倍
其他部门，显示的工资为原工资
*/ 
select salary 原始工资,department_id,
case department_id
when 30 then salary*1.1
when 40 then salary*1.2
when 50 then salary*1.3
else salary
end as 新工资
from employees;

使用2：类似于多重if，区间判断，函数可以写判断，并且搜索函数只会返回第一个符合条件的值，其他case被忽略

case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
…
else 要显示的值n或语句n
end

/*案例：查询员工的工资的情况
如果工资>20000,显示A级别
如果工资>15000,显示B级别
如果工资>10000,显示C级别
否则，显示D级别*/
select salary,
case
when salary>20000 then 'A'
when salary>15000 then 'B'
when salary>10000 then 'C'
else 'D'
end as 工资等级
from employees;

③ifnull() ：ifnull(不是Null时返回的值,是null时返回的值)

更多参考：MySQL常见函数

三、MySQL正则表达式

菜鸟MySQL正则表达式

REGEXP(regexp)

四、With as

1.含义

WITH AS短语，也叫做子查询部分（subquery factoring），可以定义一个SQL片断，该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高，也可以在UNION ALL的不同部分，作为提供数据的部分。

2.用法

with name as(select …)

select * from name