1. 变量
-
系统变量
- 全局变量
- 会话变量
-
自定义变量
- 用户变量
- 局部变量
-
下面例子中可能使用的数据表
-
employees 员工表
-
departments 部门表
-
jobs 工种表
-
locations 地点表
员工表分别通过部门id、工种id与部门表、工种表关联，部门表通过地点id与地点表关联。
-
1.1. 系统变量
-
系统变量：变量由系统提供，不是用户定义，属于服务器层面
-
使用的语法
-
查看所有的系统变量
show global variables;#查看全局变量 show 【session】 variables;#查看会话变量 -
查看满足条件的部分系统变量
show global | 【session】 variables like '%char%'; -
查看指定的某个系统变量的值
select @@global.系统变量名;#查看指定的全局变量 select @@【session.】系统变量名;#查看指定的会话变量 -
为某个系统变量赋值
set global | 【session】 系统变量名=值;或者
set @@global.系统变量名=值; set @@session.系统变量名=值;
-
-
全局变量的作用域：针对于所有的会话（连接）有效，但不能跨重启。服务器每次启动将为所有全局变量赋初始值。
-
会话变量的作用域：仅仅针对当前会话（连接）有效。
1.2. 自定义变量
- 自定义变量：变量是用户自己定义的，不是系统提供的
1.2.1 用户变量
-
作用域：针对于当前会话（连接）有效，同于会话变量的作用域。应用在任何地方，也就是begin end里面或begin end外面。
-
声明并初始化
-
set @用户变量名=值; -
set @用户变量名:=值; -
select @用户变量名:=值;
-
-
赋值（更新用户变量的值）
-
方式一
#通过set或select set @用户变量名=值; set @用户变量名:=值; select @用户变量名:=值; -
方式二
#通过select into select 字段 into @变量名 from 表;
-
-
查看用户变量的值
-
select @用户变量名;
-
1.2.2. 局部变量
-
作用域：仅仅在定义它的begin end中有效，应用在begin end中的第一句话。
-
声明
declare 变量名 类型; declare 变量名 类型 default 值： -
赋值
-
方式一
#通过set或select set 局部变量名=值; set 局部变量名:=值; select @局部变量名:=值; -
方式二
#通过select into select 字段 into 局部变量名 from 表;
-
-
查看局部变量的值
select 局部变量名;
2. 存储过程
- 存储过程是一组预先编译好的SQL语句的集合，理解为批处理语句。将实现某个功能的一段代码封装起来，只暴露名字，隐藏具体实现过程。
- 好处
- 提高代码的重用性
- 简化操作
- 减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率
2.1. 语法
-
创建
create procedure 存储过程名(参数列表) begin 存储过程体（一组合法的SQL语句） end注意：
-
参数列表包含三部分：参数模式 参数名 参数类型
- 参数模式：
- in：该参数可以作为输入，也就是该参数需要调用方传入值
- out：该参数可以作为输出，也就是该参数可以作为返回值
- inout：该参数既可以作为输入又可以作为输出，也就是该参数既可以传入值，也可以返回值
- 参数模式：
-
如果存储过程体仅仅只有一句话，begin end可以省略
-
存储过程体中的每条SQL语句的结尾要求必须加分号
-
存储过程的结尾可以使用delimiter重新设置
delimiter 结束标记
-
-
调用
call 存储过程名(实参列表);
2.2. 空参列表
-
示例
#创建存储过程 delimiter $ create procedure myp1() begin insert into admin(username,`password`) values('john','0000'),('lily','1111'); end $ #调用存储过程 call myp1()$ delimiter ; #将结束标记重新改为分号
2.3. 创建带in模式参数的存储过程
-
示例
#创建存储过程，根据员工名查询对应的部门信息，有一个in模式参数 delimiter $ create procedure myp2(in name varchar(20)) begin select d.* from departments d join employees e on d.department_id=e.department_id where e.last_name=name; end $ #调用存储过程 call myp2('K_ing')$ delimiter ; #将结束标记重新改为分号#创建存储过程，判断用户是否登录成功，有多个in模式参数 create procedure myp3(in username varchar(20),in password varchar(20)) begin declare result int default 0; #声明变量并初始化 select count(*) into result #对变量赋值 from admin a where a.username=username and a.password=password; select if(result>0,'登录成功','登录失败'); #查询结果 end $ #调用存储过程 call myp3('zhangsan','8888')$ delimiter ; #将结束标记重新改为分号
2.4. 创建带out模式参数的存储过程
-
示例
#创建存储过程，根据员工名返回对应的部门名，有一个out模式参数 delimiter $ create procedure myp4(in name varchar(20),out dep_name varchar(20)) begin select d.department_name into dep_name from departments d join employees e on d.department_id=e.department_id where e.last_name=name; end $ #调用存储过程 set @depname=''$ #定义并初始化用户变量 call myp4('Olson',@depname)$ #调用存储过程，并将返回值赋值给用户变量depname select @depname$ #查询输出结果 delimiter ; #将结束标记重新改为分号#创建存储过程，根据员工名返回对应的部门名和部门id，有多个out模式参数 delimiter $ create procedure myp5(in name varchar(20),out dep_name varchar(20),out dep_id int) begin select d.department_name,d.department_id into dep_name,dep_id from departments d join employees e on d.department_id=e.department_id where e.last_name=name; end $ #调用存储过程 call myp5('Olson',@depname,@depid)$ #也可以直接赋值，不需要先定义变量。调用存储过程，并将返回值分别赋值给用户变量depname和depid select @depname,@depid$ #查询输出结果 delimiter ; #将结束标记重新改为分号
2.5. 创建带inout模式参数的存储过程
-
示例
#传入a和b的值，最终a和b都翻倍并返回 delimiter $ create procedure myp6(inout a int,inout b int) begin set a=a*2; set b=b*2; end$ #调用存储过程 set @m=10$ set @n=20$ call myp6(@m,@n)$ select @m,@n$ #查询输出结果 delimiter ; #将结束标记重新改为分号
2.6. 删除存储过程
-
语法
drop procedure 存储过程名;
2.7. 查看存储过程信息
-
语法
show create procedure 存储过程名;