三篇学会MySQL数据库【高级部分】

作者🙈：周棋洛
简介☘️：大二计算机专业学生
概要⛽：MySQL高级，视图，索引……
上篇⬆️：三篇学会MySQL数据库【查询】

在这里插入图片描述

前言： 前天和大家分享了MySQL的搜索，今天是万字教程的最后一篇高级部分，本文会讲解视图，存储过程，索引，触发器，事务，安全管理，数据库备份与还原以及性能等知识，全文24000字，干货满满，我还准备了全文思维导图供大家梳理思路或者下载复习使用，快来学习吧！如果觉得还不错，还请点赞，收藏，关注支持我呀，如果发现文章有写错的部分，欢迎在评论区留言⌨️

文章目录

在这里插入图片描述

全文思维导图

整理了思维导图，方便小伙伴下载无水印思维导图，方便后期的复习
在这里插入图片描述

在这里插入图片描述

表介绍 🍉

学习之前，先说一下使用的表结构，以及相关数据

grade年级表

CREATETABLE`grade`(`id`int(11)NOTNULLAUTO_INCREMENT,`grade_name`varchar(100)DEFAULTNULLCOMMENT'班级名字',PRIMARYKEY(`id`))ENGINE=InnoDBAUTO_INCREMENT=4DEFAULTCHARSET=utf8

grade表数据

mysql>select*from grade;+----+--------------+| id | grade_name   |+----+--------------+|1| 高三一班     ||2| 高三二班     ||3| 高一二班     |+----+--------------+

stus表结构

CREATETABLE`stus`(`id`int(11)NOTNULLAUTO_INCREMENT,`gradeId`int(11)DEFAULTNULL,`NAME`varchar(100)NOTNULL,`age`int(3)DEFAULTNULL,`bir`dateDEFAULTNULL,`english`int(3)DEFAULTNULL,`chinese`int(3)DEFAULTNULL,PRIMARYKEY(`id`),KEY`fk_stu_grage`(`gradeId`),CONSTRAINT`fk_stu_grage`FOREIGNKEY(`gradeId`)REFERENCES`grade`(`id`))ENGINE=InnoDBAUTO_INCREMENT=6DEFAULTCHARSET=utf8

stus表数据

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+

在这里插入图片描述

视图 🍎

思维导图 📌

在这里插入图片描述

视图概念

视图是虚拟的表，与包含数据的表不同，视图本身不包含数据，视图只包含使用时动态检索出的数据，如果你创建了复杂的视图或嵌套了视图，可能会发现性能严重下降

为什么使用视图？

为了保障数据安全性，提高查询效率

视图的使用规则和限制

与表一样，视图必须唯一命名
视图可以嵌套，从视图检索数据构造新的视图
有足够权限，才可以创建视图
对于视图创建的数量没有限制
视图不可以索引，触发器和默认值
视图可以和表一起使用

视图操作

创建视图

语法

createview 视图名 
as 
查询语句;

mysql>createview zhouql_grade_id asselect gradeId from stus where name ="周棋洛";

mysql>select*from zhouql_grade_id;+---------+| gradeId |+---------+|1|+---------+

查看创建视图语句

语法

showcreateview 视图名;

mysql>showcreateview zhouql_grade_id;+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+|View|CreateView| character_set_client | collation_connection |+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+| zhouql_grade_id |CREATEALGORITHM=UNDEFINEDDEFINER=`skip-grants user`@`skip-grants host`SQL SECURITY DEFINERVIEW`zhouql_grade_id`ASselect`stus`.`gradeId`AS`gradeId`from`stus`where(`stus`.`NAME`='周棋洛')| utf8                 | utf8_general_ci      |+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

更新视图

1. 直接删除DROP再CREATE

直接删除视图，再创建

2. CREATE OR REPLACE VIEW

mysql>CREATEORREPLACEVIEW zhouql_grade_id
    ->as->select gradeId from stus
    ->where name ="张郁苗";
    
mysql>select*from zhouql_grade_id;+---------+| gradeId |+---------+|2|+---------+

删除视图

语法

dropview 视图名;

1. 直接删除

mysql>dropview zhouql_grade_id;
Query OK,0rows affected (0.00 sec)

2. 判断存在再删除

mysql>dropviewifexists zhouql_grade_id;
Query OK,0rows affected,1 warning (0.00 sec)

