MySql学习笔记-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

84 阅读 0 评论 56 点赞

我是靠谱客的博主舒适冬瓜，最近开发中收集的这篇文章主要介绍MySql学习笔记-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

2022.7.22

SQL全称：Structured Query Language 是结构化查询语言，用于访问和处理数据库的标准的计算机语言

SQL的特点

1.具有综合统一性，不同数据库支持的SQL稍有不同

2.非过程化语言

3.语言简洁，用户容易接受

4.以一种语法结构提供两种使用方式

SQL语言基础

语法特点：

1.SQL对关键字的大小写不敏感

2.SQL语句可以以单行或者多行书写，以分号结束

3.SQL的注释：

数据库系统

1.数据库：数据库(DataBase)是一个以某种组织方式存储在磁盘上的数据的集合

2.数据库应用：数据库应用系统是指基于数据库的应用软件例如学生管理系统，购物网站，新媒体平台等等

3.数据库管理系统（数据库软件）

a.关系型数据库(RDBMS):

b.非关系型数据库(NoSQL)

4.SQL和数据库管理系统的关系

a.SQL是一种用于操作数据库的语言，SQL适用于所有关系型数据库

b.MySQL，Oracle,SQLServer是一个数据库软件，这些数据库软件支持标准SQL，也就是通过SQL可以使用这些软件，不过每一个数据库系统会在标准SQL的基础上拓展自己的SQL语法

c.大部分的NoSQL数据库有自己的操作语言，对SQL支持的并不好

5.关系型数据数据库管理系统组成

数据库管理系统(DBMS)主要由数据库和表组成，一个系统可以有很多数据库，每个数据库可以有很多表

MySQL

MySQL的特点：

MySQL的版本：

目前MySQL的最新版本是MySQL8.0

如果之后因为安装MySQL的问题导致某些功能无法实现，到时候再参考：2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程_哔哩哔哩_bilibili

的p7视频重新安装MySQL，两种安装方式都是可以的

MySQL的卸载(需要时再学习）

以下是解压包安装方式删除时需要进行的操作

MySQL常用图形管理工具

我们一般使用mysql图形管理工具来连接Mysql，然后在图形化界面上操作Mysql

MySQL的管理维护工具非常多，除了系统自带的命令行管理工具之外，还有许多其他的图形化管理工具

常用的工具有：

Navicat，这是一套快速，可靠的数据库管理工具，Navicat是以直觉化的图形用户界面而建的，可以兼容多种数据库，支持多种操作系统；

SQLyog，是一个快速而简洁的图形化管理MySQL数据库的工具，它能够在任何地点有效地管理你的数据库，使用SQLyog可以快速直观的让你从世界的任何角落通过网络来维护远端地MySQL数据库；

MySQL Workbench，这是MySQL官方提供地图形化管理工具，分为社区版和商业版，社区版完全免费，而商业版是按年收费，支持数据库的创建，设计，迁移，备份，导出和导入等功能，并且支持Windows,Linux和mac等主流操作系统

DataGrip:这是一款数据库管理客户端工具，方便连接到数据库服务器，执行sql，创建表，创建索引以及导出数据等

其他工具：

综合以上，前两者用的最多

图形化工具连接MySQL

同样也可以在SQLyog中创建连接

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

MySQL数据库基本操作-DDL

DDL(Data Definition Language),数据定义语言，该语言部分包括以下内容：

DDL对数据不进行操作，对装数据的数据结构进行操作

1.对数据库的常用操作

2.对表结构的常用操作-创建表

注意第一行的use的使用，创建表是在指定数据库内进行的

这里面涉及到了数据类型，如int,varchar,date等还有很多，数据类型是指在创建表的时候为表中字段指定数据类型，只有数据符合类型要求才能存储起来，使用数据类型的原则是：够用就行，尽量使用取值范围小的，而不用大的，这样可以更多的节省存储空间

常用的有：1.数值类型；2.日期和时间类型；3.字符串类型

1.数值类型

符号的表示用signed,unsigned

黄色部分是用的较多的

2.字符串类型

3.日期类型

TIMESTAMP会涉及到时区，会自动获取当前时区时间

3.对表结构的常用操作-其他操作

4.对表结构的常用操作-修改表结构格式

A.修改表添加列了解一下，用的不多

B.修改列名和类型

C.修改表删除列

D.修改表名

2022.7.23

MySQL数据库基本操作-DML

1.基本介绍：DML是指数据操作语言，英文全称是Data Manipulation Language,用来对数据库中表的数据记录进行更新

关键字： 1.插入insert 2.删除delete 3.更新upgrade

1. 数据插入

两种格式均可，注意SQL语句以分号结尾，所以SQL语句可以分行写，其次可以在一条insert语句中同时插入多个列表值，values只需要写一次，括号之间用逗号隔开，如图所示：

不一定要求同时对所有列进行赋值，根据自己的需要进行赋值，注意前后列名和属性值对应

2.数据修改

第一种方式会无脑的修改表中对应字段的所有属性值，一般不使用，通常用第二个限制条件where来达成数据修改的目的

where不光可以判等，也可以进行其他逻辑运算

3.数据删除

注意和之前的drop区分，drop适用范围是表和数据库而不涉及其中的数据内容，当需要删除内容时需要用到下面的两个函数

注意当需要出现表达式的时候，应遵从下面的格式：

必须有一个变量来接收表达式的值，一般都是原列名

MySQL约束

何为约束，英文为：constraint，约束实际上就是表中数据的限制条件

作用：表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些列的值(手机号)不能为空，有些列的值(身份证号)不能重复

分类：

1.主键约束：该约束可以加到某个列上也可以加到多个列上

a.添加单列主键：

constraint <约束名> 可以省略不写

主键的作用：

当个一个列设定主键之后，如果之后相同列插入相同的列值就会报错，报途中摘要信息部分的错误

另外，设定NULL也是不可以的，这就是主键约束的特点，主键可以唯一标识一行数据

b.添加多列主键(联合主键)

同样也是不能出现重复的情况的，多个键作为主键时其中的值不能完全相同，允许其中几个相同，但是不能出现NULL即空值

c.通过修改表结构添加主键

d.删除主键约束

删除单列和联合主键都是一样的语法，因为主键只有一个，联合主键也只是多个属性共同组成一个主键

2.自增长约束

结果为：

由此可见自增长的值如果在一开始没有给定的话，默认从1开始，具体如下图所示：

指定自增字段初始值

delete删除数据之后，自增长还是在最后一个值基础上加1而不是直接从1开始，而truncate会从1开始，永远都是从1开始，不会以之前设置的默认值开始

3.非空约束（not null)

