oracle 表查询笔记

简单查询

       

引用

从数据库中查询数据，需要执行SELECT语句。当执行select语句时，既可以查询所有列，也可以根据需求查询特定列；既可以使用表达式，也可以使用函数处理null。基本查询语句如下：
select *|{[distinct] column|expression [alias],...}
from table;
如上所示，table用于指定表名，column用于指定列名，expression用于指定表达式，alias用于指定列别名。

基本查询
查询所有列是指检索所有列的数据，在选择列表中直接指定星号(*)可以查询所有列。
1）查询特定列
根据所需求的column来指定查询。
2）排除重复行
用distinct关键字来排除表中重复的相同的行记录。
3）使用算术运算符 
通过数字列上使用(+、-、*、/)，可以取得应用程序所需要的特定结果。（使用的优先级和数学上相同）
4）处理null
   null表示未知值，它既不是空格也不是0，如果没有为特定列提供数据，并且该列没有默认值，那么其结果为：null
如果不处理null，那么其结果为空；
使用nvl()函数来处理null：当使用函数nvl（column，0）时，如果column存在数值，则函数返回其原有数值；如果column为null，则函数返回0。(将null值替换为0)
使用nvl2()函数，nvl2(column,column*2,column)时，如果column不是null，则返回column*2的结果值；如果column为null，则返回column的原值；（对不是null的值进行修改，null的值不处理）
5）连接字符串
使用“ | |”操作符连接字符串；
使用concat()连接字符串；concat(column,"字符")；
6）使用列别名
在查询select语句时，后面的column可以添加别名，使用as｜空格
2、限制查询
1）使用常规比较符
常规比较符包括：=,<>,!=,>=,<=,>,<;
当在where中使用数字时，既可以直接引用数字值，也可以用单引号引住数字值；
当在where子句中使用字符值时，必须引用单引号；
在使用日期值时，也必须用单引号；
2）其他比较符
between...and
该操作符用于指定特定范围的条件，其中在between操作符后面指定比较小指，在and操作符后指定较大值。

在where子句中使用like操作符
使用通配符%，当执行模糊查询时，通配符%用于表示0个或者多个字符；
"_"下通配符表示单个字符。

in操作服用于执行列表匹配操作。当列或表达式结果匹配于列表中的任一个值时，条件为true，如果 where子句中指定条件x in（y1，y2,y3）,那么该条件等同于x=y1,or x=y2, or x=y3.

is null操作符用于检测列或表达式的结果为null。如果结果为null，则返回true，否则返回false。

引用

注意：当与null进行比较时不要使用（＝）、不等于（<>）、操作符。尽管使用它们不会有任何语法错误，但条件总是false。

3）使用逻辑操作符
逻辑操作符包括：and、or、not，and操作符用于指定同时满足两个条件，or操作符用于指定满足多个条件的任意一个条件即可，而not操作符用于指定想法的条件。
在这三个逻辑操作符中的优先级从高到低：not，and，or；如果需要改变优先级，那么可以使用括号。

引用

何时分别使用and，or，not？
当执行SQL操作时，如果SQL语句结果必须同时满足于多个条件，那么需要使用逻辑操作符and；
如果SQL语句结果只需满足于多个条件的任一条件，那么就使用逻辑操作符or；
如果要返回不满足特定条件的语句结果，那么应该使用逻辑操作符not。

3、排序数据
1）单列排序
默认情况下，使用order by 执行排序操作时，数据是以升序方式排序的。当执行升序排序时，既可以在排序列后指定asc关键字，也可以不指定关键字；desc用于指定降序排序。

引用

注意：
－1、当select语句同时包含多个子句（where，group by,having,order by 等）时，order by 必须是最后一个语句。
－2、当以特定列执行升序排序时，如果排序列存在 null 行，那么 null 会显示在最后面；如果是降序时，null 行会显示在最前面。
使用列位置排序：
       当执行排序操作时，不仅可以指定列名，列别名进行排序，也可以按照列或表达式在选择表中的位置进行排序。
如果列名或表达式名称很长，那么使用列位置排序可以缩减排序语句长度。另外当使用 union、union all、intersect、minus 等集合操作符合并查询结果时，如果选择类表的列名不同，并且希望进行排序，那么必须使用列位置进行排序。
－3、基于多列进行排序；当以多列进行排序时，首先按照第一列进行排序，当第一列存在相同数据时，以第二列进行排序，以此类推。

复杂查询

引用

连接查询
1）相等连接
相等连接是指使用等值比较符(=)指定连接条件的连接查询，它主要用于检索主从表之间的关联数据。
语法如下：
SELECT table1.column,table2.column FROM table1,table2
WHERE table1.column1 = table2.column2;
如上所示：table1，table2用于指定表名，column、column1、column2则用于指定列明。
注：表别名用于简化连接查询并提高查询性能。当使用表别名时，表别名在表名后面定义，并且列前缀可以直接使用表别名。

引用

使用连接查询的注意事项*
－1、使用连接查询时，应该在列名前加表明作为前缀。但是，如果不同表之间列名不同，那么可以不加表名作为前缀；如果在不同表之间存在同名列，那么在列名之前必须加表明作为前缀，否则会因为列的二义性而报错；
－2、当使用连接查询时，必须在where子句中指定有效的连接条件（不同表的列之间进行连接）。如果不指定连接条件，或者指定了无效的连接条件，那么会导致生成笛卡儿集（X*Y）。

