数据库函数的详细总结

目录

1、 字符串函数

2、 数学函数

3、 日期函数【重要】

4、 日期字符串转换函数【重要】

5、 流程函数【重要！！】

---

1、 字符串函数

函数	解释
CONCAT (string2 [,... ])	连接字串
LENGTH (string )	string长度
REPLACE (str ,search_str ,replace_str )	在str中用replace_str替换search_str
SUBSTRING (str , position [,length ])	从str的position开始,取length个字符
LTRIM (string2 ) RTRIM (string2 ) trim	去除前端空格或后端空格

-- =========== 字符串函数 ============
-- concat(str1,....) 连接字符串 【重要】
select concat('a','1','b','2') from dual;
select concat('a','1','b','2'),sid,sname from stu;
select concat(sid,sname),sid,sname from stu;
select concat('',sname),sid,sname from stu;
select concat('我叫',sname,',今年',age,'明年',age+1,'岁') from stu;
​
-- left(string2,length) 从 string2 中的左边起取 length 个字符
select left('java',2)
select left(sname,1) from stu; -- 取出姓氏
​
-- length 获得长度 , utf8中,一个中文三个字节
select length(sname),sname from stu;
select length('abc');
​
-- 替换
-- REPLACE (str ,search_str ,replace_str ) 在 str 中用 replace_str 替换 search_str
select replace('java','av','AV');
select replace(sname,'三','叁') from stu;
​
-- SUBSTRING (str , position [,length ] 截取
select substring('java',2); -- 从第2位,取到末尾
select substring('java',2,2); -- 从第2位,取2个
-- 取出stu表中姓名,姓,名
select sname 姓名 ,left(sname,1) 姓,substring(sname,2) 名 from stu;
-- 插入时截取部分数据插入
insert into stu (sname) value('java');
insert into stu (sname) value(substring('java',2,2));
-- 更新时取部分数据更新
update stu set sname = left('史密斯',1) where sid = 1011
​
-- ltrim rtrim trim 虑空
select ltrim(' java '),rtrim(' java '),trim(' java ');
select length(' java '),length(ltrim(' java ')),length(rtrim(' java ')),length(trim(' java '));
-- 插入时使用虑空
insert into pet(id,nick,weight)
values(64,trim(' jerry '),1)

2、 数学函数

函数	解释
CEILING (number2 )	向上取整
FLOOR (number2 )	向下取整
FORMAT (number,decimal_places )	保留小数位数,格式化format(x,y),对x保留y位小数,会四舍五入
RAND([seed])	RAND([seed]),seed是种子,可不写.写了随机数固定
ROUND(x,[d])	将x四舍五入,d是保留的位数,可不写
TRUNCATE(X,D)	截取

-- =========== 数学函数 ============
select abs(-1);
select ceiling(1.1),floor(1.1);
-- 数字格式化 fromat(x,d) x是原始数据,d是保留的小数位数(会四舍五入)
select format(100,1);
select format(100.15,1);
select format(100.14,1);
​
select avg(score) from stu;
select format(avg(score),1) from stu;
​
-- 数字格式化truncate(x,d) x是原始数据,d是保留的小数位数(不会四舍五入)
select truncate(100.9,0);
select truncate(100.19,1);
select truncate(100.11,1);
​
-- 四舍五入 round(x[,d]) x是必填,原始数据
select round(100.91); -- 默认是将小数四舍五入后变整数
select round(100.91,1); -- 参数2是保留小数位数
select round(100.99,1); 
select round(100.999,2); 
​
-- 随机数 rand() 0-1之间的小数
select rand();
select ceiling(rand() * 30);
insert into tb_1 (age) values (ceiling(rand() * 30))

3、 日期函数【重要】

函数	解释
SYSDATE()	当前时间
CURRENT_DATE ( )	当前日期
CURRENT_TIME ( )	当前时间
CURRENT_TIMESTAMP ( )	当前时间戳
DATE (datetime )	返回datetime的日期部分
DATEDIFF (date1 ,date2 )	两个日期差
NOW ( )	当前时间
YEAR|MONTH|DATE (datetime )	年月日

-- =========== 日期函数 ============
-- 获得当前日期时间
select now(); -- now() 当前日期和时间
select sysdate(); -- sysdate()当前系统日期和时间
select current_date(); -- 获得当前日期
select current_time();-- 获得时分秒
select current_timestamp();-- 获得当前时间戳
update tb_order set order_time = now() where oid = 1;
​
-- 单独获得年,月,日
select year(now());
select year('1970-01-01');
select year(order_time) from tb_order
select month('1970-01-01');
select day('1970-01-01');
-- 查询2023年的订单
select * from tb_order where year(order_time) = 2000
-- 当月生日人数
select count(*) from t10 where month(birthday) = month(now())

4、 日期字符串转换函数【重要】

函数	解释	说明
date_format(日期,模板)(8版本已经移除)	日期 --> 字符串	格式化
str_to_date(字符串,模板)	字符串 --> 日期	解析

常见的模板:

• %Y-%m-%d 2000-01-02

• %Y年%m月%d日 2000年02月02日

%Y：代表4位的年份

%y：代表2为的年份

%m：代表月, 格式为(01……12)

%c：代表月, 格式为(1……12)

%d：代表月份中的天数,格式为(00……31)

%e：代表月份中的天数, 格式为(0……31)

%H：代表小时,格式为(00……23)

%k：代表 小时,格式为(0……23)

%h： 代表小时,格式为(01……12)

%I： 代表小时,格式为(01……12)

%l ：代表小时,格式为(1……12)

%i： 代表分钟, 格式为(00……59)

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)

