oracle中的常用sql语句(10)

常用的sql语句,如建表、建索引、建约束条件;以及增删改查和高级查询操作

select ceil(-1.34) from dual;
round(number[,integer])--四舍五入
select round(3.1415926) from dual;
select round(3.1415926,3) from dual;
trunc(number[,integer]) --截取数字
select trunc(3.1415926) from dual;
select trunc(3.1415926,3) from dual;
dbms_random.value([number,number]) --产生随机数
select dbms_random.value from dual; [0,1)
select dbms_random.value(10,20) from dual; [number1 ~ number2)

--5.1.1.3 日期函数
round(date[,fmt])
select round(sysdate) from dual;
select round(sysdate,'year') from dual;
trunc(date[,fmt])
select trunc(sysdate) from dual;
select trunc(sysdate,'year') from dual;
sysdate
select sysdate from dual;
months_between(date1,date2)
select round(abs(months_between(to_date('2011-01-01','yyyy-dd-mm'),sysdate)),1) from dual;
add_months(date,number) --在date日期上加number个月以后的结果
select add_months(sysdate,10) from dual;
next_day(date,char) --下一个星期char
select next_day(next_day(sysdate,'星期日'),'星期日') from dual;
last_day(date) --包括date在内地月份中最后一天
select last_day(sysdate) from dual;

--5.1.1.4 转换函数
to_char(date[,fmt])
to_char(number[,fmt])
select '当前时间为：' || to_char(sysdate) from dual;
select '当前时间为：' || to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select '年龄为：' || 100 from dual;
select '年龄为：' || to_char(100) from dual;
select '年龄为：' || to_char(100,'C999,999,999.99') from dual;
select '年龄为：' || to_char(100,'L000,000,000.00') from dual;
select to_char(50,'rn') from dual;
select to_char(3999,'RN') from dual;

to_date(char[,fmt])


to_number(char[,fmt])
select to_number('12345') + 5 from dual;
select to_number('12345.456') from dual;

--5.1.1.5 其他函数
user
select user from dual;
uid
select uid from dual;
select uid as 编号,user as 用户名 from dual;
nvl(expr1,expr2)
select nvl(null,'是空的') from dual;
select nvl('不空','时空的') from dual;

select topic_id as 编号,topic_title as 标题,
nvl(to_char(topic_last_reply_user_id),'无') as 最后回复用户ID
from bbs_topic;
nvl2(expr1,expr2,expr3)

--5.1.2 分组函数
--5
.1.2.1 常用分组函数
--聚合函数：count avg sum min max
select user_id as 编号,user_name as 登录名,user_birthday as 畜生年月,
to_char(sysdate,'yyyy') - to_char(use

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新资格考试认证oracle中的常用sql语句(10)全文阅读和word下载服务。