【02】Java进阶：19-MySQL进阶、可视化工具使用、多表间的关系、连接查询、子查询、事务、数据的备份和还原、数据库设计三大范式、

最新推荐文章于 2024-03-26 16:50:19 发布
最新推荐文章于 2024-03-26 16:50:19 发布
阅读量386 收藏
点赞数 1
分类专栏： Java 文章标签： 数据库 可视化 mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/satan_devil/article/details/113649801
版权

day19-MySQL进阶

今日内容

  • 可视化工具使用
  • 多表之间的关系
  • 多表查询
    • 连接查询(交叉连接, 内连接,外连接)
    • 子查询
  • 事务
    • 开启事务,提交事务,回滚事务
    • 事务特性和隔离级别(面试)
    • 演示和处理数据库安全性问题
  • 数据库备份和还原
  • 三大范式

学习目标

  • 能够理解外键约束
  • 能够说出多表之间的关系及其建表原则
  • 能够使用内连接进行多表查询
  • 能够使用左外连接和右外连接进行多表查询
  • 能够使用子查询进行多表查询
  • 能够理解事务的概念
  • 能够在MySQL中使用事务
  • 能够完成数据的备份和恢复
  • 能够理解三大范式

第一章-可视化工具使用

实操-可视化软件的使用【会用就行】

1.目标

  • 掌握可视化软件的使用

2.步骤

  1. 连接数据库
  2. 对数据库的操作
  3. 对表的操作
  4. 对数据的操作

3.讲解

  • 连接数据库

    在这里插入图片描述

  • 对数据库的操作

    在这里插入图片描述

  • 对表的操作

  • 创建表

    在这里插入图片描述

    在这里插入图片描述

  • 修改表

    在这里插入图片描述

    在这里插入图片描述

  • 删除表

    在这里插入图片描述

  • 对数据的操作

    • 插入数据

      在这里插入图片描述

    • 删除数据

      在这里插入图片描述

    • 修改数据

4.小结

  1. 注意点:表, 记录如果创建好了, 没有展示, 需要刷新一下就可以了

第二章-多表间的关系

知识点-为什么要拆表?

1.目标

  • 知道拆表原因

2.分析

单表的缺点

创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id主键并自动增长,添加5条数据

CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_name VARCHAR(30),
	dep_location VARCHAR(30)
);

-- 添加数据
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州');

INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');

缺点:表中出现了很多重复的数据(数据冗余)，如果要修改研发部的地址需要修改3个地方。

在这里插入图片描述

3.讲解

解决方案:将一张表分成2张表(员工表和部门表)
在这里插入图片描述

-- 创建部门表
CREATE TABLE department (
	id INT PRIMARY KEY AUTO_INCREMENT,
	dep_name VARCHAR(20),
	dep_location VARCHAR(20)
);

-- 创建员工表
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT
);

-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州');
INSERT INTO department (dep_name, dep_location) VALUES('销售部', '深圳');

-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES 
('张三', 20, 1), 
('李四', 21, 1), 
('王五', 20, 1), 
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);

问题：
当我们在employee的dep_id里面输入不存在的部门,数据依然可以添加.但是并没有对应的部门，不能出现这种情况。employee的dep_id中的内容只能是department表中存在的id
在这里插入图片描述

目标:需要约束dep_id字段的值, 只能是department表中已经存在id
解决方式:使用外键约束

4.小结

​ 有些情况下,使用一张表表示数据 数据不好维护, 存在数据冗余,比较乱的现象

​ 使用多张表,需要对数据进行约束,不约束添加的数据也会不合法

知识点-外键约束【重点】

1.目标

  • 掌握外键的使用

2.分析

​ 表和表之间存在一种关系，但是这个关系需要谁来维护和约束？

3.讲解

3.1外键约束作用

​ 保证引用完整性. 用来维护多表间关系

外键: 一张从表中的某个字段引用主表中的主键
主表： 约束别人
副表/从表： 使用别人的数据，被别人约束
在这里插入图片描述

3.2外键的语法
  1. 添加外键
1. 新建表时增加外键：
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释：
CONSTRAINT -- 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES -- 主表名(主键字段名) 表示参照主表中的某个字段

2. 已有表增加外键：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);


-- 删除员工表,从新创建
CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20),
	age INT,
	dep_id INT,
	-- 添加一个外键
	-- 外键取名公司要求,一般fk结尾
	CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id)
);
-- 添加正确数据
INSERT INTO employee (NAME, age, dep_id) VALUES
('张三', 20, 1),
('李四', 21, 1),
('王五', 20, 1),
('老王', 20, 2),
('大王', 22, 2),
('小王', 18, 2);


-- 验证: 添加错误数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);// 报错
  1. 删除外键
ALTER TABLE 表 drop foreign key 外键名称;
-- 删除
ALTER TABLE employee DROP FOREIGN KEY emp_depid_ref_dep_id_fk;


