MySQL

最新推荐文章于 2023-08-15 18:33:25 发布

Healerjy

最新推荐文章于 2023-08-15 18:33:25 发布

阅读量155

点赞数

文章标签： spring boot java restful

本文链接：https://blog.csdn.net/Healerjy/article/details/130755644

版权

MySQL

1.1、连接数据库

命令行操作

mysql -uroot -p'连接密码' --连接数据库
update mysql.user set authentication_string=password('修改后的密码')
where user='root' and Host='localhost';--修改用户密码
flush privileges--刷新权限

1.2、操作数据库

mysql关键字不区分大小写

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

创建数据库

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

删除数据库
```
DROP DATABASE IF EXISTS test
```

使用数据库

--如果表名或字段名是一个特殊字符，需要带``字符 如: select `user` from test
use test --use 数据库名

查看数据库

SHOW DATABASES--显示所有存在的数据库

1.3、数据库字段的数据类型

数值

tinyint 很小的数据 1个字节
smallint 较小的数据 2个字节
int 标准的整数 4个字节常用
bigint 较大的数据 8个字节
float 浮点数（单精度） 4个字节
double 浮点数（双精度） 8个字节
decimal 字符串形式的浮点数常用于金融计算

字符串

char 固定大小的字符串 0-255
varchar 可变字符串 0-65535 常用对应java中的String
tinytext 微型文本 2^8-1
text 文本 2^16-1 保存大文本

时间日期

date YYYY-MM-DD 日期
time HH:mm:ss 时间格式
datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
timestamp 时间戳，1970-1-1到现在的毫秒数也较为常用
year 年份

null

没有值，未知
不要使用null进行运算，否则结果为null

1.4、数据库的字段属性

Unsigned

无符号的整数
限制该列不能声明为负数

zerofill

0填充
不足的位数使用0来填充如： int （3) 5 --> 005

自增

通常是自动在上一条记录的基础上加1
通常用来设置唯一的主键，且必须是整数类型
可以自定义设计主键自增的起始值和步长（每次递增的数值）

非空

NULL 和 NOT NULL
NOT NULL 如果不给它赋值会报错
NULL 如果不给它赋值，默认为null

默认default

设置默认的值
如 sex 字段默认值为男，如果不指定该列的值则默认为男

1.5、创建数据表

sql语句创建数据表

CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(50) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(50) NOT NULL DEFAULT '123' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  PRIMARY KEY (`id`)
)
--PRIMARY KEY (`主键字段名`) 设置主键
--每一行后需要加上,最后一行可以不加
CREATE TABLE `表名`(
     `字段名` 列类型 [属性] [索引] [注释]，--[]中的属于非必须项，可写可不写
     `字段名` 列类型 [属性] [索引] [注释]，
     `字段名` 列类型 [属性] [索引] [注释]，
      .........
     `字段名` 列类型 [属性] [索引] [注释]，
      PRIMARY KEY (`主键字段名`)
)

常用命令

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

1.6、数据库引擎

-- 数据库引擎
/*
INNODB     默认使用
MYISAM     常在早些年使用

*/

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

常规使用操作

MYISAM 节约空间速度较快
INNODB 安全性高支持事务的处理多表多用户操作（外键支持）

在物理空间存在的位置

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

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

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

设置数据库字符集编码

CHARSET=utf8 --不设置会使用mysql的默认编码（不支持中文）

MySQL的默认编码为Latin1,不支持中文

在My.ini中配置默认编码

character-set-server=utf8

1.7、修改删除表

修改表sql代码

-- 修改表名  ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1

-- 添加表的字段  ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)

-- 修改表的字段 （重命名，修改约束）

-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE student1 MODIFY age VARCHAR(15) -- 修改约束

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

-- 删除表字段
ALTER TABLE student1 DROP age1

-- 删除表（如果表存在再删除）
DROP TABLE IF EXISTS student

-- 注：所有创建和删除尽量加上判断，避免报错

1.8、MySQL数据管理

外键

方式一：创建表时添加外键约束

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

-- 学生表的gradeid字段要去引用年级表的gradeid
-- 定义外键key
-- 给外键添加约束（执行引用）REFERENCES
CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(50) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(50) NOT NULL DEFAULT '123' 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 '家庭住址',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

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

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

