花了3天学习了mysql数据库的基础知识

MySql基础知识

1. 初识MySql

百度百科：MySQL是一个**关系型数据库管理系统**，由瑞典[MySQL AB](https://baike.baidu.com/item/MySQL AB/2620844) 公司开发，属于 Oracle 旗下产品。MySQL 是最流行的关系型数据库管理系统之一，在 WEB 应用方面，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是s将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型和大型网站的开发都选择 MySQL 作为网站数据

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

1.1 安装MySql

安装建议：

• 压缩包安装

步骤：

• 解压压缩包到自己电脑环境目录下

• 配置环境变量 (系统变量->path->新建->粘贴环境目录下的bin目录路径)

• bin目录下新建一个配置文件ini

  文件内容:

[mysqld]
#目录换成自己的目录
basedir=D:\Environment\mysql-8.0\  #'\'要加上
datadir=D:\Environment\mysql-8.0\data\ #此目录会自动生成 照着写上
port=3306  #端口
skip-grant-tables  #跳过密码验证

• CMD管理员模式运行命令

cd bin目录  #切换到bin目录
mysqld -install  # 安装mysql服务
mysqld --initialize-insecure --user=mysql#初始化数据库文件  会生成data目录
net start mysql#启动mysql服务,进去修改密码

到应用管理->服务里面看有没有启动
mysql -u root(用户名) -p      #进入mysql  -p后面是可以输入密码的,初次进入,不用加密码,因为还没有
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';#修改密码
flush privileges;//刷新权限

最后一步:
注释掉myini配置文件中的最后一行跳过密码,因为我们有密码了
重启mysql服务
mysql>exit

#启动与关闭服务的命令
net stop mysql
net start mysql

sc delete mysql #清空服务，mysql需要重装

1.2 安装可视化软件

• workbench（个人比较喜欢用，后面用idea了）
• Sqlog

1.3 workbench 操作

• 新建数据库 school
• 新建表 student

show database
use school
show tables
describe student
create database school
exit
--
/* */
DDL --定义
DML --操作
DQL --查询
DCL --控制
CRUD --增删改查程序员
CV   --cv程序员
API  --api程序员
CRUD --业务程序员

2. 操作数据库

2.1 操作数据库（了解）

create database if not exists school --创建
drop database if exists school  --删除
use `school` -- 使用数据库  如果是个特殊字符就加`
show database --查看所有的数据库

2.2 操作数据库表

-- 建表
create table if not exists student(
`id` int not null auto_increment comment '学号',`name` varchar(30)not null default '匿名' comment '姓名',
`pwd` varchar(20) not null default '123456' comment '密码',`sex` varchar(2) not null default '女' comment '性别',
`birthday` datetime default null comment '出生日期',`address` varchar(100) default null comment '家庭住址',
`email` varchar(50) default null comment '邮箱',primary key (`id`))
engine=innodb default charset=utf8MB4
--查看表的结构
DESC student
--查看建表,建数据库的语句
show create table student
show create database school

格式总结：

create teble if not exists `表名`(
`字段名` 列类型 [属性] [索引] [注释]，
`字段名` 列类型 [属性] [索引] [注释]，
......
primary key(`主键`))ENGINE-INNDB DEFAULT CHARSET=utf8

2.3 操作数据库数据

2.3.1 数据库的列类型

数值

• tinyint 1
• smallint 2
• mediumint 3
• int 标准的整数 4
• bigint 较大的数据 8
• float 浮点数 4
• double 浮点数 8 （精度）
• decimal 字符串形式的浮点数，金融计算的时候使用

字符串

• char 字符串固定大小的 0-255
• varchar 可变字符串 0-65535 常用 对应java的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进行运算、

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

Unsigned

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

zerofill：0填充

AI：自增

PK：主键

NN：非空

2.4 数据表的类型

--关于数据库引擎
INNODB 默认使用
MYISAM 早些年使用的

区别

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键的约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

常规操作使用：

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

物理空间存在的位置

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

MySql引擎再物理文件上的区别:

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

设置数据库表的字符集&引擎

CHARSET=utf8 --不设置的话，会默认使用默认的字符集，不支持中文！！！Latin1
engine=innodb default charset=utf8MB4

在my.ini中默认设置的：character-set-server=utf8

2.5 修改&删除表

修改

• 修改表名

  alter table student(旧名) rename as student5(新名)

• 增加表的字段

  alter table student(表名) add age int(11)

• 修改表的字段

  • 重命名 alter table student(表名) chang age age1 int(2)
  • 修改约束 和字段类型alter table student(表名) modify age varchar(45)

• 删除表的字段

​ alter table student drop age

删除

• 删除表

​ Drop table if exists student

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

注意点：

• ``字段名，使用这个符号包裹
• 注释
• sql尽量写小写，习惯

3. 数据管理

3.1 外键

方式一：

key `fk_gradeid` (`gradeid`) --定义外键key
constraint `fk_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`) --给这个外键添加约束（执行引用） references 引用

删除有外键关系的表的时候，要先将引用别人的表删除，才能删除本表

方式二：创建表的时候没有外键关系，修改它有外键关系

alter table `student`

add constraint `fk_gradeid` foreign key (`gradeid`) references `grade` (`gradeid`) 

以上的物理外键，属于数据库级别的外键，不建议使用！避免数据库过多造成困扰，一切外键在应用层解决！！！

最佳方法：在应用层实现！！

3.2 DML操作

• insert（插入）
  • insert into student ([字段1，字段2，字段3]) values (值1，2，3)，（值4，5，6）->插入多个字段
  • 主键自增，可以省略
  • 如果不写表的字段，他会自动一一匹配，但是，一般写插入语句，我们一定要数据和字段一一对应！
  • 字段是可以省略的，但是后面的值，要一一对应，不能少
• update（修改）

--带条件修改
update `student` set name=`dd` where id = 1
--不指定条件，修改所有的值
update `student` set name=`dd` 

• 带条件

  > 
  <
  >=
  <=
  != / <>  不等于
  between and
  and
  or
  

  注意：

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

• delete (删除）

delete from 表名 [where 条件]
---不会清除主键自增

• truncate (清空)

truncate table 表名
--会清除主键自增

delete 删除的问题：

• INNODB
  • 存在内存当中，重启数据库，自增从1开始
• MYISAM
  • 存在文件当中，重启数据库后，自增还是从原来的节点开始

4. DQL数据查询（重点）

4.1 DQL指定查询字段

• selete语句和别名使用

--查询全部
select 字段,...from表
--查询指定字段
select 字段,[字段2],[字段3] from 表
--别名
select 字段 [as 别名]，[字段2 as 别名] from student [as 别名]
--函数，合并
select concat('姓名：',字段) as 新名字 from 表名

select的完整语法

select [all|distinct]
{*|table.*|table.字段1[as 别名1],[字段2 as [别名2]]}
from 表名[as 别名1]
-- 联合查询
[left|right|inner join 表2] 
-- 指定结果需满足的条件
[where ... and...] 
-- 指定结果按照哪几个字段来分组
[group by...] 
-- 过滤分组记录必须满足的次要条件
[having]
-- 指定查询记录按一个或者多个条件排序
[order by]
-- 指定查询记录从哪条至哪条
[limit {[offset,]row_count | row_countOFFSET offset}]

• 去重 distinct

  select distinct 字段 from 表名	
  

  • 用UNION ALL黏在一起 UNION默认去重，union 的结果是去重的，不去重要用union all

• 数据库列（表达式）

select version（） -- 查询系统版本 （函数） 
select 100*7-88 as 计算结果 -- 用来计算  （表达式）
select @@auto_ increment_increment -- 查询自增的步长 （变量）

--学员考试成绩+1 分 后查看
select studentNo,studentResult+1 as '提分后' from result

总结数据库中的表达式：

• 文本值，列，NULL，函数，计算表达式，系统变量…

if函数

select 
if(age>=25,'25岁及以上','25岁以下') as age_cut,count(id) as number
from user_profile
group by age_cut

多情况选择：

case

when …then…

when …then…

[else]

end

• 例子

select device_id,gender,case when age<20 then '20岁以下'
when age>=20 and age<=24 then '20-24岁'
when age>=25 then '25岁及以上'
when age is null then '其他'
end as age_cut
from user_profile

day()格式化date

select day(date) as day,count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08-%'
group by date;

select 表达式 from 表

4.2 where 条件子句

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

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

• 逻辑运算符
  • and &&
  • or ||
  • not !

尽量使用英文符号

select 字段 from 表
where 条件
--模糊查询（区间）
select 字段 from 表
where between 23 and 44

模糊查询：本质是比较运算符

• 比较运算符

  • a is null
  • a is not null
  • a between b and c
  • a like b
  • a in (a1,a2,a3)

• like

  • % 匹配0-任意的字符,且只在like里面用
  • _ 表示一个字符

• in

  • in**(2,3,4)**

4.3 联表查询

join对比

join（连接的表） on（条件）

思路：
1.分析需求，分析查询的字段是来自哪些表？
2.确定使用哪种连接？ 共有7种
3.确定交叉点（在这些表种哪些数据是相同的）->判断的条件 比如：学生表中的studentNo = 成绩表中的studentNo

操作	描述
innner join	所有符合的
left join	会从左表中返回所有的值，即使右表中没有
right join	会从右表中返回所有的值，即使右表中没有

--牛客例子
SELECT university,difficult_level,COUNT(t2.question_id)/COUNT(distinct(t2.device_id)) as avg_answer_cnt
from user_profile t1
inner join question_practice_detail t2
on t1.device_id = t2.device_id
inner join question_detail t3
on t3.question_id = t2.question_id
GROUP BY university,difficult_level;

--https://www.nowcoder.com/practice/5400df085a034f88b2e17941ab338ee8?tpId=199&tqId=1975675&ru=%2Fpractice%2Ff4714f7529404679b7f8909c96299ac4&qru=%2Fta%2Fsql-quick-study%2Fquestion-ranking&sourceUrl=%2Fexam%2Foj%3Fpage%3D1%26tab%3DSQL%25E7%25AF%2587%26topicId%3D199

• where 和 on 的区别
  • join on – 是一个具体的语法
  • where – 等值查询

三表查询

思路：

• 要查询哪些数据
• 要从哪几个表中查： from 表 xxx(inner left right)join 连接的表 on 交叉条件
• 假设是要求在多个表查询，慢慢来，先查询两张表，然后根据需要选择左中右连接来慢慢增加！

4.4 自连接（一张表拆成两张表）

自连接说白了就是把一张表分成两张 然后又采用联表查询的方式进行操作

这篇文章写得很详细，可以参考看看 MySQL查询之自连接查询

4.5 分页和排序

• 排序
  • 升序 ASC
  • 降序 DESC

ORDER BY ASC|DESC

• 分页（为什么分页? 缓解数据库压力，良好体验） 瀑布流（应用于图片，无限往下刷，刷不完 抖音也是！）
  • limit

mysql分页   
 需用到的参数:   
 pageSize 每页显示多少条数据   
 pageNumber 页数 从客户端传来   
 totalRecouds 表中的总记录数 select count (*) from 表名   
 totalPages 总页数   
 totalPages=totalRecouds%pageSize==0?totalRecouds/pageSize:totalRecouds/pageSize+1
 --- （解释：整数页的时候，总页数就是totalRecouds/pageSize，不是整数页的时候，总页数就是totalRecouds/pageSize+1） 
 pages 起始位置   
 pages= pageSize*(pageNumber-1)   
 SQL语句:   
 select * from 表名 limit pages, pageSize;   
 mysql 分页依赖于关键字 limit 它需两个参数:起始位置和pageSize   
 起始位置=页大小*(页数-1)   
 起始位置=pageSize*(pageNumber -1)   

4.6 子查询

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

-- 纯子查询（更快）

-- 联表查询（推荐）
select 字段
from 表1 别名1
inner join 表2 别名2
on 条件
where 子句
[group by]
[having]  分组之后再过滤
order by
limit 起始页，页面大小

5.MySql函数

5.1 常用函数

• 数学运算 格式：select 函数
  • ABS()
  • CEILING() 向上取整
  • FLOOR() 向下取整
  • RAND() 返回0-1之间的随机数
  • SIGN() 判断一个数的符号 正数返回1，负数返回-1
• 字符串函数
  • char_length(‘’) 字符串长度
  • concat(‘’,‘’) 拼接字符串
  • insert(‘’) 从某个位置开始替换某个长度
  • upper() 转大写字母
  • lower() 转小写字母
  • instr() 返回第一次出现的 子串的索引
  • replace() 替换出现的指定的字符串
  • substr() 返回指定的子字符串 （源，截取的位置，截取的长度）
  • reverse() 反转字符串
• 时间和日期函数（重点）
  • DATE()获取当前的日期
  • NOW() 获取当前的时间
  • localtime()获取本地时间
  • sysdate()获取系统时间
  • 时间格式：
    • year()
    • month()
    • day()
    • hour()
    • minute()
    • second()
• 系统
  • System_user() 获取系统用户
  • user()
  • version()

5.2 聚合函数

• count() 计数
  • count(1),count(*) 不会忽略null值，本质是计算行数
  • count(列名) 会忽略null值
• min() 最小值
• max() 最大值
• sum() 求和
• avg() 平均分

5.3 数据库级别的MD5加密

什么是MD5？

MD5由MD4、MD3、MD2改进而来，主要增强算法复杂度和不可逆性。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域

-- 插入的时候加密
insert into 表名 values(...,MD5('12345'))
-- 前端传来密码，java中校验
select * from 表名 where pwd=MD5(pwd) -- pwd是从前端获取的

6. 事务

6.1 什么是事务？

要么都成功，要么都失败！

• 事务原则ACID：原子性，一致性，隔离性，持久性（脏读，不可重复读，虚读（幻读））
  • 原子性：要么都成功，要么都失败
  • 一致性：事务前后的数据完整性要保持一致
  • 持久性：事务一旦提交则不可逆，被持久化到数据库中
  • 隔离性：事务的隔离是指多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离

隔离所导致的一些问题：

• 脏读：指的是一个事务读取了另外一个事务未提交的数据
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同（这个不一定是错误，只是某些场合不对）
• 虚读（幻读）：是指再一个事务内读取到了别的事务插入的数据，导致前后读取不一致

6.2 执行事务

-- mysql默认开启事务自动提交的
set autocommit = 0
set autocommit = 1
-- 手动处理事务
set autocommit = 0  -- 关闭自动提交
-- 事务开启
start transaction -- 标记一个事务的开始，从这个之后的sql都在一个事务内
 insert xx 
 insert xx
 -- 提交：提交就被持久化（成功）
 commit
 -- 回滚：回到原来的样子（失败）
 rollback
 
 -- 事务结束
 set autocommit = 1 -- 开启自动提交
 
 -- 了解
 savepoint 保存点名字  -- 设置一个事务的保存点
 rollback to savepoint 保存点名字 -- 回滚到保存点
 release savepoint 保存点名字 -- 撤销保存点
 

模拟场景

create database bank character set utf8 collate utf8_general_ci;
use bank;

-- 建表
 create table if not exists `account` (
`id` int not null auto_increment,
`name` varchar(30) not null,
`money` decimal(9,2) not null,
primary key(id)
)engine=InnoDB default charset=utf8mb4;

insert into account(`id`,`name`,`money`)
values(1,'甲',10000.00),(2,'乙',2000.00);
select * from account;
-- 模拟转账：事务
set autocommit = 0;-- 关闭自动提交
start transaction; -- 开启一个事务，一组事务
update account set money=money-500 where `name`=甲;
update account set money=money+500 where `name`=乙;

commit ; -- 提交事务，就被持久化了
rollback; -- 回滚

set autocommit = -1;-- 恢复默认

7.索引

MySQL支持诸多存储引擎，而各种存储引擎对索引的支持也各不相同，因此MySQL数据库支持多种索引类型，如BTree索引，哈希索引，全文索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1 索引的分类

• 主键索引 primary key
  • 唯一标识，只能有一个列作为主键
• 唯一索引 unique key
• 常规索引 key
  • 默认的，index key关键字
• 全文索引
  • 快速定位数据

基础语法:

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
--- 2.1 常规索引:create index 索引名（命名一般规则： id+表名+字段名） on 表（字段）

-- 显示所有的索引信息
show index from student
-- 增加一个全文索引 fulltext  索引名 （列名）
alter table school.sutdent add fulltext index `studentName` (`studentName`)
-- EXPLAIN分析sql的执行状况

EXPLAIN  SELECT *FROM student;

EXPLAIN SELECT * FROM student where MATCH(studentName) AGAINST('')

7.2 测试索引

插入100万条数据

增加了索引，速度加快了！索引在大数据的时候，作用大，小数据，用处不大！

7.3 索引原则

• 不是越多越好

• 不要对经常变动的数据加索引

• 小数据量的表不需要加所以哦

• 索引一般加在常用来查询的字段上

索引的数据结构！！！

链接好文：https://blog.codinglabs.org/articles/theory-of-mysql-index.html

Btree，B+tree

8. 权限管理和备份

8.1 用户管理

• 可视化管理 sqlyog中实现（具体不演示了）

• 用命令实现：

-- 创建用户
create user 用户 identified by '密码'
-- 修改当前用户密码
set password = password('密码);
set password for 用户 = password('密码);
-- 重命名
rename user 原来名字 to 新名字
--用户授权
gtant all privileges on *.* to 用户  -- 除了别给人授权，其他都能干
--查询指定用户的权限
show grant for 用户
-- 撤销权限  revoke哪些权限，在哪个库撤销，给谁撤销
revoke all privileges on *.* from 用户
--删除用户
drop user 用户

8.2 MySql 备份

为什么要备份？

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

备份方式：

• 直接拷贝data物理文件 data 目录
• sqlyog导出：选择结构和数据
• 使用命令行导出
  • mysqldump -h 主机 -u 用户 -a 密码 数据库 表1 表2 表3 > 物理磁盘位置/文件名（加.sql）
    • mysqldump -hlocalhost -uroot -a123456 school >D:/a.sql

导入：

登录的情况下，切换到指定数据库

• source d:/a.sql

未登录的情况下

• mysql -u用户名 -p密码 库名 < 备份文件 （本质还是登录了！）

把数据库给别人，把sql文件导出来给别人就行！！！！！！！！！！！！！！

9. 规范数据库设计

当数据库比较复杂的时候，就要设计！

• 收集信息，分析需求
• 标识实体
• 标识实体之间的关系

10.三大范式（了解，范式要背）

引用好文链接：数据库三大范式是什么？(3NF详解)

• 第一范式(1NF)：属性不可分割，即每个属性都是不可分割的原子项。(实体的属性即表中的列)

• 第二范式：

  • 前提：满足第一范式
  • 每张表只描述一件事情

• 第三范式：

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

11.JDBC

JDBC（Java DataBase Connectivity）是Java和数据库之间的一个桥梁，是一个规范而不是一个实现，能够执行SQL语句。它由一组用Java语言编写的类和接口组成。各种不同类型的数据库都有相应的实现。

步骤：

• 加载驱动
• 连接数据库的DriverManager
• 获得执行的sql的对象 Statement
• 获得返回的结果集
• 释放连接

11.1 第一个JDBC程序

public class jdbcDemo {
    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/bank?autoReconnect=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "a123456";
        //3.连接成功，获取数据库对象  Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);
        //4.获取执行sql的对象Statement
        Statement statement = connection.createStatement();
        //5.执行SQL, 可能存在结果  查看返回结果
        String sql = "select * from account";
        //返回结果
        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("money="+resultSet.getObject("money"));
        }

        //释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

URL

String url = "jdbc:mysql://localhost:3306/bank?autoReconnect=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true";
//mysql  --3306
//oracle --1521
jdbc:oracle:thin:@localhost:1521:sid

Staement是执行SQL的对象 PrepareStatement 是执行SQL的对象

String sql：编写sql
statement.executeQuery();//查询操作，返回结果集ResultSet
statement.execute();//执行任何Sql
statement.executeUpdate();//更新，插入，删除，都是用这个，返回一个受影响的行数

ResultSet

//获取指定的数据类型
resultSet.getObject()
resultSet.beforeFirst()//移动到第一
resultSet.afterLast()//移动到最后
resultSet.next()//读取下一行
resultSet.previous();//移动到第一行
resultSet.absolute(row)//移动到指定行

11.2 Statement对象

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

statement对象的executeUpdate方法，用于向数据库发送增删改的语句，执行完后，将会返回一个整数，即增删改语句导致了数据库几行数据发生了变化

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

增删查改代码实现：

CRUD操作：-create

使用execuUpdate(String sql)方法完成数据添加操作

Statement st = conn.createStatement();
String sql = "insert into account() values()";
int num = st.executeUpdate(sql);
if(num>0){
	system.out.println("插入成功！");
}

CRUD操作-delete

使用execuUpdate(String sql)方法完成数据删除操作

Statement st = conn.createStatement();
String sql = "delete from account where id=1";
int num = st.executeUpdate(sql);
if(num>0){
	system.out.println("删除成功！");
}

CRUD操作-update

使用execuUpdate(String sql)方法完成数据修改操作

Statement st = conn.createStatement();
String sql = "update account set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
	system.out.println("修改成功！");
}

CRUD-read

使用executeQuery(String sql)方法完成数据库查询操作

Statement st = conn.createStatement();
String sql = "select * account where id=1";
ResultSet rs = st.executeQuery(sql);//结果集
while(rs.next()){
	//根据获取列的数据类型，分别盗用rs的响应方法映射到java对象中
}

编写工具类后的代码实现：

public class TestInsert {
    public static void main(String[] args) {
       /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;


        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();
            //获取sql的执行对象
            st = conn.createStatement();
            //插入的sql语句
            String sql = "insert into account(id,`name`,`money`)"+"values(5,'戊',600.00)";
            //判断是否插入成功，插入成功会返回影响的行数，因此判断行数是否大于0即可
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);//rs这里没用到结果集，所以报黄
        }
    }
}

public class TestDelete {
    public static void main(String[] args) {
        /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;


        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();
            //获取sql的执行对象
            st = conn.createStatement();
            //插入的sql语句
            String sql = "delete from account where id = 3";
            //判断是否插入成功，插入成功会返回影响的行数，因此判断行数是否大于0即可
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);//rs这里没用到结果集，所以报黄
        }
    }
}

public class TestUpdate {
    public static void main(String[] args) {
        /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;


        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();
            //获取sql的执行对象
            st = conn.createStatement();
            //插入的sql语句
            String sql = "update account set money = 400.00 where id = 4";
            //判断是否插入成功，插入成功会返回影响的行数，因此判断行数是否大于0即可
            int i = st.executeUpdate(sql);
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);//rs这里没用到结果集，所以报黄
        }
    }
}

public class TestQuery {
    public static void main(String[] args) {
        /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();
            //获取sql的执行对象
            st = conn.createStatement();
            //插入的sql语句
            String sql = "select * from account";
            //返回结果集
            rs = st.executeQuery(sql);

            while(rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);//rs这里没用到结果集，所以报黄
        }
    }
}

工具类编写：

//加载工具类
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 in = jdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");//获取到了输入流
            Properties properties = new Properties();
            properties.load(in);
            //获取到配置文件中的相应值
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1.加载驱动，驱动只加载一次
            Class.forName(driver);//
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接:封装到了getConnection()方法中了
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放资源：封装到了release方法中了
    public static void release(Connection conn, Statement st, ResultSet rs){
        if (rs!=null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
            if (st!=null){
                try {
                    st.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }

    }
}

编写db.properties：

driver=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/bank?autoReconnect=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true
username=root
password=a123456

11.3 SQL注入

sql存在漏洞，sql会被拼接,数据库会被攻击，盗取信息。

代码实现：

//sql注入问题
/*登录业务模拟*/
public class Sql {
    public static void main(String[] args) {
//        login("dengzh","a123456");//登录
        login(" ' or '1=1","a123456");//SQL注入
    }
    public static void login(String name,String password) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        //连接数据库
        try {
            conn =jdbcUtils.getConnection();
            st = conn.createStatement();//创建Statement对象，执行sql

            //定义sql
            String sql = "select * from users where `name` ='"+name+"'and `password`='"+password+"'";//注意飘`和双引号“的使用
           //结果集
            rs = st.executeQuery(sql);
            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }

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

