数据库学习汇总

最新推荐文章于 2024-04-26 13:29:49 发布

燕南来

最新推荐文章于 2024-04-26 13:29:49 发布

阅读量3.6k

点赞数 4

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_43642752/article/details/126289672

版权

mysql 专栏收录该内容

1 篇文章 2 订阅

订阅专栏

1,数据库概念

数据库，就是存放数据的仓库

数据（Data）：实际上指的是描述事物的符号记录

数据库（DataBase，简称DB）是长期存储在计算机内部有结构的、大量的、共享的数据集合。

长期存储：持久存储有结构：
类型：数据库不仅可以存放数据，而且存放的数据还是有类型的
关系：存储数据与数据之间的关系
大量：大多数数据库都是文件系统的，也就是说存储在数据库中的数据实际上就是存储在磁盘的文件中
共享：多个应用程序可以通过数据库实现数据的共享

数据库管理系统(Database Management System , 简称DBMS)：数据库管理系统可以对数据库的建立、使用和维护进行管理。

常见的数据库管理系统有：

Mysql
PostgreSQL
SQL Server
Access
Sybase
SQLite

数据库系统（Database System，简称DBS）是指在计算机应用系统中引入数据库后的系统构成。数据库系统由数据库、数据库管理系统（及其开发工具）、应用系统、数据库管理员（和用户）构成。

数据库应用系统(DataBase Application System , 简称DBAS)：数据库应用系统是指系统开发人员利用数据库系统资源开发出来的，面向某一类实际应用的应用系统软件。

数据库管理员（Database Administrator，简称DBA）有重新组织数据的能力，能改变数据的存储结构或数据存储位置。

2,关系型数据库和非关系型数据库

关系型数据库

关系型数据库，采用了关系模型来组织数据的存储，以行和列的形式存储数据并记录数据与数据之间的关系 —— 将数据存储在表格中，可以通过建立表格与表格之间的关联来维护数据与数据之间的关系。
非关系型数据库

非关系型数据库，采用键值对的模型来存储数据，只完成数据的记录，不会记录数据与数据之间的关系。

在非关系型数据库中基于其特定的存储结构来解决一些大数据应用的难题。

NoSQL(Not only SQL)数据库来指代非关系型数据库。

3,SQL结构化查询语句

3.1, SQL概述

SQL（Structured Query Language）结构化查询语言，用于存取、查询、更新数据以及管理关系型数据库系统

SQL发展：

SQL是在1981年由IBM公司推出，一经推出基于其简洁的语法在数据库中得到了广泛的应用，成为主流数据库的通用规范
SQL由ANSI组织确定规范
在不同的数据库产品中遵守SQL的通用规范，但是也对SQL有一些不同的改进，形成了一些数据库的专有指令，如限制查询数据条数：
MySQL：limit
SQLServer : top
Oracle：rownum

SQL分类：根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类

DDL（Data Definition Language）数据定义语言
- 用于完成对数据库对象（数据库、数据表、视图、索引等）的创建、删除、修改

DML（Data Manipulation Language）数据操作/操纵语言
用于完成对数据表中的数据的添加、删除、修改操作
添加：将数据存储到数据表
删除：将数据从数据表移除
修改：对数据表中的数据进行修改

DQL（Data Query Language）数据查询语言
用于将数据表中的数据查询出来

DCL（Data Control Language）数据控制语言
用于完成事务管理等控制性操作

3.2,MySQL中SQL的基本语法

在MySQL Command Line Client 或者Navicat等工具中都可以编写SQL指令

SQL指令不区分大小写
每条SQL表达式结束之后都以 ; 结束
SQL关键字之间以空格进行分隔
SQL之间可以不限制换行（可以有空格的地方就可以有换行）
MySQL注释：
单行注释： – 注释内容
单行注释： #注释内容
多行注释： /* 注释内容 */

3.3,DDL数据定义语言(数据库操作)

使用DDL语句可以创建数据库、查询数据库、修改数据库、删除数据库

3.3.1,查询数据库

显示当前mysql中的数据库列表
show databases;

显示指定名称的数据库的创建的SQL指令
show create database 库名;

3.3.2,创建数据库

创建数据库
create database 库名;

创建数据库，当指定名称的数据库不存在时执行创建
create database if not exists 库名;

在创建数据库的同时指定数据库的字符集（字符集：数据存储在数据库中采用的编码格式 utf8、gbk）
create database 库名 character set 字符集;

3.3.3,修改数据库信息

修改数据库的字符集
alter database 库名 character set 字符集;

3.3.4,删除数据库

删除数据库
drop database 库名;

如果数据库存在则删除数据库
drop database is exists 库名;

3.3.5,使用/切换数据库

use 库名;

3.4,DDL数据定义语言(数据表操作)

3.4.1,创建表

数据表实际就是一个二维的表格，一个表格是由多列组成，表格中的每一列称之为表格的一个字段

语法
create table 库名.表名（
字段名1 类型[(宽度)约束条件],
字段名2 类型[(宽度)约束条件],
字段名3 类型[(宽度)约束条件]
);

注意

最后一个字段之后不能加逗号
.在同一张表中，字段名不能相同
宽度和约束条件可选，字段名和类型是必须的
如果创建表时不指定库名,则该表默认建立在当前查询所在的库下

约束条件
是在数据类型之外对字段附加的额外限制

常见约束
not null 非空
default 默认值
auto_increment 自增长
primary key 主键 非空且唯一

3.4.2,查询该数据库下的所有表

show tables;

3.4.3,查询指定表的表结构

desc 表名;

3.4.4,删除指定的表

删除数据表
drop table 表名;
当数据表存在时删除数据表
drop table if exists 表名;

3.4.5,修改表

修改表名
alter table 旧表名 rename to 新表名;
修改表的字符集，表的字符集默认和数据库一致
alter table 表名 character set 字符集;
添加列（字段）
alter table 表名 add 列名 类型;
修改列（字段）的列表和类型
alter table 表名 change 旧列名 新列名 类型;
只修改列（字段）类型
alter table 表名 modify 列名 类型;
删除列（字段）
alter table 表名 drop 列名;

3.5,MySQL的数据类型

数据类型，指的是数据表中的列中支持存放的数据的类型

3.5.1,数值类型

类型	内存空间	范围	说明
tinyint	1byte	有符号 -128~127 无符号 0~255	特小型整数
smallint	2byte	有符号 -32768 ~ 32767 无符号 0~65535	小型整数
mediumint	3byte	有符号 -2^23 ~ 2^23 - 1 无符号 0~2^32-1	中型整数
int/integer	4byte		整数
bigint	8byte		大型整数
float	4byte		单精度小数
double	8byte		双精度数字
decimal	(第一参数+2)byte		decimal(10,2) 表示数值一共有10位其中小数位有2位

3.5.2,字符串类型

类型	字符长度	说明
char	0~255 字节	定长字符串，最多可以存储255个字符 ;当我们指定数据表字段为char(n) 此列中的数据最长为n个字符，如果添加的数据少于 n，则补’\u0000’至n长度
varchar	0~65536 字节	可变长度字符串，此类型的类最大长度为65535
tinyblob	0~255 字节	存储二进制字符串
blob	0~65535 字节	存储二进制字符串
mediumblob	0~1677215 字节	存储二进制字符串
longblob	0~4294967295 字节	存储二进制字符串
tinytext	0~255 字节	文本数据（字符串）
text	0~65535 字节	文本数据（字符串）
mediumtext	0~1677215 字节	文本数据（字符串）
longtext	0~4294967295 字节	文本数据（字符串）

3.5.3,日期类型

