目录
一.外键关联表中数据的删除方式
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分页进行取出结果。