1. 数据库引擎

1.1 INNODB AND MYISAM

MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键不支持支持
全文索引支持不支持(InnoDB 1.2.x版本开始支持全文检索)
表空间的大小较小较大,2倍左右

常规使用操作:

  • MYISAM:节约空间,速度较快
  • INNODB:安全性高,事务的处理,多表多用户操作

在屋里控件的位置:

  • 所有数据库文件都存在data目录下
  • 本质还是文件的存储

MySQL引擎在物理文件上的区别

  • InnoDB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件(MySQL8.0之前)
  • MYISAM对应文件
    • *.frm -表结构的定义文件
    • *.MYD 数据文件(data)
    • *.MYI 索引文件(index)

设置数据库表的字符集编码 ---- CHARSET=utf8mb4
不设置的话,会是MySQL默认的字符集编码~(不支持中文!)
MySQL的默认编码是Latin1,不支持中文
在my.ini中配置默认的编码

2. DQL查询数据

SELECT语法

SELECT [ALL | DISTINCT]
{* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
		[LEFT | RIGHT | INNER JOIN table_name2] -- 联合查询
    [ON table1.column_name=table2.column_name] -- 联合查询等值判断,与JOIN一起使用
    [WHERE column_name operator value] -- 指定结果需满足的条件
    [GROUP BY column_name] -- 指定结果按照哪几个字段来分组
    [HAVING aggregate_function(column_name) operator value] -- 过滤分组的记录必须满足的次要条件
    [ORDER BY column_name,column_name ASC|DESC] -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}]; -- 指定查询的记录从哪条至哪条
注意:[]代表可选,{}代表必选

2.1 DQL(Data Query Language)

  • 所有的查询操作都用它 Select
  • 简单的查询,复杂的查询它都能做
  • 数据库中最核心的语言,最重要的语句
  • 使用频率最高的语句

2.2 查询数据

SELECT * FROM table_name; -- 查询整张表

SELECT column_name,[...column_name] 
FROM table_name; -- 查询指定数据

SELECT column_name AS xxx 
FROM table_name AS xxx; -- 给表或者字段起别名

SELECT CONCAT(param, column_name) AS xxx 
FROM table_name; -- 将字段与其他字符串等结合形成新的字符串

SELECT DISTINCT column_name 
FROM table_name; -- 去掉查询数据重复的部分

SELECT @@auto_increment_increment; -- 查询自增步长

SELECT 表达式(1 + 1之类的) AS Result; -- 简单的计算

SELECT column_name + 1 
FROM table_name; -- 实现增加减少 

2.3 WHERE子句

作用:检索数据中符合条件的数据

  • 逻辑运算符
运算符语法描述
and &&a and b a && b逻辑与,两个都为真,结果为真
or
not !not a !a逻辑非,真为假,假为真
SELECT column_name, [...column_name] 
FROM table_name 
WHERE column_name >= xx AND(&&) column_name <= xx;

SELECT column_name, [...column_name] 
FROM table_name 
WHERE column_name BETWEEN xx AND xx;

SELECT column_name, [...column_name] 
FROM table_name 
WHERE column_name != xx;

SELECT column_name, [...column_name] 
FROM table_name 
WHERE NOT column_name = xx;
  • 模糊查询(比较运算符)
运算符语法描述
IS NULLa is null如果操作符为NULL,结果为真
IS NOT NULLa is not null如果操作符不为null,结果为真
BETWEENa between b and c若a在b和c之间,则结果为真
LIKEa like bSQL匹配,如果a匹配b,则结果为真
INa in b(a1,a2,a3...)假设a在a1,或者a2...其中的某一个值中,结果为真
% -- 通配符(0~任意个字符) _ -- 占位符(1个字符)
SELECT column_name,[...column_name] 
FROM table_name 
WHERE column_name LIKE x%; -- 查询以某个字开头的数据

SELECT column_name,[...column_name] 
FROM table_name 
WHERE column_name LIKE x_; -- 查询以某个字开头并且只有两个字的数据

IN
SELECT column_name 
FROM table_name 
WHERE column_name IN(xxx,xxx,xxx); -- 查询括号中的数据(相当于多个or),应为具体值,不能像LIKE这样;一个 IN 只能对一个字段进行范围比对,如果要指定更多字段,可以使用 AND 或 OR 逻辑运算符

