1 内置函数应用

—1.1 概念

在开发称之为“方法”，将一组逻辑语句放在方法体中，对外暴露的方法名。

—1.2 作用

1、隐藏代码实现细节
2、提高代码的重用性

—1.3 调用方法

select 函数名(参数) [from 表]

—1.4 关注点

函数名
函数功能

—1.5 分类

单行函数,例如： concat()、length()等。
分组函数,例如：sum()、count()等。
其他函数 now()

—1.6 单行函数

——1.6.1 字符函数

length

作用： 获取字节量，受到字符集影响(一个中文字符utf8占用3个字节，gbk则为2个)。
案例：
select length('abc');
select length('刘荣浩');
show variables like '%char%';查看字符集
select length('😀');4个字节
# 判断表种某列的字节,帮助我们确认数据类型,判断索引是否需要前缀。
select length(name) as len from world.city order by len desc ;
desc world.city;

concat

作用： 拼接字符串
案例：
select concat(user,"@",host) from mysql.user;
root@%
oldguo@10.0.0.%

备份命令
select concat(" mysqldump -uroot -p123 ",table_schema," ",table_name,">/bak/",table_schema,"_",table_name,".sql") as sqltext
from information_schema.tables where table_schema='world';
+--------------------------------------------------------------------------------+
| sqltext
|+--------------------------------------------------------------------------------+
| mysqldump -uroot -p123 world city >/bak/world_city.sql
|
| mysqldump -uroot -p123 world country >/bak/world_country.sql
|
| mysqldump -uroot -p123 world countrylanguage >/bak/world_countrylanguage.sql
|+--------------------------------------------------------------------------------+

upper&lower

作用：转换大小写
案例：
select upper('abc')
select lower('CDE')

substr

作用： 截取字符串
语法： substr(字符串,pos,[len]) substring(字符串,pos,[len])
案例：
select SUBSTR('sdsfndjskdk',5,3);结果：ndj
select SUBSTR('sdsfndjskdk',1,3);结果：sds
select substr('sdsfndjskdk',1,3) as test;结果：sds
select substr('sdsfndjskdk',7) as test;结果：jskdk
select substr('sdsfndjskdk',4,3) as test;结果：fnd

混合使用
模糊授权，类似 grant all on firmus.cs_* 
案例： 
select concat("grant all on world.cs_",substr(table_name,4)," to root@'10.0.0.%'") from information_schema.tables where table_schema='world' and table_name like 'cs_%';

instr

作用：返回子集首次次出现的索引。
select INSTR('ABCDBCA','B') AS test;2
案例：
select INSTR('杨不悔爱上了殷六侠','殷六侠') AS test;7
实际需求：判断某个字符串是否在表中某行出现过。
select id, instr(name,'qingdao') as a from world.city where countrycode='CHN' having a>0 ;
select sum(instr(name,'qingdao')) as a from world.city where countrycode='CHN';

trim

作用： 掐头去尾。
案例：
select TRIM(' 张翠山 ') AS test;张翠山
select TRIM('a' from 'aaaaa张翠山aaaa') AS test;张翠山

lpad

作用: 左填充
案例：
select LPAD('张柏芝',10,'*') AS test;*******张柏芝

rpad

作用：右填充
案例：
select RPAD('张柏芝',10,'*') AS test;张柏芝*******

replace

作用：替换
# 案例：
select REPLACE('谢霆锋***张柏芝','张柏芝','王菲') as test;谢霆锋***王菲
#例子： 替换-为空
select replace(uuid(),'-','') as uuid;生成不带-分隔符的UUID

——1.6.2 数学函数

round

作用： 四舍五入
案例：
select ROUND(3.1415);3
select ROUND(3.1415,3);3.142

ceil

作用：向上取 >= 最小整
案例：
select CEIL(3.14);4
select CEIL(3.00);3
select CEIL(-3.14);-3

floor

作用:向下<= 最大取整
案例：
select floor(9.99);9
select FLOOR(9.00);9

truncate

作用： 小数点保留截断
案例：
select TRUNCATE(-3.15,1);-3.1

mod

