Oracle数据库架构与操作指南-优快云博客

实例名SID 一般数据库名和实例名是一对一, 集群中数据库名和实例名是一对多的关系
服务名service_name oracle9i开始引入数据库服务名。SERVICE_NAME的出现就是为了应对并发技术。如果数据库有域名，则数据库服务名就是全局数据库名；否则，数据库服务名与数据库名相同
TNSName 把<host>:<port>/<service_name> 以别名的形式写入到本地oracle客户端 tnsnames.ora 中, 这个别名就对应具体连接信息

Java JDBC Thin Driver 连接 Oracle有三种方法

(1)ServiceName:  推荐格式，对于集群来说，每个节点的SID 是不一样的，但是SERVICE_NAME 确可以包含所有节点
    jdbc:oracle:thin:@//<host>:<port>/<service_name>
    #Example: jdbc:oracle:thin:@//192.168.2.1:1521/XE

Oracle RAC环境, thin连接字符串可以用正斜杠+服务名

jdbc:oracle:thin:@<host>:1521/<service_name>

(2)SID:
    jdbc:oracle:thin:@<host>:<port>:<SID>
    #Example: jdbc:oracle:thin:@192.168.2.1:1521:X01A
(3)TNSName:
    jdbc:oracle:thin:@<TNSName>
    #Example: jdbc:oracle:thin:@GL

工具:

客户端机器连接oracle数据库，需要在客户端机器上安装oracle的客户端软件

sqlplus：oracle11g自带的一个客户端黑屏界面工具，可以连接到某个数据库的实例

SQL PLUS命令：是SQLPLUS工具中特有的语句：

1.在该工具中执行的命令叫SQLPLUS命令；2.关健字可以简写；3.提倡大写；4.不能够对表数据进行增删改查操作，只能完成显示格式控制；5.可以不用分号结束；6.通常称做命令。

本地数据库
sqlplus 用户名/密码 [as 角色名]

远程数据库
sqlplus 用户名/密码@ip地址[:端口]/service_name [as 角色名]

设置显示的列宽
字符型varchar2、日期型date：column 字段 format a12;    -- 设置字段显示12个宽度位
数值型number：column 字段 format 9999;   -- 设置字段显示4个数字位宽
设置显示的高度：set pagesize 80;   -- 页显示80条记录
执行最近一次的SQL语句：/

三方客户端工具：PLSQL Developer (常见)、Navicat Premium (简单)、Toad......

使用

用户模式

用户=账户, 　用户的权限不同

用户拥有模式（一一对应，名称相同），模式拥有模式对象。

模式（方案）：一个用户拥有数据库对象的集合；

模式对象：逻辑结构（段，约束、视图、过程、程序包）；

用户访问其他模式对象要有　访问权限，附加模式名作为前缀

超级管理员身份：超级用户名sys，角色dba

示例模式：scott，hr

解锁用户：alter user scott/hr account unlock;
设置密码tiger：alter user scott/hr identified by tiger;

超级管管理员角色进入：sqlplus / as sysdba

普通用户进入：sqlplus scott/tiger

use 数据库名

查询当前用户是谁：show user;

查询用户下的所有对象：select * from tab;

查询emp表的结构：desc emp;

使用字符串连接符号：select 'hello' || ' world' "结果" from dual; --输出hello world，单引号是字符串型或日期型

系统当前时间：select sysdate from dual;

保存SQL语句到硬盘：

spoole:/oracle-1.sql;

sql语句；

spool off;

将硬盘文件e:/oraclesql.sql，读到orcl实例中并执行：

@ e:/oraclesql.sql;

oracle中三大类型

字符 varchar2，数字 number，日期 date

定长字符 char, 大字段 LOB, ROWID (隐式的,保存每条记录的物理地址,通过它访问速度最快)

（1）number(5)：5表示最多存99999
（2）number(6,2)：6表示小数+整数不多于6位。2表示最多显示2位小数，采用四舍五入，不足位数补0，同时要设置col ... for ...
（3）varchar2(8)：8表示字节
（4）date：默认格式为：'27-月-15'
（5）CLOB【Character Large OBject】：大文本对象，即超过65565字节的数据对象，最多存储4G
（6）BLOB【Binary Large OBject】：大二进制对象，即图片，音频，视频，最多存储4G

隐式数据类型转换

（1）varchar2变长/char定长 --> number。例如：'123'->123
（2）varchar2/char --> date。例如：'25-4月-15'->'25-4月-15'
（3）number ---> varchar2/char。例如：123->'123'
（4）date ----> varchar2/char。例如：'25-4月-15'->'25-4月-15'

隐式转换注意：
1）=号二边的类型是否相同
2）如果=号二边的类型不同，尝试的去做转换
3）在转换时，要确保合法合理，否则转换会失败，例如：12月不会有32天，一年中不会有13月

显式转换：

将数字/日期转成字符串：to_char(数值/日期,'格式yyyy mm dd $ L "常量"' )函数

