Java数据库编程

第一章 关系型数据库设计

1.数据库设计的重要性

1. 失败的数据库设计造成的后果

• 在程序运行阶段就会出现数据操作异常、修改复杂、数据冗余等问题
• 影响程序的性能，甚至会导致程序崩溃

2. 优秀的数据库设计带来的好处

• 降低应用程序的开发难度
• 具备高效的查询效率
• 具备良好的扩展性

2.三大范式

在设计数据库时，有一些专门的规则，它们被称为数据库的设计范式

1. 第一范式

第一范式强调的是表中列的原子性，也就是说任意一列不能够再拆分成其他几列

2. 第二范式

第二范式是建立在第一范式的基础上的，第二范式要求表必须有一个主键，并且非主键列必须完全依赖主键，而不能只依赖主键的一部分

3. 第三范式

第三范式在第二范式的基础上更进一步，第三范式要求每列都和主键直接依赖，而不是间接依赖

第二章 MySQL初体验

1.数据类型

1. 数值类型

tinyint 1字节 smallint 2字节 mediumint 3字节 int 4字节 float 4字节 double 8字节 decimal M+2字节

2. 字符串类型

char varchar tinytext text

3. 日期类型

date datetime time timestamp year

2.字段的约束和属性

• primary key(pk) 主键约束
• foreign key(fk) 外键约束
• auto_increment 自动增长
• not null 非空约束
• default 默认约束
• unique key(uk) 唯一约束

3.存储引擎

MySQL 5.7版本默认的存储引擎是InnoDB，可以通过以下语句查看当前默认的存储引擎

• show variables like ‘default_engine%’;

如需修改默认存储引擎，可以通过配置向导或修改配置文件my.ini实现

数据表默认使用当前MySQL默认的存储引擎，可以根据实际的使用场景在创建数据表时设置表的存储引擎类型，语法如下

create table 表名(

#省略代码

)engine=存储引擎;

第三章 SQL高级(一)

1.DDL 数据定义语言

• 创建数据库

create database 数据库名

• 查看有哪些数据库

show database;

• 选择数据库

use 数据库名

• 删除数据库

drop database 数据库名

• 创建数据表

create table if not exists 表名(

• 字段名 数据类型 [ 字段属性 | 约束 ] [ 索引 ] [ comment 备注],

…..

);

• 查看表

1.查看所有表 show tables; 2.查看某张表的表结构 describe 表名/desc 表名

• 删除表

drop table if exists 表名

• 修改表名

alter table 旧表名 rename to 新表名

• 修改表的字段

alter table 表名 change 原字段名 新字段名 数据类型 [属性]

• 给表添加字段

alter table 表名 add 字段名 数据类型[属性]

• 删除表的字段

alter table 表名 drop 字段名

2.DQL 数据查询语言

• select *(或字段名列表) from 表名 where 条件 group by 分组的字段名 having 条件

order by 排序的字段名 asc或desc limit 位置偏移量,行数

3.DML 数据操纵语言

• 插入单行数据

insert into 表名 (字段名列表) values (值列表)

• 插入多行数据

insert into 表名 (字段名列表) values (值列表1),(值列表2)……;

• 将查询结果插入新表

1.新表必须自己先创建好 insert into 新表(字段1,字段2,…) select 字段1,字段2,…… from 原表

2.无需自己创建新表,会自动创建 create 新表(select 字段1,字段2,… from 原表)

• 更新数据

update 表名 set 字段1=值1,字段2=值2,…… where 条件

• 删除数据

delete from 表名 where 条件

直接删除所有行:truncate table 表名

4.TCL 事务控制语言

• 保存已完成的内容

commit

• 在事务中设置保存点，方便以后回滚该位置

savepoint

• 回滚

rollback

• 改变事务选项

set transaction

第四章 SQL高级(二)

1.IN子查询

在使用比较运算符时，子查询的返回结果不能是多条记录，可以使用IN解决此问题

2.NOT IN 子查询

与IN子查询相反

3.EXISTS/NOT EXISTS 子查询

EXISTS子查询的作用是检测是否存在符合查询条件的记录，然后根据业务逻辑查询符合要求的相应记录

第五章 存储过程

1.MySQL存储过程的优缺点

• 减少网络流量
• 提升执行速度
• 减少数据库连接次数
• 安全性高
• 高复用性
• 可移植性差

