Oracle基础知识6

Oracle二维表管理–创建、维护、删除表

Oracle创建表

创建表并同时添加相应的约束条件,下面将分为两个板块进行介绍。常用的完整性约束条件有 NOT NULLuniqueprimary keyforeign keycheck 短语等,具体解释请往下看。

创表常用类型

常见格式:

1
create table 表名(字段名 数据类型, 字段名 数据类型, ...);

常用的数据类型如下:

  • number 类型
    • 数值类型
      • number(a) 总长度为a
      • number(a,b) 总长度为a, 小数位长度为b,小数位可以不写
  • varchar2 类型
    • 可变字符类型 varchar2(ln) ln表示字符的最大长度,实际存储内存长度是根据字符大小来分配,但最大长度不能够超过ln。
    • 特点:动态分配存储空间,节省空间。(存储时底层需要做一次判断,会降低效率 )
  • char 类型
    • 固定字符类型 char(ln) 不管字符数据长度是多大,直接开辟ln大小的空间存储空间。
    • 特点:存储效率高于varchar2()
  • date 类型 (日期类型)

举例如下:

1
2
3
4
5
6
7
8
9
create table student(
sno number(10),
sname varchar2(100),
sage number(3),
ssex char(4),
sfav varchar2(500),
sbirth date,
sqq varchar2(30)
);

这样的二维表在实际运用中并不合理,需要添加一些必要的约束条件来真正合理创建一个表,具体解释请继续往下看。

增加约束条件

上面的表我们虽然创建出来了,但是我们不难想象,在没有对表中的数据加以约束的情况下,无论多么不合理的数据都可以存储进去,这样无疑也就大大减小了数据的存在价值。为了解决这个问题,我们对上述代码加以约束,来增强数据库的完整性。

  • 添加主键,主键非空唯一

    • 直接在创建表的对应字段后添加primary key,这样方便,可以通过constraint 主键名 primary key来定义约束名。

    • 在创建表的最后使用constraint <完整性约束条件名> <完整性约束条件>加以约束,下面给出一个较为正式的主键子句模板:constraint pk_表名_字段名 primary key(字段名);

    • 在创建表之后加入如下字段可设置主键:

      1
      alter table 表名 add constraint pk_表名_字段名(约束名) primary key(字段名);
    • 删除主键

      1
      alter table 表名 drop constraint 主键的约束名;
  • 添加非空约束,即输入内容时不可为NULL

    • 直接在创建表的对应字段后添加not null

    • 在创建表语句的最后面使用constraint ck_表名_字段名 check(字段名 is not null);

    • 在创建表后是加入如下字段:

      1
      alter table 表名 add constraint ck_表名_字段名 check(字段名 is not null);
    • 删除非空约束

      1
      alter table 表名 drop constraint 对应的非空约束名;
  • 添加检查约束

    • 直接在创建表的对应字段后添加check(字段名相应的约束条件)

    • 在创建表语句的最后面使用constraint ck_表名_字段名 check(字段名相应的约束条件);

    • 在创建表后添加如下字段:

      1
      alter table 表名 add constraint ck_表名_字段名 check(字段名相应的约束条件);
    • 删除检查约束

      1
      alter table 表名 drop constraint 对应的约束名;
  • 添加唯一约束(不是主键,可以为空,但不可重复)

    • 直接在创建表的对应字段后添加unique

    • 在创建表的最后面加入constraint un_表名_字段名(约束名) unique(sqq)

    • 在创建表后添加如下字段

      1
      alter table 表名 add constraint 约束名(可参考上面的命名方式) unique(sqq);
    • 删除唯一约束

      1
      alter table 表名 drop constraint 对应的约束名;

综上给个例子供参考

1
2
3
4
5
6
7
8
9
create table sss(
id number(10) constraint 主键名 primary key,
ssex varchar2(4) not null,
sname varchar(30),
sphone number(11) constraint 约束名1 unique,
constraint 约束名2 check(ssex in('男','女'))
)
alter table sss add constraint 约束名3 check(sname is not null);
alter table sss add constraint waiJianYueShu foreign key(id) references ss(id);

外键约束条件

