B站 MySQL学习随手记全是满满的干货！

业务级别的MySQL学习与使用

1、数据库分类

1. 关系型数据库（SQL）

MySQL，Oracle，SQL Server，DB2，SQLlite
通过表和表之间的，行与列之间的关系进行数据的存储。（例如：学生信息表）

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

Redis，MongDB
对象存储，通过对象的自身的属性来决定

2、MySQL安装与配置

下载压缩文件，并解压缩到目标文件夹下
配置环境变量，例如安装路径为：F:\Environment\mysql-8.0.17-winx64 ,系统环境变量下path中新建填写 F:\Environment\mysql-8.0.17-winx64\bin.

安装目录下新建并配置my.ini文件

[mysql]
default-character-set=utf8
[mysqld]
#注意将这两个路径替换成实际的安装目录
basedir=F:\Environment\mysql-8.0.17-winx64
datadir=F:\Environment\mysql-8.0.17-winx64\data
port=3306
max_connections=200
character-set-server=utf8
default-storage-engine=INNODB
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

以管理员身份启动命令提示符,运行mysqld -install命令安装MySQL，显示成功后，再运行mysqld --initialize,初始化数据库，此时安装目录下生成了data文件夹
以net start mysql命令启动MySQL，启动成功后，执行mysql -u root -p登录MySQL，此时需要密码，打开data文件夹下的一个.err文件，找到随机密码输入登录.

6.修改密码，执行以下命令:ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';就可以重设密码，然后重启MySQL即可正常使用。

3、MySQL简单的命令行操作

net start mysql --启动数据库服务
mysql -u root -p --连接数据库

--所有的语句都以 分号 结尾
show database; --查看所有数据库

use + 'database name' --切换数据库

show tables; --查看数据库中所有的表
describe student; --显示数据库中所有的表的信息

...

4、数据库语言

DDL(数据库定义语言)
DML(数据库操作管理语言)
DQL(数据库查询语言)
DCL(数据库控制语言)

4.1、数据库的数据类型

数值

数据类型	数据大小	数据说明
tinyint	1个字节	十分小的数据
smallint	2个字节	较小的数据
mediumint	3个字节	中等大小的数据
int(常用)	4个字节	标准整数
bigint	8个字节	较大的数据
float	4个字节	单精度浮点数
double	8个字节	双精度浮点数
decimal		字符串形式的浮点数(金融计算使用)

字符串

数据类型	数据大小	数据说明
char	0~255	字符串固定大小(容易造成空间浪费)
varchar(常用)	0~65535	可变字符串
tinytext	2^8 - 1	微型文本
text(常用)	2^16 - 1	文本串

时间日期

类型名称	格式	描述
date	YYYY-MM-DD	日期格式
time	HH:mm:ss	时间格式
datetime	YYYY-MM-DD HH:mm:ss	常用的时间格式
timestamp	1970.1.1到现在的毫秒数	时间戳

4.2、拓展

每一个表，都必须存在以下五个字段！未来的项目要求，表示一个记录的存在意义

id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间

4.3、navicat中用sql语句创建表

要求创建一个学生表包含以下字段
学号（int），姓名，密码，性别，生日，家庭住址，邮箱。

--注意点:
--1、表名和字段名都尽量用 ``(反单引号) 括起来
--2、所有语句结尾要加逗号

