SQL语句总结杂记【收集中】

表test只有ID和NAME字段，包含三行数据。

一、select null from test;和select * from test;对比：

1、SQL〉select * from test；

ID   NAME

---   -----------

1      a

2      b

3      c

已返回3行

2、SQL〉select null from test；

NULL

-----------

已返回3行

说明：null表示select语句执行后获得了结果集，只是不显示内容在屏幕上，但在屏幕上每个数据行还是占了空间。

二、SQL〉select ‘you’ from test;（字符串）或者 SQL〉select 50  from test;（数值）

注释：表test有几行数据，该sql语句就返回几行。

1、SQL〉select  ‘you’ from test；

YOU

-----------

you

you

you

已返回3行

2、SQL〉select 50 from test；

50

-----------

50

50

50

已返回3行

三、not exists和exists的意思

两者一般用在where子句中或having子句中。

exists（select * from B）表示select * from B返回的结果集存在（即（select * from B）exists），也就说，select * from B返回的结果集不为空的，此时，exists（select * from B）返回的布尔值为真。若select * from B返回的结果集为空的，则exists（select * from B）这个条件就不成立，即exists（select * from B）返回的布尔值为假。

not  exists（select * from B）表示select * from B返回的结果集不存在（即（select * from B）not exists），也就说，select * from B返回的结果集为空的，此时，not exists（select * from B）返回的布尔值为真。若select * from B返回的结果集为不空的，则not exists（select * from B）这个条件就不成立，即not exists（select * from B）返回的布尔值为假。

例如，
表A
ID NAME 
1   A1
2   A2
3   A3

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

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)
执行结果为
1 A1
2 A2
原因可以按照如下分析：

带有exists（或not exists）子句时，我们要对表A的每一行进行分析，即从表A拿出一行来，看此时exists（或not exists）子句返回的布尔值是否为真，为真，则表A拿出来的这一行就可以放入结果集中。

表A中id=1的数据行时，

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=１)
中的SELECT * FROM B WHERE A.ID=B.AID即变为SELECT * FROM B WHERE B.AID=１，而SELECT * FROM B WHERE B.AID=１是有结果集返回的，所以

EXISTS (SELECT * FROM B WHERE A.ID=B.AID)为真，故而SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)有了一条数据行，

即1 A1，返回。

表A中id=2的数据行时，

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
中的SELECT * FROM B WHERE A.ID=B.AID即变为SELECT * FROM B WHERE B.AID=2，而SELECT * FROM B WHERE B.AID=2是有结果集返回的，所以

EXISTS (SELECT * FROM B WHERE A.ID=B.AID)为真，故而SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)有了一条数据行，

即2 A2，返回。

表A中id=3的数据行时，

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
中的SELECT * FROM B WHERE A.ID=B.AID即变为SELECT * FROM B WHERE B.AID=3，而SELECT * FROM B WHERE B.AID=3是没有结果集返回的，所以

EXISTS (SELECT * FROM B WHERE A.ID=B.AID)为假，故而SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID=B.AID)没有一条数据行，

返回。

四、双引号和单引号在SQL语句中的应用范围

表A
ID NAME 
1   A1
2   A2
3   A3

1、双引号用于表名和列名，还有他们的别名

例如，select “ID”   “Id”，“Ta”.name   from  "A"   'Ta';

若是select ‘ID’  “Id”，name   from a;，则结果为：

Id

---

ID

ID

ID

注释：单引号下字符串ID不是表示表A的列名ID了。同时，也可以看出非列名或表名的单引号下的字符串也可以有自己的别名。

若是select "ID"  'Id'，name   from  a;，则会提示出错。

若是select  id，name   from  'A';，则会提示出错。

若是select  “abD”，“Ta”.name   from  "A"   'Ta';，则会提示 ora-000904: “abD”标识符无效。

因为表A中不存在列名为abD的列。

2、单引号则只能用于表示非列名（或表名）及其别名的字符串上

例如，select id||'tao'  from  a;

ID||'tao'

---------

1tao

2tao

3tao

3、SQL语句中用到的函数里的参数若是字符串，则要用单引号，而不是双引号来表示字符串

     select dump（‘abc’） from dual；

4、select  "Ta".id  "order" , "Ta"."NAME"from a  "Ta" where exists(select * from a where id = "Ta"."order");

该例句，说明加引号的别名或表名（列名）在他处使用时也要带上引号。

注释：

       默认时（即不带引号时），表名，列名，还有他们的别名都是以大写形式存入表中的。

           带引号时，表名，列名，还有他们的别名都是以原样形式（即输入大写，存入表中的也是大写；输入小写，存入表中的也是小写）存入表中的。

          例如，下面的例子。

           默认时（即不带引号时），

           SQL〉select id，name from  a；//表名，列名以小写输入

           ID NAME     //以大写形式存入表中的
            1   A1
            2   A2
            3   A3

            带引号时，

             SQL〉select id，“namE” from  a；

             ora-000904:“namE”标识符无效

             因为在表A中已有的列名name是全部大写的，与namE是不一样的，所以Oracle认为namE不是表A的列名。

     

