mysql编程（简单了解）

mysql编程开发

变量

系统变量：MySQL内置变量

全局变量
--查看所有系统变量
SHOW VARIABLES\G;（默认查会话变量）或SHOW GLOBAL VARIABLES（查全局变量）
show variables like "%path%"; #通过模糊查询

全局变量：在所有终端中生效

-- 查看全局变量
SHOW GLOBAL VARIABLES LIKE '变量名';
SELECT @@global.变量名;-- 修改全局变量（当前会话可能不生效，需重新连接）
SET GLOBAL 变量名 = 值;
SET @@global.变量名 = 值;修改变量
-- 临时修改全局变量（需权限）
SET GLOBAL max_connections = 200;  -- 调整最大连接数为200slect @@max_connections；--查看变量

会话变量：在当前会话（本次登录）。会话变量必须加 @ 符

SET @变量名 = 值;  -- 赋值
SELECT @变量名;    -- 使用

-- 临时修改会话变量（当前连接生效）
SET session wait_timeout = 3600;   -- 调整超时时间为1小时
-- 查看会话变量（如字符集）
SHOW VARIABLES LIKE 'character_set_client';
-- 或直接查询
SELECT @@session.wait_timeout;

局部变量：在存储过程内部定义一个变量，只在该存储过程内部有效。局部变量无 @ 符号

    -- 定义局部变量DECLARE total INT DEFAULT 0;-- 赋值SELECT COUNT(*) INTO total FROM users;-- 使用SELECT total;

用户变量：用来临时存储，用户自己的数据，例如：select的查询结果。

用户变量操作示例
-- 赋值（方式1：直接赋值）
set @n1 = "10";
set @n2 = "10";
select @n1 +@n2;出来的结果是30-- 赋值（方式2：查询结果赋值）
SELECT COUNT(*) INTO @user_count FROM users;  -- 将 users 表行数存入 @user_count
SELECT @user_count AS total_users;    -- 输出 users 表行数

触发器

触发器：当某个表在进行（update\insert\delete）操作时，自动同步进行的关联操作。

例如：在对A表插入一条新的数据时，B表会同步记录日志。

创建触发器

create trigger trigger_name 
before/after insert/update/delete        #操作类型
on 表名 
[ for each row ]  -- 行级触发器
begintrigger_stmt ;
end;这是创建的语法，但是如果我们想让弄成 DELIMITER 分隔符的显式声明，
临时修改分隔符，避免；冲突
DELIMITER //
create trigger trigger_name 
before/after insert/update/delete        #操作类型
on tbl_name 
[ for each row ]  -- 行级触发器
begintrigger_stmt ;    #触发后要做什么
end;
//
DELIMITER ;

删除触发器

drop trigger [库名]trigger_name

查看触发器：

show triggers ;

发器主要有以下六种执行时间和事件

1. BEFORE INSERT

• 执行时间：在插入操作发生之前。
• 作用：允许你在数据插入之前修改数据，或对插入操作进行验证。

2. AFTER INSERT

• 执行时间：在插入操作发生之后。
• 作用：可以在数据插入后执行额外的操作，如更新其他表、记录日志等。

3. BEFORE UPDATE

• 执行时间：在更新操作发生之前。
• 作用：允许你在数据更新之前对数据进行校验或修改。

4. AFTER UPDATE

• 执行时间：在更新操作发生之后。
• 作用：用于在数据更新之后执行一些后续操作，比如同步其他表的数据或执行计算。

5. BEFORE DELETE

• 执行时间：在删除操作发生之前。
• 作用：允许你在数据删除之前进行某些检查或处理，例如备份删除的数据或记录日志。

6. AFTER DELETE

• 执行时间：在删除操作发生之后。
• 作用：用于在数据删除之后执行一些操作，如清理相关数据或通知系统其他部分。

触发器的执行顺序

在一个操作中，如果涉及多个触发器，它们的执行顺序是固定的：

• BEFORE INSERT 触发器在 AFTER INSERT 触发器之前执行。
• BEFORE UPDATE 触发器在 AFTER UPDATE 触发器之前执行。
• BEFORE DELETE 触发器在 AFTER DELETE 触发器之前执行。

插入触发器：

例如：我们有一个员工表 employees 和一个日志表 employee_log。每当有新的员工被插入到 employees 表时，我们希望在 employee_log 表中记录这次插入的操作。

-- 创建 employees 表
CREATE TABLE employees (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(100) NOT NULL,position VARCHAR(100),salary DECIMAL(10, 2)
);-- 创建 employee_log 表
CREATE TABLE employee_log (log_id INT AUTO_INCREMENT PRIMARY KEY,action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,action_type VARCHAR(50),employee_id INT,employee_name VARCHAR(100),employee_position VARCHAR(100),employee_salary DECIMAL(10, 2)
);-- 创建插入触发器
DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGININSERT INTO employee_log (action_type, employee_id, employee_name, employee_position, employee_salary)VALUES ('INSERT', NEW.id, NEW.name, NEW.position, NEW.salary);
END;
//
DELIMITER ;我们有一个员工表 employees 和一个日志表 employee_log。每当有新的员工被插入到 employees 表时，我们希望在 employee_log 表中记录这次插入的操作。

