【MySQL、JDBC总结】

MySQL

参考：https://www.bilibili.com/video/BV1NJ411J79Wp=45&vd_source=5f5ea5ad3feec587cf827af102313eae

1. 数据库分类

关系型数据库：（SQL）

• MySQL、Oracle、SQLServer，DB2，SQLite
• 通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL）Not Only

• Redis，MongoDB
• 对象存储，通过对象自身的属性来决定

DBMS（数据库管理系统）

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

2. MySQL

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

• 过去：由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发

• 现在：属于 Oracle 旗下产品。

• MySQL 是最流行的关系型数据库管理系统之一

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

体积小、速度快、总体拥有成本低，中小型网站或大型网站，集群！

2.1 Navicat建库

3. SQL命令

mysql关键字不区分大小写

3.1 连接数据库

mysql -u用户名 -p密码	-- 连接数据库

update mysql.user set authentication_string=password('新密码') where user='用户名' and Host ='localhost';  -- 修改密码

flush privileges;	-- 刷新权限

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

show databases; -- 查看所有的数据库

use school;  -- 切换数据库 use 数据库名

show tables; -- 查看数据库中所有的表

describe student; -- 显示表中所有的信息

create database westos; -- 创建数据库

exit; -- 退出连接

-- sql单行注释

/*sql多行注释*/

数据库 xxx 语言 CRUD 增删改查

• DDL 定义

• DML 操作

• DQL 查询

• DCL 控制

4. 操作数据库

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

4.1 操作数据库

1、创建数据库

CREATE DATABASE westos;	-- 创建数据库
CREATE DATABASE IF NOT EXISTS westos; -- 如果不存在则创建

2、删除数据库

DROP DATABASE westos;	-- 删除数据库
DROP DATABASE IF EXISTS westos;	-- 如果存在则删除

3、使用数据库

-- tab键的上面，如果你的表名或者字段名是一个特殊字符，就需要带``
USE school

4、查看数据库

SHOW DATABASE; -- 查看所有的数据库

4.2 数据库的列类型

数值

• tinyint 十分小的数据 1字节
• smallint 较小的数据 2字节
• mediumint 中等大小的数据 3字节
• int 标准的整数 4字节 常用的
• bigint 较大的数据 8字节
• float 浮点数 4字节 （精度问题）
• double 浮点数 8字节 （精度问题）
• decimal 字符串的浮点数 金融计算的时候，一般用decimal

字符串

• char 字符串，固定大小的 0~255
• **varchar 可变字符串 0~65535 ** 常用的变量
• tinytext 微型文本 $2^8-1$
• text 文本串 $2^{1}6-1$ 保存大文本

时间日期

• date YYYY-MM-DD,日期
• time HH:mm:ss，时间格式
• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳 也较为常用
• year 年份表示

null

• 没有值/未知
• 不要使用NULL进行运算，结果为NULL

4.3 数据库的字段属性

Unsigned：

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

zerofill：

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

AUTO_INCREMENT（自增）：

• 通常理解为自增，自动在上一条记录的基础上 +1 （默认）
• 通常用来设计唯一的主键 index 必须是整数类型
• 可以自定义设计主键自增的起始值和步长

NULL/Not NULL：

• 假设设置为Not NULL，如果不给他赋值，就会报错
• NULL，如果不填写值，默认为NULL

default 默认：

• 设置默认的值
• 如果不指定该列的值，则会有默认的值

4.4 创建数据库表

--  注意：使用英文(),表的 名称 和 字段 尽量使用 `` 括起来 
-- 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

格式

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令

SHOW CREATE DATABASE schoo1  --查看创建数据库的语句
SHOW CREATE TABLE student  --查看student数据表的定义语句
DESC student  --显示表的结构

4.5 数据表的类型

关于数据库引擎

INNODB 默认使用

MYISAM 早些年使用的

	MYISAM	INNODB
事物支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为2倍

常规使用操作：

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹对应一个数据库

本质还是文件的存储

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

• INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的 ibdata 文件
• MYISAM 对应的文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件 （data）
  • *.MYI 索引文件 （index）

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

MySQL的默认编码是Latin1，不支持中文

MySQL的utf8是utfmb3，只有三个字节，节省空间但不能表达全部的UTF-8。所以推荐使用utf8mb4。

4.6 修改删除表

修改

• change 可以更改列名 和 列类型

