变量

CREATE DATABASE IF NOT EXISTS school;
USE school;

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(20),
    dept VARCHAR(20),
    score DECIMAL(5,2)
);

INSERT INTO employees (emp_name, dept, score) VALUES
('大翔', '技术部', 100),
('白歌', '技术部', NULL);

DELIMITER //

CREATE PROCEDURE CalcSum(IN a INT, IN b INT, OUT result INT)
BEGIN
    DECLARE temp INT DEFAULT 0;  -- 声明局部变量，默认值为 0
    SET temp = a + b;
    SET result = temp;
END //

DELIMITER ;

-- 调用
SET @res = 0;
CALL CalcSum(10, 20, @res);
SELECT @res AS result;

DELIMITER //

CREATE PROCEDURE GetStats()
BEGIN
    DECLARE v_count INT DEFAULT 0;
    DECLARE v_avg DECIMAL(5,2);
    DECLARE v_max DECIMAL(5,2);

    SELECT COUNT(*) INTO v_count FROM employees;
    SELECT AVG(score) INTO v_avg FROM employees;
    SELECT MAX(score) INTO v_max FROM employees;

    SELECT v_count AS 总人数, v_avg AS 平均分, v_max AS 最高分;
END //

DELIMITER ;

-- 调用
CALL GetStats();

DECLARE var_name datatype [DEFAULT value];   -- 声明
SET var_name = expr;                         -- SET 赋值
SELECT col INTO var_name FROM table;         -- SELECT INTO 赋值

-- 直接赋值
SET @name = '大翔';
SET @age = 20, @score = 90.5;

SELECT @name, @age, @score;

-- 在查询中赋值
SELECT COUNT(*) INTO @total FROM employees;
SELECT @total;

-- 第一步：获取总数
SELECT COUNT(*) INTO @total FROM employees;
SELECT @total AS 总人数;

-- 第二步：用它做分页计算
SET @page_size = 1;
SET @total_pages = CEIL(@total / @page_size);
SELECT @total_pages AS 总页数;

-- 第三步：计算最后一页的记录数
SET @last_page_size = @total - (@total_pages - 1) * @page_size;
SELECT @last_page_size AS 最后一页记录数;

-- 先初始化变量
SET @sum = 0;

-- 使用 := 在 SELECT 中累加
SELECT emp_id, score, @sum := @sum + IFNULL(score, 0) AS running_total
FROM employees
ORDER BY emp_id;

-- 查看最终累加值
SELECT @sum AS 总分;

-- 不初始化，直接使用
SELECT emp_id, score, @sum2 := @sum2 + score AS running_total
FROM employees
ORDER BY emp_id;

-- 查看系统变量
SELECT @@global.max_connections;
SELECT @@session.sql_mode;
SELECT @@sql_mode;  -- 默认查看会话级

-- 设置会话变量
SET SESSION sql_mode = 'STRICT_TRANS_TABLES';
SELECT @@sql_mode;

-- 设置全局变量（需 SUPER 权限）
-- SET GLOBAL max_connections = 500;

-- 用户变量在过程外定义
SET @outer_var = '用户变量值';

DELIMITER //

CREATE PROCEDURE VarScopeDemo()
BEGIN
    DECLARE local_var VARCHAR(50);  -- 局部变量
    SET local_var = '局部变量值';

    -- 局部变量和用户变量都可以在过程内使用
    SELECT local_var AS 局部变量, @outer_var AS 用户变量;
END //

DELIMITER ;

CALL VarScopeDemo();

-- 过程外只能访问用户变量
SELECT @outer_var AS 过程外用户变量;

-- 过程外不能访问局部变量，下面这句会报错
-- SELECT local_var;  -- ERROR 1054 (42S22): Unknown column 'local_var' in 'field list'