五、别名的作用域：

              1.1、 列名的别名

        在嵌套查询中，内层查询的列名的别名可以作用于外层的子查询中，但反之不行。（因为总是先得出内层的结果集，再得出外层的。得出内层的结果集时，内层列名不会释放的，直到整个SQL语句执行结束，外层列名还未开始分配使用））若外层查询的列名的别名与内层的子查询中的列名的别名同名的话，则在内层的子查询中，起作用的是内层的子查询中的列名的别名。此时，该列名的在外层时就有歧义的，要列名的别名前加上表名。

       例如，表t1和t2的列名都是ID和NAME两个字段.

SQL> select t2. * from t2,(select * from t1 ) d where d.id <id; //id因为内层的t1和外层t2都有名为id的列名，

                                                                          所以列名（或别名）就有歧义的， 要别名前加上表名。
select t2. * from t2,(select * from t1 ) d where d.id <id
                                                       *
第 1 行出现错误:
ORA-00918: 未明确定义列

SQL> select t2. * from t2,(select * from t1 ) d where d.id <t2.id;

        ID NAME
---------- --------------------
         2 yourlove
         3 eygle
         4 game
         3 eygle
         4 game
         4 game

已选择6行。

SQL> select  * from t2,(select * from t1 ) d where d.id <t2.id;

        ID NAME                         ID NAME
---------- -------------------- ---------- --------------------
         2 yourlove                      1 about
         3 eygle                         1 about
         4 game                          1 about
         3 eygle                         2 yourlove
         4 game                          2 yourlove
         4 game                          3 eygle

已选择6行。

SQL> select  * from t2,(select id you from t1 ) d where d.you <t2.id;

        ID NAME                        YOU
---------- -------------------- ----------
         2 yourlove                      1
         3 eygle                         1
         4 game                          1
         3 eygle                         2
         4 game                          2
         4 game                          3

已选择6行。

SQL> select  * from t2,(select id you from t1 ) d where you <t2.id;

        ID NAME                        YOU
---------- -------------------- ----------
         2 yourlove                      1
         3 eygle                         1
         4 game                          1
         3 eygle                         2
         4 game                          2
         4 game                          3

已选择6行。

SQL> select  * from t2,(select id you from t1 ) d where you <id;

        ID NAME                        YOU
---------- -------------------- ----------
         2 yourlove                      1
         3 eygle                         1
         4 game                          1
         3 eygle                         2
         4 game                          2
         4 game                          3

已选择6行。

SQL>

 1.2、 表名的别名   

         在嵌套查询中，   

         首先，内层子查询的表名的别名能否作用于外层，要分成两种情况来讲：

           一是，如果内层子查询位于from子句里，则内层的的表名的别名是能作用于外层的非from子句的位置（如where子句）。

以下为通过row_number() over(...)删除重复数据的例子，仅供参考：

delete from acc_fundnav
where rowid in (select row1
                   from (select rowid row1,
                                row_number() over(partition by HOST_ID order by rowid) lev
                           from acc_fundnav)
                  where lev > 1)

其中，select row1
                   from(select rowid row1,
                                row_number() over(partition by HOST_ID order by rowid)lev
                           from acc_fundnav)
                  where lev > 1

二是，如果内层子查询位于非from子句里（如where子句），则内层的的表名的别名是不能作用于外层的。   （在where或from定义，在select使用？不行？）

 

至于，外层的的表名的别名能否作用于内层，也是相同情况，要分成两种情况来讲：

         一是，如果内层子查询位于from子句里，则外层的的表名的别名是不能作用于内层的。

例如，表t1有ID和NAME两个字段，则
select   a. *   from t1 a，（select  * from t1 b where b.id <a.id）会提示“A”."ID"：无效标识符。

         二是，如果内层子查询位于非from子句里（如where子句），则外层的的表名的别名是能作用于内层的。

例如，表t1有ID和NAME两个字段，则
select   a. *   from t1 a where id not in（select  id from t1 b where b.id <a.id）执行成功。

         总之，表的别名（无论是内层，还是外层的）要能跨层作用，要符合以下条件：

           在同一层次的select语句上，定义表的别名的子句（或者定义表的别名的子查询所在的子句）要早于(并列也不行)使用该表的别名的子句（或者使用该表的别名的子查询所在的子句）。如，在from子句中定义，在where子句中使用，这里from和where属于同一层次的。

                    列名的别名所使用的规律和表的别名索总结的规律一样。

          表的别名内外层同名歧义与列名的别名情况一样应该。

          还有，无论（select）查询语句是否嵌套，表名的别名只能在from子句里定义，列名的别名只能在select子句里定义。

        另外，对“select语句中（在同一层次上）的各个部分子句”或是“在同一层次的select语句”这一个概念做出说明：

      select  ta.id,ta.name  from （select * from  t1 where id〉5） ta   where  ta.id 〉10；

     其中，select  ta.id,ta.name子句、from （select * from  t1 where id〉5） ta子句
和where  ta.id 〉10子句是出于同一层次的。

      而lect *子句、 from  t1 子句和where id〉5子句是出于同一层次的。

     

