oracle子连接,Oracle -- 子查询,连接运算

一、子查询

-- 1.单行子查询

--查询与员工SMITH属于同一部门的员工信息

select * from emp where deptno =(select deptno from emp where ename='SMITH');

--2.多行子查询

-- 查询与部门10员工工作岗位相同的员工信息

select * from emp where job in(select job from emp where deptno=10);

-- 2.2 查询与部门10员工工作岗位不相同的员工信息

select * from emp where job not in(select job from emp where deptno=10);

-- 3.多列子查询

--查询与SMITH的部门和岗位都相同的员工信息

select * from emp where (deptno,job) =(select deptno,job from emp where ename='SMITH');

--子查询中all关键字的使用:

--4.显示工资比部门号30的所有员工的工资都要高的员工的,姓名,工资,部门号

-- 求出30号部门所有员工的工资

-- select sal from emp where deptno = 30;

select ename,sal,deptno from emp where sal > all (select sal from emp where deptno = 30);

--第二种方案

select ename,sal,deptno from emp where sal > (select max(sal) from emp where deptno = 30);

--子查询中any关键字的使用

--5.显示工资比部门号30的任意员工的工资都要低的员工的,姓名,工资,部门号

--select sal from emp where deptno = 30;

select ename,sal,deptno from emp where sal > any (select sal from emp where deptno = 30);

--第二种方案

select ename,sal,deptno from emp where sal > (select min(sal) from emp where deptno = 30);

--.内联视图子查询(分页查询)

--8.查询30号部门中工资排序前5名的员工信息。

/*

rownum是Oracle对查询结果集加的一个伪列 (强调:先要有结果集)。

简单的说 rownum 是对符合条件结果的序列号。它的值是从1开始的

但是他却不受到sql中order by排序的影响

*/

select rownum,e.* from ( select * from emp where deptno = 30 order by sal desc ) e where rownum <=5

--9.查询所有员工中工资排序在6到10名之间的员工信息。

/*

页数1,每页显示行数: 5

结束范围: <= 页数* 显示行数

开始范围: > (页数-1) * 显示行数

*/

select * from ( select rownum rn,e.* from ( select * from emp order by sal desc ) e where rownum <= 2 * 5 ) rn where rn > (2 - 1) * 5

/*笛卡尔积

笛卡尔积――是关系的乘法,

它将分别来自两个数据集合中的行

以所有可能的方式进行组合。

A = {1, 2, 3}

B = {4, 5, 6}

A * B = {

(1,4), (1, 5), (1, 6)

(2, 4), (2, 5), (2, 6)

(3, 4), (3, 5), (3, 6)

}

3 * 3 = 9

*/

--1.显示10部门下所有员工的部门名称,

--员工姓名,工资(emp,dept)

--select * from dept;

--select * from emp;

-- dept * emp = 4 * 14 = 56

select d.dname,e.ename,e.sal

from emp e,dept d -- 得到emp,dept数据的笛卡尔积

where e.deptno = d.deptno -- 通过where条件消除笛卡尔积

and e.deptno = 10;

--2.查询工资高于自己部门平均工资的员工的姓名,工资信息

-- 先求每个部门的平均工资

select deptno,avg(sal) dept_sal_avg from emp

group by deptno

-- 拿员工工资和员工所在部门的平均工资做比较

select e.ename,e.sal,dsa.dept_sal_avg from emp e,(

select deptno,avg(sal) dept_sal_avg from emp

group by deptno

) dsa where e.deptno = dsa.deptno and sal > dsa.dept_sal_avg

连接运算:

连接运算是由一个笛卡尔积运算和一个选取运算构成的。

首先用笛卡尔积完成对两个数据集合的乘运算,

然后对生成的结果集合进行选取运算,

确保只把分别来自两个数据集合并且具有重叠部分(符合选取运算)的行合并在一起。

left[outer] join(左连接)

返回包括左表(集合)中的所有记录和右表(集合)中联结字段相等的记录

right[outer] join(右连接)

返回包括右表中的所有记录和左表中联结字段相等的记录

inner join(等值连接)

只返回两个表中联结字段相等的行

-- 外连接:

-- 学生信息表

create table t_student(

f_id int,

f_name varchar2(30)

);

-- 创建一个考试成绩表(字段:成绩编号,科目,分数,学生编号)

create table t_exam(

f_id int,

f_subject varchar2(30),

f_score number(3,1),

f_student_id int

);

select * from t_exam;

select * from t_student;

insert into t_student values(1,'张三');

insert into t_student values(2,'李四');

insert into t_student values(3,'王五');

insert into t_student values(4,'赵六');

insert into t_exam values(1,'Java',99,1);

insert into t_exam values(2,'C++',99,1);

insert into t_exam values(3,'Java',98,2);

insert into t_exam values(4,'Java',59,3);

insert into t_exam values(5,'Java',49,5);

delete from t_exam;

--3.左外联接

select s.f_name,e.f_subject,e.f_score from t_student s

left join t_exam e

on

s.f_id = e.f_student_id;

--4.右外联接

select s.f_name,e.f_subject,e.f_score from t_student s

right join t_exam e

on

s.f_id = e.f_student_id;

--5.内(等值)连接

select s.f_name,e.f_subject,e.f_score from t_student s

inner join t_exam e

on

s.f_id = e.f_student_id;

/*

提示:

select * from emp e inner join dept d on e.deptno = d.deptno;

相当于:

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

*/

--6.自连接

--查询员工‘SMITH’ 的上级领导的姓名

select p.ename from emp e,emp p

where e.ename='SMITH' and e.mgr = p.empno;

--7.查询每一个学生的姓名、考试的科目数、平均成绩

/*

nvl(value1, replace_with)

功能:如果value1为NULL,则nvl函数返回replace_with的值,否则返回value1的值。

*/

select f_name,( select count(*) from t_exam where f_student_id = ts.f_id) subject_nums,

( select nvl(avg(f_score), 0) from t_exam where f_student_id = ts.f_id) avg_score

from t_student ts

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值