读书笔记——SQL CookBook

SQL CookBook——很多各样的例子

 

1. coalesce

COALESCE ( expression [ ,...n ] )

返回第一个非空表达式，如果所有表达式全为null，则返回null。

 

2. group by中使用count时关于null列

如果group by中对某列a使用count（a）as num, 那么对于所有a为空的行，不计算到num中。

如何才能使得即使a为空，也要计算进去呢，这时候就要使用count(*)，他计算行数，而不关心行中该列是否为空，不是对行的该列值进行判断后才计算行数。

 

group by语句中，要想在select中显示的表列，就必须放在group by后，聚集函数除外。   在group by 后有的列，可以不显示在select后边。

 

3. 关于sql中的别名问题

sql中可以使用列别名、表别名，定义过别名之后何时生效，何时可以使用呢。这就要根据sql执行的顺序： from where select……  

from之后是表名，所以表名的别名最早生效，where中就可以使用表名别名了，select中对列定义完列别名之后，在后边的order by等或者外层的查询中才可以使用它所定义的别名。

 

4. 数据库中的字符串连接

使用concat函数，其中oracle中可以使用函数简写方式双竖线，sqlserver中可以使用加号。

mysql的concat函数中如果有一个是null，那么就返回null。oracle中只要一个不为null，就不返回null。

 

5. select中使用条件逻辑

     使用

case when 条件表达式  then 结果表达式

                   when 条件表达式  then 结果表达式

                   else   结果表达式

                   end   as  别名

其中，else可以没有，如果没有对应可以满足的情况，那么返回null值。

 

6.取多少行数据

mysql使用limit 50（50条） 或者 limit 200,50（从200+1条开始取50条）

oracle 使用where  rownum<=5 

sqlserver   select top n * from table

db2    select * from table fetch first 5 rows only

其中需要特殊说明的是oracle 的方法与其他不同，其他数据库是从结果集中指定要返回的行数，但是oracle的rownum是在获取每一个满足条件的行

后才开始赋值，并递增rownum的值作为行号。但是如果条件不符合，那么就会放弃该行，去取下一行然后再判断是否满足条件。

对于rownum=1,不管表中是否有行，都会尝试返回一行，所以是可行的。但是对于rownum=10，每次获取一行后判断条件为行数是否等于10，而每次都不满足，就放弃该行再继续取下一行，对于放弃的操作rownum的数值不会增加，返回的总行数也仍然是一行，不满足条件，这莫不是个死循环。所以方法不可行。 

oracle使用rownum<=n时判断条件是:每返回一行，就判断当前总行数是否满足<=n，如果满足那么为当前行赋值rownum（如果是第一行，值为1）,继续返回下一行，判断总行数（如果之前已经反回了m个满足条件的行，那么现在的总行数是m+1）m+1是否<=n,如果满足就为刚返回的这一行rownum赋值为m+1，继续取下一行，如果不满足则结束取值。

 

7.随机排序原理

作数据库的时候给个字段，加上参数(rand())这样产生的就是每条记录都一个随机的id，然后以后要排序的时候order by这个字段就可以了。这样的方法检索速度很快。而且不会重复纪录，因为rand()产生的是很长的随机范围2.804989E-02～1.281764E-02
order by rand()运行的时候实际上已经将rand()搁到记录集中了

mysql获取n行随机数据   select * from table order by random() limit 50;

oracle获取n行随机数据   select * from (select rownum as num, t.* from table t order by dbms_random.value())  b where b.num<=50

select * from (select  t.* from table t order by dbms_random.value())  where rownum<=50

sqlserver select top 50 , * from table order by newid();

 

8.多列使用in的问题

select * from table1 where (a1, a2, a3) in (select b1, b2, b3 from table2);

也就是 select table1.* from table1 m inner join table2  n on(m.a1=n.b1 and m.a2=n.b2 and m.a3=n.b3) 

 

9. not in 的本质是什么？

