MySQL

MySQL

JavaEE: 企业级Java开发 Web

前端（页面：展示数据）

后台（连接点：连接数据库JDBC，连接前端（控制视图跳转，和给前端传递数据））

数据库（存数据，txt, excel, word… …）

初识MySQL

数据库的重要性

  1. 大数据时代，得数据者得天下
  2. 存数据
  3. 数据库是所有软件体系最核心的存在

什么是数据库

数据库（DB，DataBase）

概念：数据仓库，软件，安装在操作系统（Windows，Linux，Mac）之上！SQL，可以存储大量的数据。500万！

作用：存储数据，管理数据

数据库分类

关系型数据库：（SQL）

  • MySQL，Oracle，Sql Server, DB2, SQLlite
  • 通过表和表之间，行和列之间的关系进行数据的存储， 学员信息表，考勤表，… …

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

  • Redis/MongDB
  • 非关系型数据库，对象存储，通过对象自身的属性来决定。

DBMS（数据库管理系统）

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

image-20200913194526770

MySQL简介

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

以前属于瑞典MySQL AB公司，在属于Oracle旗下产品。

MySQL是最好的RDBMS（Relational DataBase Management System）应用软件之一。

开源软件。

体积小、速度快、总体拥有成本低。

中小型网站，或者大型网站，可以集群。

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

MySQL安装

安装地址：https://dev.mysql.com/downloads/mysql/

安装建议：

  1. 尽量不要使用exe安装，会牵涉到注册表
  2. 尽可能使用压缩包安装

步骤：

  1. 解压zip

  2. 添加环境变量：在path中添加bin的目录

  3. 在mysql文件夹下新建my.ini文件

    1
    2
    3
    4
    5
    [mysqld]
    basedir=D:\Program Files (x86)\mysql-8.0.21\
    datadir=D:\Program Files (x86)\mysql-8.0.21\data\
    port=3306
    ;skip-grant-tables

  4. 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld-install(安装mysql)。

  5. 再输入mysqld –initialize-insecure –user=mysql 初始化数据文件。

  6. 然后再次启动mysql（使用net start mysql），然后用命令mysql -u root -p进入mysql管理页面

  7. 进入页面以后更改root密码

    1
    ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'pwd';

  8. 输入flush privileges; 刷新权限

  9. 重启mysql(net stop mysql, net start mysql)

  10. 启动 mysql -u root -p

安装SQLyog

  1. 下载客户端后输入注册码

  2. 打开，连接数据库

  3. 新建一个数据库school

  4. 新建一张表student

image-20200914145307361

每一个sqlyog操作本质就是对应一些命令

image-20200914145743635

然后保存

右键student打开表即可添加数据

连接数据库

1
2
3
4
5
6
7
8
9
10
mysql -uroot -p --连接数据库
flush privileges;  --刷新权限
show databases;   --查询所有的数据库
use school; --切换数据库
show tables;  --查看所有表
describe student;  --显示数据库中表的信息
create database westos;  --创建一个数据库
exit; --退出连接
-- --单行注释
/* 多行注释 */

数据库语言 CRUD增删改查

DML：操作

DDL：定义

DQL：查询

DCL：控制

操作数据库

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

mySQL的关键字不区分大小写

操作数据库

  1. 创建数据库 

    1
    CREATE DATABASE [IF NOT EXISTS] westos;

  2. 删除数据库

    1
    DROP DATABASE IF EXISTS westos

  3. 使用数据库

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

  4. 查看数据库

    1
    SHOW DATABASES;

数据库的列类型

数值

  • tinyint 十分小的数据 1字节
  • smallint 较小数据 2字节
  • int 4字节
  • mediumint 中等数据 3字节
  • bigint 较大数据 8字节
  • float 4字节
  • double 8字节（精度问题，计算机无法处理无限循环小数）
  • decimal 字符串形式浮点数 金融计算的时候，一般是使用decimal

字符串

  • char 字符串 0~255
  • varchar 可变字符串 0~65535 对应String类型，常用变量
  • tinytext 微型文本 $2^{8} - 1$
  • text 文本串 $2^{16} - 1$ 保存大文本

时间日期

java.util.Date

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

null

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

数据库的字段属性

Unsigned：

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

Zerofill：

  • 0填充
  • 不足的位数使用0来填充

自增：

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

非空：NULL not null

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

默认：

  • 设置默认值
  • sex，默认为男，如果不制定值，则为默认值

