Oracle入门笔记(五)

一、外联查询与内联查询

外联：
   *不仅包括有关联关系的数据，还能够级联查询没有关联关系的数据、

   (1)左外联： left outer join 表名(新表/右表) on
      * 左表(旧表)能够关联右表的数据
         +左表关联不上右表的左表数据
      * 左表的数据全，右表的内容可能会出现空
      * outer 可以出现也可以不出现   

        select e.*,d.*
        from emp e
         left outer join dept d on e.deptno = d.deptno;

   (2)右外联： right outer join  on
      * 右表能够关联上左表的数据
         + 右表关联不上左表的右表数据

 

        select e.*,d.*
        from emp e
         right outer join dept d on e.deptno = d.deptno;        

-- 查询所有的员工的部门信息以及没有员工的部门信息

 

   select e.*,d.*
   from dept d
    left join emp e on d.deptno = e.deptno

 或者：

 

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

    (3)全外联：full  outer  join  on
          * 两张表能够关联的数据
          + 左表关联不上右表的左表数据
          + 右表关联不上左表的右表数据

 

      select e.*,d.*
             from emp e
             full outer join dept d on e.deptno = d.deptno;

内联查询：
    * 能够通过关联关系关联起来的数据叫做内联数据。

    join 新加入的表 on 关联关系

 

    多表连接：

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

   或者

    select e.*,d.* from emp e
        join dept d on e.deptno = d.deptno
          where 条件

 

二、子查询

子查询：
   * 一个查询的结果 是另一个查询的条件
   * in :  外层查询条件只需要满足子查询中的某一个值就可以
   Exec:   where  deptno in(10,20)
         等价于 where deptno = 10 or deptno =20
   * in的效率很低：条件值需要与查询结果中的值逐个匹配
   * not in : and的关系
           where  deptno not in(10,20)
           等价于 where deptno <> 10 and deptno <> 20
   * some/any :意思和in相近
      区别： in用在无符号的SQL
             some/any需要符号
   * all :比子查询结果中的所有值都大 都小
        大于最大 小于最小
   * exists :是否存在
       能否根据子查询查询到数据
      not exists : 只关系不成立的数据

           select d.* from dept d
           where exists(select e.* from emp e
                   where e.deptno=d.deptno);

------------------------------------------------------
-- 查询员工工资比部门20中所有员工都大的其他部门员工信息

 

 -- select max(sal) from emp where deptno = 20;
 
   select * from emp where sal>
        all(select sal from emp where deptno = 20);

-- 查询属于部门'SALES'的员工信息

 

    select e.* from emp e;
    select d.deptno from dept d where d.dname='SALES';

    select e.* from emp e
      where deptno= (select d.deptno from dept d
      where d.dname='SALES');

--------------------------------------------------
-- 查询部门名为'SALES'或者在'NEW YORK'工作的员工信息

 

    select e.* from emp e
      where deptno =some(select d.deptno from dept d
      where d.dname='SALES' or d.loc='NEW YORK');

-- 查询部门名为'SALES'或者在'NEW YORK'工作的员工信息

 

    select e.* from emp e
      where deptno in(select d.deptno from dept d
      where d.dname='SALES' or d.loc='NEW YORK')

三、序列(可以理解为mysql中的主键)

 

序列： sequence
    * 是一个有序的整理列值：能够通过调用这个序列对象
      返回一个整数值
    * 通常序列用来做表的主键列值
      Oracle没有主键自增特性，通过调用序列值来实现主键自增
    * 每次调用序列不论是否使用成功，都会自动增长

     创建序列：
            create sequence 序列名;
     完整的写法：
            create sequence SEQ_TEST    
            minvalue 1    -- 最小值 从哪开始生成
           maxvalue 100     --最大值
           start with 1    -- 下一次缓冲位置
           increment by 1   -- 每次增长多大
           cache 20    -- 缓冲大小
           cycle  -- 循环生成

       nextval : 序列生成的下一个可用值
       currval : 当前序列已经生成的值

四、视图

视图 : view
        * 视图是将编译后的查询语句存储在数据中。
 
 使用视图的好处：
  * 视图是编译后的查询语句，使用视图能够提高查询效率。
  * 视图能够屏蔽原表的字段，保护敏感字段不被私自访问。
  * 简单视图可以更新，复杂视图无法更新
     简单视图：数据来自于一张表
     复杂视图：数据来自于多张表
  * 视图中的数据动态的来自于源表

创建视图：

       create or replace view 视图名 as 查询结果;

  Exec:

       create or replace view v_emp
            as select empno,ename,sal from emp;

五、索引

 

索引： index
   * 索引类似目录一样，能够帮助我们提高查询效率的数据对象
   * 索引是被单独存放在索引块中
   * 如果对于增删改比较频繁的表，会影响表增删改的效率
      因为数据库会花费资源去维护索引块
   * 通常增删改较少并且查询比较频繁的表适合使用索引;
     数据量比较少也不适合使用索引;
     一张表数据量如果大于2GB ，查询频繁可以使用索引

创建索引：
  create index 索引名 on 表名(列名1,列名2....);
  

   （2）* 如果多个列起到一个索引的作用，复合索引。
 

    create index ind_emp on emp(ename);     --普通索引

    -- Drop indexes
    drop index IND_EMP;
    -- Create/Recreate indexes
    create index IND_EMP1 on EMP (ENAME);

索引类型：
  普通索引  index
  唯一性索引 unique index
      * 该列上不能出现重复值
  位图索引  bitmap index
      * 通常用在基数比较小的列上 （比如性别）
 

 

六、表空间     

表空间：
     * 有关数据库的一些数据对象(表 视图 回滚段 簇..)
       的存储单位
     
     *我们之间所说的数据对象是存储在用户中：
      因为每一个用户都有自己的默认表空间，而表空间
      都指向一些真实存在的数据文件。
 
 表空间的好处：
   *  解决与系统表空间的争用
   *  合理使用磁盘分布
   *  方便数据的备份和迁移

            数据存储：  最小的存储单位 ：数据块  2KB
                                       ||
                                    盘区
                                       ||
                                      段
                                       ||
                           表空间   <===== 数据文件

创建表空间：

create tablespace 表空间名 nologging
datafile '路径\et1505.dbf'
size 50m     --文件的初始大小
autoextend on next 50m   -- 每次扩充多大
maxsize 200m      ---文件最大多大
extent management local  -- 继承本地管理策略

删除表空间：

 

drop tablespace 表空间名 including contents and datafiles;

Exec:

 

drop tablespace et1504s including contents and datafiles;

修改用户的默认表空间：

 

alter user 用户名 default tablespace 表空间名;

题：
-- 创建用户etoak
-- 授权DBA
-- 进入etoak，创建表A 插入一些数据
-- 连接system， 创建表空间 etoakspace
-- 让etoak用户的默认表空间更改为 etoakspace
-- 再次进入etoak 创建表B 插入一些数据
-- 连接system,删除表空间etoakspace
 问：表A和表B谁还存在？ 是否仍然能够插入数据？