难说再见，请拥抱我。oracle完结篇

开篇寄语

诉苦亦无用，许多事只得靠自己。

不要因为晚上没有月光，而认为白天不会来；不要因为自己伤心、失落，就认为是天意弄人。学会安慰自己，生活就是磨练，风雨过会还会看到彩虹的。

视图、索引、序列

10.1 视图

视图可以看作一张虚拟表。视图正如其名字的含义一样，是另一种查看数据的入口。

常规视图本身并不存储实际的数据，而仅仅是由SELECT语句组成的查询定义的虚拟表 。

从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，而且视图还可以被嵌套，一个视图中可以嵌套另一个视图。

在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。

视图是建立在表、结果集或视图上的虚拟表，视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。视图提供了一个统一访问数据的接口。（即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限）。从而加强了安全性，使用户只能看到视图所显示的数据。

10.1.1 视图的特点

视图中的数据并不属于视图本身，而是属于基本的表，对视图可以像表一样进行insert,update,delete操作。
视图不能被修改，表修改或者删除后应该删除视图再重建。
视图的数量没有限制，但是命名不能和视图以及表重复，具有唯一性。
视图可以被嵌套，一个视图中可以嵌套另一个视图

10.1.2 视图的功能

1.简化用户操作
2.能以不同的角度观察同一个数据库
3.对重构数据库提供了逻辑独立性：
利用视图将需要的数据合并或者筛选，但是不影响原表的数据和结构
4.对机密数据提供安全保护：
可以建立不同的视图对用不同的用户，以达到安全的目的。

10.1.3 创建视图

注意需要有权限
create or replace view 视图名 as select语句 [with read only]

10.1.4 视图使用

求每个部门中薪水低于平均薪水的员工姓名和薪水
求部门经理人中平均薪水最低的部门名称

10.2 索引

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了数据库系统的性能，加快数据的查询速度与减少系统的响应时间

索引（index）被创建于已有的表中，它可使对行的定位更快速更有效。可以在表格的一个或者多个列上创建索引，每个索引都会被起个名字。用户无法看到索引，它们只能被用来加速查询。

更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常用于搜索的列上面创建索引。

下面举两个简单的例子：
图书馆的例子：一个图书馆那么多书，怎么管理呢？建立一个字母开头的目录，例如：a开头的书，在第一排，b开头的在第二排，这样在找什么书就好说了，这个就是一个索引（聚集索引），可是很多人借书找某某作者的，不知道书名怎么办？图书管理员在写一个目录，某某作者的书分别在第几排，第几排，这就是一个索引（非聚集索引）。

字典的例子：字典前面的目录，可以按照拼音和部首去查询，我们想查询一个字，只需要根据拼音或者部首去查询，就可以快速的定位到这个汉字了，这个就是索引的好处。

10.2.1 oracle索引

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；
4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引。
5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
6）oracle创建主键时会自动在该列上创建索引

1.若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍
2.若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
3.创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

唯一性较好字段适合建立索引，大数据量才有效果

10.2.2 索引创建和删除

create index indexName on tableName (colName...) drop index indexName
查看索引
select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

10.3 序列

序列(SEQUENCE)是序列号生成器，可以为表中的行自动生成序列号，产生一组等间隔的数值(类型为数字)。不占用磁盘空间，占用内存。
其主要用途是生成表的主键值，可以在插入语句中引用，也可以通过查询检查当前值，或使序列增至下一个值。

10.3.1 序列的使用

