常用函数

常用函数

 

 (1)as给列以别名显示:select username as 用户名 from t_user;(这里的as关键字可以省略)

 (2)distinct去掉重复的行：select distinct username from t_user;

 (3)使用运算符：select age+10 from t_user;(给每个人的年龄都加10岁)

 (4)in匹配集合中的任意值：select * from t_user where username in('马文涛','宝宝');

 (5)like模糊查询：%匹配0个或多个任意字符，_匹配1个任意字符。

 

select * from t_user where username like '%涛%';

***涛  涛***  *涛*  涛

select * from t_user where username like '张_'; 

 

(6)null判断某列为空：select * from t_user where sex is null;

(这里用is，不能用=，如果要返回不为null的记录就可以用is not null)

           

(7)order by排序：ASC: 升序排列（可以省略）,DESC: 降序排列

升序:select u.userid,u.username from t_user u order by u.userid;

降序:select u.userid,u.username from t_user u order by u.userid desc;

 

(8)聚集函数(对一组数据进行处理，返回一个值):

AVG–求平均值，COUNT–统计记录数，MAX–最大值，MIN–最小值，SUM–求和

<1>返回最小和最大的工资： select min(sal),max(sal) from emp;

<2>返回总记录数： select count(*) from emp;

<3>返回某个字段不为空的记录数： select count(comm) from emp;

<4>返回不为空且不重复的记录数： select count(distinct job) from emp;

 

(9)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 后面。

select 列1,聚集函数 from table group by 列1;

select 列1,列2,聚集函数 from table group by 列1,列2;        

(10)having  where分组前的条件过滤             having分组后的过滤

select username from t_user group by username having count(*) >=2;

       

一、伪列 【不用你创建，只要创建完表，自己就会存在的】

rowid【用来寻址用的，我想找到表中的某一行，通过rowid定位】

rownum【代表行号，来标记一行，唯一的号码的】

 

select owner from t where rownum <=10; 伪列  rowid

delete from t where rownum<=10000;（删除t表10000行）

 

五、between 某范围之间

select * from emp where sal between 1000 and 5000; 【等价下面的】

select * from emp where sal >= 1000 and sal<=5000;

        

六、字符串连接               ||

SQL> select id，xingming from t1;

SQL> select '员工编号:     '||empno as "员工编号",'  员工姓名:      '||ename as "员工姓名" from emp;

 

七、length

select length('##123##') len from dual;

 

八、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;

 

 

 

九、TO_CHAR 是把日期或数字转换为字符串

SQL> select sysdate from dual;

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

select to_char(sysdate,'yyyy/mm/dd hh24:mi:ss') from dual;

select to_char(sysdate,'yyyy*mm#dd hh24！mi-ss') from dual;

 

SQL> select to_char(123,'9999.00') from dual;

SQL> select to_char(12333,'$99,999.99') from dual;

SQL> select to_char(123334444,'$99,999.99') from dual;

 

十、substr  截取函数

SQL> select substr('12345',2,3) from dual;

(截取从第二个字符开始的3个字符)

select substr('1234523232323',2) from dual;

(截取从第二个字符开始,一直到字符串结束)

SQL> select substr('123456789',-5) from dual;

(截取后5位)

 

 

十一、字符是否包含   instr(string,substring,position,occurrence)

string:代表源字符串

substring:代表想从源字符串中查找的子串

position:代表查找开始的位置,默认为1

occurrence:代表查找值第几次出现,结果为字符串的位置,默认为1

 

没有找到，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('syranmoan','an',-1,1) FROM dual; -- 返回 8

SELECT instr('abc','d') FROM dual;  -- 返回 0

　　注：也可利用此函数来检查 'abc' 中是否包含  'd'，如果返回0表示不包含，否则表示包含。

 

 

十二、abs 绝对值

SQL> select abs(-5) from dual; (返回值为5)

SQL> select abs(5.5)  from dual;(返回值为5.5)

 

十三、Round 函数 (四舍五入)

 

SQL> select round(123.123) from dual;

SQL> select round(123.789) from dual;

SQL> select round(123.123,2) from dual;

SQL> select round(123.789,2) from dual;

 

十四、取整(ceil向上取整   floor向下取整)

trunc取整,不进行四舍五入,全舍

SQL>  select 3/2 from dual;

SQL>  select trunc(-3/2),ceil(-3/2),floor(-3/2),-3/2 from dual;

SQL>  select trunc(3/2),ceil(3/2),floor(3/2),3/2 from dual;

 

十五、to_date 把字符串转换成日期

 

SQL> create table tt2(id int,time date);

SQL> insert into tt2 values (1,to_date('2011-11-11 11:11:11','YYYY-MM-DD HH24:MI:SS'));

 

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(to_date('2017-09-04','yyyy-mm-dd'),'day') from dual;  ---周天

 

两个日期间的天数 

    select sysdate - to_date('20150903','yyyymmdd') from dual;    

    select floor(sysdate - to_date('20150903','yyyymmdd')) from dual;     

    select trunc(sysdate - to_date('20150903','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 TO_CHAR(to_date('2015-01-01','yyyy-mm-dd'),'DDD'),sysdate from dual;     

   select TO_CHAR(to_date('2015-01-31','yyyy-mm-dd'),'DDD'),sysdate from dual;

  

十六、Replace替换函数  

select replace('abc','b','######') from dual;  

  

十七、lpad [左添充] rpad [右填充](用于控制输出格式)

select lpad('func',7,'=') s1, rpad('func',7,'-') s2 from dual;

十八．  在Oracle中，“A minus B”用于将结果集B中含有的记录从结果集A中移除，即用于获取存在于结果集A中而不存在于结果集B中的记录；“A union B”和“A union all B”用于获取结果集A和结果集B的并集，其中前者将只保留重复记录中的一条，而后者将保留所有的记录；“A intersect B”用于获取结果集A和结果集B共有的记录，即它们的交集。在本文中将简要的介绍一下intersect的用法。

十九 符号函数（一般用sign(x)表示）是很有用的一类函数，能够帮助我们在几何画板中实现一些直接实现有困难的构造。符号函数能够把函数的符号析离出来。在数学和计算机运算中，其功能是取某个数的符号（正或负）：当x>0，sign(x)=1;当x=0，sign(x)=0;当x<0，sign(x)=-1；在通信中，sign(t)表示这样一种信号：当t≥0，sign(t)=1;即从t=0时刻开始，信号的幅度均为1；当t<0，sign(t)=-1；在t=0时刻之前，信号幅度均为