MySQL详解
1、初识MySQL
作为一个程序员应该有的能力
1.1、为什么学习数据库
数据库是所有软件中最核心的存在
1.2、数据库的分类
关系型数据库
- MySQL Oracle Sql Server DB2
- 通过表与表之间的,行与行之间的联系进行数据的存储
非关系型数据库
- redis MongDB
- 非关系型数据库是进行对象的存储
DBMS(数据库管理系统)
- 数据库的管理软件,科学有效的管理我们的数据,维护和维护数据
- MySQL 数据管理系统
sc delete mysql 清空服务
在命令行输入此命令会将电脑中的MySQL的服务进行删除
常用的MySQL的可视化软件
- SQLyog
- Navicate
SQLyog的激活码
https://blog.csdn.net/akuan1992/article/details/91979721
1.3、连接数据库
命令行连接
mysql -u root -p --连接数据库
update mysql user set anthentication_string=password('123456')where user='root' and Host='localhost'; --修改数据库的密码
flush privileges; --刷新权限
----------------------------------------
show databases; --查看所有的数据库
--注:所有的语句都需要用分号结尾
use 数据库的名称; --表示使用数据库,对相应的数据库进行相关的操作
database change;
show tables; --查看数据库中所有的表
describe 表的名称; --查看表的结构
-------------------------------------------
create databases 数据库的名称; --表示创建一个数据库
create table 表的名称; --表示创建一个表
exit; --表示推出数据库
-------------------------------------------
--SQL的原本注释表示单行注释
数据库的语言分类 核心:增删改查
DDL:表示数据库的定义语言
DML:表示数据库的操作语言
DQL:表示数据库的查询语言
DCL:表示数据库的控制语言
2、操作数据库
2.1、操作数据库
创建数据库
create database [if not exit] 数据库的名称;
删除数据库
drop database [if exit] 数据库的名称;
使用数据库
use 数据库的名称;
注:当在数据库中或是数据库的表中使用的字符为特殊字符的时候可以用``进行涵盖
例:
select `user` from 表的名称;
查看所有的数据库
show databases;
2.2、数据库的数据类型
数值
- tinint:用来表示十分小的数据,其大小为1个字节
- smallint:用来表示较小的数据,其大小为2个字节
- int:用来表示标准的数据(最常用的数据类型),其大小为4个字节
- bigint:用来表示较大的数据,其大小为8个字节
- float:浮点型,其大小为4个字节
- double:浮点型,其大小为8个字节
- decimal:字符串形式的浮点数,在处理金融业务的时候采用。
字符串
- char:字符串,0-255 最小
- varchar:字符串,0-65535(最常用)
- tingtext:微型文本,2^8-1 (多用于论文博客当中)
- text:文本串,2^16-1(多用于文本,博客,论文,等大型文章之中,较为常用)
时间、日期
- date: YYYY-MM-DD 表示日期
- time HH:MM:SS 表示时间
- datetime YYYY-MM-DD HH:MM:SS 表示日期和时间(较为常用)
- year 表示年份
- timestamp 时间戳,从1970.1.1开始到现在的毫秒数,全世界统一。
null
注: null表示空值,没有值。其不要使用null进行运算,不然其结果为null
2.3、创建数据库的表
CREATE TABLE `student` (
`id` int(6) NOT NULL AUTO_INCREMENT COMMENT '学号',
`pwd` varchar(18) NOT NULL DEFAULT '123456' COMMENT '密码',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`email` varchar(40) DEFAULT NULL COMMENT '邮箱',
`address` varchar(100) NOT NULL COMMENT '家庭地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--comment 表示进行注释
--default 表示设置默认值
--charset 设置字符的格式
--AUTO_INCREMENT 设置自增的效果
注:在进行数据库的语句的编写时,所有的数据库语句后家逗号,最后一句不需要添加
常用命令
show create database 数据库的名称 --查看创建数据库的语句
show create table 表的名称 --查看表的语句
desc 表的名称; --查看表的结构
关于数据库的引擎
默认的使用INNODB
MYISAM早期的使用
区别:
MYSIAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为MAISAM的两倍 |
常规的使用操作
MYISAM:节约空间,速度快
INNODB:安全性高,支持事务处理,支持多表多用户操作
在物理空间存在的位置
所有的数据库的文件都存在了data目录下,一个文件夹对应一个数据库
本质还是文件存储
MySQL搜索引擎在物理文件上的区别
- INNODB在数据库表中只有一个*.frm文件,以及上级目录下的ibdata1文件
- MYISAM对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件(index)
设置数据库表的字符集编码
charset=utf8
注:不设置的话,会时MySQL默认的字符集编码(不支持中文!)
2.4、删除数据表
修改
alert table 旧表的名称 rename as 新表名 --对表进行重命名
alert table 表的名称 add 表的属性
alert table teacher add age int(11)--在表中添加属性
alert table 表的名称 modify 字段名 列属性[]
alert table teacher modify age varchar(11) --修改约束
alert table 表的名称 change 旧的字段名称 新的字段名称 列属性[]
alert table teacher change age age1 int(11) --字段的重命名
删除
alert table 表的名称 drop 字段名
alert table teacher drop age(11) --删除字段名
drop table if exists 表的名称
注:所有的创建和删除的操作尽量加上判断,避免报错
3、MySQL数据管理
3.1、外键(了解即可).
方法一
CREATE TABLE `grade` (
`gradeId` int(6) NOT NULL AUTO_INCREMENT COMMENT '年级号',
`gradeName` varchar(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeId`)
) ENGINE=InnoDB AUTO_INCREMENT=542453 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`stuId` int(6) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(12) NOT NULL COMMENT '学生姓名',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`gradeId` int(6) NOT NULL COMMENT '年级号',
`adress` varchar(50) NOT NULL DEFAULT 'null' COMMENT '家庭地址',
`email` varchar(50) NOT NULL DEFAULT 'null' COMMENT '邮箱',
PRIMARY KEY (`stuId`),
KEY `FK_gradeId` (`gradeId`),
CONSTRAINT `FK_gradeId` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`gradeId`)
) ENGINE=InnoDB AUTO_INCREMENT=3124 DEFAULT CHARSET=utf8;
方法二
CREATE TABLE `grade` (
`gradeId` int(6) NOT NULL AUTO_INCREMENT COMMENT '年级号',
`gradeName` varchar(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeId`)
) ENGINE=InnoDB AUTO_INCREMENT=542453 DEFAULT CHARSET=utf8;
CREATE TABLE `student` (
`stuId` int(6) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(12) NOT NULL COMMENT '学生姓名',
`sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
`gradeId` int(6) NOT NULL COMMENT '年级号',
`adress` varchar(50) NOT NULL DEFAULT 'null' COMMENT '家庭地址',
`email` varchar(50) NOT NULL DEFAULT 'null' COMMENT '邮箱',
PRIMARY KEY (`stuId`)
#KEY `FK_gradeId` (`gradeId`),
#CONSTRAINT `FK_gradeId` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`gradeId`)
) ENGINE=InnoDB AUTO_INCREMENT=3124 DEFAULT CHARSET=utf8
ALTER TABLE `student` ADD CONSTRAINT `FK_gradeId` FOREIGN KEY(`gradeId`) REFERENCES `grade` (`gradeId`);
注:通过Add添加的方式进行添加
3.2、DML语言(全部记住)
- insert
- delete
- update
3.3、添加语句
insert
insert into 表的名称 (字段名,字段名1,字段名2) values ('属性值','属性值1','属性值2') --插入一条能语句
insert into 表的名称 (字段名,字段名1,字段名2) values
('属性值','属性值1','属性值2'),
('属性值3','属性值4','属性值5') --插入多条数据
注:
- 字段与字段之间通过英文的逗号进行隔开
- 字段时可以省略的,但是后边的属性值必须一一对应
- 可以同时插入多条数据,values后边的值需要通过逗号隔开
3.4、修改
update 修改的对象 (判断条件) set原来的值=新值
UPDATE `student` SET `name`='张四' where `name`='李四'
--update 表名 set 字段名='value' where 字段名='value'
修改多个属性
UPDATE `student` SET `name`='张四',`adress`='山东省' where `stuId`=1
--在修改多个属性的时候,中间通过逗号进行分割
条件:where子句运算符
操作符 | 含义 | 范围 | 结果 |
---|---|---|---|
= | 等于 | Boolean类型 | |
<>或!= | 不等于 | Boolean类型 | |
> | Boolean类型 | ||
< | Boolean类型 | ||
<= | Boolean类型 | ||
>= | Boolean类型 | ||
between…and… | 在某个区间 | Boolean类型 | |
and | 与 && | Boolean类型 | |
or | || | Boolean类型 |
语法:update 表名 set conlum_name=value,[conlum_name=value…] where [条件]
注:
- colnum_name是数据库的列,尽量带上``
- 条件,筛选的条件,如果没有指定则会修改所有的列
- value是一个具体的值,也可以是一个变量(设置时间)
- 多个设置的属性之间通过英文逗号隔开
3.5、删除
delete命令
语法:delete from 表名 [where条件]
DELETE FROM `student` WHERE `stuId`=1
truncate命令
作用:完全清空一个数据库,表的结构和索引约束都不会变!
语法:truncate+表名;
delete与truncate的区别
相同点:
- 都能删除数据,不会删除表的结构
不同
- truncate 重新设置自增列,计数器归零
- truncate 不会影响事务
4、DQL数据查询(重点)
4.1、指定查询字段
select * from --查询表中的所有的信息
select 字段名1,字段名2 from 表明 --指定查询表中的信息
select 字段名 as 新的字段名,字段名2 as 新的字段名2 from 表名 --查询表中指定的数据,并给结果起一个新的名字 as可以给字段名起新名字,也可以给表起新名字。
函数 concat(a,b)
SELECT CONCAT('名字:',`name`) AS '新名字' FROM `student`
distinct 去重复
作用:去除select查询出来的结果中重复的数据,只显示一条
SELECT `name` FROM `student`
SELECT DISTINCT `name` FROM `student`
@@auto_increment_increment 查询数据库中自增的步长
SELECT @@auto_increment_increment --查询数据库中自增的步长
进行计算
SELECT 100*3-1 AS '计算结果'
查询数据库的版本
SELECT VERSION() --查询数据库的版本
4.2、where条件子句
逻辑运算符
作用:检索数据中符合条件的值
搜索的条件由一个或者多个表达式组成,结果 一般是布尔值
运算符 | 语法 | 描述 |
---|---|---|
and && | a and b | 与 |
or || | a or b | 或 |
not ! | not a | 非 |
注:尽量使用英文字母
模糊查询 比较运算符
运算符 | 语法 | 描述 |
---|---|---|
is null | a is null | 如果操作符为null结果为真 |
is not null | a is not null | 如果操作符为 not null,结果为真 |
between…and… | a between b and c | 若a在b与c之间,结果为真 |
like | a like b | 如果a匹配到b,结果为真 |
in | a in(a1,a2,a3…) | 假设a在a1或者a2…中结果为真 |
例:
SELECT name FROM student WHERE `name`LIKE '李%' --模糊查询,查询所有的姓李的人
SELECT `name` FROM student WHERE `name` LIKE '李_' -- _表示李后边一个字
SELECT `name` FROM student WHERE `name` LIKE '李__' -- _表示李后边一个字
in
查询指定的学生
SELECT `name`,`gradeId` FROM `student` WHERE `gradeId` IN(123,124,125)
4.3、联表查询
JOIN on对比
操作 | 描述 |
---|---|
inner join | 如果表中至少有一个匹配,就返回值 |
left join | 会在左表中返回值,即使右表中没有匹配 |
right join | 会在右表中返回值,即使左表中没有匹配 |
自连接及联表查询
自己与自己连接。核心:一张表切换成两张表
CREATE TABLE `class` (
`categoryId` int(6) NOT NULL auto_increment , --主题标签
`pId` INT(6) not NULL, --父标签
`categoryName` VARCHAR(12) not NULL,
PRIMARY KEY (`categoryId`)
)
INSERT INTO `class` (`categoryId`,`pId`,`categoryName`) VALUES
(3,1,'软件开发'),
(5,1,'美术设计'),
(4,3,'数据库'),
(8,2,'办公信息'),
(2,1,'信息技术'),
(6,3,'web开发'),
(7,5,'软件开发');
自连接便是自己与自己进行连接
例:
父类:
categoryId | categoryName |
---|---|
2 | 信息技术 |
3 | 软件开发 |
5 | 美术设计 |
子类:
pId | categoryId | categoryName |
---|---|---|
3 | 4 | 数据库 |
3 | 6 | web开发 |
5 | 7 | 软件开发 |
2 | 8 | 办公信息 |
查询父类对应的子类的类型
父类 | 子类 |
---|---|
信息技术 | 办公信息 |
软件开发 | web开发 |
软件开发 | 数据库 |
美术设计 | 美术设计 |
SELECT a.categoryName,b.categoryName
FROM class AS a
INNER JOIN class AS b
ON a.categoryId=b.pId
4.4、分页和排序
order by(排序)
SELECT * FROM `student`
ORDER BY `gradeId` DESC --降序
SELECT * FROM `student`
ORDER BY `gradeId` ASC --升序
limit(分页)
语法:limit起始的位置,页面的大小
注:limit永远放在数据库语句的最后
SELECT * from `student`
LIMIT 0,5
注:页面的计算方法
总页数=总的数/一页的个数
4.5、子查询
例
第一种方法,普通查询
--第一种方法
SELECT `name`,sex,a.gradeId,adress,email,gradeName FROM student AS a
INNER JOIN grade AS b ON a.gradeId=b.gradeId --普通查询
第二种·方法,子查询
SELECT `name`,sex,a.gradeId,adress,email gradeName FROM student AS a
INNER JOIN grade AS b WHERE a.gradeId=ANY(
SELECT gradeId FROM grade
)
注:当进行子查询的时候,显示子语句子语句返回的不是1行时,需要在子语句前添加any关键字进行改错
第三种方法
SELECT `name`,sex,a.gradeId,adress,email,gradeName FROM student AS a
INNER JOIN grade AS b
WHERE a.gradeId IN(
SELECT gradeId FROM grade
)
select语法关键字的使用顺序
5、MySQL常用的函数
5.1、常用函数
#获取绝对值
SELECT ABS(-8)
#向上取整
SELECT CEILING(9.4)
#向下取整
SELECT FLOOR(9.4)
#获取随机数
SELECT RAND()
--判断一个数的符号,0返回0 ,正数返回1,负数返回-1
#SELECT SIGN(-10)
#字符串函数
SELECT CHAR_LENGTH('枯藤老树昏鸦') 返回字符串的长度
#合并字符串
SELECT CONCAT('hello','world')
#插入字符串,替换字符串 表示从第几个开始替换几个字符
SELECT INSERT('枯藤老树昏鸦',1,6,'小桥流水人家')
#变化成小写字母
SELECT LOWER('HELLO WORLD')
#转换成大写
SELECT UPPER('hello world')
#返回第一次出现的字符串的索引
SELECT INSTR('lisi','i')
#替换指定的字符串
SELECT REPLACE('Hello world','Hello','world')
#返回指定字符串(源字符串,截取位置,截取长度
SELECT SUBSTR('枯藤老树昏鸦' ,'3','6')
#反转
SELECT REVERSE('落日与晚风')
#获取当前日期
SELECT CURRENT_DATE()
SELECT NOW()
SELECT CURDATE()
#本地时间
SELECT LOCALTIME()
#系统时间
SELECT SYSDATE()
5.2、聚合函数
函数名称 | 描述 |
---|---|
count() | 计数 |
sun() | 求和 |
AVG() | 平均值 |
MAX() | 最大值 |
MIN() | 最小值 |
… |
SELECT COUNT(`name`) FROM student --在进行统计的时后会自动的省略null值
SELECT COUNT(*) FROM student --与count(1)相比,在进行统计的时候都是按照行来统计,但是相比较count(1)来比较,速度较慢
SELECT COUNT(1) FROM student
5.3、MD5加密
注:MD5的加密使不可逆的
CREATE TABLE `user`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '编号',
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)
INSERT into `user` (`name`,`pwd`) VALUES
('张三','sdmu@123'),
('李四','sdmu@123'),
('王五','sdmu@123');
uPDATE `user` SET pwd=MD5(pwd) WHERE id=1 --对密码进行加密
#INSERT INTO `user` (`name`,`pwd`) VALUES('赵六',MD5(123456)) --进行MD5加密
5.4、select语法的小结
注:顺序很重要
- select +去重+要查询的字段 from 表名(注意,表和字段可以取别名)
- xxx join 要连接的表 on 等值判断
- where(具体的值,子查询的查询语句)
- group by(通过哪个字段来进行分组)
- having(对分组后的数据进行过滤,条件和where一样,但是放的位置不同)
- order by…(通过哪个字段进行排序,升序或者使降序)
- limit (starting,oagenumber)
6、事务
6.1、什么是事务
要么都成功,要么都失败
案例:银行转账
- SQL执行 A给B转钱
- SQL执行 B收到A的转账
注:两者必须同时进行,要么都成功,要么就失败
事务原则 ACID原则 原子性、一致性、隔离性、持久性 (脏读,幻读…)
-
原子性:类似与转账,要么都完成,要么都失败
-
一致性:针对事务操作前与操作后的数据的完整性的一致性
-
持久性:事务一旦提交便不可逆
-
隔离性:多个用户同时机型操作的时候互不影响
-
脏读:一个事务读取了另一个事务没有进行提交的数据
隔离所导致的问问题
脏读:一个事务读取了另一个事务没有进行提交的数据
不可重复读:在一个事务内读取表中的某一行数据,多次读取的结果不同(这不一定是个错误,可能是使用的场合不对)
虚读:是指在一个事务内读取到了别的事务插入放入数据导致前后的读取结果不一样
注:MySQL是默认开启事务的
set autocommit=0 --表示关闭事务
set autocommit=1 --表示开启事务
---手动处理事务
set autocommit=0 --关闭自动提交
--事务开启
start transaction --标记一个事务的开始,从这个之后的sql都在一个事务内 transaction交易的意思
insert xxx
insert xxx
--提交 持久化(成功)
commit
--回滚 回到原来的样子(失败!)
rollback
---事务结束
set autocommit=1 --开启自动提交
--了解
savepoint 保存点名 --设置一个事务的保存点
rollback to savepoint 保存点名 --回滚到保存点
release savepoint --删除保存点
模拟银行转钱业务
SET autocommit=0; -- 关闭事务
START TRANSACTION --开始事务管理
UPDATE bill SET money=money-1000 WHERE `name`='A'
UPDATE bill SET money=money+1000 WHERE `name`='B'
COMMIT; -- 数据成功,进行提交
SET ROLLBACK;--数据失败,回滚
SET autocommit=1; --开启事务管理
- 关闭事务
2.标记一个事务的开始,从这个之后的sql都在一个事务内 transaction交易的意思
3.进行数据的更新
4.提交或者是失败回滚
7、索引(重点+0)
MySQL官方对索引的定义为:索引是帮助MySQL改哦小获取数据的数据结构,提取句子的主干,就可以得到索引的本质:索引是数据结构
7.1索引的分类
在一个表中主键索引只能有一个,但是唯一索引可以有多个
-
主键索引 (primary key)
- 唯一标识,不可重复,只能有一个列作为主键
-
唯一索引(unique key)
- 避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引
-
常规索引(key /index)
- 默认的,index或者是key关键字进行设置
-
全文索引(FullText)
- 在特定的数据库的引擎下才有,MYISAM,快速定位所选的内容。
索引的使用
- 在创建表的时候给字段增加索引
- 创建完毕后增加索引
SHOW INDEX FROM student --显示student表中的所有的索引信息
alert table school.student add fulltext index `studentName`(`studentName`); --为school数据库中的student表增加一个全文索引(索引名字) 列名
explain --分析sql的执行状况
7.2、索引的原则
- 索引不是越多越好
- 不要对经常变动的数据进行添加索引
- 小数据不需要添加索引
- 索引一般添加到常用来查询的字段上
索引的数据结构
https://blog.csdn.net/qq_36381855/article/details/80011876
8、权限的管理和备份
8.1、用户管理常用语句(记住)
--创建用户 create user 用户名 indentified by '密码'
create user lisi indentified by '123456'
--修改密码(修改当前用户的密码)
set password=password('123456') --表示将当前用户的密码修改为123456
--修改密码(修改指定用户的密码)
set password for lisi=password('123456') --表示修改lisi这个用户的密码为123456
--修改用户的名字
rename user lisi to lisi2 --将原来的用户重命名为lisi2
--用户授权 all privileges 全部的权限,库.表
grant all privileges on *.* to lisi --表示给lisi这个用户赋予全部的权限,但是其权限仍然小于root用户的权限
--查看用户的权限
show grant for lisi --查看指定用户的权限
--查看管理员的权限
show grant for root@localhost
--撤销权限,撤销那些权限 在那个库进行撤销,给谁撤销
revoke all privileges on *.* from lisi
--删除用户
drop user lisi --表示删除lisi这个用户
8.2、数据库备份
作用:保证数据库中的数据不丢失,对数据库中的数据进行转移
数据库备份的方式
- 拷贝物理文件
- 导出sql语句
- 使用命令行 mysqldump
--mysqldump -h本地 -u登录的用户 -p密码 数据库的名称 数据库中的表名 > 物理磁盘中的位置/文件名
mysqldump -hlocalhost -uroot -plyj18366635303 bank > E:/a.sql
9、规范数据库的设计
9.1、设计数据库的流程
数据库的设计,在软件开发中
- 分析需求:分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
设计数据库的步骤
- 收集信息,分析需求
- 标识实体类(把需求落到每个字段上)
常见的系统
- 博客
- bbs
- crm
- bootstrap
- element
- ant design
9.2、三大范式
第一范式
原子性:保证每一列的数据都不可以再分
第二范式
前提:必须满足第一范式
每张表只描述一件事
第三范式
前提必须满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关
规范性和性能问题
关联查询的表不超过三张
- 考虑商业化的需求和目标,(成本,用户体验!)数据库的性能更加重要
- 在规范性能问题的时候,需要适当的考虑一下规范性的
- 会故意给某些表添加一些冗余的字段,(从多表查询变为单表查询)
- 故意增加一些计算列(从大数据量降低为小数据的查询,或者是添加索引)
10、数据库驱动和JDBC(重点)
10.1、JDBC
import java.sql.*;
import static java.lang.Class.forName;
public class JdbcTestDemo {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver"); //数据库的驱动
String url = "jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
String use="root";
String pwd="lyj18366635303";
Connection connection = DriverManager.getConnection(url, use, pwd);
Statement statement= connection.createStatement();
String sql="SELECT * FROM student";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println("stuId="+resultSet.getObject("stuId"));
System.out.println("name="+resultSet.getObject("name"));
System.out.println("sex="+resultSet.getObject("sex"));
System.out.println("gradeId="+resultSet.getObject("gradeId"));
System.out.println("adress="+resultSet.getObject("adress"));
System.out.println("email="+resultSet.getObject("email"));
}
statement.close();
connection.close();
resultSet.close();
}
}
DriverManger
Class.forName("com.mysql.cj.jdbc.Driver"); //数据库的驱动
Connection connection = DriverManager.getConnection(url, use, pwd);
//connection代表的是数据库
//在数据库中设置自动提交
//connection.rollback 表示数据库中的回滚
//connection.commit 表示数据库中的提交数据
//connection.setAutocommit 设置数据库的自动提交
statement 、PrepareStatement执行sql的对象
statement.executeQuery() //执行修改操作
statement.executeUpdate(); //执行修改操作
statement.execute(); //执行所有的sql语句
ResultSet 查询的结果集,其中封装了所有的结果集
resultSet.getObject(); //获取数据库中的数据
resultSet.getString(); //一般是获取数据库中的varchar的数据类型
resultSet.getInt(); //获取数据库中的Int类型
resultSet.getFloat();
resultSet.getDouble();
遍历 指针
resultSet.beforeFirst();// 移动到最前端
resultSet.afterLast(); //移动到最后面
resultSet.next(); //移动到下一个数据
resultSet.previous() ; //移动到前一行
resultSet.abolute(row); //移动到指定行
释放资源
statement.close();
connection.close();
resultSet.close();
10.2、statement对象
jdbc中的statement对象用于像数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象对数据库发送增删改查的命令即可。
statement对象的executeUpdate方法,用于想数据库发送增、删改的SQL语句,executeUpdate执行完后,将返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的resultSet对象
package lesson;
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 url=null;
private static String password=null;
private static String username=null;
InputStream in;
public static void jdbcUtil() throws IOException, ClassNotFoundException {
InputStream in= jdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"); //获取db.properties中的数据
Properties properties=new Properties();
properties.load(in);
driver= properties.getProperty("driver");
url= properties.getProperty("url");
password= properties.getProperty("password");
username= properties.getProperty("username");
Class.forName(driver);
// System.out.println("你好");
// System.out.println("你好"+in);
// System.out.println("driver="+url);
// System.out.println("username="+username);
// System.out.println("password"+password);
}
public Connection conn() throws SQLException {
Connection connection=DriverManager.getConnection(url,username,password);
return connection;
}
public void test(Connection conn1,Statement st,ResultSet rs) throws SQLException {
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(conn1!=null){
conn1.close();
}
}
// public static void main(String[] args) throws IOException, ClassNotFoundException {
// jdbcUtil();
// }
}
package lesson;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class jabcDemo {
public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
jdbcUtil jdbcUtil=new jdbcUtil();
jdbcUtil.jdbcUtil();
Connection conn = jdbcUtil.conn();
try (Statement statement = conn.createStatement()) {
String sql = "SELECT * FROM student";
ResultSet rs = statement.executeQuery(sql);
while (rs.next()) {
System.out.println("stuId=" + rs.getObject("stuId"));
System.out.println("name=" + rs.getObject("name"));
System.out.println("sex=" + rs.getObject("sex"));
System.out.println("gradeId=" + rs.getObject("gradeId"));
System.out.println("adress=" + rs.getObject("adress"));
System.out.println("email=" + rs.getObject("email"));
}
jdbcUtil.test(conn, statement, rs);
}
}
}
SQL注入
本质:在数据库中存在的漏斗,会出现数据信息泄露
10.3、PreparedStatement对象
优势:能够防止SQL注入
- 新增
package lesson;
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 url=null;
private static String password=null;
private static String username=null;
InputStream in;
public static void jdbcUtil() throws IOException, ClassNotFoundException {
InputStream in= jdbcUtil.class.getClassLoader().getResourceAsStream("db.properties"); //获取db.properties中的数据
Properties properties=new Properties();
properties.load(in);
driver= properties.getProperty("driver");
url= properties.getProperty("url");
password= properties.getProperty("password");
username= properties.getProperty("username");
Class.forName(driver);
// System.out.println("你好");
// System.out.println("你好"+in);
// System.out.println("driver="+url);
// System.out.println("username="+username);
// System.out.println("password"+password);
}
public Connection conn() throws SQLException {
Connection connection=DriverManager.getConnection(url,username,password);
return connection;
}
public void test(Connection conn1,Statement st,ResultSet rs) throws SQLException {
if(rs!=null){
rs.close();
}
if(st!=null){
st.close();
}
if(conn1!=null){
conn1.close();
}
}
// public static void main(String[] args) throws IOException, ClassNotFoundException {
// jdbcUtil();
// }
}
package lesson2;
import lesson.jdbcUtil;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import static lesson.jdbcUtil.jdbcUtil;
public class test {
// PreparedStatement pst;
public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
jdbcUtil jdbcUtil=new jdbcUtil();
jdbcUtil.jdbcUtil(); //调用构造方法
Connection connection = jdbcUtil.conn();
//使用preparedStatement通过占位符进行预编译
String sql="insert into class (categoryId,pId,categoryName) values(?,?,?)";
//进行手动编译,先给sql但是不执行
PreparedStatement pst = connection.prepareStatement(sql);
//手动给参数赋值
pst.setInt(1,10);
pst.setInt(2,7);
pst.setString(3,"软件工程");
int i = pst.executeUpdate();
if (i!=0){
System.out.println("数据插入成功");
}
jdbcUtil.test(connection,pst,null);
}
}
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bvdqSMqp-1631687423985)(…/AppData/Roaming/Typora/typora-user-images/image-20210914153933673.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Pez8V0si-1631687423985)(…/AppData/Roaming/Typora/typora-user-images/image-20210914153950163.png)]
注:在添加时间的时候,需要将java中的事件转化为sql中的时间格式
10.4、事务
要么都成功,要么都失败
原子性、一致性、隔离性、持久性
模拟银行转账业务
package lesson3;
import lesson.jdbcUtil;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class testDemo {
public static void main(String[] args) throws IOException, ClassNotFoundException, SQLException {
PreparedStatement pst=null;
jdbcUtil jdbcUtil=new jdbcUtil();
jdbcUtil.jdbcUtil();
Connection conn = jdbcUtil.conn();
String sql1 ="UPDATE bill set money=money-2000 where name ='B'";
pst = conn.prepareStatement(sql1);
int i = pst.executeUpdate();
String sql2="update bill set money=money+2000 where name ='A'";
pst = conn.prepareStatement(sql2);
int i1 = pst.executeUpdate();
if (i!=0&&i1!=0){
System.out.println("转账成功");
}else {
System.out.println("转账失败");
}
jdbcUtil.test(conn,pst,null);
}
}
10.5、数据库连接池
操作数据库时数据库的流程:数据库连接—执行完毕—释放资源—在连接数据库—在释放资源。比较耗费资源
池化技术:准备一些预先的资源,过来就连接预先准备好的
开源数据源的实现
- DBCP
- C3P0
- Druid: 阿里
使用了这些数据连接池之后,便可省略连接数据库的代码
DBCP
需要的jar包
commons-dbcp-1.4、commons-pool-1.6
dbcp.properties文件
driverClassName=com.mysql.cj.jdbc.Driver
#url
url=jdbc:mysql://localhost:3306/school?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
#用户名
username=root
#密码
password=lyj18366635303
#初试连接数
initialSize=30
#最大活跃数
maxTotal=30
#最大idle数
maxIdle=10
#最小idle数
minIdle=5
#最长等待时间(毫秒)
maxWaitMillis=1000
#程序中的连接不使用后是否被连接池回收(该版本要使用removeAbandonedOnMaintenance和removeAbandonedOnBorrow)
#removeAbandoned=true
removeAbandonedOnMaintenance=true
removeAbandonedOnBorrow=true
#连接在所指定的秒数内未使用才会被删除(秒)(为配合测试程序才配置为1秒)
removeAbandonedTimeout=1
dbcpUtil
package lesson4;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
public class dbcpUtil {
DataSource dataSource;
public dbcpUtil() throws IOException {
//进行文件的读取
InputStream resourceAsStream=dbcpUtil.class.getClassLoader().getResourceAsStream("dbcp.properties");
Properties properties=new Properties();
properties.load(resourceAsStream);.
try {
//BasicDataSourceFactory 工厂模式,进行对象的创建
dataSource= BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public void setClose(Connection connection,PreparedStatement preparedStatement, ResultSet resultSet) throws SQLException {
if (resultSet!=null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (preparedStatement!=null){
try {
preparedStatement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
connection.close();
}
}
}
test.class
package lesson4;
import java.io.IOException;
import java.sql.*;
public class dbcpTest {
public static void main(String[] args) throws IOException, SQLException {
Connection connection;
PreparedStatement pst;
ResultSet resultSet;
dbcpUtil dbcpUtil=new dbcpUtil();
connection = dbcpUtil.getConnection();
String sql="SELECT * FROM student";
pst=connection.prepareStatement(sql);
resultSet = pst.executeQuery(sql);
while (resultSet.next()){
System.out.print("stuId=" + resultSet.getObject("stuId"));
System.out.print("name=" + resultSet.getObject("name"));
System.out.print("sex=" + resultSet.getObject("sex"));
System.out.print("gradeId=" + resultSet.getObject("gradeId"));
System.out.print("adress=" + resultSet.getObject("adress"));
System.out.println("email=" + resultSet.getObject("email"));
}
dbcpUtil.setClose(connection,pst,resultSet);
}
}
C3P0
需要的jar包
c3p0-09.5.5、mchange-commons-java-0.2.19
结论
无论使用什么数据源本质是一样的,DataSource接口不会变,方法就不会变。
;
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection!=null){
connection.close();
}
}
}
test.class
```java
package lesson4;
import java.io.IOException;
import java.sql.*;
public class dbcpTest {
public static void main(String[] args) throws IOException, SQLException {
Connection connection;
PreparedStatement pst;
ResultSet resultSet;
dbcpUtil dbcpUtil=new dbcpUtil();
connection = dbcpUtil.getConnection();
String sql="SELECT * FROM student";
pst=connection.prepareStatement(sql);
resultSet = pst.executeQuery(sql);
while (resultSet.next()){
System.out.print("stuId=" + resultSet.getObject("stuId"));
System.out.print("name=" + resultSet.getObject("name"));
System.out.print("sex=" + resultSet.getObject("sex"));
System.out.print("gradeId=" + resultSet.getObject("gradeId"));
System.out.print("adress=" + resultSet.getObject("adress"));
System.out.println("email=" + resultSet.getObject("email"));
}
dbcpUtil.setClose(connection,pst,resultSet);
}
}
[外链图片转存中…(img-Nj7lapBU-1631687423987)]
C3P0
需要的jar包
c3p0-09.5.5、mchange-commons-java-0.2.19
结论
无论使用什么数据源本质是一样的,DataSource接口不会变,方法就不会变。