Java学习笔记07——Oracle数据库SQL开发基础

一、Oracle数据库系统搭建

（1）下载安装：将下载的两个压缩文件解压到同一文件夹下，仅安装数据库软件，单实例数据库安装，企业版，Oracle基目录/主目录（软件位置）。
（2）数据库创建：Database Configuration Assistant，创建数据库，一般用途或事务处理/数据仓库，全局数据库名/SID，不选配置Enterprise Manager，system(数据库操作员)与sys（数据库所有者）口令，文件系统存储，示例方案，创建数据库后解锁并设置Scott和hr的口令。
（3）配置监听器：Net Configuration Assistant，监听程序配置，添加，监听器名称，协议，端口号。监听器服务启动允许外部程序连接数据库，数据库实例服务启动数据库才可以被使用。
（4）数据库常用工具：
管理工具：Oracle Universal Installer，Database Configuration Assistant，Net Configuration Assistant（配置监听器、命名方法、本地网络、目录使用）。
开发工具：SQL Plus(基于命令行)，SQL Developer(图形化)，PL/SQL Developer(第三方程序，只有32位，企业开发常用)，TOAD（第三方程序，企业开发常用）。

二、SQL语句

SQL分类：
数据查询语言DQL：select；
数据操作语言DML操作行数据：insert，delete，update，merge（合并行）；
数据定义语言DDL操作数据库对象（表、视图）：create，drop，alter，truncate（截断表），rename，comment（为表和列添加注释）；
数据控制语言DCL：grant（允许），revoke（撤销），控制对数据和数据库对象的访问；
事务控制语言TCL：commit，rollback，savepoint。

三、SQL数据查询

1.SELECT基本查询

SELECT *|(DISTINCT)列名|表达式((AS) 列别名) FROM 表名 (WHERE 条件) （GROUP BY 列） （HAVING 分组条件）
 (ORDER BY 列名|列别名|表达式|序号 (ASC|DESC),(其他列的排序方式));
即 SELECT 列限定 FROM 表名 行限定 对查询结果按指定列和条件分组 对查询结果排序。
执行顺序：FROM,WHERE,GROUP BY,HAVING,SELECT,ORDER BY.
a.含有null的算术表达式，计算结果也为null。
b.比较运算符：＞,＞=,＜,＜=,=,＜＞,!=,^=(后三个均为不等于)；特殊运算符：(NOT)BETWEEN...AND|LIKE|IN,IS (NOT) NULL；多个条件使用NOT，AND，OR连接。
c.字符与日期值需要使用单引号定界，字符值大小写敏感，日期的值是格式敏感的。

2.单行函数：接受一个数据，输出一个数据。主要分为以下几类：

字符函数：LOWER,UPPER,INITCAP(大小写相关)，TRIM,LTRIM,RTRIM,LPAD,RPAD(补齐至指定位),INSTR,SUBSTR,LENGTH,CONCAT,REPLACE。
*不查询表求表达式值时：SELECT 表达式 FROM doal;
数值函数：四舍五入ROUND，去尾TRUNC，求余数MOD。
日期函数：SYSDATE，MONTHS_BETWEEN，ADD_MONTH，LAST_DAY，EXTRACT(从日期中获取某一部分值)，可直接做数值运算。
转换函数：TO_CHAR（将日期或数值转换为指定格式的字符），TO_DATE（将指定格式的字符转换为日期）,TO_NUMBER（将指定格式字符转换为数值）。
空值转换函数：NVL(如果值为空，用...代替)，NVL2(如果为空，用...代替，不为空用...代替)；NULLIF(如果表达式值相同为空)。
条件分支函数：CASE...WHEN 条件 THEN...ELSE...END(表达式)，DECODE（函数，根据表达式不同执行不同的操作）。

3.多行函数即聚合函数：

MAX,MIN：可以应用在数值、字符和日期类型数据。
SUM,AVG：只能用于数值类型数据。
COUNT：统计表达式不为空的行数。
GROPU BY：根据指定的列进行分组统计，HAVING：限定显示分组后的数据。此时在SELECT子句中出现的非分组列，必须出现在GROUP BY子句中。

4.表连接：从多表中查询数据，需要基于某个条件将表连接起来。

