文章目录
一、系统变量
说明:变量由系统定义,不是用户定义,属于服务器层面
注意:
- 全局变量需要添加GLOBAL关键字,
- 会话变量需要添加SESSION关键字,如果不写,默认会话级别
使用步骤
查看所有系统变量
SHOW GLOBAL VARIABLES;#全局变量
SHOW SESSION VARIABLES;#会话变量
查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';
SHOW SESSION VARIABLES LIKE '%char%';
查看指定的系统变量的值
#默认会话变量
SELECT @@session.系统变量名;#会话
SELECT @@global.系统变量名;#全局
为某个系统变量赋值
方式一:
SET GLOBAL/【SESSION】 系统变量名=值;
方式二:
SET @@global/【SESSION】.系统变量名=值;
1.1 全局变量
作用域:服务器每次启动将为所有的全局变量赋初始值,针对于所有会话(连接)有效,但不能跨重启
①查看所有全局变量
SHOW GLOBAL VARIABLES;
②查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%g%';
③查看指定的系统变量的值
SELECT @@global.flush_time;
④为某个系统变量赋值
SET @@global.autocommit=0;
SET GLOBAL autocommit=0;
1.2 会话变量
作用域:针对于当前会话(连接)有效
①查看所有会话变量
SHOW VARIABLES;#省略默认会话
SHOW SESSION VARIABLES;
②查看满足条件的部分会话变量
SHOW SESSION VARIABLES LIKE '%g%';
③查看指定的会话变量的值
SELECT @@autocommit;
SELECT @@session.tx_isolation;
④为某个会话变量赋值
#隔离级别更改为读未提交
方式一
SET @@session.tx_isolation='read-uncommitted';
方式二
SET SESSION tx_isolation='read-committed';
二、自定义变量(重点)
引用:https://blog.csdn.net/qq_41081716/article/details/108428687
说明:变量由用户自定义,而不是系统提供的
使用步骤:
1、声明
2、赋值
3、使用(查看、比较、运算等)
2.1 用户变量
作用域:针对于当前会话(连接)有效,作用域同于会话变量应用在任何地方,也就是BEGIN END里面或BEGIN END外面
赋值操作符:=或:=
①声明并初始化
方式一★
SET @变量名=值;
方式二
SET @变量名:=值;
方式三
SELECT @变量名:=值;
②赋值(更新变量的值)
方式一:★
SET @变量名=值;
SET @变量名:=值;
SELECT @变量名:=值;
例子:
SELECT
@row := row_no,
@name := book_name
FROM tb_book
WHERE id = 1;
注意:使用SET赋值时可以用“=”或“:=”,但是使用SELECT语句赋值时必须用“:=赋值”。
方式二:
SELECT 字段 INTO @变量名
FROM 表
where 条件;
例如:
①
SELECT AVG(`salary`) INTO @avg_s
FROM `employees`;
②
SELECT row_no,book_name INTO @row,@name FROM tb_book WHERE id = 1;
③使用(查看变量的值)★
SELECT @变量名;
例如
①
SELECT @avg_s;
②
-- 输出结果
SELECT @row;
SELECT @name;
SET @m=1;
SET @n=1;
SET @sum-@m+@n;
SELECT @sum;
2.2 局部变量
作用域:仅仅在定义它的BEGIN END块中有效
应用在 BEGIN END中的第一句话
例如
(1)声明两个局部变量m和n,并均赋值为1.
(2)把m和n的值加和并赋值给新声明的变量sum,显示sum的值.
三、案例
3.1 案例1
更多例子参见:
sql中变量的使用
https://blog.csdn.net/qq_41081716/article/details/108428687
如:
表:Scores
(Id, Score)
('1', '3.5')
('2', '3.65')
('3', '4.0')
('4', '3.85')
('5', '4.0')
('6', '3.65')
排序
select
score ,
(@i:=@i+1) as rank1
from Scores,(select @i:=0) init //定义变量一般写在from语句之后,相当于在末尾定义变量
order by score desc
3.2 案例2
select user_id as seller_id, if (r2.item_brand is null || r2.item_brand != favorite_brand, "no", "yes") as 2nd_item_fav_brand
from users
left join (
select r1.seller_id, items.item_brand from (
select
@rk := if (@seller = a.seller_id, @rk + 1, 1) as rank,
@seller := a.seller_id as seller_id,
a.item_id
from (
select seller_id, item_id
from orders
order by seller_id, order_date
) a, (select @seller := -1, @rk := 0) b) r1
join items
on r1.item_id = items.item_id
where r1.rank = 2
) r2 on user_id = r2.seller_id;
select id as seller_id, max(if(t.rk=2.0 and t.favorite_brand = t.item_brand, 'yes', 'no')) as 2nd_item_fav_brand
from
(select id, favorite_brand, order_date, item_brand,
if(@pre_id = id, @num:=@num+1, @num:=1) as rk,
@pre_id:=id
from
(select u.user_id as id, u.favorite_brand, o.order_date, i.item_brand
from users as u left outer join orders as o
on u.user_id = o.seller_id
left outer join items as i
on o.item_id = i.item_id
order by u.user_id, o.order_date) as a, (select @pre_id:=null, @num:=1) as b) as t
group by id
四、用户变量和局部变量的对比
自定义变量 | 作用域 | 定义位置 | 语法 |
---|---|---|---|
用户变量 | 当前会话 | 会话的任何地方 | 加@符号,不用指定类型 |
局部变量 | BEGIN END中 | 只能在BEGIN END的第一句话 | 一般不用加@,需要指定类型 |