-- 从新为已经存在的表增加外键
ALTER TABLE employee ADD CONSTRAINT emp_depid_ref_dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id);
3.3外键的级联【了解】

  • 要把部门表中的id值2，改成5，能不能直接修改呢？

    UPDATE department SET id=5 WHERE id=2;

    不能直接修改:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接修改主表(部门表)主键

    要删除部门id等于1的部门, 能不能直接删除呢？

    DELETE FROM department WHERE id = 1;

    不能直接删除:Cannot delete or update a parent row: a foreign key constraint fails 如果副表(员工表)中有引用的数据,不能直接删除主表(部门表)数据

    什么是级联操作：
    在修改和删除主表的主键时，同时更新或删除副表的外键值，称为级联操作
    ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
    ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除

    具体操作：

    • 删除employee表
    • 重新创建employee表，添加级联更新和级联删除
    CREATE TABLE employee (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(30),
	age INT,
	dep_id INT,
	CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);
  • 再次添加数据到员工表和部门表
  INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
  INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
  INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
  INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
  INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
  INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
  • 把部门表中id等于1的部门改成id等于10
  UPDATE department SET id=10 WHERE id=1;

在这里插入图片描述

  • 删除部门号是2的部门
  DELETE FROM department WHERE id=2;

在这里插入图片描述

4.小结

  1. 外键约束的作用: 维护多表的关系, 保证引用数据的完整性
  2. 外键的语法
alter table 表 add foreign key(列[外键]) references 表(列[主键]) [ON UPDATE CASCADE][ON DELETE  CASCADE]
  1. 外键注意事项
    • 外键的这个列的类型必须和参照主表主键列的类型一致
    • 参照列必须是主键

知识点-多表间关系

1.目标

  • 能够说出多表之间的关系及其建表原则

2.分析

​ eg: 下订单(t_order)—>谁下(t_user), 买了什么(t_product)

​ 现实生活中，实体与实体之间肯定是有关系的，比如：老公和老婆，部门和员工，老师和学生等。那么我们在设计表的时候，就应该体现出表与表之间的这种关系！分成三种：

  1. 一对多
  2. 多对多
  3. 一对一

3.讲解

一对多（1:n）

例如：班级和学生，部门和员工，客户和订单

一的一方: 班级 部门 客户

多的一方:学生 员工 订单

一对多建表原则: 在从表(多方的一方)创建1一个字段,字段作为外键指向主表(一方)的主键
在这里插入图片描述

多对多

多对多（m:n）
例如：老师和学生，学生和课程，用户和角色

一个老师可以有多个学生,一个学生也可以有多个老师 多对多的关系

一个学生可以选多门课程,一门课程也可以由多个学生选择 多对多的关系

一个用户可以有多个角色,一个角色也可以有多个用户 多对多的关系

多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键。

在这里插入图片描述

一对一(通常单表)

一对一（1:1）

例如: 一个公司可以有一个注册地址，一个注册地址只能对一个公司。

例如:一个老公可以有一个老婆,一个老婆只能由一个老公

在实际的开发中应用不多.因为一对一可以创建成一张表。
两种建表原则：

  • 外键唯一：主表的主键和从表的外键（唯一），形成主外键关系，外键唯一UNIQUE

  • 外键是主键：主表的主键和从表的主键，形成主外键关系
    在这里插入图片描述

多表设计之多表分析及创建

需求:完成一个学校的选课系统，在选课系统中包含班级，学生和课程这些实体。

分析:

班级和学生: 一对多

学生和课程: 多对多

  1. 班级和学生之间是有关系存在：

    一个班级下包含多个学生，一个学生只能属于某一个班级（一对多的关系）。

  2. 学生和课程之间是有关系存在：

    一个学生可以选择多门课程，一门课程也可以被多个学生所选择（多对多的关系）。

    -- 班级表
create table class(
	cid int primary key auto_increment,
	cname varchar(30)
);
-- 学生表
create table student(
 sid int primary key auto_increment,
	sname varchar(40),
	age int,
	cno int,
	constraint stu_c_fk1 foreign key(cno) references class(cid) 
);
-- 课程表
create table course(
 coid int primary key auto_increment,
	coname varchar(40)
);

-- 创建中间表
create table stu_cou(
   sno int,
			cono int,
			constraint stu_cou_fk2 foreign key(sno) references student(sid),
			constraint stu_cou_fk3 foreign key(cono) references course(coid) 
);

4.小结

  1. 1对多: 在多方创建一个列作为外键 指向一方的主键
  2. 多对多: 创建一张中间表,这个表里面至少包含两个列,都作为外键,指向一方的主键
  3. 1对1: 先当做1对多, 再在外键列添加唯一约束

第三章-连接查询

环境准备

-- 创建部门表
CREATE TABLE dept (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20)
);

INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');

