sql常用函数
--分组函数 select avg(emp.salary), max(emp.salary), count(emp.salary), count(emp.employeeid), count(*) from employee emp where emp.deptid = '10' --分组数据 部门的工资和 select emp.deptid, sum(emp.salary) from employee emp -- WHERE emp.employeeid='1001' group by emp.deptid --部门工资和大于6000 select emp.deptid, sum(emp.salary) from employee emp group by emp.deptid having sum(salary) > 6000; -sql 1999新连接标准 select emp.employeeid, emp.deptid, dept.deptid, dept.deptname from employee emp join deptment dept on emp.deptid = dept.deptid join qualification qual on emp.qualid = qual.qualid; --左连接 select * from 表a left outer jion 表b on 表a的列=表b的列;表a在前 select * from deptment dept left outer join employee emp on dept.deptid = emp.deptid; --右连接 select * from 表a left outer jion 表b on 表a的列=表b的列;表b在前 select * from employee emp right outer join deptment dept on dept.deptid = emp.deptid; --全连接 select * from 表a full outer jion 表b on 表a的列=表b的列;和右连接 效果一样 select * from employee emp full outer join deptment dept on dept.deptid = emp.deptid;
--内置函数 dual哑表 --字符型函数 --concat连接字符串,substr 截取从a到b位置的字符串,instr select concat(1,'ab'),substr('123456',-3,2),instr('abcbc','bc')from dual; select instr ('abcdedfgd','d',5) from dual;--字符串查找,最后数字可以指定查找的起始位置 select replace('abddcdc','dd','ll') from dual;--字符串替换 --trim ltrim rtrim用法 select trim(' a b c '),length(trim(' a b c ')) from dual;--去除前后空格 select ltrim(' abc '),length(ltrim(' abc '))from dual;--去除前面空格 select ltrim('1022012032233','102') from dual;--从前面去除包含1,0,2的字符,遇到不是则终止 select rtrim(' abc '),length(rtrim(' abc '))from dual;--去除后面空格 select rtrim('22331099011109','109')from dual;--从后面去除包含1,0,2的字符,遇到不是则终止 --日期型函数 --表示 year 的:y 表示年的最后一位 、 -- yy 表示年的最后2位 、 yyy 表示年的最后3位 、 yyyy 用4位数表示年 --表示month的: mm 用2位数字表示月 、mon 用简写形式, 比如11月或者nov 、 month 用全称, 比如11月或者november --表示day的:dd 表示当月第几天 、ddd 表示当年第几天 、 dy 当周第几天,简写, 比如星期五或者friday 当周第几天。 --表示hour的:hh 2位数表示小时 12进制、 hh24 2位数表示小时 24小时 --表示minute的:mi 2位数表示分钟 --表示second的:ss 2位数表示秒 60进制 --表示季度的:q 一位数 表示季度 (1-4) --另外还有ww 用来表示当年第几周 w用来表示当月第几周。 select to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss') from dual; select sysdate from dual;--系统时间 select add_months(to_date('2004-05-07 13:23:44','yyyy-mm-dd hh24:mi:ss'),2) from dual;--月份加 select add_months(sysdate,-2) from dual;--月份减 select add_months(SYSDATE,2) from dual;--月份加 select months_between(sysdate,'2-8月-16') dd from dual; select sysdate-hiredate from scott.emp;--相差天数 select (sysdate-hiredate)/7 from scott.emp;--周数 Select to_char(sysdate,'yyyy') from dual;--当前年 Select to_char(sysdate,'MM') from dual;--当前月 Select to_char(sysdate,'dd') from dual;--当前日 Select to_char(sysdate,'yyyy-mm') from dual;--当前年月 Select to_char(sysdate,'yyyy-MM-dd') from dual;--当前年月日 Select Extract(year from sysdate) from dual;--当前年 select extract (month from sysdate) from dual;--当前月 select extract (day from sysdate) from dual--当前日 select last_day(sysdate) from dual;--当前日期所在月的最后一天 --NEXT_DAY(d, day_of_week) --返回由"day_of_week"命名的,在变量"d"指定的日期之后的第一个工作日的日期。 --参数"day_of_week"必须为该星期中的某一天。 SELECT next_day(to_date('2018-09-18','YYYY-MM-DD'),1) FROM dual; SELECT next_day(to_date('2018-09-18','YYYY-MM-DD'),2) FROM dual; SELECT SYSDATE,next_day(SYSDATE,1) FROM dual; --current_date()返回当前会话时区中的当前日期 select sessiontimezone,current_date from dual; --round(日期,a)a为year moth day ..四舍五入 SELECT ROUND(to_date('20170619','yyyymmdd'),'year') FROM dual;--2017-1-1 SELECT ROUND(to_date('20170719','yyyymmdd'),'year') FROM dual;--2018-1-1 SELECT ROUND(to_date('20170215','yyyymmdd'),'month') FROM dual;--2017-2-1 SELECT ROUND(to_date('20170212','yyyymmdd'),'month') FROM dual;--2017-2-1 SELECT ROUND(to_date('20170216','yyyymmdd'),'month') FROM dual;--2017-3-1(日>15,月份进一) SELECT ROUND(to_date('2017-02-16 12:00:01','yyyy-mm-dd hh24:mi:ss'),'month') FROM dual; SELECT ROUND(to_date('20180813','yyyymmdd'),'day') FROM dual;--2018-08-12(周日) SELECT ROUND(to_date('20180815','yyyymmdd'),'day') FROM dual;--2018-08-12(周日) SELECT ROUND(to_date('20180817','yyyymmdd'),'day') FROM dual;--2018-08-19(周日)(日个位数>5,日期进到这周星期日) SELECT ROUND(to_date('20180822','yyyymmdd'),'day') FROM dual;--2018-08-19(周日)(日个位数<=5,日期退到上周星期日) SELECT to_char(ROUND(to_date('20180822','yyyymmdd'),'day'))FROM dual; SELECT TRUNC(to_date('20170216','yyyymmdd'),'month') FROM dual;--2017-2-1 --日期加减 日期-interval --当前时间减去7分钟的时间 select sysdate,sysdate - interval '7' MINUTE from dual; --当前时间减去7小时的时间 select SYSDATE,Sysdate- interval '7' hour from dual; --当前时间减去7天的时间 select sysdate,SYSDATE - interval '7' day from dual; --当前时间减去7月的时间 select sysdate,sysdate - interval '7' month from dual; --当前时间减去7年的时间 select sysdate,sysdate - interval '7' year from dual; --时间间隔乘以一个数字 select sysdate,sysdate - 8*interval '7' hour from dual; --数字型函数 select abs (-100) from dual;--绝对值 SELECT POWER(5,3) FROM dual;--power(a,n)a的n次方 SELECT POWER(-5,3) FROM dual; SELECT POWER(4,-2) FROM dual; select mod (10,3) from dual;--求模 select ceil (12.6) from dual;--取上限整数 select floor (12.6) from dual;--取下限整数 select round (12.5) from dual;--四舍五入 select round (12.46327,1) from dual;--四舍五入,逗号后数字表示精度 select round (16.46327,-1) from dual;--四舍五入,逗号后数字表示精度 select trunc (12.455,1) from dual;--截取,逗号后数字表示精度