MySQL 学习
1. 数据库的分类
1.1 关系型数据库:
- MySQL、Oracle等
- 通过表与表、行与列的关系进行存储数据。
1.2 非关系型数据库:
- Radis等
- 通过存储对象来存储数据,数据由对象的属性决定。
2. 操作数据库
操作数据库 —> 操作数据库中的表 —> 操作数据库中表的数据
2.1 操作数据库
-
创建数据库
-
creat database [if not exists] testdatabase;
-
-
删除数据库
-
drop database [if exists] testdatabase;
-
-
使用数据库
-
-- 如果表名或字段名是特殊字符,则需要带上`` use `testdatabase`;
-
-
查看数据库
-
show databases;
-
2.2 创建数据库的表
create table if not exists `test_table01`(
`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
2.3 数据表的类型
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAM | INNODB | |
---|---|---|
事务支持 | 不支持 | 支持 |
数据行锁定 | 不支持 | 支持 |
外键约束 | 不支持 | 支持 |
全文索引 | 支持 | 不支持 |
表空间的大小 | 较小 | 较大,约为2倍 |
各自的优点:
- MYISAM: 节约空间,速度较快
- INNODB: 安全性高,事务的处理,多表多用户操作
2.4 修改删除表
修改表
-- 修改表的各种操作
-- 1.修改表的表名 公式: alter table 旧表名 rename as 新表名;
alter table `test_table01` rename as `test_table`;
-- 2.增加表的字段 公式: alter table 表名 add 字段名 数据类型 [默认 注释];
alter table `test_table` add age02 int(2) default 18 comment '年龄';
-- 3.修改表的字段 公式: alter table 表名 modify 旧字段名 新数据类型;
-- MODIFY只能改数据类型和约束; 公式: alter table 表名 change 旧字段名 新字段名 新数据类型;
-- CHANGE可以重命名以及数据类型和约束,但必须重命名后才能改数据类型和约束。
alter table `test_table` MODIFY age VARCHAR(2);
alter table `test_table` CHANGE age02 age int(2);
alter table `test_table` CHANGE age age01 VARCHAR(2);
-- 4.删除表的字段 公式: alter table 表名 drop 旧字段名;
alter table `test_table` DROP age01
删除表
-- 删除表的操作
-- 公式: drop table [if exists] `表名`;
drop table if exists `test_table`;
3. MySQL 数据管理
3.1 外键
MySQL可以在创建表时或者修改表时添加物理外键,数据库级别的外键,但不建议使用(避免数据库国多造成困扰)。
最佳实践
- 数据库就是单纯的表,只用来存数据,只有行(数据)和列(字段)。
- 当想要使用多张表的数据,使用外键时,通过程序来实现外键。
3.2 DML 语言(全部记住)
插入(insert)
-- 插入字段操作
-- 公式:insert into `表名` (`字段一`,`字段二`,....) values ('数据一','数据二',....);
insert into `test_table` (`name`,`pwd`) values ('小王','123654');
-- 插入多条字段
insert into `test_table` (`name`,`pwd`) values ('小红','789654'),('小白','132146');
修改(update)
-- 修改字段操作
-- 公式:update `表名` set `字段名一` = '新的值'[, `字段名二` = '新的值',...] where [条件];
update `test_table` set `name` = '老王' where `name` = '老王';
-- 修改多个字段
update `test_table` set `name` = '老王',`pwd` = '987465' where `name` = '小王';
注意:
- 合理运用条件中的 =,!=,>,<,>=,<=,between..and..,and,or
- 当没有设置条件时,将修改所有数据
删除(delete)
-- 删除字段操作
-- 公式:1.delete from `表名` where [条件]
-- 2.truncate table `表名`
delete from `test_table` where `name` = '小白';
truncate table `test_table`;
delete 和 truncate 的区别:
- 相同点:都能删除数据,都不会修改表结构
- 不同点:
- truncate 会重新设置 自增列 计数器会归零
- truncate 不会影响事务
了解即可:delete之后,重启数据库,不同表引擎的区别:
- INNODB:自增会从1开始(存在内存中,断电即失)
- MYISAM:继续从上一个增量开始(存在文件中,不会丢失)
4. DQL 查询数据(最重要⭐)
select语法
select [all | distinct]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
from table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[where ...] -- 指定结果需满足的条件
[group by ...] -- 指定结果按照哪几个字段来分组
[having] -- 过滤分组的记录必须满足的次要条件
[order by ...] -- 指定查询记录按一个或多个条件排序
[limit {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条到哪条
注意:[]代表可选,{}代表必选
4.1 指定查询字段
-- 查询全部的学生
-- 公式:select 字段 from `表名`;
SELECT * FROM `student`;
-- 查询指定字段
select `studentno`, `studentname` from `student`;
-- as用来起别名,字段和表名都可以
select `studentno` as 学号, `studentname` as 姓名 from `student`;
-- 拼接函数concat(a,b)
select CONCAT('学号:',`studentno`,',姓名:',`studentname`) as 信息 from `student`;
去重(distinct)
-- 查询哪些学生参加了考试
select * from `result`; -- 查询全部成绩
select `studentno` from `result`; -- 查询有哪些学生参加考试
-- 数据有重复,需要去重
select distinct `studentno` from `result`;
数据库的列(表达式)
-- 学员成绩+1分
select `studentno` as 学号, `studentresult` + 1 As 新成绩 from `result`;
4.2 Where 条件子句
-- ========================================== where子句 ==============================================
select `studentno` , `studentresult` from `result`;
-- 查询成绩在60-100的学生
select `studentno`, `studentresult` from `result`
where `studentresult` >= 60 and `studentresult` <= 100;
-- &&表达式
select `studentno`, `studentresult` from `result`
where `studentresult` >= 60 && `studentresult` <= 100;
-- between...and 表达式
select `studentno`, `studentresult` from `result`
where `studentresult` between 60 and 100;
-- not 表达式
select `studentno`, `studentresult` from `result`
where not `studentresult` > 60
模糊查询(比较运算符)
-- %代表0-任意个字符,_代表一个字符,只能用于like中
-- 查询所有姓张的学生
select * from `student`
where `studentname` like '张%';
-- 查询姓张的两个字的学生
select * from `student`
where `studentname` like '张_';
-- 查询姓张的三个字的学生
select * from `student`
where `studentname` like '张__';
-- 查询名字里带张的学生
select * from `student`
where `studentname` like '%张%';
-- 查询名字中间带张的学生
select * from `student`
where `studentname` like '_%张%';
-- ============================== in =============================================
-- 查询学号在1000,1001,1002的学生
select * from `student`
where `studentno` in(1000,1001,1002);
-- 查询地址在北京朝阳、广东深圳的学生
select * from `student`
where `address` in('北京朝阳','广东深圳');
-- ============================== null / not null =============================================
-- 查询电话号码为空的学生
select * from `student`
where `phone` = '';
-- 查询电话号码不为空的学生
select * from `student`
where `phone` is not null;
4.3 联表查询
join对比
![](C:\Users\86134\Pictures\Saved Pictures\7种join理论.jpg)
-- ========================================= 联表查询 =============================================
/* 思路:
1. 分析需求,分析查询的字段来自哪些表
2. 确定使用哪种连接查询?7种
3. 确定交叉点(这两个表哪些数据是相同的)
*/
-- 查询参加了考试的学生(学号、姓名、科目编号、分数)
select * from `student`;
select * from `result`;
-- inner join
select s.studentno,studentname,subjectno,studentresult
from result as r
inner join student as s
on s.studentno = r.studentno;
-- right join 结果多了没参加考试的学生
select s.studentno,studentname,subjectno,studentresult
from result as r
right join student as s
on s.studentno = r.studentno;
-- right join 把左右两边的表调换试试,结果只有参加了考试的学生,因此,left左边的表都展示,right右边的表都展示
select s.studentno,studentname,subjectno,studentresult
from student as s
right join result as r
on s.studentno = r.studentno;
-- left join 结果和inner join一样,都是参加了考试的学生
select s.studentno,studentname,subjectno,studentresult
from result as r
left join student as s
on s.studentno = r.studentno;
-- left join 把左右两边的表调换试试,结果多了没参加考试的学生,因此,left左边的表都展示,right右边的表都展示
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno;
-- 查询缺考的同学
select s.studentno,studentname,subjectno,studentresult
from student as s
left join result as r
on s.studentno = r.studentno
where studentresult is null;
-- 查询学生所属的年纪(学号、姓名、年纪名称)
select studentno,studentname,gradename
from student as s
left join grade as g
on s.gradeid = g.gradeid;
-- 查询科目所属的年纪(科目名称,年纪名称)
select subjectname,gradename
from grade as g
inner join subject as s
on g.gradeid = s.gradeid;
-- 思考题:查询参加考试同学的信息:学号、姓名、科目名、分数
select s.studentno,studentname,subjectname,studentresult
from result as r
left join student as s
on s.studentno = r.studentno
left join `subject` as k
on r.subjectno = k.subjectno
-- 思考题:查询参加 数据库结构-1 考试同学的信息:学号、姓名、科目名、分数
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where sub.subjectname = '数据库结构-1';
操作 | 描述 |
---|---|
inner join | 如果两个表种至少有一个匹配,就返回行 |
left join | 会把left左边的表作为主表,返回左表所有的值,即使右表中没有匹配 |
right join | 会把right右边的表作为主表,返回右表所有的值,即使左表中没有匹配 |
自连接
-- ============================== 自连接 =============================================
-- 查询父子信息
select f.categoryName as '父栏目',z.categoryName as '子栏目'
from category as f, category as z
where f.categoryid = z.pid;
4.4 分页和排序
排序(order by)
-- 排序的公式: order by 通过哪个字段排序 怎么排(asc升序,desc降序)
-- 查询的结果根据成绩排序
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where sub.subjectname = '数据库结构-1'
order by studentresult asc;
分页(limit)
-- 分页的公式: limit 起始值,页面大小
-- 第一页: limit 0,5 (1-1)*5
-- 第二页: limit 5,5 (2-1)*5
-- 第三页: limit 10,5 (3-1)*5
-- 第四页: limit 15,5 (4-1)*5
-- 第N页: limit (n-1)*5,5 (n-1)*pagesize,pagesize
-- [pagesize:页面大小]
-- [(n-1)*pagesize:起始值]
-- [n:当前页]
-- [数据总数 / 页面大小 = 总页数]
-- 对排序的结果进行分页
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
order by studentresult asc
limit 0,5;
-- 查询 java第一学年 课程成绩排名前十的学生,并且分数要大于80的学生信息(学号、姓名、课程名称、分数)
select s.studentno,studentname,subjectname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where r.studentresult > 80 and sub.subjectname = 'Java程序设计-1'
order by r.studentresult desc
limit 0,10;
4.5 子查询
-- =========================================== where子查询 =============================================
-- 1.查询数据库结构-1 的所有考试结果(学号、名字、成绩)降序排序
-- 方式一:连接查询
select s.studentno,studentname,studentresult
from result as r
inner join `student` as s
on r.studentno = s.studentno
inner join `subject` as sub
on r.subjectno = sub.subjectno
where sub.subjectname = '数据库结构-1'
order by studentresult desc
-- 方式二:子查询
select s.studentno,studentname,studentresult
from result as r
inner join student as s
on r.studentno = s.studentno
where subjectno = (
select subjectno
from `subject`
where subjectname = '数据库结构-1'
)
order by studentresult desc
-- 分数不小于80分的学生的学号和姓名
-- 方式一:连接查询
select distinct s.studentno,studentname
from student as s
inner join result as r
on s.studentno = r.studentno
where studentresult >= 80
-- 方式二:子查询
select studentno,studentname
from student
where studentno in (
select studentno
from result
where studentresult >= 80
)
-- 在这个基础上加个科目,高等数学-2
-- 方式一:
select s.studentno,studentname
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where subjectname = '高等数学-2' and studentresult >= 80
-- 方式二:
select studentno,studentname
from student
where studentno in (
select studentno
from result as r
inner join `subject` as sub
on r.subjectno = sub.subjectno
where subjectname = '高等数学-2' and studentresult >= 80
)
-- 方式三:(虽然阅读成本高,但效率要高于联表查询)
select studentno,studentname
from student
where studentno in (
select studentno
from result
where subjectno = (
select subjectno
from `subject`
where subjectname = '高等数学-2'
) and studentresult >= 80
)
-- 查询c语言-1 前五名学生的信息(学号,姓名,成绩)
-- 方式一: 联表查询
select s.studentno,studentname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
inner join `subject` as sub
on sub.subjectno = r.subjectno
where subjectname = 'C语言-1'
order by studentresult desc
limit 0,5
-- 方式二:子查询
select s.studentno,studentname,studentresult
from student as s
inner join result as r
on s.studentno = r.studentno
where subjectno = (
select subjectno
from `subject`
where subjectname = 'C语言-1'
)
order by studentresult desc
limit 0,5
4.7 分组和过滤
-- 查询不同课程的平均分,最高分,最低分,并且平均分要高于80
-- 核心:根据不同学科分组
select subjectname,avg(studentresult) as 平均分,max(studentresult) as 最高分,min(studentresult) as 最低分
from result as r
inner join `subject` as sub
on r.subjectno = sub.subjectno
group by r.subjectno
having 平均分 >= 80
5. MySQL函数
5.1 聚合函数
函数 | 描述 |
---|---|
count() | 计数 |
sum() | 求和 |
avg() | 平均值 |
max() | 最大值 |
min() | 最小值 |
-- =========================================== 聚合函数 =============================================
-- count(字段) 会忽略所有的null值
-- count(*) 不会忽略null值,本质是计算行数
-- count(1) 不会忽略null值,本质是计算行数
-- 对于有主键的时候,用count(字段)效率要高于count(1),否则count(1)效率高,即count(主键列)>count(1)>count(非主键列)
select count(studentname) from student;
select count(*) from student;
select count(1) from student;
select sum(studentresult) as 总分 from result;
select avg(studentresult) as 平均分 from result;
select max(studentresult) as 最高分 from result;
select min(studentresult) as 最低分 from result;
5.2 数据库级别的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`(`id`,`name`,`pwd`)
values('1','张三','132456'),
('2','李四','564123'),
('3','王五','456784'),
('4','赵六','213456'),
('5','小王','789546')
-- 加密
update `testmd5` set `pwd` = md5(`pwd`)
-- 插入时加密
insert into `testmd5`(`id`,`name`,`pwd`) values('6','小明',md5('123654'))
-- 查询
select * from `testmd5` where `name` = '小明' and `pwd` = md5('123654')
6. 事务⭐
事务原则:ACID原则 原子性、一致性、隔离性、持久性
参考链接:(https://blog.csdn.net/dengjili/article/details/82468576/)
- 原子性(Atomicity)
要么都成功,要么都失败
- 一致性(Consistency)
事务前后的数据完整性要保持一致
- 隔离性(Isolation)
事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务 的操作数据所干扰,多个并发事务之间要相互隔离。
- 持久性(Durability)
事务一旦提交则不可逆,被持久化到数据库中
隔离所导致的一些问题
- 脏读:
指一个事务读取了另外一个事务未提交的数据。
- 不可重复读:
在一个事务内读取表中的某一行数据,多次读取结果不同。(这个不一定是错误,只是某些场合 不对)
- 虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据,导致前后读取数量总量不一致。
执行事务
-- =========================================== 事务 =============================================
-- mysql 默认开启事务自动提交
set autocommit = 0 -- 关闭自动提交
-- 事务开启
start transaction -- 标记一个事务的开始,从这个之后的 sql 都在同一个事务内
insert xx
insert xx
-- 提交:持久化(成功)
commit
-- 回滚:回到原来的样子(失败)
rollback
-- 事务结束
set autocommit = 1 -- 开启自动提交
-- 了解
savepoint 保存点名 -- 设置一个事务的保存点
rollback to savepoint 保存点名 -- 回滚到保存点
release savepoint 保存点名 -- 撤销保存点
模拟场景
-- 转账
create database money character set utf8 collate utf8_general_ci;
use money;
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',1000.00)
-- 模拟转账
set autocommit = 0; -- 关闭自动提交
start transaction; -- 开启事务
update `account` set `money` = `money` - 500 where `name` = 'a'; -- a给b转500
update `account` set `money` = `money` + 500 where `name` = 'b'; -- b收到a的500
commit; -- 提交,持久化
rollback; -- 回滚,提交后没法回滚
set autocommit = 1; -- 开启自动提交
7. 索引
MySQL官方对索引的定义为:索引(index)是帮助MySQL高效获取数据的数据结构。索引是数据结构。
7.1 索引的分类
在一个表中,主键索引只能有一个,唯一索引可以有多个
- 主键索引(primary key)
- 唯一的标识,主键不可重复,只能有一个列作为主键
- 唯一索引(unique key)
- 避免重复的字段出现,唯一索引可以有多个
- 常规索引(key / index)
- 默认的
- 全文索引(FullText)
- 在特定的数据库引擎下才有,MYISAM
- 快速定位数据
参考链接:https://blog.csdn.net/jiadajing267/article/details/81269067
基础语法
-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后,增加索引
-- 显示所有的索引信息
SHOW INDEX FROM student;
-- 新增一个索引 (索引名) 列名
ALTER TABLE `student` ADD UNIQUE KEY `UK_IDENTITY_CARD` (`identity_card`);
ALTER TABLE `student` ADD KEY `K_STUDENT_NAME`(`student_name`);
ALTER TABLE `student` ADD FULLTEXT INDEX `FI_PHONE` (`phone`);
-- explain 分析sql执行的状况
EXPLAIN SELECT * FROM student; -- 非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(`phone`) AGAINST('138'); -- 全文索引
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表'
-- 插入100万数据.
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),'[email protected]',concat('18',floor(rand()*999999999)),
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'; -- 0.421sec
explain select * from app_user where `name` = '用户99999'; -- 992742rows
-- id_表名_字段名
-- create index 索引名 on 表名(字段名)
create index id_app_user_name on app_user(`name`);
select * from app_user where `name` = '用户99999'; -- 0.001sec
explain select * from app_user where `name` = '用户99999'; -- 1row
![](C:\Users\86134\Pictures\Saved Pictures\微信图片_20221027152913.jpg)
索引在小数据量的时候,用处不大,但在大数据量的时候,区别十分明显
7.3 索引原则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表不需要加索引
- 索引一般加在常用于查询的字段上
索引的数据结构
参考链接:CodingLabs - MySQL索引背后的数据结构及算法原理
Hash类型的索引
Btree:innodb的默认数据结构
8. 权限管理和备份
8.1 用户管理
SQL命令操作
用户表:mysql.user
-- 创建用户
CREATE USER leez01 IDENTIFIED BY '123456';
-- 修改密码(修改当前用户密码)
SET PASSWORD = PASSWORD('123456');
-- 修改密码(修改指定用户密码)
SET PASSWORD FOR leez01 = PASSWORD('123456');
-- 重命名 RENAME 原名子 leez TO 新名字;
RENAME USER leez01 TO leez;
-- 用户授权 ALL PRIVILEGES 全部的权限,库,表
-- ALL PRIVILEGES 除了给别人授权不行,其他都能干
GRANT ALL PRIVILEGES ON *.* TO leez;
-- 查询权限
SHOW GRANTS FOR leez; -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost; -- 查看root用户的权限
-- 撤销权限 REVOKE哪些权限,在哪个库,给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM leez;
-- 删除用户
DROP USER leez;
8.2 MySQL备份
- 直接拷贝物理文件
- 在 navicat 这种可视化工具中手动导出
- 使用命令行导出, mysqldump 命令行使用
# 一张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql
# 多张表 mysqldump -h主机 -u用户名 -p密码 数据库 表名1 表名2 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/a.sql
# 数据库 mysqldump -h主机 -u用户名 -p密码 数据库 >物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/a.sql
# 导入
# 登录的情况下,切换到指定的数据库
# source 备份文件
# 也可以这样
mysql -u用户名 -p密码 库名<备份文件
9. 规范数据库设计
糟糕的数据库设计
- 数据冗余,浪费空间
- 数据库插入和删除都会麻烦、异常(屏蔽使用物理外键)
- 程序的性能差
良好的数据库设计
- 节省内存空间
- 保证数据库的完整性
- 方便我们开发系统
软件开发中,关于数据库的设计
- 分析需求,分析业务和需要处理的数据库的需求
- 概要设计:设计关系图E-R图
三大范式
参考链接:关系型数据库设计:三大范式的通俗理解 - 景寓6号 - 博客园 (cnblogs.com)
第一范式(1NF)
原子性:保证每一列不可再分
第二范式(2NF)
前提:满足第一范式
第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
每张表只描述一件事情
第三范式(3NF)
前提:满足第一范式和第二范式
第三范式需要确保数据表中的每一列数据都和主键直接相关,而不能间接相关。
规范数据库的设计
规范性和性能的问题
关联查询的表不得超过三张表
- 考虑商业化的需求和目标(成本,用户体验)数据库的性能更加重要
- 在规范性能的问题的时候,需要适当的考虑一下规范性
- 故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
- 故意增加一些计算列(从大数据库降低为小数据量的查询:索引)
10. JDBC⭐
创建测试数据库
CREATE DATABASE jdbcstudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USER 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,'张三','123456','[email protected]','1980-12-04'),
(2,'李四','123456','[email protected]','1981-12-04'),
(3,'王五','123456','[email protected]','1982-12-04');
10.1 编写测试代码
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JDBCDemo01 {
public static void main(String[] args) throws ClassNotFoundException, SQLException {
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");
//2.用户信息和URL
// useSSL=true可能会报错
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
String userName = "root";
String passWord = "";
//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();
}
}
步骤总结:
- 加载驱动
- 连接数据库DriverManager
- 获取执行SQL的对象 Statement
- 获得返回的结果集
- 释放连接
DriverManager
//1.加载驱动
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//推荐这种写法加载驱动
Class.forName("com.mysql.jdbc.Driver");
Connection connection = DriverManager.getConnection(url, userName, passWord);
// connection代表数据库
// 数据库设置自动提交
// 事务提交
// 事务回滚
connection.setAutoCommit(true);
connection.commit();
connection.rollback();
URL
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
// mysql默认端口3306
// 协议://主机地址:端口号/数据库名?参数1&参数2&参数3
// oracle默认端口1521
// jdbc:oracle:thin:@localhost:1521:sid
Statement 执行sql对象 、 PreparedStatement 执行sql对象
String sql = "SELECT * FROM users";//编写SQL
statement.executeQuery();//执行查询 返回ResultSet
statement.executeUpdate();//新增,删除,修改,都用这个,返回受影响的行数
statement.execute();//执行任何SQL
ResultSet 查询的结果集,封装了所有的查询结果
获得指定的数据类型
//在不知道列类型的情况下使用
resultSet.getObject();
//如果知道列类型,就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getDouble();
resultSet.getBigDecimal();
resultSet.getFloat();
resultSet.getDate();
//...
遍历,指针
resultSet.beforeFirst();//移动到最前面
resultSet.afterLast();//移动到最后面
resultSet.next();//移动到下一个数据
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放资源
resultSet.close();
statement.close();
connection.close();//消耗资源
10.2 statement对象详解
jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的SQL语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发送了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作,示例操作:
Statement statement = connection.createStatement();
String sql = "insert into user(...) values(...)";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("插入成功~");
}
CRUD操作-delete
Statement statement = connection.createStatement();
String sql = "delete from user where id=1";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("删除成功~");
}
CRUD操作-update
Statement statement = connection.createStatement();
String sql = "update user set name='' where name =''";
int num = statement.executeUpdate(sql);
if (num > 0) {
System.out.println("修改成功~");
}
CRUD操作-read
Statement statement = connection.createStatement();
String sql = "SELECT * FROM users";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
//根据获取列的数据类型,分别调用resultSet的相应方法映射到java对象中
}
代码实现
-
提取工具类
import java.io.IOException; import java.io.InputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; 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"); //驱动只用加载一次 Class.forName(driver); } catch (IOException e) { e.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //获取连接 public static Connection getConnection() throws SQLException { return DriverManager.getConnection(url, username, password); } //释放资源 public static void release(Connection con, 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 (con != null) { try { con.close(); } catch (SQLException e) { e.printStackTrace(); } } } }
配置文件db.properties
driver=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false username=root password=123456
-
编写增删改的方法,
executeUpdate
import com.zyy.lesson02.utils.JDBCUtils; 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 con = null; Statement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); st = con.createStatement(); String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)\n" + "VALUES (5,'钱七','123456','[email protected]','1988-12-04')"; int num = st.executeUpdate(sql); if (num > 0) { System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
import com.zyy.lesson02.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 con = null; Statement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); st = con.createStatement(); String sql = "DELETE FROM users WHERE `id`=5"; int num = st.executeUpdate(sql); if (num > 0) { System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
import com.zyy.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 con = null; Statement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); st = con.createStatement(); String sql = "UPDATE users SET birthday='1990-12-01' WHERE id=1"; int num = st.executeUpdate(sql); if (num > 0) { System.out.println("更新成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
-
查询
import com.zyy.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 con = null; Statement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); st = con.createStatement(); String sql = "SELECT * FROM users WHERE id=1"; rs = st.executeQuery(sql); while (rs.next()) { System.out.println("id="+rs.getInt("id")); System.out.println("name="+rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
10.3 SQL注入
sql存在漏洞,会被攻击导致数据泄露 SQL会被拼接
import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SQLQuestion {
public static void main(String[] args) {
//正常登录
//login("张三","1234567");
//sql注入
login("' or '1=1","123456");
}
public static void login(String userName, String password) {
Connection con = null;
Statement st = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
st = con.createStatement();
String sql = "SELECT * FROM users WHERE `name`='"+userName+"' AND `password`='"+password+"'";
// SELECT * FROM users WHERE `name`='' or '1=1' AND `password`='123456'
System.out.println(sql);
rs = st.executeQuery(sql);
while (rs.next()) {
System.out.println("id="+rs.getInt("id"));
System.out.println("name="+rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.release(con, st, rs);
}
}
}
导致结果:错误的用户名或者密码可以获取到全部的用户信息
10.4 preparement对象详解
PreparedStatement可以防止SQL注入,效率更好
-
新增
import com.zyy.lesson02.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestInsert { public static void main(String[] args) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); //使用?占位符代替参数 String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES (?,?,?,?,?)"; //预编译SQL,先写SQL,然后不执行 st = con.prepareStatement(sql); //手动给参数赋值 st.setInt(1, 5); st.setString(2, "钱七"); st.setString(3, "123456"); st.setString(4, "[email protected]"); st.setDate(5, new java.sql.Date(new java.util.Date().getTime())); int num = st.executeUpdate(); if (num > 0) { System.out.println("插入成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
-
删除
import com.zyy.lesson02.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestDelete { public static void main(String[] args) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); //使用?占位符代替参数 String sql = "DELETE FROM users WHERE `id`=?"; //预编译SQL,先写SQL,然后不执行 st = con.prepareStatement(sql); //手动给参数赋值 st.setInt(1, 5); int num = st.executeUpdate(); if (num > 0) { System.out.println("删除成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
-
更新
import com.zyy.lesson02.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class TestUpdate { public static void main(String[] args) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); //使用?占位符代替参数 String sql = "UPDATE users SET birthday=? WHERE id=?"; //预编译SQL,先写SQL,然后不执行 st = con.prepareStatement(sql); //手动给参数赋值 st.setDate(1, new java.sql.Date(new java.util.Date().getTime())); st.setInt(2, 1); int num = st.executeUpdate(); if (num > 0) { System.out.println("修改成功!"); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
-
查询
import com.zyy.lesson02.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 con = null; PreparedStatement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); //使用?占位符代替参数 String sql = "SELECT * FROM users WHERE id=?"; //预编译SQL,先写SQL,然后不执行 st = con.prepareStatement(sql); //手动给参数赋值 st.setInt(1, 1); rs = st.executeQuery(); while (rs.next()) { System.out.println("id="+rs.getInt("id")); System.out.println("name="+rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
-
防止sql注入
import com.zyy.lesson02.utils.JDBCUtils; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; public class SQLQuestion { public static void main(String[] args) { //正常登录 //login("张三","123456"); //sql注入 login("' or '1=1", "123456"); } public static void login(String userName, String password) { Connection con = null; PreparedStatement st = null; ResultSet rs = null; try { con = JDBCUtils.getConnection(); // PreparedStatement 防止SQL注入的本质,把传递进来的参数当做字符 // 假设其中存在转义字符,比如说'会被直接转义 String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?"; st = con.prepareStatement(sql); st.setString(1, userName); st.setString(2, password); rs = st.executeQuery(); while (rs.next()) { System.out.println("id=" + rs.getInt("id")); System.out.println("name=" + rs.getString("name")); } } catch (SQLException e) { e.printStackTrace(); } finally { JDBCUtils.release(con, st, rs); } } }
执行结果:查不到任何结果
10.5 事务
要么都成功,要么都失败
ACID原则
-
原子性:要么全部成功,要么全部失败
-
一致性:总数不变
-
隔离性:多个进程互不干扰
-
持久性:一旦提交不可逆,持久化到数据库了
隔离性的问题:
-
脏读:一个事务读取了另外一个没有提交的事务
-
不可重复读:在同一个事务内,重复读取表中数据,表数据发生了改变
-
幻读:在一个事务内,读取到了别人插入的数据,导致前后读出来的结果不一致
代码实现
- 开启事务
con.setAutoCommit(false);
- 一组业务执行完毕,提交事务
- 可以在catch语句中显示的定义回滚语句,但是默认失败就会回滚
正常情况
import com.zyy.lesson02.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 con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//关闭自动提交 自动会开启事务
con.setAutoCommit(false);//开启事务
// A 转 B 100元
String sql1 = "update account set money=money-100 where name='A'";
ps = con.prepareStatement(sql1);
ps.executeUpdate();
String sql2 = "update account set money=money+100 where name='B'";
ps = con.prepareStatement(sql2);
ps.executeUpdate();
//业务完毕,提交事务
con.commit();
System.out.println("A 转 B 100元 成功!");
} catch (SQLException e) {
e.printStackTrace();
try {
con.rollback();
} catch (SQLException ex) {
ex.printStackTrace();
}
} finally {
JDBCUtils.release(con, ps, rs);
}
}
}
异常情况
import com.zyy.lesson02.utils.JDBCUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction2 {
public static void main(String[] args) {
Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
con = JDBCUtils.getConnection();
//关闭自动提交 自动会开启事务
con.setAutoCommit(false);//开启事务
// A 转 B 100元
String sql1 = "update account set money=money-100 where name='A'";
ps = con.prepareStatement(sql1);
ps.executeUpdate();
//默认失败
int x = 1/0; //一定会异常
String sql2 = "update account set money=money+100 where name='B'";
ps = con.prepareStatement(sql2);
ps.executeUpdate();
//业务完毕,提交事务
con.commit();
System.out.println("A 转 B 100元 成功!");
} catch (SQLException e) {
e.printStackTrace();
//如果异常,默认也会回滚,下面不写也可以
// try {
// con.rollback();
// } catch (SQLException ex) {
// ex.printStackTrace();
// }
} finally {
JDBCUtils.release(con, ps, rs);
}
}
}
标签:java,--,MySQL,学习,studentno,sql,import,null
From: https://www.cnblogs.com/leezStudy/p/16836082.html