MySQL快速上手[学习笔记]（三）

Love The Way You Lie 2023-02-18 09:40 90阅读 0赞

**前言：**  
课程：[《三大主流数据库快速上手》][Link 1]（点击跳转官方课程，b站有资源，可自搜）  
[笔记][Link 2]（点击跳转豆丁网）  
此处是个人学习笔记，用作回顾用途

**目录：**  
七、函数  
八、视图  
九、触发器  
十、存储过程

# 七、函数 #

**1.数学函数**

<table> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>作用</td> 
  </tr> 
  <tr> 
   <td>ABS(x)</td> 
   <td>返回 x 的绝对值</td> 
  </tr> 
  <tr> 
   <td>CEIL(x),CEILING(x)</td> 
   <td>返回大于或等于 x 的最小整数</td> 
  </tr> 
  <tr> 
   <td>FLOOR</td> 
   <td>返回小于或等于 x 的最大整数</td> 
  </tr> 
  <tr> 
   <td>RAND()</td> 
   <td>返回 0~1 的随机数</td> 
  </tr> 
  <tr> 
   <td>RAND(X)</td> 
   <td>返回 0~1 的随机数，x 值相同时返回的随机数相同</td> 
  </tr> 
  <tr> 
   <td>SIGN(X)</td> 
   <td>返回 x 的符号。x 是负数、0、正数分别返回-1,0 和 1</td> 
  </tr> 
  <tr> 
   <td>PI()</td> 
   <td>返回圆周率（3.141593</td> 
  </tr> 
  <tr> 
   <td>TRUNCATE(x,y)</td> 
   <td>返回数值 x 保留到小数点后 y 位的值</td> 
  </tr> 
  <tr> 
   <td>ROUND(x)</td> 
   <td>返回离 x 最近的整数</td> 
  </tr> 
  <tr> 
   <td>ROUND(x,y)</td> 
   <td>保留 x 小数点后 y 位的值，但截断时要进行四舍五入</td> 
  </tr> 
  <tr> 
   <td>POW(x,y),POWER(x,y)</td> 
   <td>返回 x 的 y 次方(x^y)</td> 
  </tr> 
  <tr> 
   <td>SQRT(x)</td> 
   <td>返回 x 的平方根</td> 
  </tr> 
  <tr> 
   <td>EXP(x)</td> 
   <td>返回 e 的 x 次方(e^x)</td> 
  </tr> 
  <tr> 
   <td>MOD(x,y)</td> 
   <td>返回 x 除以 y 的余数</td> 
  </tr> 
  <tr> 
   <td>LOG(x)</td> 
   <td>返回自然对数(以 e 为底的对数)</td> 
  </tr> 
  <tr> 
   <td>LOG10(x)</td> 
   <td>返回以 10 为底的对数</td> 
  </tr> 
  <tr> 
   <td>RADIANS(x)</td> 
   <td>将角度转换成弧度</td> 
  </tr> 
  <tr> 
   <td>DEGREES(x)</td> 
   <td>将弧度转换成角度</td> 
  </tr> 
  <tr> 
   <td>SIN(x)</td> 
   <td>求正弦值</td> 
  </tr> 
  <tr> 
   <td>ASIN(x)</td> 
   <td>求反正弦值</td> 
  </tr> 
  <tr> 
   <td>COS(x)</td> 
   <td>求余弦值</td> 
  </tr> 
  <tr> 
   <td>ACOS(x)</td> 
   <td>求反余弦值</td> 
  </tr> 
  <tr> 
   <td>TAN(x)</td> 
   <td>求正切值</td> 
  </tr> 
  <tr> 
   <td>ATAN(x),ATAN2(x,y)</td> 
   <td>求反正切值</td> 
  </tr> 
  <tr> 
   <td>COT(x)</td> 
   <td>求余切值</td> 
  </tr> 
 </tbody> 
</table>

例子：

select ABS(1),ABS(-1),PI();

select sqrt(16),mod(3,2);

select ceil(8.9),floor(8.9);

**2.字符串函数**

