4. 常见函数
一、字符函数
-
概念
- 类似于Java的方法,将一组逻辑语句封装在方法中,对外暴露方法名
-
优点
- 隐藏了实现细节
- 提高代码的重用性
-
调用
- select 函数名(实参列表) 【 from 表】;
-
特点
- 叫什么(函数名)
- 干什么(函数功能)
-
分类
- 单行函数(如 concat、length、ifnull)
- 分组函数(功能:做统计使用,又称为统计函数、聚合函数、组函数)
-
concat拼接字符串【utf8中汉字占三个字符】
- select concat (last_name,’-‘,first_name) as 姓名 from student;
-
upper、lower大小写
-
select upper(‘tom’);
-
select lower(‘tom’);
-
实例:将姓变大写,名变小写,然后拼接
select concat(upper(last_name),’-’,lower(first_name)) as 姓名 from student;
-
-
substr、sbustring【索引从1开始】
-
截取从指定索引处后方所有字符
SELECT SUBSTR(‘iPhone13ProMax’,7) AS output (13ProMax)
-
截取从指定索引处指定字符长度的字符
SELECT SUBSTR(‘iPhone13ProMax’,7,2) AS output (13)
-
姓名中首字符大写,其他字符小写,然后用_拼接,显示出来
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),’-’,LOWER(SUBSTR(last_name,2))) AS output from student;
-
-
instr 返回字符串第一次出现的索引
- SELECT INSTR(‘iPhone13ProMax’,‘iPhone’) AS output 结果:1
-
trim【除去字段两边内容默认去除空格】
- SELECT TRIM(‘ 何浩祥 ’) AS out_put结果:何浩祥
- SELECT TRIM (‘aa’ from ‘aaaaa何aaa浩祥aaaa’) AS out_put 结果:何aaa浩祥
-
LPAD用指定的字符实现左填充指定长度
- SELECT LPAD (‘何浩祥’,10,’6’) AS out_put; 结果:6666666何浩祥
-
RPAD用指定的字符实现右填充指定长度
- SELECT RPAD (‘何浩祥’,10,’!’) AS out_put; 结果:何浩祥!!!!!!!
-
replace 替换
- SELECT REPLACE(‘何浩祥牛逼何浩祥牛逼’,’牛逼’,’厉害’) AS HHXNB;
二、数字函数
-
round四舍五入
- SELECT ROUND(1.56) 2
- SELECT ROUND(1.527,2) 1.53
-
ceil 向上取整,返回>=该参数的最小整数
- SELECT CEIL(1.001) 2
-
floor 向下取整,返回<=该参数的最大整数
- SELECT FLOOR(-9.99) -10
-
truncate 【截断】
- SELECT TRUNCATE(1.6999,1) 1
-
mod 取余
- mod(a,b): a-a/b*b
- SELECT MOD(10,3); 1
三、日期函数
-
SELECT NOW(); 返回系统当前日期
-
SELECT CURDATE(); 返回当前系统日期,不包含时间
-
SELECT CURTIME(); 返回当前时间不包含日期
-
指定部分,年、月、日、时分秒
- SELECT YEAR(‘2000-11-13’) AS 年;
- SELECT MONTH(NOW()) AS 月;
- SELECT MONTHNAME(NOW()) AS 月; 月份的英文
-
STR_TO_DATE将字符通过指定格式转换日期
-
查询入学时间为2008-12-11的学生信息
SELECT * FROM student WHERE inschool=‘2008-12-11’;
SELECT * FROM student WHERE inschool=STR_TO_DATE(‘12-11 2008’,’%c-%d %Y);
-
-
DATE_FORMAT 将日期转换为字符
- SELECT DATE_FORMAT(NOW(),’%y年%m月%d日’) AS out_put;
-
DATEDIFF,两个时间相差的天数
SELECT DATEDIFF(NOW(),’2000-11-13’);
四、 其他函数
-
SELECT VERSION (); 版本号
-
SELECT DATABASE(); 当前数据库
-
SELECT USER(); 当前用户
五、流程控制函数
-
if 函数:if else的效果
-
SELECT IF(10>5,’大’,’小’);
-
查询学生信息,是否获得奖状
SELECT name,awards,IF(awards IS NULL ‘没奖状 呵呵’,’有奖状哈哈 ’) AS 备注 FROM student;
-
-
case函数的使用
-
switch case
-
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
when 常量2 then 要显示的值2或语句2
。。。
else 要显示的值n或语句n
end
-
案例查询学生的英语成绩
一班的显示成绩为成绩的1.3倍
二班的显示成绩为成绩的1.2倍
三班的显示成绩为成绩的1.1倍
其他班显示原始成绩
SELECT Englis AS 原始成绩,class
case class
when ‘一班’ then English*1.3
when ‘二班’ then English*1.2
when ‘三班’ then English*1.1
else English
end as 附加成绩
from student
-
-
多重if
-
case
when 条件1 then 要显示的值1或者语句1
when 条件2 then 要显示的值2或者语句2
。。。
else 要显示的语句n
end
-
案例如果英语成绩大于90,显示级别为A
案例如果英语成绩大于80,显示级别为B
案例如果英语成绩大于60,显示级别为C
其他情况显示D
SELECT English
case
when English>90 then ‘A’
when English>80 then ‘B’
when English>60 then ‘C’
ELSE ‘D’
END AS 成绩级别
FROM student
-
-
六、分组函数
-
功能:用作统计使用,又称为聚合函数或统计函数或者组函数
-
分类:sum求和、avg 平均值、max最大值、min最小值、count计算非空个数
-
简单使用:
-
SELECT SUM(English) FROM student;
-
SELECT AVG(English) FROM student;
-
SELECT MAX(English) FROM student;
-
SELECT MIN(English) FROM student;
-
SELECT COUNT(English) FROM student;
-
SELECT SUM(English) 和,AVG(English) 平均值,MAX(English) 最大值,MIN(English) 最小值,COUNT(English) 个数
FROM student
-
SELECT SUM(English) 和,ROUND(AVG(English) ,2) 平均值,MAX(English) 最大值,MIN(English) 最小值,COUNT(English) 个数
FROM student
-
-
特点
-
sum、avg一般用于处理数值型,max、min可以处理任何数据类型
-
以上分组函数都忽略null值
-
可以和distinct搭配去实现去重
SELECT SUM(DISTINCT English),SUM(English) FROM student;
-
conut函数的详细介绍
统计所有行数
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;
-
和分组函数一同查询的字段要求是group by后的字段
-