MySQL 非空约束指字段的值不能为空，对于使用了非空约束的字段如果用户在添加数据时没有指定值，数据库系统就会报错

以下为方法一的可行与不可行：

单纯的NULL表示为空值，加上引号会变成字符串

4.唯一性约束

唯一约束(Unique Key)是指所有记录中字段的值不能重复出现，例如为id字段加上唯一性约束后，每条记录的id值都是唯一的，不能出现重复的情况，虽然不能重复，但是添加唯一约束的列可以为NULL，因为NULL ！= NULL

注意：在MySQL中NULL和任何值都不相同，甚至和自己都不相同

删除唯一约束语法： alter table <表名> drop index <唯一约束名>; 如果不是使用的第二种方法添加的唯一约束，那么此时的唯一约束名就是列名

重点是方式一

5.默认约束

MySQL默认值约束用来指定某列的默认值

给定的默认值必须符合定义的类型，整型对整型，字符串对字符串

在给定默认值的基础上如果再次insert向表中插入值的话就会覆盖掉原来的默认值

删除默认约束： alter table <表名> change column <字段名> <类型> default null;

6.零填充约束 用的少，了解即可

零填充主要针对数值类型

MySQL数据库基本操作-DQL

概念：

1.数据库管理系统一个重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选以及确定数据以什么样的格式显示

2.MySQL提供了功能强大灵活的语句来实现这些操作，即select语句来查询数据

中括号内的内容可选

在进行基本查询之前，需要进行数据准备，要创建数据库和表：

创建出来的表如下图所示：

首先不能直接上强度，先学会简单查询，就是不带条件的查询，如下图：

注意：from后面跟的是表的名字，表示从哪个表中查询数据，select后面跟的是列的名字，表示要筛选哪些列的数据

查询所有商品时，可以列出每个列的名称，但是当列的名称过多时这样会比较麻烦，于是可以用*代替所有列的名称

关于起别名的这个操作会在之后的多表查询中凸显作用

最后一个可以这么写 select pname,price+10 new_price from product new_price是+10之后的新列名

运算符

数据库中的表结构确立后，表中的数据代表的意义就可以确定，通过MySQL运算符进行运算，就可以获取到表结构以外的另一种数据

例如，学生表中存在一个birth字段，这个字段表示学生的出生年份，而运用MySQL的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据

MySQL支持四种运算符：

1.算术运算符；2.比较运算符；3.逻辑运算符；4.位运算符

1.算术运算符

2.比较运算符

3.逻辑运算符

4.位运算符(了解)

了解完每个运算符的基本内容之后，接下来先学算术运算符：