利用视图简化复杂的联结

如果要查询学生周棋洛在几班，首先要查询学生表 stus 中周棋洛的班级id，在去班级表 grade 中查询相应 id 对应的班级，sql语句如下

mysql>select gradeId from stus where name ="周棋洛";+---------+| gradeId |+---------+|1|+---------+

mysql>select grade_name from grade where id =1;+--------------+| grade_name   |+--------------+| 高三一班     |+--------------+

经过两条SQL语句，最终得出周棋洛同学是高三一班的

为了方便点，人们想把两条SQL语句合并为一条SQL，于是子查询来了，万物皆可嵌套，语句如下

mysql>select grade_name from grade where id =(select gradeId from stus where name ="周棋洛");+--------------+| grade_name   |+--------------+| 高三一班     |+--------------+

视图最常见的应用之一就是隐藏复杂的SQL，比如复杂的联结查询

1.创建视图 gradeName，该视图使用隐式内连接查询学生表的班级id与班级表的id相同的数据

mysql>createview gradeName
    ->as->select stus.name,stus.gradeId,grade.grade_name from stus,grade
    ->where stus.gradeId = grade.id;
Query OK,0rows affected (0.03 sec)

mysql>select*from gradeName;+--------------+---------+--------------+| name         | gradeId | grade_name   |+--------------+---------+--------------+| 周棋洛       |1| 高三一班     || 张郁苗       |2| 高三二班     || 小猪佩奇     |1| 高三一班     || 猪爸爸       |3| 高一二班     || 猪妈妈       |3| 高一二班     |+--------------+---------+--------------+5rowsinset(0.03 sec)

2.现在有了视图 gradeName 这张虚拟表作为中间表，想查询任意一个学生是几班的就非常舒服了，语句如下，你 get 到了吗 🍉

mysql>select name,grade_name
    ->from gradeName
    ->where name ="周棋洛";+-----------+--------------+| name      | grade_name   |+-----------+--------------+| 周棋洛    | 高三一班     |+-----------+--------------+

mysql>select name,grade_name
    ->from gradeName
    ->where name ="张郁苗";+-----------+--------------+| name      | grade_name   |+-----------+--------------+| 张郁苗    | 高三二班     |+-----------+--------------+

利用视图格式化检索出的数据

创建一个视图 stuNameAndAge，要求有一个字段 name_age，值是 stus 表中查询出的学生姓名加年龄，并按照年龄的降序排列，SQL如下

mysql>createview stuNameAndAge
    ->as->select concat(rtrim(name),rtrim(age))as name_age
    ->from stus
    ->orderby age;
Query OK,0rows affected (0.03 sec)

mysql>select*from stuNameAndAge;+---------------+| name_age      |+---------------+| 小猪佩奇6|| 猪妈妈7|| 猪爸爸8|| 周棋洛18|| 张郁苗18|+---------------+

验证视图是动态的虚拟表

可以

简单验证

一下视图就是本身是不存数据的，它其实存的就是SQL语句，在你使用视图时，它会执行该视图的SQL来动态的查询获取最新数据，可以向学生表添加一条记录，此时的stus表已经改变，再去查一下视图有没有随之更新，实验证明，

视图就是动态虚拟表

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+5rowsinset(0.00 sec)

mysql>insertinto stus(gradeId,name,age,bir,english,chinese)->values(2,"小爱同学",5,"2012-05-23",100,100);
Query OK,1row affected (0.04 sec)

mysql>select*from stus;+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

mysql>select*from stuNameAndAge;+---------------+| name_age      |+---------------+| 小爱同学5|| 小猪佩奇6|| 猪妈妈7|| 猪爸爸8|| 周棋洛18|| 张郁苗18|+---------------+6rowsinset(0.00 sec)

总结

视图是虚拟的表
一般将视图用来检索(select语句)而不用于更新(insert,update,delete)

在这里插入图片描述

存储过程 🍊

思维导图 📌

在这里插入图片描述

存储过程概念

MySQL5添加了对存储过程的支持，所以想要使用存储过程，先要保证版本大于等于5，简单说存储过程就是为以后的使用而保存的一条或多条MySQL语句的集合，可把它看成批处理，但是它的作用不仅仅是批处理

创建存储过程

语法

createprocedure 存储过程名()begin
    语句集合;end;

