数据库笔记

显示部分

语法：show databases like；

-：匹配当前位置多个字符

%：匹配指定位置多个字符

获取以my开头的全部数据库：’my%‘；

获取m开头，后面第一个字母不确定，最后为database的数据库；’m-database‘；

选择数据库

数据是存储到数据表，表存在数据库下。如果要操作数据，那么必须进入到对应的数据库才行

use mydatabases；

修改数据库

修改数据库字符集（库选项）：字符集和校对集

基本语法：alter database 数据库名字 charset =字符集；

删除数据库

语法：drop database 数据库名字

数据表操作

创建数据表

普通创建表

语法：create table 表名（字段名 字段类型【字段属性】 字段名 字段类型【字段属性】）

表必须放到对应的数据库下

1.在数据表名字前加上数据库名字，用'.'连接即可，数据库.数据表

2.在创建数据表之前先进入某个具体的数据库即可：use 数据库名字

表选项：与数据库类似

engine：存储引擎，存储数据的方式

charset：字符集，只对当前自己表有效

collate：校对集

显示数据集

.frm来自于innodb存储引擎，所有的文件都存储在外部的ibdata文件中；

显示表结构

本质含义：显示表中所包含的字段信息（名字，类型，属性等）

desc 表名

显示表创建语句

查看数据表创建时的语句，此语句看到的结果已经不是用户之前自己输入的

语法：show create table 表名

设置表属性

修改表结构

修改表名：rename table 旧表名 to 新表名

修改表选项：alter table 表名 表选项 【=】新值

新增字段：alter table 表名 add 【column】 新字段名 列类型 【列属性】【位置first/after 字段名】

修改字段名：alter table 表名 change 旧字段名 新字段名 字段类型 【列属性】【新位置】

修改字段类型（属性）：alter table 表名 modify 字段名 新类型 【新位置】

删除表列：alter table 表名 drop 字段名；

删除表结构

语法：drop table 表名

插入数据

 insert into my_teacher(name,age) values('jack',30);

语法：向表中所有字段插入数据

insert into 表名 values（对应表结构）

查询数据

 select * from my_teacher;

简单条件查询

select 字段列表 * from 表名 where 字段名 = 值；

删除操作

delete from 表名 where 条件

删除年龄为30的老师

delete from my-teacher where age=30；

更新操作

更新：将数据进行修改

语法：update 表名 set 字段名 = 新值 where 条件

字符集

字符在计算机中对应的编码叫做字符编码

set names gbk；

============================================等价于

set character-set-client=gbk //为了让服务器识别客户端传来的数据

set character-set-connection=gbk//更好的帮助客户端与服务端之间进行字符集转换

set character-set-results=gbk//为了告诉客户端服务端所有的返回的数据字符集

connection只是为了更方便客户端与服务端进行字符集转换而设

无符号标识设定

无符号：表示存储的数据在当前字段中，没有负数（只有正数，区间为0-255）

语法：在类型之后加上一个unsigned

显示长度

指数据在数据显示的时候，到底可以显示多长位

tinyint（3）：便是最长可以显示3位

zerofill：从左侧开始填充0（左侧不会改变数值大小），所以负数的时候就不能使用zerofill，一旦使用zerofill就相当于确定该字段位unsigned

小数类型

浮点型和定点型

浮点型是一种可能丢失精度的数据类型，可能不精准

浮点型：有部分用于存储数据，有部分用于存指数

语法：float 表示不指定小数位的浮点数

float（M,D）：表示一共存储M个有效数字，其中小数部分占D位

定点数

保证数据精确的小数

decimal

系统自动根据存储的数据来分配存储空间，每大概9个数就会分配四个字节来进行存储，同时小数和整数部分是分开的

decimal（M，D）：M表示总长度，最大值不能超过65，D代表小数部分长度，最长不超过30

字符串型

char

定长字符，指定长度之后，系统一定会分配指定的空间用于存储数据

