MySQL知识总结

筑梦小子

已于 2022-06-18 10:27:47 修改

阅读量789

点赞数 1

文章标签： mysql 数据库 database

于 2022-05-08 16:14:04 首次发布

本文链接：https://blog.csdn.net/weixin_47651920/article/details/124648029

版权

四.MYSQL中对数据库数据进行备份的操作

1.语法

2.示例

五.Mysql关于各种排序问题（不使用窗口函数）

1.普通排序

2.重复有间隔的排序

3.重复无间隔的排序

一.外键关联表中数据的删除方式

1.在数据库中使用命令来关闭/打开外键约束（硬删除）

mysql中的命令如下：

set foreign_key_checks = 0; -- 关闭外键约束
#关闭外键后，就可以使用删除的语句对数据进行正常删除
set foreign_key_checks = 1; -- 打开外键约束

2.在建表的时候进行级联操作（硬删除）

（1）语法格式

foreign key(需要设置外键的字段)

references 需要外键关联的表名(需要关联的字段)

on delete cascade 级联删除
on update cascade 级联更新

（2）示例

-- 学生表
create table stu (
    id int primary key auto_increment,
    name varchar(20)
);

insert into
    stu
values
(1, 'xiaoli'),
    (2, 'xiaohei');

-- 成绩表（其中包含外键约束）
create table sco (
    id int primary key auto_increment,
    stu_id int,
    score decimal,
    foreign key(stu_id) references stu(id) on delete cascade
);

insert into
    sco
values
(null, 1, 69),
(null, 2, 80);

-- 验证带有外键约束的数据是否可以删除
delete from
    sco
where
    stu_id = 1;

3.使用软删除的方法

软删除：相当于是在删除数据的时候，并不是直接删除，而是将数据标志为删除。例如在表上创建一个删除字段isDelete,默认为fasle, 当删除一个数据的时候，只是将该字段设置为true即可，之后对于外部隐藏该数据即可，但是在数据库中该数据还是存在的。

二.提高数据库库查询效率的方式总结

1.建立索引

对于符合建立索引条件时，可以建立索引来加快查询效率

2.避免使用效率低的查询语句

（1）在查询语句时尽量指定查询的列，避免使用select * from ....

（2）如果需要连接两个表的时候，如果查询的结果集没有重复元素，则可以使用union all（不进行去重）代替 union(带有去重，效率较低)。

（3）排序的字段没有索引的话，尽量少进行排序。

（4）可以使用between 尽量少使用 in。

（5）对于关联表的时候，能不使用left/right join 就少使用，尽量使用inner join 。

因为inner join 会自动以数据少的表作为驱动表；而left join 是以左表进行驱动表，如果左表数据较多的时候，这时候效率就会降低。

（6）尽量少使用模糊匹配，如‘%abc’。

三.数据库事务的相关知识

1.事务的四大特性

原子性：一组操作，要么全部成功，要么全部失败。

持久性：数据提交之后，会数据会持久化到硬盘中

一致性：一个事务里面，多次查询到的结果是一致的。

隔离性（和并发有关）：不同事务对数据的操作是相互隔离的，在进行提交或回滚之前，修改的数据之后字节能够看到。

2.并发执行事务时产生的问题

（1）脏读

如果一个事务A对数据进行修改，但是最后发生了回滚（rollback），在这个期间，事务B获取到了事务A修改的数据，这时候就出现了脏读。

总结：一个事务读取到另一个事务未提交的数据就是脏读。

解决脏读的方法：进行写加锁

（2）幻读

事务A在前后读取两次数据的时候，在这个中间过程的时候事务B增加了数据，导致两次读取到的数据不同，这时候就是幻读。

总结：两次读取同一范围的数据，产生的结果不同，就是幻读。

解决方法：进行串行化操作

（3）不可重复读

事务A对数据读取两次的过程中，事务B对数据进行修改，导致事务A两次读取到的数据不一致，导致的结果就是不可重复读。

总结：一个事务，两次读取到的数据不一致，就是不可重复读。

解决方法：进行读加锁。

3.MYSQL事务的四种隔离级别

（1）read uncommitted （读未提交）：允许读取未提交的数据。会导致脏读。

（2）read committed（读已提交）: 只允许读取提交后的数据。解决了脏读，但是存在不可重复度。

（3）repeatable read（可重复读）:读写都加了锁，解决了脏读和不可重复读，但是会出现幻读。

（4）serializable（串行化）:串行化执行，解决了上面所有问题。

四.MYSQL中对数据库数据进行备份的操作

1.语法

mysqldump -u[用户名] -p[密码] 数据库名称 > 文件路径+文件名

mysqldump -uroot -p123456 test>e:/mysqltest/test.dump

2.示例

五.Mysql关于各种排序问题（不使用窗口函数）

1000，2000，2000，3000，4000

1.普通排序

排序为1，2，3，4，5

使用一个变量来记录排序的次序，之后使用order by 即可

表数据为：

create table emp(
id int primary key auto_increment,
salary mediumint
)
insert into emp(salary) values(2000),(1000),(2000),(3000),(4000);

查询并排序的语句

-- 查询并排序的语句
set @rank=0;
select 
salary,
@rank:=@rank+1 as rank
from emp
order by salary desc;

查询结果：

2.重复有间隔的排序

排序为1，2，2，4，5

-- p变量保存上一次排好名的数值，然后与当前数值进行比较，如果想等，就将上次排名赋值给当前排名
-- 不想等时，就修改p的值为当前数值，在比较过程中z变量一直变化，当相等的处理完，rank就变为了z的值
SET @rank = 0 ,@p = NULL ,@z = 1;
SELECT
	salary,
	rank
FROM
	(
		SELECT
			salary,
			@rank := IF (@p = salary ,@rank ,@z) rank,
		    @p := salary,
		    @z :=@z + 1
	    FROM
		    emp
	    ORDER BY
		    salary
	) tmp;

3.重复无间隔的排序

排序为1，2，2，3，4

-- 相当于是先使用了一个临时变量保存salary,如果和保存的salary相等，说明和上一个排名相同，就显示上一个排名
-- 如果和上一个salary不同，那么就修改这个临时变量（:= 赋值操作）,然后排名+1
SET @rank=0,@p=null;
SELECT
salary,
CASE WHEN @p=salary THEN @rank
WHEN @p:=salary THEN @rank:=@rank+1 END AS rank
FROM emp
ORDER BY salary;

如果需要查询该分组的第几名信息，可以先进行分组，然后再进行排序，最后通过limit分页进行取出结果。

筑梦小子

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL知识总结

目录一.外键关联表中数据的删除方式2.在建表的时候进行级联操作（硬删除）（1）语法格式（2）示例3.使用软删除的方法二.提高数据库库查询效率的方式总结三.数据库事务的相关知识1.事务的四大特性2.并发执行事务时产生的问题（1）脏读（2）幻读（3）不可重复读3.MYSQL事务的四种隔离级别一.外键关联表中数据的删除方式1.在数据库中使用命令来关闭/打开外键约束（硬删除）mysql中的命令如下：set foreign_key_chec
复制链接

扫一扫