create table
if
not exists `student` (
    `id` int ( 4 ) 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 = utf8

格式

CREAT TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ...
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

4.4、删除和修改数据表

修改

-- 修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE `student` RENAME AS `stu`
-- 增加字段 ALTER TABLE 表名 ADD `字段名` 列属性
ALTER TABLE `stu` ADD `age` INT(3) AFTER `sex`

-- 修改表的字段
-- ALTER TABLE 表名 MODIFY 字段名 列属性
ALTER TABLE stu MODIFY age VARCHAR(3) -- 修改约束
-- ALTER TABLE 表名 CHANGE 表名 新表名 列属性
ALTER TABLE stu CHANGE age sage int(3) -- 字段重命名

-- 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE stu DROP sage

-- 删除表(先判断是否存在再删除)
DROP TABLE IF EXISTS `tch`

建议所有的创建删除操作尽量加上判断，一面报错

常用命令

show CREATE DATABASE school --查看创建数据库的语句
SHOW CREATE TABLE student --查看创建student数据表的定义语句
DESC student --查看student表的结构

5、数据表类型

5.1关于数据库引擎

INNODB 默认使用
MYISAM 早期使用

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大(约为前者2倍)
优点	节约空间，速度快	安全性高，多表多用户操作

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

INNODB 在数据库表中只有一个.frm文件(MySQL8.0后没有)，以及上级目录下的ibdata1文件。
MYISAM 对应的文件
- .frm文件 --> 表结构定义文件
- .MYD文件 --> 数据文件(data)
- .MYI文件 --> 索引文件(index)

设置数据库表的字符编码

charset=utf8

不设置的话，MySQL的默认的字符集编码会导致中文乱码

6、MySQL数据管理

6.1外键

6.1.1、外键的添加

1、创建时添加

-- 将学生表中的grade字段设置为外键,引用gradelist中的gradeid字段
-- 方式一 自定约束名
KEY `约束名` (`设为外键的字段号`),
constraint `约束名` foreign key (`设为外键的字段号`) references `被引用的表`(`被引用的字段`)

-- 示例
KEY `FK_gradeid` (`grade`),
constraint `FK_gradeid` foreign key (`grade`) references `gradelist`(`gradeid`)

-- 方式二
foreign key (`设为外键的字段号`) references `被引用的表`(`被引用的字段`)

2、创建后修改表添加外键

alter table `表名`
add constraint `约束名` foreign key (`设为外键的字段号`) references `被引用的表`(`被引用的字段`);

-- 示例
ALTER TABLE `stu`
ADD constraint `FK_gradeid` FOREIGN KEY (`grade`) REFERENCES `gradelist` (`gradeID`);

以上的操作都是物理外键，数据库级别的外键，不建议使用!

阿里Java规范:[强制]不得使用外键与级联，一切外键概念必须在应用层（代码层）解决
每次做delete和update都必须考虑外键的约束，会导致开发的时候很痛苦，测试数据极为不方便。

6.1.2、最佳实践

数据库中之存放数据，只有行（数据）和列（字段）。
当需要使用多张表的时候，可以用程序去实现外键。

6.2、DML语言(牢记)

DML语言：数据操作语言

Insert

-- 插入数据
-- 语法：INSERT INTO `表名`（[`字段1`,`字段2`,...]）VALUES('值1','值2',...)
-- 值与字段一一对应
INSERT INTO `gradelist`(`gradeName`)VALUES ('大一');

-- 一次插入多条数据
INSERT INTO `gradelist`(`gradeName`)VALUES ('大二'),('大三'),('大四');

update

-- 修改数据
-- 语法:UPDATE 表名 set `字段名` = '新值' [where (条件)]
UPDATE `stu` SET `name` = '路人甲' WHERE `name` = '张三';

-- 若不指定条件 默认修改所有
UPDATE `stu` SET `name` = '路人'

-- 修改多个数据
UPDATE `stu`
SET `grade` = 1,birthday = '2001-7-9' -- 修改的数据用逗号隔开
WHERE `name` = '路人甲';

delete
```
-- 语法 delete from 表名 [where 条件]
-- 全部删除（危险操作）
DELETE FROM `stu`

-- 删除指定数据
DELETE FROM `stu` where id = 3;
```
- TRANCATE命令
作用：完全清空一个数据库表，表的结构和索引约束不会改变
- delete 与 trancate命令
相同点：
都能够删除数据，都不会删除表结构
不同点:
TRANCETE 重新设置自增列，计数器归零TRANCATE不会影响事务

7、DQL查询数据(重点)

（Data Query LANGUAGE）数据查询语言

7.1、简单的select查询

…

7.2、联表查询 Join ON

在这里插入图片描述

连接方式	描述
Inner Join	如果表中有一个匹配项，就返回行
Left Join	从左表中返回所有值，即使在右表中没有匹配
Right Join	从右表中返回所有值，即使在左表中没有匹配

7.3、分页（limit）和排序 (order by)

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

-- 表示显示从第几条起的多少条数据
select..
from..
where..
...
limit 0,5 -- 显示查询的从第一条开始的五条数据

7.4、聚合函数及分组过滤

7.4.1、常用的聚合函数

函数名	描述
Count()	计数
SUM()	求和
AVG()	求平均值
MAX()、MIN()	最大、最小值
…	…

count(列名)，count(1)，count(*) 的区别

使用方法	区别
count(列名)	只包括列名那一列，在统计结果的时候，会忽略列值为空
count(1)	包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(*)	包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL

后两者本质上区别不大，都不会忽略值为NULL的数据行，在特定的使用场景下，执行效率有所不同

7.4.2、group by （分组）及 having（过滤分组）

group by 是让排序结果按规则进行分组排列
having 与 where用途相似用来过滤分组中不符合要求的分组

-- 代码示例：
    -- 要求筛选平均分>80的学科
    select SubjectName as '科目',
           AVG(StudentResult) as '平均分',
           MAX(StudentResult) as '最高分',
           MIN(StudentResult) as '最低分'
    FROM result
    LEFT JOIN `subject`
    ON result.SubjectNo = `subject`.SubjectNo
    GROUP BY result.SubjectNo
    HAVING 平均分>80

7.5、（拓展）数据库级别的MD5加密

主要增强算法复杂度和不可逆性
- MD5不可逆：具体的值MD5加密后的密文是一样的

-- ============测试MD5============
  create table `testmd5` (
    `id` int ( 4 ) not null,
    `name` varchar ( 20 ) not null,
    `pwd` varchar ( 50 ) not null,
    primary key ( `id` )
  ) engine = innodb default charset = utf8

  insert into testmd5 values (1,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')

  -- 加密
  UPDATE testmd5
  SET pwd=MD5(pwd)
  WHERE id = 1

  -- 插入时加密
  insert into testmd5 values (4,'小明',MD5('123456'))

  -- 查询校验
  SELECT *
  FROM testmd5
  where `name` = '小明' and pwd = MD5('123456')

7.6、select总结

-- 语法顺序
select [去重] `要查询的字段` [as '别名'] -- 联表查询时避免模棱两可
from `表名`
(Inner/left/right) join `连接的表` on 等值条件
where (具体的值，或者子查询语句)
group by (分组参照)
having (过滤分组的条件) -- 用法与where一样
order by `排序的参考字段` [ASC/DECS] -- 默认递增
limit index,count -- 列出从第index条开始的count条数据

8、事务（Transaction）

8.1、什么是事务

作为单个逻辑工作单元执行的一系列操作，要么都成功，要么都失败

事务的特性（4个) 又叫ACID特性

原子性（Atomicity）
一致性（Consistency）
隔离性（Isolation）
持久性（Durability）

参考博客：https://blog.csdn.net/dengjili/article/details/82468576

原子性（Atomicity）：
要么都成功，要么都失败

一致性（Consistency）：
事务前后的数据完整性要保证一致

持久性（Durability）：
事务一旦提交不可逆，被持久化到数据库中

隔离性（Isolation）：
多个用户并发访问数据库时，数据库为每个用户开启事务，不被其他事务的操作所干扰，并发事务之间相互隔离

隔离所导致的一些问题：
脏读，不可重复读，幻读。

8.2、具体实现

-- mysql 默认开启事务自定提交
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启（默认）

-- 简单流程

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

-- 2、事务开启
START TRANSACTION -- 标记一个事务的开启

/* 之后的sql操作都在这同一个事务内进行 */



-- 3、提交：持久化（成功!）
COMMIT
-- 3、回滚：回到起始状态（失败!）
ROLLBACK

-- 4、事务结束
SET autocommit = 1 -- 开启自动提交


-- 拓展了解
SAVEPOINT `保存点名` --设置一个事务的保存点
ROLLBACK TO SAVEPOINT `保存点名` --若事务失败 回滚到上一个保存点
RELEASE SAVEPOINT `保存点名`  -- 撤销保存点

8.2.1、模拟场景

-- =======模拟转账场景=========
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

USE shop

  CREATE TABLE `account`(
    `id` INT(3) NOT NULL auto_increment,
    `name` VARCHAR(10) not NULL,
    `money` DECIMAL(9,2) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE = INNODB CHARSET = utf8

INSERT INTO `account`(`name`,`money`) VALUES ('A','2000'),('B','10000')

SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启事务

UPDATE account set money = money - 500 WHERE `name` = 'A' -- A转出500
UPDATE account SET money = money + 500 WHERE `name` = 'B'  -- B收款500

COMMIT; -- 提交事务
ROLLBACK; -- 回滚

set autocommit = 1;

9、索引

索引（index）是帮助MySQL高效获取数据的数据结构。

9.1、索引的分类

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

主键索引（primary key）

唯一的标识，主键不可重复，只能有一个列作为主键
唯一索引（unique key）

避免重复的列出现，唯一索引可以重复，多个列都可以标识为唯一索引
常规索引（index/key）

默认，用index/key关键字设置
全文索引（FullText）

快速定位数据位置

9.2、基础语法

-- =======索引的使用=====
-- 显示所有索引信息
show index from student

-- 增加一个索引 add fulltext index `索引名`(`列名`)
alter table student add fulltext index `studentname`(`studentname`)

-- 删除索引：
DROP INDEX 索引名 ON 表名字;
-- 删除主键索引:
ALTER TABLE 表名 DROP PRIMARY KEY;

-- explain 分析sql执行状况
explain select * from student

explain select * from student where match(studentname) against ('刘')

9.3、测试索引

  SELECT *
  FROM app_user
  where  `name` = '用户99999'  -- 创建索引前 > 时间: 1.924s
                              -- 创建索引后 > 时间: 0.083s

  -- 索引名 命名规范 id_表名_字段名
  -- CREATE INDEX 索引名 ON 表(字段)
  CREATE INDEX id_app_user_name ON app_user(`name`);

在这里插入图片描述

从测试结果可以发现索引显著提高了大量数据的查询性能

9.4、索引原则

索引并不是越多越好
不要对经常变动的数据加索引
小数据的表不需要添加索引
索引一般加在常用来查询的字段上

索引的数据结构

Hash类型的索引
Btree:InnoDB的默认数据结构

推荐阅读 MySQL索引背后的数据结构及算法原理

10、权限管理与数据库备份

10.1、用户管理

可视化窗口管理

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RaEaWt5D-1581391881960)(Mysql学习文件/用户管理.png)]

