MySQL详解-2022.09.01
初识 MySQL
- JavaEE：企业级 java 开发
- 前端：页面、展示、数据
- 后端：连接点：连接数据库，链接前端
- 数据库：存数据
- 数据库（DB）分类
- 关系型数据库（SQL）
- MySQL、Oracle、DB2、SqlServer、SQLlite
- 通过表和表之间，行和列之间的关系进行数据的存储
- 非关系型数据库（NoSQL）
- Redis、MongDB
- 非关系型数据库，对象存储，通过对象自身的属性来决定
- 关系型数据库（SQL）
DBMS（数据库管理系统）
- 数据库的管理软件，管理我们的数据；维护和获取数据
- MySQL，本质是数据库管理系统（）
一：MySQL 简介
- MySQL 是一个关系型数据库管理系统
- 前世：瑞典 MySQL AB 公司开发
- 今生： Oracle 旗下产品
- MySQL 是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 软件
- MySQL 所使用的 SQL 语言是用于访问数据库的最常用标准化语言
- MySQL 是开放源码软件
- 一般大中小型网站的开发都选择 MySQL 作为网站数据库
- 官网：点击下载
- 安装 MySQL 步骤
二：数据库语言
- DDL：定义
- DML：操作
- DQL：查询
- DCL：控制
三：操作数据库
关键字不区分大小写
3.1 数据表的类型（引擎）
- InnoDB：默认使用
- MYISAM：早些年使用
| MYISAM | InnoDB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大，约为前者 2 倍 |
-
常规的使用操作
- MYISAM：节约空间，速度较快
- InnoDB：安全性高，事物处理，多表多用户操作
-
在物理空间存储的位置
- 所有的数据库文件都存在 data 目录下
- 本质还是文件都存储
-
MySQL 引擎在物理文件上的区别
- InnoDB：在数据库表中只有一个 *.frm 文件，以及上级目录下的 ibdata1 文件
- MYISAM 对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
-
设置数据库表的字符集编码
charset=utf8-
不设置的话，会 mysql 默认的字符集编码（默认不支持中文）
-
MySQL 的默认编码是 Latin1，不支持中文
-
在 my.ini 中配置默认的编码，之后可以不添加
character-set-server=utf8 -
3.2 操作数据库
常用命令
- 查看创建数据库的语句
SHOW CREATE DATABASE MySQLDB;
- 查看数据表的定义语句
SHOW CREATE TABLE student2;
- 显示表结构
DESC student2;
[]：表示可以写也可以不写
创建数据库
CREATE DATABASE [IF NOT EXISTS] teacher;
CREATE DATABASE teacher;
删除数据库
DROP DATABASE [IF EXISTS] teacher;
DROP DATABASE teacher;
切换数据库
USE school;
查看所有的数据库
SHOW DATABASES;
查看数据库中所有的表
show TABLES;
显示数据库中所有的信息
DESCRIBE student;
退出连接
exit;
注释
--
3.3 数据库的列类型
- 数值
- tinytint：十分小的数据 -----1 个字节
- smallint：较小的数据 -------2 个字节
- int：标准的数据 ---------------4 个字节
- bigint：较大数据 --------------8 个字节
- float：浮点数 -------------------4 个字节
- double：浮点数 ---------------8 个字节
- decimal：字符串形式的的浮点数，金融计算的时候，一般是使用 decimal
- 字符串
- char：字符串固定大小的 ------------0～255
- varchar：可变字符串 -----------------0～65535（常用 String 变量）
- tinytext：微型文本 --------------------2^8-1
- text：文本串 ----------------------------2^16-1
- 时间日期
- data：日期 -----------------------YYYY-MM-DD
- time：时间 -----------------------HH🇲🇲ss
- datatime：最常用时间 ------YYYY-MM-DD HH🇲🇲ss
- timestamp：时间戳 -----------1970.1.1 到现在的毫秒数
- year：年份表示
- null
- 没有值，未知
- ==== 注意不要使用 null 进行运算，结果为 null
3.4 数据库的字段属性
unsigned:
- 无符号的整数
- 声明了该列不能声明为负数
zerofill：
- 0 填充的
- 不足的位数，使用 0 来填充。 Int(3). 5----005
自增：
- 通常理解为自增，自动在上一条记录的基础上 + 1（默认）
- 通常用来设计唯一的主键，必须是整数类型
- 可以自定义设计主键自增的
非空：
- 设置选中，则必有值，没有值将会发生错误
默认值：
- 设置默认的值
- sex，默认值为男，如果不指定该列的值，则会有默认的
3.5 数据表操作
创建表
CREATE TABLE [IF NOT EXISTS] 表名(
字段名 列属性 [属性] [索引] [注释],
字段名 列属性 [属性] [索引] [注释],
字段名 列属性 [属性] [索引] [注释],
………………………………
字段名 列属性 [属性] [索引] [注释],
)[表类型][字符集设置][注释]
CREATE TABLE IF NOT EXISTS student2(
id INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
name VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
pws VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
sex VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
brithday DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT charset=utf8
create table db_goods (
id bigint unsigned not null AUTO_INCREMENT comment '主键',
goods_id int(11) unsigned not null comment '商品 id',
goods_title varchar(225) not null DEFAULT '' comment '商品名称',
goods_price decimal(10,2) not null comment '商品价格',
create_time timestamp not null DEFAULT CURRENT_TIMESTAMP comment '创建时间',
update_time timestamp not null DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商品表';
修改表
-
修改表名
ALTER TABLE 旧表名 RENAME AS 新表名ALTER TABLE student RENAME AS student3 -
添加字段
ALTER TABLE student3 ADD 字段名 列属性;ALTER TABLE student3 ADD birthday VARCHAR(8); -
修改字段
-
修改约束
ALTER TABLE student3 MODIFY 字段名 列属性;ALTER TABLE student3 MODIFY birthday INT(8); -
修改字段名字
ALTER TABLE student3 CHANGE 旧字段名 新字段名 列属性;ALTER TABLE student3 CHANGE birthday bir INT(8); -
删除表的字段
ALTER TABLE student3 DROP 字段名;ALTER TABLE student3 DROP bir;
-
删除表
DROP TABLE [IF EXISTS] 表名;
DROP TABLE IF EXISTS student4;
外键
- 在创建表的时候设置外键是不建议使用的，这种方式为物理外键，数据库级别的外键（避免数据库过多造成困扰）
最佳实现外键方式
- 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
- 我们想使用多张表的数据，想使用外键（程序去实现）
四：数据处理 (DML)
4.1 添加
-
插入多个字段的值（字段个数一定要与值个数相同）
insert into 表名 ([字段名],[字段名],[字段名]) values (['值 1'],['值 2'],['值 3']);insert into student2 (name,pws,sex) values ('张三','123456789','男'); -
一次插入多个对象的值 (一次插入两条数据)
insert into 表名 ([字段名],[字段名],[字段名]) values (['值 1'],['值 2'],['值 3']),(['值 1'],['值 2'],['值 3']),[];insert into student2 (name,pws,sex) values ('张三','123456789','男'),('张四','aaaaaa','男');
4.2 修改
-
修改某个字段的值
update 表名 set 字段名='新值' where 条件;update student2 set name='神人' where id=1; -
修改多个字段的值
update 表名 set 字段名1='新值 1',字段名2='新值 2',[] where 条件;update student2 set name='神人',pws='123' where id=1;- where 子句运算符
操作符号 含义 范围 结果 = 等于 5=6 false <> 或 != 不等于 5<>6 Ture < 小于 5<6 Ture > 大于 5>6 False <= 小于等于 7<=9 False >= 大于等于 9>=8 Ture between 闭合区间 [2,5] between 2 and 5 And 与 5>1 and 5<9 Ture or 或 5>7 or 6<4 False 案例学习
update 表名 set 字段名='新值' where 条件1 and 条件2;update student2 set name='凡人' where id='1' and pws='123'; -
注意点：
- 字段名必须是表中存在的
- 条件，如果没有指定，则会修改所有数据
- value：是一个具体的值，也可以是一个变量
- where：条件中的字段值必须加单引号
4.3 删除
-
删除指定数据
delete from 表名 where 条件;delete from student2 where id=1; -
清空一张表
truncate 表名;truncate student2; -
delete 和 truncate
- 相同点：都能删除数据，都不会删除表头
- 不同点：
- truncate：
- 重新设置自增列，计数器会归零
- 不会影响事务
- truncate：
五：查询数据 (DQL)
5.1DQL：核心内容
-
数据库查询语言
-
所有的查询操作都用它
-
简单的查询，复杂的查询都能完成
-
使用频率最高的语言
-
语法
select [all | distinct] [*] [字段名 as 别名, 字段名 as 别名] from 表名 [as 表别名] [left | right | inner join 表名 on 条件] [where 条件] [group by] [having] [order by] [limit {[offset,]row_count | row_countOFFSET offset}]
5.2 指定查询字段
-
查询所有的字段
select * from student; -
查询部分字段，起别名
select studentno as '学号',studentname as '名字' from student as stu; -
concat(a,b): 给 b 字段前面添加 a 内容
select concat('名字：',studentname) as '新名字' from student; -
distinct：结果去重
select distinct studentno from result; -
数据库的列（表达式）
select version(); select 100*3-1 as 计算结果; select studentno,studentresult+1 as 提分后 from result; -
where 条件字句：按条件查询
运算符 语法 描述 and 或 && a and b 两个都为真，结果真 or 或 || a or b 其中一个为真，结果为真 not 或！ not a 真为假，假为真 select studentno,studentresult from result where studentresult>=95 and studentresult<=100;select studentno,studentresult from result
where studentresult>=95 && studentresult<=100;select studentno,studentresult from result where studentresult between 95 and 100;
select studentno,studentresult from result where not studentno = 1000; -
模糊查询：比较运算符
运算符 语法 描述 is null a is null 如果 a 为空，结果为真 is not null a is not null 如果 a 不为空，结果为真 between a beween b and c 如果 a 在 b、c 之间，则结果为真 like a like b a 匹配 b，结果为真 in a in(a1,a2,a3,...) a 如果在 a1,a2,a3... 中某一个值中，则结果为真 -- 查询第一个字为刘 select studentno,studentname from student where studentname like '刘 %';-- 查询第一个字为刘，总共 2 个字
select studentno,studentname from student where studentname like '刘 _';-- 查询第一个字为刘，总共为 3 个字
select studentno,studentname from student where studentname like '刘 _ _';-- 查询学号为 1000，1001，1002
select studentno,studentname from student where studentno in(1000,1001,1002);-- 查询地址不为空的
select studentno,studentname from student where address is not null;
5.3 连表查询
-
join 查询
-
如何区分左右，直接看
-
join on：连接查询
-
where：等值查询
-
inner join
-- 只有右左右表有一个满足就返回 select s.studentno,studentname,subjectno,studentresult from student as s INNER JOIN result as r where s.studentno = r.studentno; -
right join
-- 左表为 student; 右表为：result select s.studentno,studentname,subjectno,studentresult from student as s right join result as r on s.studentno = r.studentno; -
left join
-- 左表为 student；右表为 result select s.studentno,studentname,subjectno,studentresult from student as s left join result as r on s.studentno = r.studentno;
操作 描述 inner join 如果表中至少有一个匹配，就返回行 left join 左表中返回所有的值，即使右表中没有匹配 right join 右表中返回所有的值，即使左表中没有匹配 #### 5.4 自连接 -
5.4 自连接
- 自己的表和自己的表连接，核心：一张表拆成两张一样的表
5.5 分页和排序
-
排序：asc 生序，desc 降序
-
order by
-- 查询考试成绩，按降序排序 select * from result order by studentresult desc; select * from result order by studentresult asc;
-
-
分页
-
缓解数据库压力、给人好的感觉
-
limit：开始的行数 -1，页面条数
-
起始值：（n-1)*pageSize
-
n：当前页
-
pageSize：页面大小
-
数据总数 / 页面大小 = 总页面
select * from result order by studentresult desc limit (n-1)*pageSize,pageSize;-- 第 3 条数据开始，取 3 条数据 select * from result order by studentresult desc limit 2,3;
-
5.6 子查询和嵌套查询
-
子查询
-
where 中的值是计算得到
-
在 where 中在加入一个 select 语句
-
由里及外
-
-
嵌套查询
5.7 MySQL 函数
-
聚合函数（使用特别多）
函数名称 描述 count 计数 sum 求和 avg 平均 max 最大 min 最小 ... ... -- 查询有多少记录 select count(studentname) from student; -- 忽略所有 null select count(*) from student; -- 不会忽略 null select count(1) from student; -- 不会忽略所有的 null-- 全班平均分
select avg(studentresult) from result;-- 统计工资总分
select sum(studentresult) from result;-- 得到最低分
select min(studentresult) from result;-- 得到最高分
select max(studentresult) from result; -
常用函数
- 绝对值函数
- 向下取整
- 向上取整
- 返回 0～1 之间的随机数
- 判断一个数的符号
- 得到一个字符串的长度
- 拼接字符串
- 替换字符串
- 大小写字母转化
六：事务
- MySQL 默认开启事务自动提交
- 要么成功、要么失败
- 转账案例
- 将一组 SQL 放在一个批次中去执行
- 事务原则：ACID 原则：原子性、一致性、持久性、隔离性； （脏读、虚 (幻) 读）
- 事务一旦提交不可逆
- 原子性：
- 要么成功，要么失败
- 一致性：
- 事务前后的数据的完整性保持一致
- 持久性：
- 事务一旦提交不可逆，被持久化到数据库中
- 隔离性：
- 多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务操作数据所干扰。多个并发事务之间相互隔离
- 脏读：
- 一个事物读取了另为一个事务未提交的数据
- 不可重复读：
- 在一个事务内读取表中的某一行数据。多次读取结果不同，（这个不一定是错误，只是某些场 合不对）
- 幻读：
- 一个事务内读到别的事务插入的数据，导致前后读取不一致
- 手动处理事务
-- MySQL 默认开启事务自动提交
set autocommit = 0 -- 关闭
-- 手动处理事务
start transaction -- 标记一个事务的开始，从这个之后的 sql 都在同一个事务内
-- 提交 ：持久化
commit
-- 回滚 ：回到的原来的样子（失败！！！）
rollback
-- 事务结束
set autocommit = 1 -- 开启
-- 设置一个事务的保存点
savepoint 保存点名
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存点
代码实现
package day08;
import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Demo02 {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
<span class="hljs-keyword">try</span> {
conn = JdbcUtils.getConnection();
// 关闭数据库的自动提交，变成手动提交
conn.setAutoCommit(false);
<span class="hljs-type">String</span> <span class="hljs-variable">sql1</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money = money - 100 where name = 'A'"</span>;
st = conn.prepareStatement(sql1);
st.executeUpdate();
<span class="hljs-type">String</span> <span class="hljs-variable">sql2</span> <span class="hljs-operator">=</span> <span class="hljs-string">"update account set money = money + 100 where name = 'B'"</span>;
st = conn.prepareStatement(sql2);
st.executeUpdate();
<span class="hljs-comment">//业务完毕提交事务</span>
conn.commit();
System.out.println(<span class="hljs-string">"成功"</span>);
} <span class="hljs-keyword">catch</span> (SQLException throwables) {
throwables.printStackTrace();
<span class="hljs-keyword">try</span> {
// 如果失败则回滚事务，不写默认失败回滚
conn.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
七：索引
索引：帮助 MySQL 高效获取数据的数据结构，提取句子主干，就可以得到索引的本质：索引就是数据结构
快速得到结果
-
索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量大表不需要加索引
- 一般添加在常用来查询的字段上
-
索引的数据结构
7.1 索引的分类
1. 在创建表的时候添加
2. 创建完毕后，增加索引
-
创建一个索引
create index 索引名 on 表名('字段名');create index id_app_user_name on app_user('name'); -
显示所有的索引信息
show index from 表名;show index from student; -
添加一个索引
alter table school.student add fulltext index studentName (studentName); -
分析 sql 执行的状况
explain select * from student;
1. 主键索引（primary key ）
-
唯一的标识，主键不可重复, 只能有一个列为主键
primary key (字段名)primary key (studentId)
2. 唯一索引（unique key）
-
避免重复的列出现, 唯一索引可以重复，多个列都可以标识为唯一索引
unique key 索引名 (字段名 )unique key studentName (studentName )
3. 常规索引（key/index）
- 默认的，index；key 关键字设置
4. 全文索引（fulltext）
- 在特定的数据库引擎下才有，MySAM
- 快速定位数据
八：备份
-
保证重要数据不丢失
-
数据转移方便
-
方式：
-
直接拷贝物理文件
-
利用可视化工具中手动导出
mysqldump -h主机 -u用户名 -p密码 数据库 表名 >目标地址mysqldump -hlocalhost -uname -pabcdef shool >D:/a.sql -
利用命令行导入
# 登录情况下，切换到指定数据库 source D:/a.sql
-
九：加密 MD5
-
加密后的字符串相同，所以比较加密后的结果即可
-
在数据插入的时候加密
insert into student values(1009,MD5(loginpwd)) -
校验过程
- 用户输入密码先 MD5 加密再比较就可以了
十：用户管理
-
用户表：mysql.user
- 本质：读这张表进行增删改查
-
添加用户
create user 用户名 identified by ' 密码;-- 创建一个用户 create user myname identified by 'abcdef'; -
删除用户
drop user 用户名;-- 删除用户名 drop user name; -
授权
grant all privileges on 库.表 to 用户名;-- 授予所有权限 grant all privileges on *.* to name;-- 查看权限
show grant for name;-- 撤销权限
revoke all privileges on . from name; -
修改密码
-- 修改指定用户的密码 set password for myname = password('abcdef');-- 修改当前用户密码 set password = password('abcdef'); -
重命名用户名
rename user 旧用户名 to 新用户名;-- 修改用户名 rename user myname to newname;
十一：三大范式
- 13.1 为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 无法正常显示信息
- 删除异常
- 丢失有效信息
- 三大范式
- 第一范式：保证每一列不可分割
- 要求数据库表的每一列都是不可分割的原子数据项；
- 第二范式：满足第一范式；每张表只描述一件事情
- 在 1NF 的基础上，非码属性必须完全依赖于候选码（在 1NF 基础上消除非主属性对主码的部分函数依赖）；第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）；
- 第三范式：满足第一范式；第二范式；表中不能有间接相关，只能有之间关系
- 在 2NF 基础上，任何非主属性不依赖于其它非主属性（在 2NF 基础上消除传递依赖）；第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
- 第一范式：保证每一列不可分割
- 规范性和性能的问题
- 考虑商业化的需求和目标，（成本，用户体验）数据库的性能更加重要
- 在规范性能的问题的时候，需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段。（从多表查询中变为单表查询）
- 故意增加一些计算列。（从大数据量大查询降低为小数据量大查询：索引）
十二：规范数据库设计
12.1 为什么需要设计
- 糟糕的数据库设计
- 数据冗余，浪费空间
- 数据库插入和删除会麻烦、异常【屏蔽使用物理外键】
- 程序的性能差
- 良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
- 软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库的需求
- 概要设计：设计关系图 E-R 图
- 设计数据库的步骤：（个人博客）
- 收集信息，分析需求
- 用户表（用户登录注销，用户的个人信息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章信息）
- 友链表（友链信息）
- 自定义表（系统信息，某个关键的字，或者一些主字段）
- 评论表（评论信息）
- 说说表
- 标识实体（把需求落地到每个字段）
- 标识实体之间的关系
- 收集信息，分析需求
十三：数据库驱动和 JDBC
-
驱动：声卡、显卡、数据库
- 为了让应用程序与数据库相连接
- 数据库厂商提供数据库驱动
-
JDBC：
- java 操作数据的规范，俗称 JDBC
-
JDBC 案例
-
创建一个普通项目
-
导入数据库驱动
package day08;import java.sql.*;
public class JdbcTest {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
// 1. 加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");// 2. 用户信息和 url
String url = "jdbc:mysql://localhost:3306/shool?" +
// 使用 unicode(中文) 编码
"useUnicode=true&" +
// 字符编码为 utf-8
"characterEncoding=UTF-8&" +
// 安全连接
"useSSL=true&" +
// "useJDBCCompliantTimezoneShift=true&" +
// "useLegacyDatetimeCode=false&" +
"serverTimezone=Asia/Shanghai";
String username = "root";
String password = "abcdef";// 3. 连接成功，数据库对象;connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);// 4。执行 sql 的对象 statement 执行 sql 的对象
Statement statement = connection.createStatement();// 5。执行 sql 对象去执行 sql，可能存在结果，查看返回结果
String sql = "select * from student";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getObject("id"));
}// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
} -
步骤总结
- 加载驱动
- 连接数据库 DriverManager
- 获得执行 sql 的对象 Statement
- 获得返回的结果集
- 释放连接
-
对象解释
-
DriverManager
Class.forName("com.mysql.cj.jdbc.Driver"); Connection connection = DriverManager.getConnection(url, username, password);//connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit(true);
connection.commit();
connection.rollback(); -
url
String url = "jdbc:mysql://localhost:3306/shool?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=Asia/Shanghai";//mysql -- 3306
// 协议：// 主机地址：端口 / 数据库名？参数 1& 参数 2& 参数 3//oralce --1521
//jdbc:oracle:thin:@localhost:1521:sid -
statement 执行 sql 的对象
String sql = "select * from student";statement.executeQuery(sql); // 查询操作返回 resultset
statement.execute(sql); // 执行任意 sql
statement.executeUpdate(); // 更新、插入、删除都用这个，返回一个受影响的行数
statement.executeBatch(); // 批处理 sql -
ResultSet 查询结果集：封装了所有的查询结果
获得指定的数据类型
resultSet.getObject(); // 在不知道列类型的情况下使用 resultSet.getString(); resultSet.getInt(); resultSet.getFloat(); resultSet.getDouble(); resultSet.getDate();遍历: 可以移动光标，到指定位置
resultSet.beforeFirst(); // 移动到最前面 resultSet.afterLast(); // 最后面 resultSet.previous(); // 前一行 resultSet.next(); // 下一行 resultSet.absolute(4); // 指定行
-
-
statement 对象详解
-
增
String sql = "insert into student(....) values(...)"; int num = statement.executeUpdate(sql); if(num > 0){ System.out.println("插入成功") } -
删
String sql = "delect from student where id = 1"; int num = statement.executeUpdate(sql); if(num > 0){ System.out.println("删除成功") } -
改
String sql = "update student set name =''where name =''"; int num = statement.executeUpdate(sql); if(num > 0){ System.out.println("修改成功") } -
查
String sql = "select * from student where id = 1"; // 可以使用这个，但是一般使用下面那个 //ResultSet rs = statement.executeUpdate(sql); ResultSet rs = statement.executeQuery(sql); if(rs.next()){ // 根据获取列的数据结构，分别调用 rs 的相应方法映射 java 对象中 }
-
-
utils 工具包案例
- db.properties
driver=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=Asia/Shanghai username=root password=abcdef- JdbcUtils 工具类
package utils;import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;public class JdbcUtils {
<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-type">String</span> <span class="hljs-variable">driver</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-type">String</span> <span class="hljs-variable">url</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-type">String</span> <span class="hljs-variable">username</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-type">String</span> <span class="hljs-variable">password</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-keyword">static</span> { <span class="hljs-keyword">try</span> { <span class="hljs-type">InputStream</span> <span class="hljs-variable">in</span> <span class="hljs-operator">=</span> JdbcUtils.class.getClassLoader().getResourceAsStream(<span class="hljs-string">"db.properties"</span>); <span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>(); properties.load(in); driver = properties.getProperty(<span class="hljs-string">"driver"</span>); url = properties.getProperty(<span class="hljs-string">"url"</span>); username = properties.getProperty(<span class="hljs-string">"username"</span>); password = properties.getProperty(<span class="hljs-string">"password"</span>);// 1. 驱动只需要加载一次
Class.forName(driver);} <span class="hljs-keyword">catch</span> (IOException | ClassNotFoundException 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 throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}- 插入案例
package day08;import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;public class TestInsert {
public static void main(String[] args) throws SQLException {
Connection connection = JdbcUtils.getConnection();<span class="hljs-type">Statement</span> <span class="hljs-variable">statement</span> <span class="hljs-operator">=</span> connection.createStatement(); <span class="hljs-type">String</span> <span class="hljs-variable">sql</span> <span class="hljs-operator">=</span> <span class="hljs-string">"insert into student(name,age) values('a',55)"</span>; <span class="hljs-type">int</span> <span class="hljs-variable">i</span> <span class="hljs-operator">=</span> statement.executeUpdate(sql); <span class="hljs-keyword">if</span> (i > <span class="hljs-number">0</span>){ System.out.println(<span class="hljs-string">"插入成功"</span>); }// 释放资源
JdbcUtils.release(connection,statement,null);}} -
sql 注入问题
-
PreparedStatement 对象
-
可以防止 sql 注入，并且效率更高
-
增
package day08;import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;public class Demo01 {
public static void main(String[] args) throws SQLException {
Connection conn = null;
PreparedStatement st = null;<span class="hljs-keyword">try</span> { conn = JdbcUtils.getConnection();// 使用？ 占位符代替参数
String sql = "insert into student(name,age) values(?,?)";// 预编译 sql，先写 sql，然后不执行
st = conn.prepareStatement(sql);// 手动给参数赋值
st.setString(1,"小宝宝");
st.setInt(2,55);// 执行
int i = st.executeUpdate();
if (i > 0){
System.out.println("插入成功");
}
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
-
-
-
连接池
-
数据库连接 -------- 执行完毕 -------- 释放资源
-
连接 --- 释放 十分浪费系统资源
-
池化技术：
- 准备一些预先的资源，过来就连接预先准备好的。
- 编写连接池，只需要实现 DataSource
- 最小连接数：10
- 最大连接数：15
- 等待超时：100ms
-
开源数据源实现：使用这些连接池，我们就不需要编写连接数据库的代码了
- DBCP
- C3P0
- Druid：阿里巴巴
-
DBCP
- 导入 dbcp.jar
- 导入 pool.jar
driverClassName=com.mysql.cj.jdbc.Driver url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=UTF-8&useSSL=true&serverTimezone=Asia/Shanghai username=root password=abcdef #初始化连接 initialSize=10 #最大连接数 maxActive=50 #最大空闲连接 maxIdle=20 #最小空闲连接 minIdle=5 #超时等待时间以毫秒为单位 maxWait=60000 #附带属性，属性必须这样写：【属性名 =porperty;】 connectionProperties=useUnicode=true;characterEncoding=UTF8 #指定由于连接池所创建的连接自动提交状态 defaultAutoCommit=true #指定由连接池创建的连接为只读模式 #defaultReadyOnly=true #指定事务的级别 defaultTranslationIsolation=READ_UNCOMMITTEDpackage utils; import org.apache.commons.dbcp2.BasicDataSourceFactory;import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;public class JdbcDBCP {
<span class="hljs-keyword">private</span> <span class="hljs-keyword">static</span> <span class="hljs-type">DataSource</span> <span class="hljs-variable">dataSource</span> <span class="hljs-operator">=</span> <span class="hljs-literal">null</span>; <span class="hljs-keyword">static</span> { <span class="hljs-keyword">try</span> { <span class="hljs-type">InputStream</span> <span class="hljs-variable">in</span> <span class="hljs-operator">=</span> JdbcDBCP.class.getClassLoader().getResourceAsStream(<span class="hljs-string">"DBCP.properties"</span>); <span class="hljs-type">Properties</span> <span class="hljs-variable">properties</span> <span class="hljs-operator">=</span> <span class="hljs-keyword">new</span> <span class="hljs-title class_">Properties</span>(); properties.load(in); <span class="hljs-comment">//创建数据源,通过工厂模式</span> dataSource = BasicDataSourceFactory.createDataSource(properties); } <span class="hljs-keyword">catch</span> (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 throwables) {
throwables.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
} -
C3P0
- 导入 C3P0.jar
- 导入 machange-commons-java.jar
<?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/jdbcstudy?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=UTC</property> <property name="user">root</property> <property name="password">123456</property><span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"acquiredIncrement"</span>></span>5<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"initialPoolSize"</span>></span>10<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"minPoolSize"</span>></span>5<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxPoolSize"</span>></span>20<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"></<span class="hljs-name">default-config</span>></span><!-- 如果在代码中 "ComboPooledDataSource ds=new ComboPooledDataSource("MySQL");" 这样写就表示使用的是 MySQL-->
<default-config>
<property name="MySQL">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/school?userUnicode=true&characterEncoding=utf8&uesSSL=true&serverTimezone=Asia/Shanghai</property>
<property name="user">root</property>
<property name="password">123456</property><span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"acquiredIncrement"</span>></span>5<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"initialPoolSize"</span>></span>10<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"minPoolSize"</span>></span>5<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"><<span class="hljs-name">property</span> <span class="hljs-attr">name</span>=<span class="hljs-string">"maxPoolSize"</span>></span>20<span class="hljs-tag"></<span class="hljs-name">property</span>></span> <span class="hljs-tag"></<span class="hljs-name">default-config</span>></span></c3p0-config>package utils; import org.apache.commons.dbcp2.BasicDataSourceFactory; import javax.sql.DataSource; import java.io.InputStream; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.Properties; public class JdbcC3P0 { private static ComboPooledDataSource ds = null; static { try { // 创建数据源, 通过工厂模式创建对象 ds = new ComboPooledDataSource("MySQL"); } catch (Exception e) { e.printStackTrace(); } } // 获取连接 public static Connection getConnection() throws SQLException { return ds.getConnection(); // 从数据源中获取连接 } // 释放资源 public static void release(Connection connection, Statement statement, ResultSet resultSet){ if (resultSet != null){ try { resultSet.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (statement != null){ try { statement.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (connection != null){ try { connection.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } }package day08; import utils.JdbcC3P0; import utils.JdbcDBCP; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.SQLException; public class Demo04 { public static void main(String[] args) throws SQLException { Connection conn = null; PreparedStatement st = null; try { conn = JdbcC3P0.getConnection(); // 使用？ 占位符代替参数 String sql = "insert into student(name,age) values(?,?)"; // 预编译 sql，先写 sql，然后不执行 st = conn.prepareStatement(sql); // 手动给参数赋值 st.setString(1,"小名"); st.setInt(2,22); // 执行 int i = st.executeUpdate(); if (i > 0){ System.out.println("插入成功"); } } catch (SQLException throwables) { throwables.printStackTrace(); }finally { JdbcC3P0.release(conn,st,null); } } }
-
-
结论
- 无论使用什么数据源，本质还是一样的，DataSource 接口不变，getconnection 不会变