Oracle数据库基础学习

Oracle数据库基础学习个人之前就学习过 mysql sqlserver 但笔记没有在CSDN上可能会有些跳跃~不明确地方可以看这位大佬的笔记~不是我的.Oracle 简介OracleDatabase又名Oracle RDBMS , 是甲骨文公司的一款关系数据库管理系统。(世界上最好的数据库系统(历史悠久))优点支持多用户的高性能的事务处理 在保持数据安全性完整性方面 性能卓越~ 支持分布式数据库和分布处理具有可移植性,Oracle 可以在 window Linux 等多个操作系统上使用~ (SqlServer微软的数据库只能在window上使用)缺点:收费! 服务收费… 后期的安装维护也是收费的… 中小型公司一般不会使用~RDBMS关系型数据库.多个表数据之同存在着关系 , 在这些表上的数据操作依赖于关系 (关系用来描述多个表之间的数据依存,包括了一对一 、一对多 、多对多的关系)这些关系在 Oracle数据库中表现为主键、外键这些约東条件…RDBMS就是一个建立在这些关系模型基础上的 , Oracle从7.3的版本就完全支持关系型数据库Mysql SqlServer db2 Oracle 都属于关系型数据库;Oracle基本概念Oracle 的概念个人刚开始学的时候有点难~主要是因为学习过Mysql SqlServer 后, 与Oracle 有很多不同概念理论, 与之前有不同; 建议自废武功不要想其它的数据库在学习;Oracle数据库oracle数据库包含逻辑结构和物理结构物理结构 是指数据库是物理存储数据的集合包括 数据文件ORA或者DBF控制文件联机文件日志文件参数文件…逻辑结构 是指描述数据组织方式的 一组逻辑概念及它们之间的关系;oracle数据库的概念和其他数据库概念有些不一样mysql数据库创建数据库的过程是–创建库–创建表而oracle创建数据库的过程是–创建一个表空间–创建一个用户–由用户去创建/管理 表。所以oracle数据库和其他数据库是有不同之处的。可以这样理解oracle是一个大的数据库由用户来管理的。(所以使用时候必须首先创建数据库, 才能使用Oracle ; 可以在安装时候同时创建数据库~ )(一般在完成Oracle数据库安装并创建Oracle 实例后, 就会自动建立多个 表空间 系统表 system…用户) 不同的用户就可以理解为不同的库全局数据库名全局数据库名 是用于区分一个数据库的标识在安装数据库 创建数据库 … 需要使用的。它由数据库名称域名构成类似于网络中的域名; 使数据库的命名在整个网络环境中唯一 。数据库名称数据库名就是一个数据库的标识(唯一)在数据库安装或创建完成之后声明; (就是安装数据库时候的 起的名字~ )(不建议修改会很繁琐, 因为数据库名还被写入控制文件中控制文件是以二进制型式存储的, 用户无法修改控制文件的内容。)假设用户修改了参数文件中的数据库名但是在Oracle启动时。由于参数文件中的DB_NAME与控制文件中的数据库名不一致导致数据库启动失败将返回ORA-01103错误。查询当前数据库名 :select name from v$database; (建议system登录状态;)域名在分布式数据库系统中不同版本的数据库服务器之间不论运行的操作系统是unix或是windows各服务器之间都可以通过数据库链路进行远程复制数据库域名主要用于分布式环境中的复制。什么时候使用域 :1.在oracle分布式环境中两个数据库之间要通过数据库链路进行数据的远程传输 2.在同一网络环境下两个数据库的数据库名相同查询数据库的域名 :select value from v$parameter where name ‘db_domain’; (建议system登录状态;)数据库实例每个启动的数据库都对应一个数据库实例通过实例来访问操作数据库中数据如果把 数据库 理解为, 硬盘上的文件(应用程序)数据库实例就是, 内存共享运行状态一组服务器后台进程; (一个数据库可以有多个实例)表空间tablespace每个Oracle数据库都是由 多个表空间构成的; (用户建立的数据库也是存储到对应的 表空间中)一个表空间可以由多个 数据文件组成, 但一个数据文件只能属于一个 表空间; (表空间 又属于数据库)每个数据库都有一个 SYSTEM 的系统表空间…SYSAUX TEMP … 都是创建数据库时自动创建的。管理员可以创建自定义的表空间 并分配给指定的用户 也可也为表空间增加 删除数据文件;数据文件.dbf、.ora通常文件扩展名.dbf是用于存储数据库数据的文件; ( 数据库表的 记录,索引,视图… )一个数据库文件可能存储很多表的数据, 而一个 表的数据可能来源于很多 数据文件; (不存在一对一关系)控制文件.ctl控制文件的扩展名.ctl是一个二进制的文件; 控制文件存储的信息有很多: 数据文件 和 日志文件的位置和名字(是数据库启动和运行所必须的文件Oracle 读写数据,就是根据控制文件来查找对应的文件, 因此一个数据库至少有一个控制文件;) Oracle11g默认三个日志文件.log日志文件扩展名是.log它记录了数据所有更改信息,并提供了一种数据恢复机制; 确保系统崩溃 或其它意外重新恢复数据日志文件是成组使用的, 每个日志文件组 有一个或多个日志文件,在工作过程中多个组循环使用.(一个组写满了换其它组~)模式和模式对象模式是数据库对象的集合( 表 索引…) Oracle 为每一个数据库用户创建一个模式, 次模式为当前用户所拥有。和用户具有相同的名称~安装Oracle安装教程.Oacle 数据类型字符数据类型char(size)指定长字符数据(不指定情况下默认1字节), 列长度 1~2000字节 ;如果输入字节数小于指定的字节数, 空格填补 大于指定字节报错~varchar2(size)可变长字符数据 , 列长度 1~4000字节在定义字符类型时候指定其大小但是在实际赋值时候会根据输入字节数而变化输入字节数小于定义字节数, 不会以空格替代而是变成字节数小的 (节省内存) 如果输入字节数大于指定字节数则还是会报错!nchar(size)nchar 及中国字符集以 Unicode 存储方式;nchar 和 char 区别就在于存储方式不一致, char (1) 和 nchar(1) 字段长度为 1字节 和 1字符(等于2字节);分别传入 a 是没有问题的, 但占用字节分别是 1 和 2 , 如果都插入中文字符 啊 char(1) 是插不进去的;nvarchar2(size)存储方式和 nchar 类似Unicode ; 在这个基础上又和 varchar2 类似, 可变字符长度…扩:nvarchar2 和 varchar2 在mysql 中并没有所以刚开始学习时候有一点不习惯…nvarchar2 和 varchar2 是在 nvarchar和 varchar 基础上进行了优化Oracle也又nvarchar 和 varchar 数据类型;但底层还是nvarchar2 和 varchar2 所以最后执行时候还是 2 为了效率建议使用 2的数值数据类型number(p,s)可变长数值数据: number可以存储 正数 负数 零 定点数 浮点数…p 表示精度1~38之间( 数指从左往右算 不为0 不计 负 小数点的有效数)s 为范围 -84~127之间 表示小数点右边数字数 可以是0; (对于不要的小数遵循 四舍五入原则)最后得到的值有效位 ≤ p 则成立日期时间数据类型Date日期型数据 和mysql 大致一样, Oracle中提供 SYSDATE函数返回当前日期和时间;TIMESTAMP存储 年月日时分秒 , SYSTIMESTAMP函数功能是返回当前日期 时间 和 时区LOB数据类型LOB 又称 ‘大对象’ 通俗易懂就是 一个特别大的对象, 可以存储多达4GB 的非结构化信息 如声音 剪辑 视频剪辑等…LOB 允许对数据进行 高效 随机 分段的访问, LOB可以是外部的 也可以是内部的这取决于它相对于数据库的位置;可以通过 PL/SQL 中提供的程序包 DBMS——LOB 完成。一个表中可以有多个列被定义为 LOB数据类型;LOB数据类型有CLOB BLOB BFILE NCLOBclob能够存储大量字符数据最大可达到4G, 可以存储单字符数据 和 多字符数据(XML文档 新闻 内容介绍等含有大量内容的文档)blob二进制数据最大可达到4G (图片视频声音)bfile能够将二进制文件, 存储在数据库外部的操作系统文件中, BFILE 列存储一个BFILE 定位器, 指向位于服务器文件系统上的二进制文件(最大4GB)nclob用于存储大量的 nchar 字符数据, nclog 支持同时 固定宽度字符 变宽字符(Unicode字符数据)终于结束大量的理论…到了sql 语句建议有点基础的看…Oracle SQl语句Sql 语言是针对数据库而言的 一门语言它可以 创建数据库表视图 增删改查……数据库的一系列操作不区分大小写全称 “结构化查询语言”SQL 组成a. DML(数据库操作语言) 用来 增删改 表中数据 eg: INSERT , DELETE ,UPDATEb. DDL(数据库定义语言) 在 数据库中 创建或 删除数据库对象等操作 eg: CERATE,DROP,ALTERc. DQL(数据库查询语言) 用来 对 数据库中 数据进行 查询 eg: SELECTd. DCL(数据库控制语言) 用来 控制数据库组件 存钱许可 存取权限 eg:GRANTREVOKE奉上OneNote笔记~不够详细, 太多了不好发… Sql语句大致都是通用的… 但也有少部分区别oracle 中没有了数据库的概念, 所以说上面对数据库的操作, 可以忽略…Oracle 中的伪列伪列就像是Oracle 表中的一个列(Mysql没有~) 每个表都有的~伪列可以从表中查询, 不可以修改 删除 新增…暂时只介绍 ROWID ROWNUMROWIDoracle数据库的表中的每一行数据都有一个唯一的标识符或者称为 rowid在oracle内部通常就是使用它来访问数据的。rowid需要 10个字节的存储空间并用18个字符来显示。用途快速访问表中某行数据 2,显示表中行是如何存储的 3.可以作为表中行的唯一标识符~ROWID格式ROWNUM是查询返回的结果集中行的序号可以使用它来限制查询返回的行数 (分页查询)注意 使用伪列ROWNUM做条件查询-- scott 用户下,随便查看一个系统表,及伪列 rowid rownumselecte.*,rowid,rownumfromemp e;CREATE TABLE命令Qracle创建表与Mysql 有些不同因为由用户管理表~/* 多行注释 */--单行注释CREATETABLE[用户.]表名(列 类型[属性约束索引注释],)-- [] 为可选选项, 用户可选,不指定情况下默认当前登录用户~利用现有表创建新表CREATETABLE新表ASSELECT*FROM旧表;-- 创建一个新表 和 旧表一模一样包括数据(可以指定列,来改变创建表的列...)-- 一般用于日表 (有些项目,非常大 每天会有非常多的数据, 如果都放在一个表中时间久了就会异常大越来越多 不利于备份数据..-- 而对于日表 每天都有 其表结构是一定每天都一样的CREATETABLE新表ASSELECT*from旧表where12;-- 加上条件使没有符合条件的数据赋值给新表,新表就是一个只有表结构的表了~事务操作语句 TCL在Oracle 中事务控制语句TCL 主要由COMMIT 提交事务即把事务中对数据库修改操作进行永久保存~ROLLBACK 回滚事务取消对数据库所做的任何修改;SAVEPOINT 在事务中创建存储点相当于单击游戏的存档 存档点~ROLLBACK TO 存储点 将事务回滚到指定的存储点相当于, 单击游戏的回档Oracle Sql操作符算术操作符加减乘除 ±*/ …比较操作符 ! BETWEEN~AND(在…之间 IS NULL(是否null) LIKE(模糊查询) IN(范围查询)逻辑操作符AND(与) OR(或) NOT(非)集合操作符将两个查询结果组合成一个结果集(高中数学的集合概念~)使用前需要遵循一些规则 1.两个查询的表 列数,类型要匹配兼容对应… 2.这种查询操作不适合LOG类型的数据, 最后展示的列标题以第一个select 为准UNION并集将两个查询结果集合并起来,并删除重复的行 UNION ALL并集所有将两个查询结果组合在一起,包括重复数据INTERSECT交集只返回两个表都有的相同数据MINUS减集在第一个查询结果中, 排除第二个查询结果中出现的行 eg: 表1数据A B C 表2数据A 减集就是B C 连接操作符 ||Oracle 中使用 || 符合进行拼接字符串操作类似于 Mysql中的 号;实例 scott登录~select EName || Job from emp; 对 EName列 和 Job 列拼接-- 以dept表举例select*fromdept-- 事务操作语句 TCL-- 新增insertintodeptvalues(50,开发,);insertintodeptvalues(60,测试,);savepointa;-- 设置存储点;insertintodeptvalues(70,人事,);-- 新增rollbacktoa;-- 回滚到存储点;commit;-- 提交事务;-- 模拟分页 5~6条记录;select*from(selectd.*,rownumasrfromdept d)ddwherer4andr6SQL函数函数,通俗易懂用于完成某种特定操作 Oracle对这种操作进行了实现, 就像Java的方法;Oracle 常用的函数有 单行函数 聚合函数 转换函数 …转换函数将指以 一种数类型 —— 转换为 —— 另一种数据类型的操作;函数功能TO_CHAR()将 一种数据类型 转换为字符串类型;TO_DATE()将 char 或 varchar类型 转换为时间类型TO_NUMBER()将 数字的字符类型 转换为数值类型;TO_CHAR()语法TO_CHAR( d|n , [,fmt] );参数, d 或 n 可选[,fmt ]; d表示日期类型数据, n 就是数字类型数据可选[,fmt] 是指参数的格式类型TO_DATE( char,[fmt] )TO_NUMBER( char|varchar )select*fromdual-- TO_CHAR(d|n,[,fmt]);-- dual 提供的最小的工作表,任何用户都可以访问; 只有一行一列具有某bai些特殊功用。常用来通过select语句计算常数表du达式。-- sysdate 获取当前系统时间;-- fmMM fmDD: fm是指把 01 09这种,使用空格或者0填充给替换调不需要SELECTTO_CHAR(sysdate,YYYY年fmMM月fmDD日 HH24:MI:SS)FROMdual;-- 将当前时间转换成字符类型 年月日时分秒~;selectTO_CHAR(sysdate,MM)FROMdual;-- 只获取当前月份-- 9:表示数值的格式 S:表示 - 数符号selectTo_CHAR(123,$999.99S)FROMdual;-- 把数字123 转换为字符 $123.00 加$符号字符串;-- B:如果数值是给小数整数部分是0 就显示为空格;selectTO_CHAR(0.44,B999.9)FROMdual;-- ...还有很多不一一举例;-- TO_DATE( char,[fmt] )-- 将字符类型 转换成 时间格式(就可以参与比较~但Oracle以及对字符进行隐式转换了..); fm去0selectTO_DATE(2000-09-09,yyyy-fmmm-fmdd)FROMdual;-- TO_NUMBER( char|varchar )-- 将字符类型 转换成 数值格式(就可以参与运算了~ Oracle貌似已将进行隐式转换了..)selectSQRT(To_NUMBER(100))FROMdual;-- 将100字符转换成数值,并 sqrt(); 获取其平方根;-- 10*10100;其它函数除了, 日期函数数字函数转换函数Oracle还提供一些单行函数 一般称为 “其它函数”;函数功能NVL( expl1, expl2 )如果expl1 值为null 则返回 expl2的值; 不为null 返回expl1值;NVL2( expl1, expl2, expl3 )如果expl1 值为null 则返回 expl3的值; 不为null 返回expl2值;DECODE( value, if1, then1, if2, then2, …else )如果value 值为 if1 返回if1 值value 值为 if2 返回if2 值… 没有符合返回else;-- NVL( expl1, expl2 )selectnvl(null,1)fromdual;-- NVL2( expl1, expl2, expl3 )selectnvl2(null,1,2)fromdual;-- DECODE( value, if1, then1, if2, then2, …else )selectdecode(1,-- value1,1,-- if1 ,then12,2,-- if2 ,then2,3-- else)fromdual;--实战: scott用户下emp表select*fromemp;-- 查询emp表: ename sal commselectename,sal,comm,salnvl(comm,0)ass1,-- 如果comm列为null 就是0,与sal加;nvl2(comm,salcomm,sal)ass2,-- 如果comm列为null 就sal 不是null就salcomm;to_char(hiredate,mm)asm,-- 获取入职的月份;decode(to_char(hiredate,mm),01,一月,-- 根据月份判断,并输出字符...02,二月,03,三月,04,四月,05,五月,06,六月,下半年)asmm,fromemp分析函数Oracle 8.1.6 版本提供分析函数, 分析函数是对一组查询结果进行运算的, 然后获得结果…语法函数名([ 参数]) OVER( [ 分区子句 ] [ 排序子句])函数名分析函数名字参数函数需要传入的参数分区子句PARTITION BY 列将查询结果分为不同的组功能类似于GROUP BY语句排序子句ORDER BY asc|desc将每个分区进行排序常见的函数– 根据deptno 分组, sal排序;函数功能RANK具有相等值的行排位相同序数随后跳跃,1~3.. 如果,1记录与2记录相同则序号都是 1,不同的数据序号是3DENSE_RANK具有相等值的行排位相同序号是连续的不会因为相同的记录跳跃排序,会出现多个相同的序号ROW_NUMBER返回连续的排位不论值是否相等EMP表是 Scott用户下都有的一个系统测试表! 放心使用~-- 分析函数-- emp表,以deptno 列分析~selecte.*,-- 根据deptno 分组, sal排序;rank()over(partitionbydeptnoorderbysaldesc)asr1,-- 具有相等值的行排位相同序数随后跳跃dense_rank()over(partitionbydeptnoorderbysaldesc)asr2,-- 具有相等值的行排位相同序号是连续的row_number()over(partitionbydeptnoorderbysaldesc)asr3-- 返回连续的排位不论值是否相等fromemp e;