当前位置:   article > 正文

java———MySQL 学习_java mysql

java mysql

1.初识MySQL

1.1数据库分类

关系数据库:(SQL)

  • MySQL,Oracle,Sql Server,DB2,SQLlite
  • 通过表和表之间,行和列之间的关系进行数据存储,存储结构是由行和列的表组成

非关系型数据库:(NoSQL) Not Only

  • Redis,MongDB
  • 非关系型数据库,对象存储,一个对象的自身的属性来决定

DBMS(数据库管理系统)

  • 数据库的管理软件,科学有效的管理我们的数据。维护和获取数据
  • MySQL,数据库管理系统

1.2MySQL简介

  • MySQL是一个关系型数据库管理系统

  • 由瑞典MySQL AB 公司开发,属于 Oracle 旗下产品

  • MySQL是最好的 RDBMS (Relational Database Management System,关系数据库管理系统) 应用软件之一。

  • 开放源码,中小型,大型网站

  • 官网:https://www.mysql.com/

  • 常用:5.7(稳定)8.0

1.3MySQL安装(解压包类型安装)

在这里插入图片描述

在这里插入图片描述

2.命令输出

sc delete mmysql:清空服务

1.4安装可视化软件SQLyog

1.新建一个数据库

在这里插入图片描述

2.创建学生表

在这里插入图片描述

1.5连接数据库

命令行连接

mysql -uroot -p123456 --连接数据库

UPDATE mysql.`user` SET authentication_string=PASSWORD('123456') WHERE USER='root' AND HOST='localhost'; -- 修改用户密码

flush privilenges; -- 刷新权限

-------------------------------------------
-- 所有的语句都使用分号结尾

show databases; --查看所以数据库

use schhool; --切换数据库

show tables; -- 查看数据库中所有表
describe student; --显示数据库中所有的信息

create database westos; --创建数据库westos

exit --退出连接

-- 单行注释
/*  (多行注释)
edfw
edf
*/

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26

2.操作数据库

操作数据库 --> 操作数据库中的表 > 操作表中的数据

2.1操作数据库

[]: 代表可选

1.创建数据库

CREATE DATABASE [IF NOT EXISTS] westos; -- 如果没有这个数据库就创建
  • 1

2.删除数据库

DROP DATABASE [IF EXISTS] westos; --如果有这个数据库就删除
  • 1

3.使用数据库

-- tab键上面,如果你的表名是一个特殊字符,就需要带``
USE 'schhool';
  • 1
  • 2

4.查看数据库

SHOW DATABASES --查看所以打的数据库
  • 1

2.2,数据库的列类型

数值

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等 3
  • int 标准的整数 4
  • bigint 较大 8
  • float 浮点数 4
  • double 浮点数 8
  • decimal 字符串形式的浮点数 金融计算的时候 ,一般使用

字符串

  • char 字符串固定大小 0-255

  • varchar 可变字符串 0-65535 常用的 String

  • tinytext 微型文本 2^8-1

  • text 文本串 2^16-1 保存大文本

时间日期

  • date YYYY-MM-DD 日期格式

  • time HH:mm:ss 最常用的时间格式

  • timestamp 时间戳 1970.1.1 到现在的毫秒数!

  • year 年份表示

null

  • 没有值,未知
  • 注意,不要使用这个类型进行运算,结果为null

2.3数据库的字段属性(重点)

Unsigned

  • 无符号整数
  • 声明了该列不能声明为负数

zerofill:

  • 不足的位数使用0来填充,int(3),5 —>005

自增:

  • 自动在上一条记录基础上+1(默认)
  • 通常用来设计唯一的主键~ index,必须是整数类型
  • 可以自定义设计主键自增订单起始值和步长

非空 NULL not null

  • 假设设置为not null,如果不给赋值 ,就会报错
  • NULL,如果不填,默认为null

默认:

  • 设置默认的值
  • sex,默认值为男,如果不指定该列的值,则会有默认的值!

做项目用,每一个表,都必须存在以下五个字段!

id
主键
`versi` on
乐观锁
is_delete
伪删除
gmt_ create 
创建时间
gmt update  修改时间
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9

2.4,创建数据库表(重点)