SELECT column_name 
FROM table_name 
WHERE column_name = '' OR column_name IS NULL;

2.4 联表查询

2276941-20210418000256407-103253976.png

JOIN ON 连接查询  -- ON用于联表条件
WHERE 等值查询		-- WHERE用于过滤条件

-- INNER JOIN 匹配才返回
SELECT a.column_name,[...column_name] 
FROM table_name AS a INNER 
JOIN table1_name AS b 
ON(相当于WHERE) a.column_name = b.column_name; -- 查询交集,相同的字段应该用别名区分,否则系统不能分辨

-- RIGHT JOIN 右表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,左表的字段都置NULL。
SELECT a.column_name,[...column_name] 
FROM table_name AS a RIGHT 
JOIN table1_name AS b 
ON a.column_name = b.column_name;

-- LEFT JOIN 左表不管能不能匹配上条件,最终都会保留:能匹配,正确的保留; 若不能匹配,右表的字段都置NULL。
SELECT a.column_name,[...column_name] 
FROM table_name AS a LEFT 
JOIN table1_name AS b 
ON a.column_name = b.column_name;
操作描述
INNER JOIN如果表中至少有一个匹配,就返回行(匹配才返回)
LEFT JOIN会从左表中返回所有的值,即使右表中没有匹配(不匹配为NULL)
RIGHT JOIN会从右表中返回所有的值,即使左表中没有匹配(不匹配为NULL)

联表嵌套按照逻辑顺序依次进行

SELECT a.column_name,[...column_name] 
FROM table_name AS a RIGHT 
JOIN table1_name AS b ON a.column_name = b.column_name LEFT 
JOIN table2_name AS b ON a.column_name = b.column_name; 

2.5 自连接

自连接查询其实等同于连接查询,需要两张表,只不过它的左表(父表)和右表(子表)都是自己。做自连接查询的时候,是自己和自己连接,分别给父表和子表取两个不同的别名,然后附上连接条件。​

简单来说就是在一个表中进行查询,这个表中的数据出现层级关系,我们可以通过他们的关系进行查询所需的数据。

SELECT a.column_name, b.column_name, [...column_name] 
FROM table_name AS a, table_name AS b 
WHERE a.column1_name = b.column2_name;

2.6 分页和排序

  • 排序
-- ASC 升序 DESC 降序 ORDER BY要放在WHERE后
SELECT column_name, [...column_name] 
FROM table_name 
WHERE column1_name = xx 
ORDER BY ASC(DESC);

-- 如果列有相同的数据,要进一步排序,可以继续添加列名。例如,使用ORDER BY score DESC, gender表示先按score列倒序,如果有相同分数的,再按gender列排序
SELECT id, name, gender, score 
FROM students 
ORDER BY score 
DESC, gender;
  • 分页
-- LIMIT 后面的参数为限制显示几条数据 OFFSET后面的参数为起始索引(从0开始) LIMIT xx OFFSET xx 可以简写为 LIMIT xx(索引),xx(大小)
SELECT column_name, [...column_name] 
FROM table_name 
WHERE column1_name = xx 
ORDER BY ASC(DESC) 
LIMIT xx OFFSET xx(LIMIT xx, xx);

单独使用LIMIT进行分页查询在数据量很大的时候会变慢,应该进行优化

分页查询

2.7 子查询(嵌套查询)

嵌套查询是指在一个外层查询中包含有另一个内层查询。也就是说,一个查询的WHERE部分采用了另一个查询的得到的值作为查询条件,执行顺序是从内而外。

在一个SELECT 语句的WHERE 子句或HAVING 子句中嵌套另一个SELECT 语句的查询称为嵌套查询,又称子查询。子查询是SQL 语句的扩展,其语句形式如下:
SELECT <目标表达式1>[,...]
FROM <表或视图名1>
WHERE [表达式] (SELECT <目标表达式2>[,...]
FROM <表或视图名2>)
[GROUP BY <分组条件>
HAVING [<表达式>比较运算符] (SELECT <目标表达式2>[,...]
FROM <表或视图名2> )]
1、返回一个值的子查询
当子查询的返回值只有一个时,可以使用比较运算符如=、<、>、>=、<=、!=等将富查询和子查询连接起来。
2、返回一组值的子查询
如果子查询的返回值不止一个,而是一个集合时,则不能直接使用比较运算符,可以在比较运算符和子查询之间插入ANY、SOME或ALL。其中等值关系可以用IN操作符。

