sql函数整理及sql执行顺序

1.sql关键字
1．union子句用法

union子句
union操作符将两个查询结果合并为一个结果集。为连接查询合并两个表时：列的数日和顺序在查中必须一致；数据类型兼容
语法：
select  语句
union   [ all ]
select  语句
注意：
1 .union运算从最终结果集中删除重复记录，如想不删除重复记录使用all关键字
2 .第一个select语句中不能用order by或compute子句，只能是最后一个select语名后用
 例：查询计算机系的学生式年龄不大于19岁的学习，按年龄倒排序
select   *   from  department  where  dept = ' computer ' ;
union ;
select  *   from  student  where  age <= 19
order   by  age  desc
2．WHERE子句可包括各种条件运算符：
　　比较运算符(大小比较)：>、>=、=、<、<=、<>、!>、!<
　　范围运算符(表达式值是否在指定的范围)：BETWEEN…AND…
　　NOT BETWEEN…AND…
　　列表运算符(判断表达式是否为列表中的指定项)：IN (项1,项2……)
　　NOT IN (项1,项2……)
　　模式匹配符(判断值是否与指定的字符通配格式相符):LIKE、NOT LIKE
　　空值判断符(判断表达式是否为空)：IS NULL、NOT IS NULL
　　逻辑运算符(用于多条件的逻辑连接)：NOT、AND、OR
1、范围运算符例：age BETWEEN 10 AND 30相当于age>=10 AND age<=30
2、列表运算符例：country IN ('Germany','China')
3、模式匹配符例：常用于模糊查找，它判断列值是否与指定的字符串格式相匹配。可用于char、varchar、text、ntext、datetime和smalldatetime等类型查询。
可使用以下通配字符：
　　百分号%：可匹配任意类型和长度的字符，如果是中文，请使用两个百分号即%%。
　　下划线_：匹配单个任意字符，它常用来限制表达式的字符长度。
　　方括号[]：指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。[^]：其取值也[] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
　　例如：
限制以Publishing结尾，使用LIKE '%Publishing'
限制以A开头：LIKE '[A]%'
限制以A开头外：LIKE '[^A]%'

