week06 day04 (数据库高级函数 procedure 、sql写函数)

本文详细介绍了SQL中的存储过程和自定义函数的使用方法,包括语法、参数类型、变量声明等内容,并通过多个实例展示了如何创建和调用存储过程及函数。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

一. ER模型

  • 矩形: 代表实体
  • 椭圆:代表实体的属性
  • 菱形:relation 代表实体之间的关系

二. 存储过程(procedure)

1. 语法

语法:
create procedure 存储过程名(参数,…)
begin
	//代码
end//
 注意:

       因为在存储时需要写sql语句,所以sql语句结尾会需要加 ' ; ' , 如果在存储的过程中添加 " ; ", 会导致存储过程结束,导致存储过程中的语法有缺失,所以要解决 "结束符" 的问题, 

     1.1 修改当前窗口中的结束符
delimiter //

2.存储(procedure的使用)

    2.1 简单的sql(只有一条sql语句的)
# create procedure 函数名称(in是写入  参数名称  参数的类型)
begin
create procedure pro1(in params int)
#注意 最终输出的只有一个sql
select * from stuinfo WHERE sid = params;
end //

# 调用函数pro1(填的是参数)
call pro1(32)

#简单的
create procedure pro_1()
select * from stuinfo;
//

#如果存储过程中就一条SQL语句,begin…end两个关键字可以省略。

#调用存储过程
call pro_1()//

    2.2  多条sql

#包涵多条sql语句的
-- in代表输入参数,可以省略
-- return
-- procedure方便大型语句的查询;在创建成功以后,直接进行了语法的检查;
create procedure pro_2(in param int)
begin
	select * from stuinfo where sid=param;
	select * from score where sid=param;
end//

#调用
call pro_2(5)// 

3. 参数的类别

  • 在存储过程中,没有return,如果需要返回值,通过输出参数来实现
  • 在MySQL中,参数分为3类,输入参数(in),输出参数(out),输入输出参数(inout),默认情况下是是输入参数(in)
  • 存储过程中没有return,这里用out (输出参数),输出参数不用传递参数,输出参数要放在参数的最后

4. 局部变量

     declare 是声明局部变量

create procedure pro2(in id int)
begin
# declare 在sql中代表创建局部变量
	declare `name` varchar(10) default "Tom";
	select * from stuinfo where `sid` = `id` or `sname` = `name`;
end 

# 这里defalut name 是 tom, 所以结果一定会有tom 这个数据,如果选中sid也是tom的话,结果就
#只有一条,否则结果是两条

call pro2(31)

5. 全局变量

  • 全局变量前面必须有一个@,全局变量的数据类型取决于变量的值。如果一个全局变量没有赋值,他的数据类型为null,且全局变量只能接受单个值
 into与 全局变量 
create procedure pro3(in limit_skip int,in limit_number int ,out result int)
begin
	# into是相当于赋值,不会打印结果,只是把结果放在变量中国,如果想要打印结果,就select 变量
	
	select sum(sid)+limit_skip+limit_number into result from `stuinfo`;
end

/*首先 先解释(in limit_skip int,in limit_number int ,out result int) 
有两个输入参数 分别是limit_skip和limit_number, 输出参数是result。
其次,sql语句的解释, 首先对sid求和,其结果是122,然后 加上 两个我们传进去的两个参数,
这三个数字的求和 通过 into 传给 输出函数 result
*/

# 设置一个全局变量
# sum(sid)= 122
# set @res=null;

-- 调用pro函数, 2是limit_skip,4是limit_number,@res是传出参数
call pro3(2,4,@res) ;

# 打印 传出参数的结果,因为 sid求和 与 另外两个参数 的值传给了 res,打印res即可
select @res

# 结果是122 + 2+ 4 =128
inout参数与全局变量

        inout: 既可以传入也可以传出


create procedure pro4(inout result int)
begin
 # result 通过 inout参数 既可以传入 又可以传出 
	select sum(sid)+result into result from `stuinfo`;

end

# 将全局参数 res 设置默认值 10
set @res = 10;
# 因为函数pro4 只有一个 inout参数 result,传一个就行
call pro4(@res);
select @res

6.procedure的使用 

     在orderinfo表中,使用二八定律,也就是计算前20%的人群会消费80%的消费总额。