-- 创建员工表
CREATE TABLE emp (
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(10),
  gender CHAR(1),   -- 性别
  salary DOUBLE,   -- 工资
  join_date DATE,  -- 入职日期
  dept_id INT
);

INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);

知识点-交叉查询【了解】

1.目标

  • 了解交叉查询

2.分析

​ 交叉查询把若干张表(>=2)没有条件的连接在一起,进行展示

3.讲解

  1. 语法
select a.列,a.列,b.列,b.列 from a,b ;  

select a.*,b.* from a,b ;  
--或者 
select * from a,b;
  1. 练习: 使用交叉查询部门和员工
SELECT * FROM dept, emp;

在这里插入图片描述

以上数据其实是左表的每条数据和右表的每条数据组合。左表有3条，右表有5条，最终组合后3*5=15条数据。

左表的每条数据和右表的每条数据组合，这种效果称为笛卡尔乘积

在这里插入图片描述

3.小结

  • 交叉查询其实是一种错误.数据大部分是无用数据,叫笛卡尔积.
  • 假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}。可以扩展到多个集合的情况。

知识点-内连接查询【重点】

1.目标

  • 掌握内连接查询

2.分析

​ 交叉查询产生这样的结果并不是我们想要的，那么怎么去除错误的，不想要的记录呢，当然是通过条件过滤。通常要查询的多个表之间都存在关联关系，那么就通过==关联关系(主外键关系)==去除笛卡尔积。

3.讲解

3.1 隐式内连接

隐式里面是没有inner关键字的

select [字段,字段,字段][*] from a,b where 连接条件 --(a表里面的主键 = b表里面的外键)
3.2 显示内连接

显示里面是有inner关键字的

select [字段,字段,字段][*] from a [inner] join b on 连接条件 [ where 其它条件]

-- 课堂sql
-- 隐式内连接
-- 语法: select [字段,字段,字段][*] from a,b where 连接条件 --(a表里面的主键 = b表里面的外键) 
select * from dept,emp where dept.id = emp.dept_id;  -- 内连接  显示所有信息
-- 内连接   显示部门名称,员工名称,工资,性别
select dept.name,emp.name,emp.salary,emp.gender from dept,emp where dept.id = emp.dept_id;  

-- 简化: 区别名  工作中常见
select d.name,e.name,e.salary,e.gender from dept as d,emp as e where d.id = e.dept_id;  
select d.name,e.name,e.salary,e.gender from dept d,emp  e where d.id = e.dept_id;  -- 掌握

-- 显示内连接查询
-- 语法: select [字段,字段,字段][*] from a [inner] join b on 连接条件 [ where 其它条件]
select * from dept inner join emp on dept.id = emp.dept_id; 
select d.`NAME`,e.`NAME`,e.salary from dept d inner join emp e on d.id = e.dept_id; 
select d.`NAME`,e.`NAME`,e.salary from dept d  join emp e on d.id = e.dept_id;
3.3练习

查询所有部门下的员工信息,如果该部门下没有员工则不展示.

-- 2.1 隐式内连接方式 
SELECT * FROM dept , emp where emp.`dept_id`=dept.`id`;
-- 2.2 显示内连接方式  
SELECT * FROM dept INNER JOIN emp ON emp.`dept_id`=dept.`id`;

在这里插入图片描述

4.小结

  1. 内连接的特点(查的是什么东西)

    内连接查询的是公共部分,满足连接条件(主外键关系)的部分

  2. 使用内连接的关键点

    • 使用主外键关系做为条件来去除无用信息. 抓住主外键的关系,用主外键作为连接条件 b表里面的外键 = a表里面的主键
    • 显示内连接里面的,on只能用主外键关联作为条件,如果还有其它条件,后面加where

  3. 语法

-- 隐式(不出现inner)
select * from a,b where a.主键=b.外键 and 其它条件

-- 显示(出现inner)
select * from a inner join b on a.主键=b.外键 where 其它条件

知识点-外连接【重点】

1.目标

  • 掌握外连接查询

2.分析

​ 我们发现内连接查询出来的是公共部分. 如果要保证某张表的全部数据情况下进行连接查询. 那么就要使用外连接查询了. 外连接分为左外连接和右外连接

3.讲解

3.1左外连接

​ 以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示.

​ 可以理解为：在内连接的基础上保证左边表的数据全部显示

  1. 语法
select [字段][*] from a left [outer] join b on 条件
  1. 练习:查询所有部门下的员工
SSELECT * FROM dept LEFT OUTER JOIN emp ON emp.`dept_id`=dept.`id`;
3.2右外连接

​ 以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示

可以理解为：在内连接的基础上保证右边表的数据全部显示

  1. 语法
select 字段 from a right [outer] join b on 条件
  1. 练习:查询所有员工所对应的部门
SELECT * FROM dept RIGHT OUTER JOIN emp ON emp.dept_id=dept.id;