-- 目标:创建一个school数据库
 -- 创建学生表(列,字段) 使用SQL 创建
 -- 学号int 登陆密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址(address),email
 
 -- 注意点,使用英文(),表的名称 和 字段 尽量使用 `` 括起来
 -- AUTO_INCREMENT 自增
 -- 字符串使用 单引号括起来
 -- 所有的语句后面加,(英文的),最后一个不加
 -- PRIMARY KEY 主键,一般应该表中只有一个唯一的主键!
 CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT'姓名',
	`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT'密码',
	`sex` VARCHAR(2) NOT NULL DEFAULT'女' COMMENT'性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(100) DEFAULT NULL COMMENT'家庭地址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT'邮箱',
	PRIMARY KEY('id')
 )ENGINE = INNODB DEFAULT CHARSET=utf8
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19

格式:

 CREATE TABLE IF NOT EXISTS `表名`(
 	`字段名` 列属性 [属性] [索引] [注释],
 	`字段名` 列属性 [属性] [索引] [注释],
 	......
 	`字段名` 列属性 [属性] [索引] [注释],
 )[表类型][字符集设置][注释]
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

常用命令

show create database school -- 查看创建数据库的语句
show sreate table student -- 查看student数据表的定义语句
desc student -- 显示表的结构
  • 1
  • 2
  • 3

2.5、数据库的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
  • 1
  • 2
  • 3
  • 4
  • 5
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大,约为2倍

常规使用操作:

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

在物理空间存在的位置

所有的数据库外键都在data目录下,一个文件夹就对应一个数据库,本质还是文件的存储

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

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

设置数据库表的字符集编码

CHARSET=utf8(创建表时修改)
  • 1

默认为Latin1,不支持中文

2.在my.ini 中配置默认的编码(不推荐,如果被其他人使用但是没有配置配置文件就会出错)

character-set-server=utf8
  • 1

2.6、修改删除表

修改

--修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher1 -- 将表名修改成teacher1

--增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT11--修改表的字段 (重命名,修改约束)
-- ALTER TABLE 表名 MODIFY 字段名 列属性[] 
ALTER TABLE teacher1 MODIFY age VARCHAR11-- 修改约束,将age的字段类型修改为varchar
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT1-- 字段重命名,将age重命名为age1且类型更改为int

--删除表的字段
ALTER TABLE teacher1 DROP age1
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

删除表

DROP TABLE IF EXISTS teacher1 -- 如果表存在,就删除表
  • 1

所有的创建和删除操作尽量加上判断,以免报错

注意点:

  • `` 字段名,使用这个包裹
  • 注释 – /**/
  • sql关键字不敏感,建议小写
  • 所有符号用英文

3、MySQL的数据管理

3.1、外键(了解)

方式一,在创建表的时候增加约束

在这里插入图片描述

添加外键:

KEY `FK_ gradeid` ( `gradeidi` )-- 定义外键
-- 关联两表中的班级id字段
CONSTRAINT `FK_ gradeid` FOREIGN KEY ( `gradeid` ) REFERENCES ` grade` ( `gradeid` )
  • 1
  • 2
  • 3

方式二:创建表后添加外键

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FORETGN KEY`gradeid`REFERENCES `grade``gradeid`---ALTER TABLE 表 ADD CONSTRAINT 约束名 FORETGN KEY(作为外键的列) REFERENCES 哪个表(哪个字段)
  • 1
  • 2
  • 3
  • 4

以上操作都是物理外键,数据库级别的外键,我们不建议使用,(避免数据库过多造成困扰)

最佳实现

  • 数据库只存储数据
  • 我们想使用多张表的数据,想使用外键(程序代码去实现)

3.2、DML语言

**数据库意义:**数据存储,数据管理

DML语言:数据操作语言

  • Insert(添加)
  • update(修改)
  • delete(删除)

3.3、添加

-- 插入语句(添加)
-- insert  into 表名([字段名1,字段名2,字段名3])values('值1','值2','值3')
-- 由于主键自增我们可以省略
INSERT INTO `studio`(`name`)VALUES('打啊'); 
-- 一次可以插入多个name属性,即插入多条数据
-- 一个括号代表一条数据,一条数据中可以插入多条属性值
-- 英文逗号隔开
INSERT INTO `studio`(`name`)VALUES('打啊'),('z'),('u'); 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

3.4、修改

-- 修改语句
-- 修改多条属性值时,只需要将各属性之间用逗号隔开
-- 如果不加where判断语句,那么会默认修改所以的数据,如果是修改名字,那么所以录入的名字都会被修改
UPDATE `studio` SET `name`='小邹',`age`=3 WHERE id=1;
-- 语法
-- UPDATE `表名` SET `colnum_name`=value,[`colnum_name`=value] WHERE id=1;
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

where条件运算符

操作符会返回布尔值

操作符含义范围结果
=等于5=6false
<>或者!=不等于5<>6true
>
<
<=
>=
BETWEEN … and …在某个范围内[2,5]
AND&&
OR||

注意:

  • colnum_name 是数据库的列,尽量带上
  • value也可以是一个变量

3.5、删除

delete命令

-- 删除数据
-- 要加条件,不然会全部删除

DELETE FROM `studio` WHERE id=1;
  • 1
  • 2
  • 3
  • 4

TRUNCATE命令

作用:完全清空一个数据库表,表的结构和索引约束不会变!

-- 清空 student表
TRUNCATE TABLE`student`
  • 1
  • 2

delete的TRUNCATE区别

  • 相同点:都能删除数据,都不会删除表结构
  • 不同:
    • TRUNCATE 重新设置 自增列 计数器会归零
    • TRUNCATE 不会影响事务

DELETE删除的问题,重启数据库,现象:

  • InnoDB 自增列会从1开始(存在内存当中,断电即失)
    Mysql8之前的版本自增数是取当数据个数的值+1, 8及之后会存储自增值,使用时自接拿(应该不是和Myisam一样的文件存储)
  • MyISAM 继续从上一个自增量开始 (存在文件中,不会丢失)

4、DQL查询数据(最重点)

4.1、DQL

(Data Query LANGUAGE:数据查询语言)

  • 所有的查询操作都用它 Select
  • 简单的查询,复杂的查询它都能做
  • 数据库中最核心的语言,最重要的语句
  • 使用频率最高的语句
    在这里插入图片描述

注意:上面的顺序不能上下更改

4.2、指定查询字段

-- 查询全部学生 SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `StudentNO`,`name` FROM student

-- 别名,给结果起一个名字 AS 可以给字段起别名,也可以给表起别名
SELECT `StudentNO` AS 学号,`name` AS 学生姓名 FROM student AS s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',name) AS 新名字 FROM student
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

语法:SELECT 字段,… FROM 表

去重 distinct

作用:去除SELECT查询出来的结果中重复的数据,只显示一条

-- 查询一下有哪些同学参加了考试,成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `StudentNO` FROM result --查询有哪些同学参加了考试
SELECT DISTINCT `StudentNO` FROM result --将重复的查询结果去除并返回不重复结果
  • 1
  • 2
  • 3
  • 4

数据库的列

SELECT VERSION() -- 查询系统版本(函数)
SELECT 100*3-1 AS 查询结果 -- 用来计算(表达式)
SELECT @@auto_increment_increment -- 查询自增的步长(变量)

-- 学员考试成绩加1分后查看
SELECT `StudentNO`,`StudentResult`+1 AS '提分后' FROM result
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

数据库中的表达式:文本值,列,Null,函数,计算表达式,系统变量…

select 表达式 from 表

4.3、where条件子句

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

逻辑运算符

运算符语法描述
and &&a and b a&&b逻辑与,两个都为真,结果为真
or ||a or b a||b逻辑或,其中一个为真,则结果为真
Not !not a ! a逻辑非,真为假,假为真

模糊查询:比较运算符

运算符语法描述
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 (a1,a2,a3…)假设a在a1,或者a2…其中的某一个值中,结果为真
-- ===================== 模糊查询 =======================
-- 查询姓刘的同学
-- like结合 %(代表0到任意个字符)  _(一个字符)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%'

-- 查询姓刘的同学,名字后面只有一个字的
SELECT `StudentNo``StudentName` FROM `Student`
WHERE StudentName LIKE '刘_'

-- 查询姓刘的同学,名字后面只有两个字的
SELECT `StudentNo``StudentName` FROM `Student`
WHERE StudentName LIKE '刘__'

-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo``StudentName` FROM `Student`
WHERE StudentName LIKE '%嘉%'

