Oracle学习基础（二）总结

落日映苍穹つ 2022-02-27 23:56 338阅读 0赞

/*
      本节知识用到的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;