本文介绍了MySQL的学习路径，包括数据库概念、创建与连接、数据管理、DQL/DML查询、事务处理、索引与权限、备份与设计规范，以及JDBC应用和最佳实践。
一日一句：最怕你一生碌碌无为，还安慰自己平凡可贵
1、初识MySQL
1.1、为什么学习数据库
1.2、什么是数据库
1.3、数据库分类
1.4、mySQL简介
1.5、创建数据库
1.6、连接数据库
命令行连接！
mysql -uroot -proot --连接数据库
–所有语句使用分号结尾
show databases; --查看所有数据库
use school --切换数据库 use 数据库名
show tables; --查看数据库中所有的表
describe student； --显示数据库中所有的信息
create database westos； --创建一个数据库
exit； --退出连接
– 单行注释（sql本来的注释）
/*
多行注释
*/
2、 操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
–mysql不区分大小写–
2.1、创建数据库（了解）
create databases 【if not exits】数据库名;
2、删除数据库
drop databases 【if exists】 数据库名；
3、使用数据库
use 数据库名
4、查看所有数据库
show databases --查看所有数据库
学习思路：
- 对照sqlyog可视化历史记录查看sql
- 固定的语法关键字必须强行记住！
2.2、 数据库的列类型
数值
- tinyint 十分小的数据 1个字节
- smallint 较小的的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节 常用的
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节（精度问题）
- decimal 字符串形式的浮点数 金融计算的时候，一般使用decimal
字符串
- char 字符串固定大小的 0-255
- varchar 可变字符串 0-63356 常用的string
- tinytext 微型文本 2^8-1
- text 文本串 2^16-1 保存大文本
时间日期
- date YYYY-MM-DD,日期
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳，1970.1.1 到现在的毫秒数！也较为常用！
- year 年分表表示
null
- 没有值，未知
- 注意，不要使用null进行运算，结果为null
2.3、 数据库的字段属性（重点）
Unsigned：
- 无符号的整数
- 声明了该列不能声明为负数
zerofill：
- 0填充的
- 不足的位数，使用0来填充，int（3），5—005
自增：
- 通常理解为自增，自动在上一条记录基础上加1（默认）
- 通常来设计唯一的主键-index，必须是整数类型
- 可以自定义设计主键的初始值和步长
非空 NUll not null
- 假设设置为not null，如果不给他赋值，就会报错
- null，如果不填写值，就默认为null！
默认：
- 设置默认的值
- sex 默认为男
2.4、创建数据库表（重点）
-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住址,email
-- 创建表之前 , 一定要先选择数据库
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】
表名（’字段名‘ 列类型 【属性】 【索引】 【注释】，
’字段名‘ 列类型 【属性】 【索引】 【注释】，
…
’字段名‘ 列类型 【属性】 【索引】 【注释】
）【表类型】 【字符类型】 【注释】
2.5、MyISAM和InnoDB区别
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 较小 | 较大，约为2倍myisam |
常规使用：
- MYISAM 节约空间，速度较快
- INNODB 安全性高，事务的处理，多表多用户的操作
在物理空间存在的位置
所有的数据文件都存在data目录下
本质还是文件的存储
mysql引擎在物理文件上的区别
- Innodb在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM对应文件
- *.frm 表结构的定义文件
- *.MYD 数据文件（data）
- *.MYI 索引文件（index）
设置数据库中的字符集编码
charset=utf8
2.6、 修改删除表
删除
–删除表（如果存在就删除）
DROP TABLE IF EXISTS teacher1;
3、MySQL数据管理
3.1、外键（了解即可）
3.2、DML语言（全部记住）
**数据库意义：**数据存储，数据管理
DML语言：数据库操作语言
- insert
- update
- delete
3.3、insert 添加
insert
3.4、update 修改
update 修改谁（条件） set 原来的值=新值
--修改学员名字
update `student` set `name`='梁伟浩' where id=1;
--不指定条件的情况下，会修改所有的表
update `student` set `name`='长江7号'
--修改多个属性，逗号隔开
update `student` set `name`='梁伟浩',`email`='1814356884@qq.com' where id = 1;
--语法：
update 表名 set colnum_name = valus,[colnum_name=valus...] where [条件]
条件：where字句运算符 id等于某个值，大于某个值，在某个区间内修改
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <>或！= | 不等于 | 5<>6 | true |
| > | |||
| < | |||
| <= | |||
| >= | |||
| between…and… | 在某个范围内 | 【2,5】 | |
| and | 我和你 && | 5>1 and 1>2 | false |
| or | 我或你 || | 5>1 or 1>2 | true |
–通过多个条件定位数据
update
studenrsetname=‘梁伟浩’ wherename=‘777’ and sex=‘男’
语法：update 表名 set colnum_name=value,[colnum_name=value,…] where [条件]
注意：
- colnum_name 是数据库的列，尽量带上``
- 条件，筛选的条件，如果没有指定，则会修改所有的列
- value，是一个具体的值也可以是一个变量
- 多个设置属性之间，使用逗号隔开
update
studentsetbirthday= current_time wherename=‘888’ and sex=‘男’
3.5、delete 删除
delete
–删除数据（避免这样写，会全部删）
语法：delete form student
–删除指定数据
delete form student where id = 1;
4、 DQL查询数据（最重点）
4.1、DQL
(Data Query LANGHAGE：数据查询语言)
- 所有的查询操作都用它 select
- 简单的查询，复杂的查询它都能做
数据库中最核心的语言，最重要的语句- 使用频率最高的语句
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 表
有的时候列名字不是那么见名知意，可以使用as更改名字或者表名
去重 distinct
作用：去除select查询出来的结果中重复的数据，重复的数据只有一条
-- 查询一次啊有哪些同学参加了考试，成绩 SELECT * FROM result -- 查询全部考试的成绩 SELECT `studentno` FROM result -- 查询有哪些同学参加了考试 SELECT DISTINCT `studentno` FROM result -- 发现重复的数据，去重
数据库的列（表达式）
SELECT VERSION() -- 查询数据库版本 （函数） SELECT 100*3-5 AS '计算结果' -- 用来计算（表达式） SELECT @@auto_increment_increment -- 查询自增的步长（变量） -- 学员考试成绩+1分 查看 SELECT `studentno` AS 学号,`studentresult`+1 AS '提分后' FROM result
数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量。。。
select 表达式 from 表
4.3、where条件字句
作用：检索数据库中符合条件的值
搜索条件由一个或者多个表达式组成~结果布尔值
逻辑运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b a&&b | 逻辑与，两个都为真，结果为真 |
| or || | a or b a||b | 逻辑或，其中一个为真，则结果就为真 |
| not ! | not a !a | 逻辑非，真为假，假为真！ |
尽量使用英文字母
========WHERE==========
-- 查询成绩在95到100分之间
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`>60 AND `studentresult`<80
-- and &&
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult`>60 && `studentresult`<90
-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM result
WHERE `studentresult` BETWEEN 70 AND 90
-- 除了1000号学生之外的同学成绩
SELECT `studentno`,`studentresult` FROM result
WHERE `studentno` != 1000;
-- != not
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno` = 1000;
模糊查询：比较运算符
| 运算符 | 语法 | 描述 |
|---|---|---|
| is null | a is | 如果操作符为null，结果为真 |
| is not null | a is not null | 如果操作符不为null，结果为真 |
| between | a between b and c | 若a在b和c区间，则结果为真 |
| like | a like b | sql匹配如果a匹配b，则结果为 真 |
| in | a in（a1,a2,a3…） | 假设a在a1，或者a2…其中某一个值，结果为真 |
===================模糊查询===============
-- 查询姓梁的同学
-- 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 '%亦%'
-- ===========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
4.4、 联表查询
join对比
-- =======================联表查询 join=========================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result
/*
思路：
1.分析需求，分析查询的字段来自那些表，（联表查询）
确定交叉点（这两个表中那个数据是相同的）
判断条件：学生表中studentno=成绩表 studentno
*/
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,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON r.studentno=s.studentno
INNER JOIN SUBJECT sub
ON r.subjectno=sub.subjectno
– join （连接的表） on （判断条件） 连接查询
– where 等值查询
| 操作 | 描述 |
|---|---|
| inner join | 如果表中至少有一个匹配，就返回行 |
| left join | 从左表返回所有的值，即使右表中没有匹配 |
| right join | 从右表表返回所有的值，即使左表中没有匹配 |
自连接（了解）
自己的表和自己的表连接，核心：一张表拆为两张一样的表即可
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryname |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps设计 |
操作：查询父类对应的子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 查询父子信息
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 r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
4.5、分页和排序
order by
-- ========================分页和排序=================================
-- 排序：升序asc，降序desc
-- 查询的结果根据成绩降序 排序
-- ORDER BY通过哪个字段排序，怎么排
-- 查询的结果根据成绩降序 排序
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
ORDER BY`studentresult` ASC
limit
-- 100万
-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好，瀑布流
-- 分页，每页只显示3条数据
-- 语法： limit 当前页，页面的大小
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
ORDER BY`studentresult` ASC
LIMIT 1,5
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- pageSize：页面大小，
-- （n-1）*pageSize：起始值
-- n：当前页
-- 数据总数/页面大小=总页数
-- 查询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 sub.`subjectno` = r.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>=60
ORDER BY `studentresult` DESC
LIMIT 0,2
语法：limit（查询起始值下标，pageSize）
4.6、 子查询
where（这个值是计算出来的）
本质：在where语句中嵌套了一个查询语句
where（select*from）
-- 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
-- 方式二：使用子查询（有里及外）
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
WHERE `subjectno`=(
SELECT subjectno FROM `subject`
WHERE `subjectname` = '高等数学-2'
)
ORDER BY `studentresult` DESC
-- 分数不小于80分的学生的学号和姓名 DISTINCT 去重
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
WHERE `studentresult` >=70
-- 在这个基础上增加一个科目，高等数学-2
SELECT DISTINCT s.`studentno`,`studentname`
FROM student s
INNER JOIN `result` r
ON r.`studentno` = s.`studentno`
WHERE `studentresult` >=70 AND `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-2'
)
-- 查询课程为高等数学-2且分数不小于80的同学和姓名
SELECT s.`studentno`,`studentname`
FROM `student` s
INNER JOIN result r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高级数学-2' AND `studentresult` >= 70
-- 再改造
SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN(
SELECT `studentno` FROM result WHERE `studentresult` >80 AND
`subjectno`=(
SELECT `subjectno` FROM `subject` WHERE `subjectname`='高等数学-2'
)
)
5、 MySQL函数
5.1、 常用函数
-- ========================常用函数===========================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 随机数
SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1，整数返回1
-- 字符串
SELECT CHAR_LENGTH('梁伟浩') -- 字符串长度
SELECT CONCAT('fjkvhdfklx','为何','HA') -- 拼接字符串
SELECT INSERT('我爱编程helloworld',1,2,'超级热爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER ('Liangweihao') -- 小写字母
SELECT UPPER ('Liangweihao') -- 大写字母
SELECT INSTR('liangwh','h') -- 返回第一次出现的子串的索引
SELECT REPLACE ('梁伟浩坚持每天学习','坚持','努力奋斗') -- 替换出现的指定字符串
SELECT SUBSTR('梁伟浩坚持每天学习',4,6) -- 返回指定的字符串（源字符串，截取位置，截取的长度）
SELECT REVERSE('梁伟浩坚持每天学习') -- 反转
-- 时间和日期函数（记住）
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 USER()
SELECT SYSTEM_USER()
SELECT VERSION()
5.2、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count（） | 计数 |
| sum() | 求和 |
| avg() | 平均值 |
| max() | 最大值 |
| min() | 最小值 |
-- ====================聚合函数========================
-- 都能统计表中的数据（想查询一个表中有多少个记录，就会使用这个count（））
SELECT COUNT(`studentno`) FROM student -- count（指定列）,会忽略所有的null值
SELECT COUNT(*) FROM student; -- count(*) 不会忽略所有的null值
SELECT COUNT(1) FROM result; -- 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不可逆，具体的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,'zhangsa','123456'),(2,'lisi','321123'),(3,'wangwu','123456')
-- 加密
UPDATE `testmd5` SET pwd=MD5(pwd) WHERE id = 3
UPDATE `testmd5` SET pwd=MD5(pwd) -- 加密全部密码
-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小米',MD5('123321'))
-- 如何校验：将用户传递过来的密码，进行md5加密，然后比对加密后的值
SELECT * FROM `testmd5` WHERE `name`='小米' AND pwd=MD5('123321')
6、 事务
要么都成功，要么都失败
银行转账列子
将一组sql放在一个批次中去执行
事务原则：ACID原则，原子性，一致性，隔离性，持久性（脏读，幻读…）
原子性（Atomicity）
要么都成功，要么都失败
一致性（Consistency）
事务前后的数据完整性要保持一致，1000
持久性（Durability）–事务提交
事务一旦提交不可逆，将被持久化到数据库中
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，事物之间要互相隔离
隔离所导致的一些问题
脏读：
指一个事务读取了另外一个事务未提交的数据
不可重复读：
在一个事务内读取到别人表中的某一行数据，多次读取的结果不同（这个不一定是错误，只是某些场合不对）
虚度（幻读）
是在一个事务内读取到别的事务插入的数据，导致前后读取不一样
原子性
一致性
持久性
事务一旦提交不可逆
隔离性
-- ========================事务=======================
-- mysql 是默认开启事务自动提交的、
SET autocommit = 0 /*关闭*/
SET autocommit=1 /*开启（默认的）*/
-- 手动处理事务
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交：持久化（成功!）
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 开启自动提交
-- 了解
SAVEPOINT 保存点名 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名 -- 撤销 保存点
测试事务实现转账
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
CREATE DATABASE `shop1`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop1`;
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点（一组事务）
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务，就被持久化了
# rollback;
SET autocommit = 1; -- 恢复自动提交
ROLLBACK; -- 回滚
SET autocomit = 1; -- 恢复默认值
7、索引
mysql官方对索引的定义为：**索引（index）是帮助mysql高效获取数据的数据结构。**提取句子主干，就可以得到索引的本质：索引是数据的结构。
7.1、索引的分类
在一个表中，主键索引只能有一个，唯一索引可以有多个
- 主键索引（PRIMARY KEY）
- 唯一标识，主键不可重复，只能有一个列作为主键
- 唯一索引 （UNIQUE KEY）
- 避免重复的列出现唯一的索引可以重复，多个列都可以标识位 唯一索引
- 常规索引 （KEY/INDEX）
- 默认的，index，key关键字来设置
- 全文索引 （FullText）
- 在特定的数据库引擎下才有，myisam
- 快速定位数据
SET autocomit = 1; -- 恢复默认值
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
USE school
-- 显示所有的索引信息
SHOW INDEX FROM student
-- 增加一个全文索引 （索引名） 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `studentname`(`studentname`);
-- explain 分析sql执行的情况
EXPLAIN SELECT * FROM student; -- 非全文索引
SELECT * FROM student WHERE MATCH(studentname) AGAINST ('梁')
7.2、 测试索引
首先创建一个用户表
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), '181435684@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' -- 1.055sec
SELECT * FROM app_user WHERE NAME='用户9999' -- 1.029s
SELECT * FROM app_user WHERE NAME='用户9999' -- 1.067s
EXPLAIN SELECT * FROM app_user WHERE NAME='用户9999' -- 分析sql语句
SELECT * FROM student
-- id_表名_字段名
-- create index 索引名 on 表（字段）
CREATE INDEX id_app_user_name ON app_user(`name`);
-- 加了索引后
SELECT * FROM app_user WHERE NAME='用户9999' -- 0.001s
索引在小数据量的时候，用处不大，但是在大数据的时候，区别十分明显
7.3、索引原则
- 索引不是越多越好
- 不要对经常变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用来查询的字段上
索引的数据结构
Hash类型的索引
Btree：InnoDB的默认数据结构
8、权限管理和备份
8.1、用户管理
sql命令操作
%是允许远程操作
8.2、mysql备份
为什么要备份：
- 保证重要数据不丢失
- 数据转移
mysql数据备份的方式
-
直接拷贝物理文件
-
在sqlyog这种可视化工具中手动导出
-
直接拷贝数据库文件和相关配置文件
mysqldump客户端
作用 :
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器,不一定是MySQL服务器
-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
可以-w携带备份条件
-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
9、数据库设计规范
9.1、为什么需要设计
当数据库比较复杂的时候，我们就需要设计了
糟糕的数据库设计：
- 数据冗余
- 数据库插入和删除都会麻烦、异常【屏蔽物理外键】
- 程序的性能差
良好的数据库设计：
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中，关于数据库的设计
- 分析需求：分析业务和需要处理的数据库需求
- 概要设计：设计关系图e-r图
设计数据库的步骤：（个人博客）
-
收集信息，分析需求
- 用户表（用户登录注销，用户的消息，写博客，创建分类）
- 分类表（文章分类，谁创建的）
- 文章表（文章信息）
- 评论表
- 友链表（友链信息）
- 自定义表（系统信息，某个关键字，或者一些主字段）key：value
- 说说表（发表心情。。id。。content。。create_id）
-
标识实体（把需求落地到每个字段）
-
标识实体之间的关系
- 写博客：user–》blog
- 创建分类：user–》category
- 关注：user—》user
- 友链：links
- 评论：user-user-blog
用户表
分类表
博客表
评论表
友链表
9.2、三大规范
为什么需要数据规范化？
-
信息重复
-
更新异常
-
插入异常
- 无法正常显示信息、
-
删除异常
- 丢失有效的信息
三个范式(了解)
10、JDBC
10.1、数据库驱动
驱动：声卡、显卡、数据库
我们程序会通过数据库驱动，和数据库打交道！
10.2、JDBC
sun公司为了简化开发人员的（对数据库的统一）操作，提供了一个（java操作数据库的）规范，俗称jdbc，这些规范的实现由具体的厂商去做~
对于开发人员来说，我们只需要掌握jdbc接口操作即可！
java.sql
javax.sql
还需要导入一个数据库驱动包
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')`users`
1.创建一个普通项目
2.导入驱动
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>mchange-commons-java</artifactId>
<version>0.2.19</version>
</dependency>
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
package com.liang.jdbc;
import java.sql.*;
//我的第一个jdbc程序
public class JdbcFirsDemo {
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=false";
String username="root";
String password="root";
//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("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();
}
}
步骤总结：
- 加载驱动
- 连接数据库 DriverManager
- 获得执行sql的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager Class.forName("com.mysql.jdbc.Driver");//固定写法，加载驱动 Connection connection = DriverManager.getConnection(url, username, password); //connection 代表数据库 //数据库设置自动提交 //事务提交 //事务回滚 connection.rollback(); connection.commit(); connection.setAutoCommit();
URL
String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false"; //muysql --3306 //jdbc:mysql://主机地址：端口号/数据库名？参数1&参数2&参数3 //oralce -- 1521 //jdbc：orcale：thin:@localhost:1521:sid
Statement 执行sql的对象
释放资源
释放连接 resultSet.close(); statement.close(); connection.close();//耗资源，用完关掉
10.4、statement对象
代码实现
-
提取工具类
package com.liang.jdbc2.utils; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.Properties; public class JdbcUtils { private static String drive = null; private static String url=null; private static String username=null; private static String password=null; static { try { InputStream in= JdbcUtils.class.getClassLoader().getResourceAsStream("com/liang/db.properties"); Properties properties = new Properties(); properties.load(in); drive= properties.getProperty("drive"); url = properties.getProperty("url"); username=properties.getProperty("username"); password= properties.getProperty("password"); //1.驱动只加载一次 Class.forName(drive); } catch (IOException | ClassNotFoundException 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 throwables) { throwables.printStackTrace(); } } if (st!=null){ try { st.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } if (conn!=null){ try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } } } -
编写增删改方法，
executeUpdate
package com.liang.jdbc2.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestDelete {
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 = "delete from users where id=3";
int i = st.executeUpdate(sql);
if (i > 0) {
System.out.println("删除成功！！！！！！！");
}
} catch (
SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn, st, rs);
}
}
}
package com.liang.jdbc2.utils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
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`)" +
"VALUES (5,'lwh','123321','15151515','2021-02-18')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功！！！！！！！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3、查询 executeQuery
package com.liang.jdbc2.utils;
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 conn=null;
Statement st=null;
ResultSet rs=null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
//sql
String sql ="select * from users where id=1";
rs = st.executeQuery(sql);//查询完毕会返回一个结果集
while (rs.next()){
System.out.println("name");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
sql注入
sql存在漏洞，会被攻击导致数据泄露，sql会被拼接
10.5、PreparedStatement对象
PreparedStatement对象可以防止sql注入
package com.liang.jdbc3;
import com.liang.jdbc2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Set;
public class TestInsert {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement
st= null;
try {
conn= JdbcUtils.getConnection();
//区别
//使用？ 占位符代表参数
String sql="INSERT INTO users(`id`,`name`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
st=conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
//手动给参数赋值
st.setInt(1,4);//id
st.setString(2,"lwh");
st.setString(3, "123321");
st.setString(4,"454545@qq.com");
st.setDate(5, new java.sql.Date(new Date(1).getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn, st, null);
}
}
}
查询
package com.liang.jdbc3;
import com.liang.jdbc2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
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);//传递参数
st.executeQuery();//执行
if (rs.next()){
System.out.println(rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn, st,rs);
}
}
}
10.5、idea连接数据库
在url后添加：?serverTimezone=GMT%2B8设置时区,即可连接成功
10.6、 事务
要么都成功要么都失败
ACID原则
原子性：要么全部完成，要么都不完成
一致性：总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库
隔离性的问题：
脏读：一个事务读取了另外一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表数据发生了该表
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致
代码实现
-
开启事务
关闭数据库自动提交功能，自动会开启事务conn.setAutoCommit(false);
-
一组事务执行完毕，提交事务
-
可以在catch语句中显示的定义回滚语句，但默认失败就会回滚
package com.liang.jdbc4; import com.liang.jdbc2.utils.JdbcUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; 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 account set money=money-100 where name='a'"; st = conn.prepareStatement(sql1); st.executeUpdate(); String sql2="update account set money=money+100 where name='b'"; st=conn.prepareStatement(sql2); st.executeUpdate(); //事务完毕，提交事务 conn.commit(); System.out.println("成功"); } catch (SQLException e) { try { conn.rollback();//如果失败则回滚事务 } catch (SQLException throwables) { throwables.printStackTrace(); } e.printStackTrace(); }finally { JdbcUtils.release(conn,st,rs); } } }
10.7、 数据库连接池
数据库连接—执行完毕—释放
连接—释放 十分浪费资源
池化技术:准备一些预先的资源，过来就连接预先准备好的
最小连接数：10
最大连接数：15
等待超时：100ms
编写连接池吗，实现一个接口 DataSource
开源数据源实现（拿来即用）
DBCP
C3P0
Druid：阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了！
DBCP
需要用到的jar包
package com.liang.jdbc5;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils_DBCP {
private static DataSource dataSource;
static {
try {
InputStream in= JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源 工厂模式--》创建
dataSource = BasicDataSourceFactory.createDataSource(properties);
} 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 throwables) {
throwables.printStackTrace();
}
}
if (st!=null){
try {
st.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
}
package com.liang.jdbc5;
import com.liang.jdbc2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class TextDBCP {
public static void main(String[] args) {
Connection conn=null;
PreparedStatement st= null;
try {
conn= JdbcUtils_DBCP.getConnection();
//区别
//使用？ 占位符代表参数
String sql="INSERT INTO users(`id`,`name`,`PASSWORD`,`email`,`birthday`) VALUES (?,?,?,?,?)";
st=conn.prepareStatement(sql);//预编译sql，先写sql，然后不执行
//手动给参数赋值
st.setInt(1,4);//id
st.setString(2,"lwh");
st.setString(3, "123321");
st.setString(4,"454545@qq.com");
st.setDate(5, new java.sql.Date(new Date(1).getTime()));
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils_DBCP.release(conn, st, null);
}
}
}
dbcpconfig.properties
```properties
#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=root
#<!-- 初始化连接 -->
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
c3p0
## 总结：
- 多一个逗号或者少一个逗号都会导致查询失败
- ``点的时候要在飘外面.
- 建表的时候名字是用``瓢而不是''
扫一扫
1349
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
