常用sql总结

Select语句完整的语法规则如下：

select {*,column[alias],...}  
from table 
[where...]          首先执行where语句将原有记录过滤
[group by...]       执行group by 进行分组
[having...]         执行having过滤分组
[order by...]       然后将select中的字段值选出来
                       最后执行order by 进行排序

例子：

1 between...and...
  select ename,sal from emp where sal between 800 and 1500;

说明：between...and... 包含相等的情况

2.处理日期  oracle的默认日期格式为“DD-MON-RR”，即两位的日期-月份缩写(对中文来说就是全称)-两位的年份，如下：2017年7月6日


select distinct * from  user_role t where rolename in ('机构管理员','管理人员','测试人员') and t.operatetime >'01-7月-17 ';

select distinct * from  user_role t where rolename in ('机构管理员','管理人员','测试人员') and t.operatetime between '01-7月-17 'and '06-7月-17 ' ;

3.模糊查询
  关键字‘like’被用作模糊查询 ，通配'%’ 表示零个或多个字符；
                                 通配'_'  表示一个字符；
select distinct * from  user_role t where rolename like '%管理%';

select distinct * from  user_role t where rolename like '管理_';

4 .排序
  order by 通常情况下，取出数据的时候是按照插入数据时的顺序进行显示的，当需要按要求排序显示时，用关键字order by . 默认升序asc  降序 desc

select  t.rolename ,t.operatetime from  user_role t where t.rolename in ('机构管理员','管理人员','测试人员') and t.operatetime > '01-7月-17 ' order by t.operatetime desc ;
  按照多个字段排序
select t.userroleid , t.rolename ,t.operatetime from  user_role t where t.rolename in ('机构管理员','管理人员','测试人员') and t.operatetime > '01-7月-17 ' order by t.operatetime desc ,t.userroleid desc ;

5 .大小写sql函数 lower  upper

select lower('AaCDEFG')  a from dual 

select upper('AaCdFG')  a from dual 

6 取子串substr
select substr('sssssshello','10',5) from dual;

7.取ASCII码数值所对应的字符

   select chr(65) from dual;//A

8.求一个字符的ASCII码数值
   select ascii('A') from dual; //65

9.四舍五入 round
    select round(24.5) from dual; //25
    select round(24.5527,2) from dual;//24.55
    select round(24.5527,-2) from dual;//0
    select round(24.5527,-1) from dual;//20

10.to_char();

to_char
        用于将数字或日期 转换为特定的字符串，to_char有两个参数，
        第一个参数为需要进行转换的日期或数字
        第二个参数为特定的转换格式，对于数字，to_char 有以下几个格式指定：

格式控制符   含义
9   代表一位数字，如果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示
0   代表一位数字，如果该位没有数字则强制显示0
$   显示美元符号
L   显示本地货币符号
.   显示小数点
，  显示千分位符号

日期格式
  YYYY，YY  代表四位、两位数字的年份
  MM        用数字表示的月份
  DD        数字表示的日
  MON       月份的缩写，对中文月份来说就是全拼 (2月)
  DY        星期的缩写，对中文的星期来说就是全称
  HH24、HH12  12小时 或者24小时
  MI        分钟数
  SS        秒数


     select to_char(sysdate,'YYYY-MM-DD MON DY HH24:MI:SS') from dual;
//2017-07-18 7月  星期二 16:49:31
     select to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') from dual;
     select to_char(sysdate,'YYYY/MM/DD HH24:MI:SS') from dual;

11.to_date
    将特定格式的字符串转换为日期格式，这个函数共有两个参数，
    第一个参数为一个自定义的日期字符串
    第二个参数指定这个字符串的格式

eg:  查询出1981年3月2日中午以后入职的雇员信息
    select ename ,hiredate from emp where hiredate > to_date('1981-03-02 12:00:00','YYYY-MM-DD HH24:MI:SS');

    select T.ROLENAME e ,T.OPERATETIME from aes_user_role T where T.OPERATETIME > to_date('2017-07-02 12:00:00','YYYY-MM-DD HH24:MI:SS') order by T.Operatetime desc;

12. to_number 
    将特定格式的字符串转换成数字格式，这个函数共有两个参数，
       第一个参数为一个自定义的数字字符串，
       第二个参数指定这个字符串的格式
      select ename,sal from emp where sal > to_number('$1,250.00','$9,999.99');//1250


select TO_NUMBER('199912'),TO_NUMBER('450.05') from dual;//199912,450.05

13.组函数，聚合函数，聚组函数 都一个意思
   avg: 求平均值
   max：求最大值
   min: 求最小值
   sum: 求和
   count: 求记录的数量 

14. group by 分组
    当需要进行数据分组的时候用到
eg：  计算每个部门的平均工资
         select mvg(sal) from emp group by deptno;
    规则：  出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中。
    出错eg: select ename ,max(sal) from emp group by deptno;
出错解释： 如果按照deptno进行分组，每组会有一个薪水最大值，但每个组里面会有多个名字，不能够产生唯一的ename,因此会报错。

    正常eg:select t.enterprisedepid ,sysdate ,2*3 from enterprise_dep t group by t.enterprisedepid;
15. 使用having对分组进行限制
    假如我们需要从分组数据中把某些特定的组剔除出去的时候，需要使用Having关键字，例如： 将平均薪水大于1000的组的平均薪水从emp表中取出来
  eg: select avg(sal) from emp group by deptno having avg(sal)>1000;

综合应用：
    我们要求薪水大于1200，按照部门分组，而且这些人分组后组内平均薪水必须大于1500，我们要查询分组的平均工资。
     select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal);
  解释：上述语句中首先会执行Where语句，将不符合选择条件的记录过滤掉；
         然后再将过滤后的数据按照group by 子句中的字段进行分组；
         接着使用having子句过滤掉不符合条件的分组；
         然后再将剩下的数据排序显示。