MySQL数据库+jdbc -- 笔记

Mysql

注解：导包，配置文件等内容均在此分享链接

链接：https://pan.baidu.com/s/12uvp3zrQowF41Y2PERjFUA
提取码：YQHF

​ 学习视频链接：https://www.bilibili.com/video/BV1NJ411J79W

​ 如有疑问私信： V: CBWR-K Q:1213456783

1、连接数据库

1.软件下载

mysql5.7 64下载地址：

https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip

2.步骤

• 1、下载后得到zip压缩包.

• 2、解压到自己想要安装到的目录，本人解压到的是D:\Environment\mysql-5.7.19

• 3、添加环境变量：我的电脑->属性->高级->环境变量

  • 选择PATH,在其后面添加: 你的mysql 安装文件下面的bin文件夹

• 4、编辑 my.ini 文件 ,注意替换路径位置

basedir=D:\Program Files\mysql-5.7\
datadir=D:\Program Files\mysql-5.7\data\
port=3306
skip-grant-tables

• 5、启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld –install (安装mysql)
• 6、再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件
• 7、然后再次启动mysql 然后用命令 mysql –u root –p 进入mysql管理界面（密码可为空）
• 8、进入界面后更改root密码

update mysql.user set authentication_string=password('123456') where
user='root' and Host = 'localhost';

• 9、刷新权限

flush privileges;

• 10、修改 my.ini文件删除最后一句skip-grant-tables
• 11、重启mysql即可正常使用

 net stop mysql
 net start mysql

• 12、连接上测试出现以下结果就安装好了

1.1、下载可视化软件

可手动操作,管理MySQL数据库的软件工具

特点 : 简洁 , 易用 , 图形化

鄙人使用的Navicat 下载链接：

http://www.navicat.com.cn/products

下载注册机，助于我们破解

https://www.jb51.net/article/199496.htm

1.2、连接数据库

使用SQLyog管理工具自己完成以下操作 :

• 连接本地MySQL数据库
• 新建MySchool数据库
  • 数据库名称MySchool
  • 新建数据库表(grade)
    • 字段
      • GradeID : int(11) , Primary Key (pk)
      • GradeName : varchar(50)

在历史记录中可以看到相对应的数据库操作的语句 .

打开MySQL命令窗口

• 在DOS命令行窗口进入 安装目录\mysql\bin
• 可设置环境变量，设置了环境变量，可以在任意目录打开！

连接数据库语句：mysql -h 服务器主机地址 -u 用户名 -p 用户密码

注意 : -p后面不能加空格,否则会被当做密码的内容,导致登录失败 !

1.3、连接命令

命令	说明
flush privileges;	刷新权限
net start mysql	开启服务
net stop mysql	停止服务
mysql -u root -p	登陆账号
exit	退出连接

#修改数据库管理密码
update mysql.user set authentication_string=password('wyq121') where user='root' and Host = 'localhost';

1.4、基本语法操作

命令	说明
show databases;	查看所有的数据库
use 数据库名;	切换数据库
show tables;	查看数据库中所有的表
describe 表名;	显示数据库中所有表的信息
create database 新数据库名;	创建一个数据库
– /**/	注释

数据库×××语言 CRUD 增删改查 CV API CRUD (业务)

DDL 定义

DML 操作

DQL 查询

DCL 控制

2、操作数据库

操作数据库 > 操作数据库中的表> 操作数据库中表的数据

mysql关键字不区分大小写

中括号可选，大括号必选

2.1、操作数据库（了解）

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] westos

2、删除数据库

DROP DATABASE [IF EXISTS] westos

3、使用数据库

-- tab 键的上面，如果表明或者字段名是一个特殊字符，就需要带 ``
USE `school`

4、查询数据库

SHOW DATABASES -- 查看所有的数据库

2.2、数据库的数据 列 类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用的 int
• big 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节 (精度问题)
• decimal 字符串形式的浮点数 金融计算的时候，一般是使用decimal

字符串

• char 固定大小的字符串 0~255
• varchar 可变字符串 0~65535 常用的变量 对应String
• tinytext 微形文本 2^8-1
• text 文本串 2^16-1 保存大文本

时间日期

java uito.Date;

• date YYYY-MM-DD 日期
• time HH: mm: ss 时间格式
• datetime YYYY-MM-DD HH: mm: ss 最常用的时间格式
• timestamp 时间戳 1970.1.1 到现在的毫秒数！ 也较为常用
• tear 年份表示

null

• 没有值，未知
• 注意 不要使用null尽行运算，结果为null

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

Unsigned:

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

zerofill:

• 0填充的
• 不足的位数使用0来填充 , int(3) , 5 … 005

自增：

• 通常理解为自增，自动在上一条记录的基础上 + 1 （默认）
• 通常用来设计唯一的主键~ index , 必须是整数类型
• 可以自定义设计主键自增的起始值和步长

非空 Null not null：

• 假设设置为not null 如果不给它赋值就会报错！
• Null, 如果不填写值，默认就是null

默认：

• 设置默认的值！
• sex , 默认值为男 ， 如果不指定该列的值，则会有默认的值

拓展： 听听就好

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

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

2.4、创建数据库表（重点）

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

格式

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

常用命令

SHOW CREATE DATABASE school  #查看数据库的语句
SHOW CREATE TABLE student  	 #查看student数据表的定义语句
DESC student				 #显示表的结构

2.5、数据表的类型

#关于数据库引擎
/*
	INNODB 默认使用
	MYISAM 早些年使用
*/

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

常规使用操作：

• MYISAM 节约空间，速度较快

• INNODB 安全性高，事物的处理，多表多用户操作

在物理空间存在的位置

所有的数据库文件都存在 data 目录下 一个文件夹就对应一个数据库

本质还是文件的存储！

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

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

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

CHARSET = utf8

不设置的话 ， 会是mysql默认的字符集编码~（不支持中文）

MYSQL 的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-sever=utf8

2.6、修改删除表

修改

# 修改表名 ：ALTER TABLE 旧表名 RENAME AS 新表明
ALTER TABLE student RENAME AS student1
# 增加表字段 ：ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student ADD love VARCHAR(11) DEFAULT 'wyq'