创建数据库表

1
2
3
4
5
6
7
8
9
10
11
CREATE TABLE `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学员id',
	`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加到最后面，一般一个表只有一个
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

格式

1
2
3
4
5
CREATE TABLE [IF NOT EXISTS] `table name`(
	`char name` col_type [attribute] [index] [comment],
    ...
	`char name` col_type [attribute] [index] [comment]
)[table_type][charset][comment]

常用命令

1
2
3
SHOW CREATE DATABASE name  -- 查看创建数据库的语句
... TABLE name
DESC TABLE name --显示表的结构

数据表类型

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

常规使用操作：

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

在物理空间存在的位置

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

本质还是文件存储

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

1
CHARSET=utf8

不设置的话，会使用mysql默认的字符集编码~

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

在my.ini中配置默认的编码

1
character-set-server=utf8

修改删除表

修改

1
2
3
4
5
6
7
8
9
-- 修改表名
ALTER TABLE student RENAME AS teacher
-- 增加表的字段
ALTER TABLE `teacher` ADD `age` INT(11)
-- 修改表的字段
ALTER TABLE `teacher` MODIFY age VARCHAR(10)   -- 修改约束
ALTER TABLE `teacher` CHANGE age age1 INT(1)   -- 字段重命名
-- 删除表的字段
ALTER TABLE `teacher` DROP age1

删除

1
2
-- 删除表
DROP TABLE [iF NOT EXISTS] `teacher`

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

注意点：

  • 所有字段名尽量用``包裹起来
  • 注释 – 、/**/
  • sql 关键字大小写不敏感，建议小写
  • 所有符号用英文

MySQL数据管理

外键

