Mysql基础入门篇（二）

最新推荐文章于 2023-10-07 10:43:08 发布

缘灬小太阳

最新推荐文章于 2023-10-07 10:43:08 发布

阅读量123

点赞数

分类专栏： Mysql 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/qq_43389754/article/details/109494695

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

一、约束

1.什么是约束？

概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。

2.约束的分类

主键约束：primary key
非空约束：not null
唯一约束：unique
外键约束：foreign key

2.1.非空约束

not null，某一列的值不能为null,如果在添加数据的时候为null则添加不进去`

1. 创建表时添加约束
		CREATE TABLE stu(
			id INT,
			NAME VARCHAR(20) NOT NULL -- name为非空
		);
	2. 创建表完后，添加非空约束
		ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL;

	3. 删除name的非空约束
		ALTER TABLE stu MODIFY NAME VARCHAR(20);	
		
当为某一个字段添加非空约束的时候，默认是不能添加null 

特殊的情况。当使用insert进行添加数据的时候，如果不给定义非空约束的字段添加数据，那么系统会默认给非空约束的字段添加默认值 varchar -空字符串  int - 0

2. 2 唯一约束

unique，某一列的值不能重复
唯一约束可以有NULL值，但是只能有一条记录为null
如果出现重复的数据则报错

1. 在创建表时，添加唯一约束
		CREATE TABLE stu(
			id INT,
			phone_number VARCHAR(20) UNIQUE -- 手机号
		);
2. 删除唯一约束
		ALTER TABLE stu DROP INDEX phone_number; -- 和删除非空约束不一样
3. 在表创建完后，添加唯一约束
		ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE;

2.3 主键约束

primary key
含义：非空且唯一
一张表只能有一个字段为主键
主键就是表中记录的唯一标识

1. 在创建表时，添加主键约束
		create table stu(
			id int primary key,-- 给id添加主键约束
			name varchar(20)
		);

2. 删除主键
		-- 错误 alter table stu modify id int ;
		ALTER TABLE stu DROP PRIMARY KEY;

3. 创建完表后，添加主键
		ALTER TABLE stu MODIFY id INT PRIMARY KEY;
		
4. 自动增长：
		1.  概念：如果某一列是数值类型的，使用 auto_increment 可以来完成值得自动增长(一般配合int类型的主键进行使用)

		2. 在创建表时，添加主键约束，并且完成主键自增长
		create table stu(
			id int primary key auto_increment,-- 给id添加主键约束
			name varchar(20)
		);	
		3. 删除自动增长（了解）
		ALTER TABLE stu MODIFY id INT;
		4. 添加自动增长（了解）
		ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

注意：如果删除了以前的自动增长的数据，那么下一次自动增长会继续往后+1

2.4 外键约束

外键约束较为复杂，通常在多张表产生关系后才会出现外键约束
在学习外键约束之前我们来看看之前单张表存储数据的缺陷把

单表的缺点：

# 创建一个员工表包含如下列(id, name, age, dep_name, dep_location),id 主键并自动增长,添加 6 条数据
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, '销售部', '深圳');

以上数据表的缺点:

数据冗余（有重复的部门名称和地名）
2)后期还会出现增删改的问题(如果部门的工作地点改了，该表所有的员工对应的地名都要一一修改)

解决方案

-- 解决方案：分成 2 张表
-- 创建部门表(id,dep_name,dep_location)
-- 一方，主表
create table department(
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
-- 创建员工表(id,name,age,dep_id)
-- 多方，从表
create table employee(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int -- 外键对应主表的主键
)

-- 添加 2 个部门
insert into department values(null, '研发部','广州'),(null, '销售部', '深圳');
select * from department;
-- 添加员工,dep_id 表示员工所在的部门
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);

select * from employee;

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

在这里插入图片描述

问题目标: 需要约束 dep_id 只能是 department 表中已经存在 id
解决方式: 使用外键约束

2.4.1.什么是外键约束

什么是外键：在从表中与主表主键对应的那一列，如：员工表中的 dep_id
主表：一方，用来约束别人的表
从表：多方，被别人约束的表

在这里插入图片描述

外键约束：foreign key,让表于表产生关系，从而保证数据的正确性。
	1. 在创建表时，可以添加外键
		* 语法：
			create table 表名(
				....
				外键列
				constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
			);
	eg:
	-- 1) 先删除之前的副表/从表 employee
	drop table employee;
	-- 2）创建从表 employee 并添加外键约束 emp_depid_fk
    -- 多方，从表
    create table employee(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int, -- 外键对应主表的主键
    -- 创建外键约束
    constraint emp_depid_fk foreign key (dep_id) references department(id)
    )

当添加完外键约束后：
1.不能再员工表中添加不存在部门的员工
2.不能随意删除部门表中的数据，因为从表正在引用这些数据

2.4.2.删除外键及在添加

-- 删除 employee 表的 emp_depid_fk 外键
alter table employee drop foreign key emp_depid_fk;

-- 在 employee 表情存在的情况下添加外键
alter table employee add constraint emp_depid_fk 
foreign key (dep_id) reference

2.4.3.删除外键及在添加

问题：
在部门表和员工表进行外键关联的时候，我们可以去随意的修改部门表的名称，但是如果修改部门表的编号id就不可以，为什么呢？因为部门表的id被员工表进行了引用。如果要想修改部门的编号id就很麻烦，所以我们可以使用级联操作，让多表之间支持

-- 我们可以在添加外键的时候让多表之间支持级联更新和删除（先把之前的外键删除，重新添加）
		1. 添加级联操作
			语法：ALTER TABLE 表名 ADD CONSTRAINT 外键名称 
					FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称) ON UPDATE CASCADE ON DELETE CASCADE  ;
		2. 分类：
			1. 级联更新：ON UPDATE CASCADE 
			2. 级联删除：ON DELETE CASCADE

二、数据库的设计

1.多表之间的关系

表与表之间的三种关系
一对多：最常用的关系部门和员工
多对多：学生选课表和学生表，一门课程可以有多个学生选择，一个学生选择多门课程
一对一：相对使用比较少。人和身份证

1.1.一对多关系

一对多（1:n）例如：班级和学生，部门和员工，客户和订单，分类和商品
一对多建表原则: 在从表(多方)创建一个字段,字段作为外键指向主表(一方)的主键

在这里插入图片描述

1.2.多对多关系

多对多（m:n）例如：老师和学生，学生和课程，用户和角色
多对多关系建表原则: 需要创建第三张表，中间表中至少两个字段，这两个字段分别作为外键指向各自一方的主键

在这里插入图片描述

1.3.一对一关系

一对一（1:1）在实际的开发中应用不多.因为一对一可以创建成一张表。

在这里插入图片描述

三、数据库的备份和还原

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

1 . 备份格式： DOS 下，未登录的时候。这是一个可执行文件 exe，在 bin 文件夹
	mysqldump -u 用户名 -p 密码 数据库 > 文件的路径
eg：
-- 备份 day21 数据库中的数据到 d:\day21.sql 文件中
  mysqldump -uroot -proot day21 < d:/day21.sql
-- 导出结果：数据库中的所有表和数据都会导出成 SQL 语句

2.还原格式：mysql 中的命令，需要登录后才可以操作
USE 数据库；
SOURCE 导入文件的路径;

eg:
 还原 day21 数据库中的数据，注意：还原的时候需要先登录 MySQL,并选中对应的数据库。
1) 删除 day21 数据库中的所有表
2) 登录 MySQL
3) 选中数据库
4) 使用 SOURCE 命令还原数据
5) 查看还原结果
use day21;
source d:/day21.sql

图形化界面备份与还原

备份数据库中的数据

选中数据库，右键 ”备份/导出”
指定导出路径，保存成.sql 文件即可

在这里插入图片描述

还原数据库中的数据

删除数据库
数据库列表区域右键“执行 SQL 脚本”，指定要执行的 SQL 文件，执行即可

在这里插入图片描述

四、多表查询

1 什么是多表查询？

多表查询就是从多张表中查询数据，它和单表的查询语句结构是一样的。只不过from后查询的表明不只一个！

先准备一些表来演示多表查询：

准备sql
	# 创建部门表
	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,
		FOREIGN KEY (dept_id) REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
	);
	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);

2 笛卡尔积

查询语句：SELECT * FROM emp,dept；

以上查询出来的结果如下：

在这里插入图片描述

什么是笛卡尔积：假设有两个集合A,B .取这两个集合的所有组成情况。
部门表有三列数据，员工表有五列数据，查询出来的最终结果有15条数据。
但是发现以上情况查询出来的数据有很多无用的数据

发现以上有很多条数据中，员工表的部门id并不匹配部门表的id，这些数据是在我们的业务中不希望出现的垃圾数据。

所以我们需要消除无用的数据，要么怎么去消除这些无用的数据？我们需要根据当前的业务规则来进行判断。比如以上表中的规则就是部门表的id和员工表的dept_id不一致就是无用的。只有员工表.dept_id = 部门表.id 的数据才是有用的

消除无用的笛卡尔积使用以下三种多表查询分类：

内连接查询
外连接查询
子查询

3.内连接查询

内连接的查询其实还可以细化分为二类：1.隐式内连接2.显示内连接
这二种只是写法上不同，效果是一样的

隐式内连接：使用where条件消除无用数据

 例子：
			-- 查询所有员工信息和对应的部门信息，在where条件后跟上规则（消除无用数据）
			SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
			
			-- 查询员工表的名称，性别。部门表的名称
			SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
			
			--起别名（当表名过长时）
			SELECT 
				t1.name, -- 员工表的姓名
				t1.gender,-- 员工表的性别
				t2.name -- 部门表的名称
			FROM
				emp t1,
				dept t2
			WHERE 
				t1.`dept_id` = t2.`id`;

显示内连接

语法： select 字段列表 from 表名1 [inner] join 表名2 on 条件
join（加入）：在查询表名1的时候加入表名2

例如：
SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	
SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;	-- inner可以省略

注意：隐式和显示的操作一样，只是写法格式不一样。

内连接查询分析步骤：

从哪些表中查询数据
条件是什么 (主外键)
查询哪些字段

4.外连接查询

外连接分为：1.左外连接2.右外连接

4.1.左外连接

语法：select 字段列表 from 表1 left [outer] join 表2 on 条件；
查询的是左表所有数据以及其交集部分。

比如：当员工表新入职了一个员工“小白龙”，但是还没有分配部门（外键可以为空）。

在这里插入图片描述

现在要查询的需求：
查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
如果直接用内连接查询会发现无法显示出小白龙的信息！因为内连接会把小白龙这条数据当成不合法的数据！

-- 所以我们要使用左外连接：
SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
--我们发现左外连接其实就是在显示内连接的前面多加了一个left。就能达到效果。显示出小白龙信息

在这里插入图片描述

4.2.右外连接

语法：select 字段列表 from 表1 right [outer] join 表2 on 条件；
查询的是右表所有数据以及其交集部分。

-- 查询所有的部门以及部门里的员工
	SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;

注意：
1.左外连接和右外连接其实只是一个相对的概念，只要学习一个就可以了
2.内连接，左外，右外连接的区别：

内连接是查询交集的
左外是查询左表所有数据以及其交集部分。
右外是查询右表所有数据以及其交集部分。

5.子查询

什么是子查询？
查询中嵌套查询，称嵌套查询为子查询。

比如：需要查询员工表中工资最高的员工信息？
我们一般需要分二步：
1.先查询出最高工资
SELECT MAX(salary) FROM emp;
2.根据最高工资查询
SELECT * FROM emp WHERE emp.`salary` = 9000;

以上二步可以用子查询一步完成：
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

子查询不同情况

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

5.1 子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断。运算符： > >= < <= =
比如刚才的子查询是查询最高工资的，查询的结果就是单行单列。只有一个数据，可以直接作为条件进行运算符判断！！！

-- eg:查询员工工资小于平均工资的人。
-- 该查询需要先查询出平均工资，而该查询出来的结果平均就是一个单行单列的结果，可以直接作为条件使用运算符进行判断
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);

5.2 子查询的结果是多行单列的

如果子查询的结果是多行单列的，子查询也可以作为条件进行判断，但是只能使用运算符in来判断

eg:查询'财务部'和'市场部'所有的员工信息
一般要查询以上结果需要：
1.先通过部门表查询出财务部和市场部的id
SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部';
2.在通过查询出的id，再通过员工表查询员工的信息
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;
SELECT * FROM emp WHERE dept_id IN(2,3);

以上查询出的财务部和市场部的id所得到的结果就是多行单列的！
我们可以通过in运算符来进行一次性判断：
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '财务部' OR NAME = '市场部');

5.3 子查询的结果是多行多列的

子查询可以作为一张虚拟表参与查询
eg:查询员工入职日期是2011-11-11日之后的员工信息和部门信息

-- 1.以上查询我们可以先查询2011-11-11日之后的员工信息：
SELECT * FROM emp WHERE emp.join_date > '2011-11-11'

--该查询出来的结果是多行多列的，接下来我们还需要把这二条数据所对应的部门信息查询出来。我们可以把该查询的结果当成一张虚拟的表和部门表进行关联：

-- 子查询
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

-- 当然，我们也可以使用普通的内连接也能查询出结果：
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

6. 多表查询练习

表一：employee
在这里插入图片描述
表二：dept

表三：salgrade
在这里插入图片描述

-- 1.查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。
SELECT d.deptno,d.dname,d.loc,COUNT(d.deptno) deptnum
from dept d INNER JOIN employee e 
on d.deptno = e.deptno
GROUP BY d.deptno


-- 2. 列出所有员工的姓名及其直接上级的姓名。
select e1.ename,(select ename from employee e2 where e2.empno=e1.mgr) as boss_name from employee e1;

-- 3. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。
select e.empno,e.ename,d.dname
FROM dept d,employee e
where d.deptno = e.deptno AND
e.hiredate < (select hiredate from employee e2 where e2.empno=e.mgr) 

-- 4. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
SELECT e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname
FROM employee e LEFT OUTER JOIN dept d
on e.deptno = d.deptno

-- 5. 列出最低薪金大于1500的各种工作及从事此工作的员工人数。
SELECT min(sal) minsal,job, COUNT(*) numb
FROM employee
GROUP BY job
HAVING minsal > 1500;

-- 6. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。
SELECT e.ename
FROM employee e,dept d
where e.deptno = d.deptno and d.dname ='销售部'

-- 7. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。

SELECT
	t1.ename ,t2.ename, t3.dname, t4.grade
FROM 
	employee t1 -- 员工
LEFT OUTER JOIN
	employee t2  ON t1.mgr = t2.empno -- 上级
INNER JOIN
	dept t3  ON t1.deptno = t3.deptno -- 部门
INNER JOIN 
	salgrade t4  ON t1.sal BETWEEN t4.losal AND t4.hisal-- 工资等级

WHERE t1.sal > (SELECT AVG(sal) FROM employee);


-- 8.列出与SMITH从事相同工作的所有员工及部门名称。
SELECT e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,e.deptno,d.dname
from employee e, dept d
where e.deptno = d.deptno and e.job = (SELECT job from employee where ename = 'SMITH');

-- 9.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。
SELECT e.ename,e.sal,d.dname
from employee e, dept d
where e.deptno = d.deptno AND
e.sal > all (SELECT e2.sal from employee e2 where e2.deptno = 30)

五、事务

1. 事务的概念

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

准备工作

-- 创建tb_person表
create table tb_person (id int PRIMARY key auto_increment,name VARCHAR(30), money double);

-- 添加记录
INSERT into tb_person VALUES(null,'张三',10000) ,(null,'李四',10000);

转账功能

-- 张三出账100元
update tb_person set money = money - 100 where name = "张三";

出错了!!
-- 李四入账100元
update tb_person set money = money + 100 where name = "李四";

在这里插入图片描述

2.事务管理的使用

语法

      -- 开启事务 : 意味着事务不会自动提交，需要开发人员手动提交
      start transaction;
      -- 回滚事务 : 将事务回滚到开启事务时的保存点，并结束事务
      rollback;
      -- 提交事务 : 将数据持久化到数据库保存，并结束事务
      commit;

代码实现

      -- 开启事务 : savepoint : 保存点
      START TRANSACTION;
      
      -- 转账
      UPDATE tb_person SET money = money - 100 WHERE NAME = '张三';
      
      出错了!!
      UPDATE tb_person SET money = money + 100 WHERE NAME = "李四";
      
      -- 回滚事务
      ROLLBACK;
      
      -- 提交事务
      COMMIT;

3. 事务的提交方式

概述

mysql数据库的事务提交方式默认为自动提交

分类
- 自动提交
  - 发生DML操作，那么数据会自动持久化到数据库保存
- 手动提交
  - 发生DML操作，需要手动提交数据，数据才会持久化到数据库保存
语法

-- 查询提交方式
select @@autocommit; -- 1代表自动提交，0代表手动提交
-- 设置提交方式
set @@autocommit = 1/0;

4.事务的四大特征

概述
- 也叫事务ACID
原子性(atomicity)
- 一个事务不可再拆分，要么一起成功，要么一起失败
一致性(consistency)
- 事务操作的前后，数据总量保持不变
隔离性(isolation)
- 事务与事务之间相对独立，取决于隔离级别
持久性(durability)
- 事务提交或回滚后，数据将永久保存到数据库

5. 事务的隔离级别

分类
存在的问题
语法

-- 设置隔离级别
set global transaction isolation level 隔离级别;
set session transaction isolation level 隔离级别;
-- 查看隔离级别
select @@tx_isolation;

6. 脏读的发生和解决

脏读
- 隔离级别为read uncommited ,一个事务可以读取另一个事务中未提交的数据
解决方案
- 设置隔离级别为read commited(读已提交)
代码实现
- 窗口A

-- 设置隔离级别为read uncommited
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询隔离级别
SELECT @@tx_isolation;

-- read uncommited : 读未提交，一个事务可以读取到另一个事务中未提交数据

  -- 开启事务

  START TRANSACTION;

  -- 转账业务

  UPDATE tb_person SET money = money - 100 WHERE NAME = '张三';

  UPDATE tb_person SET money = money + 100 WHERE NAME = '李四';

  -- 提交事务

  COMMIT;

窗口B

  -- 设置隔离级别为read uncommited
  SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


  -- 查询隔离级别
  SELECT @@tx_isolation;


  -- 开启事务
  START TRANSACTION;


  -- 查询记录
  SELECT * FROM tb_person;

  -- 提交事务
  COMMIT;

7. 不可重复读的发生和解决

概述

当隔离级别为read uncommitted、read committed,一个事务读取另一个事务中的数据，前后不一致。
解决方案
- 设置隔离级别为repeatable read
代码实现
- 窗口A

-- 设置隔离级别为read uncommited
SET SESSION TRANSACTION ISOLATION LEVEL read committed;

-- 查询隔离级别
SELECT @@tx_isolation;

-- read uncommited : 读未提交，一个事务可以读取到另一个事务中未提交数据

 -- 开启事务

  START TRANSACTION;

  -- 转账业务

  UPDATE tb_person SET money = money - 100 WHERE NAME = '张三';

  UPDATE tb_person SET money = money + 100 WHERE NAME = '李四';

  -- 提交事务

  COMMIT;

窗口B


  -- 设置隔离级别为read uncommited
  SET SESSION TRANSACTION ISOLATION LEVEL read committed;


  -- 查询隔离级别
  SELECT @@tx_isolation;


  -- 开启事务
  START TRANSACTION;


  -- 查询记录
  SELECT * FROM tb_person;


  -- 提交事务
  COMMIT;

8. 幻读的发生和解决

概述

当隔离级别为read uncommitted、read committed、repeatable read时，一个事务先查询是否有指定记录，结果为没有，但是添加时又显示有该条记录。
解决方案
- 设置隔离级别为serializable
代码实现
- 窗口A

-- 设置隔离级别为read uncommited
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE  READ;

-- 查询隔离级别
SELECT @@tx_isolation;

-- read uncommited : 读未提交，一个事务可以读取到另一个事务中未提交数据

  -- 开启事务

  START TRANSACTION;

  -- 添加记录

  INSERT INTO tb_person VALUES(5,'小明',10000);

  -- 提交事务

  COMMIT;

窗口B

  -- 设置隔离级别为read uncommited
  SET SESSION TRANSACTION ISOLATION LEVEL  REPEATABLE READ ;

  -- 查询隔离级别
  SELECT @@tx_isolation;

  -- 开启事务
  START TRANSACTION;

  -- 查询记录
  SELECT * FROM tb_person;

  INSERT INTO tb_person VALUES(5,'郑飞宇',10000);

  -- 提交事务
  COMMIT;

9. 数据库的设计范式

概述

设计数据库时，需要遵循的一些规范。要遵循后边的范式要求，必须先遵循前边的所有范式要求设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式，各种范式呈递次规范，越高的范式数据库冗余越小

分类

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

一类范式
- 表结构中，字段具有不可再分性
二类范式
- 表结构中，消除部分函数依赖
- 几个概念：
  
  函数依赖：A–>B,如果通过A属性(属性组)的值，可以确定唯一B属性的值。则称B依赖于A 例如：学号–>姓名。（学号，课程名称） --> 分数
  - 完全函数依赖：A–>B, 如果A是一个属性组，则B属性值得确定需要依赖于A属性组中所有的属性值。例如：（学号，课程名称） --> 分数
  - 部分函数依赖：A–>B,如果A是一个属性组,则B属性值得确定只需要依赖于A属性组中某一些值即可。例如：（学号，课程名称） – > 姓名
  - 传递函数依赖：A–>B, B – >C . 如果通过A属性(属性组)的值，可以确定唯一B属性的值，在通过B属性（属性组）的值可以确定唯一C属性的值，则称 C 传递函数依赖于A 例如：学号–>系名，系名–>系主任
  码：如果在一张表中，一个属性或属性组，被其他所有属性所完全函数依赖，则称这个属性(属性组)为该表的码例如：该表中码设置为（学号，课程名称）
  - 主属性：码属性组中的所有属性
  - 非主属性：除码属性组以外属性
三类范式
- 表结构中，在2NF基础上，消除传递函数依赖

缘灬小太阳

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql基础入门篇（二）

一、约束1.什么是约束？概念：对表中的数据进行限定，保证数据的正确性、有效性和完整性。2.约束的分类主键约束：primary key非空约束：not null唯一约束：unique外键约束：foreign key2.1.非空约束not null，某一列的值不能为null,如果在添加数据的时候为null则添加不进去`1. 创建表时添加约束 CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NULL -- name为非
复制链接

扫一扫

专栏目录