# 修改表的字段 （重令名，修改约束！）
-- ALTER TABLE 表名 MODIFY 字段名 列属性（）
ALTER TABLE student MODIFY LOVE INT(520)  #修改约束
-- ALTER TABLE 表名 CHANGE 旧名称 新名称 列属性（）
ALTER TABLE student CHANGE LOVE LOVE1 INT(520) #字段重令名

-- 删除表的字段 ：ALTER TABLE 表名 DROP 字段名
ALTER TABLE student DROP LOVE

删除

# 删除表 （如果存在再删除）
DROP TABLE IF EXISTS student

所有的创建和删除操作尽量加上判断以免报错

注意点：

• `` 字段名，使用这个包裹！
• 注释 – /**/
• sql 关键字大小写不敏感 建议写小写 自己看的懂
• 所有的符号都是英文

3、MySQL数据管理

3.1、外键（了解即可）

方式一、在创建表的时候，增加约束（麻烦，比较复杂）

CREATE TABLE IF NOT EXISTS`grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学生表的 gradeid 字段 要去引用年级表的 gradeid
-- 定义外键Key 
-- 给这个外键添加约束 （执行引用） references 引用

CREATE TABLE IF NOT EXISTS `student2`(
	`id` INT(4) NOT NULL COMMENT '学号',
	`name` VARCHAR(11) NOT NULL DEFAULT 'wyq' COMMENT '姓名',
	`pwd` VARCHAR(15) NOT NULL DEFAULT '123456' COMMENT '密码',
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学生的年级',
	`sex` VARCHAR(3) NOT NULL DEFAULT '男' COMMENT '性别',
	`email` VARCHAR(50)  DEFAULT NULL COMMENT '邮箱',
  	PRIMARY KEY(`id`)	,
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

删除有外键关系的表的售后，必须要先删除引用别人的表（从表） ，再删除被引用的表（主表）

方式二、创建表成功后添加外键约束

CREATE TABLE `student2` (
  `id` int(4) NOT NULL COMMENT '学号',
  `name` varchar(11) NOT NULL DEFAULT 'wyq' COMMENT '姓名',
  `pwd` varchar(15) NOT NULL DEFAULT '123456' COMMENT '密码',
  `gradeid` int(10) NOT NULL COMMENT '学生的年级',
  `sex` varchar(3) NOT NULL DEFAULT '男' COMMENT '性别',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

# 创建表的时候没有外键关系
ALTER TABLE student2
ADD CONSISTENT `FK_gradeid`FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE 表 ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 那个表的(那个字段)

以上的操作都是物理外键，数据库级别的外键，不建议使用（避免数据库过多造成困扰，这里了解即可）

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
• 我们先使用多张表的数据，想使用外键（程序去实现）

3.2、DML语言（全部记住）

**数据库的意义：**数据储存，数据管理

DML语言： 数据操作语言

• Insert
• update
• delete

3.3、添加

insert

-- 插入语句（添加）
-- insert into 表名([字段名1，字段2,字段3])values('值1','值2','值3',....)
INSERT INTO `grade`(`gradename`) VALUES('大四')

-- 由于逐渐自增我们可以省略（如果不写表字段，他就会一一匹配）
INSERT INTO `grade` VALUES('大三')
-- 一般写插入语句时，我们一定要数据和字段一一对应

-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('大二'),('大一') 

INSERT INTO `student2`(`name`) VALUES ('张三')
INSERT INTO `student2`(`name`,`pwd`,`sex`) 
VALUES ('李四','aaaaaa','男'),('王五','aaaaaa','男')

语法：

insert into 表名([字段1，字段2,字段3])values('值1','值2','值3',....)

注意事项：

​ 1.字段与字段之间使用 英文逗号 隔开

​ 2.字段是可以省略的，但是后面的值必须要一一对应

​ 3.可以同时插入多条数据，VALUES 后面的值，需要使用，隔开即可VALUES（）,（），....

3.4、修改

update

-- 修改学员名字，带了条件
UPDATE `wang` SET `name` = '牛逼' WHERE id = 10;

-- 不指定条件的情况下，会改动所有的表
UPDATE `wang` SET `name` = '牛逼'

#语法：
-- UPDATE 表名 SET 字段名 = 新值 set,字段名 = 新值 where[条件]

-- 修改多个值
UPDATE `wang` SET `name` = '王怡情',`sex` = '女',`love` = 'null' WHERE id = 1;

条件: where子句运算符id等于某个值，大于某个值，在某个区间内修改…

操作符会返回

操作符	含义	范围	结果
=	等于	5=6	false
<> 或 !=	不等于	5<>6	true
>	大于
<	小于
<=	小于等于
>=	大于等于
BETWEEN … and …	在某个范围内	[2,5]
AND	我和你 &&	5>1 and 1>2
OR	我或你 | |	5>1 or 1>2

-- 通过多个条件定位数据，无上限！ && ||
UPDATE `wang` SET `name` = '王怡情' WHERE `name`='葛霄' AND sex = '男';
UPDATE `wang` SET `sex` = '女' WHERE `name`='王怡情' AND sex = '男';
UPDATE `wang` SET `love` = 'nice' WHERE `name`='王怡情' AND sex = '女';

官方语法: UPDATE 表名 set colnum_name = value,[colnum_name - value,....] where [条件]

语法: UPDATE `表名` SET colnum_name = `新值` WHERE `字段名` = '值' ADC `字段名` = '值'

注意事项:

• colnum_name 是数据库的列，尽量戴上``

• where 后面是筛选的条件，如果没有指定则会修改所有的列

• value 是一个具体的值，也可以是一个变量

• 多个设置的属性之间，使用英文逗号隔开

  UPDATE `wang` SET `name` = CURRENT_TIME WHERE `name` = '尚奇奇' AND sex = '女'
  

3.5、删除

delete 命令

语法： delete from 表名 [where 条件]

-- 删除数据 (避免这样写，会全部删除)
DELETE FROM `wang` 

-- 删除指定数据
DELETE FROM `wang` WHERE id = 1;

TRUNCATE 命令

作用:完全清空一个数据库表，表的结构和索引约束不会变~

-- 清空 grade 表
TRUNCATE `grade`

delete TRUNCATE 的区别

• 相同点： 都能删除数据，都不会删除表结构
• 不同：
  • TRUNCATE 重新设置 自增列 计数器会归零
  • TRUNCATE 不会影响事务

-- 测试delete 和 TRUNCATE 的区别
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8
-- 添加数据
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')
-- 不会影响自增
DELETE FROM `test` 
-- 自增会归零
TRUNCATE TABLE `test` 

了解即可：DELETE删除的问题，重启数据库，现象

• InnODB 自增列会重1开始（存在内存中的，断电即失）
• MyISAM 继续从上一个自增量开始（存在文件夹中得，不会丢失）

4、DQL查询数据（重中之重）

4.1、DQL

（Data Query LANGUAGE：数据查询语言）

• 所有的查询操作都用它 Select
• 简单的查询，复杂的查询它都能做~
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条

注意 : [ ] 括号代表可选的 , { }括号代表必选得

4.2、查询所有字段

-- 查询全部的学生    SELECT 字段 FROM 表
SELECT * FROM wang

-- 查询指定字段
SELECT `name`,`love`,`sex` FROM wang 

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `name` AS 名字,`love` AS 特征,`sex` AS 性别 FROM wang AS s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名:',name) as 新名字 FROM wang

语法：SELECT 字段，... FROM 表

有的时候，列名字不是那么的见名知意，我们用AS起别名 字段名 AS 别名 表明 AS 别名

去重 dis

作用：去除SELECT 查询出来的结果中重复的数据，重复的数据只显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM `result` -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentNumber` FROM result
-- 发现重复数据，去重
SELECT DISTINCT `studentNumber` FROM result

