sql函数

 

一、COALESCE()函数 

（取非空，若1个参数不为空返回第一个参数，若为空，看第二个参数，依次--）
主流数据库系统都支持COALESCE()函数，这个函数主要用来进行空值处理，其参数格
式如下： 
COALESCE ( expression,value1,value2……,valuen) 
  COALESCE()函数的第一个参数expression为待检测的表达式，而其后的参数个数不定。
COALESCE()函数将会返回包括expression在内的所有参数中的第一个非空表达式。如果
expression不为空值则返回expression；否则判断value1是否是空值，如果value1不为空值则返
回value1；否则判断value2是否是空值，如果value2不为空值则返回value3；……以此类推，
如果所有的表达式都为空值，则返回NULL。 
  我们将使用COALESCE()函数完成下面的功能，返回人员的“重要日期”：如果出生日
期不为空则将出生日期做为“重要日期”，如果出生日期为空则判断注册日期是否为空，如
果注册日期不为空则将注册日期做为“重要日期”，如果注册日期也为空则将“2008年8月8
日”做为“重要日期”。实现此功能的SQL语句如下： 
MYSQL、MSSQLServer、DB2: 
SELECT FName,FBirthDay,FRegDay, 
COALESCE(FBirthDay,FRegDay,'2008-08-08')  AS ImportDay  
FROM T_Person 
Oracle: 
SELECT FBirthDay,FRegDay,  
COALESCE(FBirthDay,FRegDay,TO_DATE('2008-08-08', 'YYYY-MM-DD HH24:MI:SS'))  
AS ImportDay  

 

FROM T_Person 

  执行完毕我们就能在输出结果中看到下面的执行结果： 
FName  FBirthDay  FRegDay  ImportDay 
Tom  1981-03-22 00:00:00  1998-05-01 00:00:00  1981-03-22 00:00:00 
Jim  1987-01-18 00:00:00  1999-08-21 00:00:00  1987-01-18 00:00:00 
Lily  1987-11-08 00:00:00  2001-09-18 00:00:00  1987-11-08 00:00:00 
Kelly  1982-07-12 00:00:00  2000-03-01 00:00:00  1982-07-12 00:00:00 
Sam  1983-02-16 00:00:00  1998-05-01 00:00:00  1983-02-16 00:00:00 
Kerry  <NULL>  1999-03-01 00:00:00  1999-03-01 00:00:00 
Smith  <NULL>  <NULL>  2008-08-08 

 

BillGates  1972-07-18 00:00:00  1995-06-19 00:00:00  1972-07-18 00:00:00 

  这里边最关键的就是Kerry和Smith这两行，可以看到这里的计算逻辑是完全符合我们的

 

需求的。

COALESCE()函数可以用来完成几乎所有的空值处理，不过在很多数据库系统中都提
供了它的简化版，这些简化版中只接受两个变量，其参数格式如下： 
MYSQL: 
  IFNULL(expression,value) 
MSSQLServer: 
  ISNULL(expression,value) 
Oracle: 
  NVL(expression,value) 
  这几个函数的功能和COALESCE(expression,value)是等价的。比如SQL语句用于返回人
员的“重要日期”，如果出生日期不为空则将出生日期做为“重要日期”，如果出生日期为空
则返回NULL： 
MYSQL: 
SELECT FBirthDay,FRegDay,  
IFNULL(FBirthDay,FRegDay)  AS ImportDay  
FROM T_Person 
MSSQLServer: 
SELECT FBirthDay,FRegDay,  
ISNULL(FBirthDay,FRegDay)  AS ImportDay  
FROM T_Person 
Oracle: 
SELECT FBirthDay,FRegDay,  
NVL(FBirthDay,FRegDay)  AS ImportDay  

FROM T_Person 

二、TRIM（）的用法

RIM函数用来对字符串进行剪裁操作，包括TRIM,LTRIM,RTRIM三个函数。虽然平常用的比较多，但还是有些用法没有细究过。

1.TRIM可以带语义型参数

TRIM(str1)，表示去掉两边空格 
TRIM(leading str2 from str1)，去掉str1左边的str2，相当于LTRIM(str1,str2) 
TRIM(trailing str2 from str1)，去掉str1右边的str2，相当于RTRIM(str1,str2) 
TRIM(both str2 from str1)，去掉str1两边的str2。

SQL> select trim('   x   ') from dual;
TRIM('X')
---------
x

SQL> select trim(leading '.' from '...x...') from dual;
TRIM(LEADING'.'FROM'...X...')
-----------------------------
x...

SQL> select trim(trailing '.' from '...x...') from dual;
TRIM(TRAILING'.'FROM'...X...')
------------------------------
...x