4.小结

  1. 语法
select * from a left outer join b on 连接条件  --左外连接
select * from a right outer join b on 连接条件 --右外连接
  1. 内连接和外连接的区别
    • 内连接: 查询的是公共部分,满足连接条件的部分
    • 左外连接: 以左边表为主表, 查询出左边表的所有的数据. 再通过连接条件匹配出右边表的数据, 如果满足连接条件, 展示右边表的数据; 如果不满足, 右边的数据通过null代替
  2. 应用
1.用户1和订单m
  查询所有的用户的订单信息		外连接
  查询下单的用户的信息          内连接

2.用户1和账户m
  查询所有的用户的账户信息      外连接
  查询所有用户的开户信息        内连接

第四章-子查询【重点】

子查询入门

1.目标

  • 知道什么是子查询

2.分析

​ 我们刚刚讲解了内连接和外连接查询, 但是如果遇到很复杂的场景, 内连接和外连接查询可能查询不出来.我们就可以使用子查询了.

3.讲解

3.1什么是子查询

直观一点: 一个查询里面至少包含2个select

  • 一个查询语句的结果作为另一个查询语句的条件

  • 有查询的嵌套，内部的查询称为子查询

  • 子查询要使用括号

  • 子查询结果的三种情况：

    1. 子查询的结果是一个值的时候
      在这里插入图片描述

    2. 子查询结果是单例多行的时候
      在这里插入图片描述

    3. 子查询的结果是多行多列
      在这里插入图片描述

3.小结

  1. 子查询嵌套查询, 查询的语句可以作为另外一个查询语句的条件. 直观一点: 一条语句里面包含了多个select
  2. 子查询套路
    • 先写整体的
    • 缺什么东西 补什么
    • 再综合

知识点-子查询进阶

1.目标

  • 掌握子查询的三种类别

2.分析

​ 尽管子查询的语法很灵活,没有固定的写法.但是它也有一些规律.

3.讲解

子查询的结果是一个值的时候

子查询结果只要是单个值，肯定在WHERE后面作为条件
SELECT 查询字段 FROM 表 WHERE 字段[= > < <>]（子查询）;

  1. 查询工资最高的员工是谁？

    1. 查询最高工资是多少
      SELECT MAX(salary) FROM emp;

    在这里插入图片描述

    1. 根据最高工资到员工表查询到对应的员工信息
      SELECT * FROM emp WHERE salary=(SELECT MAX(salary) FROM emp);

    在这里插入图片描述

  2. 查询工资小于平均工资的员工有哪些？

    1. 查询平均工资是多少
      SELECT AVG(salary) FROM emp;

    在这里插入图片描述

    1. 到员工表查询小于平均的员工信息
      SELECT * FROM emp WHERE salary < (SELECT AVG(salary) FROM emp);

    在这里插入图片描述

子查询结果是单列多行的时候

子查询结果只要是单列，肯定在WHERE后面作为条件
子查询结果是单列多行，结果集类似于一个数组，父查询使用IN运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;

  1. 查询工资大于5000的员工，来自于哪些部门的名字

    1. 先查询大于5000的员工所在的部门id
      SELECT dept_id FROM emp WHERE salary > 5000;

    在这里插入图片描述

    1. 再查询在这些部门id中部门的名字
      SELECT dept.name FROM dept WHERE dept.id IN (SELECT dept_id FROM emp WHERE salary > 5000);

    在这里插入图片描述

  2. 查询开发部与财务部所有的员工信息

    1. 先查询开发部与财务部的id
    SELECT id FROM dept WHERE NAME IN('开发部','财务部');

    在这里插入图片描述

    1. 再查询在这些部门id中有哪些员工
    SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME IN('开发部','财务部'));

    在这里插入图片描述

子查询的结果是多行多列

子查询结果只要是多行多列，肯定在FROM后面作为表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件;
子查询作为表需要取别名，否则这张表没用名称无法访问表中的字段

  • 查询出2011年以后入职的员工信息，包括部门名称

    1. 在员工表中查询2011-1-1以后入职的员工
    SELECT * FROM emp WHERE join_date > '2011-1-1';

    在这里插入图片描述

    1. 查询所有的部门信息，与上面的虚拟表中的信息组合，找出所有部门id等于的dept_id
    SELECT * FROM dept d, (SELECT * FROM emp WHERE join_date > '2011-1-1') e WHERE e.dept_id = d.id;

    在这里插入图片描述

4.小结

  1. 子查询的结果是单行单列(一个值情况), 一般放在where后面作为条件, 通过=,>,<,<>
select ... from ... where 列 = (子查询)
  1. 子查询的结果是单列多行, 一般放在where后面作为条件, 通过in
select ... from ... where 列 in (子查询)
  1. 子查询的结果是多行多列, 一般放在from后面作为虚拟表, 需要给虚拟表取别名
select ... from (子查询) as 别名