例如创建一个存储过程 engaverage，用来计算学生表的英语成绩平均分，语句如下:

mysql>delimiter//
mysql>createprocedure engaverage()->begin->selectavg(english)as englishaverage
    ->from stus;->end//
Query OK,0rows affected (0.06 sec)

mysql>delimiter;
mysql>

这里需要注意，如果是在mysql的命令行创建，得使用指令

delimiter 自定义结束符

修改mysql默认的结束符 ; 然后再创建完成之后再次使用命令修改回来，不然执行不成功，注意哈，如果使用可视化工具，就不用了

使用存储过程

MySQL称存储过程的执行为调用，因此MySQL执行存储过程的关键字使用了

call

语法

call 存储过程名();

mysql>call engaverage();+----------------+| englishaverage |+----------------+|72.0000|+----------------+1rowinset(0.00 sec)

Query OK,0rows affected (0.01 sec)

执行成功，计算出了学生的英语平均成绩为72分，还不错

使用参数存储过程

上面演示的是一个简单的存储过程，它只简单显示查询语句的结果，一般存储过程不显示结果，而是把结果返回给你指定的变量

变量（variable） 内存中一个特定区域，用于存储临时数据

假如要计算学生英语的最高分，最低分，平均分使用带变量的存储过程完成，如下，这个存储过程接收

三个参数

，enMin表示英语最低分，enMax表示英语最高分，enAvg表示英语平均分，每个参数必须

指定类型

，关键字

OUT

指出相应的参数用来从存储引擎传出一个值，MySQL支持

IN

表示传入，存储过程位于

begin 和 end

语句中，保存到相应的变量通过

into

关键字指定

mysql>delimiter $
mysql>createprocedure englishcomputed(->out engMin decimal(6,3),->out engMax decimal(6,3),->out engAvg decimal(6,3)->)->begin->selectmin(english)into engMin
    ->from stus;->selectmax(english)into engMax
    ->from stus;->selectavg(english)into engAvg
    ->from stus;->end$
Query OK,0rows affected (0.03 sec)

mysql>delimiter;

MySQL变量

MySQL中所有变量都必须以 @ 开始

使用带参存储过程

为调用此存储过程，必须指定3个变量名，因为此存储过程要求3个参数，因此必须正好传递3个参数，不能多也不能少，因此使用3个变量将存储过程计算结果保存下来

mysql>call englishcomputed(@englishMin,@englishMax,@englishAverage);
Query OK,1row affected (0.03 sec)

查询变量值

mysql>select@englishMax;+-------------+|@englishMax|+-------------+|130.000|+-------------+

mysql>select@englishMin;+-------------+|@englishMin|+-------------+|34.000|+-------------+

mysql>select@englishAverage;+-----------------+|@englishAverage|+-----------------+|72.000|+-----------------+

查看存储过程状态

语法

SHOWPROCEDURESTATUSLIKE"存储过程名";

\G格式化输出

mysql>SHOWPROCEDURESTATUSLIKE"englishcomputed";+------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| Db   | Name            |Type|Definer| Modified            | Created             | Security_type |Comment| character_set_client | collation_connection |Database Collation |+------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+| db12 | englishcomputed |PROCEDURE| skip-grants user@skip-grants host |2022-05-2618:26:13|2022-05-2618:26:13|DEFINER|| utf8                 | utf8_general_ci      | utf8_general_ci    |+------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+1rowinset(0.00 sec)

mysql>SHOWPROCEDURESTATUSLIKE"englishcomputed"\G;***************************1.row***************************
                  Db: db12
                Name: englishcomputed
                Type: PROCEDUREDefiner: skip-grants user@skip-grants host
            Modified: 2022-05-2618:26:13
             Created: 2022-05-2618:26:13
       Security_type: DEFINERComment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1rowinset(0.00 sec)

查看创建存储过程语句

语法

SHOWCREATEPROCEDURE 存储过程名;

\G格式化输出

mysql>SHOWCREATEPROCEDURE englishcomputed;+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+|Procedure| sql_mode                                                                                  |CreateProcedure| character_set_client | collation_connection |Database Collation |+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+| englishcomputed | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |CREATEDEFINER=`skip-grants user`@`skip-grants host`PROCEDURE`englishcomputed`(out engMin decimal(6,3),out engMax decimal(6,3),out engAvg decimal(6,3))beginselectmin(english)into engMin
from stus;selectmax(english)into engMax
from stus;selectavg(english)into engAvg
from stus;end| utf8                 | utf8_general_ci      | utf8_general_ci    |+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+1rowinset(0.00 sec)