接下来通过条件查询来熟悉运算符的使用：

如下：

接下来来简单了解一下位运算符，位运算符是在二进制数上进行计算的运算符，位运算会将操作数先变成二进制数，进行位运算，然后再将计算结果从二进制变回十进制数

2022.7.24

1.排序查询

如果我们需要对读取的数据进行排序，就可以使用MySQL的order by子句来设定你想按哪个字段哪种方式来进行排序再返回搜索结果

关于第二个SQL语句，在执行时先判断price才会判断后面的category_id，当price相同时后面的才会按升序排列

2.聚合查询

之前所做的查询都是横向查询，它们都是根据条件一行一行的进行判断，而使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值，另外聚合函数会忽略空值

3.聚合查询的null值处理

4.分组查询

分组查询是指使用group_by子句对查询信息进行分组

group by后面可以跟多个字段

分组之后的条件筛选-having

5.分页查询-limit

第一条是0，索引是从0开始的，因此上面的从第四条开始显示需写入3

6.INSERT INTO SELECT语句

两个表的列必须完全一致

SQL执行顺序：from-->where-->group by-->count()-->having-->select-->order by-->limit

下面是对上面的知识点的小练习：

补充一个知识点，ifnull(sal,0) 如果sal的值为null,则当作0，不为null，则还是原来的值

正则表达式

下面是正则表达式的实操：

MySQL多表操作

多表关系：MySQL多表之间的关系可以概括为：一对一，一对多/多对一，多对多

介绍完表之间可能存在的关系之后，先来学习一下外键约束

外键约束

利用主表的主键来控制从表的外键

特点：

定义一个外键时需要遵守下列规则：

1.主表必须已经存在于数据库中，或者是当前正在创建的表

2.必须为主表定义主键

3.主键不能包含空值，但允许在外键中出现空值，也就是说只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的

4.在主表的表名后面指定列名或列名的组合，这个列或列的组合必须是主表的主键或候选键

5.外键中列的数目必须和主表的主键中列的数目相同

6.外键中列的数据类型必须和主表主键中对应列的数据类型相同

接下来来创建外键试试看

创建从表：

最后一行前两个就是给外键起个名字，之后删除外键的时候直接指定名字删除就行，然后指定外键为哪一个属性，还要写references表示参照主表中的哪个属性，要受其约束

利用MySQL的逆向表到模型的功能，就能看到上面创建的两个表之间的联系：

上面所学的是第一种方式，与之前创建主键一样，也可以在创建完表之后再设定外键：

即如下图所示：

多少有点麻烦，最好还是用第一种

在创建完外键约束之后，那么这个外键约束到底有什么用呢，下面来展开学习：

即便把从表的所有全删了也可以

对于外键的设置是不会影响到对表的查询的，只会对表的增删改会有影响

接下来来了解一下如何删除外键约束

外键约束-多对多关系

中间表有sid,cid两个外键列，有了外键则该表就是从表，因此上面两个表就是主表

主表一定要在从表之前创建

图示如下：

修改和删除时中间从表可以随便删除和修改，但是两边的主表受从表依赖的数据不能删除或者修改

2022.7.25

多表操作之多表联合查询

多表查询就是同时查询两个或两个以上的表，因为有的时候用户在查看数据的时候需要显示的数据来自多张表，多表查询有以下分类：

为了实现多表联合查询，接下来先准备查询数据：

注意：外键约束对多表查询并无影响

---------------------------------------------------------------------------------------------------------------------------------

接下来先进行交叉连接查询：

1.交叉连接查询返回被连接的两个表所有数据行的笛卡尔积

2.笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配

3.假如A表有m行数据，B表有n行数据，则返回m*n行数据

4.笛卡尔积会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

进行查询的结果为下图，存在很多匹配错误的信息：

---------------------------------------------------------------------------------------------------------------------------------

接下来学习一下内连接查询，内连接查询实际上就是求多张表的交集

注意99标准中的inner标准可以不写

查询结果为，肉眼可见的冗余信息少了很多，剩下的都是满足条件的：

---------------------------------------------------------------------------------------------------------------------------------

接下来来学习联合查询中的外连接查询

外连接分为左外连接(left outer join),右外连接(right outer join),满外连接(full outer join).

注意：oracle里面有full join,可是在mysql对full join支持的不好，我们可以使用union来达到目的

注意：outer可以省略，以左外连接为例，左边的数据会全部输出，右边的如果有对应的数据即相交的一部分数据也会输出，剩下的没有对应的部分会补全null

结果如下：

左外连接会把左表的所有元素全部输出(1001-1004),右表若有对应的数据则尽数输出，没有就补null