第五章-事务

环境的准备

-- 账户表
create table account(
    id int primary key auto_increment,
    name varchar(20),
    money double
);

insert into account values (null,'zs',1000);
insert into account values (null,'ls',1000);
insert into account values (null,'ww',1000);

知识点-事务的概述

1.目标

  • 掌握什么是事务以及事务作用

2.路径

  1. 什么是事务
  2. 事务作用

3.讲解

3.1什么是事务
  • 事务指逻辑上的一组操作，组成这组操作的单元要么全部成功，要么全部失败。
    • 操作: 转账
    • 组成单元: zs钱-100, ls钱+100
      • 操作成功: zs钱900,ls钱1100
      • 操作失败: zs钱1000,ls钱1000
      • 不可能发生: zs钱900,ls钱1000; zs钱1000,ls钱1100
3.2.事务的作用

​ 保证一组操作全部成功或者失败。

4.小结

  1. 事务是逻辑上的一组操作, 组成这组操作的单元要么全部成功, 要么全部失败
  2. 作用: 保证全部成功或者全部失败

知识点-MYSQL进行事务管理

1.目标

  • 掌握MYSQL进行事务管理

2.路径

  1. 自动事务(mysql默认)
  2. 手动开启一个事务
  3. 事务原理
  4. 事务回滚点

3.讲解

3.1自动事务(mysql默认)

​ 一条sql语句就是一个事务

3.2手动开启一个事务

  • 方式一: 手动开启事务的方式 【掌握】

    ​ start transaction;开启事务

    ​ commit；提交

    ​ rollback；回滚

  • 方式二: 设置MYSQL中的自动提交的参数【了解】

    查看MYSQL中事务是否自动提交

    show variables like '%commit%';

    设置自动提交的参数为OFF

    set autocommit = 0;-- 0:OFF  1:ON
3.3回滚点【了解】
3.3.1什么是回滚点

​ 在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

3.3.2 回滚点的操作语句

在这里插入图片描述

3.3.3具体操作

  1. 将数据还原到1000

  2. 开启事务

  3. 让张三账号减3次钱

  4. 设置回滚点：savepoint three_times;

  5. 让张三账号减4次钱

  6. 回到回滚点：rollback to three_times;

  • 总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。
3.3.4应用场景
插入大量的数据的时候.  1亿条数据 需要插入很久.  要求: 1亿条数据是一个整体,要么全部插入成功的 要么都不插入成功.

4.小结

  1. 手动开启事务 语法

    start transaction;  -- 开启事务
commit;             -- 提交
rollback;           -- 回滚

  2. 注意

    • 建议手动开启事务, 用一次 就开启一次
    • 开启事务之后, 要么commit, 要么rollback
    • 一旦commit或者rollback, 当前的事务就结束了
    • 回滚到指定的回滚点, 但是这个时候事务没有结束的

知识点-事务特性和隔离级别

1.目标

  • 掌握事务的特性和隔离级别

2.路径

  1. 事务特性
  2. 事务隔离级别

3.讲解

3.1事务特性【面试题】

  • 原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

    eg: zs 1000; ls 1000; 
	zs 给 ls转100
	要么都发生zs 900; ls 1100;
	要么都不发生zs 1000; ls 1000;

  • 一致性（Consistency）事务前后数据的完整性必须保持一致.

    eg: zs 1000; ls 1000;  一共2000
	zs 给 ls转100
	要么都发生zs 900; ls 1100; 	一共2000
	要么都不发生zs 1000; ls 1000; 一共2000

  • 持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。

    eg: zs 1000 给小红 转520, 张三 提交了

  • 隔离性（Isolation）事务的隔离性是指多个用户并发操作数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。 简单来说: 事务之间互不干扰

3.2如果不考虑隔离性，会引发下面的问题

​ 事务在操作时的理想状态： 所有的事务之间保持隔离，互不影响。因为并发操作，多个用户同时访问同一个数据。可能引发并发访问的问题

在这里插入图片描述

3.3事务隔离级别

​ 可以通过设置事物隔离级别解决读的问题

3.3.1事务四个隔离级别
级别名字隔离级别脏读不可重复读幻读数据库默认隔离级别
1读未提交read uncommitted是是是
2读已提交read committed否是是Oracle
3可重复读repeatable read否否是MySQL
4串行化serializable否否否

隔离级别越高，性能(效率)越差，安全性越高。

3.3.2设置隔离级别
  • 设置事务隔离级别
set session transaction isolation level  隔离级别;
eg: 设置事务隔离级别为read uncommitted,read committed,repeatable read,serializable
set session transaction isolation level read uncommitted;
  • 查询当前事务隔离级别
select @@tx_isolation;

4.小结

  1. 事务的特性(今天需要理解, 面试之前背诵一下.)
    • 原子性
    • 一致性
    • 持久性
    • 隔离性
  2. 不考虑隔离性会引出一些问题: 脏读, 不可重复读, 幻读
  3. 这些问题 可以 隔离级别解决

