11_第07章 数据库对象管理.ppt

第7章数据库对象管理 操作系统认证方式 OSDBA对应的用户组为ORA DBA OSOPER对应的用户组为ORA OPER Review 创建用户修改用户权限管理语句删除用户 1 授予系统权限 GRANTTO 2 授予数据对象权限 GRANTONTO 用户的管理 第7章数据库对象管理 Review 角色 不使用角色分配权限 使用角色分配权限 权限 用户 Manager 角色是若干权限的组合 第7章数据库对象管理 Oracle系统角色创建角色对角色授权指定用户的角色修改角色删除角色 Review 角色的管理 第7章数据库对象管理 第7章数据库对象管理 本章主要介绍Oracle数据库中表 索引 视图和序列的管理方法 主讲 李翠霞电话 63887291email qyliying 办公室 水环楼306 第7章数据库对象管理 本章学习目标 掌握表的创建 修改和删除方法 掌握对表中数据的插入 更新和删除的方法 掌握使用select语句进行数据查询的方法 掌握视图的概念及视图的创建 修改和删除方法 理解索引的概念 掌握如何创建 管理和删除索引 掌握创建 修改 删除和使用序列的方法 第7章数据库对象管理 本章主要内容 7 1表管理7 2数据查询7 3视图管理7 4索引管理7 5序列管理 SQLStatements SELECTINSERTUPDATEDELETEMERGECREATEALTERDROPRENAMETRUNCATECOMMENTGRANTREVOKECOMMITROLLBACKSAVEPOINT Datamanipulationlanguage DML Datadefinitionlanguage DDL Transactioncontrol Datacontrollanguage DCL TablesUsedintheCourse EMPLOYEES DEPARTMENTS JOB GRADES 第7章数据库对象管理 7 1表管理 创建表修改表删除表插入数据修改数据删除数据 第7章数据库对象管理 创建表 表Users的结构 第7章数据库对象管理 创建表 管理页面 第7章数据库对象管理 创建表 表管理页面 第7章数据库对象管理 创建表 选择方案 第7章数据库对象管理 创建表 选择表组织 第7章数据库对象管理 创建表 表管理器 第7章数据库对象管理 数据类型 第7章数据库对象管理 创建表 数据类型NUMBER可以用来表示所有的数值数据 fieldnameNUMBER precision scale VARCHAR2用来表示变长的字符串数据类型 fieldnameVARCHAR2 max length CHAR保存固定长度字符串 最大长度为2KB DATE保存固定长度的日期数据 BLOB保存二进制大对象 通常用来保存图像和文档等二进制数据 CLOB保存字符型大对象 varchar2数据类型最多只能保存4000个字符 如果要保存的字符串数据超过此范围 应使用clob数据类型 precision是最大长度 scale是小数点后的位数 第7章数据库对象管理 创建表 CREATETABLE语句的基本使用方法如下所示 Createtable schema table name columndatatype default expression column constraint table constraint tablespacetablespace name 例 创建表Users SQL语句如下 CREATETABLEUsers UserIdNumberPrimaryKey UserNameVarchar2 40 NOTNULL UserTypeNumber 1 UserPwdVarchar2 40 第7章数据库对象管理 创建表 数据完整性 数据完整性是关系数据库模型的基本原则 是用户在表上定义的一系列规则或约束条件 以及在表之间定义的一系列相互关系 DEPTNO的值必须与表DEPT中的DEPTNO值匹配 EMPNO每行都必须非空且惟一 数据完整性的作用 强制要求数据库中只能接受正确的 合理的数据 防止错误的或无效的数据被插入到表中 第7章数据库对象管理 数据完整性分为三类 实体完整性参照完整性用户定义的完整性实体完整性 规则1 主键的各个属性都不能为空值 参照完整性 规则2 外键或者取空值 或者等于被参照关系中的主键的某个值 用户定义的完整性 规则3 属性 或属性组 的取值应当满足用户定义的约束条件 创建表 数据完整性 第7章数据库对象管理 Oracle通过为表中的列定义各种约束来实现数据完整性 约束条件NotNull 防止NULL值进入到指定的列 Unique 保证在指定的各列中没有重复的值 Check 检查在约束中指定的条件是否得到了满足 Primarykey 用来惟一地标识出表的每一行 并且防止出现NULL值 一个表只能有一个主键约束 Foreignkey 通过使用公共列在表之间建立一种父子关系 在表上定义的外键可以指向其他表的主键或者惟一键 约束级别列级约束表级约束 创建表 数据完整性的定义 第7章数据库对象管理 创建表 如果要在自己的方案中创建表 要求用户必须具有CREATETABLE系统权限 如果要在其他方案中建表 则要求用户必须具有CREATEANYTABLE系统权限 Createtable语法如下 Createtable schema table name columndatatype default expression column constraint table constraint tablespacetablespace name 第7章数据库对象管理 注意事项 如果不指定约束名Oracleserver自动按照SYS Cn的格式指定约束名在什么时候创建约束 建表的同时建表之后可以在表级或列级定义约束可以通过数据字典视图查看约束 创建表 第7章数据库对象管理 创建表 约束的定义格式 CREATETABLEemployees employee idNUMBER 6 last nameVARCHAR2 25 NOTNULL salaryNUMBER 8 2 commission pctNUMBER 2 2 hire dateDATECONSTRAINTemp hire date nnNOTNULL NOTNULL的定义格式 只能定义在列级 第7章数据库对象管理 NOTNULL约束 保证列值不能为空 第7章数据库对象管理 UNIQUE约束的定义格式 CREATETABLEemployees employee idNUMBER 6 last nameVARCHAR2 25 NOTNULL emailVARCHAR2 25 salaryNUMBER 8 2 commission pctNUMBER 2 2 UNIQUE hire dateDATENOTNULL CONSTRAINTemp email ukUNIQUE email 创建表 约束的定义格式 第7章数据库对象管理 UNIQUE约束 EMPLOYEES UNIQUE约束 INSERTINTO 第7章数据库对象管理 主键的定义格式 Createtablestudents Snovarchar2 10 primarykey Snamevarchar2 8 notnull Ssexvarchar2 2 notnullcheck Ssex F orSsex M Sagenumber Sdeptvarchar2 15 default Computer 创建表 约束的定义格式 ConstraintPK Snoprimarykey Sno 第7章数据库对象管理 PRIMARYKEY约束 DEPARTMENTS PRIMARYKEY 第7章数据库对象管理 外键的定义格式 CREATETABLEemployees employee idNUMBER 6 last nameVARCHAR2 25 NOTNULL emailVARCHAR2 25 salaryNUMBER 8 2 commission pctNUMBER 2 2 hire dateDATENOTNULL department idNUMBER 4 CONSTRAINTemp dept fkFOREIGNKEY department id REFERENCESdepartments department id CONSTRAINTemp email ukUNIQUE email 创建表 约束的定义格式 第7章数据库对象管理 可以定义在表级或列级 CREATETABLEemployees employee idNUMBER 6 last nameVARCHAR2 25 NOTNULL emailVARCHAR2 25 salaryNUMBER 8 2 commission pctNUMBER 2 2 hire dateDATENOTNULL department idNUMBER 4 CONSTRAINTemp dept fkFOREIGNKEY department id REFERENCESdepartments department id CONSTRAINTemp email ukUNIQUE email Referencesdepartments department id 第7章数据库对象管理 FOREIGNKEY约束的关键字FOREIGNKEY 在表级指定子表中的列REFERENCES 标示在父表中的列ONDELETECASCADE 当父表中的列被删除时 子表中相对应的列也被删除ONDELETESETNULL 子表中相应的列置空ONDELETENOACTION 不进行任何操作 第7章数据库对象管理 FOREIGNKEY约束 DEPARTMENTS EMPLOYEES Students Courses Enrollment 第7章数据库对象管理 在数据库StuDB中定义一个表 表名为Students 表中各列的要求见表 创建表 语句举例 第7章数据库对象管理 Createtablestudents Snovarchar2 10 primarykey Snamevarchar2 8 notnull Ssexvarchar2 2 notnullcheck Ssex F orSsex M Sagenumber Sdeptvarchar2 15 default Computer 列级约束 第7章数据库对象管理 Createtablestudents Snovarchar2 10 Snamevarchar2 8 notnull Ssexvarchar2 2 notnull Sagenumber Sdeptvarchar2 15 default Computer constraintPK SNOprimarykey Sno constraintCHK SSEXcheck Ssexin F M 表级约束 Students Courses Enrollment 第7章数据库对象管理 练习 练习 请写出创建表Courses和Enrollment的SQL命令 CREATETABLECOURSES CnoVARCHAR2 6 PRIMARYKEY CnameVARCHAR2 20 NOTNULL PreCnoVARCHAR2 6 CreditsNUMBER CREATETABLEENROLLMENT SnoVARCHAR2 10 CnoVARCHAR2 6 GradeNUMBER CONSTRAINTPK SCNOPRIMARYKEY SNO CNO CONSTRAINTFK SNOFOREIGNKEY SNO REFERENCESSTUDENTS SNO CONSTRAINTFK CNOFOREIGNKEY CNO REFERENCESCOURSES CNO 第7章数据库对象管理 查看约束信息 查询当前方案中某表的约束信息selecttable name constraint name constraint type deferred deferrable statusfromuser constraintswheretable name STUDENTS 查询定义了约束的列Selecttable name constraint name column name positionfromuser cons columnswheretable name ENROLLMENT 第7章数据库对象管理 7 1表管理 创建表修改表删除表插入数据修改数据删除数据 第7章数据库对象管理 修改表 查看和编辑表Users中的数据 第7章数据库对象管理 修改表 表在创建之后还允许对其进行更改 如添加或删除表中的列 修改表中的列 以及对表进行重新命名和重新组织等 普通用户只能对自己方案中的表进行更改 而具有ALTERANYTABLE系统权限的用户可以修改任何方案中的表 修改表Altertabletable nameAddcolumndatatype Modify columndatatype Drop unused column column name1 column name1 renamecolumncolumn nametonewname setunused column name1 column name1 第7章数据库对象管理 修改表 ALTERTABLE语句修改表 1 添加列 例 在表Users中添加一个列tmpcol SQL ALTERTABLEUsersADD tmpcolNUMBER 5 2 2 修改列名 例 将表Users中tmpcol列的名称修改为tmpcol 1 SQL ALTERTABLEUsersRENAMECOLUMNtmpcolTOtmpcol 1 第7章数据库对象管理 修改表 3 删除列 例 将表Users中的列tmpcol 1删除 SQL ALTERTABLEUsersDROPCOLUMNtmpcol 1 4 将列设置为不可用 例 将表Users中的列tmpcol 1设置为不可用 SQL ALTERTABLEUsersSETUNUSED tmpcol 1 5 删除不可用的列 例 删除表Users中所有的不可用列 SQL ALTERTABLEUsersDROPUNUSEDCOLUMNS 第7章数据库对象管理 添加约束 创建表之后如果需要添加约束 除NOTNULL以外 可以用以下语句实现 ALTERTABLEtable nameAdd CONSTRAINTconstraint name Constraint type col1 col2 condition 第7章数据库对象管理 Students Courses Enrollment 课程名唯一 第7章数据库对象管理 添加约束 例 修改Courses表的定义 要求课程名必须唯一 altertablecoursesaddconstraintUnique Cnameunique Cname 例 修改Students表的定义 要求学生的年龄不能为空 altertableStudentsmodifySagenotnull 只能用modify格式 第7章数据库对象管理 删除约束 ALTERTABLEStudentsDropCONSTRAINTCHK SSEX ALTERTABLECoursesDropUNIQUE Cname ALTERTABLECoursesDropPrimaryKeyCascade ALTERTABLEStudentsModifySageNULL 第7章数据库对象管理 删除约束 第7章数据库对象管理 删除约束 第7章数据库对象管理 删除约束 第7章数据库对象管理 7 1表管理 创建表修改表删除表插入数据修改数据删除数据 第7章数据库对象管理 删除表 删除选项窗口 DROPTABLE语句删除表 例 删除表Users DROPTABLEUsers 第7章数据库对象管理 7 1表管理 创建表修改表删除表插入数据修改数据删除数据 第7章数据库对象管理 插入数据 INSERT语句的基本使用方法如下所示 INSERTINTO 列名1 列名2 列名n VALUES 值1 值2 值n 例 向表Users中插入数据INSERTINTOUsers UserId UserName UserType UserPwd VALUES 1 Admin 1 Admin INSERTINTOUsers UserId UserName UserType Us