oracle辅导(2--1)

表连接（关联查寻）

 

多表查询时不加where子句，过滤条件是无效的条件，会产生两表之间记录的相互逐条匹配（组合），产生无效的结果（笛卡尔积）。

 

注意：表连接时，要注意查询的表之间的字段关系，表之间的字段所表示的信息的关系

 

等值连接

 

  select [表别名1.字段名1]，[表别名2.字段名2],...

  from 表1 表别名1 ，表2 表别名2

  where 表别名1.字段名3=表别名2.字段名4;

 

  表连接时，表与表之间有同名字段，可以加上表的别名，加以区分，用表名.字段名或表别名.字段名（列名）。表的字段名是唯一，不用加表名.或表的别名。

 

注意：为表起了别名，就不能再使用表名.字段名。

例：select a.first_name,a.last_name,b.name

from s_emp a,s_dept b

where a.dept_id=b.id;

 

非等值连接

 

  select [表别名1.字段名1]，[表别名2.字段名2],...

  from 表1 表别名1 ，表2 表别名2

  where 表别名1.字段名3 ..... 表别名2.字段名4

 

  ....可以使比较运算符，也可以使其他的除了'='的运算符

 

例：select e.ename, d.grade,e.sal

from emp e,salgrade d

where e.sal between d.losal and d.hisal;

 

自连接

 

用两个别名把一张表中的数据分成两部分，然后在使用条件过滤。

 

 select [表别名1.字段名1]，[表别名2.字段名2],...

  from 表1 表别名1 ，表1 表别名2

  where 表别名1.字段名3=表别名2.字段名4;

 

例：select a.first_name ename,b.first_name cname from s_emp a,s_emp b where a.manager_id=b.id;

 

以上所提到的表连接，都叫做内连接，严格匹配两表的记录。

 

外连接

 

外连接会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，数据库会模拟出记录去和那些不匹配的记录匹配。

 

例：select a.first_name enamei,a.id,b.first_name cname,b.id from s_emp a,s_emp b where a.manager_id=b.id(+);

 

注意：要把那一方的记录全部都显示出来，还有注意条件(+)跟在要全部显示的那个表的字段后。

 

组函数

 

group 组

group by 分组子句，按指定的分组规则分组 ，这个group by 子句可以跟在 select 语句后或是 having后面。group by子句也会出发排序操作，会按分组字段排序。

 

select [组函数或分组的字段名] ，... from 表名 group by [字段名1],[字段名2],.....；

 

例：select avg(salary) from s_emp group by dept_id;

 

注意：组函数可以处理一组数据，返回一个值。组函数会忽略空值。count()除外，他会把空记录也记录在内。

 

avg(..),求平均值，sum(..),求和 这两个函数的参数只能是number型的。

 

以下所提到的函数可以使用任意类型做参数。

count(..)，用来统计记录数，可以使用排重命令。count(...)默认使用的是all。

max(..),min(..)求最大值和最小值，

count(*),统计表中记录数。

 

例：select max(b.name),avg(a.salary), max(c.name) from s_emp a,s_dept b,s_region c where a.dept_id=b.id and b.region_id=c.id group by b.id;

 

注意：只要写了group by子句，select后就只能用group by后的字段或者是组函数。

      where子句只能够过滤记录。

 

having子句可以过滤组函数结果或是分组后的记录，且写在group by子句后。

 

例:

 select max(b.name),avg(a.salary), max(c.name)

 from s_emp a,s_dept b,s_region c

where a.dept_id=b.id and b.region_id=c.id

group by b.id having sum(a.salary)>4000;

 

column 也可以定义有别名的列的格式。

column "别名" 格式定义

 

注意：要先过滤掉不需要的记录，然后再进行分组操作，提高效率。 

子查询

 

子查询，就是可以嵌在任何的sql语句中的select语句。

 

在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。

 

注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值）。配合使用子查询返回的结果必须符合运算符的用法。 

例:

select first_name,title from s_emp where title=any(select title from s_emp where last_name='Smith') and upper(last_name)!='SMITH';

select first_name,title from s_emp where title in (select title from s_emp where last_name='Smith') and upper(last_name)!='SMITH';