创建和管理数据表.ppt

模块三 创建和管理数据表 主讲 马建霞TELQ 48189407 使用Oracle数据库的开发流程 服务器端 安装Oracle服务器软件 创建数据库 安装时自动创建 配置监听器 安装时自动配置 启动Oracle实例 自动启动服务 安装Oracle客户端软件 配置网络服务名 以新用户登录Oracle 提交SQL查询 创建新用户并授权 创建用户表空间 客户端 目标任务 任务一 创建与管理表空间任务二 创建与管理用户任务三 创建数据表任务四 管理数据表 任务一 创建与管理表空间 表空间 tablespace 是Oracle数据库中最大的逻辑结构 它是数据库的逻辑划分 Oracle数据库就是由一个或多个表空间组成的 一个表空间由一个或多个数据文件组成 但一个数据文件只能属于一个表空间 所有的数据库对象都存放在指定的表空间中 但主要存放的是表 所以称作表空间 默认情况下系统会自己创建一个System的表空间 数据库的几个概念与写字楼中的概念的对应关系 子任务1 1 创建表空间 ORACLE可以创建的表空间大致有三种类型 1 TEMPORARY 临时表空间 用于临时数据的存放 创建临时表空间的语法如下 CREATETEMPORARYTABLESPACE SAMPLE 2 UNDO 还原表空间 用于存入重做日志文件 创建还原表空间的语法如下 CREATEUNDOTABLESPACE SAMPLE 3 用户表空间 最重要 也是用于存放用户数据的表空间可以直接写成 CREATETABLESPACE SAMPLE TEMPORARY和UNDO表空间是ORACLE管理的特殊的表空间 只用于存放系统相关数据 子任务1 1 创建表空间 续 利用CREATETABLESPACE命令创建和管理表空间 语法格式 CREATE UNDO TEMPORARY TABLESPACEtablespace nameDATAFILE TEMPFILE path filename SIZEinteger K M REUSE AUTOEXTEND OFF ON NEXTinteger K M MAXSIZE UNLIMITED integer K M MINIMUMEXTENTinteger K M DEFAULTSTORAGEstorage clause ONLINE OFFLINE LOGGING NOLOGGING PERMANENT TEMPORARY EXTENTMANAGEMENT DICTIONARY LOCAL AUTOALLOCATE UNIFORM SIZEinteger K M 子任务1 1 练习 1 创建大小为50M的表空间student 禁止自动扩展数据文件 2 创建一个临时表空间temp 文件的存放路径自定 空间的初始大小为10M 指定允许分配给数据文件的最大磁盘空间为200M 注意 可以用下面的语句查看表空间的信息Select fromdba tablespaces 子任务1 1 练习 下面两段创建表空间语句 1 CREATETEMPORARYTABLESPACEtemp2TEMPFILE F oracle temp01 ORA SIZE32MREUSEAUTOEXTENDONNEXT640K 2 CREATETABLESPACEdataLOGGINGDATAFILE F oracle data dbf SIZE50MREUSEAUTOEXTENDONNEXT10MMAXSIZE200MDEFAULTSTORAGE INITIAL16KNEXT32KMINEXTENTS1 请分别说出两条语名的含义 3 CREATETABLESPACE IMAGEDATA LOGGINGDATAFILE E ORACLE ORADATA DATA 01 DBF SIZE2000MREUSEAUTOEXTENDONNEXT51200KMAXSIZE3900M E ORACLE ORADATA XL DATA 02 DBF SIZE2000MREUSEAUTOEXTENDONNEXT51200KMAXSIZE3900M E ORACLE ORADATA XL DATA 03 DBF SIZE2000MREUSEAUTOEXTENDONNEXT51200KMAXSIZE3900M E ORACLE ORADATA XL DATA 04 DBF SIZE2000MREUSEAUTOEXTENDONNEXT51200KMAXSIZE3900MEXTENTMANAGEMENTLOCALSEGMENTSPACEMANAGEMENTAUTO 子任务1 2 修改表空间 语法格式 ALTERTABLESPACEtablespace name ADDDATAFILE TEMPFILE path filename SIZEinteger K M REUSE AUTOEXTEND OFF ON NEXTinteger K M MAXSIZE UNLIMITED integer K M RENAMEDATAFILE path filename nTOpath re filename n DEFAULTSTORAGEstorage clause ONLINE OFFLINE NORMAL TEMPORARY IMMEDIATE LOGGING NOLOGGING READONLY WRITE PERMANENT TEMPORARY 子任务1 2 修改表空间 续 练习1 增加表空间的容量任务要求 通过ALTERTABLESPACE命令把一个新的数据文件添加到student表空间 文件名为student1 路径自定 并指定AUTOEXTENDON和MAXSIZE300M 注意 尽管可以设置MAXSIZEUNLIMITED 但应总是规定一个文件的最大尺寸值 子任务1 2 修改表空间 续 2 修改原有文件的大小增加表空间的容量任务要求 修改Student表空间 设置每次以2M的大小自动扩展表空间 注意 修改表空间的容量需要用Alterdatabase命令 3 修改表空间的属性任务要求 修改Student表空间的属性 指定其将来的表 索引等不需要进行日志处理 提示 用altertablespace命令可能直接修改表空间的属性 子任务1 2 修改表空间 续 4 修改表空间的名称任务要求 将student表空间的名称改为student jrg 再改回为Student 语法格式 ALTERTABLESPACEold nameRENAMETOnew name5 设置数据库的默认表空间通常情况下 数据库的默认表空间为SYSTEM或USERS表空间 提示 可以用以下的语句查看用户的默认表空间Selectusername default tablespacefromdba users 任务要求 将Student表空间设置为数据库的默认表空间 语法格式 ALTERDATABASEDEFAULTTABLESAPCEtablespace name 子任务1 3 删除表空间 1 删除表空间test 但不删除其文件droptablespacetest 2 删除表空间data同时删除表空间的内容 了解 droptablespacedataincludingcontents 3 删除表空间data及其包含的内容以及数据文件droptablespacedataincludingcontentsanddatafiles 使用OEM创建表空间 以sys用户 sysdba连接身份登录OEM 出现 数据库 页 单击 存储 点击 表空间 如下图 使用OEM创建表空间 在客户端的OEM中可以查看表空间信息 如下图 任务二 创建与管理用户 Oracle默认用户只有用合法的用户帐号才能访问Oracle数据库Oracle有几个默认的数据库用户 Oracle默认用户 SYS SYSTEM SCOTT 数据库中所有数据字典表和视图都存储在SYS模式中 SYS用户主要用来维护系统信息和管理实例 SYSTEM是默认的系统管理员 该用户拥有Oracle管理工具使用的内部表和视图 通常通过SYSTEM用户管理数据库用户 权限和存储等 SCOTT用户是Oracle数据库的一个示范帐户 在数据库安装时创建 子任务2 1 创建新用户 要连接到Oracle数据库 就需要创建一个用户帐户每个用户都有一个默认表空间和一个临时表空间CREATEUSER命令用于创建新用户 语法格式如下 CREATEUSERusernameIDENTIFIEDBYpasswordORIDENTIFIEDBYEXETERNALLY DEFAULTTABLESPACEtablespace TEMPORARYTABLESPACEtemptablespace QUOTA integerK M UNLIMITED ONtablespace QUOTA integerK M UNLIMITED ONtablespace PROFILESprofile name PASSWORDEXPIRE ACCOUNTLOCKorACCOUNTUNLOCK 子任务2 1 练习 1 创建用户byxy 密码为baiyun 2 创建一个以自己的名字命名 用拼英命名 如果有英文名的可用英文名 的用户 密码自定 设置默认表空间为student 临时表空间为temp 请用记事本写出相应的代码 并在SQL Plus中运行 子任务2 1 练习 续 3 在SQL Plus中创建一个user1的用户 设置密码为123456 看能否正常运行 4 在SQL Plus中依次执行如下的语句 createusermyuseridentifiedbywelcomedefaulttablespacestudenttemporarytablespacetemppasswordexpire 创建完成后 以myuser用户身份连接到数据库 会出现什么情况 说明什么 子任务2 2 给用户授予权限 权限指的是执行特定命令或访问数据库对象的权利Oracle中存在两种权限系统权限 SYSTEMPRIVILEGE 允许用户在数据库中执行指定的行为 一般可以理解成比较通用的一类权限 对象权限 OBJECTPRIVILEGE 允许用户访问和操作一个指定的对象 该对象是一个确切存储在数据库中的命名对象 系统权限 oracle包含100多种系统权限 其主要作用 执行系统端的操作 比如CREATESESSION是登陆的权限 CREATETABLESPACE创建表空间的权限管理某类对象 比如CREATETABLE是用户建表的权限管理任何对象 比如CREATEANYTABLE ANY关键字表明该权限 权力 比较大 可以管理任何用户下的表 所以一般只有DBA来使用该权限 普通用户是不应该拥有该类权限的 表的系统权限 CREATETABLE 建表 CREATEANYTABLE 在任何用户下建表 ALTERANYTABLE 修改任何用户的表的定义 DROPANYTABLE 删除任何用户的表 SELECTANYTABLE 从任何用户的表中查询数据 UPDATEANYTABLE 更改任何用户表的数据 DELETEANYTABLE 删除任何用户的表的记录 索引及会话系统权限 索引 CREATEANYINDEX 在任何用户下创建索引 ALTERANYINDEX 修改任何用户的索引定义 DROPANYINDEX 删除任何用户的索引 会话 SESSION CREATESESSION 创建会话 登陆权限 ALTERSESSION 修改会话 表空间系统权限 表空间CREATETABLESPACE 创建表空间 ALTERTABLESPACE 修改表空间 DROPTABLESPACE 删除表空间 UNLIMITEDTABLESPACE 不限制任何表空间的配额 注意 表空间的所有权限都不应该分配给普通用户 系统特权 系统特权权限SYSDBA和SYSOPERSYSOPER的权限 启动停止数据库 恢复数据库等SYSDBA的权限 所有SYSOPER功能的管理权限 创建数据库等权限 注意 以系统特权权限登陆的用户一般都是特权用户 或称为超级用户 以SYSDBA身份登陆的用户在ORACLE中是权限最大的用户 可以执行数据库的所有操作 这些特权权限是不应该随便赋予给普通用户的 对象权限 对象权限是在指定的表 视图 序列或过程上执行指定动作的权限或权利 每个对象都有一个特殊的可授予的权限集 对象权限的种类不是很多 但数量较大 因为具体对象的数量很多 对象权限的分类 子任务2 2 给用户授予权限 续 GRANT命令可用于为用户分配权限或角色 GRANTCONNECTTO用户名 CONNECT角色允许用户连接至数据库 并创建数据库对象 GRANTRESOURCETO用户名 RESOURCE角色允许用户使用数据库中的存储空间 GRANTCREATESEQUENCETO用户名 此系统权限允许用户在当前模式中创建序列 此权限包含在CONNECT角色中 子任务2 2 给用户授予权限 续 授予用户MARTIN操作TEST表对象的权限 GRANTSELECTONTESTTOMARTIN 允许用户查询TEST表的记录 GRANTUPDATEONTESTTOMARTIN 允许用户更新TEST表中的记录 GRANTALLONTESTTOMARTIN 允许用户插入 删除 更新和查询TEST表中的记录 子任务2 2 练习 1 给用户 majianxia 自己创建的用户名 设置权限 允许该用户连接 connect或者createsession 至数据库 并创建数据库对象 例如 createtable createview 再试着以majianxia 自己创建的用户名 连接到数据库 区分connect权限和createsession权限 2 给用户 majianxia 设置权限 允许该用户插入 删除 更新和查询Scott emp表中的记录 子任务2 3 管理用户 1 修改用户密码语法DBA可以修改任何普通用户的密码 而不需要知道用户的旧密码 在sqlplus下执行password命令来修改登陆用户自己的密码 提示会输入旧密码和新密码 ALTERUSERuserIDENTIFIEDBY新密码 子任务2 3 管理用户 续 2 用户状态 OPEN EXPIRED LOCKED OPEN表正常状态 为用户帐号初始创建后状态 EXPIRED表示密码过期 用户下次登陆的时候需要修改密码 LOCKED表示该帐户已被锁定 不能执行任何Oracle相关操作 即使拥有相关的权限 状态管理语句 ALTERUSERuserPASSWORDEXPIRE 密码过期ALTERUSERuserACCOUNTLOCK UNLOCK 帐户锁定 解锁 子任务2 3 管理用户 续 3 删除用户语法CASCADE表示系统先自动删除该用户下的所有对象 然后再删除该用户的定义 已经登陆的用户是不允许被删除的 DROPUSERuser CASCADE 回收对象权限 回收对象权限语法 对象的权限会级联回收 权限的查询DBA SYS PRIVS 查询所有的系统权限的授权情况 SESSION PRIVS 能够查询出当前会话已经激活的所有系统权限 DBA TAB PRIVS 查询出表的对象权限的授权情况 REVOKE对象权限种类ON对象名FROMuser 子任务2 3 练习 1 修改 user1 用户的密码为 byxy2011 并用连接的方式验证密码修改是否成功 2 以majianxia用户的身份连接到数据库 再删除 user1 用户 看看会出现什么结果 分析原因并解决问题 并删除user1用户 3 回收user1用户的权限 了解角色 角色 ROLE 的目的就是为了简化权限的管理 子任务二综合练习 1 建立新用户user neu2 给用户user neu授权 使其能够登陆到数据库 能够查询scott下的emp表 3 查询用户user neu的权限 4 回收用户user neu的登陆权限 5 回收用户user neu的所有对象权限 6 建立角色role neu 7 给角色role neu授权 使其能够登陆到数据库 可选 8 赋角色role neu给用户user neu 可选 9 删除角色role neu 可选 10 删除用户user neu 可选 子任务二综合练习 1 建立新用户user neucreateuseruser neuidentifiedbymjxdefaulttablespaceusers 2 给用户user neu授权 使其能够登陆到数据库 能够查询scott下的emp表 grantconnecttouser neu grantselectonscott emptouser neu 3 查询用户user neu的权限 select fromdba sys privswheregrantee MJX Select fromsession privs select fromdba tab privswheregrantee USER NEU 4 回收用户user neu的登陆权限 revokecreatesessionfromuser neu 5 回收用户user neu的所有对象权限 revokeallonscott empfromuser neu 6 建立角色role neu Createroleuser neu 7 给角色role neu授权 使其能够登陆到数据库 可选 grantconnecttorole neu 8 赋角色role neu给用户user neu 可选 grantrole neutouser neu 9 删除角色role neu 可选 droprolerole neu 10 删除用户user neu 可选 Dropuseruser neu 任务三 创建数据表 分成两个子任务来完成 子任务3 1 创建数据表子任务2 创建临时表 子任务3 1 创建数据表 任务要求 在数据库中创建如下的teacher数据表 请用记事本写出SQL代码 并在SQL Plus以你自己的名字命名的用户登录并执行 表1 教师档案表 teacher 任务三 创建数据表 1 方案 所谓方案 就是一系列数据库对象的集合 是数据库中存储数据的一个逻辑表示或描述 Oracle10g数据库中并不是所有的数据库对象都是方案对象 方案对象有表 索引 触发器 数据库链接 PL SQL包 序列 同义词 视图 存储过程 存储函数等 非方案对象有表空间 用户 角色 概要文件等 在Oracle10g数据库中 每个用户都拥有自己的方案 创建了一个用户 就创建了一个同名的方案 方案与数据库用户是对应的 但在其他关系型数据库中两者却没有这种对应关系 所以方案和用户是两个完全不同的概念 要注意加以区分 在默认情况下 一个用户所创建的所有数据库对象均存储在自己的方案中 任务三 创建数据表 续 2 表结构表是oracle数据库数据存储的基本单元 通过行和列来组织数据 字段 字段名 字段数据类型 字段长度 约束 默认值等 任务三 创建数据表 续 3 基本的数据类型 1 char n 定长字符串 n 1to2000字节 即最大长度为2K 如果不指定长度 缺省为1个字节长 一个汉字为2字节 新建一个测试表test char 只有一个char类型的列 长度为10SQL createtabletest char colAchar 10 2 varchar2 n 可变长的字符串 n 1to4000字节 如果数据长度没有达到最大值n Oracle会根据数据大小自动调节字段长度 如果你的数据前后有空格 Oracle会自动将其删去 varchar2 n 是最常用的数据类型 创建一个表 只有一列 类型为varchar2 长度为10SQL createtabletest varchar colvarchar2 10 任务三 创建数据表 续 3 基本的数据类型 续 3 number m n m 1to38 n 84to127 m是所有有效数字的位数 n是小数点以后的位数 如 number 5 2 但在一行数据中的这个字段输入575 316 则真正保存到字段中的数值是575 32 4 date 该数据类型用于定义日期时间数据 长度为7B 5 raw n n 1to2000可变长二进制数据 在具体定义字段的时候必须指明最大长度n Oracle用这种格式来保存较小的图形文件或带格式的文本文件 如MiceosoftWord文档 raw是一种较老的数据类型 将来会逐渐被blob clob nclob等大的对象数据类型所取代 任务三 创建数据表 续 3 基本的数据类型 续 6 存储大对象的数据类型 blob 用于存储非结构化数据 比如二进制图像 clob 单字节字符数据 用于存储大型的固定宽度字符数据 nclob 用于存储大型的 固定宽度字符集数据bfile 用于存储操作系统文件中的非结构化数据 大对象数据类型的列不能出现在where groupby或orderby子句中 不能在SQL Plus等环境中查询 显示大对象类型的数据 也不能通过insert语句插入大对象类型的数据常见的数据类型具体见下页表 任务三 创建数据表 续 创建数据表的语法格式 CREATETABLE schema table name column namedatatype DEFAULTexpression column constraint n PCTFREEinteger PCTUSEDinteger INITRANSinteger MAXTRANSinteger TABLESPACEtablespace name STORGEstorage clause CLUSTERcluster name cluster column n LOGGING NOLOGGING ASsubquery 任务三 创建数据表 续 创建表时的命名规则和注意事项1 表名和字段名的命名规则 必须以字母开头 可以含符号A Z a z 0 9 2 大小写不区分 3 不用SQL里的保留字 一定要用时可用双引号把字符串括起来 4 用和实体或属性相关的英文符号长度有一定的限制 子任务3 1 练习 1 要求 在数据库中创建如下的teacher数据表 请用记事本写出SQL代码 并在SQL Plus以你自己的名字命名的用户登录并执行 子任务3 1 练习 续 2 阅读下面的SQL语句 指出该CreateTable命令创建的表的特性 createtableITEM typeidvarchar2 14 notnull typevarchar22 10 tablespaceUSERS ITEM这个表是在USERS表空间中创建的pctfree10 用于控制空闲空间比例 更新操作比较多时 此参数应设为较高的值initrans1 指定针对同一个块所允许的最小并发事务数目maxtrans255storage initial64Kminextents1maxextentsunlimited 子任务3 2 创建临时表 当缓存中间数据时候 需要创建一个临时表 其实oracle本身在这方面就已经考虑很全了 除非有些高级应用的时候才考虑自己创建临时表 Oracle的临时表创建之后基本不占用表空间 如果你没有指定临时表 包括临时表的索引 存放的表空的时候 你插入到临时表的数据是存放在ORACLE系统的临时表空间中 TEMP 建立临时表语法 1 ONCOMMITDELETEROWS定义了建立事务级临时表的方法CREATEGLOBALTEMPORARYTABLETABLE NAME COUMNS ASSELECT FROMTABLE ONCOMMITDELETEROWS 当前session发出commit rollback命令 则该事务周期发生的所有数据自动被Oracle删除 Oracletruncatetable 但不影响任何其他session的数据 建立临时表语法 2 ONCOMMITPRESERVEROWS定义了创建会话级临时表的方法CREATEGLOBALTEMPORARYTABLETABLE NAME COUMNS ASSELECT FROMTABLE ONCOMMITPRESERVEROWS 当前session结束 用户正常退出 用户不正常退出 Oracle实例崩溃 Oracle对这个会话的中发生的数据进行删除 Oracletruncatetable 但不影响任何其他session的数据 子任务3 2 练习 完成Word文档中的练习 任务四 管理数据表 子任务4 1 修改数据表的结构子任务4 2 重命名数据表子任务4 3 删除数据表子任务4 4 截断数据表 子任务4 1 修改数据表的结构 添加列语法 修改列语法 删除列语法 ALTERTABLEtableDROP columnname columnname ALTERTABLEtableMODIFY columnnamedatatype DEFAULTexpr columnnamedatatype ALTERTABLEtableADD col