2.创建和调用存储过程

1. 创建存储过程

CREATE PROCEDURE 过程名( [过程参数[,…… ] ] )

[特性]

存储过程体

2. 声明语句分隔符

在MySQL中，默认使用 ；作为分隔符，使用DELIMITER关键字可以改变分隔符，在创建存储过程前首先声明分隔符。将分隔符设置为$$或//

DELIMITER $$或//

3. 定义存储过程的参数

IN：输入参数，在存储过程中可以使用该参数，但他不能被返回

OUT：输出参数，该参数可以在存储过程中发生改变并可以返回

INOUT：输入输出参数，该参数可以被改变和返回

4. 过程体的标识

在定义存储过程的过程体时，需要标识开始和结束

BEGIN …..END 分隔符

5. 调用存储过程

CALL 存储过程名(参数列表)

6. 存储过程中的变量

DECLARE 变量名 数据类型 DEFAULT 值

7. 设置用户权限

CREATE

[DEFINER={ user | CURRENT_USER} ] #定义 DEFINER

PROCEDURE 存储过程名

[SQL SECURITY {DEFINER | INVOKER } |…… ] #特性

BEGIN

….

END

8. 查看存储过程的状态

SHOW PROCEDURE STATUS

9. 查看存储过程的创建代码

SHOW CREATE PROCEDURE 存储过程名

10. 修改存储过程

ALTER PROCEDURE 存储过程名[特性….]

11. 删除存储过程

DROP PROCEDURE 存储过程名

3.存储过程的控制语句

1. CASE条件语句

在MySQL中的CASE条件语句有两种写法

CASE WHEN 条件 THEN 语句列表 ….. END CASE; CASE 列名 WHEN 条件 THEN 语句列表 ….. END CASE;

2. 循环语句

在MySQL中有WHILE循环语句，LOOP循环语句，REPEAT循环语句

#WHILE循环 [label:] WHILE 条件 DO 语句列表 END WHILE [label] #LOOP循环 [label:] LOOP 语句列表 END LOOP [label] #REPEAT循环 [label] REPEAT 语句列表 UNTIL 条件 END REPEAT [label]

3. 条件语句

IF-ELSEIF-ELSE条件语句是最常用的一种条件语句

IF 条件 THEN 语句列表 [ELSEIF 条件 THEN 语句列表] [ELSE 语句列表] END IF;

第六章 MySQL数据库企业级开发技术

1.事务

1. 事务指将一系列数据操作捆绑成为一个整体并进行统一管理的机制
2. 事务四大特性 简称ACID

• 原子性

事务是一个完整的操作，事务的各元素是不可分的

• 一致性

当事务完成时，数据必须处于一致状态

• 隔离性

对数据进行修改的所有并发事务是彼此隔离的，这表明事务必须是独立的，它不应以任何方式依赖或影响其他事务

• 持久性

事务的持久性指不管系统是否发生故障，事务处理的结果都是永久的

3. 执行事务的SQL语句

#开启事务 BEGIN;或START TRANSACTIOW; #一组作为事务运行的SQL语句 #提交事务 COMMIT; #开启事务 BEGIN;或START TRANSACTIOW; #一组作为事务运行的SQL语句 #回滚事务 ROLLBACK;

4. 设置自动提交关闭或开启

SET autocommit =0|1；

5. 使用事务的原则

• 事务尽可能简短
• 事务中访问的数据量应尽量少
• 查询数据时尽量不要使用事务
• 在处理事务的过程中，尽量不要出现等待用户输入的操作

2.索引

1. 提高数据库的查询效率
2. MySQL中常用的索引

• 普通索引 INDEX

基本索引类型，对数据列没有任何限制

• 唯一索引 UNIQUE

不允许两行具有相同的索引值

• 主键索引 PRIMARY KEY

一种特殊的唯一索引，不允许有空值

• 全文索引 FULLTEXT

在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值

3. 创建索引

CREATE [UNIOUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column_name[length]….);

4. 查看索引

SHOW INDEX FROM table_name;

5. 删除索引

DROP INDEX index_name ON table_name

3.视图

1. 可以将视图看作一张虚拟表，通常由一张或多张表的行或列的子集组成
2. 视图常用来进行以下操作