11.4 PrepareStatement 对象

PrepareStatement:可以防止sql注入，效率更高！

本质是将传进来的当作字符，其他的符号会转义

• 使用PrepareStatement的增删查改代码

插入：

public class TestInsert {
    public static void main(String[] args) {
        /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        PreparedStatement st = null;

        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();

            // 区别: 使用?  先写sql不执行
            String sql = "insert into account(id,`name`,`money`) values (?,?,?)";
            //获取sql的执行对象时，进行预编译
            st = conn.prepareStatement(sql);
            //给？赋值
            st.setInt(1,3);//1 表示第一个参数，3表示该参数的值
            st.setString(2,"dengxm");
            st.setString(3,"900000.00");
            //判断是否插入成功，插入成功会返回影响的行数，因此判断行数是否大于0即可
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);//rs这里没用到结果集  传一个null
        }
    }
}

删除：

public class TestDelete {
    public static void main(String[] args) {
        /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();
            //定义插入的sql语句
            String sql = "delete from account where id = ?";
            //获取sql的执行对象时进行预编译
            st = conn.prepareStatement(sql);
            //给问号 （？）赋值
            st.setInt(1,4);
            //判断是否插入成功，插入成功会返回影响的行数，因此判断行数是否大于0即可
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,null);//rs这里没用到结果集
        }
    }
}