数据库的列（表达式）

SELECT VERSION()	-- 查询系统版本（函数）
SELECT 100*3-1 AS 计算结果 -- 用来计算 （表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学员考试成绩 + 1分查看
SELECT `studentNumber` +1 AS '提分后' FROM result

数据库中的表达式： 文本值 ， 列 ， 函数 ， 计算表达式 ， 系统变量…

select 表达式 from 表

4.3、where条件字句

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

搜索的条件由一个或者多个表达式！ 结果都是一个布尔值

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与 两个都为真，结果为真
or ||	a or b a || b	逻辑或 其中一个为真，结果为真
Not !	not a ! a	逻辑非 真为假 ， 假为真

尽量使用英文字母

-- ===========================    where    ===========================
SELECT `name` AS 名字,`studentNumber` AS 分数 FROM result

-- 1.查询考试成绩在95 ~ 100分之间的
SELECT `name` AS 名字,`studentNumber` AS 分数 FROM result
WHERE studentNumber>=95 AND studentNumber<=100

-- 2.and &&
SELECT `name` AS 名字,`studentNumber` AS 分数 FROM result
WHERE studentNumber>=95 && studentNumber<=100

-- 3.模糊查询（区间）
SELECT `name` AS 名字,`studentNumber` AS 分数 FROM result
WHERE studentNumber BETWEEN 95 AND 100 

-- 1.除了20号之外学生的成绩
SELECT `id` AS 编号,`name` AS 名字,`studentNumber` AS 分数 FROM result
WHERE id != 25

-- 2.   !=  not
SELECT `id` AS 编号,`name` AS 名字,`studentNumber` AS 分数 FROM result
WHERE NOT id = 25
-- ======================= in（具体的一个或者多个值） =======================
-- 查询指定的学生 1 3 5 7号学员
SELECT `id` AS 编号,`name` AS 姓名 FROM `result`
WHERE `id` IN (1,3,5,7)

-- 查询在北京的学生
SELECT `id` AS 编号,`name` AS 姓名 FROM `result`
WHERE `Address` IN ('北京')

-- ========================== null   not null ==========================
-- 查询密码为空的学生 null ''
SELECT `id` AS 编号,`name` AS 姓名,`pwd` AS 密码  FROM `result`
WHERE `pwd` = '' OR `name` = ''

-- 查询成绩不为空的同学
SELECT `id` AS 编号,`name` AS 姓名,`pwd` AS 密码  FROM `result`
WHERE `studentNumber` IS NOT NULL

-- 查询成绩为空的同学
SELECT `id` AS 编号,`name` AS 姓名,`pwd` AS 密码  FROM `result`
WHERE `studentNumber` IS NULL

模糊查询 ： 比较运算符

运算符	语法	描述
IS NULL	a is null	如果操作符为 NULL 则结果为真
IS NOT NULL	a is not null	如果操作符不为 NULL 则结果为真
BEWEEN	a between b and c	若a 在 b 和 c 之间 则结果为真
Like	a like b	SQL 匹配 如果 a 匹配到 b 则结果为真
In	a in (a1,a2,a3,…)	假设a在a1,a2,a3…其中的某一个值中那结果为真

4.4、联表查询

JOIN 对比

-- ========================== 联表查询 join ==========================
-- 查询参加了考试的同学（学号，姓名，密码，成绩）
SELECT * FROM result
SELECT * FROM test

/*
1. 分析需求，分析查询的字段来自那张表
2. 确定使用哪种连接查询？ 7种
确定交叉点（这两个表中那个数据是相同的）
判断的条件 ： 学生表中 id = 成绩表 id
*/

-- Inner join 
SELECT s.id,s.name ,pwd,studentNumber,love
FROM result AS s
INNER JOIN wang AS r
WHERE s.id = r.id

-- Right Join
SELECT s.id,s.name ,pwd,studentNumber,love
FROM result s
RIGHT JOIN wang r
ON s.id = r.id

-- Left Join
SELECT s.id,s.name ,pwd,studentNumber,love
FROM result s
LEFT JOIN wang r
ON s.id = r.id

操作	描述
Inner join	如果表中至少有一个匹配，就返回 true
left join	会从左表中返回所有的值，即使右表中没有匹配
right join	会从右表中返回所有的值，即使左表中没有匹配

