oracle数据库的了解和使用

文章目录1. 概述1数据库2实例3表空间4用户5) schema6数据库的持久化7注释8mysql和oracle数据库逻辑结构类比2. 数据库操作1创建表空间2创建操作表空间的用户3给用户授权4表字段5表约束6创建表7修改表3. oracle序列1序列概念2序列的创建3序列的属性4虚拟表dual5序列实现主键自增6scott表的了解和使用4. oracle常用函数1数值函数2字符函数3日期函数4转换函数5通用函数6find_in_set 函数7聚合函数8分组函数5. oracle分页查询1关键字2特点3原理3分页查询6. oracle视图1概述2格式3注意点7. oracle索引1格式8. oracle foreach标签的使用9. 数据迁移需要注意的点1长字符串的处理2空字符的处理10. mysql和Oracle区别1主键2单双引号的处理3翻页4长字符串的处理1. 概述1数据库oracle的数据库是一个物理的概念就是指的安装的软件是一个物理文件的集合(包括数据文件、控制文件、日志文件等)。 oracle 可以看作是一个大的数据库一个操作系统只有一个库。2实例由内存 和后台进程组成负责管理数据库的运行。 数据库只要一运行就会有一个实例产生只要一关闭这个实例就会结束。 数据库实例可以看成数据库运行的状态。 本质数据库运行提供的进程和加载数据库文件到内存的过程。 默认监听的端口是1521 一个实例永远只能管理一个数据库12c及以后多租户架构下略有不同但核心仍是1对1。而一个数据库可以被多个实例同时管理 这就是 Oracle RAC (真正应用集群)多个实例分布在多台服务器上并发访问同一个数据库实现高可用与负载均衡。3表空间1. 表空间是oracle数据库的逻辑存储单元是位于数据库和数据文件之间的一层抽象相当于给数据库进行分类但它必须通过物理上实际存在的“数据文件”来承载数据。 2. 一个表空间可以包含一个或者多个物理数据文件这些数据文件可以分布在同一个目录下的不同文件、不同目录下、不同磁盘下、不同存储设备下。这样多存储路径的好处有突破OS文件大小限制、动态扩容、负载均衡、高可用、存储分层等。 3. 一个数据文件只能属于一个表空间。数据库中的数据是存在于表空间的一个或者多个数据文件中 。数据文件被加入到表空间后只有满足特定的条件才可以删除一般不删除。特定条件包括 1文件必须为空即使空表也不行。 2不能是唯一/首个文件如果是唯一/首个文件则无法删除。 3文件必须在线处于离线状态的文件无法直接删除。 4特殊表空间限制属于 system 或者 sysaux 等系统表空间的数据文件永远不能被删除。 4. 表空间的比喻小区、楼房与房间 一个小区包括A区和B区。A区包括1、2、3栋楼B区包括4、5、6栋楼。 整栋楼(1-6栋)相当于一个数据库是物理概念上的相当于oracle里的数据文件 (.dbf)实实在在存在于硬盘上占用磁盘空间。 “A区”、“B区”是逻辑上的你看不到“区”这个实体但你知道这个区的房子在哪几栋楼里相当于表空间你看不到它本身但它规定了数据存在哪些文件里。 具体楼栋比如A区2栋也是物理上的相当于表空间的真实存储路径。 5. 针对同一个表空间可以创建多个不同的用户对其进行操作不同用户根据权限不同对表空间的操作权限也不相同。oracle 对表空间和数据的管理是由用户执行不同用户可以看作单独个体可以在同一表空间中创建属于自己的私人空间多个用户间可以创建同一个名字的表表和其他数据库对象都是创建在用户下的所以说 mysql 的数据库表 类似于 oracle 的 用户schema。4用户mysql是直接使用root用户连接操作数据库的但是Oracle不是Oracle有多种不同类型的用户每种用户的权限会有一些区别用户分类有 1系统管理员 sys 最高级别权限 专业dba角色登陆使用 2一般管理员 system 日常管理的权限 普通管理员 3普通用户 a. 数据库自带的用户 scott b. 管理员创建的用户 自定义5) schema1. schema 指的是用户拥有的所有数据库对象的集合。 2. schema 是一个逻辑容器包含了某个用户创建的所有对象表、视图、索引、存储过程等。 3. 创建用户时会自动创建一个同名的 Schema不能重命名用户和 Schema 是一 一对应、不可分割的。Schema 依附于用户不能单独存在。用户记录在DBA_USERS 中同名的 Schema 记录在 DBA_SCHEMAS 中 。如果创建用户后没有创建对象那么会生成一个空的Schema。创建对象后Schema 中开始有内容。 4. schema 具体包含的内容有表、索引、视图、存储过程、函数、包、序列、同义词、触发器等。6数据库的持久化数据的永久保存。 oracle的数据都是以 .dbf 格式的文件存储在了磁盘上达到永久保存的效果。7注释单行注释-- 多行注释/* */8mysql和oracle数据库逻辑结构类比MySQL 的 “实例” ≈ Oracle 的 实例数据库。 MySQL 的“数据库” ≈ Oracle 的“用户 Schema”。【MySQLdatabase 逻辑命名空间 默认物理存储位置目录逻辑上像“业务库”物理上是一个目录Oracleuser 逻辑命名空间tablespace 物理存储位置】。 Oracle 的“表空间” ≈ MySQL 实例数据目录下的一个「文件夹 一组 .ibd 文件」。 Mysql一个 database 下的表/视图等 ≈ Oracle 一个 schema 下的表/视图等。2. 数据库操作1创建表空间create tablespace test1(表空间名) datafile E:\oracle\database\test1.dbf(物理文件路径) size 100m(默认大小) autoextend on next 10m (每次自动扩展10兆) 验证因为oracle是物理存储所以可以直接在自己的创建目录下查找有没有自己创建的文件有就是创建成功了。2创建操作表空间的用户create user aaa(用户名) identified by pwd(密码) default tablespace test1(默认可以操作的表空间) 验证可以在Objects下面的Users文件夹下找到刚创建的用户。3给用户授权grant 角色/权限 to aaa(用户) 角色分类 connect(最基本的角色) //不能创建表只能连接查询 resource (比较高级的角色) //可以创建修改表删除表 dba (最高级别权限) // 所有权限可以删除任意表4表字段1数值 number(v1,v2) //v1: 数值的总长度v2: 小数的位数如果都不写默认是32位的整数 2字符 char(10) //固定长度的字符串类型括号里的数字就是实际长度 varchar2(10) //可变长度的字符串类型括号里的数字是最大长度 3日期 date //日期格式 yyyy-MM-dd HH:mm:ss timestamp //比秒更精确的日期类型可以到秒后9位5表约束1. 主键约束 2. 唯一约束 3. 非空约束 4. 外键约束 5. 检查约束 //例如check(gender in (0,1,2)) 作用判断列值是否满足表达式6创建表create table person( pid number(10) primary key, //主键 pname vachar2(20) not null, //非空 phone varchar2(20) unique, //唯一 gender number(1) check(gender in (0,1,2)) //检查 )7修改表1. 添加列 alter table 表名 add(列名 数据类型) 2. 修改列类型/字段长度 alter table 表名 modify(列名 数据类型) 例如 ALTER TABLE WORK_JOB MODIFY (CONTENT VARCHAR2(1500)); 3. 重命名列名 alter table 表名 rename column 旧列 to 新列 4. 删除列 alter table 表名 drop column 列名3. oracle序列1序列概念1. Oracle不像mysql可以直接实现主键的自增Oracle如果想实现主键的自增需要用到辅助工具这个工具就是序列。 2. 序列是一个从指定数值开始按照特定规律不断增长的数列。2序列的创建create sequence (seq_name)序列名 [increment by 1 start with 1 ]3序列的属性nextval返回序列下一次生成的值 currval当前序列生成的值如果没有执行过 nextval那么不能执行currval不然会报错。4虚拟表dual查序列的时候如果只查 select seq_name.nextval 会报错提示需要有表但是序列没有表和之对应 这时候就会用到虚拟表 dual。 虚拟表没有实际含义 就是为了补充数据库查询的语法结构的。5序列实现主键自增实现方式主键的值用序列名.nextval 表示就可以了 例 insert into person values (seq_name.nextval,11,”asd”) ;6scott表的了解和使用Oracle数据库为了方便用户使用本身自带了一些用户和表scott用户就是系统自带的用户里面也有一些表的信息比如员工表部门表等等。4. oracle常用函数1数值函数1) ABS(x) //返回x的绝对值 2) ceil(x) //返回大于等于x的最小整数值,向上取整 3) floor(x) //返回小于等于x的最大整数值,向下取整 4) mod(x,y) //返回x除以y的余数 5) round(x[,y]) //返回四舍五入后的值, 如果y不为整数则截取y整数部分如果y0则四舍五入为y位小数如果y小于0则四舍五入到小数点向左第y位。2字符函数1) CONCAT(c1,c2) //连接两个字符串同 c1||c2 2LPAD(c1,n[,c2]) //在字符串c1的左边用字符串c2填充直到长度为n时为止c2 为追加的字符串,默认是空格 3RPAD(c1,n[,c2]) //在字符串c1的右边用字符串c2填充直到长度为n时为止c2 为追加的字符串,默认是空格 4LTRIM(c1,[,c2]) //删除c1左边出现的字符串c2默认是空格 5REPLACE(c1,c2[,c3]) //替换 c1: 被替换前的字符或者变量 c2要被替换的字符串 c3替换后的字符串默认为空(即删除之意不是空格) 6SUBSTR(c1,n1[,n2]) //在字符表达式c1里从第n1位开始取n2个字符如果不指定n2,则从第n1个字符直到结束的字串。 7trim(a from b) //将a字符首位的b字符去掉如果b不填默认是空格、 8upper(a) //将字符a转大写 9lower(b) //将b转小写3日期函数1sysdate //查询当前的系统时间 2months_between(v1,v2) //查询两个时间间隔的月数4转换函数oracle的时间格式 24小时hh24 分钟mi 1to_char(v1,v2) //日期转字符v1表示日期v2表示转换后的日期格式 例 select to_char(sysdate,yyyy-MM-dd HH24:mi:ss) //查询当前年月日时分秒 select to_char(sysdate,day) //查询当前星期几 2to_date(v1,v2) //字符转日期v1表示字符串日期v2表示转换后的日期 例 select to_date(2018-10-14 19:22:12,yyyy-mm-dd hh24:mi:ss) from dual;5通用函数1nvl(v1,v2) //空值判断如果v1为空返回v2,如果v1不为空返回v1跟mysql的ifnull(b1,b2)函数一样。6find_in_set 函数mysql select * from artile where find_in_set(3,type); oracleselect * from artile where instr(,||APPLY_ORG||,, ,473977465526550528,)0; 注 ,||APPLY_ORG||, //在字符串前后加逗号确保首尾元素能被正确匹配。 ,473977465526550528, //确保匹配完整的单词避免部分匹配7聚合函数1count() //统计数量 2sum() //求和 3avg() //求平均值 4max() //最大值 5min() //最小值 注意 聚合函数计算时会忽略 null 值的记录列值为 null 时不参与计算。 如果想让 null 值也参与计算那么可以使用nvl(v1,v2)函数。8分组函数关键字 group by 分组前筛选数据使用where 分组后筛选数据使用having 聚合函数 规则使用group by 作为分组那么select 语句中只能查询 group by 后面的列和聚合函数其他的列不能查询不然会报错。5. oracle分页查询1关键字使用关键字rownum2特点1rownum是一个伪列是数据库查询数据后生成的一系列数字数字从1开始不断自增1。 2因为rownum不是一个具体的列所有rownum可以直接查询但是不能作为列名进行关联必须要起别名才可以。 3rownum可以识别小于号(),但是不能识别大于号()针对有 的查询需要使用子查询才可以。3原理1数据库执行sql语句查询。 2提取第一条记录生成rownum 为1 。 3判断rownum 是否满足条件如果满足就返回记录如果不满足就放弃。 a. 针对 筛选第一条记录rownum 是1 满足 n(n1) 会返回数据并继续提取直到数据不满足要求。 b. 针对 筛选 如果 n0 那么所有的数据都满足要求返回的是所有的数据。如果 n 0那么第一条数据 rownum 是1就直接不符合要求没有数据返回。 4继续提取记录生成rownum。 5重复执行步骤 3、4。3分页查询分页查询 方法1 select * from (select rownum r ,a.* from a ) t where t.r (pagenum-1)* pagesize and t.r pagenum* pagesize 1) 方法2 select * from (select rownum r, a.* from a where rownum pagenum* pagesize 1) t where t.r (pagenum-1)* pagesize ; pagenum 第几页 pagesize一页几条数据6. oracle视图1概述视图可以看作一个虚拟表不真实存储数据主要用于数据的查询数据的来源为原始表。 创建视图的意义 1简化sql语句的开发将一个查询结果作为一个视图下次可以直接调用。 2权限细分以及数据安全创建的视图只有原表的一部分数据并且只读不可修改。2格式create view 视图名称 as select 语句3注意点1. 如果视图数据来源于单表且不含distinct、聚合函数、分组函数、分页等信息那么可以修改视图。 2. 如果是复杂视图包含连表查询子查询那么不能直接更新视图的数据需要去更新基表的数据。7. oracle索引1格式create index 索引名 on 表名(列1名称列2名称)8. oracle foreach标签的使用oracle不支持原生的foreach标签操作需要修改如下 insert idimportExcelData parameterTypelist useGeneratedKeysfalse insert into tbl_marketing_activities (id,owner,type,name,state,startdate,enddate,budgetcost,actualcost,description,createby,createtime) select t.* from( foreach collectionlist itemobj separatorunion all select #{obj.id},#{obj.owner},#{obj.type},#{obj.name},#{obj.state},#{obj.startDate},#{obj.endDate},#{obj.budgetcost},#{obj.actualcost},#{obj.description},#{obj.createBy},#{obj.createTime} from dual /foreach ) t /insert 注意 1SQL中没有VALUES 2foreach标签中的separator的属性为UNION ALL将查询合并结果集。 3要在insert标签中加入useGeneratedKeysfalse如果不加则会报如下错误 ### Cause: java.sql.SQLSyntaxErrorException: ORA-00933: SQL 命令未正确结束。9. 数据迁移需要注意的点1长字符串的处理oracle insert 和 update 的时候最大可操作的字符串长度小于等于4000个单字节如果要插入更长的字符串需要考虑字段使用CLOB 类型方法借用ORACLE 里自带的DBMS_LOB 程序包。 因此导数据或者插入数据的时候需要进行长度判断超出长度的字段值应该提出警告返回上次操作。2空字符的处理Mysql的非空字段可以有空的内容(空字符串)但是oracle里定义了非空字段就不允许有空的内容。 如果按照mysql的 NOT NULL 来定义 ORACLE表结构导数据的时候会产生错误。 因此导数据的时候要对空字符进行判断如果为NULL或者空字符需要把它改成一个空格的字符串。10. mysql和Oracle区别1主键mysql 一般使用主键自增创建表的时候只要指定表的主键为 auto_increment插入记录时不需要再指定记录的主键值mysql将自动增长 oracle 没有自动增长类型需要使用序列。插入记录的时候将序列号的下一个值赋值给主键达到类似自增的效果。2单双引号的处理mysql里可以既可以使用单引号包起字符串也可以使用双引号包起字符串默认情况下功能相同。 oracle里只可以使用单引号来包起字符串而双引号用来包起 标识符(表名、列名。别名)。 如 -- 错误Oracle 中双引号用于标识符不是字符串 SELECT * FROM users WHERE name John; -- ORA-00904: John: invalid identifier -- 正确字符串必须用单引号 SELECT * FROM users WHERE name John;3翻页mysql处理翻页比较简单直接使用limit(a,b)即可。 a为小值b为大值。 oracle翻页需要使用关键字rownum。 rownum特点 1rownum是一个伪列是数据库查询数据后生成的一系列数字数字从1开始不断自增1。 2因为rownum不是一个具体的列所有rownum可以直接查询但是不能作为列名进行关联必须要起别名才可以。 3rownum可以识别小于号(),但是不能识别大于号()。 具体使用rownum进行分页查询方法 select * from (select rownum r, a.* from a where rownum pagenum* pagesize 1) t where t.r (pagenum-1)* pagesize ; --pagenum 第几页 --pagesize一页几条数据4长字符串的处理mysql字符串最大长度可以达到1万以上。 oracle字符串最大长度只有4000个单字节如果要插入更长的字符串需要使用CLOB类型方法借用ORACLE 里自带的DBMS_LOB 程序包。