• modify 只能更改列属性 只需要写一次列名, 比change 省事点

-- 修改表名：ALTER TABLE I旧表名RENAME AS新表名
ALTER TABLE teacher RENAME AS teacher1

-- 增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT(11)

-- 修改表的字段（重命名，修改约束！）ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11) -- 修改约束

--  ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重名名

-- 删除表的字段：ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher1 DROP age1

删除

-- 删除表
DROP TABLE IF EXISTS teacher1

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

注意点：

• 字段名，使用 `` 包裹
• 注释 – /* */
• sql关键字大小写不敏感

5. MySQL的数据管理

5.1 外键

• SQL 外键（Foreign Key）用于将两个表连接在一起，让两个表的数据保持同步。

• 外键由表中的一个字段或者多个字段构成，一个表的外键用来指向另一个表的主键（Primary Key）。包含外键的表称为从表，被指向的表称为主表。从表的数据受到主表的约束，向从表中插入或者更新数据时，外键的值必须存在于主表的主键中。

方式一，在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称'，
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键 key
-- 给这个外键添加约束 (执行引用) references 引用

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 '出生日期'，
    `gradeid` INT(10) NOT NULL COMMENT '学生的年级'，
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址'，
    `emai1` VARCHAR(50) DEFAULT NULL COMMENT '邮箱'，
    PRIMARY KEY(`id`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表时，必须先删除引用别人表（从表），再删除被引用的表（主表）

方式二：创建表之后，添加外键约束

CREATE TABLE `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称'，
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键 key
-- 给这个外键添加约束 (执行引用) references 引用

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 '出生日期'，
    `gradeid` INT(10) NOT NULL COMMENT '学生的年级'，
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址'，
    `emai1` VARCHAR(50) DEFAULT NULL COMMENT '邮箱'，
    PRIMARY KEY(`id`),
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 创建表的时候没有外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)

-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 哪个表(哪个字段)

以上都是物理外键，数据库级别的外键，不建议使用（避免数据库过多 造成困扰，这里了解即可）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们想使用多张表的数据，想使用外键（程序去实现）

5.2 DML语言

DML：数据操作语言

• insert
• update
• delete

5.3 添加

insert

-- 插入语句
-- INSERT INTO 表名([字段名1,字段2,字段3]) VALUES('值1'),('值2'),('值3')
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略（如果不写表的字段，他们就会一一匹配
-- 一般写插入语句，我们一定要数据和字段一一对应

-- 插入多个字段
INSERT INTO `grade`(`gradename`) 
VALUES('大一'),('大二')

语法：INSERT INTO 表名([字段名1,字段2,字段3]) VALUES('值1'),('值2'),('值3'，......)

注意事项：

• 字段和字段之间使用英文逗号隔开
• 字段是可以省略的，但是后面的值必须要一一对应，不能少
• 可以同时插入多条数据，valuses后面的值，需要使用(),(),()隔开

5.4 修改

update 修改谁（条件） set原来的值 = 新值

-- 修改
UPDATE `grade` SET `gradename`='高三' WHERE gradeid=1;

-- 不指定的条件下，会改动所有的表
UPDATE `grade` SET `gradename`='高三' ;

-- 修改多个属性，逗号隔开
UPDATE`student` SET `name`='王五',`emai1`='24736743gqg.com' WHERE id=1;

-- 通过多个条件定位数据
UPDATE`student` SET `name`='王五123' WHERE `name`='张三' AND `sex`=	`男`;

语法：UPDATE 表名 SET column_name=value WHERE 条件

条件：where 字句，运算符

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

注意事项：

• column_name是数据库的列，尽量戴上``
• 条件，筛选的条件，如果没有指定，则会修改啊所有列
• value，是一个具体的值，也可以是一个变量
• 多个设置的属性之间，是用英文逗号隔开

UPDATE `student` SET `birthday`=CURRENT._TIME WHERE `name`='长江7号' AND sex='女'

5.5 删除

delete

语法：delete form 表名 [where 条件]

-- 删除数据(避免这样写，会全部删除)
DELETE FROM `student`
-- 删除指定数据
DELETE FROM `student` WHERE id=1;

TRUNCATE 命令

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

-- 清空student表
TRUNCATE `student`

TRUNCATE 与 delete 的区别

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

delete删除的问题，重启数据库，现象：

• Innodb 自增列会从1开始（存在内存中，断电即失）
• MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

6. DQL查询数据

6.1 DQL

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

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

6.2 指定查询字段

-- 查询全部的学生
SELECT * FROM `student`

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`

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

-- 函数	concat(a,b)
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student

语法：SELECT 字段1，... FROM 表

有的时候，列名字不是那么的见名知意，我们起别名 AS 字段名 AS 别名 表名 AS 别名

去重 distinct

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

-- 查询哪些同学参加了考试，成绩
SELECT * FROM result  -- 查询全部的考试成绩
SELECT `studentno` FROM result  -- 查询有那些同学参加了考试
SELECT DISTINCT `studentno` FROM result  -- 发现重复数据，去重

数据库的列

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

-- 学生考试成绩加一
SELECT `studentno`,`studentresult`+1 AS 提分后 FROM result

数据库中的表达式：文本值，列，Null，函数，计算表达上，系统变量

select 表达式 from 表

6.3 where条件子句

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

搜索的条件：由一个或多个表达式组成，结果为布尔值

逻辑运算符

尽量使用英文字母

-- ================where====================
SELECT studentNo,`StudentResult` FROM result

-- 查询考试成绩在95~100分之间
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>=95 AND StudentResult<=100
and
&&
SELECT studentNo,`studentResult` FROM result
WHERE StudentResult>=95 &studentResult<=100

-- 模糊查询（区间）
SELECT studentNo,`studentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentNo,`studentResult` FROM result
WHERE studentNo!=1000;

-- != not
SELECT studentNo,`studentResult` FROM result
WHERE NOT studentNo=1000

模糊查询：比较运算符

-- ===========模糊查询=============
-- 查询姓刘的同学
-- 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 nu11====
-- 查询地址为空的学生 nu11''
SELECT `studentNo` , `studentName`FROM student
WHERE address='' oR address IS NULL

-- 查询有出生日期的同学不为空
SELECT studentNo , `studentName` FROM `student` 
WHERE BornDate` IS NOT NULL

-- 查询没有有出生日期的同学为空
SELECT `studentNo`, `studentName` FROM `student`
WHERE` BornDate` IS NULL

6.4 联表查询

JOIN 对比

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用 left join 时，on 和 where 条件的区别如下：

1. on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。

2. where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

-- ======联表查询join ====================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数)
SELECT *FROM student
SELECT *FROM result