mysql>SHOWCREATEPROCEDURE englishcomputed\G;***************************1.row***************************Procedure: englishcomputed
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    CreateProcedure: CREATEDEFINER=`skip-grants user`@`skip-grants host`PROCEDURE`englishcomputed`(out engMin decimal(6,3),out engMax decimal(6,3),out engAvg decimal(6,3))beginselectmin(english)into engMin
from stus;selectmax(english)into engMax
from stus;selectavg(english)into engAvg
from stus;end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1rowinset(0.00 sec)

删除存储过程

直接删除

语法

dropprocedure 存储过程名;

mysql>dropprocedure engaverage;
Query OK,0rows affected (0.00 sec)

判断存在再删除

语法

dropprocedureifexists 存储过程名;

mysql>dropprocedureifexists engaverage;
Query OK,0rows affected,1 warning (0.00 sec)

在这里插入图片描述

索引 🍏

思维导图 📌

在这里插入图片描述

索引概念

其实小伙伴数字数据结构的话，其中有个数据结构叫做数组，它就是通过下标索引来查找元素的，很快，但是添加，删除，修改都成了它的泪，MySQL索引也是一样

顺序访问

顺序访问是全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据，顺序访问实现简单，但是当表中有大量数据的时候，效率非常低下。在几千万条数据中查找少量的数据，使用顺序访问方式将会遍历所有的数据，花费了大量时间，显然会影响数据库的查询性能

索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式，使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快速地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

索引的优缺点

世上没有完美的事物，算法里面。想提高查询速度，要付出代码，可能是占用更多内存记录，索引也是一样

优点

通过创建索引能保证数据库表中每一行数据的唯一性
大大加快数据的查询速度，这是使用索引很重要的点
可以给所有列类型设置索引
……

缺点

创建和维护索引需要耗费时间，并且随着数据量的上升，耗时也会增加
索引需要磁盘空间，使用索引，除了数据表占空间外，每个索引还要占一定的数据空间，如果有大量的索引，文件空间将变得超大
当对表里数据增删改时，索引也要动态维护，这会降低数据得维护速度，数据量大可能很致命
……

MySQL索引分类

按数据结构分类可分为：B+tree索引、Hash索引、Full-text索引
按物理存储分类可分为：聚簇索引、二级索引（辅助索引）
按字段特性分类可分为：主键索引、普通索引、前缀索引
按字段个数分类可分为：单列索引、联合索引（复合索引、组合索引）

创建索引

语法

createindex 索引名 on 表名(字段名称(长度));

mysql>createindex stus_index on stus(name);
Query OK,0rows affected (0.07 sec)

查看索引

showindexfrom 数据表;

删除索引

dropindex 索引名 on 表名;

这里没有详细讲解各种索引的创建，以及数据结构深入的东西，后期会在MySQL专栏慢慢详解，索引有好也有坏，把握好什么时候使用很重要

在这里插入图片描述

触发器 🍒

思维导图 📌

在这里插入图片描述

触发器概念

MySQL对触发器支持的版本为5及以上，触发器是MySQL响应插入，更新，删除语句时自动执行的一条SQL语句

创建触发器

想要创建触发器，则需要给出4条信息，1，唯一的触发器名 2，触发器关联的表 3，触发器对插入，删除，更新那些操作起作用， 4，触发器何时执行（前，后）

注意：在创建触发器时，尽量保证每个数据库的触发器名唯一，最然MySQL要求的是在表及别唯一，但是不能保证以后会有变化，好习惯吧！🆗

单词：trigger vt.触发；引起；发动；开动；起动

例子：

mysql>createtrigger firsttrigger afterinserton stus
    ->for each rowselect'哈哈，我触发器来喽'into@haha;
Query OK,0rows affected,1 warning (0.01 sec)

create trigge

用来创建名为

firsttrigger

的新触发器，这里使用

on stus

表示作用在 stus 这张表，

after insert

表示在插入操作完成之后执行触发器，

for each row

表示代码每插入行执行，查询将字符串存入变量，好像是MySQL新的约定，🆗