作用： 取模
算法:
方法一：
mod(a,b) ---> a-a/b*b
方法二：
被除数为正为正，被除数为负为负
案例：
select MOD(10,3);1
select MOD(10,-3);1
select MOD(-10,3);-1
select MOD(-10,-3);-1

rand

select floor(rand()*10)+1;
作用： 生成某个范围内的随机整数
select 10+floor(90*rand());10~99之间的随机数
# 需求从以下生成的字符串中，随机截取连续6个字符。
select replace(uuid(),'-','') as uuid;
select substr(replace(uuid(),'-','') ,1+floor(rand()*28),10) as ps;

# 生成一个第一字母为大写，复杂度为（大写字母、小写字母、数字组合）的12位密码
select concat(substr('ABCDEFGHIJKLMNOPQRSTUVWXYZ',1+floor(rand()*26),1),substr(replace(
uuid(),"-",""),1+floor(rand()*21),11));

综合案例：

# 生成随机时间字符串
11:34:15
00-23
00-59
00-59
# 1. 生成1-23随机整数
select rand(); -- 生成0-1之间任意小数
select rand()*23;-- 生成0-23之间任意小数
select floor(rand()*23) --生成0-22之间的随机整数
select 1+floor(rand()*23) --生成1-23之间的随机整数
select floor(rand()*24) --生成0-23之间的随机整数
select
# 2. 拼接
select concat(
lpad(floor(rand()*24),2,'0'),":",
lpad(floor(rand()*60),2,'0'),":",
lpad(floor(rand()*60),2,'0')
) as t ;
练习： 生成一个随机的IP地址
SELECT CONCAT(
FLOOR(RAND()*255),".",
FLOOR(RAND()*256),".",
FLOOR(RAND()*256),".",
FLOOR(RAND()*256)
) AS IP ;

进制换算

conv(n,from_base,to_base)
select conv("a",16,2);1010
select ascii('a'); 97
bin(n)
oct(n)
hex(n)
char(n,...)
返回由参数n,...对应的ascii代码字符组成的一个字串(参数是n,...是数字序列,null值被跳过)
mysql> select char(77,121,83,81,'76');
'mysql'
mysql> select char(77,77.3,'77.3');
'mmm'

——1.6.3 日期函数

当前时间、日期函数

select NOW();2023-11-16 18:44:42
select CURDATE();2023-11-16
select CURTIME();18:44:25
select current_timestamp();2023-11-16 18:45:08

截取时间

select MONTH(NOW());
select MONTHNAME(NOW());
select DAY(NOW());
select HOUR(NOW());
select MINUTE(NOW());
select SECOND(NOW());
案例：
select (year(NOW())-year('1992-02-14 06:01:30'));31
select FROM_UNIXTIME(12344234)2029-08-25 17:23:54
select UNIX_TIMESTAMP('2020-05-23');1590163200
SELECT DATE(FROM_UNIXTIME(UNIX_TIMESTAMP('1970-01-01') + FLOOR(RAND() * (UNIX_TIMESTAMP('2020-05-23') - UNIX_TIMESTAMP('1970-01-01') + 1)))) AS date;生产一个随机日期

以指定格式识别日期

select STR_TO_DATE()
例子：
select STR_TO_DATE('5-3 2020','%m-%d %Y') as test;

以指定字符串格式输出日期

select DATE_FORMAT()
select DATE_FORMAT(NOW(),'%Y-%m-%d');

——1.6.4 其他函数

version()
database()
user()
uuid()
BENCHMARK(count,expr)

——1.6.5 流程控制函数

if函数 ==> if else

select if(2>1,'yes','no')
案例：
select user,if(user='root',"管理员","普通用户") from mysql.user;

case函数

用法1: 等值判断
case 表达式
when 等值判断 then 值1
...
else 值N。
end
举例：
select
case num
when 110 then CONCAT(num,':抓小偷')
when 119 then CONCAT(num,':救火')
else CONCAT(num,':救人')
end
as test
from tt;
用法2：范围判断
case
when 条件1 then 结果或语句
when 条件2 then 结果或语句
else
end
# 案例：
统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表(case)