image-20200914194621041

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
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
-- 给这个外键添加约束（执行引用）
CREATE TABLE `student` (
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学员id',
	`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 '家庭住址',
	`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
	-- primary key加到最 后面，一般一个表只有一个
	PRIMARY KEY(`id`),
	KEY `FK_gradeid` (`gradeid`),  -- 打算作为外键
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

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

方式2：添加表成功后，再添加外键约束

1
2
3
4
5
-- 创建表时没有外键关系
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

-- alter table 表 add constraint 约束名 foreign key （作为外键的列） references 被引用的表（被引用的字段）

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

最佳实现：

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

DML语言

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

DML语言：数据操作语言

  • insert
  • update
  • delete

添加

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 插入语句（添加）
-- insert into 表名 （[字段名1， 字段2 ...])（相当于构造参数） values('值1’), ('值2'), ('值3')...（相当于对象）
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于主键自增，可以省略主键（如果不写表的字段，就会一一匹配）
INSERT INTO `grade` VALUES('大三')  -- 错误，因为会默认插入到gradeid中

-- 一般写插入语句，需要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade`
(`gradename`)
VALUES('大一'),('大二'),('大三');

INSERT INTO `student`
(`name`,`pwd`)
VALUES
('张三','123456'),
('李四','345678');

注意事项：

  1. 字段和字段之间使用英文逗号隔开
  2. 字段可以省略，但是值必须一一对应（不建议，容易出错）
  3. 可以同时插入多条数据，VALUES后面的值需要使用逗号隔开

修改

update 修改对象（条件） set原来的值 -> 新值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 修改学员的名字
-- 带了条件
UPDATE `student`
SET `name`='shuwen'
WHERE `id`=1;

-- 不制定条件的情况下，会改动所有的表!
UPDATE `student`
SET `name`='Apocalypse';

-- 修改多个属性
UPDATE `student`
SET `name`='chen', `email`='1136@qq.com'
WHERE id=1;

-- 语法
-- update 表名 set column_name = value,[column_name=value,...] where 条件

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

操作符会返回boolean值

操作符 意义 范围 结果
= 等于
<> 或 != 不等于
>
<
>=
<=
BETWEEN … AND … 闭区间[x,y] BETWEEN id=2 AND id=5
AND 相当于&& 5>1 and 1>2 false
OR 相当于|| 5>1 or 1>2 true 
1
2
3
4
-- 通过多个条件定位数据, 无上限
UPDATE `student`
SET `name`='hao'
WHERE `name`='shuwen' AND id=2;

注意：

  • 使用column列尽量带上``

  • 筛选条件，如果没有指定则会修改所有的列

  • value是一个具体的值，也可以是一个变量

    1
    2
    3
    4
    -- 可以设置值为某个变量
    UPDATE `student`
    SET `birthday`=CURRENT_TIME
    WHERE `name`='hao';

  • 多个设置的属性之间使用英文逗号隔开

删除

delete命令

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

1
2
3
4
5
-- 删除指定数据
DELETE FROM `student` WHERE id=1;

-- 切忌！
DELETE FROM `student` -- 会删除整张表

TRUNCATE 命令

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

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

delete 和truncate的区别：

  • 相同点：都能删除数据，都不会删除表结构

  • 不同点：

    • truncate命令会重新设置自增列，计数器会归零

      1
      2
      3
      4
      5
      -- 不会影响自增
      DELETE FROM `test`;

      -- 会重置自增，归零
      TRUNCATE TABLE `test`;

    • truncate不会影响事务

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

  • INNODB 自增列会从1开始，(存在内存当中，断点即失)
  • MyISAM 继续从上一个自增量开始 (存在文件中，不会丢失)

DQL查询数据

DQL

（Data Query Language: 数据查询语言）

  • 所有的查询操作都使用它 Select
  • 简单的查询，复杂的查询都能做
  • 数据库中最核心的语言
  • 使用频率最高
1
2
3
4
5
6
7
8
9
10
SELECT [ALL | DISTINCT]
{* |table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
	[left | right | inner join table_name2] -- 联合查询
	[where ...]  --指定结果需要满足的条件
	[group by ...] -- 指定结果按照哪几个字段来分组
	[having ...] -- 过滤分组的记录必须满足的次要条件
	[order by ...] -- 指定查询记录按照一个或者多个条件排序
	[limit {[offset,]row_count | row_countOFFSET offset}];
	-- 指定查询的记录从哪条至哪条

顺序很重要：

select 去重 查询的字段（主义：表和字段可以取别名）

xx join 要连接的表 on 等值判断

where (具体的值， 子查询语句)

group by(通过哪个字段来分组)

having （过滤分组后的信息，条件和where一样，位置不同）

order by（通过哪个字段排序）[升序/降序]

limit startindex, pagesize

查询：跨表，跨数据库

指定查询字段

1
2
3
4
5
6
7
8
9
10
11
12
-- 查询所有字段   SELECT 字段 FROM 表
SELECT * FROM student
SELECT * FROM result

-- 查询指定字段
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查询出来的结果中重复的数据，重复的数据只显示一条

1
2
3
4
5
-- 查询一下有哪些同学有成绩
SELECT * FROM result  -- 查询全部的考试成绩
SELECT `studentno` FROM result
-- 需要去重
SELECT DISTINCT `studentno` FROM result

数据库的列（表达式）

1
2
3
4
5
6
7
8
9
-- 查询系统的版本
SELECT VERSION()  -- 函数

SELECT 100*3-1 AS 计算结果  -- 计算表达式

SELECT @@auto_increment_increment  -- 查询自增的步长（变量）

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

数据库中的表达式：文本的值，列，NULL，函数，计算表达式… …

select 表达式 from 表

where条件子句

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

搜索的条件由一个或者多个表达式组成

逻辑运算符

与或非

and &&

or ||

Not !

尽量使用英文字母

1
2
3
4
5
6
7
8
9
10
11
12
-- 查询考试成绩在90-120之间
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` >= 90 AND `studentresult` <= 120

-- 模糊查询
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 95 AND 120

-- 除了1000号学生之外的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno`=1000
-- where `studentno` != 1000

模糊查询：比较运算符

运算符 语法 描述
IS NULL a is null 如果操作符为null,则结果为真
IS NOT NULL a is not null 如果操作符不为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…其中的某一个值中，则结果为真 
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
-- 模糊查询
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 95 AND 120

-- 除了1000号学生之外的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno`=1000
-- where `studentno` != 1000

-- 查询姓刘的同学
-- like结合 %(0到任意个字符) _（一个字符）
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '刘%'

-- 查询姓刘的同学，名字后面只有一个字
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '刘_'

-- 查询名字当中有伟字的同学
SELECT `studentno`,`studentname` FROM student
WHERE `studentname` LIKE '%伟%'

-- in（具体是一个或者多个值） 查询1001,1002,1003号学员信息
SELECT `studentno`,`studentname` FROM student
WHERE `studentno` IN (1001,1002,1003);

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM student
WHERE `address` LIKE '%湖北%';

SELECT `studentno`,`studentname` FROM student
WHERE `address`='' OR address IS NULL;

-- 查询有出生日期的同学
SELECT `studentno`,`studentname` FROM student
WHERE  `borndate` IS NOT NULL;

联表查询

JOIN 对比

image-20200920191058689

image-20200920191421788

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
-- 联表查询
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*
思路：
1.需求：分析查询的字段来自哪些表，（连接查询）
2.确定使用哪种连接查询？7种
3.确定一个交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的studentno = 成绩表中的studentno
*/

-- join（连接的表） on（判断条件） 连接查询
-- where 等值查询


SELECT s.studentno, studentname, subjectno, studentresult
FROM student AS s
INNER JOIN result AS r
WHERE 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

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

-- 查询了参加的考试的同学信息：学号，学生姓名，科目名称，分数
/*
思路：
1.需求：分析查询的字段来自哪些表，student, result, subject（连接查询）
2.确定使用哪种连接查询？7种
3.确定一个交叉点（这两个表中哪个数据是相同的） 
判断的条件：学生表中的studentno = 成绩表中的studentno
*/

SELECT s.studentno, studentname, subjectname, studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

-- 我要查询哪些数据 select ...
-- 从哪几个表中查 from 表 xxx join 表
-- on 交叉条件
-- 假设存在一种多张表查询，先查询两张表，然后再慢慢增加

-- from A left join B
-- from B right join A
操作 描述
inner join 如果表中至少有一个匹配，就返回行
left join 即使右表中没有匹配，也会从左表中返回对应的值
right join 和left join相反

自连接

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

示例表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `category`(
	`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT	'主题id',
	`pid` INT(10) NOT NULL COMMENT '父id',
	`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY(`categoryid`)
)ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

父类

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

子类

pid categoryid categoryName
3 4 数据库
2 8 办公信息
3 6 web开发
5 7 美术设计

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

父类 子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 ps技术 
1
2
3
4
-- 查询父类信息:把一张表分成两张一模一样的表
SELECT a.categoryName AS '父栏目', b.categoryName AS '子栏目'
FROM category AS a, category AS b
WHERE a.`categoryid` = b.`pid`

分页和排序

排序

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 分页和排序 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 = sub.subjectno
WHERE subjectname = '高等数学-1'
ORDER BY studentresult ASC

分页

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- 100万数据无法直接显示在前端
-- 分页的原因
-- 缓解数据压力，但也可以使用瀑布流（如百度图片）
-- 每页只显示5条数据
-- 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 5n-5, 5 (n-1)*pageSize, pageSize

SELECT * FROM `subject`
LIMIT 6,5

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

子查询

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

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

where(select * from )

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

-- 方式二：使用子查询(由里及外)
SELECT studentno, subjectno, studentresult
FROM result
WHERE subjectno = (SELECT subjectno FROM `subject` WHERE subjectname LIKE '高等数学-1')
ORDER BY studentresult DESC

-- 查询分数不小于80的学生的学号和姓名，高等数学-2
-- 查询高等数学-2 的编号
SELECT studentname, s.studentno, studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno
WHERE r.studentresult >= 80 AND subjectno = (
	SELECT subjectno FROM `subject`
	WHERE subjectname = '高等数学-2'
)

-- 查询所有高等数学-1 的学生学号

嵌套查询

在子查询语句里面再嵌套一层子查询语句

1
2
3
4
5
6
7
8
9
10
11
-- 再改造
SELECT studentno, studentname
FROM student s
-- 如果查询的结果不唯一，则使用in
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
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT SubjectName, AVG(StudentResult) AS 平均分, MAX(StudentResult) AS 最高分, MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno`   -- 通过什么字段来分组
HAVING 平均分 > 80

MySQL函数

官网地址：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

常用函数

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
-- 数学运算
SELECT ABS(-8)  -- 绝对值
SELECT CEILING(1.1) -- 向上取整
SELECT FLOOR(1.3) -- 向下取整
SELECT RAND() -- 返回一个[0,1]之间的随机数
SELECT SIGN(-5) -- 判断符号 负数返回-1，正数返回1,0返回0

-- 字符串
SELECT CHAR_LENGTH('好') AS len  -- 字符串长度
SELECT CONCAT('我爱','Java') AS str  -- 拼接字符串
SELECT INSERT('I love code', 3, 0, 'truly ') -- 插入，替换
SELECT LOWER('ABC')
SELECT UPPER('abc')
SELECT INSTR('kuang','a') -- 查找子串在字符串中的位置
SELECT REPLACE('life','f','v')  -- 替换所有的指定字符串
SELECT SUBSTR('livvve', 3, 3) -- 返回指定被截取子串
SELECT REVERSE('hello') -- 反转

-- 查询姓周的同学,将刘改为邹
SELECT REPLACE(studentname, '刘', '邹') 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()

聚合函数（更常用）

函数名称 描述
COUNT()
SUM()
AVG()
MAX()
MIN()
… 
1
2
3
4
5
6
-- 聚合函数
-- 都能统计表中的数据
SELECT COUNT(studentname) FROM student;  -- Count(字段)，会忽略所有的null值
SELECT COUNT(*) FROM student;   -- Count(*)，不会忽略null值，本质计算行数
SELECT COUNT(1) FROM result;    -- Count(1)，不会忽略null值，本质计算行数，但只会检查一列

数据库级别的MD5加密

MD5：主要增强算法的复杂度和不可逆性。

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

MD5破解网站的原理：背后有一个字典，MD5加密后的值 对比 加密前的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 测试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, 'zhangsan', '123456'),
(2, 'lisi', '134567'),
(3, 'wangwu', '134567');

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd);

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

-- 校验:将用户传递进来的密码进行md5加密，比对加密后的值
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd = MD5('123456')

事务

概念

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

事务的原则：ACID原则

原子性，一致性，隔离性，持久性 （脏读，幻读…）

详细介绍链接：https://blog.csdn.net/dengjili/article/details/82468576

原子性（Atomicity）

要么成功，要么失败

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

一致性（Consistency）

事务前后的数据完整性保持一致，比如最终一致性，A和B的总钱数不会变化，始终为1200

隔离性（Isolation）

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

隔离所导致的一些问题：

  • 脏读：

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

  • 不可重复读：

    在一个事务内读取表中的某一行数据，多次读取的结果不同。

  • 虚读（幻读）：

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

持久性（Durability）

– 事务提交

事务一旦提交则不可逆，被持久化到数据库中

执行事务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- MySQL默认开启事务自动提交
SET autocommit = 0  -- 关闭
SET autocommit = 1  -- 开启

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

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

-- 提交： 持久化(成功）
COMMIT
-- 回滚： 回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1  -- 开启自动提交

SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名  -- 回滚到保存点
RELEASE SAVEPOINT 保存点名    -- 撤销保存点

image-20201001133509760

转账实例：

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
-- ===============================
-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9, 2) NOT NULL,   -- 9位数，其中小数点两位
	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;  -- 提交事务
ROLLBACK;  -- 回滚，一旦提交数据就被持久化而不能回滚了

SET autocommit = 1;  -- 恢复默认值
-- ========================

在Java中的使用情况：

1
2
3
4
5
6
7
8
public Function{
    try(){
        something;
        commit();
    }catch(Exception){
        rollback();
    }
}

索引

详细介绍链接：http://blog.codinglabs.org/articles/theory-of-mysql-index.html

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

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

索引的分类

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

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

基础语法：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后增加索引

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

-- 增加一个索引(索引名，类名)
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`)