/*思路
1．分析需求，分析查询的字段来自哪些表,(连接查询)
2．确定使用哪种连接查询? 7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentNo = 成绩表的 studentNo
*/
SELECT s.studentNo,studentName,SubjectNo,studentresult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo = r.studentNo

操作	描述
Inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配
full join	只要其中一个表中存在匹配，则返回行

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

• 自连接的基本语法如下：

SELECT a.column_name, b.column_name...
FROM table1 a, table1 b
WHERE a.common_field = b.common_field;

​ 在这里，WHERE子句可以根据您的需要给定的任意表达式。

举例：

1. 考虑下面的两个表中，(a)CUSTOMERS表如下：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

2. 现在，让我们用自连接这个表，如下所示：

SQL> SELECT  a.ID, b.NAME, a.SALARY
     FROM CUSTOMERS a, CUSTOMERS b
     WHERE a.SALARY < b.SALARY;

3. 这将产生以下结果：

+----+----------+---------+
| ID | NAME     | SALARY  |
+----+----------+---------+
|  2 | Ramesh   | 1500.00 |
|  2 | kaushik  | 1500.00 |
|  1 | Chaitali | 2000.00 |
|  2 | Chaitali | 1500.00 |
|  3 | Chaitali | 2000.00 |
|  6 | Chaitali | 4500.00 |
|  1 | Hardik   | 2000.00 |
|  2 | Hardik   | 1500.00 |
|  3 | Hardik   | 2000.00 |
|  4 | Hardik   | 6500.00 |
|  6 | Hardik   | 4500.00 |
|  1 | Komal    | 2000.00 |
|  2 | Komal    | 1500.00 |
|  3 | Komal    | 2000.00 |
|  1 | Muffy    | 2000.00 |
|  2 | Muffy    | 1500.00 |
|  3 | Muffy    | 2000.00 |
|  4 | Muffy    | 6500.00 |
|  5 | Muffy    | 8500.00 |
|  6 | Muffy    | 4500.00 |
+----+----------+---------+

6.5 分页和排序

排序

-- ==========分页 limit 和排序 order by =============