外键约束条件理论上也是在添加约束条件时一起添加,但因为外键约束较为特殊,这里独立出来讲一下,这也就完成了参照完整性!

  • 作用:当在子表中插入的数据在父表中不存在,则会自动报错。

  • 概念:当一张表中的某个字段的值需要依赖另外一张表的某个字段的值,则使用外键约束

    • 其中主动依赖的表称为子表,被依赖的表称为父表。
    • 外键加在子表中
  • 使用外键方式

    • 在子表中的字段后直接使用 references 父表名(字段),例如:

      1
      cno number(10) references clazz(cno)
    • 在创建表语句的最后面使用

      1
      constraint fk_子表名_字段名 foreign key (字段名) references 父表名(参考字段名);
    • 在创建表后使用

      1
      alter table 表名 add constraint fk_子表名_字段名 foreign key (字段名) references 父表名(参考字段名);
    • 删除外键

      1
      alter table 表名 drop constraint 外键约束名;
  • 外键选取:一般选取父表的主键作为子表的外键。

  • 外键缺点:

    • 无法直接删除父表数据,除非级联删除
    • 级联删除:在添加外键约束时,使用关键字 on delete cascade
      • 使用:当删除父表数据时,自动删除子表相关所有数据。
      • 缺点:无法保留子表历史数据。
    • 级联删除:在添加外键约束时,使用关键字 on delete set null
      • 删除父表数据时,将子表中依赖字段的值设置为NULL。
      • 注意:子表依赖字段不能添加非空约束。
  • 因为外键有这么多缺点,所以我们在删除父表时,可以先删除子表的外键依赖,创建新表后再在表中加入外键依赖,或者根本就不用外键。用Java代码来限制以达到外键依赖效果。

创表示例

1
2
3
4
5
6
7
8
9
10
--创建学生表
create table student(
sno number(10) constraint pk_stu_sno primary key,
sname varchar2(100) constraint ck_stu_sname not null,
sage number(3) constraint ck_stu_sage check (sage>0 and sage <150),
ssex char(4) constraint ck_stu_ssex check(ssex in('男','女')),
sfav varchar2(500),
sqq varchar2(30) unique,
cno number(10) references clazz(cno)
);

查看表结构

在命令窗口中输入desc 表名 回车后可查看表结构。

维护表

修改表名称

1
rename 原表名 to 新表名;

添加新的字段

1
alter table 表名 add 字段名 数据类型 约束;  --类似于上面增加约束条件的第一种方式

新增字段一般不加非空约束,因为如果原表中有数据的话,新增的字段内的数据一定是null

修改原有字段

  • 修改字段名

    1
    alter table 表名 rename column 字段名 to 新的字段名;
  • 修改字段类型

    1
    alter table 表名 modify 字段名 新的类型;

删除字段

1
alter table 表名 drop column 字段名;

删除表

1
drop table 表名;

Oracle的其他知识

Oracle序列

  • 序列作用

    • 由于序列的机制问题,我们可以用序列的值作为主键使用,来动态获取主键的值,这样新增数据的时候极大的避免了主键冲突。
    • 使用的是序列值.nextval作为主键。
    • 主键的值非空唯一即可,不需要主键的值是连续的值。
  • 创建默认序列

    • 1
      create sequence 序列名;
    • 1
      2
      3
      create sequence cc;
      select cc.currval from dual; --查看序列当前值
      select cc.nextval from dual; --查看序列自增后的值
    • 特点1:默认开始是没有值的,也就是指针只在了没有值的位置。

    • 特点2:序列名.nextval 每执行一次就会自增一次,默认步长为1。

    • 特点3:序列名.currval查看当前序列的值,开始时没有的。

  • 创建自定义序列

    • 1
      2
      3
      4
      5
      create sequence aa   --序列名 
      start with 2016210000 --设置序列开始值,默认为空,第一次自增为1
      increment by 2 -- 设置序列步长,默认为1
      maxvalue 2017000000 --设置序列最大值
      cache 10 --缓存设置为10
  • 删除序列

    • 1
      drop sequence 序列名;

Oracle视图

视图定义:

  • 视图是从若干基本表和(或)其他视图构造出来的表。

  • 在创建一个视图时,只是存放的视图的定义,也即是动态检索数据的查询语句,而并不存放视图对应的数据

  • 在用户使用视图时才去求相对应的数据。所以视图被称作“虚表”

