网站开发所要达到的目标,网站专题页优化,如何用易语言做网站,线上销售培训班课程1. 概念 MySQL中的存储过程指的是存储在数据库中的SQL语句集合。当创建好存储过程后#xff0c;在运行时提供所需参数#xff0c;存储过程就可以以代码指定的方式使用参数执行并返回值。 存储过程的特点包括#xff1a; 封装与复用#xff1a;可以把某一业务SQL封装在存储过…1. 概念 MySQL中的存储过程指的是存储在数据库中的SQL语句集合。当创建好存储过程后在运行时提供所需参数存储过程就可以以代码指定的方式使用参数执行并返回值。 存储过程的特点包括 封装与复用可以把某一业务SQL封装在存储过程中需要用到的时候直接调用即可减少了网络交互提升了通信速率。接收参数与返回数据在存储过程中可以传递参数也可以接收返回值。编译与执行效率存储过程只在创建时进行编译以后每次执行存储过程都不需要在重新编译而一般SQL语句每执行一次就编译一次所以使用存储过程可提高数据库执行速度。 2. 基本语法
2.1 创建 -- 创建存储过程
create procedure 存储过程名 (参数没有就不传)
beginSQL语句
end;-- 调用存储过程
call 存储过程名传的参数没有就不传;栗子
-- 创建存储过程
create procedure p1 ()
beginselect * from emp;select count(*) from emp;
end;-- 调用存储过程
call p1();2.2 查看 -- 查看存储过程
-- select 字段名 from information_schema.ROUTINES where ROUTINE_SCHENA 存储过程名-- 查询指定数据库存的储过程及状态信息
-- show create procedure 存储过程名-- 查询存储过程的定义栗子
select * from information_schema.ROUTINES where ROUTINE_SCHEMA study;
show create procedure p1; 2.3 删除 -- 删除存储过程
-- drop procedure (if exists) 存储过程名; 括号内容可省略drop procedure if exists p1;2.4 命令行注意事项 -- 注意在命令行中执行存储过程需修改SQL语句结束符号修改结束符用关键字delimiter
-- delimiter 符号 delimiter $$3. 变量 概念 系统变量系统变量是MySQL服务器定义的用户只能修改其值不能创建新的系统变量。系统变量分为全局变量和会话变量。全局变量对所有会话都有效修改全局变量会影响所有新会话但已存在的会话不受影响直到MySQL服务器重启。会话变量则是在当前会话级别有效其变更只会影响到当前会话。需要注意的是对系统变量的修改在MySQL服务器重启后会恢复为默认值要想永久修改需要更改配置文件。用户变量用户变量是用户根据需要自己定义的变量用户变量不用提前声明在使用的时候直接用“变量名”就可以。其作用域为当前连接。局部变量局部变量是根据需要在局部生效的变量访问之前需要DECLARE声明。在存储过程和函数中都可以定义和使用局部变量。 3.1 系统变量 -- 系统变量
global全局系统变量 所有会话生效
session当前会话变量当前会话生效-- 查看系统变量括号二选一
-- show (global/session) variables; 查看所有的系统变量 show session variables;
show global variables; -- show (global/session) variables like ; 模糊匹配系统变量show global variables like auto%;-- select 系统变量名 查看指定系统变量select autocommit;-- 设置系统变量不加global或者session会默认修改当前会话的变量-- 当服务器重启会自动回复到初始值若要永久修改可在配置文件 /etc/my.cnf 中修改
-- set (session/global) 系统变量名 值; 设置指定系统变量的值 set autocommit 1;3.2 用户变量 -- 用户变量-- 用户变量不需要提取声明或者初始化未初始化的用户变量默认赋值null
-- 设置用户变量
-- set 用户变量名1 值 用户变量名2 值 ...;
-- set 用户变量名1 : 值, 用户变量名2 : 值, ...;-- SQL中 和 : 都能赋值select niubi;
set niubi : 666;-- select 用户变量名1 : 值, 用户变量名2 : 值, ...;
select niuhai : 999;
select niuhai;-- select count(*) into 用户变量名 from 表名;-- 将查询表统计返回的结果赋值给用户变量
select count(*) into 赋值结果 from emp;
select 赋值结果; 3.3 局部变量 -- 作用域只要begin和end之间生效需要declare提前声明-- declare 变量名 变量类型 default 默认值-- 赋值 变量名 值create procedure p1()
begindeclare emp_count int default 0;select count(*) into emp_count from emp;select emp_count;
end;call p1; 4. 流程控制语句 两类语句 条件控制语句if、case语句 循环控制语句 4.1 if -- if判断条件的使用-- if 条件1 then-- 满足条件1执行的语句-- elseif 条件2 then-- 满足条件2执行的语句-- else-- 不满足以上所有条件执行的语句-- end if;参数 -- 创建过程
-- 传递变量进行判断
create procedure p3(in source int)
begin-- 声明局部变量declare result varchar(10);if source 80 thenset result 优秀;elseif source 60 thenset result 及格;elseset result 不及格;end if;-- 查询结果select result;
end;-- 调用过程
call p3(99); 练习
-- 练习2将传入的200分制的分数换算成100分制并返回
create procedure p5(inout score double)-- inout修饰的变量可以输入和输出参数
beginset score : score * 0.5;
end;-- 调用
set score 91;
call p5(score);
select score; 4.2 case - 使用规则
-- case开始casewhen 条件1 then 满足条件1执行的语句when 条件2 then 满足条件2执行的语句when 条件3 then 满足条件3执行的语句else 不满足以上条件执行的语句
-- case结束
end case;-- 输入季度并进行判断
create procedure p6(in month int)
begindeclare result varchar(10);casewhen 1 month and month 3 then set result 第一季度;when 4 month and month 6 then set result 第二季度;when 7 month and month 9 then set result 第三季度;when 10 month and month 12 then set result 第四季度;else set result 非法参数;end case;select concat(您输入的月份为, month, 属于, result);
end;call p6(19); 4.3 while -- 使用规则
while 条件 do满足条件执行的SQL语句不满足条件退出循环
end while-- 练习累加n的值
create procedure p7(in n int)
begindeclare sum int default 0;while n 0doset sum sum n;set n n - 1;end while;select sum;
end;-- 调用
call p7(10); 4.4 repeat -- 使用规则
repeat要执行的sql语句until 满足条件就退出循环end repeat;
end repeat;-- 练习计算前n个数的值
create procedure p8(in n int)
begindeclare sum int default 0;repeatset sum sum n;set n n - 1;until n 0end repeat;select sum;
end;-- 调用
call p8(10); 4.5 loop -- 使用规则
-- leaver 跳出循环
-- iterate 中断本次循环进行下一次循环create procedure p10(in n int)
begin-- 定义变量统计总和declare sum int default 0;-- 循环fun: loop-- 满足以下条件跳出循环if n 0 thenleave fun;end if;-- 满足以下条件不进行累加if n % 2 0 thenset n n - 1;iterate fun;end if;-- 赋值set sum sum n;set n n - 1;end loop fun;-- 查看最终结果select sum;
end;call p10(5); 5. 游标cursor -- 用来存储查询结果集的数据类型在存储过程和函数中可以用游标对结果集进行数据处理
-- 使用规则声明open、fetch、close
-- 声明游标 declare 游标名 cursor for 查询语句查询到的内容将会存储到游标内;
-- open 游标名;
-- fetch 游标名 into 变量1变量2...;游标内的每一条记录都会存储到对应的变量变量接收的类型要和游标记录的数据类型保持一致
-- close 游标名; -- 条件处理程序handler处理程序出现问题时该如何执行如当游标内的记录为空时如何退出循环
-- 使用规则declare handler_action handler for condition_value, condition_value,... statement;
-- 声明一个处理异常程序的语句即遇到上面的状态码如02000时进行什么程序操作如exit中止程序后该游标什么状态如关闭游标-- handler_action
-- continue继续执行当前程序;
-- exit退出程序;-- condition_value
-- sqlstate sqlstate_value状态码如02000
-- sqlwarning所有以01开头的状态码的简写
-- not found所有以02开头的状态码的简写
-- sqlexception所有没有被sqlwarning、not found捕抓到的状态码的简写create procedure p11(in source varchar(10))
begin-- 声明游标内数据的类型declare uname varchar(10) default null;declare uid int;declare uage int;declare udept_id int;declare uaddress varchar(10);-- 声明游标declare emp_cursor cursor for select * from emp where address source;-- 声明条件处理程序当游标满足什么条件自动退出程序当游标记录的数据为空时退出程序并关闭游标declare exit handler for not found close emp_cursor;-- 或者使用状态码退出-- declare exit handler for SQ-- declare exit handler for sqlstate 02000 close emp_cursor;-- 创建新表存储游表的记录drop table if exists tb_emp;create table tb_emp(id int primary key auto_increment,name varchar(10) not null,address varchar(10));-- 打开游标open emp_cursor;-- 循环遍历存储游标内的记录while true do-- 获取游标内的数据存储到定义的临时变量中fetch emp_cursor into uid, uname, uage, udept_id, uaddress;-- 将数据存入新表insert into tb_emp value (null, uname, uaddress);end while;-- 关闭游标close emp_cursor;
end;-- 调用存储过程
call p11(广州); 6. 存储函数 -- 使用规则函数必须要有返回值
create function 函数名称(参数列表)
returns type函数返回值类型 character函数特征
begin函数内部要执行的语句return 返回值
end;-- 使用规则函数必须要有返回值
create function 函数名称(参数列表)
returns type函数返回值类型 character函数特征
begin函数内部要执行的语句return 返回值
end; 7. 触发器 触发器的主要特点和功能包括 自动化业务逻辑和操作触发器能够自动执行存储过程响应特定事件如insert、update和delete语句从而实现约束、默认值或处理业务逻辑的功能。响应特定事件每个触发器都有一个触发事件和响应事件。触发事件通常是数据表上的INSERT、UPDATE或DELETE语句而响应事件则是在触发事件后MySQL服务器执行的操作。处理多种操作触发器可以处理多种操作比如设置默认值、展示错误信息、实现复杂的业务逻辑等。 需要注意的是在MySQL中只有执行INSERT、UPDATE和DELETE操作时才能激活触发器其他SQL语句则不会激活触发器。 通过创建和使用触发器开发人员可以更有效地管理数据库中的数据确保数据的完整性和一致性同时减少手动干预的需要提高应用程序的自动化程度。 创建触发器语法 create trigger 触发器名称 after/before insert on 触发器关联的表名 for each row begin insert into 触发器的记录插入的哪张表的名称 (插入内容); end; -- 创建触发器
-- 插入数据触发器
-- 创建触发器trigger 触发器名字
create trigger tb_emp_insert_trigger-- 触发器在emp表操作之后触发after insert on emp for each row
-- 触发器内部结构
begininsert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values(null, insert, now(), new.id,concat(插入数据内容为id,new.id,name,new.name,age,new.age,address,new.address ));
end; create trigger 触发器名称 after/before update on 触发器关联的表名 for each row begin insert into 触发器的记录插入的哪张表的名称 (插入内容); end; -- 创建修改数据触发器
create trigger tb_emp_update_trigger-- 触发器在emp表操作之后触发after update on emp for each row
-- 触发器内部结构
begininsert into emp_logs (id, operation, operation_time, operation_id, operation_prams) values(null,update,now(),new.id,concat(更新之前数据id, old.id, name, old.name, age, old.age, address, old.address,更新之后数据id, new.id, name, new.name, age, new.age, address, new.address ));
end; 删除同理
