Oracle学习基础（二）总结

/*
  本节知识用到的Oracle自带表：emp(员工表),dept(部门表),salgrade(工资等级表)
      select * from emp;
      select * from dept;
      select * from salgrade;
      
*/

  多表查询：
      笛卡尔积：实际上是两张表的乘积，实际意义不大。
   格式：select * from 表1,表2
 

--笛卡尔积
select * from emp;--14条
select * from dept;--4条
select * from emp,dept;--56条数据
select * from emp,dept where emp.deptno =dept.deptno --14条

---

    内联接：
       隐式内链接：
              等值内联接： where e1.deptno = d1.deptno
              不等值内联接：where e1.deptno<> d1.deptno
              自链接：自己连接自己  form emp e1,emp e2 where e1.empno=e2.mgr
       显示内联接：
              select * from 表1 inner join 表2 on 联接条件
               内联接的inner关键字可以省略。                    
 

 select * from emp e1, dept d1 where e1.deptno <> d1.deptno

--查询员工编号,员工姓名,员工的部门名称,员工的工资等级,经理的编号,经理的姓名,经理的部门名称,经理的工资等级
--将工资等级 1,2,3,4 显示成 中文的 一级 二级 三级...（隐式内联接的运用）
select e1.empno,
       e1.ename,
       d1.dname,
       case s1.grade
            when 1 then '一级'
            when 2 then '二级'
            when 3 then '三级'
            when 4 then '四级'
            else
              '五级'
            end "等级",
      e1.mgr,
      m1.ename,
      d2.dname,
      decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "等级"
from emp e1,emp m1,dept d1,dept d2,salgrade s1,salgrade s2
where 
     e1.mgr=m1.empno
and e1.deptno =d1.deptno
and m1.deptno =d2.deptno
and e1.sal between s1.losal and s1.hisal
and m1.sal between s2.losal and s2.hisal;

---

    外连接：（标准，通用写法）
        左外连接：left outer join .. on .. 显示左表所有的记录，如果右表没有对应记录，就显示空
        右外连接：right outer join... on...显示右表所有的记录，如果左表没有对应记录，就显示空
        outer可以省略不写。
    Oracle中的外连接：(+)实际上是如果没有对应记录就加上空值
        select * from emp e1,dept d1 where e1.deptno(+)=d1.deptno
    即：以d1为基础表显示e1表，如果没有对应就为空。（哪个加(+)另外一个就是基础表）

--以emp表为基础表的左外连接
select * from emp e1 left join dept d1 on e1.deptno=d1.deptno
--以dept为基础表的左外连接
select * from dept d1 left outer join emp e1 on e1.deptno=d1.deptno

---

   子查询：查询语句嵌套查询语句；用来解决复杂的查询问题
       分类：按照子查询返回的条目数，分为：单行子查询和多行子查询
       单行子查询：(单行操作符)
                      > >= = < <= <> !=
       多行子查询：（多行操作符）
                      in(),not in(),any(),all(),exists(), not exists()
                  通常情况下，数据库中不要出现null,最好的方法加上not null,
                  null值并不代表不占空间，一个null占char(100)，也就是一百个字符的空间。
      
 

--查询最高工资的的员工信息（单行子查询）
---1.查询出最高工资
select max(sal) from emp;--5880.6
---2.工资等于最高工资的员工信息
select * from emp where sal=(select max(sal) from emp)

--查询领导信息(多行子查询)
---1.查询所有经理的信息
select mgr from emp;
select distinct mgr from emp;--去除重复的经理信息
---2.结果
select * from emp where empno in(select distinct mgr from emp)


--查询出比20号部门所有员工薪资高的员工信息 10 20 30
--------（单行子查询做题）
---1.20号部门最高工资
select max(sal) from emp where deptno=20;
---2.员工信息
select * from  emp where sal > (select max(sal) from emp where deptno=20)
-------(多行子查询做题)
---1.20号部门所有员工薪资
select sal from emp where deptno=20;
---2.大于集合所有的工资的员工信息
select * from emp where sal > all(select sal from emp where deptno=20)

---

   exists(查询语句)：存在的意思。
       作用：判断一张表的记录是否存在于另外一张表中。
    返回值是布尔类型：当查询语句有结果时返回true,否则返回false。
   
   数据量比较大的时候非常高效的。
 

--查询有员工的部门信息。（exists()的使用）
select * from dept d1 where exists(select * from emp e1 where e1.deptno = d1.deptno );

---

    rownum:伪列，系统自动生成的一列，用来表示行号。
    rownum是Oracle中特有的用来表示行号的，默认值/起始值是1 ，在每查询出结果之后，再添加1.
    rownum最好不要做大于判断，可以做小于判断--->> 如果做大于判断容易造成没有初始值的结果。
    SQL执行顺序：
          from... where ... group by ...having...select ...rownum...order by...limit...
          

