【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 2^8-1 281
  • text 文本串 2 1 6 − 1 2^16-1 2161 保存大文本

时间日期

  • 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 早些年使用的

MYISAMINNODB
事物支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大,约为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=6false
<> 或 !=不等于5<>6true
>
<
<=
>=
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条件子句

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

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

逻辑运算符

image-20220605173256706

尽量使用英文字母

-- ================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

模糊查询:比较运算符

image-20220605175131405

-- ===========模糊查询=============
-- 查询姓刘的同学
-- 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 时,onwhere 条件的区别如下:

  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 |
+----+----------+-----+-----------+----------+
  1. 现在,让我们用自连接这个表,如下所示:
SQL> SELECT  a.ID, b.NAME, a.SALARY
     FROM CUSTOMERS a, CUSTOMERS b
     WHERE a.SALARY < b.SALARY;
  1. 这将产生以下结果:
+----+----------+---------+
| 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.csdn.net/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.csdn.net/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可视化工具中手动导出

    image-20220620152719178

  • 使用命令行导出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.csdn.net/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

image-20220622144131173

测试代码:

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连接数据库

image-20220622170001699

点击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’

image-20220622171102272

成功

image-20220622171540436

点击应用,等待加载完毕

image-20220622171843083

选择数据库:

image-20220622172513198

双击数据库:

image-20220622172953577

修改:

动画

写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.csdn.net/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接口不会变,方法就不会变

  • 1
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值