java———MySQL 学习_java mysql

1.初识MySQL

1.1数据库分类

​ 关系数据库：（SQL）

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

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

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

DBMS（数据库管理系统）

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

1.2MySQL简介

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

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

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

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

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

• 常用：5.7（稳定）8.0

1.3MySQL安装（解压包类型安装）

2.命令输出

sc delete mmysql：清空服务

1.4安装可视化软件SQLyog

1.新建一个数据库

2.创建学生表

1.5连接数据库

命令行连接

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

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

flush privilenges; -- 刷新权限

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

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

use schhool; --切换数据库

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

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

exit --退出连接

-- 单行注释
/*  （多行注释）
edfw
edf
*/

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

2.操作数据库

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

2.1操作数据库

[]: 代表可选

1.创建数据库

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

2.删除数据库

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

3.使用数据库

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

4.查看数据库

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

2.2，数据库的列类型

数值

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

字符串

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

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

• tinytext 微型文本 2^8-1

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

时间日期

• date YYYY-MM-DD 日期格式

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

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

• year 年份表示

null

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

2.3数据库的字段属性（重点）

Unsigned：

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

zerofill：

• 不足的位数使用0来填充，int（3），5 —>005

自增：

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

非空 NULL not null

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

默认：

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

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

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

2.4，创建数据库表（重点）

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

格式：

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

常用命令

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

2.5、数据库的类型

-- 关于数据库引擎
/*
INNODB 默认使用
MYISAM 早些年使用的
*/
1
2
3
4
5

常规使用操作：

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

在物理空间存在的位置

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

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

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

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

CHARSET=utf8（创建表时修改）
1

默认为Latin1，不支持中文

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

character-set-server=utf8
1

2.6、修改删除表

修改

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

--增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher1 ADD age INT（11）

--修改表的字段 （重命名，修改约束）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[] 
ALTER TABLE teacher1 MODIFY age VARCHAR（11） -- 修改约束，将age的字段类型修改为varchar
-- ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]
ALTER TABLE teacher1 CHANGE age age1 INT（1） -- 字段重命名，将age重命名为age1且类型更改为int

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

删除表

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

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

注意点：

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

3、MySQL的数据管理

3.1、外键（了解）

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

添加外键：

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

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

ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FORETGN KEY（`gradeid`） REFERENCES `grade` （`gradeid`）

---ALTER TABLE 表 ADD CONSTRAINT 约束名 FORETGN KEY（作为外键的列） REFERENCES 哪个表（哪个字段）
1
2
3
4

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

最佳实现

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

3.2、DML语言

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

DML语言：数据操作语言

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

3.3、添加

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

3.4、修改

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

where条件运算符

操作符会返回布尔值

注意：

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

3.5、删除

delete命令

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

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

TRUNCATE命令

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

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

delete的TRUNCATE区别

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

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

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

4、DQL查询数据（最重点）

4.1、DQL

（Data Query LANGUAGE：数据查询语言）

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

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

4.2、指定查询字段

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

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

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

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

语法：SELECT 字段，… FROM 表

去重 distinct

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

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

数据库的列

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

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

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

select 表达式 from 表

4.3、where条件子句

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

逻辑运算符

模糊查询：比较运算符

-- ===================== 模糊查询 =======================
-- 查询姓刘的同学
-- like结合 %（代表0到任意个字符）  _（一个字符）
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '刘%'

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

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

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

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

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

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

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

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

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

4.4、联表查询

join

join联表查询的语法：

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

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

那么，使用格式为：

SELECT 表别名1.属性1
1

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

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

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


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

和ON配合使用前提下：

例：二表联表查询

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

例：三表查询

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

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

4.5、分页和排序

排序 ORDER

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

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

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

分页 LIMIT

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

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

4.6、子查询

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

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

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

4.7、分组和过滤

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

5、MySQL函数

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

5.1、常用函数

等等，具体看官方文档

5.2、聚合函数

-- 统计表中数据
SELECT COUNT(`BornDate`) FROM student; -- Count(字段),会忽略所有的null值
SELECT COUNT(*) FROM student; -- 不会忽略null值，本质是计算行数
SELECT COUNT(1) FROM student; -- 不会忽略null值，本质是计算行数

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