mysql>insertinto stus(gradeId,name,age,bir,english,chinese)->values(2,"王子",24,"2000-12-12",150,150);
Query OK,1row affected (0.00 sec)

mysql>select@haha;+-----------------------------+|@haha|+-----------------------------+| 哈哈，我触发器来喽          |+-----------------------------+1rowinset(0.00 sec)

插入一条数据，查看变量，已经执行了，🆗

注意：只有表支持触发器，视图和临时表都不支持触发器，因此，每个表最多有6个触发器，增加之前，增加之后，删除之前，删除之后，修改之前，修改之后，如果触发器失败，则语句也会不执行，如果语句失败，将不执行触发器，稳了

删除触发器

直接删除

mysql>droptrigger firsttrigger;
Query OK,0rows affected (0.00 sec)

判断有再删除

mysql>droptriggerifexists firsttrigger;
Query OK,0rows affected,1 warning (0.00 sec)

注意：触发器不能修改或覆盖，如果想修改它，必须先删除它，然后再重新创建它

在这里插入图片描述

事务 🍓

思维导图 📌

在这里插入图片描述

事务概念

事务可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行

MySQL支持多种引擎，并不是所有的引擎都能支持事务，例如常见的

MyISAM

不支持事务，而

InnoDB

是支持事务的，如果你的业务场景需要事务，那么一定要正确使用引擎

几个术语

事务（transaction）

指一组SQL语句

回退（rollback）

指撤销指定SQL语句的过程

提交（commit）

指将未存储的SQL语句结果写入到数据库表

保留点（savepoint）

指事务处理中设置的临时占位符，你可以对它发布回退，这里与回退整个事务处理不同

开始事务

MySQL中使用下面语句标识事务开始

mysql>starttransaction;
Query OK,0rows affected (0.00 sec)

使用rollback

mysql>delimiter $
mysql>deletefrom stus
    ->where name ="周棋洛";->select*from stus;->rollback;->select*from stus;-> $
Query OK,1row affected (0.03 sec)+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+5rowsinset(0.03 sec)

Query OK,0rows affected (0.05 sec)+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.05 sec)

mysql>delimiter;

看上面的语句，因为我是再命令行执行的，因此我需要改一下

结束

标志，以便于我能写；

不立即执行

，OK，首先我把名字为

周棋洛

的删了，查询一下，看到下面打印确实没了，但是我后面还有一个

rollback

，咦，哦，我

又滚回来

了，再次查询一下，咦，呜，发现删掉的周棋洛又回来了，没了的周棋洛突然攻击我哈哈，这就是

事务里的回滚

使用commit

如果你没开启事务，那么默认就是自动提交的，但是一旦你开启事务，就需要手动提交事务，只有这样对于数据的写操作才能真正的执行，提交使用 commit

当 commit 或 rollback 语句执行后，事务还会自动关闭

mysql>delimiter $
mysql>select*from stus;->deletefrom stus
    ->where name ="小爱同学";->commit;->select*from stus;-> $
+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78||6|2| 小爱同学     |5|2012-05-23|100|100|+----+---------+--------------+------+------------+---------+---------+6rowsinset(0.00 sec)

Query OK,1row affected (0.01 sec)

Query OK,0rows affected (0.01 sec)+----+---------+--------------+------+------------+---------+---------+| id | gradeId | NAME         | age  | bir        | english | chinese |+----+---------+--------------+------+------------+---------+---------+|1|1| 周棋洛       |18|2001-06-01|78|NULL||2|2| 张郁苗       |18|2002-11-07|130|120||3|1| 小猪佩奇     |6|2015-06-10|34|NULL||4|3| 猪爸爸       |8|2012-09-12|34|56||5|3| 猪妈妈       |7|2012-09-11|56|78|+----+---------+--------------+------+------------+---------+---------+5rowsinset(0.01 sec)

mysql>delimiter;
mysql>

使用保留点

对于简单的事务，提交和回滚是OK的，但是对于复杂的事物操作，可能需要部分提交或回滚

为了能够回退部分事物，需要再合适的位置防止占位符，这占位符就是保留点，创建保留点，使用语句

savepoint delete1;

上面语句，创建了一个保留点，名字叫做 delete1，任何保留点都必须有标识它的唯一名字，这样在回滚时，才能知道自己到底滚哪里，滚多远

