MySQL中的函数

字符函数

-- length():获取参数值的字节个数
SELECT num,NAME,LENGTH(num) FROM student;


-- char_length():获取参数值的字符个数
SELECT num,NAME,CHAR_LENGTH(NAME) FROM student;


-- concat():拼接字符串  as 别名
SELECT num,CONCAT(NAME,':',gender,'*',birthday)AS information FROM student;


-- upper()/lower():将字符串变成大写/小写
SELECT num,UPPER(NAME),LOWER(NAME) FROM student;

-- substring(str,pos,length):截取字符串，从位置1开始
SELECT num,SUBSTRING(NAME,2,3) FROM student;


-- instr(str,指定字符):返回子串第一次出现的索引，如果找不到返回0
SELECT num,INSTR(NAME,'三') FROM student;

-- trim(str):去掉字符串前后的空格或子串，trim（指定子串 from 字符串）
SELECT num,TRIM('m' FROM NAME) FROM student;

-- lpad(str,length,填充字符):用指定的字符实现左填充将str填充为指定长度
SELECT num,LPAD(phone,20,'a') FROM student;


-- rpad(str,length,填充字符):用指定的字符实现右填充将str填充为指定长度
SELECT num,RPAD(phone,20,'a') FROM student;


-- replace(str,old,new):替换，替换所有的子串
SELECT num,REPLACE(NAME,'三','a') FROM student;

case when then end语句

-- case when 条件 than 结果1 else 结果2 end————(可以有多个when)

SELECT NAME,
	(CASE WHEN height>=1.70 THEN '高个子'
	WHEN height>=1.60 THEN '正常'
	ELSE '低个子' END)AS height,gender 
	FROM student;

-- ifnull(被检测值，默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回原本的值


SELECT NAME,
IFNULL(height,'暂未录入') AS height,gender
 FROM student

if函数

-- if函数：if（条件，结果1，结果2）
SELECT NAME,
IF(NAME='李宏杰','智叟','聪明')AS idea,gender 
FROM student

数学函数

-- 数学函数

-- round(数值)：四舍五入
SELECT NAME,ROUND(height)AS height FROM student;
-- 指定保留小数位数
SELECT NAME,ROUND(height,1)AS height FROM student;

-- ceil(数值)：向上取整，返回>=该参数的最小整数
SELECT NAME,CEIL(height) FROM student;


-- floor(数值)：向下取整，返回<=该参数的最大整数
SELECT NAME,FLOOR(height) FROM student;



-- truncate(数值，保留小数的位数):截断,小数点后截断到几位,不会四舍五入
SELECT NAME,TRUNCATE(height,1) FROM student;


-- mod(被除数,除数):取余,被除数为正,则为正,被除数为负,则为负
SELECT NAME,MOD(num,2) FROM student;



-- rand():获取随机数,返回0-1之间的小数
SELECT NAME,RAND() FROM student;

日期函数

-- now()

SELECT NAME,NOW(),CURDATE(),CURTIME() FROM student;


-- year()month()day()日期格式化

SELECT NAME,YEAR(rig_time),MONTH(rig_time),DAY(rig_time) FROM student;


-- str_to_date()将字符串格式转化为日期类型

SELECT NAME,STR_TO_DATE('2001-1-30','%Y-%m-%d')AS TIME FROM student;


-- date_format()将日期格式类型转化为指定的字符串

SELECT NAME,DATE_FORMAT(birthday,'%Y-%m-%d') FROM student;



-- datediff()计算两个日期之间相差的天数

SELECT NAME,DATEDIFF(CURDATE(),birthday) FROM student;

SELECT NAME,DATEDIFF(STR_TO_DATE('2004-5-28','%Y-%m-%d'),birthday) FROM student;

分组函数/聚合函数

-- 分组函数/聚合函数,统计函数(都忽略null)
-- sum求和,avg平均值(一般用于处理数值型)
-- max最大值,min最小值,count计数(可以处理任何类型)

SELECT SUM(height),AVG(height) FROM student;


SELECT MAX(height),MIN(height) FROM student;


-- 统计行数   count(*):推荐    count(1)    count(列名):如果列的值为空不统计

SELECT COUNT(*) FROM student;


SELECT COUNT(height) FROM student;


SELECT NAME FROM student WHERE height=(SELECT MIN(height) FROM student)