5.3、数据库级别的MD5加密

6.事务

6.1、什么是事务

事务原则：ACID原则 原子性，一致性，隔离性，持久性

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

原子性：（Atomicity）

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

一致性（Consistency）

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

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

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

事务的隔离级别

脏读：

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

不可重复读：

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

虚读(幻读)

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

执行事务

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

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

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

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

INSERT XX
INSERT XX

-- 提交：持久化（成功！）
COMMIT
-- 回滚：回到原来的样子（失败！）
ROLLBACK

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

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

模拟场景

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

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

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

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

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

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

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

7、索引

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

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

7.1、索引的分类

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

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

基础语法

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

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

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

-- EXPLAIN 分析sql执行的状况

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

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

7.2、索引原则

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

索引的数据结构

详细文章：

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

B+Tree数据结构：

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

B-Tree数据结构

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

索引调优

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

InnoDB索引实现

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

MyISAM索引实现

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

8、权限管理和备份

8.1、用户管理

8.2、MySQL备份

为什么要备份：

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

MySQL数据库备份方式：

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

9、规范数据库设计

9.2、三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第二范式

每张表只描述一件事情

第三范式（3NF）

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

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

（规范数据库的设计）

规范性 和 性能的问题

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

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

10、JDBC

数据库驱动

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

JDBC

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

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

使用到的java包

java.sql

javax.sql

导入的数据库驱动包

10.1、第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;

USE jdbcStudy;

CREATE TABLE users(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);

INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zou','123456','zou@','2000-10-02'),(2,'fei','123456','fei@','1000-01-20'),(3,'ming','123456','ming@','2000-02-10');
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.新建java项目

3、导入数据库驱动

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

4、编写测试代码

import java.sql.*;

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

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

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

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

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

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

结果：

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

Process finished with exit code 0

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

步骤总结：

1、加载驱动

2、连接数据库 DriverManager

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

4、获得返回的结果集

5、释放连接

DriverManager

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

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

URL

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

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

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

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

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

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

获得指定的数据类型

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

遍历，指针

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

释放资源

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

10.2、statement对象

CRUD操作-create

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

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

CRUD操作 - delete

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

CRUD操作 - update

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

CRUD操作 - read

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

完整代码实现：

• Properties（Java.util.Properties），该类主要用于读取Java的配置文件

• JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
  1

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

第一步：编写java类型能够读取的配置文件 db.properties ，文件中存放 driver、url、username、password 的数据库连接需要的参数

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

第二步：写关于JDBC驱动的工具类（将需要用到的重复操作集中，减少耦合性）

package lesson01.utils;

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

public class JdbcUtils {

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

    static{

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

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

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

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

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

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

package lesson01;

import lesson01.utils.JdbcUtils;

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

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

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

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

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

3.2、删除数据

将添加数据

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

的数据库语句，改为删除的数据库语句即可：

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

3.3、更改数据

同理、将 sql 语句改为：

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

3.4、查询数据

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

import lesson01.utils.JdbcUtils;

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

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

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

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

10.3、SQL注入

1、什么是SQL注入

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

2、SQL注入的代码实现

import lesson01.utils.JdbcUtils;

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

public class SQL_injection {

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

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


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


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

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

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

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

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

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

10.4、PreparedStatement对象

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

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

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

10.4.1、插入数据

package lesson01;

import lesson01.utils.JdbcUtils;

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

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

10.4.2、删除数据

与上面代码的区别:

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

变成：

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

---

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

变成：

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

10.4.3、更新数据

与上面代码的区别:

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

变成：

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

---

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

变成：

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

10.4.4、查询数据

package lesson01;

import lesson01.utils.JdbcUtils;

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

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

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

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

            st.setInt(1,2);

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

10.8、事务

ACID原则

原子性：要么全部完成，要么都不完成

一致性：总数不变

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

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

隔离性的问题：

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

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

虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致

例：

import lesson01.utils.JdbcUtils;

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

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

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

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

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

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

10.9、数据库连接池

连接 – 释放 十分浪费资源

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

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

等待超时的时间。

本质上：编写连接池，实现一个接口 DataSource

开源数据源实现

DBCP

C3P0

Druid：阿里巴巴

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