实操-演示数据库安全性问题的发生

1.目标

  • 能够演示数据库安全性问题的发生

2.路径

  1. 演示脏读
  2. 演示不可以重复读
  3. 演示避免不可重复读
  4. 演示隔离级别Serializable

3.讲解

在这里插入图片描述

4.小结

  1. 建议大家对着文档操作一遍,体会一下

第六章-数据的备份和还原 【会操作就行】

实操-数据的备份和还原

1.目标

  • 掌握数据的备份和还原

2.分析

​ 在服务器进行数据传输、数据存储和数据交换，就有可能产生数据故障。比如发生意外停机或存储介质损坏。这时，如果没有采取数据备份和数据恢复手段与措施，就会导致数据的丢失，造成的损失是无法弥补与估量的。

3.讲解

3.1命令行方式
  1. 备份格式
mysqldump -u用户名 -p密码 数据库 > 文件的路径
  1. 还原格式
SOURCE 导入文件的路径;

注意：还原的时候需要先登录MySQL,并选中对应的数据库

3.2.使用navicat备份和还原
  1. 备份

在这里插入图片描述

  1. 还原

在这里插入图片描述

4.小结

  1. 会使用navicat操作就行了, 工作里面一般是运维在处理, 运维处理的话一般使用定时任务自动备份.

第七章-数据库设计三大范式【了解】

知识点-数据库设计三大范式

1.目标

  • 能够理解三大范式

2.分析

​ 好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响。

​ 建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则就称为范式。

3.讲解

3.1 1NF
3.1.1概述

​ 数据库表的每一列都是不可分割的原子数据项，不能是集合、数组等非原子数据项。即表中的某个列有多个值时，必须拆分为不同的列。简而言之，第一范式每一列不可再拆分，称为原子性

3.1.2 应用

在这里插入图片描述

3.1.3 总结

​ 如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便）。遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）

3.2 2NF
3.2.2概述

​ **在满足第一范式的前提下，表中的每一个字段都完全依赖于主键。**所谓完全依赖是指不能存在仅依赖主键一部分的列。简而言之，第二范式就是在第一范式的基础上所有列完全依赖于主键列。当存在一个复合主键包含多个主键列的时候，才会发生不符合第二范式的情况。比如有一个主键有两个列，不能存在这样的属性，它只依赖于其中一个列，这就是不符合第二范式。

简而言之,第二范式需要满足:

  1. 一张表只描述一件事情
  2. 表中的每一个列都依赖于主键
3.2.2 应用

在这里插入图片描述

3.3.3总结

​ 如果不准守第二范式，数据冗余，相同数据无法区分。遵守第二范式减少数据冗余，通过主键区分相同数据。

3.3 3NF
3.3.1概述

​ 在满足第二范式的前提下，表中的每一列都直接依赖于主键，而不是通过其它的列来间接依赖于主键。简而言之，第三范式就是所有列不依赖于其它非主键列，也就是在满足2NF的基础上，任何非主列不得传递依赖于主键。所谓传递依赖，指的是如果存在"A → B → C"的决定关系，则C传递依赖于A。因此，满足第三范式的数据库表应该不存在如下依赖关系：主键列 → 非主键列x → 非主键列y

3.3.2应用

在这里插入图片描述

3.2.3总结

​ 如果不准守第三范式，可能会有相同数据无法区分，修改数据的时候多张表都需要修改（不方便修改）。遵守第三范式通过id可以区分相同数据，修改数据的时候只需要修改一张表（方便修改）。

4.小结

在这里插入图片描述

总结

- 能够理解外键约束
   ==保证数据引用完整性\正确性.==  用来维护多表间关系
    外键: 一张从表中的某个字段引用主表中的主键
    主表： 约束别人
    副表/从表： 使用别人的数据，被别人约束

- 能够说出多表之间的关系及其建表原则
    1. 1对多: 在多方创建一个列作为外键 指向一方的主键
   2. 多对多: 创建一张中间表,这个表里面至少包含两个列,都作为外键,指向一方的主键
   3. 1对1: 先当做1对多, 再在外键列添加唯一约束
   
- 能够使用内连接进行多表查询
     显示: select [字段,字段,字段][*] from a [inner] join b on 连接条件 [ where 其它条件]
     隐式:  select [字段,字段,字段][*] from a, b where 连接条件
- 能够使用左外连接和右外连接进行多表查询
      左外连接   select [字段][*] from a left [outer] join b on 条件
      右外连接   select [字段][*] from a right [outer] join b on 条件
- 能够使用子查询进行多表查询
     子查询结果一个值:select ... from 表 where 条件 [= < > <>] (子查询)
	 子查询结果单列多个值:select ... from 表 where 条件 in (子查询)
     子查询结果多列多行: select ... from 表 ,(子查询) as 别名 where  条件
     子查询结果多列多行: select ... from 表 left\right join (子查询) as 别名 on  条件