<table> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>作用</td> 
  </tr> 
  <tr> 
   <td>CHAR_LENGTH(s)</td> 
   <td>返回字符串 s 的字符数</td> 
  </tr> 
  <tr> 
   <td>LENGTH(s)</td> 
   <td>返回字符串 s 的长度</td> 
  </tr> 
  <tr> 
   <td>CONCAT(s1,s2,...)</td> 
   <td>将字符串 s1,s2 等多个字符串合并为一个字符串</td> 
  </tr> 
  <tr> 
   <td>CONCAT_WS(x,s1,s2,...)</td> 
   <td>同上，但是每个字符串直接要加上 x</td> 
  </tr> 
  <tr> 
   <td>INSERT(s1,x,len,s2)</td> 
   <td>将字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串</td> 
  </tr> 
  <tr> 
   <td>UPPER(s),UCASE(s)</td> 
   <td>将字符串 s 的所字母都变成大写字母</td> 
  </tr> 
  <tr> 
   <td>LOWER(s),LCASE(s)</td> 
   <td>将字符串 s 的所字母都变成小写字母</td> 
  </tr> 
  <tr> 
   <td>LEFT(s,n)</td> 
   <td>返回字符串 s 的前 n 个字符</td> 
  </tr> 
  <tr> 
   <td>RIGHT(s,n)</td> 
   <td>返回字符串 s 的后 n 个字符</td> 
  </tr> 
  <tr> 
   <td>LPAD(s1,len,s2)</td> 
   <td>字符串 s2 来填充 s1 的开始处，使字符串长度达到 len</td> 
  </tr> 
  <tr> 
   <td>RPAD(s1,len,s2)</td> 
   <td>字符串 s2 来填充 s1 的结尾处，使字符串长度达到 len</td> 
  </tr> 
  <tr> 
   <td>LTRIM(s)</td> 
   <td>去掉字符串 s 开始处的空格</td> 
  </tr> 
  <tr> 
   <td>RTRIM(s)</td> 
   <td>去掉字符串 s 结尾处的空格</td> 
  </tr> 
  <tr> 
   <td>TRIM(s)</td> 
   <td>去掉字符串 s 开始和结尾处的空格</td> 
  </tr> 
  <tr> 
   <td>TRIM(s1 from s)</td> 
   <td>去掉字符串 s 中开始和结尾处的字符串 s1</td> 
  </tr> 
  <tr> 
   <td>REPEAT(s,n)</td> 
   <td>将字符串 s 重复 n 次</td> 
  </tr> 
  <tr> 
   <td>SPACE(n)</td> 
   <td>返回 n 个空格</td> 
  </tr> 
  <tr> 
   <td>REPLACE(s,s1,s2)</td> 
   <td>用字符串 s2 替代字符串 s 中的字符串 s1</td> 
  </tr> 
  <tr> 
   <td>STRCMP(s1,s2)</td> 
   <td>比较字符串 s1 和 s2</td> 
  </tr> 
  <tr> 
   <td>SUBSTRING(s,n,len),MID(s,n,len)</td> 
   <td>获取字符串 s 中的 n 个位置开始长度为 len 的字符串</td> 
  </tr> 
  <tr> 
   <td>LOCATE(s1,s),POSITION(s1 IN s),INSTR(s,s1);</td> 
   <td>从字符串 s 中获取 s1 的开始位置</td> 
  </tr> 
  <tr> 
   <td>REVERSE(s)</td> 
   <td>将字符串 s 的顺序反过来</td> 
  </tr> 
  <tr> 
   <td>ELT(n,s1,s2,...)</td> 
   <td>返回第 n 个字符串</td> 
  </tr> 
 </tbody> 
</table>

例子：

#concat
    select concat('sha','ng','hai'),concat_ws('-','sha','ng','hai');

#insert
    select insert('shanghai',6,3,'main');

#upper&lower
    select upper('mysql'),lower('MYSQL');

#reverse
    select reverse('abcdefg');

**3.日期函数**

