`
yuxuguang
  • 浏览: 136408 次
  • 性别: Icon_minigender_1
  • 来自: 北京
社区版块
存档分类
最新评论

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;
这个则是通常用到的内连接,显示两表都符合条件的记录
总之,
左连接显示左边全部的和右边与左边相同的
右连接显示右边全部的和左边与右边相同的
内连接是只显示满足条件的!

分享到:
评论

相关推荐

    SQL Select语句完整的执行顺序

    1、from子句组装来自不同数据源的数据; 2、where子句基于指定的条件对记录行进行筛选; 3、group by子句将数据... 标准顺序的 SQL 语句为: Select 考生姓名, max(总成绩) as max总成绩 from tb_Grade where 考生姓名

    null、查询顺序、字符串函数.sql

    null、查询顺序、字符串函数.sql

    收获不止SQL优化

    第3章 循规蹈矩——如何读懂SQL执行计划 34 3.1 执行计划分析概述 35 3.1.1 SQL执行计划是什么 35 3.1.2 统计信息用来做什么 36 3.1.3 数据库统计信息的收集 37 3.1.4 数据库的动态采样 37 3.1.5 获取执行...

    SQL查询安全性及性能优化

    将SQL代码插入到应用程序的输入参数中,之后,SQL代码被传递到数据库执行。从而达到对应用程序的攻击目的。 注入原理 常见攻击方法 检测是否可以注入【检测注入点】 示例:...

    收获,不止SQL优化--抓住SQL的本质

    第3章 循规蹈矩——如何读懂SQL执行计划 34 3.1 执行计划分析概述 35 3.1.1 SQL执行计划是什么 35 3.1.2 统计信息用来做什么 36 3.1.3 数据库统计信息的收集 37 3.1.4 数据库的动态采样 37 3.1.5 获取执行...

    sql和MySQL的语句执行顺序分析

    目前还在查看,但是在查阅资料时发现了一些有益的知识,给大家分享一下,就是关于sql以及MySQL语句执行顺序: sql和mysql执行顺序,发现内部机制是一样的。最大区别是在别名的引用上。 一、sql执行顺序 (1)from ...

    Microsoft SQL Server 2005技术内幕: T-SQ程序设计.pdf

    本书涵盖了T-SQL程序设计的方方面面,如基于集合的编程技术、日期和时间相关的XML和CLR数据类型的使用、临时对象、T-SQL和CLR用户自定义函数、存储过程、触发器、事务和新的错误处理结构、应用并发模型支持并发用户...

    Sql开窗函数资源-Dark-Horse-Programmer.zip

    sql开窗函数详解 其中包括hadoop,hive,hdfs,shell,linux基础,开窗函数学习资料和讲解视频 川在川上曰:靠谱! SaL 的运行顺序 from 1 where 2group by 3having 4 select 5 基本查询开窗查询 order by 6 limit 7 ...

    Microsoft SQL Server 2008技术内幕:T-SQL查询(第二卷)

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

    SQLServer2008技术内幕T-SQL查询包含源代码及附录A

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

    SQL Server 中 EXEC 与 SP_EXECUTESQL 的区别.doc

    3、EXEC 执行纯动态SQL,执行时可能无法使用预编译的执行计划,关键是不安全,可以导致 SQL 注入 ,而 SP_EXECUTESQL 执行参数化动态 SQL ,执行时能使用预编译的执行计划,而且保存存储过程时就可以确定可以使用的预...

    Microsoft+SQL+Server+2008技术内幕:T-SQL查询_源代码及附录 中文版

    主要内容包括SQL的基础理论、查询优化、查询算法及复杂度,以及在使用子查询、表表达式、排名函数、数据聚合和透视转换、TOP和APPLY、数据修改、分区表、特殊数据结构等实际应用时会遇到的各种高级查询问题和解决...

    Microsoft SQL Server 2005 Express Edition SP3

    执行默认安装时,无法远程连接到 SQL Server Express。您需要启用 TCP/IP 并检查是否已启用防火墙。 若要启用 TCP/IP,请执行下列操作: 在“开始”菜单中,选择“所有程序”,依次指向 Microsoft SQL Server 2005 ...

    SQL Server2005基础教程

     10.5 执行存储过程的不同方法  10.6 使用RETURN  10.7 控制流程  10.8 综合应用  10.9 小结  第11章 T-SQL基础  11.1 使用多个表  11.2 变量   11.3 临时表  11.4 聚合  11.5 GROUP BY  ...

    Oracle PL/SQL程序设计(第5版)(下册)第二部分

    《Oracle PL/SQL程序设计(第5版)》基于Oracle数据库11g,从PL/SQL编程、PL/SQL程序结构、PL/SQL程序数据、PL/SQL中的SQL、PL/SQL应用构建、高级PL/SQL主题这6个方面详细系统地讨论了PL/SQL以及如何有效地使用它。...

    oracle的sql优化

     *Sql语句中大量使用函数时候会导致很多索引无法使用上,要针对具体问题分析 4.其它  避免使用Select *,因为系统需要去帮你将*转换为所有的列名,这个需要额外去查询数据字典。  Count(1)和Count(*)差别不大。  ...

    21天学会SQL

    第三篇主要讲解SQL的编程,包括T-SQL语言、存储过程及触发器。第四篇讲解与商业智能有关的内容,包括集成服务、报表服务和分析服务。最后一篇是综合案例篇,分别使用.NET和Java语言实现了与SQL Server 2008的连接,...

    SQLPrompt 6.1.0.67(安装包、注册机)

    SQL Prompt格式不会再用大写表达XML函数 在SQL Prompt代码片段里,你可以使用 $PASTE$把你剪贴板里的内容插入到查询里去 修复了在WIN8里的一些稳定问题 数据浏览器的功能由于没有多有实用性已经被移除了 *断网离线...

Global site tag (gtag.js) - Google Analytics