Oracle

简介

• Oracle 服务器:是一个关系数据库管理系统(RDBMS)，它提供全面的, 近乎完整的信息管理。由Oracle 实例和Oracle 数据库组成

• Oracle 数据库: 位于硬盘上实际存放数据的文件, 这些文件组织在一起, 成为一个整体, 即为 Oracle 数据库. 因此在 Oracle 看来, “数据库” 是指硬盘上文件的集合, 必须要与内存里实例合作, 才能对外提供数据管理服务。

• Oracle 实例: 位于内存里，它由一个共享的内存池和多个进程所组成, 共享的内存池可以被所有进程访问. 用户如果要存取数据库(也就是硬盘上的文件) 里的数据, 必须通过实例才能实现, 不能直接读取硬盘上的文件。
  优点：失败转移,负载平衡

• 表空间和数据文件
  表空间由多个数据文件组成，数据文件只能属于一个表空间
  表空间为逻辑概念，数据文件为物理概念

• 段、区和块
  段存在于表空间中
  段是区的集合
  区是数据块的集合
  数据块会被映射到磁盘块
  

数据类型

sqlplus命令

运行窗口输入sqlplus,进入操作oracle界面，输入用户名和密码

• 显示格式化操作
  设置每行显示的数据长度：SET LINESIZE 300
  设置每页显示的数据行数： SET PAGESIZE 30
• 调用记事本命令
  ed 文件名称（没有则创建，编写完保存即可）
  执行命令：@文件名称（如果后缀是“*.sql”,后缀可以不写），如果要执行磁盘上的一个sql文件，必须输入绝对路径名称。
• 切换用户
  CONN 用户名/密码 【AS SYSDBA】,如果现在使用的是SYS账号登录，那么必须要加上【AS SYSDBA】,
• show user 来查看当前登录用户是哪一位。
• 调用本机命令
  host 本机命令

查询

• 消除重复数据，使用DISTINCT关键字处理，此关键字只能够出现在SELECT字句中
• 所有的关系运算符是不区分数据类型的。空判断：IS NULL ,IS NOT NULL
• 大小写转换函数
  转大写：字符串 UPPER(字符串|数据列)
  转小写：字符串 LOWER(字符串|数据列)
• 首字母大写，其余字母小写
• 字符串 initcap(字符串|列)
  取得字符串长度：数字 LENGTH(字符串|列)
  替换制定字符串的内容：字符串 REPLACE（字符串|列，old string,new String ）
  字符串截取操作（索引从一开始）：
• 有指定位置截取到结尾：字符串 SUBSTR(字符串|数据列，截取开始索引)
• 指定截取的开始位置和结束位置： 字符串 SUBSTR(字符串|数据列，截取开始索引，截取结束索引) 。
• oracle中的SUBSTR()函数进行截取的时候下标是0还是1开始没有任何的区别，oracle之中的字符串是从1开始的，但是即使你设置为0，oracle会自行处理，同时，SUBSTR()函数还可以设置为负数，表示从后面的指定位置开始。
• 去掉左右空格函数：字符串TRIM(字符串|列)

数字函数

• 四舍五入函数：数字 ROUND(数字|列【，保留位数】)
  
• 截取小数函数：数字 TRUNC(数字 |列【，保留小数为】)
• 求模： 数字 MOD(数字1|列1 ，数字2|列2 )

日期函数

oracle中提供有俩个伪列（是不存在表中的列，但是却可以直接使用的列）：SYSDATE,SYSTIMESTAMP

• 当有了系统日期之后就可以通过以下三个公式来进行一些简单的计算
  日期+数字=日期，表示若干天之后的日期；
  日期 -数字=日期，表示若干天之前的日期；
  日期-日期=数字，表示连个日期之间的间隔天数。
  范例：SELECT SYSYDATE +3 FROM dual;
• 计算两个日期之间所经历的月数：
  格式： 数字 MONTHS_BETWEEN(日期1|列1，日期2|列2 )