<table> 
 <tbody> 
  <tr> 
   <td>函数</td> 
   <td>作用</td> 
  </tr> 
  <tr> 
   <td>CURDATE(),CURRENT_DATE()</td> 
   <td>返回当前日期</td> 
  </tr> 
  <tr> 
   <td>CURTIME(),CURRENT_TIME()</td> 
  </tr> 
  <tr> 
   <td>返回当前时间</td> 
   <td>NOW(),CURRENT_TIMESTAMP(),LOC ALTIME(),SYSDATE(),LOCALTIMEST</td> 
  </tr> 
  <tr> 
   <td>AMP()</td> 
   <td>返回当前时间和日期</td> 
  </tr> 
  <tr> 
   <td>UNIX_TIMESTAMP()</td> 
   <td>以 UNIX 时间戳的形式返回当前时间</td> 
  </tr> 
  <tr> 
   <td>UNIX_TIMESTAMP(d)</td> 
   <td>将时间 d 以 UNIX 时间戳的形式返回</td> 
  </tr> 
  <tr> 
   <td>FROM-UNIXTIME(d)</td> 
   <td>把 UNIX 时间戳的时间转换为普通格式的时间</td> 
  </tr> 
  <tr> 
   <td>UTC_DATE()</td> 
   <td>返回 UTC(Universal Coordinated Time)日期</td> 
  </tr> 
  <tr> 
   <td>UTC_TIME()</td> 
   <td>返回 UTC 时间</td> 
  </tr> 
  <tr> 
   <td>MONTH(d)</td> 
   <td>返回日期 d 中的月份值，范围是 1~12</td> 
  </tr> 
  <tr> 
   <td>MONTHNAME(d)</td> 
   <td>返回日期 d 中的月份名称，如 February</td> 
  </tr> 
  <tr> 
   <td>DAYNAME(d)</td> 
   <td>返回日期 d 是星期几，若 Monday</td> 
  </tr> 
  <tr> 
   <td>DAYOFWEEK(d)</td> 
   <td>返回日期 d 是星期几，1 表示星期日</td> 
  </tr> 
  <tr> 
   <td>WEEKDAY(d)</td> 
   <td>返回日期 d 是星期几，0 表示星期一</td> 
  </tr> 
  <tr> 
   <td>WEEK(d)</td> 
   <td>计算日期 d 是本年的第几个星期，范围是 0~53</td> 
  </tr> 
  <tr> 
   <td>WEEKOFYEAR(d)</td> 
   <td>计算日期 d 是本年的第几个星期，范围是 1~53</td> 
  </tr> 
  <tr> 
   <td>DAYOFYEAR(d)</td> 
   <td>计算日期 d 是本年的第几天</td> 
  </tr> 
  <tr> 
   <td>DAYOFMONTH(d)</td> 
   <td>计算日期 d 是本月的第几天</td> 
  </tr> 
  <tr> 
   <td>YEAR(d)</td> 
   <td>返回日期 d 中的年份值</td> 
  </tr> 
  <tr> 
   <td>QUARTER(d)</td> 
   <td>返回日期 d 是第几季度，范围是 1~4</td> 
  </tr> 
  <tr> 
   <td>HOUR(t)</td> 
   <td>返回时间 t 中的小时值</td> 
  </tr> 
  <tr> 
   <td>MINUTE(t)</td> 
   <td>返回时间 t 中的分钟值</td> 
  </tr> 
  <tr> 
   <td>SECOND(t)</td> 
   <td>返回日期 t 中的秒钟值</td> 
  </tr> 
  <tr> 
   <td>EXTRACT(type FROM d)</td> 
   <td>从日期d 中获取指定的值，type 指定返回的值，如 YEAR</td> 
  </tr> 
  <tr> 
   <td>TIME_TO_SEC(t)</td> 
   <td>将时间转换成秒</td> 
  </tr> 
  <tr> 
   <td>SEC_TO_TIME(s)</td> 
   <td>将秒为单位的时间 s 转换成时分秒的格式</td> 
  </tr> 
  <tr> 
   <td>TO_DAYS(d)</td> 
   <td>计算日期 d~0000 年 1 月 1 日的天数</td> 
  </tr> 
  <tr> 
   <td>FROM_DAYS(n)</td> 
   <td>计算从 0000 年 1 月 1 日开始 n 天后的日期</td> 
  </tr> 
  <tr> 
   <td>DATEDIFF(d1,d2)</td> 
   <td>计算日期 d1~d2 之间相隔的天数</td> 
  </tr> 
  <tr> 
   <td>ADDDATE(d,n)</td> 
   <td>计算起始日期 d 加上 n 天的日期</td> 
  </tr> 
  <tr> 
   <td>ADDDATE(d,INTERVAL expr type)</td> 
   <td>计算起始日期 d 加上一个时间段后的日期</td> 
  </tr> 
  <tr> 
   <td>DATE_ADD(d,INTERVAL expr type)</td> 
   <td>同上</td> 
  </tr> 
  <tr> 
   <td>SUBDATE(d,n)</td> 
   <td>计算起始日期 d 减去 n 天的日期</td> 
  </tr> 
  <tr> 
   <td>SUBDATE(d,INTERVAL expr type)</td> 
   <td>计算起始日期 d 减去一个时间段后的日期</td> 
  </tr> 
  <tr> 
   <td>ADDTIME(t,n)</td> 
   <td>计算起始时间 t 加上 n 秒的时间</td> 
  </tr> 
  <tr> 
   <td>SUBTIME(t,n)</td> 
   <td>计算起始时间 t 减去 n 秒的时间</td> 
  </tr> 
  <tr> 
   <td>DATE_FORMAT(d,f)</td> 
   <td>照表达式 f 的要求显示日期 d</td> 
  </tr> 
  <tr> 
   <td>TIME_FORMAT(t,f)</td> 
   <td>照表达式 f 的要求显示时间 t</td> 
  </tr> 
  <tr> 
   <td>GET_FORMAT(type,s)</td> 
   <td>根据字符串 s 获取 type 类型数据的显示格式</td> 
  </tr> 
 </tbody> 