所以你需要计算有效消费(已支付)的。再按照 会员号进行group by,再依据消费金额大小,筛选出前20%的人,并求其消费总额,(金额从大到小,计算20%的人),再用相同方法计算出后80%的消费。

create procedure pro_ac()
begin
   # 定义一个局部表量 用于存储数据
	declare top20 int ;
	declare last80 int;
  # 这个是计算出已支付的 20%的人 与80%的人各有多少,
  #并且通过into传入的参数top20和last80中,不进行输出
	select round(count(DISTINCT uid)*0.2),
					round(count(DISTINCT uid)*0.8)
	into top20,last80
	from orderinfo
	where `ispaid`="已支付";
	
     # 这里是计算 20%人 的消费总和 
	select sum(amt) into top20
	FROM
	(
	select uid,sum(amount) as amt
	from orderinfo
	where `ispaid` = "已支付"
	GROUP BY uid
	order by amt desc
	limit top20
	)as tmp;

      # 计算80%人的消费总和
	select sum(amt)into last80
	FROM
	(
	select uid,sum(amount) as amt
	from orderinfo
	where `ispaid` = "已支付"
	GROUP BY uid
	order by amt asc
	limit last80
	)as tmp1;
	
 # 这里才是输出
	select top20 ,last80 from dual;
END

call pro_ac();

7. 添加索引

索引:是一种记录会消耗存储空间

最左前缀法:建立的索引当中最左边的字段一定要出现在sql语句之中。也就是说(`uid`,`ispaid`,`amount`) 像这样的联合索引当你 只使用 uid时,也可以进行查询。不然

只能`uid`,`ispaid`,`amount`三者同时出现才能进行搜索。

添加索引语法: alter table `表名称` add index `索引名字`(字段) 
alter table orderinfo add index `union1`(`uid`,`ispaid`,`amount`)

alter table orderinfo add index `union2`(`ispaid`,`amount`,`uid`)

alter table orderinfo add index `unioin3`(`amount`,`uid`,`ispaid`)


select `amount` from table where `ispad` = "已支付" order by uid 
查看是否使用索引:explain select
explain select * from orderinfo where ispaid="已支付"

三. sql 编程

1. case-when语句(可以写在sql语句中) (生成透视表)
create procedure pro_9(in num int)
begin
	#需要做判断的变量
     case num
          when 1 then select '杀马特' as '气质';
          when 2 then select '屌丝' as '气质';
          when 3 then select '正常人' as '气质';
          when 4 then select '贵族' as '气质';
          else select '输入不正确' as '气质';
     end case;
end //

call pro_9(2)//

 case when 条件1 then 结果

 case when 条件2 then 结果

select 
		year(Sage),
		case when ssex = "男" then count(*) end as 男,
		case when ssex = "女" then count(*) end as 女
		from student
		group by  ssex,year(Sage);
举例:case when then  和 case xx when then when 区别
case when end, 这种是透视表,会直接添加一列新的列
SELECT
    CASE 
        WHEN age < 18 THEN '未成年'
        WHEN age >= 18 AND age < 60 THEN '成年' 
        ELSE '老年'
    END AS age_group,
    SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
    SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM persons
GROUP BY age_group
ORDER BY age_group;

 

这种是对列中每一行进行操作
SELECT
    CASE gender
        WHEN 'M' THEN 'Male'
        WHEN 'F' THEN 'Female'
        ELSE 'Unknown'
    END AS gender_text,
    COUNT(*) AS count
FROM persons
GROUP BY gender_text
ORDER BY gender_text;

 

2. if-else  只能用在函数中,不可以直接使用在sql语句中
if 条件 then
  //代码1
	
elseif 条件 then
  // 代码2

else
  // 代码3
	
end if;




create procedure pro_8(in grade int)
begin
     if grade=1 then
        select '金牌会员' as '等级';
     elseif grade=2 then
        select '普通会员' as '等级';
     else
         select '游客' as '等级';
     end if;
end 
#调用
call pro_8(3)

3. loop循环

