[大数据]狂神说mysql笔记

MySql

1. 什么是mysql

2. 有什么用

3. 怎么用

1.简介

1.1概念

• 关系型数据库（SQL）：通过表和表之间，行和列之间的关系进行存储

  • DBMS（数据库管理系统），用于管理数据库的一种软件

  • 相当于打开一个excel表格需要一个软件打开它，而DBMS就是实现这样一个功能

  • mysql就是数据库管理系统软件之一

  • 它是目前世界上最好的RDBMS（关系型数据库系统）应用软件之一

  • 特点：体积小，速度快，总体拥有成本低

• 非关系型数据库（NO SQL：not only sql）：通过对象存储，通过对象自身的属性来决定

• 严格上来说，非关系型数据库并不是一种数据库，它实际是一种数据结构化存储的集合，本质上来说就是一种集合（文档型，键值对型，图形数据库，列式数据库）

  • redis、mongoDB

官网 MySQL

1.2作用

DBMS说白了就是用于存储数据，以及管理数据

所使用的语言是SQL语言，重点学习如何操作数据库

1.3安装

1. 尽量不要安装exe，里面有大量的注册表，卸载麻烦

2. 安装压缩包 MySQL :: MySQL Downloads

启动mysql:net start mysql

具体安装出现的问题查看 cmd下无法启动MySQL的几种原因及解决方法_打瞌睡_的博客-CSDN博客_cmd打不开mysql

2.操作数据库

2.1操作数据库

创建数据库

CREATE DATABASE school;

删除数据库

DROP DATABASE school;

查看数据库

SHOW DATABASES;

使用数据库

USE school;

2.2数据库的列类型

数值

• tunyint 十分小的数据 1个字节

• smallint 较小的数据 2个字节

• mediumint 中等大小的数据 3个字节

• int 标准的数据 4个字节

• 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

2.3数据库的字段属性

unsigned

• 无符号的整数

• 表示声明的数不能为负数

zerofill

• 填充0的

• 若要求的长度没有达到，就使用0填充

自增

• auto_increment

• 自增属性在整一张表中只有一个

• 表示数量会自动加1

非空

• not null

• 定义的字符串或数值不能为空

2.4创建表