-- 排序：升序 ASC ， 降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果， 成绩降序排序
SELECT s.`studentNo`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = '数据库结构*-1'
ORDER BY studentresult DESC

-- 为什么分页？
-- 缓解数据库压力，给人体验更好  瀑布流
-- 分页，每页显示五条送数据
-- 语法： limit 当前页，页面的大小
SELECT s.`studentNo`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN `result` r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.`subjectno` = '数据库结构*-1'
ORDER BY studentresult DESC
LIMIT 0,5

-- 第1页 limit 0,5
-- 第2页 limit 5,5
-- 第3页 limit 10,5
-- 第3页 limit 10,5
-- 第n页 limit (n-1)*pageSize,pageSize
-- pageSize:页面大小

语法：limit(查询起始下标，pagesize)

6.6子查询

where(这个值是计算出来的)

本质：在 where 中嵌套一个子查询语句

where(select * from)

-- ======= where ==============
-- 查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '数据库结构-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询（有里及外）
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
WHERE subjectno = (
			SELECT subjectno FROM `subject`
			WHERE subjectname = '数据库结构-1'
)
ORDER BY studentresult DESC

6.7 Select小结

顺序很重要：

• select 去重 要查询的字段 from 表 （注意：表和字段可以取别名）
• xxx join 要链接的表 on 等值判断
• where （具体的值，子查询语句）
• Group By （通过哪个字段来分组）
• Having （过滤分组后的信息，条件和 where 是一样的，位置不同）
• Order By … （通过哪个字段排序）【升序/降序】
• Limit startIndex,pageSize

7. MySQL函数

官网：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

需要的时候查就完了

7.1 常用函数

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

-- 字符串函数
SELECT CHAR_LENGTH('hello world') -- 字符串长度
SELECT CONCAT('我','爱','你')  		 -- 拼接字符串
SELECT INSERT(str,pos,len,newstr)		-- 查询，替换
SELECT LOWER(str)  -- 转小写
SELECT UPPER(str)  -- 转大写
SELECT REPLACE(str,from_str,to_str)  -- 替换出线的指定字符串
SELECT SUBSTR(str FROM pos FOR len)  -- 返回指定的子字符串
SELECT REVERSE(str)  -- 反转

-- 查询姓 周的同学，名字 邹
SELECT REPLACE(stuname,'周','邹') FROM student
WHERE studentname LIKE '周%'

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

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

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

7.2 聚合函数

函数	作用
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列的和···

-- ===========聚合函数===================
-- 都能够统计表中的数据(想查询一个表中有多少个记录，就使用这个count() )
SELECT COUNT(`BornDate`) FROM student; -- Count(字段)，会忽略所有的nul1值
SELECT COUNT(*) FROM student; -- count (*)，不会忽略 nu11 值，本质计算行数
SELECT COUNT(1) FROM result; -- Ccount(1)，不会忽略忽略所有的nu11值本质计算行数

SELECT SUM( `studentResult`) AS 总和 FROM result
SELECT AVG( `studentResult`) AS 平均分 FROM result
SELECT MAX( `studentResult`) AS 最高分 FROM result
SELECT MIN( `studentResult`) AS最低分 FROM result

7.3 数据库级别的MD5加密

