Oracle中常用sql函数（1）

1、动态性能视图及相关数据字典

（1）查看服务器状态------select  open_mode  from  v$database

（2）查看实例名-------select  instance_name  from  v$instance

（3）查看当前用户数据库下面有哪些表--------select  table_name  from  user_tables

（4）查看数据字典--------select  *  from  dict

（5）select * from dict where table_name='USER_TABLES' ------------注意条件中值的大写

（6）查看字典视图列--------select column_name,comments from dict_columns where table_name='USER_TABLES';

2、常用sql

（1）使用运算符：select age+10 from 表名;

（2）in匹配集合中的任意值：select * from student_drb where name in('张三','宝宝');

（3）like模糊查询：%匹配0个或多个任意字符，_匹配1个任意字符-------select * from student_drb where name like '_三'

（4）null判断某列为空：select * from t_user where sex is null;(这里用is，不能用=，如果要返回不为null的记录就可以用is not null)

（5）返回不为空且不重复的记录数： select count(distinct sex) from t_user;

（6）group by分组(分组了就不能直接返回*，经常和聚合函数count(age)一起使用):
             <1>单字段，按部门号分组，并统计每部门人数:select deptno,count(*) from emp group by deptno;
             <2>多字段，按性别和年龄分组: select sex,age,count(*) from t_user group by sex,age;             
        group by有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面。

（7）having过滤分组：select username from t_user group by username having count(*) >=2;

（8）字符串连接：select '学号:' || sno as s,'姓名：' || name from student_drb   注意：使用单引号不能使用双引号

（9）length函数：select age, length(age) len from student_drb

（10）LTRIM,RTRIM,TRIM【多用于处理空格】
LTRIM:左删除----left
RTRIM:右删除----right
TRIM:删除串两边的字符
select length('123') len1, length(ltrim(' 123  ')) lentrim from dual;
select length(' 123  ') len1, length(rtrim(' 123  ')) lentrim from dual;
select length(' 123  ') len1, length(trim(' 123  ')) lentrim from dual;

（11）TO_CHAR 是把日期或数字转换为字符串
   select sysdate from dual;
 select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;
 select to_char(123,'9999.00') from dual; 
 select to_char(12333,'$99,999.99') from dual;
 select to_char(123334444,'$99,999.99') from dual;

另外，

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as nowTime from dual;   //日期转化为字符串   
select to_char(sysdate,'yyyy') as nowYear   from dual;   //获取时间的年   
select to_char(sysdate,'mm')    as nowMonth from dual;   //获取时间的月   
select to_char(sysdate,'dd')    as nowDay    from dual;   //获取时间的日   
select to_char(sysdate,'hh24') as nowHour   from dual;   //获取时间的时   
select to_char(sysdate,'mi')    as nowMinute from dual;   //获取时间的分   
select to_char(sysdate,'ss')    as nowSecond from dual;   //获取时间的秒

select to_char(sysdate,'day') from dual---------------------返回的是星期几

（12）substr  截取函数
 select substr('12345',2,3) from dual;(截取从第二个字符开始的3个字符)------------234
 select substr('123456789',-5) from dual;(截取后5位)--------------56789

（13）字符是否包含（返回查找字符或字符串在原字符串中的位置）instr(string,substring,position,occurrence)
string:代表源字符串
substring:代表想从源字符串中查找的子串
position:代表查找开始的位置,默认为1
occurrence:代表查找值第几次出现返回结果为字符串的位置，没有找到，instr函数返回0.

SELECT instr('syranmo','s') FROM dual; -- 返回 1
SELECT instr('syranmo','ra') FROM dual;  -- 返回 3
SELECT instr('syran mo','a',1,2) FROM dual;  -- 返回 0
（根据条件，由于a只出现一次，第四个参数2，就是说第2次出现a的位置，显然第2次是没有再出现了，所以结果返回0。注意空格也算一个字符！）
SELECT instr('syranmo','an',-1,1) FROM dual;  -- 返回 4
　　     （就算是由右到左数，索引的位置还是要看‘an’的左边第一个字母的位置，所以这里返回4）
SELECT instr('abc','d') FROM dual;  -- 返回 0
　　 注：也可利用此函数来检查 'abc' 中是否包含  'd'，如果返回0表示不包含，否则表示包含。

（14）abs 绝对值 
select abs(-5) from dual; (返回值为5)
select abs(5.5)  from dual;(返回值为5.5)

（15）Round 函数 (四舍五入)
select round(123.123) from dual;---------123
select round(123.8) from dual;---------124

（16）trunc  取整【 截掉小数点后值，不会四舍五入】
select 3/2 from dual;-----------------------1.5
select trunc(3/2) from dual;---------------1

（17）to_date 把字符串转换成日期

select  to_date('2015-05-07','yyyy-mm-dd')  from dual

两个日期间的天数      
    select floor(sysdate - to_date('20150501','yyyymmdd')) from dual; -------其中floor(x)函数是返回不大于x的最大整数；ceil(x)函数是返回不小于x的最大整数
    select sysdate - to_date('20150501','yyyymmdd') from dual;     
    select trunc(sysdate - to_date('20150501','yyyymmdd')) from dual; 

月份差 
  select months_between(to_date('03-31-2015','MM-DD-YYYY'),to_date('01-31-2015','MM-DD-YYYY')) "MONTHS" FROM DUAL;  
  select months_between(to_date('03-31-2015','MM-DD-YYYY'),to_date('01-15-2015','MM-DD-YYYY')) "MONTHS" FROM DUAL;     

一年中的第几天

select trunc(sysdate - to_date('20160101','yyyymmdd') +1) as d from dual;

（18）Replace替换函数
    select replace('abc','b','1') from dual;   ------------a1b

（19）lpad [左添充] rpad [右填充](用于控制输出格式)
select lpad('func',7,'=') s1, rpad('func',7,'-') s2 from dual;---------     ===func,func---

总共7个字符不够的向左或向右填充