/*
连接查询
如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join
(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接 right join
(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
等值连接和非等值连接
自连接
*/
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
/*思路:
(1):分析需求,确定查询的列来源于两个类,student result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

自连接（了解）

自己的表和自己的表连接，核心：一张表拆为两张一样的表

父类

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

子类

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

操作：查询父类对应的子类

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

/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/

-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`

-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`

-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'

4.5、分页和排序

排序

-- 排序 ： 升序 ASE ， 降序 DESC
-- ORDER BY 通过那个字段排序，怎么排

-- 查询的结果根据 成绩升序 排序  
SELECT * FROM `result`
ORDER BY studentNumber ASC

-- 查询的结果根据 成绩降序 排序  
SELECT * FROM `result`
ORDER BY studentNumber DESC

分页

• – 100万条数据，不可能全部显示在网页中 所以要使用分页
• – 为什么要分页？
• – 缓解数据库压力，给人的体验更好，就像看小说一样，可以切换下一页而不是无限往下翻

-- 分页，每页只显示5条数据
-- 语法： limit 起始位置，几条数据
-- 网页应用： 当前页，总的页数，页面的大小
-- limit 0,5  1~5
-- limit 1,5  2~6
-- limit 6,5  2~6

SELECT * FROM `result`
ORDER BY studentNumber ASC
LIMIT 0,5

-- 第一页 limit 0,5		(1-1)*5
-- 第二页 limit 5,5		(2-1)*5
-- 第三页 limit 10,5	(3-1)*5
-- 第N页 limit 				(N-1)*pageSize,pageSize
-- [pageSize : 页面大小]
-- [(n-1)* pageSize起始值]
-- [n : 当前页]
-- [数据总数/页面大小 = 总页数]

语法： limit(查询起始下标，pageSize)

练习： p23

-- 思考
-- 查询学员信息(学号，姓名，课程名称，分数) 要求：查询分数要大于80且排名前十的学生信息
SELECT `id`,`name`,`sex`,`studentNumber`
FROM result
WHERE studentNumber>79
ORDER BY studentNumber DESC
LIMIT 0,10

4.6、子查询

不会

不会

不会

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分,平均分大于10
-- 核心： 根据不同的课程分组

SELECT any_value(`name`), AVG(id) AS 平均分,MAX(id),MIN(id)
FROM result r
GROUP BY r.name -- 通过什么来分组
HAVING 平均分>=10

4.8、Select 小结

5、MySQL函数

官方：(https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html)

5.1、常用但不完全常用的函数

函数	结果	说明
数学运算
ABS(-8)	8	取绝对值
CEILING(9.4)	10	向上取整
FLOOR(9.4)	9	向下取整
RAND()	0~1	返回一个0~1的随机数
SIGN(-10)	-1	判断一个数的符号 负数返回-1
SIGN(10)	1	判断一个数的符号 正数返回1
字符串函数
CHAR_LENGTH(‘王怡情’)	王怡情	字符串长度
CONCAT(‘喜’,‘欢’,‘王怡情’)	喜欢王怡情	拼接字符串
INSERT(‘我爱编程helloworld’,1,2,‘超级热爱’)	超级热爱编程	查询，替换 从位置1开始删除2个字符后拼接新字符
LOWER(‘LIKE’)	like	转小写
UPPER(‘wyq’)	WYQ	转大写
INSTR(‘xiHuanYiQing’,‘Q’)	9	返回第一次出现此符号的索引
REPLACE(‘栗哥说我想看海’,‘我’,‘想和智超一块去’)	栗哥说想和智超一块去看海	替换出现的指定字符
SUBSTR(‘我爱智超’,2,3)	爱智超	返回指定的字符串
REVERSE(‘啊智说想你’)	你想说智啊	反转
查询所有性李的名字 ，修改为王性	SELECT REPLACE(name,‘李’,‘王’) FROM 表名 +>	WHERE name LIKE ‘李%’
时间和日期函数（记住）
CURRENT_DATE()	2021-06-17	获取当前年月日
CURDATE()	2021-06-17	获取当前年月日
NOW()	2021-06-17 00:31:26	获取当前年月日时分秒
LOCALTIME()	2021-06-17 00:31:36	获取本地年月日时分秒
SYSDATE()	2021-06-17 00:31:51	获取系统年月日时分秒
YEAR(NOW())	2021	获取本地年份
MONTH(NOW())	6	获取当前月份
DAY(NOW())	17	获取当前日份
HOUR(NOW())	0	获取当前小时数
MINUTE(NOW())	34	获取当前分数
SECOND(NOW())	22	获取当前秒数
系统
SYSTEM_USER()	root@localhost	获取当前用户名和主机名
USER()	root@localhost	同 SYSTEM_USER()
VERSION()	5.7.19	版本

代码演示：

-- =================== 常用函数 ================、

-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4)  -- 向下取整
SELECT RAND()  -- 返回一个0~1的随机数
SELECT SIGN(-10)  -- 判断一个数的符号 0-0 负数返回-1，正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('王怡情') -- 字符串长度
SELECT CONCAT('喜','欢','王怡情') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，替换
SELECT LOWER('LIKE')  -- 转小写
SELECT UPPER('wyq') -- 转大写
SELECT INSTR('xiHuanYiQing','Q') -- 返回第一次出现此符号的索引
SELECT REPLACE('栗哥说我想看海','我','想和智超一块去') -- 替换出现的指定字符
SELECT SUBSTR('我爱智超',2,3) -- 返回指定的字符串 从第几个开始，截取几位 若没说指定截取几位则截取到底
SELECT REVERSE('啊智说想你') -- 反转

-- 查询性李的同学 ，修改为王性
SELECT REPLACE(`name`,'李','王') FROM result
WHERE `name` LIKE '李%' 

-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前年月日 
SELECT CURDATE() -- 获取当前年月日
SELECT NOW()   -- 获取当前年月日时分秒 
SELECT LOCALTIME()  -- 获取本地年月日时分秒
SELECT SYSDATE()   -- 获取系统年月日时分秒 
SELECT YEAR(NOW())  -- 获取本地年份
SELECT MONTH(NOW())	-- 获取当前月份
SELECT DAY(NOW())   -- 获取当前日份
SELECT HOUR(NOW())  -- 获取当前小时数
SELECT MINUTE(NOW())-- 获取当前分数
SELECT SECOND(NOW())-- 获取当前秒数

-- 系统
SELECT SYSTEM_USER() -- 获取当前用户名和主机名
SELECT USER() -- 同 SYSTEM_USER() 
SELECT VERSION() -- 版本

5.2、聚合函数（常用）

函数名称	说明
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

-- ============ 聚合函数 ============
-- 都能够统计 表中的数据（想查询表中有多少个记录，就是用COUNT()）
SELECT COUNT(name) FROM result -- Count(字段) 会忽略所有null值
SELECT COUNT(*) FROM result  -- Count(*) 不会忽略所有null值
SELECT COUNT(1) FROM result  -- Count(1) 不会忽略所有null值 本质 计算行数

SELECT SUM(`id`) AS 总和 FROM `result` 
SELECT AVG(`id`) AS 平均分 FROM `result` 
SELECT MAX(`id`) AS 最大分 FROM `result`
SELECT MIN(`id`) AS 最小分 FROM `result`

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

什么是MD5？

主要增强算法复杂度和不可逆性

MD5不可逆，具体的值的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,'zhangsan','123456'),
(2,'lisi','123456'),
(3,'wangwu','123456'),
(4,'mazi','123456')

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

UPDATE testmd5 SET pwd=MD5(pwd) WHERE id != 1

UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(5,'wangyiqing',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后匹配加密后的值
SELECT * FROM testmd5 WHERE `name`='wangyiqing' AND pwd=MD5('123456')

6、事务

6.1、什么是事务

要么都成功，要么都失败

——————

1、SQL执行 A给B转账 --> 200 B200

2、SQL执行 B收到A的钱 A800 --> 400

——————

将一组SQL放在一个批次中去执行~

事务原则：ACID原则 原子性、一致性、隔离性、持久性 （脏读，换读）

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

• 原子性（Atomicity）

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

• 一致性（Consistency）

  • 总量守恒，A B钱的总数永远不可能变

• 持久性（Durability）

  • 事务没有提交 恢复到原状
  • 事务已提交 持久化到数据库
  • 事务一旦提交就不可逆

• 隔离性（Isolation）

  • 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干务之间要相互隔离。

隔离所导致的一些问题

脏读：

指一个事务读取了另外一个事务未提交的数据。

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

执行事务

-- =========== 事务 =============

-- mysql 是默认开启事务自动提交的
SET autocommit = 0 -- 关闭
SET autocommit = 1 -- 开启(默认)

-- 手动处理事务
SET autocommit = 0 -- 关闭自动提交
-- 事务开启
START TRANSACTION  -- 标记一个事务的开始，从这个之后的slq 都在同一个事务内
INSERT xx
INSERT xx

-- 提交： 持久化（成功！）
COMMIT
-- 回滚： 回到原来的样子（失败）
ROLLBACK

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

-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销保存点

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
-- 新建表
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (`ID`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`) 
VALUES('A',2000.00),('B',10000.00)

-- 模拟转账：事务
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' -- A减500

COMMIT -- 提交事务，一旦提交就被持久化了
ROLLUP -- 回滚
SET autocommit = 1; -- 恢复默认值

7、索引

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

7.1、索引的分类

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

• 主键索引（PRIMARY KEY）
  • 唯一的标识，主键不可重复、只能有一个列作为主键
• 唯一索引（UNIQUE KEY）
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识为 唯一索引
• 常规索引（KEY / index）
  • 默认的，index ， key关键字来设置
• 全文索引（FullText）
  • 在特定的数据库引擎下才有， MyISAM
  • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM account

-- 增加一个全文索引
ALTER TABLE `result` ADD FULLTEXT INDEX `name`(`name`)

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM result  -- 非全文索引
EXPLAIN SELECT * FROM result WHERE MATCH(name) AGAINST('王怡情');

7.2、主键索引

主键 : 某一个属性组能唯一标识一条记录

特点 :

• 最常见的索引类型
• 确保数据记录的唯一性
• 确定特定数据记录在数据库中的位置

7.3、唯一索引

作用 : 避免同一个表中某数据列中的值重复

与主键索引的区别

• 主键索引只能有一个
• 唯一索引可能有多个

CREATE TABLE `Grade`(
 `GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
 `GradeName` VARCHAR(32) NOT NULL UNIQUE
 -- 或 UNIQUE KEY `GradeID` (`GradeID`)
)

7.4、常规索引

作用 : 快速定位特定数据

注意 :

• index 和 key 关键字都可以设置常规索引
• 应加在查询找条件的字段
• 不宜添加太多常规索引,影响数据的插入,删除和修改操作

CREATE TABLE `result`(
 -- 省略一些代码
 INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)

-- 创建后添加
2 ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);

7.5、全文索引

百度搜索：全文索引

作用 : 快速定位特定数据

注意 :

• 只能用于MyISAM类型的数据表
• 只能用于CHAR , VARCHAR , TEXT数据列类型
• 适合大型数据集

#方法一：创建表时
 CREATE TABLE 表名 (
 字段名1 数据类型 [完整性约束条件…],
 字段名2 数据类型 [完整性约束条件…],
 [UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY
 [索引名] (字段名[(长度)] [ASC |DESC])
 );


 #方法二：CREATE在已存在的表上创建索引
 CREATE [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 
ON 表名 (字段名[(长度)] [ASC |DESC]) ;


 #方法三：ALTER TABLE在已存在的表上创建索引
 ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX
 索引名 (字段名[(长度)] [ASC |DESC]) ;


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


 #显示索引信息: SHOW INDEX FROM student;
 */

 /*增加全文索引*/
 ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname`
(`StudentName`);

 /*EXPLAIN : 分析SQL语句执行性能*/
 EXPLAIN SELECT * FROM student WHERE studentno='1000';

 /*使用全文索引*/
 -- 全文搜索通过 MATCH() 函数完成。
 -- 搜索字符串做为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个
记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文
本之间的相似性尺度。
 EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');

 /*
 开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况

 MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
 MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
 测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。

关于 EXPLAIN ： https://blog.csdn.net/jiadajing267/article/details/81269067

7.6、测试索引（扩展）

-- 建表app_user：
CREATE TABLE `app_user` (
	`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
	`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
	`email` varchar(50) NOT NULL COMMENT '用户邮箱',
	`phone` varchar(20) DEFAULT '' COMMENT '手机号',
	`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
	`password` varchar(100) NOT NULL COMMENT '密码',
	`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
	`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
	`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'

-- 批量插入数据：100w 
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`,
`age`)
VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*
(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(),
FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();

索引效率测试

 -- 无索引
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)