</table>

例子：

select curdate(),current_time(),now();

select month('2020-06-19 09:07:47');

**4.条件判断函数**  
（1）IF(exp,v1,v2)

（2）IFNULL(v1,v2)

（3）CASE  
①case when expr1 then v1 \[when expr2 then v2\]… else vn end（相当于 if）

②case expr1 when e1 then v1 \[when e2 then v2\]…else vn end（相当于 switch）

**5.系统信息函数**

<table> 
 <tbody> 
  <tr> 
   <td>函数 </td> 
   <td> 作用</td> 
  </tr> 
  <tr> 
   <td>VERSION() </td> 
   <td> 返回数据库的版本号</td> 
  </tr> 
  <tr> 
   <td>CONNECTION_ID() </td> 
   <td> 返回服务器的连接数</td> 
  </tr> 
  <tr> 
   <td>DATABASE(),SCHEMA() </td> 
   <td> 返回当前数据库名</td> 
  </tr> 
  <tr> 
   <td>USER(),SYSTEM_USER(),SESSION_USER() </td> 
   <td> 返回当前用户</td> 
  </tr> 
  <tr> 
   <td>CURRENT_USER(),CURRENT_USER </td> 
  </tr> 
  <tr> 
   <td>CHARSET(str) </td> 
   <td> 返回字符串 str 的字符集</td> 
  </tr> 
  <tr> 
   <td>COLLATION(str) </td> 
   <td> 返回字符串 str 的字符排列方式</td> 
  </tr> 
  <tr> 
   <td>LAST_INSERT_ID() </td> 
   <td> 返回最近生产的 auto_increment 值</td> 
  </tr> 
 </tbody> 
</table>

例子：

**6.加密函数**  
（1）PASSWORD(str)

select password('root');

\#mysql8.0没有这个函数，使用authentication\_string

select user,host,authentication_string from mysql.user;

（2）MD5(str)

select md5('111222333');

（3）ENCODE(str,pwd\_str)和DECODE(scty,pwd\_str)

select encode('root','r');
    #（如果要存储，则需存储的字段类型为 blog）

select decode(encode('root','r')'r');
    #（对 encode 加密后的数据进行解密）