rollbackto delete1;

上面语句意思是，回滚到保留点 delete1 的位置，事物完成，保留点也就销毁了，所以我们无需操心

更改默认的提交行为

默认MySQL行为是自动提交的，假如你想手动提交，你可以使用

mysql>set autocommit=0;
Query OK,0rows affected (0.00 sec)

autocommit 标志决定是否自动提交，如果为0，表示为假，MySQL不自动提交，为真则自动提交

这里只是演示，所以，我再改回来

mysql>set autocommit=1;
Query OK,0rows affected (0.00 sec)

在这里插入图片描述

安全管理 🐣

思维导图 📌

在这里插入图片描述

访问控制

概念：你需要给用户提供他们所需要的访问权，且仅提供他们所需的访问权，这就是所谓访问控制

管理用户

MySQL用户账户和信息存储在名为mysql的数据库中，当我们需要获取所有用户账号列表时，需要直接访问它

1.登录MySQL

2.使用mysql数据库

mysql>use mysql;Database changed

3.查看用户

mysql>selectuserfromuser;+---------------+|user|+---------------+| mysql.session|| mysql.sys     || root          |+---------------+3rowsinset(0.04 sec)

创建用户账号

使用create user语句，创建账号

下面语句创建了用户sen密码为123456

createuser sen identified by'123456';

有可能会报错，解决方法是刷新一下权限表

flush privileges;

重新命名一个用户账号，使用rename user语句

mysql>renameuser sen to liusen;
Query OK,0rows affected (0.00 sec)

删除用户账号

mysql>dropuser liu;
Query OK,0rows affected (0.00 sec)

设置访问权限

创建完用户账号之后，接着就要分配访问权限，新创建的用户账号没有访问权限，它们可以登录MySQL，但看不到数据，不能执行任何操作

为了看到用户账号的权限，可以使用

show grants for 用户账号;

mysql>show grants for liusen;+------------------------------------+| Grants for liusen@%|+------------------------------------+|GRANTUSAGEON*.*TO'liusen'@'%'|+------------------------------------+1rowinset(0.03 sec)

此结果表示根据没有任何权限，还挺可爱Q

为了设置权限，使用 grant 语句，使用 grant 语句需要以下信息，要授予的权限，被授予访问权限的数据库或表，用户名

mysql>grantselecton db12.*to liusen;
Query OK,0rows affected (0.03 sec)

mysql>show grants for liusen;+------------------------------------------+| Grants for liusen@%|+------------------------------------------+|GRANTUSAGEON*.*TO'liusen'@'%'||GRANTSELECTON`db12`.*TO'liusen'@'%'|+------------------------------------------+2rowsinset(0.00 sec)

上面语句，表示授予用户liusen权限，权限是允许对db12数据库的所有表进行select（读）操作，即授予只读访问权限，然后再查看权限，就看到已经有了

假如，liusen 用户不老实，一秒都查询上千次，我就要收回它的只读权限，怎么做呢？

mysql>revokeselecton db12.*from liusen;
Query OK,0rows affected (0.00 sec)

mysql>show grants for liusen;+------------------------------------+| Grants for liusen@%|+------------------------------------+|GRANTUSAGEON*.*TO'liusen'@'%'|+------------------------------------+1rowinset(0.00 sec)

revoke 撤销；取消；废除；使无效

使用revoke关键字，阿哦，可怜的小 liusen🥹

还有很多权限，自行百度了

更改密码

如果用户想要更改密码，可以使用

set password

语句

mysql>set password for liusen = password('666666');
Query OK,0rows affected,1 warning (0.00 sec)

注意，新密码必须传入 password() 函数进行加密

在这里插入图片描述

数据库备份与还原 🥔

思维导图 📌

在这里插入图片描述

备份

mysqldump
命令可以将数据库中的数据备份成一个
文本文件
，表的结构和表中的数据将存储在生成的文本文件中。

注意：使用备份命令时应退出MySQL的登录，如果在MySQL的命令行使用会报错，原因很简单，给你看张图你就懂了

在这里插入图片描述

备份一个数据库

语法

mysqldump -u 用户名 -p 数据库名 > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p db12 > D:/dbdump/db12.sql
Enter password: ****

没报错没提示，打开文件夹low一眼