-- ================ in (具体的一个或者多个值) ==========
-- 查询 1001,1002,1003号学员
SELECT `StudentNo``StudentName` FROM `Student`
WHERE StudentNo IN (1001,1002,1003);

-- 查询在北京和安徽的学生
SELECT `StudentNo``StudentName` FROM `Student`
WHERE `Address` IN ('北京','安徽')

-- =========null   not null============

-- 查询地址为空的学生 null
SELECT `StudentNo``StudentName` FROM `Student`
WHERE `address`='' OR address IS NULL

--查询有出生日期的同学    不为空
SELECT `StudentNo``StudentName` FROM `Student`
WHERE `BornDdte` IN NOT NULL

-- 查询没有有出生日期的同学     为空
SELECT `StudentNo``StudentName` FROM `Student`
WHERE `BornDdte` IN  NULL
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

4.4、联表查询

join

join联表查询的语法:

SELECT 表别名1.属性1,属性2,属性3 -- 属性代表需要查询的全部栏目,如:name,age,sex。
FROM 表名 AS 表别名1
INNER JOIN 表名 AS 表别名2
ON 表别名1.属性1 = 表别名2.属性1 -- 如果两张表中都有属性1,如:都有name属性
  • 1
  • 2
  • 3
  • 4

注意:两表的共有属性在写 SELECT 时需要指明使用哪个表的属性,如:共有属性为 属性1