CREATE TABLE IF not EXISTS`student` (
?  `id` INT (10) NOT NULL auto_increment COMMENT '学号',
?  `name` VARCHAR (20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
?  `pwd` VARCHAR (20) DEFAULT NULL COMMENT '密码',
?  `address` VARCHAR (50) DEFAULT NULL COMMENT '家庭住址',
?  `email` VARCHAR (30) DEFAULT NULL COMMENT '邮箱',
?  `birthday` datetime DEFAULT null comment '生日',
?  PRIMARY KEY (`id`)
?);

格式

?-- 注意字段使用飘包裹，字符串使用单引号
?-- 所有的符号全部用英文
?-- mysql关键字大小写不敏感，建议小写
?create table [if not exists] `表名`(
? ?--`字段名` 类型 [其他属性] 注释写在后面,
? ?--`字段名` 类型 [其他属性] 注释写在后面,
?  ......
? ?--`字段名` 类型 [其他属性] 注释写在后面
?)[表类型 字符集  注释]

查询的命令符

-- 查询创建的数据库
?SHOW CREATE DATABASE school;
?-- 查询数据库中创建的表
?SHOW CREATE TABLE student;
?CREATE TABLE `student` (
?  `id` int NOT NULL AUTO_INCREMENT COMMENT '学号',
?  `name` varchar(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
?  `pwd` varchar(20) DEFAULT '123456' COMMENT '密码',
?  `address` varchar(50) DEFAULT NULL COMMENT '家庭住址',
?  `email` varchar(30) DEFAULT NULL COMMENT '邮箱',
?  `birthday` datetime DEFAULT NULL COMMENT '生日',
?  PRIMARY KEY (`id`)
?) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
?-- 查看表的结构
?DESC student;

2.5数据库表的类型

?-- 查看数据库所支持的引擎
?SHOW ENGINES;

常用的数据库引擎：

• MYISAM

  • 体积小，速度快

• INNODB

  • 安全性高，有事务的处理，适用于多表用户操作

在物理空间存在的位置

所有的数据库文件都存在于data目录下

本质上还是文件的存储

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

• innodb在数据库表中只有一个*.frm文件，以及上级目录下的ibdata文件

• myisam对应的文件

  • *.frm 表结构的定义文件

  • *.MYD 数据文件

  • *.MYI 索引文件

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

?charset=utf8

假设不设置的话，会是数据库中默认的编码，不支持中文

或者在my.ini文件中设置默认的编码，以后都不用设置（不推荐）

?

character-set-server=utf8MB4

2.6修改和删除表

修改

-- 修改表名
?alter table 旧表名 rename as 新表名
?-- 增加表的字段
?alter table 表名 add 字段名 字段的列属性
?-- 修改表的字段
?alter table 表名 modify 字段名 修该的属性 ? 修改约束
?-- 修改表的字段名
?alter table 表名 change 旧字段名 新字段名 ?  重命名

删除

?-- 删除表的字段
?alter table 表名 drop 字段名

所有的增加和删除尽量加上判断操作[if exists]

3.MYSQL的数据管理

3.1外键约束（了解）

方式一：在创建表里面添加一个外键，在执行引用

CREATE TABLE if not exists`grade`(
`gradeid` int(10) NOT NULL auto_increment COMMENT '年级id',
`gradename` VARCHAR(20) DEFAULT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
);
-- 学生表的gradeid要去引用年级表的gradeid
-- 定义一个外键
-- 给这个外键添加约束 执行引用 
CREATE TABLE if not exists `student` (
	`id` INT NOT NULL AUTO_INCREMENT COMMENT '学号',
	`gradeid` INT (10) NOT NULL,
	`pwd` VARCHAR (20) DEFAULT NULL COMMENT '密码',
	`address` VARCHAR (50) DEFAULT NULL COMMENT '家庭住址',
	`email` VARCHAR (30) DEFAULT NULL COMMENT '邮箱',
	`birthday` datetime DEFAULT NULL COMMENT '生日',
	`name` VARCHAR (30) DEFAULT NULL,
	PRIMARY KEY (`id`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT  `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci

这种方式，在删除表的时候，应删除去引用外表的表（主表），之后再删除被引用外键的表，比较复杂

方式二：先创好表，再添加约束，执行引用

ALTER table `student` ADD 
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

格式

alter table 表名 add
constraint 外键约束 foreign key (引用的键) reference 被引用的表名（被引用的键）

以上的操作都是物理操作，就是数据库上面去才做（不推荐） 避免困扰

最佳实践

• 数据库只用来存储数据

• 要使用外键，之后用程序实现

3.2DML（数据库管理语言）（全部记住）

数据库管理语言只用来存储数据和管理数据集

之后要使用外键，就使用java程序实现

数据库管理语言

• insert

• delet

• update

3.3添加

-- 插入语句
-- INSERT INTO 表名（字段名）value (值1)，(值2)，(值3)，...
-- 插入的值要没有设置默认的值,会报错
insert into `student`(`name`,`pwd`,`address`) values ('hanqing','qwqwqw','广东');
insert into `student`(`name`,`address`,`email`,`birthday`)VALUES
('王五','湖南','122323243@qq.com','2020-01-09'),
('李六','湖南','122323243@qq.com','2020-01-09'),
('admin','广东','1438617560@qq.com','1995-08-06');

注意：

• 插入的字段，要与后面的值一一对应

• 所有的符号要使用英文

• 可以插入多个值，后面要使用英文逗号隔开

3.3删除

-- 删除语句
-- DELETE FROM 表名 WHERE 条件
-- 如果不加条件，则会所有的数据都删除
DELETE FROM student WHERE id=1;

truncate 清空数据库，但不会删除表的结构和索引

delete 和 truncate区别

• drop

  • 清空数据库，删除表结构和索引

• 相同点

  • 都会清空数据库，不会删除表的结构和索引

• 不同点

  • truncate 不仅会清空数据库，而且自增列也会影响，自增归为默认状态

  • delete 不会影响自增列，有事务的处理，安全性高

关于delete删除数据的问题 自增列的影响

• innodb 断电自增列即为0

• myisam 即使断电的情况下也不会改变自增列

3.4修改

-- 修改语句
-- update 表名 set 字段名=字段值,... where 条件
update `student` set `name`='god' where id=1;
-- 如果不加条件的情况下，所有的字段值都会被更改

修改某一个字段或者多个字段的值，用逗号隔开

where 条件 返回的是一个boolean值

注意：

• 字段名尽量加上飘，避免与关键字冲突

• 条件相当于是筛选，如果没有指定，就会修改所有的值

• 字段值可以是字符串，也可以是变量

• 多个属性之间使用逗号隔开

• trim标签可以将多余的逗号去掉

update `student` set `birthday`=CURRENT_TIME WHERE id>2;

4.DQL查询数据（重点）

4.1DQL

data query language：数据查询语言

• 查询所有数据 select

• 无论是简单的还是复杂的数据都可以查询

• 是数据库最核心得到一个语句

SELECT语句的完整语法为：
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[join...on...连接表]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]

4.2简单查询

-- 查询语句
-- SELECT 字段 FROM 表名
-- 查询所有字段
SELECT * from student;
-- 查询指定字段
SELECT `studentno`,`studentname` from student;
-- 给字段起别名 使用as
SELECT `studentno` as 学号,`studentname` as 学生姓名 FROM student;
-- 给字段起别名 使用as 也可以给表起别名
SELECT `studentno` as 学号,`studentname` as 学生姓名 FROM student as s;
-- 使用函数 CONCAT() 给结果拼接字符串
SELECT CONCAT('姓名：',`studentname`) from student;

有时候查询到的数据有大部分是相同的，这时候就可以考虑去重

distinct

SELECT DISTINCT `studentno` from student;

数据库的表达式

-- 查看数据库的版本
SELECT VERSION()    函数
-- 通过select可以进行简单的计算
SELECT 100*3-1 as 计算结果;  计算表达式
-- 查询自增的步长
SELECT @@auto_increment_increment;  变量
-- 给所有学生的成绩加1分
SELECT `studentno`,`studentresult`+1 FROM result;

4.3where条件子句

搜索的一个或多个条件为真，返回的结果为真，在mysql中表示检索到了

逻辑运算符

-- 查询所有学生的成绩
SELECT `studentno`,`studentresult` from result;
-- 查询90 -100分的学生
SELECT `studentno`,`studentresult` from result
WHERE studentresult>=90 and studentresult<=100

SELECT `studentno`,`studentresult` from result
WHERE studentresult>=90 && studentresult<=100

SELECT `studentno`,`studentresult` from result
WHERE studentresult between 90 and 100

-- 查询学号除了1000的学生
SELECT `studentno`,`studentresult` from result
WHERE not studentno=1000;

SELECT `studentno`,`studentresult` from result
WHERE studentno !=1000;

-- 查询学号除了1000 而且成绩在90-100分的学生
SELECT `studentno`,`studentresult` from result
WHERE (not studentno=1000) and (studentresult BETWEEN 90 and 100)

-- 查询名字为赵强的学生
SELECT * from student
WHERE `studentname`='赵强'

-- 查询有多少个成绩在90~100分的学生
SELECT COUNT(*) from result
WHERE studentresult between 90 and 100

模糊查询

-- 非空判断
-- 判断地址为空的学生
SELECT `studentno`,`studentname` from student
WHERE address is NULL OR address=''

-- 判断地址不为空的学生
SELECT `studentno`,`studentname` from student
WHERE address is not NULL AND `address` != ''

-- 查询学号在1005~1015之间的学生
SELECT `studentno`,`studentname` from student
WHERE `studentno` BETWEEN 1005 AND 1015

-- 查询姓赵的学生 like '%' 表示查询任意字符  '_' 表示查询一个字符
SELECT `studentno`,`studentname` from student
WHERE `studentname` LIKE '赵%'

-- 查询姓名带有强的学生 like '%' 表示查询任意字符  '_' 表示查询一个字符
SELECT `studentno`,`studentname` from student
WHERE `studentname` LIKE '%强%'

-- 查询学号有1001,1006,1009的学生
SELECT `studentno`,`studentname` from student
WHERE `studentno` in(1001,1006,1009)

注意:在sql中，字符串没有值不一定是为null，也可以是空字符串

4.4 联表查询

-- 查询参加考试的同学，包含的信息包括学号，姓名，科目编号，分数
/*
分析思路
1.分析需求，查询的字段涉及到哪些表
2.确定要哪些连接查询
3.确定交叉点，涉及到的表有哪些相同的字段
判断条件：学生表的studentno = 成绩表的studentno
*/
join on 连接查询
join where 等价查询
-- inner join
SELECT s.studentno,studentname,subjectno,studentresult
FROM student as s
INNER JOIN result as r
WHERE 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
--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 `subjectno` is NULL
-- 查询没有参加考试的同学，包含的信息包括学号，姓名，科目，分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student as s
RIGHT  JOIN result as r
ON s.studentno=r.studentno
inner JOIN `subject` sub
on r.subjectno=sub.subjectno
-- 查询学生所属的年级（学号，姓名，年级名称）
SELECT studentno,studentname,gradename
from student s
INNER JOIN grade g
on s.gradeid=g.gradeid
-- 查询科目对应的年级(科目名称，年级名称)
SELECT subjectname,gradename
from `subject`
INNER JOIN grade
on `subject`.gradeid=grade.gradeid
-- 查询参加高等数学-1考试的同学，包含的信息包括学号，姓名，科目名，分数
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
LEFT JOIN `subject` sub
on r.subjectno=sub.subjectno
WHERE sub.subjectname='高等数学-1'

思路分析

1. 分析需求，查询的字段设计到哪些表

2. 确定要哪种连接

3. 判断交叉点，作为条件

联表查询有7中，但只需要知道一下三个就行

• inner join 两表连接的相同数据

• right join 以右表为基准查询

• left join 以右表为基准查询

语法:

查询字段 from 表1 left join 表2 on 交叉条件

查询字段 from 表1 right join 表2 on 交叉条件

自连接：将一个表拆分成两张表，之后进行连接

父类

子类

查询父类对应的子类关系

SELECT a.`categoryname`as 父类, b.`categoryname` as 子类
FROM category as a,category as b
WHERE a.categoryid=b.pid

4.5分页和排序

• order by 排序

  • 升序 ASC

  • 降序 DESC

-- 查询参加考试的同学，包含的信息包括学号，姓名，科目名，分数（以分数升序展示）
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
ORDER BY studentresult ASC
-- 查询参加考试的同学，包含的信息包括学号，姓名，科目名，分数（以分数降序展示）
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
ORDER BY studentresult DESC

• limit(起始下标，页面大小) 分页

  • 起始下标=(第几页面数-1) *页面大小 pageIndex

  • 页面大小 pageSize

  • 总页数=总数据条数 / 页面大小 pageCounts

分页的操作必须放在最后

-- 查询参加考试的同学，包含的信息包括学号，姓名，科目名，分数（以分数降序展示）
-- LIMIT 起始下标,页面大小
-- 查询前面六位的同学
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
inner JOIN `subject` sub
on r.subjectno=sub.subjectno
ORDER BY studentresult DESC
LIMIT 0,6
-- 查询考试成绩前十名的同学（学号，姓名，成绩）
SELECT s.studentno,studentname,studentresult
from student s
INNER JOIN result r
on s.studentno=r.studentno
order by studentresult DESC
LIMIT 0,10
-- 查询考试成绩大于80分，并且排在前六名的同学（学号，姓名，成绩）
SELECT s.studentno,studentname,studentresult
from student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE studentresult>80
ORDER BY studentresult DESC
limit 0,6

4.6子查询

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

本质：where里面嵌套一个子查询语句

-- 查询参加高等数学考试的学生（学号，姓名。科目编号，成绩）,降序排列
-- 方式一：连接查询
SELECT s.studentno,studentname,r.subjectno,studentresult
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN `subject`sub
on sub.subjectno=r.subjectno
WHERE subjectname='高等数学-1'
order by studentresult DESC

-- 方式二：子查询
SELECT s.studentno,studentname,r.subjectno,studentresult
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE subjectno=(
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
)
order by studentresult DESC

-- 查询分数不小于80的学生的学号，姓名
SELECT s.studentno,studentname
from student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE studentresult>=80
-- 查询参加高等数学-1分数不小于80的学生的学号，姓名
SELECT s.studentno,studentname
from student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE studentresult>=80
 and 
subjectno=(
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
)
-- 查询参加高等数学-1分数不小于80的学生的学号，姓名
-- 嵌套查询(由里及外)
SELECT studentno,studentname
from student
WHERE studentno in(
SELECT studentno FROM result WHERE studentresult>=80
 and 
subjectno=(
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
))
-- 查询参加高等数学-1，前5名同学的信息（学号，姓名，成绩）
-- 联表查询
SELECT s.studentno,studentname,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 DESC
LIMIT 0,5
-- 查询参加高等数学-1，前5名同学的信息（学号，姓名，成绩）
-- 子查询
SELECT s.studentno,studentname,studentresult
from student s
INNER JOIN result r
on s.studentno=r.studentno
WHERE subjectno= (
SELECT subjectno from `subject` WHERE subjectname='高等数学-1'
)
ORDER BY studentresult DESC
LIMIT 0,5

• 查询的字段要是涉及到两张表以上，只能使用联表查询

• where条件判断的值仅仅涉及到条件的话，才可以使用子查询的方法

• 子查询的效率会更高一点，因为不涉及到联表查询

4.7分组

-- 查询学员的各科的总分，平均分，最高分，最低分的信息（学号，姓名，科目名称）平均分大于80
SELECT subjectname,SUM(studentresult) 总分,MIN(studentresult) 最低分,AVG(studentresult) 平均分,MAX(studentresult) 最高分
from result r
INNER JOIN `subject` sub
on sub.subjectno=r.subjectno
GROUP BY r.subjectno
HAVING 平均分>80   -- 表示筛选条件，必须是在过滤分组之后再筛选

5.函数

5.1常用函数（不常用）

5.2聚合函数

SELECT SUM(studentresult) as 总和 from result
SELECT AVG(studentresult) as 平均值 from result
SELECT MAX(studentresult) as 最大值 from result
SELECT MIN(studentresult) as 最小值 from result
-- 求指定字段的总条数。不会查出null的值
SELECT count(studentresult) as 最小值 from result
-- 求表中所有数据的总条数。会查出null的值
SELECT count(*) as 最小值 from result
-- 求表中所有数据的总条数。会查出null的值
SELECT count(1) as 最小值 from result

5.3数据库级别的MD5加密（扩展）

md5加密操作是强算法操作和不可逆的

可以在插入sql语句的时候给密码进行加密，这样更安全

解密：md5在线解密破解,md5解密加密 但较为复杂的密码或者是加了特殊符号的或许解密不了

6.事务

要么都成功，要么都失败

四个特性：

• 原子性：不可再分割的工作单位，一个事务中要么都完成，要么都不完成

• 一致性：从一致的状态转换到另一个一致的状态，事务前后的数据保持一致

• 隔离性：屏蔽其他的事务，专注自己的事务，互不干扰

• 持久性：存储在数据库的数据是永久的，事务一旦提交就不可逆，事务未提交就返回到原样

隔离产生的问题

• 脏读：指一个事务读取到了另一个事务未提交的数据

• 不可重复性

• 虚读

set autocommit=1;-- 设置自动提交事务开启，mysql的事务默认是开启的 
set autocommit=0;-- 设置自动提交事务关闭
-- 手动关闭自动提交事务
set autocommit=0；
-- 开启事务
START transaction; 
-- 如果数据没有问题就提交事务，提交成功，数据一旦提交就持久化
commit;
-- 如果数据出错就不提交事务，回滚到原来的数据
rollback;
-- 再次设置事务开启状态
set autocommit=1;
-- 可以设置保存点
SAVEPOINT ;
-- 可以设置删除保存点
release SAVEPOINT;
-- 查看自动提交开启状态
show variables like 'autocommit';

模拟账户转账

-- 关闭自动提交事务
SET autocommit=0;
-- 开启事务
start transaction;
-- 插入sql语句
UPDATE account SET money=money-500 WHERE `name`='A';
UPDATE account SET money=money+500 WHERE `name`='B';
-- 数据没问题就提交事务,数据一旦提交，就持久化到数据库
COMMIT;
-- 数据有问题就回滚
ROLLBACK;
-- 开启自动提交变成默认状态
set autocommit=1;

navicat的自动提交事务关闭了，导致了修改数据之后事务一直未提交

7.索引

8.权限管理和备份

9.规范数据库设计

10.JDBC

• 什么是JDBC

JDBC即Java DataBase Connectivity，java数据库连接 从根本来讲，JDBC是一种规范，它提供的接口，是一套完整的、可移植的访问底层数据库的程序。

• 有什么用

JDBC 提供的API可以让JAVA通过API方式访问关系型数据库，执行SQL语句，获取数据

• 怎么用

声卡驱动就是用来实现计算机与声卡之间的通信

显卡驱动就是用来实现计算机与显卡之间的通信

...

同样的，jdbc驱动就是用来实现java程序与数据库管理系统之间的通信

执行的顺序和打开可视化数据库一样：

1. 注册或加载驱动------->（找到可视化数据库软件）

2. 获取连接池-------->（打开连接）

3. 获取用户信息（用户名和密码）和url（符合url协议获取到指定的库名）-------->（输入用户信息和url）

4. 连接数据库-------->（点击连接）

5. 获取执行sql语句对象----->（新建查询）

6. 执行sql语句------>（执行sql语句）

7. 关闭资源------>（关闭可视化数据库软件）

10.1数据库驱动

声卡，显卡都是通过驱动与操作系统打交道

而前面所说的，数据库是数据库管理系统，也应有一个驱动和java程序建立通信

我们的java程序会通过数据库驱动，和数据库打交道

10.2jdbc

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

需要导入数据库驱动包

10.3第一个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,'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');

编写测试代码

?

package com.kuangstudy.lesson01;
??
?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 ?  useSSL = true值防止对数据库的随意修改，应设置为false
? ? ? ? ?String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&&useSSL=false";
? ? ? ? ?String username = "root";
? ? ? ? ?String password = "123456";
? ? ? ? ?//3.获取连接对象 connection表示已经连接到数据库
? ? ? ? ?Connection connection = DriverManager.getConnection(url, username, password);
? ? ? ? ?//4.获取执行sql对象
? ? ? ? ?Statement statement = connection.createStatement();
??
? ? ? ? ?//5.执行sql语句 ，返回一个结果集
? ? ? ? ?String sql = "SELECT * from users";
? ? ? ? ?ResultSet resultSet = statement.executeQuery(sql);
? ? ? ? ?while (resultSet.next()){
? ? ? ? ? ? ?System.out.println(resultSet.getInt("id"));
? ? ? ? ? ? ?System.out.println(resultSet.getString("name"));
? ? ? ? ? ? ?System.out.println(resultSet.getString("password"));
? ? ? ? ? ? ?System.out.println(resultSet.getString("email"));
? ? ? ? ? ? ?System.out.println(resultSet.getDate("birthday"));
? ? ? ? ? ? ?System.out.println("================");
? ? ? ?  }
? ? ? ? ?//6.关闭资源
? ? ? ? ?statement.close();
? ? ? ? ?connection.close();
? ? ? ? ?resultSet.close();
? ?  }
?}

操作jdbc的执行步骤：

1. 加载驱动

2. 获取连接对象，连接数据库

3. 获取执行sql对象

4. 返回结果

5. 关闭资源

DriverManager

我们操作Driver和获取connection对象都是交给DriverManager统一管理，DriverManager可以注册和删除加载的驱动

static {
? ? ? ? ?try {
? ? ? ? ? ? ?DriverManager.registerDriver(new Driver());
? ? ? ?  } catch (SQLException var1) {
? ? ? ? ? ? ?throw new RuntimeException("Can't register driver!");
? ? ? ?  }
? ?  }
?Connection connection = DriverManager.getConnection(url, username, password);

url

url路径是指定到哪个数据库，并设置SSL（安全套接层）为false

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

connection

此时，获取到的connection是含有指定数据库

Connection connection = DriverManager.getConnection(url, username, password);

statement

现在就可以获取编译sql的对象

?Statement statement = connection.createStatement();

resuleMap

?statement.executeQuery(sql); ?//用于查询语句，返回的是一个结果集
?statement.execute(sql);//用于任何一个sql语句
?statement.executeUpdate(sql); ?//用于添加、删除、修改，返回的是一个被影响的行数

10.4 statement

若java程序想要对数据库数据进行增删改查，只需要执行statement，这样可以向数据库发送sql语句。使用executeUpdate方法适合增删改操作，返回的结果是一个被影响的行数，也就是增删改的操作对数据库表几行数据发生了变化

statement.executeQuery()用于查询数据库表的数据，返回的是一个结果集，数据被封装到里面

CRUD操作 delete

?package com.kuangstudy.lesson01;
??
?import java.sql.Connection;
?import java.sql.DriverManager;
?import java.sql.SQLException;
?import java.sql.Statement;
??
?public class TestDelete {
? ? ?public static void main(String[] args) throws ClassNotFoundException, SQLException {
? ? ? ? ?//1.加载驱动
? ? ? ? ?Class.forName("com.mysql.cj.jdbc.Driver");
? ? ? ? ?//2.连接数据库
? ? ? ? ?String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
? ? ? ? ?String username = "root";
? ? ? ? ?String password = "123456";
? ? ? ? ?Connection connection = DriverManager.getConnection(url, username, password);
? ? ? ? ?//3.获取执行sql对象
? ? ? ? ?Statement statement = connection.createStatement();
? ? ? ? ?String sql = "DELETE from users WHERE id=2 ";
? ? ? ? ?int i = statement.executeUpdate(sql);
? ? ? ? ?if (i > 0){
? ? ? ? ? ? ?System.out.println("删除成功");
? ? ? ?  }
? ? ? ? ?//4.关闭资源
? ? ? ? ?statement.close();
? ? ? ? ?connection.close();
? ?  }
?}

CRUD操作 insert

?

Statement statement = connection.createStatement();
? ? ? ? ?String sql = "INSERT INTO users(`name`,`password`,`email`,`birthday`) VALUES('zhangsan','232322','zhangsan@163.com','1995-08-06'),('lisi','232322','lisi@163.com','2002-08-06'),('wangwu','232322','wangwu@163.com','1989-08-06'),('hanqing','232322','1438617560@qq.com','1995-08-06') ";
? ? ? ? ?int i = statement.executeUpdate(sql);
? ? ? ? ?if (i > 0){
? ? ? ? ? ? ?System.out.println("添加成功");
? ? ? ?  }

CRUD操作 update

Statement statement = connection.createStatement();
? ? ? ? ?String sql = "UPDATE users set `name`= '汉青' WHERE id=9 ";
? ? ? ? ?int i = statement.executeUpdate(sql);
? ? ? ? ?if (i > 0){
? ? ? ? ? ? ?System.out.println("修改成功");
? ? ? ?  }

提取工具类

建立一个util包，里面用来存放相同操作的代码，包括：

• 用户信息和url

• 连接数据库对象

• 关闭资源

另外，得新建一个properties文件，放在src文件夹下，用于存放用户信息以及加载驱动的实现类，包括

• driver=com.mysql.cj.jdbc.Driver

• username=root

• password=123456

• url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false

创建工具类

?package com.kuangstudy.lesson02.util;
??
?import java.io.IOException;
?import java.io.InputStream;
?import java.sql.*;
?import java.util.Properties;
??
?public class JdbcUtil {
? ? ?private static String driver=null;
? ? ?private static String username=null;
? ? ?private static String password=null;
? ? ?private static String url=null;
? ? ?static {
? ? ? ? ?try {
? ? ? ? ? ? ?InputStream rs = JdbcUtil.class.getClassLoader().getResourceAsStream("db.properties");
? ? ? ? ? ? ?Properties properties = new Properties();
? ? ? ? ? ? ?properties.load(rs);
? ? ? ? ? ? ?driver = properties.getProperty("driver");
? ? ? ? ? ? ?username = properties.getProperty("username");
? ? ? ? ? ? ?password = properties.getProperty("password");
? ? ? ? ? ? ?url = properties.getProperty("url");
? ? ? ? ? ? ?//驱动只用加载一次
? ? ? ? ? ? ?Class.forName(driver);
? ? ? ?  } catch (IOException | 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 set) {
? ? ? ? ?if (set != null){
? ? ? ? ? ? ?try {
? ? ? ? ? ? ? ? ?set.close();
? ? ? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ? ? ?e.printStackTrace();
? ? ? ? ? ?  }
? ? ? ?  }
? ? ? ? ?if (statement != null){
? ? ? ? ? ? ?try {
? ? ? ? ? ? ? ? ?statement.close();
? ? ? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ? ? ?e.printStackTrace();
? ? ? ? ? ?  }
? ? ? ?  }
? ? ? ? ?if (connection != null){
? ? ? ? ? ? ?try {
? ? ? ? ? ? ? ? ?connection.close();
? ? ? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ? ? ?e.printStackTrace();
? ? ? ? ? ?  }
? ? ? ?  }
? ?  }
?}

CRUD操作

?package com.kuangstudy.lesson02;
??
?import com.kuangstudy.lesson02.util.JdbcUtil;
??
?import java.sql.Connection;
?import java.sql.SQLException;
?import java.sql.Statement;
??
?public class TestDelete {
? ? ?public static void main(String[] args)  {
? ? ? ? ?Connection connection = null;
? ? ? ? ?Statement statement = null;
? ? ? ? ?try {
? ? ? ? ? ? ?connection = JdbcUtil.getConnection();
? ? ? ? ? ? ?statement = connection.createStatement();
? ? ? ? ? ? ?String sql = "DELETE from users WHERE id=9";
? ? ? ? ? ? ?int i = statement.executeUpdate(sql);
? ? ? ? ? ? ?if (i > 0){
? ? ? ? ? ? ? ? ?System.out.println("删除成功！");
? ? ? ? ? ?  }
? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ?e.printStackTrace();
? ? ? ?  }finally {
? ? ? ? ? ? ?JdbcUtil.release(connection,statement,null);
? ? ? ?  }
? ?  }
?}
?public class TestInsert {
? ? ?public static void main(String[] args)  {
? ? ? ? ?Connection connection = null;
? ? ? ? ?Statement statement = null;
? ? ? ? ?try {
? ? ? ? ? ? ?connection = JdbcUtil.getConnection();
? ? ? ? ? ? ?statement = connection.createStatement();
? ? ? ? ? ? ?String sql = "INSERT INTO users(`name`,`password`,`email`,`birthday`) VALUES('zhangsan','232322','zhangsan@163.com','1995-08-06'),('lisi','232322','lisi@163.com','2002-08-06'),('wangwu','232322','wangwu@163.com','1989-08-06'),('hanqing','232322','1438617560@qq.com','1995-08-06') ";
? ? ? ? ? ? ?int i = statement.executeUpdate(sql);
? ? ? ? ? ? ?if (i > 0){
? ? ? ? ? ? ? ? ?System.out.println("添加成功！");
? ? ? ? ? ?  }
? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ?e.printStackTrace();
? ? ? ?  }finally {
? ? ? ? ? ? ?JdbcUtil.release(connection,statement,null);
? ? ? ?  }
? ?  }
?}
?public class TestUpdate {
? ? ?public static void main(String[] args)  {
? ? ? ? ?Connection connection = null;
? ? ? ? ?Statement statement = null;
? ? ? ? ?try {
? ? ? ? ? ? ?connection = JdbcUtil.getConnection();
? ? ? ? ? ? ?statement = connection.createStatement();
? ? ? ? ? ? ?String sql = "UPDATE users set `name`= '汉青' WHERE id=13";
? ? ? ? ? ? ?int i = statement.executeUpdate(sql);
? ? ? ? ? ? ?if (i > 0){
? ? ? ? ? ? ? ? ?System.out.println("修改成功！");
? ? ? ? ? ?  }
? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ?e.printStackTrace();
? ? ? ?  }finally {
? ? ? ? ? ? ?JdbcUtil.release(connection,statement,null);
? ? ? ?  }
? ?  }
?}
?public class TestSelect {
? ? ?public static void main(String[] args)  {
? ? ? ? ?Connection connection = null;
? ? ? ? ?Statement statement = null;
? ? ? ? ?ResultSet resultSet = null;
? ? ? ? ?try {
? ? ? ? ? ? ?connection = JdbcUtil.getConnection();
? ? ? ? ? ? ?statement = connection.createStatement();
? ? ? ? ? ? ?String sql = "select * from `users` where id=13";
? ? ? ? ? ? ?resultSet = statement.executeQuery(sql);
? ? ? ? ? ? ?if (resultSet.next()){
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getInt("id"));
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getString("name"));
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getString("password"));
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getString("email"));
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getDate("birthday"));
? ? ? ? ? ? ? ? ?System.out.println("=====================");
? ? ? ? ? ?  }
??
? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ?e.printStackTrace();
? ? ? ?  }finally {
? ? ? ? ? ? ?JdbcUtil.release(connection,statement,resultSet);
? ? ? ?  }
? ?  }
?}

问题：而statement编译sql时会出现sql注入问题

模拟登陆操作

?

package com.kuangstudy.lesson02;
??
?import com.kuangstudy.lesson02.util.JdbcUtil;
??
?import java.sql.Connection;
?import java.sql.ResultSet;
?import java.sql.SQLException;
?import java.sql.Statement;
??
?public class SQL注入 {
? ? ?public static void main(String[] args) {
? ? ? ? ?//使用字符串拼接一个or，表示有一个为真则结果为真
? ? ? ? ?login("' or '1=1","232322");
? ?  }
? ? ?//用户登录
? ? ?public static void login(String username,String password){
? ? ? ? ?Connection connection = null;
? ? ? ? ?Statement statement = null;
? ? ? ? ?ResultSet resultSet = null;
? ? ? ? ?try {
? ? ? ? ? ? ?connection = JdbcUtil.getConnection();
? ? ? ? ? ? ?statement = connection.createStatement();
? ? ? ? ? ? ?String sql = "select * from `users` where `name`='"+ username+"' and `password`='"+password+"' ";
? ? ? ? ? ? ?resultSet = statement.executeQuery(sql);
? ? ? ? ? ? ?if (resultSet.next()){
? ? ? ? ? ? ? ? ?System.out.println("登录成功");
? ? ? ? ? ?  }
? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ?e.printStackTrace();
? ? ? ?  }finally {
? ? ? ? ? ? ?JdbcUtil.release(connection,statement,resultSet);
? ? ? ?  }
??
??
? ?  }
?}

解决办法，使用preparedstatement方法提前将预编译sql语句，并使用?站位符

10.5 PreparedStatement

PreparedStatement可以防止sql注入问题，效率更高

先进行预编译sql，将要设置的字段值使用占位符

本质：预编译会将传递进来的参数包裹成字符，而单引号会被转义字符转换为空内容，有效的防止sql注入的问题

1. 查询

 String sql = "select * from users where id=?";
? ? ? ? ? ? ?pst = connection.prepareStatement(sql);//预编译语句
? ? ? ? ? ? ?pst.setInt(1,14);
? ? ? ? ? ? ?ResultSet resultSet = pst.executeQuery();
? ? ? ? ? ? ?while (resultSet.next()){
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getString("name"));
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getString("password"));
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getString("email"));
? ? ? ? ? ? ? ? ?System.out.println(resultSet.getString("birthday"));
? ? ? ? ? ?  }

1. 插入

 String sql = "insert into users(`name`,`password`,`email`,`birthday`) value (?,?,?,?)";
? ? ? ? ? ? ?pst = connection.prepareStatement(sql);//预编译语句
? ? ? ? ? ? ?pst.setString(1,"王丽萍");//传递参数
? ? ? ? ? ? ?pst.setString(2,"wlp123");
? ? ? ? ? ? ?pst.setString(3,"wangliping@163.com");
? ? ? ? ? ? ?//获得时间戳 java.Date ? 再将时间戳转换为sql.Date下面的时间
? ? ? ? ? ? ?pst.setDate(4,new Date(new java.util.Date().getTime()));
? ? ? ? ? ? ?int i = pst.executeUpdate();

1. 删除

String sql = "delete from users where id=?";
? ? ? ? ? ? ?pst = connection.prepareStatement(sql);//预编译语句
? ? ? ? ? ? ?pst.setInt(1, 8);//传递参数
? ? ? ? ? ? ?int i = pst.executeUpdate();
? ? ? ? ? ? ?if (i > 0) {
? ? ? ? ? ? ? ? ?System.out.println("删除成功");

1. 修改

String sql = "update users set `name`=? ,`password` = ?where id=?";
? ? ? ? ? ? ?pst = connection.prepareStatement(sql);//预编译语句
? ? ? ? ? ? ?pst.setString(1,"李昂");
? ? ? ? ? ? ?pst.setString(2,"liang@163.com");
? ? ? ? ? ? ?pst.setInt(3,6);
? ? ? ? ? ? ?int i = pst.executeUpdate();

1. 模拟登录测试

String sql = "select * from `users` where `name`=? and `password`=?";
? ? ? ? ? ? ?statement = connection.prepareStatement(sql);
? ? ? ? ? ? ?statement.setString(1,username);
? ? ? ? ? ? ?statement.setString(2,password);
? ? ? ? ? ? ?resultSet = statement.executeQuery();

10.6使用idea连接数据库

1.打开idea,进入界面

2.选择+号，进入选择MYSQL

3.填写用户信息（和mysql可视化软件一样）?

4.连接成功！

如果出现连接不成功的，看看是否时区问题，若是时区问题，因为数据库默认的时区比当前的时区晚8小时，所以需要在my.ini文件修改设置时区:default-time_zone='+08:00'，之后重启mysql，之后再次连接

重启mysql，在命令行输入services.msc打开服务，选中MySQL重启服务

连接成功之后

编写sql的地方

查看数据库的表

??

10.7 Jdbc事务

事务：要么成功，要么失败

ACID原则

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

• 一致性：最终的结果总数据不发生改变

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

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

模拟转账事务

?

//模拟转账
?public class TransferDemo {
? ? ?public static void main(String[] args) throws SQLException {
? ? ? ? ?//connection代表数据库
? ? ? ? ?Connection connection = null;
? ? ? ? ?PreparedStatement st = null;
? ? ? ? ?try {
? ? ? ? ? ? ?connection = JdbcUtil.getConnection();
? ? ? ? ? ? ?//1.手动设置关闭自动提交事务，此时，事务自动开启
? ? ? ? ? ? ?connection.setAutoCommit(false);
? ? ? ? ? ? ?String sql1 = "update account set money = money - 100 where `name` = 'A';";
? ? ? ? ? ? ?st = connection.prepareStatement(sql1);
? ? ? ? ? ? ?st.executeUpdate();
? ? ? ? ? ? ?int num = 1/0;//强制制造问题
? ? ? ? ? ? ?String sql2 = "update account set money = money + 100 where `name` = 'B';";
? ? ? ? ? ? ?st = connection.prepareStatement(sql2);
? ? ? ? ? ? ?st.executeUpdate();
? ? ? ? ? ? ?//2.提交数据
? ? ? ? ? ? ?connection.commit();
? ? ? ? ? ? ?System.out.println("转账成功");
? ? ? ?  } catch (SQLException e) {
? ? ? ? ? ? ?//数据出现问题，事务会自动回滚到原来的样子，这里可以不用设回滚
? ? ? ? ? ? ?try {
? ? ? ? ? ? ? ? ?connection.rollback();
? ? ? ? ? ?  } catch (SQLException ex) {
? ? ? ? ? ? ? ? ?ex.printStackTrace();
? ? ? ? ? ?  }
? ? ? ?  }finally {
? ? ? ? ? ? ?JdbcUtil.release(connection,st,null);
??
? ? ? ?  }
??
? ?  }

问题：每次连接数据库都需要消耗大量的资源，效率低

解决：创建一个连接池。每次应用程序需要从数据库获取数据时，直接从连接池中申请一个连接使用，用完之后连接池回收连接，从而达到连接复用的效果，并减少资源消耗的目的

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

开源的连接池项目：

• DBCP

• D3P0

• Druid

使用了这些数据库连接池，我们就不需要编写连接数据库的代码

DBCP

 InputStream rs = DBCPUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");
? ? ? ? ? ? ?Properties properties = new Properties();
? ? ? ? ? ? ?properties.load(rs);
? ? ? ? ? ? ?//获取数据源 BasicDataSourceFactory  工厂：用来创建对象的
? ? ? ? ? ? ? ? ?dataSource = BasicDataSourceFactory.createDataSource(properties);

总结：无论用什么连接源，本质还是一样的，DataSource接口不会变，获取connection方法就不会变