mysql8提示找不到encode()/decode()方法，可以尝试更换connnector版本为5.x，或者用md5()等加密函数替代。  
原因：[mysql8安装时，可选择两套不同的加解密][mysql8](点击跳转参考链接)

**7.其它函数**  
（1）format(x,n)  
对 x 进行格式化，保留 n 位小数

select format(3896.59,3), format(3896.59,1);

![在这里插入图片描述][20200618085812751.png]  
（不够补位–0，采取四舍五入方式）

（2）IP 地址和数字互相转化  
inet\_aton(ip)  
inet\_ntoa(n)

select inet_aton('192.168.10.108');

![在这里插入图片描述][2020061809002272.png]

select inet_ntoa('3232238188');

![在这里插入图片描述][2020061809010446.png]  
（3）重复执行指定操作函数 benchmark(n,expr)  
执行n次expr并测算时间

select benchmark(100000000,now());

![在这里插入图片描述][2020061809074155.png]  
BENCHMARK()的返回值总是0，但是mysql客户端会打印一行来输出语句的大概执行时间。

# 八、视图 #

语法：  
create  
\[algorithm = \{undefined | merge | temptable\}\]  
view 视图名 \[\{属性清单\}\]  
as select 语句  
\[with \[cascaded|local\] check option\];

algorithm:选择的算法  
with check option:表示更新视图时要保证在该视图的权限范围之内  
undfined：表示 MySQL 自动选择所需使用的算法  
merge：表示将视图的语句与视图的定义合并，使得视图定义的某一部分取代语句的对应部分  
temptable：将视图的结果存入临时表  
cascaded：表示更新视图时要满足所有相关视图和表的条件  
local：表示更新视图时，要满足该视图本身的定义的条件即可

**（一）创建视图**  
**1.查看是否有权限创建**

select select_priv,create_view_priv from mysql.user where user ='root'

![在这里插入图片描述][20200628172624855.png]  
**2.创建视图**  
①在单表中创建视图

create view book_view1 as select * from book;

create view book_view2(name) as select bookname from book;

②在多表中创建视图

create algorithm=merge view 
    book_view3(bookname,stuname)
    as select bookname,stuname 
    from book as a,stu as b where a.stuid = b.stuid;

**（二）查看视图**  
1.查看视图内容

select * from book_view1;

![在这里插入图片描述][2020062817332462.png]  
2.desc查看视图结构

desc book_view1;

![在这里插入图片描述][20200628173348884.png]  
3.查看是虚拟表还是实体表

show table status like 'book_view1' \G;

![在这里插入图片描述][20200628173536665.png]  
（book\_view1 是虚拟表）

show table status like 'book' \G;

![在这里插入图片描述][20200628174819914.png]  
（book是实体表）

4.在view和views中查看视图

show create view book_view1 \G;

![在这里插入图片描述][20200628174944130.png]

select * from information_schema.views \G;

![在这里插入图片描述][20200628175038776.png]  
（三）修改视图

1.通过 create or replace view 修改  
（如果没有则创建，有则修改）

create or replace algorithm=temptable 
    view book_view1(name) 
    as select bookname from book;

2、通过 alter 修改  
（只能修改不能创建）

alter view book_view2(name)
    as select stuname
    from stu
    with check option;

（四）更新视图（更新视图实际上是更新表）

select * from book;

![在这里插入图片描述][20200628175500616.png]

create view book_view4(name) 
    as select bookname from book where id=1;

select * from book_view4;

![在这里插入图片描述][20200628175550167.png]

update book_view4 set name ='python教程';

select * from book_view4;

![在这里插入图片描述][20200628175618784.png]  
**（五）删除视图**

drop view if exists book_view3;

\#判断用户是否有删除权限

select drop_priv from mysql.user where user= 'root';

![在这里插入图片描述][20200628175836498.png]

# 九、触发器 #

触发器的作用：修改一个表的数据，相关联的表的相应数据也会跟着修改。

官方一点的说法：触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

[在MySQL中，创建触发器语法如下：][MySQL]（点击跳转参考文章）  
CREATE TRIGGER trigger\_name trigger\_time trigger\_event  
ON tbl\_name FOR EACH ROW trigger\_stmt