-- 子查询一般不使用排序
SELECT column_name, [...column_name] 
FROM table_name 
WHERE column_name = (
  SELECT column_name, [...column_name] 
  FROM table_name 
  WHERE column_name = xxx
) ORDER BY column_name DESC;

2.8 分组和过滤

GROUP BY 语句根据一个或多个列对结果集进行分组。
在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。
HAVING 子句可以让我们筛选分组后的各组数据。

SELECT column_name, function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name; -- 指定结果按照哪几个字段分组
HAVING column_name operator value -- 过滤分组的记录必须满足的次要条件

3. MySQL函数

3.1 常用函数

-- 数学运算
SELECT ABS(-8) --绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个 0~1之间的随机数
SELECT SIGN() -- 判断一个数的符号 0 -> 0 负数返回-1,正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('SolitudeAlma') -- 字符串长度
SELECT CONCAT('I', 'LOVE', 'YOU') -- 拼接字符串
SELECT INSERT('I LOVE YOU', 1 , 1, 'He also') -- 查询替换
SELECT LOWER('I LOVE YOU') -- 转换成小写
SELECT UPPER('i love you') -- 转换成大写
SELECT INSTR('I LOVE YOU', 'Y') -- 返回目标字串第一次出现的索引
SELECT REPLACE('I LOVE YOU', 'YOU', 'ME') -- 替换指定字串
SELECT SUBSTR('I LOVE YOU', 3, 4) -- 返回指定的子字符串 param (string, offset, length)
SELECT REVERSE('你坚强伟大') -- 反转字符串

-- 时间和日期函数
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间
SELECT YEAR(NOW) -- 其余类推

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

3.2 聚合函数(常用)

函数名称描述
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
......
-- COUNT
SELECT COUNT(column_name) 
FROM table_name; -- 指定列,COUNT(字段)会忽略NULL值

SELECT COUNT(*) 
FROM table_name; -- COUNT(*)不会忽略NULL值 本质 计算行数

SELECT COUNT(1) 
FROM table_name; -- COUNT(1)不会忽略NULL值 本质 计算行数

-- SUM
SELECT SUM(column_name) AS xxx 
FROM table_name;

-- AVG
SELECT AVG(column_name) AS xxx 
FROM table_name;

-- MAX
SELECT MAX(column_name) AS xxx 
FROM table_name;

-- MIN
SELECT MIN(column_name) AS xxx 
FROM table_name;

3.3 数据库级别的MD5加密(扩展)

什么是MD5?
主要增强算法复杂度和不可逆性
MD5不可逆,它其实就是一个映射关系,相同的值的md5是一样的,也可能存在哈希碰撞,不同的值md5过后也是一样的,但概率较小
MD5网站的原理是他们有一张彩虹表,但是这个表不会很大,所以只有一些常见的字符串才能正确解密

INSERT INTO table_name (column_name[,column_name][,...])
VALUES(xx, xx, MD5(xx));

SELECT column_name 
FROM table_name 
WHERE column_name = MD5(xxx);

4. 事务

4.1 什么是事务

要么都成功,要么都失败


  1. SQL执行 A给B转账 A1000 ---> 200 B200
  2. SQL执行 B收到A的钱 A800 ---> B400

将一组SQL放在一个批次中去执行

事务原则性:ACID原则 原子性,一致性,隔离性,持久性 (脏读,幻读)
事务ACID理解

原子性(Atomicity)
要么都成功,要么都失败
一致性(Consistency)
事务前后的数据完整性要保持一致,1000
持久性(Durability)--- 事务的提交
事务一旦提交则不可逆,被持久化到数据库中
隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。​

隔离所导致的问题

  • 脏读:指一个事务读取了另外一个事务未提交的数据。
  • 不可重复读:在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)
  • 虚读(幻读):是指在一个事务内读取了别的事务插入的数据,导致前后读取不一致。

执行事务

MySQL事务

-- MySQL是默认开启事务自动提交的
SET autocommit = 0; -- 关闭
SET autocommit = 1; -- 开启

-- 手动处理事务
SET autocommit = 0; -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记事务的开始,从这个之后的sql都在同一个事务内

