oracle笔记系列（五）

第十二章: 修改表和约束(alter语句)

    测试的表:
    create table t_user(
            id number constraint user_id_pk primary key,
            name varchar2(100),
            salary number
    );
    drop table t_user;

    //在表中添加一个新的列
    alter table t_user    add birthday date;

    //删除表的某列
    alter table t_user   drop column birthday;

    //给表中的列添加约束,这个约束相当于之前的表级约束
    alter table t_user
    add constraint user_name_un
    unique(name);
    
    //测试刚添加的唯一约束是否生效
    insert into t_user(id,name) values(1,'zs');
    insert into t_user(id,name) values(2,'zs');
    
    //删除表中的约束
    alter table t_user
    drop constraint user_name_un;

    //修改表的名字:
    rename t_user to mytest;
    rename mytest to t_user;

    //修改表中某列的类型
    alter table t_user  modify (name varchar2(500));

    //让约束失效:必须知道约束的名字
    alter table t_user
    disable constraint user_id_pk cascade;
    
    //测试是否设置成功
    insert into t_user(id,name) values(1,'zs1');
    insert into t_user(id,name) values(1,'zs2');


    //让失效的约束再次生效
    alter table t_user
    enable constraint user_id_pk;


    //截断表中的数据(删除),不需要提交,默认已经提交,并且不能回滚
        truncate table t_user;
    相当于: delete from t_user;  commit;

    comment on table t_user is '很好';//给表添加注释
   
    comment on column t_user.name is 'good';//给列添加注释
 
    select * from user_tab_comments where table_name=upper('t_user');   //查看表中注释
  
    select * from user_col_comments  //查看列中的注释
    where  comments is not null
    and  table_name=upper('t_user');

第十三章: 序列

    Sequence 序列:  作用是帮我们生成主键列的值(非空唯一)

    创建序列:  一般不需要设置sequence的属性,使用默认的方式去创建就可以了.
    create sequence 序列名;

    如果需要设置属性,那么就加上下面的语句.
    [increment by n]  每次拿出值加多少
    [start with n]    初始值从几开始
    [{maxvalue n | nomaxvalue}]  最大值
    [{minvalue n | nominvalue}]  最小值
    [{cycle | nocycle}]  到了最大值后是否循环(如果循环会从1开始)
    [{cachen | nocache}] 每次在缓存里面放多少个值.


    例如:创建序列并设置属性
    create sequence seq_test
    increment by 2
    start with 45
    maxvalue 60
    cycle
    nocache;
    
    drop sequence seq_test;

    例如:创建序列使用默认属性
    create sequence seq_test;
  
    对应序列,我们只有俩种操作:
        ①获得序列中的下一个值  这个值对于当前这个序列的其他值来说,肯定是非空唯一
            select seq_test.nextval  from dual;
        ②查询序列中当前的值是多少
            select seq_test.currval  from dual;
    
    向t_user表插入数据,其中id值可以需要生成
        create table t_user(
            id number constraint user_id_pk primary key,
            name varchar2(100),
            salary number
        );
        drop table t_user;
    
    //创建序列
    drop sequence t_user_seq;
    create sequence t_user_seq;
    //插入数据 使用序列产生id值
    insert into t_user(id,name,salary) values(t_user_seq.nextval,'tom',2000);  

    通过数据字典查询当前用户的序列
     select sequence_name  from user_sequences;
    

第十四章: 视图view

    视图就是提取一张或者多张表的数据生成一个映射，操作视图可以达到操作原表的效果，方便数据的管理以及安全操作。

    视图的作用:   ①隐藏表中的重要数据  ②代替一些比较长的sql语句

    视图分为2类:
        ①简单视图:  视图所代表的sql中如果没有group by语句,没有组函数,查询的只有一张表,那么这样的视图就是简单视图.
        ②复杂视图：  视图所代表的sql中如果有group by语句,或者有组函数,或者查询的是多张表,那么这样的视图就是复杂视图.

    简单视图和复杂视图的区别:  通过简单视图可以修改原来表中的数据,通过复杂视图是不能修改原来的数据的。

    创建视图：
        create or replace view 视图名字
        as
        sql语句
    
    删除视图： drop view 视图名字;


    测试表:
    create table t_user(
        id number constraint user_id_pk primary key,
        name varchar2(100),
        salary number
    );
    drop table t_user;
    
    插入数据:
    insert into t_user(id,name,salary)
    select id,last_name,salary
    from s_emp;
    //创建视图
    create or replace view v_test
    as
    select *
    from t_user
    where id > 10;
    //查看视图内容
    select * from v_test;

    //可以通过*简单视图*对原来的表进行数据的删除/更新/插入
    delete from v_test where id=16;
    update v_test set name = 'zhangsan' where id = 20;
    insert into v_test(id,name,salary) values(28,'tom1',3000);   
    
     with read only语句
        特点:只能通过视图进行查询数据,不能修改
        例如:
        create or replace view v_test
        as
        select *
        from t_user
        where id > 10
        with read only;    
        这个视图v_test将来只能查询,不能进行修改


     with check option语句
        特点:通过视图进行的修改 那么也必须可以通过这个视图能够显示出来,要不然就操作失败

    例如:
        //测试用的表及其数据
        drop table t_user;
        create table t_user(
            id number constraint user_id_pk primary key,
            name varchar2(100),
            salary number
        );
        insert into t_user values(1,'tom',1000);
        insert into t_user values(2,'tom2',2000);       
       // 创建视图:
        create or replace view v_test
        as
        select id,name,salary
        from t_user
        where id=2
        with check option;      
        //查询视图中的数据
        select * from v_test;

        //插入报错 因为这个操作通过视图显示不出来
        insert into v_test values(3,'tom3',3000);
        //更新失败 因为这个操作通过视图显示不出来
        update v_test    set name='lily'    where id=1;
        //更新成功 因为这个操作通过视图可以显示出来
        update v_test  set name='lily'  where id=2;

    
    复杂视图
        例如:
            create or replace view v_test
            as
            select avg(salary)
            from t_user       
        复杂视图只能用来查询,不能修改

