SQL.Cookbook笔记



1.where子句是在select之前进行处理的，在处理求解“问题”查询的where子句之前，salary和commission并不存在，要到where子句处理完成之后，别名才生效，然后from子句是在where之前处理的。将原查询放在from子句中，那么在最外层where子句之前，以及最外层的where子句“看到”别名之前，就已经生成查询结果。

select * from (select sal as aslary,comm ascommission from emp)x where salary <5000;

2.双竖线连接运算符

select ename ||’works as A’||job as msgfrom emp where deptno=10

3.case表达式直接在select语句中执行条件逻辑

Select ENAME,SAL,

        Casewhen sal <=2000 then ‘underpaid’

            When sal >=4000 then ‘overpaid’

                  Else‘OK’

        Endas STATUS

From emp;

3.多个字段排序，order by的次序是从左向右

对子串进行排序：select ename，job from emp order by substr(job,length(job)-2);

4.在order by中使用case

Select ename,sal,job,comm from emp

order by

case when job=’salesman’

 then comm

else sal

       end;

动态改变对结果进行排序

Select ename,sal,job,comm,

case when job=’salesman’ then comm

else sal

        end as ordered

from emp order by 5;

5.union all把多个表的行组合在一起，注意对应列的数据类型要一致

Select enname as enname_and_dname,deptnofrom emp where deptno=10

Union all

Select ‘----------’,null from t1

Union all

Select dname,deptno from dept

//三个查询的结果叠加起来，不去重复，t1为有一个ID为1的记录

Union运算符可以筛选掉重复项，使用union大致等于使用unionall子句的查询结果使用distinct：

Select distinct deptno from(select deptnofrom emo union all select deptno from dept);

通常查询中不要使用distinct，所以，一般除非确有必要，使用union all，而不适用union

6.等值联接（equi-join），也就是内联接，将多个表进行笛卡尔积，

Select e.ename,d.loc,e.deptno,d.deptno fromemp e,dept d where e.deptno=de.deptno

也可以将联接逻辑放在from子句中，而不是where子句中，inner关键字可选

Select e.ename,d.loc,from emp einner join dept don(e.deptno=d.deptno);

7 intersect集合操作

 

8.从一个表中查找另一个表没有的值

Select deptno from dept except(minus)select deptno from emp;

Select deptno from dept where deptno not in(select deptno from emp);

因为in和not in 本质上是or和not or的操作，在sql中true or null 结果为true，false or null的结果是null，当结果中有一个null的时候，null就会延续下去。

要解决与not in和null有关的问题，可以使用not exists和相关子查询

Select d.deptno from deptd where not exists (select null from emp e where d.deptno=e.deptno);

9.在处理聚集与联接混合操作时，需要注意是否会产生重复行，可以用两种方法避免聚集函数计算错误，方法一，在调用聚集函数时使用关键字distinct，这样每个值参与计算一次；方法二，在进行联合前进行聚集操作（在内联视图中），这样，聚集计算已经在联接前完成了，就避免了聚集函数计算问题

10.insert into dept (deptno,dname,loc)values (50,‘Programing’，‘Baltim’)

插入别的表的数据：

Insert into dept_east (deptno,dname,loc)select deptno,dname,loc from dept where loc in(‘newyork’,’boston’);

复制表定义

Create table dept_2 like dept;

Create table dept_2 as select * from deptwhere 1=0

在使用create table as语句时，除非where后面的条件为false，此时就会生成一张空表

11.使用translate和replace将数字与字符分离

Select replace (

        Translate(data,’0123456789’,’0000000000’),’0’) ename,

        To_name(

         Replace(

         Translate(lower(data),

                  ‘abcdefghjklmnopqrstuvwxyz’,

                  Rpad(‘z’,26,’z’)),’z’))sal

From (

Select ename ||sal data from emp)