开发者社区> 科技小能手> 正文

Oracle 10g数据库基础之基本查询语句-下-连接&子查询

简介:
+关注继续查看

 Oracle 10g数据库基础之基本查询语句-下-连接&子查询

--资料参考:张烈 张建中《数据库管理员培训讲义》
实验18:表的连接查询
该实验的目的是掌握基本的联合查询.
表的连接
我们要从多张表中要得到信息,就得以一定的条件将表连接在一起查询。

Cartesian(笛卡儿)连接

当多张表在一起查询时,没有给定正确的连接条件,结果是第一张表的所有行和第二张表的所有行进行矩阵相乘,得到n*m行的结果集。

一般来说笛卡儿连接不是我们需要的结果。
但表如果有一行的情况下,结果有可能正确。

SQL> select ename,dname from emp,dept;

 
ENAME      DNAME

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

SMITH      ACCOUNTING
ALLEN      ACCOUNTING
……..
SMITH      RESEARCH
ALLEN      RESEARCH
……..
SMITH      SALES
ALLEN      SALES
……..
SMITH      OPERATIONS
ALLEN      OPERATIONS
……..
 
56 rows selected
 
SQL>

结果为每个员工在每个部门上了一次班,4*14=56,这并不是我们想得到的结果。

要避免笛卡儿连接一定要给定一个正确的连接条件。
等值连接
在连接中给定一个相等的连接条件。
SQL> select ename,dname from emp,dept where emp.deptno=dept.deptno;

当列的名称在两张表内重复的时候,要加表的前缀来区分,避免不明确的定义。
表的别名
1。便于书写
2。将同名的表区分
3。一旦定义了别名,表的本名就无效
4。只在该语句内有效
5。定义方式为表名后紧跟别名,用空各间隔。

SQL> select ename,dname from emp e,dept d where e.deptno=d.deptno;

SQL99的书写方式

select ename,dname from emp e join dept d on (e.deptno=d.deptno)

效率是相同的,SQL99是国标

列的别名,为了区分相同的列的名称,这是别名的本质。
SQL> select ename,dname,e.deptno,d.deptno

  from emp e,dept d

  where e.deptno=d.deptno;

上述显示有两个列名称都叫deptno,我们无法区分。

SQL> select ename,dname,e.deptno "员工表",d.deptno "部门表"

  from emp e,dept d
  where e.deptno=d.deptno;

不等连接
连接条件不是一个相等的条件。

SQL> select ename,sal,grade

  from emp,salgrade
  where sal between LOSAL and hisal;

外键连接
将一张表有,而另一张表没有的行也显示出来。

SQL> select ename,dname,emp.deptno from emp,dept

where emp.deptno=dept.deptno;

这句话不会显示40号部门,因为40部门没有员工。

SQL> select ename,dname,dept.deptno from emp,dept where emp.deptno(+)=dept.deptno;

+号的意思为将没有员工的部门,用NULL来匹配

+号不能同时放在等号的两边,只能出现在一边。

自连接
表的一列和同一个表的另一列作为连接的条件。

SQL> select w.ename "下级" ,m.ename "上级"

  from emp w,emp m
  where w.mgr=m.empno(+);

其中“下级”“上级”为列的别名。区分相同的列。

W为表的别名。区分相同的表。别名的本质。

+)为了将没有上级的人也显示。

过滤结果

想在结果中过滤去一些内容请用and运算。

SQL> select w.ename "下级" ,m.ename "上级"

  from emp w,emp m
  where w.mgr=m.empno(+)
  and w.deptno=30;

实验19:sql99规则的表连接操作

该实验的目的是掌握新的ORACLE表之间的联合查询语法.

SQL99规则的书写格式

. Nature(自然)连接

这是SQL99规则。

所有同名的列都作为等值条件。
同名的列的数据类型必须匹配。
列的名称前不能加表的前缀。

SQL> select ename,deptno,dname from emp natural join dept;

Using指定列的连接
当有多列同名,但想用其中某一列作为连接条件时使用。
SQL> select ename,deptno ,dname from emp join dept using (deptno);

SQL99的外键连接
SQL99写法

select ename,dname,dept.deptno

from dept left outer join emp

on(dept.deptno=emp.deptno);
9I前的写法

select ename,dname from emp,dept

where emp.deptno(+)=dept.deptno;

SQL> select ename,dname,dept.deptno
  from dept left outer join emp
  on(dept.deptno=emp.deptno);

