03创建和管理数据表

1、模块三：创建和管理数据表主讲：马建霞TELQ：48189407使用 Oracle 数据库的开发流程服务器端安装 Oracle 服务器软件创建数据库(安装时自动创建)配置监听器(安装时自动配置)启动Oracle实例(自动启动服务)安装 Oracle 客户端软件配置网络服务名以新用户登录 Oracle提交 SQL 查询创建新用户并授权创建用户表空间客户端目 标 任 务：任务一：创建与管理表空间任务二：创建与管理用户任务三：创建数据表任务四：管理数据表任务一：创建与管理表空间表空间（tablespace）是Oracle数据库中最大的逻辑结构，它是数据库的逻辑划分，Oracl

2、e数据库就是由一个或多个表空间组成的，一个表空间由一个或多个数据文件组成，但一个数据文件只能属于一个表空间。所有的数据库对象都存放在指定的表空间中。但主要存放的是表， 所以称作表空间，默认情况下系统会自己创建一个System的表空间。 数据库表空间1表空间2数据文件1数据文件2段1段2块 块 块 扩展区1数据库的几个概念与写字楼中的概念的对应关系：写字楼中的概念数据库中的概念概念虚、实概念逻辑、物理大楼实的数据库物理的公司虚的表空间逻辑的占用的房间实的数据文件物理的子任务1-1：创建表空间ORACLE可以创建的表空间大致有三种类型:(1)TEMPORARY: 临时表空间,用于临时数据的存放;创

3、建临时表空间的语法如下: CREATE TEMPORARY TABLESPACE SAMPLE.(2)UNDO : 还原表空间. 用于存入重做日志文件.创建还原表空间的语法如下: CREATE UNDO TABLESPACE SAMPLE.(3)用户表空间: 最重要,也是用于存放用户数据的表空间可以直接写成: CREATE TABLESPACE SAMPLETEMPORARY 和 UNDO 表空间是ORACLE 管理的特殊的表空间.只用于存放系统相关数据.子任务1-1：创建表空间（续） 利用CREATE TABLESPACE命令创建和管理表空间 ,语法格式：CREATE UNDO|TEMPOR

4、ARY TABLESPACE tablespace_nameDATAFILE | TEMPFILE path/filename SIZE integer KM REUSE AUTOEXTEND OFFON NEXT integer KM MAXSIZE UNLIMITEDinteger KM MINIMUM EXTENT integer K | M DEFAULT STORAGE storage_clause ONLINEOFFLINE LOGGINGNOLOGGING PERMANENTTEMPORARY EXTENT MANAGEMENT DICTIONARYLOCAL AUTOALLOC

5、ATEUNIFORM SIZE integer KM ；子任务1-1：练习1、创建大小为50M的表空间student，禁止自动扩展数据文件。2、创建一个临时表空间temp,文件的存放路径自定，空间的初始大小为10M，指定允许分配给数据文件的最大磁盘空间为200M。 注意：可以用下面的语句查看表空间的信息Select * from dba_tablespaces;子任务1-1：练习下面两段创建表空间语句：1、CREATE TEMPORARY TABLESPACE temp2 TEMPFILE F:oracletemp01.ORA SIZE 32M REUSE AUTOEXTEND ON NEXT

6、 640K ;2、CREATE TABLESPACE data LOGGING DATAFILE F:oracledata.dbf SIZE 50M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE 200M DEFAULT STORAGE ( INITIAL 16K NEXT 32K MINEXTENTS 1 );请分别说出两条语名的含义。3、CREATE TABLESPACE IMAGEDATALOGGINGDATAFILE E:ORACLEORADATADATA_01.DBF SIZE 2000M REUSE AUTOEXTENDON NEXT 51200K MA

7、XSIZE 3900M,E:ORACLEORADATAXLDATA_02.DBF SIZE 2000M REUSE AUTOEXTENDON NEXT 51200K MAXSIZE 3900M,E:ORACLEORADATAXLDATA_03.DBF SIZE 2000M REUSEAUTOEXTEND ON NEXT 51200K MAXSIZE 3900M,E:ORACLEORADATAXLDATA_04.DBF SIZE 2000M REUSE AUTOEXTENDON NEXT 51200K MAXSIZE 3900MEXTENT MANAGEMENT LOCAL SEGMENT SP

8、ACE MANAGEMENT AUTO子任务1-2：修改表空间语法格式：ALTER TABLESPACE tablespace_name ADD DATAFILETEMPFILE path/filename SIZE integer KM REUSE AUTOEXTEND OFFON NEXT integer KM MAXSIZE UNLIMITEDinteger KM RENAME DATAFILE pathfilename,n TO pathre_filename,n DEFAULT STORAGE storage_clause ONLINEOFFLINE NORMALTEMPORARYI