其中：  
trigger\_name：标识触发器名称，用户自行指定；  
trigger\_time：标识触发时机，取值为 BEFORE 或 AFTER；  
trigger\_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；  
tbl\_name：标识建立触发器的表名，即在哪张表上建立触发器；  
trigger\_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。  
由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER 、DELETE。  
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

**（一）创建和使用触发器**  
**1、创建单条执行语句的触发器**  
语法：create trigger 触发器名 before | after 触发事件  
on 表名 for each row 执行语句

/*创建account表*/
    CREATE TABLE account
    (
    acct_num INT,
    amount DECIMAL(10,2)
    );
    /*创建myname表*/
    CREATE TABLE myname(
    id int(11) DEFAULT NULL,
    name char(20) DEFAULT NULL
    );

要求：在向表account新增数据后，便会触发myname表新增一条记录1 ,‘after insert’

CREATE TRIGGER trig_insert AFTER INSERT ON account
    FOR EACH ROW INSERT INTO myname VALUES (1,'after insert');

insert into account values('1','100');

select * from account;

![在这里插入图片描述][20200628191555991.png]

select * from myname;

![在这里插入图片描述][20200628191611251.png]

**2、创建多条执行语句的触发器**  
语法：create trigger 触发器名 before | after 触发事件  
on 表 名 for each row begin  
执行语句  
end  
（在执行多条语句时，会以分号结束，为解决问题，delimiter \#）

设有4个测试数据表：

CREATE TABLE test1(a1 INT);
    CREATE TABLE test2(a2 INT);
    CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
    CREATE TABLE test4(
    a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    b4 INT DEFAULT 0
    );

要求：创建一个包含多个执行语句的触发器

/*创建触发器*/
    DELIMITER //
    CREATE TRIGGER testref BEFORE INSERT ON test1
    FOR EACH ROW BEGIN
    INSERT INTO test2 SET a2 = NEW.a1;
    DELETE FROM test3 WHERE a3 = NEW.a1;
    UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
    END
    //
    DELIMITER ;

/*分别向test3和test4表中插入测试数据*/
    INSERT INTO test3 (a3)
    VALUES (NULL), (NULL), (NULL), (NULL), (NULL),
    (NULL), (NULL), (NULL), (NULL), (NULL);
    INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

/*查看触发器未执行前的结果*/
    SELECT * FROM test1;
    SELECT * FROM test2;
    SELECT * FROM test3;
    SELECT * FROM test4;

/*向test1表中插入数据，激活触发器的调用事件调用触发器*/
    INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

/*查看触发器执行后的结果*/
    SELECT * FROM test1;
    SELECT * FROM test2;
    SELECT * FROM test3;
    SELECT * FROM test4;

**（二）查看触发器**  
1.show triggers  
![在这里插入图片描述][2020062819321169.png]  
2.通过 triggers 表查看

select * from information_schema.triggers \G;

![在这里插入图片描述][20200628193723148.png]  
3.查询指定的

select * from information_schema.triggers where trigger_name='trig_insert' \G;

![在这里插入图片描述][20200628195149268.png]  
**（三）删除触发器**

drop trigger trig_insert;

检查：

select * from information_schema.triggers where trigger_name='trig_insert' \G;

![在这里插入图片描述][20200628195319558.png]

# 十、存储过程 #

语法：  
create procedure sp\_name(\{proc\_parameter\[…\]\})  
\[characteristic …\] routine\_body

sp\_name：存储过程名称  
proc\_parameter：存储过程的参数列表  
characteristic：存储过程的特性  
routine\_body：SQL 语句的内容，可以用  
begin…end 来标志 SQL 语句的开始和结束  
proc\_parameter 由 3 部分组成：输入输出类型、参数名和参数类型  
形式：\[in | out | inout\] param\_name type