- 能够理解事务的概念
    事务: 要么全部成功,要么全部失败
    
- 能够在MySQL中使用事务
    开启事务: start transaction;
	提交事务:commit;
    回滚事务: rollback;    
    回滚点:  savepoint 名;   rollback to 回滚点名;-- 没有结束事务
       
- 能够完成数据的备份和恢复
     navicat----
- 能够理解三大范式 
    略
忘尘 丶莫负
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Spring Boot进阶(58)：轻松搞定数据存储！Spring Boot与PostgreSQL完美集成，让你的应用更稳定更高效！
**My Coding Family**
08-25 1289
Spring Boot如何集成PostgreSQL数据库及实战使用？不会我教你！
Spring Boot入门(07)：整合 MySQL 和 Druid数据源 | 全网最详细保姆级教学(两万字)
**My Coding Family**
08-16 1万+
在Spring Boot中，整合MySQL和Druid数据源是不可或缺的一步。本文将为您介绍最详细的保姆级教学，从配置pom.xml到创建数据库、数据表，再到编写Java代码，一步一步地带您实现整合MySQL和Druid数据源。文章总字数达到两万字，细致全面，绝不让您失望。来一起学习吧！
mysql多表格连接查询例题一
weixin_45971531的博客
09-07 184
MySql 多个表格链接查询 三个表格连接查询，首先要有一个表格里面要有另外两个表格的参数，像这样： 这样的题就有先把三个表格建起来，下面的图是建立表格，并且通过sql语句把数据添加进去，如图： -- 名人表 create table celebrity( sid int, sname VARCHAR(255), sage int, ssex VARCHAR(255) ) INSERT INTO `celebrity`(`sid`, `sname`, `sage`, `sse
mysql多表连接例子_MYSQL多表查询事例
weixin_34495151的博客
01-19 128
1.order by 排序mysql> select * from xin order by id;+------+----------+| id | name |+------+----------+| 1 | lilie || 2 | tianjin || 3 | shanghai |+------+----------+2.按照升序排列mysql&...
多表查询练习
qq_42378434的博客
04-10 844
-- 部门表 CREATE TABLE dept ( id INT PRIMARY KEY PRIMARY KEY, -- 部门id dname VARCHAR(50), -- 部门名称 loc VARCHAR(50) -- 部门所在地 ); -- 添加4个部门 INSERT INTO dept(id,dname,loc) VALUES (10,'教研部','北京'), (20,'...
mysql多表连接查询举例_MySQL多表连接查询
weixin_29302047的博客
02-08 197
连接查询是数据库查询中很重要的一个组成部分。而多表连接查询是在日常开发中经常需要用到的一个操作。在此对多表连接查询做一个总结，为了便于理解本文以一个实际开发中的案例作为例子来讲解。表结构首先我们创建三张表：用户表(t_user)、账户表(t_account)和账户流水表(t_account_info)，表中的字段非常的简单，分别如下：t_useruser_iduser_name1ricky2jac...
mysql多表连接查询举例_mysql
weixin_32392989的博客
01-19 110
Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联接等1.1.1 摘要 Join是关系型数据库系统的重要操作之一，SQL Server中包含的常用Join：内联接、外联接和交叉联...实际的项目，存在多张表的关联关系。不可能在一张表里面就能检索出所有数据。如果没有表连接的话，那么我们就需要非常多的操作。比如需要从A表找出限制性的条件来从B表中...
Mysql基础知识+部分进阶知识
Chen__sir__的博客
07-26 523
最新黑马MySql视频讲解详细笔记及重点知识整理
Java 学习路线大全，再也不用迷路啦（持续更新）
腾讯全栈-ITCJF
10-14 2703
路线特点 最新，完整一条龙，从入门到入土（⭐ 表示推荐学习） 给出目标、学习建议、关键知识点、最优资源以及各类资源推荐（视频、书籍、文档、项目、工具等） 划分阶段、更有计划，且在最后给出持续学习的方向、探索 Java 程序员发展的无限可能 前言 首先呢，我们要了解 Java 的应用场景和就业方向，看看和自己的学习目的是否一致，目前，Java 的岗位需求多，是后台开发的主流编程语言，功能强大，还是很值得学习的。 阶段 1：Java 入门 目标 培养兴趣、快速上手 前期准备 准备好一款在线、随时随地
MySQL 学习笔记
甲三的博客
05-07 745
MySQL数据库介绍及安装 官方定义MySQL DBA工作内容 运维DBA 初级：各版本、各平台安装搭建、升级 中级：体系结构原理、基础管理（启动关闭、初始化配置文件管理、多实例管理、用户权限管理、基本SQL（增删改查））、 日志管理、备份与恢复、主从复制（构建、状态监控） 高级：高可用（MGR、InnoDB Cluster）、高性能（优化） 开发DBA 懂至少一门开发语言 ：JAVA、Python 基本SQL语句深入学习（增删改查）、数据库结构设计（建模） 高级SQL：存储过程、函数、触发器、视图、事件
表关联查询
weixin_33928467的博客
06-09 72
2019独角兽企业重金招聘Python工程师标准>>> ...
子查询与连接使用指南
tangerll的专栏
01-31 1134
 子查询就是查询中又嵌套的查询,嵌套的级数随各数据库厂商的设定而有所不同,一般最大嵌套数不超过15级,实际应用中,一般不要超过2级,否则代码难以理解.一般来说,所有嵌套子查询都可改写为非嵌套的查询,但是这样将导致代码量增大.子查询就如递归函数一样,有时侯使用起来能达到事半功倍之效,只是其执行效率同样较低,有时用自身连接可代替某些子查询,另外,某些相关子查询也可改写成非相关子查询.   子查询常
MySQL --- 多表查询 - 表与表之间的关系
weixin_60569662的博客
07-16 1527
左边是学生表，学生表对应一些学生的信息，例如学生的id，课程表也有一个课程id，课程表和学生此时还没有建立关系，当我们的学生选择了课程之后，如果之间在学生的字段后面加上课程id，由于学生要选择很多门课程，需要很多列用于存储课程的信息，不方便存储，引入一个中间表，只需要两个字段即可，第一列是学生id，也就是黛绮丝，既选择了java课程也选择了php课程，通过id在学生课程关系表里面找到学生选择的课程，然后在课程表里面找到具体的课程信息。一对多(多对一、一个对应多个的关系，树状模型).........
【示例】MySQL-多表查询详解-连接查询+子查询
最新发布
HMTeen
03-26 2090
多表查询是在Web开发时候比较常用的操作。一般复杂一点的项目，都需要进行多表查询。本文以自建数据表，完成多表查询的讲解学习。
MySQL多表操作
ziyi813的博客
02-11 229
MySQL多表操作 多表关系 MySQL多表之间的关系可以概括为：一对一，一对多/多对一关系，多对多。 一对一关系 一个学生只有一张身份证；一张身份证只能对应一个学生 在任一表中添加唯一外键，指向另一方主键，确保一对一关系 很少见，如果一张表的信息字段太多了，一些访问频率不高的数据可以放到另一个表，需要的时候再联表查询。 一对多/多对一关系 部门和员工： 分析：一个部门有多个员工，一个员工只能对应一个部门 实现原则：在多的一方建立外键，指向一的一方的主键。 多对多关系 学生和课程 分析：一个学生可以选择
MySQL多表关联查询
weixin_45833112的博客
06-14 3227
用户角色表 角色表 权限表 角色权限表 表间关系 关联查询用户表、用户角色表和角色表 多表连接 笛卡尔集 外连接 左外连接：left join
数据库多表关联查询
07-10 1万+
数据库多表关联查询一、JOIN的使用二、JOIN等同于INNER JOIN等同于WHERE二、多表关联查询 一、JOIN的使用 数据库表之间的关联查询使用“join … on …” 关键字。 JOIN分为： JOIN INNER JOIN LEFT JOIN RIGHT JOIN FULL JOIN 五种JOIN查询的区别： JOIN: 在表中存在至少一个匹配时，则返回行； INNER JOIN：在表中存在至少一个匹配时，则返回行。 LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行 ； RIG
开源web三维可视化_福利放送！为大家推荐5个数据分析师必备的数据可视化工具...
weixin_39995351的博客
12-03 1760
近年来，随着云和大数据时代的来临，数据可视化产品已经不再满足于使用传统的数据可视化工具来对数据仓库中的数据抽取、归纳并简单的展现。传统的数据可视化工具仅仅将数据加以组合，通过不同的展现方式提供给用户，用于发现数据之间的关联信息。新型的数据可视化产品必须满足互联网爆发的大数据需求，必须快速的收集、筛选、分析、归纳、展现决策者所需要的信息，并根据新增的数据进行实时更新。因此，在大数据时代，数据可视化工...
002-Navicat创建数据库和表教程
热门推荐
Jack魏
08-23 5万+
Navicat创建数据库和表教程1.[安装MySQL数据库](https://blog.csdn.net/WeiHao0240/article/details/86908034)2.[安装Navicat Premium 12](https://blog.csdn.net/WeiHao0240/article/details/86651569)3.连接本地数据库4.导出5.导入6.查询 1.安装My...
MySQL进阶：分组查询、子查询与表连接详解
MySQL（二）是关于深入理解MySQL数据库系统中高级查询技术的一篇文章，主要涵盖了以下几个关键知识点： 1. **分组查询**（重点）： - 分组查询是通过`GROUP BY`语句对数据进行划分，常用于统计或聚合信息。例如，...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值