Oracle数据库复习

Oracle复习

SQL是Structured Query Language（结构化查询语言）的缩写。

SQL的最主要的两个特点是：简单和高效

一、E-R图建模要点

（一）建模过程

1.抽象出实体和联系，使用ER图表示

2.将ER图转化为多个关系模式（表结构）

3.使用范式消除冗余

（二）基本元素

实体集(Entity Set)

1.实体(Entity)指客观存在并可相互区别的事件或物体

2.实体集具有相同类型、相同性质的实体的集合

3.用矩形表示

属性(Attributes)

1.实体所具有的某一特性

2.用于实体集相连的椭圆表示

3.主标识属性加下划线表示

联系(Relationship)

1.用与相应的实体集相连的菱形表示

2.表示实体集之间的关联

3.联系可能有自己的属性

（三）E-R图转换为表的规则

1.每个实体集转化为一张表

除多值属性以外的所有属性对应着字段

实体的主标识称为主键

组合属性将其子属性转化为表中的属性

组合属性本身不转换

多值属性（如：爱好）；组合属性（如：地址）

2.多值属性和实体的主标识属性组合转化为一张表

表中的所有属性为表的主键

也可以单独设立主键

3.M:N的联系引入关系表

4.1:N的联系可不引入关系表

5.1:1的联系不必引入关系表

二、范式

1.范式是符合某一种级别的关系模式的集合

2.关系数据库中的关系必须满足一定的要求，满足不同程度要求的为不同范式

3.范式的种类：

第一范式（1NF）

第二范式（2NF）

第三范式（3NF）

BC范式（BCNF）

4.范式的目的：消除冗余

（一）冗余的坏处

1.数据冗余太大

浪费大量的存储空间

例：每一个系主任的名字重复出现

2.更新异常

数据冗余，更新数据时，维护数据完整性代价太大

例：某系更换系主任后，系统必须修改与该系统学生有关的每一个元组

3.插入异常

该插的数据插不进去

如果一个系刚成立，尚无学生，就无法把这个系及系主任的信息存入数据库

4.删除异常

不该删除的数据不得不删

如果某个系的学生全部毕业了，删除该系学生信息的同时，把这个系主任的信息也丢掉了

（二）好的关系的特点

1.不发生插入异常、删除异常、更新异常

2.数据冗余应尽可能少

3.原因：由存在于模式中的某些数据依赖引起的

4.解决方法：通过分解关系来消除其中不合适的数据依赖

（三）1NF 第一范式

表中没有多值字段

（四）2NF 第二范式

（五）3NF 第三范式

三、数据库语言

（一）SQL语言的特点

1.语言简单易学、风格统一

2.非过程性语言

3.为加强SQL的语言能力，各厂商增强了过程性语言的特征

（二）语言分类

1.DDL（创建视图、表、索引）

数据库定义语句:Date Definition Language

用来定义数据库的对象，如数据表、视图、索引等

2.DQL（增、删、改）

数据库查询语言select

3.DML

数据处理语言：Data Control Language

在数据库表中更新，增加和删除记录

如：update,insert,delete

4.DCL

数据库控制语言：Data Control Language

指用与设置用户权限和控制事务语句

如grant,revoke,if...else,while,begin tran,commit tran,rollback tran等

（三）查询语句

1.语法（S-F-W-G-H-O）

查询的结果是一个结果集

结果集也是一个关系表

SELECT目标列[,目标列...]

FROM 表名[,表名...]

[GROUP BY 列名[,列名...][ HAVING 条件表达式]]

[ORDER BY 列名[ASC|DESC][,列名[ASC|DESC]...]]

查询示例：

SELECT A_ID,A_NAME
FROM A
WHERE A_NAME LIKE 'A%';

2.Inner Join

inner join 内连接
语法格式：

SELECT * FROM A INNER JOIN B ON A.A_ID=B.A_ID
SELECT stu_name ,col_name 
FROM T_STUDENT stu INNER JOIN T_COLLEGE col
ON stu.COLLEGE = col.COL_ID ;
--以下两种方式结果相同
SELECT * FROM A INNER JOIN B ON A.A_ID=B.A_ID
SELECT * FROM A,B WHERE A.A_ID=B.A_ID