-- 创建索引
CREATE INDEX idx_app_user_name ON app_user(name);

-- 测试普通索引
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

索引在小数据量的时候，用户不大，但是在大数据的时候，区别十分明显

7.7、索引原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引应该加在常用来查询的字段上~

索引的数据结构

-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

8、权限管理

8.1、用户管理

SQLyog 可视化管理

SQL命令操作

用户表：mysql.user

本质：对这张表进行增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED BY '密码'
CREATE USER wangyiqing IDENTIFIED BY '123456'

-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('123456')

-- 修改密码（修改指定用户密码）
SET PASSWORD FOR wangyiqing = PASSWORD('kekeaiai')

-- 重令名 RENAME USER 原名 TO 新名
RENAME USER wangyiqing TO 王怡情

-- 用户授权 ALL PRIVILEGES 全部的权限 , 库.表
-- ALL PRIVILEGES 除了给别人权限 其他啥都能干
GRANT ALL PRIVILEGES ON *.* TO 王怡情

-- 查询权限
SHOW GRANTS FOR 王怡情 -- 查看指定用户的权限
SHOW GRANTS FOR root @localhost  

-- ROOT用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 REVOKE 那些权限 在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM 王怡情

-- 删除用户
DROP USER wangyiqing

8.2、MySQK备份