如果需要写多个表，格式如下：

下面这个是第二条语句的查询结果：

同样的右表全部输出，左边的如果匹配就输出，没有就补null

下面是实现union的结果：

就是将左外和右外两次的结果进行合并，上下合并

union还有一种写法：union all，与union的区别就是union all不会进行去重而仅仅是简单的上下合并，上面是左外连接，下面是右外连接

---------------------------------------------------------------------------------------------------------------------------------

接下来是多表联合查询中的子查询

注意：子查询不一定非得要求是多表

子查询就是指的在一个完整的查询语句之中，嵌套若干个不同功能的小查询，从而一起完成复杂查询的一种编写方式，通俗一点就是包含select嵌套的查询

子查询可以返回的数据类型一共分为四种：

1.单行单列：返回的是一个具体列的内容，可以理解为一个单值数据

2.单行多列：返回一行数据中多个列的内容

3.多行多列：返回多行记录之中同一列的内容，相当于给出了一个操作范围

4.多行多列：查询返回的结果是一张临时表

以下为子查询的实例：

在了解完子查询基本用途之后，来学习一下子查询关键字：

在子查询中，有一些常用的逻辑关键字，这些关键字可以提供更丰富的查询功能，主要关键字如下：

1.ALL；2.ANY;3.SOME;4.IN;5.EXISTS

1.ALL关键字

2.ANY和SOME关键字 SOME用的少，更多的用ANY

3.IN关键字

4.EXISTS关键字相当于一个条件判断语句，成立的话才会执行前面的查询语句

注意这里用到了别名查询

---------------------------------------------------------------------------------------------------------------------------------

多表联合查询-自关联查询

MySQL有时在信息查询时需要进行对表自身进行关联查询，即一张表自己和自己关联，一张表当成多张表来用，注意自关联时表必须给表起别名

接下来进行多表查询的训练;

2022.7.27

MySQL函数

在MySQL中，为了提高代码重用用性和隐藏实现细节，MySQL提供了很多函数，函数可以理解为别人封装好的模板代码

在MySQL中，函数非常多，主要分为以下几种函数：

1.聚合函数；2.数学函数；3.字符串函数；4.日期函数；5.控制流函数；6.窗口函数

1.聚合函数

具体操作如下：

2.数学函数不区分大小写

ceil(x)表示向上取整；floor(x)表示向下取整

round(x,y)中的y表示保留小数位数

常用的就是：绝对值，向上/下取整，求列表最大最小以及最后的四舍五入函数

3.字符串函数

ltrim是去除字符串左边空格，同样有rtrim是去除字符串右边空格

若要直接去除两端的空格，直接调用trim即可

常用的是upper()和lower()

4.日期函数

在第三个函数中存在格式化输出，%Y和%y是不同的

最后一个是日期向前跳转，还有一个是date_add()同样的参数和使用方法，作用是日期向后跳转

expr type可以是day month或者year，会在指定type上面进行相加减

下面这个函数也是和上面的是一个作用

不必死记，待之后用到的话再回来查

5.控制流函数

if逻辑判断语句

case_when逻辑判断语句

case后的表达式可以省略，但是相应的when部分的condition需要变化

6.窗口函数

语法结构如下：

6.1序号函数

序号函数有三个：ROW_NUMBER(),RANK(),DENSE_RANK()，可以用来实现分组排序，并添加序号

row_number()那一行执行完会多出来一列，起个别名叫rn

rank和row_number的排序会有所不同，rn那一列会出现并列且序号不会连续

如果要序号连续，除了row_number(),还有dense_rank()

注意不加partiction的话会进行全局排序，以上面的数据为例，如果没有partiction的话研发部和销售部两个部门的薪资比较会一起进行，如果加上partiction的话会各自再自己的分组里面进行比较

6.2开窗聚合函数 SUM,AVG,MIN,MAX

6.3 分布函数-CUME＿DIST和PERCENT_RANK

partiction_by参数是可以省略的，如果省略就会对整个表进行计算

下面的函数和上面的也差不多，就是计算公式不同

6.4 前后函数-LAG和LEAD

结果如下：

设置默认值的话会自动填入默认值，没有就是null

同样lead也是这种作用：

6.5 头尾函数(first_value和last_value)

结果如下：

6.6 其他函数(NTH_VALUE和NTILE(n))

显示结果如下：

N_TILE(): 括号内的数字是多少就会分成多少组

结果如下：

2022.7.28

MySQL的视图

介绍
1.视图(view)是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用

2.数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中

