MySQL编程开发（了解）
MySQL编程开发
· 变量
系统变量：
MySQL内置变量；
# 查看所有系统变量
show variables \G;
# 通过模糊查询筛选变量
show variables like '%path%';（支持通配符 % 或 _）
# 通过set修改系统变量
-- 临时修改全局变量（需权限）
set global max_connections = 200; --调整最大连接数为200
-- 直接查询
select @@max_connections;
-- 临时修改会话变量（当前连接生效）
set session wait_timeout = 3600; --调整超时时间为1小时
-- 直接查询
select session wait_timeout;
全局变量：在所有终端中都生效；
会话变量：在当前会话（本次登录）；
用户变量：用于临时存储用户自己的数据，例如：select查询的结果
# 赋值（方式1：直接赋值）
set @num1 = 10;
set @num2 := 20; -- 支持 := 避免与 sql 关键字冲突
# 赋值（方式2：临时存储查询结果，把查询结果赋值给@user_name）
select username into @user_name from users;
# 也可以进行一些运算，被函数调用
select @num1 + @num2; -- 输出30
select @user_count; -- 查询users表行数
局部变量：在存储过程内部定义的变量，只在该存储过程内部有效
· 触发器
触发器（Triggers） 是当某个表在进行（update/insert/delete）操作时，自动同步进行的关联操作。
例如：在对A表插入新的数据，B表会同步记录日志。
创建触发器
create trigger trigger_name
before/after insert/update/delete
on tbl_name
[ for each row ] -- 行级触发器
begin
trigger_stmt ;
end;
删除触发器
drop trigger [schema_name.]trigger_name
查看触发器
show triggers ;
· 存储过程
将复杂的SQL语句，包含在存储过程中，存放在服务端。
客户端使用call存储过程名;执行该存储过程；
优点：简化客户端的SQL编写，减少网络发送的数据量（减少网络带宽），加速执行，提高效率。
缺点：不适合分布式的环境使用。
总结：存储过程适合用于处理数据库端的复杂、高频复用的业务逻辑，能够提升性能和安全性；但在可移植性、调试、资源占用等方面存在不足。
注意事项
权限控制：创建存储过程/函数需 CREATE ROUTINE 权限，执行需 EXECUTE 权限。
作用域：存储过程/函数的局部变量需用 DECLARE 声明，且必须在 BEGIN...END 块的开头。
性能：频繁调用的简单逻辑建议用函数，复杂批量操作建议用存储过程。
安全性：避免在函数中执行数据修改操作（如 INSERT），可能导致不可预测的副作用。
# 创建存储过程（需先修改分隔符，避免分号冲突）
delimiter $$
create procedure countemployees(
in dept_id int, -- 输入参数：部门 id
out 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 函数名（参数）……;
特性 | 存储过程 | 函数 |
返回值 | 无或多个输出参数 | 必须返回一个单一值 |
调用方式 | CALL procedure_name( ) | SELECT function_name( ) |
SQL上下文 | 可包含数据修改（如 INSERT） | 通常禁止修改数据（仅查询） |
适用场景 | 复杂业务逻辑（如批量数据处理） | 简单计算或单一结果查询 |
# 创建函数（需开启安全模式，或指定 `deterministic` 等特性）
delimiter $$
create function calculateannualsalary(emp_id int)
returns decimal(10,2) -- 指定返回值类型
deterministic -- 声明函数是确定性的（相同输入返回相同输出）
begin
declare 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;
· MySQL内置和自定义函数
-- ================ 内置函数 ================
# 数值函数
abs(x) -- 绝对值 abs(-10.9) = 10
format(x, d) -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x) -- 向上取整 ceil(10.1) = 11
floor(x) -- 向下取整 floor (10.1) = 10
round(x) -- 四舍五入去整
mod(m, n) -- m%n m mod n 求余 10%3=1
pi() -- 获得圆周率
pow(m, n) -- m^n
sqrt(x) -- 算术平方根
rand() -- 随机数
truncate(x, d) -- 截取d位小数
# 时间日期函数
now(), current_timestamp(); -- 当前日期时间
current_date(); -- 当前日期
current_time(); -- 当前时间
date('yyyy-mm-dd hh:ii:ss'); -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss'); -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp(); -- 获得unix时间戳
from_unixtime(); -- 从时间戳获得时间
# 字符串函数
length(string) -- string长度，字节
char_length(string) -- string的字符个数
substring(str, position [,length]) -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str) -- 在str中用replace_str替换search_str
instr(string ,substring) -- 返回substring首次在string中出现的位置
concat(string [,...]) -- 连接字串
charset(str) -- 返回字串字符集
lcase(string) -- 转换成小写
left(string, length) -- 从string2中的左边起取length个字符
load_file(file_name) -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad) -- 重复用pad加在string开头,直到字串长度为length
ltrim(string) -- 去除前端空格
repeat(string, count) -- 重复count次
rpad(string, length, pad) --在str后用pad补充,直到长度为length
rtrim(string) -- 去除后端空格
strcmp(string1 ,string2) -- 逐字符比较两字串大小
# 聚合函数
count()
sum();
max();
min();
avg();
group_concat()
# 其他常用函数
md5();
default();