-- 分析SQL执行的状况
EXPLAIN SELECT * FROM student; -- 常规索引，非全文索引

EXPLAIN SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘');   -- 全文索引

测试索引

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
CREATE TABLE `app_user`(
	`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
	`email` VARCHAR(50) NOT NULL COMMENT '用户邮箱',
	`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
	`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别（0：男，1：女）',
	`password` VARCHAR(100) NOT NULL COMMENT '密码',
	`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
	`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
	`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
	PRIMARY KEY (`id`)
) ENGINE=INNODB CHARSET=utf8mb4  COMMENT='app用户表'

-- 插入100万数据
DELIMITER $$ -- 写函数之前必须要写，标志
CREATE FUNCTION `mock_data`()
RETURNS INT
DETERMINISTIC
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),
		'54323456@qq.com', 
		CONCAT('18', FLOOR(RAND() * ((999999999-100000000) + 100000000)) ),
		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';  -- 0.518sec

-- id _ 表名 _ 字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
SELECT * FROM app_user WHERE `name`='用户9999';  -- 0.002 sec

索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显~

索引原则

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

索引的数据结构

Hash 类型的索引

Btree：INNODB的默认数据结构

权限管理

用户管理

SQL yog 可视化管理

image-20201001160431570

SQL 命令操作

