目录
oracle小问题
JDBC连接oracle的三种格式和连接oracle rac的多个虚拟ip
双引号在Oracle中表示区分大小写，Oracle是严格区分大小写的，未使用双引号时默认全部大写。可能很多人都会误认为oracle是不区分大小写的，原因是我们在写查询语句的时候，大小写无所谓，结果都是一样的，这只是个表面现象，只要你不使用双引号表示区分大小写，oracle会默认把你写的脚本全部转换为大写的。
基础介绍
SQL是一种结构化查询语言，上世纪80年代世界上基本有80多种数据库，每一个数据库都有自己的一套操作命令，开发者从A数据库切换到B时，需要重新学习。在70年代末，IBM开发了最早的SQL操作标准，而Oracle数据库是世界上第一个支持SQL语法的数据库。
发展到今天，所有的关系型数据库都支持SQL语法了。常用的是DB2、Oracle、SQL server、MySQL。对应的数据量和场景一般是几十亿、上亿、8000万、500万。
网上很多免费课程资源，个人认为不错的是下面网易云课程的：
SQL分三种类型：
DML（数据操作语言）：数据库的查询和更新操作。
DDL（数据定义语言）：数据对象的创建（表、用户），比如create、drop、alter，需要用到设计范式
DCL（数据库控制语言）：进行数据权限的管理操作，由DBA负责，系统人员工作。
1.查用户所有的表
select * from tab;
SELECT语句的查询顺序，首先是FROM表，然后是WHERE过滤条件，其次是group by ，然后是SELECT选择列，最后是ORDER BY 排序语句
格式化指令：
设置每行显示的数据长度：SET LINESIZE 300;
设置每页显示的数据行数：SET PAGESIZE 30;
设置字段显示的长度： COL 字段 FORMAT A10;
切换用户：
CONN 用户名/密码 [AS SYSDBA]，如果是sys用户一定要加SYSDBA
常用函数
字符函数：
UPPER()：转大写字母
LOWER()：转小写字母
INITCAP()：首字母大写
LENGTH()：查询字段的长度
SUBSTR(列|字符串，开始索引，长度)：截取函数，需要注意的是Oracle中substr的截取索引首字母是从1开始！
substr(ename,length(ename)-2,3) 的效果等同于 substr(ename,-3)
数值函数：
ROUND(数字，[小数位])：不加小数据默认不保留小数进位；小数位可以为负数，表示对指定的整数位进位
TRUNC(数字，[小数位])：类似round函数，只不过不四舍五入
MOD(被除数，除数)：求模（求余）函数
日期函数：
SYSDATE、SYSTIMESTAMP称为伪列
日期的操作公式：
利用日期函数计算是最准确的：
ADD_MONTHS(日期，月数)：指定日期增加若干个月的日期
MONTHS_BETWEEN(日期，日期)：两个日期之间的月数
LAST_DAY(日期)：指定日期所在月的最后一天
NEXT_DAY(日期，‘星期X’)：指定日期下一周所在的日期
查看scott用户表emp中雇佣日期到今天一共是多少个年、月、日逻辑：
select
empno,
ename,
hiredate,
trunc(months_between(sysdate,hiredate)/12)year,
TRUNC(MOD(months_between(sysdate,hiredate),12)) month,
TRUNC(SYSDATE-ADD_MONTHS(HIREDATE,months_between(sysdate,hiredate)))DAY
from emp ;
转换函数
重要：TO_CHAR(日期|数字，转换格式)：将日期或者数字转换为字符串
TO_DATE(字符串，转换格式)：将字符串转换为日期
TO_NUMBER(字符串)：将字符串转换为数字
日期的转换格式：
日期：年（yyyy）月（mm）日(dd)
时间：时（hh、hh24）分（mi）秒(ss)
数字：任意数字（9）货币符号（L）
TO_CHAR函数可以将日期格式化，同时很重要的作用是可以将日期拆分！也可以拆分数字。
而Oracle带类型的自动转换，即使‘1’+‘2’也会自动把字符转换为数字计算，不用to_number（）
通用函数
NVL(NULL，默认值)：处理NULL的函数，即如果传入值是NULL则用默认值显示，否则显示传入值
DECODE(字符串|数值，比较内容1，显示内容1，比较内容2，显示内容2，...[默认显示内容])：相当于if else的作用
统计函数
COUNT () MAX() MIN() SUM() AVG()
其中count(字段) count(*) 和count(distinct *)的区别：
count(*)返回查询数据的行数，
count(字段)是返回不为空的字段的行数，如果字段这一列的数据不为空，效果和count（*）是一样的
count(distinct *)：返回去重后的数据的行数
注意点：分组函数可以嵌套，但是select中只可以用嵌套的统计函数，例如：
SELECT MAX(AVG SAL)
FROM emp
GROUP BY deptnoWHERE HAVING的区别：
where发生在group by分组之前，属于分组前的数据筛选；不允许使用统计函数
having是发生在分组后，是属于分组后的数据筛选；可以使用统计函数
多表查询
多表查询一定是有笛卡尔乘积的，只是通过where条件显示过滤了。优秀的系统设计师不使用多表查询。
所以，多表查询是会影响性能的。
内连接：也称为等值连接。
外连接：左外连接、右连接、全连接。
Oracle的外连接也可以用（+）表示：left join 可以用a.id=b.id(+)
数据集合
UNION、 UNION ALL 、MINUS 、INTERRECT ：多个结果返回列的结构需要一样。
union相比union all多了一个去重。union不会把union重复的记录显示，会去重后显示。
INTERSECT：取两个数据的交集。
MINUS：返回差集合，由第一个查询减去第二个查询结果。
子查询
可以用于where，having，from各个地方，其中WHERE和FROM使用的子查询最多
IN ANY ALL
=ANY 和IN的功能是一样的
>ANY：比子查询的最小值要大
<ANY：比子查询的最大值要小
>ALL：比子查询的最大值要大
<ALL：比子查询的最小值要小
实际工作中的子查询主要是为了解决多表查询笛卡尔积带来的性能问题。
EXISTS：in是对一个数据列的验证，exists针对的是一个数据行的验证，并且EXISTS在验证的时候，所有的数据行信息都要验证
事务
事务是针对数据更新使用的，只有DML的更新操作才有事务的支持。
session：会话的 意思，表示唯一的一个用户，每一个登陆Oracle的用户都会建立一个session会话，而每一个session上都拥有独立的事务处理操作。比如不同的客户端以同一个用户登陆，都会有不同是session。
session上有两个命令：
commit：提交。执行了多条更新操作，只有执行commit之后，更新才会真正发出；没有执行commit之前，所有的更新操作都会在缓冲区之中。
rollback：事务回滚操作。如果发现更新的操作有问题，则恢复所有的更新操作，以保证原始数据不被破坏。
死锁
不同的session对同一条记录更新操作会是等待的命令，以保证数据更新的操作
伪列
SYSDATE SYSTIMESTAMP是伪列，ROWNUM ROWID
行号：ROWNUM
所有rownum不是固定的，是动态显示的行号。
作用： 1. 限制返回函数 比如：select *from myemp where rownum <3;
2.给查询的数据进行编号
3.数据分页
数据分页
ROWID
进行表中数据保存的时候，除了用户可以见到的数据外，所有表中的数据行都会有一个唯一的一块物理地址编号，这种编号可以通过rowid找到。
通过rowid查找数据是最快的，比通过主键还要快！
表的创建和管理
表的重命名
Oracle中的每一张表都是Oracle的一个对象，为了能够记录所有对象的信息，提供了数据字典，一个有三个级别：
1. 用户级别：user_*开头，是一个用户使用的数据字典
2.管理员级别：dba_*开头，由数据库管理员使用的数据字典
3.全部级别：all_*开头，用户和管理员都可以使用的数据字典
表属于数据库的对象，对数据库对象的操作只有3类语法： CREATE DROP ALTER
所以表的重命名实际上就是更新数据字典中的信息。但是需要使用Oracle的特定命令完成：
RENAME 旧表名 TO 新表名;
在Oracle里一旦发生了DDL操作，所有未提交的事务会自动提交。
截断表
TRUNCATE TABLE xxx
表被截断后，所占用的数据空间都会被释放掉。
表的删除
表的删除是属于对象的删除。
DROP TABLE 表名称；
Oracle10 g之前，drop table命令后，对数据库而言表就是被删除了，从Oracle 10g之后有了闪回（flash back）技术，类似Windows的回收站，删除表后是先将表放在回收站中，在数据字典中提供了BIN*，表明数据不是空的。
闪回技术也是Oracle自己的，不是标准SQL
查看回收站：
select * from user_recyclebin;
表恢复：
FLASHBACK TABLE MYDEPT TO BEFORE DROP;
强制删除表，不经过回收站：
DROP TABLE mydept PURGE;
清空回收站；
PURGE RECYCLEBIN;
在drop table 表之后会有BIN开头的文件，若想删除需要1.清空回收站或者2.PURGE TABLE 表
数据库的约束
约束是保证数据完整性的一种手段，SQL中约束分为六种：数据类型、非空约束、唯一约束、主键约束、检查约束、外键约束。
非空约束 NK：不为空
唯一约束UNIQUE UK：
Null不受唯一约束的限制。每个约束也是对象，可以在数据字典“user_constraints”中查看哪张表里有约束，“user_cons_columns”可查看约束的列。但是数据字典都是DBA一般查看，开发者开发的时候设置约束可指定对象。
比如设置约束
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20) NOT NULL,
email VARCHAR2(30),
CONSTRAINT uk_email UNIQUE(email)
)主键约束：唯一约束+非空约束 PK
CREATE TABLE memberkey(
mid NUMBER,
name VARCHAR2(20) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY(mid)
);
insert into memberkey(mid,name) values(1,'wang');
insert into memberkey(mid,name) values(1,'li');
insert into memberkey(mid,name) values(null,'li');
报错会有设置好的约束对象名称。
检查约束：CHECK CK
需要注意的是表设置过多的检查约束，会影响更新的速度性能。
DROP TABLE member PURGE;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20) NOT NULL,
age NUMBER,
CONSTRAINT ck_age CHECK(age between 0 and 150)
);
insert into member(mid,name,age) values(1,'wang',12);
insert into member(mid,name,age) values(1,'li',200);
insert into member(mid,name,age) values(null,'li',20);
外键约束：
外键关联指的是子表中的某一列与父表中的某一列数据范围相匹配，FORIGN KEY FK。
DROP TABLE book PURGE;
DROP TABLE member PURGE;
CREATE TABLE member(
mid NUMBER,
name VARCHAR2(20) NOT NULL,
CONSTRAINT pk_mid PRIMARY KEY(mid)
);
CREATE TABLE book(
bid NUMBER,
title VARCHAR2(20) NOT NULL,
mid NUMBER,
CONSTRAINT pk_bid PRIMARY KEY(bid),
CONSTRAINT fk_mid FOREIGN KEY(mid) REFERENCES member (mid)
);
insert into member(mid,name) values(1,'wang');
insert into member(mid,name) values(2,'li');
insert into book(bid,title,mid) values(1001,'java',1);
insert into book(bid,title,mid) values(2001,'python',2);
insert into book(bid,title,mid) values(3001,'Oracle',1);
限制1：表中有外键关系，删除父表前一定先删除子表
但是外键约束的父表和子表会有一个问题，删除父表时，若子表还存在，因为子表的依赖，父表无法删除，若想强制删除需要使用命令：
DROP TABLE member CASCADE CONSTRAINT;
限制2：父表中作为外键关联的主键，必须设置为主键约束或者是唯一约束
限制3： 默认情况下，如果父表中有对应的子表记录，那么父表记录无法删除。应该先删除子表记录后再删除父表记录。
但是我们需要删除父表的记录后，子表的相应数据自动删除，这就需要配置级联操作。
数据的级联删除：ON DELETE CASCADE
级联更新： ON DELETE SET NULL
序列
所有的关系型数据库中都会有自增长的列，Oracle在12c之后才会提供自动增长列，之前的Oracle版本都只能通过手工通过序列的方式实现。
序列的创建
CREATE SEQUENCE myseq
INCREMENT BY 2;
创建的默认序列最小值为1，没有最大值，开始值为1，步长默认值为1
序列对象创建完后会保存在数据字典中，若要查询可以使用USER_SEQUENCES；
DROP SEQUEENCE myseq;
序列的使用
序列使用有两个伪列
序列对象.nextval
序列对象.currval
如果序列要想为某个字段进行自动编号操作，只能使用insert操作，不能再创建表的时候实现。
视图
包装了复杂查询的SQL语句对象。
创建视图：
CREATE VIEW 视图名称 AS SELECT
CREATE OR REPLACE VIEW 视图名称 AS SELECT ：视图不存在则创建一个新的视图，存在则替换原有视图。
为了保护视图的创建条件不被更新，会在末尾加一句WITH CHCECK OPTION;
但是我们创建视图的时候都会创建只读视图，加一句WITH READ ONLY
删除视图：
DELETE VIEW 视图名称;
索引
CONN sys/change_on_install AS SYSDBA;
SET AUOTOTRACE ON ; --打开跟踪器
查看SQL语句可以发现是全表扫描TABLE ACCESS FULL|
创建索引
CREATE INDEX emp_sal ON emp(sal);
使用索引可以提升数据的查询性能，但是表中的数据量很庞大，上亿条时，改变数据就会改变索引树，索引就会变成拖累，反而会降低数据库的性能。
用户管理
需要sys用户的管理员权限进行用户权限的操作和更改，
用户权限：
创建用户：
CREATE USER user1 IDENTIFIED BY mypasswd;
为用户授权：
GRANT CREATE SESSION TO user1; --有创建session权限才可以让用户登陆
GRANT CREATE TABLE TO user1; --为用户赋予创建表的权限
但是在Oracle正常操作需要一堆权限，通过以上方式一个一个权限分配会很麻烦，所以Oracle提供了两个角色：CONNECT RESOURCE
GRANT CONNECT,RESOURCE TO user1;
但是拥有这两个角色的用户都拥有什么权限呢？可以使用如下语句查看一下：
SELECT * FROM dba_sys_privs WHERE grantee IN ('RESOURCE', 'CONNECT') ORDER BY 1;
正常使用用户可以给这几个权限：
GRANT CREATE SESSION TO test;
GRANT create any TABLE to test;
GRANT CONNECT,RESOURCE TO test;
GRANT DROP TABLE TO test;修改用户密码：
ALTER USER user1 IDENTIFIED BY password;
用户密码过期，更改：
ALTER USER user1 password EXPIRE;
锁定和解锁用户：
ALTER USER user1 ACCOUNT LOCK;
ALTER USER user1 ACCOUNT UNLOCK;
对象权限
给用户访问其他用户对象的权限：
GRANT SELECT,INSERT ON scott.emp TO user1;
回收权限和角色：
REVOKE CONNECT,RESOURCE FROM user1;
REVOKE CREATE SESSION,CREATE TABLE FROM user1;
删除用户：
DROP USER user1 CASCADE;
数据库的备份
数据库冷备份：指的是归档备份，即数据库停止，专门去备份。
备份内容：
控制文件、重做日志文件
数据库范式
范式（Normal Form）理论，英国人E.F.Codd（关系数据库之父）在上个世纪70年代提出，是关系数据库理论的基础，也是我们在设计数据库结构过程中所要遵循的规则和指导方法。
定义：第一范式（1NF）是指在关系模型中，所有的属性都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。即实体中的某个属性有多个值时，必须拆分为不同的属性。
通俗理解：第一范式就是属性不可分割，每个字段都应该是不可再拆分的。也就是，数据表中每一个字段都不可再分。单表
定义：第二范式(Second Normal Form,2nd NF)是指每个表必须有且仅有一个数据元素为主关键字(Primary key),其他数据元素与主关键字一一对应。通常称这种关系为函数依赖(Functional dependence)关系，即表中其他数据元素都依赖于主关键字,或称该数据元素惟一地被主关键字所标识。
关键点：函数和函数依赖
通俗理解：第二范式就是要求表中要有主键，表中其他其他字段都依赖于主键，即主键约束。
比如表中字段是单价、数量和总价，而总价是可以通过单价乘以数量这个函数计算出来的，所以表中不应该有总价这个字段。
第二范式就是多对多的关系。
第三范式
一对多的关系，实际使用中第三范式使用是最多的。
注意：
在实际表设计中，基本所有的范式都会被打破，所以秉着根据业务需要尽可能减少多表、复杂查询为前提。
树形查询
SELECT * FROM EMP START WITH empno=7369
CONNECT BY EMPNO = PRIOR MGR
执行计划就是用树形查询现实的，
SELECT LPAD(' ',LEVEL )||ENAME AS tmp ,e.* FROM EMP e START WITH empno=7839 CONNECT BY PRIOR EMPNO = MGR 
树形查询创建索引
其他
其他知识点：
1. ||用来进行输出的内容连接
2. between and 的查询只需要匹配一个条件，而>= and <= 是匹配两个条件的，所以between更优
3. not in 和null的使用，两个一起使用的话就相当于与筛选条件失败了。使用not in是为了查询部分行，而not in null就是查询所有行。若使用不会有任何结果返回
4.删除操作有：1）逻辑删除 2）物理删除 一般会选用flag设置为逻辑删除
5.Oracle的SQL执行后都会返回更新行数，如果没有更新，则返回0
6.order by 可以接数字，order by 1就是按照所选的第一列排序
SELECT ENAME ,JOB,ABS(SAL) FROM SCOTT.EMP ORDER BY 3;
这里的数字是select后面的对应列，所以用了窗口函数后，order by的数字有时候和字段列不是一致的。
同时也可用case when语句
select * from emp order by case when deptno=30 then 0 else deptno end
7.select *,rownum from emp ; 是报错的，使用以下就不会报错：
select e.*,rownum from emp e;8. <> != not in 都是不等于，建议用 not in
9.varchar2最多可以存4000个字节，当存不下时可以用clob，clob可以存无限大（用的很少）
10. null与任何数运算结果都是空值，所以运算时要注意空值，nvl(null,0)来处理null
11.比较a，b两个sql查询结果是否一致：1)ab的行数是否一样2）a minus b 3)b minus a是否都为空
12. create table table_a as select * from table_b; 表a和表b的数据和结构是一样的，但是不会复制b表的索引和约束。
46
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
