DQL语言---常见函数

一、概述

功能：类似于java中的方法
好处：提高重用性和隐藏实现细节
调用：select 函数名（实参列表）

二、单行函数

1.字符函数

注意：索引从1开始

concat：连接

SELECT CONCAT(str1,str2,....);
SELECT CONCAT('a','b','c');#abc

length:获取字节长度

SELECT LENGTH('我爱我的祖国');

upper：变大写

SELECT UPPER('abc');

lower：变小写

SELECT LOWER('ABC');

substr、substring：截取子串

SELECT SUBSTR(str,pos);#从pos索引位置开始，截取后面所有字符
SELECT SUBSTR('我爱我的祖国',5);#祖国

SELECT SUBSTR(str,pos,len);#从pos索引位置开始，截取字符长度为len的子串
SELECT SUBSTR('我爱我的祖国',1,2);#我爱

instr：获取子串第一次出现的索引

SELECT INSTR(str,substr);
SELECT INSTR('我爱我的祖国','祖国');#5

trim：去前后空格

SELECT LENGTH(TRIM('    我爱我的祖国    '));#18（一个汉字占3个字节）

还可以去前后指定字符
SELECT TRIM('a' FROM 'aaa我爱我的祖国aaa');#我爱我的祖国

lpad：用指定字符实现左填充指定长度

SELECT LPAD(str,len,padstr);
SELECT LPAD('我爱我的祖国',10,'*');#****我爱我的祖国

rpad:用指定字符实现右填充指定长度（与左填充同理）

replace：替换

SELECT REPLACE(str,from_str,to_str);
SELECT REPLACE('我有一个梦想','一个','两个');#我有两个梦想

2.数学函数

round：四舍五入

SELECT ROUND(X);#对x四舍五入取整
SELECT ROUND(1.56);#2

SELECT ROUND(X,D); #对x四舍五入，保留D位小数
SELECT ROUND(1.567,2);#1.57

ceil：向上取整

SELECT CEIL(X); #返回>=X的最小整数
SELECT CEIL(1.5);#2                                                                                              

floor：向下取整

SELECT FLOOR(X); #返回<=X的最大整数
SELECT FLOOR(1.5);#1

truncate：截断

SELECT TRUNCATE(X,D); #返回小数点保留D位后的X
SELECT TRUNCATE(1.69,1);#1.6

mod：取余

SELECT MOD(n,m);#n%m

rand：获取随机数，返回0-1之间的小数

SELECT RAND();#返回不重复的随机数
SELECT RAND(N);#返回重复的随机数

3.日期函数

now：返回当前日期+时间

SELECT NOW();

curdate：返回当前系统日期，不包含时间

SELECT CURDATE();

curtime：返回当前时间，不包含日期

SELECT CURTIME();

monthname：以英文形式返回月

SELECT MONTHNAME(NOW());

返回年、月、日、时、分、秒

SELECT YEAR(NOW());
SELECT YEAR('1998-1-1');

SELECT MONTH(NOW());
SELECT MONTHNAME('1998-1-1');
SELECT MONTHNAME(NOW());

SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());

str_to_date：将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('9-13-1999','%c-%d-%Y');

date_format：将日期转换成字符

SELECT DATE_FORMAT('2018/6/6','%Y年%m月%d日');
SELECT DATE_FORMAT(NOW(),'%Y年%m月%d日');

datediff：返回两个日期相差的天数

SELECT DATEDIFF('2018-1-5','2018-1-3');

4.其他函数

version：当前数据库服务器的版本

SELECT VERSION();

database：当前打开的数据库

SELECT DATABASE();

user：当前用户

SELECT DATABASE();
SELECT USER();

passsword：返回字符型参数的密码形式

SELECT PASSWORD('字符');

md5(‘字符’)：返回该字符的md5加密形式

SELECT MD5('字符');

5. 流程控制函数

IF函数

if(条件表达式，表达式1，表达式2);#如果条件表达式成立，返回表达式1，否则返回表达式2

SELECT IF(10>5,'大','小');#大

case结构

情况一：

case 变量或表达式或字段
when 常量1 then 值1
when 常量2 then 值2
...
else 值n
end

SELECT 
CASE IF(10>5,'大','小')
WHEN '大' THEN '有道理'
WHEN '小' THEN '离谱'
ELSE '不知道'
END AS "我觉得10>5";

情况二：

case 
when 条件1 then 值1
when 条件2 then 值2
...
else 值n
end

SELECT 
CASE 
WHEN 10>5 THEN "有道理"
WHEN 10<5 THEN "离谱"
ELSE "我不知道"
END AS "我觉得10>5";

三、分组函数

1.分类

max：最大值
min：最小值
sum：和
avg：平均值
count：计算个数

2.特点

①语法

SELECT MAX(字段) FROM 表名;#min、sum、avg、count用法相同

②支持的类型
sum和avg一般用于处理数值型
max、min、count可以处理任何数据类型

③以上分组函数都忽略null

④都可以搭配distinct使用，实现去重的统计

SELECT SUM(DISTINCT 字段)
FROM 表;

⑤count函数
count(字段)：统计该字段非空值的个数
count(*):统计结果集的行数

效率上：
MyISAM存储引擎，count( * )最高
InnoDB存储引擎，count( * )和count(1)效率>count字段

⑥ 和分组函数一同查询的字段，要求是group by后出现的字段