MySQL存储过程及日期时间函数的用法

前言

一、日期时间函数

MySQL 也支持日期时间处理,提供了很多处理日期和时间的函数。一些常用的日期时间函数
字符串函数 描述
curdate() 返回当前时间的年月日
curtime() 返回当前时间的时分秒
now() 返回当前时间的日期和时间
month(x) 返回日期 x 中的月份值
week(x) 返回日期 x 是年度第几个星期
hour(x) 返回 x 中的小时值
minute(x) 返回 x 中的分钟值
second(x) 返回 x 中的秒钟值
dayofweek(x) 返回 x 是星期几,1 星期日,2 星期一
dayofmonth(x) 计算日期 x 是本月的第几天
dayofyear(x) 计算日期 x 是本年的第几天

1.1 MySQL 日期时间函数的使用方法,具体操作如下所示。

curdate() 返回当前时间的年月日
curtime() 返回当前时间的时分秒
now() 返回当前时间的日期和时间

mysql> select curdate(),curtime(),now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2020-10-16 | 01:47:38  | 2020-10-16 01:47:38 |
+------------+-----------+---------------------+
1 row in set (0.05 sec)

month(x) 返回日期 x 中的月份值
week(x) 返回日期 x 是年度第几个星期
hour(x) 返回 x 中的小时值

mysql> select month('2020-10-16'),week('2020-10-16'),hour('08:56');
+---------------------+--------------------+---------------+
| month('2020-10-16') | week('2020-10-16') | hour('08:56') |
+---------------------+--------------------+---------------+
|                  10 |                 41 |             8 |
+---------------------+--------------------+---------------+
1 row in set (0.00 sec)

当前年月日是年度第几个星期

mysql> select week(curdate());
+-----------------+
| week(curdate()) |
+-----------------+
|              41 |
+-----------------+
1 row in set (0.00 sec)

当前的日期和时间是年度的第几个星期

mysql> select week(now());
+-------------+
| week(now()) |
+-------------+
|          41 |
+-------------+
1 row in set (0.00 sec)

minute(x) 返回 x 中的分钟值
second(x) 返回 x 中的秒钟值
显示现在时间的分钟值,还有秒值

mysql> select minute(now()),second(now());
+---------------+---------------+
| minute(now()) | second(now()) |
+---------------+---------------+
|            57 |            52 |
+---------------+---------------+
1 row in set (0.00 sec)

dayofweek(x) 返回 x 是星期几,1 星期日,2 星期一
显示今天是一周当中的第几天

mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
|                6 |
+------------------+
1 row in set (0.00 sec)

dayofmonth(x) 计算日期 x 是本月的第几天

mysql> select  dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
|                16 |
+-------------------+
1 row in set (0.00 sec)

dayofyear(x) 计算日期 x 是本年的第几天

mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
|              290 |
+------------------+
1 row in set (0.00 sec)

二、存储过程

前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句,使用这样的SQL 语句虽然可以完成用户的需求,但在实际的数据库应用中,
有些数据库操作可能会非常复杂,可能会需要多条 SQL 语句一起去处理才能够完成,这时候就可以使用存储过程, 轻松而高效的去完成这个需求。
1.存储过程就是数据库中的函数
2.增强数据库的安全性
java Python php 后台程序 —》数据库
sql语句写在程序中(java…);

2.1存储过程的优点

  • 存储过程执行一次后,生成的二进制代码就驻留在缓冲区,之后如果再次调用的话,将 直接调用二进制代码,使得存储过程的执行效率和性能得到大幅提升。
  • 存储过程是 SQL 语句加上控制语句的集合,有很强的灵活性,可以完成复杂的运算。
  • 存储过程存储在服务器端,客户端调用时,直接在服务器端执行,客户端只是传输的调 用语句,从而可以降低网络负载。
  • 存储过程被创建后,可以多次重复调用,它将多条 SQL 封装到了一起,可随时针对 SQL语句进行修改,不影响调用它的客户端。
  • 存储过程可以完成所有的数据库操作,也可以通过编程的方式控制数据库的信息访问权限。

2.2创建存储过程

使用 CREATE PROCEDURE 语句创建存储过程,其语法格式如下所示。

delimiter $$ '//建立存储过程'
create  procedure  <过程名>(过程参数,参数名,类型) '//【IN|OUT|INOUT】<参数名><类型>
-> begin '//开始'
....
-> end $$ '//结束'

delimiter ; '//结束存储过程'
中间一定要有空格

2.3 in表示传入参数

mysql> delimiter  $$
mysql> create procedure in_param(in p_in int)
    -> begin
    -> select p_in;
    -> set  p_in=2;
    -> select p_in;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> call in_param(1);  '//输入一个值'
+------+
| p_in |
+------+
|    1 |
+------+
1 row in set (0.00 sec)
+------+
| p_in |
+------+
|    2 |
+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

2.4 out表示输出参数

(@p_out) 是将这个变量名中的值输出出来

mysql> delimiter $$
mysql> create procedure out_param(out p_out int)
    -> begin
    -> select p_out;
    -> set p_out=2;
    -> select p_out;
    -> end $$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> call out_param(@p_out);
+-------+
| p_out |
+-------+
|  NULL |
+-------+
1 row in set (0.00 sec)
+-------+
| p_out |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

2.5 inout输入参数

既能输入又能输出

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
    mysql> delimiter ;
    mysql> set @p_inout=1;
    mysql> call inout_param(@p_inout)
+---------+
| p_inout |
+---------+
|       1 |
+---------+
+---------+
| p_inout |
+---------+
|       2 |
+---------+

mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
|        2 |
+----------+
'//调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量'

2.6 变量

变量定义
局部变量声明一定要放在存储过程体的开始:

declare [变量名] [变量类型]
datatype 为 MySQL 的数据类型,如: int, float, date,varchar(length)

变量赋值

SET 变量名 = 表达式值 [,variable_name = expression ...]

用户变量
在MySQL客户端使用用户变量:

mysql > SELECT 'Hello World' into @x;  
mysql > SELECT @x;  
+-------------+  
|   @x        |  
+-------------+  
| Hello World |  
+-------------+ 
mysql > SET @y='Goodbye Cruel World';  
mysql > SELECT @y;  
+---------------------+  
|     @y              |  
+---------------------+  
| Goodbye Cruel World |  
+---------------------+ 
mysql > SET @z=1+2+3;  
mysql > SELECT @z;  
+------+  
| @z   |  
+------+  
|  6   |  
+------+

注释
两个横杆-- 单行注释

2.7 MySQL存储过程的删除

mysql> drop procedure out_param;
Query OK, 0 rows affected (0.00 sec)

2.8 条件语句

if-then-else 语句

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17)
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

2.9 case语句:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 
case
    when var=0 then
    insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case

2.9.1 循环语句

while ···· end while

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

while 条件 do
–循环体
endwhile

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页