🍖视图、触发器、事务、存储过程、函数、流程控制
一.视图
1.什么是视图
- 视图就是通过查询得到一张虚拟表(并非真实存在), 然后保存下来, 下次可以直接使用
- 视图的本质也是一张表
2.视图的作用
- 如果要频繁的操作一张虚拟表, 就可以将其制作成视图后直接操作
3.视图的使用
-
语法 :
create view [表名] as [查询表的语句] -
创建两个相互关联的表
select * from emp;
select * from dep;
- 创建视图
create view emp_dep_view as
select * from
emp inner join dep
on emp.dep_id=dep.did;
show tables;
select * from emp_dep_view;
- 修改视图内容
update emp_dep_view set name="BigBob" where name="Bob";
select * from emp_dep_view;
select * from emp;
alter view emp_dep_view2 as select * from emp where id>4;
show tables;
select * from emp_dep_view2;
我们发现修改了视图表, 原来的 emp 表的数据也发生了改变
- 删除视图
drop view emp_dep_view;
show tables;
4.视图总结
- 修改视图的内容会影响真正表的数据, 所以视图一般不修改,只是用来查询的
- 视图的修改一般只争对创建视图时as后面定义的虚拟表的sql查询语句
- 创建视图在硬盘上只会有表结构(.frm), 没有表数据(.idb). 数据还是来自于之前的表(所以该视图,原数据会改变)
致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便
二.触发器
1.什么是触发器
- 对一张表进行 : 增、删、改操作的时候, 自动触发预先编译好的 SQL 语句的执行 (没有查操作)
2.触发器的作用
- 保证数据的完整性, 还可以起到类似于事务回滚的效果, 帮我们实现监控、日志等
3.自动触发情况
- 增前、增后
- 删前、删后
- 改前、改后
4.注意点说明
- delimiter : 改变输入的结束符，默认情况下输入结束符是分号 ";"，下面我把它改成了自定义 "%%"，这样做的目的是把多条含分号的语句做个封装，全部输入完之后一起执行，而不是一遇到默认的分号结束符就自动执行 (只在当前窗口有效)
- new : 表示即将插入的数据行
- old : 表示即将删除的数据行
5.触发器的使用
- 完整语法
delimiter %% # 修改结束符
create trigger [触发器名] [before/after] [insert/update/delete] on [表名]
for each row # 表示每往上面创建的表进行指定操作之后就执行下面 "begin...end" 里面的SQL语句
begin
[sql 语句]
end %%
delimiter ; # 将结束符改回来
- 触发器的命名(见明知意)
🥝创建一个往"t01"表内插入记录之前触发"sql"语句的触发器
delimiter %%
create trigger tri_before_insert_t01 before insert on t01
for each row
begin
[SQL语句]
end %%
delimiter ;
🥝创建一个在"t01"表删除记录之后触发"sql"语句的触发器
delimiter %%
create trigger tri_after_delete_t01 after delete on t01
for each row
begin
[sql语句]
end %%
delimiter ;
🥝创建一个在对"t01"表修改数据之前就触发"sql"语句的触发器
delimiter %%
create trigger tri_before_update_t01 before update on t01
for each row
[SQL语句]
end %%
delimiter ;
6.触发器示例
- 模拟 cmd 命令的执行, 执行失败则添加到错误日志中
🥝准备两张表"cmd"、"error_log"
create table cmd(
id int primary key auto_increment,
user varchar(16) not null,
permi char(4) not null,
cmd varchar(60) not null,
sub_time datetime,
success enum("yes","no") not null default "no"
);
create table error_log(
id int primary key auto_increment,
error_cmd varchar(60),
error_time datetime
);
🥝创建触发器,当"cmd"表中的"cuccess"字段是"no",那么将触发执行"error_log"表的插入操作
delimiter %%
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
if new.success="no" then # 如果即将插入的记录的"success"字段是"no",则执行下面的语句
insert error_log(error_cmd,error_time) value(new.cmd,new.sub_time); # 加分号
end if;
end %%
delimiter ;
🥝开始模拟插入记录
insert cmd(user,permi,cmd,sub_time,success) value
("shawn","0644","ls -l /root",now(),"yes"),
("shawn","0644","ps -elf",now(),"yes"),
("shawn","0644","groupadd xing",now(),"no"),
("shawn","0644","cat /etc/gshadow",now(),"no");
🥝查看"error_log"
select * from error_log;
7.删除触发器
drop trigger tri_after_insert_cmd
- 删除之后,再向"cmd"表中插入记录,字段"success"为"no"数据所在的行就不会再添加到"error_log"中去了
三.事务
1.什么是事务
- 事务是由一条或多条SQL语句组成的逻辑执行单元, 可以比喻成一个容器, 里面放的就是一堆SQL语句, 这些语句要么全部执行成功, 要么一个都无法执行成功(原子性)
2.为什么使用事务
- 对数据进行一系列的操作的时候, 为了防止这些操作中部分操作成功而另一些操作失败, 从而造成数据的不正确性, 这个时候我们就需要使用事务将其回滚到原来的状态
3.事务的四大特征 (ACID)
- 原子性(Atomicity) : 事务是一段程序的最小执行单元, 不可再分(就如同自然界的原子不可再分), 所以事务中的操作要么都成功, 要么都失败
- 一致性(Consistency) : 事务的执行, 必须使数据库从一个一致性状态, 变成另一个一致性状态, 一致性是通过原子性来保证的
- 隔离性(Lsolation) : 各个事务的执行互不干扰, 任意一个事务的内部操作对其他并发的事务, 都是隔离的; 也就是说 : 并发执行的事务之间不能看到对方的中间状态, 并发执行的事务之间不能相互影响
- 持续性(Durability) : 也叫"持久性", 指事务一旦提交, 对数据的任何改变都记录到永久存储器中, 通常是物理数据库
4.如何使用事务
- 关键字
🍎开启事务
begin; # 或者下面的语句
start transaction;
🍎事务回滚(回滚到之前的状态,并关闭事务)
rollback; # 回滚 + 关闭
🍎事务提交(将修改提交,并关闭事务)
commit; # 提交 + 关闭
由上面关键字可以看出, 一个事务的开始对应一个回滚或者提交, 之后就需要重新开启事务
- 银行余额示例
🍎先创建一个用户余额表并插入记录
create table user(
id int primary key auto_increment,
name varchar(16) not null,
balance int not null
);
insert user(name,balance) value
("shawn",150000),
("song",20000),
("xing",520022),
("hai",10000);
select * from user; # 查看一下所有记录
🍎开启事务
begin; # 或者 start transaction;
🍎更新记录
update user set balance=100 where name="shawn";
update user set balance=100 where name="song";
update user set balance=100 where name="xing";
update user set balance=200 where name="hai";
select * from user; # 查看一下是否修改成功
🍎事务回滚
rollback; # 回滚之后, 该事务就关闭了
select * from user; # 查看一下是否回滚到原来的数据
🍎再开启一个事务
begin;
🍎再次对数据进行更新
update user set balance=200 where name="shawn";
update user set balance=200 where name="song";
commit; # 事务提交, 并关闭了该事务
select * from user; # 查看数据的变化
rollback; # 再次使用事务回滚将不在起作用, 因为事务已经关闭了
select * from user;
四.存储过程
1.什么是存储过程
- 存储过程中包含了一系列的 SQL 语句, 就类似于 Python 中定义的函数, 通过调用存储过程名(函数名)来执行其内部的一堆 sql 语句(Python代码)
2.使用存储过程的优缺点
- 优点 :
- 用于替代程序写的 SQL 语句, 实现程序与 SQL 语句的解耦
- 基于网络的传输, 只传"存储过程名"比传一堆的 SQL 语句的数据量小的多
- 缺点 :
- 程序的可扩展性非常低
3.应用程序与数据库结合使用的三种开发模式
- 第一种
"应用程序" : 程序员自己写代码开发
"mysql" : 提前写好的存储过程, 提供给程序来调用
🔰优点 : 提升了开发效率, 执行效率提升(只传输'存储过程名')
🔰缺点 : 可扩展性查, 可能使用者的一些变动就需要修改整个存储过程
- 第二种
程序员在开发应用程序的过程中涉及到的数据库操作也是自己手动编写
🔰优点 : 扩展性很高
🔰缺点 : 开发效率低, 编写SQL语句太过繁琐, 且有些重复, 后期维护也不方便
- 第三种
程序员开发程序时只写程序代码, 而sql语句是基于别人写好的框架直接拿过来使用 (例如使用ORM框架)
🔰优点 : 开发效率比第一种和第二种都要高
🔰缺点 : 语句的可扩展性差, 并可能出现效率低下的问题
4.创建存储过程 (无参)
- 语法
delimiter %% # 更换默认结束符
create procedure [存储过程名]()
begin
[一堆SQL语句]
end %%
delimiter ; # 将默认结束符改回来
- 简单示例
🍅先创建一个表, 在插入几条子记录
create table text(
id int primary key auto_increment,
name varchar(100) not null,
sub_time datetime);
insert text(name,sub_time) value
("python详解",now()),
("Java入门",now()),
("派大星的故事",now()),
("小江放牛羊",now());
🍅创建无参存储过程,对"text"表进行一些操作
delimiter %%
create procedure p01()
begin
select * from text;
insert text(name,sub_time) value("杀牛羊手法2",now());
end %%
delimiter ;
🔰在"mysql"中调用
call p01();
🔰在"Python"中的"pymysql"中调用
# 首先导入模块并连接数据库
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123456",
database="test03",
charset="utf8"
)
# 得到游标
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p01") # 调用存储过程
print(cursor.fetchall()) # 拿到结果并输出
cursor.close() # 关闭游标
conn.close() # 关闭连接
- mysql 中调用
5.创建存储过程 (有参)
- 三种参数类型
"in" : 仅用于传入参数 (声明后面的变量为外部参数)
"out" : 仅用于返回值 (声明后面的变量是一个返回值,需要使用"set"声明)
"inout" : 既可以传入时使用有可以返回值时使用 (声明后面的变量可以但参数也可以当返回值,需要"set"声明)
- 语法
delimiter %%% # 修改默认结束符
create procedure [存储过程名](
in|out|inout [参数名] [类型],
.....(可以多个参数),
......
)
begin
[sql语句]
end %%%
delimiter ; # 将默认结束符改回来
- in : 传入参数示例
🍅创建有参存储过程
delimiter %%%
create procedure p02(
in num01 int
)
begin
select * from text where id=num01;
end %%%
delimiter ;
🍅在"mysql"中直接调用
call p02(4);
🍅"Python"中使用"pymysql"模块调用
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123456",
database="test03",
charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.callproc("p02",(4,)) # 参数传入的是一个元组
print(cursor.fetchall())
cursor.close()
conn.close()
- out : 返回值示例 (in+out)
🍅创建有参存储过程
delimiter %%%
create procedure p03(
in num01 int,
out res01 int
)
begin
select * from text where id=num01;
set res01=num01+1;
end %%%
delimiter ;
🍅在"mysql"中调用
call p03(4,@res01); # @res01 表示定义一个全局变量来接收返回值
select @res01; # 查看这个返回值
🍅在"python"中使用"pymysql"调用
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123456",
database="test03",
charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
res = ""
cursor.callproc("p03", (4, res)) # 第二个参数是用来接收返回值的
print(cursor.fetchall())
cursor.execute("select @_p03_0,@_p03_1;") # "@_p03_0"代表的是传入的第一个参数,"@_p03_1"代表的是第二的参数,也就是返回值
print(cursor.fetchall())
cursor.close()
conn.close()
- inout : 能传能返回示例
🍅创建存储过程
delimiter %%%
create procedure p04(
inout num01 int
)
begin
select * from text where id>num01;
set num01=num01+num01;
end %%%
delimiter ;
🍅在"mysql"中调用
set @res=2; # 先定义一个全局变量
call p04(@res); # 然后传进去,一方面当参数, 一方面接收返回值
select @res # 查看这个返回值
🍅在"Python"使用"pymysql"调用
import pymysql
conn = pymysql.connect(
host="127.0.0.1",
port=3306,
user="root",
password="123456",
database="test03",
charset="utf8"
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
res = 2 # 定义一个变量
cursor.callproc("p04", (res,)) # 传入这个变量,可当参数,也可接收返回值
print(cursor.fetchall())
cursor.execute("select @_p04_0;") # 查看返回值
print(cursor.fetchall())
cursor.close()
conn.close()
6.删除存储过程
- 语法
drop procedure [存储过程名];
- 示例
drop procedure p01;
五.函数
1.什么是函数
- 上面介绍的存储过程类似于 python 中的自定义的函数
- 而这里的函数指的是 mysql 提供的内置函数
2.常用内置函数介绍
👉🏻mysql 内置函数官方文档👈🏻
👉🏻mysql 内置函数中文文档👈🏻
- 数学函数
ROUND(x,y) # 返回参数x的四舍五入的有y位小数的值
RAND() # 返回 0 到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值
- 聚合函数(常用于GROUP BY从句的SELECT查询中) 👉🏻使用示例
AVG(col) # 返回指定列的平均值
COUNT(col) # 返回指定列中非NULL值的个数
MIN(col) # 返回指定列的最小值
MAX(col) # 返回指定列的最大值
SUM(col) # 返回指定列的所有值之和
GROUP_CONCAT(col) # 返回由属于一组的列值连接组合而成的结果
- 字符串函数
CHAR_LENGTH(str) # 返回值为字符串str 的长度,长度的单位为字符,一个多字节字符算作一个单字符。
CONCAT(str1,str2,...) # 字符串拼接,如有任何一个参数为NULL,则返回值为 NULL
CONCAT_WS(separator,str1,str2,...) # 字符串拼接（自定义连接符）
CONCAT_WS() # 不会忽略任何空字符串 (然而会忽略所有的 NULL）
CONV(N,from_base,to_base) # 进制转换
例如：SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
FORMAT(X,D) # 将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位,并将结果以字符串的形式返回。若 D 为 0,则返回结果不带有小数点,或不含小数部分
例如：SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr) # 在str的指定位置插入字符串, pos：要替换位置其实位置, len：替换的长度, newstr：新字符串
特别的：如果pos超过原字符串长度,则返回原字符串;如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr) # 返回字符串 str 中子字符串的第一个出现位置
LEFT(str,len) # 返回字符串str 从开始的len位置的子序列字符
LOWER(str) # 变小写
UPPER(str) # 变大写
REVERSE(str) # 返回字符串 str ，顺序和字符顺序相反。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len) # 不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
- 日期和时间函数
CURDATE()或CURRENT_DATE() # 返回当前的日期
CURTIME()或CURRENT_TIME() # 返回当前的时间
DAYOFWEEK(date) # 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) # 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) # 返回date是一年的第几天(1~366)
DAYNAME(date) # 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) # 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) # 返回time的小时值(0~23)
MINUTE(time) # 返回time的分钟值(0~59)
MONTH(date) # 返回date的月份值(1~12)
MONTHNAME(date) # 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() # 返回当前的日期和时间
QUARTER(date) # 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) # 返回日期date为一年中第几周(0~53)
YEAR(date) # 返回日期date的年份(1000~9999)
- 加密函数
MD5() # 计算字符串str的MD5校验和
PASSWORD(str) # 返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法
- 控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END # 如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END # 如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f) # 如果test是真，返回t；否则返回f
IFNULL(arg1,arg2) # 如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2) # 如果arg1=arg2返回NULL；否则返回arg1
ps : 流程控制练习
🍎准备表 : 将下面数据复制到"sql"后缀的文件中
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : student
Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001
Date: 2018-01-02 12:05:30
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` int(11) NOT NULL,
`c_name` varchar(255) DEFAULT NULL,
`t_id` int(11) DEFAULT NULL,
PRIMARY KEY (`c_id`),
KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_id` int(10) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
`num` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) NOT NULL,
`s_name` varchar(255) DEFAULT NULL,
`s_age` int(10) DEFAULT NULL,
`s_sex` char(1) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` int(10) NOT NULL,
`t_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');
🍎创建一个库,并导入该文件
create database school_test; # 创建一个库
use school_test
source J:\MySql\mysql-5.6.48-winx64\data\sql_file\school_test.sql # 导入该文件(需要文件路徑)
show tables; # 查看是否导入成功
select * from student;
select * from teacher;
🍎统计各科各分数段人数, 显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
# 以各学科分组,显示课程ID和课程名字,于是需要将course与score链表,使用流程控制+sum()函数统计各分数段的人数
select course.c_id,course.c_name,
sum(case when (num between 85 and 100) then 1 else 0 end) as "[100-85]",
sum(case when (num between 70 and 84) then 1 else 0 end) as "[85-70]",
sum(case when (num between 60 and 69) then 1 else 0 end) as "[70-60]",
sum(case when num<60 then 1 else 0 end) as "[ <60]"
from score,course where score.c_id=course.c_id
group by score.c_id;
3.重点函数 : date_format( ) 介绍
- 语法
select date_format([date],[format]) # 将传入的时间(date)按照传入的格式(format)进行转换
- 基本使用示例
select date_format("2021-02-22","%W %M %Y") as time; # 星期 月 年
select date_format("2020-01-25 11:14:23","%H:%i:%s") as time; # 时 分 秒
select date_format("2020-01-25 11:14:23","%D %y %d %m %b %j") as time; # 日 年 日 月 月 日
select date_format("1111-11-11 11:11:11","%H %k %I %r %T %S %w")as time;
- 示例
🍅将"text"表中的"sub_time"提取出来转换成"时分秒"格式进行分组,并统计个数
select * from text;
select date_format(sub_time,"%H:%i:%s") as time,count(id)
from text
group by time;
4.自定义函数 (删除 + 调用)
自定义函数不同于存储过程, 函数中不能写 sql 语句(否则报错), 函数仅仅是 sql 中被应用的功能; 如果需要在 begin...end 中放置 sql 语句, 使用存储过程就行了
- 语法
delimiter %%%
create function [函数名](
[参数1] [类型],
[参数2] [类型])
returns [类型]
begin
[一系列代码]
end %%%
delimiter ;
- 示例1
🍅定义一个函数(求两个数和)
delimiter %%% # 修改默认结束符
create function f01(
num01 int,
num02 int) # 定义参数以及类型
returns int # 设置返回值的类型为 int
begin
declare num03 int; # 声明变量 num03 为 int 类型
set num03=num01+num02;
return(num03);
end %%%
delimiter ; # 将默认结束符修改回来
🍅调用自定义函数
select f01(2,4); # 查询+调用
select f01(2,4) into @res; # 调用并将返回值给变量 @res
select @res; # 查看变量值
- 示例二
🍅定义一个函数
delimiter %%%
create function f012(
num01 int)
returns int
begin
declare res int default 0; # 声明变量 res 为 int 类型,如果传入的不是 int,默认返回 0
if num01 = 10 then
set res = num01*num01;
elseif num01 = 20 then
set res = num01*num01;
elseif num01 = 30 then
set res = num01*num01;
else
set res = num01;
end if;
return(res);
end %%%
delimiter ;
🍅调用
select f012(10);
select f012(20);
select f012(30);
select f012(40);
select f012("ad");
- 删除函数
drop function [函数名];
drop function f012;
六.流程控制
1.条件语句 : if
delimiter %%%
create procedure p01(in num int)
begin
if num = 1 then
select 1;
elseif num = 2 then
select 2;
else select 3;
end if;
end %%%
delimiter ;
2.循环语句
- while 循环
delimiter %%%
create procedure p02()
begin
declare num int;
set num = 0;
while num < 3 do
select num;
set num = num + 1;
end while;
end %%%
delimiter ;
- repeat 循环
delimiter %%%
create procedure p03()
begin
declare num int;
set num = 0;
repeat
select num;
set num = num + 1;
until num > 3 # 结束条件:当num>3时结束
end repeat;
end %%%
delimiter ;
- loop 循环
delimiter %%%
create procedure p04()
begin
declare num int default 0;
myloop:loop
set num=num+1;
if num>=3 then
leave myloop; # 当 num>=3 结束循环
end if;
select num;
end loop myloop;
end %%%
delimiter ;
---end---