select course.cname,
group_concat(case when ifnull(sc.score,0)>=85 then student.sname end) as '优秀',
group_concat(case when ifnull(sc.score,0) between 70 and 85 then student.sname
end ) as '良好',
group_concat(case when ifnull(sc.score,0) between 60 and 70 then student.sname
end) as '一般',
group_concat(case when ifnull(sc.score,0)< 60 then student.sname end) as '不及格'
from student
join sc
on sc.sno=student.sno
join course
on course.cno=sc.cno
group by course.cno;

+--------+-------------------+-------------------+------------+-----------+
| cname  | 优秀              | 良好              | 一般       | 不及格    |
+--------+-------------------+-------------------+------------+-----------+
| linux  | li4,zhao4         | wang5,oldboy      | ma6,oldboy | NULL      |
| python | zhang4            | NULL              | wang5      | NULL      |
| mysql  | zhang4,wang5,oldp | zhao4,ma6,oldgirl | NULL       | li4,zh4   |
+--------+-------------------+-------------------+------------+-----------+

2 系统变量

—2.1 全局变量

全局生效，只影响新生成的会话。
show global variables like '%read_only%';
show global variables like '';
set global read_only=1;
select @@global.read_only;

—2.2 会话变量

只影响当前会话。

mysql> show session variables like '%read_only%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_read_only      | OFF   |
| read_only             | OFF   |限制普通用户
| super_read_only       | OFF   |限制管理员
| transaction_read_only | OFF   |
+-----------------------+-------+

—2.3 持久化更改变量

set persist  read_only=1;
8.0特性，即使重启也不会像global一样失效，因为会在系统层面生成一个mysql-auto.cnf

3 自定义变量

—3.1 用户变量

——3.1.1 作用域

针对会话有效，会话任意位置使用。单独设置或者在存储过程函数都可。

——3.1.2 声明并初始化

set @var=值;
set @var1:=值;
select @var2:=值;

——3.1.3 赋值（更新）

方式1：
set @var=值;
set @var1:=值;
select @var2:=值;

方式2：
select count(*) into @count
from world.city

注意：只能单一赋值

——3.1.4 使用

select @count;
混合案例：
select replace(uuid(),'-','') into @str;
select substring(@str,floor(rand()*21+1),11) into @str1;
select
concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',floor(rand()*26+1),1),@str1);

—3.2 局部变量

——3.2.1 作用域

必须在存储过程内部使用，即：begin ....end中。

——3.2.2 声明

DECLARE 变量名 类型;
DECLARE 变量名 类型 DEFAULT 值;

——3.2.3 赋值

方式1：
set var=值;
set var1:=值;
select @var2:=值;

方式2：
select count(*) into count
from world.city
select id ,name ,age from t1 into v_id,v_name,v_age

——3.2.4 使用

select count;

4 存储过程基础应用

—4.1 语法

DELIMITER $$
CREATE
    PROCEDURE `world`.`test`(参数列表)
    BEGIN
        过程体（1组SQL语句）
    END$$
DELIMITER ;

—4.2 语法说明

DELIMITER $$ :
说明：
语句结束标记定义。
过程体中，每条SQL都应该使用“;”结束。并且所有语句作为一个过程体运行。
如果过程体只有一条SQL，可省略begin end。
参数列表：
参数模式 参数名 参数类型
参数模式 ：
IN ： 输入参数，单独传参。
OUT ： 输出参数，作为返回值的参数。
INOUT： 既输入有输出，可做输入也可做输出。

—4.3 调用

CALL 存储过程名(实参列表);

—4.4 应用实例

——4.4.1 空参数列表应用

DELIMITER $$
USE `world`$$
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p1`()
    COMMENT 'p1'
BEGIN
    select *
    from world.city
    where countrycode='CHN';
END$$
DELIMITER ;

call p1();

——4.4.2 IN 参数列表应用

DELIMITER $$
USE `world`$$
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p2`(in cd varchar(20))
    COMMENT 'p2'
BEGIN
    select *
    from world.city as wc
    where wc.countrycode='cd';
END$$
DELIMITER ;

call p2('USA');

DELIMITER $$
USE `world`$$
CREATE PROCEDURE `p3` (in cd varchar(20) ,in pop int)
BEGIN
    select * from city as a
    where a.countrycode=cd
    and a.population < pop;
END$$
DELIMITER ;

call p3('USA',90000);