-- 提交:一旦被提交就持久化
COMMIT;
-- 回滚:回滚到原来的样子
ROLLBACK;
-- 事务结束
SET autocommit = 1; -- 开启自动提交

-- 了解
SAVEPOINT savepoint_name; -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT savepoint_name; -- 回滚到保存点
RELEASE SAVEPOINT savepoint_name; -- 删除保存点 

5. 索引

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。
提取句子主干,就可以得到索引的本质:索引是数据结构。

5.1 索引分类

  • 主键索引(PRIMARY KEY)
    • 唯一的标识,主键不可重复,只有一个列作为主键
  • 唯一索引(UNIQUE KEY)
    • 避免重复的列,唯一索引可以重复,多个列都可以标识为唯一索引
  • 常规索引(KEY/INDEX)
    • 默认的,index/key关键字来设置
  • 全文索引(FULLTEXT)
    • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引

-- 显示所有的索引信息
SHOW INDEX FROM table_name;

-- 增加一个索引
ALTER TABLE table_name ADD index_name(column_name);

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM table_name; -- 非全文索引
EXPLAIN SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('xx'); -- 全文索引

5.2 测试索引

插入一百万条数据

--插入100w条数据
DELIMITER $$ -- 写函数之前必须写,标志
SET GLOBAL log_bin_trust_function_creators = TRUE; -- 设置之后才允许创建函数

CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
  DECLARE i INT DEFAULT 0;
  
  WHILE i < num DO
  	-- 插入语句
		INSERT INTO da(id) VALUES(i);
    SET i = i + 1;
  END WHILE;
  RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据

SELECT * FROM table_name WHERE column_name = '中间值,不要太靠前'; -- 时间: 0.47s
CREATE INDEX index_name ON table_name(column_name); -- 创建索引
SELECT * FROM table_name WHERE column_name = 99999; -- 时间: 0.001s
EXPLAIN SELECT * FROM table_name WHERE column_name = 99999;

EXPLAIN的结果:
image.png
索引在数据量大的时候作用很大

5.3 索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引
Btree:InnoDB的默认数据结构​

阅读:
https://blog.codinglabs.org/articles/theory-of-mysql-index.html https://blog.csdn.net/jiadajing267/article/details/81269067

6.权限管理和备份

6.1 用户管理

SQL命令操作

用户表:mysql.user
本质:对这张表进行增删改查

-- 创建用户
CREATE USER user_name IDENTIFIED BY 'password'; -- user_name是用户名,password是密码

-- 修改密码(修改当前用户的密码)
SET PASSWORD = PASSWORD('password');

-- 修改密码(指定用户)
SET PASSWORD FOR user_name = PASSWORD('password');

-- 重命名
RENAME USER pri_user_name TO now_user_name;

-- 用户授权
GRANT ALL PRIVILEGES ON *.* TO user_name; -- ALL PRIVILEGES所有权限(除了给别人授权,root才有) *.* 库.表

-- 查询权限
SHOW GRANTS FOR user_name -- 指定用户的权限
SHOW GRANTS FOR [email protected]

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM user_name;

-- 删除用户
DROP USER user_name;

6.2 数据库备份

为什么要备份:

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件
  • 在可视化工具中导出
  • 使用命令行导出 --> mysqldump
-- h host_name u user_name p password database_name table_name > path
mysqldump -h localhost - u root -p password database_name table_name > path;

-- h host_name u user_name p password database_name table1_name table2_name table3_name > path
mysqldump -h localhost - u root -p password database_name table1_name table2_name table3_name> path;

-- h host_name u user_name p password database_name > path
mysqldump -h localhost - u root -p password database_name > path;

-- 导入
-- 登录的情况下,切换到指定的数据库
-- source file
source path;

mysql -u root -ppassword database_name < path;

7. 规范数据库设计

7.1 为什么需要设计

当数据库比较复杂的时候,我们就需要设计了​

糟糕的数据库设计:

  • 数据冗余,浪费空间
  • 数据库插入和删除都会麻烦、异常
  • 程序的性能差

良好的数据库设计:

  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统

软件开发中,关于数据库的设计

  • 分析需求:分析业务和需要处理的数据库的需求
  • 概要设计:设计关系图E-R图