--查询rownum大于2的所有记录
select rownum,e1.* from emp e1 where rownum>2; -----没有任何记录(rownum没有起始值。)
--查询rownum大于等于1的所有记录 
select rownum,e1.* from emp e1 where rownum >=1;---14条记录
--查询rownum < 6 的所有记录
select rownum,e1.* from emp e1 where rownum < 6;--5条记录


--rownum 排序
select rownum,e1.* from emp e1 order by sal;---rownum排序会乱，原因是rownum添加过早。

--------找到员工表中工资最高的前三名(rownum的分页)
--1.工资降序排列emp表
select e1.* from emp e1 order by sal desc;
--2.将上面的结果当作一张表处理,此时添加rownum,取前三，再查询
select rownum, t1.* from (select e1.* from emp e1 order by sal desc) t1 where rownum<4;

       关联子查询（子查询语句依赖别的表），非关联子查询（子查询可以独立执行）。
      

--关联子查询：此处后面语句中的e.deptno=e2.deptno是有关联的。不能独立执行。
select * from emp e where sal > (select avg(sal) from emp e2 group by deptno having e.deptno=e2.deptno);

     rowid:伪列，每行记录所存放的真实物理地址（十六进制显示）。
     rownum:行号,每查询记录后就添加一个行号。（如果指定rownum>2,此时rownum不是从1开始，所以没有起始值可以rownum++）

----去除表中重复记录，并保留rowid最小的记录。（rowid的真实使用）
--1.创建测试表
create table p(
       name varchar2(10)
);
--2.添加多个相同的数据，可以多执行几遍
insert into p values('黄伟福');
insert into p values('赵洪');
insert into p values('杨华');
---3.写出删除语句
delete from p where 
---4.查询测试表的所有信息和rowid
select rowid,p.* from p;
---5.找出rowid最小的列
select min(rowid) from p;
---6.查询出不同名字中，rowid不是最小的数据
select p1.* from p p1 where p1.rowid>(select min(rowid) from p p2 where p1.name=p2.name )
---7.删除第6步中数据
delete from p p1 where p1.rowid>(select min(rowid) from p p2 where p1.name=p2.name )

     rownum:分页查询
     在oracle中只能使用子查询做分页查询
 

----查询第6条到第10条数据
--1.查询出标上rownum的数据
select rownum,emp.* from emp;
--2.between...and ...分页
select * from (select rownum hanghao, emp.* from emp) tt where tt.hanghao between 6 and 10; 

     集合运算：
         并集：将两个查询结果进行合并。
         交集：将两个查询结果取交集。
         差集：将两个查询结果相减。


         并集运算：
              union:去除重复的，并且排序。
              union all:不会去除重复的。
         注意点：
　　　　     1，Union可以对字段名不同但数据类型相同的结果集进行合并；
　　　　     2，如果字段名不同的结果集进行Union，那么对此字段的Order by子句将失效。
　　　　       这里一起总结一下：
　　　　     3.Union，对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；
　　　　       Union All，对两个结果集进行并集操作，包括重复行，不进行排序；2016-12-23
　　　       4.可以在最后一个结果集中指定Order by子句改变排序方式

---显示工资大于1500或者20号部门下的员工（排除重复）。
select * from emp where sal > 1500
union
select * from emp where deptno = 20;

--显示工资大于1500或者20号部门下的员工（不排除重复）。
select * from emp where sal > 1500
union all
select * from emp where deptno = 20;

     交集运算：intersect
 

----工资大于1500,并且20号部门下的员工
select * from emp where sal>1500
intersect
select * from emp where deptno=20

     差集运算：minus
     

--1981年入职员工(不包括总裁和经理)
---1.查询1981年入职的员工
select * from emp where to_char(hiredate,'yyyy')='1981';
---2.查询出总裁和经理
select * from emp where job='PRESIDENT' or job='MANAGER';
---3.两者做差集运算得出结果
select * from emp where to_char(hiredate,'yyyy')='1981'
minus
select * from emp where job='PRESIDENT' or job='MANAGER';

   集合运算中的注意点：
       1.列的类型要一致，不然会报错。
       2.按照顺序写。（保证类型一一对应）
       3.列的数量要一致，如果不足，用空值null填充。
 

--正常输出：
select ename,sal from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
--列的类型不匹配（报错）
select ename,sal from emp where sal > 1500
union
select sal,ename from emp where deptno = 20;
--列的数量不匹配
--情况一：少列（报错）
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal from emp where deptno = 20;
--情况二：null填充（正常输出）
select ename,sal,deptno from emp where sal > 1500
union
select ename,sal，null from emp where deptno = 20;