——4.4.3 OUT 参数列表应用

DELIMITER $$
USE `world`$$
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p4`(out cc varchar(20),out count int)
BEGIN
    select c.countrycode,count(*) into cc,count
    from city as c
    where c.countrycode='USA';
    select cc,count;
END$$
DELIMITER ;

call p4(@aa,@bb);

USE `world`;
DROP procedure IF EXISTS `p5`;
DELIMITER $$
USE `world`$$
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p5`(in bb varchar(20) ,out cc varchar(20),out count int)
BEGIN
    select c.countrycode,count(*) into cc,count
    from city as c
    where c.countrycode=bb;
    select cc,count;
END$$
DELIMITER ;

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p6`(in c int ,out aname varchar(20),out bname varchar(20))
BEGIN
    select a.name,b.name into aname,bname
    from city as a join country as b
    on a.countrycode=b.code
    where a.population < c ;
    select aname,bname;
END

——4.4.4 INOUT 参数列表应用

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p7`(inout a int, inout b int ,out c int)
BEGIN
select a*2 into a ;
select b*2 into b ;
select a*b into c;
select a,b,c ;
END
set @a=10;
set @b=20;
call p7(@a,@b,@c)

——4.4.5 变量在过程中应用

-- 变量使用练习1：
/*
需求： 存储过程中实现，往指定表中插入1行随机值
uname:6字符随机长度。
pass：12位随机密码，第一位是大写，剩下的是随机数字字母组合。
*/

-- 创建表t1
use test;
create table t1(
id int not null primary key auto_increment,
uname varchar(64) not null ,
pass varchar(20) not null
)engine=innodb charset=utf8mb4;

select substr('abcdefghijklmnopqrstuvwxyz',1+floor(rand()*20),6);

USE `test`;
DROP procedure IF EXISTS `test`.`p_var1`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_var1`()
BEGIN
declare v_u varchar(64) ;
declare v_p varchar(20);
declare str,str_11 varchar(64);
declare str_1 varchar(64) default 'abcdefghijklmnopqrstuvwxyz';
select substr(str_1,1+floor(rand()*20),6) into v_u;
select replace(uuid(),'-','') into str;
select substring(str,floor(rand()*21+1),11) into str_11;

select concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',floor(rand()*26+1),1),str_11) into v_p;

insert into t1 (uname,pass) values(v_u,v_p);
END$$
DELIMITER ;

-- 调用方法：
call p_var1();
select * from t1;

-- 变量使用练习2（学员）：
/*
需求： 存储过程中实现，往指定表中插入1行随机值
uname:6字符随机长度。
pass：12位随机密码，第一位是大写，剩下的是随机数字字母组合。
u_time: 随机出生日期(1980-2020)，例如：1996-01-02
u_age ：根据出生日期算出来。
u_tel ：随机手机号
*/

-- 提示： 随机日期
SELECT DATE(FROM_UNIXTIME(UNIX_TIMESTAMP('1970-01-01') +
FLOOR(RAND() * (UNIX_TIMESTAMP('2020-05-23') - UNIX_TIMESTAMP('1970-01-01') +
1))))
AS date;

/*
1 38 00001111
1 30-99 00000000-99999999
*/
-- 生成随机手机号:
select concat('1',30+floor(rand()*70),lpad(floor(rand()*100000000),8,'0'));

-- 存储过程体：
USE `test`;
DROP procedure IF EXISTS `test`.`p_var2`;

DELIMITER $$
USE `test`$$
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_var2`()
BEGIN

-- 定义变量:
declare v_u,v_p,v_t,v_tel varchar(64);
declare v_a int;
declare str,str_11 varchar(64);
declare str_1 varchar(64) default 'abcdefghijklmnopqrstuvwxyz';
-- 给变量赋值：
select substr(str_1,1+floor(rand()*20),6) into v_u;
select replace(uuid(),'-','') into str;
select substring(str,floor(rand()*21+1),11) into str_11;
select concat(substring('ABCDEFGHIJKLMNOPQRSTUVWXYZ',floor(rand()*26+1),1),str_11) into v_p;

