MySQL

2023-08-09 12:45:16

MySQL

狂神说java视频

1、初始MySQL

1.1、用途

javaEE：企业级java开发 Web
前端（页面：展示，数据！）
后台（连接点：连接数据库 JDBC，连接前端（控制，控制视图跳转，和给前端传递数据））
数据库（存数据，Txt，Excel，word）

1.2、什么是数据库

DataBase
数据仓库，软件，安装在操作系统上，存储大量数据
作用：存储数据，管理数据
数据库是所有软件体系中最核心的存在 DBA

1.3、数据库分类

关系型数据库（SQL）
- MySQL
- Oracle
- SQL Server
- DB2
- SQLlite
- 通过表与表之间，行和列之间的关系进行数据的存储
非关系型数据库（NoSQL）
- Redis
- MongDB
- 非关系型数据库：对象存储，通过对象的自身的属性来决定
  ng)

1.4、DBMS（数据库管理系统）

数据库的管理软件，科学有效的管理我们的数据，维护和获取数据

1.5、MySQL简介

MySQL百度百科

MySQL最新版本安装下载地址

MySQL旧版本安装下载地址

1.6、安装MySQL

配置文件ini

# mysql配置文件ini
[mysqld]
# 目录一定要换成自己的 最后的 "\"也要加上
basedir=D:\Environment\mysql-5.7.19\
# data目录在D:\Environment\mysql-5.7.19\下找不到，不要自己新建，配置文件写好了，到时候自动生成。否则初始化的时候可能会失败。
datadir=D:\Environment\mysql-5.7.19\data\
port=3306
# 跳过密码验证
skip-grant-tables

注意：

第5步的时候一定要管理员模式下启动cmd
第七步的时候 -u 后面可以空格再接root，也可以不用空格直接后面就是root，但是-p后面一定不能有空格，-p后面直接接着输入密码，或者-p之后直接回车，然后出现要输入密码，输入密码即可，输入的密码是不显示的，所以一定要注意写对密码。

1.7、数据库 XXX语言

2、操作数据库

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

MySQL关键字不区分大小写

2.1、操作数据库

2.2、数据库的列类型

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

拓展：

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

-- 创建一个school数据库
create database if not exists `school`;
-- 使用数据库
use `school`;
-- 创建学生表
drop table if exists `student`;
create table `student`(
	`studentno` int(4) not null comment '学号',
    `loginpwd` varchar(20) default null,
    `studentname` varchar(20) default null comment '学生姓名',
    `sex` tinyint(1) default null comment '性别，0或1',
    `gradeid` int(11) default null comment '年级编号',
    `phone` varchar(50) not null comment '联系电话，允许为空',
    `address` varchar(255) not null comment '地址，允许为空',
    `borndate` datetime default null comment '出生时间',
    `email` varchar (50) not null comment '邮箱账号允许为空',
    `identitycard` varchar(18) default null comment '身份证号',
    primary key (`studentno`),
    unique key `identitycard`(`identitycard`),
    key `email` (`email`)
)engine=myisam default charset=utf8;

2.5、数据表的类型

在物理控件存在的位置

设置数据库表的字符编码

2.6、修改、删除数据库表

3、MySQL数据管理

3.1、外键（了解即可）

不足：删除有外键关系的表的时候，必须要先删除引用别人的表（从表），再删除被引用的表（主表*）

注意：以上的操作都是物理外键，数据库级别的外键，我们不建议使用！（避免数据库过多造成困扰）

3.2、DML语言（全部记住）

数据库意义：数据存储，数据管理
DML语言：数据操作语言

insert
update
delete

3.3、添加

insert

注意事项：

字段和字段之间使用英文逗号隔开
字段是可以省略的，但是后面的值必须要一一对应，不能少
可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可VALUES(),()…

3.4、修改

3.5、删除

4、DQL查询数据（重点）

4.1、DQL

4.2、指定查询字段

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

-- 查询指点字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果起一个名字 AS 可以给字段起别名，也可以给表起别名
SELECT `studentno` as 学号,`studentname` as 学生姓名 FROM student as s