设计数据库的步骤:(个人博客)

  • 收集信息,分析需求
    • 用户表(用户登录注销,用户的个人信息,写博客,创建分类(多人博客))
    • 分类表(文章分类,谁创建的)
    • 文章类(文章的信息)
    • 友链表(友链信息)
    • 自定义表(系统信息,某个关键的字,或者一些主字段) key:value
  • 标识实体(把需求落实到每个字段)
  • 标识实体之间的关系
    • 写文章:user --> blog
    • 创建分类:user --> category
    • 关注:user --> user
    • 友链:links
    • 评论:user --> user --> blog

7.2 三大范式

为什么需要数据规范化?

  • 信息重复
  • 更新异常
  • 插入异常
    • 无法正常显示信息
  • 删除异常
    • 丢失有效的信息

三大范式

第一范式(1NF)
要求数据库表的每一列都是不可分割的原子数据项
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
每张表只描述一件事情
第三范式(3NF)
前提:满足第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能简介相关。​

规范性和性能问题
关联查询的表不能超过三个

  • 考虑商业化的需求和目标,(成本、用户体验)数据库的性能更加重要
  • 在规范性能的问题的时候,需要适当的考虑一下规范性
  • 故意给某些表增加一些冗余的字段。(多表查询 --> 单表查询)
  • 故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

8. JDBC

8.1 JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(Java操作数据库的)规范,俗称JDBC
这些规范的实现由具体厂商去做
对于开发人员来说,我们只需要掌握JDBC接口的操作即可​

java.sql
javax.sql
数据库驱动包

8.2 第一个JDBC程序

创建测试数据库

CREATE DATABASE `jdbc_study` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

USE `jdbc_study`;

CREATE TABLE `users`(
 `id` INT PRIMARY KEY,
 `name` VARCHAR(40),
 `password` VARCHAR(40),
 `email` VARCHAR(60),
 birthday DATE
);

 INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)
VALUES('1','zhangsan','123456','[email protected]','1980-12-04'),
('2','lisi','123456','[email protected]','1981-12-04'),
('3','wangwu','123456','[email protected]','1979-12-04')
  1. 创建一个项目

  2. 导入数据库驱动
    image.png

  3. 编写测试代码

package com.JDBC.MySQL;

import java.sql.*;