为什么要备份：

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

MySQL数据库备份的方式

• 直接拷贝物理文件
• 在sqlyog这种可视化工具中手动导出
  • 在想要导出的表或数据库中右键，选择备份或导出
• 使用命令行导出 mysqldump 命令行使用

# mysqldump -h主机  -u 用户名 -p 密码 数据库 表名 >物理磁盘位置:文件名
mysqldump -hlocalhost -uroot -p123456 school result >E:/123.sql

# mysqldump -h主机  -u 用户名 -p 密码 数据库 表名1 表名2 表名3 >物理磁盘位置:文件名
mysqldump -hlocalhost -uroot -p123456 school result >E:/123.sql

# mysqldump -h主机  -u 用户名 -p 密码 数据库>物理磁盘位置:文件名
mysqldump -hlocalhost -uroot -p123456 school result >E:/123.sql

# 导入
# 登陆的情况下，切换到指定数据库
# source 备份文件
source d:/a.sql

mysql -u 用户名 -p 密码 库名< 备份文件

假设需要备份数据，防止数据丢失。

把数据库给朋友发过去，sql文件发给别人或存手机上即可

9、规范数据库设计（了解、选修）

9.1、为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

• 数据沉余，浪费空间
• 数据库插入和删除都会麻烦，异常【屏蔽使用物理外键】

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据库的需求
• 概要设计：设计关系图 E-R图

设计数据库的步骤：（个人博客）

• 收集信息，分析需求
  • 用户表（用户登录注销，用户的个人信息。写博客，创建分类）
  • 分类表（文章分类 ，谁创建的）
  • 文章表（文章的信息）
  • 评论表
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键的字或者一些主题） key ：value
  • 说说表（发表心情 … id… content…create_time）
• 标识十题（把需求落地到每个字段）
• 标识十题之间的关系
  • 写博客 ：user -->blog
  • 创建分类：user -->category
  • 关注：user -->user
  • 友链：links
  • 评论：user-user-blog

9.2、三大范式

为什么需要数据规范化?

不合规范的表设计会导致的问题：

• 信息重复
• 更新异常
• 插入异常
  • 无法正确表示信息
• 删除异常
  • 丢失有效信息

百度搜索：三大范式

第一范式

第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式

第二范式

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一

范式（1NF）。

第二范式要求每个表只描述一件事情

第三范式

如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.

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

规范化和性能的关系

为满足某种商业目标 , 数据库性能比规范化数据库更重要

在数据规范化的同时 , 要综合考虑数据库的性能

通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间

通过在给定的表中插入计算列,以方便查询

10、JDBC（重点）

10.1、数据库驱动

驱动：声卡、显卡

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

10.2、JDBC

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

这些规范的实现由具体的厂商去做

对于发开人员来说，我们只需要掌握jdbc接口的操作即可

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

1.创建一个普通项目

2.导入数据库驱动

3.编写测试代码

package one;

import java.sql.*;

/*
@author Draven
@data 2021/6/17 - 6:23
@V: CBWR-K
*/
// 我的第一个JDBC程序
public class JdbcFistDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法
        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);
        //4.执行SQL对象
        Statement statement = connection.createStatement();
        //5.执行SQL的对象去执行SQL 可能存在结果，查看返回结果
        String sql = "SELECT * FROM `users`";

        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("pwd="+ 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();
    }
}

步骤总结

1.加载驱动

2.链接数据库 DriverManager

3.获取执行SQL的对象 Statement

4.获得返回的结果集 ResultSet

5.释放连接

DriverManager

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

// connection 代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
	connection.rollback();
    connection.commit();
    connection.setAutoCommit();

URL

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

//mysql -- 3306
// jdbc:mysql://主机地址:端口号/数据库名？参数1&参数2&参数3

// oralce -- 1521
//jdbc:oracle:thin:@localhost:1512:sid

Statement 执行SQL的对象 PrepareSratement执行SQL对象

String sql = "SELETE * FROM users";  // 编写SQL

statement.executeQuery(); // 查询操作 返回Resultset
statement.execute();  // 执行任何SQL
statement.executeUpdate(); // 更新、插入、删除、都是用的这个，返回一个受影响行数

ResultSet

获得指定的数据类型

resultSet.getObject(); // 在不知道类型的情况下使用
// 如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
......

遍历，指针

resultSet.beforeFirst(); // 移动到最前面
resultSet.afterLast();  // 移动到最后面
resultSet.next();       //移动到写一个数据
resultSet.previous(); //移动到前一行
resultSet.absolute() // 移动到指令行

释放资源

//6.释放资源
resultSet.close();
statement.close();
connection.close(); // 好资源，用完关掉

10.4、statement对象

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

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

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

CRUD操作-create

使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

Statement st = conn.createStatement();
String sql = "insert into user(...) value(...)`";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功")
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作

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

CRUD操作-update

使用executeUpdate(String sql)方法完成数据修改操作，示例操作

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

CRUD操作-read

使用executeUpdate(String sql)方法完成数据查询操作，示例操作

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

代码实现

1.提取工具类