视图作用:

  • 在开发过程中会使用视图将表中部分字段提供出去,将重要字段隐藏起来,这样可以保护重要数据。保密、安全。
  • 保护真实的表,并隐藏用户的核心数据,但对视图上的增删改操作也会直接被执行到真实的表中。
  • 可以将多个表的联合查询放到一个视图中进行计算,这样我们就可以看作是一个“表”,然后运用单表操作。

使用视图:

  • 创建视图

    1
    create[or replace] view 视图名 as select 对外提供的字段内容 from 真实表名;--[]中表替换可省略
  • 删除视图

    1
    drop view 视图名;

视图特点:

  • 保护真实表,隐藏重要字段数据,保护数据。

  • 在视图中的操作会映射执行到真实表中。

  • 可以手动开启只读模式,使用关键字 with read only

    1
    create view 视图名 as select 对外提供的字段内容 from 真实表名 with read only;
  • 注意

    • 视图的创建必须拥有dba权限。

视图总结:

  • 视图是一个虚拟表,对应一条SELECT语句,可将它的输出看作一个表

  • 视图不存储数据

  • 改变基本表的数据,也会反应到基于该表的视图上

  • 视图可以基于基本表的若干行,若干列

  • 视图可以基于一个表、多个表,甚至是基于其他的视图

  • 使用视图可以提高数据访问的安全性,只显示指定的行列数据

  • 使用视图可以降低查询的难度,定制数据显示

  • 可以对视图进行CRUD操作,实际上是对基本表的CRUD操作

  • 如果视图对应多个表,一般不允许添加操作,可以通过触发器解决

  • 使用with read only定义只读视图

Oracle索引

索引作用:提升查询效率。当数据过少时,建立索引反而会降低查询效率。

使用索引:

1
2
create index 索引名 on 表名(字段名);     --创建索引
drop index 索引名; --删除索引

索引特点:

  • 显示的创建,隐式的执行
  • oracle会自动给表的主键创建索引

开发中使用索引的要点:

  • 索引数据可能要占用大量的存储空间。
  • 索引改善检索操作的性能,但降低数据插入、修改和删除的性能。在执行这些操作时,DBMS必须动态地更新索引。
  • 限制表中索引的数目。索引越多,在修改表时对索引做出修改的工作量越大。
  • 并非所有数据都适合于索引。唯一性不好的数据(如省)从索引得到的好处不比具有更多可能值的数据(如姓名)从索引得到的好处多。
  • 索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据,则该数据可能是索引的备选。
  • 可以在索引中定义多个列(如省加城市),这样的索引只在以省加城市的顺序排序时有用。如果想按城市排序,则这种索引没有用处。

Oracle分页查询

当一个表中的数据量特别大的时候,如果一次性全部显示给用户,则造成页面过于庞大,会大大降低用户体验,因此,这就需要分页查询。

  • rownum关键字

    • Oracle对外提供的自动给查询结果编号的关键字,与每行的数据没有关系。
    • 把查询出来的结果按照从1开始编号。
    • 该关键字只能够做<<= 的判断,不能进行>>=的判断。
  • 分页规律

    • 每页显示m条数据,查询第n条数据

    • 1
      select t.* from (select rownum r,e.* from 要分页的表 e where rownum <= m*n) t where r>m*(n-1);  --伪代码
    • 要分页的表既可以是真实的表,也可以是一条查询语句。

Oracle触发器

有关触发器的相关介绍,请大家自行百度。这里仅给出一个自己在完成课程设计时用到的例子,希望能够打个样,由于时间原因就不详细介绍了。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
--对老师表创建触发器
create or replace trigger teacher_tri
after insert or update or delete
on tea_info
for each row
declare
-- local variables here
begin
if inserting then
insert into tea_check(tea_id,tea_pwd) values(:NEW.tea_id,:NEW.tea_pwd);
elsif updating then
update tea_check set tea_id=:NEW.tea_id,tea_pwd = :NEW.Tea_Pwd where tea_id=:old.tea_id;
elsif deleting then
delete from tea_check where tea_id=:OLD.tea_id;
end if;
end teacher_tri;

Oracle数据备份

Oracle图形化界面操作

  • 可以创建表,并可以添加约束。

  • 在要操作数据的表上右键选择编辑数据,完成对表数据的管理。

  • 创建序列、索引、视图
  • 创建新用户
您的每一份支持将鼓励我继续创作!
-------------本文结束感谢您的阅读-------------