-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',studentname) as 新名字 FROM student

SELECT * FROM student

SELECT `studentno`,`studentname` FROM student

SELECT `studentno` as 学号,`studentname` as 学生姓名 FROM student as s

SELECT CONCAT('姓名：',studentname) as 新名字 FROM student

去重 distinct

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

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

数据库的列（表达式）

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

-- 学员考试成绩 + 1 分查看
SELECT `studentno`,`studentresult`+1 as '提分后' FROM result

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

select 表达式 from 表

4.3、Where条件字句

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

-- ================================ where ======================================

SELECT studentno,`studentresult` FROM result

-- 查询考试成绩再 95-100 分之间
-- and 

SELECT studentno,`studentresult` FROM result WHERE studentresult>=95 AND studentresult<=100
-- &&
SELECT studentno,`studentresult` FROM result WHERE studentresult>=95 && studentresult<=100


-- 模糊查询（区间） BETWEEN  AND
SELECT studentno,`studentresult` FROM result WHERE studentresult BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT studentno,`studentresult` FROM result WHERE studentno!=1000;

-- != not
SELECT studentno,`studentresult` FROM result WHERE not studentno=1000;

测试：

-- ============================= 模糊查询 ================================
-- 查询姓刘的同学
-- like结合 %（代表0到任意个字符）  _（一个字符）
SELECT `studentno`,`studentname` FROM `student` WHERE studentname like '刘%';

-- 查询姓刘的同学，名字后面只有一个字的
SELECT `studentno`,`studentname` FROM `student` WHERE studentname like '刘_';

-- 查询姓刘的同学，名字后面只有两个字的
SELECT `studentno`,`studentname` FROM `student` WHERE studentname like '刘__';

-- 查询名字中有嘉字的同学 %嘉%
SELECT `studentno`,`studentname` FROM `student` WHERE studentname like '%嘉%';

-- ======================== in(具体的一个或者多个值） ==============================
-- 查询 1001 1002 1003 号学员
SELECT `studentno`,`studentname` FROM `student` WHERE studentno in (1001,1002,1003);

-- 查询在北京的学生
SELECT `studentno`,`studentname` FROM `student` WHERE `address` in ('北京朝阳');


-- ========================null not null ==============================
-- 查询地址为空的学生 null ''
SELECT `studentno`,`studentname` FROM `student` WHERE `address`='' or address is null;

-- 查询有出生日期的同学 不为空
SELECT `studentno`,`studentname` FROM `student` WHERE `borndate`is not null;

-- 查询没有出生日期的同学 为空
SELECT `studentno`,`studentname` FROM `student` WHERE `borndate` is  null;

4.4、联表查询

join

join对比

-- ============================= 联表查询 join ==============================

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
/*思路：
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定使用哪些连接查询？7种
3.确定交叉点（这两个表中哪个数据是相同的）
4.判断的条件：学生表中的 studentno=成绩表中的 studentno
*/
-- INNER Join
SELECT s.studentno,studentname,subjectno,studentresult FROM student as s INNER JOIN result as r 
WHERE s.studentno =r.studentno

-- on 和 where的区别：on是先筛选后关联，where是先关联后筛选。on用于批量，where用于单个
-- join(连接的表) on(判断的条件) 连接查询
-- where   等值查询

-- Right Join
SELECT s.studentno,studentname,subjectno,studentresult FROM student  s RIGHT JOIN result  r 
on s.studentno =r.studentno


-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult FROM student  s LEFT JOIN result  r 
on s.studentno =r.studentno

student表数据：

result表数据：

-- INNER Join
SELECT s.studentno,studentname,subjectno,studentresult FROM student as s INNER JOIN result as r 
WHERE s.studentno =r.studentno

测试结果：

-- Right Join
SELECT s.studentno,studentname,subjectno,studentresult FROM student  s RIGHT JOIN result  r 
on s.studentno =r.studentno

测试结果：

-- Left Join
SELECT s.studentno,studentname,subjectno,studentresult FROM student  s LEFT JOIN result  r 
on s.studentno =r.studentno

测试结果：

-- 查询缺考的同学
SELECT s.studentno,studentname,subjectno,studentresult FROM student  s LEFT JOIN result  r 
on s.studentno =r.studentno
WHERE studentresult is null;

测试结果：

-- 思考题（查询了参加考试的同学信息：学号，学生姓名，科目名，分数）
/*思路：
1.分析需求，分析查询的字段来自哪些表，student,result,`subject`（连接查询）
2.确定使用哪些连接查询？7种
3.确定交叉点（这两个表中哪个数据是相同的）
4.判断的条件：学生表中的 studentno=成绩表中的 studentno
*/

SELECT s.studentno,studentname,subjectname,studentresult 
FROM student  s 
RIGHT JOIN result  r 
on s.studentno =r.studentno
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
-- 我要查询哪些数据 select ...
-- 从那几个表中查FROM 表 XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
-- FROM a LEFT JOIN b
-- FROM a RIGHT JOIN b

subject表数据：

测试结果：

自连接

自连接：

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

category表数据：

-- 查询category表中父子信息
SELECT a.`categoryname` as '父栏目',b.`categoryname` as '子栏目'
FROM `category` as a,`category` as b
WHERE a.categoryid=b.pid

测试结果：

-- 查询学员所属的年级（学号，学生的姓名，年级名称）
SELECT studentno,studentname,gradename
FROM student s
INNER Join grade g
on s.gradeid=g.gradeid

-- 查询科目所属的年级（科目名称，年级名称)
SELECT subjectname,gradename
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 s.studentno=r.studentno
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'