注释：

  因为oracle（的CBO ）在解析select语句（SQL语句）时，总是先从最外层的from子句得出原始的表内的行集合（为区别于结果集，结果集指的是执行一个select语句所得的结果，该select语句无论是嵌套还是子查询的）。所以from里定义的别名，在之后执行where子句时就是已知的。这里的这个表名的别名只有在最外层select子句执行后，才会释放，相当于一个程序里的全局变量。（参看本文的第七大点）

（因为总是先得出内层的结果集，再得出外层的。得出内层的结果集时，内层表名已经释放，外层表名还未开始分配使用）

   其实，无论是别名的作用域，还是如select语句里面各个子句的执行顺序，都是由执行计划决定，更确切地说，由oracle的优化器（CBO）决定。优化器决定具体的执行计划。

             2、  列名的别名只能作用的select子句和order by子句中，不作用于其他地方。（因为sql语句首先执行from部分即多表的连接，接着是where等from的字句，最后才是select部分，再最后才是order by子句中）

        SQL> select  id num,name from t2 order by num;     可以执行

       SQL> select  id num,name from t2group by num ;   不可以执行

      SQL> select  id num,name from t2 where  num 〉2;   不可以执行

       因为列名的别名是在select 子句中定义的，所以在select 子句前执行的子句里由于该列名的别名还未定义，所以不认识该列名的别名，即该列名的别名作用域不在此处；在select 子句后执行的子句里由于该列名的别名已经定义，所以认识该列名的别名，即该列名的别名作用域在此处。这样，由列名的作用域可以暴露出子句的执行顺序。

 

六、分组函数可以放在select后和having后，不可放在where后，其他子句的还没试过的，应该是不可以的。

       而单行函数则可以放在SQL语句的任何位置。

       having是group by子句的从属子句，having后只能跟出现在group by子句里的字段（即列名）或是分组函数。

七、在非嵌套（即只有单层）情况下，select语句中（在同一层次上）的各个部分子句的执行顺序：

     首先执行from部分即多表的连接，

      接着执行下面两个的顺序可以是任意的，因为他们的关系是并列关系：where子句或是group by+having子句，不过我们一般的思路是先where子句，再group by+having子句。对于group by+having子句的内部执行顺序是先group by，后having，

      [接着执行下面三个的顺序可以是任意的，因为他们的关系是并列关系：where子句或是group by+having子句或是Oder by子句，不过我们一般的思路是先where子句，再group by+having子句，最后Oder by子句，对于group by+having子句的内部执行顺序是先group by，后having，]

     接着是执行select语句中的关于rownum的限制条件部分（即除select子句外的含有rownum的字句）。

----见：《只需在order by 的字段上加主键或索引即可让oracle先按该字段排序，然后再用rownum标号》

    再接着是执行select子句。

    最后才是执行order by子句。

    如果order by子句里的字段被设置了主键约束或是被设置索引了，那么order by子句执行之后，oracle系统还会重新对结果集的rownum值进行编号。---见：《rownum的特点以及它与order by 子句的执行顺序关系》

注释：

1、因为列名的别名是在select 子句中定义的，所以在select 子句前执行的子句里由于该列名的别名还未定义，所以不认识该列名的别名，即该列名的别名作用域不在此处；在select 子句后执行的子句里由于该列名的别名已经定义，所以认识该列名的别名，即该列名的别名作用域在此处。这样，由列名的作用域可以暴露出子句的执行顺序。

2、这里，若没有特殊说明，表里的各字段都是普通的字段，即没有加上索引或是约束之类的东西的。

3、 其实，无论是别名的作用域，还是如select语句里面各个子句的执行顺序，都是由执行计划决定，更确切地说，由oracle的优化器（CBO）决定。优化器决定具体的执行计划。

八、嵌套查询内层使用了多表连接的形式时，若内层select使用*且外层select也使用*，则会出错，提示外层select使用的*为未明确定义列，原因不清楚（原因就是因为表t1和t2的列名都是ID和NAME两个字段，即两个表的列名重名了，不是因为多表连接的关系）。

 

 

SQL> select t2. * from t2,(select*from t1) d where d.id <t2.id;

        ID NAME
---------- --------------------
         2 yourlove
         3 eygle
         4 game
         3 eygle
         4 game
         4 game

已选择6行。

SQL> select *from (select* from t1，t2) d where d.id <t2.id;

select  * from (select * from t1，t2 ) d where d.id <t2.id;

             *

第 1 行出现错误:
ORA-00918: 未明确定义列

如下例子是可以执行的：

SQL> select  d.country from (select * from t1，t2 ) d where d.id <t2.id;

可以执行（假设t2有country字段）

 

总结：

     当SQL语句为select* from t1，t2；时可以执行，是因为虽然表t1和t2两个表的列名重名了，但是此时oracle系统认为from子句里有两个表（是两个表的连接），oracle系统可以区分同名的列名是来自哪个表的。

     当SQL语句为select *from (select* from t1，t2)； 时不可以执行，是因为此时oracle系统认为from子句里只有一个表，即(select* from t1，t2)，而在这一个表里有名字相同的两列，所以就会提示ORA-00918: 未明确定义列。