SELECT DATE(FROM_UNIXTIME(UNIX_TIMESTAMP('1980-01-01') +
FLOOR(RAND() * (UNIX_TIMESTAMP('2020-01-01') - UNIX_TIMESTAMP('1980-01-01') +
1)))) into v_t;
select year(now())-year(v_t) into v_a;
select concat('1',30+floor(rand()*70),lpad(floor(rand()*100000000),8,'0')) into
v_tel;
-- 调用变量：
insert into t2(uname,pass,u_time,u_age,u_tel)
values(v_u,v_p,v_t,v_a,v_tel);
END$$
DELIMITER ;

call p_var2()
select * from t2;

5 存储过程高级应用-流程控制结构

—5.1 顺序结构

从上至下一次执行

—5.2 分支结构

——5.2.1 介绍

多条路径中选择其中一条

——5.2.2 case

方法1：
case 变量|表达式|字段
when 判断的值 then 结果或语句
when 判断的值 then 结果或语句
...
else 结果或语句
end case;

方法2：
case
when 条件1 then 语句;
when 条件2 then 语句;
else 语句
end case;

——5.2.3 if

if 条件1 then 语句1;
elseif 条件2 then 语句2;
...;
else 语句n;
end if;

——5.2.4 分支判断语句应用

案例：判断胖瘦
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_if`(in tz int )
BEGIN
declare result varchar(10);
if tz <100 then
set result='靓';
elseif tz between 100 and 130 then
set result='壮';
else
set result='胖';
end if;
select result;
END

案例：判断年龄范围
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_case1`(age int )
BEGIN
declare result varchar(10) ;
case
when age between 0 and 10
then
set result='黄口小儿';
when age between 11 and 20
then
set result='青少年';
when age between 21 and 30
then
set result='青年';
when age between 31 and 50
then
set result='中年';
else
set result='退休了';
end case;
select result;
END

案例：判断输入的用户密码
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_login`(in u varchar(20),in p
varchar(20))
BEGIN
declare result varchar(20);
declare count int default 0;
select count(*) into count
from t2 where t2.username=u and t2.pass=p;
case
when count>0
then set result='success!';
else set result='error!';
end case;
select result;
END

—5.3 循环结构

——5.3.1 介绍

满足条件，重复执行一段代码

——5.3.2 类型

while
loop
repeat

——5.3.3 循环控制

iterate ----> continue
leave -----> break

——5.3.4 while语法

[标签：] while 条件 do
循环体；
end while [标签]；

——5.3.5 loop 语法（死循环）

[标签：] loop
循环体；
end loop [标签]；

——5.3.6 repeat

[标签：] repeat
循环体；
until 条件
end repeat [标签]；

——5.3.7 循环语句基础应用

/*
案例: t4表
id name age gender
1 asdfss 23 M
2 erfghj 32 F
... xsrhnc 22 M
10000 ertyup 18 F
需求： 随机往t4表插入1w行数据
id: 1-10000
name: 随机6个字符
age :18-35
gender : M/F
*/

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p1_kz`(in num int)
BEGIN
declare str1 varchar(64) default 'abcdefghijklmnpqrstuvwxyz';
declare str2 varchar(10) default 'MF';
declare v_name varchar(64);
declare v_age int;
declare v_gender varchar(10);
declare i int default 0;
while i<=num
do
    select substr(str1,1+floor(rand()*20),6) into v_name;
    select substr(str2,1+floor(rand()*2),1) into v_gender;
    set v_age=18+floor(rand()*12);
    insert into tt values(i,v_name,v_age,v_gender);
    set i=i+1;
end while ;
END

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `pt_repeat`(in num int)
BEGIN
declare str1 varchar(64) default 'abcdefghijklmnpqrstuvwxyz';
declare str2 varchar(10) default 'MF';
declare v_name varchar(64);
declare v_age int;
declare v_gender varchar(10);
declare i int default 1;
repeat
    select substr(str1,1+floor(rand()*20),6) into v_name;
    select substr(str2,1+floor(rand()*2),1) into v_gender;
    set v_age=18+floor(rand()*12);
    insert into tt values(i,v_name,v_age,v_gender);
    set i=i+1;
    until i>num
