-----------数值函数
---绝对值
select abs(-123) from dual;
--求模
select mod (12,5) from dual;
--取整
--上限值
select ceil(123.45) from dual;
--下限值
select floor (123.45) from dual;
--四舍五入
select round(123.55) from dual;
select round(123.5267,2) from dual;--参数2表示小数点保留几位数
select round(126.5267,-1) from dual;--参数-1表示小数点向左移动1位
字符串函数、替换函数----------------------------------------------------------------------------
--截取,直接舍掉
select trunc (123.52) from dual;
select trunc (126.5267,2) from dual;
select cno, round ( avg(degree) ,2) from score group by cno;
--计算字符串长度
select sname, length (sname) from student;
select * from student where length (sname)>2;
--去空格
select trim (' 付 s十b 亮 ') from dual;
select ltrim (' 付 s十b 亮 ') from dual;
select rtrim (' 付s十b亮 ') from dual;
--替换
select replace(' abc def ', ' ' , '#' )from dual;--把空格换成#
select replace (sname, '王', '李') ,sname from student where sname like '王%';
--更新姓王的学生名称为姓李的
update student set sname = replace (sname, '王', '李') where sname like '王%';
-- 查找字符串
select instr ('bac', 'a') from dual; --数据库中索引和字符串位置从1开始
--截取字符串
select substr('abdjhwkfjf', 2) from dual; --从第2位截取后面所有
select substr('abdjhwkfjf', 2,5) from dual; --从第2个截取到第五位结束
select substr('abdjhwkfjf', -8,5) from dual; --从右边向左数8位向右截取5位长度,长度不能是负数
select sname, substr(sname,1,1) || '同学'from student ;
--成绩为空的替换成0
select t.*, nvl (degree ,0) from score t;
select t.*, nvl (degree ,100,0) from score t;-- 100是不为空的替换成100,为空的默认为0
--替换
select t.* , decode (ssex, '1' , '男' ,'2','女' ,'不知道') from student t;
-- 返回当前用户
select user from dual ;
聚合函数------------------------------------------------------
--1.聚合函数 返回单个值
--记录条数
select count (sno)from student where sclass=95031;
--查平均成绩
select sum(degree)/count (1) from score;
select avg(degree) 平均值 from score;
--合计总成绩
select sum(degree) from score;
--最高成绩
select * from score order by degree desc;
select max (degree)最大值, min (degree)最小值,avg(degree) 平均值 from score;
时间函数-------------------------------------------------------------------------
--字符串数字之间运算
select cast('123' as number ) +123 from dual;
----------------------时间函数
--查询系统时间
select sysdate from dual;
insert into course values ('8-123','设计素描','856',sysdate);
select sysdate +1 from dual;
--增加系统月份
select add_months (sysdate,2) from dual;
--查询系统本月最后一天
select last_day (sysdate) from dual;
转换函数-----------------------------------------------------------------
--22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select t.sbirthday, to_char (t.sbirthday,'yyyy-mm-dd hh24:mi:ss') from student t where sno='108'
select sno,sname,sbirthday from student where to_char (sbirthday,'yyyy')=
(select to_char (t.sbirthday,'yyyy') from student t where sno='108');
select * from student where sbirthday >= to_date('1977-1-1','yyyy-mm-dd');