本人浅读《数据库系统概念》前11章，品读经典，大有裨益，全篇结合了《mysql必知必会》一书，将所有sql语句改为mysql语法。由于本书的12-16章太过晦涩，参考了中国人民大学的数据库系统概论课程，根据该课程整理笔记，与君分享。本人才疏学浅，且经验不足，许多知识仅仅停留在理论阶段，欢迎指正和补充，不胜感激。
引言
设计数据库系统的目的是为了管理大量信息，对数据的管理既涉及信息存储结构的定义，又涉及信息操作机制的提供。此外数据库系统必须提供所存储信息的安全性保证，即使在系统崩溃或有人企图越权访问时也应保障信息的安全性。如果数据将被多用户共享，那么系统必须设法避免可能产生的异常结果。
数据视图
数据库系统是一些相互关联的数据以及一组使得用户可以访问和修改这些数据的程序的集合。数据库系统的一个主要目的是给用户提供数据的抽象视图，也就是说，系统隐藏关于数据存储和维护的某些细节。
数据抽象
- 物理层。物理层详细描述复杂的底层数据结构
- 逻辑层。描述数据库中存储什么数据以及这些数据间存在什么关系。
- 视图层。只描述数据库的某个部分，用户不关心所有信息，只需要访问数据库的一部分，视图层的定义是为了使用户与系统的交互更简单。
在物理层，数据库系统为数据库程序设计人员屏蔽了最底层的存储细节。
在逻辑层，程序设计人员正是在这个抽象层次上使用某种程序设计语言进行工作，数据库管理员也常常在这个层次上工作。
在视图层，视图层定义了数据库的多个视图，数据库用户看到的是这些视图。
实例和模式（instance & schema）
数据库实例：特定时刻存储在数据库中的信息的集合
数据库模式：数据库的总体设计。数据库系统可以分为不同的模式。物理模式在物理层描述数据库的设计，逻辑模式在逻辑层描述数据库的设计，数据库在视图层也可以有几种模式，有时称子模式，它描述了数据库的不同视图。
数据模型（data model）
数据库结构的基础是数据模型。数据库模型是一个描述数据、数据联系、数据语义以及一致性约束的概念工具的集合。数据模型提供了一种描述物理层、逻辑层以及视图层数据库设计的方式。
- 关系模型（relational model）。关系模型用表的集合来表示数据和数据间的联系。每个表有多个列，每一列有唯一的列名。每个表包含某种特定类型的记录，每个记录类型定义了固定数目的字段。表的列对应于记录类型的属性。当今大量的数据库系统都是基于关系模型。
- 实体联系模型（E-R）。这种模型现在在构建关系模型之前使用，来更好的构建关系模型。
- 基于对象的数据模型（object-based data model）。可以看做是E-R模型增加了封装、方法和对象标识等概念后的扩展。
- 半结构化数据模型。允许先沟通能够类型的数据项含有不同属性集的数据定义，一般用于配置文件。可扩展标记语言XML广泛的用来表示半结构化数据模型。
数据库语言
SQL、中级SQL、高级SQL三章介绍SQL语言。本文将语言内容都替换成了mysql语法！
数据库系统提供数据定义语言来定义数据库模式，以及数据操纵语言来表达数据库的查询和更新。这两种语言并不是分离的，它们构成了单一数据库语言的不同部分。
数据库操纵语言（Data-Manipulation Language，DML）
数据库操纵语言使得用户可以访问或操纵那些按照某种适当的数据模型组织起来的数据。
- 对存储在数据库中想信息进行检索
- 向数据库中插入信息
- 删除信息
- 修改信息
数据定义语言
数据库模式是通过一系列定义来说明的，这些定义由数据定义语言（Data-Definition Language，DDL）来表达。
存储在数据库中的数据必须满足某些完整性约束。DDL语言提供了指定这种约束的工具。
- 域约束。每个属性都必须对应一个所有可能的取值构成的域（整数、字符、日期等）。声明一种属性属于属于某种具体的域就相当于约束它可以取的值。
- 参照完整性。一个关系中给定属性集上的取值也在另一关系某属性集的取值中出现（表现为外键）。
- 断言。数据库需要时刻满足的某一条件。域约束和参照完整性是断言的特殊形式。（CREATE ASSERTION语句）
- 授权（authorization）对于不同的用户在数据库中的不同数据值上允许不同的访问类型。最常见的是读权限、插入全险、更新权限、删除权限。可以赋予用户所有权限或者没有或者部分权限。
数据库设计
数据库设计和R-R模型章讨论设计过程和ER模型内容
设计过程
数据库设计的主要内容是数据库模式的设计。
1.初始阶段是全面刻画预期的数据库用户的数据需求，此阶段成果是制定出用户需求的规格文档
2.概念设计阶段。选择数据模型，将需求转换成数据库的概念模式。用ER模型表示概念设计。概念设计定义了数据库中表示的实体，实体的属性，实体之间的联系，以及实体上的约束。
3.逻辑设计阶段。将高层的概念模式映射到要使用的数据库系统的实现数据模型上。E-R模型映射到表上。涉及关系数据理论和规范化。可以使用3NF设计方法
4.物理设计阶段。指定数据库的物理特性，这些特性包括文件组织的形式以及内部的存储结构的选择。
E-R模型
E-R数据模型描述实体对象，以及这些对象间的联系。
实体通过属性集合来表述。
联系是几个实体之间的关联。
实体集是同一类型所有实体的集合。
同一类型所有联系的集合称作联系集。
数据库逻辑模式可以用E-R图进行图形化表示（下图示例，第一张图为国内课本定义的ER图，用navicat工具构建的时候如第二张图片，会标明主键，表示外键关系）。最常用的方法之一是采用统一建模语言（UML）。
规范化
概念设计ER模型构建完毕后，需要将其进行规范化，即ER模型到关系表的转换，目标是生成一个关系模式集合，使我们存储信息时没有不必要的冗余，同时又能很轻易的检索数据。这个过程需要用到后面的规范化理论。
数据存储和查询
后面有几章会单独讨论存储和文件结构、索引和散列、查询处理、查询优化。
存储管理器
存储管理器将各种应用程序提交的DML语句翻译为底层文件系统命令，因此，存储管理器负责数据库中的数据的存储、检索和更新。存储管理器负责与文件管理器进行交互，原始数据通过操作系统提供的文件系统存储在磁盘上。
存储管理部件包括：
- 权限及完整性管理器，它检测是否满足完整性约束，并检查视图访问数据的用户的权限。
- 事务管理器，它保证即使发生故障，数据库也保持在一致的状态，并保证并发事务的执行不发生冲突。
- 文件管理器，它管理磁盘存储空间的分配，管理用于标识磁盘上所存储信息的数据结构。
- 缓冲区管理器，它负责将数据从磁盘上存到内存中来，并决定哪些数据应被缓存在内存中。
存储管理器实现了几种数据结构，作为数据库系统物理实现的一部分：
- 数据文件，存储数据库自身
- 数据字典，存储关于数据库结构的元数据，尤其是数据库模式。
- 索引，提供对数据项的快速访问。提供了指向包含特定值的数据的指针。散列是另外一种索引方式，在某些情况下速度更快。
查询处理器
- DDL解释器，解释DDL语句并将这些定义记录在数据字典中。
- DML编译器，将查询语言中的DML语句翻译为一个执行方案，包括一系列查询执行引擎能理解的低级指令。一个查询被翻译为多种执行方案，进行查找优化，即找出代价最小的。
- 查询执行引擎，执行低级指令。
事务管理
关于事务管理更详细内容单独有事务、并发、恢复 三章内容。
-
原子性：要么做完，要么就不做。
-
一致性：如果从 A 账户转账到 B 账户，不可能因为 A 账户扣了钱，而 B 账户没有加钱吧。
-
隔离性：类似数据库进程间的互斥，比如对同一条数据同时进行删除和修改。事务隔离级别（Transaction Isolation Level），从上往下，级别越来越高，安全性越来越高。隔离性是保证一致性的重要手段。
- READ_UNCOMMITTED
- READ_COMMITTED（oracle默认隔离级别）
- REPEATABLE_READ（mysql默认隔离级别）
- SERIALIZABLE
- 持久性：执行insert，数据库保证有一条数据永久存放在磁盘中。
事务管理器包括并发控制管理器和恢复管理器。原子性和持久性的保证是恢复管理器的职责。发生故障，数据库必须被恢复到开始执行以前的状态。当多个事务同时更新数据库时，并发控制管理器控制并发事务之间的相互影响，保证数据库一致性和隔离性。
关系模型
术语
在关系模型的术语中，关系用来指代表，而元组用来指代行。属性指代的是表中的列。关系实例表示一个关系某时刻的元组集合。对于关系的每个属性，都存在一个允许取值的集合，称为该属性的域。
当我们谈论数据库时，我们必须区分数据库模式（数据库逻辑设计）和数据库实例（数据库快照）。类似关系和关系实例的区别。
码
- 超码：是一个或多个属性的集合，这些属性的组合可以使我们在一个关系中唯一标识一个元组。超码中可能包含无关紧要的属性，即去掉这些属性也能唯一标识。
- 候选码：去掉所有无关紧要属性后的属性，又称为最小超码。
- 主码：代表被数据库设计者选中的，主要用来在一个关系中区分不同元组的候选码。习惯上把一个关系模式的主码属性列在其他属性前面。主码属性划上下划线。
- 外码：一个关系模式r1可能在它的属性中包括另一个关系模式r2的主码，这个属性在r1上称作参照r2的外码。
SQL
SQL、中级SQL、高级SQL三章内容由于联系紧密，并于一章中。所有的语法都是MYSQL
语言简介
MYSQL、ORACLE以及SQL SERVER是基于客户机-服务器的数据库。
SQL语言有一下几个部分（前两部分是本章讨论内容，我们实践为主，不将其特意分开）：
- 数据定义语言（Data-Definition Language，DDL）：提供定义关系模式、删除关系以及修改关系模式的命令。
- 数据操纵语言（Data-Manipulation Language，DML）：提供从数据库中查询信息，以及在数据库中插入元组删除元组、修改元组的能力。
- 完整性（integrity）：SQL DDL包括定义完整性约束的命令，保存在数据库中的数据必须猫族所定义的完整性约束。破坏完整性约束的更新是不允许的。
- 视图定义（view definition）：SQL DDL包括定义视图的命令
- 事务控制（transaction control）：SQL包括定义事务的开始和结束的命令
- 嵌入式SQL和动态SQL：定义SQL语句如何嵌入到通用编程语言中
- 授权（authorization）：SQL DDL包括定义对关系和视图的访问权限的命令
mysql安装与卸载
mysql5.7下载安装包https://pan.baidu.com/s/1VKyhkilLoqmELCBm6jp62w 提取码：qqhn
mysql安装下载环境配置https://blog.csdn.net/WA_MC/article/details/106657652
navicatFormysql下载压缩包https://pan.baidu.com/s/1PX-YbGDIAZ560hspLcdgfw 提取码：12j5
mysql卸载
1.控制面板卸载（或geek）
2.c盘programFiles、programFiles 86文件夹内删除 mySQL文件夹
3.c盘隐藏文件夹programData 也有mySQL文件夹（数据库数据），删除
mysql引擎
为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。建表的时候可以指定扫描引擎（表后使用ENGINE=，如果不使用，默认引擎为MyISAM）
- InnoDB是一个可靠的事务处理引擎，mysql5.6开始支持全文本搜索
- MEMORY在功能等同MyISAM，但由于数据存储在内存中，速度很快
- MySIAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理
外键不能跨引擎。
连接、建库、等命令
控制台连接本机mySQL
mysql -u root -p
输入密码
控制台连接远程mySQL
mysql -h主机地址 -u用户名 -p
eg:mysql -h192.168.1.103 -uroot -p
输入用户密码
退出mysql
quit
建库命令
create database wollo default character set utf8;
删库
drop database wollo;
显示表信息
show tables [from database];
显示创建表语句
show create table shiro.student;
显示库
show databases;
显示创建库信息
show create database shiro;
打开库(相当于进入此库中，进行操作不再需要是哪个库的表)
use shiro
查看表结构
describe student
服务器状态
show status
显示授权用户的安全权限
show grants
数据类型
数值类型（常用）：
int（20）
float（8，1）
double（20，1）
字符类型：
char(固定长度)
varchar(动态分配长度）
TEXT：64K字符
LONGTEXT：4GB字符
MIDIUMTEXT：16K字符
TINYTEXT：255字节字符
SET：64个串组成的预定义集合的0个或多个串
ENUM：64K个串组成的预定义集合的0个或多个串
日期类型：
date：格式为yyyy-MM-dd
datetime:yyyy-MM-dd hh:mm:ss 8字节
timestamp: 自动进行时区的转换，4字节
time:时间
year:年份
二进制类型：
BLOB：最大长度为64KB
MEDIUMBLOB：最大长度16MB
LONGBLOB：最大长度4GB
TINYBLOB：最大长度255字节
创建表
下例中有主键约束和外键约束的三种方法。unique唯一约束、default约束用法也类似。mysql没有检查约束，oracle有。主键约束=unique + not null 。IF NOT EXISTS用于检查创建时是否已经存在同名表。
create table teacher(
id int(10) primary key auto_increment,
name varchar(20)
);
create table student(
id int(10) primary key auto_increment, -- 添加主键1.
name varchar(20) not null,
age int(3),
tid int(3) REFERENCES teacher(id), -- 添加外键1.
-- 添加主键2.，可以设定报错信息 constraint pk_student_id primary key(id)
-- 添加外键2. constraint fk_teacher FOREIGN key (tid) REFERENCES teacher(id)
);
-- 添加主键3. alter table student add constraint pk_student_id primary key(id)
-- 添加外键3，alter table student add constraint fk_teacher FOREIGN key(tid) REFERENCES teacher(id)
-- 删除主键约束 alter table student drop primary key
-- 删除外键约束 alter table student drop FOREIGN key fk_teacher
-- 添加自增约束 alter table student modify id int(10) auto_increment;
-- 删除表drop table student
-- 增加字段 alter table student add sphone int(11)
-- 修改字段类型 alter table student modify sphone varchar(11)
-- 修改字段名 alter table student change sphone phone varchar(11)
-- 删除字段名 alter table student drop column phone
-- 修改表名 rename table student to student2
插入
insert into teacher values(default,'老师1');
insert into teacher values(default,'老师2');
insert into student values(default,'学生1',15,1);
insert into student values(default,'学生2',26,1);
insert into student values(default,'学生3',35,1);
insert into student values(default,'学生4',33,1);
insert into student values(default,'学生5',22,2);
insert into student values(default,'学生6',34,2);
insert into student values(default,'学生7',27,2);
指定字段插入
insert into student(id,name,age,tid) values(15,'学生15',28,2);
可以插入查询结果
insert into student (select ...)
多条插入（这样的插入比两个SQL语句效率高！）
insert into student(id,name,age,tid) values
(15,'学生15',28,2),(16,'学生16',27,1);
insert操作可能很耗时，可能降低等待处理的select语句的性能，如果数据检索是最重要的，可以降低insert语句的优先级（当然这也适用于update 和 delete语句），在insert 和into之间添加LOW_PRIORITY
删除
delete from student where name='学生1'
-- 删除表所有信息
delete from student
更新
update student set name='yanyue' where id=2
更新时一行或多行出现错误，整个update操作被取消，如果希望即使发生错误，也继续完成更新，使用UPDATE IGNORE TABLE
查询
单关系查询
select * from student
有时候我们想强行删除重复，使用distinct关键词
select distinct tid from student
select 字句还可含带有+、-、*、/ 运算符的算数表达式
select distinct tid+1 from student
where字句，sql允许where字句中使用逻辑连词and or和not。
select * from student where age>30
多关系查询
分类方式1：等值连接和非等值连接
等值连接
student和teacher表进行连接，生成笛卡尔积（表1的每一行与表2的所有行配对，而不管它们逻辑上是否正确，笛卡尔积的元组个数为表1、表2元组个数的积）后用where字句进行筛选
select * from student,teacher where student.tid=teacher.id
非等值连接
select *from emp e,salgrade s where e.sal>s.losal and e.sal<=s.hisal
分类方式2：字段名必须相同的自然连接
自然连接
自然连接（natural join，natural可省略）需要字段名相同，连接结果该字段名只有一列，去重
select *from emp natural join dept
指明使用指定字段进行等值筛选，两表字段也必须同名
select *from natural join dept using(deptno)
分类方式3：内连接和外连接。我们将不保留未匹配分组的连接运算称作内连接（Inner join）。
内连接
-- inner可省略
select student.id,student.name,age,tid,teacher.name tname from student inner join teacher
on student.tid=teacher.id
这里student.id是因为两表中有同名属性，需要进行区分，teacher.name tname 是表示给该字段起一个别名，查询表的时候显示的是该别名。
外连接
外连接分为左外连接，右外连接，全外连接
左外连接=左表全部记录+相关联结果
select * from student
left outer join teacher
on student.tid=teacher.id
右外连接=右表全部记录+相关联结果
select * from student
right outer join teacher
on student.tid=teacher.id
全外连接=左表全部记录+右表全部记录+相关联结果=左外连接+右外连接-相关联结果（即去重复）
mysql中没有全外连接
可以使用union实现
SELECT * FROM emp e LEFT JOIN dept d ON e.deptno=d.deptno
UNION
SELECT * FROM emp e RIGHT JOIN dept d ON e.deptno=d.deptno;
分类方式4：自连接，和同一张表连接
自连接
在单条select语句中不止一次引用相同的表
select e1.*,e2.* from emp e1,emp e2 where e1.mgr=e2.empno
嵌套子查询
select * from student where tid in (select id from teacher)
some
> some表示至少比集合中某一个大，允许<=some，>=some，=some(等价in，等于集合中任意一个)，<>some（不等于集合中任意一个即可）
-- 至少比某一个大
select * from student s1
where age > some(select s2.age from student s2 where tid=1)
>all表示比集合中所有都大，允许<=all，>=all，=all（等于集合中所有），<>all(等价not in，不等于集合中所有）
select * from student s1
where age > all(select s2.age from student s2 where tid=2)
exists
select * from student s1 where
EXISTS(select * from teacher where s1.tid=teacher.id)
也有not exists
select * from student s1 where
NOT EXISTS(select * from teacher where s1.tid=teacher.id)
from子句中子查询
mysql中必须给个别名
select * from (select * from student where tid=1) stu
计算字段子查询
select name,(select teacher.name from teacher where student.tid=teacher.id ) '老师名字'
from student
where tid IS NOT NULL
基本运算
字符串表示
-
SQL使用单引号代表字符串。如果单引号是字符串中的组成部分，那就用两个单引号字符来表示，比如it’s写成it’'s。
-
mysql默认大小写不敏感，即 ‘a’=‘A’ 为真，可以更改成敏感的。
字段连接符
select student.name,concat('老师名字是',teacher.name) '老师信息'
from student
join teacher on student.tid=teacher.id
like通配符
在字符串上可以使用like操作符来实现模式匹配，我们使用两个特殊的字符来描述，是大小写敏感的，特殊字符即%和_用转义字符即可。‘ab%cd%’ 匹配所有以ab%cd开头的字符串。可以用not like搜寻不匹配项。
- %表示匹配任意子串
- _表示匹配任意一个字符
eg
select * from student where name like '学生%'
select * from student where name like '学__'
-- 包含特殊字符用反斜杠
select * from student where name like '%\%'
limit
limit一般用于分页
select * from student limit 1,2
表示从第1个元组开始（0开始计数）查询2个元组
REGEXP
注意：mysql仅支持少部分正则！前半部分介绍标准正则，后面单独介绍mysql正则！
常用正则 https://c.runoob.com/front-end/854/
转义字符(多数正则表达式实现使用单个反斜杠转义特殊字符，以便能使用这些字符本身。但mysql要求两个反斜杠！)
标准字符（字母大写是取反）（小数点不能匹配 \n）
自定义字符（中括号中^表示取反,挨着的表示或） 正则表达式的特殊符号在中括号中失去特殊意义，除 ^（除某某之外）和-（范围）有了另外的特殊含义。
量词
？表示0到1次，+代表至少一次，*代表任意次。^表示开头。$表示结尾。小数点匹配除\n以外任意
\d{6} 六个数字
\d\d{6} 七个数字
(\d\d){6} 十二个数字
\d{3,6} 三到六个数字 贪婪模式（8个数字，6+2）
\d{3,6}? 三到六个数字 非贪婪模式（8个数字，3+3+2）
\d{3,}至少三个数字
\b用法
gaoqi\b 右侧不能是\w
\bgaoqi 左侧不能是\w
\bgaoqi\b 两侧不能有\w
^和$定位符
^i 文本开头是i
$i 文本结尾是i
|
表示或
分组
正则表达式通过使用括号将表达式分为不同的分组，识别的方法是通过从左至右搜寻左半括号，遇到第一个左半括号时，则该左半括号与对应的右半括号所包含的内容即为第一分组，以此类推 。例如，在表达式((A)(B( C)))，有四个这样的组：((A)(B( C)))、(A)、(B( C))、( C)
分组取值：当通过正则表达式匹配到字符串时，可以使用matcher.group(i)等方式取到第i个分组所匹配到的子字符串。
（）：目标字符串需要匹配exp,并将该分组匹配的子文本保存到自动命名的组里，自动以数字从1开始分组；
(?exp)：目标字符串需要匹配exp,并将该分组匹配的子文本保存到名称为name的组里，也可以写成
后面几个方式都不会视为分组
?: 匹配pattern，包含该pattern
正向预查：
（？=pattern） 匹配后面为pattern的，但匹配结果不包含该pattern
（？!pattern） 匹配后面不为pattern的
负向预查：
（？<=pattern） 匹配前面为pattern，不包含pattern的
（？<!pattern） 匹配前面不为pattern的
练习
1.座机或手机号码
(?:0\d{2,3}-\d{7,9})|(?:1[3,5,7,8,9]\d{9})
2.邮箱
[\w,]+@[a-z0-9A-Z]+(.[a-z]{2,4})
Java中正则
正则在java程序里面所有一个斜杠变两个斜杠，自己解析一个，mysql解析一个，另外java中的特殊字符也需要用反斜杠！
//fdasfsd87987fsdfadf8789，匹配这个字符串 \w+
//正则在java程序里面一个斜杠变两个斜杠
Pattern p=Pattern.compile("\\w+");
Matcher m=p.matcher("fdasfsd87987fsdfadf8789");
System.out.println(m.matches()); //尝试将整个字符序列与该模式匹配
//结果为true
Pattern p=Pattern.compile("\\w+");
Matcher m=p.matcher("fdasfsd87987&&fsdfadf8789");
//System.out.println(m.matches());//匹配不上会报错
System.out.println(m.find());//类似指针找到之后指针下移指向下一个匹配的
System.out.println(m.group());
System.out.println(m.find());
System.out.println(m.group());
//true
//fdasfsd87987
//true
//fsdfadf8789
//正则分组
Pattern p=Pattern.compile("([a-z]+)([0-9]+)");
Matcher m=p.matcher("fdasfsd87987&&fsdfa432&&df8789");
while(m.find()){
System.out.println(m.group());
System.out.println(m.group(1));
System.out.println(m.group(2));
}
// fdasfsd87987
// fdasfsd
// 87987
// fsdfa432
// fsdfa
// 432
//replaceAll方法
Pattern p=Pattern.compile("[a-z]");
Matcher m=p.matcher("fdasfsd87987&&fsdfa432&&df8789");
String str=m.replaceAll("*");
System.out.println(str);
//*******87987&&*****432&&**8789
//split与正则
String str="a,b,c";
String []arrs=str.split("\\d+");
System.out.println(Arrays.toString(arrs));
爬取网页URL
public static void main(String[] args) throws IOException {
List<String> list;
list = getMather("https://www.163.com","utf-8");
for (String str:list) {
System.out.println(str);
}
}
public static List<String> getMather(String url,String charSet) throws IOException {
List<String>list=new ArrayList<>();
String destStr= String.valueOf(getURLContent(url,charSet));
Pattern p=Pattern.compile("href=\"(http.+?)\"");//.+表示任意个数的任意字符 ？表示0次或一次匹配 即非贪婪模式！
Matcher m=p.matcher(destStr);
while(m.find()){
list.add(m.group(1));
}
return list;
}
//获得URL对应网页源码内容
public static StringBuilder getURLContent(String urlStr,String charSet)throws IOException{
StringBuilder sb=new StringBuilder();
URL url=new URL(urlStr);
BufferedReader reader=new BufferedReader(
new InputStreamReader(url.openStream(),charSet));
String temp="";
while((temp=reader.readLine())!=null){
sb.append(temp);
}
return sb;
}
// https://static.ws.126.net/163/f2e/www/index20170701/css/head~65fdc48dac14c.css
// https://static.ws.126.net/163/f2e/commonnav2019/css/commonnav_headcss-89aa49bc1b.css
// https://www.163.com/
// https://www.163.com/#f=topnav
// https://m.163.com/newsapp/#f=topnav
// https://open.163.com/#f=topnav
// https://hongcai.163.com/?from=pcsy-button
// https://u.163.com/aosoutbdbd8
// https://mail.163.com/client/dl.html?from=mail46
mysql正则
不确定mysql拥有正则中的哪些功能，无法一一列举
-- mysql匹配不区分大小写，如果想区分，REGEXP后使用binary关键字
select * from student where name REGEXP '..1'
-- 特殊字符使用双反斜杠，斜杠本身用四个反斜杠(mysql必知必会一书中是三个，我实践的是4个)
select * from student where name REGEXP '\\\\'
字符类
order by
order by字句默认使用升序（asc表示升序，默认的不用写），可以在后面加上desc表示降序
select * from student order by age
select * from student order by age desc
排序可以在多个属性上进行，先按照tid升序，再在相同的tid中按照age降序
select * from student order by tid,age desc
between
比较运算符说明一个值是小于或等于某个值，同时大于或等于另一个值。
select * from student where age between 20 and 30
还可以使用not between
select * from student where age not between 20 and 30
NOT
mysql支持使用NOT对IN、BETWEEN和EXISTS子句取反，这与多数其他DBMS对各种条件取反有很大区别！
IN
IN操作符一般比OR操作符执行更快
select * from student where age in(26,22)
AND OR
AND优先于OR，不过实践中尽量用括号区别优先级
select * from student where age >20 and tid=1
select * from student where age >20 or tid=1
NULL
-
在通过过滤选择出过滤值可能为空的行时，在匹配过滤时或不匹配过滤时都不会返回为该值为空的行
-
另外还有一个筛选空字段的IS NULL
select * from student where tid IS NULL
- 当使用ORDER BY时，首先呈现NULL值。如果你用DESC以降序排序，NULL值最后显示。当使用GROUP BY时，所有的NULL值被认为是相等的，故只显示一行（如下测试）。
select tid from student group by tid
集合运算
并运算
union自动去除重复，如果想保留重复，使用union all代替
(select * from student where age<20)
UNION
(select * from student where age>30)
注：mysql只实现了union，没有交运算，差运算。
聚集函数
我们经常需要汇总数据而不用把它们实际检索出来，为此MYSQL提供了专门的函数，使用这些函数，MYSQL查询可用于检索数据，以便分析和报表生成。所有聚集函数都能执行多个列上的计算。
AVG
计算列平均值（忽略值为null的行）
select AVG(age) from student
count
计算符合条件行的数目（忽略null值）
select count(*) from student
对特定列中有值的行进行计数
select count(tid) from student
MAX
select MAX(age) from student
MIN
select MIN(age) from student
SUM
select SUM(age) from student
以上5个聚集函数都可以使用DISTINCT和ALL分别表示去重和不去重（默认）。这些函数是高效的，比在应用程序中计算要快得多！
分组
前面学习了聚集函数，如果需要从逻辑上分组，计算每个组的聚集函数怎么办？能按照多个列进行分组group by tid，age
select tid,count(*) from student group by tid
HAVING子句过滤分组，分组后我们希望过滤掉某些不符合条件的分组，与where用法无差，只不过where过滤行，HAVING过滤分组
select tid,count(*) from student group by tid HAVING tid IS NOT NULL
select子句顺序
SELECT FROM WHERE GROUP BY HAVING ORDER BY LIMIT
先通过select from找出表，where子句筛选，group分组，Having筛选分组，order by分组排序，limit定位分组
select * from student where tid IS NOT NULL
group by age Having age>15
order by age limit 1,2
函数
相对来说，函多数SQL语句是可移植的，在SQL实现之间有差异时，这些差异通常不那么难处理。而函数的可移植性却不强。几乎每种主要的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。
大多数SQL实现支持以下类型的函数
- 删除或填充值，转换大小写的文本函数
Left()返回串左边的字符，从1开始计数。Right()返回串右边字符
select id,LEFT(name,1) from student
Length()返回串的长度
select id,LENGTH(name) from student
Locate()找出串的一个子串，返回子串开始位置，从1开始计数
select id,LOCATE('ck',name) from student
Upper()、Lower()转换大小写
select id,UPPER(name) from student
LTrim()、RTrim()
去掉串左边空格、去掉串右边空格
SubString()
-- 从第二个位置开始截2个字符的子串
select id,SUBSTRING(name,2,2) from student
Soundex()
Soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。
--比如cindy与cindi
select * from student where SOUNDEX(name)=SOUNDEX('cindi')
- 在数值数据上进行算数操作的数值函数
- 处理日期和时间
（此处系转载https://www.cnblogs.com/zluckiy/p/10295387.html）
1.时间转字符串
DATE_FORMAT(日期，格式字符串)
SELECT DATE_FORMAT(NOW(), ‘%Y-%m-%d %H:%i:%s’);
2.字符串转时间
STR_TO_DATE(字符串，日志格式)
SELECT STR_TO_DATE(‘2019-01-20 16:01:45’, ‘%Y-%m-%d %H:%i:%s’);
3.时间转时间戳
select unix_timestamp(now());
4.字符串转时间戳
select unix_timestamp(‘2019-01-20’);
5.时间戳转字符串
select from_unixtime(1451997924,‘%Y-%d’);
附日期格式如下：
%M 月名字(January……December)
%W 星期名字(Sunday……Saturday)
%D 有英语前缀的月份的日期(1st, 2nd, 3rd, 等等。）
%Y 年, 数字, 4 位
%y 年, 数字, 2 位
%a 缩写的星期名字(Sun……Sat)
%d 月份中的天数, 数字(00……31)
%e 月份中的天数, 数字(0……31)
%m 月, 数字(01……12)
%c 月, 数字(1……12)
%b 缩写的月份名字(Jan……Dec)
%j 一年中的天数(001……366)
%H 小时(00……23)
%k 小时(0……23)
%h 小时(01……12)
%I 小时(01……12)
%l 小时(1……12)
%i 分钟, 数字(00……59)
%r 时间,12 小时(hh:mm:ss [AP]M)
%T 时间,24 小时(hh:mm:ss)
%S 秒(00……59)
%s 秒(00……59)
%p AM或PM
%w 一个星期中的天数(0=Sunday ……6=Saturday ）
%U 星期(0……52), 这里星期天是星期的第一天
%u 星期(0……52), 这里星期一是星期的第一
select date_format(NOW(),'%Y-%m-%d') '当前时间';
2005年9月的所有订单（假设每个分组有自己的日期）
可以用between and来表示日期范围
select cust_id,order_num from orders
where Data(order_date) between '2005-09-01' AND '2005-09-30'
另一种不需要知道该月有多少天的方法
select cust_id,order_num from orders
where Year(order_date)=2005 AND MONTH(order_date)=9
视图
在此之前，我们一直都在逻辑模型层操作，即操作的关系都是实际存储在数据库中的。让所有用户都看到整个逻辑模型是不合适的，处于安全考虑我们需要隐藏特定的数据。除了安全考虑，我们可能希望创建一个更符合特定用户直觉的个人化的关系集合。
、
不是逻辑模型的一部分，但作为虚关系对用户可见的关系称为视图view。视图在概念上包含查询结果中的元组，但不进行与计算和存储，存储的是与视图关系相关联的查询表达式，当视图关系被访问时，其中的元组是通过计算查询结果而被创建出来的。
create view student_teacher(id,name,age,tname)
AS
(select student.id,student.name,age,teacher.name from student,teacher
where student.tid=teacher.id)
select * from student_teacher
查看创建视图的语句
show create view student_teacher
更新视图
create or replace view
删除视图
drop view student_teacher
视图可以直接当普通关系来用即可。视图不能索引，也不能有关联的触发器或默认值。前面我们看到视图都是和select语句一起使用，因为视图主要用于数据检索，而不用于更新，且很多视图是无法更新的（比如有聚集函数的等）。
事务
sql标准规定当一条SQL语句被执行，就隐式的开启了一个事务，也可以显示声明事务开始位置。默认的MYSQL行为是自动提交所有更改，即一条sql语句一个事务。
可以通过更改默认提交行为，set autocommit=0表示不自动提交，该字段针对连接而不是整个服务器
for example1：
now autocommit=1
delete from student where name='jack'
rollback
rollback无效，因为已经自动commit了，commit之后无法回滚
for example2:
set autocommit=0
delete from student where name='jack'
rollback
rollback success，经过测试，如果不显示设置事务开始，一条语句就代表一个事务，不手动commit的话，执行下一跳sql语句会自动commit，无法回滚
for example3:
start TRANSACTION
delete from student where name='jack';
update student set name='jimmy' where name='jim';
rollback
rollback success! rollback或commit之后，事务会自动结束
for example4:
如果事务中sql有错误，到错误处就停止执行了，直接执行以下语句，在第三行停并回滚前面操作，不执行commit，如果单独执行commit，依然会提交，第二行被提交，且无法rollback。这个地方我想过既然有自动回滚为什么在做JDBC的时候为什么还需要在catch中rollback，因为不手动回滚事务不会结束，这样可能会导致锁表之类的问题
start TRANSACTION;
delete from student where name='cindy' ;
delete from student where name'charlie';
commit;
for example5：
start TRANSACTION;
savepoint delete1;
delete from student where name='cindy' ;
savepoint delete2;
delete from student where name='charlie';
rollback to delete2
rollback to delete1
索引
索引分类
通俗点说，索引是表的目录，在查找内容之前可以先在目录中查找索引位置，以此快速定位查询数据。对于索引，会保存在额外的文件中。
索引种类：
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）unique
主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
全文索引：对文本的内容进行分词，进行搜索 ，下面单独讲解，即全文本搜索
索引创建
创建普通索引语法：
create index student_name on student(name)
创建unique索引
unique约束和create unique index的效果等价
create unique index student_name on student(name)
组合索引
create index student_name_id on student(name,tid)
删除索引
drop index student_name on student
全文本搜索
MyISAM支持全文本搜索，innodb 从1.2.x 开始才增加了全文索引支持，MySQL 5.6 开始支持。
在MySQL 5.7.6之前，全文索引只支持英文全文索引，不支持中文全文索引，需要利用分词器把中文段落预处理拆分成单词，然后存入数据库。
从MySQL 5.7.6开始，MySQL内置了ngram全文解析器，用来支持中文、日文、韩文分词。经测试ngram不是默认的解析器，默认解析器如果有中文搜索不到英文，也不能搜索中文
全文本搜索开启
create table student(
id int(10) primary key auto_increment,
name varchar(20) not null,
age int(3),
tid int(3),
FULLTEXT(name) WITH PARSER ngram
)ENGINE=InnoDB
虽然语法可以在建表时这么写，但不要在导入数据前使用FULLTEXT，因为更新索引耗时。可以在建表用alter启用。
alter table student add FULLTEXT(name) WITH PARSER ngram
FULLTEXT索引指定的列，MySQL自动维护该索引，在增加、更新、删除行时候，索引自动更新。
使用全文搜索
select * from student where match(name) AGAINST('good')
可以看到为什么搜索good，doog却也匹配了？
这是因为ngram解析器分词
n=1: ‘g’, ‘o’, 'o, ‘d’
n=2: ‘go’, ‘oo’, ‘od’
n=3: ‘goo’, ‘ood’
n=4: ‘good’
查看分词n值，应该默认是2
show variables like '%token%';
所以doog中有oo，能够匹配，所以符合搜索条件，默认解析器是没有分词的。
查询扩展
比如查询good，首先进行一个基本的全文本搜索，找出与搜索条件匹配的所有行，其次mysql检查这些匹配行并选择所有有用的词，再使用这些有用的词进行搜索，第二次搜索的结果是排序在第一次之后的。
select * from student where match(name) AGAINST('good' WITH query expansion)
布尔文本搜索
搜索模式分为自然搜索和布尔搜索，自然搜索返回结果会自动按照相关性排序，相关性高的在前面。相关性的值是一个非负浮点数，0表示无相关性。在布尔方式中，不按相关性排序返回。
自然搜索中，将自然搜索作为字段可以得到每个分组相关性值。
select student.*,match(name) AGAINST('good') as rank from student
比如匹配good而不匹配student（使用默认解析器示例，ngram情况拆分太过复杂）
select * from student where
match(name) AGAINST('+good -student' IN BOOLEAN MODE)
使用说明
- 在索引全文本数据时，如果搜索三个（我mysql5.7测试的是不包括三个）以下字符的词，搜索结果为空。
- 如果返回结果大于50%，则该搜索无效，但这个规则不适用于IN BOOLEAN
- 如果表中的行数少于三行，全文搜索不返回结果
- 忽略词中的单引号，例如don’t索引为dont
授权
mysql创建一个名为root的用户账号，它对整个mysql服务器有完全的控制。可能在对非工作的数据库实验MYSQL时，用root进行登录很好，不过在日常工作中，决不能使用root，应该创建一系列的账号，有的用于管理，有的供开发人员，有的供用户。
通过命令查看用户，MYSQL用户账号和信息都存储在名为mysql的数据库中
创建一个用户
CREATE USER ‘username’@‘host’ IDENTIFIED BY ‘password’;
username – 用户名
host – 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如 果想让该用户可以从任意远程主机登陆,可以使用通配符%
password – 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登 陆服务器
CREATE USER 'wityy'@'localhost' IDENTIFIED BY '123456';
CREATE USER 'wityy'@'172.20.0.0/255.255.0.0' IDENDIFIED BY '123456';
CREATE USER 'wityy'@'%' IDENTIFIED BY '123456';
CREATE USER 'wityy'@'%' IDENTIFIED BY '';
CREATE USER 'wityy'@'%';
重新命名用户
rename user ‘oldname’to 'newname';
删除用户
DROP USER ‘username’@'host’;
改密码
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
查看用户权限
show grants for ben
USAGE表示根本没有权限。
授权包括数据上的授权和数据库模式上的授权。
-
数据的授权包括增删改查四种权限。当用户提交查询或更新时，SQL执行先基于该用户获得过的权限检查此查询或更新是否授权过，如果没有，则拒绝执行该SQL。
-
数据库模式上的授权，用户创建、修改或删除关系。拥有某些形式的权限的用户还可以把这样的权限授予给其他用户（转授权限），或者收回此前授出的权限。
权限层次：
grant 权限 on 权限层次 to 用户
- 整个服务器，授予全局权限：on *.*
- 整个数据库，on db_name.*:拥有db_name数据库下的所有表的权限
- 特定的表，使用on db_name.table
- 特定的列
grant update(age) on shiro.student to ben; - 特定的存储过程
关于权限转授GRANT OPTION，用户被授予了某个权限，那么默认情况下，该用户是不能把这个权限授予给其他人的。但是可以使用WITH GRANT OPTION这个子句来让该用户可以将权限再授予给其他人。
grant select on shiro.student to 'ben'@'%' with grant option;
JDBC
导包时都是导 java.sql
Statement & PreparedStatement
设置命令，最常用的是PreparedStatement，是Statement的子接口。PreparedStatement只是处理SQL命令的另一种形式，是和Statement并列的，能够解决Statement SQL语句注入的缺陷，并且PreparedStatement的执行效率要比Statement高，这是因为PreparedStatement的流程是先创建SQL命令对象进行预编译，然后再给占位符赋值，在执行同一条SQL语句时，PreparedStatement就只用编译一次，Statement就要编译多次，SQL注入在实际开发当中不会出现，PreparedStatement效率较高，Statement可以字符串拼接才是他们各自的优点
execute和executeUpdate的区别
相同点：二者都能够执行增加、删除、修改等操作。
不同点：
1、execute可以执行查询语句，然后通过getResult把结果取出来。executeUpdate不能执行查询语句。
2、execute返回Boolean类型，true表示执行的是查询语句，false表示执行的insert、delete、update等。executeUpdate的返回值是int，表示有多少条数据受到了影响。
public int insUser() throws ClassNotFoundException, SQLException {
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接对象
Connection conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/textjdbc","root","123456");
//创建SQL命令对象
Statement stmt=conn.createStatement();
//创建SQL语句
String sql="insert into tuser values(3,'王物','999')";
String sql2="insert into tuser values(4,'王物2','999')";
String sql3="insert into tuser values(5,'王物3','999')";
String sql4="insert into tuser values(6,'王物4','999')";
//传递SQL语句
//注：executeUpdate和executeQuery，前者更偏向于非查询语句，后者偏向查询语句，使用resultset接收。
int i = stmt.executeUpdate(sql);
i+= stmt.executeUpdate(sql2);
i+=stmt.executeUpdate(sql3);
i+=stmt.executeUpdate(sql4);
stmt.close();
conn.close();
return i;
}
public int insUser2() throws ClassNotFoundException, SQLException {
///加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接对象
Connection conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/textjdbc","root","123456");
//创建SQL命令对象
String sql="insert into tuser values(?,?,?)";
PreparedStatement ps=conn.prepareStatement(sql);
//预备语句
ps.setObject(1,7);
ps.setObject(2,"哇哈哈");
ps.setObject(3,"999");
int i= ps.executeUpdate();
ps.setObject(1,8);
ps.setObject(2,"哇哈哈");
ps.setObject(3,"999");
i+= ps.executeUpdate();
ps.close();
conn.close();
return i;
}
批量处理(适合有很多条命令需要执行的时候)（更适合用Statement）
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/textjdbc","root","123456");
conn.setAutoCommit(false);
Statement stmt=conn.createStatement();
for (int i=0;i<2000;i++){
stm.addBatch("insert into u_name(u_age,u_name)values(23,'是周周啊"+i+"')");
}
stm.executeBatch();
conn.commit();
stmt.close();
conn.close();
ResultSet
ResultSet用法（查询语句）
//加载驱动
Class.forName("com.mysql.jdbc.Driver");
//创建连接对象
Connection conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/textjdbc","root","123456");
String sql="select u_age,u_name from u_name where u_age>?";
PreparedStatement ps=conn.prepareStatement(sql);
ps.setObject(1,20);
ResultSet rs=ps.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
rs.close();
ps.close();
conn.close();
JDBC格式规范
PreparedStatement+ResultSet，因为这里的mysql表使用的MyISAM，不支持事务，所以没有写rollback
public class Demo01 {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/textjdbc","root","123456");
String sql="select u_age,u_name from u_name where u_age>?";
ps=conn.prepareStatement(sql);
ps.setObject(1,20);
rs=ps.executeQuery();
while(rs.next()){
System.out.println(rs.getInt(1)+" "+rs.getString(2));
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Statement
public class Demo01 {
public static void main(String[] args) {
Connection conn=null;
Statement stm=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/textjdbc","root","123456");
String sql="insert into u_name(u_age,u_name)values(23,'是周周啊')";
stm=conn.createStatement();
stm.execute(sql);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
if(stm!=null){
stm.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
注：上面两种格式规范中都没有添加事务，下面单独写一节介绍添加事务的规范。
事务
首先明确MyISAM不支持事务，InnoDB支持事务，建表默认InnoDB。只在提交没有错误的事务才会改变数据库
如果setAutoCommit默认，那么一个DML语句就相当于一个事务，每执行一个都会提交，出错也不需要回滚，因为出错没有对数据库产生影响。如果设置为false，一个事务多条SQL，出错就需要在catch中手动回滚，没出错就手动提交。
public class StudentDaoImpl {
public void UpdateName(String newName){
String driver="com.mysql.jdbc.Driver";
String username="root";
String password="wityy";
String url="jdbc:mysql://localhost:3306/shiro";
String sql="insert into u_name(u_age,u_name)values(23,'是周周啊')";
Connection conn=null;
Statement stmt=null;
try {
Class.forName(driver);
conn= DriverManager.getConnection(url,username,password);
conn.setAutoCommit(false);
stmt=conn.createStatement();
int i=stmt.executeUpdate(sql);
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
try {
if(stmt!=null){
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
常见报错
ClassNotFoundException 驱动类未找到，加载驱动类出错
no suitble driver for url错误
invalid username/password 用户名或密码错误
违反唯一约束条件 主键冲突
刚刚新建的表更改数据，stmt返回0但是不报错，因为数据库还没刷新，重开一下就好了
注意事项
业务逻辑应当和数据库操作分开，不要把业务逻辑写到dao层中。
ORM（Object Relational Mapping）
public class Student {
private int id;
private String name;
private int age;
private int tid;
public Student() {
super();
}
public Student(int id, String name, int age, int tid) {
super();
this.id = id;
this.name = name;
this.age = age;
this.tid = tid;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
}
用JavaBean存储数据，用ArrayList存储多个JavaBean对象
//声明List集合
ArrayList<Student>list=new ArrayList<>();
String driver="com.mysql.jdbc.Driver";
String username="root";
String password="wityy";
String url="jdbc:mysql://localhost:3306/shiro";
String sql="select *from student";
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs = null;
try {
Class.forName(driver);
conn= DriverManager.getConnection(url,username,password);
conn.setAutoCommit(false);
ps=(PreparedStatement) conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()){
Student stu=new Student();
stu.setId(rs.getInt("id"));
stu.setAge(rs.getInt("age"));
stu.setName(rs.getString("name"));
stu.setTid(rs.getInt("tid"));
list.add(stu);
}
conn.commit();
return list;
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
return list;
JDBCUtil
下面是以前写过的一个控制台demo案例~
五个方面：
dao层放接口，防止业务层和数据库操作层的方法被恶意串改。
daoImpl是数据库操作层
serviceImpl是业务层（标准写法应该还有service包的，这里将接口都写到dao里面了）
util是工具包
pojo是查询存放数据的JavaBean
（test是程序入口）
pojo -Emp
package pojo;
import java.util.Date;
public class Emp {
private int empno;
private String ename;
private String job;
private String mgr;
private Date date;
private double sal;
private double comm;
private int deptno;
public int getEmpno() {
return empno;
}
public void setEmpno(int empno) {
this.empno = empno;
}
public String getEname() {
return ename;
}
public void setEname(String ename) {
this.ename = ename;
}
public String getJob() {
return job;
}
public void setJob(String job) {
this.job = job;
}
public String getMgr() {
return mgr;
}
public void setMgr(String mgr) {
this.mgr = mgr;
}
public Date getDate() {
return date;
}
public void setDate(Date date) {
this.date = date;
}
public double getSal() {
return sal;
}
public void setSal(double sal) {
this.sal = sal;
}
public double getComm() {
return comm;
}
public void setComm(double comm) {
this.comm = comm;
}
public int getDeptno() {
return deptno;
}
public void setDeptno(int deptno) {
this.deptno = deptno;
}
public Emp(int empno, String ename, String job, String mgr, Date date, double sal, double comm, int deptno) {
this.empno = empno;
this.ename = ename;
this.job = job;
this.mgr = mgr;
this.date = date;
this.sal = sal;
this.comm = comm;
this.deptno = deptno;
}
public Emp() {
}
@Override
public String toString() {
return "Emp{" +
"empno=" + empno +
", ename='" + ename + '\'' +
", job='" + job + '\'' +
", mgr='" + mgr + '\'' +
", date=" + date +
", sal=" + sal +
", comm=" + comm +
", deptno=" + deptno +
'}';
}
}
操作数据库层
package daoImpl;
import pojo.Emp;
import util.JdbcUtil;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class EmpDaoImpl implements EmpDao {
//查询员工信息
public ArrayList<Emp> selAllEmpInfo(){
//声明集合对象
ArrayList<Emp> list=new ArrayList<>();
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
//加载驱动
try {
conn= JdbcUtil.getConn();
String sql="select *from emp";
ps=JdbcUtil.getPreSta(conn,sql);
rs=ps.executeQuery();
while(rs.next()) {
Emp emp = new Emp();
emp.setEmpno(rs.getInt("empno"));
emp.setComm(rs.getInt("comm"));
emp.setDate(rs.getDate("hiredate"));
emp.setDeptno(rs.getInt("Deptno"));
emp.setEname(rs.getString("Ename"));
emp.setJob(rs.getString("Job"));
emp.setMgr(rs.getString("Mgr"));
emp.setSal(rs.getInt("Sal"));
list.add(emp);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtil.closeAll(conn,rs,ps);
}
return list;
}
//根据编号查询员工信息
public Emp selEmpInfoByEmpno(int empno){
//声明jdbc变量
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
Emp e=null;
try {
conn=JdbcUtil.getConn();
String sql="select *from emp where empno=?";
ps=JdbcUtil.getPreSta(conn,sql);
ps.setObject(1,empno);
rs=ps.executeQuery();
if(rs.next()){
e=new Emp();
e.setEmpno(rs.getInt("empno"));
e.setComm(rs.getInt("comm"));
e.setDate(rs.getDate("hiredate"));
e.setDeptno(rs.getInt("Deptno"));
e.setEname(rs.getString("Ename"));
e.setJob(rs.getString("Job"));
e.setMgr(rs.getString("Mgr"));
e.setSal(rs.getInt("Sal"));
}
} catch (SQLException ex) {
ex.printStackTrace();
}finally {
JdbcUtil.closeAll(conn,rs,ps);
}
return e;
}
//添加员工信息
public int insEmpInfo(int empno,String ename,String job,int mgr,java.util.Date hiredate,double sal,double comm,int deptno){
String sql="insert into emp values(?,?,?,?,?,?,?,?)";
//将util Date 转化成sql date,固定操作
java.sql.Date d=new java.sql.Date(hiredate.getTime());
int i=JdbcUtil.excuteDml(sql,empno,ename,job,mgr,d,sal,comm,deptno);
return i;
}
//更改员工姓名
public int upEmp(String newname,int empno){
return JdbcUtil.excuteDml("update emp set ename=? where empno=?",newname,empno);
}
//删除员工信息
public int delEmp(int empno){
return JdbcUtil.excuteDml("delete from emp where empno=?",empno);
}
}
业务层
package serviceImpl;
import dao.EmpDao;
import daoImpl.EmpDaoImpl;
import pojo.Emp;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Scanner;
public class EmpServiceImpl implements EmpServiceDao {
EmpDao ed= (EmpDao) new EmpDaoImpl();
//更新用户信息
public void upEmp(){
//获取用户数据
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户编号");
int empno=sc.nextInt();
System.out.println("请输入新的用户名");
String newName=sc.next();//next无法输入带空格回车的字符串
int i=ed.upEmp(newName,empno);
//提示用户更新结果
if(i>0){
System.out.println("更新 成功");
}else{
System.out.println("更新 失败");
}
}
public void insEmp(){
Scanner sc=new Scanner(System.in);
System.out.println("请输入用户编号");
int empno=sc.nextInt();
System.out.println("请输入新增用户名");
String ename=sc.next();
System.out.println("请输入新增用户工作");
String job=sc.next();
System.out.println("请输入新增用户领导编号");
int mgr=sc.nextInt();
System.out.println("请输入新增用户的入职日期（YYYY-MM-DD）");
String date=sc.next();
System.out.println("请输入新增用户基本工资");
double sal =sc.nextDouble();
System.out.println("请输入新增用户提成");
double comm =sc.nextDouble();
System.out.println("请输入新增用户的部门编号");
int deptno=sc.nextInt();
//获取数据库操作对象
//将字符串类型的日期转换成Date类型
java.util.Date hiredate=null;
try {
hiredate=new SimpleDateFormat("yyyy-MM-dd").parse(date);
} catch (ParseException e) {
e.printStackTrace();
}
int i=ed.insEmpInfo(empno,ename,job,mgr,hiredate,sal,comm,deptno);
if(i>0){
System.out.println("增加成功");
}else{
System.out.println("增加失败");
}
}
public void delEmp(){
Scanner sc=new Scanner(System.in);
System.out.println("请输入要删除的员工编号：");
int empno=sc.nextInt();
//获取数据库对象
int i=ed.delEmp(empno);
if(i>0){
System.out.println("删除成功");
}else{
System.out.println("删除失败");
}
}
//根据用户编号进行查询
public void searchEmp(){
Scanner sc=new Scanner(System.in);
System.out.println("请输入要查询的员工编号");
int empno=sc.nextInt();
Emp emp=ed.selEmpInfoByEmpno(empno);
System.out.println(emp);
}
//查询全部员工信息
public void searchAllEmp(){
List<Emp> list =ed.selAllEmpInfo();
for(int i=0;i<list.size();i++){
Emp emp=list.get(i);
System.out.println(emp+"\n");
}
}
}
JdbcUtil
JdbcUtil解决了两个问题：
问题1:当更改数据库时，必须要将程序重启才能完成改变，显然实际开发服务器不能停.将这些变量存到文件里面，可以通过更改这些文件，实现数据库的更改。在src下新建一个properties文件（一般命名db），properties是必须存放键值对的，是对普通文件提取数据过程的一种封装，java有对应的properties专门来读取properties文件
driver=oracle.jdbc.driver.OracleDriver
url=jdbc:oracle:thin:@//localhost:1521/XE
user=scott
pwd=bjxst
问题2：消除代码冗余
package util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtil {
private static String driver;
private static String url;
private static String pwd;
private static String user;
//获取属性文件的内容，类加载驱动
static {
//创建properties对象获取属性文件的内容
Properties p=new Properties();
//获取属性文件的读取流对象(JdbcUtil.class.会动态定位目前的项目src目录)
InputStream is= JdbcUtil.class.getResourceAsStream("/db.properties");
//加载属性配置文件
try {
p.load(is);
} catch (IOException e) {
e.printStackTrace();
}
driver= p.getProperty("driver");
url= p.getProperty("url");
pwd= p.getProperty("pwd");
user= p.getProperty("user");
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
//解决Connection冗余
public static Connection getConn(){
Connection conn=null;
try {
conn= DriverManager.getConnection(url,user,pwd);
return conn;
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
//解决Statement冗余
public static PreparedStatement getPreSta( Connection conn,String sql){
PreparedStatement ps=null;
try {
ps=conn.prepareStatement(sql);
return ps;
} catch (SQLException e) {
e.printStackTrace();
}
return ps;
}
public static Statement getSta(Connection conn){
Statement stmt=null;
try {
stmt=conn.createStatement();
return stmt;
} catch (SQLException e) {
e.printStackTrace();
}
return stmt;
}
public static void closeAll(Connection conn,ResultSet rs,Statement stmt){
//关闭资源
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
}
try {
if(rs!=null){
stmt.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(rs!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
public static int excuteDml(String sql,Object...objs){
Connection conn=null;
PreparedStatement ps=null;
try {
//获取数据库连接对象
conn= JdbcUtil.getConn();
conn.setAutoCommit(false);
//获取sql命令对象
ps= conn.prepareStatement(sql);
for(int i=0;i<objs.length;i++){
ps.setObject(i+1,objs[i]);
}
int i=ps.executeUpdate();
conn.commit();
JdbcUtil.closeAll(conn,null,ps);
return i;
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
e.printStackTrace();
}
return 0;
}
}
Dao层放接口（包括数据库层和业务层的方法）
程序入口
package test;
import serviceImpl.EmpServiceImpl;
import java.util.Scanner;
public class Test {
public static void main(String[] args) {
EmpServiceImpl es=new EmpServiceImpl();
do{
System.out.println("*******************欢迎访问员工管理系统：请选择服务**************************");
System.out.println("*********************************************");
System.out.println("1.查询所有员工信息");
System.out.println("2.根据编号查询员工信息");
System.out.println("3.新增员工信息");
System.out.println("4.修改员工姓名");
System.out.println("5.删除员工信息");
System.out.println("6.退出系统");
Scanner sc=new Scanner(System.in);
int ch=sc.nextInt();
switch (ch){
case 1:es.searchAllEmp();
break;
case 2:es.searchEmp();
break;
case 3:es.insEmp();
break;
case 4:es.upEmp();
break;
case 5:es.delEmp();
break;
case 6:
System.out.println("谢谢使用");return;
default:
System.out.println("输入有误");
break;
}
}while (true);
}
}
数据库分页
我的另外一篇博客，分页demo https://blog.csdn.net/WA_MC/article/details/112406700
特殊数据类型
时间系列
java时间日期总结
这篇链接文章比较详细分类了java中常用的日期时间类及其使用场景，除了以下三个sql相关日期时间的类没有涉及到，该文章中也提到了这三个都是java.util.Date的子类，进行了限制或扩充
java.sql.Date 年月日
java.sql.Time 时分秒
java.sql.Timestamp 年月日时分秒
数据库中设计时间日期的类型有以下5种：
- date对应java.sql.date
- time对应java.sql.time
- datetime对应java.sql.timstamp
- timestamp对应java.sql.timstamp
- year特殊点，可以用Calendar获取year然后转为字符串，year可以直接传入4个字符的字符串表示年份
datetime和timestamp区别：
datatime设置的是什么时间就是什么时间；
timestamp则是存入数据库的是标准UTC时间，然后其他客户端拿到时转换为其当前时区时间。
timestamp存储的范围为：’1970-01-01 00:00:01.000000’ 到 ‘2038-01-19 03:14:07.999999’；
datetime 存储的范围为：’1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="insert into t_user(u_date,u_time,u_datetime,u_timestamp,u_year) values(?,?,?,?,?)";
ps=(PreparedStatement) conn.prepareStatement(sql);
//假设传过来是字符串，先进行一个字符串到date的转换
DateFormat df = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date date = df.parse("2021-12-31 09:15:25");
java.sql.Date sqldate = new java.sql.Date(date.getTime());
java.sql.Time sqltime = new java.sql.Time(date.getTime());
java.sql.Timestamp sqltimestamp = new java.sql.Timestamp(date.getTime());
Calendar cal = new GregorianCalendar();
cal.setTime(date);
System.out.println(cal);
System.out.println(date);
String year = String.valueOf(cal.get(Calendar.YEAR));
ps.setObject(1,sqldate);
ps.setObject(2,sqltime);
ps.setObject(3,sqltimestamp);
ps.setObject(4,sqltimestamp);
ps.setObject(5,year);
ps.execute();
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (ParseException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
流数据类型
CLOB(character large object)
如果将text变量设置为主键，就必须要设置键长，如果不是主键，不设置也不能设置键长
文件字符流写入
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="insert into text(u_text) value(?)";
ps=(PreparedStatement) conn.prepareStatement(sql);
ps.setClob(1, new FileReader("d:/a.txt")); //直接字符流
ps.execute();
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
字符串转字节流再转字符流写入
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="insert into text(u_text) value(?)";
ps=(PreparedStatement) conn.prepareStatement(sql);
String text = "流水不争先，争的是滔滔不绝";
//字符串转字节流再转字符流
ps.setClob(1, new BufferedReader(new InputStreamReader
(new BufferedInputStream(new ByteArrayInputStream(text.getBytes())))));
ps.execute();
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
读取文本
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="select * from text";
ps=(PreparedStatement) conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
Clob c= rs.getClob("u_text");
Reader reader=c.getCharacterStream();
int temp=0;
while((temp=reader.read())!=-1){
System.out.print((char) temp);
}
System.out.println();
}
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
BLOB二进制对象
一张表必须要有一个主键，blob如果是主键，就必须设定键长，如果不是主键，就不用也不能设定键长
存一张图片(图片、音频只能二进制存储)，当然，很多时候我们不将图片和音频存入数据库而是通过存储路径
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="insert into t_blob(u_blob) values(?)";
ps=(PreparedStatement) conn.prepareStatement(sql);
ps.setBlob(1,new FileInputStream(new File("e:/a.png")));
ps.execute();
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
从数据库中去取出图片
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="select * from t_blob";
ps=(PreparedStatement) conn.prepareStatement(sql);
rs = ps.executeQuery();
while(rs.next()){
Blob b=rs.getBlob("u_blob");
InputStream is=b.getBinaryStream();
OutputStream os=new FileOutputStream(new File("d:/gua.jpg"));
byte[] temp=new byte[1024];
int len=-1;
while((len=is.read(temp))!=-1){
os.write(temp);
}
}
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
conn.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
数据库连接池
技术介绍
在Java中，数据库存取技术可分为如下几类：
- JDBC直接访问数据库
- JDO技术
- 第三方O/R工具，如Hibernate, ibatis,mybatis 等
JDBC是java访问数据库的基石， JDO, Hibernate等只是更好的封装了JDBC。
JDBC(Java Database Connectivity)是一个独立于特定数据库管理系统、通用的SQL数据库存取和操作的公共接口（一组API），定义了用来访问数据库的标准Java类库，（java.sql,javax.sql）使用这个类库可以以一种标准的方法、方便地访问数据库资源。JDBC为访问不同的数据库提供了一种统一的途径，为开发者屏蔽了一些细节问题。JDBC的目标是使Java程序员使用JDBC可以连接任何提供了JDBC驱动程序的数据库系统，这样就使得程序员无需对特定的数据库系统的特点有过多的了解，从而大大简化和加快了开发过程。
当然也可以不使用统一的JDBC接口分别使用不同的数据库厂商各自的驱动，但这样就不能体现Java的可移植性了。
JDBC是sun公司提供一套用于数据库操作的接口，java程序员只需要面向这套接口编程即可。不同的数据库厂商，需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动。-----面向接口编程
即
JDBC:一套用于数据库操作的接口
JDBC驱动：需要针对这套接口，提供不同实现。不同的实现的集合，即为不同数据库的驱动,比如使用mysql就需要mysql驱动的jar包
数据库连接池c3p0、dbcp（还有durid）
在使用开发基于数据库的web程序时，传统的模式(JDBC)基本是按以下步骤：
- 在主程序（如servlet、beans）中建立数据库连接
- 进行sql操作
- 断开数据库连接
但这种模式开发，存在的问题:
普通的JDBC数据库连接使用 DriverManager 来获取， 每次向数据库建立连接的时候都要将 Connection 加载到内存中，再验证用户名和密码(得花费0.05s～1s的时间)。需要数据库连接的时候，就向数据库要求一个， 执行完成后再断开连接。这样的方式将会消耗大量的资源和时间。数据库的连接资源并没有得到很好的重复利用.若同时有几百人甚至几千人在线，频繁的进行数据库连接操作将占用很多的系统资源，严重的甚至会造成服务器的崩溃。
对于每一次数据库连接，使用完后都得断开。否则，如果程序出现异常而未能关闭，将会导致数据库系统中的内存泄漏，最终将导致重启数据库。
这种开发不能控制被创建的连接对象数，系统资源会被毫无顾及的分配出去，如连接过多，也可能导致内存泄漏，服务器崩溃。
为解决传统开发中的数据库连接问题，可以采用数据库连接池技术。
数据库连接池的基本思想就是为数据库连接建立一个“缓冲池”。预先在缓冲池中放入一定数量的连接，当需要建立数据库连接时，只需从“缓冲池”中取出一个，使用完毕之后再放回去。
数据库连接池负责分配、管理和释放数据库连接，它允许应用程序重复使用一个现有的数据库连接，而不是重新建立一个。
数据库连接池在初始化时将创建一定数量的数据库连接放到连接池中，这些数据库连接的数量是由最小数据库连接数来设定的。无论这些数据库连接是否被使用，连接池都将一直保证至少拥有这么多的连接数量。连接池的最大数据库连接数量限定了这个连接池能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数量时，这些请求将被加入到等待队列中。
JDBC 的数据库连接池使用 javax.sql.DataSource 来表示（JNDI），DataSource 只是一个接口，该接口通常由服务器(Weblogic, WebSphere, Tomcat)提供实现，也有一些开源组织提供实现：
- DBCP 数据库连接池
- C3P0 数据库连接池
DataSource 通常被称为数据源，它包含连接池和连接池管理两个部分，习惯上也经常把 DataSource 称为连接池
DataSource用来取代DriverManager来获取Connection，获取速度快，同时可以大幅度提高数据库访问速度。
DBCP数据源
DBCP 是 Apache 软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Common-pool. 如需使用该连接池实现，应在系统中增加如下两个 jar 文件：
Commons-dbcp.jar：连接池的实现
Commons-pool.jar：连接池实现的依赖库
Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。
数据源和数据库连接不同，数据源无需创建多个，它是产生数据库连接的工厂，因此整个应用只需要一个数据源即可。
当数据库访问结束后，程序还是像以前一样关闭数据库连接：conn.close(); 但上面的代码并没有关闭数据库的物理连接，它仅仅把数据库连接释放，归还给了数据库连接池。
JNDI
该技术的使用背景：
在高频率访问数据库时，使用数据库连接池可以降低服务器系统压力，提升程序运行效率，数据库连接池的原理是拥有多个数据库连接对象。JNDL是将数据库连接交由应用服务器来管理,然后在应用服务器的JNDI对象中通过lookup()方法来查找取得数据源。另外的，在数据库内容中介绍的连接池是第三方组件的，而JNDI是Java提供的接口
实现Pool+JNDL的步骤
在web项目的META-INF中存放context.xml,在context.xml中编写数据库连接池相关属性
maxActive最大活跃连接对象个数
maxIdle最大闲置个数
auth=“Container” 设置了Container，只有启动服务器部署，Pool才能发挥作用
<?xml version="1.0" encoding="UTF-8"?>
<Context>
<Resource
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/login"
username="root"
password="bjxst"
maxActive="50"
maxIdle="30"
name="test"
auth="Container"
maxWait="10000"
type="javax.sql.DataSource"
/>
</Context>
JNDI操作
java:comp/env是固定的 test是连接池的name属性
Context ctx = new InitialContext();
DataSource ds=(DataSource) ctx.lookup("java:comp/env/test");
//返回的是数据库连接对象，之后像JDBC基本操作一样进行处理
Connection conn= ds.getConnection();
demo示例
@Override
protected void service(HttpServletRequest req, HttpServletResponse resp) {
Context ctx=null;
Connection conn=null;
PreparedStatement ps = null;
ResultSet rs=null;
try {
ctx = new InitialContext();
DataSource ds=(DataSource) ctx.lookup("java:comp/env/test");
//返回的是数据库连接对象，之后像JDBC基本操作一样进行处理
conn= ds.getConnection();
//创建sql命令
String sql="select *from flower";
//创建sql命令对象
ps=conn.prepareStatement(sql);
//占位符赋值
//执行sql命令
rs=ps.executeQuery();
while(rs.next()){
Flower flower=new Flower(rs.getInt("id"),rs.getString("name"),
rs.getDouble("price"),rs.getString("production"));
System.out.println(flower);
}
} catch (NamingException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
try {
if(conn!=null){
conn.close();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
//关闭资源
第三方组件
这里介绍DBCP和c3p0，两种连接池分别有两种方式创建数据库的连接
public class DataSourceTest {
//使用 C3P0 方式二：
@Test
public void test4() throws SQLException{
DataSource ds = new ComboPooledDataSource("helloc3p0");
Connection conn = ds.getConnection();
System.out.println(conn);
}
//使用 C3P0 方式一：
@Test
public void test3() throws Exception{
ComboPooledDataSource cpds = new ComboPooledDataSource();
cpds.setDriverClass("com.mysql.jdbc.Driver");
cpds.setJdbcUrl("jdbc:mysql://127.0.0.1:3306/test");
cpds.setUser("root");
cpds.setPassword("123456");
Connection conn = cpds.getConnection();
System.out.println(conn);
}
//使用 DBCP 方式二：
@Test
public void test2() throws Exception{
Properties pros = new Properties();
pros.load(DataSourceTest.class.getClassLoader().getResourceAsStream("dbcp.properties"));
DataSource ds = BasicDataSourceFactory.createDataSource(pros);
Connection conn = ds.getConnection();
System.out.println(conn);
}
//使用 DBCP 方式一：
@Test
public void test1() throws SQLException{
BasicDataSource bds = new BasicDataSource();
bds.setDriverClassName("com.mysql.jdbc.Driver");
bds.setUrl("jdbc:mysql://127.0.0.1:3306/test");
bds.setUsername("root");
bds.setPassword("123456");
bds.setInitialSize(10);
bds.setMaxActive(10);
Connection conn = bds.getConnection();
System.out.println(conn);
//将连接放回到连接池中
conn.close();
}
}
使用方式二的c3p0需要一个配置文件（当然也可以用属性文件），且配置文件的名字必须是c3p0-config.xml，因为数据库厂商底层是根据key-value来封装数据的，所以会根据这个key来进行匹配读取，而且必须放在src的路径下，因为读取是通过类加载器的方式进行读取的。使用方式二的dhcp需要一个属性文件和jdbc的属性文件大致一样，就不多赘述了
c3p0-config.xml
<c3p0-config>
<named-config name="helloc3p0">
<!-- 连接数据库的四个字符串 -->
<property name="driverClass">com.mysql.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://127.0.0.1:3306/test</property>
<property name="user">root</property>
<property name="password">123456</property>
<!-- 若连接池满了一次增长几个 -->
<property name="acquireIncrement">5</property>
<!-- 连接池初始大小 -->
<property name="initialPoolSize">10</property>
<!-- 连接池中最小连接数 -->
<property name="minPoolSize">5</property>
<!-- 连接池中最大连接数 -->
<property name="maxPoolSize">10</property>
<!-- 整个连接池中最多管理的 Statement 的个数 -->
<property name="maxStatements">10</property>
<!-- 连接池中每个连接最多管理的 Statement 的个数 -->
<property name="maxStatementsPerConnection">2</property>
</named-config>
</c3p0-config>
注意：配置文件的 的名字要和DataSource ds = new ComboPooledDataSource(“helloc3p0”);要传入的参数一致。
C3P0相关jar包:https://pan.baidu.com/s/1eRLznfBuDKv5zssmN4s7jw
提取码：omdw
DBCP相关jar包：https://pan.baidu.com/s/1LUsN5D6uQgPki1hKIX387A
提取码：1111
druid相关jar包（没用过，之前用druid都是springboot+maven的）：https://pan.baidu.com/s/1A2IyjoQQiu-0VZlt2jBcPA
提取码：1111
函数和过程
函数和过程允许业务逻辑作为存储过程记录在数据库中，并在数据库内执行。尽管业务逻辑能够完全存储在数据库以外，但把它们定义成数据库中的存储过程有几个优点。它允许应用访问这些过程，允许当业务规则发生变化时进行单个点的改变，而不用改变应用系统的其他部分。应用代码可以调用函数和存储过程，而不是直接更新数据库关系
如果只有一个返回值，用存储函数，否则，一般用存储过程。
mysql的函数功能默认是off，至少我的5.7版本是这样
查看函数是否开启
show variables like '%func%';
开启：
SET GLOBAL log_bin_trust_function_creators=1;
关闭：
SET GLOBAL log_bin_trust_function_creators=0;
存储函数与存储过程的使用
创建存储过程(CREATE PROCEDURE)
首先创建如下两张表,并初始化一些数据。
基本语法格式如下：
CREATE PROCEDURE sp_name (parameters)
[characteristics ...] routine_body
其中：CREATE PROCEDURE为创建存储过程的关键字；sp_name为存储过程的名称(唯一性，没有所谓的重载方法概念)；parameters为参数列表；characteristics指定存储过程的特性(该部分可以省略,即使用默认声明)；routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。
parameters 表现形式 --> [IN(OUT/INOUT)] param_name type。IN代表入参、OUT代表出参、INOUT代表既可以是入参也可以是出参， param_name参数名称，type是类型(类型是mysql数据库中支持的任意类型,VARCHAR需要指定长度,如VARCHAR(255))。
characteristics 表现形式 -->
| 名称 | 说明 |
| LANGUAGE SQL | 说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值 |
| [NOT] DETERMINISTIC | 指明存储过程执行的结果是否确定。DETERMINISTIC表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。NOT DETERMINISTIC表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为 NOT DETERMINISTIC。 |
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA} | 指明子程序使用SQL语句的限制。CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；NO SQL表明子程序不包含SQL语句；READS SQL DATA说明子程序包含读写读数据的SQL语句；MODIFIES SQL DATA表明子程序包含写数据的语句；默认情况下，系统会指定为CONTAINS SQL。 |
| SQL SECURITY {DEFINER | INVOKER} | 指明谁有权限来执行。DEFINER表示只有定义者才能执行。INVOKER表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER |
| COMMENT 'string' | 注释信息,可以用来描述存储过程或存储函数。 |
--简单的示例：
DELIMITER $$ -- 声明结束符
CREATE PROCEDURE list_emp() -- 创建无参数存储过程
COMMENT '查询所有员工及其所在部门' -- characteristics 部分(可以省略)
BEGIN
SELECT * FROM tb_emp e
INNER JOIN tb_dept d
ON e.dept_id = d.dept_id;
END $$
DELIMITER ; -- 恢复默认结束符
CALL list_emp(); -- 调用存储过程
SHOW PROCEDURE STATUS LIKE '%list_emp%'; -- 查看存储过程
DROP PROCEDURE list_emp; -- 删除存储过程,目前编写错误的话直接删除重写
存储过程定义详情(SHOW PROCEDURE STATUS LIKE '%list_emp%'; -- 查看存储过程)：
执行结果(CALL list_emp(); -- 调用存储过程)：
--有参数的存储过程：
DELIMITER $$
CREATE PROCEDURE count_emp(OUT total_emp INT)
COMMENT '统计员工数量'
BEGIN
SELECT COUNT(*) INTO total_emp FROM tb_emp;
END $$
DELIMITER ;
SET @total_emp = 10; -- 声明变量
CALL count_emp(@total_emp); -- 调用存储过程
SELECT @total_emp; -- 获取结果
DROP PROCEDURE count_emp; -- 删除存储过程
执行结果：
总结说明：
1、这里的DELIMITER $$
为了避免与存储过程中的SQL语句结束符号冲突,需要使用DELIMITER改变存储过程的结束符,并以’END $$‘结束存储过程。存储过程定义完毕再以’DELIMITER ;‘恢复默认结束符号。亦可以指定其它符号为结束符，但是不能用反斜杠’’,它是mysql中的转义符。当然,简单的存储过程不更改结束符大多数情况下也是不会出现错误的。
2、有参数的时候,VARCHAR类型需要指定长度，比如VARCHAR(255)。
创建存储函数(CREATE FUNCTION)
基本语法格式如下：
CREATE FUNCTION func_name(params)
RETURNS type
[characteristics ...] routine_body
其中：CREATE FUNCTION为创建存储函数的关键字；func_name为存储函数的名称(唯一性，没有所谓的重载方法概念)；params为参数列表；RETURNS type语句表示函数返回数据的类型，可以是mysql中的任意数据类型；characteristics指定存储函数的特性(和存储过程一样)；routine_body是存储函数主体。
参数列表：IN、OUT、或INOUT只对PROCEDURE是合法的(FUNCTION中总是默认IN参数，所以声明存储函数入参的时候不不能声明IN，会报错，因为默认是IN)。RETURNS子句只能对FUNCTION作指定，对函数而言这是强制的。它用来指定函数的返回类型，而且函数体必须包含一个RETURN value语句。
-- 简单的示例：
DELIMITER $$ -- 更改结束符
CREATE FUNCTION count_dept()
RETURNS INT
COMMENT '统计部门数量'
BEGIN
RETURN (SELECT COUNT(*) FROM tb_dept);
END $$
DELIMITER ; -- 还原结束符
DROP FUNCTION count_dept; -- 删除存储函数
SHOW FUNCTION STATUS LIKE '%count_dept%'; -- 查看存储函数定义
SELECT count_dept(); -- 调用存储函数
执行结果(SELECT count_dept(); -- 调用存储函数)：
变量的使用(关键字：DECLARE)
变量可以在子程序中声明并使用,这些变量的作用域范围是在BEGIN...END程序中,不能单独在存储过程外部声明变量。
1、定义变量
基本语法：
DECLARE var_name[,var_name1]... date_type [DEFAULT value];
var_name是局部变量的名称，可以同时声明多个变量，但是类型只能声明一次，就是说声明多个变量只能是同类型；date_type是变量类型，可以是mysql中任意的数据类型(VARCHAR需要指明长度，例如VARCHAR(255))；DEFAULT 为变量设置默认值，如果是多参数，不能使用，默认值可以被声明为常量，也可以指定一个表达式。如果没有指定默认值，则为null。
简单的示例：
DECLARE param1 INT DEFAULT 10; -- 声明单参数
DECLARE param1, param2, param3 INT; -- 声明多参数
2、为变量赋值
定义变量后，可以为变量赋值以改变其值。
通过SET...为变量赋值，基本语法：
SET var_name = expr[,var_name=wxpr]....; -- 可以同时为单个或多个变量赋值,expr可以是具体的值，也可以是表达式。
--简单的示例
DECLARE param1, param2, param3 INT; -- 声明3个变量
SET param1=10, param2=20; -- 为param1和param2赋具体的值
SET param3 = param1 + param2; -- 将param1和param2的运算结果赋值给param3
通过SELECT...INTO...为一个或多个变量赋值，基本语法如下：
SELECT col_name[,...] INTO var_name[,...] table_expr;
这个SELECT语法把选定的列直接存储到对应位置的变量(所以要求col_name和var_name一一对应)。col_name表示字段名称，var_name表示定义的变量名称；table_expr表示查询条件表达式，包括名称和WHERE子句。
--简单的示例：
SELECT dept_name, dept_addr INTO v_dept_name, v_dept_addr FROM tb_dept WHERE dept_id = 1;
事务的使用
事务并不会影响存储过程或存储函数的执行顺序，也不会中断执行(存储过程或存储函数始终会执行到END)。
START TRANSACTION; -- 开始事务
COMMIT; -- 提交事务
ROLLBACK; -- 回滚事务
流程控制的使用
流程控制语句用来根据条件，控制语句的执行。mysql中用来构造控制流程的语句有：IF、CASE、LOOP、LEAVE、ITERATE、REPEAT、WHILE。
IF语句（说明：mysql中还有一个IF()函数，注意区别）：
注意：IF语句必须配合THEN、END IF使用。除了IF还有ELSEIF(没有分开)和ELSE。
基本语法：
IF (expr_condition) THEN
....do something.....
ELSEIF (expr_condition) THEN
....do something.....
ELSE
....do something.....
END IF;
说明：如果表达式expr_condition计算结果为true,则执行THEN后面的逻辑，如果都不匹配则执行ELSE。条件判断表达式建议用括号包起来，增加可读性。
CASE语句
CASE语句也是一个条件判断语句。需要配合WHEN、THEN和END CASE使用。
CASE语句有两种语法格式：
CASE
WHEN expr_condition THEN ...do something...
WHEN expr_condition THEN ...do something...
....多个 WHEN THEN 语句.....
[ELSE ...do something...]
END CASE;
其中expr_condition为条件表达式,计算结果为true,则执行THEN后面的语句。多个WHEN...THEN依次执行，ELSE为可选条件。
另外一种语法格式：
CASE case_expr
WHEN expr THEN .....do something....
WHEN expr THEN .....do something...
....多个表达式...
[ELSE ...dosomething..]
END CASE;
其中，case_expr表示条件判断的表达式,WHEN后的表达式结果如果和case_expr匹配,则执行相应的THEN后面的语句。没有则执行ELSE，ELSE为可选。这种语法类似switch...case(建议使用第一种,逻辑清晰一点)。
LOOP、REPEAT、WHILE、LEAVE和ITERATE
其中LOOP、REPEAT和WHILE都是创建循环语句的关键词。
LEAVE用来退出任何被标注的循环语句(类似break，只能用于循环语句)。
ITERATE语句将执行顺序转到语句段开头处(类似continue，只能用于循环语句)。
LOOP基本语法（需要配合 END LOOP）：
[loop_label]:LOOP
...do something....
END LOOP [loop_label];
其中，loop_label为可选,表示LOOP体的标签,do something为循环体。
LOOP简单示例(配合LEAVE和ITERATE)：
DECLARE id INT DEFAULT 0;
add_loop:LOOP
SET id = id + 1;
IF(id>10) THEN
LEAVE add_loop;
ELSE
ITERATE add_loop;
END IF;
END LOOP add_loop;
REPEAT基本语法(需要配合 UNTIL和END REPEAT)：
[repeat_label：] REPEAT
....do something....
UNTIL expr_condition;
END REPEAT [repeat_label];
其中,repeat_label为可选，表示REPEAT循环语句的标签,UNTIL指定循环条件。REPEAT执行过程是，每次循环体执行完毕需要去判断一下条件表达式expr_condition，如果为true继续执行，否则结束循环。
WHILE基本语法（需要配合DO和END WHILE使用）：
[while_lebel:] WHILE expr_condition DO
.....do something....
END WHILE [while_label];
其中，while_label为可选，表示WHILE循环语句的标签。WHILE的执行过程是，先判断条件表达式expr_condition，如果为true执行循环，否则结束循环(与REPEAT的区别是：WHILE先判断条件，REPEAT是后判断条件)。
调用存储过程
基本语法：
CALL sp_name([params,...]);
说明：如果存储过程没有参数，不能省略括号；参数个数要与存储过程定义的入参和出参个数匹配，用逗号隔开；出参类型的参数需要加上@符号。
调用存储函数
基本语法：
SELECT func_name([params...]);
说明：如果存储函数没有参数，不能省略括号；因为存储函数都是入参，所以只需注意匹配个数即可。
查看存储过程和存储函数
基本语法：
SHOW [PROCEDURE | FUNCTION] STATUS [LIKE 'pattern'];
说明：查看存储过程即PROCEDURE、存储函数即FUNCTION。LIKE为过滤条件(按存储过程、函数名字匹配)，如果没有则是查看所有定义。
SHOW [CREATE | PROCEDURE] FUNCTION sp_name;
此语句查看定义存储函数、存储过程的脚本。
删除存储过程、存储函数
基本语法：
DROP [PROCEDURE | FUNCTION] [IF EXISTS] sp_name;
说明：sp_name为存储过程或存储函数的全称。[IF EXISTS]是mysql的一个扩展，建议使用。如果存储过程或函数不存在，它可以防止错误的产生，但是会产生一个用SHOW WARNINGS查看的警告。
修改存储过程、存储函数
注意：只能修改存储过程或函数的定义，不能修改执行的逻辑代码或参数。
基本语法：
ALTER [PROCEDURE | FUNCTION] sp_name [characteristic.....];
其中，sp_name为存储过程或函数的名称，characteristic指定存储过程的特性。可以修改的特性和定义存储过程的时候的可选特性是一样的，不重复累赘。
总结
1、存储过程和存储函数的区别(除了关键字：PROCEDURE、FUNCTION)
本质上都是存储程序。
参数类型不同：存储函数不允许声明出参类型，只能通过return关键字返回；
调用方式不同：存储过程用CALL，存储函数用SELECT；
存储函数限制比较多，多以建议使用存储过程，慎用存储函数。
2、修改存储过程、存储函数
存储过程或存储函数中的代码是不提供修改的，只能通过drop删除后，重新编写，或者直接编写一个新的程序。只能修改存储过程或存储函数的特性。
3、存储过程和存储函数可以相互调用
存储过程和存储函数包含自定义的SQL语句集合，所以，可以使用CALL或SELECT调用其它存储过程和存储函数。但是不能使用DROP删除其它存储过程和存储函数。
4、注意区别参数名字和表中的字段名
在定义存储过程和存储函数的时候，参数名称一定要与表中的字段名区别开来，否则可能出现无法预期的结果。
JDBC调用存储过程
(不写JDBC与函数是因为函数不常用)
DELIMITER $$ -- 声明结束符
CREATE PROCEDURE p_student_upd(IN s_tid int,IN s_id int)
BEGIN
update student set tid = s_tid where id = s_id;
END $$
DELIMITER ;
call p_student_upd(1,7);
DELIMITER $$ -- 声明结束符
CREATE PROCEDURE p_student_del(IN s_name varchar(20))
BEGIN
delete from student where name = s_name;
END $$
DELIMITER ;
call p_student_del('学生5');
DELIMITER $$ -- 声明结束符
CREATE PROCEDURE p_student_ins(IN s_name varchar(20),IN s_age int(3),IN s_tid int(3))
BEGIN
insert into student values(default,s_name,s_age,s_tid);
END $$
DELIMITER ;
call p_student_ins('学生5',20,1);
-- 返回单个值
DELIMITER $$ -- 声明结束符
CREATE PROCEDURE p_student_count(OUT count int)
BEGIN
select count(*) into count from student;
END $$
DELIMITER ;
SET @count = 10; -- 声明变量
call p_student_count(@count);
select @count;
-- 返回多个值
DELIMITER $$ -- 声明结束符
CREATE PROCEDURE p_student_all()
BEGIN
select (name,age,tid) from student;
END $$
DELIMITER ;
更、删、增使用基本一致，只举一个例子
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="{call p_student_upd(?,?)}";
ps= (PreparedStatement) conn.prepareCall(sql);
ps.setObject(1, 1);
ps.setObject(2, 6);
ps.execute();
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(ps!=null){
ps.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
查单个值
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="{call p_student_count(?)}";
cs = conn.prepareCall(sql);
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();
int count = cs.getInt(1);
System.out.println(count);
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(cs!=null){
cs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
查多个值
Connection conn=null;
CallableStatement cs=null;
ResultSet rs=null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/shiro","root","wityy");
conn.setAutoCommit(false);
String sql="{call p_student_all()}";
cs = conn.prepareCall(sql);
rs = cs.executeQuery();
while(rs.next()){
System.out.println(rs.getString("name") + " " + rs.getInt("age") + " " + rs.getInt("tid"));
}
conn.commit();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
try {
conn.rollback();
} catch (SQLException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
e.printStackTrace();
} finally {
try {
if(rs!=null){
rs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(cs!=null){
cs.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
游标
创建游标
首先在MySql中创建一张数据表：CREATE TABLE IF NOT EXISTS `store` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`count` int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7;
INSERT INTO `store` (`id`, `name`, `count`) VALUES
(1, 'android', 15),
(2, 'iphone', 14),
(3, 'iphone', 20),
(4, 'android', 5),
(5, 'android', 13),
(6, 'iphone', 13);
我们现在要用存储过程做一个功能，统计iphone的总库存是多少，并把总数输出到控制台。
--在windows系统中写存储过程时，如果需要使用declare声明变量，需要添加这个关键字，否则会报错。
delimiter //
drop procedure if exists StatisticStore;
CREATE PROCEDURE StatisticStore()
BEGIN
--创建接收游标数据的变量
declare c int;
declare n varchar(20);
--创建总数变量
declare total int default 0;
--创建结束标志变量
declare done int default false;
--创建游标
declare cur cursor for select name,count from store where name = 'iphone';
--指定游标循环结束时的返回值
declare continue HANDLER for not found set done = true;
--设置初始值
set total = 0;
--打开游标
open cur;
--开始循环游标里的数据
read_loop:loop
--根据游标当前指向的一条数据
fetch cur into n,c;
--判断游标的循环是否结束
if done then
leave read_loop; --跳出游标循环
end if;
--获取一条数据时，将count值进行累加操作，这里可以做任意你想做的操作，
set total = total + c;
--结束游标循环
end loop;
--关闭游标
close cur;
--输出结果
select total;
END;
--调用存储过程
call StatisticStore();
fetch是获取游标当前指向的数据行，并将指针指向下一行，当游标已经指向最后一行时继续执行会造成游标溢出。
使用loop循环游标时，他本身是不会监控是否到最后一条数据了，像下面代码这种写法，就会造成死循环
read_loop:loop
fetch cur into n,c;
set total = total+c;
end loop;
declare continue HANDLER for not found set done = true;
所以在循环时加上了下面这句代码：
--判断游标的循环是否结束
if done then
leave read_loop; --跳出游标循环
end if;
如果done的值是true，就结束循环。继续执行下面的代码。
使用方式
游标有三种使用方式： 第一种就是上面的实现，使用loop循环； 第二种方式如下，使用while循环：drop procedure if exists StatisticStore1;
CREATE PROCEDURE StatisticStore1()
BEGIN
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
fetch cur into n,c;
while(not done) do
set total = total + c;
fetch cur into n,c;
end while;
close cur;
select total;
END;
call StatisticStore1();
第三种方式是使用repeat执行：
drop procedure if exists StatisticStore2;
CREATE PROCEDURE StatisticStore2()
BEGIN
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
repeat
fetch cur into n,c;
if not done then
set total = total + c;
end if;
until done end repeat;
close cur;
select total;
END;
call StatisticStore2();
在mysql中，每个begin end 块都是一个独立的scope区域，由于MySql中同一个error的事件只能定义一次，如果多定义的话在编译时会提示Duplicate handler declared in the same block。
drop procedure if exists StatisticStore3;
CREATE PROCEDURE StatisticStore3()
BEGIN
declare _n varchar(20);
declare done int default false;
declare cur cursor for select name from store group by name;
declare continue HANDLER for not found set done = true;
open cur;
read_loop:loop
fetch cur into _n;
if done then
leave read_loop;
end if;
begin
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'iphone';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
iphone_loop:loop
fetch cur into n,c;
if done then
leave iphone_loop;
end if;
set total = total + c;
end loop;
close cur;
select _n,n,total;
end;
begin
declare c int;
declare n varchar(20);
declare total int default 0;
declare done int default false;
declare cur cursor for select name,count from store where name = 'android';
declare continue HANDLER for not found set done = true;
set total = 0;
open cur;
android_loop:loop
fetch cur into n,c;
if done then
leave android_loop;
end if;
set total = total + c;
end loop;
close cur;
select _n,n,total;
end;
begin
end;
end loop;
close cur;
END;
call StatisticStore3();
上面就是实现一个嵌套循环，当然这个例子比较牵强。凑合看看就行。。
动态SQL
Mysql 支持动态SQL的功能，set @sqlStr='select * from table where condition1 = ?';
prepare s1 for @sqlStr;
--如果有多个参数用逗号分隔
execute s1 using @condition1;
--手工释放，或者是 connection 关闭时， server 自动回收
deallocate prepare s1;
触发器
- 只有表支持触发器，视图不支持
- 保持每个数据库中的触发器名唯一
- 与其他DBMS相比，mysql支持的触发器相当初级
- 应该用触发器来保证数据的一致性（大小写、格式等）
- 触发器的一种非常有意义的使用是创建审计追踪，使用触发器，把更改记录到另一个表中非常容易
- mysql触发器不能调用存储过程
- 删除触发器 drop trigger 触发器名
- before一般用于数据验证和净化，after用于业务逻辑更新
- 多语句触发器，begin end来包住多条sql即可
insert trigger
-
NEW关键字表示被插入的行
-
在before insert中，插入的值可以被更新
-
对于auto_increment列，insert执行之前为0，之后为生成的值
-- 没有想到合适的业务逻辑，所以这个例子并不恰当，只看使用方法
create trigger newteacher before insert on teacher
for each row set NEW.id = 3;
insert into teacher values(1,'老师3');
delete trigger
- OLD关键字表示被删除的行
- OLD中值全部只读
-- 删除老师之前，删除该老师的所有学生,因为有学生tid和老师id之间外键约束，所以要用before
create trigger delteacher before delete on teacher
for each row delete from student where tid = OLD.id;
delete from teacher where id = 1;
update trigger
- OLD关键字访问旧值
- NEW关键字访问新更新的值
- before中，新值可以被更新
- OLD值全部只读
create trigger updatevendor before update on vendors
for each row set NEW.vend_state = Upper(NEW.vend_state);
备份
日志
性能改善
mysql必知必会一书中提供了一些改善SQL语句性能的小技巧，但不是绝对的。
关于关系数据理论和数据库设计对应书中的7/8两章，过于晦涩，故参考中国人民大学王珊教授的数据库高级篇课程进行整理，并在其基础上尽量贴近实践，
关系数据理论
why
问题引出
针对一个具体问题，应该如何构造一个适合于它的数据模式，应该构造几个关系，每个关系由哪些属性组成。什么样的设计是一个好的数据库逻辑设计呢？关系数据理论就是解决这个问题的，关系数据理论是在数据库的逻辑设计阶段起作用的。
该设计中存在的问题：
- 数据冗余度大：相同数据太多。系主任名字重复出现
- 更新异常：更新时维护代价大。如果某系更换系主任，系统必须修改与该系学生有关的每一个元组
- 插入异常：该插入的插不进去。如果一个系还没有招生，就无法将该系极其系主任的信息存入数据库
- 删除异常：不该删除的删除。如果某个系的学生全部毕业，该系极其系主任信息丢掉了。
所以好的模式的特点应该是不会发生插入异常、删除异常、更新异常、数据冗余尽可能少。
出现这些异常的原因：数据依赖引起的。所以前人将观察、经验上升为理论，有了现在的关系数据理论，用规范化理论改造关系模式，消除其中不合适的数据依赖。
数据依赖：
在上面的关系中，数据依赖有以下3个，都是函数依赖，函数依赖是根据属于语义确定的
Sno -> Sdept
Sdept -> Mname
(Sno,Cno) - > Grade
通俗的说，数据依赖表示唯一确定关系，比如Sno唯一确定Sdept，我们可以将函数依赖用图表示
完全函数依赖和部分函数依赖
（Sno，Cno）- > Grade 是完全函数依赖(表示为箭头上加F，Full)
（Sno，Cno） -> Sdept 是部分函数依赖，因为Sno -> Sdept（表示为箭头上加P，Part）
通过看函数依赖图可以很清楚看出是完全还是部分。
传递函数依赖
Sno -> Mname 为传递依赖，因为Sno -> Sdept，Sdept -> Mname
通过看函数依赖图可以很清楚找出传递函数依赖。
关系模式表示
R<U,F> R为关系名，U表示属性集合，F表示依赖关系集合
上面的例子用关系模式表示，并且建议经常使用上面的函数依赖图来表示函数依赖
Student<U,F>
U = {Sno，Sdept，Mname，Cno，Grade}
F = {Sno -> Sdept，Sdept -> Mname，(Sno,Cno) - > Grade}
主属性和非主属性
在候选码中的属性称为主属性，不在任何候选码中的属性称为非主属性。
规范化
满足不同程度要求的关系对应不同范式，低级范式包含高级范式，一个低级范式的关系，通过模式分解可以转换为若干个高级范式的关系模式的集合，这个过程就叫做规范化。
1NF
所有属性都是不可分的基本数据项，所有关系都满足1NF
S-L-C（Sno，Cno，Sdept，Sloc，Grade），每个系的学生住在同一个楼
主属性：Sno，Cno
非主属性：Grade，Sdept，Sloc
非主属性Sdept和Sloc部分函数依赖于码（Sno，Cno）
1NF存在的问题：
- 插入异常：学生还未选课，就无法插入（Cno是主属性）
- 删除异常：如果学生选课信息全部删除，学生信息也被删除了
- 更新异常：学生转系，需要修改它选修所有课程的Sdept和Sloc信息
- 数据冗余：一个学生选多门课程，Sdept和Sloc重复多次
2NF
如果每一个非主属性都完全函数依赖于R的码，则R∈2NF
投影分解，将部分函数依赖分开，划分的规则是函数依赖图中的虚线箭头消除，并且保留实线箭头。分解后如下图。
SC（Sno，Cno，Grade）S-L（Sno，Sdept，Sloc）现在就符合2NF了，
现在我们来看1NF中的存在的四个问题是否得到解决：
- 消除插入异常。学生还未选课，也可以将信息插入到SL中
- 消除删除异常。删除所有选课记录，学生信息仍在SL表中
- 消除更新异常。学生转系Sdept和Sloc只需修改一次
- 消除冗余。每个学生Sdept、Sloc只存储一次
2NF可以在一定程度上减轻异常和冗余，但不能完成消除异常。
在2NF中还存在非主属性对码存在传递函数依赖（Sno -> Sloc），传递函数依赖存在问题如下：
- 插入异常：某个系还没有学生，就无法存储该系的信息
- 删除异常：如果学生毕业，删除所有学生同时，该系的信息也丢掉
- 数据冗余：SL表每个系Sdept和Sloc冗余
- 更新异常：调整一个系学生住处，每个学生都需要更改Sloc
3NF
满足2NF基础上，如果每个非主属性都不存在对码的传递函数依赖，R∈3NF
SL分解为两个关系模式，消除传递函数依赖，得到3NF
S-D（Sno，Sdept）D-L（Sdept，Sloc）
现在我们来看2NF中存在的问题是否得到解决：
- 消除插入异常。即使没有在校学生，也能在DL中插入系信息
- 消除删除异常。删除某个系的全部学生，系信息仍存在
- 数据冗余。一个系的Sloc只存储一次
- 修改某个系的学生住处，只需要DL的一个元组Sloc属性
3NF仍不能完全消除各种异常和冗余。
BCNF
因为上边的例子2NF到3NF后也满足了BCNF，所以另外举一个例子
STJ（S，T，J）S表示学生，T表示教师，J表示课程
每个教师只教一门课，每门课由多个老师教 T -> J
某一个学生选定课后就确定一个老师 (S，J) -> T
某个学生选修某个教师的课就确定所选课的名称（S，T）-> J
候选码（S，J）（S，T）。STJ都是主属性，不存在非主属性对码的部分函数依赖和传递函数依赖，STJ∈3NF
该3NF存在y问题：
- 插入异常：如果教师开设某门课程，但未有学生选修，则课程信息无法存入数据库
- 删除异常：如果选修某门课程的学生毕业，则教师开设该课程的信息丢失
- 数据冗余：所有选修一个课程的学生都要记录教师信息
- 更新异常：课程更换教师，所有选修该教师课程的学生都要更新T
问题出现的原因是主属性部分依赖于码（T - > J），分解规则是将部分函数依赖单独提出，TJ，SJ
SJ(S，J) TJ(T，J)
- 消除插入异常。教师开设课程可以只在TJ中
- 消除删除异常。学生毕业，开设课程信息在TJ中
- 消除冗余。每个教师开设课程信息只在TJ中存储一次
- 消除更新异常。课程更换教师，只需要修改TJ中一个元组
BCNF实现了模式的彻底分解，达到了函数依赖最高的规范化程度，彻底消除了操作异常。通常认为BCNF是修正的第三范式，有时称为扩展的第三范式
4NF
3NF->4NF消除非平凡且非函数依赖的多值依赖，不讲解，因为一般我们彻底消除函数依赖就已经是规范化程度很高的设计了。
5NF
4NF->5NF消除连接依赖，不讲解。
模式分解
Armstrong公理系统
Armstrong公理系统为模式分解提供理论基础。Armstrong公理系统推理规则如下
蕴含：R<U,F> X - > Y 称F蕴含 X -> Y (其中X - > Y为函数依赖)
-
自反律 若Y为X子集，则X -> Y
-
增广律 若X - > Y 则XZ - > YZ
-
传递律 若X -> Y 且 Y -> Z 则X -> Z
-
合并规则 X -> Y 且 X -> Z 则X -> YZ
-
伪传递规则 X -> Y 且WY -> Z 则XW -> Z
-
分解规则 X -> Y 且Z为Y子集 则X -> Z
闭包
在关系模式R<U,F>中为F所逻辑蕴含的函数依赖的全体叫做F的闭包，记作F+。X的闭包为F的所有闭包中类似 X -> A 这样的依赖，记作XF+。
通过这些推导规则可以求出一个关系模式的F+（一般主要是求X的闭包，而不是求整个闭包）
求解XF+的迭代算法，下面举了一个例子
判定X -> Y是否成立可以通过求XF+，判定Y是否为XF+的子集。
已知关系模式R<U,F>,其中U={A,B,C,D,E}
F={AB -> C，B -> D，C -> E，EC -> B，AC -> B}。求AB的闭包
X0 = AB
找出左侧为AB子集的
AB -> C B -> D
将X0和它们两个右侧合并 X1 = X0 U CD = ABCD
X0 != X1 ，继续计算X2，找到ABCD子集（不包括已经找到的），C -> E，AC -> B
将X1和它们两个右侧合并，X2 = X1 U BE = ABCDE
X2 ！= X1 但X2已经为全部属性集合，所以（AB）F+ =
注：另一种结束情况是Xi = X(i - 1)算法终止
最小依赖集
最小依赖集F满足其中的函数依赖均不能由F中其他函数依赖导出（即没它不行），F中各函数依赖左部均为最小属性集（即X ->A，不可能再有X的子集Z使得 Z -> A）
求最小依赖集，步骤如下：
- 将F的所有函数依赖的右边化为单一属性
- 去掉F中的所有函数依赖左边的冗余属性
- 去掉F中所有的冗余的函数依赖
R<U,F>，U = ABCD， F={A -> BD，AB -> C，C -> D}
- BD分开，分为A ->B，A -> D， F={A ->B，A -> D，AB -> C，C -> D}
- 看AB两个有没有冗余属性
去掉B求A+ = {A,B,C,D} ,所以B冗余
去掉A求B+ = {B,C,D}，所以A不冗余，不能去掉
F={A ->B，A -> D，A -> C，C -> D}
- 通过去掉依赖，看其他依赖能不能推出该依赖（通过求A+判定）
尝试去掉A -> D，A+={A,B,C,D} 可以去掉
得到F = {A->B,A->C,C->D}
现在还可以尝试去掉A -> C，A+ ={B} 不能去掉
最小依赖集不一定是唯一的
模式分解算法
对实践来讲，前面求解闭包和求解最小依赖集都是为了模式分解做先导知识的，前面我们在进行规范化的时候因为表属性比较少，使用投影分解法能直接分解，模式分解提供更一般的分解方法。
在分解方法中，只有能够保证分解后的关系模式与原关系模式等价的方法才有意义，等价的概念分为以下三种
- 无损连接性，无损连接性不一定能解决异常和冗余，只能保证不丢失信息
- 保持函数依赖，F+ = （F1 U F2 U F3…）+，保持函数依赖能够减轻或解决各种异常情况
- 既要有保持函数依赖，又有无损连接性
S-L（Sno，Sdept，Sloc），F = {Sno->Sdept，Sdept->Sloc}
S-L为2NF，Sloc传递函数依赖于Sno，该关系模式存在非主属性对码的传递函数依赖，将该关系模式有以下几种分解情况
-
（1）SN(Sno) SD(Sdept) SO(Sloc) 虽然规范化程度很高，显然丢失了数据之间联系的信息
-
（2）NL(Sno,Sloc) DL(Sdept,Sloc) 仍然丢失信息，NL中通过Sloc不能确定Sdept信息
- （3）ND(Sno,Sdept) NL(Sno,Sloc) 没有丢失信息，有无损连接性，但函数依赖Sdept ->Sloc依赖没有投影到这两个关系模式上，这样会导致学生转系必须同时修改两张表
- （4）ND(Sno,Sdept) DL(Sdept,Sloc) 保持函数依赖性，即Sno->Sdept，Sdept->Sloc都在，和原依赖相同，并且有无损连接性
算法1：转换为3NF的保持函数依赖分解，见书
算法2：转换为3NF的无损连接+保持函数依赖分解，见书
算法3：转换为BCNF的无损连接分解，见书
算法4：达到4NF的无损连接分解，见书
数据库设计
WHAT？
数据库设计是对于一个给定的应用环境，设计一个优良的数据库 视图、逻辑模式和物理结构，并据此建立数据库及其应用系统，使之能够有效存储和管理数据，满足各种用户的应用需求，包括信息管理要求和数据处理要求。
大型数据库设计涉及多学科的综合性技术，是一项庞大的工程项目，要求设计人员掌握多方面的知识和技术，包括：
- 计算机的基础知识
- 软件工程的原理和方法
- 程序设计的方法和技巧
- 数据库的基本知识
- 数据库设计技术
- 应用领域的知识（此点是非技术知识，但至关重要）
数据库设计工具
PowerDesigner，原先是单纯的数据库设计工具，现在扩展了各种UML建模。
Rational Rose UML工具- 数据库建模
CA ERWin 功能欠打、易于使用的数据建模、数据库设计与开发工具
设计步骤
在引言中，数据库设计只写了主要的四个阶段（这里直接搬过来了），除此之外，还应该有数据库实施和数据库维护阶段
1.需求分析阶段是全面刻画预期的数据库用户的数据需求，此阶段成果是制定出用户需求的规格文档
2.概念设计阶段。选择数据模型，将需求转换成数据库的概念模式。用ER模型表示概念设计。概念设计定义了数据库中表示的实体，实体的属性，实体之间的联系，以及实体上的约束。
3.逻辑设计阶段。将高层的概念模式映射到要使用的数据库系统的实现数据模型上。E-R模型映射到表上。涉及关系数据理论和规范化。可以使用3NF设计方法
4.物理设计阶段。指定数据库的物理特性，这些特性包括文件组织的形式以及内部的存储结构的选择。
5.数据库实施阶段。编写与调试应用程序，组织数据入库并试运行
6.数据库维护阶段。经过试运行后即可投入正式运行，在运行过程中必须不断对数据库设计进行评估调整与修改。
设计一个完善的数据库系统往往是上面6个步骤的不断反复，设计数据库应用系统也是同时这样进行的，两方面同时进行，同时完善，同时补充。
需求分析阶段
重要性：结果是否准确地反应了用户的实际要求，将直接影响到后面各个阶段的设计，并影响到设计结果是否合理和实用。
需求分析常常被忽视：
- 设计人员认为这是软任务，急于进行具体设计
- 用户嫌麻烦
- 领导不重视
确定用户需求的难点：
- 用户缺少计算机知识，不能准确表达自己需求，提出需求不断变化，有时常常提出一些不切实际的需求
- 设计人员缺少用户的专业知识，不易理解用户的真正需求，甚至误解用户的需求
解决方法：设计人员必须不断深度地与用户进行交流才能逐步确定用户的实际需求。常常是产品经理做这个事情。
数据字典
关于数据库中数据的描述，它不是数据本身，而是数据的数据，在需求分析阶段建立，在数据库设计过程中不断修改和完善。
数据项
数据结构 = {数据结构名，含义说明，组成：{数据项或数据结构}}
数据流
数据存储
处理过程
概念设计阶段
- 概念模型是数据库设计的有力工具
- 数据库设计人员和用户之间进行交流的语言
- 概念模型应该简单、清晰、易于用户理解
ER（Entity Relationship）
一般用ER图来描述概念模型
- 一对一联系：班级和班长
- 一对多联系：班级和学生
- 多对多联系：课程和学生
例子：
扩展的ER模型
ISA联系，即父类子类之间联系，用△表示
（1）分类属性：
分类属性是父实体的一个属性。
（2）不相交约束和可重叠约束
在△中加叉号表示不相交约束，只能为其中一个子类实体，可重叠约束就是可以是多个子类实体，没有叉号即可。
（3）完备性约束
父类中实体是否必须是某一个子类实体，用双线连接表示全部特化单线连接反之。
基数约束
对实体间一对一、一对多、多对多联系的细化
Part-of 联系
描述某个实体型是另外一个实体型的部分
可以分为独占和非独占
非独占联系：整体损坏，部分仍然存在
独占联系：整体被破坏，部分实体不能存在
用非强制参与联系表示非独占的Part-of联系，比如下图中轮子可以不被汽车所拥有
用弱实体和识别联系来表示独占联系
UML
注：这里仅仅是使用UML类图的语言来表示ER图，这与软件工程设计中的类图是不同的，关于软件工程的设计方法建议读《软件工程》黑皮书。要注意到数据库设计和数据库应用系统设计本来就是同时进行的，也可以说数据库设计是软件工程设计的一部分。
Unified Modeing Language 称为统一建模语言，为软件开发的各个阶段提供模型化和可视化支持的规范语言，从需求规格描述到系统完成后的测试和维护
类图
可以用类图来表示ER图，如下图
逻辑设计阶段
ER图转换为关系模式集合，将实体型、实体属性、实体间的联系转换为关系模型
实体型的转换
- 关系模式的属性 ——实体的属性
- 关系模式的码——实体的码
实体型间1:1联系
- 可以转换为一个独立的关系模式
- 也可以在相连的任意一端对应的关系模式合并
（1）独立关系模式
管理（职工号，班级号）
（2）管理与班级合并
班级（班级号，{班级其他属性}，职工号）在班级中加入了教师的码，即职工号
（3）管理和教师合并
教师（职工号，{教师其他属性}，班级号）在教师中加入了班级的码，即班级号
实体型间1:n联系
（1）独立关系模式
组成（学号，班级号）
（2）合并
学生（学号，{学生其他属性}，班级号）
实体型间m:n联系
单独关系模式
选修（学号，课程号，成绩）
数据模型优化
关系数据模型的优化通常以规范化理论为指导
首先可以确定数据模型的数据依赖，然后将较低范式分解为高级范式
到底规范化到上面程度，要权衡潜在问题和效率两个方面。当查询经常需要连接时，连接运算代价很高，我们需要考虑降低规范化程度。非BCND的关系模式会存在不同程度异常，如果我们的操作只是查询并不增删改，那么这些潜在的异常就不会发生。
用户子模式
即试图机制考虑局部的特殊需求和用户体验。
- 使用更符合用户习惯的别名
- 针对不同级别的用户定义不同的视图，提高系统的安全性
- 简化用户对系统的使用，比如复杂查询
物理设计阶段
在描述物理设计阶段之前，我们需要了解数据库的一些文件存储与索引知识(下一章)
数据库的物理设计是为一个给定的逻辑数据模型选取一个最适合应用要求的物理结构的过程，就是数据库的物理设计。数据库在物理设备上的存储结构与存取方法称为数据库的物理结构，它依赖于选定的DBMS。
具体要做：
- 在模式上建立索引或者建立聚簇
- 为关系、日志、索引、备份等数据库文件选择物理存储结构
在设计之前应该掌握的数据库知识：
-
DBMS内部提供哪些索引（例如B+、Hash、BitMap）、索引各自的特性、怎样建立这些索引。
-
有哪些存储结构（比如行存储、列存储、聚簇存储），如何选择。
在设计之前详细分析要运行的事务作为物理设计的目标：
查询事务
- 查询所设计的关系
- 查询条件涉及的属性
- 连接条件涉及的属性
- 查询的投影属性
更新事务（更删改差不多，故只写一个）
- 被更新的关系
- 每个关系上的更新操作条件涉及的属性
- 修改操作要改变的属性值
每个事务在各个关系上运行的频率和性能要求（我们的数据库物理设计需要满足这些要求，否则可能需要对逻辑设计进行更改重构）
索引存取方法选择
索引是为了提高存取的效率。这里的索引和前面sql语句中的索引index是一致的，不同DBMS实现不同，在此处先只考虑一般化的数据库物理设计情况，在文章的最后单独一章会来分析mysql的底层（基于《高性能MYSQL》一书）。
根据应用要求确定：
- 对哪些属性列建立索引
- 对哪些索引要设计为唯一索引、组合索引
- 选择合适的索引方法（B+、hash，R-Tree，BitMap等）
在哪些属性上建立索引？选择索引存取方法的一般规则：
- 如果一个属性经常作为查询条件，考虑在这个属性上建立索引
- 如果一个属性经常作为最大值和最小值等聚集函数的参数，考虑在这个属性上建立索引
- 如果一个属性经常在连接操作的连接条件中出现，则考虑在这个属性上建立索引
选择什么索引方法？
B+ Hash BitMap索引方法内容放在了下一章中。
散列在查找类似select * from student where id = 1这样的查询时更高效，平均查找时间为常数，而顺序索引（B+等）与表的大小成正比。顺序索引技术在指出了一个值范围的查询中比散列更可取（比如select * from student where id > 5）因为散列范围值通常在不同的桶中。
通常设计者会使用顺序索引（B+或B等），除非他预先知道将来不会频繁使用范围查询，在这种情况下使用散列。
聚簇存取方法
为了提高某个属性的查询速度，把这个属性上具有相同值的元组集中存放在连续的物理块中。该属性称为聚簇码。
聚簇能够大大提高按聚簇属性进行查询的顺序（oracle中有，好像mysql中没有，具体不是还不清楚）。此外，还可以多个关系组合聚簇（对应于多表聚簇文件组织）
适用范围：
当SQL语句中包含有与聚簇码有关的Order by，Group BY，Union，Distinct等子句或短语时，使用聚簇特别有利。适用于很少对基表进行增删该操作的情况。
局限：
- 维护代价相当大，对乙有关系建立聚簇，该关系的物理位置需要改变
- 只能提高某些特定应用的性能。
数据库实施和维护
定义数据库结构即DDL创建数据库结构。
数据装载包括ETL（一般的DBMS有相应的工具）：
- 数据抽取
- 数据转换
- 数据载入
数据库应用程序的设计应该与数据库设计并行进行，在数据库实施阶段编制与调试数据库的应用程序，在数据入库完成之前可以使用模拟数据。
数据装载入库并且应用程序调试好之后进行数据库试运行。主要工作包括：
- 功能测试
- 性能测试
数据装载不是一步完成的，应该是分期入库，同时进行程序调试和数据库试运行，在试运行阶段，系统不稳定，必须做好数据库的转储和恢复工作。
数据库的运行和维护阶段主要是由数据库管理员完成的。包括数据库的转储和恢复，即针对不同的应用要求制定不同的转储计划，定期对数据库和日志文件进行备份。另外对数据库的安全性、完整性控制，这是运维阶段的工作。在数据库运行过程中，数据库管理员必须监督系统运行，对监测数据进行分析，找出改进系统性能的方法。后面如果数据库性能下降，可能还会进行数据库的重组织和重构造。
设计人员
- 系统分析人员，负责需求分析和规范说明，确定系统硬件、软件配置。
- 数据库设计人员和数据库管理员， 负责数据库各级模式的设计，数据库的改进、重组、重构。必须参加需求调查和系统分析。很多情况下由数据库管理员担任。
- 应用开发人员 包括程序员和操作员 负责编制程序和准备软硬件环境并进行调试和安装
存储结构与索引
注意：这只是一般性的讨论，每个DBMS实现都不相同。
一个数据库被映射到多个不同的文件，这些文件由底层的操作系统来维护，这些文件永久地存在于磁盘上。一个文件在逻辑上组织称为记录的一个序列，这些记录映射到磁盘块上。因为文件由操作系统作为一种基本结构提供，所以我们将假定作为基础的文件系统是存在的。我们需要考虑用文件表示逻辑数据模型的不同方式。
每个文件分成定长的存储单元，称为块，块是存储分配和数据传输的基本单元。一个块可能包含很多条记录，此外，我们需要要求每个记录包含在单个块中。
定长记录和变长记录
在关系数据库中，不同关系的元组通常具有不同的大小，把数据库映射到文件可以使用定长记录或者变长记录，前者更容易实现。
定长记录
比如说一个表的字节数为53字节（当中没有变长的记录类型）。每条记录都是53字节，秉持一个记录只能在一个块中的规则，如果块有多余空间但不足一条记录，就让它空着。
在文件的开始处我们分配一定数量的字节作为文件头。文件头将包含有关文件的各种信息。我们需要在文件头中存储的只有内容被删除的第一个记录的地址，用这第一个记录来存储第二个可用记录的地址，以此类推。被删除的记录形成了空闲列表插入一条新记录时，我们使用文件头指向的记录，并改变文件头的指针以指向下一个可用记录。如果没有可用空间，将该新记录添加到文件末尾
变长记录
变长记录以下面几种方式出现在数据库系统中
- 多个关系表在一个文件中存储
- 允许一个或多个字段是变长的记录类型
- 允许记录类型中包含重复字段，如数组等
有多种变长记录的存储管理技术，这里仅介绍分槽页结构。分槽页结构一般用于在块中组织记录。如图所示。每个块的开始处有一个块头，块头中包含的信息有：1.块头中已存储的条目个数；2.块中空闲空间的末尾地址;3.条目数组，每个条目中存储了该条目所对应变长记录的大小和地址。
如果一条记录被删除，首先，它所占用的空间被释放，他所对应的条目被置成删除状态；其次，块中位于被删除记录左边的所有记录右移，使删除而被释放的空间集中到块的中部，所有被移动的记录所对应条目 的 地址值也需要跟着修改，使其指向记录的起始位置；最后，修改末尾地址的值，使它指向空闲空间的尾部，只要块中有空闲的空间，使用类似的技术可以使记录增长或缩短。 由于块的大小有限，典型为4KB,因此在块内移动记录的代价并不会太高。
文件中记录的组织
上面我们已经研究了如何在一个文件结构中表示记录，下一个问题就是如何在文件中组织这些记录，下面是在文件中组织记录的几种可能的方法：
- 堆文件组织。一条记录可以放在文件中的任何地方，只要那个地方有空间存放这条记录。记录是没有顺序的。通常每个关系使用一个单独的文件，通俗说就是没有规律的组织方式，随便放。（应该没有使用这种文件组织的DBMS）
- 顺序文件组织。记录根据搜索码的值顺序存储。
- 多表聚簇文件组织。几个不同的关系存储在同一个文件中，而且不同关系的记录存储在相同的块中，于是一个IO操作可从所有关系中取到相关的记录。例如两个关系做连接运算时相匹配的记录被认为是相关的。
- 散列文件组织。每条记录的某些属性上计算一个散列函数，散列函数的结果确定了记录应该放到文件的哪个块中。将数据文件中搜索码通过hash函数散列到桶上（通常一个桶就是一个磁盘块）
顺序文件组织
顺序文件是为了高效处理按某个搜索码的顺序排序的记录而设计的。搜索码是任何一个属性或者属性的集合。为了快速按搜索码的顺序获取记录，我们通过指针把记录链接起来。此外为了减少IO操作，在物理上尽可能按照搜索码顺序存储记录。顺序文件组织允许记录按排序的顺序读取。对于删除，我们可以使用前面的空闲列表来管理删除，对插入操作，规则如下：
- 按照搜索码顺序找到应该插入的块
- 如果该块中有空闲记录，就在这里插入新的记录，否则将记录插入到一个溢出块中，不管哪种情况，都要调整指针使得能按搜索顺序把记录链接在一起。
当插入操作很多时（溢出块变多），搜索码顺序和物理顺序之间的一致性可能完全丧失，在这种情况下，顺序处理将变得效率十分低下，此时文件应该重组，使得它再一次按物理顺序存放，重组的代价是很高的。
多表聚簇文件组织
多表聚簇文件组织是一种在每一块中存储两个或者更多个关系的相关记录的文件结构，这样的文件组织允许我们使用一次块的读操作来读取满足连接条件的记录。因此我们可以进行更高效的连接查询。何时使用多表聚簇依赖于数据库设计者所认为的最频繁的查询类型，多表聚簇的谨慎使用可以在查询处理中产生明显的性能提高。
索引
使用索引这种结构，查找一个ID为35526的元组，不需要读取整个关系，系统利用索引直接定位这些记录，取出该记录所在的块即可。
两种基本的索引分类如下（还有一些索引类型是数据库经常支持和使用的，比如B+树）：
- 顺序索引。顺序索引按顺序存储搜索码的值，并将每个搜索码与包含该搜索码的记录关联起来。
- 散列索引。将值平均分配到若干散列桶中，一个值所属的散列桶是由散列函数决定。
下面将展开关于索引的一些技术，每种技术的评价应该基于下面这些因素：
- 能有效支持的访问类型
- 访问时间
- 插入时间
- 删除时间
- 空间开销
注：这里对于书中索引与散列一章尽量简化了实现细节，我认为了解即可，达到能对别人讲解结技术的结构是什么就行了。
顺序索引
顺序索引按顺序存储搜索码的值，并将搜索码与记录关联起来。如果包含记录的文件按照某个搜索码指定的顺序排序，那么该搜索码对应的索引称为聚集索引（主索引），否则称为非聚集索引（辅助索引）。索引项由一个搜索码值和指向具有该搜索码值的一条或者多条记录的指针构成。指向记录的指针包括磁盘块号以及块内偏移量。辅助索引可以提高搜索码查询性能同时，增加了修改数据库的开销（因为必须是稠密索引），可以使用散列文件组织上构建辅助索引，这样的索引叫做散列索引。
关于顺序索引，进一步可以分为稠密索引和稀疏索引（辅助索引都是稠密索引，因为不按顺序存储 稀疏索引就没法工作了），表现为是否每一条记录对应一个索引，这里不展开。
B+树索引
B+树查询非常高效。关于B类型树有很多类型，有AVL、红黑树、B+树、B-树、B*树（关于其他这些树在我写的数据结构专栏里面文章有）。B+树索引是在数据插入和删除的情况下仍然能保持其执行效率的几种使用最广泛的索引结构之一。
mysql使用B+树索引，Oracle使用B树索引，很多人都在说其实没有B树，其实B树这个说法指的是这里本书提到的B树索引，也就是N叉搜索树的结构（消除了B+树值的冗余），但我们还是不能说二叉搜索树就是B树。
注：关于B+树，在数据结构中也有介绍，其实这种数据结构主要就是应用在数据库存储中的。
- 多分平衡树，存取效率高
- 既能随机查找，又能顺序查找
- 增删改查操作，树保持平衡
秩=3
所有的叶节点是在同一级上的
对数据{2,3,5,7,11,13,17,19,23,29,31,37,41,43,47}的存储如下
随机查找：
IO数为4（这个不代表图中的1234编号，图中标号仅表示查找步骤顺序），B树实际上是通过这样一个树结构从磁盘中拿取，存储了很多个这样的块，当需要时从磁盘中拿出，我们需要从磁盘中椅子拿出三个块，然后还需要拿取一次记录，所以一共是4次。如果我们将第1第2层节点保存在缓冲区，那么IO次数就是2（拿到31 37 41这个块 + 拿一次记录）
范围查找：
【38，45】范围查找，随机查找码值第一个大于38的然后在叶子结点中进行顺序查找，到比45大的位置停止
Hash索引
哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。
选择hash存取的原则：
如果一个关系的属性主要要出现在等值连接条件中（select *from emp,dept where emp.deptno=dept.deptno）或者主要出现在等值比较选择条件（select *from emp,dept where emp.deptno > 1）中，使用hash存取 。
索引带来的额外开销
- 维护索引的开销
- 查找索引的开销
- 存储索引的开销
位图索引（BitMap）
位图索引是一种针对多个字段的简单查询设计的一种特殊的索引。
select * from r where gender = ‘f’ and income_level = ‘L2’
图中有对gender和income_level的位图
为了计算这个查询，我们将这两个位图进行逻辑与。我们想要得到的结果应该是01000，我们找出所有符合条件的记录，然后在这些满足条件的记录里中查找来计算出查询结果。这样就不用扫描整个关系。
数据字典存储
一个关系数据库需要维护关系关系的数据，如关系的模式等，一般来说，这样的“关于数据的数据”称为元数据，这个与数据库设计中的数据字典不是同一个东西，这个是实实在在存储在数据库中的关系模式的信息。所有这些元数据信息组成了一个微型数据库，一些数据库系统使用专用的数据结构和代码来存储这些信息。通常人们更倾向于在数据库中存储关于数据库本身的数据（mysql就是这么做的，有一个系统数据库存储这些信息），简化了系统的总体结构，并且允许使用数据库的全部能力来对系统数据进行快速访问。
数据库缓冲区
数据库系统的一个主要目标就是尽量减少磁盘和存储器之间传输的块数目。减少磁盘访问次数的一种方法是在主存储器中保留尽可能多的块。这样做的目标 是最大化要访问的块已经在主存中的几率，这样就不再需要访问磁盘。缓冲区是主存储器中用于存储磁盘块拷贝的部分，负责缓冲区空间分配的子系统称为缓冲区管理器。
当数据库系统中的程序需要磁盘上的块时，它向缓冲区管理器发出请求，如果这个块已经在缓冲区中，缓冲区管理器将这个块在主存中的地址传给请求者，如果不在，首先为这个块在缓冲区分配空间（可能发生置换，绝大多数DBMS使用LRU置换算法），然后缓冲区管理器把请求的块从磁盘读入缓冲区，并将在主存中的地址传给请求者。
- 被钉住的块，为了使数据库系统能够从系统崩溃中恢复（之后恢复为单独一章内容），限制一个块写回磁盘的时间是十分必要的，当一个块上的更新操作正在进行，不允许将该块协会磁盘，这种块被称为被钉住的块。
- 块的强制写出，某些情况下，尽管不需要一个块所占用的缓冲区空间，但必须将它写回磁盘。因为发生崩溃时进行强制写出，主存储器的内容包括缓冲区的内容在崩溃时将丢失，而磁盘上的数据一般在崩溃时得以保留。
关系查询处理和优化
SQL处理过程
对查询语句进行词法分析和语法分析，进一步进行有效性检查、视图转换、安全性检查、完整性检查，生成查询树，然后进行查询优化（包括代数优化、物理优化等），优化的结果是生成查询执行计划，代码生成器最后生成可执行代码。
举个例子
select sname from student where sno = “20100017” 该语句经历下面四个阶段的过程
查询分析
对查询语句进行扫描、进行词法分析和语法分析（和编译器的原理的一样的）
词法分析：从查询语句识别出这种正确的语言符号
-
保留字：select from where
-
变量：sname student sno
-
常量：“20100017”
-
运算符： =
语法分析：按照SQL语言的句法解释查询语句，错写为下面语句，when在词法分析中当做变量，在进行语法分析的时候这个地方应该为where，但是却是一个变量when，故检查出错误。
select sname from student when sno = "20100017"
查询检查
- 有效性检查：检查语句中的数据库对象，如关系名、属性名是否存在和有效，根据数据字典中有关的模式定义信息进行检查。
- 视图转换：是在查询视图时需要进行的过程，一般查询时不经历此过程。把对视图的操作转换为对基本表的操作。
- 安全性检查：根据数据字典中的用户权限对用户的存取权限进行检查
- 完整性检查：根据数据字典中存储的完整性约束定义，对句子进行检查（比如主键约束、外键约束、唯一约束等等），在这个例子中，如果sno 是char(8)，那么 where sno = "20100017"是错误的
在查询检查结束后，生成语法分析树，类似下图查询语句
select Sname from Student join SC using(Sno) where SC.Cno = '2'
查询优化
查询优化是关系数据库成功的关键因素之一，早年的关系数据库性能不高备受诟病，争是由于查询优化技术的进步，才使得关系数据库走向产品。查询优化是选择一个高效的查询处理策略。
优化分类（两者结合进行）：
- 代数优化：对关系代数表达式的优化
- 物理优化：存取路径和底层操作算法的选择
查询执行
根据优化器得到的执行策略生成查询执行计划，代码生成器生成查询执行计划的代码，有两种执行方式，自顶向下和自底向上
查询操作算法
理解查询操作有利于我们理解查询优化（主要是物理优化）的过程
选择操作实现
- 全表扫描：对查询的基本表顺序扫描，逐一检查每个元组是否满足选择条件，把满足条件的元组作为结果输出。适合小表，不适合大表
- 索引扫描方法：适合于选择条件中的属性上有索引（B+或者Hash）。
需要考虑选择的具体情况选择是全表扫描还是索引扫描（多条件的索引又可分为合并条件还是逐一考虑这些条件）这种选择的规则将在查询优化中的物理优化处说明
C1:无条件，采用全表扫描
C2：Sno = “201215121” 结果集小，如果为索引（或散列），使用索引扫描
C3：Sage > 20 结果集如果太大，使用全表扫描，如果结果集不大而且也有B+索引，那就用索引（散列不适合范围查找）
C4：Sdept = ‘CS’ and Sage > 20 如果两个都是索引，可以使用两个索引指针的交集，然后用该交集去获取结果集。或者先通过Sdept的索引找出指针，然后通过这些指针到student表中检索，对得到的元组检查条件Sage>20是否满足。多个条件的情况下比较复杂，会分别考虑每个条件再合并结果，也可能逐一考虑这些条件，甚至太复杂的情况直接扫描全表。
连接操作实现
- 嵌套循环算法：参与连接的两个表分别作为外循环和内循环，并且检查两个元组在连接属性上是否相等，比如select Sname from Student join SC using(Sno) Student可以作为外循环，SC作为内循环，循环过程中判断是否Sno是否相等，满足条件，则串接后作为结果输出
- 排序合并算法：将要连接的两个表按照连接属性进行排序，取Student表中第一个Sno，依次扫描SCI表中具有相同Sno的元组，扫描到Sno不同的元组后，返回Student表扫描它的第2个元组，再从原来结束位置开始扫描SC。Student和SC表都只用扫描一遍。相比嵌套循环额外的开销是排序（对于大表，排序是值得的）。
- 索引连接算法：对于Student每一个元组，由Sno值通过SC的索引查找相应的SC元组（SC上有索引即可）。这样只需要扫描Student一次即可。
- hash Join算法：连接属性为hash码，用hash函数把较小的表（Student）元组散列到桶（内存中的桶）中，然后将较大的表按照相同的hash函数在响应的桶中匹配。
查询优化
集中式数据库的IO代价是最主要的（代价主要是IO代价、CPU处理时间、内存空间），分布式数据库比集中式数据库要多考虑一个通信代价（IO代价和通信代价是最主要的）。
案例
通过一个案例来说明说明数据库代数优化和物理优化，后面再用两节来说明数据库如何进行代数优化和物理优化。
情况1
情况2
情况3
索引
代数优化
理想中的代数优化是找出所有的优化表达式在其中选出最优的，现实中的代数优化是朝着一个方向（或者说规则）优化 ，不是最优，但是较优（类似DFS算法思想）
代数优化需要解决两个问题：关系代数表达式的等价变换原则（不介绍）和启发式规则（即什么样的变换是好的，主要介绍一下这个）
典型的启发式规则
- 选择运算应尽可能先做
- 选择运算之后放入中间文件之前进行投影
- 连接运算之后放入中间文件之前进行投影
- 选择和笛卡尔积结合起来，通常是不会计算笛卡尔积的，因为代价太高了，总是在连接时去选择（即只把符合连接选择条件的放到中间文件中）
- 将结果不是很大的子表达式结果放到中间文件中重复使用，比如视图实例化。
物理优化
物理优化有基于规则的启发式优化（经验规则）和基于代价估算的优化，常常是先通过启发式优化选择一些候选方案，然后对这些候选方案进行代价估算，从而选出最佳方案。
启发式优化
选择操作的启发式规则：
- 对于小关系，使用全表顺序扫描，即使有索引
- 对于大关系
-
- 对于选择条件是（主码 = 值）的查询，此时查询结果只有一个值，使用索引
- 对于选择条件是（非主属性 = 值）并且该选择列上有索引的查询，需要估算查询结果的元组数目，如果比例较小（<10%）使用索引，否则使用全表顺序扫描。
- 对于选择条件是属性上的非等值查询或者范围查询并且选择列上有索引，方法同上一条
- 对于用AND连接的多选择条件，组合索引优先，如果是分别索引，指针交集或者一个索引查找元组，然后判断这些元组是否满足剩余条件（这个之前在查询操作算法一节中也提到了），其他情况使用全表扫描。
- 对于OR连接的使用全表扫描
连接操作的启发式规则：
- 如果两个表都按照连接属性排序，选用排序合并算法
- 如果一个表在连接属性上有索引，使用索引连接算法
- 如果上面两个规则都不适用，其中一个表较小，使用Hash join算法。
- 嵌套循环方法，选择较小的表作为外循环
代价估算优化
代价估算优化需要一些统计信息并根据这些信息来进行估算代价。
对基本表：
- 元组总数
- 元组长度
- 占用块数
- 占用溢出块数
对表的每个列：
- 该列不同值的个数
- 列最大值最小值
- 是否有索引
- 哪种索引（B+、Hash、聚簇）
- 每个值的选择率（具有该值的元组数/元组总数）
对索引：
- 索引层数
- 不同索引值个数
- 索引的选择基数S
- 索引叶节点数
注：具体计算就不举例了，感觉也用不到。
不要指望将优化的任务全部交给数据库，应该根据优化特定写出适合数据库优化的SQL语句，SQL调优需要工程师有很高的水平。
并发控制
事务执行方式：
- 串行执行
每个时刻只有一个事务运行，其他事务必须等到这个事务结束以后方能运行。不能充分利用系统资源，发挥数据库共享资源的特点。
- 交叉并发
事务的并行操作轮流交叉运行。这是单处理机中方式。
- 同时并发
多处理机系统，每个处理机运行一个事务，多处理机同时运行多个事务，实现真正的并行运行，前提是必须要在多处理机上运行。
并发带来的问题
违反隔离性，进而破坏了一致性（隔离是保证一致性的手段）。
并发操作带来的数据不一致性可以分为三类：
-
丢失修改
（写-写）T1读数据，T2读相同数据，T1修改，T2修改，T1修改丢失。（PS：我觉得也是不可重复读的一种，如果不能重复读不会发生丢失修改）
-
不可重复读
（读-修改）T1读取数据后，T2执行读相同数据并更新数据，T1此时读取的数据与数据库不一致
-
幻读
（读-删除）T1读取某些数据，T2删除其中部分记录，T1中数据与数据库不一致（读-插入）或者T1读取某些数据记录，T2插入一些记录，T1中数据与数据库不一致。
-
脏读
（修改-读）T1修改数据并写回磁盘，T2读取同一个数据，T1由于某种原因被rollback，T2读到的数据就与数据库中数据不一致，读到的就是脏数据。
并发控制就是要用正确的方式调度并发操作，保证事务隔离性，从而避免造成数据的不一致性。但对数据库的应用有时允许某些不一致性，可以减少系统开销。除此之外还需要保证事务是可串行化的，数据一致性+事务可串行化 = 正确并发控制。
并发控制的主要技术（这里主要讲封锁技术）
- 封锁（Locking）
- 时间戳（Timestamp）
- 乐观控制法
- 多版本并发控制（MVCC）
封锁
（同操作系统处理进程通信类似）事务在对某个数据对象操作前，对其进行加锁。在释放锁之前，其他事务不能更新该数据对象，事务对某个数据对象加锁后有什么样的控制由锁的类型决定。
基本锁类型
- 排它锁 （X锁） T自己可以读取和修改，其他事务不能读取和修改。不能继续加S锁、X锁。
- 共享锁 （S锁） T自己可以读，但不能修改。其他事务也可以读，且可以继续加S锁，但不能再加X锁，直到T释放S锁。保证了事务T读时其他事务可以读，但不能修改。
封锁协议
分为三级封锁协议，不同级别的封锁协议达到的数据库一致性程度是不同的（加什么锁、锁多久）。
一级封锁协议：
事务T在修改数据R之前必须加X锁直到事务结束释放。正常结束commit，非正常结束rollback。
一级封锁协议可以解决丢失修改问题，即同一个数据，只能允许一个事务内修改。
允许读，所以没有解决不可重复读、脏读问题，锁的范围仅仅对记录修改加锁、无法解决幻读问题。
二级封锁协议：
在一级封锁基础上，事务T读取数据R之前必须加S锁，读完释放。
二级封锁协议可以解决丢失修改和脏读问题，丢失修改一级封锁已经解决，主要看脏读，T1修改数据R（加X锁）写入数据库，X锁上不能加S锁，所以T2不能读。
读完就释放，仍然无法解决不可重复读（读-修改问题）和幻读（读-删除、新增问题）
三级封锁协议：
在一级封锁基础上，事务T在读取数据R之前必须先对其加S锁，直到事务结束释放。
三级封锁协议可以解决丢失修改、脏读、不可重复读。因为三级封锁协议可以看做S锁变成长锁，所以能解决丢失修改和脏读。T1读（加S锁）后直到事务结束才释放，所以不会有重复读问题。
但是如果采用的是是表级锁粒度低的锁不能解决幻读，表级锁可以解决幻读。
活锁与死锁
(同操作系统处理通信中活锁死锁基本一致)
活锁
最简单的方法是使用FCFS可以解决。
死锁
操作系统中对于死锁有4中策略，数据库使用的是死锁的诊断与解除（其他不好实现），这里诊断相比操作系统多一个超时法，解除方法和操作系统也不一致。
诊断：
- 超时法：等到时间超过设定时间（有可能误判死锁）
- 等待图法：同操作系统内容。
解除：
选择处理死锁代价最小的事务，将其撤销，即释放了此事务持有的所有锁，使其他事务能继续运行，之后再进行事务恢复
可串行化调度
通过加锁保证一致性不能解决并发控制吗？
通过加锁只能保证对于同一个数据两个事务之间干扰的问题，无法解决多个数据两个事务之间的干扰问题，所以需要可串行化调度。（类似操作系统中使用信号量来保证可串行化）
冲突可串行化调度，冲突可串行化调度是更严格的可串行化调度，通过保证冲突操作次序不变，交换两个事务不冲突操作次序可以得到冲突可串行化调度。
再来分析一下上面的不可串行化调度和串行化调度，在串行调度中冲突操作次序应该是S(B) X(A)S(A)X(B)，或者S(A)X(B)S(B) X(A)。在第三张图中，冲突操作次序是S(B)S(A)X(A)X(B),与串行化调度次序不同。（注意：这只能说明不是冲突可串行化调度，不能说明是可串行化调度）在第4张图中，顺序为S(B)X(A)S(A)X(B)，与串行调度一致，一定是冲突可串行化调度。
冲突可串行化调度用于判断其正确性比较合适，但不适合实现可串行性，DBMS普遍采用两段锁协议实现并发调度可串行性
两段锁协议：遵循两段锁协议的调度是可串行化调度的充分条件，可能发生死锁。
遵守三级封锁协议一定遵守两段锁协议
封锁粒度
封锁的对象可以是逻辑单元或者物理单元，封锁粒度与系统的并发度和并发控制的开销密切相关。应该根据封锁开销和并发度两个因素，适当选择封锁粒度，封锁粒度太大，并发度低，封锁粒度太小，开销大，应该在两者之间力求平衡。
- 逻辑单元：属性值、属性值的集合、元组、关系、索引项、整个索引、整个数据库等
- 物理单元：索引页、数据页、物理记录等。
选择封锁粒度
- 需要处理大量元组的用户事务，以关系为封锁粒度
- 需要处理多个关系的大量元组的用户事务，以数据库为封锁单位
- 处理少量元组的用户事务，以元组为封锁单位
多粒度封锁
多粒度树
- 以树形结构来表示多级封锁粒度
- 根节点是整个数据库，表示最大的数据粒度
- 叶节点表示最小的数据粒度
多粒度封锁协议
- 允许独立度中的每个节点被独立加锁
- 子节点继承父节点的锁，加在本身的锁叫做显式封锁，继承的锁叫做隐式封锁，二者效果是一样的。
- 系统检查封锁冲突时既要检查所有祖先节点（隐式封锁是否冲突），检查其自身（显式封锁是否冲突），又要检查所有下级结点（隐式封锁是否冲突）
显然这样的检查效率很低
意向锁
意向锁是对多粒度封锁的改进，提高加锁时的检查效率，多粒度锁和意向锁是除了X和S两种基本锁之外的锁。意向锁广泛应用与DBMS中
- 如果对一个节点加意向锁，则说明该节点的下层节点正在被加锁
- 对任一节点加基本锁，必须先对它的所有祖先节点加意向锁
比如对元组基本锁，必须先对数据库及其祖先关系加意向锁。
常用意向锁：IS（Intent Share）锁、IX（Intent Exclusive）锁、SIX（Share Intent Exclusive）锁
IS锁
比如如果想对下图黄圈元组加S锁，必须对关系R1、数据库加IS锁
IX锁
比如如果想对下图黄圈元组加X锁，必须对关系R1、数据库加IX锁
SIX锁
比如事务读关系R1并更新它的元组，先对关系R1祖先加IS和IX锁，关系R1加SIX锁（表示对R1加S锁），该元组加X锁。
数据锁相容矩阵
锁的强度
以强锁代替弱锁是安全的，反之则不然
申请封锁应该自上而下，释放封锁应该自下而上。
1134
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