CREATE TABLE `student` (
  `id` INT NOT NULL  AUTO_INCREMENT COMMENT '学号',
  `name` VARCHAR(50) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` VARCHAR(50) NOT NULL DEFAULT '123' 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 '家庭住址',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

-- 为已创建的表添加外键
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 引用的表 (引用字段)
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (gradeid) REFERENCES `grade` (`gradeid`)

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

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

最佳实践

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

1.9、DML语言(数据操作语言)

数据库的意义：数据存储，数据管理

常用DML语言:

insert
delete
update

insert(插入)

-- 插入语句（添加）
-- insert into 表名（[字段1，字段2，字段3,......]）values（'值1','值2','值3',......）

INSERT INTO `grade`(`gradename`) VALUES('大一')
-- 由于主键自增可以省略（如果不写表的字段，就会一一匹配）
-- INSERT INTO `grade` VALUES('大一')  等同于 INSERT INTO `grade`(`gradeid`,`gradename`) VALUES('大一')
-- 一般写插入语句时，一定要数据和字段一一对应

-- 插入多个值（一次插入多条值）
INSERT INTO `grade`(`gradename`)VALUES('大二'),('大三')

-- 插入多个字段
INSERT INTO `student`(`name`)VALUES('李杰')
INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('李杰','1234567','女')
INSERT INTO `student`(`name`,`pwd`,`sex`)VALUES('李华','1234567a','男'),('李梅','1234567b','女')

注意事项：

1.字段与字段之间使用英文逗号隔开

2.字段是可以省略的，但是后面的值必须一一对应，不能少

3.可以同时插入多条值，VALUES后面的值需要使用逗号隔开，如 VALUES(’ ‘),(’ ')

update(修改)

-- update 表名 set 字段名=value where(指定条件)
-- 修改学生姓名
UPDATE `student` SET `name`='李杰杰' WHERE id=7

-- 不指定条件的情况下，会修改所有的name的值
UPDATE `student` SET `name`='李杰杰'

-- 修改多个属性，字段名（列名）用逗号隔开
UPDATE `student` SET `name`='李杰',`address`='浙江杭州' WHERE id=7

条件：where子句运算符如id大于某个值、等于某个值、在某个区间内修改…

操作符会返回 布尔值

操作符	含义	范围	结果
=	等于	5=6	false
<> 或 !=	不等于	5<>7	true
>	大于	7>9	false
<	小于	7<9	true
>=	大于等于	17>=9	true
<=	小于等于	7<=6	false
between…and…	在某个区间内	between 2 and 5	[2,5]
and	x和y &&	7>5 and 7<6	false
or	x或y \|\|	7>5 or 7<6	true

注意：

字段名是数据库的列，尽量带上``
条件筛选的条件，如果没有指定条件，则会修改所有的列
修改多个属性，字段名（列名）用逗号隔开

delete(删除)

-- 删除数据
-- delete from 表名 where（条件）
DELETE FROM `student` WHERE id=8

-- 下面的写法会删除表的全部数据，避免这样写
DELETE FROM `student`

truncate(清空命令)

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

-- 清空student表
TRUNCATE `student`

truncate和delete的区别

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

了解即可：DELETE删除的问题，重启数据库，现象

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

2.0、DQL查询数据（重点）

SELECT完整语法

SELECT语法

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]        -- 指定结果需满足的条件
[GROUP BY…]     -- 指定结果按照哪几个字段来分组
[HAVING…]       -- 过滤分组的记录必须满足的次要条件
[ORDER BY…]     -- 指定查询记录按一个或多个条件排序
[WITH OWNERACCESS OPTION]

DQL：Data Query Language 数据查询语言

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

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

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

-- 别名  给查询结果起一个名字 AS 可以给字段取别名，也可以给表取别名
SELECT `name` AS 姓名,`gradeid` AS 年级编号 FROM student

-- 函数 concat(a,b) 拼接字符串
SELECT CONCAT('姓名：',NAME) AS 新名字 FROM student

语法：select 字段,… from 表名

去重 distinct

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

-- 查询去重   SELECT DISTINCT 字段名 FROM 表名
SELECT DISTINCT NAME FROM student

数据库的列（表达式）

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

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

select 表达式 from 表

where条件子句

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

逻辑运算符

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

-- 查询考试成绩在95-100之间
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentresult`>=95 AND `studentresult`<=100

-- and  &&
SELECT `studentno`,`studentresult` FROM result 
WHERE  `studentresult`>=95 && `studentresult`<=100

-- 模糊查询（区间） BETWEEN   AND
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentresult` BETWEEN 95 AND 100

-- 除了学号为1之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentno`!=1

-- not !=
SELECT `studentno`,`studentresult` FROM result 
WHERE NOT `studentno`

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为null ,结果为真
IS NOT NULL	a is not null	如果操作符为 not null ,结果为真
BETWEEN AND	a BETWEEN b AND c	如果a在b和c之间，结果为真
Like	a like b	SQL匹配，如果a匹配b,结果为真
In	a in (a1,a2,a3,…)	假设a在 a1,a2,a3,…其中的某一个值中，结果为真

-- 模糊查询
-- like  结合 %（代表0到任意个字符） _（代表一个字符）
-- 查询姓王的学生
SELECT `name` FROM `student` WHERE `name` LIKE '王%'

-- 查询姓王的学生,名字后面只有一个字的
SELECT `name` FROM `student` WHERE `name` LIKE '王_'

-- 查询姓王的学生,名字后面只有两个字的
SELECT `name` FROM `student` WHERE `name` LIKE '王__'

-- 查询名字中间有子的学生
SELECT `name` FROM `student` WHERE `name` LIKE '%子%'


--  in （具体的一个或多个值）
-- 查询 id 为 4，5，6的学生
SELECT `id`,`name` FROM student WHERE id IN(4,5,6)

联表查询

JOIN

--  联表查询 join --

--  查询参加考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM `student`
SELECT * FROM `result`

/*思路
1.分析需求，分析查询的字段来自那些表，（连接查询）
2.确定使用哪种连接查询？  共7种连接查询  
3.确定交叉点 （两张表中那个字段是相同的）
  判断条件  学生表中的  studentNo = 成绩表中的 studentNo
*/
-- INNER JOIN --
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
INNER JOIN result AS r
ON s.studentno=r.studentno

-- RIGHT JOIN --
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
RIGHT JOIN result AS r
ON s.studentno=r.studentno

-- LEFT JOIN --
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
LEFT JOIN result AS r
ON s.studentno=r.studentno

-- 查询缺考的同学 --
SELECT s.studentno,studentname,subjectno,studentresult
FROM student AS s 
LEFT JOIN result AS r
ON s.studentno=r.studentno
WHERE studentresult IS NULL

-- join on 连接查询
-- where   等值查询

--  三表查询
--  查询参加考试的同学（学号，姓名，科目编号，分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student AS s 
RIGHT JOIN result AS r
ON s.studentno=r.studentno
INNER JOIN
SUBJECT AS sub 
ON r.subjectno=sub.subjectno

操作	描述
inner join	如果表中至少有一个匹配，就返回行
left join	会从左表中返回所有值，即使右表中没有匹配
right join	会从右表中返回所有值，即使左表中没有匹配

自连接

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

父类

categoryid	categoryname
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryname
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

操作：查询父类对应子类的关系

父类	子类
信息技术	办公信息
软件开发	数据库 web开发
美术设计	ps技术

-- 查询父子信息  把一张表拆为两张一样的表
SELECT a.`categoryname` AS '父栏目',b.`categoryname` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid``category``grade``subject`

--  联表查询练习--
--  查询学生所属年级--
SELECT `studentno`,`studentname`,`gradename`
FROM `student` AS s INNER JOIN `grade` AS g
ON 
s.`gradeid`=g.`gradeid` 

--  查询科目所属的年级 --
SELECT `subjectname`,`gradename`
FROM `subject` AS sub INNER JOIN `grade` AS g
ON 
sub.`gradeid`=g.`gradeid`

--  查询参加数据结构考试的学生信息：学号、姓名、科目名、分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s INNER JOIN `result` r
ON
s.`studentno`=r.`studentno`
INNER JOIN
`subject` sub
ON
sub.`subjectno`=r.`subjectno`
WHERE sub.`subjectname` LIKE('数据结构')

2.1、分页和排序

排序

--  分页 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
sub.`subjectno`=r.`subjectno`
WHERE sub.`subjectname`='数据结构'
ORDER BY `studentresult` ASC

分页

--  为什么要分页？
--  缓解数据库压力，用户体验更好
--  语法： limit 起始值，页面的大小
--  limit 0,5   1-5
--  limit 1,5   2-6
--  limit 6,5   7-11

--  网页应用： 当前页  总页数  页面大小
--  第一页 limit 0,5
--  第二页 limit 5,5
--  第三页 limit 10,5
--  第N页  limit (N-1)*5,5
--  公式   (N-1)*pageSize,pageSize
--  pageSize(页面大小)  N(当前页)  (N-1)*pageSize（起始值）
--  总页数=数据总数/页面大小

SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s INNER JOIN `result` r
ON
s.`studentno`=r.`studentno`
INNER JOIN
`subject` sub
ON
sub.`subjectno`=r.`subjectno`
WHERE sub.`subjectname`='数据结构'
ORDER BY `studentresult` ASC
LIMIT 0,5

语法：limit(查询起始下标，页面大小)

2.2、子查询

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

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

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

-- 方式二：使用子查询(由里到外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result`
WHERE `subjectno` =(SELECT `subjectno` FROM `subject` WHERE `subjectname`='数据结构')
ORDER BY `studentresult` DESC

2.3、MySQL函数

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

常用函数

--  ====常用函数====
-- 数学运算
SELECT ABS(-9)      -- 取绝对值
SELECT CEILING(6.7) -- 向上取整
SELECT FLOOR(7.8)   -- 向下取整
SELECT RAND()       -- 返回一个0-1之间的随机数
SELECT SIGN(20)    -- 返回参数的符号  负数返回-1  正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('123456789')        -- 返回字符串长度
SELECT CONCAT('J','A','V','A')         -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级爱') -- 从1的位置开始替换2个长度
SELECT LOWER('Java')                   --  小写
SELECT UPPER('Java')                   --  大写
SELECT INSTR('java','a')               --  返回第一次出现的索引
SELECT REPLACE('坚持就能成功','坚持','努力') -- 替换指定字符串
SELECT SUBSTR('坚持就能成功',1,5)      --  从第1个开始截取5个字符串
SELECT REVERSE('坚持就能成功')         --  反转字符串

-- 时间和日期函数
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()       -- mysql版本

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均数
MAX()	最大值
MIN()	最小值
…	…

--  聚合函数 --

-- 查询一个表中有多少条记录使用count()
SELECT COUNT(`studentname`) FROM `student`  -- count(字段) 会忽略null值
SELECT COUNT(*) FROM `student`              -- count(*)    不会忽略null值  本质 计算行数
SELECT COUNT(1) FROM `student`              -- count(1)    不会忽略null值  本质 计算行数

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

-- GROUP BY  分组
-- 查询不同课程的平均分、最高分、最低分
-- 核心：根据不同的课程分组
SELECT `subjectname`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
FROM `result` AS r
INNER JOIN  `subject` AS sub
ON 
r.`subjectno`=sub.`subjectno`
GROUP BY `subjectname`
HAVING AVG(`studentresult`)>80

2.4、数据库级别的MD5加密（拓展）

什么是MD5 ?

主要增强算法复杂度和不可逆性

MD5不可逆，具体值的MD5是一样的

MD5破解网站的原理，背后有一个字典，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` (`id`,`name`,`pwd`)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` (`id`,`name`,`pwd`)VALUES(4,'李华',MD5('123456'))

--  如何校验： 将用户传递进来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='李华' AND pwd=MD5('123456')
--  注意：进行第二次加密后的pwd,使用MD5(pwd)查询的结果为空

2.5、事务

什么是事务？

要么都成功，要么都失败

1、SQL执行 A给B转账 A 1000 --> 200 B 200

2、SQL执行 B收到A的转账 B 400 A 800

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

事务原则：ACID原则原子性，一致性，隔离性，持久性（脏读，幻读…）

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

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后数据的完整性必须保持一致

隔离性（Isolation）

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

持久性（Durability）

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

事务的隔离级别

脏读：

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

幻读：

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

执行事务

--  === 事务 ===
-- MySQL默认开启事务自动提交

SET autocommit=0  /* 关闭*/
SET autocommit=1  /* 开启(默认的)*/

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

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


-- 提交：持久化 (成功)
COMMIT

-- 回滚:回到原来的样子 （失败）
ROLLBACK

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

-- 了解
SAVEPOINT  保存点名 -- 设置一个事务的保存点

ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点

RELEASE  SAVEPOINT 保存点名    -- 撤销保存点

模拟场景

--  === 模拟转账 ===
--  创建数据库
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

USE shop -- 使用shop数据库

-- 创建表
CREATE TABLE `account`(
  `id`  INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL ,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `account` (`name`,`money`) VALUES('A',1000.00),('B',2000.00)

-- 模拟转账


SET autocommit=0   -- 关闭自动提交

START TRANSACTION  -- 开启事务

UPDATE `account` SET `money`=`money`-500 WHERE `name`='A'  -- A-500

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

COMMIT; -- 提交事务，一旦被提交就持久化了

ROLLBACK -- 回滚

SET autocommit=1  -- 开启自动提交

2.6、索引

MySQL官方对索引的定义为：**索引（Index）是帮助MySQL高效获取数据的数据结构。**提取句子主干，就可以得到索引的本质：索引是数据结构。

索引的分类

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

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

普通索引

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


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

-- 增加索引
ALTER TABLE student ADD FULLTEXT INDEX `studentname`(`studentname`)

-- EXPLAIN  分析sql执行的情况
EXPLAIN SELECT * FROM student -- 非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(`studentname`) AGAINST('张')

测试索引

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT NULL,
`eamil` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT NULL,
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '' ,
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 插入100万条数据
DELIMITER $$  -- 写函数之前必须要写，作为标志

CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
     DECLARE num INT DEFAULT 1000000;
     DECLARE i INT DEFAULT 0;
     
     WHILE i<num  DO
       INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('用户',i),'28487277327@qq.com',CONCAT('19',FLOOR(RAND()*999999999)),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
      SET i=i+1;
      END WHILE;
      RETURN i;
END;

SELECT mock_data() -- 查找函数

SELECT * FROM `app_user` WHERE `name`='用户9999'  -- 用时1.715秒
SELECT * FROM `student`                           -- 用时0.004秒

EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户9999'

-- 创建索引
-- id_表名_字段名
-- CREATE INDEX 索引名 ON 表(字段)
CREATE INDEX id_app_user_name ON app_user(`name`);

-- 创建索引后 再次执行SELECT * FROM `app_user` WHERE `name`='用户9999' 

SELECT * FROM `app_user` WHERE `name`='用户9999' -- 用时0.003秒
SELECT * FROM `app_user`

注：索引在小数据的时候用处不大，但是在大数据的时候，区别很大

索引原则

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

索引的数据结构

Hash类型的索引

Btree:InnoDB的默认数据结构

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

2.7、权限管理和备份

用户管理

SQL yog可视化管理

在这里插入图片描述

SQLyog连接数据库报错 plugin caching_sha2_password could not be loaded

解决办法：

// 运行cmd 启动Mysql服务
net start mysql

// 登录Mysql(需输入密码)
mysql -用户名 root -p

// 选择数据库(这一步不可省略)
use mysql

// 查看plugin设置
select host, user, plugin from user;

// 可以看到root的plugin是caching_sha2_password，我们希望改成mysql_native_password
ALTER USER root@localhost IDENTIFIED WITH mysql_native_password BY '密码';

SQL命令操作

用户表：mysql.user

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

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

CREATE USER gjy01 IDENTIFIED BY '123456'

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

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

-- 重命名  RENAME USER 原来的名字 TO 新名字
RENAME USER gjy TO gjy2

-- 用户授权  ALL PRIVILEGES 全部权限 库.表
GRANT ALL PRIVILEGES ON *.* TO gjy

-- 查询权限
SHOW GRANTS FOR gjy  -- 查询指定用户权限

-- 撤销权限  ALL PRIVILEGES 全部权限 库.表
REVOKE ALL PRIVILEGES ON *.* TO gjy

-- 删除用户  DROP USER 用户名
DROP USER gjy

2.8、MySQL备份

为什么要备份：

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

MySQL数据库备份方式：

直接拷贝物理文件
在Sql yog这种可视化工具中手动导出
- 在想要导出的库或者表中，右键，选择备份或导出
使用命令行导出 mysqldump 命令行使用

#导出一张表
# mysqldump -h主机 -u用户名 -p密码 数据库名 表名 >物理磁盘/文件名
mysqldump -hlocalhost -uroot -p123456 test student >D:/a.sql

#导出多张表
# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 ...... >物理磁盘/文件名

#导出数据库
# mysqldump -h主机 -u用户名 -p密码 数据库名 >物理磁盘/文件名
mysqldump -hlocalhost -uroot -p123456 test student >D:/a.sql

#导入数据库和表
#登录的情况下，切换到指定的数据库
#source 物理磁盘/文件名
source D:/a.sql;

2.9、规范数据库设计

为什么需要设计数据库？

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

不合格的数据库设计：

数据冗余，浪费空间
数据插入和删除都会麻烦，异常【屏蔽使用物理外键】
程序性能差

良好的数据库设计：

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

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

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

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

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

3.0、三大范式

为什么需要数据规范化？

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

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF)

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

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

规范数据库的设计

规范性和性能的问题

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

考虑商业化的需求和目标（成本，用户体验）数据库性能更加重要
在规范性能的问题的时候，需要适当的考虑一下规范性！
故意给某些表增加一些冗余的字段（从多表查询变为单表查询）
故意增加一些计算列（从大数据量变为小数据量的查询）

3.1、JDBC

数据库驱动

驱动：声卡、显卡、数据库

程序会通过数据库驱动和数据库打交道！

在这里插入图片描述

JDBC

sun公司为了简化开发人员对数据库的统一操作，提供了一个规范（java操作数据库的规范）的JDBC

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

对于开发人员来说，我们只需要掌握JDBC的接口即可

java.sql

javax.sql

还需要导入一个数据库驱动包 mysql-connector-java-8.0.24.jar

驱动下载地址：https://dev.mysql.com/downloads/connector/j/

第一个JDBC程序

创建测试数据库

CREATE DATABASE `jdbcStudy` CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `jdbcStudy`;

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

INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)
VALUES(1,'zhangsan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

1.使用idea创建一个普通项目

2.导入数据库驱动

3.编写测试代码

package com.gjy.lesson01;

import java.sql.*;

// 第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) {
        // 1.加载驱动
        //mysql5.5以后可以不写
        //Class.forName("com.mysql.cj.jdbc.Driver");
        // 2.用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?                                  useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
        //3.连接成功，数据库对象
        Connection connection;

        {
            try {
                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("password=" + resultSet.getObject("password"));
                    System.out.println("email=" + resultSet.getObject("email"));
                    System.out.println("birthday=" + resultSet.getObject("birthday"));
                    System.out.println("=====================================");
                }
                //6.关闭连接
                resultSet.close();
                statement.close();
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }


    }


}

