Oracle学习笔记(三)

一、子查询

        子查询分为单行子查询和多行子查询

        子查询(内查询)、主查询(外查询),子查询要包含在括号内

       1、单行子查询:只返回一条记录

              单行比较操作符

 

             

 

       2、多行子查询:返回多条记录

             

 

             IN:在集合中(在里面的都算)

             ANY:和集合中的任意一个值进行比较

 

                          (1)小于某集合中的任意一个值,就是小于集合中的最大值。
                          (2)大于某集合中的任意一个值,就是大于最小值。

             ALL:和集合中的所有值进行比较

                          (1)小于某集合中的所有值,就是小于最小值。
                          (2)大于某集合中的所有值,就是大于最大值。

       3、相关子查询:子查询中的条件要引用主查询的值

 

注意点:

      1、单行操作符对应单行子查询,多行操作符对应多行子查询

      2、在主查询的select, from, where, having 都可以放子查询,在select中放子查询时,要求只能是单行子查询。

 

<span style="font-family:Microsoft YaHei;">-- 找到薪水大于本部门平均薪水的员工。 
select * from emp e1 where sal > ( select avg(sal) from emp e2 where e2.deptno=e1.deptno ) 

-- 找到员工表中工资最高的前三名的员工信息 
select rownum, empno, ename, sal from (select empno, ename, sal from emp order by sal desc) where rownum<=3</span>

 

二、笛卡尔集

 

三、多表查询

        多表查询有:等值连接、不等值连接、外连接、自连接,下面按Oracle和SQL99标准来分别说明多表查询

       多表查询会先生成笛卡尔积条记录,再从这个记录中去取我们需要的,连接n个表至少需要n-1个连接条件

       主键:本表的主键

       外键:在本表中加入其他表的主键字段被称为外键

       内连接:合并具有同一列的两个以上的表的行,结果集中不包含一个表与另一个表不匹配的行

       外连接:两个表在连接过程中除了返回满足连接条件的行以外还返回左(或右)表中不满足条件的行,这种连接称为左(或右)外连接,没有匹配的行时,结果表中相应的列为空,外连接的where子句条件类似于内部连接,但连接条件中没有匹配行的表的列后面要加外连接运算符,即用圆括号括起来的加号(+)。

       左外连接:右边没有左边对应的数据则在右边写上(+)

       右外连接:左边没有右边对应的数据则在左边写上(+)

       总之那边数据少了,就在那边写(+)

 

       1、Oracle连接

       (1)内连接:等值连接和不等值连接

-- 等值连接:查询员工信息,要求显示员工的编号,姓名,月薪和部门名称
select e.empno,e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno;

-- 不等值连接:查询员工的工资级别:编号 姓名 月薪和级别
select e.empno,e.ename,e.sal,s.grade
from emp e,salgrade s
where e.sal between s.losal and s.hisal;

       (2)外连接

                  外连接的符号是(+)

                 左外连接的写法: where e.deptno=d.deptno(+),当左边存在记录,而右边无对应记录时,在右边补上“(+)”
                 右外连接的写法: where e.deptno(+)=d.deptno,当左边无对应记录,而右边有记录时,在左边不上“(+)”

                要理解“(+)”的位置:以左外连接为例,因为左边需要返回更多的记录,右表就需要“加上”更多的记录,所以在右表的链接条件上加上“(+)”

                 

                 

      (3)自连接

                  自连接:利用表的别名,将同一张表视为多张表

-- 查询员工信息:xxx的老板是yyy
select e.ename||'的老板是'||b.ename
from emp e, emp b
where e.mgr=b.empno;

       2、SQL1999连接

           (1)内连接:两边都有的才显示

--SQL99内连接实现,inner可省略
select e.*, d.*
from emp e
inner join dept d on e.deptno=d.deptno

          (2)左外连接:左边有值右边无值时左边的也全部显示

--SQL99左外连接实现,outer可省略
select e.*, d.*
from emp e
left outer join dept d on e.deptno=d.deptno

          (3)右外连接:右边有值左边无值时右边的也全部显示

--SQL99右外连接实现,outer可省略
select e.*, d.*
from emp e		
right outer join dept d on e.deptno=d.deptno

 

          (4)满外连接:任意一边有值就会显示

 

--SQL99满外连接,outer可省略
select e.*, d.*
from emp e
full outer join dept d on e.deptno=d.deptno

 

          (5)交叉连接:就是笛卡尔积

 

--SQL99交叉连接
select e.*, d.*
from emp e
cross join dept d

 

四、集合运算

 

        集合运算包括并集、交集、差集

       1、并集(Union / Union All)

             (1)Union:返回两个集合去掉重复元素后的所有记录

             (2)Union All:返回两个集合的所有记录,包括重复的记录(用的少)

--查询属于部门10与部门20的所有员工信息。
select * from emp where deptno=10
union
select * from emp where deptno=20

--查询工资在500~1500或在1000~2000范围的员工信息(这是两个工资级别)。
select * from emp where sal between 500 and 1500
union all
select * from emp where sal between 1000 and 2000

 

       2、交集(Intersect)

 

            Intersect返回同时属于两个集合的记录

<span style="font-family:Microsoft YaHei;">--查询工资在500~1500又在1000~2000范围的员工信息
select empno, ename, sal from emp where sal between 500 and 1500
Intersect
select empno, ename, sal from emp where sal between 1000 and 2000
order by empno</span>

 

      3、差集(Minus)
            Minus返回属于第一个集合,但不属于第二个集合的所有记录

 

<span style="font-family:Microsoft YaHei;">--查询属于500~1500但不属于1000~2000范围的员工信息。
select empno, ename, sal from emp where sal between 500 and 1500
Minus
select empno, ename, sal from emp where sal between 1000 and 2000
order by empno

--查询属于1000~2000但不属于500~1500范围的员工信息。
select empno, ename, sal from emp where sal between 1000 and 2000
Minus
select empno, ename, sal from emp where sal between 500 and 1500
order by empno</span>


      注意:
          1、在两个Select语句中参数类型和个数要一致,如果不一致,需要想办法补齐。

 

                例如要补个字符串,不能写个'a'、'b'等,要不影响结果,应补一个null,还要指定类型。如果是字符串,可以写 to_char(null); 如果要补数字

 

                类型,则写 to_number(null)

          2、结果集采用第一个select的表头作为表头。在第一个select上起别名才有用。在后面的select上起别名就没有用。
          3、如果有order by子句,必须放到最后一句查询语句后。

<span style="font-family:Microsoft YaHei;">--第一个SELECT语句中参数类型的别名与第二个的不一致,但结果采用的是第一个
select empno, ename, sal 薪水 from emp where sal between 500 and 1500
union
select empno, ename, sal 工资 from emp where sal between 1000 and 2000
order by 1

--查询结果
     EMPNO ENAME            薪水
---------- ---------- ----------
      7369 SMITH             800
      7499 ALLEN            1600


--两个SELECT的参数个数不一致,少的需要手动补齐
select empno, ename, sal from emp where sal between 500 and 1500
union
select deptno, dname, to_number(null) from dept

--查询结果
     EMPNO ENAME            SAL
---------- -------------- -----
        30 SALES
        40 OPERATIONS
      7369 SMITH            800
      7521 WARD             1250</span>

 

 

 

 

 

 

 

 

 

 

转载于:https://my.oschina.net/weslie/blog/472008

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值