3．in和exists
in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当，那么用in和exists差别不大。
如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in：
例如：表A（小表），表B（大表）1：select * from A where cc in (select cc from B)
效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc)
效率高，用到了B表上cc列的索引。
相反的2：select * from B where cc in (select cc from A)效率高，用到了B表上cc列的索引；select * from B where exists(select cc from A where cc=B.cc)效率低，用到了A表上cc列的索引。
not in 和not exists如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
in 与 =的区别
select name from student where name in ('zhang','wang','li','zhao');
与
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的结果是相同的。
4．Casewhenthen与DECODE用法
decode语法：
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF
decode(字段或字段的运算，值1，值2，值3）
       这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
 当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
使用方法：
1．比较大小
select decode(sign(变量1-变量2),-1,变量1,变量2) from dual; --取较小值
sign()函数根据某个值是0、正数还是负数，分别返回0、1、-1
例如：
变量1=10，变量2=20
则sign(变量1-变量2)返回-1，decode解码结果为“变量1”，达到了取较小值的目的。
2．统计合格率
select sum(DECODE(C810000125,'是',1,0))/COUNT(1) 合格率 FROM t581
这句的含义是如果C810000125字段的值为“是”则标记其为1，为其他则标记其为0，最后统计总的该字段为“是”的个数，再除以总条数则是合格率的值。
还可以这样写：
select sum(case when C810000125 = '是' then 1 else 0 end)/COUNT(1) 合格率 FROM t581 
5．Case..when用法
　　WHEN  col3 > 1  AND  col3 <2
　　  THEN '1'
　　WHEN  col3 > 2  AND  col3 <3
　　  THEN '2'
　　WHEN  col3 > 3  AND  col3 <4
　　  THEN '3'
　　ELSE  '4'
　　END  mylevel
　　FROM table1
　　注意点：
　　1、以CASE开头，以END结尾
　　2、分支中WHEN 后跟条件，THEN为显示结果
　　3、ELSE 为除此之外的默认情况，类似于高级语言程序中switch case的default，可以不加
4、END 后跟别名

2．Sql函数
2.1字符串函数
2.1.1 LOWER()和UPPER()
LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。
2.1.2数据类型转换为字符串类型STR()
把数值型数据转换为字符型数据。
STR (<float_expression>[，length[， <decimal>]])
length 指定返回的字符串的长度，decimal 指定返回的小数位数。如果没有指定长度，缺省的
length 值为10， decimal 缺省值为0。
当length 或者decimal 为负值时，返回NULL；
当length 小于小数点左边（包括符号位）的位数时，返回length 个*；
先服从length ，再取decimal ；
当返回的字符串位数小于length ，左边补足空格。
2.1.3去空格函数LTRIM()与RTRIM()
1、LTRIM() 把字符串头部的空格去掉。
2、RTRIM() 把字符串尾部的空格去掉。
2.1.4取子串函数
1、left()
LEFT (<character_expression>， <integer_expression>)
返回character_expression 左起 integer_expression 个字符。
2、RIGHT()
RIGHT (<character_expression>， <integer_expression>)
返回character_expression 右起 integer_expression 个字符。
3、SUBSTRING()
SUBSTRING (<expression>， <starting_ position>， length)
返回从字符串左边第starting_ position 个字符起length个字符的部分。
2.1.5字符串比较函数
1、CHARINDEX()
返回字符串中某个指定的子串出现的开始位置。
CHARINDEX (<’substring_expression’>， <expression>)
其中substring _expression 是所要查找的字符表达式，expression 可为字符串也可为列名表达式。如
果没有发现子串，则返回0 值。
此函数不能用于TEXT 和IMAGE 数据类型。
2、PATINDEX()
返回字符串中某个指定的子串出现的开始位置。
PATINDEX (<’%substring _expression%’>， <column_ name>)其中子串表达式前后必须有百分号
“%”否则返回值为0。
与CHARINDEX 函数不同的是，PATINDEX函数的子串中可以使用通配符，且此函数可用于
CHAR、 VARCHAR 和TEXT 数据类型。
2.2日期函数
2.2.1. 日期和字符转换函数用法（to_date,to_char）
select to_char(sysdate,'yyyy-mm-dd hh24miss') 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;   获取时间的秒
2.2.2.求某天是星期几    
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;    
   星期一    
   select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;    
   monday    
   设置日期语言    
   ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';    
   也可以这样    
   TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')   
2.2.3. 两个日期间的天数    
    select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;   
2.2.4. 时间为null的用法    
   select id, active_date from table1    
   UNION    
   select 1, TO_DATE(null) from dual;    
   注意要用TO_DATE(null)   
2.2.5.月份差 
   a_date between to_date('20011201','yyyymmdd') and to_date('20011231','yyyymmdd')    
   那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。    
   所以，当时间需要精确的时候，觉得to_char还是必要的
    
2.2.6. 日期格式冲突问题    
    输入的格式要看你安装的ORACLE字符集的类型, 比如 US7ASCII, date格式的类型就是 '01-Jan-01'    
    alter system set NLS_DATE_LANGUAGE = American    
    alter session set NLS_DATE_LANGUAGE = American    
    或者在to_date中写    
    select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;    
    注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，    
    可查看    
    select  from nls_session_parameters    
    select  from V$NLS_PARAMETERS   
2.2.7. 查找2002-02-28至2002-02-01间除星期一和七的天数
   select count()    
   from ( select rownum-1 rnum    
       from all_objects    
       where rownum = to_date('2002-02-28','yyyy-mm-dd') - to_date('2002-    
       02-01','yyyy-mm-dd')+1    
      )    
   where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )    
        not in ( '1', '7' )    
 
   查找2002-02-28至2002-02-01间除星期一和七的天数    
   在前后分别调用DBMS_UTILITY.GET_TIME, 让后将结果相减(得到的是1100秒, 而不是毫秒).   
2.2.8. 查找月份   
    select months_between(to_date('01-31-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) MONTHS FROM DUAL;    
    1    
   select months_between(to_date('02-01-1999','MM-DD-YYYY'),to_date('12-31-1998','MM-DD-YYYY')) MONTHS FROM DUAL;    
    1.03225806451613
     
2.2.9. Next_day的用法    
next_day函数      返回下个星期的日期,day为1-7或星期日-星期六,1表示星期日
   next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。    
   1 2 3 4 5 6 7    
   日 一 二 三 四 五六  
 
   ---------------------------------------------------------------
 
   select    (sysdate-to_date('2003-12-03 125545','yyyy-mm-dd hh24miss'))246060 from ddual
   日期 返回的是天 然后 转换为ss
2.2.10.一年的第几天    
   select TO_CHAR(SYSDATE,'DDD'),sysdate from dual
   310 2002-11-6 100351   

2.2.11.计算小时,分,秒,毫秒    
    select    
     Days,    
     A,    
     TRUNC(A24) Hours,    
     TRUNC(A2460 - 60TRUNC(A24)) Minutes,    
     TRUNC(A246060 - 60TRUNC(A2460)) Seconds,    
     TRUNC(A246060100 - 100TRUNC(A246060)) mSeconds    
    from    
    (    
     select    
     trunc(sysdate) Days,    
     sysdate - trunc(sysdate) A    
     from dual    
   )   

   select  from tabname    
   order by decode(mode,'FIFO',1,-1)to_char(rq,'yyyymmddhh24miss');    
 
       
   floor((date2-date1) 365) 作为年    
   floor((date2-date1, 365) 30) 作为月    
   d(mod(date2-date1, 365), 30)作为日.
   
2.2.12.round[舍入到最接近的日期](day舍入到最接近的星期日)
   select sysdate S1,
   round(sysdate) S2 ,
   round(sysdate,'year') YEAR,
   round(sysdate,'month') MONTH ,
   round(sysdate,'day') DAY from dual

2.2.13.trunc[截断到最接近的日期,单位为天] ,返回的是日期类型
   select sysdate S1,                   
     trunc(sysdate) S2,                 返回当前日期,无时分秒
     trunc(sysdate,'year') YEAR,        返回当前年的1月1日,无时分秒
     trunc(sysdate,'month') MONTH ,     返回当前月的1日,无时分秒
     trunc(sysdate,'day') DAY           返回当前星期的星期天,无时分秒
   from dual

2.2.14.计算时间差
     注oracle时间差是以天数为单位,所以换算成年月,日
   
      select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))365) as spanYears from dual        时间差-年
      select ceil(moths_between(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))) as spanMonths from dual        时间差-月
      select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))) as spanDays from dual             时间差-天
      select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))24) as spanHours from dual         时间差-时
      select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))2460) as spanMinutes from dual    时间差-分
      select floor(to_number(sysdate-to_date('2007-11-02 155503','yyyy-mm-dd hh24miss'))246060) as spanSeconds from dual 时间差-秒