用户表：mysql.user

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

详细命令链接：https://blog.csdn.net/zy_1995/article/details/102708482

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 创建用户
CREATE USER kuangshen IDENTIFIED BY '123456'

-- 修改密码
-- set password for kuangshen@localhost = password('111111')  旧版本
ALTER USER 'kuangshen'@'localhost' IDENTIFIED BY '111111'
SET PASSWORD FOR 'kuangshen2'@'localhost' = '2222222'

-- 给用户重命名
RENAME USER 'kuangshen'@'localhost' TO 'kuangshen2'@'localhost'

-- 给用户授权 ALL PRIVILEGES *.* 会授予除GRANT以外的所有权限, 要授予GRANT需加上WITH GRANT OPTION
GRANT ALL PRIVILEGES ON *.* TO 'kuangshen'@'localhost'

-- 删除用户
DROP USER 'kuangshen'@'localhost'

-- 查询权限
SHOW GRANTS FOR 'kuangshen'@'localhost'  -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost

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

MySQL备份

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

MySQL数据库备份的方式：

  • 直接拷贝物理文件…../data

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

    • 在左栏右键选择导出转储，选择结构和数据，指定导出的位置
    • 将文件直接拉入yog中即可使用

  • 使用命令行 mysqldump 在命令行中使用

    1
    2
    # mysqldump -h主机 -u用户名 -p密码 数据库名 [表1 表2 表3 ...] > 物理磁盘位置
    mysqldump -hlocalhost -uroot -pxxxxxx school student >D:/a.sql

    导入方法：

    1
    2
    3
    # 登录后使用source命令
    mysql -uroot -pxxxxxx
    source .sql文件位置

