狂神说mysql笔记

最新推荐文章于 2024-07-25 11:32:53 发布

TTiamo_

最新推荐文章于 2024-07-25 11:32:53 发布

阅读量550

点赞数 1

文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/TTiamo_/article/details/120854508

版权

MySql

什么是mysql
有什么用
怎么用

1.简介

1.1概念

关系型数据库（SQL）：通过表和表之间，行和列之间的关系进行存储
- DBMS（数据库管理系统），用于管理数据库的一种软件
- 相当于打开一个excel表格需要一个软件打开它，而DBMS就是实现这样一个功能
- mysql就是数据库管理系统软件之一
- 它是目前世界上最好的RDBMS（关系型数据库系统）应用软件之一
- 特点：体积小，速度快，总体拥有成本低
非关系型数据库（NO SQL：not only sql）：通过对象存储，通过对象自身的属性来决定
严格上来说，非关系型数据库并不是一种数据库，它实际是一种数据结构化存储的集合，本质上来说就是一种集合（文档型，键值对型，图形数据库，列式数据库）
- redis、mongoDB

官网 MySQL

1.2作用

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

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

1.3安装

尽量不要安装exe，里面有大量的注册表，卸载麻烦
安装压缩包 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
事务	不支持	支持
数据行锁定	不支持（支持表锁定）	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约它的两倍

常用的数据库引擎：

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值

操作符	含义	范围	结果
=	等于	1=2	false
<>或！=	不等于	1<>2	true
>=	大于等于	1>=2	false
<=	小于等于	1<=2	true
<	小于	1<2	true
>	大于	1>3	false
between...and...	闭合区间包含	[2,3]
and	和	多个条件判断	一假则假
or	或	多个条件判断	一真则真

注意：

字段名尽量加上飘，避免与关键字冲突
条件相当于是筛选，如果没有指定，就会修改所有的值
字段值可以是字符串，也可以是变量
多个属性之间使用逗号隔开
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中表示检索到了

逻辑运算符

运算符	语法	描述
逻辑与	and &&	两个为真，结果为真
逻辑或	or \|\|	一个为真，结果为真
逻辑非	not !	取反操作
模糊查询	between...and...	两个为真，结果为真

-- 查询所有学生的成绩
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

模糊查询

运算符	语法	描述
是否为空	a is null	如果操作符为null，结果返回true
是否不为空	a is not null	如果操作符不为null，结果返回true
区间判断	between a and b	如果区间存在，结果返回true
模糊查询	a like b	如果操作符a像b，结果返回为true
具体查询	in(a,b)	如果包含了a,b，结果返回为true

-- 非空判断
-- 判断地址为空的学生
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'

思路分析

分析需求，查询的字段设计到哪些表
确定要哪种连接
判断交叉点，作为条件

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

inner join 两表连接的相同数据
right join 以右表为基准查询
left join 以右表为基准查询

连接	原理
inner join	如果表中至少有一个匹配，就返回行
right join	会从右表返回所有的值，即使左表没有匹配
left join	会从左表返回所有的值，即使右表没有匹配

语法:

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

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

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

父类

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

子类

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

查询父类对应的子类关系

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

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聚合函数

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

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程序与数据库管理系统之间的通信

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

注册或加载驱动------->（找到可视化数据库软件）
获取连接池-------->（打开连接）
获取用户信息（用户名和密码）和url（符合url协议获取到指定的库名）-------->（输入用户信息和url）
连接数据库-------->（点击连接）
获取执行sql语句对象----->（新建查询）
执行sql语句------>（执行sql语句）
关闭资源------>（关闭可视化数据库软件）

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的执行步骤：

加载驱动
获取连接对象，连接数据库
获取执行sql对象
返回结果
关闭资源

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注入的问题

查询

 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"));
             }

插入

 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();

删除

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("删除成功");

修改

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();

模拟登录测试

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方法就不会变

TTiamo_

关注

1
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
狂神说mysql笔记

MySql 什么是mysql 有什么用怎么用 1.简介1.1概念关系型数据库（SQL）：通过表和表之间，行和列之间的关系进行存储 DBMS（数据库管理系统），用于管理数据库的一种软件相当于打开一个excel表格需要一个软件打开它，而DBMS就是实现这样一个功能 mysql就是数据库管理系统软件之一它是目前世界上最好的RDBMS（关系型数据库系统）应用软件之一特点：体积小，速度快，总体拥
复制链接

扫一扫