4.5、分页和排序

分页

-- ============================= 分页 limit 和排序 order by ===============================
-- 排序：升序 ASC，降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩降序排序
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult ASC

排序

-- ========================== 排序 ORDER BY ====================
-- 100万
-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好，瀑布流
-- 分页，每页只显示2条数据
-- 语法：limit 起始值，页面大小
-- 网页应用：当前，总的页数，页面的大小
-- LIMIT 0,2 1-2
-- LIMIT 1,2 2-3
-- LIMIT 3,5 
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult ASC
LIMIT 2,5

语法：limit（查询起始下标，pageSize）

-- 思考：
-- 查询JAVA第一学年 课程成绩排名前十的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）
SELECT s.studentno,studentname,subjectname,studentresult
FROM student s
INNER JOIN result r
on s.studentno=r.studentno
INNER JOIN subject sub
on r.subjectno=sub.subjectno
WHERE subjectname='Java第一学年' AND r.studentresult>=80
ORDER BY studentresult DESC
LIMIT 0,10

4.6、子查询

subject表的数据：

result表数据：

SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno = (
      SELECT subjectno FROM `subject`
      WHERE subjectname='数据库结构-1'
)
-- 错误：
-- Subquery returns more than 1 row
-- 解决方法：
-- 1）如果是写入重复，去掉重复数据。然后写入的时候，可以加逻辑判断(php)或者外键(mysql)，防止数据重复写入。
--    （我实际开发中遇到的就是数据重复写入的情况，在数据库查到有相同的数据两条，这不符原本的原本的业务需求）
-- 2）在子查询条件语句加limit 1,找到一个符合条件的就可以了（只是找到子查询中的一个符合条件，但是符合该条件的数据可能在result表中不存在，因此该方法可能查出数据为空）
SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno = (
      SELECT subjectno FROM `subject`
      WHERE subjectname='数据库结构-1'
			LIMIT 1
)
-- 错误：没有数据显示。将=改为IN
SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno IN (
      SELECT subjectno FROM `subject`
      WHERE subjectname='数据库结构-1'
			LIMIT 1
) 
-- 错误：This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'  改为如下，子查询多嵌套一层
SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno IN (
      SELECT sub.subjectno FROM (
			      SELECT * FROM `subject`
            WHERE subjectname='数据库结构-1'
		      	LIMIT 1
						)as sub
)
-- 错误：也是没有数据显示，原因是limit 1使得当找到一条subjectname='数据库结构-1'时就返回subjectno，但是这个编号在result表中找不到对应的，所有显示数据为空。正确的做法应该是第三中，在子查询前加any关键字。还可以只要in不要limit，如下：
SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno IN (
     SELECT subjectno FROM `subject`
      WHERE subjectname='数据库结构-1'
)
-- 3）在子查询前加any关键字
  select * from table1 where table1.colums=any(select columns from table2);