-- =========== 测试MD5 =============
CREATE TABLE `testmd5`(
		`id` INT(4) NOT NULL,
		`name` VARCHAR(20) NOT NULL,
		`pwd` VARCHAR(50) NOT NULL,
		PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO testmd5 VALUES
(1,'张三','123456'),
(2,'李四','123456'),
(3,'王五','123456');

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1	
UPDATE testmd5 SET pwd=MD5(pwd)  -- 加密全部的

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行MD5加密，然后对比
SELECT * FROM testmd5 WHERE `name` = '小明' AND pwd = MD5('123456')

8. 事务

8.1 什么是事务

要么都成功，要么都失败

---

1. SQL 执行 A 给 B 转账 A 1000 --200–> B 200
2. SQL 执行 B 收到 A 的钱 A 800 ---------> B 400

---

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

事务原则：ACID原则

参考博客链接：https://blog.youkuaiyun.com/dengjili/article/details/82468576

事务具有以下四个标准属性，通常用缩略词 ACID 来表示：

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

隔离所导致的一些问题

脏读：

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

不可重复读：

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

虚读(幻读)：

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

控制事务

有四个命令用于控制事务：

• **COMMIT：**提交更改；
• **ROLLBACK：**回滚更改；
• **SAVEPOINT：**在事务内部创建一系列可以 ROLLBACK 的还原点；
• **SET TRANSACTION：**命名事务

事务流程

1. 开始事务

   开始事务有以下两种命令，选择其一即可：

   COMMIT;
   

   或者

   START TRANSACTION;
   

   该命令用来标记一个事务的起始点。

2. 提交事务使用如下命令：

   COMMIT;
   

   提交事务意味着真正执行事务包含的 SQL 语句，并把对数据库的修改写入到磁盘上的物理数据库中。COMMIT 意味着事务结束，并且执行成功。

3. 回滚事务

   回滚意味着撤销尚未保存到物理数据库中的操作，具体语法格式如下：

   ROLLBACK;
   

   事务执行过程中如果发生了某种故障，事务不能继续执行，就可以撤销事务，此时对数据库的修改并不会保存到物理数据库中。撤销意味着事务结束，并且执行失败。

4. 回滚标记点

   ROLLBACK 命令默认回滚整个事务，也即事务中的所有修改操作都无效。但是 SQL 允许回滚事务的一部分，此时需要在事务中设置一个标记点，在该标记点之后的 SQL 语句将被回滚，之前的 SQL 语句将被成功执行。

   设置标记点使用 SAVEPOINT 命令，具体语法如下：

   SAVEPOINT point_name;
   

   point_name 为标记点名字。

   回滚到标记点使用 ROLLBACK TO 命令，具体语法如下：

   ROLLBACK TO point_name;
   

9. 索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构

9.1 索引的分类

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

显示索引信息

使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

mysql> SHOW INDEX FROM table_name\G
........

常规索引

1. 创建索引

   这是最基本的索引，它没有任何限制。它有以下几种创建方式：

   CREATE INDEX indexName ON table_name (column_name)
   

   如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2. 修改表结构(添加索引)

   ALTER table tableName ADD INDEX indexName(columnName)
   

3. 创建表的时候直接指定

   CREATE TABLE mytable(  
    
   ID INT NOT NULL,   
    
   username VARCHAR(16) NOT NULL,  
    
   UNIQUE [indexName] (username(length))  
    
   );  
   

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

1. 创建索引

   CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
   

2. 修改表结构

   ALTER table mytable ADD UNIQUE [indexName] (username(length))
   

3. 创建表的时候直接指定

   CREATE TABLE mytable(  
    
   ID INT NOT NULL,   
    
   username VARCHAR(16) NOT NULL,  
    
   UNIQUE [indexName] (username(length))  
    
   );  
   

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

• **ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)😗*该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
• **ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ，用于全文索引。

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

9.2 索引原则

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

索引的数据结构

详见：https://www.jianshu.com/p/1775b4ff123a

10. 权限管理与备份

10.1 用户管理

SQL 可视化管理

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER zhangsan IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('111111')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR zhangsan = PASSWORD('111111')

-- 重命名 RENAME USER 原来名字 TO 新名字
RENAME USER zhangsan TO zhangsan2

-- 用户授权 ALL PRIVILEGES 全部的权限
-- ALL PRIVILEGES 除了给别人授权，什么都能干
GRANT ALL PRIVILEGES ON *.* TO zhangsan2

-- 查询权限
SHOW GRANTS FOR zhangsan2 -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost
-- root用户的权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' with GRANT OPTION

-- 撤销权限 REVOKE 哪些权限， 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM zhangsan2

-- 删除用户
DROP USER zhangsan2

mysql 5.7.9以后废弃了password字段和password()函数；

authentication_string:字段表示用户密码，而authentication_string字段下只能是mysql加密后的41位字符串密码。

所以需要用一下方式来修改root密码：

参考：https://blog.youkuaiyun.com/wolf131721/article/details/93004013

ALTER user 'root'@'localhost' IDENTIFIED BY 'newpassword';

MySql 从8.0开始修改密码有了变化，在user表加了字段authentication_string，修改密码前先检查authentication_string是否为空

• 如果为空，直接修改

  ALTER user 'root'@'localhost' IDENTIFIED BY 'root'; -- 修改密码为root
  

• 如果不为空

  use mysql; 
   
  update user set authentication_string='' where user='root';-- 将字段置为空
   
  ALTER user 'root'@'localhost' IDENTIFIED BY 'root';-- 修改密码为root
  