end repeat;
END

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_loop`(in num int)
BEGIN
declare str1 varchar(64) default 'abcdefghijklmnpqrstuvwxyz';
declare str2 varchar(10) default 'MF';
declare v_name varchar(64);
declare v_age int;
declare v_gender varchar(10);
declare i int default 1;
lab1:loop
select substr(str1,1+floor(rand()*20),6) into v_name;
    select substr(str2,1+floor(rand()*2),1) into v_gender;
    set v_age=18+floor(rand()*12);
    if i>num
    then leave lab1;
    else
    insert into tt values(i,v_name,v_age,v_gender);
    set i=i+1;
    end if;
end loop lab1;
END

——5.3.8 循环控制应用

案例:
id name age gender
1 asdfss 23 M
3 erfghj 32 F
5 xsrhnc 22 M
999 ertyup 18 F

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_iterate`(in num int)
BEGIN
declare str1 varchar(64) default 'abcdefghijklmnpqrstuvwxyz';
declare str2 varchar(10) default 'MF';
declare v_name varchar(64);
declare v_age int;
declare v_gender varchar(10);
declare i int default 0;
a:while i<num
do
    select substr(str1,1+floor(rand()*20),6) into v_name;
    select substr(str2,1+floor(rand()*2),1) into v_gender;
    set v_age=18+floor(rand()*12);
    set i=i+1;
    if mod(i,2)=0 then iterate a;
    else
    insert into tt values(i,v_name,v_age,v_gender);
    end if;
end while a;
END

-- 思考：如果以上例子使用 leave ，结果是怎样？

—5.4 游标应用

# 什么是游标？
保存select语句的数据集，主要用于对数据集逐行进行处理。

# 使用方法
需要的操作：
定义游标
    DECLARE cur_1 CURSOR FOR SELECT id,name FROM city;
定义游标异常处理：
    declare done int default 1;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;
打开游标：
    open cur_1;
提取数据:
    fetch cur_1 into c_id,c_name;
关闭游标:
    close cur_1;

