Oracle数据库应用学习

Oracle数据库应用学习如有不了解的可以借鉴上一章.表空间和用户权限管理表空间:概述Oracle数据库包含 逻辑结构 和 物理结构 物理结构 是指数据库是物理存储数据的集合包括 数据文件ORA或者DBF控制文件联机文件日志文件参数文件… 逻辑结构 是指描述数据组织方式的 一组逻辑概念及它们之间的关系; 表空间 是数据库 逻辑结构中的一个重要组成部分, 表空间可以存放各种应用对象: 表 索引... 每一个表空间由一个或多个数据库文件组成...表空间的分类类别说明永久性表空间一般用来保存 视图 过程 索引 等数据 SYSTEM USERS 表空间是默认安装的;临时性表空间只用于保存系统中短期活动的数据 如果排序数据…撤销表空间用来帮助回退未提交的事务数据, 已提的数据在这里是不可以恢复的一般不需要建临时 和 撤销表空间除非把它们转移到其它磁盘中以提高性能表空间的目的1. 对不同用户|不同模式对象 分配不同的表空间, 方便对用户数据的操作 对模式对象的管理... 2. 可以将不同的数据文件 创建到不同的磁盘中有利于管理磁盘空间 提高 // 一般在Oracle系统安装并创建Oracle实例后Oracle系统会自动建立多个表空间;创建表空间通过 CREATE TABLESPACE 命令创建表空间语法-- 创建表空间-- 语法CREATETABLESPACEtablespacename DATAFILEfilename[SIZEinteger[K|M]][AUTOEXTEND[OFF|ON]];}/* tablespacename: 创建表空间的名称; DATAFILE 指定组成表空间的 一个/多个文件,当有多个数据文件使用逗号分隔; filename: 数据文件的 路径和名称(一个多个逗号分隔); SIZE 可选项,指定文件的大小 k(千字节) M(兆字节) AUTOEXTEND 用来启用 或 禁用数据文件的自动扩展; 默认应该是 off; 可选属性 NO OFF NO: 设置为NO 空间使用完毕之后,自动扩展属性; OFF: 设置为OFF 则很容易出现,表空间剩余容量为0的情况下,使数据不能在存储到数据库中; */实例-- 创建表空间wsm_540createtablespacewsm_540 datafiletbs540.dbfsize60m autoextendon;-- 登录的用户是system用户-- 查询系统表空间 系统表select*fromdba_data_files;-- 可以查看当前的表空间..修改表空间通过 ALTER TABLESPACE (对应表空间名) 命令修改表空间语法-- 更改数据文件大小 50mAlterdatabasedatafile表空间的datafile文件路径~Resize50M;-- 更改表数据文件自增;Alterdatabasedatafile表空间的datafile文件路径~Autoextendon;-- 表空间的datafile,可以查询表 dba_data_files 得到~-- .....以此类推....-- 移动数据文件(三种..);-- 方式一altertablespace表空间名;renamedatafile原生位置to移动位置;-- 方式二--物理硬盘上移动数据文件-- 方式三altertablespacetablespace_name offline;--(后续了解...)删除表空间通过 DROP TABLESPACE (对应表空间名) 命令删除表空间切记, Oracle不要轻易删除表空间, 更别不用sql命令直接找到文件形式手动删除容易导致Oracle与内部的配置不匹配影响不可逆异常语法droptablespace对应表空间名 including contentsanddatafiles;--(删除内容和文件)-- 删除表空间之前最好,对数据库进行备份;自定义用户管理当创建一个新的数据库时候Oracle将创建出一些默认数据库用户...SYS System Scott等; SYS用户 Oracle的一个超级用户主要用来维护系统信息和管理实例 只能以SYSDBA或SYSOPER角色登录 System用户 Oracle默认的系统管理员拥有DBA权限, 通常用来管理Oracle数据库的用户、权限和存储等, 只能以Normal(正常默认)方式登录 Scott用户 Oracle 系统提供的一个测试用户,,,, 默认未解锁; Scott用户模式包含四个示范表(测试表)其中一个是Emp表。使用USERS表空间存储模式对象。 通常情况下出于安全考虑对于不同的数据表需要设置不同的访问权限。 此时就需要创建不同的用户。Oracle 中的CREATE USER命令用于创建新用户。 每个用户都有一个默认表空间和一个临时表空间。如果没有指定Oracle 就将USERS设为默认表空间将TEMP设为临时表空间。解锁用户/设置修改密码# 系统级用户下# 解锁Scott 用户;alteruserscott accountunlock;# 修改Scott 用户密码为tigeralteruserscott identifiedbytiger;alteruser用户名 accountlock;# 加锁alteruser用户名 accountunlock;# 解锁conn 用户名/密码;# 可以连接数据库语法~创建用户语法--创建用户,语法;CREATEUSERuser--设置用户名IDENTIFIEDBYpassword--添加密码 (必须指定用户名和密码)[DEFAULTTABLESPACEtablespace]--添加默认表空间[TEMPORARYTABLESPACEtablespace]--添加了临时表空间 建议为用户指定默认表空间或临时表空间[QUOTA {integer[K|M]|UNLIMITED}ONtablespace--QUOTA:设置用户表空间限额,表空间为 默认表空间或临时表空间;[QUOTA {integer[K|M]|UNLIMITED}ONtablespace]...]--size_clause的单位可以是K|M,默认是UNLIMITED不限制[PASSWORD EXPIRE]--第一次登录时需要初始化密码--创建用户之后别忘了,分配权限角色哦~不然无法任何操作..-- 实例语法createuser用户名--设置用户名identifiedby密码--添加密码defaulttablespace默认表空间名--添加默认表空间temporarytablespace临时表空间--添加了临时表空间quota unlimitedon默认名--没有空间大小的限制quota100mon临时名--临时文件大小100m限制password expire--第一次登录时需要初始化密码修改/删除用户--修改密码ALTERUSER用户名 IDENTIFIEDby修改后密码;--删除用户DROPUser用户名;DROPUser用户名CASCADE;--当用户拥有模式对象,时无法删除用户可以在后面加 CASCADE删除用户和模式对象;数据库权限管理权限是用户对一项功能的 执行权力(用户必须赋予相应的权限), Oracle中根据系统管理方式不同分为系统权限 和 对象权限; 系统权限 指被授权的用户, 可以对数据库进行的操作.. 系统权限是在数据库中执行某种系统级别的操作 或 针对某一类对象执行某种操作权力; eg CREATE TABLE 创建表 CREATE VIEW : 创建视图....等系统权限; 对象权限 指用户在数据库中具体对象所拥有的权限; 针对某个特定的模式对象执行的权力如数据库中的某个表视图序列存储过程等; 角色 角色是具有名称的一组权限的组合使用角色可以更加方便 和 高效的对权限进行管理所以数据库管理员通常使用角色对用户进行授予权限; Oracle数据库用户有两种途径获得权限; 1.管理员直接向用户授予权限 2.管理员将权限授予角色然后在将 角色授予一个或多个用户..... Oracle常用的系统提供的角色 CONNECT : 临时用户特指不需要建表的用户通常只赋予他们connect role. RESOURCE : 更可靠和正式的数据库用户可以授予resource包括select/insert/update和delete等..创建表、视图、序列... DBA : 拥有所有的系统权限包括无限制的空间限额和给其他用户授予各种权限的能力。授予权限语法:-- 授予权限语法-- 授予前,当前登录用户需要是一个高级的用户..GRANT权限|角色TO用户名;-- 给用户名 WSM授予CONNECT 和 RESOURCE 角色权限;grantconnect,RESOURCEtoWSM;-- 逗号分隔可以同时赋值两个;-- 撤销用户角色|权限;REVOKE权限|角色FROM用户;序列序列是用于生成唯一、连续序号的数据库对象; 通常用来生成主键或唯一键的值; 序列可以是升序的也可以是降序的... 使用 CREATE SEQUENCE 语句创建序列创建序列语法CREATESEQUENCE 序列名[STARTWITHn]--都是可选项;[INCREMENTBYn][{MAXVALUE/MINVALUE n|NOMAXVALUE}][{CYCLE|NOCYCLE}][{CACHE n|NOCACHE}];START WITH 定义序列的初始值(即产生的第一个值)默认为1。 INCREMENT BY 用于定义序列的步长(间隔) 默认为1如果出现负值则代表序列是按照 降序排序的 MAXVALUE 定义序列生成器能产生的最大值。 NOMAXVALUE 是默认选项代表没有最大值定义这时对于递增Oracle序列系统能够产生的最大值是10的27次方;对于递减序列最大值是-1。 MINVALUE定义序列生成器能产生的最小值。 NOMAXVALUE 是默认选项代表没有最小值定义这时对于递减序列系统能够产生的最小值是?10的26次方;对于递增序列最小值是1。 CYCLE和NOCYCLE 表示当序列生成器的值达到限制值后是否循环。 CYCLE代表循环NOCYCLE代表不循环。 如果循环则当递增序列达到最大值时循环到最小值;对于递减序列达到最小值时循环到最大值。 如果不循环达到限制值后继续产生新值就会发生错误。 CACHE(缓冲)定义存放序列的内存块的大小默认为20。 NOCACHE表示不对序列进行内存缓冲。对序列进行内存缓冲可以改善序列的性能。访问序列创建序列之后, 可以通过 NEXTVAL 和 CURRVAL 序列伪列,来访问该序列的值 (实现新增..等操作) ; NEXTVAL 返回序列中下一个有效的值任何用户(表)都可以引用。 CURRVAL 中存放序列的当前值, NEXTVAL应在 CURRVAL之前指定二者应同时有效。使用序列空理论说序列没有什么用,纸上谈兵永远比不上现场实战--切换scott用户select*fromdept;--系统提供表 dept--创建一个序列 dept_insert--起始位置40createsequence dept_insertstartwith40--起始位置 41incrementby1--每次增长的个数 1minvalue1--最小值 1nomaxvalue--没有最大值cache10--数据库事先缓存10个备用~--利用序列,新增deptinsertintodeptvalues(dept_insert.NEXTVAL,asda,dasdsa);--41insertintodeptvalues(dept_insert.NEXTVAL,asda,dasdsa);--42insertintodeptvalues(dept_insert.NEXTVAL,asda,dasdsa);--43--在单独查询selectdept_insert.NEXTVALfromdual;--44selectdept_insert.NEXTVALfromdual;--45--CURRVAL不会获取下一个,而是当前值selectdept_insert.CURRVALfromdual;--45使用序列可以设置Oracle 中的主键…确保某个值在表在唯一对于主键 Oracle还可以使用 SYS_GUID函数生成32位的唯一编码作为主键;SYS_GUID():源自对数据库进行访问的时间戳 和 机器标识符; 不同时间产生不同的结果,可以作为主键使用确保了唯一性;可以多运行几次来比较结果~SELECT SYS_GUID() From dual;更改序列使用ALTER SEQUENCE语句修改序列不能更改序列的 START WITH参数语法/* 使用序列会产生裂缝: 回滚 系统异常 多个表同时使用同一序列....序列使用顺序紊乱; */--语法ALTERSEQUENCE 序列名[INCREMENTBYn][{MAXVALUE/MINVALUE n|NOMAXVALUE}][{CYCLE|NOCYCLE}][{CACHE n|NOCACHE}];--修改序列的注意事项--必须是序列的拥有者或对序列有 ALTER 权限--只有将来的序列值会被改变--改变序列的初始值只能通过删除序列之后重建序列的方法实现;删除序列使用DROP SEQUENCE语句删除序列语法-- 上面说到序列不可以修改起始值-- 但是删除可以做到类似的操作( 把A序列删除,在创建一个A序列更改其属性...实现相同的效果 )DROPSEQUENCE 序列名;--删除序列同义词同义词概念Oracle的同义词synonyms从字面上理解就是别名的意思和视图的功能类似就是一种映射关系。 同义词是数据库对象的一个别名经常用于简化对象访问和提高对象访问的安全性。 对象访问A用户模式下 查 B模型下表 SELECT * FROM B.表名; 简化代码提高安全(轻松知道对于模式位置); 在使用同义词时Oracle数据库将它翻译成对应方案对象的名字。 与视图类似同义词并不占用实际存储空间只有在数据字典中保存了同义词的定义。用途同义词是现有对象的一个别名 简化SQL语句 隐藏对象的名称和所有者 提供对对象的公共访问分类同义词共有两种类型 分别是Oracle公用同义词与Oracle私有同义词。普通用户创建的同义词一般都是私有同义词 公有同义词一般由DBA创建普通用户如果希望创建同义词则需要CREATE PUBLIC SYNONYM这个系统权限。私有同义词只能被当前模式的用户访问, 且私有同义词名称 不可以与当前模式对象名相同(表名);语法-- 私有同义词CREATEORREPLACESYNONYM 同义词名FOR模式对象.原表名;-- OR REPLACE:可选,指如果已存在该同义词替换同义词;公有同义词公有同义词可被所有的数据库用户访问语法-- 共有同义词CREATEORREPLACEPUBLICSYNONYM 同义词名FOR模式对象.原表名;当前私有同义词 和 共有同义词同名时候优先选择 私有同义词作为目标...删除同义词-- 删除私有DROPSYNONYM 名;-- 删除共有DROPPUBLICSYNONYM 名;--只是删除同义词哦,跟对象表没有任何影响索引个人在学习mysql 对索引有一点点的了解.. 主键索引 唯一索引 空间啥的... 刚才回头看了看感觉有点low就不cope重新整理一个... 索引作用提高了数据库 检索查询速度改善数据库性能索引简介索引是数据库对象之一用于加快数据的检索类似于书籍的索引(目录)。 在数据库中索引可以减少数据库程序查询结果时需要读取的数据量 (类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。) oracle会自动管理索引索引删除不会对表产生影响; 索引对用户是透明的无论表上是否有索引sql语句的用法不变, oracle创建主键时会自动在该列上创建主键索引;索引分类物理分类逻辑分类分区或非分区索引单列或组合索引B树索引唯一或非唯一索引正常或反向键索引基于函数索引位图索引。。。。。。。。索引原理1.若没有索引 搜索某个记录时(例如查找namewish需要搜索所有的记录因为不能保证只有一个wish必须全部搜索一遍 2.若在name上建立索引oracle会对全表进行一次搜索将每条记录的name值排列然后构建索引条目name和rowid存储到索引段中查询name为wish时即可直接查找对应地方, 而快速的找到这些记录... 3.创建了索引并不一定就会使用oracle自动统计表的信息后决定是否使用索引表中数据很少时使用全表扫描速度已经很快没有必要使用索引索引使用创建、修改、删除、查看1.创建索引语法--创建索引: Oracle中会有专门的文件用来存储创建的索引,删除不会对原表造成影响(注意命名不能重复..);CREATE[UNIQUE]|[BITMAP]INDEX创建索引名ON指定创建索引的表名([column1[ASC|DESC],column2[ASC|DESC],…])[TABLESPACE为索引指定表空间][.....可选,还有很多属性]/* [UNIQUE]|[BITMAP]可选属性: UNIQUE唯一索引 或 BITMAP视图索引,不指定索引默认B-tree索引. 指定创建索引的表名: (列1,列2) 可以基于多个列创建索引,列之间逗号分隔. TABLESPACE可选属性: 为索引指定表空间. */索引建立原则表中导入数据后再创建索引否则每次表中插入数据时都必须更新索引 在适当的表和字段上创建索引 小表(数据少的表)不要建立索引, 索引虽然可以提高效率, 前提是对于高量的数据对于低数据,数据的性能已经够强大了..还非要用索引来多此一举...反而影响效率; 对于不经常作为条件的列不适合作为索引..经常排序分组的列可以作为索引; 限制表中索引的数目 索引越多在修改表时对索引做出修改的工作量越大2.修改索引重命名索引alter index 旧名字 rename to 新索引名;合并索引表使用一段时间后在索引中会产生碎片此时索引效率会降低。可以选择重建索引或者合并索引,合并索引方式更好些无需额外存储空间代价较低alter index 索引名 coalesce;重建索引方式一删除原来的索引重新建立索引方式二语法 alter index 索引名 rebuild [内部过程和注意点] ; 举例 alter index 索引名 rebuild TABLESPACE 表空间; 修改索引表空间...何时重建索引用户表被移动到新的表空间后, 表上的索引并不会自动转移, 此时需要手动移动... 索引中包含很多已删除的项, 对表进行频繁的删除...造成索引浪费可以重建索引;3.删除索引语法 drop index 索引名; SqlServer中创建索引必须指定对应的表名, 而Oracle 索引名在用户账户这是唯一的,删除时不需要指定表名;4.查看索引索引是透明的, 不会影响正常的SQL语句, 使用时就正常的查询即可... 假设给 A表 A列设置索引; SELECT * FORM A表 WHERE A列 ... -- 即可,Oracle会默认使用索引... 使用工具的话可以选中查询 F5查看SQL执行顺序. (尝试一次有索引一次没索引的顺序)常见索引唯一索引 定义索引中的列,在表中唯一存在的数据; 非唯一索引 指列的数据表中可以重复... 无任何声明默认就是非唯一索引; 创建一个唯一索引 AA CREATE UNIQUE INDEX aa ON a表(列1,列2) --A表中的 列1列2的数据在表中是唯一的... -- a表(列1,列2) 这也是一种复合索引..--组合索引--创建一个非唯一组合索引createindexENAME_JOBonEMP(ENAME,JOB)--使用了索引SELECT*FROMEMPWHEREENAMESMITHANDJOBCLERK;--而,并不是使用了组合索引,组合索引内部,根据两个列拼接进行查询..SELECT*FROMEMPWHEREJOBCLERKANDENAMESMITH;DROPIndexENAME_JOB;--删除索引;B数索引oracle中最常用的索引B树索引就是一颗二叉树 叶子节点双向链表包含索引列和指向表中每个匹配行的ROWID值 所有叶子节点具有相同的深度因而不管查询条件怎样查询速度基本相同能够适应精确查询、模糊查询和比较查询创建例子craete index 索引名 on 表(列);适合使用场景列基数列不重复值的个数大时适合使用B数索引举例sd索引的遍历如下先找到id 小于等于50 的分支块再找4030 之间的分支块再找到id 为31 对应的叶子节点。根据rowid 的值找到数据块中相应的记录并显示出来。如果查找between 2 and 31 之间的数据。那么这时Oracle已经找到了31这个值之后可以通过叶节点双向连接.平行的查找包含2的索引块完成对于id的查询。反向键索引反向键索引在抱持列顺序的同时反转索引列的字节 即123 发转 321 好处 对于像身份证这种数据, 前一段都是 320xxxx的而关键是后面的不同...对这种数据列设置 **反向键索引** 查询时根据输入的值, 反着进行查找效率极高...位图索引位图索引的优点在于它适用于低基数列 (即该列的值是有限的,理论上不会是无穷大的) eg: 员工表的工种, 即使员工表 员工有几万条而职位工种也就是那几种 优点 1.对于大批即时查询的数据可以减少响应时间 2.相比其它索引占用空间明显减少 3.即使在很低的终端配置上,也能获得显著的性能; 注意位图索引,不应该频繁的 insert update delete ...需要重新锁定给员工表的 job 列创建位图索引对于 job 这种低基数列; 值不是很多的列…位图索引会以一种1/0 形式存储, 1存在 0不存在表示底层以 记录下标 及列的各值进行管理…索引注意事项通配符在搜索词首出现时oracle不能使用索引eg--我们在name上创建索引createindexindex_nameonstudent(name);--下面的方式oracle不适用name索引select*fromstudentwherenamelike%wish%;--如果通配符出现在字符串的其他位置时优化器能够利用索引如下select*fromstudentwherenamelikewish%;不要在索引列上使用not可以采用其他方式代替如下oracle碰到not会停止使用索引而采用全表扫描select*fromstudentwherenot(score100);select*fromstudentwherescore100;--替换为select*fromstudentwherescore100orscore100索引上使用空值比较将停止使用索引 egselect*fromstudentwherescoreisnotnull;