2. 更新触发器

假设我们有一个产品表 products，每当产品的价格被更新时，我们希望记录这个更新的历史信息到 price_changes 表中。

-- 创建 products 表
CREATE TABLE products (product_id INT AUTO_INCREMENT PRIMARY KEY,product_name VARCHAR(100),price DECIMAL(10, 2)
);-- 创建 price_changes 表
CREATE TABLE price_changes (change_id INT AUTO_INCREMENT PRIMARY KEY,change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,product_id INT,old_price DECIMAL(10, 2),new_price DECIMAL(10, 2)
);-- 创建更新触发器
DELIMITER //
CREATE TRIGGER before_product_update
BEFORE UPDATE ON products
FOR EACH ROW
BEGINIF OLD.price <> NEW.price THENINSERT INTO price_changes (product_id, old_price, new_price)VALUES (OLD.product_id, OLD.price, NEW.price);END IF;
END;
//
DELIMITER ;

3. 删除触发器

假设我们有一个订单表 orders 和一个已删除订单日志表 deleted_orders_log。每当订单被删除时，我们希望在 deleted_orders_log 中记录删除操作。

-- 创建 orders 表
CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY,order_date DATE,amount DECIMAL(10, 2)
);-- 创建 deleted_orders_log 表
CREATE TABLE deleted_orders_log (log_id INT AUTO_INCREMENT PRIMARY KEY,delete_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,order_id INT,order_date DATE,amount DECIMAL(10, 2)
);-- 创建删除触发器
DELIMITER //
CREATE TRIGGER after_order_delete
AFTER DELETE ON orders
FOR EACH ROW
BEGININSERT INTO deleted_orders_log (order_id, order_date, amount)VALUES (OLD.order_id, OLD.order_date, OLD.amount);
END;
//
DELIMITER ;

好的，我们来创建一个 MySQL 触发器示例，确保每次修改 grades 表中的学生成绩时，students 表中的总分会自动更新。这个触发器需要处理 grades 表中的插入、更新和删除操作，以保持总分的准确性。

存储过程（不建议使用）

用call执行，没有返回值

将一条或多条复杂的SQL语句，包含在存储过程中，存放在服务端，可以被重复调用。

客户端使用：call 存储过程名；执行该存储过程；

优点：简化客户端SQL的执行，减少网络数据量，加速执行，提高效率。

缺点：占用数据库资源，

存储过程适用于处理数据库端的复杂，高频复用的业务逻辑，能提升安全性和性能；但在可移植性，调试，资源占用等方面存在不足，实际使用中需要根据具体场景权衡，避免过度依赖或完全摒弃。

创建格式：

--创建存储过程
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
begin-- SQL语句
end ;--调用存储过程
call procedure_name() ;
--删除存储过程
DROP PROCEDURE  [IF EXISTS] sp_name ；-- 查询存储过程的状态信息
show procedure status;最简单的实例：
use mysql;delimiter $create procedure pro_test1()
beginselect "Hello MySQL" ;
end$delimiter ;

-- 创建存储过程（需先修改分隔符，避免分号冲突）
DELIMITER $$
CREATE PROCEDURE CountEmployees(IN dept_id INT,         -- 输入参数：部门 IDOUT emp_count INT       -- 输出参数：员工数量
)
BEGIN-- 查询部门员工数并赋值给输出参数SELECT COUNT(*) INTO emp_count FROM employees WHERE department_id = dept_id;
END$$
DELIMITER ;-- 调用存储过程
CALL CountEmployees(101, @count);  -- @count 是用户变量，用于接收输出结果-- 查看结果
SELECT @count AS employee_count;

函数

用select执行，有返回值

对数据进行简单的处理，例如：求和，平均值，最大，最小，文本处理，获取时间等。

使用select 函数名（参数）...;可以执行

-- 创建函数（需开启安全模式，或指定 `DETERMINISTIC` 等特性）
DELIMITER $$
CREATE FUNCTION CalculateAnnualSalary(emp_id INT) 
RETURNS DECIMAL(10,2)  -- 指定返回值类型
DETERMINISTIC         -- 声明函数是确定性的（相同输入返回相同输出）
BEGINDECLARE monthly_salary DECIMAL(10,2);DECLARE bonus DECIMAL(10,2);-- 查询月薪和奖金SELECT salary, bonus INTO monthly_salary, bonus FROM employees WHERE id = emp_id;-- 返回年薪（月薪×12 + 奖金）RETURN (monthly_salary * 12) + COALESCE(bonus, 0);  -- COALESCE 处理 NULL
END$$
DELIMITER ;-- 调用函数
SELECT id, name, CalculateAnnualSalary(id) AS annual_salary 
FROM employees 
WHERE id = 1001;

存储过程和函数的区别：

配置调优