3.Group By

--group by 分组
--统计每个院系有多少人
SELECT college,count(stu_id) FROM T_STUDENT ts 
GROUP BY  college;

4.HAVING

（1）对汇总查询后的结果在此进行过滤

（2）HAVING只能跟随GROUP BY 使用

（3）注意和WHERE的区别

WHERE在汇总前进行过滤，汇总完成后使用HAVING过滤

HAVING可以对结果进行过滤，而WHERE不可以

HAVING也可以对GROUP BY 的列进行过滤，但这种情况使用WHERE的效率会更好

--统计各院系的平均分数，只统计人数在2人以上的院系。
SELECT college,COUNT(COLLEGE),ROUND(avg(SCORE),2)  
FROM T_STUDENT ts
GROUP BY COLLEGE
HAVING COUNT(COLLEGE) >2;

5.Order By

（1）对结果进行排序

（2）ASC（默认升序）

SELECT *FROM ORDER BY C_AMOUNT;

（3）DESC（降序）

SELECT *FROM ORDER BY C_AMOUNT DESC;

（4）可以同时对多列进行排序

SELECT *FROM ORDER BY A_ID,C_AMOUNT;

6.Updata

（1）语法

UPDATE 表名

SET <列名>=<表达式>，<列名>=<表达式>

[WHERE条件]

示例：

--UPDATE 语法：
--UPDATE 表名
--set <列名>=<表达式>，<列名>=<表达式>
--[WHERE条件]  如果没有where条件将会更新表中的所有记录
UPDATE T_STUDENT 
set COLLEGE = 1
WHERE COLLEGE = '大数据与软件学院';
SELECT * FROM T_STUDENT ts ;

7.Insert

INSERT 语法
INSERT
into 表名[(列名1，列名2.。)]如果不写列名，默认给所有列插入值
values(值1,值2，。。)
示例：

insert into A VALUES(9,'AAA')
insert into [(A),(B)] VALUES(9,'AAA')
insert into T_COLLEGE values (1,'大数据与软件学院');

8.Delete

语法：DELETE FROM 表名[WHERE 条件]

--删除t_college 表中为null的数据
DELETE FROM T_COLLEGE tc WHERE COL_NAME  IS NULL ;

（四）Select子句

1.目标列

列名，或者星号

常数

函数以及列名等构成的表达式

DISTINCT关键字

2.星号‘*’表示所有的列

3.可以为每一列指定别名

（五）常用函数

1.分组函数

COUNT

--统计有多少个院系 distinct去重
SELECT DISTINCT (college)FROM T_STUDENT ts ;
SELECT COUNT(DISTINCT(college))FROM T_STUDENT ts;

SUM

AVG

--求平均分数 avg
SELECT AVG(score)FROM T_STUDENT ts ;
--取平均数后两位
SELECT ROUND(AVG(score),2)FROM T_STUDENT ts ; 

MAX

--求最高分
SELECT MAX(score) FROM T_STUDENT ts ; 

MIN

--同时求平均分，最低分
SELECT MIN(score),AVG(score) FROM T_STUDENT ts ; 

2.其他函数（非SQL标准）

字符串

日期和时间

NVL

（六）From

1.Form子句

表名

视图的名字

另一个查询的结果

可以指定别名

2.From 多表查询

--from多表查询
--查询学生的姓名和院系
SELECT stu_name ,col_name 
FROM T_STUDENT ts ,T_COLLEGE tc 
WHERE COLLEGE = col_id ;

（七）操作符详解

比较运算>，<，<=，>=，=，!=，<>（比较运算）

AND,NOT,OR

BETWEEN ... AND ... 和NOT BETWEEN ... AND

IS [NOT] NULL（空值判断）

LIKE,NOT LIKE（字符串匹配）及%和_通配符

四、JDBC基础教程

（一）什么是JDBC？

一组Java API

实现在Java中访问关系化数据库

连接数据库

执行SQL语句

获取并处理SQL语句的执行结果

关注于SQL

JDBC 3.0 SQL99

JDBC 4.0 SQL2003

1.JDBC是用于执行SQL语句的Java应用程序接口，由一组用Java语言编写的类与接口组成，在Java中将使用JDBC来访问数据库。