9、MMEDIATE LOGGINGNOLOGGING READ ONLYWRITE PERMANENT TEMPORARY 子任务1-2：修改表空间（续）练习1、增加表空间的容量任务要求： 通过ALTER TABLESPACE命令把一个新的数据文件添加到student表空间(文件名为student1，路径自定)，并指定AUTOEXTEND ON和 MAXSIZE 300M。注意： 尽管可以设置MAXSIZE UNLIMITED，但应总是规定一个文件的最大尺寸值。子任务1-2：修改表空间（续）2、修改原有文件的大小增加表空间的容量任务要求： 修改Student表空间，设置每次以2M的大小自动扩展表

10、空间。注意：修改表空间的容量需要用Alter database命令。3、修改表空间的属性 任务要求： 修改Student表空间的属性，指定其将来的表、索引等不需要进行日志处理 。提示：用alter tablespace 命令可能直接修改表空间的属性。 子任务1-2：修改表空间（续） 4、修改表空间的名称任务要求： 将student表空间的名称改为student_jrg.再改回为Student.语法格式：ALTER TABLESPACE old_name RENAME TO new_name 5、设置数据库的默认表空间 通常情况下，数据库的默认表空间为SYSTEM或USERS表空间。提示：可以用

11、以下的语句查看用户的默认表空间 Select username,default_tablespace from dba_users;任务要求： 将Student表空间设置为数据库的默认表空间。语法格式：ALTER DATABASE DEFAULT TABLESAPCE tablespace_name子任务1-3：删除表空间1、删除表空间test，但不删除其文件drop tablespace test;2、. 删除表空间data同时删除表空间的 内容(了解)drop tablespace data including contents;3、删除表空间data及其包含的内容以及数据文件drop t

12、ablespace data including contents and datafiles; 使用OEM创建表空间：以sys用户，sysdba连接身份登录OEM，出现”数据库“页，单击“存储”，点击“表空间”，如下图：使用OEM创建表空间：在客户端的OEM中可以查看表空间信息，如下图：任务二：创建与管理用户Oracle 默认用户只有用合法的用户帐号才能访问Oracle数据库Oracle 有几个默认的数据库用户 Oracle 默认用户SYSSYSTEMSCOTT数据库中所有数据字典表和视图都存储在 SYS 模式中。SYS用户主要用来维护系统信息和管理实例。SYSTEM 是默认的系统管理员，该

13、用户拥有Oracle管理工具使用的内部表和视图。通常通过SYSTEM用户管理数据库用户、权限和存储等SCOTT用户是Oracle 数据库的一个示范帐户，在数据库安装时创建子任务2-1：创建新用户要连接到Oracle数据库，就需要创建一个用户帐户每个用户都有一个默认表空间和一个临时表空间CREATE USER命令用于创建新用户,语法格式如下：CREATE USER username IDENTIFIED BY passwordOR IDENTIFIED BY EXETERNALLYDEFAULT TABLESPACE tablespaceTEMPORARY TABLESPACE temptabl

14、espaceQUOTA integer KM UNLIMITED ON tablespace,QUOTA integer KM UNLIMITED ON tablespacePROFILES profile_namePASSWORD EXPIREACCOUNT LOCK or ACCOUNT UNLOCK子任务2-1：练习 1、创建用户byxy,密码为baiyun.2、创建一个以自己的名字命名（用拼英命名，如果有英文名的可用英文名）的用户，密码自定，设置默认表空间为student，临时表空间为temp。 请用记事本写出相应的代码，并在SQL*Plus中运行。子任务2-1：练习（续） 3、在SQ

15、L*Plus中创建一个user1的用户，设置密码为123456，看能否正常运行？4、在SQL*Plus中依次执行如下的语句：create user myuser identified by welcomedefault tablespace studenttemporary tablespace temp password expire;创建完成后，以myuser用户身份连接到数据库，会出现什么情况？说明什么？子任务2-2：给用户授予权限权限指的是执行特定命令或访问数据库对象的权利Oracle中存在两种权限 系统权限（SYSTEM PRIVILEGE）：允许用户在数据库中执行指定的行为，一般可