• 如果出现如下错误

  ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
  mysql> GRANT ALL PRIVILEGES ON *.* TO IDENTIFIED BY '123' WITH GRANT OPTION;
  

  需要执行

  flush privileges;
  

  再执行

  ALTER user 'root'@'localhost' IDENTIFIED BY 'root';-- 修改密码为root
  

10.2 数据库备份

MySQL备份的方式：

• 直接拷贝物理文件

• 在SQL可视化工具中手动导出

  

• 使用命令行导出mysqldump命令行导出

  # mysqldump -h 主机 -u 用户名 -p 密码 数据库 (表1 表2 表3) > 物理磁盘位置/文件名
  mysqldump -h localhost -u root -p 123456 school student > D:/a.sql
  
  # 导入
  # 登录的情况下，切换到指定的数据库
  # source 备份文件
  source d:/a.sql
  
  mysql -u 用户名 -p 密码 库名 < 备份文件
  

11. 规范数据库设计

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

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

设计数据库的步骤：（个人博客）

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

11.1 三大数据库范式

详见：https://blog.youkuaiyun.com/weixin_44355591/article/details/106194714

第一范式（1NF）

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

第二范式（2NF）

• 前提：满足第一范式
• 第二范式要求确保表中每列与主键相关，而不能只与主键的某部分相关（主要针对联合主键），主键列与非主键列遵循完全函数依赖关系，也就是完全依赖（每张表只描述一件事）

第三范式（3NF）

• 前提：满足第二范式
• 第三范式确保主键列之间没有传递函数依赖关系，也就是消除传递依赖（确保数据表中的每一列数据都和主键直接相关，而不能间接相关）

12. JDBC

JDBC的全称是Java数据库连接(Java Database connect)，它是一套用于执行SQL语句的Java API。应用程序可通过这套API连接到关系数据库，并使用SQL语句来完成对数据库中数据的查询、更新和删除等操作

需要将 mysql-connector-java-8.0.28.jar 导入library

测试代码：

package com.swust.lesson01;

import com.mysql.cj.jdbc.Driver;

import java.sql.*;

public class JdbcDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        
        // 1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动

        // 2.用户信息和url
        // userUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true";
        String username =  "root";
        String password = "123456";

        // 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.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"));
        }

        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结：

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

DriverManager

// DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
Class.forName("com.mysql.cj.jdbc.Driver"); // 固定写法，加载驱动

Connection connection = DriverManager.getConnection(url, username, password);
//connection 代表数据库
//数据库自动提交
//事务提交
//事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();

URL

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

// mysql -- 3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3

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

Statement 执行SQL的对象 PreparedStatement 执行SQL的对象

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

statement.executeQuery(); // 查询操作，返回Result
statement.execute(); // 执行任何SQL
statement.executeUpdate(); // 更新、插入、删除，返回一个受影响的行数

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

resultSet.getObject(); // 在不知道列类型的情况下使用
// 如果知道就用指定类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
......

遍历，指针

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

释放资源

resultSet.close();
statement.close();
connection.close();

12.1 statement对象

Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可

代码实现

1. 提取工具类

   driver=com.mysql.cj.jdbc.Driver
   url=jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true
   username=root
   password=123456
   

   package com.swust.lesson02.utils;
   
   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 {
               InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
               Properties properties = new Properties();
               properties.load(inputStream);
   
               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);
       }
   
       // 释放连接资源
       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();
               }
           }
       }
   }
   

