Oracle数据库第二次.pptx

Oracle 11g PL/SQLOracle 11g PL/SQL 程序设计教程程序设计教程 第二章第二章 数据库的建立数据库的建立 主讲：杨丽娜主讲：杨丽娜2.1 2.1 数据库的逻辑设计数据库的逻辑设计 2.1.1 2.1.1 关系数据库设计基础关系数据库设计基础 2.1.2 2.1.2 关系数据库规范化关系数据库规范化 1.1.第一范式第一范式 2.2.第二范式第二范式 3.3.第三范式第三范式2.2 2.2 数据库的物理设计数据库的物理设计2.3 2.3 数据库实施数据库实施2.4 2.4 创建数据库创建数据库实体标识符实体标识符：能够将一个实体与其他实体区别开来的属性集。表表：实体的集合。实体实体：表中的行。属性属性：表中的列。几个重要概念几个重要概念n n实体标识符实体标识符表示为表示为关键字关键字或主码或主码。n n上页的关系中，哪些属性适合作为关键字？上页的关系中，哪些属性适合作为关键字？n n例如例如“学生学生”关系中的属性关系中的属性“学生学生学号学号”就是关键字，只要学号确定了，就能知道就是关键字，只要学号确定了，就能知道这个学号对应的姓名、这个学号对应的姓名、性别、出生年月和专业等性别、出生年月和专业等信息，但学生关系中的信息，但学生关系中的“性别性别”和和“专业专业”不能作为关键字，因为不能作为关键字，因为即使性别或专业确定即使性别或专业确定了，还是不能确定学生的姓名了，还是不能确定学生的姓名和学号等信息，同性别或者和学号等信息，同性别或者同专业的同专业的学生太多了。当然如果这个关系中没有同姓名学生太多了。当然如果这个关系中没有同姓名的学生，则姓名也可以作为关键字看待，这要根据具体的语义来决定。的学生，则姓名也可以作为关键字看待，这要根据具体的语义来决定。关键字关键字(主码或主键主码或主键)实体间的联系实体间的联系关系数据库规范化关系数据库规范化知识点知识点：n第一范式(1NF1NF)：如果一个实体（表）的所有属性都是不可分割的，即表中的每一行和每一列均有一个值，并且永远不会是一组值。关系关系关系关系 studentsstudentsstudentsstudents不满足第一范式，不满足第一范式，不满足第一范式，不满足第一范式，因为属性因为属性因为属性因为属性 SHSHSHSH（学生的家庭成员）（学生的家庭成员）（学生的家庭成员）（学生的家庭成员）可以再分解，如分解为父亲、母可以再分解，如分解为父亲、母可以再分解，如分解为父亲、母可以再分解，如分解为父亲、母亲。亲。亲。亲。第二范式第二范式在第一范式的基础之上更进一层在第一范式的基础之上更进一层第二范式需要确保数据库表中的每一列都和主键相关，而不第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。不可以把多种数据保存在同一张数据库表中。联合主联合主键：就是键：就是用用2个或个或2个以上的字段组成主个以上的字段组成主键。键。第二范式第二范式这样就产生一个问题：这个表中是以订单编号和商品编号作这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中为联合主键。这样在该表中商品名称商品名称、单位单位、商品价格商品价格等信等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，就非常完美了。如下面这两个所示。个表中，就非常完美了。如下面这两个所示。这样设计，在很大这样设计，在很大程度上减小了数据程度上减小了数据库的冗余。库的冗余。如果要获取订单的如果要获取订单的商品信息，使用商品信息，使用商商品编号品编号到到商品信息商品信息表表中查询即可。中查询即可。第三范式第三范式第三范式在第二范式的基础上更进一层。第三范式在第二范式的基础上更进一层。第三范式需要确保第三范式需要确保数据表中的每一列数据都和主键直接数据表中的每一列数据都和主键直接相关相关，而不能间接相关。，而不能间接相关。关系名：关系名：students学生学生属性：属性：student_id 学生学号学生学号(主码）主码）name 学生姓名学生姓名 sex 学生性别学生性别 dob 学生出生日期学生出生日期 SD 学生所在系的名称学生所在系的名称 SL 学生所住宿舍楼学生所住宿舍楼关系关系students 不满足第三不满足第三范式，因为属性范式，因为属性SL还是依还是依赖于主码赖于主码student_id,但也但也可由非主码属性可由非主码属性SD导出，导出，即即SL是函数传递依赖于是函数传递依赖于SD.第三范式第三范式比如在设计一个订单数据表的时候，可以将客户编号作比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表范式的数据库表.外外键：如果键：如果关系模式关系模式R中的某属性集不是中的某属性集不是R的的主键主键，而是另一个关系，而是另一个关系R1的主键则的主键则该该 属性属性集是关系模式集是关系模式R的外键，通常在的外键，通常在数据库设计数据库设计中缩写为中缩写为FK这样在查询订单信息的时这样在查询订单信息的时候，就可以使用客户编号候，就可以使用客户编号来引用客户信息表中的记来引用客户信息表中的记录，也不必在订单信息表录，也不必在订单信息表中多次输入客户信息的内中多次输入客户信息的内容，减小了数据冗余。容，减小了数据冗余。数据库的物理设计数据库的物理设计设计步骤：设计步骤：1.1.存储记录结构设计。存储记录结构设计。2.2.确定数据存储方式。确定数据存储方式。3.3.设计访问方法。设计访问方法。4.4.完整性和安全性考虑。完整性和安全性考虑。5.5.行成物理设计文档。行成物理设计文档。数据库的物理设计数据库的物理设计设计性能可分为以下几类：设计性能可分为以下几类：1.1.查询和响应时间。查询和响应时间。2.2.更新事物的开销。更新事物的开销。3.3.报告生成的开销。报告生成的开销。4.4.主存储空间开销。主存储空间开销。5.5.辅助存储空间。辅助存储空间。数据库实施数据库实施数据库的实现数据库的实现根据逻辑设计和物理设计的结果，在计算机上建立起实际数据根据逻辑设计和物理设计的结果，在计算机上建立起实际数据库结构、装入数据，并进行测试和运行的过程称为数据库的实库结构、装入数据，并进行测试和运行的过程称为数据库的实现。现。运行与维护阶段的主要工作运行与维护阶段的主要工作1.1.维护数据库的安全性和完整性。维护数据库的安全性和完整性。2.2.检测并改善数据库性能。检测并改善数据库性能。3.3.增加新的功能。增加新的功能。4.4.修改错误。修改错误。对用户访问进行控制对用户访问进行控制 理解Oracle RDBMS和Oracle SQL内容回顾：内容回顾：Oracle的SQL实现在很大程度上符合ANSI标准，但并非百分之百地兼容。Oralce的竞争对手也并未做到与该标准完全兼容。Oracle SQL*Plus命令行接口是一种非常好的工具，可以在任何操作系统平台下运行，允许输入和执行SQL命令。Oracle SQL Developer工具是一种非常好的GUI，可以在Windows操作系统中运行，允许输入和执行SQL命令。n区分系统权限和对象权限n授予表上的权限n在数据字典中查看权限n授予角色n区分权限和角色n完成课堂练习三种一般权限类型权限的类型描述系统权限在数据库中执行一项特定任务的能力对象权限在一个特定数据库对象上执行一项特定任务的能力角色由一项或多项系统权限和（或）对象权限，以及（或者）其他角色组成的集合系统权限什么是系统权限？什么是系统权限？使用任何一条SQL语句和（或）在数据库中执行一项任务的权利，就是一项系统权限。什么时候用到系统什么时候用到系统权限？权限？在Oracle中，一般不会轻易在一个服务器上创建多个数据库，在一个数据库中，不同的项目由不同的用户访问，每一个用户拥有自身创建的数据库对象，因此用户的概念在Oracle中非常重要。为了登录数据库，需要为用户账户授予CREATE SESSIONCREATE SESSION系统权限。为了创建一个表，需要为用户账户授予CREATE TABLECREATE TABLE系统权限，而此权限同时包含了创建一个索引的权限。对象权限什么什么是对象权限是对象权限？使用一项系统权限，在数据库中一个特定的已有对象上执行某项任务的权利，就是一项对象权限。与系统权限的不同点：与系统权限的不同点：一个用户账户为了创建数据库对象，必须拥有系统权限。一但成功创建了数据库对象，就可以将一个特定数据库对象的对象权限授予其他用户。可以将系统权限和对象权限授予数据库中的用户，也可以从数据库中的用户收回这两种权限。一些支持性语句一、语法一、语法结构：创建用户结构：创建用户CREATE USER 用户名 IDENTIFIED BY 口令 ACCOUNT LOCK|UNLOCK语法解析：语法解析：LOCK|UNLOCK创建用户时是否锁定，默认为锁定状态。锁定的用户无法正常的登录进行数据库操作。代码代码演示：创建用户演示：创建用户SQL CREATE USER JOAN2 IDENTIFIED BY OFARC3 ACCOUNT UNLOCK;一些支持性语句二、语法二、语法结构结构：修改用户：修改用户ALTER USER 用户名 IDENTIFIED BY 新口令 代码代码演示演示：修改用户：修改用户SQL ALTER USER JOAN2IDENTIFIED BY HAWAII;请请注注意意 Oracle在SQL*Plus中的命令以分号（；）结尾，代表命令完毕并执行，系统同时会把该命令保存在缓存中，缓存中只保存最近执行过的命令，如果重新执行缓存中的命令，直接使用左斜杠符号（/）。如果命令不以分号结尾，该命令只是写入缓存保存起来，但并不执行。一些支持性语句三、语法三、语法结构结构：删除用户：删除用户DROP USER 用户名请注意：请注意：如果这个用户账户拥有任何数据库对象，则上述语句无法执行成功。需要使用CASCADE选项指示SQL删除用户账户并同时删除它拥有的所有对象。DROP USER 用户名 CASCADE代码代码演示演示：删除用户：删除用户SQL DROP USER JOAN CASCADE;一些支持性语句四、语法四、语法结构结构：切换用户：切换用户CONNECT 用户名/口令请注意：请注意：CONNECT语句并不是一条SQL语句，而是SQL*Plus提供的一项增强功能，用以从一个用户账户切换到另一个用户账户。前提：要切换的用户账户未被锁定且被赋予了登录的最小权限，例如CREATE SESSION。代码代码演示演示：切换用户：切换用户假设已经登录到EFCODD账户，然后创建了账户JOAN，其登录密码为HAWAII，可以这样从EFCODD账户登录到JOAN账户：SQL CONNECT JOAN/HAWAII;数据库角色什么什么是数据库角色？是数据库角色？数据库角色（role），是一个数据库对象。一个角色对象本身不代表权限，角色只是由若干个系统权限组成的集合。下面介绍几个常用角色：CONNECT角色，主要应用在临时用户，特别是那些不需要建表的用户，通常只赋予他们CONNECT role。CONNECT是使用Oracle的简单权限，拥有CONNECT角色的用户，可以与服务器建立连接会话（session，客户端对服务器连接，称为会话）。RESOURCE角色，更可靠和正式的数据库用户可以授予RESOURCE role。RESOURCE提供给用户另外的权限以创建他们自己的表、序列、过程（procedure）、触发器（trigger）、索引（index）等。DBA角色，DBA role拥有所有的系统权限-包括无限制的空间限额和给其他用户授予各种权限的能力。用户SYSTEM拥有DBA角色。GRANT语句 代码代码演示演示：授权：授权SQL GRANT CONNECT TO JOAN;SQL GRANT RESOURCE TO JOAN;授权成功。授权成功。一般情况下，一个普通的用户（如SCOTT），拥有CONNECT和RESOURCE两个角色即可进行常规的数据库开发工作。语法语法结构：授权结构：授权GRANT角色|权限 TO 用户（角色）GRANT privilege TO user option;SQLSQL CONNECT SYSTEM/MANAGER;CONNECT SYSTEM/MANAGER;SQLSQL CREATE USER HAROLD IDENTIFIFD BY LLODY;CREATE USER HAROLD IDENTIFIFD BY LLODY;SQLSQL GRANT GRANT CREATE SESSION CREATE SESSION TO TO HAROLD;HAROLD;SQL GRANT SQL GRANT UNLIMITED TABLESPACE TO UNLIMITED TABLESPACE TO HAROLDHAROLD;SQL GRANT CREATE SQL GRANT CREATE TABLE TABLE TO HAROLDTO HAROLD;SQL CONNECT SQL CONNECT HAROLD/LLODY;HAROLD/LLODY;SQL SQL CREATE TABLE CLOKTOWER(CLOCK_ID NUMBER(11);CREATE TABLE CLOKTOWER(CLOCK_ID NUMBER(11);SQL SQL GRANT SEQUENCE SEQ_CLOCK_IDGRANT SEQUENCE SEQ_CLOCK_ID;思考思考:请简述以下语句各自完请简述以下语句各自完成了什么任务？成了什么任务？REVOKE语句语法语法结构结构：收回权限：收回权限REVOKE 角色|权限 FROM 用户（角色）请注意：请注意：授权时，使用的是TO，收回权限时，使用的是FROM。如果收回某个用户的一项系统权限，那么收回操作将即时生效。然而，在权限收回之前该用户执行的所有动作仍然有效。例如例如：某用户账户曾被授予CREATE TABLE系统权限，并创建了一些表。但是随着此用户的CREATE TABLE系统权限被回收，那么该用户再也无法创建其他表，但是之前创建的表并不会消失，依然存在。ANY 关键字 某些系统权限的名称中带有关键字ANY。例如例如：系统权限CREATE ANY TABLE这项权限允许在任何用户账户中、在数据库中的任何位置创建一个表。也就是说，如果一个系统权限的名称包含了ANY关键字，就表明被授予该权限的用户能够以任何用户账户身份执行相应的任务。思考：思考：分析下面的语句，判断表MOVIES和表TVSHOWS的拥有者是哪个用户账户。从语句中你能发现ANY关键字哪些用法？SQLSQL CONNECT SYSTEM/MANAGER;CONNECT SYSTEM/MANAGER;SQLSQL CREATE USER LAUREL IDENTIFIFD BY POKE;CREATE USER LAUREL IDENTIFIFD BY POKE;SQLSQL GRANT GRANT CREATE SESSION CREATE SESSION TO TO LAUREL;LAUREL;SQL GRANT SQL GRANT UNLIMITED TABLESPACE TO LAUREL;UNLIMITED TABLESPACE TO LAUREL;SQL GRANT CREATE SQL GRANT CREATE TABLE TABLE TO TO LAUREL;LAUREL;SQL CREATE USER SQL CREATE USER HARDY HARDY IDENTIFIFD BY IDENTIFIFD BY CLOBBER;CLOBBER;SQL GRANT CREATE SESSION TO SQL GRANT CREATE SESSION TO HARDY;HARDY;SQL GRANT UNLIMITED TABLESPACE TO SQL GRANT UNLIMITED TABLESPACE TO HARDY;HARDY;SQL GRANT CREATE SQL GRANT CREATE ANY TABLE ANY TABLE TO TO HARDY;HARDY;SQL CONNECT SQL CONNECT LAUREL/POKE;LAUREL/POKE;SQL SQL CREATE TABLE MOVIES(MOVIE_ID NUMBER(7);CREATE TABLE MOVIES(MOVIE_ID NUMBER(7);SQLSQL CONNECT CONNECT HARDY/CLOBBER;HARDY/CLOBBER;SQL CREATE TABLE SQL CREATE TABLE LAUREL.TVSHOWS(TVSHOW_ID NUMBER(7LAUREL.TVSHOWS(TVSHOW_ID NUMBER(7););ANY 关键字如何证实表的拥有者？如何证实表的拥有者？我们可以使用数据字典进行证实。代码演示代码演示：SQL SELECT OWNER,TABLE_NAME2FROM DBA_TABLES3WHERE OWNER IN(HARDY,LAUREL);执行结果：执行结果：OWNER TABLE_NAMELAUREL MOVIESLAUREL TVSHOWSALL PRIVILEGES 除了可以授予特定的系统权限，一个拥有足够权限的用户账户，例如SYSTEM用户账户或其他DBA账户，还可以执行以下语句：GRANT ALL PRIVILEGES TO user;上述语句的执行结果是为用户授予了全部系统权限。同时，在这条语句中还可以使用WITH ADMIN OPTION子句。注意注意：上述语句的执行结果不易取消，请慎用。下面这条语句并不是精确对应于上述授权语句的权限回收语句：REVOKE ALL PRIVILEGES FROM user;假定所有系统权限都已经被授予用户，那么上述语句将取消授权用户的全部系统权限。如果没有将所有系统权限授予用户，那么上述语句将返回一条错误信息。PUBLIC 关键字 PUBLIC账户是Oracle数据库中的一个内置用户账户，它代表了全体用户，其拥有的任何对象都被视为由数据库中全体用户现在和将来拥有的对象。GRANT CREATE ANY TABLE TO PUBLIC;这条语句为数据库中的每一位用户都授予了CREATE ANY TABLE权限，可以让每一位用户都能在任何用户账户中创建任何表。如果想从PUBLIC收回某项系统权限，可以保证在不收回任何其他系统权限的情况下完成这项工作，使用语句：REVOKE CREATE ANY TABLE FROM PUBLIC;仅仅回收了那些被赋予PUBLIC的权限。总结：总结：如果打算授予全部权限，可以用关键字ALL PRIVILEGES，但如果打算向全体用户授予某些特定的权限，请不要使用关键字ALL，而应该为PUBLIC授予这些特定的权限。区分系统权限和对象权限1.写出可以为用户账户JESSE授予在数据库中所有用 户账户中创建表的权限的语句。2.你已经登录了用户账户FRED，并且需要为用户账 户ETHEL授权。你执行了以下SQL语句，GRANT CREATE ANY TABLE TO ETHEL WITH ADMIN OPTION;REVOKE CREATE ANY TABLE FROM ETHEL;GRANT CREATE ANY TABLE TO JESSE;未获得任何权限 区分系统权限和对象权限3.为了使一个用户账户能够登录数据库所需的 最小系统权限是哪一个权限？4.以下哪一个系统权限可以使被授权者获得在自 己的用户账户中创建一个索引、但是无法在其 他 用 户 账 户 中 创 建 一 个 索 引 的 能 力？（）A.CREATE TABLE B.CREATE INDEX C.CREATE ANY INDEX D.CREATE ANY TABLECREATE SEESION;A衷心感谢各位同学的支持！衷心感谢各位同学的支持！谢谢！谢谢！