1、第四章 修改数据与安全,一、 操纵数据概述,数据操纵和事务控制命令： INSERT在表中增加一新行 UPDATE修改表中现有行 DELETE删除表中现有行 COMMIT使所有临时的改变最终完成 SAVEPOINT允许回滚到保存点 ROLLBACK放弃所有临时改变,二、插入数据,1、插入新行语法： INSERT INTO table(column,column.) VALUES(value,value.);此语法一次只能插入一行,2、用空值插入新行 隐式方法：从列名表中省略列名 显式方法：在值列表中指定NULL关键字3、插入特定的日期和时间值 TO_DATE函数：覆盖所插入日期和时间值的缺省设置
2、 缺省值： 缺省的世纪为当前世纪 缺省的时间为午夜,4、从其它表复制行 使用子查询写INSERT命令INSERT INTO table(.) subquery; INSERT子句中的列数应与子查询的列数相匹配,三、更新数据,1、UPDATE语法： UPDATE table SET column=value,column=value. WHERE condition;2、更新时的完整性约束错误,四、删除数据,1、DELETE命令语法： DELETE FROM table WHERE condition;2、删除时的完整性约束错误 如果要删除的行的主键是其它表的外键，则出现完整性约束错误,五、事务
3、处理,1、数据库事务 与事务相关的语句： 一组DML命令 一条DDL命令 一条DCL命令 第一条可执行SQL命令执行时事务开始 以下列事件之一结束： COMMIT或ROLLBACK DDL或DCL命令执行（自动提交） 错误、退出或系统崩溃,2、事务控制的特点 确保数据的一致性 在改变最终确认之前，预览数据的变化 给相关的操作在逻辑上分组,3、隐式事务处理 下列情况会自动提交： 发出一条DDL命令，如CREATE 发出一条DCL命令，如GRANT 从SQL*Plus正常退出，而没有显式发出COMMIT或ROLLBACK 在SQL*Plus异常中断或系统崩溃时，自动回滚,4、COMMIT或ROLL
4、BACK之前的数据状态 数据以前的状态可以恢复 当前用户可用SELECT语句查看DML操作的结果 其它用户不能查看当前用户的DML语句的结果 所影响的行被加锁，其它用户不能改变这些行中的数据,5、COMMIT之后的数据状态 数据改变被写到数据库中 以前的数据永远丢失 所有用户都可查看结果 所影响行的锁被释放；其它用户可对这些行操作 所有SAVEPOINT被清除,6、ROLLBACK之后数据的状态 数据改变被取消 以前的数据状态被恢复 所影响的行的锁被释放,7、回滚到保留点标记 使用SAVEPOINT命令在当前事务中创建标记 使用ROLLBACK TO SAVEPOINT命令回滚到标记点,六、管
5、理用户,1、创建用户 由DBA创建用户 CREATE USER命令语法：CREATE USER user IDENTIFIED BY password;,2、授予用户权限 DBA可以授予用户特定的权限 语法：GRANT privilege,privilege. TO user,user.;,3、修改口令 创建用户时，可初始化口令 ALTER USER命令语法：ALTER USER user IDENTIFIED BY password;,4、角色 角色是可以授予用户的一个权限组 语法：CREATE ROLE role; 可将权限授予角色,七、权限,1、权限类型 系统权限： 获得对数据库的访问
6、对象权限： 操纵数据库对象的内容 模式： 对象的集合，如表、视图、序列,2、系统权限 一旦创建了用户，DBA就可以通过执行GRANT命令给用户授权 典型的用户有如下系统权限：CREATE SESSIONCREATE TABLECREATE SEQUENCECREATE VIEWCREATE PROCEDURE 授权命令：GRANT system_priv,. TO user,.；,3、对象权限 可允许用户执行特定对象上的操作 对象权限因对象类型不同而不同 拥有者具有对象的所有权限 拥有者可授出其拥有对象的特定权限,GRANT object_priv(column)|ALL ON object
7、TO user|role|PUBLIC WITH GRANT OPTION;WITH GRANT OPTION指定用户有权传递权限 PUBLIC将权限授予所有用户,4、查看权限信息 ROLE_SYS_PRIVS ROLE_TAB_PRIVS USER_ROLE_PRIVS USER_TAB_PRIVS_MADE USER_TAB_PRIVS_RECD USER_COL_PRIVS_MADE USER_COL_PRIVS_RECD,5、回收对象权限 使用REVOKE命令回收授予其它用户的权限 通过WITH GRANT OPTION授予其它用户的权限也被回收 语法：REVOKE privilege
8、,privilege.|ALLON objectFROM user,user.|role|PUBLICCASCADE CONSTRAINTS,第五章 创建与管理表和视图,一、设计数据库,1、数据库设计 表及列名应分别与实体及属性对应 设计自己的命名规则 其它要求：设计索引建立视图定义规划物理存储空间重定义完整性约束,2、完整性约束及键 相应的键：主键、唯一键、外键 完整性约束类型： 实体完整性 参照完整性 列约束 自定义约束,主键 主键（PK）不允许重复值，且不能为NULL 每行由一个或多个列（复合主键）唯一标识 侯选键也可作为主键 外键 外键（FK）是一个表中的一列或多列，其参照自身或另一个
9、表中的主键或唯一键（UK） FK列值必须与相关列的值匹配或为NULL 如果FK是PK的一部分，则其不能为NULL,3、数据库设计步骤 根据实体关系图中的组件，创建表实例图，包括：表名、列名、键类型、NULL及唯一引用、外键信息、列数据类型及最大长度、样本数据 步骤： 1）将实体映射到表 2）将属性映射到列 3）将唯一标识映射到主键 4）将关系映射到外键,二、创建表,1、创建表语法： CREATE TABLE schema.table(column datatype DEFAULT exprcolumn_constraint,.table_constraint);必须有特定的权限：CREATE
10、TABLE,2、命名规则 必须以字母开头 最多30个字符 只允许为A-Z,a-z,0-9,_,$,# 同一用户的对象不能同名 不能为保留字,3、DEFAULT选项 指定插入时列的缺省值 合法的值包括：常量、表达式、或SYSDATE、USER这样的SQL函数 非法的值包括：另一个列名或伪列 缺省值的数据类型必须与列的数据类型相匹配,4、数据类型 VARCHAR2(size) CHAR(size) NUMBER NUMBER(p,s) DATE LONG RAW和LONG RAW,5、约束 1）约束类型有：NOT NULLUNIQUEPRIMARY KEYFOREIGN KEYCHECK,给约束起
11、一个名字，否则系统会用SYS_Cn格式生成一个名字 可在创建表的同时或表创建之后创建一个约束 可在列级或表级定义约束,2）语法： 列级约束： column CONSTRAINT constraint_name constraint_type, 表级约束： column,. CONSTRAINT constraint_name constraint_type (column,.),3）NOT NULL约束 保证列中不会有空值 在列级约束定义4）UNIQUE约束 指定表中不会有两行的某列或多列键值相同 如果UNIQUE键基于一列，则允许空值 可定义在表级或列级 自动创建UNIQUE索引,5）PRI
12、MARY KEY约束 为表创建一个主键；每个表只允许一个主键 要求列值是唯一的 主键的任一部分不允许为空值 可在表级或列级定义 自动创建UNIQUE索引,6）FOREIGN KEY约束 指定一列或多列作为外键 可在表级或列级定义 必须与父表中现有的值相匹配或为NULL FOREIGN KEY约束关键字： FOREIGN KEY REFERENCES ON DELETE CASCADE,7）CHECK约束 定义每行必须满足的条件 表达式不允许： 参照伪列CURRVAL、NEXTVAL、LEVEL、ROWNUM 调用SYSDATE、UID、USER、USERENV函数 引用其它行值的查询 可定义在
13、表级或列级,6、根据表实例图创建表 1）创建一个脚本文件，加入CREATE TABLE语法 2）映射列名、数据类型及长度 3）映射NOT NULL约束（除PRIMARY KEY），为列级约束 4）映射PRIMARY KEY约束 5）映射UNIQUE、CHECK、FOREIGN KEY约束 6）保存并执行脚本文件,7、根据另一个表创建表 语法：CREATE TABLE table(column,column,.)AS subquery;指定列的个数应与子查询列的个数相匹配,三、修改表,1、概述 ALTER TABLE DROP TABLE 其它影响表的命令有：RENAME、TRUNCATE、CO
14、MMENT 发出这些DDL命令时，会自动提交,2、增加列： 语法： ALTER TABLE table ADD (column datatype DEFAULT exprNOT NULL,column datatype.);新列成为最后一列,3、修改列 语法： ALTER TABLE table MODIFY (column datatype DEFAULT exprNOT NULL,column datatype,);可改变列的数据类型、长度、缺省值及NOT NULL列约束,4、删除列 ALTER TABLE table DROP COLUMN col;5、增加约束： 语法： ALTER T
15、ABLE table ADD CONSTRAINT constraint type (column);使用MODIFY子句增加NOT NULL约束,6、删除约束： ALTER TABLE table DROP PRIMARY KEY|UNIQUE (column) |CONSTRAINT constraint CASCADE;7、激活或禁止约束 ALTER TABLE table DISABLE |ENABLE CONSTRAINT constraint CASCADE;,8、删除表 DROP TABLE table CASCADE CONSTRAINTS;CASCADE CONSTRAINT
16、S选项将删除依赖的完整性约束 不能回滚本命令,9、改变对象的名字 语法： RENAME old_name TO new_name;必须是对象的拥有者,10、物理删除表 语法： TRUNCATE TABLE table;是一条DDL命令 使用本命令不能回滚删除的行,11、添加注释 语法： COMMENT ON TABLE table | COLUMN table.column IS text;可使用空字符串清除注释 注释可通过如下数据字典视图查看： ALL_COL_COMMENTS USER_COL_COMMENTS ALL_TAB_COMMENTS USER_TAB_COMMENTS,四、视图
17、,1、概述 视图是基于表或其它视图的逻辑表 优点 限制数据库存取 简化查询 数据独立性 相同数据的不同表现,2、创建视图语法： CREATE OR REPLACE VIEW view (alias,alias.) AS subquery WITH CHECK OPTION CONSTRAINT constraint WITH READ ONLY子查询可包含复杂的SELECT语法 可用CREATE OR REPLACE修改视图,3、在视图上执行DML操作 如果视图包含下列情况，则不能删除行： 组函数 GROUP BY子句 DISTINCT命令,如果视图包含下列情况，则不能修改视图中的数据： 上述
18、任一条件 由表达式定义的列ROWNUM伪列 如果视图包含下列情况，则不能增加数据： 上述任一条件 视图中没有包含基表中的所有NOT NULL列,WITH CHECK OPTION子句确保视图上进行的DML仍在视图域中WITH READ ONLY选项确保不能进行DML操作4、查看视图信息 USER_VIEWS,5、删除视图 语法： DROP VIEW view;删除视图不会丢失数据,第六章 其它数据库对象和数据字典,一、序列,1、概述 序列自动产生唯一的数值 是一个可共享的对象 通常被用于创建主键值,2、创建序列语法： CREATE SEQUENCE nameINCREMENT BY nSTAR
19、T WITH nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCYCLECACHE n | NOCACHE,3、查看序列信息USER_SEQUENCES,4、使用序列 NEXTVAL和CURRVAL伪列 NEXTVAL返回下一个可用序列值 CURRVAL包含当前的序列值：在CURRVAL有值之前，必须发出NEXTVAL,5、修改序列 语法： ALTER SEQUENCE sequenceINCREMENT BY nMAXVALUE n | NOMAXVALUEMINVALUE n | NOMINVALUECYCLE | NOCY
20、CLECACHE n | NOCACHE,可修改增量值、最大值、最小值、循环选项、及缓冲选项 必须是拥有者或有ALTER该序列的权限 只影响以后的序列值6、删除序列 语法： DROP SEQUENCE sequence;,二、索引,1、概述 索引是数据库对象 Oracle服务器用来加速行的检索 通过使用快速路径存取方法加快数据的定位，以减少磁盘的I/O 独立于其索引的表 由Oracle服务器自动使用并维护,2、索引的创建 自动创建 手工创建3、索引类型 唯一索引：确保列有唯一值 非唯一索引：加速查询 单列索引：索引只使用一列 复合索引：索引使用多列,4、创建索引 语法：CREATE INDEX
21、 index ON table(column,column.);,应建索引： WHERE子句或连接条件中频繁使用的列 列包含的值范围很大 列包含大量空值 表很大，且大部分查询检索的行少于2-4% 很多索引不是总能加速查询 不创建索引 表很小 列不经常作为查询条件使用 大部分查询要检索多于2-4%的行 表经常更新,5、查看索引信息USER_INDEXESUSER_IND_COLUMNS,6、删除索引 语法：DROP INDEX index;要删除一个索引，必须是索引的拥有者或具有DROP ANY INDEX权限,三、同义词,1、通过创建同义词（对象的另一名称）简化对对象的访问 2、创建语法： C
22、REATE PUBLIC SYNONYM synonym FOR object; 只有DBA可创建并删除公有同义词 3、删除同义词： DROP SYNONYM synonym;,四、其它对象,过程 函数 包 触发器,五、数据字典,1、Oracle数据库的表 用户表： 用户创建并维护的一组表 包含用户信息 数据字典： Oracle服务器创建并维护的一组表 包含数据库信息,2、数据字典视图 四类视图（前缀） USER用户拥有的对象 ALL用户有权存取的对象 DBA所有的数据库对象 V$服务器性能 其它视图 DICTIONARY IND,3、例 查询USER_CONSTRAINTS来查看所有约束的定义和名字查询USER_CONS_COLUMNS视图来查看与约束名相关的列,
