Oracle笔记 :基本查询(select)

varchar和varchar2：varchar（可变字符串）是oracle7以前使用的，不支持各国的语言，varchar2支持。

oralce里面定义了一张空表dual，里面只有一个字段dummy（空的），此表可以用来计算各种表达式。

oracle中，任何含有空值的数学表达式的结果都为空值。

字符串连接符：|| 相当于java中的+；

空值在sql里面也是关键字，查找时应该为 字段 is null；

 

显示表的信息（字段）：desc 表名;

select语句：

基本查询：

显示表的内容：

 

select * from 表名;

 

显示当前系统时间：

 

select sysdate from dual;

 

去重复（关键字作用于所有查询字段）：

 

select distinct 字段 from 表名;   
//当查询的为多个字段（用逗号隔开）时，查询出的单个字段可能有重复，但多个字段组合肯定无重复。

 

去重复（关键字作用于某个查询字段）：

 

select distinct（字段） from 表名;  

 

显示的数据后加字符串：

 

select name || "aaa" from 表名; 
//输出的结果就类似于：许阳“aaa“，若所添加的字符串本身含单引号，则把单引号改为2个单引号就可打印出个一单引号。

 

限制条件where：

 

select 字段 from 表名 where 过滤条件。
//where后的过滤条件：大于：> 小于：< 不等于：>< 数值之间（包含）：between and 为空：is null 不为空：not is null 或者：or 多个数据中： in 模糊查询：like

 

查询多表：

 

select A.字段 b.字段 from 表A,表B where 过滤条件。

 

分组Group by：

 

select 字段 from 表名 where 过滤条件 group by 字段。

 

过滤分组Having：

select * from 表名 where 条件 group by having 某字段 过滤条件 值；
//可以过滤group by以后的数据。

排序order by：

select * from 表名 where 条件 order by 字段 desc/asc（降序/升序）。
//order by 和group by可以联合使用。

子查询(在另一个select的结果集中查询的语句叫子查询)：

 

select  字段 from 表名 where 字段 in （select 字段 from 表2 where 条件）; 

 

联合查询：指对多个查询所获得的结果集进行集合操作。包括union，union all，intersect，minus。

select 字段 from 表A 关键字 select 字段 from 表B ;
//两表结构（字段和类型）完全相同，查找的字段也得完全相同。
union：求并集，去掉重复的值。
union all：同union，不去重复的值。
intersect：取交集。
minus：求差集，返回第一个集合存在而第二个集合中不存在的值。
四个关键字的优先级相同，可混合使用（先取两表并集，用并集取和第三张表的交集……）。 

自然连接：作用于两张表有相同列的表，相当于拼接两张表的数据。关键字为natural join

//表A：id,name,age,studentNo  表B：id,studentNo,classNo,gradeNo
//若要将数据对应的合并起来，应该写如下语句：
select studentNo(公共关键字) a.name a.age, b.classNo,b.geadeNo  from A a natural join B b;
//公共关键字不属于任何一个表，否则报错。查询两表需要的字段，将natural join后的表合并到natural join前的表。
//此处oracle会查找studentNo相等的列然后合并，相当于在查询语句后省略了where a.studentNo = b.studentNo; 

内连接：自然连接强制使用两表的公共列作为搜索条件，且两值必须相等。所以有极大限制。内连接则不需要。关键字为inner join

 

//表A：id,name,age,studentNo  表B：id,stNo,classNo,gradeNo
select a.name,a.age,a.studentNo,b.classNo,b.gradeNo from A a inner(可以省略不写) join B b on a.studentNo = b.stNo;
//相比于自然连接的好处就在于不用有公共列，可以手动指定限制条件（在on后面）
//oralce默认使用的就是内连接。

 

此SQL语句等价于：

 

select a.name,a.age,a.studentNo,b.classNo,b.gradeNo from A a,B b where a.studentNo = b.stNo;

 

那为什么还要使用内连接呢？这是因为当from 后面跟多个表的时候，oracle会自动优化，表的顺序会被打乱，而join后跟的表顺序是不会被打乱的。

外连接：内连接所指定的两个数据源处于平等地位（就是说肯定有某个列有关系，比如上面的studentNo和stNo，虽然列名不一样，但是实质上数据是相同的），外连接则是将一个数据源作为基础，将另一个数据源与之进行条件匹配。即使条件不匹配，基础数据源中的数据还是会出现在结果集中。根据不同的数据源，可分为左连接（left join）和右连接（right join）。

//表A：id，name，age，studentNo 表B：id，stNo，classNo，gradeNo
左连接：select a.name,a.age,a.studentNo,b.classNo,b.gradeNo from A a left join B b on a.studentNo = b.stNo;
//from后为数据源。
//假设刚开学，有的学生只是报名了，那么此时A表中就应该有信息，但是B表中还没有，使用内查询的话就会显示出A,B两表同时有记录的数据。
//此时只报名没有分配班级的学生就不会显示出来。而使用左连接则A表的数据会全部显示，B表中有的可以匹配上的自动合并，没有就为空。

类似于下图：

 

右连接：select a.name,a.age,a.studentNo,b.classNo,b.gradeNo from A a right join B b on a.studentNo = b.stNo;
//于左连接相反，join后跟的是数据源。

//假设毕业了，有的学生走了，那么此时A中的数据被删除，但是B表中还没有，则查询出来的记录就和上图相反。（例子太牵强，凑合着看）

 

外连接的简略写法：

外连接使用left join 和right join用来判断数据源，on相当于where。因此，oracle提供了简便的写法，在where 条件后面将表的列使用（+）作为标注，被标注的为附属表。从而可以省略left/right join 以及on的关键字。

所以上面的右连接sql就改成：

 

select a.name,a.age,a.studentNo,b.classNo,b.gradeNo from A a ,B b where a.studentNo(+) = b.stNo;

 

那为什么还要使用left/right join 以及on的关键字呢，这是因为当所要查询的表很多的时候，用关键字就比标注的sql维护起来容易的多了。各有优劣。

全连接：指进行一次左连接和一次右连接，把数据取并集并去除重复的数据。关键字full join ... on

 

//还是如上述AB表，全连接不分数据源表和附属表。
select a.name,a.age,a.studentNo,b.classNo,b.gradeNo from A a full join B b on a.studentNo = b.stNo;
//查询出的数据就会是有得没有后两个字段，有的没有前三个字段。
//过程相当于取A，B两表的左连接，然后取两表的右连接，对两个结果集进行union操作。