SQL命令

系统数据库中有一用户表即:mysql.user,
本质上还是对表数据的增删改查。

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER sakura0817 IDENTIFIED BY '170312'

-- 修改密码（修改当前用户密码）
SET password = '170312';

-- 修改密码（修改指定用户密码）
set password FOR sakura0817 = '170312'

-- 用户重命名
RENAME USER sakura0817 TO 5akura

-- 用户授权 all privileges全部的权限 （Grant Option）无法授予
grant all privileges on *.* to 5akura

-- 查看权限
show grants for 5akura -- 查看指定用户
show grants for root@localhost -- 查看管理员

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM 5akura

-- 删除用户
DROP USER 5akura

10.2、MySQL备份

备份方式

直接拷贝data文件夹
在可视化工具中手动导出
使用命令行

模板：
mysqldump -h主机地址 -u用户名 -p密码 数据库名 表名 >导出路径
示例：
mysqldump -hlocalhost -uroot -p170312 shop >F:/文档文件/shop.sql

# 导入
登陆后
source 备份文件

11、数据库的规约，三大范式

11.1、为什么要需要设计

当数据较复杂时，我们需要设计数据库辅助管理数据

糟糕的数据库设计

数据冗余，浪费空间

数据库插入删除操作麻烦、产生异常[屏蔽使用物理外键]