#loop遇到leave退出
create procedure proc(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     sign:loop          #循环标志
         set total=total+i;
         set i=i+1; 
         if i>=num then
            leave sign;# leave=break
         end if;
     end loop;
     select total from dual;
end //

call proc(100)//
#如果没有设置标签名,leave loop
#sign是循环名,用于结束循环,可以自己随意取名字

4. while循环

#语法:
while 条件 do
	//代码
end while


create procedure pro_11(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     while num>=i do
           set total=total+i;
           set i=i+1;
     end while;
     select total from dual;
end //

call pro_11(100)//

5.repeat循环

#语法
repeat
	代码
	until 条件    -- 直重复到条件为true才结束
end repeat

create procedure pro_12(in num int)
begin
     declare total int default 0;
     declare i int default 0;
     repeat
           set total=total+i;
           set i=i+1;
           until i>num
     end repeat;
     select total from dual;
end //

call pro_12(100)//

6. leave和iterate

leave类似于break,iterate类似于continue


create procedure pro_13()
begin
     declare i int default 0;
     sign:while i<5 do
           set i=i+1;
           if(i=3) then
                   #leave sign;   -- 类似于break
                   iterate sign;    -- 类似于continue
           end if;
           select i from dual;
     end while;
end //

call pro_13()//

四. 自定义函数

    调用直接 select

#语法:
Create function 函数名(形参) returns 返回的数据类型
begin
	//函数体
end


#第一步
delimiter //

#不带参数的函数
create function myfun() returns varchar(32)
begin
     return 123;
end//

#调用函数
select myfun()//

#Linux中的mysql不支持函数
#先查看是否支持
show variables like 'log_bin_trust_function_creators';
#进入/etc/my.cnf
#放在[mysqld]
log_bin_trust_function_creators=1
#写好以后重启mysql服务器
service mysqld restart


#带参数
create function myfun_1(num1 int,num2 int) returns int
begin
     declare num int default 0;
     set num=num1+num2;
     return num;
end //

select myfun_1(100,200)//


#删除函数
drop function myfun_1//


#自己封装 首字母大写
create function firstup(str varchar(1024)) returns varchar(1024)
begin
	return concat(ucase(substr(str,1,1)),substr(str,2));
end//


-- 封装一个首字母大写的自定义函数

create function firstup(str varchar(32)) returns varchar(32)
begin
	declare a varchar(1);
	declare b varchar(32);
	set a = upper(left(str,1));
	set b = lower(substr(str,2));
	return concat(a,b);
end

select firstup("word")

<think>我们正在使用Navicat for MySQL来设置定时任务(事件)以自动更新数据库表数据。根据引用[1]的内容,我们可以按照以下步骤操作: 1. 创建一个函数(或存储过程)来执行更新操作。 2. 创建一个事件,调用这个函数,并设置执行计划(例如每天执行)。 具体步骤: **步骤1:创建更新函数(存储过程)** 在Navicat中,我们可以通过以下SQL语句创建一个存储过程(函数)来更新表。假设我们要将表`week7_user`中的`isqiandao`字段每天更新为0。 ```sql DELIMITER $$ CREATE PROCEDURE update_qiandao() BEGIN UPDATE week7_user SET isqiandao = 0; END$$ DELIMITER ; ``` 注意:这里使用`DELIMITER`来改变语句结束符,因为存储过程中包含分号。 **步骤2:创建事件** 接下来,创建一个事件来定期调用这个存储过程。例如,我们设置每天零点执行: ```sql CREATE EVENT reset_qiandao_event ON SCHEDULE EVERY 1 DAY STARTS CURRENT_DATE + INTERVAL 1 DAY -- 从明天开始,也可以设置为从当前时间开始 DO CALL update_qiandao(); ``` 或者,如果我们希望从特定的时间开始,可以调整`STARTS`子句。例如,设置为今天零点开始(如果当前时间已经过了零点,则从明天零点开始): ```sql CREATE EVENT reset_qiandao_event ON SCHEDULE EVERY 1 DAY STARTS TIMESTAMP(CURRENT_DATE) -- 今天零点 DO CALL update_qiandao(); ``` 注意:在创建事件之前,请确保MySQL的事件调度器已开启。可以通过以下命令查看并开启: ```sql -- 查看事件调度器状态 SHOW VARIABLES LIKE 'event_scheduler'; -- 如果未开启,使用以下命令开启(需要SUPER权限) SET GLOBAL event_scheduler = ON; ``` **步骤3:在Navicat中操作** 除了直接执行SQL语句,我们也可以在Navicat的图形界面中操作: 1. 打开Navicat,连接到数据库。 2. 在左侧导航栏中,找到“函数”节点,右键单击选择“新建函数”。按照向导输入上述存储过程的代码,保存(命名为`update_qiandao`)。 3. 在左侧导航栏中,找到“事件”节点,右键单击选择“新建事件”。 - 在“定义”选项卡中,输入:`CALL update_qiandao();` - 在“计划”选项卡中,设置执行周期。选择“按计划”,然后选择“每天”,设置开始时间(例如00:00:00)。 4. 保存事件,命名为`reset_qiandao_event`。 **注意事项:** - 确保MySQL版本支持事件(MySQL 5.1及以上)。 - 确保用户有创建事件和存储过程的权限。 - 事件调度器必须开启(`event_scheduler`为`ON`)。 通过以上步骤,就可以实现每天自动将`week7_user`表中的`isqiandao`字段更新为0。 §§相关问题§§ 1. 如何验证Navicat中的定时任务是否成功执行? 2. 除了每天执行,Navicat事件调度还支持哪些时间间隔设置? 3. 如果我想在每周一的零点执行更新,应该如何设置事件? 4. 如何修改或删除已创建的定时任务?</think>### Navicat 设置定时任务自动更新数据库表数据 在 Navicat 中配置定时任务(事件)自动更新表数据,需按以下步骤操作(以 MySQL 为例): #### 🔧 步骤 1:启用事件调度器 ```sql -- 检查事件调度器状态 SHOW VARIABLES LIKE 'event_scheduler'; -- 若未开启则执行(需管理员权限) SET GLOBAL event_scheduler = ON; ``` #### 📝 步骤 2:创建更新函数/存储过程 ```sql DELIMITER $$ CREATE PROCEDURE auto_update_data() BEGIN -- 示例:每日将 isqiandao 字段重置为 0 UPDATE your_table SET isqiandao = 0; END$$ DELIMITER ; ``` > 📌 替换 `your_table` 和字段名为实际表名及更新逻辑 [^1] #### ⏰ 步骤 3:创建定时事件 在 Navicat 图形界面操作: 1. 连接数据库 → 点击 **事件** → **新建事件** 2. **定义** 选项卡输入: ```sql CALL auto_update_data(); -- 调用存储过程 ``` 3. **计划** 选项卡设置: - 执行频率:选 `重复` - 间隔:`1` 天(按需调整) - 开始时间:设置首次执行时间(如 `2024-06-20 00:00:00`) #### ✅ 步骤 4:保存并激活 1. 设置事件名称(如 `daily_reset_event`) 2. 状态选 `ENABLE` 3. 点击 **保存** 按钮 #### 🔍 验证执行 ```sql -- 查看事件状态 SHOW EVENTS FROM your_database; -- 手动测试执行 CALL auto_update_data(); ``` > ⚠️ **注意事项** > - 确保用户有 `EVENT` 权限 > - 复杂更新建议先用事务包裹 > - 时间格式需符合 `YYYY-MM-DD HH:MM:SS` > - Oracle 定时任务语法不同(参考引用[2]的 `dbms_job`)[^2] --- ### 📚 相关问题 1. 如何设置 Navicat 定时任务在每周一凌晨执行? 2. 如果更新失败,如何查看 MySQL 事件执行日志? 3. 如何优化大数据表的定时更新性能? 4. Navicat 定时任务与 Linux crontab 调用 SQL 脚本有何优劣? [^1]: Navicat for MySQL 设置定时任务(事件)。新建一个函数 ,取名为update_qiandao ,内容为 UPDATE week7_user SET isqiandao = 0 在“计划”选项卡中设置 执行时间,这里我选择的是每天。 [^2]: 使用Oracle做定时任务。declare jobno number; begin dbms_job.submit(jobno,'cx_qcsiteadmin_db.qp_pre_load_report;',trunc(sysdate),'trunc(sysdate)+1');--每天零点更新 commit; end; [^3]: mysql数据库 定时触发过程。创建一个表: DROP TABLE IF EXISTS `test_sche`; CREATE TABLE `test_sche` ( `id` int(11) NOT NULL, `counts` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test_sche` VALUES('1','188');
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值