语法：char（L),L代表字符数，长度位0到255

varchar

变长字符：指定长度之后，系统会根据实际存储的数据来计算长度，分配合适的长度

varchar（L),L长度理论值位0-65535

每个varchar数据产生后，系统都会在数据后面增加1-2个字节的额外开销：是用来保存数据所占用的空间长度，如果数据本身小于127个字符：额外开销一个字节；如果大于127个，就开销两个字节

utf8：一个字符占用3个字节

char和varchar的区别

1.char一定会使用指定的空间，varchar是根据数据来定空间

2.char的数据查询效率比varchar高：varchar是需要通过后面的记录数来计算

如果确定数据一定占指定长度，使用char类型

如果不确定数据到底有多少，使用varchar

如果数据长度超过255个字符，不论是否固定长度，都会使用text

text

存储普通的字符文本

 blob：一般不会使用blob来存储文件本身，通常是使用一个链接来指向对应的文件本身。

tinytext：使用一个字节来保存，能存储的数据为：2^8+1

text:两个字节保存，2^16+2

mediumtext：三个字节保存，2^24+3

longtext：四个字节保存，2^32+4

集合

是一种将多个数据选项可以同时保存的数据类型，本质是将指定的项按对应的二进制位来进行控制：1表示该项被选中，0表示该项没有被选中

语法：set（‘值1’，‘值2’，‘值3’）

系统在进行存储的时候会自动将得到的二进制颠倒过来，然后再进行转换乘十进制存储

10010010

存储转换：01001001    1+8+64=73

set集合的意义：
1.规范数据

2.节省存储空间

enum：单选框

set：复选框

mysql记录长度

mysql的记录长度总长度不能超过65535个字节。

varchar除了存储的数据本身要占用空间，还需要额外的空间（2个字节）来保存记录长度

utf8最多只能存储21844个字符

gbk最多只能存储32766个字符

列属性

列描述：用来给开发人员进行维护的一个注释说明

comment‘字段描述’

主键

在一张表中，有且只有一个字段，里面的值具有唯一性

两种增加主键的方式

1.直接在需要当作主键的字段之后，增加primary key属性来确定主键

2.在所有字段之后增加primary key选项

表后增加

语法：alter table 表名 add primary key（字段）

查看主键

desc my-pri；

查看表的创建语句

show create table my-pri；

删除主键

语法：alter table 表名 dropprimary key

主键约束

主键一旦增加，那么对对应的字段有数据要求

1.当前字段对应的数据不能为空

2.当前字段对应的数据不能有任何重复

主键分类

业务主键：主键所在的字段，具有业务意义

逻辑主键：自然增长的整型

自动增长

语法：在字段之后增加一个属性auto-increment

修改自动增长

1.查看自增长：自增长一旦触发使用后，会自动的在表选项中增加一个选项（一张表最多只能拥有一个自增长）

2.表选项可以通过修改表结构来实现

alter table 表名 auto-increment=值

删除自增长

在字段属性之后不再保留anto-increment，当用户修改自增长所在字段时，如果没有看到该属性，系统会自动清除该自增长

唯一键

unique key，用来保证对应的字段中的数据唯一的

主键也可以保证字段数据唯一性，但是一张表只有一个主键

1.唯一键在一张表中可以有多个

2.唯一键允许字段数据位null，null可以有多个（null不参与比较）

创建唯一键

1.直接在表字段之后增加唯一键标识符：unique【key】

2.在所有的字段之后使用unique key（字段列表）

3.在创建完表之后也可以增加唯一键

alter table 表名 add unique key（字段列表）

查看唯一键

唯一键是属性，可以通过查看表结构来实现

效果：在不为空的情况下，不允许重复

删除唯一键

一个表中允许存在多个唯一键：假设命令和主键一样

alter table 表名 drop index 唯一键名字

index关键字：索引，唯一键是索引的一种（提升查询效率）

表关系

表与表之间（实体）有什么样的关系，每种关系应该如何设计表结构