16、以理解成比较通用的一类权限。 对象权限（OBJECT PRIVILEGE）：允许用户访问和操作一个指定的对象，该对象是一个确切存储在数据库中的命名对象。系统权限oracle包含100多种系统权限，其主要作用：执行系统端的操作，比如CREATE SESSION是登陆的权限，CREATE TABLESPACE创建表空间的权限管理某类对象，比如CREATE TABLE是用户建表的权限管理任何对象，比如CREATE ANY TABLE，ANY关键字表明该权限“权力”比较大，可以管理任何用户下的表，所以一般只有DBA来使用该权限，普通用户是不应该拥有该类权限的。表的系统权限CREATE TABLE（建表

17、）CREATE ANY TABLE（在任何用户下建表）ALTER ANY TABLE（修改任何用户的表的定义）DROP ANY TABLE（删除任何用户的表）SELECT ANY TABLE（从任何用户的表中查询数据）UPDATE ANY TABLE（更改任何用户表的数据）DELETE ANY TABLE（删除任何用户的表的记录）。索引及会话系统权限索引：CREATE ANY INDEX（在任何用户下创建索引）ALTER ANY INDEX（修改任何用户的索引定义）DROP ANY INDEX（删除任何用户的索引）会话：（SESSION）CREATE SESSION（创建会话，登陆权限）ALT

18、ER SESSION（修改会话）表空间系统权限表空间CREATE TABLESPACE（创建表空间）ALTER TABLESPACE（修改表空间）DROP TABLESPACE（删除表空间）UNLIMITED TABLESPACE（不限制任何表空间的配额）注意： 表空间的所有权限都不应该分配给普通用户。系统特权系统特权权限SYSDBA和SYSOPERSYSOPER的权限：启动停止数据库，恢复数据库等SYSDBA的权限：所有SYSOPER功能的管理权限；创建数据库等权限。注意： 以系统特权权限登陆的用户一般都是特权用户，或称为超级用户。以SYSDBA身份登陆的用户在ORACLE中是权限最大的用户

19、，可以执行数据库的所有操作。这些特权权限是不应该随便赋予给普通用户的。对象权限对象权限 是在指定的表、视图、序列或过程上执行指定动作的权限或权利。每个对象都有一个特殊的可授予的权限集。对象权限的种类不是很多，但数量较大，因为具体对象的数量很多。对象权限的分类权限分类对象类型表(Table)视图(View)序列(Sequence)存储(Procedure)SELECT(选择)INSERT(插入)UPDATE(更改)DELETE(删除)ALTER(修改)INDEX(索引)REFERENCE(引用)EXECUTE(执行)子任务2-2：给用户授予权限（续）GRANT 命令可用于为用户分配权限或角色 G

20、RANT CONNECT TO 用户名; CONNECT角色允许用户连接至数据库，并创建数据库对象GRANT RESOURCE TO用户名; RESOURCE角色允许用户使用数据库中的存储空间GRANT CREATE SEQUENCE TO用户名; 此系统权限允许用户在当前模式中创建序列，此权限包含在CONNECT角色中子任务2-2：给用户授予权限（续）授予用户 MARTIN 操作TEST表对象的权限 GRANT SELECT ON TEST TO MARTIN; 允许用户查询 TEST 表的记录GRANT UPDATE ON TEST TO MARTIN; 允许用户更新 TEST 表中的记录

21、GRANT ALL ON TEST TO MARTIN; 允许用户插入、删除、更新和查询 TEST 表中的记录子任务2-2：练习1、给用户“majianxia”（自己创建的用户名）设置权限，允许该用户连接（connect或者create session）至数据库，并创建数据库对象(例如：create table,create view)。再试着以majianxia （自己创建的用户名）连接到数据库。区分connect 权限和create session权限。2、给用户“majianxia”设置权限，允许该用户插入、删除、更新和查询Scott.emp表中的记录.子任务2-3：管理用户1、修改用户

22、密码语法DBA可以修改任何普通用户的密码，而不需要知道用户的旧密码。在sqlplus下执行password命令来修改登陆用户自己的密码，提示会输入旧密码和新密码。ALTER USER user IDENTIFIED BY 新密码；子任务2-3：管理用户（续）2、用户状态：OPEN、EXPIRED、LOCKED。OPEN表正常状态，为用户帐号初始创建后状态。EXPIRED表示密码过期，用户下次登陆的时候需要修改密码；LOCKED表示该帐户已被锁定，不能执行任何Oracle相关操作（即使拥有相关的权限）。状态管理语句：ALTER USER user PASSWORD EXPIRE;-密码过期ALT

