Mysql创建存储过程showstudent

创建存储过程showatudent，根据当前系统时间判断当前季节。
1-3月为春季，4-6月为夏季，7-9月为秋季，10-12月为冬季。
并显示数据库schooldb中出生于当前季节的学生人数。

DELIMITER //
CREATE PROCEDURE showatudent()
BEGIN
    DECLARE current_month INT;
    DECLARE current_season VARCHAR(10);
    DECLARE student_count INT;
    SET current_month = MONTH(NOW());
    
    IF current_month BETWEEN 1 AND 3 THEN
        SET current_season = '春季';
    ELSEIF current_month BETWEEN 4 AND 6 THEN
        SET current_season = '夏季';
    ELSEIF current_month BETWEEN 7 AND 9 THEN
        SET current_season = '秋季';
    ELSE
        SET current_season = '冬季';
    END IF;
    
    SELECT COUNT(*) INTO student_count FROM schooldb WHERE DATE_FORMAT(birthdate, '%m') = current_month;
    
    SELECT CONCAT('当前季节为', current_season, '，出生于当前季节的学生人数为', student_count, '人') AS result;
END //
DELIMITER ;

以下为示例，表名（目前用的schooldb）根据实际情况改下：

-- 修改默认的语句分隔符
DELIMITER //

CREATE PROCEDURE showatudent()
BEGIN
    DECLARE current_season VARCHAR(10);
    DECLARE season_start_month INT;
    DECLARE season_end_month INT;
    DECLARE student_count INT;
    
    -- 获取当前月份
    SET @current_month := MONTH(NOW());
    
    -- 判断当前季节
    IF @current_month BETWEEN 1 AND 3 THEN
        SET current_season := '春季';
        SET season_start_month := 1;
        SET season_end_month := 3;
    ELSEIF @current_month BETWEEN 4 AND 6 THEN
        SET current_season := '夏季';
        SET season_start_month := 4;
        SET season_end_month := 6;
    ELSEIF @current_month BETWEEN 7 AND 9 THEN
        SET current_season := '秋季';
        SET season_start_month := 7;
        SET season_end_month := 9;
    ELSEIF @current_month BETWEEN 10 AND 12 THEN
        SET current_season := '冬季';
        SET season_start_month := 10;
        SET season_end_month := 12;
    ELSE
        SET current_season := '无效季节';
        SET season_start_month := 0;
        SET season_end_month := 0;
    END IF;
    
    -- 查询出生于当前季节的学生人数
    SET @query := CONCAT('SELECT COUNT(*) FROM schooldb WHERE MONTH(birthday) BETWEEN ', season_start_month, ' AND ', season_end_month);
    PREPARE stmt FROM @query;
    EXECUTE stmt INTO student_count;
    DEALLOCATE PREPARE stmt;
    
    -- 显示结果
    SELECT CONCAT('当前季节：', current_season) AS '季节', CONCAT('出生于当前季节的学生人数：', student_count) AS '学生人数';
END //

DELIMITER ;

• 你看下这篇博客吧, 应该有用👉 ：MySQL中show命令的使用（持续积累更新）
• 除此之外, 这篇博客: MySQL指令学习笔记中的 5. 通过show指令显示当前数据库中的所有表 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
  

• mysql> show tables;
  +-----------------+
  | Tables_in_demo1 |
  +-----------------+
  | student         |
  +-----------------+
  1 row in set (0.00 sec)
  

• 以下回答由chatgpt基于相关博客总结生成:

  首先需要创建一个存储过程，名为showstudent，代码如下:

  CREATE PROCEDURE showstudent()
  BEGIN
   DECLARE season INT;
   SET season = MONTH(NOW());
   IF season BETWEEN 1 AND 3 THEN
     SELECT COUNT(*) FROM schooldb WHERE MONTH(birthday) BETWEEN 1 AND 3;
   ELSEIF season BETWEEN 4 AND 6 THEN
     SELECT COUNT(*) FROM schooldb WHERE MONTH(birthday) BETWEEN 4 AND 6;
   ELSEIF season BETWEEN 7 AND 9 THEN
     SELECT COUNT(*) FROM schooldb WHERE MONTH(birthday) BETWEEN 7 AND 9;
   ELSEIF season BETWEEN 10 AND 12 THEN
     SELECT COUNT(*) FROM schooldb WHERE MONTH(birthday) BETWEEN 10 AND 12;
   END IF;
  END
  

  在该存储过程中，首先定义了一个名为season的整型变量，用于存储当前系统时间的月份（使用MONTH函数），然后利用IF...ELSEIF...ELSE语句判断当前是哪个季节，并利用COUNT函数统计符合条件的学生数量，最后返回结果。

  调用该存储过程可以使用以下代码：

  CALL showstudent();
  

  注：需先在schooldb中创建一张表，包含学生信息，其中包括birthday字段，存储学生的生日信息。