变量

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

  1. //查看所有的系统变量
  2. show global|session variables;
  4. //查看指定的某个系统变量的值
  5. select @@变量名;
  7. //为某个系统变量赋值
  8. set 系统变量名=值;
  1. #自定义变量
  2. //用户变量：针对于当前会话(连接有效)，同时会话变量的作用域
  4. //声明并且初始化
  5. set @用户变量名=值;或
  6. set @用户变量名:=值;
  8. //赋值
  9. set @用户变量名=值;或
  10. select 字段 into @变量名 from 表;
  12. eg:select count(*) into @count
  13.    from employees;
  15. //查看
  16. select @用户变量名;
  19. //局部变量：仅仅在定义它的begin end中有效;应用在begin end中的第一句话
  20. //声明
  21. declare 变量名 类型;
  22. declare 变量名 类型 default 值;
  24. //赋值
  25. set 局部变量名=值;或
  26. select 字段 into 局部变量名 from 表;
  28. //使用
  29. select 局部变量名;

存储过程

一组经过预先编译的sql语句的封装

  1. //创建语法
  2. create procedure 存储过程名(参数列表)
  3. [characteristics ...]
  4. begin
  5.     存储过程体(一组sql语句)
  6. end
  8. 参数列表包含：参数模式、参数名、参数类型
  9. 参数模式：
  10. in：该参数可作为输入，也就是该需要调用方法入值
  11. out：该参数可作为输出，也就是该参数可作为返回值
  12. inout：该参数可作为输入和输出
  14. //调用语法
  15. call 存储过程名(实参列表);
  17. 注：存储过程的每条sql语句的结尾要求必须加分号
  18.         存储过程的结尾可以用 Delimiter 重新设置。重新设置后，调用存储过程时结尾也要用重新设置的符号
  20. eg:Delimiter 结束标记
  22. //删除存储过程
  23. drop procedure  存储过程名;
  25. //查看存储过程
  26. show procedure status where db=数据库名;
  1. #创建带in模式参数的存储过程
  2. //创建存储过程实现 用户是否登录成功
  3. DELIMITER $
  4. CREATE procedure myp2(in username varchar(20),in password varchar(20))
  5. begin
  6.         declare result int default 0;
  7.         select count(*) into result
  8.         from admin
  9.         where admin.username = username 
  10.         and admin.password=password;
  11.         select if(result>0,'成功','失败') 结果;
  12. end $
  14. #创建带out模式参数的存储过程
  15. //创建存储过程 通过传入的员工id查出员工名字和所在部门名
  16. create procedure myp1(in empId int,out empname varchar(20),out depname varchar(20))
  17. begin
  18.         select e.last_name,d.department_name into empname,depname
  19.         from employees e INNER JOIN departments d
  20.         on e.department_id=d.department_id
  21.         where employee_id=empId;
  22. end;
  24. set @empname,@depname;        //定义两个变量来接收返回的值
  25. call myp1(101,@empname,@depname);                
  26. select @empname,@depname;
  29. #创建带inout模式参数的存储过程
  30. //传入a，b两个值，使其翻倍后输出
  31. create procedure myp3(inout a int,inout b int)
  32. begin
  33.             set a = a*2;
  34.             set b = b*2;
  35. end;
  37. set@a=2;
  38. set@b=3;
  39. call myp2(@a,@b);
  40. select @a,@b;

存储函数

一组预先编译好的sql语句集合

  1. #创建语法
  2. create function 函数名(参数名,参数类型....) 
  3. returns 返回类型
  4. [characteristics ...]
  5. begin
  6.         函数体        #函数体必须有RETURN 语句
  7. end;
  10. #调用语法
  11. select 函数名(参数列表)
  13. #查看函数
  14. show function status where db=数据库名;
  16. #删除函数
  17. drop function 函数名;
  1. //根据员工id查出工资
  2. create FUNCTION myf2(empid int) 
  3. returns DOUBLE
  4. begin
  5.         DECLARE m double DEFAULT 0.0;
  6.         select salary into M
  7.         from employees 
  8.         where employee_id =empid;
  9.         return M;
  10. end;
  12. select myf2(101);

区别

存储过程：可以有0个返回，也可以有多个返回，适合做批量插入、批量更新
函数：只有一个返回，适合做数据处理后返回一个结果

循环控制

放在begin end中使用

  1. #分类
  2. while、loop、repeat
  4. #循环控制
  5. iterate类似于 continue，结束本次循环继续下一次
  6. leave类似于 break 跳出当前所在循环
  1. #语法
  2. while 循环条件 do
  3.             循环体
  4. end while;
  1. #案例
  2. //向表中插入指定个数的随机字符串
  3. create table stringContent(
  4.         id int PRIMARY KEY auto_increment,
  5.         content varchar(20)
  6. );
  7. create procedure test_randstr_insert(in insertCount int)
  8. begin
  9.         DECLARE i int DEFAULT 1;
  10.         DECLARE str VARCHAR(26) DEFAULT 'abcdefghijklmnopqrstuvwxyz';
  11.         DECLARE startIndex int DEFAULT 1;#起始索引
  12.         DECLARE len int DEFAULT 1;#截取字符长度
  14.         while i < insertCount DO
  16.                 set len = FLOOR(RAND()*(20-startIndex+1)+1);
  17.                 set startIndex = FLOOR(RAND()*26+1);
  18.                 insert into stringContent(content) VALUES(SUBSTR(str,startIndex));
  19.                 set i=i+1;
  20.         end while;
  21. end;