程序的性能差

良好的数据库设计

节省内存空间

保证数据库的完整性

方便我们开发系统

关于数据库的设计
- 分析需求：分析业务和需要处理的数据库要求
- 概要设计：设计关系的ER图
数据库设计具体步骤（个人博客为例）
- 收集信息，分析需求
  - 用户表（用户登录注销，用户个人信息，写博客，创建分类）
  - 分类表（文章分类，创建者）
  - 文章类（文章的信息）
  - 评论表
  - 友联表（友链信息）
  - 自定义表（系统信息，某个关键字，或者一些主字段）
- 标识实体（将每个需求落地到每个关键字）

11.2、三大范式

11.1、为什么要需要设计

当数据较复杂时，我们需要设计数据库辅助管理数据

糟糕的数据库设计

数据冗余，浪费空间

数据库插入删除操作麻烦、产生异常[屏蔽使用物理外键]

程序的性能差

良好的数据库设计

节省内存空间

保证数据库的完整性

方便我们开发系统

关于数据库的设计
- 分析需求：分析业务和需要处理的数据库要求
- 概要设计：设计关系的ER图
数据库设计具体步骤（个人博客为例）
- 收集信息，分析需求
  - 用户表（用户登录注销，用户个人信息，写博客，创建分类）
  - 分类表（文章分类，创建者）
  - 文章类（文章的信息）
  - 评论表
  - 友联表（友链信息）
  - 自定义表（系统信息，某个关键字，或者一些主字段）