那么,使用格式为:

SELECT 表别名1.属性1
  • 1

注意:使用的是 left join 时,别名使用最好是以左边表为基准的表的别名

-- C,D,E,F 是要从两张表中查询的属性,如A表中有:C,D,E两个属性,B表中有:C,F两个属性,C就是连接点
SELECT a.C,D,E,F 

--left 和right 是相对连接时两个表的位置比如:
-- A表 是在左边 B表 在右边
FROM A AS a LEFT JOIN B AS b 


-- 也可以用 join on 是固定语法,代表连接查询,
-- 等值查询 与 连接查询 作用相同
ON a.c = b.c -- 两表中相同的属性
-- 这里也可以用 where a.c=b.c ,这属于 等值查询
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

和ON配合使用前提下:

操作描述
Inner join两个表中有相同的属性(如:上面的C),且存在相同的属性值(如:两表的C属性下面都有一个叫张三的,那么代表匹配成功),就代表是查询的目标之一
left join如果A表的C属性下面有张三这一条属性值,但是B中没有张三这一条属性值,也会返回这一行,即:两表中有相同属性C时,以A表中的C属性下面的全部数据为基准,前提:A表在
right join两表中有相同属性C时,以A表中的C属性下面的全部数据为基准,前提:A表在

例:二表联表查询

-- 查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult -- 列举,需要查询的属性
FROM student s -- 表明处于左边的表
LEFT JOIN result r
ON s.studentNo = r.studentNo
  • 1
  • 2
  • 3
  • 4
  • 5

例:三表查询

-- 查询参加考试的同学信息:学号,学生姓名,科目名,分数
-- 分析需求:student表:学号,学生姓名,分数。result:分数,科目名。subject:科目名,分数...

-- 先student与result联表查询,并且用偏向result表的join方式联表
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RICHT JOIN result r
ON r.studentNO = s.studentNO
-- 然后让 result 与 subject联表
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

4.5、分页和排序

排序 ORDER

-- 排序 :升序 ASC,降序 DESC
-- ORDER BY 通过那个字段排序,怎么排

-- 查询的结果根据 成绩 降序 排序
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RICHT JOIN result r
ON r.studentNO = s.studentNO
-- 然后让 result 与 subject联表
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo

ORDER BY StudentResult ASC -- 成绩升序排序
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13

分页 LIMIT

SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RICHT JOIN result r
ON r.studentNO = s.studentNO
-- 然后让 result 与 subject联表
INNER JOIN subject sub
ON r.SubjectNo = sub.SubjectNo

LIMIT 0,5 -- 显示数据库查询结果的从第一条到第五条的数据,0:代表初始的位置,5:代表每次显示的条数
-- 公式:(第n页 - 1) X pageSize(每次显示的条数) 就是分页的方法 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10

4.6、子查询

子查询:在where里面嵌套查询

-- 查询课程为 高等数学-2 且分数不小于 80 的同学的学号和姓名

-- 先用 IN 关键字来筛选符合条件的学生,IN 的括号内查询分数大于 80 的,然后利用子查询,查询科目为 高等数学-2的
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (
	SELECT StudentNo FROM result WHERE StudentResult > 80 AND SubjectNo = (
    	SELECT SubjectNo FROM `subject` WHERE `SubjectName` = '高等数学-2'
    )	
)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8

4.7、分组和过滤