练习

-- ============================= 子查询练习1 ==============================
-- 1、查询数据库结构-1的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT studentno,r.subjectno,studentresult
FROM result r
INNER JOIN `subject` sub
on r.subjectno=sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY studentresult DESC

-- 方式二：使用子查询（由里及外）
-- 查询所有数据库结构-1 的学生学号
SELECT studentno,subjectno,studentresult
FROM result 
WHERE subjectno in(
    SELECT subjectno FROM `subject`WHERE subjectname='数据库结构-1'
		      	
)
ORDER BY studentresult DESC

-- =============================== 子查询练习2 ============================
-- 2.分数不小于80分的学生的学号和姓名
-- 在这个基础上增加一个科目，高等数学-2
-- 查询高等数学-2的编号
-- 方式一：
SELECT s.studentno,studentname
FROM student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON sub.subjectno=r.subjectno
WHERE r.studentresult>=80 AND sub.subjectname='高等数学-2'

-- 方式二：
-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE studentresult >=80

-- 在这个基础上增加一个科目，高等数学-2
-- 查询高等数学-2的编号
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno=s.studentno
WHERE studentresult >=80 AND subjectno IN(
      SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2'
)

-- 方式三：（由里及外）
SELECT studentno,studentname FROM student WHERE studentno IN (
			 SELECT studentno FROM result WHERE studentresult >=80 AND subjectno =ANY(
			        SELECT subjectno FROM `subject` WHERE subjectname='高等数学-2' 
							)
)

-- ============================== 子查询练习三 =====================================
-- 查询C语言-1 前5名同学的成绩的信息（学号，姓名，分数）
-- 方式一：
SELECT s.studentno,studentname,studentresult
FROM  student s
INNER JOIN result r
ON s.studentno=r.studentno
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
WHERE sub.subjectname='C语言-1'
ORDER BY studentresult DESC
LIMIT 0,5
-- 方式二：
SELECT s.studentno,studentname,studentresult
FROM  student s
INNER JOIN result r
ON s.studentno=r.studentno
WHERE subjectno = ANY(
      SELECT subjectno FROM `subject` WHERE subjectname='C语言-1'
)
ORDER BY studentresult DESC

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT subjectname, AVG(studentresult) as 平均分,MAX(studentresult) as 最高分,MIN(studentresult) as 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>80

4.8、Select小结

5、MySQL函数

官网：http://www.searchdoc.cn/rdbms/mysql/dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.com.coder114.cn.html

5.1、常用函数