一对一：一张表中的一条记录与另外一张表中最多有一条明确的关系：通常，此设计方案保证两张表中使用同样的主键即可

多对多：一张表中的一条记录在另外一张表中可以匹配到多条记录，反过来也一样。

多对多解决方案：增加一个中间表，让中间表与对应的其他表形成两个多对一的关系

新增操作

多数据操作

语法：insert into 表名 [(字段列表）]values（值列表），（值列表）；

insert into my_teacher values('张三'),('李四'),('王五');

主键冲突

主键冲突更新：

insert into 表名 [(字段列表）]values（值列表）on duplicate key update 字段=新值

insert into my_teacher values(’stu0004‘，’小婷‘）on duplicate key update stu -name = ’小婷‘；

主键冲突替换：

replace into  [(字段列表）]values（值列表）；

蠕虫复制

一分为二，成倍增加。从已有数据中获取数据，并且将获取到的数据插入到数据表中。

语法：

insert into 表名 [(字段列表）]select */字段列表 from 表；

更新数据

1.会跟随条件更新

update 表名 set 字段名 =新值 where 判断条件；

2.没有条件，是全表更新数据。但可以使用limit来显示更新的数量；

update 表名 set 字段名 =新值 【where 判断条件】 limit 数量；

删除数据

1.尽量不要全部删除，应该使用where进行判定

2.使用limit来限制要删除的具体数量

无法重置auto-increment

重置auto-increment：

truncate 表名；

查询数据

完整的查询指令：

select select 选项 字段列表 from 数据源 where 条件 group by 分组 having 条件 order by 排序 limit 限制

select 选项：系统该如何对待查询得到的结果

all：默认的，表示保存所有的记录

distinct：去除重复的记录，只保留一条（所有字段都重复）

字段列表：有的时候需要从多张表获取数据，在获取数据的的时候，可能存在不同表中有同名的字段，需要将同名的字段命名为不同名的：别名：alias

语法：字段名 【as】 别名

name as name1

From数据源

from为前面的查询提供数据：数据源只要是一个符合二维表结构的数据即可。

单表数据

from 表名

多表数据

from 表1，表2；

结果：两张表的记录数相乘，字段数拼接

本质：从第一张表取出一条记录，去拼凑第二张表的所有记录，保留所有结果。得到的结果是笛卡尔积

动态数据

from 后面跟的数据不是一个实体表，而是一个从表中查询出来得到的二维结果表（子查询）。

from （select 字段列表 from 表）as 别名；

where 子句

用来从数据表获取数据的时候，进行条件筛选

group by子句

表示分组的含义，根据指定的字段，将数据进行分组，分组的目标是为了统计

分组统计

group by 字段名

分组后，只会保留每组的第一条记录

多分组

将数据按照某个字段进行分组之后，对已经分组的数据进行再次分组

语法：group by 字段1，字段2；

分组排序

分组默认有排序的功能，按照分组字段进行排序，默认是升序

语法：group by 字段 [asc|desc]，字段[asc|desc]；

回溯统计

当分组进行多分组之后，网上统计的过程中，需要进行层层上报，将这种层层上报统计的过程称之为回溯统计，每一次分组向上统计的过程都会产生一次新的统计数据，而且当前数据对应的分组字段为null。

语法：group by 字段 【asc|desc】with rollup；

having 子句

用来进行数据条件筛选

在group子句之后：可以针对分组数据进行统计筛选，where不行

where 不能使用聚合函数：聚合函数是用在group by分组的时候，where已经运行完毕

having在group by分组之后，可以使用聚合函数或者字段别名（where是从表中取出数据，别名是在数据进入内存之后才有的）

where之后的所有操作都是内存操作

order by 子句

排序，根据校对规则对数据进行排序

limit 子句

限制记录数量获取

limit 数量

分页

利用limit来限制获取指定区间的数据

语法：limit offset，length；offset：偏移量，从那开始，length：具体获取多少条记录

运算符

在mysql中除法的运算结果使用浮点数表示

=：在mysql中，没有对应的==比较符号，只使用=来进行相等判断

<=>：相等比较

特殊应用：在字段结果中进行比较运算

在mysql中，数据会自动转换成同类型，再比较

在mysql中，没有bool值，1代表true，0代表false

between为闭区间查找，条件1必须小于条件2

is运算符

专门用来判断字段是否为null的运算符

is null/is not null

联合查询

将多个查询的结果合并到一起（纵向合并）：字段数不变，多个查询的记录数合并。

语法：

select 语句

union [union 选项]

select 语句；

union选项：与select选项基本一样

distinct：去重，去掉完全重复的数据（默认的）

all：保存所有的结果

union 理论上只要保证字段数一样，不需要每次拿到的数据对应的字段类型一致

order by 的使用

在联合查询中，如果要使用order by 那么对应的select 语句必须使用括号括起来，若要生效，必须配合使用limit：limit后面必须跟对应的限制数量（通常使用较大值，大于对应表的记录数）

连接查询

将多张表连到一起进行查询（会导致记录数行和字段数列发生改变）

交叉连接

将两张表的数据与另外一张表彼此交叉

笛卡尔积

表1 cross join 表2；

内连接

从一张表中取出所有的记录去另外一张表中匹配：利用匹配条件进行匹配，成功了则保留，失败了放弃。

语法：表1 ioin 表2 on 匹配条件；

因为表的设计通常容易产生同名字段，尤其是id，所以为了避免重名出现错误，通常使用表名.字段名，来确保唯一性。

即my_student.class.id=my_class.id;

如果条件中使用到对应的表名，而表名通常比较长，多以可以通过表别名来简化。

外连接

按照某一张表作为主表（表中所有记录在最后都会保留），根据条件去连接另外一张表，从而得到目标数据。

左外连接：左表是主表

右外连接：右表是主表

语法：

左连接：主表 left join 从表 on 连接条件

右连接：主表 right join 从表 on 连接条件

特点：

外连接中主表数据记录一定会保存：连接之后不会出现记录数少于主表（内连接可能）

左连接和右连接可以相互转换，但数据对应的位置（表顺序）会改变

子查询

当一个查询是另一个查询的条件时，乘之为子查询。

子查询：指在一条select语句中，嵌入了另外一条select语句，那么被嵌入的select语句称之为子查询语句。

主查询

主查询：主要的查询对象，第一条select语句，确定的用户所有获取的数据目标（数据源），已经要具体得到的字段信息。

1.子查询是嵌入到主查询中的。

2.子查询是辅助主查询的，要么作为条件，要么作为数据源。

3.子查询其实可以独立存在，是一条完整的select语句。

标量子查询

子查询得到的结果是一个数据

select * from 数据源 where 条件判断 =/<> （select 字段名 from 数据源 where 条件判断）；

子查询得到的结果只有一个值

 列子查询

得到的结果是一列多行数据

语法：

主查询 where 条件 in（列子查询）；

 

行子查询

返回的结果是一行多列

主查询 where 条件 【（构造一个行元素）】=（行子查询）；

select * from my_student where (stu_age,stu_height)= (select max(stu_age),max(stu_height) from my_student);

表子查询

返回的结果是多行多列

 表子查询适用于from 数据源：from 子查询

select 字段表 from （表子查询）as 别名；

from后必须要跟对应的表名

 exists子查询

返回的结果只有0或1

整库数据备份与还原

mysqldump.exe

不只是备份数据，还备份对应的sql指令，数据库被删，利用sql备份依然可以实现数据还原。

不适合特大型数据备份，也不适合数据变换频繁型数据库备份。

sql备份

用到的是专门的备份客户端，因此还没与数据库服务器进行连接。

mysqldump/mysqldump.exe -jpup 数据库名字【表1 【表2...]] > 备份文件地址

数据还原

当进行数据（sql还原），必须指定数据库

1.mysql.exe -hup 数据库<文件位置

2.在sql 指令，提供了一种导入sql 指令的方式

source sql文件位置；

3.人为操作：打开备份文件，复制所有sql指令，到mysql.exe客户端中去执行

用户权限管理

在不同的项目中给不同的角色（开发者）不同的操作权限，为了保证数据库数据的安全。

通常，一个用户的密码不会长期不变，所以需要经常性的变更数据库用户密码来确保用户本身安全（mysql客户端用户）

用户管理

mysql需要客户端进行连接认证才能进行服务器操作：需要用户信息。mysql中所有的用户信息都是保存在mysql数据库下的user表中。

在mysql中，对应的用户管理中，是有对应的host和user 共同组成主键来区分用户。

user：代表用户的用户名

host：代表本质是允许访问的客户端（ip或者主机地址）。如果host使用*代表所有的用户（客户端）都可以访问

创建用户

 当用户创建完成后，用户是否可以使用？

mysql -uuser2

删除用户

修改用户密码

 password（）

1.使用专门的修改密码的指令

set password for 用户 = password（‘新的明文密码’）

 

2.使用更新语句update来修改表

update mysql.user set password =password（新的明文密码’）；where user = ‘’ and host=’‘

权限管理

在mysql中将权限管理分为三类

1.数据权限

2.结构权限

3.管理权限

授予权限

grant

将权限分配给指定的用户

grant 权限列表 on 数据库/*.表名/* to 用户

权限列表：使用逗号分隔，但是可以使用all privileges 代表全部权限

 取消权限

revoke

将权限从用户中收回

revoke 权限列表/all privilege on 数据库/*.表/* from 用户；

刷新权限

flush

将当前对用户的权限操作，进行一个刷新，将操作的具体内容同步到对应的表中

flush privilege；

外键 

外键的操作

 MUL：多索引，外键本身是一个索引，外键要求外键字段本身也是一种普通索引

外键不允许修改，只能先删除后增加

alter table 从表 drop foreign key 外键名字；

删除对应的索引：alter table 表名 drop index 索引名字；

外键基本要求

 

 外键约束主要约束的对象是主表操作：从表就是不能插入主表不存在的数据

视图基本操作

本质是sql 指令

create view 视图名字 as select 指令；

查看视图结构：试图本身是虚拟表，所以关于表的一些操作都适用于视图

使用视图

视图是一张虚拟表：可以直接把视图当作‘表‘操作，但是视图本身没有数据，是临时执行select语句得到对应的结果。视图主要进行用户查询操作

select 字段列表 from 视图名字【字句】；

修改视图

本质是修改视图对应的查询语句

alter view 视图名字 as 新 select 指令；

事务安全

 事务基本原理

mysql允许将事务统一进行管理（存储引擎innodb），将用户所做的操作，暂时保存起来，不直接放到数据表（更新），等到用于确认结果之后再进行操作。

 事务在mysql 中通常是自动提交的，但是也可以使用手动事务。

自动事务

autocommit，当客户端发送一条sql指令（写操作：增删改）给服务器的时候，服务器在执行之后，不用等待用户反馈结果，会自动将结果同步到数据表。

证明：利用两个客户端，一个客户端执行sql指令，另外一个客户端查看执行结果

自动事务：系统做了额外的步骤来帮助用户操作，系统是通过变量来控制的。autocommit 

show variables like ‘autocommit’

关闭自动事务：

set autocommit =off；

查看执行结果

一旦自动事务关闭，就需要用户提供是否同步的命令

commit ：提交 （同步到数据表，事务也会被清空）

rollback：回滚（清空之前的操作，不要了）

事务没有提交的对比查看：在执行事务端的客户端中，系统在进行数据查看的时候会利用事务日志中保存的结果对数据进行加工

通常不会关闭自动事务

手动事务

不管是开始还是结束都需要用户（程序员），手动的发送事务操作指令来实现

手动事务对应的命令

start transaction； 开启事务：从这条语句开始，后面的所有语句都不会直接写入到数据表（保存在事务日志中）

事务处理：多个写指令构成

事务提交：commit/rollback，到这个时候所有的事务才算结束

开启事务

 执行事务

将多个连续的但是是一个整体的sql指令，逐一执行

1.事务操作1：新增数据

2.事务操作2：更新数据

提交事务

确认提交：commit，数据写到数据表（清空）

回滚操作：rollback，所有数据无效并清空

回滚点

savepoint，当有一系列事务操作时，而其中的步骤如果成功了，没有必要重新来过，可以在某个点（成功），设置一个记号（回滚点），然后如果后面有失败，那么可以回到这个记号位置。

增加回滚点：savepoint 回滚点名字；//字母数字和下划线构成

回到回滚点：rollback to 回滚点名字；//那个记号（回滚点）之后的所有操作没有了

注意：在一个事务处理中，如果有很多个步骤，那么可以设置多个回滚点。但如果回到了前面的回滚点，后面的回滚点就失效了。

1.增加回滚点操作

2.出现错误步骤 

3.回到正确点：回滚

 事务特点

 数据表中的数据修改，要么是所有操作一次性修改，要么是根本不动

 

 

变量 

mysql本质是一种编程语言，需要很多变量来保存数据。mysql中很多的属性控制都是通过mysql中固有的变量来实现。

系统变量

系统内部定义的变量

show variables 【like ’pattern‘】；

MySQL 允许用户使用select查询变量的数据值（系统变量）

select@@变量名； 

修改系统变量：两种修改方式

1.局部修改（会话级别）：只针对当前自己客户端当次连接有效

set 变量名 =新值；

2.全局修改：针对所有的客户端：’所有时刻’都有效

set global 变量名= 值；||set @@global.变量名=值；

全局修改之后：所有连接的客户端并没有发现改变？全局修改只针对新客户端生效（正在连着的无效）

如果想要本次连接对应的变量修改有效，那么不能使用全局修改，只能使用会话级别修改（set 变量名 =值）；

会话变量

也成为用户变量，与mysql客户端是绑定的，设置的变量，只对当前用户使用的客户端生效。

定义用户变量：set @变量名 =值；

 在mysql中没有比较符号==，所以使用=代替比较符号：有时候在赋值的时候会报错；mysql为了避免系统分不清是赋值还是比较：特定增加一个变量的赋值符号：   ：=

set @变量名：=值；

mysql是专门存储数据的，允许将数据从表中取出存储到变量中，查询得到的数据必须只能是一行数据（一个变量对应一个字段值）：mysql没有数组。

1.赋值且查看赋值过程：select @变量1 =字段1，@变量2 =字段2 from 数据表 where 条件；

 错误，使用=，系统会当作比较符号来处理

正确处理：

2.只赋值，不看过程：select 字段1，字段2。。。from数据源 where 条件 into @变量1，@变量2。。。

 查看变量：select @变量名；

  局部变量

作用范围在begin到恩典语句块之间。在该语句块里设置的变量，declare语句专门用于定义局部变量。

1.局部变量时使用declare关键字声明

2.局部变量declare语句出现的位置一定是在begin和end之间（begin end是在大型语句块中使用：函数/存储过程/触发器）

3.声明语法：declare 变量名 数据类型【属性】；

流程结构

代码的执行顺序

if分支在mysql有两种用法

1.用在select查询当中，当作一种条件来进行判断

语法：if（条件，为真结果，为假结果）

2.用在复杂的语句块中（函数/存储过程/触发器）

 语法：

if 条件表达式 then

         满足条件要执行的语句；

end if；

复合语法

if 条件表达式 then 

          满足条件要执行的语句；

else 

         不满足条件要执行的语句；

end if；

while循环

循环体都是需要在大型代码块中使用

语法：

while 条件 do

       要循环执行的代码；

end while；

结构标识符

为某些特定的结构进行命名，然后为的是在某些地方使用名字

语法：

标识名字：while 条件 do

循环体

end while【标识名字】；

标识符的存在主要是为了循环体中使用循环控制。在mysql中没有continue和break，有自己关键字替代：

iterate：迭代，以下的代码不执行，重新开始循环（continue）

leave：离开，整个循环终止（break）

语法：

标识名字：while 条件 do

          if   条件判断  then

             循环控制；

              iterate/leave 标识名字；

 end

循环体

end while【标识名字】；

函数

在mysql中函数分为两类：系统函数（内置函数）和自定义函数

不管是内置函数还是用户自定义函数，都是使用select函数名（参数列表）；

内置函数

mid（）：从左侧开始截取，知道指定位置（位置如果超过长度，截取所有）

char-length（），length（）； 一个字符两个字节

 l

concat（），instr（）

lcase（）， left（）

 ltrim（），mid（）

 时间函数

 

 now(),curdate(),curtime();

 dateddiff();

 date_add();

unix_timestamp():获取时间戳

 

from——unixtime（）：将指定事件戳转换成对应的日期时间格式

 

 数学函数

 

 其他函数

 

自定义函数

自定义函数：用户自己定义的函数

函数：实现某种功能的语句块（由多条语句组成）

1.函数内部的每条指令都是一个独立的个体：需要符合语句定义规范：需要语句结束符分号；

2.函数是一个整体，而且函数是在调用的时候才会被执行，那么当设计函数的时候，意味着整体不能被中断

3.mysql一旦见到语句结束符分号，就会自动开始执行

解决方案：在定义函数之前，尝试修改临时的语句结束符

delimiter

修改临时语句结束符：delimiter 新符号

中间为正常sql 指令：使用分号结束（系统不会执行：不认识分号）

使用新符号结束

修改回语句结束符：delimiter；

创建函数

要素：function 关键字，函数名，参数（形参和实参【可选】），确认函数返回值类型，函数体，返回值

函数定义基本语法：

修改语句结束符

create function 函数名（形参） returns 返回值类型

begin

//函数体

return 返回值数据；//数据必须与结构中定义的返回值类型一致

end

语句结束符

修改语句结束符（改回来）

 

 并不是所有的函数都需要begin和end：如果函数体本身只有一条指令（return），那么可以省略begin和end

形参：在mysql中需要为函数的形参指定数据类型（形参本身可以有多个）

语法：

变量名 字段类型 

查看函数

 1.可以通过查看function状态，来查看所有的函数

show function status 【like ’pattren‘】；

2.查看函数的创建语句：show create function 函数名字；

调用函数

自定义函数的调用与内置函数的调用是一样的：select函数名（实参列表）；

select my_func(),my_func(), my_func3(100,1000);

删除函数 

drop function 函数名；

注意事项

1.自定义函数是属于用户级别的：只有当前客户端对应的数据库中可以使用

2.可以在不同的数据库下看到对应的函数，但是不可以调用

3.自定义函数：通常是为了将多行代码集合到一起解决一个重复性的问题

4.函数因为必须规范返回值：那么在函数内部不能使用select 指令：select 一旦执行就会得到一个结果（result set）：select 字段 into @变量：（唯一可用） 

流程结构案例

需求：从1开始，知道用户传入的对应的值为止，自动求和：凡是5的倍数都不要

设计：

1.创建函数

2.需要一个形参：确定要累加到什么位置

3.需要定义一个变量来保存对应的结果

set @变量名

使用局部变量来操作：此结果是在函数内部使用

declare 变量名 类型 【=默认值】；

4.内部需要一个循环来实现迭代累加

5.循环内部需要进行条件判断控制：5的倍数

变量作用域

变量能够使用的区域范围

局部作用域

使用declare关键字声明（在结构体内：函数/存储过程/触发器），而且只能在结构体内部使用

1.declare关键字声明的变量没有任何符号修饰，就是普通字符串，如果在外部访问该变量，系统会自动认为是字段

会话作用域

用户定义的，使用@符号定义的变量，使用set关键字

会话作用域：在当前用户档次连接有效，只要在本连接之中，任何地方都可以使用（可以在结构内部，也可以跨库）

全局作用域

所有的客户端所有的连接都有效：需要使用全局符号来定义

set global 变量名 = 值；

set @@global.变量名=值；

通常：在sql编程的时候，不会使用自定义变量来控制全局。一般都是定义会话变量或者在结构中使用局部变量来解决问题。

存储过程

 存储过程操作

创建过程

create procedure 过程名字【（参数列表）】

begin

过程体

end

结束符

如果过程体中只有一条指令，那么可以省略begin和end

过程基本上也可以完成函数对应的所有功能

查看过程 

查看过程与查看函数完全一样：除了关键字

查看全部存储过程：show procedure status【like pattren】；

查看过程创建语句：show create procedure 过程名字；

调用过程

没有返回值，select 不可能调用

调用过程有专门语法：call过程名（实参列表）

 删除过程

drop procedure 过程名字；

存储过程的形参类型

 

 

 

触发器

是一种特殊类型的存储过程，不同于存储过程，主要是通过事件进行触发而被执行的，而存储过程可以通过存储过程名字而被直接调用

触发器：trigger，接近于js中事件的知识。提前给某张表的所有记录绑定一段代码，如果改行的操作满足条件（触发），这段提前准备好的代码就会自动执行

作用

1.可在写入数据表前，强制检验或转换数据（保证数据安全）

2.触发器发生错误时，异动的结果会被撤销（如果触发器执行错误，那么前面用户已经执行成功的操作也会被撤销：事务安全）

3.部分数据库管理系统可以针对数据定义语言ddl使用触发器，ddl触发器

4.可依照特定的情况，替换异动的指令instead of（mysql不支持）

触发器优缺点

优点

1.触发器可通过数据库中的相关表实现级联更改。（如果某张表的数据改变，可以利用触发器来实现其他表的无痕操作【用户不知道】）

2.保证数据安全：进行安全校验

缺点

1.对触发器过分的依赖，势必影响数据库的结构，同时增加维护的复杂程度

2.造成数据在程序层面不可控

创建触发器

create trigger 触发器名字 触发时机 触发事件 on 表 for each row

begin

end

触发对象：on 表 for each row，触发器绑定实质是表中的所有行，因此当每一行发生指定的改变的时候，就会触发触发器

触发时机

每张表中对应的行都会有不同的状态，当sql指令发生的时候，都会令行中数据发生改变，每一行总会有两种状态：数据操作前和操作后

before：在表中数据发生改变前的状态

after：在表中数据已经发生改变后的状态

触发事件

触发事件：mysql中触发器针对的目标是数据发生改变，对应的操作只有写操作（增删改）

注意事项

一张表中，每一个触发时机绑定的出发时间对应的触发器类型只能有一个：一张表中只能有一个对应after insert 触发器

因此，一张表中最多的触发器只能由6个：before insert，before update，before delete，after insert ，after update，after delete

需求：有两张表，一张是商品表，一张是订单表（保留商品id），每次订单生成，商品表中对应的库存就应该发生变化。

1.创建两张表：商品表和订单表

 

 2.创建触发器：如果订单表发生数据插入，对应的商品就应该减少库存

create trigger 名字 after insert on my_orders for each row

 

查看触发器

show triggers；

查看触发器的创建语句

show create trigger 触发器名字；

触发触发器

让触发器指定的表中，对应的时机发生对应的操作即可

1.表为my_orders

2.在插入之后

3.插入操作

删除触发器

drop trigger 触发器名字；

触发器应用

 

商品自动扣除库存

 

验证结果 

若库存数量没有商品订单多

操作目标：订单表，操作时机：下单前，操作事件：插入

 

结果验证