规范数据设计

为什么需要设计？

当数据库比较复杂的时候，就需要进行设计。

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

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

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

三大范式

为什么需要数据规范化？

可能出现的问题：

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

详细链接：https://www.cnblogs.com/wsg25/p/9615100.html

第一范式

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

第二范式

前提：满足第一范式

每张表只描述一件事情

第三范式

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

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

规范性和性能的问题

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

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

JDBC

数据库驱动

image-20201002111118497

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

JDBC概念

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

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

对于开发人员，只需要操纵API即可。

image-20201002111425645

java.sql

javax.sql

还需要导入一个数据库驱动包

mysql-connector-java.jar

第一个JDBC程序

创建测试数据库

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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, 'zhansan', '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. 创建一个普通项目

  2. 导入数据库驱动

  3. 编写测试代码

    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
    import java.sql.*;

    public class jdbcFirstDemo {
        public static void main(String[] args) throws ClassNotFoundException, SQLException {
            //1. 加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");  //固定写法，加载驱动
            //2. 用户信息和url
            String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT";
            String username = "root";
            String password = "123456";
            //3. 连接成功，数据库对象 Connection 代表数据库
            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("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();
        }
    }

步骤总结：

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

DriveManager

1
2
3
4
5
6
7
8
9
10
Class.forName("com.mysql.cj.jdbc.Driver");  //固定写法，加载驱动

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

url

1
2
3
4
5
6
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT";
//mysql --3306
//jdbc:mysql:(协议)/localhost:3306/数据库名?参数1&参数2&参数3...

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

Statement –执行SQL 的对象 PrepareStatement

1
2
3
4
5
6
//编写sql
String sql = "select * from users";

statement.executeQuery("");  //查询操作，返回结果集
statement.execute("");       //执行任何sql
statement.executeUpdate(""); //更新，插入，删除，返回受影响的行数

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

获取指定的数据类型

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

遍历，指针

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

释放资源

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

statement对象

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

Statement对象的executeUpdate方法，用于向数据库发送增删改的SQL语句，executeUpdate执行完后，将会返回一个整数（变化的行数）。

Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回查询结果的ResultSet对象。

CRUD操作- create

使用executeUpdate（String sql）方法完成数据添加操作：

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

CRUD操作- delete

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

CRUD操作- update

1
2
3
4
...;
String sql = "update user set name = '' where name = '' ";
int num = st.executeUpdate(sql);
...;

CRUD操作- read

1
2
3
4
5
6
...;
String sql = "select * from user where id = 1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
    ...;
}