23、ER USER user ACCOUNT LOCKUNLOCK;-帐户锁定/解锁子任务2-3：管理用户（续）3、删除用户语法CASCADE表示系统先自动删除该用户下的所有对象，然后再删除该用户的定义。已经登陆的用户是不允许被删除的。DROP USER user CASCADE回收对象权限回收对象权限语法：对象的权限会级联回收。权限的查询DBA_SYS_PRIVS：查询所有的系统权限的授权情况。SESSION_PRIVS：能够查询出当前会话已经激活的所有系统权限。DBA_TAB_PRIVS：查询出表的对象权限的授权情况。REVOKE 对象权限种类 ON 对象名 FROM user子任务2-3：练

24、习1、修改“user1”用户的密码为“byxy2011”；并用连接的方式验证密码修改是否成功；2、以majianxia用户的身份连接到数据库，再删除“user1”用户，看看会出现什么结果？分析原因并解决问题，并删除user1用户。3、回收user1用户的权限。了解角色：角色（ROLE）的目的就是为了简化权限的管理。 权限用户单独授予权限使用角色授予权限role子任务二综合练习：1.建立新用户user_neu 2. 给用户user_neu授权,使其能够登陆到数据库，能够查询scott下的emp表;3.查询用户user_neu的权限;4.回收用户user_neu的登陆权限;5.回收用户user_n

25、eu的所有对象权限;6.建立角色role_neu ;7.给角色role_neu授权,使其能够登陆到数据库（可选） ;8.赋角色role_neu给用户user_neu （可选） ;9.删除角色role_neu （可选） ;10.删除用户user_neu （可选） ;子任务二综合练习：1.建立新用户user_neu create user user_neu identified by mjx default tablespace users;2. 给用户user_neu授权,使其能够登陆到数据库，能够查询scott下的emp表;grant connect to user_neu;grant sel

26、ect on scott.emp to user_neu;3.查询用户user_neu的权限;select * from dba_sys_privs where grantee=MJX;Select * from session_privs;select * from dba_tab_privs where grantee=USER_NEU;4.回收用户user_neu的登陆权限;revoke create session from user_neu;5.回收用户user_neu的所有对象权限;revoke all on scott.emp from user_neu;6.建立角色role_n

27、eu ;Create role user_neu;7.给角色role_neu授权,使其能够登陆到数据库（可选） ; grant connect to role_neu;8.赋角色role_neu给用户user_neu （可选） ; grant role_neu to user_neu;9.删除角色role_neu （可选） ; drop role role_neu;10.删除用户user_neu （可选） ;Drop user user_neu;任务三：创建数据表分成两个子任务来完成： 子任务3-1：创建数据表 子任务2：创建临时表 子任务3-1：创建数据表任务要求：在数据库中创建如下的tea

28、cher数据表，请用记事本写出SQL代码，并在SQL*Plus以你自己的名字命名的用户登录并执行。 表1：教师档案表（teacher）教师编号姓名性别工作时间政治面貌学历职称系别1张三男1969-11-10群众大学本科副教授经济2郭新女1979-6-25党员大学本科讲师计算机任务三：创建数据表1、方案： 所谓方案，就是一系列数据库对象的集合，是数据库中存储数据的一个逻辑表示或描述。Oracle 10g数据库中并不是所有的数据库对象都是方案对象，方案对象有表、索引、触发器、数据库链接、PL/SQL包、序列、同义词、视图、存储过程、存储函数等，非方案对象有表空间、用户、角色、概要文件等。 在Ora

29、cle 10g数据库中，每个用户都拥有自己的方案，创建了一个用户，就创建了一个同名的方案，方案与数据库用户是对应的。但在其他关系型数据库中两者却没有这种对应关系，所以方案和用户是两个完全不同的概念，要注意加以区分。在默认情况下，一个用户所创建的所有数据库对象均存储在自己的方案中。任务三：创建数据表（续）2、表结构 表是oracle数据库数据存储的基本单元，通过行和列来组织数据。 字段：字段名、字段数据类型、字段长度、约束、默认值等。任务三：创建数据表（续）3、基本的数据类型（1）char(n)：定长字符串，n=1 to 2000字节，即最大长度为2K，如果不指定长度，缺省为1个字节长（一个汉字