在MySQL数据库中，我们可以使用字符串来存储时间，但是如果我们需要基于时间字段进行查询操作（查询在某个时间段内的数据）就不便于查询实现

类型	格式	说明
date	yyyy-MM-dd	日期，只存储年月日
time	hh-mm-ss	时间，只存储时分秒
year	yyyy	年份
datetime	yyyy-MM-dd hh-mm-ss	日期+时间，存储年月日时分秒
timestamp	yyyyMMddhhmmss	日期+时间（时间戳）

3.6,字段约束

在创建数据表的时候，指定的对数据表的列的数据限制性的要求（对表的列中的数据进行限制）

保证数据的有效性
保证数据的完整性
保证数据的正确性

约束的分类有：

非空约束（not null）：限制此列的值必须提供，不能为null
唯一约束（unique）：在表中的多条数据，此列的值不能重复
主键约束（primary key）：非空+唯一，能够唯一标识数据表中的一条数据
外键约束（foreign key）：建立不同表之间的关联关系

3.6.1,非空约束

限制数据表中此列的值必须提供

3.6.2,唯一约束

在表中的多条数据，此列的值不能重复

3.6.3,主键约束

主键：就是数据表中记录的唯一标识，在一张表中只能有一个主键（主键可以是一个列，也可以是多个列的组合）

当一个字段声明为主键之后，添加数据时：

此字段数据不能为null
此字段数据不能重复

创建表时添加主键
create table student(
	sid int primary key,
	sname varchar(20),
	sgander char(4)
);
create table teacher(
	tid int,
	tname varchar(20),
	tgander char(4),
	primary key(tid)
);
删除数据表主键约束
alter table student drop primary key;
创建表之后添加主键约束
alter table student modify sid int primary key;

3.6.4,字段自增

在mysql创建表时可将类型为int的字段设置为自动增长

如创建学生表将学会设置为主键,且主键自动增长
create table student(
	sid int primary key  auto_increment,
	sname varchar(20),
	sgander char(4)
);
注意:

自动增长从1开始，每添加一条记录，自动的增长的列会自动+1，当我们把某条记录删除之后再添加数据，自动增长的数据也不会重复生成（自动增长只保证唯一性、不保证连续性,从表中最大的向后增长）

3.6.5,联合主键

联合主键：将数据表中的多列组合在一起设置为表的主键

create table teacher(
	tid int,
	tname varchar(20),
	tgander char(4),
	primary key(tid,tname)
);

3.6.6,外键约束

外键约束

3.7,DML数据操作语言

用于完成对数据表中数据的插入、删除、修改操作

3.7.1,插入数据

语法：

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

3.7.2,删除数据

语法：

delete from 表名 [where 条件]

3.7.3,修改数据

语法：

update 表名 set 列名1=值1，列名2=值2 [where 条件];

3.8,DQL查询数据

3.8.1,基础查询

语法:

select 列名1,列名2,… from 表名 [where 条件];

如果要显示查询到的记录的所有列，则可以使用 * 替代字段名列表（不推荐）

3.8.2,where⼦句

在删除、修改及查询的语句后都可以添加where⼦句（条件），⽤于筛选满⾜特定的添加的数据进⾏删除、修改和查询操作。
delete from 表名 where 条件;
update 表名 set ... where 条件;
select ... from 表名 where 条件;
条件关系运算符

= 等于

!= <> 不等于

> 大于

< 小于

>= ⼤于等于

<= ⼩于等于

between and 区间查询 between v1 and v2 [v1,v2]

条件逻辑运算符

and 并且筛选多个条件同时满⾜的记录

or 或者筛选多个条件中⾄少满⾜⼀个条件的记录

not 取反

3.8.3,like子句

在where⼦句的条件中，我们可以使⽤like关键字来实现模糊查询

语法：
select * from 表名 where 列名 like 'reg'; 
在like关键字后的reg表达式中

% 表⽰任意多个字符

_ 表⽰任意单个字符

3.8.4,计算列

对从数据表中查询的记录的列进⾏⼀定的运算之后显⽰出来
可用运算符包括 +(加) -(减) *(乘) /(除) %(取余)

SELECT 9+2,9-2,9*2,9/2,9%2;
+-----+-----+-----+--------+------+
| 9+2 | 9-2 | 9*2 | 9/2    | 9%2  |
+-----+-----+-----+--------+------+
|  11 |   7 |  18 | 4.5000 |    1 |
+-----+-----+-----+--------+------+
as字段起别名

为查询结果的列名去⼀个语义性更强的别名 (as 关键字也可以省略)
select 字段1 as 别名1, 字段2 别名2... from 表名;
distinct消除重复⾏

从所选字段的查询的结果中将所有字段完全重复的记录消除
select distinct 字段名 from 表名;
排序order by

将查询到的满⾜条件的记录按照指定的列的值升序/降序排列
select * from 表名 where 条件 order by 列名 asc|desc;
order by 列名表⽰将查询结果按照指定的列排序
asc 按照指定的列升序（默认）
desc 按照指定的列降序

3.8.5,聚合函数

SQL中提供了⼀些可以对查询的记录的列进⾏计算的函数

count
count() 统计函数，统计满⾜条件的指定字段值的个数（记录数）

max
max() 计算最⼤值，查询满⾜条件的记录中指定列的最⼤值
min
min()计算最⼩值，查询满⾜条件的记录中指定列的最⼩值
sum
sum() 计算和，查询满⾜条件的记录中指定的列的值的总
avg
avg() 求平均值，查询满⾜条件的记录中计算指定列的平均值

3.8.6,⽇期函数和字符串函数

⽇期函数

当我们向⽇期类型的列添加数据时，可以通过字符串类型赋值（字符串格式必须为：yyyy-MM-dd hh:mm:ss）

我们可以通过以下函数获取当前系统时间：

now()

sysdate()

字符串函数

通过SQL指令对字符串进⾏处理

concat(colnum1,colunm2,...)拼接多列

upper(column) 将字段的值转换成⼤写

lower(column) 将指定列的值转换成⼩写

substring(column,start,len) 从指定列中截取部分显⽰ start从1开始

trim(str) 去除字符串中首尾的空格

3.8.7,分组函数group by

分组：就是将数据表中的记录按指定的列进⾏分组

语法:
select 分组字段/聚合函数
from 表名
[where 条件]
group by 分组列名 [having 条件]
[order by 排序字段]
select后通常显⽰分组字段和聚合函数(对分组后的数据进⾏统计、求和、平均值等)

语句执行顺序:

先根据where条件从数据库查询记录
group by对查询记录进⾏分组
执⾏having对分组后的数据进⾏筛选

3.8.8,分⻚查询limit

当数据表中的记录⽐较多的时候，如果⼀次性全部查询出来显⽰给⽤⼾，⽤⼾的可读性/体验性就不太好，因此我们可以将这些数据分⻚进⾏展⽰。

语法:
select ...
from ...
where ...
limit param1,param2;
param1：表⽰获取查询语句的结果中的第⼀条数据的索引（索引从0开始）

param2：表⽰获取的查询记录的条数（如果剩下的数据条数

3.9,数据表的关联关系

3.9.1,关联关系介绍

MySQL是⼀个关系型数据库，不仅可以存储数据，还可以维护数据与数据之间的关系⸺通过在数据表中添加字段建⽴外键约束。

数据与数据之间的关联关系分为四种：

⼀对⼀关联
⼀对多关联
多对⼀关联
多对多关联

3.9.2,一对一关联

一张表的一条记录一定只能与另外一张表的一条记录进行对应，反之亦然。

