MySQL的索引、事务、备份、DCL

最新推荐文章于 2024-10-10 21:13:48 发布

丿BAIKAL巛

最新推荐文章于 2024-10-10 21:13:48 发布

阅读量642

点赞数

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

本文链接：https://blog.csdn.net/Keep__Me/article/details/124316323

版权

索引事务数据库备份权限控制 MySQL

关键词由CSDN通过智能技术生成

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

一、索引

索引如同书本中的目录一样，是在设计表的过程中为表中指定的部分字段加入一个能够快捷查询该数据的目录结构。

索引是一种允许直接访问数据库表中某一数据行的树形结构，为了提高数据库查询效率而生。

索引是独立于表之外的对象。

如果表中创建了索引，则该索引在表空间中所占的内存空间是原有的1.5倍。

索引能够提高数据库的查询速度，但是不可滥用，如果滥用，则内存空间占用较大，数据库的整体运行效率较低。

分类

主键索引
唯一索引
外键索引

主外键，唯一索引在建表前创建

普通索引

普通索引任何时候都能创建

索引使用规则

为经常出现再WHERE子语句中的列创建索引
不要在小表（几百条数据）中加索引
为经常出现在order by，distinct后面的字段创建索引
为经常作为表连接条件的列加索引
不要在经常作为DML（增删改）操作的表上加索引【会降低效率】
不要在小表中加索引
不要在不同值较少的类中加索引【例如：性别、部门】

创建普通索引：

方式一：

create index 索引名 on 表名（字段，字段，…）;

方式二：

create table 表名(

字段数据类型约束，

index [indexName] ( 字段 )

)

删除普通索引：

drop index 索引名 on 表名;

alter table 表名 drop index 索引名；

查询表中所有的索引信息：

show index from 表名;

主外键，唯一索引创建方式：

方式一：

create table 表名（

id int primary key , – 主键索引

name varchar(20) unique, – 唯一索引

did int，

foreign key(did) references 主表(主键) — 外键索引

）

方式二：

alter table 表名 add primary key(字段)

alter table 表名 add unique index_name (字段)

alter table 表名 add index index_name(字段)

删除主键索引

alter table 表名 drop primary key;

删除外键索引：

alter table 表名 drop foreign key 外键索引名;

二、事务

事务就是在项目如果有一个非常复杂的功能需要执行多个DML语句（增删改）才能真正的完成此功能，那么我们就可以使用事务来管理该多个DML语句。

实现多个DML语句要么全部执行完成则完成功能，要么如果有一个语句没有完成，则全部执行失败回滚原始，功能执行失败。

事务其实就是数据库中用来统一管理多个DML语句的业务逻辑功能。

事务用来管理多条DML语句，一旦事务管理了，则多条DML语句就是一个不可分割的整体，要么所有DML语句执行成功，要么所有DML语句执行失败。

例：

insert into …

update stu set …

delete from stu where …

三条语句全部执行成功，才能够完全的执行成功，并且去改变数据库表中的数据。

如果第三条执行失败，前两条不会改变数据库，则全部执行失败。

特性：多个sql语句看成一个整体，要么全部执行成功，要么全部执行失败。

成功：事务提交

失败：事务回滚

结合现实中的例子来进行事务的理解：假如有一个转帐表【account(name,money)】

去饭店吃饭进行支付宝转账：【小王向饭店付款12元】

1，先从自己的支付宝余额中减12元

2，然后把12元增加到商家的支付宝中

数据库：

转出金钱：update account set money=money-12 where name=‘小王’

转入金钱：update account set money=money+12 where name=‘饭店’

问题：如果转出成功，转入没有成功，转账功能就会出错，转账没有成功。

解决：用事务进行管理

把转出金钱和转入金钱两条修改的SQL语句作为一个整体，如果两条SQL语句都执行成功了，则该功能成功提交；

如果两条SQL语句中有一条执行失败，则该功能就自动实现事务回滚，提交失败。

（如果第二条执行失败，那么第一条也会执行失败，会回到原始状态，会-12元失败）

事务四大特征（ACID）：
Atomicity（原子性）、Consistency（一致性）、Isolation（隔离性）、Durability（持久性）

【面试中会经常问到】

原子性：整个事务中的所有操作是一个不可分割的整体，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。
隔离性：隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆，必须串行化或序列化请求，使得在同一时间仅有一个请求用于同一数据。
持久性：在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

事务管理：mysql中默认的是每一条sql语句都是一个事务，且该事务自动提交。开启事务：begin/start transaction

提交事务：commint

回滚事务：rollback

以下内容了解即可：

查看事务是否默认提交：select @@autocommit;（1：自动提交 0：手动提交）

修改事物的提交方式：set @@autocommit=值;

设置保存点：savapoint 保存点名

回滚到保存点：rollback to 保存点；

-- 事务-----------------------

create table account(
id int primary key auto_increment,
name varchar(20),
money int
);

-- 添加数据
insert into account values(null,'小王',20000);
insert into account values(null,'饭店',1000); 
-- 查询数据
select * from account

-- 模仿支付宝付款【事务管理】