3.使用视图查询数据时，数据库系统会从原来的表中取出对应的数据，因此，视图中的数据是依赖于原来的表中的数据的，一旦表中的数据发生改变，显示在视图中的数据也会发生改变

作用：

1.简化代码，可以把重复使用的查询封装成视图重复使用，同时可以使复杂的查询易于理解和使用

2.安全原因：如果一张表中有很多数据，很多信息不希望让所有人看到，此时可以使用视图

创建视图

语法格式如下：

首先还是进行数据的准备，创建新的数据库并导入脚本文件，脚本文件其实就是省去了创建表格和导入数据的操作，重点需要掌握创建视图的方法

show full tables可以显示表的类型，如果表是视图类型就会显示view

视图并不是真正存储而只是原表的显示

修改视图

修改视图是指修改数据库中已存在的表的定义，当基本表的某些字段发生改变时，可以通过修改视图来保持视图和基本表之间一致，MySQL中通过create or replace view语句和alter view语句来修改视图

语法格式如下：

更新视图

某些视图是可更新的，也就是说可以再update,delete或insert等语句中使用它们，以更新基表的内容。对于可更新的视图，在视图中的行和基表中的行必须=具有一对一的关系，如果视图包含下属结构中的任何一种，那么它就是不可更新的

视图中虽然可以更新数据，但是有很多的限制，一般情况下，最好将视图作为查询数据的虚拟表，而不要通过视图更新数据，因为使用视图更新数据时，如果没有全面考虑在视图中更新数据的限制，就可能造成数据更新的失败

在视图中插入数据实际上还是在原表中插入数据，可能会出现报错的情况

删除视图和重命名视图

删除视图时只会删除视图的定义而不会删除视图中的数据

如果在日常使用时select会嵌套很多，建议使用视图，边查询边建立视图

2022.7.29

MySQL的存储过程

什么是存储过程：

1.MySQL5.0版本开始支持存储过程

2.简单地说存储过程就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法

3.存储过程就是数据库SQL语言层面的代码封装与重用

有哪些特性：

1.有输入输出参数，可以声明变量，有if/else,case,while等控制语句，通过编写存储过程，可以实现复杂的逻辑功能

2.函数的普遍特性：模块化，封装，代码复用

3.速度快，只有首次执行需经过编译和优化步骤，后续被调用可以直接执行，省去以上步骤

接下来以一个入门案例来具体了解MySQL存储过程

注意自定义结束符号可以是：$$,//,\这三种，一般用两个美元符号作为结束符号

最后的delimiter ；表示之前使用的结束符号为自定义的，现在恢复为原来的分号结束符

调用存储过程使用call关键字，实际上可以看出来存储过程就像是一个函数，使用方法也和函数一样

存储过程中的变量定义：

1.局部变量：指的是用户自定义，在begin/end块中有效

begin中的三行语句分别意思为：1.定义/声明变量；2.给变量赋值；3.输出变量的值

另一种给局部变量赋值的方法如下：

select....into

2.用户变量:指的是用户自定义的变量，当前会话(连接)有效，类比java的成员变量

当连接断开时，用户变量便失去作用

3.系统变量（可以理解为MySQL已经提前定义好的)

1.系统变量又分为全局变量和会话变量

2.全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改

3.会话变量在每次建立一个新的连接的时候，由MySQL来初始化，MySQL会将当前所有全局变量的值复制一份，来作为会话变量

4.也就是说，如果在建立会话之后，没有手动更改过会话变量与全局变量的值，那所有这些变量的值都是一样的

5.全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话(也就是当前的数据库连接)

6.有些系统变量的值是可以利用语句来动态进行更改的，但是有些系统变量的值却是只读的，对于哪些可以更改的系统变量，我们可以利用set语句进行更改

4.存储过程传参-in

in表示传入的参数，可以传入数值或者变量，即使传入变量，并不会更改变量的值，可以内部更改，仅仅作用在函数范围内

相当于C或者Python中的形式参数

下面的例子是传入两个参数的情况：

5.存储过程传参-out

out表示从存储过程内部传值给调用者

上面的begin和end之间表示根据传入的参数来查到对应的ename，然后给out_ename赋值，使用到了into

call()中传入的是对应位置的参数，@o_ename是用来接收返回值的

除了可以一次性传入多个值，也可以同时返回多个值，如下图所示注意call中要有足够的参数来接收返回值

6.存储过程传参-inout

inout表示从外部传入的参数经过修改后可以返回的变量，既可以使用传入变量的值也可以修改变量的值(即使函数执行完)

具体例子如下：

---------------------------------------------------------------------------------------------------------------------------------

MySQL存储过程-流程控制

1.分支语句-if

