百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

MySQL学习

2022-10-28 分类：野生技术 阅读(157) 评论(0)

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),\'19224305@qq.com\',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\',\'zs@sina.com\',\'1980-12-04\'),
(2,\'李四\',\'123456\',\'lisi@sina.com\',\'1981-12-04\'),
(3,\'王五\',\'123456\',\'wangwu@sina.com\',\'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();
    }
}

步骤总结：

  1. 加载驱动
  2. 连接数据库DriverManager
  3. 获取执行SQL的对象 Statement
  4. 获得返回的结果集
  5. 释放连接

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对象中
}

代码实现

  1. 提取工具类

    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

    image-20210714221731402

    driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

  2. 编写增删改的方法，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\',\'qianqi@sina.com\',\'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);
        }
    }
}

  3. 查询

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

导致结果：错误的用户名或者密码可以获取到全部的用户信息

image-20210714223650768

10.4 preparement对象详解

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

  1. 新增

    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, \"qianqi@sina.com\");
            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);
        }
    }
}

  2. 删除

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

  3. 更新

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

  4. 查询

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

  5. 防止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原则

  • 原子性：要么全部成功，要么全部失败

  • 一致性：总数不变

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

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

隔离性的问题：

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

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

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

代码实现

  1. 开启事务con.setAutoCommit(false);
  2. 一组业务执行完毕，提交事务
  3. 可以在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);
        }
    }
}

来源：https://www.cnblogs.com/leezStudy/p/16836082.html
本站部分图文来源于网络，如有侵权请联系删除。

未经允许不得转载：百木园 » MySQL学习
标签：

相关推荐

  • 暂无文章

热门文章