oracl--表管理，约束管理，序列管理

一：表操作 
 

       --创建用户

        create user learn_object identified by test; 

        --给予权限 
 
        grant dba to learn_object; 

 

创建表的方式：

        --第一种 创建表 
 
       create table tb_userinfo( 

            userid number primary key, --primary key表示主键 （唯一 并且不能为空） 
 
           username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 '' 

            sex number default 0   --默认值 default 值 当插入数据为空时 自动填上默认值 插入不为空的值 插入当前值 
       ) 

         第二种 复制表

           create table 表名称 as select * from 表 [where 1!=1]

         第三种 类型赋值法

            create type  userobj as object (
                  id number,
                  uname varchar2(20),
                 createDate date
           )
  
         create table userinfo_cpy_type of userobj

        --修改表名称 
 
       rename 旧表名to 新表名
 
        --修改列名 
 
       alter table userinfo rename column 旧列名 to 新列名
 
        --修改列类型 
 
       alter table userinfo modify username char(20)  
 
        --添加列 
 
       alter table userinfo add age number default 18 
 
        --删除列 
 
       alter table userinfo drop column age 

       --查询表的结构

           desc 表名  --命令下

           select * from all_tab_columns where table_name='USERINFO1'  --数组字典表（DD） 在sql语句中

二：删除操作 

   删除表：把表中所有的行和表结构都删除。 
 
    DROP TABLE 表名;--Oracle中删除表时并没有直接删除，只是放置到“回收站” 
 
   显示回收站中的对象：SHOW RECYCLEBIN; 
 
   恢复回收站中的表：FLASHBACK TABLE 表名 TO BEFORE DROP; 
 
   删除回收站中的表：PURGE TABLE 表名; 
 
   彻底删除 不进回收站：DROP TABLE 表名 PURGE; 
 

    截断表：删除表中所有的数据行，重置表的存储空间。 
 
    TRUNCATE TABLE 表名; 
 
      TRUNCATE TABLE 表名  和  delete from 表名 的区别 ： 
 

          1  truncate是ddl语句  delete 是dml语句 都可以删除数据行 

          2  truncate不需要提交事物 delete需要提交事物 

          3  truncate删除表数据及表的存储 无法恢复 delete删除 可以使用归档日志恢复   

          4  delete可以删除指定条件的记录 

          5  如果表结构损坏了 导致数据无法访问 必须重置表 使用truncate 因为delete 不会删除数据 只是隐藏数据 （查询的效率依然地下）

             如果需要根据条件删除 并且希望可以恢复 一般使用delete

 三 表分区 （了解）
 
           意义 ：可以容灾（出现事故后 还有一部分数据保留） 
 
      范围(range(字段名)) 
 
          范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。这种分区方式是最为常用的，并且分区键经常采用日期 
 
       格式: 
 
            create table XXX() partition by range(表中数值类型的字段名)(partition 分区名 values less than 具体值  [tablespace 表空间名] ,…) 

    列表(list(字段名)) 
 
            该分区的特点是某列的值只有几个 
 

           …partition by list(字段名) (partition 分区名 values (值列表)值  [tablespace 表空间名] ,…)) 

    哈希(hash) 
 
            分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。 
 
           …partition by hash(表中数值类型的字段名)(partition 分区 [tablespace 表空间名] ,…) 
 
            … partition by hash(表中数值类型的字段名)PARTITIONS n STORE IN (s1,….sn) 

    列表(list(字段名))

    复合分区(分区组合)  

四：约束 

      1》约束的查询方式

       --可以通过dd表来查询约束

       select * from all_constraints where table_name='USERINFO'

        约束的类型

         C (Check约束)

         P (主键约束)

         U （唯一约束）

         R (外键约束)

        --直接添加不为空的约束 

创建表时指定约束：

CREATE TABLE [方案名.]表名(

列名  列类型  [default 默认值] [列级约束类型],  …

[CONSTAINT 表级约束类型]     );

使用单独的SQL语句添加约束：

ALTER TABLE 表名
ADD [CONSTRAINT 约束名]  约束类型(要约束的列名);
ALTER TABLE 表名
ADD [CONSTRAINT 约束名] 
FOREIGN KEY (要添加外键约束的列名)

REFERENCES 目标表名(目标表的列名);

约束名的建议：约束类型_表名_字段名、FK_主表_从表_列名

删除约束：

ALTER TABLE 表名 DROP CONSTRAINT 约束名;

       2》not null 约束（列级）

       create table tb_userinfo( 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空） 

                   username varchar2(20) not null, --not null 表示当前的列 不允许插入 null 和 '' 
       ) 

        --constraints 约束名 可以指定约束名称 