（1）相等连接/不等连接：基于两张表的某一个或某些列进行等值或不等值连接。
FROM 表1 NATURAL JOIN 表2：基于两张表的所有同名列进行等值连接；
FROM 表1 JOIN 表2 USING(列名)：基于两张表的指定同名列进行等值连接；
FROM 表1 JOIN 表2 ON 表1.列名 比较运算符 表2.列名：对两张表的任意列进行等值连接或不等值连接。
（2）自连接：将一张表作为两张表看待进行连接。
FROM 表 表别名1 JOIN 表 表别名2 ON 表别名1.列名1=表别名2.列名2;
（3）外连接：使用空行匹配（左侧表|右侧表|两个表）没有匹配行的连接。
FROM 表1 [[LEFT|RIGHT|FULL] OUTER] JOIN 表2 ON 表1.列名=表2.列名;
（4）连接多张表：FROM 表1 JOIN 表2 ON 表1.列=表2.列 JOIN 表3 ON 表1.列=表3.列 ...;

5.子查询：查询中嵌套查询，分为单行子查询，多行子查询，多列子查询，非相关子查询，相关子查询。

（1）单行子查询：子查询结果只有一种值。
SELECT ... FROM ... WHERE 列名 比较操作符 （子查询）;
比较操作符：=,＜,＞,＜=,＞=,＜＞。
（2）多行子查询：子查询结果为有多种值。
SELECT ... FROM ... WHERE 列名 比较操作符 （子查询）;
比较操作符：ANY,ALL,IN。
（3）相关子查询：子查询中需要使用主查询中行的查询，子查询与主查询相关。
执行流程：从主查询中获取一行，利用主查询中获取行执行子查询，执行主查询。
普通相关子查询：SELECT ... FROM 表 表别名 WHERE 列 比较运算符 （SELECT ... FROM 表 WHERE 列名=表别名.列名）;
使用EXISTS进行相关子查询：在子查询中自判断存在性的查询。
SELECT ... FROM 表 表别名 WHERE [NOT] EXISTS (SELECT ... FROM 表 WHERE 列名=表别名.列名)

四、Oracle数据操作

1.简单DML操作

DML：包括插入、删除、修改操作表中数据。
INSERT INTO 表[(列列表)] VALUES (值列表)：只能向表中插入一行，值列表必须同列列表匹配，没有在列列表中出现的列值为该列的默认值，不指定列列表时需要将值列表写全。
INSERT INTO 表[(列列表)] 子查询：可以向表中插入其他表的多行数据，值列表必须同列列表匹配，没有在列列表中出现的列值为该列的默认值。
DELETE FROM 表名 [WHERE 条件]：删除所有行或满足条件的行。
UPDATE 表名 SET 列1=值1，列2=值2，... [WHERE 条件]：更新表中所有行或符合条件的行。

2.复杂DML操作

无条件多表插入：INSERT ALL INTO 表1 [(表1列列表)] VALUES (查询列列表) INTO 表2 [(表2列列表)] VALUES (查询列列表)... SELECT 列列表 FROM 表0;
有条件多表插入：INSERT [FIRST|ALL] WHEN 条件1 THEN INTO 表1 [(表1列列表)] VALUES (查询列列表) WHEN 条件2 THEN INTO 表2 [(表2列列表)] VALUES (查询列列表)... SELECT 列列表 FROM 表0：FIRST将行插入到第一个满足条件的表中，而ALL会将行插入到所有满足条件的表中。
更新中使用子查询：UPDATE 表 SET 列1=（子查询）,... WHERE 列 比较运算符 （子查询），可以使用普通子查询与相关子查询。
删除中使用子查询：DELETE FROM 表 WHERE 列 比较运算符 (子查询)，可以使用普通子查询与相关子查询。

五、Oracle事务控制

1.事务：由一组DML组成的逻辑工作单元，包含一组DML或者一个DCL或者一个DDL，具有原子性、一致性、独立性、持久性（数据一旦提交或回滚后数据永远被修改），有提交和回滚两种状态。
2.事务提交：发布COMMIT、正常退出系统环境、遇到DCL或者DDL，提交对数据的修改。
事务回滚：发布ROLLBACK、非正常退出系统环境，取消对数据的修改。
SAVEPOINT name：事务执行过程中定义可回滚的保存点。
ROLLBACK TO [SAVEPOINT] name：回滚到已定义的保存点。