- 标识实体（将每个需求落地到每个关键字）
- 标识实体之间的关系

11.2、三大范式（数据库设计规范）

为什么需要数据规范化
- 信息重复
- 更新异常
- 插入异常
- 删除异常

11.2.1、三大范式具体内容

第一范式（1NF）

要求数据库表中属性都是不可再分的原子数据项。

第二范式（2NF）

在1NF的基础上，所有的非码属性必须完全依赖于候选码（消除部份依赖）。

第三范式（3NF）

在2NF的基础上，任何非主属性不依赖与其他任何非主属性（消除传递依赖）。

11.2.2、规范性与性能问题

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

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

12、JDBC(重点！！)

12.1、简介

12.1.2、数据库驱动

程序 连接访问 数据库 需要通过数据库驱动。

12.1.2、JDBC是什么

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-U86KlsIb-1582618981763)(JDBC是什么.png)]

由于不同数据库的驱动都不相同，操作也各不相同

SUN公司为了简化开发人员的（对数据库的统一）操作，提供了一个（Java操作数据库）规范，俗称JDBC，规范的实现由各个厂商去做，开发人员只需要掌握JDBC接口的使用即可。

12.2、第一个JDBC程序

12.2.1、创建测试数据库

  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 values (1,'张三','123456','zs@sina.com','1999-12-03'),
  (2,'李四','123456','ls@sina.com','1997-11-13'),
  (3,'王五','123456','ww@sina.com','2000-2-08');

12.2.2、编写Java程序

创建一个空项目
导入数据库驱动jar包
编写Java程序

package com.sakura.jdbcstudy;

import java.sql.*;

/**
 * 第一个JDBC程序
 *
 * @author 桜
 * @Date 2020/2/11
 */
public class JDBC_demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {

        //1、加载驱动
        /**
         * com.mysql.jdbc
         * 使用新版驱动 com.mysql.cj.jdbc
         */
        Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法

        //2、用户信息和url
        /**
         * ？:访问链接
         * 三个参数:
         * useUnicode=true 支持中文编码
         * characterEncoding=utf8 字符集utf-8
         * useSSL=true 使用安全链接
         */
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String userName = "root";
        String passWord = "170312";

        //3、连接成功,获取数据库对象（getConnection(url,username,password)方法）  返回对象为数据库
        Connection connection = DriverManager.getConnection(url, userName, passWord);

        //4、执行SQL对象(createStatement()方法)
        Statement statement = connection.createStatement();

        //5、SQL的对象去执行SQL(若有执行结果，需要查看返回结果)
        String sql = "select * from users";

        ResultSet resultSet = statement.executeQuery(sql);//执行sql查询语句,并返回封装好的结果集;

        //解读结果集 getObject(列名)
        while (resultSet.next()) {
            System.out.println("id = "+resultSet.getObject("id"));
            System.out.println("name = "+resultSet.getObject("NAME"));
            System.out.println("password = "+resultSet.getObject("PASSWORD"));
            System.out.println("email = "+resultSet.getObject("email"));
            System.out.println("birth = "+resultSet.getObject("birthday"));
            System.out.println("-------------------");
        }