如果不指定约束名称 系统会自动分配一个名称 

 列存在了 添加约束 需要考虑 真实的数据行是否和约束冲突 因为要满足一致性

        alter table userinfo drop constraints nu_age

        alter table userinfo modify age number [constraints nu_age]    not null

        --直接添加不为空的约束 给约束添加别名 
 

        3》主键约束（表级，列级）

       create table tb_userinfo( 
                    userid number primary key, --primary key表示主键 （唯一 并且不能为空） 
                   username varchar2(20) [constraint nullable_username] not null, --添加别名后 会添加一个别名对应的约束 
        ) 
        

        --主键 它的意义在于 添加一列 用于唯一标识当前行  uniqie约束 知识表示 当前的列不允许出现重复的的值

        --在技术上  主键唯一 不能为空  uniqie 唯一能为空

       alter table userinfo drop constraints SYS_C0011165

       alter table userinfo modify userid constraints pri_userid primary key

       4》check约束（表级，列级）

        --通过check的方式 添加不为空的约束  check的语法要和where条件一致         
 
        create table tb_userinfo( 

                    userid number primary key, --primary key表示主键 （唯一 并且不能为空） 

                   username varchar2(20) [constraint check_username]  check(username is not null) --通过添加约束的方式 添加not null 
        )     
 

        alter table userinfo modify sex VARCHAR2(20) constraints chk_sex check(sex in('女','男'))

        alter table userinfo modify age number constraints chk_age check(age between 1 and 100)
         
        --check约束必须满足where条件  不能使用子查询 
 
       create table tb_userinfo( 
 
               userid number primary key, 

               username varchar2(20) not null, 

               sex number constraint check_sex check(sex in(0,1)) 
        )    
 
        5》外键约束（表级，列级）
 

       --定义外键  外键的定义 必须能唯一定位到外键对应的记录 比如 知道学生信息后 就唯一确定了他所在的班级 

       --外键 如果某个表中存在一列 关联到另外一张的主键 这一列叫做外键列

       --如果某个列被添加了外键 外键列的值 必须是 引用表中主键的值 或者是null


       --班级表 
 

       create table tb_grade( 

           cid  number primary key, 

           cname varchar2(20) not null 
        ) 
 
        --cid number check(cid in(select cid from tb_grade)) 用于理解 
 

       create table tb_student( 

           sid number primary key, 

           sname varchar2(20) not null, 

           cid number [constraint FOR_STUDENT_CID] references TB_GRADE (CID)         
        ) 
 
        --外键的引用 必须通过alter table的方式来添加 
 
       alter table tb_student add constraint FOR_STUDENT_CID foreign key (CID)  references TB_GRADE (CID)         
 
        --删除外键必须通过名称  未定义名称时 需要通过      dba_constraints表去查询         
 
        alter table tb_student drop constraint SYS_C008553    
 
        --查询表所有的约束类型  C表示check约束 P代表主键  R代表外键
 
        select * from dba_constraints  where table_name='TB_STUDENT'                    
 
五：序列 

使用序列：通过序列的伪列来引用序列值

NEXTVAL：返回下一个序列值。可用做某个表的主键值

CURRVAL：返回当前序列值。仅用于查看

在查看序列的当前值，必须先通过检索序列的下一个值对序列进行初始化

示例：

select test_seq.nextval from dual;

select test_seq.currval from dual;

获取有关序列的信息：

从user_sequences视图中可以获取有关序列的信息。

删除序列：

DROP SEQUENCE 序列名;

        --创建序列 

minvalue表示范围中最小的那个值 

maxvalue表示范围中最大的那个值 

      start with 表示序列从1开始递增  increment by表示步值（每次累加值） 
 
       create sequence TB_GRADE_SEC 
 
        minvalue 1 
 
        maxvalue 999999999999999999999999999 
 
        start with 1 
 
        increment by 1 
 
        cache 20; 
 
        --查询当前值得下一个值 
 
       select TB_GRADE_SEC.NEXTVAL from dual; 
 
        --查询当前值 
 
       select TB_GRADE_SEC.Currval from dual; 
 
        select * from tb_grade; 
 
        --在insert语句中使用序列  一般使用 序列来控制 主键的值
 

       insert into tb_grade values(TB_GRADE_SEC.NEXTVAL,'test'); 

       insert into tb_student values((select max(stuid)+1 from tb_student),'张三',1)
 
       commit;               
 
        --删除序列 
 
       drop sequence tb_grade_sec