-- 查询不同课程的平均分,最高分,最低分
SELECT SubjectName,AVC(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`subject` = sub.`subject`
GROUP BY r.subjectNo
HAVING 平均分 > 80 -- 只能在GROUP下面写,即分组后才能写过滤条件,即获得结果后对结果再次进行过滤
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

5、MySQL函数

官网:https://dev.mysql.com/doc/refman/5.7/en/sql-function-reference.html

5.1、常用函数

在这里插入图片描述

等等,具体看官方文档

5.2、聚合函数

函数名称描述
COUNT()计数
SUM()求和
AVG()平均值
MAX()最大值
MIN()最小值
-- 统计表中数据
SELECT COUNT(`BornDate`) FROM student; -- Count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student; -- 不会忽略null值,本质是计算行数
SELECT COUNT(1) FROM student; -- 不会忽略null值,本质是计算行数

-- 查询不同课程的平均分,最高分,最低分
SELECT SubjectName,AVG(StudentResult) AS 平均分,MAX(StudentResult),MIN(StudentResult)
FROM result r
INNER JOIN `subject` sub
ON r.`subject` = sub.`subject`
GROUP BY r.subjectNo
HAVING 平均分 > 80
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

5.3、数据库级别的MD5加密

在这里插入图片描述

6.事务

6.1、什么是事务

事务原则:ACID原则 原子性,一致性,隔离性,持久性

参考博客连接:https://blog.csdn.net/dengjili/article/details/82468576

原子性:(Atomicity)

原子性是指事务是一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。

一致性(Consistency)

事务前后数据的完整性必须保持一致,如:转账前后两个账户的总金额不会发生变化

持久性(Durability)
持久性是指一个事务一旦被提交,它对数据库中数据的改变就是永久性的,接下来即使数据库发生故障也不应该对其有任何影响

隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作数据所干扰,多个并发事务之间要相互隔离。

事务的隔离级别

脏读:

指一个事务读取了另外一个事务未提交的数据。

不可重复读:

在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合不对)

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。
(一般是行影响,多了一行)

执行事务

-- ======================= 事务 ====================

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

-- 手动处理事务
SET autocommit = 0 --关闭自动提交

-- 事务开启
START TRANSACTION -- 标记一个事务的开始,从这个之后的 SQL 都在同一个事务内

INSERT XX
INSERT XX

-- 提交:持久化(成功!)
COMMIT
-- 回滚:回到原来的样子(失败!)
ROLLBACK

-- 事务结束
SET autocommit = 1 -- 开启自动提交

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27

模拟场景

-- 模拟转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci -- 创建数据库 shop 并且编码方式为 utf-8
USE shop -- 使用数据库 shop