代码实现

  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
    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
    package 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{
                //获取文件信息
                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 (IOException e){
                e.printStackTrace();
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
        //获取连接
        public static Connection getConnection() throws SQLException {
            return DriverManager.getConnection(url, username, password);
        }
        //释放连接资源
        public static void release(Connection connection, Statement statement, ResultSet resultSet){
            if(resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(statement != null){
                try {
                    statement.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
            if(connection != null){
                try {
                    connection.close();
                } catch (SQLException throwables) {
                    throwables.printStackTrace();
                }
            }
        }
    }

    db.properties文件：

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

  2. 编写增删改查的方法

    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
    //增删改
    import 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 = SQL语句;
                int i = statement.executeUpdate(sql);
                if(i > 0){
                    System.out.println("success!");
                }
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            } finally {
                JdbcUtils.release(connection, statement, resultSet);
            }
        }
    }
    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
    //查询
    import 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 = "select * from users";
                resultSet = statement.executeQuery(sql);
                if(resultSet != null){
                    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("===========================");
                    }
                }
            } catch (SQLException sqlException) {
                sqlException.printStackTrace();
            } finally {
                JdbcUtils.release(connection, statement, resultSet);
            }
        }
    }

SQL注入问题 (sql injection)

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

1
2
3
4
5
sql = "select * from users where name ='" + username + "' and password = '" + password + "'";
//此时如果username和password为：
username = "' or '1=1'";
password = "' or '1=1'";
//则表达式恒成立，用户的所有数据将被盗取

PreparedStatement对象

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

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
import utils.JdbcUtils;

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

public class TestSelect1 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            String username = "shuwen";
            String password = "123456";
            connection = JdbcUtils.getConnection();
            String sql = "select * from users where name = ? and password = ?";

            //preparedStatement本质：把传递进来的参数当做字符，即用引号包裹，如果存在转义字符，如'，则直接忽略
            preparedStatement = connection.prepareStatement(sql);  //预编译

            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);

            resultSet = preparedStatement.executeQuery();  //执行

            if(resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

使用IDEA连接数据库

在右侧栏添加数据库

image-20201002173513965

image-20201002173726030

image-20201002173822608

连接成功后，可以选择数据库

image-20201002174038984

image-20201002174055364

如果连接失败，可以在设置-》驱动里面查看版本是否正确，或者类是否正确。

JDBC处理事务

回顾

要么都成功，要么都失败

ACID原则

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

一致性：总数不变

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

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

隔离性的问题：

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

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

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


代码实现：

  1. 开启事务connection.setAutoCommit(false);
  2. 一组业务执行完毕，提交事务
  3. 可以在catch语句中显式定义回滚语句，但默认会回滚
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
import 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);
            preparedStatement.execute();

            String sql2 = "update account set money = money + 100 where name = 'B'";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.execute();

            //业务完毕，提交事务
            connection.commit();
            System.out.println("success!");

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

数据库连接池

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

但是连接到释放会十分浪费系统资源

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

最小连接数：10

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

等待超时：100ms

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

开源数据源实现（拿来即用）

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包：

commons-dbcp,commons-pool

dbcpconfig.properties

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
# 连接设置 这里面的名字，是DBCP数据源定义好的
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT
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 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

JdbcUtils_DBCP.java 利用工厂模式创建数据库连接

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
package utils;

import org.apache.commons.dbcp.BasicDataSourceFactory;

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

public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;

    static{
        try{
            //获取文件信息
            InputStream inputStream = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(inputStream);

            //创建数据源 工厂模式 --》 创建
            dataSource = BasicDataSourceFactory.createDataSource(properties);

        }catch (IOException e){
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();  //从数据源中获取连接
    }
    //释放连接资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(connection != null){
            try {
                connection.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
import utils.JdbcUtils;
import utils.JdbcUtils_DBCP;

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

public class TestDBCP {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils_DBCP.getConnection();  //获取数据库连接

            statement = connection.createStatement();
            String sql = "select * from users";
            resultSet = statement.executeQuery(sql);
            if(resultSet != null){
                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("===========================");
                }
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        } finally {
            JdbcUtils_DBCP.release(connection, statement, resultSet);
        }
    }
}

C3P0

需要用到的jar包

c3p0, mchange-commons-java

结论

无论使用什么数据源，本质相同：DataSource接口不会变，方法就不会变

Druid

Java 数据库
#Java #SQL
MySQL
http://example.com/2023/01/10/MySQL/
作者
Chen Shuwen
发布于
2023年1月10日
许可协议