⼈ --- ⾝份证 ⼀个⼈只有⼀个⾝份证、⼀个⾝份证只对应⼀个⼈
学⽣ --- 学籍 ⼀个学⽣只有⼀个学籍、⼀个学籍也对应唯⼀的⼀个学⽣
⽤⼾ --- ⽤⼾详情 ⼀个⽤⼾只有⼀个详情、⼀个详情也只对应⼀个⽤⼾

3.9.3,一对多与多对一

一张表中有一条记录可以对应另外一张表中的多条记录；但是反过来，另外一张表的一条记录只能对应第一张表的一条记录，这种关系就是一对多或多对一。
班级 --- 学⽣
图书 --- 分类
商品 ---- 商品类别

3.9.4，多对多关联

一对表中（A）的一条记录能够对应另外一张表（B）中的多条记录；同时B表中的一条记录也能对应A表中的多条记录
学⽣ --- 课程
订单 --- 商品

3.9.5,外键约束

将⼀个列添加外键约束与另⼀张表的主键进⾏关联后，这个外键约束的列添加的数据必须要在关联的主键字段中存在，保证了数据的完整性
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名)

3.9.6,外键约束-级联

当存在外键关系时，只要存在对主表某一主键的引用时，就无法对该主键进行删除和更改

但是可通过级联操作来进行删除和修改

#添加外键约束，同时添加级联更新  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE;
#当主表中的主键进行更新时，所有引用该主键的表也会进行更新

#添加外键约束，同时添加级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON DELETE CASCADE;
#主表的主键进行删除时，所有引用该主键的记录（表中的一行即为一个元组，或一条记录）将会被删除。

#添加外键约束，同时添加级联更新和级联删除  标准语法：
ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 主表名(主键列名) 
ON UPDATE CASCADE ON DELETE CASCADE;

3.10，连接查询

在MySQL中可以使⽤join实现多表的联合查询⸺连接查询，join按照其功能不同分为三个操作：

inner join 内连接
left join 左连接
right join 右连接

3.10.1,内连接查询inner join

#语法：
select ... from 表1 inner join 表2 on 匹配条件 [where 筛选条件];

3.10.1.1,笛卡尔积

笛卡尔积：使⽤A中的每个记录⼀次关联B中每个记录，笛卡尔积的总数=A总数*B总数
如果直接执⾏ select … from 表1 inner join 表2; 会获取两种数据表中的数据集合的笛卡尔积（依次使⽤表1中的每⼀条记录去匹配表2的每条数据）

3.10.1.2,内连接条件

两张表时⽤inner join连接查询之后⽣产的笛卡尔积数据中很多数据都是⽆意义的，我们可以添加两张进⾏连接查询时的条件来消除⽆意义的数据

使⽤ on 设置两张表连接查询的匹配条件，查询结果只获取两种表中匹配条件成⽴的数据，任何⼀张表在另⼀种表如果没有找到对应匹配则不会出现在查询结果中
# 使⽤where设置过滤条件：先⽣成笛卡尔积再从笛卡尔积中过滤数据（效率很低）
select * from A INNER JOIN B where A.id = B.id;
# 使⽤ON设置连接查询条件：先判断连接条件是否成⽴，如果成⽴两张表的数据进⾏组合⽣成⼀条结果
记录
select * from A INNER JOIN B ON A.id = B.id;

3.10.2,左连接leftjoin

左连接：显⽰左表中的所有数据，如果在有右表中存在与左表记录满⾜匹配条件的数据，则进⾏匹配；如果右表中不存在匹配数据，则显⽰为null
# 语法
select * from A LEFT JOIN B ON 匹配条件 [where 条件];

3.10.3,右连接rightjoin

右连接：显⽰右表中的所有数据，如果在有左表中存在与右表记录满⾜匹配条件的数据，则进⾏匹配；如果左表中不存在匹配数据，则显⽰为null
# 语法
select * from A RIGHT JOIN B ON 匹配条件 [where 条件];

3.10.4,数据表别名

如果在连接查询的多张表中存在相同名字的字段，我们可以使⽤ 表名.字段名 来进⾏区分，如果表名太⻓则不便于SQL语句的编写，我们可以使⽤数据表别名

3.10.5,⼦查询/嵌套查询

⼦查询----先进⾏⼀次查询，第⼀次查询的结果作为第⼆次查询的源/条件（第⼆次查询是基于第⼀次的查询结果来进⾏的）

⼦查询返回单个值（单⾏单列）

# 如果⼦查询返回的结果是⼀个值（单列单⾏），条件可以直接使⽤关系运算符（= != ....）
#teacher 表中属性tid,name   student中属性 sid,sname,tid
select * from student where tid = (select tid from  teacher where
tname='张三');

⼦查询返回多个值（多⾏单列）

# 如果⼦查询返回的结果是多个值（单列多⾏），条件使⽤IN / NOT IN
select * from teacher where tid IN (select tid from student where
name LIKE '张%');

⼦查询返回多个值（多⾏多列）

#先查询学生表中tid=1的所有学⽣信息，将这些信息作为⼀个整体虚拟表(多⾏多列)，
#再基于这个虚拟表查询性姓张的学⽣信息（‘虚拟表’需要别名）
select * from (select * from students where cid=1) t where sname like "张%";

3.11,视图

3.11.1,概念

视图，就是 由数据库中⼀张表或者多张表根据特定的条件查询出得数据构造成得 虚拟表

3.11.2,视图的作⽤

安全性：如果我们直接将数据表授权给⽤⼾操作，那么⽤⼾可以CRUD数据表中所有数据，加⼊我们想要对数据表中的部分数据进⾏保护，可以将公开的数据⽣成视图，授权⽤⼾访问视图；⽤⼾通过查询视图可以获取数据表中公开的数据，从⽽达到将数据表中的部分数据对⽤⼾隐藏。

简单性：如果我们需要查询的数据来源于多张数据表，可以使⽤多表连接查询来实现；我们通过视图将这些连表查询的结果对⽤⼾开放，⽤⼾则可以直接通过查询视图获取多表数据，操作更便捷。

3.11.3,视图操作

#语法:
create view <view_name> AS select_statement
#实例: 创建一个与student表相同的视图
create view v_select as select * from student
#查询视图结构
desc v_select;
#修改视图
#第一种
create OR REPLACE view <view_name>
AS <查询语句>;
#第二种
alter <view_name>
AS  <查询语句>;

3.11.4,视图数据的特性

视图是虚拟表，查询视图的数据是来源于数据表的。当对视图进⾏操作时，对原数据表中的数据是否由影响呢？

查询操作：如果在数据表中添加了新的数据，⽽且这个数据满⾜创建视图时查询语句的条件，通过查询视图也可以查询出新增的数据；当删除原表中满⾜查询条件的数据时，也会从视图中删除。
新增数据：如果在视图中添加数据，数据会被添加到原数据表,但是原表改变视图并不会发生改变
删除数据：如果从视图删除数据，数据也将从原表中删除
修改操作：如果通过修改数据，则也将修改原数据表中的数据

视图使用建议:对复制查询简化操作,且不会对数据进行修改的情况下可使用视图,同时在创建视图时可对字段进行重命名，使视图对字段名进行隐藏保护

3.12,索引

为什么要使用索引?

数据库是⽤来存储数据，在互联⽹应⽤中数据库中存储的数据可能会很多(⼤数据)，数据表中数据的查询速度会随着数据量的增⻓逐渐变慢，从⽽导致响应⽤⼾请求的速度变慢，⽤⼾体验差,因此需要引入索引.

3.12.1,索引的概念

索引，就是⽤来提⾼数据表中数据的查询效率的。是将数据表中某⼀列/某⼏列的值取出来构造成便于查找的结构进⾏存储，⽣成数据表的 ⽬录 。