30、为2字节）；新建一个测试表test_char.,只有一个char类型的列。长度为10 SQL create table test_char(colA char(10); （2）varchar2(n)：可变长的字符串， n=1 to 4000字节。 如果数据长度没有达到最大值n，Oracle 会根据数据大小自动调节字段长度，如果你的数据前后有空格，Oracle 会自动将其删去。 varchar2(n)是最常用的数据类型。 创建一个表,只有一列，类型为varchar2,长度为10 SQL create table test_varchar( col varchar2(10); 任务三：创建数据表（

31、续）3、基本的数据类型（续）（3）number(m,n) ：m=1 to 38 、n=-84 to 127， m是所有有效数字的位数，n是小数点以后的位数。如：number(5,2)，但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32。 （4）date ：该数据类型用于定义日期时间数据，长度为7B。（5）raw(n)：n=1 to 2000可变长二进制数据，在具体定义字段的时候必须指明最大长度n，Oracle 用这种格式来保存较小的图形文件或带格式的文本文件，如Miceosoft Word文档。 raw是一种较老的数据类型，将来会逐渐被blob、clob、ncl

32、ob等大的对象数据类型所取代。任务三：创建数据表（续）3、基本的数据类型（续）（6）存储大对象的数据类型：blob ：用于存储非结构化数据，比如二进制图像。clob：单字节字符数据，用于存储大型的固定宽度字符数据。nclob ：用于存储大型的，固定宽度字符集数据bfile ：用于存储操作系统文件中的非结构化数据。大对象数据类型的列不能出现在where,group by或order by子句中。 不能在SQL*Plus等环境中查询、显示大对象类型的数据，也不能通过insert语句插入大对象类型的数据 常见的数据类型具体见下页表。任务三：创建数据表（续）创建数据表的语法格式：CREATE TABL

33、E schema. table_name(column_name datatype DEFAULT expression column_constraint,n) PCTFREE integer PCTUSED integerINITRANS integerMAXTRANS integer TABLESPACE tablespace_name STORGE storage_clause CLUSTER cluster_name(cluster_column,n) LOGGING |NOLOGGING AS subquery 任务三：创建数据表（续）创建表时的命名规则和注意事项1)表名和字段名的

34、命名规则：必须以字母开头，可以含符号A-Z,a-z,0-9,_,$,#2)大小写不区分； 3)不用SQL里的保留字, 一定要用时可用双引号把字符串括起来；4)用和实体或属性相关的英文符号长度有一定的限制。子任务3-1：练习 1、要求：在数据库中创建如下的teacher数据表，请用记事本写出SQL代码，并在SQL*Plus以你自己的名字命名的用户登录并执行。字段名称字段类型字段大小允许空值备注teacherno8位数字必填教师编号teachername必填姓名teachersex性别worktime工作时间political政治面貌education学历professional职称departm

35、entno系别telephone联系电话子任务3-1：练习（续） 2、阅读下面的SQL语句，指出该Create Table命令创建的表的特性。create table ITEM( typeid varchar2(14) not null, type varchar22(10)tablespace USERS / ITEM这个表是在USERS表空间中创建的 pctfree 10 /用于控制空闲空间比例，更新操作比较多时，此参数应设为较高的值 initrans 1 / 指定针对同一个块所允许的最小并发事务数目 maxtrans 255 storage ( initial 64K minextent

36、s 1 maxextents unlimited );子任务3-2：创建临时表当缓存中间数据时候，需要创建一个临时表，其实oracle本身在这方面就已经考虑很全了，除非有些高级应用的时候才考虑自己创建临时表。Oracle的临时表创建之后基本不占用表空间，如果你没有指定临时表（包括临时表的索引）存放的表空的时候，你插入到临时表的数据是存放在ORACLE系统的临时表空间中（TEMP）。 建立临时表语法:1、ON COMMIT DELETE ROWS 定义了建立事务级临时表的方法CREATE GLOBAL TEMPORARY TABLE TABLE_NAME(COUMNS )AS SELECT FR

37、OM TABLE.ON COMMIT DELETE ROWS;当前session发出commit/rollback命令，则该事务周期发生的所有数据自动被Oracle删除(Oracle truncate table)。但不影响任何其他session的数据。建立临时表语法:2、ON COMMIT PRESERVE ROWS 定义了创建会话级临时表的方法CREATE GLOBAL TEMPORARY TABLE TABLE_NAME(COUMNS )AS SELECT FROM TABLEON COMMIT PRESERVE ROWS;当前session结束(用户正常退出 / 用户不正常退出 / O

38、racle实例崩溃)，Oracle对这个会话的中发生的数据进行删除(Oracle truncate table)。但不影响任何其他session的数据。子任务3-2：练习完成Word文档中的练习。任务四：管理数据表子任务4-1：修改数据表的结构子任务4-2：重命名数据表子任务4-3：删除数据表子任务4-4：截断数据表子任务4-1：修改数据表的结构添加列语法：修改列语法：删除列语法：ALTER TABLE tableDROP (columnname ,columnname); ALTER TABLE table MODIFY(columnname datatypeDEFAULT expr , columnname datatype.);ALTER