数据库系统应用开发实用教程电子课件第章管理表PPT课件.pptVIP

1 第4章管理表 1 概述 表是数据库中最基本和最重要的模式对象 是数据实际存放的地方 其他许多数据库对象 索引 视图等 都以表为基础 关系数据库中的表其存储数据的逻辑结构是一张二维表 由行和列两部分组成 表中的一行为一条记录 描述一个实体 表中的一列用于描述实体的一个属性 2 主要工作 创建表是进行数据库中数据存储管理的基础 也是应用程序开发的第一步 在创建表之前应根据应用的需要 做好表的规划与设计工作 包括表名 列名 列类型 约束和存储空间及位置等 管理表还包括对表中记录的相关操作如插入 修改 删除等 3 经过分析用户需求 并对需求进行综合 归纳与抽象 形成一个独立于具体DBMS的概念模型 并将概念结构转换为Oracle所支持的数据模型 然后到数据库物理设计阶段选取其物理结构 这一阶段需要构建最基本的数据库对象 数据表 并对表进行数据录入工作 4 工作情景 了解管理表的常用命令 熟练使用常用命令 任务分析 4 任务分解 任务1 创建用户 任务2 对用户授权 任务3 利用SQL语句创建表 任务4 利用子查询创建表 任务5 创建使用BLOB列存储图像的表 任务6 为表添加或修改约束 任务7 为表录入或修改数据 任务8 使用提交和撤销命令进行交或回滚数据 使用保存点进行小事务处理 5 学习目标 目标1 了解数据库用户和模式 目标2 了解授予和回收用户权限 目标3 掌握创建表的命令及各种Oracle数据类型 目标4 掌握利用子查询创建表的方法及好处 目标5 掌握使用BLOB列存储图像的方法 目标6 掌握各种不同的约束类型及管理约束 目标7 掌握数据操作语言 熟练进行增加 删除 修改数据 目标8 掌握事物控制语言 6 7 任务1 创建用户使用createuser命令 每个用户都有一个默认的表空间和临时表空间 如果没有指定 Oracle10g将USERS设为默认表空间 将TEMP作为临时表空间 任务1 创建用户 7 8 创建用户命令 语法格式 CREATEUSERusernameidentifiedbypassword defaulttablespacetablespace temporarytablespacetablespace QUOTA 正整数 K M UNLIMITED ON表空间名 例 创建一个用户usera SQL connectsystem orcl SQL CREATEUSERuseraidentifiedbyuseradefaulttablespaceuserstemporarytablespacetemp 8 实训1 实训项目 创建用户 实训内容与步骤 1 创建用户 P126 创建名为stu user的用户 创建名为teach user的用户 9 10 任务2 用户的权限分为两种 系统权限和对象权限 授权利用GRANT语句来完成 任务2 对用户授权 10 11 授予权限命令 1 系统权限授权语法格式 GRANTsystem priv roleTOuser WITHADMINOPTION 说明 system priv 要授予的系统权限 WITHADMINOPTIN 向其他用户授权的能力传递给被授予者 只有拥有createsession权限的用户才能连接到数据库 11 12 授予权限命令 例 对用户usera授予连接数据库的权限 SQL GRANTcreatesessiontousera 例 为新用户直接授予Oracle中预定义的CONNECT角色 SQL GRANTconnecttousera 例 授予RESOURCE角色 SQL GRANTresourcetousera 12 13 授予权限命令 2 对象权限授权语法格式 GRANTobject privONobject nameTOuser WITHGRANTOPTION 说明 object priv 要授予的对象权限 ONobject name 表示某一个具体对象 如表 视图等 WITHGRANTOPTIN 同前 13 14 授予权限命令 例 为用户usera授予scott用户的emp表的查询 更新和删除操作权限 SQL connectscott tiger SQL GRANTSELECTONempTOusera SQL GRANTUPDATEONempTOusera SQL GRANTDELETEONempTOusera 例 将scott用户的emp表的所有权授予usera SQL GRANTALLONempTOusera 14 15 收权命令 3 收回权限语法格式 REVOKEsystem priv roleFROMuser WITHADMINOPTION 说明 system priv 是授予用户的系统权限 15 16 收权命令 例 回收用户usera的SCOTT用户下emp表的SELECT和UPDATE对象权限 SQL REVOKESELECT UPDATEONempFROMusera 例 回收用户usera的SCOTT用户下EMP表的所有权限 SQL REVOKEALLonempFROMusera 16 17 修改用户口令 使用ALTERUSER语句对用户信息进行修改 ALTERUSER语句最常用的情况是用来修改用户口令 任何用户都可以修改自己的口令 例 修改用户usera的口令 SQL ALTERUSERuseraidentifiedbynewpsw 17 18 删除用户 使用DROPUSER语句删除已有的用户 如果要删除的用户模式中包含有数据对象 则必须在DROPUSER子句中指定cascade关键字 否则Oracle将返回错误信息 例 删除用户usera 并且同时删除他所拥有的所有表 索引等对象 SQL DROPUSERuseracascade 18 19 数据类型 数据类型的作用在于指明存储数值时需要占据的内存空间大小 在创建表时要为每个列指明其数据类型 可以使用Oracle内置的数据类型 也可使用用户自定义的数据类型 Oracle内置的数据类型主要有数值类型 字符类型 日期类型 LOB类型 二进制类型和行类型等 19 20 数据类型 1 数值类型NUMBER P S 用于存储整数和实数 P是精度 表示数值的总位数 最大38位 S是刻度范围 可在 84 127间取值 例 NUMBER 5 2 可以用来存储表示 999 99 999 99间的数值 P S可以在定义时省略 例如 NUMBER 6 NUMBER等 20 21 数据类型 2 字符类型 CHAR n BYTE CHAR 用于存储定长的字符串 例 CHAR n n为字符串长度 最大为2000字节 VARCHAR2 n BYTE CHAR 描述变长字符串 例 VARCHAR2 n n为字符串长度 最大为4000字节 当列中保存的字符串长度小于n时 按实际长度分配空间 LONG 用于存储高达2GB的的可变字符串 NCHAR NVARCHAR2 国家字符集 用来存储Unicode类型字符串 21 22 数据类型 3 日期类型 DATE 用于存储固定长度的日期和时间数据 TIMESTAMP n 允许存储小数形式的秒值 INTERVALYEAR n TOMONTH 存储以年份和月份表示的时间段 例 INTERVAL 3 6 YEARTOMONTH表示3年6个月 INTERVALDAY m TOSECOND n 存储以天数 小时数 分钟数和秒数表示的时间段 例 INTERVAL 36 20 10 DAYTOSECOND表示3天6小时20分10秒 22 23 数据类型 4 LOB类型 CLOB 存储大量字符数据 NCLOB 用来存储可变长度的Unicode字符数据 BLOB 存储较大的可变长度的二进制对象 如图形 视频剪辑和声音文件 BFILE 存储指向二进制格式文件的定位器 23 24 数据类型 5 RAW和LONGRAW类型用来存储二进制数据 RAW 类似于CHAR 声明方式RAW L L为长度 以字节为单位 作为数据库列最大2000 作为变量最大32767字节 LONGRAW 类似于LONG 作为数据库列最大存储2G字节的数据 作为变量最大32760字节 24 25 数据类型 6 行类型 ROWID ROWID数据类型被称为 伪列类型 用于Oracle内部保存表中每条记录的物理地址 Oracle通过ROWID可最快地定位某行具体数据的位置 在使用ROWID字段时必须显式指定名称 UROWID 行标识符 用于表示索引化表中行的逻辑地址 25 实训2 实训项目 授权 收权 更改用户密码 删除用户 实训内容与步骤 P126 1 更改用户密码 更改用户stu user密码为stu123 2 授权 将SCOTT用户emp表的查看更新权限授予用户stu user 3 回收权限 回收用户stu user对SCOTT用户emp表的查看更新权限 4 删除用户stu user 26 27 任务3 表是数据实际存放的地方 表的逻辑结构是一张二维表 表中的一行为一条记录 表中的一列为一个属性 任务3 利用SQL语句创建表 27 28 创建表命令 语法格式 CREATETABLE schema table name column namedatatype DEFAULTexpression column constraint n PCTFREEinteger PCTUSEDinteger INITRANSinteger MAXTRANSinteger TABLESPACEtablespace name STORAGEstorage clause CLUSTERcluster name cluster column n ASsubquery 28 29 创建表命令 说明 table name 表的名称 column name 指定表的一个列的名字 Datatype 该列的数据类型 DEFAULTexpresssion 指定由expresssion表达式定义的默认值 column constraint 定义一个完整性约束作为列定义的一部分 29 30 创建表命令 column constraint子句的基本语法格式为 CONSTRAINTconstraint name NOT NULL UNIQUE PRIMARYKEY REFERENCES schema table name column name CHECK condition 其中 NOT NULL定义该列是否允许为空 UNIQUE定义字段的唯一性 PRIMARYKEY定义字段为主键 REFERENCES定义外键约束 CHECK condition 定义该列数据必须符合的条件 30 31 创建表命令 PCTFREE 指定数据块中必须保留的最小空闲空间比例 默认值为10 PCTUSED 设置数据块是否可用的界限 默认值为40 INITRANS 指定分配给表的每一数据块中的事务条目的初始数量 默认值为1 MAXTRANS 指定可更新分配给表的数据块的最大并发事务数 默认值为数据块大小的函数 TABLESPACE 指定表存放在由tablespace name指定的表空间中 如果不指定此项 则表存放在默认表空间中 CLUSTER 指定该表是命名为cluster name的簇的一部分 ASsubquery 表示将由子查询返回的行插入到所创建的表中 31 32 创建表命令 STORAGE 指定表的存储特征 此子句的基本格式为 STORAGE INITIALintegerK integerMNEXTintegerK integerMMINEXTENTSintegerMAXEXTENTSinteger UNLIMITEDPCTINCREASEintegerFREELISTSintegerFREELISTGROUPinteger 其中 INITIAL 指定为表分配的第一个区大小 NEXT指定第一个扩展区的大小 MINEXTENTS为创建段时已分配的总区数 MAXEXTENTS表示Oracle数据库可以分配给该对象的总区数 PCTINCREASE指定每个区相对于上一个区的增长百分比 FREELISTS指定表 簇或索引的每个空闲列表组的空闲列表数量 FREELISTGROUP指定表 簇或索引的空闲列表组的数量 32 33 创建表命令 例 创建雇员表 Employee 包括员工号 员工名 工作职位 主管领导 雇佣日期 基本工资 补贴 部门号等员工信息 SQL CREATETABLEEmployee 雇员信息 EmpIdNUMBER 4 CONSTRAINTPK EMPRIMARYKEY ENameVARCHAR2 10 NOTNULL jobVARCHAR2 9 MGRNUMBER 4 HireDateVARCHAR2 20 BaseSalNUMBER 7 2 CommNUMBER 7 2 DeptIdNUMBER 2 CONSTRAINTFK DEPTREFERENCESDepartment DepId 33 34 创建表命令 例 创建部门表 Department 包括部门号 部门名称 部门描述信息 SQL CREATETABLEDepartment DepIdNUMBER 2 CONSTRAINTPK DEPPRIMARYKEY DNameVARCHAR2 16 UNIQUE DescribesVARCHAR2 13 34 35 创建表命令 例 创建工资级别表 Salgrade 包括级别 最低工资 最高工资信息 SQL CREATETABLESALGRADE 工资级别 GRADENUMBER LOSALNUMBER HISALNUMBER 35 实训3 实训项目 创建表 实训内容与步骤 P126 1 创建雇员表 2 创建部门表 3 创建职位信息表 4 创建用户信息表 36 37 任务4 如要创建的新表和数据库中已存在的表有较多相同的字段 可在CREATETABLE中利用子查询来简化创建表的工作 任务4 利用子查询创建表创建表 37 38 利用子查询创建表 例 将30部门的员工名 工作职位 工资保存在新表EMP 30中 SQL CREATETABLEEMP 30 ename job sal asSELECTename job salFROMempwheredeptno 30 38 实训4 实训项目 用子查询创建表 实训内容与步骤 P132 创建一个表 保存工资高于2500元的员工的员工号 员工名和部门号 语句为 SQL CREATETABLEemployee info empid ename deptid asSELECTempid ename deptidFROMemployeewheresal 2500 39 40 任务5 大对象 LOB 数据类型用于存储非结构化数据 其中包括文本 图形图像 视频剪辑和声音剪辑 任务5 创建使用BLOB列存储图像的表 40 41 使用LOB类型数据库列 LOB数据类型的数据库列用于存储定位器 而该定位器指向大对象的存储位置 这些大对象可以存储在数据库中 也可以存储在外部文件中 当LOB值为NULL时不包含定位器 选择空的LOB列可返回一个定位器 它可以通过DBMS LOB程序包用数据填充LOB 在INSERT或UPDATE语句中使用特殊函数 如EMPTY BLOB 和EMPTY CLOB 将NULL或非NULL的LOB初始化为空 41 42 使用LOB类型数据库列 例 创建blob列存储图像的表 CREATETABLEphoto photo nonumber imageblob DECLAREl bfileBFILE l blobBLOB BEGININSERTINTOphoto photo no image VALUES 1 EMPTY BLOB RETURNimageINTOl blob l bfile BFILENAME IMAGS g1 JPG DBMS LOB OPEN l bfile DBMS LOB FILE READONLY DBMS LOB LOADFROMFILE l blob l bfile DBMS LOB GETLENGTH l bfile DBMS LOB CLOSE l bfile COMMIT END 42 43 表约束管理 在数据库中存储数据 必须保证数据的正确性 准确性 一致性和完整性 ORACLE使用完整性约束 integrityconstraints 防止不合法的数据写入数据库 约束可以通过使用CREATETABLE语句指定 也可以创建表之后使用ALTERTABLE添加或修改 43 44 约束的类型 约束的类型 1 非空约束 NOTNULL 限制为某个列提供NULL值 2 唯一约束 UNIQUE 保证在指定的列中没有重复值 3 检查约束 CHECK 检查约束用来限制列值所允许的取值范围 4 主键约束 PRIMARYKEY 唯一的标识出表的每一行 并且防止出现NULL值 一个表只能有一个主键约束 5 外键约束 FOREIGNKEY 通过使用公共列在表之间建立一种父子 parent child 关系 在表上定义的外部键可以指向主键或者其他表的唯一键 44 45 定义约束 约束定义通过查询USER CONSTRAINTS可以获得相关信息 语法格式 CREATETABLE schema table columndatatype DEFAULTexpr column constraint 列级约束 table constraint 表级约束 45 实训5 实训项目 创建表时使用LOB列存储图像 实训内容与步骤 P115 在scott用户下创建具有blob列存储图像的表 CREATETABLEphoto photo nonumber imageblob 46 47 任务6 任务6 为表添加或修改约束 47 48 添加约束 语法格式 ALTERTABLEtable nameADD CONSTRAINTconstraint name constraint type column1 name column2 name condition 48 49 添加约束 例 以表Employee为例 说明定义约束和添加约束 SQL CREATETABLEEmployee EmpIdNUMBER 4 CONSTRAINTP PKPRIMARYKEY ENameVARCHAR2 10 NOTNULL JobVARCHAR2 40 MGRNUMBER 4 HireDateVARCHAR2 20 BaseSalNUMBER 7 2 CommNUMBER 7 2 DeptIdNUMBER 2 49 50 添加约束 例 为BaseSal列添加检查约束 SQL ALTERTABLEEmployeeADDCONSTRAINTP CKCHECK BaseSal 1000 例 为Job列添加空 非空键约束 使用MODIFY子句代替ADD子句 SQL ALTERTABLEEmployeeMODIFYJobNOTNULL 例 为DeptId列添加外键约束 SQL ALTERTABLEEmployeeADDCONSTRAINTP FKFOREIGNKEY DeptId REFERENCESDepartment DepId 50 51 设置约束状态 约束有激活 Enable 和禁用 Disable 两种状态 默认情况下 约束创建之后就一直起作用 禁用约束是一种暂时的方法 在禁用约束状态下完成操作之后 还应该设为激活状态 约束定义时使用关键字DISABLE来设置为禁用状态 使用关键字DISABLE来改变其状态 51 52 禁用约束 例 创建表时将EmpId的主键约束设置为禁用 SQL CREATETABLEEmployee EmpIdNUMBER 4 PRIMARYKEYDISABLE 例 修改BaseSal列的检查约束为禁用状态 SQL ALTERTABLEEmployeeDISABLECHECK BaseSal 1000 或者SQL ALTERTABLEEmployeeDISABLEP CK 52 53 激活约束 例 修改BaseSal列的检查约束为激活状态 SQL ALTERTABLEEmployeeENABLEP CK 53 54 检查约束信息 数据字典视图ALL CONSTRAINTS USER CONSTRAINTS DBA CONSTRAINTSALL CONS COLUMNS USER CONS COLUMNS DBA CONS COLUMNS 包含了约束名称 约束类型 状态等信息 包含了约束的列信息 可查询约束所对应的列 54 55 检查约束信息 例 查询Employee表中的所有约束 SQL SELECTCONSTRAINT NAME CONSTRAINT TYPE STATUSFROMUSER CONSTRAINTSWHERETABLE NAME EMPLOYEE 例 查询Employee表中各个约束所对应的列 SQL SELECTCONSTRAINT NAME COLUMN NAMEFROMUSER CONS COLUMNSWHERETABLE NAME EMPLOYEE 55 56 修改表 表创建后 可以利用ALTERTABLE命令修改表 包括增加列 修改列的属性和删除列 可进行表参数的修改以及表的重命名和约束的添加 修改 删除和禁用等 语法格式 ALTERTABLE schema tablename ADD columnnamedatatype DEFAULTexpression column constraint n MODIFY columnnamedatatype DEFAULTexpression column constraint n DROPCOLUMNcolumnname 56 57 修改表 1 添加列使用ALTERTABLE ADD语句实现表中列的添加 例 修改雇员表 Employee 在列中增加一列存放性别数据 SQL ALTERTABLEEmployeeADDSexCHAR 2 57 58 修改表 2 修改列类型使用ALTERTABLE MODIFY语句实现表中列的修改 例 修改雇员表 Employee 中列的宽度 小数位 数据类型或缺省值 SQL ALTERTABLEEmployeeMODIFYEnamevarchar2 16 注 如果要更改数据类型 则要修改的列内容必须为空 58 59 修改表 3 修改列名使用ALTERTABLE RENAMECOLUMN语句修改列的名称 例 将表 Employee 中HireDate列名修改为Hire Date SQL ALTERTABLEEmployeeRENAMECOLUMNHireDatetoHire Date 59 60 修改表 4 删除列名使用ALTERTABLE DROPCOLUMN语句直接删除列 例4 26 从雇员表 Employee 中删除列sex SQL ALTERTABLEEmployeeDROPCOLUMNsex 例4 27 从雇员表 Employee 中删除列sex Comm SQL ALTERTABLEEmployeeDROP sex Comm 60 61 修改表 5 表名的修改表名的修改使用ALTERTABLE RENAMETO语句实现 也可以直接执行RENAME TO语句 例4 28 将雇员表 Employee 重命名为Empl SQL ALTERTABLEEmployeeRENAMETOEmpl 或者 SQL RENAMEEmployeeTOEmpl 61 62 修改表 6 表注释的添加可以使用COMMENTON IS语句为表或表中的列添加或补充注释 例 SQL COMMENTONTABLEEmployeeIS 雇员表 SQL COMMENTONCOLUMNEmployee BasesalIS 基本工资 62 63 删除表结构 使用DROPTABLE删除表 删除表时 表中存储的数据也将同时被删除 如果在删除表同时要删除其他表中的相关外键约束 使用CASCADECONSTRAINTS子句 语法格式 DROPTABLEtable name CASCADECONSTRAINTS 例 删除salgrade表 SQL DROPTABLEsalgrade 63 实训6 实训项目 约束管理 实训内容与步骤 1 在创建表的同时进行约束管理 P126 2 修改表和修改约束 P132 64 65 任务7 数据操纵语言 DML 用来操纵表或视图的数据 INSERT命令插入数据 UPDATE命令更新数据 DELETE删除数据 SELECT命令查询数据 任务7 为表录入或修改数据 65 66 插入数据 1 单行插入INSERT语句用于在指定的表中添加数据 语法格式 INSERTINTOtable name column list VALUES values 例 向Department表插入如下数据 SQL INSERTINTODepartmentVALUES 50 SUPPORTING ZHUHAI 66 67 插入数据 2 利用子查询进行多值插入语法格式 INSERTINTOtable name column1 column2 subquerySubquery 表示从有一个子查询来向表中插入数据 例 利用子查询向Department表插入多条记录 SQL INSERTINTODepartmentSELECT FROMscott dept 例 向Employee表插入多条记录 SQL INSERTINTOEmployeeSELECT FROMscott emp 67 68 修改数据 1 直接修改格式语法格式 UPDATEtable nameSETcolumn name value WHEREcondition 例 将编码为 7521 的雇员的薪水改为1500元 SQL UPDATEEmployeeSETbasesal 1500WHEREempid 7521 例 将部门号为 20 雇员的基本工资增加300元 SQL UPDATEEmployeeSETbasesal basesal 300WHEREDEPTNO 20 68 69 修改数据 2 嵌套修改格式语法格式 UPDATEtablename1SET column name1 column name2 SELECTcolumn name1 column name2 FROMtablename2 WHEREcondition 例 将Employee表中编号为 7521 雇员的职位和工资修改为与EMP表中编号为 7566 雇员的职位和工资 SQL UPDATEEmployeeSET job basesal SELECTjob salFROMEMPWHEREempno 7566 WHEREempid 7521 69 70 删除数据 1 使用DELETE命令DELETE语句使用户可以删除表中的一条或多条记录 语法格式 DELETEFROMtable nameWHEREcondition例 将编码为 7499 的员工信息删除 SQL DELETEFROMEmployeeWHEREempid 7499 70 71 删除数据 2 使用TRUNCATETABLE命令使用TRUNCATETABLE可以删除表中的全部记录 语法格式 TRUNCATETABLEtable name例 用TRUNCATE删除Employee表 SQL TRUNCATETABLEEmployee 71 实训7 实训项目 操作表 实训内容与步骤 1 插入数据 P132 2 更新数据 P134 3 联合查询数据 P134 72 73 任务8 事务 Transaction 可以被看作一个工作逻辑单元 是一系列作为一个单元被全部提交或全部回滚的SQL语句