SQL> select trim(both '.' from '...x...') from dual;
TRIM(BOTH'.'FROM'...X...')
--------------------------
x

2.去掉两边单字符有两种方法

--不能直接用trim(str1,str2)
SQL> select trim('aaaxaaa','a') from dual;
select trim('aaaxaaa','a') from dual
ORA-00907: missing right parenthesis

--方法1.ltrim(rtrim())或rtrim(ltrim())
SQL> select ltrim(rtrim('aaaxaaa','a'),'a') from dual;
LTRIM(RTRIM('AAAXAAA','A'),'A'
------------------------------
x

--方法2.trim(both str2 from str1) 
SQL> select trim(both 'a' from 'aaaxaaa') from dual;
TRIM(BOTH'A'FROM'AAAXAAA')
--------------------------
x

3.去掉多个字符只能用ltrim或rtrim

SQL> select ltrim('abcxcab','abc') from dual;
LTRIM('ABCXCAB','ABC')
----------------------
xcab

SQL> select rtrim('abcxcab','abc') from dual;
RTRIM('ABCXCAB','ABC')
----------------------
abcx

SQL> select trim(both 'abc' from 'abcxcab') from dual;
select trim(both 'abc' from 'abcxcab') from dual
ORA-30001: trim set should have only one character

trim函数带单字符str2参数会报错ORA-00907: missing right parenthesis 
带多字符语义参数会报错ORA-30001: trim set should have only one character。

而且，ltrim或rtrim不是完全匹配str2字符串，而是把str2中存在的字符全部剪裁掉，不论顺序，知道字符在str2中不存在。再看下面的例子会更清晰：

SQL> select rtrim('abcxcab','Abc') from dual;
RTRIM('ABCXCAB','ABC')
----------------------
abcxca

Step1.函数rtrim指明从右到左剪裁 
Step2.str1的最右边字符’b’，存在于str2(‘Abc’)内，裁掉 
Step3.str1右边第二个字符’a’，在str2中不存在，rtrim函数终止。

三、sql中exists,not exists的用法

-如果表tablename1存在，就删掉该表
if exists (select name from sysobjects where xtype='U' and name='tablename1' )
drop table tablename1

--如果表tablename1不存在，就创建该表
if not  exists (select name from sysobjects where xtype='U' and name='tablename1' )
create  table tablename1 (name varchar(100) null )

判断存在数据
if exists ( select 1 from tablename where ???? )

 

exists : 强调的是是否返回结果集，不要求知道返回什么, 比如：
  select name from student where sex = 'm' and mark exists(select 1 from grade where ...) ,只要
exists引导的子句有结果集返回，那么exists这个条件就算成立了,大家注意返回的字段始终为1，如果改成“select 2 from grade where ...”，那么返回的字段就是2，这个数字没有意义。所以exists子句不在乎返回什么，而是在乎是不是有结果集返回。

而 exists 与 in 最大的区别在于 in引导的子句只能返回一个字段，比如：
  select name from student where sex = 'm' and mark in (select 1,2,3 from grade where ...)  
，in子句返回了三个字段，这是不正确的，exists子句是允许的，但in只允许有一个字段返回，在1，2，3中随便去了两个字段即可。

而not exists 和not in 分别是exists 和 in 的 对立面。

exists （sql 返回结果集为真）  
not exists (sql 不返回结果集为真）

下面详细描述not exists的过程：

如下：
表A
ID NAME  
1   A1
2   A2
3   A3

表B
ID AID NAME
1   1     B1
2   2     B2  
3   2     B3

表A和表B是１对多的关系 A.ID => B.AID

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
3 A3
===========================================================================
EXISTS = IN,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因
SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B)

NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B)
 

 有时候我们会遇到要选出某一列不重复,某一列作为选择条件,其他列正常输出的情况.

如下面的表table:

Id  Name  Class Count  Date

 1   苹果    水果    10     2011-7-1

 1   桔子    水果    20     2011-7-2

 1   香蕉    水果    15     2011-7-3

 2   白菜    蔬菜    12     2011-7-1

 2   青菜    蔬菜    19     2011-7-2

如果想要得到下面的结果:(Id唯一,Date选最近的一次)

1   香蕉    水果    15     2011-7-3

2   青菜    蔬菜    19     2011-7-2

正确的SQL语句是:

SELECT Id, Name, Class, Count, Date
FROM table t
WHERE (NOT EXISTS
          (SELECT Id, Name, Class, Count, Date FROM table 
         WHERE Id = t.Id AND Date > t.Date))

如果用distinct,得不到这个结果, 因为distinct是作用与所有列的

SELECT DISTINCT Id, Name, Class, Count, Date FROM table