2.JDBC本身是一个标准。没有JDBC之前，不同的数据库需要提供自己的编程接口，程序员需要使用这些特定的编程接口访问数据库，如果换一个数据库必须使用另一套编程接口访问另一种数据库。

3.JDBC是一种规范，它让各数据库厂商为Java程序员提供标准的数据库访问类和接口，这样就使得独立于DBMS的Java应用程序的开发工具和产品成为可能。

（二）JDBC的意义

1.数据库间的差异

网络协议

特殊功能

标准SQL之外的扩展

2.JDBC提供

平台无关地访问数据库

数据库位置透明

通过一组API屏蔽了底层数据库的差别，使用同一方式访问数据库

（三）JDBC与JDBC驱动

1.jdbc驱动是JDBC API 访问数据库的类的实现

2.JDBC驱动从结构上分为两类

两层结构：Client直接访问数据库

三层结构：Client通过应用服务器的连接池访问数据库

（四）JDBC编程步骤

package com.stx.level01.jdbc;

import java.sql.Connection;
import java.sql.*;
import java.sql.DriverManager;
import java.sql.SQLException;

public class Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1.装载驱动
        Class.forName("oracle.jdbc.OracleDriver");
//        2.创建连接
        Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:xe","cps_22","000000");
//        3.创建语句对象
        Statement stmt = conn.createStatement();
//        4.执行语句
        stmt.executeUpdate("insert into t_course values(3,'HTML基础')");
        stmt.executeUpdate("insert into t_course values(4,'哈哈哈')");
//        5.释放资源
        stmt.close();
        conn.close();
    }
}

1.java.sql.DriverManager

装载驱动

调用静态方法

步骤：

（1）将数据库驱动包含在CLASSPATH

（2）装载数据库驱动

Class.forName(...)

DriverManager.registerDriver(...)

（3）调用getConnention得到连接

DriverManager. getConnection 
getConnection(String url) 
getConnection(String url, Properties info) 
getConnection(String url, String user, String password) 

2.获得连接时需要的参数汇总

（1）驱动名(Driver Name)

（2）URL

​ jdbc:subprotocol:subname 格式

（3）Properties 数据库其它属性

（4）User 数据库用户

（5）password 数据库用户口令

3.执行JDBC Statement

（1）执行Statement得到结果集（对于查询）

（2）执行更新以修改数据库表的数据

（3）Statement对象方法

public ResultSet executeQuery(String sql) throws SQLException

public int executeUpdate(String sql) throws SQLException

public boolean execute(String sql) throws SQLException

举例

ResultSet rs = stmt.executeQuery(sql);
int n = stmt.executeUpdate(sql);

注意

Statement接口中常用的方法主要以execute开头。

executeQuery一般用来执行查询语句，返回值类型是ResultSet，表示查询出来的结果。

executeUpdate一般用来执行增、删、改操作，返回的结果是整数，表示受影响的记录数。

execute方法可以用于执行任何SQL语句，一般用于JDBC的框架中。

（五）提取数据

ResultSet接口中的getXXX方法

getString

getInt

getFloat

getDouble

getBoolean

getDate

getTime

getTimeStamp

两种参数形式

int 列号 - 从1开始计

String-列名，不区分大小写

将游标移动至某一行后，可调用ResultSet的getXXX方法提取数据，此类方法接受的参数可以是列名也可以是列号。

（六）ResultSet接口总结

1.由Statement执行生成的查询结果集

2.默认不能用于更新

3.包含一个游标，初始指向第一行前，通常用于循环取数据

4.默认只能向前移动游标

5.通常存在于数据库服务器端

必须保持数据库连接

依赖所执行的Statement

通过设定fetchSize来设定每次从服务器端搬多少条记录到客户端

（七）释放资源

1.Connection

public void close()
    throws SQLException

2.Statement

public void close()
    throws SQLException

3.ResultSet

public void close()
    throws SQLException

4.释放的顺序与创建的顺序相反

5.释放过程中的异常处理

6.释放时机-一般放在finally块中

7.代码封装

（八）异常处理-SQLException

可恢复异常的处理

捕获并进行处理

不可恢复异常的处理

不捕获

捕获后封装成其他类型的异常再抛出