if语句包含多个条件判断，根据结果为True,False执行语句，与编程语言中的if,else if,else语法类似，其语法格式如下：

例子如下：

2.流程控制-case

case是另一个条件判断的语句，类似于编程语言中的switch语句

3.循环语句

循环是一段在程序中只出现一次但可能会连续运行多次的代码

循环中的代码会运行特定的次数，或者是运行到特定条件成立时结束循环

循环分类：

while,repeat,loop

循环控制

leave类似于break,跳出，结束当前所在的循环

lterate类似于continue，继续，结束本次循环，继续下一次

3.1while循环用的最多

标签可以省去

利用while来循环向表中插入数据

如果加上leave和literate的话需要以下面的格式书写：

leave label表示当满足当前的if条件时便跳出循环，最终结果中不会含有满足该条件的数据

literate就是跳出本次循环，然后进行下一次循环

注意条件控制语句的位置，如果写的位置不对的话可能会造成死循环

3.2 repeat循环

3.3 loop循环

if相当于一个跳出循环的条件判断语句

---------------------------------------------------------------------------------------------------------------------------------

MySQL的存储过程-游标cursor的使用

游标(cursor)是用来存储查询结果集的数据类型，在存储过程和函数中可以使用光标对结果集进行循环的处理，光标的使用包括光标的声明，open,fetch和close

格式：

以实例来演示游标如何使用：

fetch会一次性读取一整行数据，下一次就会读取下一行数据，into后面的表示保存到后面的变量中

在上面的循环中会报出一个错误，错误提示是在fetch过程中遇见了空行即没有数据的行，导致循环提前终止，但是最终结果显示是完全的，表中的数据会尽数输出，针对这种异常，下面来学习异常处理

异常处理-handler句柄

特别注意：在语法中变量声明，游标声明，handler声明是必须按照先后顺序书写的，否则创建存储过程出错

declare flag是定义标记值

定义句柄：定义异常的处理方式

1.continue：继续执行剩余代码；exit:直接终止程序；undo:目前版本已不支持undo

2.触发条件：条件码；条件名（三个）

3.异常出现之后执行什么代码：例如上面 flag=0表示异常出现，为1表示没有异常发生

一般定义完异常处理不会立即执行，只有发生之后才会执行

---------------------------------------------------------------------------------------------------------------------------------MySQL存储函数

记住创建的格式，和存储过程也很类似，只不过多了个return

MySQL触发器

简单理解触发器可以这样来：假设有两张表，向其中一张表中添加数据的同时我希望另一张表也会同步更新数据，这其中的功能通过触发器来实现

介绍

1.触发器，就是一种特殊的存储过程，触发器和存储过程一样是一个能够完成特定功能，存储在数据库服务器上的SQL片段，但是触发器无需调用，当对数据库表中的数据执行DML操作时会自动触发这个SQL片段的执行而无需手动调用

2.在MySQL中，只有执行insert,delete,update操作时才能触发触发器的执行

3.触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

4.使用别名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，现在触发器还只支持行级触发，不支持语句级触发

触发器的特性

1.什么条件会触发：I，D，U

2.什么时候触发：在增删改前或者后

3.触发频率：针对每一行执行

4.触发器定义在表上，附着在表上,一般时附着在表上

操作-创建触发器

格式：

1.创建只有一个执行语句的触发器

for each row表示行级触发

2.创建多个执行语句的触发器

触发器只要被定义之后，每一次调用相应语句之后就会自动执行

操作-NEW和OLD

MySQL中定义了NEW和OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体的：

使用方法：NEW.columnName (columnName为相应数据表某一列名)

使用实例如下：

在写入NEW之后，下面的插入数据会自动匹配concat中的属性，自然会在表中自动出现

注意insert只有NEW，没有OLD

下面是update中的使用：

这样显示出来的是还没有进行update之前的数据，加入原来的password=123456，那么表中显示的数据就还为123456

如果要显示修改之后的值，将OLD改为NEW即可：

这样表中显示的就是000000

下面是delete操作中的OLD：

显示的信息就是删除之前的信息

综上所述：NEW和OLD就是在触发器中获取信息时需要使用到的关键字

操作-其他操作

1.查看触发器：即查看当前所有定义的触发器

2.删除触发器：注意关键字是drop

关于触发器的注意事项：

1.MySQL中触发器中不能对本表进行增删改操作，以免递归循环触发

2.尽量少使用触发器，假设触发器出发每次执行1s，插入500条数据就需要触发500次触发器，光是触发器执行的时间就花费了500s，而insert 500条数据一共是1s，那么这个insert的效率就非常低了

3.触发器是针对每一行的；对增删改非常频繁的表切记不要使用触发器，因为它会非常消耗资源