characteristic 取值：  
☞language sql：说明 routine\_body 是由 SQL 语句组成  
☞\[not\] deterministic：执行结果是否确定  
☞\{contains sql | no sql | reads sql data | modifies sql data\}：  
①contains sql：子程序包含 SQL 语句，但不包含读或写数据的 SQL\[默认\]  
②no sql：子程序中不包含 SQL 语句  
③reads sql data：包含可读数据的语句  
④modifies sql data：包含可写数据语句  
☞sql security \{definer | invoker\}：who 有权限来执行  
①definer：只有定义者方可执行\[默认\]  
②invoker：调用者可以执行  
☞comment ‘string’：注释信息

[**(一)创建存储过程**][Link 3]（点击跳转参考资料）  
**①简单的存储过程**  
语法：  
create procedure 存储过程名（参数）  
begin  
sql 语句…  
end;

delimiter //
    create procedure p_book()
    begin
    select * from book;
    end;
    //

调用存储过程的语法：  
call 存储过程名（实参）；

call p_book();

![在这里插入图片描述][20200628212538598.png]

**②带参数的存储过程(in、out 、inout)**  
in可以省略不写，默认就是in类型

delimiter //
    create procedure pro_book(in x int)
    begin
    select * from book where id=x;
    end
    //

call pro_book(3);

![在这里插入图片描述][20200628213200558.png]

调用out存储过程前，需要先定义一个变量：set @变量类名 = 值；  
calll 存储过程名（@变量名）；

select * from stu;
    insert into stu values('12','张三');

delimiter //
    create procedure pro_stu(out x int)
    begin
    select max(stuid) into x from stu where stuname = '张三';
    end
    //

set @x=1;
    call pro_stu(@x);
    select @x;

![在这里插入图片描述][2020062822262176.png]

delimiter //
    create procedure pro__stu(in a varchar(20),out b int)
    begin
    select max(stuid) into b from stu where stuname = a;
    end
    //
    set @b=1;
    call pro__stu('张三',@x)
    select @x;

![在这里插入图片描述][2020062822262176.png]  
**（二）创建存储函数**  
语法：  
create function sp\_name(\[func\_parameter\[…\]\])  
returns type  
\[characteristic…\] routine\_body

sp\_name：存储函数名称func\_parameter：存储函数参数列表  
returns type：指定返回值的类型  
func\_parameter 由 2 部分组成：参数名和参数类型  
形式：param\_name type

[mysql 创建函数时出现 Error Code : 1418 错误解决办法：][mysql _ Error Code _ 1418]（点击挑转参考资料）

SET GLOBAL log_bin_trust_function_creators = 1;

delimiter //
    create function name_from_stu(sid int)
    returns varchar(20)
    begin
    return(
    select stuname from stu
    where stuid = sid);
    end //

select name_from_stu(5)

![在这里插入图片描述][20200629073807392.png]

**（三）变量的使用**  
注意：在存储过程或存储函数中使用！！！）  
1、声明变量：  
语法：declare 参数名 \[,…\] 类型 \[default value\]

declare mysql int default 100;

2、赋值  
语法：set 变量名=值\[,变量名=值\]

set mysql = 10;

语法：select 字段\[,…\] into 变量名\[,…\] from 表名 where 条件

selext age into mysql from employee where e_no= 1001;

**(四)流程控制**

注意：在存储过程或存储函数中使用！！！）  
**1、IF 语句语法：**  
if 条件 then 执行语句  
else if 条件 then 执行语句  
…  
else 执行语句  
end if  
**2、CASE 语句(等值判断) 语法：**  
①  
case 变量  
when 变量值 then 执行语句  
…  
else 执行语句  
end case  
②  
case  
when 条件 then 执行语句  
…  
else 执行语句  
end case

**3、LOOP 语句**  
无限循环，直到遇到 leave 才停止  
语法：  
\[循环开始的标识:\]loop  
执行语句  
end loop \[循环结束标识\]

**4、LEAVE 语句例 子**  
save\_num loop  
执行语句  
条件判断  
leave save\_num  
end loop save\_num

**5、ITERATE 语句**  
（只能跳出本次循环）  
语法：  
iterate label

**6、REPEAT 语句**  
（跳出所有循环）  
语法：  
开始标志 repeat  
条件  
结束标志（end repeat）