# 案例：
CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_cur`()
BEGIN
declare c_id int ;
declare c_name varchar(64);
declare done int default 1;
DECLARE cur_1 CURSOR FOR SELECT id,name FROM tt;
open cur_1;
fetch cur_1 into c_id,c_name;
select c_id,c_name;
fetch cur_1 into c_id,c_name;
fetch cur_1 into c_id,c_name;
select c_id,c_name;
fetch cur_1 into c_id,c_name;
fetch cur_1 into c_id,c_name;
select c_id,c_name;
fetch cur_1 into c_id,c_name;
fetch cur_1 into c_id,c_name;
select c_id,c_name;
fetch cur_1 into c_id,c_name;
fetch cur_1 into c_id,c_name;
select c_id,c_name;
fetch cur_1 into c_id,c_name;
fetch cur_1 into c_id,c_name;
select c_id,c_name;
fetch cur_1 into c_id,c_name;
fetch cur_1 into c_id,c_name;
select c_id,c_name;
fetch cur_1 into c_id,c_name;
close cur_1;
END

# 异常处理
ERROR 1329 (02000): No data - zero rows fetched, selected, or processed
++++++
declare done int default 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;
或者：
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 0;
++++++

CREATE DEFINER=`root`@`10.0.0.%` PROCEDURE `p_cur1`()
BEGIN
declare c_id int ;
declare c_name varchar(64);
declare done int default 1;
DECLARE cur_1 CURSOR FOR SELECT id,name FROM tt;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 0;
open cur_1;
fetch cur_1 into c_id,c_name;

while done=1 do
select c_id,c_name;
fetch cur_1 into c_id,c_name;
end while ;
END

—5.5 组合应用

—5.6 存储过程查询及删除

select * from information_schema.ROUTINES\G
drop procedure p_iterate;

6 自定义函数应用

—6.1 应用

CREATE FUNCTION `f1`(参数)
RETURNS int(11)
DETERMINISTIC
BEGIN
    函数体SQL；
RETURN 变量;
END

—6.2 调用方法

select f1();

—6.3 删除及查看

select * from information_schema.ROUTINES\G
drop FUNCTION f1;

7 触发器应用

—7.1 介绍

触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
触发器的特性：
1、有begin end体，begin end;之间的语句可以写的简单或者复杂
2、什么条件会触发：I、D、U
3、什么时候触发：在增删改前或者后(before/after)
4、触发频率：针对每一行执行
5、触发器定义在表上，附着在表上。
也就是由事件来触发某个操作，事件包括INSERT语句，UPDATE语句和DELETE语句；可以协助应用在数据库端确保数据的完整性。

—7.2 语法

CREATE TRIGGER trigger_name
trigger_time: { BEFORE | AFTER }
ON tbl_name FOR EACH ROW
trigger_body
trigger_event: { INSERT | UPDATE | DELETE }

—7.3 触发器应用

#日志表应用
create table t4_log(
id int not null primary key auto_increment,
act_user varchar(64),
act_type varchar(50) ,
act_time varchar(50),
act_id varchar(20),
act_comment varchar(100));

# 插入触发器
DELIMITER $$
CREATE trigger tr_insert_t4
after insert on t4 for each row
BEGIN
insert into
t4_log(act_user,act_type,act_time,act_id,act_comment)
values(user(),'insert',now(),new.id,
concat('insert into t4
values(',new.id,',',new.name,',',new.age,',',new.gender,');'));
END$$
DELIMITER;

# 删除类触发器
DELIMITER $$
CREATE trigger tr_del_t4
before delete on t4 for each row
BEGIN
insert into
t4_log(act_user,act_type,act_time,act_id,act_comment)
values(user(),'del',now(),old.id,
concat("delete from t4 where id=",old.id,";"));
END$$
DELIMITER;

## 商品库存自动更新
# 商品信息
create table goods(
    id int primary key auto_increment,
    name varchar(20) not null,
    price decimal(10,2) default 1,
    inv int comment '库存数量'
) ;

insert into goods
values
(null,'华为',11999,1000),
(null,'苹果',15999,50),
(null,'惠普',5999,2000),
(null,'小米',10999,2500),
(null,'戴尔',6999,3000);

# 订单
create table orders(
    id int primary key auto_increment,
    o_id int not null comment '商品id',
    o_number int comment '商品数量'
) ;

create trigger after_order
after insert on orders
for each row
begin
    update goods set inv = inv - new.o_number where id = new.id;
end
insert into orders(o_id,o_number) values(1,1,3);

8 事件管理器

—8.1 介绍

类似于Linux中的计划任务。

—8.2 开启时间调度器

开启事件调度器
#通过命令行
SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;
#通过配置文件my.cnf
event_scheduler = 1 #或者ON

—8.3 时间调度器应用

# 案例1（立即启动事件）
create table ev1
(
ev_name varchar(20) not null,
ev_started timestamp not null);
create event event_now
on schedule
at now()
do insert into ev1 values('ev_test', now());

# 案例2（每分钟启动事件）
create event ev2
on schedule
every 1 minute
do insert into ev1 values('ev_test1', now());

# 案例3（每秒钟启动事件）
CREATE event ev3
ON SCHEDULE
EVERY 1 SECOND
DO INSERT INTO ev3 VALUES(1);

# 案例4（每秒钟调用存储过程）
CREATE DEFINER=`root`@`localhost` EVENT `eventUpdateStatus`
ON SCHEDULE EVERY 1 SECOND
STARTS '2017-11-21 00:12:44'
ON COMPLETION PRESERVE
ENABLE
DO call updateStatus()

# 过程式创建events
DELIMITER $$
//事件的名称
CREATE EVENT `test`
//60秒循环一次
ON SCHEDULE EVERY 60 MINUTE_SECOND
// 开始时间,结束时间
STARTS '2017-11-01 00:00:00.000000' ENDS '2017-11-30 00:00:00.000000'
//过期后禁用事件而不删除
ON COMPLETION PRESERVE ENABLE
DO
BEGIN
//执行的内容
insert into ev1 values('event_now', now());
insert into ev1 values('event_now1', now());
ENDR $$
DELIMITE ;

9 视图应用

—9.1 介绍

自定义视图
系统视图

—9.2 作用

保存Select 语句执行方法。不保存数据。

—9.3 自定义应用

create view v_city
as
select a.name as aname ,b.name as bname ,a.population ,b.surfacearea
from city as a
join country as b where a.population<100;
select * from v_city;

—9.4 系统自带视图

查询系统中所有视图对象。
mysql> select table_schema,table_name,table_type from information_schema.tables
where table_type like '%VIEW%';
系统视图
information_schema(I_S),sys
用户视图

—9.5  I_S基本应用

——9.5.1 作用

提供了用来查询系统“元数据”的视图。封装了元数据查询的方法。
我们可以通过I_S和show更加方便的查询元数据信息。

——9.5.2 常用视图应用

tables : 提供数据库中所有表相关元数据
TRIGGERS ：提供数据库中所有触发器相关元数据
views ：提供数据库中所有视图相关元数据
ROUTINES ：提供数据库中所有存储过程相关元数据
COLUMNS ：提供数据库中所有表中列相关元数据
processlist ：提供数据库连接方面的系统状态。

——9.5.3 tables应用

结构介绍

desc information_schema.tables;
TABLE_SCHEMA -- 表所在的库。
TABLE_NAME -- 表名
TABLE_TYPE -- 表类型
ENGINE -- 存储引擎类型
TABLE_ROWS -- 数据行（粗略的）
AVG_ROW_LENGTH -- 平均行长度（字节）
DATA_LENGTH -- 数据长度
INDEX_LENGTH -- 索引长度
DATA_FREE -- 碎片量
CREATE_TIME -- 创建时间
UPDATE_TIME -- 更新时间
TABLE_COMMENT -- 注释

应用实例

1. 统计当前实例中业务相关的库和表的信息（排除掉mysql sys information_schema performance_schema）
库名 表个数 表名列表
mysql> select table_schema,group_concat(table_name),count(*) from
information_schema.tables where table_schema not in
('sys','mysql','information_schema','performance_schema') group by table_schema;

2. 统计当前实例每个数据库的数据总量（排除掉mysql sys information_schema performance_schema）
select table_schema,sum(table_rows * avg_row_length + index_length)/1024/1024 as
total_mb
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema')
group by table_schema;

3. 统计当前实例非innodb的表（排除掉mysql sys information_schema performance_schema）
select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB';

alter table world.aaaaa engine=innodb;

4. 查询有碎片的表信息
select table_schema,table_name ,data_free
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema')
and data_free >0;

5. 拼接SQL
a. 查询当前系统中所有非INNODB的表。
select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB';
b. 将这些非INNODB的表替换为INNODB
mysql> select concat("alter table ",table_schema,".",table_name,"
engine=innodb;") from information_schema.tables where table_schema not
in ('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB' into outfile '/tmp/alter.sql';
source /tmp/alter.sql

1. 碎片化怎么产生的，对查询或更新有什么影响吗？
对于 delete update 操作，会产生锁片。 HWM
大范围查询表数据。会额外的代价。
2. 为啥alter table table_name engine=innodb; 第一次有用，之后就没用了？
第一次整理时候效果明显。效果没有第一次的明显。

——9.5.4 TRIGGERS、views、ROUTINES、EVENTS应用

需求： 迁移备份前需要确认是否有特殊对象
mysql> select TRIGGER_SCHEMA,EVENT_OBJECT_SCHEMA,TRIGGER_NAME ,ACTION_STATEMENT
,DEFINER from information_schema.triggers where TRIGGER_SCHHEMA not in
('sys','mysql','information_schema','performance_schema');
mysql> select ROUTINE_SCHEMA , ROUTINE_NAME,ROUTINE_DEFINITION ,DEFINER from
information_schema.ROUTINES where ROUTINE_SCHEMA not in
('sys','mysql','information_schema','performance_schema')\G

——9.5.5 columns

保存了表的数据字典信息。
select
TABLE_SCHEMA,
TABLE_NAME ,
COLUMN_NAME ,
IS_NULLABLE,
DATA_TYPE ,
COLUMN_KEY ,
COLUMN_COMMENT
from information_schema.columns where table_schema not
in ('sys','mysql','information_schema','performance_schema') ;

——9.5.6 processlist应用

需求： 维护性操作需要停业务。需要将所有外部连接进行释放。(或者使用pt-kill)
select concat("kill ",id,";") from PROCESSLIST where host not in ('localhost','127.0.0.1','db01')