第一章 关系型数据库设计
1.数据库设计的重要性
- 失败的数据库设计造成的后果
- 在程序运行阶段就会出现数据操作异常、修改复杂、数据冗余等问题
- 影响程序的性能,甚至会导致程序崩溃
- 优秀的数据库设计带来的好处
- 降低应用程序的开发难度
- 具备高效的查询效率
- 具备良好的扩展性
2.三大范式
在设计数据库时,有一些专门的规则,它们被称为数据库的设计范式
- 第一范式
第一范式强调的是表中列的原子性,也就是说任意一列不能够再拆分成其他几列
- 第二范式
第二范式是建立在第一范式的基础上的,第二范式要求表必须有一个主键,并且非主键列必须完全依赖主键,而不能只依赖主键的一部分
- 第三范式
第三范式在第二范式的基础上更进一步,第三范式要求每列都和主键直接依赖,而不是间接依赖
第二章 MySQL初体验
1.数据类型
- 数值类型
tinyint 1字节 smallint 2字节 mediumint 3字节 int 4字节 float 4字节 double 8字节 decimal M+2字节
- 字符串类型
char varchar tinytext text
- 日期类型
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.创建和调用存储过程
- 创建存储过程
CREATE PROCEDURE 过程名( [过程参数[,…… ] ] )
[特性]
存储过程体
- 声明语句分隔符
在MySQL中,默认使用 ;作为分隔符,使用DELIMITER关键字可以改变分隔符,在创建存储过程前首先声明分隔符。将分隔符设置为$$或//
DELIMITER $$或//
- 定义存储过程的参数
IN:输入参数,在存储过程中可以使用该参数,但他不能被返回
OUT:输出参数,该参数可以在存储过程中发生改变并可以返回
INOUT:输入输出参数,该参数可以被改变和返回
- 过程体的标识
在定义存储过程的过程体时,需要标识开始和结束
BEGIN …..END 分隔符
- 调用存储过程
CALL 存储过程名(参数列表)
- 存储过程中的变量
DECLARE 变量名 数据类型 DEFAULT 值
- 设置用户权限
CREATE
[DEFINER={ user | CURRENT_USER} ] #定义 DEFINER
PROCEDURE 存储过程名
[SQL SECURITY {DEFINER | INVOKER } |…… ] #特性
BEGIN
….
END
- 查看存储过程的状态
SHOW PROCEDURE STATUS
- 查看存储过程的创建代码
SHOW CREATE PROCEDURE 存储过程名
- 修改存储过程
ALTER PROCEDURE 存储过程名[特性….]
- 删除存储过程
DROP PROCEDURE 存储过程名
3.存储过程的控制语句
- CASE条件语句
在MySQL中的CASE条件语句有两种写法
CASE WHEN 条件 THEN 语句列表 ….. END CASE; CASE 列名 WHEN 条件 THEN 语句列表 ….. END CASE;
- 循环语句
在MySQL中有WHILE循环语句,LOOP循环语句,REPEAT循环语句
#WHILE循环 [label:] WHILE 条件 DO 语句列表 END WHILE [label] #LOOP循环 [label:] LOOP 语句列表 END LOOP [label] #REPEAT循环 [label] REPEAT 语句列表 UNTIL 条件 END REPEAT [label]
- 条件语句
IF-ELSEIF-ELSE条件语句是最常用的一种条件语句
IF 条件 THEN 语句列表 [ELSEIF 条件 THEN 语句列表] [ELSE 语句列表] END IF;
第六章 MySQL数据库企业级开发技术
1.事务
- 事务指将一系列数据操作捆绑成为一个整体并进行统一管理的机制
- 事务四大特性 简称ACID
- 原子性
事务是一个完整的操作,事务的各元素是不可分的
- 一致性
当事务完成时,数据必须处于一致状态
- 隔离性
对数据进行修改的所有并发事务是彼此隔离的,这表明事务必须是独立的,它不应以任何方式依赖或影响其他事务
- 持久性
事务的持久性指不管系统是否发生故障,事务处理的结果都是永久的
- 执行事务的SQL语句
#开启事务 BEGIN;或START TRANSACTIOW; #一组作为事务运行的SQL语句 #提交事务 COMMIT; #开启事务 BEGIN;或START TRANSACTIOW; #一组作为事务运行的SQL语句 #回滚事务 ROLLBACK;
- 设置自动提交关闭或开启
SET autocommit =0|1;
- 使用事务的原则
- 事务尽可能简短
- 事务中访问的数据量应尽量少
- 查询数据时尽量不要使用事务
- 在处理事务的过程中,尽量不要出现等待用户输入的操作
2.索引
- 提高数据库的查询效率
- MySQL中常用的索引
- 普通索引 INDEX
基本索引类型,对数据列没有任何限制
- 唯一索引 UNIQUE
不允许两行具有相同的索引值
- 主键索引 PRIMARY KEY
一种特殊的唯一索引,不允许有空值
- 全文索引 FULLTEXT
在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值
- 创建索引
CREATE [UNIOUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (column_name[length]….);
- 查看索引
SHOW INDEX FROM table_name;
- 删除索引
DROP INDEX index_name ON table_name
3.视图
- 可以将视图看作一张虚拟表,通常由一张或多张表的行或列的子集组成
- 视图常用来进行以下操作
- 筛选表中的行
- 防止未经允许可的用户访问敏感数据
- 将多张物理数据表抽象为一张逻辑数据表
- 使用视图的好处
- 限制数据检索更容易
- 维护应用程序更方便
- 结果更容易理解
- 获得数据更容易
- 创建视图
CREATE VIEW 视图名 AS <SELECT语句>
- 查询视图中的数据
SELECT * FROM v_prescription
- 删除视图
DROP VIEW [IF EXISTS] 视图名
- 使用视图的注意事项
- 每个视图中可以使用多张表
- 与查询相似,一个视图可以嵌套另一个视图,但尽量不要超过3层
- 对视图数据进行添加,更新和删除操作实际上是直接操作引用表中的数据
4.数据库备份
- 使用mysqldump备份数据库
mysqldump -u -p 数据库名字 > 地址
- 使用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.释放资源
- Connection接口的常用方法
- Statement createStatement() 创建一个Statement对象并将SQL语句发送到数据库
- PreparedStatement(String sql) 创建一个PreparedStatement对象并将参数化的SQL语句发送到数据库
- boolean isClosed() 查询此Connection对象是否已经被关闭。若已经被关闭,则返回true,否则返回false
- void close() 立即释放此Connection对象的数据库和JDBC资源
- Statement接口的常用方法
- ResultSet executeQuery(String sql) 可以执行SQL查询并获取ResultSet对象
- int executeUpdate(String sql) 可以执行插入,删除,更新等操作,返回受影响的行数
- boolean execute(String sql) 可以执行任意SQL语句,若结果为ResultSet对象,则返回true,若结果为受影响的行数或不存在任何结果则返回false
- 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)
- JDBC由一组使用Java语言编写的类和接口组成,可以为多种关系型数据库提供统一的访问方式
- JDBC API是一套接口规范
- Connection接口负责连接数据库并但仍传送数据的任务
- Statement接口负责执行SQL语句
- ResultSet接口负责保存和处理Statement接口执行后所产生的查询结果
- SQL注入攻击是通过提交一段SQL代码,执行超出用户权限的数据库操作的攻击行为
- PreparedStatement接口能够提高代码的可读性,可维护性,SQL语句的执行性能和安全性
第九章 数据访问和DAO模式
- Properties常用方法
- String getProperty(Strting key) 用指定的键在此属性列表中搜索属性,通过参数key得到其所对应的值
- Object setProperty(String key,String value) 通过调用基类Hashtable的put()方法设置键-值对
- void load(InputStream inStream) 从输入流中读取属性列表(键和元素对),通过对指定文件进行装载获取该文件中的所有键-值对
- void clear() 清除所装载的键-值对,该方法由基类Hashtable提供
- DAO模式
- 隔离了数据访问代码和业务逻辑代码
- 隔离了不同数据库的实现
典型的DAO模式主要由一下几部分组成
- 实体类:用于存放与传输对象数据
- 数据库操作工具类:即BaseDao类,避免了数据库连接和关闭代码的重复使用,方便进行修改
- DAO接口:把对数据库的所有操作定义成抽象方法,可以提供多种实现
- DAO实现类:针对不同数据库给出DAO接口定义方法的具体实现
请登录后发表评论
注册