当我们进⾏数据查询的时候，则先在 ⽬录 中进⾏查找得到对应的数据的地址，然后再到数据表中根据地址快速的获取数据记录，避免全表扫描。

索引的数据结构默认为B+树

b+树 :

范围查询五次IO操作(叶节点指针)

id > 38 and id < 73

指针分为两个部分:

pdata : 前面存数据

pnext : 存下面一个指针的位置

总结b+树: b+树依靠指针比b树查询速度更快，也是MySQ目前默认使用的索引

3.12.2,索引的分类

MySQL中的索引，根据创建索引的列的不同，可以分为：

主键索引：在数据表的主键字段创建的索引，这个字段必须被primary key修饰，每张表只能有⼀个主键
唯⼀索引：在数据表中的唯⼀列创建的索引(unique)，此列的所有值只能出现⼀次，可以为 NULL
普通索引：在普通字段上创建的索引，没有唯⼀性的限制
组合索引：两个及以上字段联合起来创建的索引
注意：

在创建数据表时，将字段声明为主键（添加主键约束），会⾃动在主键字段创建主键索引
在创建数据表时，将字段声明为唯⼀键（添加唯⼀约束），会⾃动在唯⼀字段创建唯⼀索引

3.12.3,索引操作

唯一索引
# 创建唯⼀索引: 创建唯⼀索引的列的值不能重复
# create unique index <index_name> on 表名(列名);
create unique index index_test1 on tb_testindex(tid);
普通索引
# 创建普通索引: 不要求创建索引的列的值的唯⼀性
# create index <index_name> on 表名(列名);
create index index_test2 on tb_testindex(name);
组合索引
# 创建组合索引
# create index <index_name> on 表名(列名1,列名2...);
create index index_test3 on tb_testindex(tid,name);
全⽂索引

MySQL 5.6 版本新增的索引，可以通过此索引进⾏全⽂检索操作，因为MySQL全⽂检索不⽀持中⽂，因此这个全⽂索引不被开发者关注，在应⽤开发中通常是通过搜索引擎（数据库中间件）实现全⽂检索
create fulltext index <index_name> on 表名(字段名);
使用索引

索引创建完成之后⽆需调⽤，当根据创建索引的列进⾏数据查询的时候，会⾃动使⽤索引;组合索引需要根据创建索引的所有字段进⾏查询时触发。

在命令⾏窗⼝中可以查看查询语句的查询规划：

查看索引
# 命令⾏
show create table <索引名>;

4,JDBC

4.1,JDBC介绍

JDBC (全称Java DataBase Contectivity) ：Java与数据库的连接，数据库编程。

JDBC 是Java语⾔（JDK）为完成数据库的访问操作提供的⼀套统⼀的标准

4.2,JDBC的使用步骤

4.2.1,加载驱动

根据使⽤的数据库版本选择对应版本的jar包

MySQL数据库版本为5.x,驱动jar包建议使⽤ 5.1.47

MySQL数据库版本为8.x,驱动jar包建议使⽤ 8.0.x

之后，将驱动jar⽂件添加到Java⼯程中

在Java⼯程的根⽬录中创建lib⽂件夹

将下载好的jar⽂件粘贴到lib⽬录

右键该jar包，选择Add as Library，点击OK

4.2.2,注册驱动

通过反射机制，将驱动jar⽂件中提供的驱动类载⼊到JVM中

//1.注册驱动（驱动jar包中的驱动类（Driver）的路径：com.mysql.cj.jdbc.Driver）
Class.forName("com.mysql.cj.jdbc.Driver");


//该加载机制实际上为运行Driver中的静态代码块,为DriverManager注册一个驱动
//com.mysql.cj.jdbc.Driver源码:
public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

4.2.3,创建连接

//2.创建连接：通过数据库驱动管理器 DriverManager（java.sql.Driver）获取连接
// JDBC JDK提供的数据库连接的规范 --- java.sql
// java.sql.Connection接⼝ ⼀个对象就表⽰⼀个数据库连接
// url 数据库的统⼀资源定位器（定位数据库连接资源）
// url参数
// characterEncoding 设置编码
// useSSL=true 使⽤SSL通信
// useTimezone=true 设置客⼾端和服务端时区转换
String url = "jdbc:mysql://localhost:3306/所需使用的数据库名称";
Connection connection = DriverManager.getConnection(url,"root","root角色的密码");

4.2.4,编写sql指令

//3.编写要执⾏的SQL指令:SQL指令中需要的参数可以通过字符串拼接的形式（会导致SQL注⼊）
String sql = "insert into
books(book_id,book_name,book_author,book_price,book_stock,book_desc)
values('"+id+"','"+name+"','"+author+"',"+price+","+stock+",'"+desc+"')";

4.2.5,加载sql指令

//4.加载SQL指令:获取SQL指令的加载器
// java.sql.Statement 对象 可以理解为 SQL指令的“加载器”
// java.sql.PreparedStatement 对象 SQL指令的“预编译加载器”
Statement statement = connection.createStatement();

4.2.6,执行sql命令,获取结果

//5.执⾏SQL获取执⾏结果
// a. 如果SQL指令为DQL,则 ResultSet rs = statement.executeQuery(sql); rs中就是查
询结果
// b. 如果SQL指令为DML，则 int i = statement.executeUpdate(sql); i就表⽰DML操作
影响的数据⾏数
// 如果i>0，表⽰DML操作是成功的；如果i=0表⽰DML操作对数据表中的数据没有影响
int i = statement.executeUpdate(sql);

4.2.7,处理执行结果

//6.处理结果（业务）
// 添加操作：返回值i>0表⽰添加成功，否则表⽰添加失败
// 修改操作：返回值i>0表⽰修改对数据有影响，如果i=0 表⽰对数据库没有影响
// 删除操作：返回值i>0表⽰删除对数据有影响，如果i=0 表⽰对数据库没有影响
// 查询操作：从ResultSet rs中取出查询结果，封装到Java对象中
System.out.println(i>0?"添加成功":"添加失败");

4.2.8,关闭连接

//7.关闭连接
// 如果执⾏DML操作：需要关闭Statement和Connection
// 如果执⾏DQL操作：需要关闭ResultSet、Statement和Connection
// 关闭之前先判空、由⼤到⼤关闭
if(statement != null && !statement.isClosed()){
	statement.close();
}
if(connection !=null && !connection.isClosed()){
	connection.close();
}
//如果是查询操作获取得到了ResultSet.则也需要进行关闭
if(rs !=null && !rs.isClosed()){
	rs.close();
}

4.3,JDBC的核心类与接口

java.sql.DriverManager类 驱动管理器

java.sql.Connection接⼝ 数据库连接

java.sql.Statement接⼝ SQL指令的“加载/执⾏器

java.sql.ResultSet接⼝ 结果集

4.3.1,DriverManager类

注册驱动
创建数据库连接

4.3.2,Connection接⼝

Connection对象表⽰Java应⽤程序与数据库之间的连接

通过Connection接⼝对象，获取执⾏SQL语句的Statement对象
完成数据的事务管理

获取Statement对象
Statement接⼝: 编译执⾏静态SQL指令
Statement statement = connection.createStatement();
PreparedStatement接⼝：继承了Statement接⼝，预编译动态SQL指令（解决SQL注⼊问题）
PreparedStatement preparedStatement = connection.prepareStatement(sql);
- CallableStatement接⼝：继承了PreparedStatement接⼝，可以调⽤存储过程