MySQL的索引

介绍：

索引是通过某种算法，构建出一个数据模型，用于快速找出在某个列中有一特定值的行，不适用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间

索引类似一本书的目录，这样理解会更简单

索引的分类

索引是存储引擎用来快速查找记录的一种数据结构，按照实现的方式类分，主要有Hash索引和B+Tree索引

上面两种形式是根据底层数据结构来划分的

下面按照功能划分，索引划为以下分类：

下面来一个一个学：

索引的操作-创建索引：单列索引-普通索引

介绍：

单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点

格式：

这个是在创建表的时候就指定索引

通过手动查看数据表中的索引可以看到索引已经创建好了，类型是Normal即最普通的索引，索引方法是BTree,一般方法都是Btree

第二种方式是直接创建：

第三种方式是修改表结构来添加索引：

查看索引：

删除索引：

单列索引-唯一索引

介绍：

唯一索引与前面的普通索引类似，不同的就是：索引列的值必须唯一但允许有空值，如果是组合索引，则列值的组合必须唯一

单列索引-主键索引 primary

每张表一般都会有自己的主键，当我们在创建表时，MySQL会自动在主键列上建立一个索引，这就是主键索引，主键是具有唯一性并且不允许为NULL，所以它是一种特殊的唯一索引

组合索引

组合索引也叫复合索引，指的是我们在建立索引的时候使用多个字段，例如同时使用身份证和手机号建立组合索引，同时的可以将组合索引建立为普通索引或者是唯一索引

复合索引的使用符合最左原则

若要变为唯一索引，在index前面加上unique即可

上面的就是最左原则的体现，一定要从()里面的左边开始匹配，第一个缺少一个参数因此索引不起作用

全文索引

做一个了解，用的不多

概述：

1.全文索引的关键字是fulltext

2.全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配

3.用like+%就可以实现模糊匹配了，为什么还要全文索引？

like+%在文本比较少时是合适的，但是对于大量的文本数据检索，是不可想象的，全文索引在大量的数据面前，能比like+%快N倍，速度不是一个数量级但是全文索引可能存在精度问题

全文索引就相当于搜索引擎关键字

最小搜索长度和最大搜索长度是限制全文索引的作用域的

创建全文索引的方法和前面的也如出一辙

或者

插入数据之后接下来来使用全文索引：

空间索引

操作实例：

关键字是spatial key 后面跟名字，然后指定哪一列加空间索引

2022.8.12

目前跳过学习索引的原理，倘若之后的学习涉及到了该部分再回来学习

MySQL索引的特点

索引的优点

1.大大加快数据的查询速度

2.使用分组和排序进行数据查询时，可以显著减少时分组和排序的时间

3.创建唯一索引，能够保证数据库表中每一行数据的唯一性

4.在实现数据的参考完整性方面，可以加速表与表之间的连接

索引的缺点：

1.创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加

2.索引需要占据磁盘空间

3.对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

MySQL索引的使用原则

创建索引的原则：

1.更新频繁的列不应设置索引

2.数据量小的表不要使用索引（如果一个文档只有两页当然不需要使用到目录)

3.重复数据多的字段不应设为索引（比如性别只有男和女，一般来说重复的数据超过百分之15就不该建索引)

4.首先应该考虑对where和order by涉及的列上建立索引

MySQL的存储引擎

概念：

1.数据库存储引擎是数据库底层软件组织，数据库管理系统(DBMS)使用数据引擎进行创建，查询，更新和删除数据

2.不同的存储引擎提供不同的存储机制，索引技巧，锁定水平等功能，现在许多不同的数据库管理系统都支持不同的数据引擎，MySQL的核心就是存储引擎

3.用户可以根据不同的需求为数据表选择不同的存储引擎

4.可以使用 show engines 命令可以查看MySQL的所有执行引擎

可见默认的引擎是innoDB(MySQL 5.5之后指认的引擎)

下面是关于MySQL存储引擎的一些操作：

MySQL的事务

什么是事务？

1.在MySQL中的事务(Transaction)是由存储引擎实现的，在MySQL中，只有InnoDB存储引擎才支持事务

2.事务处理可以用来维护数据库的完整性，保证成批的SQL语句要么全部执行，要么全部不执行

3.事务用来管理DDL，DML，DCL操作，比如insert,update,delete语句，默认是自动提交的

以银行转账为例：

将这两条SQL语句捆绑为一个事务，两条语句要么全执行或者全不执行

事务操作

MySQL的事务操作主要有以下三种：