将字符串转成日期：to_date('字符串','格式')函数

将字符串转成数字：to_number('字符串')函数

函数：待续

TO_CHAR(数值/日期,'格式')

TO_DATE('字符串','格式')

TO_NUMBER

日期格式: YYYY MM DD HH24 MI SS

SYSDATE() 返回系统时间

NVL(a,b) 如果a是NULL，用b替代

NVL2(a,b,c) 通用函数，如果a不为NULL，取b值，否则取c值

NULLIF(a,b) 通用函数，在类型一致的情况下，如果a与b相同，返回NULL，否则返回a

decode() decode(字段,条件1,表达式1,条件2,表达式2,...表达式n)

等价于sql99的case ···

case 字段
when 条件1 then 表达式1
when 条件2 then 表达式2
else 表达式n
end

多表查询：

理论基础：笛卡尔集表=列数之和，行数之积

1.内连接【只能查询出符合条件的记录】：等值连接（只能使用=号）；非等值连接（不能使用=号，其它符号可以，例如：>=，<=，<>，betwen and等）
2.外连接查询【既能查询出符合条件的记录，也能根据一方强行将另一个方查询出来】

(+) 哪张表少加在那张

左外连接【左表数据全部保留】：
select dept.deptno "部门号",dept.dname "部门名",count(emp.empno) "人数"
from dept,emp
where dept.deptno = emp.deptno(+)
group by dept.deptno,dept.dname;

3.自连接【将一张表，通过别名看作两张表内/外连接】

集合查询：

并集运算 (合并多个查询结果)

union：二个集合中，如果都有相同的，取其一

union all：二个集合中，如果都有相同的，都取

交集运算[intersect]

差集运算[minus]

使用set time/timing on，打开时间的开关set time on;set time off;

使用set tim/timing off，关闭时间的开关set timing on;set timint off;

集合查询的细节：

1）集合操作时，必须确保集合列数是相等
2）集合操作时，必须确保集合列类型对应相同
3）A union B union C = C union B union A
4）当多个集合操作时，结果的列名由第一个集合列名决定

oracle分页：rownum

1）rownum是oracle专用的关健字
2）rownum与表在一起，表亡它亡,表在它在
3）rownum在默认情况下，从表中是查不出来的
4）只有在select子句中，明确写出rownum才能显示出来
5）rownum是number类型，且唯一连续
6）rownum最小值是1，最大值与你的记录条数相同
7）rownum也能参与关系运算
* rownum = 1 有值
* rownum < 5 有值
* rownum <=5 有值
* rownum > 2 无值
* rownum >=2 无值
* rownum <>2 有值与 rownum < 2 相同
* rownum = 2 无值
8）基于rownum的特性，我们通常rownum只用于<或<=关系运算。用别名代替做>运算

索引

查询数据时会把所有记录读取出来,然后与查询条件对比. I/O开销大时间长

针对WHERE子句频繁的大表建立索引, 将经过算法优化后表记录的ROWID存储在索引段中

表与索引在同一个表空间:简化表空间管理. 在不同表空间:提高访问性能

索引能提高查询速度, 索引会降低DML语句速度

B树索引

位图索引

反向键索引

查询其他用户的存储过程:
select * from DBA_objects where object='PROCEDURE' and owner='用户'

进入回收站
drop table users;

查询回收站中的对象
show recyclebin;

闪回，即将回收站还原
flashback table 表名 to before drop;
flashback table 表名 to before drop rename to 新表名;

彻底删除users表
drop table users purge;

清空回收站
purge recyclebin;

分区

批量导入数据

方法1 (少量数据) 表A insert语句 into表B
方法2 exp 表A 然后imp表B exp 时可加查询条件
方法3 sql Loader(sqlldr) 将格式化的文本数据上载到表中去

sqlldr是oracle自带的一个工具，用于导入文本文件的内容到oracle数据库中。
工具在：\ORACLE_HOME\bin\sqlldr.exe

首先需要有一个数据文件
然后编写控制文件 *.ctl

*.ctl格式为：

load data                --告诉sqlldr要装载数据
infile "d://test.txt"    --外部数据文件,可加多个
infile "d://test1.txt"

truncate into table test --先truncate表，然后插入
append into table test   --向表中追加数据

fields terminated by ","    --外部文件的数据以“，”分隔
optionally enclosed by '"'  --部分字段可以用双引号包起来

trailing nullcols        --表中的字段没有对应的值时填充空值
(
name "upper(:name)",     --将插入的值转换为大写
con ":id||:name",        --表中con列的值是ID和NAME的组合值
dt date"yyyy-mm-dd"      --插入日期型数据
)

命令格式： 
sqlldr username/password@sid control=*.ctl

完整命令:
\ORACLE_HOME\bin\sqlldr.exe 
userid=username/password@sid
control="控制文件.ctrl" 
data="数据文件.csv" 
log="记录的日志文件.log" 
bad="坏数据文件.csv.bad"