创建序列需要CREATE SEQUENCE系统权限。序列的创建语法如下
create sequence 序列名 start with 起始值 increment by 步进;
currval : 当前值
nextval：下一个值
drop sequence 序列名；
•INCREMENT BY:序列变化的步进，负值表示递减。(默认1)START WITH:序列的初始值 。(默认1)MAXvalue:序列可生成的最大值。(默认不限制最大值，NOMAXVALUE)MINVALUE:序列可生成的最小值。(默认不限制最小值，NOMINVALUE)CYCLE:用于定义当序列产生的值达到限制值后是否循环(NOCYCLE:不循环，CYCLE:循环)。CACHE:表示缓存序列的个数，数据库异常终止可能会导致序列中断不连续的情况，默认值为20，如果不使用缓存可设置NOCACHE`

•例  CREATE SEQUENCE SEQ_DEMO INCREMENT BY 1 START WITH 1 NOMAXvalue NOCYCLE NOCACHE;
currval 表示序列的当前值，新序列必须使用一次nextval 才能获取到值，否则会报错

第十一章 JDBC

11.1 JDBC 介绍

JDBC（Java DataBase Connectivity,java数据库连接）是一种用于执行SQL语句的Java API，可以为多种关系数据库提供统一访问，它由一组用Java语言编写的类和接口组成。JDBC提供了一种基准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序，同时，JDBC也是个商标名。

我们安装好数据库之后，我们的应用程序也是不能直接使用数据库的，必须要通过相应的数据库驱动程序，通过驱动程序去和数据库打交道。其实也就是数据库厂商的JDBC接口实现，即对Connection等接口的实现类的jar文件。

11.2 JDBC使用

11.2.1 常用接口

1）Driver接口

Driver接口由数据库厂家提供，作为java开发人员，只需要使用Driver接口就可以了。在编程中要连接数据库，必须先装载特定厂商的数据库驱动程序，不同的数据库有不同的装载方法。如：
装载MySql驱动：Class.forName("com.mysql.jdbc.Driver");
装载Oracle驱动：Class.forName("oracle.jdbc.driver.OracleDriver");

2）Connection接口

Connection与特定数据库的连接（会话），在连接上下文中执行sql语句并返回结果。DriverManager.getConnection(url, user, password)方法建立在JDBC URL中定义的数据库Connection连接上。

连接MySql数据库：Connection conn = DriverManager.getConnection("jdbc:mysql://host:port/database", "user", "password");
连接Oracle数据库：Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@host:port:database", "user", "password");
连接SqlServer数据库：Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://host:port; DatabaseName=database", "user", "password");

常用方法：
•createStatement()：创建向数据库发送sql的statement对象。
•prepareStatement(sql) ：创建向数据库发送预编译sql的PrepareSatement对象。
•prepareCall(sql)：创建执行存储过程的callableStatement对象。
•setAutoCommit(boolean autoCommit)：设置事务是否自动提交。
•commit() ：在链接上提交事务。
•rollback() ：在此链接上回滚事务。

3）Statement接口

用于执行静态SQL语句并返回它所生成结果的对象。
两种Statement类：
•Statement：由createStatement创建，用于发送简单的SQL语句（不带参数）。
•PreparedStatement ：继承自Statement接口，由preparedStatement创建，用于发送含有一个或多个参数的SQL语句。PreparedStatement对象比Statement对象的效率更高，并且可以防止SQL注入，所以我们一般都使用PreparedStatement。
常用Statement方法：
•execute(String sql):运行语句，返回是否有结果集
•executeQuery(String sql)：运行select语句，返回ResultSet结果集。
•executeUpdate(String sql)：运行insert/update/delete操作，返回更新的行数。
•addBatch(String sql) ：把多条sql语句放到一个批处理中。
•executeBatch()：向数据库发送一批sql语句执行。

4）ResultSet接口

ResultSet提供检索不同类型字段的方法，常用的有：
•getString(int index)、getString(String columnName)：获得在数据库里是varchar、char等类型的数据对象。
•getFloat(int index)、getFloat(String columnName)：获得在数据库里是Float类型的数据对象。
•getDate(int index)、getDate(String columnName)：获得在数据库里是Date类型的数据。
•getBoolean(int index)、getBoolean(String columnName)：获得在数据库里是Boolean类型的数据。
•getObject(int index)、getObject(String columnName)：获取在数据库里任意类型的数据。

ResultSet还提供了对结果集进行滚动的方法：
•next()：移动到下一行
•Previous()：移动到前一行
•absolute(int row)：移动到指定行
•beforeFirst()：移动resultSet的最前面。
•afterLast() ：移动到resultSet的最后面。
使用后依次关闭对象及连接：ResultSet → Statement → Connection

11.2.2 JDBC使用步骤

加载JDBC驱动程序 → 建立数据库连接Connection → 创建执行SQL的语句Statement → 处理执行结果ResultSet → 释放资源

1）注册驱动 (只做一次)
Class.forName(“oracle.jdbc.driver.OracleDriver”); 
2）建立连接
Connection conn = DriverManager.getConnection(url, user, password); 
URL用于标识数据库的位置，通过URL地址告诉JDBC程序连接哪个数据库，URL的写法为：
3）创建执行SQL语句的statement

//Statement  
String id = "5";
String sql = "select * from tb_test where id=" +  id;
Statement st = conn.createStatement();  
st.executeQuery(sql);  
//存在sql注入的危险
//如果用户传入的id为“5 or 1=1”，那么将查询出表中的所有记录
//PreparedStatement 有效的防止sql注入(SQL语句在程序运行前已经进行了预编译,当运行时动态地把参数传给PreprareStatement时，即使参数里有敏感字符如 or '1=1'也数据库会作为一个参数一个字段的属性值来处理而不会作为一个SQL指令)
String sql = "select * from tb_text where id=?";  
PreparedStatement ps = conn.preparedStatement(sql);  
ps.setString(1, 110); //占位符从1开始
ps.executeQuery();

4）处理执行结果(ResultSet)

 ResultSet rs = ps.executeQuery();  
 While(rs.next()){  
     rs.getString(“col_name”);  
     rs.getInt(1);  
     //…
 } 

5）释放资源

//数据库连接（Connection）非常耗资源，尽量晚创建，尽量早的释放
 //都要加try catch 以防前面关闭出错，后面的就不执行了
try {
    if (rs != null) {
        rs.close();
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    try {
        if (st != null) {
            st.close();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        try {
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}