之前的所有SQL操作其实也有事务，只是MySQL自动帮我们完成的，每执行一条SQL时MySQL就帮我们自动提交事务，因此如果相要手动控制事务，则必须关闭MySQL的事务自动提交

在MySQL中直接使用set来改变MySQL的自动提交模式

还是以银行转账演示：

rollback显示的就是修改之前的状态

在使用中要么提交要么回滚，选一个用

MySQL中事务的特性

MySQL的事务-隔离级别

isolate，顾名思义就是将事务与另一个事务隔离开，为什么要隔离呢？如果一个事务正在操作的数据被另一个事务修改或者删除了，最后的执行结果可能无法到达预期，如果没有隔离性还会导致其他问题

上图表示的是事务之间的紧密程度，越向下表示关联程度越稀疏

下面是隔离级别涉及到的操作：

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------2022.8.13

MySQL的锁机制

概述：

锁使计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）

在数据库中，除传统的计算机资源（CPU，RAM，I/O等）的争用之外，数据也是一种供许多用户共享的资源

如何保证数据并发访问的一致性，有效性是对所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素，从这个角度来说，锁对数据库而言显得尤外重要，也更加复杂

加上锁就不让你访问，没有锁就没有限制

从对数据操作的粒度分：

1.表锁：操作时，会锁定整个表

2.行锁：操作时，会锁定当前操作行

从对数据操作的类型分：

1.读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响

2.写锁（排他锁）：当前操作没有完成之前，他会阻断其他写锁和读锁

相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制，下表中罗列出了各引擎对锁的支持情况：

MySQL锁的特性可大致归纳如下：

从上述特点可见，很难笼统的说哪种锁更好，只能就具体应用的特点来说哪种锁更合适

仅从锁的角度来说：表级锁更合适于查询为主，只有少量按索引条件更新数据的应用，如Web应用

而行级锁更合适于大量按索引条件并发更新少量不同数据，同时又有并查询的应用，如一些在线事务处理(OLTP)系统

MySQL的锁机制-MyISAM表锁

MyISAM存储引擎只支持表锁

如何加表锁

MyISAM在执行查询语句（select)前，会自动给涉及的所有表加读锁，在执行更新操作(update,delete,insert等）前，会自动给涉及的表加读写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁

加上读锁之后表示表当前只能读而不能修改，而且当前只能对当前的表进行操作，不能访问或者修改其他的表

当需要删除锁时，用unlock关键字

MySQL的锁机制-InnoDB行锁

行锁特点：

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务；二是采用了行级锁，MyISAM都不支持

行锁模式：

InnoDB实现了以下两种类型的行锁

共享锁(S):又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改

排他锁(X):又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据进行读取和修改

对于UPDATE，DELETE和INSERT语句，InnoDB会自动给设计数据集加排他锁（X）

对于普通SELECT语句，InnoDB不会加任何锁

行锁模式

可以通过以下语句显示给记录集加共享锁或排他锁

--------------------------------------------------------------

MySQL的日志

介绍：

在任何一种数据库中，都会有各种各样的日志，记录着数据库工作的方方面面，以帮助数据库管理员追踪数据库曾经发生过的各种事件，MySQL也不例外

日志分类

1.错误日志

2.二进制日志

3.查询日志

4.慢查询日志

1.错误日志

错误日志是MySQL中最重要的日志之一，它记录了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息，当数据库出现任何故障导致无法正常使用时，可以首先查看此日志

该日志是默认开启的，默认存放目录为mysql的数据目录，默认的日志文件名为hostname.err(hostname是主机名)

查看日志位置指令：

2.binlog日志

二进制日志记录了所有的DDL（数据定义语言）语句和DML（数据操纵语言）语句，但是不包括数据查询语句，此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制，就是通过该binlog实现的

二进制日志，MySQL8.0默认已开启，低版本的MySQL的需要通过配置文件开启，并配置MySQL日志的格式

windows系统：my.ini Linux系统：my.cnf

日志格式：

1.statement:

该日志格式在日志文件中记录的都是SQL语句（statement），每一条对数据进行修改的SQL都会记录在日志文件中，通过MySQL提供的mysqlbinlog工具，可以清晰地查看到每条语句的文本，主从复制的时候，从库（slave）会将日志解析为原文本，并在从库重新执行一次

2.row:

该日志格式在日志文件中记录的是每一行的数据变更，而不是记录SQL语句，比如执行SQL语句：update tb_book set status='1',如果时statement日志格式，在日志中会记录一行SQL文件；如果是ROW，由于是对全表进行更新，也就是每一行记录都会发生变更，ROW格式的日志中会记录每一行的数据变更

3.mixed：

混合了前两种格式

下面是涉及到的操作：