public class JdbcFistDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver"); // 固定写法
        //2.用户信息和url
        //useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";
        //3.连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url,username,password);

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

        //5.执行SQL的对象去执行SQL 可能存在结果，查看返回结果
        String sql = "SELECT * FROM `users`";

        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("pwd="+ 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();
    }
}

2.加载驱动

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 prop = new Properties();
            prop.load(in);

            driver = prop.getProperty("driver");
            url = prop.getProperty("url");
            username = prop.getProperty("username");
            password = prop.getProperty("password");

            //1.驱动只用加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,username,password);
    }
    //释放连接资源
    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();
            }
        }
    }
}

增

public class TestInsert {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection(); // 获取数据库连接
            st = conn.createStatement();    //获取SQL的执行对象
            String sql="INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(7,'栗志青','123456lzq','16666666@qq.com','2021-6-17')";

            int num = st.executeUpdate(sql);
            if (num>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

删

public class TestDelete {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            String sql = "delete from users where id = 4";
            st = conn.createStatement();
            int num = st.executeUpdate(sql);
            if (num>0){
                System.out.println("删除成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

改

public class TestUpdate {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            String sql = "update users set name='wang',email='1213456783@qq.com' where id = 4";
            st=conn.createStatement();
            int num = st.executeUpdate(sql);
            if (num>0){
                System.out.println("更新成功");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

查

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            String sql = "select * from users";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getInt(1));
                System.out.println(rs.getString(2));
                System.out.println(rs.getString(3));
                System.out.println(rs.getString(4));
                System.out.println(rs.getDate(5));
                System.out.println("name");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

db.properties 关键配置文件代码

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=Asia/Shanghai
username=root
password=123456

SQL 注入问题

sql存在漏洞，会被共计导致数据泄露

代码如下: 趁服务器不注意，使用假命令糊弄过校验

public class SQL注入 {
    public static void main(String[] args) {
    // 正常登陆 login("wang","123456");
        login(" ' or ' 1 = 1","'or'1=1"); // 技巧
    }
    // 登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            //          SELECT * FROM users WHERE `NAME` = 'wang' AND `PASSWORD` = '123456';
            //          SELECT * FROM users WHERE `NAME` = ` ` or ` 1=1 ` AND `PASSWORD` = ''or'1=1'
            String sql = "SELECT * FROM users WHERE `NAME` = '"+username+"' AND `PASSWORD` = '"+password+"'";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("++++++++++++++++++++++");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

10.5、PreparedStatement对象

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

增

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = JdbcUtils.getConnection();
            // 区别
            // 使用？ 占位符代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(?,?,?,?,?)";

            st = connection.prepareStatement(sql); // 预编译SQL  先写SQL 然后不执行

            // 手动赋值
            st.setInt(1,8); // id
            st.setString(2,"怡情"); // NAME
            st.setString(3,"123456"); // password
            st.setString(4,"2465941819@qq.com"); // email
            // 注意点： sql.Date    数据库 java.sql.Date()
            //        util.Date   JAVA  new java.util.Date().getTime() 获得时间戳

            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}

删

public class TestDelete {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = one.JdbcUtils.getConnection();
            // 区别
            // 使用？ 占位符代替参数
            String sql = "delete from users where id=?";

            st = connection.prepareStatement(sql); // 预编译SQL  先写SQL 然后不执行

            // 手动赋值
            st.setInt(1,3);
            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}

改

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = one.JdbcUtils.getConnection();
            // 区别
            // 使用？ 占位符代替参数
            String sql = "update users set `NAME`=? where id=?";

            st = connection.prepareStatement(sql); // 预编译SQL  先写SQL 然后不执行

            // 手动赋值
            st.setString(1,"怡情会飞");
            st.setInt(2,1);
            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,st,null);
        }
    }
}

查

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();

            String sql = "select * from users where id=?"; //表写SQL

            st = conn.prepareStatement(sql);

            st.setInt(1,1); //传递参数

            //执行
            rs = st.executeQuery();
            if (rs.next()){
                System.out.println(rs.getString("NAME"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

防止sql注入

login(" ' or ' 1 = 1","'or'1=1"); // 技巧 无法使用

练习实例

SQL注入

public class SQL注入 {
    public static void main(String[] args) {

    // 正常登陆 login("wang","123456");
        login(" ' or ' 1 = 1","'or'1=1"); // 技巧
    }
    // 登陆业务
    public static void login(String username,String password){
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try{
            conn = JdbcUtils.getConnection();
            //          SELECT * FROM users WHERE `NAME` = 'wang' AND `PASSWORD` = '123456';
            //          SELECT * FROM users WHERE `NAME` = ` ` or ` 1=1 ` AND `PASSWORD` = ''or'1=1'
            String sql = "SELECT * FROM users WHERE `NAME` = '"+username+"' AND `PASSWORD` = '"+password+"'";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(rs.getString("NAME"));
                System.out.println(rs.getString("PASSWORD"));
                System.out.println("++++++++++++++++++++++");
            }
        }catch (Exception e){
            e.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL无注入

public class SQL无注入 {
    public static void main(String[] args) {
            // 正常登陆
            login("wang","123456");
        }
        // 登陆业务
        public static void login(String username,String password){
            Connection conn = null;
            PreparedStatement st = null;
            ResultSet rs = null;
            try{
                conn = JdbcUtils.getConnection();
                // PreparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
                // 假设其中存在转义字符，  比如  引号 ' 会被直接转义
                String sql = "select * from users where `NAME`=? and `PASSWORD`=?";
                st = conn.prepareStatement(sql);
                st.setString(1,username);
                st.setString(2,password);

                rs = st.executeQuery(); // 查询完毕会返回一个结果集

                while (rs.next()){
                    System.out.println(rs.getString("NAME"));
                    System.out.println(rs.getString("PASSWORD"));
                    System.out.println("++++++++++++++++++++++");
                }
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                JdbcUtils.release(conn,st,rs);
            }
    }
}

10.6、IDEA链接数据库

注： 链接数据库必须导入此包

链接：https://pan.baidu.com/s/1mIy0QHYrNwdUkC8gi_hXYw
提取码：YQHF
复制这段内容后打开百度网盘下载 附加导入教程

10.7、jdbc操作事务

要么都成功，要么都失败

ACID 原则

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

一致性：总数不变

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

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

隔离性的问题：

脏读： 一个事务读取了另一个没有提交的事务

不可重复读：在同一个事务内，重复读取表中的数据，数据表发生了改变

虚读（幻读）：一个事务内，读取到了别人插入的数据，导致前后读出来结果不一致

代码实现

1、开启事务conn.setAutoCommit(false)

2、一组业务执行完毕，提交事务

3、可以在catch语句中显示的定义 回滚语句，但默认失败就会回滚

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

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

                String sql1 = "update wang set gradeid = gradeid-20 where name = 'A'";
                st = conn.prepareStatement(sql1);
                st.executeUpdate();

                String sql2 = "update wang set gradeid = gradeid+20 where name = 'B'";
                st = conn.prepareStatement(sql2);
                st.executeUpdate();

                // 业务完毕 提交事务
                conn.commit();
                System.out.println("成功!");

            } catch (SQLException e) {
                try {
                    conn.rollback(); // 如果失败则回滚事务
                    System.out.println("*");
                } catch (SQLException s) {
                    s.printStackTrace();
                }
            }finally {
                JdbcUtils.release(conn,st,null);
            }
    }
}

10.8、数据库连接池

数据库连接 …执行完毕…释放

连接 – 释放 十分浪费系统资源

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

… 开门 – 业务员 ： 等待 – 服务 —

常用连接数： 10个人

最小连接数：10个

最大连接数：15 业务最高承载上限

15以后排队等待

等待超时 ： 100ms 到时见自动提醒退出

编写连接池，实现一个借口 DateSource

开发数据源实现 （拿来即用）

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp-1.4 、 commons-pool-1.6

public class JdbcUtils_DBCP {
    private static DataSource dataSource = null;

    static {
        try {
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties prop = new Properties();
            prop.load(in);

            //创建数据源  工厂模式 --》 创建对象
            dataSource = BasicDataSourceFactory.createDataSource(prop);


        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();  // 从数据源中获取连接
    }
    //释放连接资源
    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();
            }
        }
    }
}

public class TestDBCP {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        try {
            conn = commons.JdbcUtils_DBCP.getConnection(); // 获取数据库连接

            String sql="INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
            "VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);    //获取SQL的执行对象

            //手动赋值
            st.setInt(1,9); // id
            st.setString(2,"栗志青"); // NAME
            st.setString(3,"123456"); // password
            st.setString(4,"2465941819@qq.com"); // email
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));

            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            commons.JdbcUtils_DBCP.release(conn,st,null);
        }
    }
}