%T：代表 时间,格式为24 小时(hh:mm:ss)

%S：代表 秒,格式为(00……59)

%s：代表 秒,格式为(00……59)

-- =========== 日期/字符串转换函数 ============
/*
 日期 --> 字符串  date_format(date,'%Y-%m-%d')
 字符串 --> 日期 str_to_date('datestr','%Y-%m-%d') 
 ---------------------
 日期模板
 %Y年 %m月 %d日
 %H时 %i分钟 %S秒
*/
select date_format(now(),'%Y年%m月%d日')
select str_to_date('2022年11月18日','%Y年%m月%d日')
​
insert into t10 (id,birthday) value (1,str_to_date('2020-01-01','%Y-%m-%d'))

5、 流程函数【重要！！】

函数	解释
IF(expr1,expr2,expr3)	如果expr1为真，则返回expr2，否则返回expr3,相对于是三目运算
IFNULL(expr1,expr2)	如果 expr1不是NULL,则返回expr1，否则返回expr2； 一般用来替换NULL值，因为NULL值是不能参加运算的
CASE WHEN [expr1] THEN [result1]… ELSE [default] END	如果expr是真, 返回result1,否则返回default

-- inst(string,substring) 查找substring在string中的位置,找不到返回0
select if(instr('java','big') > 0,'存在','不存在');
-- 查询学生姓名,已经是否名字含三
select 
    sname,
    if(instr(sname,'三') > 0,'是','否') 
        as 是否含三
from stu;
-- ================== 流程函数 ================
select if(1>0,'真','false') from dual;
​
-- 获得所有人的平均分
select sum(score) / count(sid) from stu;
select avg(if(score is null,0,score)) from stu;
-- 查询学生学号,成绩,以及是否及格(>=60)
select sid , score , if(score >= 60,'及格','不及格') 是否及格 from stu;
-- 查询学生学号,成绩,假如没有成绩显示缺考
select sid , if(score is null,'缺考',score) from stu;
​
-- 计算年龄大于50的人数
select count(sid) from stu where age > 50
select count(if(age < 50,null,sid)) from stu
​
-- 查询学生学号,成绩,假如没有成绩显示缺考
select sid , ifnull(score,'缺考') from stu;
-- 范围判断
-- CASE WHEN [expr1] THEN [result1]… ELSE [default] END 如果expr是真, 返回result1,否则返回default
-- 查询学生id,姓名,成绩,及等级(60以下不及格,60-70,及格,71-80,中等,81-90良好,91-100优秀)
select sid,sname,score,case
when score < 60 then '不及格'
when score <= 70 then '及格'
when score <= 80 then '中等'
when score <= 90 then '良好'
else '优秀'
end as 等级
from stu

        

        本人为新手博主，如果有哪些错误的地方，请多多指正，希望大家能动动发财的小手给我点点关注吧！