- 微信
- 微博
  
  分享文章到微博
- 复制链接
  
  复制链接到剪贴板

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

周棋洛发表于 2022/05/27 22:46:48 2022/05/27

【摘要】作者🙈：周棋洛简介☘️：大二计算机专业学生概要⛽：MySQL高级，视图，索引…… 上篇⬆️：三篇学会MySQL数据库【查询】前言：前天和大家分享了MySQL的搜索，今天是万字教程...

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

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

全文思维导图

整理了思维导图，方便小伙伴下载无水印思维导图，方便后期的复习

表介绍 🍉

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

grade年级表

CREATE TABLE `grade` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `grade_name` varchar(100) DEFAULT NULL COMMENT '班级名字',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8

  
 
  1
  2
  3
  4
  5

grade表数据

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8

stus表结构

CREATE TABLE `stus` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `gradeId` int(11) DEFAULT NULL,
  `NAME` varchar(100) NOT NULL,
  `age` int(3) DEFAULT NULL,
  `bir` date DEFAULT NULL,
  `english` int(3) DEFAULT NULL,
  `chinese` int(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_stu_grage` (`gradeId`),
  CONSTRAINT `fk_stu_grage` FOREIGN KEY (`gradeId`) REFERENCES `grade` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12

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 |
+----+---------+--------------+------+------------+---------+---------+

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

视图 🍎

思维导图 📌

视图概念

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

为什么使用视图？

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

视图的使用规则和限制

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

视图操作

创建视图

语法

create view 视图名 
as 
查询语句;

  
 
  1
  2
  3

mysql> create view zhouql_grade_id as select gradeId from stus where name = "周棋洛";

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8

查看创建视图语句

语法

show create view 视图名;

  
 
  1

mysql> show create view zhouql_grade_id;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| View            | Create View                                                                                                                                                                                                  | character_set_client | collation_connection |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+
| zhouql_grade_id | CREATE ALGORITHM=UNDEFINED DEFINER=`skip-grants user`@`skip-grants host` SQL SECURITY DEFINER VIEW `zhouql_grade_id` AS select `stus`.`gradeId` AS `gradeId` from `stus` where (`stus`.`NAME` = '周棋洛')    | utf8                 | utf8_general_ci      |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+

  
 
  1
  2
  3
  4
  5
  6

更新视图

1. 直接删除DROP再CREATE

直接删除视图，再创建

2. CREATE OR REPLACE VIEW

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

删除视图

语法

drop view 视图名;

  
 
  1

1. 直接删除

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

  
 
  1
  2

2. 判断存在再删除

mysql> drop view if exists zhouql_grade_id;
Query OK, 0 rows affected, 1 warning (0.00 sec)

  
 
  1
  2

利用视图简化复杂的联结

如果要查询学生周棋洛在几班，首先要查询学生表 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   |
+--------------+
| 高三一班     |
+--------------+

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

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

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

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

  
 
  1
  2
  3
  4
  5
  6

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

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

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

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

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   |
+-----------+--------------+
| 张郁苗    | 高三二班     |
+-----------+--------------+

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

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

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

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

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

验证视图是动态的虚拟表

可以简单验证一下视图就是本身是不存数据的，它其实存的就是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 |
+----+---------+--------------+------+------------+---------+---------+
5 rows in set (0.00 sec)

mysql> insert into stus(gradeId,name,age,bir,english,chinese)
    -> values(2,"小爱同学",5,"2012-05-23",100,100);
Query OK, 1 row 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 |
+----+---------+--------------+------+------------+---------+---------+
6 rows in set (0.00 sec)

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41

总结

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

存储过程 🍊

思维导图 📌

存储过程概念

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

创建存储过程

语法

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

  
 
  1
  2
  3
  4

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

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

mysql> delimiter ;
mysql>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

这里需要注意，如果是在mysql的命令行创建，得使用指令 delimiter 自定义结束符 修改mysql默认的结束符 ; 然后再创建完成之后再次使用命令修改回来，不然执行不成功，注意哈，如果使用可视化工具，就不用了

使用存储过程

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

语法

call 存储过程名();

  
 
  1

mysql> call engaverage();
+----------------+
| englishaverage |
+----------------+
|        72.0000 |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9

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

使用参数存储过程

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

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

假如要计算学生英语的最高分，最低分，平均分使用带变量的存储过程完成，如下，这个存储过程接收三个参数，enMin表示英语最低分，enMax表示英语最高分，enAvg表示英语平均分，每个参数必须 指定类型，关键字 OUT 指出相应的参数用来从存储引擎传出一个值，MySQL支持 IN 表示传入，存储过程位于 begin 和 end 语句中，保存到相应的变量通过 into 关键字指定

mysql> delimiter $
mysql> create procedure englishcomputed(
    -> out engMin decimal(6,3),
    -> out engMax decimal(6,3),
    -> out engAvg decimal(6,3)
    -> )
    -> begin
    -> select min(english) into engMin
    -> from stus;
    -> select max(english) into engMax
    -> from stus;
    -> select avg(english) into engAvg
    -> from stus;
    -> end$
Query OK, 0 rows affected (0.03 sec)

mysql> delimiter ;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17

MySQL变量

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

使用带参存储过程

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

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

  
 
  1
  2

查询变量值

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

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

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20

查看存储过程状态

语法

SHOW PROCEDURE STATUS LIKE "存储过程名";

  
 
  1

\G格式化输出

mysql> SHOW PROCEDURE STATUS LIKE "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-26 18:26:13 | 2022-05-26 18:26:13 | DEFINER       |         | utf8                 | utf8_general_ci      | utf8_general_ci    |
+------+-----------------+-----------+-----------------------------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> SHOW PROCEDURE STATUS LIKE "englishcomputed"\G;
*************************** 1. row ***************************
                  Db: db12
                Name: englishcomputed
                Type: PROCEDURE
             Definer: skip-grants user@skip-grants host
            Modified: 2022-05-26 18:26:13
             Created: 2022-05-26 18:26:13
       Security_type: DEFINER
             Comment:
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22

查看创建存储过程语句

语法

SHOW CREATE PROCEDURE 存储过程名;

  
 
  1

\G格式化输出

mysql> SHOW CREATE PROCEDURE englishcomputed;
+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| Procedure       | sql_mode                                                                                  | Create Procedure                                                                                                                                                                                                                                                                                         | character_set_client | collation_connection | Database Collation |
+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
| englishcomputed | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `englishcomputed`(
out engMin decimal(6,3),
out engMax decimal(6,3),
out engAvg decimal(6,3)
)
begin
select min(english) into engMin
from stus;
select max(english) into engMax
from stus;
select avg(english) into engAvg
from stus;
end | utf8                 | utf8_general_ci      | utf8_general_ci    |
+-----------------+-------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE PROCEDURE englishcomputed\G;
*************************** 1. row ***************************
           Procedure: englishcomputed
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`skip-grants user`@`skip-grants host` PROCEDURE `englishcomputed`(
out engMin decimal(6,3),
out engMax decimal(6,3),
out engAvg decimal(6,3)
)
begin
select min(english) into engMin
from stus;
select max(english) into engMax
from stus;
select avg(english) into engAvg
from stus;
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36
  37
  38
  39
  40
  41

删除存储过程

直接删除

语法

drop procedure 存储过程名;

  
 
  1

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

  
 
  1
  2

判断存在再删除

语法

drop procedure if exists 存储过程名;

  
 
  1

mysql> drop procedure if exists engaverage;
Query OK, 0 rows affected, 1 warning (0.00 sec)

  
 
  1
  2

索引 🍏

思维导图 📌

索引概念

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

顺序访问

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

索引访问

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

索引的优缺点

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

优点

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

缺点

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

MySQL索引分类

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

创建索引

语法

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

  
 
  1

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

  
 
  1
  2

查看索引

show index from 数据表;

  
 
  1

删除索引

drop index 索引名 on 表名;

  
 
  1

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

触发器 🍒

思维导图 📌

触发器概念

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

创建触发器

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

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

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

例子：

mysql> create trigger firsttrigger after insert on stus
    -> for each row select '哈哈，我触发器来喽' into @haha;
Query OK, 0 rows affected, 1 warning (0.01 sec)

  
 
  1
  2
  3

create trigge 用来创建名为 firsttrigger 的新触发器，这里使用 on stus 表示作用在 stus 这张表，after insert 表示在插入操作完成之后执行触发器，for each row 表示代码每插入行执行，查询将字符串存入变量，好像是MySQL新的约定，🆗

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

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

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

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

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

删除触发器

直接删除

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

  
 
  1
  2

判断有再删除

mysql> drop trigger if exists firsttrigger;
Query OK, 0 rows affected, 1 warning (0.00 sec)

  
 
  1
  2

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

事务 🍓

思维导图 📌

事务概念

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

MySQL支持多种引擎，并不是所有的引擎都能支持事务，例如常见的 MyISAM 不支持事务，而 InnoDB 是支持事务的，如果你的业务场景需要事务，那么一定要正确使用引擎

几个术语

事务（transaction）

指一组SQL语句

回退（rollback）

指撤销指定SQL语句的过程

提交（commit）

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

保留点（savepoint）

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

开始事务

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

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

  
 
  1
  2

使用rollback

mysql> delimiter $
mysql> delete from stus
    -> where name = "周棋洛";
    -> select * from stus;
    -> rollback;
    -> select * from stus;
    -> $
Query OK, 1 row 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 |
+----+---------+--------------+------+------------+---------+---------+
5 rows in set (0.03 sec)

Query OK, 0 rows 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 |
+----+---------+--------------+------+------------+---------+---------+
6 rows in set (0.05 sec)

mysql> delimiter ;

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35

看上面的语句，因为我是再命令行执行的，因此我需要改一下结束标志，以便于我能写；不立即执行，OK，首先我把名字为周棋洛的删了，查询一下，看到下面打印确实没了，但是我后面还有一个 rollback ，咦，哦，我又滚回来了，再次查询一下，咦，呜，发现删掉的周棋洛又回来了，没了的周棋洛突然攻击我哈哈，这就是事务里的回滚

使用commit

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

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

mysql> delimiter $
mysql> select * from stus;
    -> delete from 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 |
+----+---------+--------------+------+------------+---------+---------+
6 rows in set (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Query OK, 0 rows 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 |
+----+---------+--------------+------+------------+---------+---------+
5 rows in set (0.01 sec)

mysql> delimiter ;
mysql>

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13
  14
  15
  16
  17
  18
  19
  20
  21
  22
  23
  24
  25
  26
  27
  28
  29
  30
  31
  32
  33
  34
  35
  36

使用保留点

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

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

savepoint delete1;

  
 
  1

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

rollback to delete1;

  
 
  1

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

更改默认的提交行为

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

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

  
 
  1
  2

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

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

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

  
 
  1
  2

安全管理 🐣

思维导图 📌

访问控制

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

管理用户

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

1.登录MySQL

2.使用mysql数据库

mysql> use mysql;
Database changed

  
 
  1
  2

3.查看用户

mysql> select user from user;
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
3 rows in set (0.04 sec)

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9

创建用户账号

使用create user语句，创建账号

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

create user sen identified by '123456';

  
 
  1

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

flush privileges;

  
 
  1

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

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

  
 
  1
  2

删除用户账号

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

  
 
  1
  2

设置访问权限

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

为了看到用户账号的权限，可以使用 show grants for 用户账号;

mysql> show grants for liusen;
+------------------------------------+
| Grants for liusen@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'liusen'@'%' |
+------------------------------------+
1 row in set (0.03 sec)

  
 
  1
  2
  3
  4
  5
  6
  7

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

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

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

mysql> show grants for liusen;
+------------------------------------------+
| Grants for liusen@%                      |
+------------------------------------------+
| GRANT USAGE ON *.* TO 'liusen'@'%'       |
| GRANT SELECT ON `db12`.* TO 'liusen'@'%' |
+------------------------------------------+
2 rows in set (0.00 sec)

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11

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

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

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

mysql> show grants for liusen;
+------------------------------------+
| Grants for liusen@%                |
+------------------------------------+
| GRANT USAGE ON *.* TO 'liusen'@'%' |
+------------------------------------+
1 row in set (0.00 sec)

  
 
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10

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

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

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

更改密码

如果用户想要更改密码，可以使用 set password 语句

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

  
 
  1
  2

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

数据库备份与还原 🥔

思维导图 📌

备份

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

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

备份一个数据库

语法

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

  
 
  1

示例

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

  
 
  1
  2

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

很好

备份多个数据库

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

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

  
 
  1

示例

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

  
 
  1
  2

备份所有数据库

语法

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

  
 
  1

示例

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

  
 
  1
  2

大家看，我的所有库加起来有接近6mb呢，哈哈

备份一张表

语法

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

  
 
  1

示例

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

  
 
  1
  2

备份多张表

语法

注意空格

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

  
 
  1

示例

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

  
 
  1
  2

恢复

1. 使用mysqldump指令

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

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

  
 
  1

2. source命令

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

语法

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

  
 
  1

性能(了解) 🍍

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

尾 🥝

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

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

文章来源: blog.csdn.net，作者：周棋洛ყ ᥱ ᥉，版权归原作者所有，如需转载，请联系作者。

原文链接：blog.csdn.net/m0_53321320/article/details/125001030

点赞
收藏
关注作者

0/1000

抱歉，系统识别当前为高风险访问，暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称，即可参与社区互动！

*长度不超过10个汉字或20个英文字符，设置后3个月内不可修改。

确认取消

加入云驻计划，成为创作者

华为云周边好礼
免费体验产品
特殊身份标识
线下官方门票
内部专家零距离
与10000+优质创作者共同成长

立即加入

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

文章目录

全文思维导图

表介绍 🍉

视图 🍎

思维导图 📌

视图概念

为什么使用视图 ？

视图的使用规则和限制

视图操作

创建视图

查看创建视图语句

更新视图

1. 直接删除DROP再CREATE

2. CREATE OR REPLACE VIEW

删除视图

1. 直接删除

2. 判断存在再删除

利用视图简化复杂的联结

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

验证视图是动态的虚拟表

总结

存储过程 🍊

思维导图 📌

存储过程概念

创建存储过程

使用存储过程

使用参数存储过程

MySQL变量

使用带参存储过程

查看存储过程状态

查看创建存储过程语句

删除存储过程

直接删除

判断存在再删除

索引 🍏

思维导图 📌

索引概念

顺序访问

索引访问

索引的优缺点

优点

缺点

MySQL索引分类

创建索引

查看索引

删除索引

触发器 🍒

思维导图 📌

触发器概念

创建触发器

删除触发器

直接删除

判断有再删除

事务 🍓

思维导图 📌

事务概念

几个术语

事务（transaction）

回退（rollback）

提交（commit）

保留点（savepoint）

开始事务

使用rollback

使用commit

使用保留点

更改默认的提交行为

安全管理 🐣

思维导图 📌

访问控制

管理用户

创建用户账号

删除用户账号

设置访问权限

更改密码

数据库备份与还原 🥔

思维导图 📌

备份

备份一个数据库

备份多个数据库

为什么使用视图？