结果是表table的所有不同列都显示出来,如下所示:

 1   苹果     水果    10     2011-7-1

 1   桔子    水果    20     2011-7-2

 1   香蕉    水果    15     2011-7-3

 2   白菜    蔬菜    12     2011-7-1

 2   青菜    蔬菜    19     2011-7-2

如果用Group by也得不到需要的结果,因为Group by 要和聚合函数共同使用,所以对于Name,Class和Count列要么使用Group by,要么使用聚合函数. 如果写成

SELECT Id, Name, Class, Count, MAX(Date) 
FROM table
GROUP BY Id, Name, Class, Count

得到的结果是

 1   苹果     水果    10    2011-7-1

 1   桔子    水果    20     2011-7-2

 1   香蕉    水果    15     2011-7-3

 2   白菜    蔬菜    12     2011-7-1

 2   青菜    蔬菜    19     2011-7-2

如果写成

SELECT Id, MAX(Name), MAX(Class), MAX(Count), MAX(Date) 
FROM table 
GROUP BY Id

得到的结果是:

 1   香蕉    水果    20     2011-7-3

 2   青菜    蔬菜    19     2011-7-2

如果用in有时候也得不到结果,（有的时候可以得到，如果Date都不相同（没有重复数据），或者是下面得到的Max（Date）只有一个值）

SELECT DISTINCT Id, Name, Class, Count, Date FROM table

WHERE (Date IN
          (SELECT MAX(Date)
         FROM table
         GROUP BY Id))

得到的结果是：（因为MAX(Date)有两个值2011-7-2，2011-7-3）

 1   桔子    水果    20     2011-7-2

 1   香蕉    水果    15     2011-7-3

 2   青菜    蔬菜    19     2011-7-2

注意in只允许有一个字段返回

有一种方法可以实现：

SELECT Id, Name, Class, COUNT, Date
FROM table1 t
WHERE (Date =
          (SELECT MAX(Date)
         FROM table1
         WHERE Id = t .Id))

四、Sql语句中IN和exists的区别及应用

　表展示

　　　　首先，查询中涉及到的两个表，一个user和一个order表，具体表的内容如下：

　　　　user表：

　　　　

　　　　order表：

　　　　

 

　　in

　　　　确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

　　　　具体sql语句如下：

 1 SELECT
 2     *
 3 FROM
 4     `user`
 5 WHERE
 6     `user`.id IN (
 7         SELECT
 8             `order`.user_id
 9         FROM
10             `order`
11     )

 

　　　　这条语句很简单，通过子查询查到的user_id 的数据，去匹配user表中的id然后得到结果。该语句执行结果如下：

　　　　

　　　　它的执行流程是什么样子的呢？让我们一起来看一下。

　　　　首先，在数据库内部，查询子查询，执行如下代码：

        SELECT
            `order`.user_id
        FROM
            `order`

　　　　执行完毕后，得到结果如下：

　　　　

　　　　此时，将查询到的结果和原有的user表做一个笛卡尔积，结果如下：

　　　　

　　　　此时，再根据我们的user.id IN order.user_id的条件，将结果进行筛选（既比较id列和user_id 列的值是否相等，将不相等的删除）。最后，得到两条符合条件的数据。
　　　　

　　exists

　　　　指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

　　　　具体sql语句如下：

 

 1 SELECT
 2     `user`.*
 3 FROM
 4     `user`
 5 WHERE
 6     EXISTS (
 7         SELECT
 8             `order`.user_id
 9         FROM
10             `order`
11         WHERE
12             `user`.id = `order`.user_id
13     )

 

　　　　这条sql语句的执行结果和上面的in的执行结果是一样的。

　　　　

　　　　但是，不一样的是它们的执行流程完全不一样：

　　　　使用exists关键字进行查询的时候，首先，我们先查询的不是子查询的内容，而是查我们的主查询的表，也就是说，我们先执行的sql语句是：

　　　　 SELECT `user`.* FROM `user` 

　　　　得到的结果如下：

　　　　

　　　　然后，根据表的每一条记录，执行以下语句，依次去判断where后面的条件是否成立：

 

EXISTS (
        SELECT
            `order`.user_id
        FROM
            `order`
        WHERE
            `user`.id = `order`.user_id
    )

　　　　如果成立则返回true不成立则返回false。如果返回的是true的话，则该行结果保留，如果返回的是false的话，则删除该行，最后将得到的结果返回。

　　区别及应用场景

　　　　in 和 exists的区别: 如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询，所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了 ，另外IN时不对NULL进行处理。

　　　　in 是把外表和内表作hash 连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。

　　not in 和not exists

　　　　如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

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 要快。