2）不等连接
不等连接是指使用除等值比较符之外的其他比价操作符执行连接查询，并且不等连接主要用于在不同表之间显示特定范围的信息。
3）自连接
自连接是指在同一张表之间的连接查询，它主要用在自参照表上显示上下级关系或者层次关系。自参照表是指在同一张表的不同列之间具有参照关系或主从关系的表。
注：因为自连接为在同一张表之间的连接查询，所以必须定义表别名。
4）外连接
外连接是标准连接的扩展，它不仅会返回满足连接条件的所有记录，而且还会返回不满足连接条件的部分记录。外连接是是使用(+)操作符来完成的。
注意：当使用(+)操作符执行外连接时，应该将该操作符放在显示较少行（完全满足连接条件行）的一端。
区分主从表，要根据题目具体要求，选择满足所有信息记录的表作为主表(驱动表)。主表的信息记录多余从表。

引用

"+"操作符注意事项
－1、当使用"+"操作符执行外连接时，如果在where子句中包含有多个条件，则必须在所有条件中都包含“+”操作符；
－2、“+”操作符只适用于列，而不能用在表达式上；
－3、“+”操作符不能与 or 和 in 操作符一起使用。

CROSS JOIN 连接用于生成两张表的笛卡儿集。
NATURAL JOIN连接用于基于两张表的同名列执行相等连接。
注：如果两张表的同名列数据类型不匹配，那么会显示错误信息。
用USING子句用于两张表具有多个同名列中的某个列名执行相等连接。
在建立相等连接时，如果连接列名称不同，那么需要使用ON子句指定连接条件和其他条件。
注：如果连接列的名称完成不同，那么可以使用natural join、USING子句或者ON子句建立相等连接。

引用

左连接用于返回满足连接条件的数据，以及不满足连接条件的左边的其他数据。通过LEFT［OUTER］JOIN选项来实现；
右连接同左连接相反；
完成连接用于返回连接条件的数据，以及不满足连接条件的左边表和右边表的其他数据。即为，两张表都为主表

数据分组
当开发数据库应用程序时，经常需要汇总表数据，以获得需要的数据信息，例如取得最大值、最小值、平均值和总和等。
数据分组是通过分组函数、group by 以及 having 等子句共同实现的。
1）常用分组函数
max.min.avg.sum 等函数，另外 count 函数中还可以引用表达式。因为分组函数会忽略 null 行，所以使用 count（表达式）会显示非 null 的总计行数。
2）使用 group by 和 having 子句
如果不使用 group by 子句，那么使用分组函数只能汇总表的所有行。为了对表行数据进行分组统计，需要使用 group by 子句，而为了限制分组显示结果，需要使用 having 子句。
注意：如果选择列表同时包含有列、表达式和分组函数，那么列和表示必须出现在 group by 子句中。

引用

数据分组的注意事项
－1、分组函数只能出现在选择列表、order by 子句、having 子句中。
－2、当使用分组函数时，会忽略 null 行。
－3、当使用分组函数时，可以在函数中指定 all 和 distinct 选项。其中 all 是默认选项，该选项表示统计所有行数据（包括重复值）；如果指定 distinct ，则只会统计不同行值。
－4、如果在 select 语句中同时包含有 group by、having 以及 order by 子句，那么必须将 order by 子句放在最后。
－5、如果选择列表包含有列、表达式和分组函数，那么列和表达式必须出现在 group by 子句中，否则会显示错误信息。
－6、当限制分组显示结果时，必须使用 having 子句，而不能在 where 子句中使用分组函数，否则会显示错误信息。

子查询
1）单行子查询
单行子查询是指只返回一行数据的子查询语句
2）多行子查询
多行子查询是指返回多行数据的子查询语句。当在 where 子句中使用多行子查询时，必须使用多行比较符（in、all、any）
注意：all 和 any 操作符不能单独使用，而只能于单行比较符（=，>，<，>=，<=，<>）结合使用。

子查询就是在一条SQL语句中嵌入select语句：（嵌套查询）
先执行子查询，子查询的返回结果作为主查询的条件，在执行主查询。
子查询只执行一遍；
若子查询的返回结果为多个值，oracle会去掉重复值之后，在将结果返回给主查询。（不需要在子查询中加入distinct）
关联子查询

引用

关联子查询采用的是循环（loop）的方式，执行步骤如下：
1）外部查询得到一条记录（查信先从outer表中读取数据）并将其传入到内部查询。
2）内部查询基于传入的值执行。
3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值完成查询，若符合条件，outer表中得到的那条记录就放入结果集，否则放弃。
4）重复执行步骤1－2，直道把outer表中的所有记录判断一遍。

引用

exists 采用的是循环（loop）方式，判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可。
1）外部查询得到一条记录（查询先从outer表中读取数据）并将其传入到内部查询的表。
2）对inner表中的记录一次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回true，将outer表中的记录放入结果集中，若扫描了全部记录，没有任何一条记录符合匹配条件，返回false，outer表中的该记录被过滤掉，不能出现在结果集中。
3）重复执行步骤1－2，直道把outer表中的所有记录判断一遍。

not exists
NOT EXISTS 操作符用于检测子查询是否存在返回行。如果子查询返回行，则条件值为 false；如果子查询不返回行，则条件值为true。
标量子查询

标量子查询是指没行只返回一个列值的子查询，标量子查询表达式的值是子查询选择列表项的值。如果子查询返回0行，则标量子查询表达式的值为 null；如果子查询返回超过1行，则会显示错误信息。标量子查询适用于一下情况：
1）DECODE 和 CASE的条件部分和表达式部分；
2）除 group by 子句之外的所有 select 其他子句；
3）在 update 语句 的 set 和  where 子句中比较操作符的左边。