步骤总结：

1.加载驱动

2.连接数据库DriverManager

3.获取执行sql的Statement对象

4.获得返回的结果集

5.释放连接

DriverManager

Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
Connection connection = DriverManager.getConnection(url, username, password);
//connection代表数据库
//数据库设置自动提交
//事务提交
//事务回滚
connection.commit();
connection.rollback();
connection.setAutoCommit();

URL

 String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql默认端口号3306
//jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

// oracle默认端口 1521  (了解即可)
// jdbc:oracle:thin:@localhost:1521:sid

Statement 执行sql的对象  prepareStatement执行sql的对象

// 执行之前  编写sql
String sql = "SELECT * FROM users";
statement.executeQuery();//查询操作，返回ResultSet
statement.execute();//执行任何sql
statement.executeUpdate();//更新、插入、删除、都是用这个  返回一个受影响的行数

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

获得指定的数据类型

resultSet.getObject();//如果不知道类型就使用getObject()
resultSet.getInt();
resultSet.getFloat();
resultSet.getString();
resultSet.getDate();

遍历

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

释放资源

//6.关闭连接
resultSet.close();
statement.close();
connection.close();//耗资源，用完关掉！

Statement对象

代码实现

1.提取工具类

package com.gjy.lesson02.utils;

import java.awt.dnd.DragGestureEvent;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private  static String driver=null;
    private  static String url=null;
    private  static String username=null;
    private  static String password=null;
    static {
        try {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");
            //驱动自需要加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    // 获取连接
    public static Connection getConnection() throws SQLException {
        Connection connection= DriverManager.getConnection(url,username,password);
         return  connection;
    }

    //释放连接
    public  static void release(Connection conn, Statement st, ResultSet rs){
      if(rs!=null){
          try {
              rs.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
      if(st!=null){
          try {
              st.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
      if(conn!=null){
          try {
              conn.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
    }
}

2.编写增删改的方法

增

package com.gjy.lesson02;

import com.gjy.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();
            String sql="INSERT INTO users(id,name,password,email,birthday)" +
                    "VALUES(4,'gjy','123131','136371@qq.com','2000-10-22')";
            int i=statement.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

删

package com.gjy.lesson02;

import com.gjy.lesson02.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql="DELETE FROM users WHERE id=4";
            int i=statement.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

改

package com.gjy.lesson02;

import com.gjy.lesson02.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql="update users set name='杰杰' WHERE id=4";
            int i=statement.executeUpdate(sql);
            if(i>0){
                System.out.println("修改成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

3.查询

package com.gjy.lesson02;

import com.gjy.lesson02.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;
        try {
            connection= JdbcUtils.getConnection();
            statement=connection.createStatement();
            String sql="select * from users";
            resultSet=statement.executeQuery(sql);
            while (resultSet.next()){
                System.out.println("姓名："+resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

SQL注入的问题

sql存在漏洞，会被攻击导致数据泄漏本质：SQL会被拼接 or

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

PreparedStatement对象

PreparedStatement对象的优点：

可以防止SQL注入
效率更高

1.新增

package com.gjy.lesson03;

import com.gjy.lesson02.utils.JdbcUtils;

import java.sql.*;

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,然后不执行
           //收到给参数赋值
            preparedStatement.setInt(1,5);
            preparedStatement.setString(2,"李杰");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"1212112@qq.com");
            //注意点:  sql.Date  数据库
            //        util.Date  java     new java.util.Date().getTime()获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

2.删除

package com.gjy.lesson03;

import com.gjy.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
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,然后不执行
            preparedStatement.setInt(1,4);
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

3.更新

package com.gjy.lesson03;

import com.gjy.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,然后不执行
            preparedStatement.setString(1,"gjy");
            preparedStatement.setInt(2,5);
            int i=preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

4.查询

package com.gjy.lesson03;

import com.gjy.lesson02.utils.JdbcUtils;

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

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);//预编译sql,然后不执行
            preparedStatement.setInt(1,5);
            ResultSet rs=preparedStatement.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

PreparedStatement防止SQL注入的本质：

把传递进来的参数当作字符
假设其中存在转义字符会被直接转义

3.2、使用IDEA连接数据库

在这里插入图片描述

使用IDEA连接数据库出现Server returns invalid timezone. Go to ‘Advanced’ tab and set ‘serverTimezone’ property manually 错误

解决办法：url地址后加上 serverTimezone=UTC(不推荐使用)

不推荐原因：如果你用编译器连接数据库，定义了serverTimezone=UTC，那么在你编译器上执行的SQL语句，会先以UTC时区进行存储，发送到MySQL,然后MySQL以本地时区进行转换，就会导致，执行时间比从编译器上的执行时间早8个小时，导致，同一段SQL语句，在mysql直接执行，与编译器执行，结果不同，因为时间相差8个小时

UTC:简称世界统一时间，跟北京时间相比，比北京早8个小时

正确解决方法：设置serverTimezone=Asia/Shanghai

在这里插入图片描述

连接成功如下图

在这里插入图片描述

3.3、JDBC操作事务

代码测试

package com.gjy.lesson04;

import com.gjy.lesson02.utils.JdbcUtils;

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

public class TestTransaction {
    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);//预编译sql
            preparedStatement.executeUpdate();
            String sql2="update account set money=money+100 where name='B'";
            preparedStatement=connection.prepareStatement(sql2);//预编译sql
            preparedStatement.executeUpdate();
            // 提交事务
            connection.commit();
            System.out.println("成功！");

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

3.4、数据库连接池

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

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

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

– 开门 --业务员：等待–服务–

常用连接数：10

最小连接数：10

最大连接数：100 业务最高承载上限

排队等待

等待超时：100ms

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

开源数据源实现

DBCP

C3P0

Druid:阿里巴巴

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

Healerjy

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL

如果你用编译器连接数据库，定义了serverTimezone=UTC，那么在你编译器上执行的SQL语句，会先以UTC时区进行存储，发送到MySQL,然后MySQL以本地时区进行转换，就会导致，执行时间比从编译器上的执行时间早8个小时，导致，同一段SQL语句，在mysql直接执行，与编译器执行，结果不同，因为时间相差8个小时。MySQL官网：https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html。
复制链接

扫一扫