在这里插入图片描述

很好

备份多个数据库

加上–databases选项，然后在后面同时指定多个数据库
语法

mysqldump -u 用户名 -p --databases 数据库1 数据库2 …… > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p --databases db12 db10 > D:/dbdump/db10db12.sql
Enter password: ****

在这里插入图片描述

备份所有数据库

语法

mysqldump -u 用户名-p -all-databases> 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p --all-databases > D:/dbdump/all.sql
Enter password: ****

在这里插入图片描述
大家看，我的所有库加起来有接近6mb呢，哈哈

备份一张表

语法

mysqldump -u 用户名 -p 数据库名 表名 > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p db12 stus > D:/dbdump/stus.sql
Enter password: ****

在这里插入图片描述

备份多张表

语法

注意空格

mysqldump -u 用户名 -p 数据库名 表名 表名 表名…… > 备份到哪

示例

C:\Windows\System32>mysqldump -uroot -p db12 stus grade > D:/dbdump/two.sql
Enter password: ****

在这里插入图片描述

恢复

1. 使用mysqldump指令

跟备份相似，把 > 反过来就行了

mysqldump -uroot -p --all-databases < D:/dbdump/all.sql

2. source命令

还可以使用source命令，前提是需要先登录MySQL命令行吗，它是再MySQL命令行才可以使用的，还有注意这个命令不要加 ;

语法

source 待还原数据库的磁盘路径

在这里插入图片描述

性能(了解) 🍍

对于学习MySQL，机器的配置无关紧要，但是作为生产环境的MySQL服务器，应该注意
一般来说，关键的生产DBMS应运行在自己专用服务器上
MySQL默认配置，如果你需要更改缓冲区大小，内存分配等等，可以通过show variables 和 show status 查看
使用合适正确的数据类型
索引能改善数据检索性能，确定使用索引不是一件微不足道的小事，需要分析使用的 select 语句以找出重复的 where 和 order by 子句，如果一个简单的where子句返回结果的时间太长，则可以断定其中使用的一个或多个列是需要索引的对象
索引改善检索性能，但损害数据插入，删除，更新的性能，如果有一些表，收集的数据不常被搜索，则在有必要之前不要索引他们
最重要的规则就是，每条规则在某些条件下会被打破

在这里插入图片描述

尾 🥝

恭喜你！三篇学会了MySQL数据库，完结撒花🌸🌸🌺

我想送你几句话，未来的路还很长，MySQL数据库还有很多细节等着你去深究，加油吧！少年 🛫

在这里插入图片描述

标签： mysql 数据库 database

本文转载自: https://blog.csdn.net/m0_53321320/article/details/125001030
版权归原作者 周棋洛ყ ᥱ ᥉ 所有，如有侵权，请联系我们删除。

三篇学会MySQL数据库【高级部分】

文章目录

全文思维导图

表介绍 🍉

视图 🍎

思维导图 📌

视图概念

为什么使用视图 ？

视图的使用规则和限制

视图操作

创建视图

查看创建视图语句

更新视图

1. 直接删除DROP再CREATE

2. CREATE OR REPLACE VIEW

删除视图

1. 直接删除

2. 判断存在再删除

利用视图简化复杂的联结

利用视图格式化检索出的数据

验证视图是动态的虚拟表

总结

存储过程 🍊

思维导图 📌

存储过程概念

创建存储过程

使用存储过程

使用参数存储过程

MySQL变量

使用带参存储过程

查看存储过程状态

查看创建存储过程语句

删除存储过程

直接删除

判断存在再删除

索引 🍏

思维导图 📌

索引概念

顺序访问

索引访问

索引的优缺点

优点

缺点

MySQL索引分类

创建索引

查看索引

删除索引

触发器 🍒

思维导图 📌

触发器概念

创建触发器

删除触发器

直接删除

判断有再删除

事务 🍓

思维导图 📌

事务概念

几个术语

事务（transaction）

回退（rollback）

提交（commit）

保留点（savepoint）

开始事务

使用rollback

使用commit

使用保留点

更改默认的提交行为

安全管理 🐣

思维导图 📌

访问控制

管理用户

创建用户账号

删除用户账号

设置访问权限

更改密码

数据库备份与还原 🥔

思维导图 📌

备份

备份一个数据库

备份多个数据库

为什么使用视图？