C3P0

需要用到的jar包

c3p0-0.9.5.5.jar 、 mchange-commons-java-0.2.19.jar

public class TestC3P0 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement st = null;
        try {
            connection = JdbcUtils_C3P0.getConnection(); // 原来是别人实现的，现在使用别人实现的
            // 区别
            // 使用？ 占位符代替参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(?,?,?,?,?)";
            st = connection.prepareStatement(sql); // 预编译SQL  先写SQL 然后不执行
            // 手动赋值
            st.setInt(1,10); // id
            st.setString(2,"kzc"); // NAME
            st.setString(3,"123456"); // password
            st.setString(4,"2465941819@qq.com"); // email
            st.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            // 执行
            int i = st.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(connection,st,null);
        }
    }
}

public class JdbcUtils_C3P0 {
    private static DataSource dataSource = null;

    static {
        try {
            //创建数据源  工厂模式 --》 创建对象
            dataSource = new ComboPooledDataSource("MySQL"); // 配置文件写法
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();  // 从数据源中获取连接
    }
    //释放连接资源
    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();
            }
        }
    }
}

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>


        <c3p0-config>
        <!--
        c3p0的缺省（默认）配置
        如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）-->
        <default-config>

        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>

            <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true\&amp;\characterEncoding=utf8\&amp;\useSSL=true\&amp;\vserverTimezone=UTC\&amp;\</property>
            <property name="user">root</property>
            <property name="passwordroot">123456</property>

            <property name="acquiredIncrement5">5</property>
            <property name="initialPoolSize10">10</property>
            <property name="minPoolSize5">5</property>
            <property name="maxPoolSize20">20</property>
        </default-config>

    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true\&amp;\characterEncoding=utf8\&amp;\useSSL=true\&amp;\vserverTimezone=UTC\&amp;\</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement5">5</property>
        <property name="initialPoolSize10">10</property>
        <property name="minPoolSize5">5</property>
        <property name="maxPoolSize20">20</property>
    </named-config>
        </c3p0-config>
             System.out.println("插入成功");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_C3P0.release(connection,st,null);
        }
    }
}

public class JdbcUtils_C3P0 {
    private static DataSource dataSource = null;

    static {
        try {
            //创建数据源  工厂模式 --》 创建对象
            dataSource = new ComboPooledDataSource("MySQL"); // 配置文件写法
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();  // 从数据源中获取连接
    }
    //释放连接资源
    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();
            }
        }
    }
}

c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>


        <c3p0-config>
        <!--
        c3p0的缺省（默认）配置
        如果在代码中ComboPooledDataSource ds=new ComboPooledDataSource();这样写就表示使用的是c3p0的缺省（默认）-->
        <default-config>

        <property name="driverClass">com.mysql.cj.jdbc.Driver</property>

            <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true\&amp;\characterEncoding=utf8\&amp;\useSSL=true\&amp;\vserverTimezone=UTC\&amp;\</property>
            <property name="user">root</property>
            <property name="passwordroot">123456</property>

            <property name="acquiredIncrement5">5</property>
            <property name="initialPoolSize10">10</property>
            <property name="minPoolSize5">5</property>
            <property name="maxPoolSize20">20</property>
        </default-config>

    <named-config name="MySQL">
        <property name="driverClass">com.mysql.jdbc.Driver</property>
        <property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true\&amp;\characterEncoding=utf8\&amp;\useSSL=true\&amp;\vserverTimezone=UTC\&amp;\</property>
        <property name="user">root</property>
        <property name="password">123456</property>

        <property name="acquiredIncrement5">5</property>
        <property name="initialPoolSize10">10</property>
        <property name="minPoolSize5">5</property>
        <property name="maxPoolSize20">20</property>
    </named-config>
        </c3p0-config>