修改：

public class TestUpdate {
    public static void main(String[] args) {
        /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;


        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();
            //插入的sql语句
            String sql = "update account set money = 16400.00 where id = ?";
            //获取sql的执行对象,进行预编译
            st = conn.prepareStatement(sql);
            //给问号赋值
            st.setInt(1,3);
            //判断是否插入成功，插入成功会返回影响的行数，因此判断行数是否大于0即可
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);//rs这里没用到结果集，所以报黄
        }
    }
}

查询：

public class TestQuery {
    public static void main(String[] args) {
        /*先定义好 数据库对象，操作sql对象，结果集,此代码演示的时插入操作，所以结果集的变量后面用不上*/
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;//查询使用到了结果集
        try {
            //获取数据库连接
            conn = jdbcUtils.getConnection();
            //插入的sql语句
            String sql = "select * from account where id=?";
            //获取sql的执行对象,进行预编译
            st = conn.prepareStatement(sql);
           //
            st.setInt(1,1);
            //返回结果集
            rs = st.executeQuery();

            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("money"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            jdbcUtils.release(conn,st,rs);//rs这里没用到结果集，所以报黄
        }
    }
}

12.JDBC操作事务

ACID原则

• 原子性
• 一致性
• 隔离性
• 持久性

隔离性产生的问题：

• 脏读
• 不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了改变
• 虚读（幻读）：在同一个事务内，读取到别人插入的数据

13.数据库连接池（后期具体再学习这部分吧）

池化技术：

准备一些预先的资源，过来就连接预先准备好的

• 最小连接数：10
• 最大连接数：15
• 等待超时：100ms

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

开源数据源实现

• DBCP
• C3P0
• Druid:阿里巴巴

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

DBCP

每种不同数据源，导入不同JAR包