        //6、关闭连接
        resultSet.close();
        statement.close();
        connection.close();

    }
}

12.2.3、代码步骤总结

加载驱动（使用新版驱动com.mysql.cj.jdbc.Driver）
与数据库建立连接，获取Connection对象（DriverManager.getConnection()）
获取执行sql语句的Statement对象（connection.creatStatement()）
执行sql语句获得结果集
释放连接

12.2.4、代码详细

创建驱动

    //DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
    Class.forName("com.mysql.cj.jdbc.Driver"); //固定写法

package com.mysql.cj.jdbc;

import java.sql.DriverManager;
import java.sql.SQLException;

public class Driver extends NonRegisteringDriver implements java.sql.Driver {
    public Driver() throws SQLException {
    }

    static {
        try {
            //注册驱动
            DriverManager.registerDriver(new Driver());
        } catch (SQLException var1) {
            throw new RuntimeException("Can't register driver!");
        }
    }
}

可以看到在com.mysql.cj.jdbc.Driver()类中有一个静态代码块会在加载时自动执行注册驱动，所以只需用forname()方法即可,因为对类进行反射调用,一定会引发类初始化从而执行驱动的注册。

URL

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

  // 协议/主机地址：端口号/数据库名？参数1&参数2&参数3

  //mysql-->3306,Oracle-->1521
  //jdbc:oracle:thin@localhost:1521:sid

Connection （代表数据库）

//能执行大多数数据库中的操作
connection.rollback();//事务回滚
connection.commit();//事务提交
connection.setAutoCommit();//设置自动提交

执行SQL的对象

statement（不安全）

//可以执行多种SQL语句
statement.executeQuery();//查询  返回查询结果集
statement.executeUpdate();// 更新，插入，删除 返回受影响行数
statement.execute();//执行所有SQL语句
statement.executeBatch();//批次处理

connection.prepareStatement()（防止SQL注入）

ResultSet（只有查询有结果集返回）

获取数据

resultSet.getObject()//在不清楚列数据类型时使用
//如果知道列类型就使用指定方法get数据
getString(),getFloat(),getDate(),....

遍历（指针）

resultSet.next();//下一条
resultSet.previous();//上一条
resultSet.beforeFirst();//第一条
resultSet.afterLast();//最后一条
resultSet.absolute();//指定行

资源释放

resultSet.close();
statement.close();
connection.close();//耗费资源,用完即关

12.3、SQL注入的问题

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

数据输入过滤不严格，通过SQL拼接字符串 or，进入数据库盗取数据。

public class SQL注入 {

    public static void main(String[] args) {
        login(" 'or '1=1","123456");
    }

    public static void login(String userName, String password) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();

            //SQL语言
            String sql = "select * from users where `NAME`= '"+userName+"' and `passWord` = '" +password+ "'";


            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println("NAME = "+resultSet.getString("NAME")+"--> email = " + resultSet.getString("email"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, statement, resultSet);
        }
    }
}

12.4、PreparedStatement对象

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

public class TestInsert {
    public static void main(String[] args) {
        Connection connection =null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnection();

            //区别
            //1、使用 '?'占位符代替数据作为参数
            String sql = "insert into users(id, `NAME`, `passWord`, email, birthday) values(?,?,?,?,?)";

            //2、预编译SQL,先写SQL,但是不执行
            preparedStatement = connection.prepareStatement(sql);

            //3、手动参数赋值
            // preparedStatement.setObject(); 不清楚列类型时使用
            /**
             * 参数为（列标，具体数据）
             * 1代表第一个字段,2代表第二个字段....
             */
            preparedStatement.setInt(1,5);//设置id
            preparedStatement.setString(2,"官宇辰");//设置name
            preparedStatement.setString(3,"170312");//设置passWord
            preparedStatement.setString(4,"G843452233@outlook.com");//设置email
            // 注意点 sql.Date 与 util.Date 不同
            preparedStatement.setDate(5,new Date(new SimpleDateFormat("yyyy-MM-dd").parse("2000-08-17").getTime()));//设置birthday

            //4、执行 直接执行无需传参
            int i = preparedStatement.executeUpdate();
            if (i>0) {
                System.out.println("操作成功");
            }

        } catch (SQLException | ParseException e) {
              e.printStackTrace();
        } finally {
             //...释放资源
            JdbcUtils.release(connection, preparedStatement, resultSet);

        }
      }
}

