函数定义 1.类似Java定义方法 2.可以带参数 3.必须有返回值(一行一列) ,重点:java方法可以没有返回值,数据库函数必须要有返回值 select max(score) from sc; 4.函数可以嵌套到sql语句中 注:存储过程则不可以 5.函数可嵌套调用
一、常用函数
1.日期函数
1) NOW():获取当前日期和时间 2) DAY():获取日期中的天 3) MONTH():获取日期中的月 4) YEAR():获取日期中的年 5) 日期转换函数DATE_FORMAT()/STR_TO_DATE() DATE_FORMAT():将日期转换成字符串 STR_TO_DATE():将字符串转换成日期 查询各学生的年龄,只按年份来算 按照出生日期来算,当前月日 < 出生年月的月日则,年龄减一 查询本月过生日的学生 查询下月过生日的学生 date_add(NOW(), interval 1 MONTH)
代码案例:
1.日期函数
-- 1) NOW():获取当前日期和时间
select now() from dual;
-- 2) DAY():获取日期中的天
select Day(now()) from dual;
-- 3) MONTH():获取日期中的月
select Month(now()) from dual;
-- 4) YEAR():获取日期中的年
select year(now()) from dual;
-- 5) 日期转换函数DATE_FORMAT()/STR_TO_DATE()
-- DATE_FORMAT():将日期转换成字符串
-- select date_format(now(),'%Y-%m-%d %H:%i:%s') from dual;
select date_format(now(),'%Y年%m月%d日') from dual;
-- STR_TO_DATE():将字符串转换成日期
select str_to_date('2003-4-16','%Y-%m-%d') from dual;
select * from t_student;
-- 查询各学生的年龄,只按年份来算
select *, year(now())-year(sage) from t_student;
-- 按照出生日期来算,当前月日<出生年月的月日则,年龄减一
select *,year(now())-year(sage)+if(month(now())-month(sage)>=0,0,-1) from t_student;
-- 查询本月过生日的学生
select * from t_student where month(now())=month(sage);
-- 查询下月过生日的学生
select * from t_student where month(now())+1=month(sage);
2.字符串函数
1) UPPER()/LOWER():大小写互转 2) REPLACE():搜索并替换字符串中的子字符串 3) SUBSTRING():从具有特定长度的位置开始的最一个子字符串 4) TRIM():去除前后空格 5) LENGTH():获取字符串长度 6) CONCAT():合并字符串函数,返回结果为连接参数产生的字符串,参数可以是一个或多个
代码案例:
2.字符串函数
-- 1) UPPER()/LOWER():大小写互转
select upper('i love my family'),lower('I Love my family') from dual;
-- 2) REPLACE():搜索并替换字符串中的子字符串
select uuid(),replace(uuid(),'_','') from dual;
-- 3) SUBSTRING():从具有特定长度的位置开始的最一个子字符串
select substring('i love my family',2) from dual;
-- 4) TRIM():去除前后空格
-- 5) LENGTH():获取字符串长度
select ' i love my family ',trim('i love my family '),length(' i love my family '),length(trim('i love my family '));
-- 6) CONCAT():合并字符串函数,返回结果为连接参数产生的字符串,参数可以是一个或多个
select concat(99.99,'%') from dual;
select concat('¥',99.99) from dual;
select concat('¥',99,'.00') from dual;
select * from t_student where sname like concat('%','赵','%');
3.数字函数
1)FLOOR:向下取整 2)CEIL: 向上取整 3)ROUND:四舍五入 select FLOOR(-3.14), CEIL(-3.14);
代码案例:
3.数字函数
-- 1)FLOOR:向下取整
-- 2)CEIL: 向上取整
-- 3)ROUND:四舍五入
select floor(3.14),floor(-3.14),ceil(3.14),ceil(-3.14),round(3.14),round(-3.14);
二、条件判断函数
1)IF(expr,v1,v2) 如果expr是TRUE则返回v1,否则返回v2 2)IFNULL(v1,v2) 如果v1不为NULL,则返回v1,否则返回v2 3)CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END 如果expr等于某个vn,则返回对应位置THEN后面的结果,如果与所有值都不想等,则返回ELSE后面的rn
代码案例:
- 二、条件判断函数
-- 1)IF(expr,v1,v2)
-- 如果expr是TRUE则返回v1,否则返回v2
select *,if(score<60,'不及格','及格') from t_score;
-- 2)IFNULL(v1,v2)
-- 如果v1不为NULL,则返回v1,否则返回v2
select s.sid,s.sname,ifnull(sc.score,0)
from t_student s left join t_score sc on s.sid=sc.sid;
-- 3)CASE expr WHEN v1 THEN r1 [WHEN v2 THEN v2] [ELSE rn] END
-- 如果expr等于某个vn,则返回对应位置THEN后面的结果,如果与所有值都不想等,则返回ELSE后面的rn
#适用场景:行列转换
--法一
select sid,
sum(case when cid='01' then score else 0 end)'语文',
sum(case when cid='02' then score else 0 end)'数学',
sum(case when cid='03' then score else 0 end)'英语'
from t_score group by sid;
--法二(重复的编号未合并)
select
sid,
(case cid
when '01' then '语文'
when '02' then '数学'
when '03' then '英语'
else '其他' end
)cname,score from t_score;
三、聚合函数(SUM/COUNT/AVG/MAX/MIN)
(SUM/COUNT/AVG/MAX/MIN) .... group by ... having 1) SUM():求和。常与GROUP BY一起使用,也可单独使用 2) AVG():求平均值。常与GROUP BY一起使用,也可单独使用 3) MAX():求最大值。常与GROUP BY一起使用,也可单独使用 4) MIN():求最小值。常与GROUP BY一起使用,也可单独使用 5) COUNT():统计记录的条数。常与GROUP BY一起使用,也可单独使用
四、合并(union)
1) UNION:将所有的查询结果合并到一起,然后去除掉相同的记录 2) UNION ALL:将所有的查询结果合并到一起,不会去除掉相同的记录 前提条件:结果集列数个数相同,列的类型还要相同或是兼容 使用场景:在项目统计报表模块,用来合并数据
代码案例:
四、合并(union)
-- 1) UNION:将所有的查询结果合并到一起,然后去除掉相同的记录
select 'zs',90,now() from dual union
select 'ls',60,'2004-12-12' from dual union
select 'ls',60,'2004-12-12' from dual
-- 2) UNION ALL:将所有的查询结果合并到一起,不会去除掉相同的记录
select 'zs',90,now() from dual union all
select 'ls',60,'2004-12-12' from dual union all
select 'ls',60,'2004-12-12' from dual
-- 前提条件:结果集列数个数相同,列的类型还要相同或是兼容
-- 使用场景:在项目统计报表模块,用来合并数据
--