为什么在有null值的时候not in 就会失效呢？下面将看到原因。

比如现在的条件是   where col not in ( select col2 from table2 ***)

加入子查询中出现了null值，结果为 10， 20 ，null 

那么sql就是  where col not in ( 10, 20, null)

等同于   where not ( col=10 or col=20 or col=null)

当col的值为10时，表达式结果为  not （ true or false or null）=>   true or null => null   错了 不对，不明白

在sql中，true or null的结果是null ， 但是false or null的结果也是null。当结果中只要有一个null值是，null就会延续下去。

所以使用in或者or时要非常注意到这一点。

 

10. 那么要解决not in 和null的问题，就需要使用到not exists了。

在表1中查找表2中不存在的数据：

select * from table1 where not exists( select '12368' from table2 where table1.col1 = table2.col2)

整个查询过程是什么样的呢？

首先从表1中依次取出一行， 根据条件 table1.col1 = table2.col2 判断表2中是否有满足此条件的记录，如果有exists()返回true，那么not exists()就返回false，

false的话，这一条记录就放弃。如果是true的话，这条记录就放入结果集中。

所以此时子查询中的select后边随便写什么字段都可以，因为写什么都没有影响，不影响结果。

 

11. 表连接

A LEFT JOIN B=====A LEFTOUTERJOIN B ====BRIGHTJOIN A

A RIGHT JOIN B =====A RIGHTOUTERJOIN B

A FULL JOIN B ======A FULLOUTERJOIN B

A INNER JOIN B===== A JOIN B

什么叫反连接呢，反连接（Anti-Join)用于从一个表中返回不在另一个数据源中的记录。

方法有三种： 第一种： not in     第二种： not  exists

第三种就是先使用左外连接或者右外连接，然后只保留不匹配的记录。

根据速度效率等建议首先使用第三种，然后第二种，最差第一种。如下这篇文章进行了详细分析，可以看看。

http://blog.itpub.net/post/33136/388503

 

12. 使用translate和replace函数。

 

13. oracle如何处理根据不同情况插入数据到不同的目标表。（mysql不支持）p70

orcle：

insert  all

when  loc in(2,4) then

into table1(col1, col2, col3) values ( cola, colb, colc)

when  loc in(1,3) then

into table2(col1, col2, col3) values ( cola, colb, colc)

else

into table3(col1, col2, col3) values ( cola, colb, colc)

select cola, colb, colc from dept;

insert all 和insert first的区别是 ，对于同一行数据， first在遇到表达式为真的情况就不在往后执行，all则每一条都判断和执行。

所以all有可能同一条数据插入多个表，只要满足多个条件。

 

 

14.使用其他表的值更新本表的值 p75

 

update emp e set (e.sal, e.comm) = (selecct ns.sal, ns.sal/2 from new_sal ns where ns.deptno = e.deptno)
where exists( select 1 from new_sal ns where ns.deptno = e.deptno)

这里必须要使用第二句where进行过滤，确保对什么样的数据进行操作。

因为更新emp数据，deptno相同的数据需要更新，但是deptno如果找不到相同的呢？那么(selecct ns.sal, ns.sal/2 from new_sal ns where ns.deptno = e.deptno)这条语句返回的就是空，所以其他本不应该被更新的数据就更新成空了，这不是我们要的结果。于是需要后边的条件进行过滤。切记切记。

或者通过inner join进行连接，也可以。

update emp e INNER JOIN dept d on  d.deptno = e.deptno 
set e.sal = d.sal/2

15 merge（oracle专用）            p78

 

merge into tablea a
using (select * from tableb ) b
        on (a.num = b.num)
when matched then
       update set a.com = 1000
when not matched then 
        insert (a.c1, a.c2, a.c3)
        values(b.c1, b.c2, b.c3)

16. P90  动态生成sql

select 'select count(*) from ' || table_name || ';'   counts from user_tables;

 

第五章  P83 元数据查询

第六章  使用字符串，非常强大