2. 编写增删改查，executeUpdate

   package com.swust.lesson02;
   
   import com.swust.lesson02.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 connection = null;
           Statement statement = null;
           ResultSet resultSet = null;
   
           try {
               connection = JdbcUtils.getConnection(); // 获取数据库连接
               statement = connection.createStatement(); // 获得sql的执行对象
               String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`)"+
                       "VALUES(5,'zhangsan','123456','123456@qq.com','2022-01-01')"; // 增加
   
   //            String sql = "DELETE users WHERE id = 5"; // 删除
   
   
               int i = statement.executeUpdate(sql);
   
               if(i>0){
                   System.out.println("插入成功！");
   //                System.out.println("删除成功！");
               }
   
           } catch (SQLException e) {
               e.printStackTrace();
           }finally {
               JdbcUtils.release(connection,statement,resultSet);
           }
       }
   }
   

SQL注入问题

Sql 注入攻击是通过将恶意的 Sql 查询或添加语句插入到应用的输入参数中，再在后台 Sql 服务器上解析执行进行的攻击，它目前黑客对数据库进行攻击的最常用手段之一。SQL会被拼接 or

12.2 PreparedStatement 对象

PreparedStatement 可以防止 SQL 注入，且效率更高

1. 新增

   package com.swust.lesson03;
   
   import com.swust.lesson02.utils.JdbcUtils;
   
   import java.sql.ResultSet;
   import java.util.Date;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.SQLException;
   
   public class TestInsert {
       public static void main(String[] args) {
           Connection connection = null;
           PreparedStatement preparedStatement = null;
           ResultSet resultSet = null;
   
           try {
               connection = JdbcUtils.getConnection();
   
               // 区别
               // 使用 ？ 占位符代替参数
               String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
               preparedStatement = connection.prepareStatement(sql); // 预编译SQL ，先写sql， 然后不执行
   
               // 手动给参数赋值
               preparedStatement.setInt(1,6); // id
               preparedStatement.setString(2,"lisi"); // NAME
               preparedStatement.setString(3,"123456"); // PASSWORD
               preparedStatement.setString(4,"123456@qq.com"); // email
               // 注意点： sql.Date   数据库  java.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("插入成功！");
               }
   
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           } finally {
               JdbcUtils.release(connection,preparedStatement, null);
           }
       }
   }
   

2. 删除

   package com.swust.lesson03;
   
   import com.swust.lesson02.utils.JdbcUtils;
   
   import java.sql.ResultSet;
   import java.util.Date;
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.SQLException;
   
   public class TestDelete {
       public static void main(String[] args) {
           Connection connection = null;
           PreparedStatement preparedStatement = null;
           ResultSet resultSet = null;
   
           try {
               connection = JdbcUtils.getConnection();
   
               // 区别
               // 使用 ？ 占位符代替参数
               String sql = "Delete from users where id = ?";
               preparedStatement = connection.prepareStatement(sql); // 预编译SQL ，先写sql， 然后不执行
   
               // 手动给参数赋值
               preparedStatement.setInt(1,6);
   
               // 执行
               int i = preparedStatement.executeUpdate();
               if (i > 0){
                   System.out.println("删除成功！");
               }
   
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           } finally {
               JdbcUtils.release(connection,preparedStatement, null);
           }
       }
   }
   

3. 更新

   package com.swust.lesson03;
   
   import com.swust.lesson02.utils.JdbcUtils;
   
   import java.sql.Connection;
   import java.sql.PreparedStatement;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   
   public class TestUpdate {
       public static void main(String[] args) {
           Connection connection = null;
           PreparedStatement preparedStatement = null;
           ResultSet resultSet = null;
   
           try {
               connection = JdbcUtils.getConnection();
   
               // 区别
               // 使用 ？ 占位符代替参数
               String sql = "update users set `NAME` = ? where id = ?";
               preparedStatement = connection.prepareStatement(sql); // 预编译SQL ，先写sql， 然后不执行
   
               // 手动给参数赋值
               preparedStatement.setString(1,"wangwu");
               preparedStatement.setInt(2,5);
   
               // 执行
               int i = preparedStatement.executeUpdate();
               if (i > 0){
                   System.out.println("更新成功！");
               }
   
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           } finally {
               JdbcUtils.release(connection,preparedStatement, null);
           }
       }
   }
   

4. 查询

   package com.swust.lesson03;
   
   import com.swust.lesson02.utils.JdbcUtils;
   
   import java.sql.*;
   
   public class TestSelect {
       public static void main(String[] args) {
           Connection connection = null;
           PreparedStatement preparedStatement = null;
           ResultSet resultSet = null;
   
           try {
               connection = JdbcUtils.getConnection();
   
               String sql = "select * from users where  id = ?";
   
               preparedStatement = connection.prepareStatement(sql);
               preparedStatement.setInt(1,1);
   
               // 执行
               resultSet = preparedStatement.executeQuery();
   
               if(resultSet.next()){
                   System.out.println(resultSet.getString("NAME"));
               }
   
           } catch (SQLException throwables) {
               throwables.printStackTrace();
           } finally {
               JdbcUtils.release(connection,preparedStatement,resultSet);
           }
       }
   }
   

5. PreparedStatement防注入的原因：

   之所以PreparedStatement能防止注入，是因为它把单引号转义了，变成了’，这样一来，就无法截断SQL语句，进而无法拼接SQL语句，基本上没有办法注入了。

12.3 使用IDEA连接数据库

点击Test Connection 测试

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Di6IS6AP-1655897795101)(https://s2.loli.net/2022/06/22/ouHs5GDNpK8LeQa.png)]

如果出现 Server returns invalid timezone. Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually. 错误

解决方法： 在 mysql 目录下的 my.ini 中 修改时区：

[mysqld] 下 添加 default-time-zone=‘+08:00’

成功

点击应用，等待加载完毕

选择数据库：

双击数据库：

修改：

写sql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-3JUH18Oe-1655897795111)(https://s2.loli.net/2022/06/22/pzX2APGj6WaDgEF.png)]

12.4 JDBC操作事务

代码实现

1. 开启事务connection.setAutoCommit(false);
2. 一组业务执行完毕，提交事务
3. 可以在 catch 语句中 显示定义 回滚语句 ，但是默认失败就会回滚 （显示定义不写也行）

package com.swust.lesson04;

import com.swust.lesson02.utils.JdbcUtils;

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

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

        try {
            connection = JdbcUtils.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("成功！");

        } catch (SQLException throwables) {
            // 如果失败，默认回滚
//            try {
//                connection.rollback(); // 如果失败则回滚事务
//
//            } catch (SQLException e) {
//                e.printStackTrace();
//            }
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection,preparedStatement, null);
        }
    }
}

12.5 数据库连接池

数据库连接 ---- 执行完毕 ---- 释放

连接 — 释放 十分浪费系统资源

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

什么是数据库连接池：https://blog.youkuaiyun.com/guobinhui/article/details/85157805

开源数据源实现

DBCP

C3p0

Druid:阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4

commons-pool-1.6

dbcp-2需要额外导入commons-logging的jar包

配置文件dbcp.properties：

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

#初始化连接
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_COMMITTED

工具类：

package com.swust.lesson05.utils;

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

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

public class JdbcUtils_DBCP {

    private static BasicDataSource dataSource = null;

    static {
        try {
            InputStream inputStream = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfige.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 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();
            }
        }
    }
}

测试代码：

package com.swust.lesson05;
import com.swust.lesson05.utils.JdbcUtils_DBCP;

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

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

        try {
            connection = JdbcUtils_DBCP.getConnection();

            // 区别
            // 使用 ？ 占位符代替参数
            String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql); // 预编译SQL ，先写sql， 然后不执行

            // 手动给参数赋值
            preparedStatement.setInt(1,7); // id
            preparedStatement.setString(2,"lisi"); // NAME
            preparedStatement.setString(3,"123456"); // PASSWORD
            preparedStatement.setString(4,"123456@qq.com"); // email
            // 注意点： sql.Date   数据库  java.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("插入成功！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(connection,preparedStatement, null);
        }
    }
}

C3P0

需要用到的jar包

c3p0-0.9.5.5.jar

mchange-commons-java-0.2.19.jar

配置文件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/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=false&amp;serverTimezone=UTC</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">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
    -->
    <name-config name="MySQL">
        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&amp;characterEncoding=utf8&amp;useSSL=true</property>
        <property name="user">root</property>
        <property name="password">123456</property>
        <property name="acquiredIncrement">5</property>
        <property name="initialPoolSize">10</property>
        <property name="minPoolSize">5</property>
        <property name="maxPoolSize">20</property>
    </name-config>
</c3p0-config>

工具类：

package com.swust.lesson05.utils;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

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 JdbcUtils_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 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();
            }
        }
    }
}

测试代码：

package com.swust.lesson05;
import com.swust.lesson05.utils.JdbcUtils_C3P0;
import com.swust.lesson05.utils.JdbcUtils_DBCP;

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

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

        try {
            connection = JdbcUtils_C3P0.getConnection();

            // 区别
            // 使用 ？ 占位符代替参数
            String sql = "INSERT INTO users(id ,`NAME`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql); // 预编译SQL ，先写sql， 然后不执行

            // 手动给参数赋值
            preparedStatement.setInt(1,7); // id
            preparedStatement.setString(2,"lisi"); // NAME
            preparedStatement.setString(3,"123456"); // PASSWORD
            preparedStatement.setString(4,"123456@qq.com"); // email
            // 注意点： sql.Date   数据库  java.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("插入成功！");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_C3P0.release(connection,preparedStatement, null);
        }
    }
}

总结

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