**7、WHILE 语句语法**  
开始标志 while 条件  
执行语句  
end while

（五）查看存储过程和存储函数  
1、通过 show status 查看存储过程和函数状态  
语法：show \{procedure | function\} status \[like ‘pattern’\]

show procedure status like 'p_book' \G;

![在这里插入图片描述][20200629082618691.png]

2、通过 show create 查看存储过程和函数定义语法：  
show create \{procedure | function\} sp\_name

show create procedure p_book \G;

![在这里插入图片描述][20200629082314659.png]

3、通过 information\_schema.Routines 查看存储过程和函数信息  
语法：  
select \* from information\_schema.Routines where routine\_name =’sp\_name’

（六）删除存储过程和存储函数  
语法：drop \{procedure | function\} sp\_name

select * from information_schema.Routines where routine_name='p_book' \G;

![在这里插入图片描述][2020062908291565.png]

[Link 1]: http://www.ibeifeng.com/goods-630.html
[Link 2]: https://www.sogou.com/link?url=DSOYnZeCC_qmZv6AVu39peOlHJnTOpqP73xPPLQVgYlFhNG1zqiUdA..
[mysql8]: https://www.jianshu.com/p/6d18a85d7748
[20200618085812751.png]: https://img-blog.csdnimg.cn/20200618085812751.png
[2020061809002272.png]: https://img-blog.csdnimg.cn/2020061809002272.png
[2020061809010446.png]: https://img-blog.csdnimg.cn/2020061809010446.png
[2020061809074155.png]: https://img-blog.csdnimg.cn/2020061809074155.png
[20200628172624855.png]: https://img-blog.csdnimg.cn/20200628172624855.png
[2020062817332462.png]: https://img-blog.csdnimg.cn/2020062817332462.png
[20200628173348884.png]: https://img-blog.csdnimg.cn/20200628173348884.png
[20200628173536665.png]: https://img-blog.csdnimg.cn/20200628173536665.png
[20200628174819914.png]: https://img-blog.csdnimg.cn/20200628174819914.png
[20200628174944130.png]: https://img-blog.csdnimg.cn/20200628174944130.png
[20200628175038776.png]: https://img-blog.csdnimg.cn/20200628175038776.png
[20200628175500616.png]: https://img-blog.csdnimg.cn/20200628175500616.png
[20200628175550167.png]: https://img-blog.csdnimg.cn/20200628175550167.png
[20200628175618784.png]: https://img-blog.csdnimg.cn/20200628175618784.png
[20200628175836498.png]: https://img-blog.csdnimg.cn/20200628175836498.png
[MySQL]: https://mp.weixin.qq.com/s/-lSSHwNpdgg8kag6T5WlOw
[20200628191555991.png]: https://img-blog.csdnimg.cn/20200628191555991.png
[20200628191611251.png]: https://img-blog.csdnimg.cn/20200628191611251.png
[2020062819321169.png]: https://img-blog.csdnimg.cn/2020062819321169.png
[20200628193723148.png]: https://img-blog.csdnimg.cn/20200628193723148.png
[20200628195149268.png]: https://img-blog.csdnimg.cn/20200628195149268.png
[20200628195319558.png]: https://img-blog.csdnimg.cn/20200628195319558.png
[Link 3]: https://www.bilibili.com/video/BV1pb411i71k/?spm_id_from=333.788.videocard.3
[20200628212538598.png]: https://img-blog.csdnimg.cn/20200628212538598.png
[20200628213200558.png]: https://img-blog.csdnimg.cn/20200628213200558.png
[2020062822262176.png]: https://img-blog.csdnimg.cn/2020062822262176.png
[mysql _ Error Code _ 1418]: https://blog.csdn.net/explorercn/article/details/26260711
[20200629073807392.png]: https://img-blog.csdnimg.cn/20200629073807392.png
[20200629082618691.png]: https://img-blog.csdnimg.cn/20200629082618691.png
[20200629082314659.png]: https://img-blog.csdnimg.cn/20200629082314659.png
[2020062908291565.png]: https://img-blog.csdnimg.cn/2020062908291565.png