• 加上指定月之后的日期：日期 ADD_MONTHS(日期|列，月数)
• 求出指定的下一个一周事件数的日期：日期 NEXT_DAY(日期|列，一周时间数）
  范例：计算下一个星期二
  select NEXT_DAY(SYSTEM.‘星期二’) from dual ;
• 求出指定日期所在月的最后一天日期：日期 LAST_DAY(日期|列)

转化函数

• 转字符串数据：字符串 TO_CHAR(数字|日期|列，转换格式)
  
• 转日期数据：日期 TO_DATE(字符串，转化格式)
• 转数字数据：数字 TO_NUMBER(字符串，转换格式)

通用函数

通用函数是oracle的特色函数，有两个：NVL( ) DECODE()

• 处理null数据： 数字 NVL（列|数据，默认值）
• 多数据判断：数据 DECODE(字段|数据，判断数据1，显示数据1，判断数据2，显示数据2。。。【默认显示】)

多表查询

• 内连接，也叫等值连接（类似a.id=b.id）
• 外链接（左右全）
  

where与having的区别

• where子句：是在分组前使用，而且不能够使用统计函数进行验证（经过where筛选后的数据才可以进行分组。）
• having子句：必须结合GROUP BY子句一起出现，是在分组后的过滤，可以使用统计函数。
• where子句中的条件表达式having都可以跟，而having子句中的有些表达式where不可以跟；having子句可以用集合函数（sum、count、avg、max和min），而where子句不可以。

组函数与分组统计查询

• count(* ), count(字段)，count(distanct 字段)的区别
  

• 关于统计查询的几个重要说明
  统计函数单独使用时（没有GROUP BY字句）只能够出现统计函数，不能够出现其他字段。
  使用统计查询时（存在GROUP BY字句），SELECT字句之中只允许出现统计函数与分组函数字段，其他的任何字段都不允许出现。
  统计函数在分组之中可以嵌套使用，但是嵌套之后的统计查询之中，SELECT子句不允许再出现任何的字段，包括分组字段。
  #数据更新操作

• 将表1复制到表2
  CREATE TABLE tableName AS SELECT * FROM table1;
  但是此类复制语法只是复制数据本身。约束，关系等等全都不会复制，而且此语法只有oracle能用。

行号：ROWNUM

行ID:ROWID

在进行数据显示时，用户可以通过ROWNUM这个伪列，动态的生成行的编号。

• 在oracle里面ROWNUM主要可以完成两个任务：
  取得第一行数据：
  取得前N行数据。
  

序列

• 语法
  CREATE SEQUENCE 序列名称
  【INCREMENT BY 步长】【START WITH 开始值】
  【MAXVALUE 最大值| NOTMAXVALUE】
  【MINVALUE 最小值| NOTMINVALUE】
  【CYCLE|NOCYCLE】
  【CACHE 缓存大小| NOCACHE 】
  

视图

• 语法
  CREATE [OR REPLACE ] VIEW 视图名称 AS 子查询
  [ WITH CHECK OPTIONI][ WITH READ ONLY]
• 查看所有视图
  SELECT * FROM User_Views
• 视图可以进行更新操作（单表可以，多表不行）
  当我们向视图中插入（删除）数据时，（这里指的的放入不符合查询条件的数据，视图中没有，而数据的来源表中却存在这样的一条数据）
• 查询所有的表
  SELECT * from tab
• 同义词（需管理员权限才能操作）
  CREATE [PUBLIC ] SYNONYM 同义词的名称 FOR 用户名.表名称

数据库备份

• 数据的导出操作（小数据）
  新建文件夹
  通过命令行进入该目录下
  执行exp指令
  输入用户名和密码
  导出文件：EXPDAT.DMP
• 导入文件（小数据）
  进入导出文件的路径
  执行imp指令导入数据

数据库的冷备份

用户管理（管理员操作）

• 创建用户
  CREATE USER 用户名 IDENTIFIED BY 密码
• 为用户授予连接数据库(建表，序列 等等)的权限
  GRANT CREATE SESSION（TABLE SEQUENCE …） TO 用户名
• 在ORACLE里面提供了两个角色（每一个校色包含有多个权限）：RESOURSE,CONNECT,一般只需要将这两个角色授予新用户即可。
  语法：GRANT CONNECT ,RESOURCE TO 用户名
• 修改密码
  ALTER USER 用户名 IDENTIFIED BY 新密码
• 让用户的密码在登录一次后失效，强迫用户去修改密码
  ALTER USER 用户名 PASSWORD EXPIRE
• 锁定一个用户，让用户无法使用
  ALTER USER 用户名 ACCOUNT LOCK;
  ALTER USER 用户名 ACCOUNT UNLOCK;
• 对象权限
  以上都是为用户授予的系统权限，还存在对象权限，主要包含四个权限:INSERT UPDATA ,DELETE,SELECT
• 范例：将scott.emp 的INSERT ,SELECT 权限授予用户
  GRANT SELECT ,INSERT ON scott.emp TO 用户名

权限回收(REVOKE)

• 回收对象权限
  REVOKE SELECT,INSERT ON scott.emp FROM 用户名
• 回收角色
  REVOKE CONNECT,RESOURCE FROM dog;
• 回收单个权限
  REVOKE CREATE TABLE,CREATE SEQUENCE FROM 用户名
• 删除用户名
  DROP USER 用户名 CASCADE

安装与卸载Oracle

• 安装（没写的代表默认）
  点击xxx.exe–>创建和配置数据库–>服务器类–>单实例数据库安装（另外一个更高级核心一点）–>高级安装(进入配置界面，例如语言等)–>企业版–>安装路径–>一般用途/事务处理–>配置选项（编码处理,示例方案（选中））–>方案口令（对所有用户使用相同的口令(简单起见如此定义，你也可以单独定义)）–>进入安装（完成后不要急于确定（下一步），进入用户名密码的设置）–>口令管理（修改四个用户名密码，切记不要锁定用户（sys/change_on_install,system/manager,scott/tiger,sh/sh））
• 卸载Oracle
  开始－>设置－>控制面板－>管理工具－>服务 停止所有Oracle服务。
  开始－>程序－>Oracle - OraDb11g_home1－>Oracle安装产品－> Universal Installer
  卸装所有Oracle产品，但Universal Installer本身不能被删除[如果第二步执行失败，跳到第三步，大部份第二步是失败的]
• 运行regedit，选择HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE，按del键删除这个入口。
• 运行regedit，删除以下这三个位置中的所有Oracle入口。
  HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Services\【下】所有Oracle删除
  HKEY_LOCAL_MACHINE\SYSTEM\ControlSet002\Services\【下】所有Oracle删除
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\【下】所有Oracle删除
• 运行regedit，
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\【下】所有Oracle删除，
  删除所有Oracle入口。
• 开始－>设置－>控制面板－>系统－>高级－>环境变量
  删除环境变量CLASSPATH和PATH中有关Oracle的设定
• 从桌面上、STARTUP（启动）组、程序菜单中，删除所有有关Oracle的组和图标
• 删除e:/oracleDB目录
• 【重新启动计算机】，重起后才能完全删除Oracle所在目录
• 删除与Oracle有关的文件，选择Oracle所在的缺省目录C:\Oracle，删除这个入
  口目录及所有子目录，并从Windows目录（一般为C:\WINDOWS）下删除oralce文件等等。
• 在运行框中输入“win.ini”，回车。WIN.INI文件中若有[ORACLE]的标记段，删除该段
• 【如有必要】，删除所有Oracle相关的ODBC的DSN
• 到事件查看器中，删除Oracle相关的日志
  说明：
  如果有个别DLL文件无法删除的情况，则不用理会，重新启动，开始新的安装，
  安装时，选择一个新的目录，则，安装完毕并重新启动后，老的目录及文件就可以删除掉了