• 筛选表中的行
• 防止未经允许可的用户访问敏感数据
• 将多张物理数据表抽象为一张逻辑数据表

3. 使用视图的好处

• 限制数据检索更容易
• 维护应用程序更方便
• 结果更容易理解
• 获得数据更容易

4. 创建视图

CREATE VIEW 视图名 AS <SELECT语句>

5. 查询视图中的数据

SELECT * FROM v_prescription

6. 删除视图

DROP VIEW [IF EXISTS] 视图名

7. 使用视图的注意事项

• 每个视图中可以使用多张表
• 与查询相似，一个视图可以嵌套另一个视图，但尽量不要超过3层
• 对视图数据进行添加，更新和删除操作实际上是直接操作引用表中的数据

4.数据库备份

1. 使用mysqldump备份数据库

mysqldump -u -p 数据库名字 > 地址

2. 使用MySQL命令导入数据

mysql -u -p 数据库名字 < 地址

第八章 使用JDBC连接数据库

1.加载驱动 Class.forName(“”); 2.建立连接 Connection conn = DriverManager.getConnection(“url”,”用户名”,”密码”); 3.编写sql String sql =””; 4.创建Statement对象,执行sql语句,得到结果 PreparedStatement pstmt = conn.preparedStatement(sql); 赋值 pstmt.setXXX(下标从1开始,值); ResultSet rs = stmt.executeQuery(); 5.处理结果 while(rs.next()){ rs.getXXX(); } 6.释放资源

1. Connection接口的常用方法

• Statement createStatement() 创建一个Statement对象并将SQL语句发送到数据库
• PreparedStatement(String sql) 创建一个PreparedStatement对象并将参数化的SQL语句发送到数据库
• boolean isClosed() 查询此Connection对象是否已经被关闭。若已经被关闭，则返回true，否则返回false
• void close() 立即释放此Connection对象的数据库和JDBC资源

2. Statement接口的常用方法

• ResultSet executeQuery(String sql) 可以执行SQL查询并获取ResultSet对象
• int executeUpdate(String sql) 可以执行插入，删除，更新等操作，返回受影响的行数
• boolean execute(String sql) 可以执行任意SQL语句，若结果为ResultSet对象，则返回true，若结果为受影响的行数或不存在任何结果则返回false

3. PreparedStatement接口的常用方法

• boolean execute() boolean execute(String sql) 可以执行任意SQL语句，若结果为ResultSet对象，则返回true，若结果为受影响的行数或不存在任何结果则返回false
• ResultSet executeQuery() 执行SQL查询，并返回该查询生成的ResultSet对象
• int execultUpdate() 执行SQL语句，该语句必须是一个DML语句或是无返回内容的SQL语句和DDL语句，返回受影响的行数
• void setXxx (int index,xxx x)
• void sexObject(int index,Object x)

4. JDBC由一组使用Java语言编写的类和接口组成，可以为多种关系型数据库提供统一的访问方式
5. JDBC API是一套接口规范
6. Connection接口负责连接数据库并但仍传送数据的任务
7. Statement接口负责执行SQL语句
8. ResultSet接口负责保存和处理Statement接口执行后所产生的查询结果
9. SQL注入攻击是通过提交一段SQL代码，执行超出用户权限的数据库操作的攻击行为
10. PreparedStatement接口能够提高代码的可读性，可维护性，SQL语句的执行性能和安全性

第九章 数据访问和DAO模式

1. Properties常用方法

• String getProperty(Strting key) 用指定的键在此属性列表中搜索属性，通过参数key得到其所对应的值
• Object setProperty(String key,String value) 通过调用基类Hashtable的put()方法设置键-值对
• void load(InputStream inStream) 从输入流中读取属性列表(键和元素对)，通过对指定文件进行装载获取该文件中的所有键-值对
• void clear() 清除所装载的键-值对，该方法由基类Hashtable提供

2. DAO模式

• 隔离了数据访问代码和业务逻辑代码
• 隔离了不同数据库的实现

典型的DAO模式主要由一下几部分组成

• 实体类：用于存放与传输对象数据
• 数据库操作工具类：即BaseDao类，避免了数据库连接和关闭代码的重复使用，方便进行修改
• DAO接口：把对数据库的所有操作定义成抽象方法，可以提供多种实现
• DAO实现类：针对不同数据库给出DAO接口定义方法的具体实现