一、变量及赋值
变量分类
- 局部变量
- 用户变量
- 会话变量
- 全局变量
1、局部变量
局部变量一般只在begin…end代码块中有效,作用域仅限于该语句块,在该语句执行完毕后,局部变量就消失了,局部变量的定义语法:
DECLARE var_name data_type [ DEFAULT value ];
说明 :declare
为定义变量的关键字,var_name
为自定义的变量名称,data_type
为变量的类型,整型就是int,字符串就是varchar或者char类型等等,default value
给变量赋默认值,可有可不有
给局部变量赋值有两种方式:
- set语句赋值
delimiter $$
CREATE PROCEDURE sp_demo01 () BEGIN
DECLARE
name01 VARCHAR ( 32 ) DEFAULT 'fai';
SELECT name01;
END $$
call sp_demo01();
测试结果:
- select…into…赋值
delimiter $$
CREATE PROCEDURE sp_demo02 () BEGIN
declare var_name varchar(32);
SELECT `name` into var_name from people where id =2;
select var_name;
END $$
含义:从people表中查出pid是2的数据,取出name的值,然后将name赋值给var_name
测试结果:
2、用户变量
用户变量作用于当前整个链接,如果当前连接断开后,定义的用户变量就会消失,用户变量使用前不需要申明,使用的时候使用@变量名
即可。变量赋值也有2种方式。
方式一:
set @age=18; 或者 set @age:=18
说明:申明了一个变量,并赋值为18,也可以给他赋值18.25,因为变量的类型没有严格的限制,随着值的变化而变化
方式二:
delimiter $$
CREATE PROCEDURE sp_demo02 () BEGIN
select @name01:= name from people where id =3;
select @name01;
END $$
call sp_demo02();//调用存储过程
说明:@name01
是变量名,name
是表中的字段名,当使用select语句对用户变量进行赋值时,只能使用”:=”方式
为什么说用户变量作用于当前整个链接呢?
当我们创建完整个存储过程并调用完后,可以单独执行select语句,查看当前的变量是否和存储过程时的变量是否一致
select @name01;
得到如下结果:
3、全局变量
全局变量在mysql启动的时候由服务器自动将它们初始化为默认值,这些默认值可以在mysql的配置文件修改,修改全局变量会影响到整个服务器。
全局变量查看
SHOW GLOBAL VARIABLES;
执行结果:
可以使用模糊查询某些需要的变量
show global variables like “%var%”;
4、会话变量
会话变量在每次建立一个新的连接的时候,由mysql来初始化,修改会话变量影响的是当前的数据库连接,不会影响其他的连接,查看会话变量
select @@var_name;
select @@session.var_name;
show session variables like "%var%";
如果要查看当前会话的全部会话变量,就去掉like后面的即可
设置会话变量方式:
set session var_name = value;
set @@session.var_name = value;
set var_name = value; #缺省session关键字默认认为是session
二、存储过程
一、创建存储过程
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
routine_body
[begin_label:] BEGIN
[statement_list]
……
END [end_label]
---------------------------------------------------
proc_parameter:
[ IN | OUT | INOUT ] param_name type
routine_body:
Valid SQL routine statement
说明:in表示输入参数,out表示输出参数,inout表示既可以输入,也可以输出,param_name表示参数名称;type表示参数的类型
示例一,没有参数的存储过程:
DROP PROCEDURE IF EXISTS sp_test;
DELIMITER //
CREATE PROCEDURE sp_test()
BEGIN
IF
age >= 20 THEN
SELECT
'成年人';
ELSE SELECT
'未成年人';
END IF;
END
DELIMITER ;
注意:sp_test()
表示没有参数的存储过程,“DELIMITER //” 语句的作用是将mysql的结束符设置为//,因为MYSQL默认的语句结束符为分号; ,存储过程中的SQL语句需要分号来结束,为了避免与存储过程中SQL语句结束符相冲突,需要使用DELIMITER 改变存储过程的结束符,并以"END //"结束存储过程。存储过程定义完毕之后再使用DELIMITER ;恢复默认结束符。
示例二,带有in参数的存储过程
CREATE PROCEDURE p_showage( IN age INT )
BEGIN
IF
age >= 20 THEN
SELECT
'成年人';
ELSE SELECT
'未成年人';
END IF;
END
表示在调用存储过程的时候,输入一个age变量,从而输出成年人或者未成年人
示例三:带有out参数的存储过程
delimiter //
CREATE PROCEDURE sp_add(a int, b int,out c int)
begin
set c=a+ b;
end//
call sp_add (1,2,@a);
select @a;
二、删除存储过程
DROP PROCEDURE IF EXISTS 存储过程名;
例如:
drop procedure if exists sp_test;
三、调用存储过程
call 存储过程名(参数列表);
调用存储过程时,存储过程名称后面必须加括号,哪怕该过程没有传递参数
存储过程一旦创建就不能修改,如果想要达到修改的效果,只能删除存储过程,然后重新创建
三、控制语句
SQL中的控制语句主要用于进行顺序、分支、循环等程序设计
控制语句 | 说明 |
begin…end | 程序块语句 |
if…else | 条件处理语句 |
case | 分支语句 |
while,repeat,loop | 循环语句 |
return | 无条件退出语句 |
break | 跳出循环语句 |
continue | 跳出本次循环语句 |
一、begin…end
- 主要作用是:语句块的界定
begin
语句块
end
注意:1、begin和end必须成对使用;2、通常与分支结构和循环结构一起使用;3、可以嵌套。
二、if…else
- 主要作用是:条件判断,执行if…then…else语句,如果出现多条件分支的情况下,需要加上elseif,即 if…then…elseif…then…else
declare @a int,@b varchar(100) --定义变量
set @a=3;--给变量赋值
set @b='jeson';--给变量赋值
if @a>4 then
select @a+1 as a
else
begin
select @b+'2' as b
select 900
end
将大于20的人显示成年人,否则显示未成年人
CREATE PROCEDURE p_showage ( IN age INT ) BEGIN
IF
age >= 20 THEN
SELECT
'成年人';
ELSE SELECT
'未成年人';
END IF;
END $$
显示结果:
一、控制语句
SQL中的控制语句主要用于进行顺序、分支、循环等程序设计
控制语句 | 说明 |
begin…end | 程序块语句 |
if…else | 条件处理语句 |
case | 分支语句 |
while,repeat,loop | 循环语句 |
return | 无条件退出语句 |
break | 跳出循环语句 |
continue | 跳出本次循环语句 |
一、begin…end
- 主要作用是:语句块的界定
begin
语句块
end
注意:1、begin和end必须成对使用;2、通常与分支结构和循环结构一起使用;3、可以嵌套。
二、if…else
- 主要作用是:条件判断,执行if…then…else语句,如果出现多条件分支的情况下,需要加上elseif,即 if…then…elseif…then…else
declare @a int,@b varchar(100) --定义变量
set @a=3;--给变量赋值
set @b='jeson';--给变量赋值
if @a>4 then
select @a+1 as a
else
begin
select @b+'2' as b
select 900
end
将大于20的人显示成年人,否则显示未成年人
CREATE PROCEDURE p_showage ( IN age INT ) BEGIN
IF
age >= 20 THEN
SELECT
'成年人';
ELSE SELECT
'未成年人';
END IF;
END $$
显示结果:
三、case表达式
- case的主要作用是选择语句分支,是一种简单的条件判断转换成一个函数,他会把满足条件的表达式转换为对应的结果
- case具有两种格式,简单case函数和复杂case函数
- case表达式必须以end结束
- 如果查找的字段是枚举值,则用简单函数表达式,如果查找的是字段是范围,则用复杂函数表达式
简单函数:
case sex when 1 then '男' when 2 then '女' when 3 then '人妖' else '其他' end
现在有一张表如下,要求将1转换成男,2转换成女,3转换成人妖,其他值转换成其他
sql语句:
SELECT * , case sex when 1 then '男' when 2 then '女' when 3 then '人妖' else '其他' end as 性别 from people;
转换后的结果
复杂函数:
case when sex=1 then '男' when sex=2 then '女' when sex=3 then '人妖' else '其他' end
现在有一张表如下:要求90分以上的转换成及格,80分以上的转换成良好,70分以上的转换成中等,60分以上的转换成及格,60分以下的转换成其他,并输出结果
sql语句:
SELECT
* ,
CASE
WHEN score > 90 THEN
'优秀'
WHEN score > 80 THEN
'良好'
WHEN score > 70 THEN
'中等'
WHEN score > 60 THEN
'及格' ELSE '差'
END
FROM
users;
运行后的结果为:
四、循环语句
1、while语句
现在有一张表,需要往表中添加10条数据,就需要用到循环语句来实现
创建存储过程:
delimiter $$
CREATE PROCEDURE insertData() BEGIN
DECLARE
maxid INT DEFAULT 0;
DECLARE
i INT DEFAULT 1;
WHILE
i <= 10 DO
SELECT
max( id ) INTO maxid
FROM
test1;
SET maxid = maxid + 1;
INSERT INTO test1
VALUES
( maxid, 'faith', 25 );
SET i = i + 1;
END WHILE;
END $$
调用存储过程:call insertData()
,调用完成后,查看数据库的数据:select * from test1;
2、repeat语句
语法:
repeat
内容
until 条件 //退出循环的条件
end repeat;
要求:将test1表中id为偶数的年龄加5岁
delimiter $$
CREATE PROCEDURE updateData () BEGIN
DECLARE
minid INT DEFAULT 1;
DECLARE
maxid INT DEFAULT 1;
SELECT
min( id ) INTO minid
FROM
test1;
SELECT
max( id ) INTO maxid
FROM
test1;
REPEAT
IF
minid % 2 = 0 THEN
UPDATE test1
SET age = age + 5
WHERE
id = minid;
END IF;
SET minid = minid + 1;
UNTIL minid > maxid
END REPEAT;
END $$
调用存储过程:call updateData()
,调用完成后,查看数据库的数据:select * from test1;
3、loop语句
loop名字:loop
内容
if 条件 then
leave loop名字;//结束循环
end if;
end loop;
将表中数据的年龄全部改为50
delimiter $$
CREATE PROCEDURE updateAge () BEGIN
DECLARE
minid INT DEFAULT 1;
DECLARE
maxid INT DEFAULT 1;
SELECT
min( id ) INTO minid
FROM
test1;
SELECT
max( id ) INTO maxid
FROM
test1;
myloop :
LOOP
IF
minid % 2 = 1 THEN
UPDATE test1
SET age = 50
WHERE
id = minid;
END IF;
SET minid = minid + 1;
IF
minid > maxid THEN
LEAVE myloop;
END IF;
END LOOP;
END $$
调用存储过程:call updateAge()
,调用完成后,查看数据库的数据:select * from test1;
五、break语句和Continue语句
break语句终止整个循环,continue语句结束本次循环,开始下次循环。