-- =============================== 常用函数 ============================
-- 数学运算
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('KuangShen') -- 小写字母
SELECT UPPER('KuangShen') -- 大写字母
SELECT INSERT('kuangshen','h') -- 返回第一次出现的子串的索引
SELECT REPLACE('狂神说坚持就能成功','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('狂神说坚持就能成功',4,6) -- 返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('清晨我上马') -- 反转
-- 查询姓 周的同学，名字，刘
SELECT REPLACE(studentname,'刘','周')FROM student
WHERE studentname LIKE '刘%'

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

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.2、聚合函数（常用）

-- =============================== 聚合函数 ============================
-- 都能够统计 表中的数据（想查询一个表中有多少个记录，就使用这个count()）
SELECT COUNT(identitycard) FROM student; -- Count（指定列），会忽略所有的null值
SELECT COUNT(*) FROM `subject`; -- Count（*）,不会忽略null值,本质，计算行数
SELECT COUNT(1) FROM `subject`; -- Count（1）,不会忽略所有的null值
SELECT SUM(studentresult) as 总和 FROM result
SELECT AVG(studentresult) as 平均分 FROM result
SELECT MAX(studentresult) as 最高分 FROM result
SELECT MIN(studentresult) as 最低分 FROM result

-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
SELECT subjectname, AVG(studentresult) as 平均分,MAX(studentresult) as 最高分,MIN(studentresult) as 最低分
FROM result r
INNER JOIN `subject` sub
ON r.subjectno=sub.subjectno
GROUP BY r.subjectno -- 通过什么字段来分组
HAVING 平均分>80

5.3、数据库级别的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','12345')
-- 查询
SELECT * FROM testmd5
-- 加密指定id
UPDATE testmd5 set pwd=MD5(pwd) WHERE id=1
-- 加密全部
UPDATE testmd5 set pwd=MD5(pwd)
-- 插入的时候加密
INSERT INTO testmd5 VALUES (4,'XIAOMING',MD5('123456'))
-- 如何校验：将用户传递进来的密码，进行md5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='XIAOMING' AND pwd=MD5('123456')

6、事务

6.1、什么是事务

要么都成功，要么都失败

1、SQL执行 A给B转账 A 1000 —》200 B200

2、SQL执行 B收到A的钱 A 800 —-》 B400

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

事务原则：ACID原则

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

事务ACID理解

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

-- mysql是默认开启事务自动提交的
SET autocommit = 0 /* 关闭 */
SET autocommit = 1 /* 开启（默认的） */

-- 手动处理事务


-- 事务开启
SELECT TRANSACTION -- 标记一个贰拾伍的开始，从这个之后的sql都在同一个事务内

INSERT xx
INSERT xx

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

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

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

模拟场景

-- ============================= 模拟场景 ====================================
-- 转账
CREATE DATABASE shop_qinjiang CHARACTER SET utf8 COLLATE utf8_general_ci
use shop_qinjiang
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)

SELECT * FROM `account`

-- 模拟转账：事务
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; -- 提交事务，就被持久化了，再回滚也没用了
ROLLBACK; -- 回滚

SET autocommit =1;-- 恢复默认值

7、索引

MySQL索引背后的数据结构及算法原理

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1、索引的分类

唯一索引：唯一表示的是字段唯一，比如个人账户不能重复。不允许索引的字段具有相同的索引值。在一个列中避免有重复的数据。

基础语法

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

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

-- 增加一个索引（索引名）（列名）
ALTER TABLE school_qinjiang.student  ADD FULLTEXT INDEX studentname(studentname)

-- EXPLAIN 分析sql执行的状况
EXPLAIN SELECT * FROM student  -- 非全文索引

SELECT * FROM student WHERE MATCH(studentname) AGAINST('刘')

【MySQL优化】——看懂explain

7.2、测试索引

use school_qinjiang
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'