public class JDBC_TEST {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法,加载驱动 8.0的写法
        //com.mysql.jdbc.Driver 8以下的写法
        //2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String userName = "root";
        String password = "";
        //3.连接成功,数据库对象
        Connection connection = DriverManager.getConnection(url, userName, password);
        //4.执行SQL对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象去执行SQL,可能存在结果,查看返回结果
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            System.out.println("id:" + resultSet.getInt("id"));
            System.out.println("name:" + resultSet.getString("name"));
            System.out.println("password:" + resultSet.getString("password"));
            System.out.println("email:" + resultSet.getString("email"));
            System.out.println("birthday:" + resultSet.getString("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤:

  1. 加载驱动
  2. 连接数据库Drive Manager
  3. 获得执行sql的对象 Statement
  4. 获得返回的结果集
  5. 释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法,加载驱动 8.0的写法
Connection connection = DriverManager.getConnection(url, userName, password);

// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit(true);

URL

String url = "jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=true";

// mysql -- 3306
// 协议://hostname:port/database_name?param1&param2...

//oralce -- 1521
// jdbc:oralce:thin:@localhost:1521:sid

Statement 执行SQL的对象 PrapareStatement 执行SQL的对象

String sql = "SELECT * FROM users";

statement.executeQuery();// 查询操作,返回ResultSet
statement.execute(); // 执行任何SQL
statement.executeUpdate(); // 更新、插入、删除。都是用这个,返回一个受影响的行数

ResultSet 查询的结果集:封装了所有的查询结果

获得指定的数据类型

// 未知类型
resultSet.getObject("id");
// 指定类型
resultSet.getString("id");
resultSet.getFloat("id");
resultSet.getDouble("id");
resultSet.getInt("id");

遍历(指针)

//至于为什么是行就自己百度
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到上一行
resultSet.beforeFirst();//移动到第一个数据
resultSet.afterLast();//移动到最后一个数据
resultSet.absolute(row);//移动到指定行

释放资源

//释放资源
resultSet.close();
statement.close();
connection.close();

8.3 Statement对象

JDBC中的Statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。​

Statement对象的executeUpdate方法,用于向数据库发送增删改的sql语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。​

Statement对象的executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作,示例操作:

Statement statement = connection.createStatement();
String sql = "INSERT INTO table_name(...)VALUES(...)";
int num = statement.executeUpdate(sql);
if(num > 0) {
	System.out.println("插入成功");
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作,示例操作:

Statement statement = connection.createStatement();
String sql = "DELETE FROM table_name WHERE column_name = xx";
int num = statement.executeUpdate(sql);
if(num > 0) {
	System.out.println("删除成功");
}

CRUD操作-update

使用executeUpdate(String sql)方法完成数据修改操作,示例操作:

Statement statement = connection.createStatement();
String sql = "UPDATE table_name SET column_name = 'xx' WHERE column1_name = 'xx'";
int num = statement.executeUpdate(sql);
if(num > 0) {
	System.out.println("修改成功");
}

CRUD操作-read

使用executeQuery(String sql)方法完成数据查询操作,示例操作:

Statement statement = connection.createStatement();
String sql = "SELECT * FROM table_name WHERE column_name = xx";
ResultSet resultSet = statement.executeQuery(sql);
while(resultSet.next()) {
	//根据获取的列的数据类型,分别调用resultSet的相应方法映射到java对象中
}

代码实现

  1. 提取工具类
package com.JDBC.MySQL.utils;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBC_Utils {

    private static String url = null;
    private static String username = null;
    private static String password = null;

    static {
        try {
            InputStream inputStream = JDBC_Utils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            String driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");
            //驱动只需加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }

    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if(resultSet != null) {
            try {
                resultSet.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(statement != null) {
            try {
                statement.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(connection != null) {
            try {
                connection.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

//db.properties 这个要放在src下,下面用到的也是,web项目不太一样
driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=
  1. 编写增删改
//增删改只需要修改SQL即可
package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class InsertTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils.getConnection();//获取数据库连接
            statement = connection.createStatement();//获取SQL执行对象
            String sql = "INSERT INTO users(`id`, `name`, `password`, `email`, `birthday`)VALUES(4, 'SolitudeAlma', " +
                    "'123', '[email protected]', '2021-08-07')";
            int i = statement.executeUpdate(sql);
            if(i > 0) {
                System.out.println("Success to insert");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {

            JDBC_Utils.release(connection,statement,resultSet);
        }
    }
}
  1. 查询
package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils;

import java.sql.*;

public class SelectTest {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try{
            connection = JDBC_Utils.getConnection();
            statement = connection.createStatement();

            String sql = "SELECT * FROM users WHERE id = 1";

            resultSet = statement.executeQuery(sql);

            while(resultSet.next()) {
                String name = resultSet.getString("name");
                System.out.println(name);
            }
        }catch(SQLException e) {
            e.printStackTrace();
        }finally {
            JDBC_Utils.release(connection,statement,resultSet);
        }
    }
}

SQL注入

sql存在漏洞,会被攻击导致数据泄露 SQL可能会被拼接 OR

package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQLInjection {
    public static void main(String[] args) {
        //login("zhangsan", "123456");
        login("'or' 1=1", "'or' 1=1");
    }

    //登录
    public static void login(String username, String password) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils.getConnection();//获取数据库连接
            statement = connection.createStatement();//获取SQL执行对象
            // SELECT * FROM users WHERE `name` = '' or '1=1' AND `password` = '' or '1=1';
            String sql = "SELECT * FROM users WHERE `name` = '" + username + "' AND `password` = '" + password + "'";
            resultSet = statement.executeQuery(sql);

            while(resultSet.next()) {
                String name = resultSet.getString("name");
                String pwd = resultSet.getString("password");
                System.out.println(name);
                System.out.println(pwd);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBC_Utils.release(connection,statement,resultSet);
        }
    }
}

8.4、PrepareStatement对象

PreparementStatement可以防止SQL注入,效率更高

  1. 增删改查
package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils;
import java.util.Date;
import java.sql.*;

public class InsertTest01 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils.getConnection();//获取数据库连接
            //区别
            String sql = "INSERT INTO users(`id`, `name`, `password`, `email`, `birthday`)VALUES(?, ?, ?, ?, ?)";
            //手动给参数赋值
            preparedStatement = connection.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
            preparedStatement.setInt(1, 5);
            preparedStatement.setString(2, "solitudealma");
            preparedStatement.setString(3, "1223");
            preparedStatement.setString(4, "[email protected]");
            //注意点: sql.Date 数据库
            //        util.Date Java new Date().getTime() 获取时间戳
            preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
            int i = preparedStatement.executeUpdate();
            if(i > 0) {
                System.out.println("Success to insert");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBC_Utils.release(connection,preparedStatement,resultSet);
        }
    }
}
  1. SQL注入
package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils;

import java.sql.*;

public class SQLInjection01 {
    public static void main(String[] args) {
        //login("zhangsan", "123456");
        login("'or' 1=1", "'or' 1=1");
    }

    //登录
    public static void login(String username, String password) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils.getConnection();//获取数据库连接
            // PrepareStatement 防止SQL注入的本质,把传递进来的参数当作字符
            // 假设其中存在转义字符,比如 ' 会被直接转义
            String sql = "SELECT * FROM users WHERE `name` = ? AND `password` = ?";
            preparedStatement = connection.prepareStatement(sql);//获取SQL执行对象
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();

            while(resultSet.next()) {
                String name = resultSet.getString("name");
                String pwd = resultSet.getString("password");
                System.out.println(name);
                System.out.println(pwd);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBC_Utils.release(connection,preparedStatement,resultSet);
        }
    }
}

8.5 事务

代码实现

  1. 开启事务
connection.setAutoCommit(false);
  1. 一组业务执行完毕,提交事务
  2. 可以在catch语句中显示的定义回滚语句,但默认失败就会回滚
package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils;

import java.sql.*;

public class TransactionTest {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils.getConnection();
            //关闭数据库的事务自动提交 自动会开启事务
            connection.setAutoCommit(false);// 开启事务
            String sql1 = "UPDATE account SET money = money - 100 WHERE name = 'A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();
            String sql2 = "UPDATE account SET money = money + 100 WHERE name = 'B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //业务完毕,提交事务
            connection.commit();
            System.out.println("Success");
        } catch (SQLException e) {
            //失败会自动回滚(默认)
            try {
                assert connection != null;
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBC_Utils.release(connection, preparedStatement, resultSet);
        }
    }
}


package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TransactionTest01 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils.getConnection();
            //关闭数据库的事务自动提交 自动会开启事务
            connection.setAutoCommit(false);// 开启事务

            String sql1 = "UPDATE account SET money = money - 100 WHERE name = 'A'";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            String sql2 = "SELECT * FROM account WHERE name = 'A'";
            preparedStatement = connection.prepareStatement(sql2);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                float money = resultSet.getFloat("money");
                System.out.println(name + " " + money);
            }
            // 制造错误
            int error = 1 / 0;
            String sql3 = "UPDATE account SET money = money + 100 WHERE name = 'B'";
            preparedStatement = connection.prepareStatement(sql3);
            preparedStatement.executeUpdate();

            //业务完毕,提交事务
            connection.commit();
            System.out.println("Success");
        } catch (SQLException e) {
            //失败会自动回滚(默认)
            try {
                assert connection != null;
                connection.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JDBC_Utils.release(connection, preparedStatement, resultSet);
        }
    }
}

8.6 数据库连接池

数据库连接 -- 执行完毕 -- 释放
连接 --> 释放 非常浪费系统资源​

池化技术:准备一些预先的资源,过来就连接预先准备好的
最小连接数:10
最大连接数:100 业务承载上限 超过排队等待
等待超时:100ms​

编写连接池,实现一个接口 DataSource

开源数据源实现

DBCP
C3P0
Druid:阿里​

使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了

DBCP

需要用到的jar包:
commons-dbcp-1.4、commons-pool-1.6

package com.JDBC.MySQL.utils;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JDBC_Utils_DBCP {
    private static DataSource dataSource = null;

    static {
        try {
            InputStream inputStream = JDBC_Utils.class.getClassLoader().getResourceAsStream("dbcpConfig.properties");
            Properties properties = new Properties();
            properties.load(inputStream);
            //创建数据源 工厂模式 ---> 创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();// 从数据源中获取连接
    }

    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if(resultSet != null) {
            try {
                resultSet.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(statement != null) {
            try {
                statement.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(connection != null) {
            try {
                connection.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}


//测试代码
package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class DBCP_TEST {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils_DBCP.getConnection();//获取数据库连接
            //区别
            String sql = "INSERT INTO users(`id`, `name`, `password`, `email`, `birthday`)VALUES(?, ?, ?, ?, ?)";
            //手动给参数赋值
            preparedStatement = connection.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
            preparedStatement.setInt(1, 6);
            preparedStatement.setString(2, "solitudealma");
            preparedStatement.setString(3, "1223");
            preparedStatement.setString(4, "[email protected]");
            //注意点: sql.Date 数据库
            //        util.Date Java new Date().getTime() 获取时间戳
            preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
            int i = preparedStatement.executeUpdate();
            if(i > 0) {
                System.out.println("Success to insert");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBC_Utils_DBCP.release(connection,preparedStatement,resultSet);
        }
    }
}
// dbcpConfig.properties
#连接设置
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=

#!-- 初始化连接 --
initialSize=10

#最大连接数量
maxActive=50

#!-- 最大空闲连接 --
maxIdle=20

#!-- 最小空闲连接 --
minIdle=5

#!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样:【属性名=property;】
#注意:user 与 password 两个属性会被明确地传递,因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交(auto-commit)状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读(read-only)状态。
#如果没有设置该值,则“setReadOnly”方法将不被调用。(某些驱动并不支持只读模式,如:Informix)
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别(TransactionIsolation)。
#可用值为下列之一:(详情可见javadoc。)NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

C3P0

需要用到的jar包
c3p0-0.9.5.5、mchange-commons-java-0.2.20

//封装
package com.JDBC.MySQL.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class JDBC_Utils_C3P0 {
    private static ComboPooledDataSource dataSource = null;

    static {
        try {
            //代码配置
            //dataSource = new ComboPooledDataSource();
            //dataSource.setDriverClass();
            //dataSource.setUser();
            //dataSource.setPassword();
            //dataSource.setJdbcUrl();
            //dataSource.setMaxPoolSize();
            //dataSource.setMinPoolSize();

            //创建数据源 工厂模式 ---> 创建  不加参数使用默认配置
            dataSource = new ComboPooledDataSource("MySQL");//配置文件写法
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();// 从数据源中获取连接
    }

    //释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet) {
        if(resultSet != null) {
            try {
                resultSet.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(statement != null) {
            try {
                statement.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if(connection != null) {
            try {
                connection.close();
            }catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}


package com.JDBC.MySQL;

import com.JDBC.MySQL.utils.JDBC_Utils_C3P0;
import com.JDBC.MySQL.utils.JDBC_Utils_DBCP;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class C3P0_TEST {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JDBC_Utils_C3P0.getConnection();//获取数据库连接
            //区别
            String sql = "INSERT INTO users(`id`, `name`, `password`, `email`, `birthday`)VALUES(?, ?, ?, ?, ?)";
            //手动给参数赋值
            preparedStatement = connection.prepareStatement(sql);//预编译SQL,先写SQL,然后不执行
            preparedStatement.setInt(1, 7);
            preparedStatement.setString(2, "solitudealma");
            preparedStatement.setString(3, "1223");
            preparedStatement.setString(4, "[email protected]");
            //注意点: sql.Date 数据库
            //        util.Date Java new Date().getTime() 获取时间戳
            preparedStatement.setDate(5, new java.sql.Date(new Date().getTime()));
            int i = preparedStatement.executeUpdate();
            if(i > 0) {
                System.out.println("Success to insert");
            }
        }catch (SQLException e){
            e.printStackTrace();
        }finally {
            JDBC_Utils_C3P0.release(connection,preparedStatement,resultSet);
        }
    }
}
//文件名一定是c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
    <!--
    c3p0的缺省(默认)配置
    如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省(默认)
    -->
    <default-config>
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">1209zyy,</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </default-config>

    <!--
    c3p0的命名配置,
    如果在代码中“ComboPooledDataSource ds = new ComboPooledDataSource("MySQL");”这样写就表示使用的name是mysql的缺省(默认)
    -->
    <named-config name="MySQL">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbc_study?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">1209zyy,</property>
        <property name="acquireIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </named-config>
 </c3p0-config>

结论

无论使用什么数据源,本质还是一样的,DataSource接口不会变,方法就不会变

附属代码
所有资源来源于狂神说的B站投稿视频

Q.E.D.


都懂一点,不是很懂的小捞仔