第三讲 Oracle数据库应用 一

说明：ENABLE子句将失效的约束生效 • 删除约束 语法： ALTER TABLE 表名 DROP CONSTRAINT 约束名
建表的同时添加约束
Create table OTL_NICOTINE_GRADE ( ID NUMBER not null, SEASON_NO number(4) DEFAULT(10) null, RECEIPT_NO NUMBER(8) not null, GRADE VARCHAR2(10) not null, PROPORTION NUMBER(5,2) not null, WEIGHT NUMBER(10,2) not null, VALUE NUMBER(12,2) not null, constraint PK_OTL_NICOTINE_GRADE primary key (ID, GRADE), constraint FK_OTL_NICO_REFERENCE_OTL_CHEC foreign key (ID) references ADMIN (ADMINID) , constraint UQ_ID UNIQUE (ID), constraint CK_RECEIP_NO CHECK(RECEIPT_NO BETWEEN 15 AND 40) )
×
触发器：检查信用值
帐号 00192 00288 12333
姓名 孙悟空 猪悟能 段誉
信用 …. 7 6 8 40 -10
90111 虚竹 93000 岳不群 约束方法：规则、存储过程、触发器
主键和外键
1. 主键(Primary key) 用来唯一标识表中每一行的一列或多列的组合
主键列 学号 0010012 0010013 0010014 0010015 姓名 李山 吴兰 雷铜 张丽鹃 地址 山东定陶 湖南新田 江西南昌 河南新乡 ….
查看约束信息
• 用户的所有约束
SELECT constraint_name, constraint_type, search_condition FROM user_constraints ；
• 约束建立的列
SELECT constraint_name, column_name FROM user_cons_columns；
引用完整性
实体完整性
学号 0010012 0010013 0010014 姓名 李山 吴兰 雷铜 地址 山东定陶 湖南新田 江西南昌 ….
0010015
0010016
张丽鹃
赵可以
河南新乡
河南新乡
×
0010014 雷铜 江西南昌
约束方法：唯一约束、主键约束、标识列
域完整性
学号 0010012 0010013 0010014 姓名 李山 吴兰 雷铜 地址 山东定陶 湖南新田 江西南昌 ….
科目来自百度文库数学
注意：外键的值要求与主表的主键相 对应，外键用来强制实现引用完整性； 一个表中可以有多个外键
学号 0010012 0010013 0010012
分数 88 74 67
…
数学 语文
语文
数学
0010013
0010016
81
98
Oracle的数据类型
参见：Oracle中的数据类型
创建数据库流程
可靠性
准确性
数据完整性
论数据完整性 2-2
• 数据存放在表中 • “数据完整性的问题大多是由于设计引起 的” • 创建表的时候，就应当保证以后数据输入 是正确的 ——错误的数据、不符合要求的数据不允 创建表：保证数据的完整性 = 实施完整性约束 许输入
完整性包括…2-1
• 输入的类型是否正确？
——年龄必须是数字
第三讲
Oracle数据库应用 一
目标
• • • • • • • • 数据完整性 Oracle中的数据类型 创建数据库 SQL语言基础 创建数据库表 设置表的主键、外键和建立表之间的关系 为表增加约束 Oracle中的序列
MySchool 数据库结构介绍
在线答题模块使用到的数据表
论数据完整性 2-1
•
为表添加约束
约束是表强制执行的规则，用于保证表中数据的完整性
• 添加约束的语法：
ALTER TABLE 表名 ADD CONSTRAINT 约束名 约束类型 具体的约束说明
• 约束名的取名规则推荐采用：约束类型_约束字段 – 主键（Primary Key）约束：如 PK_stuNo – 唯一（Unique Key）约束：如 UQ_stuID – 默认（Default Key）约束：如 DF_stuAddress – 检查（Check Key）约束：如 CK_stuAge – 外键(Foreign Key)约束：如 FK_stuNo
设置表的主键、外键 建立表之间的关系
• 设置主键语法
alter table <pk_table_name> add constraint [PK_name] primary key (filed_name)
• 主键列选择原则 1、最少性
– 尽量选择单个键作为主键
2、稳定性
– 尽量选择数值更新少的列作为主键
•
唯一约束 语法：
ALTER TABLE <table_name> ADD CONSTRAINT UQ_filedName UNIQUE (filedName)
•
检查约束 语法： ALTER TABLE <table_name> ADD CONSTRAINT CK_filedName CHECK(<check_condition>) 例如： ALTER TABLE stuInfo ADD CONSTRAINT CK_stuAge // CHECK(stuAge BETWEEN 15 AND 40) check( stuAge > 15 and stuAge < 40)
0010016
赵可以
河南新乡
×
0010015 李爽 云南大理 注意：一个表中只能有一个主键，主键约束确保了表中行的唯一性； 选择主键的原则：最少性、稳定性和无冗余
主键和外键
2.外键(Foreign key)
对应于主键而言，就是“子表”中对应于“主表”的列
学号 0010012 0010013 0010014 0010015 0010016 姓名 李山 吴兰 雷铜 张丽鹃 赵可以 地址 山东定陶 湖南新田 江西南昌 河南新乡 河南新乡 …
Sql语句的书写规则
•在Oracle数据库中，Sql 语句大小写不敏感
•Sql语句可单行或多行书写 •在Sql语句中，关键字不能跨多行或缩写
•为了提高可读性，一般关键字大写，其他小写
•子句通常位于独立行，便于编辑，提高可读性 •空格和缩进使程序易读
创建数据库表
• 定义表结构的语法 create table <table_name> ( <field_name> <field_type> [null | not null], … ); 例如：create table admin ( adminId number not null, loginId varchar2(20) not null, loginId varchar2(8) not null ); 删除表结构 drop table <table_name>;
• 输入的格式是否正确？
——身份证号码必须是18位
列值要求（约束）
• 是否在允许的范围内？
——性别只能是”男”或者”女”
• 是否存在重复输入？
——学员信息输入了两次
整行要求（约束）
• 是否符合其他特定要求？
——信誉值大于5的用户才能够加入会员列表
• ……
完整性包括…2-2
域完整性
自定义完整性 实体完整性
• • • 1、通过DCA工具创建数据库 2、通过SYSTEM账号登录到被创建的数据库中 3、创建数据表空间和临时表空间 //创建临时表空间 create temporary tablespace test_temp tempfile ‘E:\oracle\product\10.2.0\oradata\testserver\test_temp01.dbf’ size 32m autoextend on next 32m maxsize 2048m extent management local; //创建数据表空间 create tablespace test_data logging datafile 'E:\oracle\product\10.2.0\oradata\testserver\test_data01.dbf' size 32m autoextend on next 32m maxsize 2048m extent management local; 4、创建用户并授权
科目 数学 数学 语文 语文
学号 0010012 0010013 0010012 0010013
分数 88 74 67 81
…
数学
约束方法：外键约束
× 0010016
数学
98
0010021 98
自定义完整性
用户编号 AV0012 AV0013 AV0014 CV0015 CV0016 用户姓名 孙悟空 猪悟能 沙悟净 玄痛 乔峰 会员证 AV378289 AV378290 AV378291 AV372133 AV121322 ….
• 设置外键语法
alter table <fk_table_name> add constraint [FK_name] foreign key (filed_name) references <pk_table_name>(pk_filed_name)
附加约束
• 默认值约束 语法:
alter table <table_name> modify <filed_name> default (<value>);
Sql语句基础
•SQL被称为结构化查询语言 (Structured Query Language ) •SQL 是操作和检索关系型数据库的标准语言 •使用Sql语句，程序员和数据库管理员可以完成如下的任务
–改变数据库的结构 –更改系统的安全设置 –增加用户对数据库或表的许可权限 –在数据库中检索需要的信息 –对数据库的信息进行更新
0010015
0010016
张丽鹃
赵可以
河南新乡
河南新乡
×
8700000000 李亮 湖北江门
约束方法：限制数据类型、检查约束、外键约束、默认值、非空约束
引用完整性
学号 0010012 0010013 0010014 0010015 0010016 姓名 李山 吴兰 雷铜 张丽鹃 赵可以 地址 山东定陶 湖南新田 江西南昌 河南新乡 河南新乡 …
Oracle---序列
• 序列是Oracle提供的一个对象,可以用来生成唯一、连续的 整数 • 创建序列语法 Create sequence <seq_name> [START WITH n] --- 第一个序列号 [INCREMENT BY n] ---序列号之间的间隔 [{MINVALUE n/NOMINVALUE}] --- 序列最小 [{MAXVALUE n/NOMAXVALUE}] --- 可生成的最大值 [{CYCLE/NOCYCLE}] --- 当序列达到最小或最大值后是否 重新开始生成值 [{CACHE n/NOCACHE}]; --- 是否使用缓存
Sql语句分类
•Select查询语句 •DML语句（数据操作语言） Insert / Update / Delete / Merge(合并) •DDL语句（数据定义语言） Create / Alter / Drop / Truncate •DCL语句（数据控制语言） Grant / Revoke •事务控制语句 Commit / Rollback / Savepoint
//创建用户并指定表空间
create user testserver_user identified by testserver_user default tablespace test_data temporary tablespace test_temp; //给用户授予权限 grant connect,resource,dba to testserver_user;
修改和删除约束
• 修改约束状态 1、使约束失效 语法：
ALTER TABLE <table_name> DISABLE CONSTRAINT <restrict_name> CASCADE；
说明：DISABLE可使完整性约束失效
CASCADE 选项可使依赖的完整约束失效
2、使约束生效 语法：
ALTER TABLE <table_name> ENABLE CONSTRAINT <restrict_name>；