-- 插入一百万条数据
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`='用户99999';
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户99999';
SELECT * FROM student

-- 给表创建普通索引，创建索引之后查询用户时注意对比没有加入索引之前的用时
-- id_表名_字段名
-- CREATE INDEX 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
-- 查询用户
SELECT * FROM app_user WHERE `name`='用户99999';
EXPLAIN SELECT * FROM app_user WHERE `name` ='用户99999';

-- 总结：索引本质其实就是去找对应的ID，没加索引前是遍历

EXPLAIN SELECT * FROM app_user WHERE `name` ='用户99999';
-- 没有加索引前执行代码结果如下：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-onMcHuN2-1615651477819)(D:\笔记\java\狂神说java\MySQL\MySQL.assets\image-20210217171431484.png)]

EXPLAIN SELECT * FROM app_user WHERE `name` ='用户99999';
-- 加索引后执行代码结果如下：

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

7.3、索引原则

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

索引的数据结构

Hash类型的索引

Btree：InnoDB默认数据结构

MySQL索引背后的数据结构及算法原理

8、权限管理和数据库备份

8.1、权限管理

SQL命令操作

用户表：mysql.user

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

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

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

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

-- 重命名  RENAME USER 旧名字 TO 新名字
RENAME USER kuangshen TO kuangshen2


-- 用户授权 ALL PRIVILEGES 全部权限，库，表
-- ALL PRIVILEGES 除了给被人授权，其它都可以
GRANT ALL PRIVILEGES ON *.* TO kuangshen

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

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

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

-- 删除用户
DROP USER kuangshen

8.2、MYSQL备份

为什么要备份：

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

MySQL数据库备份的方式

直接拷贝物理文件
在可视化工具中可以手动导出
使用命令导出 mysqldump 命令行使用

-- 导出单个文件
-- mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
 mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
 
 -- 导出多个文件
 -- mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名
  mysqldump -hlocalhost -uroot -p123456 school student >D:/b.sql
  
  -- 导出数据库
   -- mysqldump -h主机 -u用户名 -p密码 数据库>物理磁盘位置/文件名
  mysqldump -hlocalhost -uroot -p123456 school>D:/c.sql
  
  -- 导入
  -- 连接用户
  mysql -uroot -p123456
  -- 使用数据库
  use school
  -- 导入a.sql文件；登录的情况下，切换到指定的数据库
  source d:/a.sql
  -- 没有登录的情况下
  mysql -u用户名 -p密码 库名<备份文件

9、规范数据库设计

9.1、为什么需要设计

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

9.2、三大范式

三大范式通俗理解

10、JDBC（重点）

10.1、数据库驱动

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

10.2、JDBC

java.sql

javax.sql

还需要导入一个数据库驱动包：mysql-connector-java-5.1.47.jar

数据库驱动包可在maven官网下载：https://mvnrepository.com/

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,'zhangsan','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 cn.dxj1016.lesson01;

import java.sql.*;

//我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动
//        2、用户信息和url
//        useUnicode=true:中文；characterEncoding=utf8：编码；useSSL=trueuseUnicode=true：SSL连接；
//        jdbc:mysql://localhost:3306/mydb3?serverTimezone=GMT%2B8&useSSL=false
//        jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false";
        String username = "root";
        String password = "980413dxj";
//        3、连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

//        4、执行SQL的对象  Statement 执行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("PASSWORD="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
            System.out.println("=====================================================");
        }

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

错误：Exception in thread “main” com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

将useSSL=true改为false就可以了。

![image-20210218155421072](https://www.icode9.com/i/ll/?i=img_convert/0ebd9b743364758592d5e3afcc02c193.png

10.4、statement对象

代码实现

1、工具类编写

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8&useSSL=false
username=root
password=980413dxj

package cn.dxj1016.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

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();
        }
    }
//    获取连接
    public static Connection getConnenction() 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();
            }
        }
    }
}

2、编写增删改成的方法，executeUpdate

package cn.dxj1016.lesson02;
import cn.dxj1016.lesson02.utils.JdbcUtils;
import java.sql.*;
//增
public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
            statement = connection.createStatement();//获得SQL的执行对象
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,email,birthday)" +
                    "VALUES (5,'kuagnshen','123456','11111111@qq.com','2020-01-01')";
            int i = statement.executeUpdate(sql);
            if (i > 0) {
                System.out.println("插入成功");

            }

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

    }
}

package cn.dxj1016.lesson02;
import cn.dxj1016.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//删
public class TestDelect {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
            statement = connection.createStatement();//获得SQL的执行对象
            String sql = "DELETE FROM users WHERE id=4;";
            int i = statement.executeUpdate(sql);
            if (i > 0) {
                System.out.println("删除成功");

            }

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

    }
}

package cn.dxj1016.lesson02;
import cn.dxj1016.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//查
public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
            statement = connection.createStatement();//获得SQL的执行对象
            String sql = "UPDATE users SET `NAME`='dxj1016' WHERE id=5";
            int i = statement.executeUpdate(sql);
            if (i > 0) {
                System.out.println("修改成功");

            }

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

    }
}

3、查询 executeQuery

package cn.dxj1016.lesson02;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
            statement = connection.createStatement();//获得SQL的执行对象
            String sql = "SELECT * FROM users ";
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));
            }

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

10.5、SQL注入

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

sql注入基础原理（超详细

SQL注入百度百科

package cn.dxj1016.lesson02;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SQL注入 {
    public static void main(String[] args) {
//        login("dxj1016","123456");
        login(" 'or '1=1"," 'or '1=1");

    }

    //    登录业务
    public static void login(String username, String password) {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
            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(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
                System.out.println("=============================================");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}
/*
执行结果：
zhangsan
123456
=============================================
lisi
123456
=============================================
wangwu
123456
=============================================
dxj1016
123456
=============================================
 */

10.6、PreparedStatement对象

PreparedStatement对象防止数据泄露

新增

package cn.dxj1016.lesson03;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
//            区别
//            使用？代替占位符参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,email,birthday) values (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
//            手动给参数赋值
            preparedStatement.setInt(1, 4);
            preparedStatement.setString(2, "qinjiang");
            preparedStatement.setString(3, "123456");
            preparedStatement.setString(4, "123344@qq.com");
//            注意点：sql.Date 数据库      java.sql.Date()
//                    util.Date Java     new Date().getTime() 获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
//            执行
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}

删除

package cn.dxj1016.lesson03;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestDelect {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
//            区别
//            使用？代替占位符参数
            String sql = "DELETE FROM users WHERE id=?;";
            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
//            手动给参数赋值
            preparedStatement.setInt(1, 4);
//            执行
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("删除成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

更新

package cn.dxj1016.lesson03;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
//            区别
//            使用？代替占位符参数
            String sql = "UPDATE users SET `NAME`=?,`PASSWORD`=? WHERE id=?";
            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
//            手动给参数赋值

            preparedStatement.setString(1, "qinjiang");
            preparedStatement.setString(2, "5678807");
            preparedStatement.setInt(3, 5);
//            执行
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("修改成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

查询

package cn.dxj1016.lesson03;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.*;

public class TestSelect {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
            String sql = "SELECT * FROM users where id=?";
            preparedStatement = connection.prepareStatement(sql);//获得SQL的执行对象
            preparedStatement.setInt(1,1);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));
            }

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

防止SQL注入

package cn.dxj1016.lesson03;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {
        //login("zhangsan","123456");
        login("'' or 1=1","123456");

    }

    //    登录业务
    public static void login(String username, String password) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = JdbcUtils.getConnenction();//获取数据库连接
//            preparedStatement 防止SQL注入的本质，把传递进来的参数当做字符
//            假设其中存在转义字符，比如说 ' 会被直接转义
            String sql = "SELECT * FROM users WHERE `NAME`=? AND `PASSWORD`=?";//Mybatis
            preparedStatement = connection.prepareStatement(sql);//获得SQL的执行对象
            preparedStatement.setString(1, username);
            preparedStatement.setString(2, password);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
                System.out.println("=============================================");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}
/*
执行结果：

 */

10.7使用IDEA连接数据库

MySQL
MySQL

10.8、事务

package cn.dxj1016.lesson04;

import cn.dxj1016.lesson02.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

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

        try {
            connection = JdbcUtils.getConnenction();
//            关闭自动提交事务
            connection.setAutoCommit(false);
            String sql1 = "UPDATE  account SET money=money-100 WHERE `NAME`='A';";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            int x = 1 / 0;//报错，回滚

            String sql2 = "UPDATE  account SET money=money+100 WHERE `NAME`='B';";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();
//            业务完毕，提交事务
            connection.commit();
            System.out.println("成功");

        } catch (Exception e) {
            try {
                connection.rollback();
                System.out.println("捕获到异常，回滚");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}
/*
执行结果：
捕获到异常，回滚
java.lang.ArithmeticException: / by zero
	at cn.dxj1016.lesson04.TestTransaction2.main(TestTransaction2.java:22)
 */

10.9、数据库连接池

DBCP连接池

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=980413dxj

#<!-- 初始化连接 -->
initialSize=10

#最大连接数量
maxActive=50

#<!-- 最大空闲连接 -->
maxIdle=20

#<!-- 最小空闲连接 -->
minIdle=5

#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8

#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true

#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=

#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED

package cn.dxj1016.lesson05.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP {
    public static BasicDataSource basicDataSource = null;

    static{
        try {
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);
//              创建数据源  工厂模式--》创建
            basicDataSource = BasicDataSourceFactory.createDataSource(properties);

        } catch (Exception e) {
            e.printStackTrace();
        }
    }
//    获取连接
    public static Connection getConnection() throws SQLException {
        return basicDataSource.getConnection();
    }
    //    释放资源连接
    public static void release(Connection conn, Statement st, ResultSet rs) {
        if (rs != null) {
            try {
                rs.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (st != null) {
            try {
                st.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}

package cn.dxj1016.lesson05;
import cn.dxj1016.lesson05.utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;

public class TestDBCP {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        try {
            connection = JdbcUtils_DBCP.getConnection();//获取数据库连接
//            区别
//            使用？代替占位符参数
            String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,email,birthday) values (?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写SQL，然后不执行
//            手动给参数赋值
            preparedStatement.setInt(1, 4);
            preparedStatement.setString(2, "qinjiang");
            preparedStatement.setString(3, "123456");
            preparedStatement.setString(4, "123344@qq.com");
//            注意点：sql.Date 数据库      java.sql.Date()
//                    util.Date Java     new Date().getTime() 获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
//            执行
            int i = preparedStatement.executeUpdate();
            if (i > 0) {
                System.out.println("插入成功");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
             JdbcUtils_DBCP.release(connection, preparedStatement, null);
        }

    }
}
/*
错误：Exception in thread "main" java.lang.NoClassDefFoundError: Could not initialize class cn.dxj1016.lesson05.utils.JdbcUtils_DBCP
	at cn.dxj1016.lesson05.TestDBCP.main(TestDBCP.java:37)
未解决
*/

11、遇到问题

执行 mysql -u root -p 123456出错：
提示
ERROR 2003 (HY000): Can’t connect to MySQL server on ‘localhost’ (10061)
在计算机管理->服务和应用程序->服务->Mysql 中发现MySQL启动之后总是马上停止
解决方法：在配置文件my.ini中注释掉 #skip-grant-tables
Navicat for MySQL在连接数据库时出现1045-Access denied for user ‘root’@‘localhost’(using password:YES)问题？
解决方法： https://www.huaweicloud.com/articles/69a429d8789d1c0fcb12263f7e6bc390.html
IDEA连接数据库出错解决方法：https://blog.csdn.net/gao_jun1/article/details/108213670
JDBC连接数据库时出错：
1. 使用老版5.1 ,如果报错 ,改成Url后面那里改成useSSL=false;
2. 使用8.0新版,加载驱动那改成Class.forName(“com.mysql.cj.jdbc.Driver”); ,然后url后面加一串&serverTimezone=Asia/Shanghai

码农公寓

MySQL

1、初始MySQL

1.1、用途

1.2、什么是数据库

1.3、数据库分类

1.4、DBMS（数据库管理系统）

1.5、MySQL简介

1.6、安装MySQL

1.7、数据库 XXX语言

2、操作数据库

2.1、操作数据库

2.2、数据库的列类型

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

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

2.5、数据表的类型

2.6、修改、删除数据库表

3、MySQL数据管理

3.1、外键（了解即可）

3.2、DML语言（全部记住）

3.3、添加

3.4、修改

3.5、删除

4、DQL查询数据（重点）

4.1、DQL

4.2、指定查询字段

4.3、Where条件字句

4.4、联表查询

join

自连接

4.5、分页和排序

分页

排序

4.6、子查询

练习

4.7、分组和过滤

4.8、Select小结

5、MySQL函数

5.1、常用函数

5.2、聚合函数（常用）

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

6、事务

6.1、什么是事务

事务原则：ACID原则

模拟场景

7、索引

7.1、索引的分类

7.2、测试索引

7.3、索引原则

8、权限管理和数据库备份

8.1、权限管理

8.2、MYSQL备份

9、规范数据库设计

9.1、为什么需要设计

9.2、三大范式

10、JDBC（重点）

10.1、数据库驱动

10.2、JDBC

10.3、第一个JDBC程序

10.4、statement对象

10.5、SQL注入

10.6、PreparedStatement对象

10.7使用IDEA连接数据库

10.8、事务

10.9、数据库连接池

11、遇到问题

相关文章