MySQL
MySQL
1. 初识MySQL
前端（页面：展示，数据）
后台（连接点：连接数据库JDBC，控制前端）
数据库（存数据）
1.1 学习目的
- 岗位需求
- 大数据时代
- 数据存取需求
- 数据库是所有软件体系中最核心的存在
1.2 概念
数据库(DB，DateBase)
概念：数据仓库，软件，安装在操作系统上。SQL是语句
作用：数据存取和管理
1.3 数据库分类
- 关系型数据库：Excel （SQL）
- MySQL，Oracle，SQL Server，DB2，SQLlite
- 通过表和表之间、行和列之间的关进行数据的存储 学员表，考勤表.......
- 非关系型数据库：{key: value} （NoSQL）
- Redis，MongDB
- 对象存储，通过对象的自身属性来决定
DBMS（数据库管理系统）
数据库管理软件，通过可视化操作，科学有效的管理和操作数据
1.4 MySQL简介
- open-source relational database management system (RDBMS)
- MySQL was owned and sponsored by the Swedish company MySQL AB, which was bought by Sun Microsystems (now Oracle Corporation).
- MySQL is a component of the LAMP web application software stack (and others), which is an acronym for Linux, Apache, MySQL, Perl/PHP/Python.
1.5 安装MySQL
1.6 SQL基础语句
mysql -uroot -p 命令行连接数据库！
flush privileges; -- 刷新权限
show databases; --查看所有数据库
use test -- test是数据库名 切换数据库（命令）
show tables; --查看表
describe 图书管理系统; --查看表中字段信息
create database student; --创建数据库
exit; --退出连接
/*
多行注释
*/
数据库语言
DDL：数据库定义语言
DML：数据库操作管理语言
DQL：数据库查询语言
DCL：数据库控制语言
CRUD程序猿
CV程序猿
API程序猿
2. 操作数据库
SQL关键字不区分大小写
操作数据库 > 操作数据库的表 > 操作数据库的表的数据
创建数据库
create DATAbase if not exists test; --创建test数据库
删除数据库
Drop DATABASE if EXISTS test; --删除test数据库
使用数据库
use test --使用test数据库
select `user` from student; --如果表名或者字段名是特殊字符段，就需要使用``包裹
查看数据库
show databases;
2.1 MySQL 数据类型
数值类型
-
tinyint 1字节
-
smallint 2字节
-
int 4字节（常用）
-
bigint 8字节
-
float 4字节
-
double 8字节
-
decimal 字符串形式的浮点数（精度问题，金融常用）
字符串
-
char 0~255
-
varchar 0~65535 常用的变量
-
tinytext 255
-
text 65535 储存大文章
日期
-
date YY-MM-DD
-
time HH-mm-ss
-
datetime YYYY-MM-DD-HH-mm-ss 最常用的时间格式
-
timestamp 时间戳（1970-01-01 00:00:00到现在的时间）
-
year
null
不要使用null进行运算，结果始终为null
2.2 数据库的字段属性
-
Unsigned：无符号整数
-
zerofill：0填充
-
自增：
- 自动在上一条记录的基础上+1
- 通常用来设置唯一的主键，必须是整数类型
- 可以自定义自增的起始值和步长
-
非空：如果不赋值，会报错
-
默认default：设置默认的值
拓展
/*每一个表都必须包含以下5个字段
id 主键
`version` 乐观锁
is_delete 伪删除
gmt_create 创建时间
gmt_update 修改时间
*/
2.3 创建数据库表
-- 表的名称和字段尽量使用``括起来 ，避免被识别为关键字
-- AUTO_INCREMENT自增
-- 字符串用单引号括起来
-- 所有的语句用逗号隔开，最后一个字段不用加
-- primary key 主键， 一般只有一个
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` date DEFAULT null comment '出生日期',
`address` varchar(100) default null comment '家庭地址',
`email` varchar(100) default null comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8
show create database school -- 查看创建数据库的语句
show create table student -- 查看student表的定义语句
2.4 数据库表的类型
MYISAM与INNODB区别
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 5.6.4后支持 |
| 表空间的大小 | 较小 | 较大，2倍 |
常规使用操作：
- MYISAM：节约空间，速度较快
- INNODB：安全性高，事务的处理，多表多用户操作
在物理空间的存储位置
所有的数据库文件都存在data目录下，一个文件夹对应一个数据库
本质还是文件的存储！
MYSQL引擎在物理文件上的区别
- INNODB在数据库表中只有一个*.frm文件，以及上级目录下的ibdata1文件
- MYISAM对应的文件
- *.frm 表结构的定义文件
- *.MYD 数据文件(data)
- *.MYI 索引文件 (index)
设置数据库的字符集编码
MySQL默认编码是Latin1，不支持中文
charset=utf8mb4
也可以在my.ini添加下述代码
default-character-set=utf8
2.5 修改删除表
修改
-- 修改表名
alter table `student` rename as student01;
-- 增加表的字段
alter table `student01` add age int(11);
-- 修改表的字段（重命名、修改约束）
alter table `student01` modify age varchar(11);-- 修改约束
alter table `student01` change age age01 int(1);-- 重命名
-- 删除表的字段
alter table `student01` drop age01;
删除
所有的创建和删除操作尽量加上判断，以免报错
-- 删除表
alter table if exists `student01`;
注意点：
- 反引号`包裹字段名，避免被系统识别为关键字
- sql关键字大小写不敏感，建议小写
3. MySQL的数据管理
3.1 外键
表与表的约束
数据库只是单纯的表，只用来存数据
不建议使用现在大多不用物理外键，都通过代码逻辑去限制
create table `grade`(
`id` int(10) not null auto_increment comment '年级id',
`name` varchar(50) not null comment '年级名称',
primary key(`id`)
)engine=innodb default charset=utf8;
create table if not exists `student`(
`id` int(4) not null auto_increment COMMENT '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`gradeid` int(10) not null comment '学生年级',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '男' comment '性别',
`birthday` date DEFAULT null comment '出生日期',
`address` varchar(100) default null comment '家庭地址',
`email` varchar(100) default null comment '邮箱',
primary key(`id`),
key `FK_gradeid`(`gradeid`),
constraint `FK_gradeid` foreign key (`gradeid`) references `grade`(`id`)
)engine=innodb default charset=utf8
create table `grade`(
`id` int(10) not null auto_increment comment '年级id',
`name` varchar(50) not null comment '年级名称',
primary key(`id`)
)engine=innodb default charset=utf8;
create table if not exists `student`(
`id` int(4) not null auto_increment COMMENT '学号',
`name` varchar(30) not null default '匿名' comment '姓名',
`gradeid` int(10) not null comment '学生年级',
`pwd` varchar(20) not null default '123456' comment '密码',
`sex` varchar(2) not null default '男' comment '性别',
`birthday` date DEFAULT null comment '出生日期',
`address` varchar(100) default null comment '家庭地址',
`email` varchar(100) default null comment '邮箱',
primary key(`id`)
)engine=innodb default charset=utf8
-- 创建表的时候没有外键的约束
alter table `student`
add constraint `FK_gradeid` foreign key(`gradeid`) references `grade`(`id`);
注意点：
- 要设置外键的字段不能为主键
- 改建所参考的字段必须为主键
- 两个字段必须具有相同的数据类型和约束
3.2 DML语言
- insert
- update
- delete
3.3 添加
-- insert into `表名`(`字段1`, `字段2`, `字段3`) values('值1'), ('值2'), ('值3')
-- 数据要一一对应
insert into `grade`(`name`) values('大四'), ('大三');
insert into `student`(`name`, `pwd`,`gradeid`) values('张三', 'aaaaaa','4');
3.4 修改
update `student` set `name`='Jezer' where id = 1; -- 指定条件
update `student` set `name`='Jezer' -- 改动整个库
update `student` set `name`='Jezer', `email` = 'jezer05@qq.com' where id = 1; -- 修改多个字段
条件语句：=，<>或!=，between...and...，and，or
3.5 删除
delete from `student` where id = 1; -- 删除指定数据
truncate `student`; -- 完全清空一张表的数据，表的结构和索引不变
delete from `student`; -- 完全清空一张表的数据，表的结构和索引不变
delete和truncate的区别
-
truncate重新设置自增列，计数器会归零，不会影响事务
-
delete删除的问题-
INNODB 自增列会从1开始（存在内存当中，断电即失）
-
MYISAM 继续从上一个自增量开始（存在文件当中，不会丢失）
-
mysql版本不一样,新版的已经修复内存问题了
4. MySQL的数据查询
4.1 查询字段信息
-- 查询所有的学生信息
select * from `student`
-- 查询指定字段
select `studentno` from `student`
-- 别名，给结果起一个名字 as
select `studentno` as '学号' from `student`
-- 函数 concat(a, b) 拼接
select concat('姓名：', studentname) as '新名字' from `student`
-- 去重
select distinct `studentno` from `result`
-- 查询系统版本
select version()
-- 用来计算
select 100 *3 - 10 as '计算结果'
-- 查询自增的步长
select @@auto_increment_increment as '自增的步长'
-- 对字段的所有数据进行修改，只是修改显示值，原值不会发生变化
select `studentno`, `studentresult` + 1 from `result`
4.2 where 条件子句
逻辑运算符
add 、&&
or 、 ||
not、 ！
select `studentno`, `studentresult` from `result` where `studentresult` >= 90 and `studentresult` <= 100
select `studentno`, `studentresult` from `result` where `studentresult` between 90 and 100
模糊查询：比较运算符
is null
is not null
a between ... and ...
a like b
a in(a1, a2, a3, ...)
-- 查询‘高’开头的课程
-- like 结合 %(代表0到任意字符) _（一个字符）
select `subjectName` from `subject` where `subjectName` like '高%'
-- 查询 classhour为110，120, 130的数据, in要求精确匹配
select `subjectName` from `subject` where `classhour` in(110, 120, 130)
4.3 连表查询
①确定需要输出的数据内容,并分析数据来源
②确定交叉点，用于连接两个表
③join on连接查询，where 等值查询
select `s.studentno`, `studentname`, `subjectno`, `studentresult`
from `student` as s -- 左表
inner join `result` as r -- 右表
on s.studentno = r.studentno
select `s.studentno`, `studentname`, `subjectno`, `studentresult`
from `student` as s -- 左表
inner join `result` as r -- 右表
where s.studentno = r.studentno
on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录，还会返回on条件为真的记录
假设存在多张表连接查询，先进行两张表连接查询
4.4 自连接
核心：一张表拆为两张表
树形结构
| categoryId | pId | categoryName |
|---|---|---|
| 2 | 1 | 信息技术 |
| 3 | 1 | 软件开发 |
| 4 | 3 | 数据库 |
| 5 | 1 | 美术设计 |
| 6 | 3 | web开发 |
| 7 | 5 | ps技术 |
| 8 | 2 | 办公信息 |
-- 查询父子信息
select a.`categoryName` as '父栏目', b. `categoryName` as '子栏目'
from `catagory` as a, `category` as b
where a.`categoryid` = b.`pid`
4.5 分页和排序
排序
select `s.studentno`, `studentname`, `subjectno`, `studentresult`
from `student` as s
inner join `result` as r
where s.studentno = r.studentno
order by studentresult desc -- 升序ASC 降序DESC
分页
select `s.studentno`, `studentname`, `subjectno`, `studentresult`
from `student` as s
inner join `result` as r
where s.studentno = r.studentno
order by studentresult desc
limit 0， 5 -- 语法：limit起始数据，页面大小
-- 查询 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 = 'JAVA第一学年' and `studentResult` >= 80
order by `studentResult` DESC
limit 0, 10
4.6 子查询
-- 查询 数据库结构-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`
where `subjectNo` = (-- 交集
select `SubjectNo` from `subject`
where `subjectName` = '数据库结构-1'
)
-- 查询课程为高等数学-2且分数不小于80的同学的学号和姓名
-- 方式一：
select distinct s.`studentNo`, `StudentName`
from `student` s
inner join result r
on r.`studentNo` = s.`studentNo`
where `studentResult` >= 80 and `subjectNo` = (
select `subjectNo` from `subject`
where `subjectName` = '高等数学-2'
)
-- 方式二：
select distinct s.`studentNo`, `StudentName`
from `student` s
inner join result r
on r.`studentNo` = s.`studentNo`
inner join `subject` sub
on r.`studentNo`= sub.`studentNo`
where `subjectName` = '高等数学-2' and `studentResult` >= 80
-- 方式三
select distinct `studentNo`, `StudentName` from `student`
where `studentNo` in (
select `studentNo` from `result` where `studentResult` >= 80 and `subjectNo` =(
select `subjectNo` from `subject` where `subjectName` = '高等数学-2'
)
)
4.7 分组与过滤
-- 查询不同课程的平均分，最高分，最低分, 平均分大于等于80
select `subjectName`, avg(`studentresult`) as '平均分', max(`studentresult`), min(`studentresult`)
from `result` r
inner join `subject` sub
on r.`subjectNo` = sub.`subjectNo`
group by r.`subjectNo`
having 平均分 >= 80
5. MySQL函数
5.1 基础函数
-- 数学运算
select abs(-23) -- 绝对值
select ceiling(9.4) -- 向上取整
select floor(9.4) -- 向下取整
select rand() -- 随机数0~1
select sign(-10) -- 返回一个数的符号 0-0 负数 -1 正数 1
-- 字符串函数
select char_length('学无止境') -- 返回字符串长度
select concat('hello', 'world') -- 拼接字符串
select insert('我爱学习', 1, 2, '超级热爱') -- 替换
select lower('HELLO') -- 转化为小写字母
select upper('hello') -- 转化为大写字母
select instr('hello', 'l') -- 某个字符出现的第一个位置
select replace('坚持就能成功', '坚持', '努力') -- 替换
select substr('坚持就能成功', 2, 2) -- 返回子字符串第二个开始，截取两个长度
select reverse('坚持就能成功') -- 反转
-- 查询姓周的同学，并替换为邹
select replace (`studentName`, '周', '邹') from `student`
where `studentName` like '周%' -- 不会对数据库进行修改
-- 时间和日期函数（记住）
select current_date() -- 当前日期
select now() -- 当前时间
select localtime() -- 本地时间
select sysdate() -- 系统时间
select year(now()) -- 年
-- 系统
select system_user() -- 系统用户
select user() -- 系统用户
select version() -- 系统版本
5.2 聚合函数
-- 统计个数
select count(`studentName`) from `student` -- 统计指定字段的记录个数，会忽略所有的null值
select count(*) from `student` -- 本质计算行数，不会忽略null值
select count(1) from `student` -- 本质计算行数，不会忽略null值
-- 求和
select sum(`studentResult`) as '总和' from `result`
-- 平均分
select avg(`studentResult`) as '平均分' from `result`
-- 最高分
select max(`studentResult`) as 'max' from `result`
-- 最低分
select min(`studentResult`) as 'min' from `result`
5.3 数据库级别的MD5加密
create table `testmd5` (
`id` int(4) not null auto_increment comment '学号',
`name` varchar(30) not null comment '姓名',
`pwd` varchar(50) not null comment ' 密码', -- 需要长一点，不然无法加密
primary key(`id`)
)engine = innoDB default charset = utf8mb4
insert into `testmd5` values(1, '张三', '123456'), (2, '李四', '123456'), (3, '王五', '123456')
-- 加密
update `testmd5` set `pwd` = md5(pwd)
-- 插入的时候加密
insert into `testmd5` values(4, '赵六', md5('123456'))
-- 如何校验：将用户传递进来的密码，进行MD5加密比对
select * from `testmd5` where `pwd` = md5('123456')
6. 事务
6.1 事务的概念
要么都成功，要么都失败
将一组SQL放在一个批次中执行
ACID原则：原子性 一致性 隔离性 持久性
原子性
这个过程包含两个步骤
A： 800 - 200 = 600
B: 200 + 200 = 400
原子性表示，这两个步骤一起成功，或者一起失败，不能只发生其中一个动作
一致性
针对一个事务操作前与操作后的状态一致
操作前A：800，B：200
操作后A：600，B：400
一致性表示事务完成后，符合逻辑运算
持久性
表示事务结束后的数据不随着外界原因导致数据丢失
操作前A：800，B：200
操作后A：600，B：400
如果在操作前（事务还没有提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：800，B：200
如果在操作后（事务已经提交）服务器宕机或者断电，那么重启数据库以后，数据状态应该为
A：600，B：400
隔离性
针对多个用户同时操作，主要是排除其他事务对本次事务的影响
隔离级别
脏读：指一个事务读取了另外一个事务未提交的数据。
不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
虚读（幻读）：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。
6.2 执行事务
-- mysql是默认开启事务自动提交的
set autocommit = 1 -- 开启自动提交（默认）
-- 手动处理事务
set autocommit 0 -- 关闭自动提交
-- 事务开启
start transaction -- 标记一个事务的开启，将之后的sql绑定在一个事务中
/*
操作语句
*/
-- 提交：持久化
commit
-- 回滚：回到原来的样子（if失败）
rollback
-- 事务结束，开启自动提交
set autocommit = 1
-- ==================================拓展=========================
savepoint '保存点名'-- 设置一个事务的保存点
rollback to '保存点名'-- 回滚到保存点
release savepoint '保存点' -- 撤销保存点
模拟场景
-- 转账
create database `shop` character set utf8mb4 collate utf8mb4_bin
use `shop`
create table `account`(
`id` int(3) not null auto_increment,
`name` varchar(30) not null,
`money` decimal(9, 2) not null, -- decimal(a, b) a为定点精度，b为小数位数
primary key(`id`)
)engine = innoDB default charset = utf8mb4
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'; -- B加500
commit;-- 提交事务,一旦提交，就会持久化
rollback; -- 回滚
set autocommit = 1; -- 恢复默认值
7. 索引
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
7.1 索引的分类
- 主键索引（primary key）
- 唯一标识，主键不可重复，只能有一个
- 唯一索引（unique key）
- 避免重复的列出现，，多个列可以标识为唯一索引
- 常规索引（key/index)
- 默认的，index、key关键字来设置
- 全文索引（fulltext）
- 在特定的数据库引擎下才有，MYISAX
- 快速定位数据
7.2 测试索引
-- 显示所有的索引信息
show index from `student`
-- 修改表结构的方式添加索引
alter table `student` add fulltext index `studentName`(`studentName`);
-- explain 分析sql执行的状况
explain select * from `student`; -- 非全文索引
-- 生成数据
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 default 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=utf8mb4 COMMENT = 'app用户表'
-- 插入1000000条数据 41.181s
SET GLOBAL log_bin_trust_function_creators=1; -- 开启创建函数功能
/*
第一个语句 delimiter 将 mysql 解释器命令行的结束符由”;” 改成了”$$”，
让存储过程内的命令遇到”;” 不执行
*/
set autocommit = 0;
start transaction;
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),'19224305@qq.com', concat('', floor(rand() * 9999999999 + 10000000000)),FLOOR(RAND()*2),
UUID(), floor(rand() * 100));
set i = i + 1;
end while;
return i;
end;$$
commit;
rollback;
set autocommit = 1;
select mock_data();
-- 测试
select * from `app_user` where `name` = '用户9999'; -- 0.605s
explain select * from `app_user` where `name` = '用户9999';
-- 创建索引 id_表名_字段名
create [fulltext]index id_app_user_name on `app_user`(`name`); -- 5.252s
select * from `app_user` where `name` = '用户9999'; -- 0.023s
explain select * from `app_user` where `name` = '用户9999';
drop index id_app_user_name on `app_user`; -- 删除索引
7.3 索引原则
- 索引不是越多越好
- 不要对进程变动数据加索引
- 小数据量的表不需要加索引
- 索引一般加到常用在查询的字段上
索引的数据结构
[MySQL索引背后的数据结构及算法原理](http://blog.codinglabs.org/articles/theory-of-mysql-index.html)
8. 权限管理和备份
8.1 用户管理
SQL命令操作
用户表：mysql.user
本质：表的增删改查
-- 创建用户
create user Jezer identified by '123456'
-- 修改密码（修改当前用户密码）
set password = password('12345678') -- 新版已经淘汰
alter user '用户名'@'IP地址' identified with mysql_native_password by '123456789';-- 8.0之后
flush privileges; -- 刷新MySQL的系统权限相关表
-- 修改密码（修改指定用户密码）
set password for Jezer = password('12345678')
-- 重命名
rename user Jezer to Jezer05
-- 用户授权
grant all privileges on *.* to Jezer05-- [with grant option]
-- 查看权限
show grants for Jezer05
-- 撤销权限
revoke all privileges on *.* from Jezer05
-- 删除用户
drop user Jezer05
8.2 MySQL备份
-
直接拷贝物理文件
-
在可视化工具中手动导出
-
cmd命令
导出
mysqldump -hlocalhost -uroot -p12345678 school student >D:/a.sql
导入
mysql -uroot -p12345678
use school;
source d:/a.sql
9. 规范数据库设计
9.1 数据库设计
糟糕的数据库设计
- 数据库冗余，造成空间浪费
- 数据库插入和删除麻烦、异常【物理外键】
- 程序的性能差
良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便系统开发
数据库设计
- 分析需求：分析业务需求和需要处理的数据库
- 概要设计：设计关系图E-R图
个人博客
- 收集信息，分析需求
- 用户表（登录注销，个人信息，博客内容，分类）
- 分类表（文章内容分类，创作者）
- 文章表
- 评论表
- 友链表（友链信息）
- 自定义表 key：value （标题等）
- 标识实体（把要求落地到每个字段）
- 标识实体之间的关系
- 写博客：user --> blog
- 创建分类：user --> category
- 关注：user --> user
9.2 三大范式
数据库常见问题
- 信息重复
- 更新异常
- 插入异常
- 删除异常
第一范式（1NF）
要求数据库表的每一列都是不可分割的原子数据项。
在上面的表中，“家庭信息”和“学校信息”列均不满足原子性的要求，故不满足第一范式，调整如下：
可见，调整后的每一列都是不可再分的，因此满足第一范式（1NF）
第二范式（2NF）
在1NF的基础上第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）
在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，这样就不满足第二范式的要求，调整如下，需分成两个表：
第三范式（3NF）
在2NF基础上，第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，
而不是主键“学号”，所以需做如下调整：
9.3 规范性与性能
-
关联查询的表不能超过三张
-
考虑商业化的需求和目标，数据库的性能更加重要（成本，用户体验）
-
有时故意给某些表增加一些冗余的字段（从多表查询变成单表查询）
-
故意增加一些计算列（从大数据量降低为小数据量的查询：索引）
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,'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')
-
创建一个普通项目
-
导入数据库驱动
-
代码编写
import java.sql.*;
public class test {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动，8.0以上
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver())
Class.forName("com.mysql.cj.jdbc.Driver");
//2.用户信息和URL
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
String username = "root";
String password = "12345678";
//3.连接成功，返回数据库对象 connection 代表数据库
Connection connection = DriverManager.getConnection(url, username, password);
//4.执行SQL的对象 statement执行sql的对象
Statement statement = connection.createStatement();
//5.执行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("birthday = " + resultSet.getObject("birthday"));
}
//6.释放连接
connection.close();
statement.close();
resultSet.close();
}
}
DriverManager
//DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver())
Class.forName("com.mysql.cj.jdbc.Driver");//推荐使用这种
URL
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8";
//mysql -- 3306
//jdbc:mysql//主机地址:端口号/数据库名?参数1&参数2&......
//oralce -- 1521
//jdbc:oracle:thin:@localhost:
Statement
String sql = "select * from `users`";//SQL语句
statement.executeQuery(sql);//查询操作，返回结果集ResultSet
statement.execute(sql);//执行任何SQL，效率相对较低
statement.executeUpdate(sql);//更新、插入、删除，返回一个受影响的行数
statement.executeBatch(sql);//批量处理sql语句，一般不使用
ResultSet
封装了所有数据查询结果
//获取数据内容
ResultSet resultSet = statement.executeQuery(sql);
resultSet.getObject("id")//在不知道数据类型的情况下使用
resultSet.getInt("id");
resultSet.getString("name");
resultSet.getFloat("id");
resultSet.getDouble("id");
//遍历，指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后
resultSet.next();//下移一条数据
resultSet.previous();//上移
resultSet.absolute(2); //移动到指定行
释放资源
connection.close();
statement.close();
resultSet.close();
10.4 Statement对象
配置文件
#配置文件
driver = com.mysql.cj.jdbc.Driver
url = jdbc:mysql://localhost:3306/jdbcStudy?useUnicode = true&characterEncoding =utf8&useSSL= true &serverTimezone=GMT%2B8
username = root
password = 12345678
封装
//函数封装
package utils;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
* @author Jay_Soul
*/
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");
Class.forName(driver);
} catch (IOException | ClassNotFoundException e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url, username, password);
}
public static void release(Connection connection, Statement statement, ResultSet resultSet) throws SQLException {
if (resultSet!=null){
resultSet.close();
}
if (statement!= null){
statement.close();
}
if (connection != null){
connection.close();
}
}
}
SQL操作
//插入操作
import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author Jay_Soul
*/
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//获取数据库连接
connection = JdbcUtils.getConnection();
//获得SQL的执行对象
statement = connection.createStatement();
String sql = "insert into `users`(`id`, `name`, `password`, `email`, `birthday`)\n" +
"values(4, 'Jezer', '123456', 'jezer05@qq.com', '2021-09-29')";
int res = statement.executeUpdate(sql);
if (res > 0) {
System.out.println("插入成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection, statement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//删除操作
import utils.JdbcUtils;
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 connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
//获取数据库连接
connection = JdbcUtils.getConnection();
//获得SQL的执行对象
statement = connection.createStatement();
String sql = "delete from `users` where `id` = 4";
int res = statement.executeUpdate(sql);
if (res > 0) {
System.out.println("删除成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection, statement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
//更新操作
import 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.getConnection();
//获得SQL的执行对象
statement = connection.createStatement();
String sql = "update `users` set `name` = 'Jay' where `name` = 'Jezer'";
int res = statement.executeUpdate(sql);
if (res > 0) {
System.out.println("修改成功！");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection, statement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
import 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) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
connection = JdbcUtils.getConnection();
statement = connection.createStatement();
String sql = "select * from `users` where `id` = 2";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
}finally {
JdbcUtils.release(connection, statement, resultSet);
}
}
}
SQL注入问题
sql存在漏洞，会被攻击导致数据泄露
原因：sql语句会被拼接,只要判断结果为true，就可调出sql数据
解决 ---> PreparedStatement
10.5 PreparedStatement 对象
可以防止SQL注入，并且效率最高
插入
import utils.JdbcUtils;
import java.sql.*;
/**
* @author Jay_Soul
*/
public class TestInsert {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//获取数据库连接
connection = JdbcUtils.getConnection();
//需要预编译SQL
//使用？占位符代替参数
String sql = "insert into `users`(`id`, `name`, `password`, `email`, `birthday`) values(?, ?, ?, ?, ?)";
preparedStatement = connection.prepareStatement(sql);
//手动给参数赋值(参数下标， 值)
preparedStatement.setInt(1, 4);
preparedStatement.setString(2, "Jezer");
preparedStatement.setString(3, "123456");
preparedStatement.setString(4, "jezer05@qq.com");
//注意点：sql.Date 不是 util.Date
preparedStatement.setDate(5,new java.sql.Date(System.currentTimeMillis()));
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("插入成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection,preparedStatement , resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
删除
import utils.JdbcUtils;
import java.sql.*;
public class TestDelete {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//获取数据库连接
connection = JdbcUtils.getConnection();
//需要预编译SQL
//使用？占位符代替参数
String sql = "delete from `users` where `id` = ?";
preparedStatement = connection.prepareStatement(sql);
//手动给参数赋值(参数下标， 值)
preparedStatement.setInt(1, 4);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("删除成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection,preparedStatement , resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
更新
import utils.JdbcUtils;
import java.sql.*;
public class TestUpdate {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
//获取数据库连接
connection = JdbcUtils.getConnection();
//需要预编译SQL
//使用？占位符代替参数
String sql = "update `users` set `name` = ? where `id` = ?";
preparedStatement = connection.prepareStatement(sql);
//手动给参数赋值(参数下标， 值)
preparedStatement.setString(1, "Jezer");
preparedStatement.setInt(2, 3);
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("更新成功");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection,preparedStatement , resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
查询
import 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.getConnection();
//需要预编译SQL
//使用？占位符代替参数
String sql = "select * from `users` where `id` = ?";
preparedStatement = connection.prepareStatement(sql);
//手动给参数赋值(参数下标， 值)
preparedStatement.setInt(1, 3);
resultSet = preparedStatement.executeQuery();
if (resultSet.next()) {
System.out.println(resultSet.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.release(connection,preparedStatement , resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
PreparedStatement防止注入的本质：将传递的参数当做字符，假设其中存在转义字符，比如说''会被直接忽略
10.6 IDEA连接数据库
jdbc:mysql://localhost:3306/ssmbuild?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=utf8&useSSL=true
10.7 JDBC处理事务
import utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransation01 {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try{
connection = JdbcUtils.getConnection();
//关闭数据库自动提交，开启事务
connection.setAutoCommit(false);
String sql1 = "update `account` set `money` = `money` - 500 where `name` = 'A'";
preparedStatement= connection.prepareStatement(sql1);
preparedStatement.executeUpdate();
String sql2 = "update `account` set `money` = `money` + 500 where `name` = 'B'";
preparedStatement= connection.prepareStatement(sql2);
preparedStatement.executeUpdate();
//业务完毕，提交事务
connection.commit();
System.out.println("操作成功！");
//恢复自动提交
connection.setAutoCommit(true);
} catch (SQLException throwables) {
//此处没有回滚语句，一旦失败也会默认回滚
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
throwables.printStackTrace();
}finally {
try {
JdbcUtils.release(connection, preparedStatement, resultSet);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
10.8 数据库连接池
著名连接池
DBCP
C3P0
Druid：阿里巴巴
编写连接池，实现一个接口 DataSource
DBCP
需要用到的jar包
-
mysql-connector-java.jar（JDBC驱动包）
-
commons-dbcp-1.4.jar
-
commons-pool-1.6.jar
在pom.xml中下载依赖项
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
<dependency>
<groupId>commons-dbcp</groupId>
<artifactId>commons-dbcp</artifactId>
<version>1.4</version>
</dependency>
<dependency>
<groupId>commons-pool</groupId>
<artifactId>commons-pool</artifactId>
<version>1.6</version>
</dependency>
创建dbcpconfig.properties
# dbcpconfig.properties
#连接设置 这里的名字是DBCP数据源中定义好的
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=GMT%2B8
username=root
password=12345678
#!-- 初始化连接 --
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
在utils下创建JdbcUtils_DBCP配置文件
package utils;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.DataSource;
import javax.swing.plaf.nimbus.State;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author Jay_Soul
*/
public class JdbcUtils_DBCP {
private static DataSource dataSource = null;
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 connection, Statement statement, ResultSet resultSet){
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
实现代码
import utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author Jay_Soul
*/
public class TestDBCP {
public static void main(String[] args) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
connection = JdbcUtils_DBCP.getConnection();
statement = connection.createStatement();
String sql = "select * from `users` where `id` = 2";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
}finally {
JdbcUtils_DBCP.release(connection, statement, resultSet);
}
}
}
C3P0
在pom.xml导入外部库
<dependency>
<groupId>log4j</groupId>
<artifactId>log4j</artifactId>
<version>1.2.17</version>
</dependency>
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
<dependency>
<groupId>com.mchange</groupId>
<artifactId>c3p0</artifactId>
<version>0.9.5.2</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.26</version>
</dependency>
创建c3p0-config.xml
<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
<!--默认配置。详细配置可查阅在线文档：https://tool.oschina.net/apidocs/apidoc?api=c3p0 -->
<default-config>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</default-config>
<!--配置连接池mysql-->
<named-config name="MySQL">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/jdbcStudy?serverTimezone=GMT%2b8&useUnicode=true&characterEncoding=utf8</property>
<property name="user">root</property>
<property name="password">12345678</property>
<property name="initialPoolSize">10</property>
<property name="maxIdleTime">30</property>
<property name="maxPoolSize">100</property>
<property name="minPoolSize">10</property>
<property name="maxStatements">200</property>
</named-config>
</c3p0-config>
在utils下创建JdbcUtils_C3P0.java
package utils;
import com.mchange.v2.c3p0.ComboPooledDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import javax.sql.CommonDataSource;
import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* @author Jay_Soul
*/
public class JdbcUtils_C3P0 {
private static ComboPooledDataSource dataSource = null;
static {
try{
/*代码配置,一般不用
dataSource = new ComboPooledDataSource("MySQL");
dataSource.setDriverClass("driverClass");
dataSource.setJdbcUrl("jdbcUrl");
dataSource.setUser("user");
dataSource.setPassword("password");
dataSource.setMaxPoolSize();
*/
//配置文件写法
dataSource = new ComboPooledDataSource("MySQL");
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
//释放链接
public static void release(Connection connection, Statement statement, ResultSet resultSet){
if (resultSet!=null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (statement != null){
try {
statement.close();
} catch (SQLException e) {
}
}
if (connection != null){
try {
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
代码实现
import utils.JdbcUtils_C3P0;
import utils.JdbcUtils_DBCP;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
* @author Jay_Soul
*/
public class TestC3P0 {
public static void main(String[] args) throws SQLException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try{
connection = JdbcUtils_C3P0.getConnection();
statement = connection.createStatement();
String sql = "select * from `users` where `id` = 2";
resultSet = statement.executeQuery(sql);
while (resultSet.next()){
System.out.println(resultSet.getString("name"));
}
}finally {
JdbcUtils_C3P0.release(connection, statement, resultSet);
}
}
}
结论
无论使用什么数据源，本质还是一样，DataSource接口不会变
Apacheyyds
浙公网安备 33010602011771号