-- 1，开启事务
-- [一旦开启事务，则其后的所有的sql都是事务管理的一部分，如果不提交事务，则sql修改的数据库中的表中的数据都是临时的改变，没有真正的修改该数据库表中的数据，只有提交事务或回滚事务才能终止事务]
begin;
start transaction;-- （两种开启事物的方式）
-- 2,转出12元
update account set money=money-12 where name='小王';
-- （执行后，使用select语句查询后发现数据改了，但是这里只是内存中临时改了，表中的数据并没有改）
-- 3，转入12元
update account set money=money+12 where name='饭店';
-- 4, 处理事务
-- 出错，回滚
rollback;
-- 成功，提交
commit;

-- 模仿一个数据库设计专家的多个操作和保存点
-- 开启事务
start transaction;
-- 9点开始上班，保存点为a
savepoint a;
	-- 开始任务
	insert into account values (null,'王五',2000);
	-- 
	update account set money=money-100 where name='李博文';
-- 10点，保存点b
	savepoint b;
	-- 开始任务
	update account set money=money+10000 where name='李博文';
	delete from account where name='王五';
-- 11点，发现问题，10点之后有个操作错误，需要回滚
select * from account;
	rollback to b;
	commit;

执行完，第2句之后，进行查询，发现数据改变（这只是内存中临时的改变）

以命令行的形式进入数据库，再进行一次查询，发现数据没有改变

在这里插入图片描述

从begin开始，执行一次完整的事务，进行提交，结果如下

在这里插入图片描述

三、数据的备份和恢复

数据的备份就是把数据库中的数据保存到本地的文件中。

数据的恢复就是把本地的数据文件执行到mysql的服务器中。

原因：

1，数据移动

2，数据保存

3，数据的意外损坏

数据的备份

1，方式一：使用命令来实现备份

mysqldump -uroot -p密码数据库>位置文件.sql

mysqldump -uroot -p123456 mysecond > d:/mysecond.sql
-- 执行完，发现d盘中多了一个mysecond.sql文件，则备份成功

2，方式二：使用第三方软件实现备份

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VRha1o1u-1650509647934)(C:\Users\王浩博\Desktop\博文发布\MySQL索引、事务、备份、DCL.assets\image-20220418143630703.png)]$

数据的回复

1，方式一：使用命令

1）登录 mysql -uroot -proot

2）创建一个空的数据库 create database 数据库名;

3）使用该数据库 use 数据库名

4）恢复数据 source 文件名.sql

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Qd8gf1qp-1650509647939)(C:\Users\王浩博\Desktop\博文发布\MySQL索引、事务、备份、DCL.assets\image-20220418144323014.png)]

注意：恢复时必须要用使用命令行生成的备份文件来进行恢复（不同备份方式生成的文件格式不同）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wsbWzc5p-1650509647941)(C:\Users\王浩博\Desktop\博文发布\MySQL索引、事务、备份、DCL.assets\image-20220418144459200.png)]

回复完成后，空数据库里生成了之前的表和数据

2，方式二：使用第三方软件

1）创建一个空数据库

2）右键空数据库来运行sql文件即可

$[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yDwK0ixf-1650509647943)(C:\Users\王浩博\Desktop\博文发布\MySQL索引、事务、备份、DCL.assets\image-20220418144808352.png)]$

四、DCL语言

DCL语言：数据库控制语言，是对数据库中的用户和权限的操作。

首先找到名为mysql的数据库，在其中新建查询，然后进行下面的操作

用户操作

mysql中所有的用户信息都在mysql数据库中的user表中

查询所有的用户信息

select * from user;

查询用户名和主机名信息

select host,user from user;

host主机：

% : 远程登录用户

localhost: 本机登录用户

创建用户：【必须是超级管理员】

create user ‘用户名’@‘主机名’ identified by ’密码‘

修改密码：【必须是超级管理员】

MySQL（5版本）user表中有password字段，所以可以使用

update user set password=password("123") where user="root";

MySQL（8版本）user表中没有password字段，所以需要使用另外的命令

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY  '你的密码'

如果你已经开启了远程访问权限，需要把localhost改成%

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY  '你的密码'

-- 查询所有的用户信息
select * from user

select host,user from user
-- 创建用户
create user 'wangwu'@'localhost' identified by '123'

-- 把用户wangwu的密码改为123456
alter user 'wangwu'@'localhost' identified with mysql_native_password
by '123456'

改完密码后可以在命令行窗口中进行登录测试有没有修改成功

权限操作

权限就是一个用户是否拥有对表操作的能力。

在这里插入图片描述

登录wangwu这个用户后，发现只能看到这一张表，

这是因为没有给wangwu这个用户授予权限

权限列表：

insert权限

select权限

update权限

delete权限

all权限

授予和撤回权限

为用户授予权限：

grant 权限列表名 on 数据库名.表名 to ‘用户名’@‘主机’

表格式：

数据库名.表名

数据库名.*（该数据库下的所有表）

* . *（所有数据库的所有表）

-- 为wangwu用户授予能够查询mysecond下的account表的查询功能
grant select on mysecond.account to 'wangwu'@'localhost';

-- 赋予删除的权限
grant delete on mysecond.account to 'wangwu'@'localhost';

-- 从wangwu中撤销select， delete权限
revoke select,delete on mysecond.account from 'wangwu'@'localhost'

-- 为wangwu用户授予能够查询mysecond下的所有表的查询功能
grant select on mysecond.* to 'wangwu'@'localhost';
-- 从wangwu中撤销select权限
revoke select on mysecond.* from 'wangwu'@'localhost'