- ```java
CallableStatement callableStatement = connection.prepareCall(sql);
事务管理
//开启事务（关闭事务⾃动提交）
connection.setAutoCommit(false);
//事务回滚
connection.rollback();
//提交事务
connection.commit();

4.3.3,Statement接⼝

⽤于编译、执⾏SQL指令

4.3.4,ResultSet接⼝

ResultSet接⼝对象，表⽰查询操作返回的结果集，提供了便利的⽅法⽤于获取结果集中的数据

使用

ResultSet rs = statement.executeQuery(select_sql);

//rs指向的为表头,即当rs.next()为空时则表示接下来没有元素了

//从ResultSet中获取数据
rs.getInt(String columnLable)//通过列名获取数据
rs.getInt(int columnLableIndex)//通过列的下标获取数据,注意下标从1开始
//当列中数据为Stirng时则需将getInt()方法换为getString()

4.4,SQL注入问题

4.4.1,什么是SQL注入问题

在JDBC操作SQL指令编写过程中，如果SQL指令中需要数据，我们可以通过字符串拼接的形式将参数拼接到SQL指令中，

如 String sql = "delete from books where book_id="+s; (s就是拼接到SQL中的变量)

使⽤字符串拼接变量的形式来设置SQL语句中的数据，可能会导致因变量值的改变引起SQL指令的原意发⽣改变 ，这就被称为SQL注⼊。SQL注⼊问题是需要避免的。

例如:
如果s的值为 1 ，SQL指令 ： delete from books where book_id=1，
如果s的值为 1 or 1=1 ，SQL指令：delete from books where book_id=1 or 1=1
那么SQL中的条件则是⼀个恒等式,则该SQL语句必定执行

4.4.2,解决SQL注入问题

使⽤PreparedStatement进⾏SQL预编译解决SQL注⼊问题：
在编写SQL指令时，如果SQL指令中需要参数，⼀律使⽤?参数占位符
如果SQL指令中有 ? ，在JDBC操作步骤中不再使⽤Statement，⽽是从Conection对象获取 PreparedStatement对SQL指令进⾏预编译
PreparedStatement preparedStatement = connection.prepareStatement(sql);
预编译完成之后，通过PreparedStatement对象给预编译后的SQL指令的 ? 复制

prepareadStatement.setInt(参数占位符序号,值);
prepareadStatement.setString(参数占位符序号,值);

SQL指令中的所有 ? 完成赋值之后，通过PreparedStatement执⾏SQL时不再加载SQL

int i = prepareadStatement.executeUpdate();
ResultSet rs = preparedStatement.executeQuery();

5，JDBC高级应用

三层架构

三层架构是指：视图层 View、服务层 Service，与持久层 Dao。它们分别完成不同的功能。
View 层：用于接收用户提交请求。
Service 层：用以实现系统的业务逻辑
Dao 层：用以实现直接对数据库进行操作。
为了更好的降低各层间的耦合度，在三层架构程序设计中，采用面向接口(抽象)编程。
即上层对下层的调用，是通过接口实现的。
而下层对上层的真正服务提供者，是下层接口的实现类。
服务标准（接口）是相同的，服务提供者（实现类）可以更换。
这就实现了层间解耦合。

5.1,工具类封装

5.1.1,代码的复用性

在我们的应⽤程序中，如果需要完成相同的操作，相同的代码⽆需重复编写，我们只需⼀次编写多次调⽤即可

JDBC数据库编程的步骤：

注册驱动
创建连接
编写SQ
获取Statement对象
执⾏SQL
处理结果
关闭连接

在以上操作中,注册驱动,创建连接,关闭连接操作为相同操作,因此可以对其进行封装以减少代码的复用性,将这些操作封装在特定的类中即形成了工具类,工具类命名常为:DBManager ,DBUtil ,JDBCUtil, DBHelper

5.2,DAO与DTO的封装

DAO Data Access Object 数据访问对象

DTO Data Transfer Object 数据传输对象（实体类）⽤于传输DML操作参数及DQL的查询结果

5.2.1,CRUD方法的封装

CRUD是指在做计算处理时的增加(Create)、读取查询(Retrieve)、更新(Update)和删除(Delete)几个单词的首字母简写。主要被用在描述软件系统中DataBase或者持久层的基本操作功能。

封装是⾯向对象的特征之⼀

我们将能够完成某个CRUD操作的代码单独定义成⼀个⽅法，当需要完成此CRUD操作时调⽤这个⽅法即可

5.2.2,DTO实体类封装

在封装CRUD⽅法时，对于查询操作⽽⾔，需要将查询到的数据库记录返回给调⽤者，但是⼀个查询⽅法只能返回⼀个值，⽽⼀条数据库记录有多个值，因此需要在Java程序中创建⼀个属性与数据库表匹配的类，通过此类的对象封装查询到的数据，我们把⽤于传递JDBC增删查改操作的数据的对象称之为数据传输对象

DTO(实体类：带有属性，其对象可以存放数据的类)

实体类创建规则

类中属性的个数和类型，与对应的数据表保持⼀致

提供所有属性的get和set⽅法

提供全参构造器

提供⽆参构造器

重写toString⽅法

重写hashcode和equals (可不写)

实现序列化Serializable接⼝(可不写)

5.2.3,DAO封装类

DAO封装：将对数据库中同⼀张数据表的JDBC操作⽅法封装到同⼀个Java类中，这个类就是访问此数据表的 数据访问对象，即主要对数据库中的数据进行增删改查

DAO代码优化

在应⽤程序开发中，如果⽅法中抛出异常且⾃⼰可以处理，则直接通过try/catch进⾏捕获处理
JDBC操作⽅法的连接需要放在finally中进⾏关闭
将数据库连接Connection、Statement、ResultSet等需要关闭的数据库对象定义在try之前
因为所有的JDBC操作都需要Conection、Statement对象，查询⽅法都需要ResultSet对象，因此在DAO中可以将这些对象定义成类的成员变量

5.3,JDBC事务管理

什么是事务

事务是指是程序中一系列严密的逻辑操作，而且所有操作必须全部成功完成，否则在每个操作中所作的所有更改都会被撤消。

事务的四⼤特性：ACID

原子性（Atomicity）**：**操作这些指令时，要么全部执行成功，要么全部不执行。只要其中一个指令执行失败，所有的指令都执行失败，数据进行回滚，回到执行指令前的数据状态。
一致性（Consistency）**：**事务的执行使数据从一个状态转换为另一个状态，但是对于整个数据的完整性保持稳定。
隔离性（Isolation）**：**隔离性是当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。
持久性（Durability）**：**当事务正确完成后，它对于数据的改变是永久性的。

事务的隔离级别

第一种隔离级别：Read uncommitted(读未提交)
如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据

解决了更新丢失，但还是可能会出现脏读

第二种隔离级别：Read committed(读提交)
如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。

解决了更新丢失和脏读问题

第三种隔离级别：Repeatable read(可重复读取)
可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。

解决了更新丢失、脏读、不可重复读、但是还会出现幻读

第四种隔离级别：Serializable(序列化)
提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读

解决了更新丢失、脏读、不可重复读、幻读(虚读)

隔离级别脏读不可重复度幻读
Read uncommitted(读未提交) √ √ √
Read committed(读提交) × √ √
Repeatable read(可重复读取) × × √
Serializable(序列化) × × ×

脏读
所谓脏读是指一个事务中访问到了另外一个事务未提交的数据

幻读
一个事务读取2次，得到的记录条数不一致

不可重复读
一个事务读取同一条记录2次，得到的结果不一致

MySQL事务管理：

start transaction (开启事务)
end transaction (结束事务)
rollback (事务回滚)
commit (提交事务)

隔离级别	脏读	不可重复度	幻读
Read uncommitted(读未提交)	√	√	√
Read committed(读提交)	×	√	√
Repeatable read(可重复读取)	×	×	√
Serializable(序列化)	×	×	×

5.3.1,JDBC事务管理

⼀个事务中的多个DML操作需要基于同⼀个数据库连接
创建连接之后，设置事务⼿动提交（关闭⾃动提交 connection.setAutoCommit(false);
当当前事务中的所有DML操作完成之后⼿动提交 connection.commit();
当事务中的任何⼀个步骤出现异常，在catch代码块中执⾏事务回滚 connection.rollback();

5.4,Service层的事务管理

DAO只负责数据库的操作，业务由service层进⾏管理

5.4.1,Service的作用

DAO负责特定的数据库操作

Servcie进⾏业务处理，Service业务处理过程如果需要数据库操作，则调⽤DAO完成

封装通用的业务逻辑，操作。
如一些数据的检验，可以通用处理。
与数据层的交互。
其他请求：如远程服务获取数据，如第三方api等。

5.4.2,Service层的事务管理

事务管理要满⾜以下条件：

多个DML操作需使⽤同⼀个数据库连接
第⼀个DML操作之前设置事务⼿动提交
所有DML操作执⾏完成之后提交事务
出现异常则进⾏事务回滚

在Service事务中可使多个DML使⽤同⼀个数据库连接

只需在Service获取连接对象，将连接对象传递到DAO中

这种将DAO类中的Connection对象通过Service传递进来，这种对象传递本来也⽆可厚⾮，但是当我们通过⾯向接⼝开发时（⾯向接⼝，是为了能够灵活的定义实现类），容易造成接⼝的冗余（接⼝污染）

接口污染是指把接口做的过于胖，派生类必须实现某些它用不到的功能，这样不仅加大了接口间的耦合，而且带来不必要的复杂性，造成接口污染。

可使用ThreadLocal容器，实现多个DML操作使⽤相同的连接;具体实现见6,ThreadLocal

为了在一定程度上避免接口污染,可使用List容器存储Connection

private static final List<Connection> con=new ArrayList<>();
//通过对容器中是否存在连接进行判断,如果存在连接则返回该连接,不存在则创建一个新的连接加入到容器中

使⽤List集合做容器，在多线程并发编程中会出现资源竞争问题，多个并发的线程使⽤的是同⼀个数据库连接对象（我们的要求是同⼀个事务中使⽤同⼀个连接，⽽并⾮多个线程共享连接）

为了解决并发编程的连接对象共享问题，我们可以使⽤ThreadLocal作为数据库连接对象的容器

public class DBHelper {
    //1.定义数据库连接信息
    private static final String DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String URL ="jdbc:mysql://localhost:3306/db_name?characterEncoding=utf8";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "123456";
    //定义ThreadLocal容器
    private static final ThreadLocal<Connection> local = new ThreadLocal<>();
    //2.静态初始化块注册驱动
    static{
        try {
        	Class.forName(DRIVER);
        } catch (ClassNotFoundException e) {
        	e.printStackTrace();
        }
    }
    //3.创建数据库连接
    public static Connection getConnectin(){
    // 󾠯从ThreadLocal容器中获取连接
    Connection connection = local.get();
    try {
        if(connection == null){
       	 	//󾠰如果容器中没有连接，则创建连接，并将创建的连接存放到容器
        	connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        	local.set(connection);
        }
    } catch (SQLException e) {
   	 	e.printStackTrace();
    }
    	return connection;
    }
    //4. 关闭连接
    // 如果使⽤ThreadLocal存储数据库连接，关闭连接时同时要将Connection对象从ThreadLocal中移除
    public static void closeConnection(){
        // 获取到当前线程使⽤的数据库连接对象
        Connection connection = local.get();
        try {
            if(connection !=null && !connection.isClosed()){
                connection.close();
            }
            // 将关闭后的连接对象从ThreadLocal中移除
            local.remove();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public static void closeStatement(Statement statement){
    	closeStatement(null,statement);
    }
    public static void closeStatement(ResultSet resultSet, Statement statement){
        try {
            if(resultSet!=null && !resultSet.isClosed()){
            	resultSet.close();
        	}
        	if(statement!=null && !statement.isClosed()){
            	statement.close();
            }
        }catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

6,ThreadLocal

6.1,什么是ThreadLocal

ThreadLocal 叫做本地线程变量，意思是说，ThreadLocal 中填充的的是当前线程的变量，该变量对其他线程而言是封闭且隔离的，ThreadLocal 为变量在每个线程中创建了一个副本，这样每个线程都可以访问自己内部的副本变量。

6.2,ThreadLocal的应用

在进行对象跨层传递的时候，使用ThreadLocal可以避免多次传递，打破层次间的约束。
线程间数据隔离
进行事务操作，用于存储线程事务信息。
数据库连接，Session会话管理。

6.3,ThreadLocal常用的方法

6.3.1,`set()`方法

ThreadLocal对象.set() 会为ThreadLocal对象调用set()方法所在的线程中的ThreadLocal.ThreadLocalMap threadLocals = null;进行赋值,赋值类型为一个Map,Map的键为当前的ThreadLocal对象,值为所传入的值.

set()的源码
public void set(T value) {
//获取当前ThreadLocal对象所在的线程
Thread t = Thread.currentThread();
//获取所在线程中存储的threadLocals(ThreadLocalMap)
ThreadLocalMap map = getMap(t);
//判断map是否为空
if (map != null)
 //不为空,则替换值
 map.set(this, value);
else
 //为空则为当前线程创建ThreadLocalMap对象并为threadLocals赋值
 createMap(t, value);
}

void createMap(Thread t, T firstValue) {
//为线程t中的threadLocals创建一个ThreadLocalMap对象进行赋值
t.threadLocals = new ThreadLocalMap(this, firstValue);
}
ThreadLocalMap 为 ThreadLocal 的一个静态内部类，里面定义了Entry 来保存数据。而且是继承的弱引用。在Entry内部使用ThreadLocal作为key，使用我们设置的value作为value。

对于每个线程内部有个ThreadLocal.ThreadLocalMap 变量，存取值的时候，也是从这个容器中来获取。

6.3.2,get()方法

public T get() {
 //获取当前ThreadLocal对象所在的线程
  Thread t = Thread.currentThread();
  //获取所在线程中存储的threadLocals(ThreadLocalMap)
  ThreadLocalMap map = getMap(t);
 //判断线程中存储的ThreadLocalMap是否为空
  if (map != null) {
      //不为空，则以当前ThreadLocal对象为键获取Entry对象
      ThreadLocalMap.Entry e = map.getEntry(this);
      //判断Entry对象是个为空
      if (e != null) {
          //不为空则返回Entry对象的值
          @SuppressWarnings("unchecked")
          T result = (T)e.value;
          return result;
      }
  }
 //线程中ThreadLocalMap为空或者未存储以当前ThreadLocal对象为键的Entry对象时设置初始值
  return setInitialValue();
}

//设置为线程中ThreadLocalMap的初始值
private T setInitialValue() {
 //设置当前值为null
 T value = initialValue();
  //获取当前ThreadLocal对象所在的线程
 Thread t = Thread.currentThread();
  //获取所在线程中存储的threadLocals(ThreadLocalMap)
 ThreadLocalMap map = getMap(t);
  //判读map是否为空
 if (map != null)
     //map不为空向map中添加一个以当前ThreadLocal对象为键。值为空的Entry对象
     map.set(this, value);
 else
     //map为空则为当前线程的threadLocals进行创建对象初始化
     createMap(t, value);
 return value;
}

protected T initialValue() {
 return null;
}

6.3.3,ThreadLocal的内存泄流问题

当ThreadLocal为null时，也就是要被垃圾回收器回收了，但是此时我们的ThreadLocalMap（thread 的内部属性）生命周期和Thread的一样，它不会回收，这时候就出现了一个现象。那就是ThreadLocalMap的key没了，但是value还在，这就造成了内存泄漏。

解决方法:

用完ThreadLocal后，执行remove操作，避免出现内存溢出情况。所以如同 lock 的操作最后要执行解锁操作一样，ThreadLocal使用完毕一定记得执行remove 方法，清除当前线程的数值。如果不remove 当前线程对应的VALUE ,就会一直存在这个值。

6.4,强引用,弱引用,软引用

强引用：普通的引用，强引用指向的对象不会被回收；
软引用：仅有软引用指向的对象，只有发生gc且内存不足，才会被回收；
弱引用：仅有弱引用指向的对象，只要发生gc就会被回收。

7,数据库连接池

7.1,什么是数据库连接池

如果每个JDBC操作需要数据库连接都重新创建，使⽤完成之后都销毁，我们的JVM会因为频繁的创建、销毁连接⽽占⽤额外的系统资源。

数据库连接本质上是可被重⽤的资源（当⼀个JDBC操作完成之后，其创建的连接是可以被其他 JDBC操作使⽤的），基于这个特性：

我们可以创建⼀个 存放数据库连接的容器 (连接池)，连接池是有最⼤容量的
当我们要进⾏JDBC操作时，直接从这个容器中获取连接
如果容器中没有空闲的连接且连接池中连接的个数没有达到最⼤值，则创建新的数据库连接存⼊连接池并给这个操作使⽤，使⽤完成之后⽆需关闭连接直接归还这个容器中即可
如果容器中没有空闲的连接且连接池中连接的个数达到最⼤值，当前操作就会进⾏等待，等待连接池中的某个连接被归还，归还之后再使⽤
如果容器中有空闲连接，则⽆需创建新的连接，直接从容器中获取这个空闲连接进⾏使⽤

连接池：存放数据库连接对象的容器

连接池作⽤：对数据库连接进⾏管理，减少因重复创建、销毁连接导致的系统开销

preview

7.2,常用的线程池

功能	dbcp	druid	c3p0	tomcat-jdbc	HikariCP
是否支持PSCache	是	是	是	否	否
监控	jmx	jmx/log/http	jmx,log	jmx	jmx
扩展性	弱	好	弱	弱	弱
sql拦截及解析	无	支持	无	无	无
代码	简单	中等	复杂	简单	简单
特点	依赖于common-pool	阿里开源，功能全面	历史久远，代码逻辑复杂，且不易维护		优化力度大，功能简单，起源于boneCP
连接池管理	LinkedBlockingDeque	数组		FairBlockingQueue	threadlocal+CopyOnWriteArrayList

由于boneCP被hikariCP替代，并且已经不再更新，boneCP没有进行调研。
proxool网上有评测说在并发较高的情况下会出错，proxool便没有进行调研。
druid的功能比较全面，且扩展性较好，比较方便对jdbc接口进行监控跟踪等。
c3p0历史悠久，代码及其复杂，不利于维护。并且存在deadlock的潜在风险。
基于连接池的性能、使⽤的便捷性、连接监控等多⽅⾯综合情况，druid是⽬前企业应⽤中使⽤最⼴泛的
Hikari在SpringBoot中默认集成，性能是诸多竞品中最好的

7.2.1, HikariPC连接池

引入依赖

配置连接池属性

编写 Hikari.properties文件

java的properties文件需要放在src内部才可被java文件识别,且建议放在src的一级目录下,与二级目录同级

#数据库连接信息
driverClassName=com.mysql.cj.jdbc.Driver
jdbcUrl=jdbc:mysql://localhost:3306/db_name?characterEncoding=utf8&serverTimezone=GMT%2B8
username=root
password=123456


############常用配置#############
# 从池中借出的连接是否默认自动提交事务
# 默认 true
autoCommit=true

# 当我从池中借出连接时，愿意等待多长时间。如果超时，将抛出 SQLException
# 默认 30000 ms，最小值 250 ms。支持 JMX 动态修改
connectionTimeout=30000

# 一个连接在池里闲置多久时会被抛弃
# 当 minimumIdle < maximumPoolSize 才生效
# 默认值 600000 ms，最小值为 10000 ms，0表示禁用该功能。支持 JMX 动态修改
idleTimeout=600000

# 多久检查一次连接的活性
# 检查时会先把连接从池中拿出来（空闲的话），然后调用isValid()或执行connectionTestQuery来校验活性，如果通过校验，则放回池里。
# 默认 0 （不启用），最小值为 30000 ms，必须小于 maxLifetime。支持 JMX 动态修改
keepaliveTime=0

# 当一个连接存活了足够久，HikariCP 将会在它空闲时把它抛弃
# 默认 1800000  ms，最小值为 30000 ms，0 表示禁用该功能。支持 JMX 动态修改
maxLifetime=1800000

# 用来检查连接活性的 sql，要求是一个查询语句，常用select 'x'
# 如果驱动支持 JDBC4.0，建议不设置，这时默认会调用  Connection.isValid() 来检查，该方式会更高效一些
# 默认为空
# connectionTestQuery=

# 池中至少要有多少空闲连接。
# 当空闲连接 < minimumIdle，总连接 < maximumPoolSize 时，将新增连接
# 默认等于 maximumPoolSize。支持 JMX 动态修改
minimumIdle=5

# 池中最多容纳多少连接（包括空闲的和在用的）
# 默认为 10。支持 JMX 动态修改
maximumPoolSize=10

# 用于记录连接池各项指标的 MetricRegistry 实现类
# 默认为空，只能通过代码设置
# metricRegistry=

# 用于报告连接池健康状态的 HealthCheckRegistry 实现类
# 默认为空，只能通过代码设置
# healthCheckRegistry=

# 连接池名称。
# 默认自动生成
poolName=zzsCP

#########少用参数############
# 如果启动连接池时不能成功初始化连接，是否快速失败 TODO
# >0 时，会尝试获取连接。如果获取时间超过指定时长，不会开启连接池，并抛出异常
# =0 时，会尝试获取并验证连接。如果获取成功但验证失败则不开启池，但是如果获取失败还是会开启池
# <0 时，不管是否获取或校验成功都会开启池。
# 默认为 1
initializationFailTimeout=1

# 是否在事务中隔离 HikariCP 自己的查询。
# autoCommit 为 false 时才生效
# 默认 false
isolateInternalQueries=false

# 是否允许通过 JMX 挂起和恢复连接池
# 默认为 false
allowPoolSuspension=false

# 当连接从池中取出时是否设置为只读
# 默认值 false
readOnly=false

# 是否开启 JMX
# 默认 false
registerMbeans=true

# 数据库 catalog
# 默认由驱动决定
# catalog=

# 在每个连接创建后、放入池前，需要执行的初始化语句
# 如果执行失败，该连接会被丢弃
# 默认为空
# connectionInitSql=

# JDBC 驱动使用的 Driver 实现类
# 一般根据 jdbcUrl 判断就行，报错说找不到驱动时才需要加
# 默认为空
# driverClassName=

# 连接的默认事务隔离级别
# 默认值为空，由驱动决定
# transactionIsolation=

# 校验连接活性允许的超时时间
# 默认 5000 ms，最小值为 250 ms，要求小于 connectionTimeout。支持 JMX 动态修改
validationTimeout=5000

# 连接对象可以被借出多久
# 默认 0（不开启），最小允许值为 2000 ms。支持 JMX 动态修改
leakDetectionThreshold=0

# 直接指定 DataSource 实例，而不是通过 dataSourceClassName 来反射构造
# 默认为空，只能通过代码设置
# dataSource=

# 数据库 schema
# 默认由驱动决定
# schema=

# 指定连接池获取线程的 ThreadFactory 实例
# 默认为空，只能通过代码设置
# threadFactory=

# 指定连接池开启定时任务的 ScheduledExecutorService 实例（建议设置setRemoveOnCancelPolicy(true)）
# 默认为空，只能通过代码设置
# scheduledExecutor=

# JNDI 配置的数据源名
# 默认为空
# dataSourceJndiName=

创建数据库连接池工具类

public class HikariUtil {
    /**
     *  1.定义HikariDataSource：表⽰HJ数据库连接池（数据源）
     */
    private static HikariDataSource hikariDataSource;

    /**
     * 2.静态代码块初始化定义HikariDataSource对象
     */
    static{
        try {
            //读取Hikari.properties配置文件
            HikariConfig config = new HikariConfig("src/Hikari.properties");
            //使用配置文件初始化HikariDataSource对象
            hikariDataSource = new HikariDataSource(config);
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建静态方法从线程池中获取连接
     */
    public static Connection getConnection() {
        Connection connection = null;
        try {
            connection = hikariDataSource.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }
}

7.3,通用JDBC操作封装

在DAO层的JDBC操作中，对数据的表增、删、改、查操作存在代码的冗余，我们可以对其公共部分进⾏封装，实现代码的复⽤。

7.3.1,DML操作封装

public class CommonDAO {
    /**
    * 公共DML操作
    * @param sql 执⾏的SQL指令
    * @return
    */
    /**
    * sql : delete from students where stu_num=?
    * args: snum
    *
    * sql : insert into students(stu_num,stu_name,stu_gender,stu_age)
    values(?,?,?,?)
    * args: 1009 Lucy ⼥ 18
    */
    //... 表示可变参数,传入的参数可为多个或为数组
    public boolean update(String sql, Object... args){
        boolean b = false;
        try{
        	Connection connection = HikariUtil.getConnection();
        	PreparedStatement preparedStatement =
        	connection.prepareStatement(sql);
        	for (int i = 0; i < args.length ; i++) {
        		preparedStatement.setObject(i+1,args[i]);
        	}
        	int i = preparedStatement.executeUpdate();
        	b = i>0;
        }catch (Exception e){
        	e.printStackTrace();
        }
        return b;
    }
}

7.3.2,DQL操作封装

public class CommonDAO<T> {
    /**
    * 公共DML操作
    */
    public boolean update(String sql, Object... args){
        boolean b = false;
        try{
            Connection connection = HikariUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < args.length ; i++) {
            	preparedStatement.setObject(i+1,args[i]);
            }
            int i = preparedStatement.executeUpdate();
            b = i>0;
        }catch (Exception e){
        	e.printStackTrace();
        }
        return b;
    }
    /**
    * 查询
    */
    public List<T> select(String sql, RowMapper<T> rowMapper,Object...args){
        List<T> list = new ArrayList<>();
        try{
            Connection connection = HikariUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i <args.length ; i++) {
            	preparedStatement.setObject(i+1,args[i]);
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                // 从查询结果中取出⼀条记录（多个值），封装到⼀个实体类对象中
                // getRow就是⽅法调⽤者在调⽤⽅法时，传递进来的⽤于映射查询结果的⽅法
                T t = rowMapper.getRow(resultSet);
                list.add(t);
            }
        }catch (Exception e){
        	e.printStackTrace();
        }  
    return list;
}

7.4,DBUtils

Commons DBUtils是Apache组织提供的⼀个针对JDBC进⾏简单封装的开源⼯具类库，使⽤ DBUtils可以极⼤简化JDBC应⽤程序开发，同时不会影响数据库访问的性能。 DBUtils是Java编程中数据库访问的轻巧、使⽤的⼯具类库

提供对数据表通⽤的DML操作
提供对数据表通⽤的DQL操作（可以把结果封装成对象、集合等类型）

DBUtils⼯具类库中的核⼼类：

QueryRunner，⽤于执⾏SQL指令（相当于我们⾃⼰封装的CommonDAO类）

update() , ⽤于执⾏DML指令
query(), ⽤于执⾏DQL指令

ResultSetHandler接⼝，结果集处理器，⽤于处理查询结果（相当于我们⾃⼰的封装的RowMapper接⼝）

BeanHandler实现类：当查询语句返回⼀条结果时，使⽤BeanHandler将结果转换成⼀个对象
BeanListHandler实现类：当查询语句返回多个结果时，使⽤BeanListHandler将结果转换成⼀个集合
ScalarHandler实现类：当查询结果返回⼀个值时，使⽤ScalarHandler将这个值转换成Java变量类型
tement = connection.prepareStatement(sql);
for (int i = 0; i < args.length ; i++) {
preparedStatement.setObject(i+1,args[i]);
}
int i = preparedStatement.executeUpdate();
b = i>0;
}catch (Exception e){
e.printStackTrace();
}
return b;
}
/**
* 查询
*/
public List select(String sql, RowMapper rowMapper,Object…args){
List list = new ArrayList<>();
try{
Connection connection = HikariUtil.getConnection();
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i <args.length ; i++) {
preparedStatement.setObject(i+1,args[i]);
}
ResultSet resultSet = preparedStatement.executeQuery();
while(resultSet.next()){
// 从查询结果中取出⼀条记录（多个值），封装到⼀个实体类对象中
// getRow就是⽅法调⽤者在调⽤⽅法时，传递进来的⽤于映射查询结果的⽅法
T t = rowMapper.getRow(resultSet);
list.add(t);
}
}catch (Exception e){
e.printStackTrace();
}
return list;
}


### 7.4,DBUtils

> Commons DBUtils是Apache组织提供的⼀个针对JDBC进⾏简单封装的开源⼯具类库，使⽤ DBUtils可以极⼤简化JDBC应⽤程序开发，同时不会影响数据库访问的性能。 DBUtils是Java编程中数据库访问的轻巧、使⽤的⼯具类库
>
> - 提供对数据表通⽤的DML操作
> - 提供对数据表通⽤的DQL操作（可以把结果封装成对象、集合等类型）
>
> DBUtils⼯具类库中的核⼼类：
>
> **QueryRunner**，⽤于执⾏SQL指令（相当于我们⾃⼰封装的CommonDAO类）
>
> - update() , ⽤于执⾏DML指令
> - query(), ⽤于执⾏DQL指令
>
> **ResultSetHandler接⼝**，结果集处理器，⽤于处理查询结果（相当于我们⾃⼰的封装的RowMapper接⼝）
>
> -  BeanHandler实现类：当查询语句返回⼀条结果时，使⽤BeanHandler将结果转换成⼀个对象
> - BeanListHandler实现类：当查询语句返回多个结果时，使⽤BeanListHandler将结果转换成⼀个集合
> - ScalarHandler实现类：当查询结果返回⼀个值时，使⽤ScalarHandler将这个值转换成Java变 量类型

燕南来

关注

4
点赞
踩
65

收藏

觉得还不错? 一键收藏
0
评论
数据库学习汇总

SQL（Structured Query Language）结构化查询语言，用于存取、查询、更新数据以及管理关系型数据库系统SQL是在1981年由IBM公司推出，一经推出基于其简洁的语法在数据库中得到了广泛的应用，成为主流数据库的通用规范SQL由ANSI组织确定规范在不同的数据库产品中遵守SQL的通用规范，但是也对SQL有一些不同的改进，形成了一些数据库的专有指令，如限制查询数据条数：SQL分类：根据SQL指令完成的数据库操作的不同，可以将SQL指令分为四类。...
复制链接

扫一扫

专栏目录