-- 创建 account 表
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL, -- DECIMAL:字符串形式的浮点数
	PRIMARY KEY(`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

 -- 插入两条数据
INSERT INTO account(`name`,`money`)
VALUES('A',2000.00),('B',10000.00)

-- 模拟转账:事务
SET autocommit = 0; -- 关闭自动提交事务
START TRANSACTION -- 开启一个事务(一组事务)

UPDATE account SET money = money - 500 WHERE `name` = 'A' -- A减500
UPDATE account SET money = money + 500 WHERE `name` = 'B' -- B加500

COMMIT; -- y=提交事务,并且持久化
ROLLBACK; -- 回滚

SET autocommit= 1; -- 恢复默认值
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27

7、索引

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构

提取句子主干,就可以得到索引的本质:索引是数据结构。

7.1、索引的分类

在一个表中,主键索引只能有一个,唯一索引可以有多个

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

基础语法

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

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

-- 增加一个全文索引(索引名) 列名
ALTER TABLE school.student ADD FULLTEXT `studentName`(`studentName`);

-- EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM student; -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentName) ACAINST('刘');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15

7.2、索引原则

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

索引的数据结构

详细文章:

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

B+Tree数据结构:

MyISAM,InnoDB都是采用此数据结构 (且带顺序访问指针) ,但是MyISAM采用非聚集(即:索引到的主键下面的data存放的是目标数据是地址),而InnoDB是将全部数据与主键聚集在一起,(即:索引到的主键下面的data存放的是目标数据)

B-Tree数据结构

在这里插入图片描述

关于两者区别,及结构可以看链接中的文章,

索引调优

InnoDB的主键最好采用自增的列,这样可以充分利用B+Tree的特性,等待下次插入数据时不需要移动庞大的数据,而是直接插入在尾部。

InnoDB索引实现

在这里插入图片描述

可以看出InnoDB必须存在主键,从左到右数值增大。

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图:

在这里插入图片描述

8、权限管理和备份

8.1、用户管理

在这里插入图片描述

8.2、MySQL备份

为什么要备份:

  • 保证数据不丢失
  • 数据转移

MySQL数据库备份方式:

  • 直接拷贝物理文件
  • 在Sqlyog这种可视化工具中手动导出

在这里插入图片描述

9、规范数据库设计

在这里插入图片描述

9.2、三大范式

第一范式(1NF)

原子性: 保证每一列不可再分

第二范式(2NF)

前提:满足第二范式

每张表只描述一件事情

第三范式(3NF)

前提:满足第一范式 和 第二范式

第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。

(规范数据库的设计)

规范性 和 性能的问题

关联查询的表不得超过三张

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

10、JDBC

数据库驱动

应用程序需要利用驱动来操作数据库,而JDBK就是应用程序与驱动打交道的。

JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作,提供了一个(java操作数据库的)规范,俗称 JDBC

这些规范的实现由具体的厂商去做

使用到的java包

java.sql

javax.sql

导入的数据库驱动包

10.1、第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

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,'zou','123456','zou@','2000-10-02'),(2,'fei','123456','fei@','1000-01-20'),(3,'ming','123456','ming@','2000-02-10');
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

2.新建java项目

3、导入数据库驱动

  • 在项目下新建lib目录,将jar包复制到lib目录下,并右键选择 Add as Libray 导入到项目中

在这里插入图片描述

4、编写测试代码

import java.sql.*;

//我的第一个JDBC程序
public class test01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        Class.forName("com.mysql.jdbc.Driver");

        // 2. 用户信息和url
        // useUnicode=true 支持中文编码
        // characterEncoding=utf8
        // useSSL=true 使用安全连接
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        // 3. 连接成功,数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password); 		  // 驱动管理.去获得连接()

        // 4. 执行SQL的对象 Statement 执行SQL的对象
        Statement statement = connection.createStatement(); // 创建一个执行SQL的对象

        // 5. 执行SQL的对象 去 执行SQL, 可能存在结果,查看返回结果
        String sql = "SELECT * FROM users";
        // 执行sql并返回结果集,Q:想要查询用,Query,更新: U (删除和插入也在更新里面),返回的结         果集以链表形式存储
        ResultSet resultSet = statement.executeQuery(sql);
        //循环取出链表中的值
        while (resultSet.next()){ // 如果还有值
            // 可以通过 列名 取相应列的数据,如果不知道列的类型,可以使用Object
            System.out.println("id=" + resultSet.getObject("id"));
            System.out.println("name=" + resultSet.getObject("NAME"));
            System.out.println("pwd=" + resultSet.getObject("PASSWORD"));
            System.out.println("email=" + resultSet.getObject("email"));
            System.out.println("birth=" + resultSet.getObject("birthday"));
            System.out.println("==============================");
        }

        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43

结果:

id=1
name=zou
pwd=123456
email=zou@
birth=2000-10-02
==============================
id=2
name=fei
pwd=123456
email=fei@
birth=1000-01-20
==============================
id=3
name=ming
pwd=123456
email=ming@
birth=2000-02-10
==============================

Process finished with exit code 0

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

步骤总结:

1、加载驱动

2、连接数据库 DriverManager

3、获得执行sql的对象 Statement(这个对象不安全)

4、获得返回的结果集

5、释放连接

DriverManager

// DriverManager.registerDriver(new com.mysql.jdbc.Driver());之前的写法,意思是注册了一个驱动,但是由于Driver()的源码就是注册一个驱动,那么整行代码本质上注册了两次,故弃用。
Class.forName("com.mysql.jdbc.Driver");//现在注册且加载驱动的固定写法
Connection connection = DriverManager.getConnection(url, username, password);

// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.rollback(); // 回滚相关
connection.commit(); // 事务提交相关
connection.setAutoCommit(); //事务自动提交相关 参数为false是关闭自动提交事务,且开启事务(此时需要手动提交事务,即调用commit()函数)
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11

URL

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

// mysql 默认端口号为: 3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3
// oralce 默认端口号为: 1521
//jdbc:oracle:thin:@localhost:1521:sid
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

Statement 执行SQL 的对象 PrepareStatement 执行SQL 的对象

String sql = "SELECT * FROM users"; // 编写SQL语句

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

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

获得指定的数据类型

resultSet.getObject(); //不知道列类型时使用
//知道列类型时使用
resultSet.getString();
resultSet.getInt(); 
resultSet.getFloat();
resultSet.getDate();
...
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7

遍历,指针

resultSet.beforeFirst(); // 移动到最前面
resultSet.afterLast(); // 移动到最后面
resultSet.next(); // 移动到下一个数据
resultSet.previous(); // 移动到前一行
resultSet.absolute(row); // 移动到指定行
  • 1
  • 2
  • 3
  • 4
  • 5

释放资源

// 6. 释放连接
resultSet.close();
statement.close();
connection.close();
  • 1
  • 2
  • 3
  • 4

10.2、statement对象

CRUD操作-create

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

Statement st = conn.createStatement();
String sql = "insert into user(。。。.) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功!!!");
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作 - delete

Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功!!!");
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作 - update

Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功!!!");
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

CRUD操作 - read

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
   //根据获取列的数据类型,分别调用rs的相应方法映射到java对象中
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

完整代码实现:

  • Properties(Java.util.Properties),该类主要用于读取Java的配置文件

  • JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
    
    • 1
    • 得到 JdbcUtils类 的Class对象,通过Class的 getClassLoader() 方法获得加载 JdbcUtils类 的类加载器对象 ClassLoader ,然后通过ClassLoader的 getResourceAsStream方法 从加载路径取得文件的输入流(会通过当前的ClassLoader的findResource方法查找指定文件)
    • 详情链接:https://www.cnblogs.com/yadongliang/p/7920053.html

第一步:编写java类型能够读取的配置文件 db.properties ,文件中存放 driverurlusernamepassword 的数据库连接需要的参数

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
  • 1
  • 2
  • 3
  • 4

第二步:写关于JDBC驱动的工具类(将需要用到的重复操作集中,减少耦合性)

package lesson01.utils;

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

public class JdbcUtils {

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

    static{

        try {
            // 获得配置文件 db.properties 的输入流
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            // 读取配置文件db.properties内容
            Properties properties = new Properties();
            properties.load(in);
            //获得指定资源
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            // 1.驱动只加载一次
            Class.forName(driver);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

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

    // 释放连接资源,Connection:代表数据库, Statement:执行SQL的对象, ResultSet:返回的结果集
    public static void release(Connection conn, Statement st, ResultSet rs)  {
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (st != null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (conn != null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66

第3.1步,编写测试类,测试插入数据操作

package lesson01;

import lesson01.utils.JdbcUtils;

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

public class testInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // 获得数据库连接
            st = conn.createStatement(); // 获得SQL的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(4,'zou4','123456','1160653906@qq.com','2020-10-10')";

            int i = st.executeUpdate(sql);
            if (i > 0)
                System.out.println("插入成功!");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32

3.2、删除数据

将添加数据

String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(4,'zou4','123456','1160653906@qq.com','2020-10-10')";
  • 1
  • 2

的数据库语句,改为删除的数据库语句即可:

String sql = "DELETE FROM users WHERE id = 4";
  • 1

3.3、更改数据

同理、将 sql 语句改为:

String sql = "UPDATE users SET `NAME` = 'zoufeiming' WHERE id = 1";
  • 1

3.4、查询数据

//package lesson01;
//
//import lesson01.utils.JdbcUtils;
//
//import java.sql.Connection;
//import java.sql.ResultSet;
//import java.sql.SQLException;
//import java.sql.Statement;
//
//public class testInsert {
//    public static void main(String[] args) {
//        Connection conn = null;
//        Statement st = null;
//        ResultSet rs = null;
//
//        try {
//            conn = JdbcUtils.getConnection(); // 获得数据库连接
//            st = conn.createStatement(); // 获得SQL的执行对象
//            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)" +
//                    "VALUES(4,'zou4','123456','1160653906@qq.com','2020-10-10')";
//
//            int i = st.executeUpdate(sql);
//            if (i > 0)
//                System.out.println("插入成功!");
//        } catch (SQLException e) {
//            e.printStackTrace();
//        }finally {
//            JdbcUtils.release(conn,st,rs);
//        }
//    }
//
//}
package lesson01;

import lesson01.utils.JdbcUtils;

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

public class testInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // 获得数据库连接
            st = conn.createStatement(); // 获得SQL的执行对象
            
====================================区别的地方==========================================
            String sql = "select * from users where id = 1";
            rs = st.executeQuery(sql); // 查询完会返回结果集
            // 循环输出想要的结果
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
====================================区别的地方==========================================
            
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }

}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54
  • 55
  • 56
  • 57
  • 58
  • 59
  • 60
  • 61
  • 62
  • 63
  • 64
  • 65
  • 66
  • 67
  • 68

10.3、SQL注入

1、什么是SQL注入

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严,攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句,在管理员不知情的情况下实现非法操作,以此来实现欺骗数据库服务器执行非授权的任意查询,从而进一步得到相应的数据信息。

2、SQL注入的代码实现

import lesson01.utils.JdbcUtils;

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

public class SQL_injection {

    public static void main(String[] args) {
        login(" 'or '2=2"," 'or '1=1");
    }
    //登陆
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection(); // 获得数据库连接
            st = conn.createStatement(); // 获得SQL的执行对象


            String sql = "select * from users where `NAME`='"+username+"' AND `PASSWORD` = '"+password+"'";
            rs = st.executeQuery(sql); // 查询完会返回结果集
            // 循环输出想要的结果
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("password"));
                System.out.println("=================");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

可以看出,本应该写成用户名和密码的参数,被写成了额外的SQL语句,从而使所有的数据库信息暴露

login(" 'or '2=2"," 'or '1=1");
  • 1

上面代码结果:包含了数据库中全部的信息,因为 用了 or 语句且 2=2 恒成立。

zou
123456
=================
fei
123456
=================
ming
123456
=================
zou4
123456
=================
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12

4、解决办法:PreparedStatement,可以对需要查询的语句先行检查,然后在决定是否查询和返回值。

10.4、PreparedStatement对象

安全的,防止SQL注入的,效率相比 Statement 类更高的

把传递进来的参数当做字符

假设其中存在转义字符,就直接忽略, ’ 会被直接转义

10.4.1、插入数据

package lesson01;

import lesson01.utils.JdbcUtils;

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

public class TestInsert_P {
    public static void main(String[] args) {
        Connection conn = null;            
        PreparedStatement st = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
            st = conn.prepareStatement(sql); // 预编译SQL,先写sql,然后不执行
            //手动给参数赋值
            st.setInt(1,5); // id
            st.setString(2,"zou5");
            st.setString(3,"123456");
            st.setString(4,"1160653906@qq.com");
            //java.sql.Date 和 util.Date ,new Date().getTime():获得当前时间年月日(时间戳)
            st.setDate(5,new java.sql.Date(new Date().getTime()));
            //执行
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36

10.4.2、删除数据

与上面代码的区别:

String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
  • 1

变成:

String sql = "delete from users where id = ?";
  • 1

st.setInt(1,5); // id
st.setString(2,"zou5");
st.setString(3,"123456");
st.setString(4,"1160653906@qq.com");
//java.sql.Date 和 util.Date ,new Date().getTime():获得当前时间年月日(时间戳)
st.setDate(5,new java.sql.Date(new Date().getTime()));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

变成:

st.setInt(1,4); // id
  • 1

10.4.3、更新数据

与上面代码的区别:

String sql = "insert into users(id,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";
  • 1

变成:

String sql = "update users set `NAME`=? where id=?;";
  • 1

st.setInt(1,5); // id
st.setString(2,"zou5");
st.setString(3,"123456");
st.setString(4,"1160653906@qq.com");
//java.sql.Date 和 util.Date ,new Date().getTime():获得当前时间年月日(时间戳)
st.setDate(5,new java.sql.Date(new Date().getTime()));
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6

变成:

st.setString(1,"zouzou");
st.setInt(2,1); // id
  • 1
  • 2

10.4.4、查询数据

package lesson01;

import lesson01.utils.JdbcUtils;

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

public class TestInsert_P {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "select * from users where id = ?";

            st = conn.prepareStatement(sql); // 预编译SQL,先写sql,然后不执行

            st.setInt(1,2);

            //执行
            rs = st.executeQuery();
            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35

10.8、事务

ACID原则

原子性:要么全部完成,要么都不完成

一致性:总数不变

隔离性:多个进程互不干扰

持久性:一旦提交不可逆,持久化到数据库中了

隔离性的问题:

脏读:一个事务读取了另一个事务没有提交的事务

不可重复的:在同一事务中,重复读取表中的数据,表数据发送了改变

虚读(幻读):在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致

例:

import lesson01.utils.JdbcUtils;

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

public class test02 {
    public static void main(String[] args) throws SQLException {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //关闭自动提交,开启事务
            conn.setAutoCommit(false);

            String sql1 = "update action set money = money - 100 where name ='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update action set money = money + 100 where name ='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕,提交事务
            conn.commit();
            System.out.println("成功!");

        } catch (SQLException e) {
            
            conn.rollback();//如果失败,则回滚,注:可以不用显示定义,默认会回滚的
            
            
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40

10.9、数据库连接池

连接 – 释放 十分浪费资源

池化技术:准备一些预先的资源,过来就连接预先准备好的

有最小,最大连接数,以及等待连接数

等待超时的时间。

本质上:编写连接池,实现一个接口 DataSource

开源数据源实现

DBCP

C3P0

Druid:阿里巴巴

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

在这里插入图片描述

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/AllinToyou/article/detail/426195
推荐阅读
相关标签
  

闽ICP备14008679号