mysql set变量while mysql变量怎么使用

mysql 变量

• 练习用到的表如下

CREATE TABLE `person` (
  `id` int(11) DEFAULT NULL,
  `first_name` varchar(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `gender` char(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (1, 'Bob', 25, 'M');
INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (2, 'Jane', 20, 'F');
INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (3, 'Jack', 30, 'M');
INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (4, 'Bill', 32, 'M');
INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (5, 'Nick', 22, 'M');
INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (6, 'Kathy', 18, 'F');
INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (7, 'Steve', 39, 'M');
INSERT INTO `test`.`person` (`id`, `first_name`, `age`, `gender`) VALUES (8, 'Anne', 25, 'F');

变量

系统变量：全局变量、会话变量
自定义变量：用户变量、局部变量

一、系统变量
说明：变量由系统提供，不是用户定义，属于服务器层面。
使用语法:

#查看所有的系统变量
SHOW VARIABLES; 
SHOW GLOBAL VARIABLES;
SHOW SESSION VARIABLES;

#查看满足条件的部分系统变量
SHOW  VARIABLES LIKE '%char%';

#查看指定的某个系统变量的值
SELECT @@global.系统变量名;
SELECT @@GLOBAL.autocommit;   1

#为某个系统变量赋值
#方式一：
SET GLOBAL | 【SESSION】.系统变量名 = 值;
#方式二：
SET @@GLOBAL | 【SESSION】.系统变量名 = 值;

注意：如果是全局级别则需要加 GLOBAL，如果是会话级别，则需要加 SESSION，如果不写则默认 SESSION

全局变量作用域：服务器每次启动将为所有全局变量赋初始值，针对所有的会话（连接）有效，但不能跨重启，重启服务器后，失效

会话变量作用域：仅仅针对以当前会话（连接）有效。

二、自定义变量
说明：变量是用户自定义的，不是由系统定义的

使用步骤：
声明
赋值
使用（查看，比较，运算等）

1.用户变量
作用域：针对于当前（会话）连接有效，同于会话变量的作用域
应用在任何地方，也就是 begin end 的里面 或者 begin end 的外面

2.局部变量
作用域：仅仅定义在它的 begin end 中 有效

	作用域	定义和使用的位置	语法
用户变量	当前会话中	会话中的任何地方	必须加@符号，不用限定类型
局部变量	BEGIN END 中	只能在BEGIN END 中，且为第一句话	一般不用加@符号，需要限定类型

#1.声明
DECLARE 变量名 类型; 
DECLARE 变量名 类型 DEFAULT 值;

#2.赋值
set @局部变量名=值；
set @局部变量名:=值；
select @局部变量名:=值；

#3.使用
SELECT 局部变量名;

a.定义和修改

方法一：

#声明并初始化 赋值的操作符： ： 或 ：=
set @变量名:=值；

方法二：

#SELECT 只能使用 ：=
select @变量名:=值；

方法三：

select 查询的结果 into @变量名 from 表;

注意：要求查询的结果只能是一个值，如果是多个是不行的

b.使用

select @变量名;

例子：声明两个变量并赋初始值，求和并打印

1用户变量：

set @num1:=3;
set @num2:=4;
SELECT @res:=@num1+@num2; #7

SELECT @num1+@num2 into @result;   #意思是将@num1+@num2的结果写入到新变量 @result中
SELECT @result #7

正常是先查询后排序

有用到变量的时候，会是先排序后查询

set @rownum:=0;
SELECT 
  first_name,
	gender,
	@rownum
FROM person
WHERE @rownum <=1
ORDER BY first_name,LEAST(0,@rownum:=@rownum+1);

3. inner join 的简写

select * from a inner join b on a.id = b.id
select * from a,b where a.id = b.id #实际上是a表b表 做了笛卡尔积，然后用 where 做筛选，留下符合条件的

练习：根据不同性别进行人员分组，然后根据分组按照年龄进行升序排序得到序号

1.第一步，先进行排序

SELECT
	first_name,
	gender,
	age
FROM person
ORDER BY gender,age ASC;

2.第二步，在进行加序号

SELECT
	first_name,
	gender,
	age,
	rank
FROM 
(SELECT
	first_name,
	gender,
	age,
	@rank:=if(@gen=gender,@rank+1,1) AS rank,
	@gen:=gender
FROM person,(SELECT @rank:=0,@gen:=NULL) temp
ORDER BY gender,age ASC) a;