2.2.15查找月的第一天,最后一天
     SELECT Trunc(Trunc(SYSDATE, 'MONTH') - 1, 'MONTH') First_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') - 1  86400 Last_Day_Last_Month,
       Trunc(SYSDATE, 'MONTH') First_Day_Cur_Month,
       LAST_DAY(Trunc(SYSDATE, 'MONTH')) + 1 - 1  86400 Last_Day_Cur_Month
   FROM dual;
2.2.16把当前时间以秒表示出来
把当前日期转换为以秒表示的形式
((sysdate -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600)
TZ_OFFSET函数显示出指定时区对UTC的偏移量。
3.oracle中sql的执行顺序
3.1.sql语句的执行步骤：
1）语法分析，分析语句的语法是否符合规范，衡量语句中各表达式的意义。
2） 语义分析，检查语句中涉及的所有数据库对象是否存在，且用户有相应的权限。
3）视图转换，将涉及视图的查询语句转换为相应的对基表查询语句。
4）表达式转换， 将复杂的 SQL 表达式转换为较简单的等效连接表达式。
5）选择优化器，不同的优化器一般产生不同的“执行计划”
6）选择连接方式， ORACLE 有三种连接方式，对多表连接 ORACLE 可选择适当的连接方式。
7）选择连接顺序， 对多表连接 ORACLE 选择哪一对表先连接，选择这两表中哪个表做为源数据表。
8）选择数据的搜索路径，根据以上条件选择合适的数据搜索路径，如是选用全表搜索还是利用索引或是其他的方式。
9）运行“执行计划”
3.2.oracle 共享原理：
        ORACLE将执行过的SQL语句存放在内存的共享池(shared buffer pool)中，可以被所有的数据库用户共享 当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的 执行路径. 这个功能大大地提高了SQL的执行性能并节省了内存的使用
三、oracle 语句提高查询效率的方法：1： where column in(select * from ... where ...); 2：... where exists (select 'X' from ...where ...); 第二种格式要远比第一种格式的效率高。在Oracle中可以几乎将所有的IN操作符子查询改写为使用EXISTS的子查询 使用EXIST，Oracle系统会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间 Oracle系统在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在在一个加了索引的临时表中 避免使用having字句 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销

3.3.SQL Select语句完整的执行顺序：
1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、使用order by对结果集进行排序;
8、执行select。
4．Sql的左连接，右连接与自连接
4.1.数据表的连接有:
1、内连接(自然连接): 只有两个表相匹配的行才能在结果集中出现
2、外连接: 包括
（1）左外连接(左边的表不加限制)
（2）右外连接(右边的表不加限制)
（3）全外连接(左右两表都不加限制)
3、自连接(连接发生在一张基表内)
4.2.左连接右连接自连接示例
select a.studentno, a.studentname, b.classname
  from students a, classes b
  where a.classid(+) = b.classid;
STUDENTNO STUDENTNAM CLASSNAME
---------- ---------- ------------------------------
    1 周虎     一年级一班
    2 周林     一年级二班
            一年级三班
以上语句是右连接：
即"(+)"所在位置的另一侧为连接的方向，右连接说明等号右侧的所有
记录均会被显示，无论其在左侧是否得到匹配。也就是说上例中，无
论会不会出现某个班级没有一个学生的情况，这个班级的名字都会在
查询结构中出现。
反之：
select a.studentno, a.studentname, b.classname
  from students a, classes b
  where a.classid = b.classid(+);
STUDENTNO STUDENTNAM CLASSNAME
---------- ---------- ------------------------------
    1 周虎     一年级一班
    2 周林     一年级二班
    3 钟林达
则是左连接，无论这个学生有没有一个能在一个班级中得到匹配的部门号，
这个学生的记录都会被显示。
select a.studentno, a.studentname, b.classname
  from students a, classes b
  where a.classid = b.classid;
这个则是通常用到的内连接,显示两表都符合条件的记录
总之,
左连接显示左边全部的和右边与左边相同的
右连接显示右边全部的和左边与右边相同的
内连接是只显示满足条件的!