PreparedStatement 如何避免SQL注入

本质：把传入的参数当作字符，把用户非法输入的单引号用\反斜杠做了转义，就避免了参数也作为条件的一部分。

12.5、IDEA连接Mysql 处理事务

开启事务connection.setAutoCommit(false);
一组业务执行完毕，提交事务。
可以在catch语句中显式的定义回滚语句,但默认失败了就会回滚。

代码实现

public class TestTransacation02 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            //关闭数据自动提交，会自动开启事务
            connection.setAutoCommit(false);

            String sql1 = "update account set money = money - 100 where name = 'A'"; //A转出100
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();



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

            //业务完毕，提交事务
            connection.commit();
            System.out.println("转账成功");


        } catch (SQLException e) {
            try {
                connection.rollback(); //失败回滚
                System.out.println("转账失败");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(connection, preparedStatement, resultSet);
        }
    }
}

12.6、数据库连接池

产生原因：因为数据库的连接与释放十分浪费系统资源

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

常用连接数
最小连接数
最大连接数（业务最高承载上限）
- 等待超时

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

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ZN9t0Nru-1582618981766)(dbcp实现接口.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c5LRonNM-1582618981767)(c3p0继承接口.png)]

优秀的开源数据源实现（使用后，项目开发中不需要编写连接数据库的代码了）

DBCP(commons-dbcp-1.4.jar，commons-pool-1.6.jar)
C3P0
Druid(阿里巴巴)

结论

无论使用什么数据源，本质都是一样，都要实现DataSource接口，方法不变，只是不同的实现方式。

posted @ 2022-01-21 20:33 5akura 阅读(42) 评论(0) 编辑收藏举报

刷新页面返回顶部

5akura

菜鸟正在努力学习~

B站 MySQL学习随手记 全是满满的干货！

业务级别的MySQL学习与使用

1、数据库分类

1. 关系型数据库（SQL）

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

2、MySQL安装与配置

3、MySQL简单的命令行操作

4、数据库语言

4.1、数据库的数据类型

4.2、拓展

4.3、navicat中用sql语句创建表

4.4、删除和修改数据表

5、数据表类型

5.1关于数据库引擎

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

6、MySQL数据管理

6.1外键

6.1.1、外键的添加

6.1.2、最佳实践

6.2、DML语言(牢记)

7、DQL查询数据(重点)

7.1、简单的select查询

7.2、联表查询 Join ON

7.3、分页（limit）和排序 (order by)

7.4、聚合函数及分组过滤

7.4.1、常用的聚合函数

7.4.2、group by （分组）及 having（过滤分组）

7.5、（拓展）数据库级别的MD5加密

7.6、select总结

8、事务（Transaction）

8.1、什么是事务

8.2、具体实现

8.2.1、模拟场景

9、索引

9.1、索引的分类

9.2、基础语法

9.3、测试索引

9.4、索引原则

10、权限管理与数据库备份

10.1、用户管理

10.2、MySQL备份

11、数据库的规约，三大范式

11.1、为什么要需要设计

11.2、三大范式

11.1、为什么要需要设计

11.2、三大范式（数据库设计规范）

11.2.1、三大范式具体内容

11.2.2、规范性与性能问题

12、JDBC(重点！！)

12.1、简介

12.1.2、数据库驱动

12.1.2、JDBC是什么

12.2、第一个JDBC程序

12.2.1、创建测试数据库

12.2.2、编写Java程序

12.2.3、代码步骤总结

12.2.4、代码详细

创建驱动

URL

Connection （代表数据库）

执行SQL的对象

ResultSet（只有查询有结果集返回）

资源释放

12.3、SQL注入的问题

12.4、PreparedStatement对象

12.5、IDEA连接Mysql 处理事务

12.6、数据库连接池

公告

B站 MySQL学习随手记全是满满的干货！