知识点
1。笛卡儿连接
2。等值连接
3。不等连接
4。外键连接
5。自连接

6SQL99的书写格式

实验20:子查询
该实验的目的是掌握子查询的语法和概念.
 子查询
谁的工资最多

简单子查询
1。先于主查询执行。
2。主查询调用了子查询的结果。
3。注意列的个数和类型要匹配。
4。子查询返回多行要用多行关系运算操作。
5。子查询要用括号括起来。

SQL> --查询工资总和高于10号部门工资总和的部门。

SQL> select deptno,sum(sal)

  from emp
  group by deptno

  having sum(sal)>(select sum(sal) from emp where deptno=10);

SQL> --查询每个部门的最大工资是谁。

SQL> select deptno,ename,sal from emp

  where (deptno,sal) in

  (select deptno,max(sal) from emp group by deptno);

子查询返回多行,用=不可以,得用in

子查询返回多列,所以对比的列也要匹配。
Any all操作

SQL> select ename,sal from emp where sal<any(1000,2000);

小于2000就可以

SQL> select ename,sal from emp where sal<all(1000,2000);

必须小于1000

小于all小于最小,大于all大于最大

SQL> select ename,sal,deptno from emp

where sal<all(select avg(sal) from emp group by deptno);

小于any小于最大,大于any大于最小

select ename,sal,deptno from emp

where sal>any(select avg(sal) from emp group by deptno)

From子句中的子查询
查询工资大于本部门平均工资的员工。

select ename,e.deptno,sal,asal

from emp e,

(select deptno ,avg(sal) asal from emp group by deptno) a

where e.deptno=a.deptno and sal>asal;

A为视图,为什么要使用别名asal,因为表达式不能当列的名称,别名的本质使用方法是使非法的合法化。

相互关联的子查询

select ename,sal ,deptno

from emp o

where sal>(select avg(sal) from emp where deptno=o.deptno) ;

先运行主查询,得到第一行,将DEPTNO传入到子查询,由子查询求出AVGSAL),在判定主查询的行是否符合查询的条件。

执行计划是将子查询看作视图的关联。这叫做SQL的自动改写。

Exists操作

找领导,其中3是常量,你写什么都可以。

当子查询有行时,Exists返回true

查到行后就不再继续查询

当子查询没有行时为假,Exists返回false

select ename,empno,mgr from emp o

where exists(select 3 from emp where mgr=o.empno);

《完》

--xjzhujunjie




本文转自xjzhujunjie 51CTO博客,原文链接:http://blog.51cto.com/xjzhujunjie/868102

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云数据库推出SQL Server 2016 单机基础版本
2017年9月,云数据库推出SQL Server 2016版本,本文是2016单机基础版本的产品形态说明。
4526 0
数据库&nbsp;连接&nbsp;查询
数据库&nbsp;连接&nbsp;查询
26 0
mysql数据库(9):常用查询的例子(下)
mysql数据库(9):常用查询的例子
28 0
Spring Boot 2.x基础教程:使用国产数据库连接池Druid
Spring Boot 2.x基础教程:使用国产数据库连接池Druid
89 0
MySQL---数据库从入门走向大神系列(四)-子查询、表与表之间的关系
本篇博客讲解(子查询)非相关子查询/相关子查询,一对一,一对多,多对一,多对的的关系! 准备: 首先我们创建一列sex。再为部分行设置好值0(女)或者1(男); delete from stud where age=26; 删除年龄为26的行。
918 0
MySQL 基础---单表数据记录查询
**查询数据记录操作: ** 简单数据记录查询 条件数据记录查询 排序数据记录查询 限制数据记录查询 统计函数和分组数据记录查询 测试表 # 创建雇员表 create table t_employee( empno int(1...
820 0
MySQL 基础---多表数据查询
多表数据记录查询: 关系数据的各种操作 内连接查询 外连接查询 子查询 关系数据操作 传统的运算: 并(UNION) 笛卡尔积(CARTESIAN PRODUCT) 专门针对数据库操作的运算---连接(JOIN) (1) 并(UNION) “并”就是把具有相同字段数目和字段类型的表合并到一起。
753 0
oracle数据库 如何查询某个表的约束条件
使用下面语句从all_constraints视图中查看某表上的约束: SELECT constraint_name, table_name, r_owner, r_constraint_name FROM all_constraints WHERE table_name = 'table_name' and owner = 'owner_name'; 另一个视图ALL_CONS_COLUMNS也包括组成表上约束列的信息。
685 0
23705
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载