5表的创建及管理详解.docx
- 文档编号:25957023
- 上传时间:2023-06-16
- 格式:DOCX
- 页数:34
- 大小:117.53KB
5表的创建及管理详解.docx
《5表的创建及管理详解.docx》由会员分享,可在线阅读,更多相关《5表的创建及管理详解.docx(34页珍藏版)》请在冰豆网上搜索。
5表的创建及管理详解
表的创建及管理
1、本次预计讲解的知识点
1、数据表的创建及管理;
2、表约束的创建及管理
3、利用DDL、DML完成一套完整的操作案例;
4、序列的使用;
5、数据的集合操作;
6、PLSQLDeveloper工具的使用;
2、具体内容
2.1、表的创建及管理(重点)
对于数据库而言实际上每一张表都表示的是一个数据库对象,而在数据库中对象指的就是DDL定义的所有操作,例如:
表、视图、索引、序列、约束等等,都属于对象的操作,所以表的建立就是对象的建立,而对象的操作主要分为以下三类语法:
·创建对象:
CREATE对象名称……;、
·删除对象:
DROP对象名称.....;
·修改对象:
ALTER对象名称......;
2.1.1、常用的数据字段
每一张数据表实际上都是由若干个字段所组成,而每一个字段都会有其对应的数据类型,而在Oracle之中,常用的数据类型有如下几种:
No.
数据类型
关键字
描述
1
字符串
VARCHAR2(n)
其中n表示的是字符串所能保存的最大长度,基本上保存200个左右的内容
2
整数
NUMBER(n)
表示最多为n位的整数,有时候也可以使用IN代替
3
小数
NUMBER(n,m)
其中m为小数位,n-m为整数位,有时候也可以使用FLOAT代替
4
日期
DATE
存放日期-时间
5
大文本
CLOB
可以存储海量文字(4G),例如存储《三国演义》、《红楼梦》
6
大对象
BLOB
存放二进制数据,例如:
电影、MP3、图片、文字
一般开发之中使用最多的:
VARCHAR2()、NUMBER、DATE、CLOB,而对于BLOB字段一般使用较少,首先BLOB可以存放4G的二进制数据,但是存放进去之后,一是数据库过于庞大,二是读取不方便;
2.1.2、表的创建
如果现在要想进行表的创建,可以使用如下的操作语法:
CREATTABLE表名称(
字段1数据类型[DEFAULT默认值],
字段2数据类型[DEFAULT默认值],
.........
字段2数据类型[DEFAULT默认值]
);
下面创建一张成员表(member),有如下保存的信息:
姓名、年龄、生日、个人简介。
CREATETABLEmember(
nameVARCHAR2(50)DEFAULT'无名氏',
ageNUMBER(3),
birthdayDATEDEFAULTSYSDATE,
contentCLOB
);
表创建成功后,下面开始向表中增加数据:
INSERTINTOmember(name,age,birthday,content)
VALUES('张三',20,TO_DATE('1990-08-12','yyyy-mm-dd'),'好人');
INSERTINTOmember(age,content)
VALUES(20,'好人');
一定要再次记住,表的创建时属于数据库对象的创建,所以使用的是CREATE语法。
2.13、表的复制
在之前学习过表的复制操作,下面给出完整的操作语法:
CREATETABLE复制表名称AS子查询;
范例:
复制一张只包含20部门雇员信息的表;
CREATETABLEemp20AS
SELECT*FROMempWHEREdeptno=20;
范例:
现在要求将emp表的表结构复制出来,不要数据---写个永远满足不了的条件即可。
CREATETABLEempnullAS
SELECT*FROMempWHERE1=2;
但是以上的语法只是oracle数据库所支持的操作,其他的数据库语法上会有一些区别。
2.1.4、为表重命名(了解)
在oracle数据库之中,所有的数据库实际上都是通过数据字典保存的,例如,在之前曾经使用过如下的一种查询:
SELECT*FROMtab;
以上就是一个数据字典,而在oracle之中,提供了三种类型的数据字典,最常用的是:
dba、user_,所以下面查询一个user_tables数据字典:
SELECT*FROMuser_tables;
也就是说oracle中的所有数据都是按照文件保存的,那么所有的内容都会在数据字典中注册,既然这样,所谓的修改表名称实际上对于oracle而言就相当于修改一条数据而已,而修改表名称的方法如下:
RENAME旧的表名称TO新的表名称;
范例:
将member表更名为person表
RENAMEmemberTOperson;
但是这种操作由于是oracle数据库所独有的一种特性,所以了解即可,不用做深入的掌握。
2.1.5、截断表(了解)
在之前曾经讲解过一个删除表数据的操作,使用的是DELETE操作,但是这种删除操作本身有一个特点,即:
可以进行事务的回滚,也就是说删除之后并不会立刻释放数据的资源,如果现在希望彻底释放掉一张表所占用的全部资源(表空间、索引等等)就可以使用截断表的语法,此语法如下:
TRUNCATETABLE表名称;
范例:
截断person表
TRUNCATETABLEperson;
但是这种语法本身只是oracle所有的,所以只做了解即可。
2.1.6、表的删除
表的删除操作指的是数据库对象的删除,既然是删除则使用DROP语句,删除表的语法如下:
DROPTABLE表名称;
范例:
删除person表
DROPTABLEperson;
2.1.7、oracle10g的新特性闪回技术(理解)
在oracle10g之后,为了预防用户的误删除表操作,专门提供了回收站的功能,用户所删除的表默认情况下会在一个回收站中保存,而用户也可以通过回收站,进行表的恢复,所以此技术称为闪回(FLASHBACK);
范例:
查看回收站
SHOWRECYCLEBIN;
这个时候可以发现所有已经删除的表都在回收站之中保存,那么下面就可以使用如下的语法恢复表:
FLASHBACKTABLE表名称BEFOREDROP;
范例:
恢复myemp表
FLASHBACK_TABLEperson;
当然,现在也可以直接删除掉回收站中的一些数据,语法如下:
PURGETABLE表名称;
范例:
删除回收占中的person表
PURGETABELperson;
范例:
清空回收站
PUEGERECYCLEBIN;
如果现在希望删除一张表,而又不希望其进入到回收站之中,则可以在删除的时候增加PURGE
DROPTABLEmyempPURGE;
这种技术是在oracle10g之后才有的,而oracle11g也是存在的。
问题:
现在在回收站之中存在了一张tab表,而后又建立了一张tab表,那么如果从回收站中恢复的话,可以吗?
无法恢复,但是一般人真不会出这样的问题。
2.1.8、修改表结构(了解)
如果一张建立好的数据表,发现其初期的结果已经不满足于后期的使用要求,则可以进行表结构的修改操作,而表的修改操作实际上就是数据库对象的修改操作,使用ALTER指令完成,例如,现在有如下一张表:
CREATETABLEmember(
MidNUMBER,
nameVARCHAR2(50)
);
现在希望可以向表中增加字段,所以此时可以采用如下语法完成:
ALTERTABLE表名称ADD(列名称数据类型[DEFAULT默认值],
列名称数据类型[DEFAULT默认值],....);
范例:
为member表增加字段
ALTERTABLEmemberADD(ageNUMBER(3),birthdayDATEDEFAULTSYSDATE);
如果增加的数据列没有默认值,则所有已有的数据的列的内容都是null,而如果增加的列指定了DEFAULT默认值的话,则所有已有的数据列都设置为默认值。
现在也可以修改已有的表结构,此时语法如下:
ALTERTABLE表名称ADD(列名称数据类型[DEFAULT默认值],
列名称数据类型[DEFAULT默认值],....);
范例:
将name字段的默认值定义为无名氏
ALTERTABLEmemberMODIFY(nameVARCHAR2(100)DEFAULT'无名氏');
虽然在SQL语法之中以及oracle数据库之中,都给出了修改表结构的操作,但是这种操作能不用就不用,从大型数据库来讲,世界上性能最高的数据库是IBMDB2,但IBMDB2本身有一个平台的限制问题,所以如果说是跨平台的数据库则是oracle数据库的性能最高。
在IBMDB2数据库之中是不允许修改表结构的,即:
表建立完成之后就不能再修改了,所以以后再开发之中也尽可能的不要去修改表结构。
额外补充:
关于软件项目的开发基本流程
1、获取需求:
--->软件销售
2、需求分析:
--->根据客户方的提示,作出需求分析
3、业务分析:
--->数据库设计及借口设计;数据表设计
4、编码实现:
--->业务梳理
5、软件测试:
--->理论上应有专门的测试人员
6、软件培训以及项目的维护;
数据表的建立前提=软件的业务分析;
2.1.9、思考题(面试题)
现在要求建立一张nation表,表中有一个name字段,里面保存四条记录:
中国,美国,巴西,荷兰,要求通过查询实现如下的操作效果:
·中国美国
·中国巴西
·中国荷兰
·美国巴西
·美国中国
·美国荷兰
剩下的以此类推,现在要求建立新的表完成此查询的操作。
本题目的主要目的并不是在于查询的编写上,而是在于规范化数据库创建脚本的格式,以后只要碰到了类似的要求,首先必须要编写一个数据库创建脚本,这个脚本的要求如下:
1、本文件的文件名后缀必须是“*.sql”;
2、先删除相应的数据表;
3、编写创建表的语句;
4、增加测试数据;
5、提交事务;
--1、删除表
DROPTABLEnationPURGE;
--2、创建表
CREATETABLEnation(
nameVARCHAR2(20));
--3、测试数据
INSERTINTOnation(name)VALUES('中国');
INSERTINTOnation(name)VALUES('美国');
INSERTINTOnation(name)VALUES('巴西');
INSERTINTOnation(name)VALUES('荷兰');
--4、事务提交
COMMIT;
本题型如果想要完成需要依靠笛卡尔积完成,属于表的自身关联
SELECTn1.name,n2.name
FROMnationn1,nationn2
WHEREn1.name<>n2.name;
而且以后如果面试之中,出现了一些复杂的查询,建议把脚本写全了。
2.2、约束(重点)
表虽然建立完成了,但是表中的数据是否合法并不能有所检查,而如果要想针对于表中的数据做一些过滤的话,则可以通过约束完成,约束的主要功能是保证表中数据的合法性,按照约束的分类,一共有五种约束:
非空约束,唯一约束,主键约束,检查约束,外键约束;
2.2.1、非空约束(NOTNULL):
NK
当数据表中的某个字段的内容不希望设置为null的话,则可以使用NOTNULL进行指定。
范例:
定义一张表
DROPTABLEmemberPURGE;
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL);
因为此时存在了“NOTNULL”约束,所以下面插入两组数据。
范例:
正确的数据
INSERTINTOmember(mid,name)VALUES(1,'张三');
INSERTINTOmember(mid,name)VALUES(null,'李四');
INSERTINTOmember(name)VALUES('王五');
范例:
插入错误的数据
INSERTINTOmember(mid,name)VALUES(9,null);
INSERTINTOmember(mid)VALUES(10);
此时出现的错误提示:
ORA-01400:
无法将NULL插入("SCOTT"."MEMBER"."NAME")
本程序之中,直接表示出了“用户”.“表名称”.“字段”.出现了错误。
2.2.2、唯一约束(UNIQUE):
UK
唯一约束指的是每一个列上的数据是不允许重复的,例如:
Email地址每个用户肯定是不重复的,那么就使用唯一约束完成。
DROPTABLEmemberPURGE;
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL,
emailVARCHAR2(50)UNIQUE
);
范例:
插入正确的数据
INSERTINTOmember(mid,name,email)VALUES(1,'张三','mldqa@');
INSERTINTOmember(mid,name,email)VALUES(2,'李四',null);
范例:
插入错误的数据
INSERTINTOmember(mid,name,email)VALUES(3,'王五','mldqa@');
此时会出现如下的错误提示:
ORA-00001:
违反唯一约束条件(SCOTT.SYS_C005422)
可是这个时候的错误提示语之前的非空约束相比并不完善,因为现在只是给出了一个代号而已,这是因为在定义约束的时候没有为约束制定一个名字,所以由系统默认分配了,而且约束的名字建议的格式“约束类型_字段”,例如:
“UK_email”,指定约束名称使用CONSTRAINT完成。
DROPTABLEmemberPURGE;
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL,
emailVARCHAR2(50),
CONSTRAINTUK_emailUNIQUE(email)
);
以后再次增加错误数据时,提示信息如下:
ORA-00001:
违反唯一约束条件(SCOTT.UK_EMAIL)
已经可以很明确的提示用户错误的位置。
2.2.3、主键约束(PrimaryKey)PK
主键约束=非空约束+唯一约束,在之前设置唯一约束的时候发现可以设置为null,而如果现在使用了主键约束之后则不能为空,而且主键一般作为数据的唯一的一个标记出现,例如:
人员的ID。
范例:
建立主键约束
DROPTABLEmemberPURGE;
CREATETABLEmember(
midNUMBERPRIMARYKEY,
nameVARCHAR2(50)NOTNULL
);
范例:
增加正确的数据
INSERTINTOmember(mid,name)VALUES(1,'张三');
范例:
错误的数据--主键设置为null
INSERTINTOmember(mid,name)VALUES(null,'张三');
错误信息,与之前的非空约束的错误提示是一样的;
ORA-01400:
无法将NULL插入("SCOTT"."MEMBER"."MID")
范例:
错误的数据--主键重复
INSERTINTOmember(mid,name)VALUES(1,'张三');
错误信息,这个错误信息就是唯一约束的错误信息,但是信息不明确,因为没起名字
ORA-00001:
违反唯一约束条件(SCOTT.SYS_C005426)
所以为了约束的使用方便,下面为主键约束起一个名字
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL,
CONSTRAINTpk_midPRIMARYKEY(mid)
);
此时重复插入数据,则错误信息如下:
ORA-00001:
违反唯一约束条件(SCOTT.PK_MID)
从正常的开发角度而言,一张表一般都只设置一个主键,但是从SQL语法的规定而言,一张表却可以设置多个主键,而此种做法称为复合主键,例如:
参考如下代码:
DROPTABLEmemberPURGE;
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL,
CONSTRAINTpk_midPRIMARYKEY(mid,name)
);
在复合主键的使用之中,只有两个字段的内容都一样的情况下,才被称为重复数据;
范例:
插入正确的数据
INSERTINTOmember(mid,name)VALUES(1,'张三');
INSERTINTOmember(mid,name)VALUES(1,'李四');
INSERTINTOmember(mid,name)VALUES(2,'李四');
范例:
插入错误的数据
INSERTINTOmember(mid,name)VALUES(1,'张三');
错误信息:
ORA-00001:
违反唯一约束条件(SCOTT.PK_MID)
但是从开发的实际角度而言,正常人都不使用复合主键,所以这个知识只是作为其相关内容做一个介绍,记住了:
只要是数据表,永远都只设置一个主键
2.2.4、检查约束(Check):
CK
检查约束指的是为表中的数据增加一些过滤条件,例如:
·设置年龄的时候范围是:
0-250
·设置性别的时候应该是:
男、女、中
范例:
设置检查约束
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL,
sexVARCHAR2(50)NOTNULL,
ageNUMBER(3),
CONSTRAINTpk_midPRIMARYKEY(mid,name),
CONSTRAINTck_sexCHECK(sexIN('男','女','中')),
CONSTRAINTck_ageCHECK(ageBETWEEN0AND250)
);
范例:
增加正确的数据
INSERTINTOmember(mid,name,sex,age)VALUES(1,'六三','中',200);
范例:
增加错误的性别--ORA-02290:
违反检查约束条件(SCOTT.CK_SEX)
INSERTINTOmember(mid,name,sex,age)VALUES(2,'刘思','人妖',200);
范例:
增加错误的年龄--ORA-02290:
违反检查约束条件(SCOTT.CK_AGE)
INSERTINTOmember(mid,name,sex,age)VALUES(2,'刘思','女',500);
检查的操作就是对输入的数据进行一个过滤。
2.2.5、主外键约束(核心难点)
之前的四种约束都是在单张表中进行的,而主外键约束是在两张表中进行的,这两张表示存在父子关系的,即:
子表中某个字段的取值范围由父表所决定。
例如:
现在要求表示出一种关系,每一个人有多本书,应该定义两张数据表:
member(主),book(子)
DROPTABLEmemberPURGE;
DROPTABLEbookPURGE;
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL,
CONSTRAINTpk_midPRIMARYKEY(mid,name)
);
CREATETABLEbook(
bidNUMBER,
titleVARCHAR2(50)NOTNULL,
midNUMBER
);
此时只是根据要求建立了两张独立的数据表,下面插入几条数据:
INSERTINTObook(bid,title,mid)VALUES(101,'Java开发',1);
INSERTINTObook(bid,title,mid)VALUES(102,'JavaWeb开发',2);
INSERTINTObook(bid,title,mid)VALUES(103,'Android开发',2);
INSERTINTObook(bid,title,mid)VALUES(104,'AJAX开发',1);
INSERTINTObook(bid,title,mid)VALUES(105,'数据库开发',1);
要想验证这个数据是否有意义,最简单的做法,就是写两个查询。
范例:
统计每个人员拥有书的数量
SELECTm.mid,m.name,COUNT(b.mid)
FROMmemberm,bookb
WHEREm.mid=b.mid
GROUPBYm.mid,m.name;
book范例:
查询出每个人员的编号,姓名,拥有书的名称
SELECTm.mid,m.name,b.title
FROMmemberm,bookb
WHEREm.mid=b.mid;
即,现在的bookmid字段应该是与membermid字段相关联的,但是由于本程序没有设置约束,所以,现在以下的数据也是可以增加的:
INSERTINTObook(bid,title,mid)VALUES(106,'PhotoShope使用手册',3);
INSERTINTObook(bid,title,mid)VALUES(105,'FLEX开发手册',8);
现在增加了两条新的记录,而且记录可以保存在数据库之中,但是这两条记录没有意义,因为membermid字段的内容没有3和8(只是在book中有这些数据,由于两个表没任何关联,所以member并没有改变什么),而要想解决这个问题就必须依靠外键约束来解决。
让bookmid的字段取值由membermid所决定,如果membermid的数据真实存在,则表示可以更新。
DROPTABLEmemberPURGE;
DROPTABLEbookPURGE;
CREATETABLEmember(
midNUMBER,
nameVARCHAR2(50)NOTNULL,
CONSTRAINTpk_midPRIMARYKEY(mid)
);
CREATETABLEbook(
bidNUMBER,
titleVARCHAR2(50)NOTNULL,
midNUMBER,
CONSTRAINTpk_bidPRIMARYKEY(b
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 创建 管理 详解