第十五章: 索引(index)

    索引的概念：
        ①类似书的目录结构
        ②Oracle 的"索引"是一种对象，是与表关联的可选对象，能提高SQL查询语句的速度
        ③索引直接指向包含所查询值的行的位置，减少磁盘I/O
        ④索引和表是相互独立的物理结构

        ⑤Oracle 自动使用并维护索引，插入、删除、更新表后，自动更新索引

    索引的创建：
        ①自动创建： 当在表中指定了primary Key或者unique约束时会自动创建唯一值索引。

        ②用户创建： 用户可以创建非唯一值索引以提高在访问数据时的效率。

        语法：
            create index 索引名
            on 表名(列名);
        例如：
             create index emp_index
             on s_emp(last_name);
        
        删除索引:  drop index 索引名;
        例如:  drop index emp_index;

        创建成功后可以通过如下语句查看：
         select index_name from user_indexes;

    给某列创建索引的原则：
        ①列经常作为where子句的限定条件或者作为连接条件
        ②列包含的数据量很大，并且很多非空的值。
        ③两个或者更多列频繁的组合在一起作为where的限定条件或者连接条件
        ④列总是作为搜索条件
        ⑤索引查出的数据量占2%~4%
        ⑥索引不是越多越好，不是索引越多越能加速查找。
        ⑦要索引的表不经常进行修改操作
    
    注意:
        ①在表中的某一个合适的列加入上了索引,那么也只有在数据量很大的时候,才能有所体现出这个查询的效率.
        ②索引一旦建立成功,那么之后这个索引就由系统来管理,我们自己是控制不了的.

    索引的种类:
        Single column  单行索引
        Concatenated   多行索引
        Unique           唯一索引
        NonUnique      非唯一索引   

    索引结构分为:
         B-tree(默认是这种结构)：  ①适合大量的增、删、改（OLTP）；  ②不能用包含OR操作符的查询；  ③适合高基数的列（唯一值多）；  ④典型的树状结构；
        位图： ① 做UPDATE代价非常高；    ②非常适合OR操作符的查询；
        反序
        函数

第十六章: 用户权限控制

    1.创建用户：  create user user_name identified by password
        例如:    create user zhangsan identified by zhangsan;

    2.删除用户：  drop user zhangsan cascade;

    3.赋予权限： grant privilege to user;
      例如:  把建表 建序列 建视图的权限赋给zhangsan
        grant create table, create sequence,create view to zhangsan;

        //把connect角色和resource角色赋给zhangsan（角色是一组权限的集合）
        grant connect,resource to zhangsan;

         注意: 只是登陆oracle数据库的话 需要的权限是create session
        
    4.修改密码：  alter user user_name identified by password;
        例如:    alter user zhangsan identified by zhangsan123;

    5.赋予某一个用户某种对象操作的权限： grant operator on object to user;       
      例如:  grant select   on t_user   to briup;

    6.回收权限：  revoke operator on object from user;
      例如:   revoke select   on t_user    from briup;
        
    7.创建同义词synonym
       作用:  可以隐藏表原来的名字
       类别:  私有同义词 公共同义词

        //给表t_user创建一个私有同义词
        create synonym my_test   for t_user;
        
        //给用户briup授权可以查询my_test
        grant select  on my_test  to briup;

        //收回用户briup查询my_test的权限
        revoke select  on my_test  from briup;
        
        //利用数据字典查看同义词synonyms，用户创建的同义词有哪些
        select synonym_name  from user_synonyms;

        //用户有权利查询的同义词有哪些
        select synonym_name  from all_synonyms;

        //用户有权利查询的同义词有哪些是以字母D开头的（表中的数据都是大写存在）
        select synonym_name
        from all_synonyms
        where synonym_name like 'D%';
        结果可以看到我们常用的dual

    8.删除同义词synonym：  drop synonym name;
   例如:  ①删除私有同义词：  drop synonym my_test;
            ②删除公共同义词：  drop public synonym my_test;
    
    9.公共的同义词
        因为普通用户没有创建public synonym的权限,所有我们需要用dba的身份登录到数据库中去创建。
        sqlplus "/as sysdba" 或者system用户登录

        create public synonym my_test2  for test.t_user;（ test.t_user表示的是test用户下面的t_user表）

      //让所有人都有查询这个同义词的权限，然后其他用户登录之后就可以通过这个公共的同义词来查询test用户下面的t_user表
      grant select on my_test2 to public;
        
    10,数据库的导入导出
        系统终端执行
          导出：exp   根据提示按回车下一步即可
          导入：imp      根据提示按回车下一步即可