SQL编程

1.变量

变量分为两种：系统变量和自定义变量

1.1.系统变量

系统定义好的变量：大部分的时候用户根本不需要使用系统变量，系统变量时用来控制服务器的表现的，如auto_commit.

  1. -- 查看系统变量
  2. show variable
  4. -- 查看具体变量值：任何一个有数据访问的内容都是由select查看
  5. select @@系统变量名;

修改系统变量：分为两种方式：会话级别，全局级别

  1. -- 会话级别：临时修改，仅在当前会话有效
  2. set 变量名 = 值;
  3. set @@变量名 = 值;
  5. -- 全局级别：一次修改，永久生效（对所有客户端都生效）
  6. set global 变量名 = 值;

1.2.自定义变量

用户自己定义的变量，系统为了区分系统变量。规定用户自定义变量必须使用一个@符号

  1. set @变量名 = 值;

在mysql中,”=”会被当成比较作用处理，为了区分，mysql重新定义了一个赋值符号： := mysql允许从数据库表中获取数据然后赋值给变量

  1. -- 方案1：边赋值边查看结果
  2. select @变量名 = 字段名 from 数据源;
  3. -- 方案2：只赋值不看结果，要求只能查询结果为1条数据。
  4. select 字段列表 from 数据源 into 变量列表

注意：所有自定义变量都是会话级别，当前客户端当次连接有效，所有的自定义变量不区分数据库（用户级别）。

需求：有两张表，一张订单表，一张商品表，每生成一个订单，意味着商品的库存要减少。

2.触发器

trigger，事先为某张表绑定好一段代码，当表中的某些内容发生改变的时候（增删改），系统会自动触发代码，执行。触发器：事件类型，触发事件，触发对象

  • 事件类型：增删改，三种类型 ：insert，delete，update
  • 触发时间：前后：before ，after
  • 触发对象：表中的每一条记录

一张表中不能具有相同触发时间触发类型的触发器：也就是一张表中最多有6种触发器

创建触发器：在mysql中：没有大括号，没有小括号，都是用对应的字符符号代替。

触发器基本语法：

  1. -- 临时修改语句结束符号
  2. delimiter 自定义符号 -- 后续遇到该符号才算结束
  3. create trigger 触发器名字 触发时间 触发类型 on 触发对象 for each row
  4. begin -- 代表大括号，开始
  5. -- 触发器的内容，
  6. end -- 代表大括号，结束
  7. -- 语句结束符
  8. 自定义符号;
  9. -- 将语句结束符修改回去
  10. delimiter ;

例子：

  1. delimiter $$
  2. create trigger after_order after insert on my_order for each row
  3. begin
  4. update my_goods set inv = inv - 1 where id = 2;
  5. end
  6. $$
  7. delimiter ;
  9. -- 如果是在编辑器中，不需要使用delimiter。我使用的是sql front。我的代码：
  10. create trigger after_order_add after insert on my_order for each row
  11. begin
  12. update my_goods set inv=inv-1 where id =1;
  13. end;
  15. --查看触发器 查看所有触发器或模糊匹配
  16. show triggers [like 'pattern']
  17. -- 查看触发器创建语句
  18. show create trigger 触发器名字
  19. show create trigger after_order
  21. -- 所有的触发器都会保存在一张系统表中 information_schema.triggers
  23. -- 修改触发器&删除触发器
  24. -- 触发器不能修改只能先删除再新增
  25. -- 删除触发器
  26. drop trigger 触发器名
  28. --触发器记录：
  29. -- 不管触发器是否触发了，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和即将执行之后的新的状态分别保留下来供触发器使用。其中要操作当前状态保存在old中，操作后的可能形态保存在new中，有点类似事务。
  31. -- 使用方式：
  32. old.字段名 / new.字段名(new 代表的是假设发生之后的结果)

3.分支结构

分支结构：实现准备多个代码块，按照条件选择性执行某段代码
在mysql中只有if分支
基本语法：

  1. if 条件判断 then
  2. -- 满足条件要执行的代码;
  3. else
  4. -- 不满足条件要执行的代码;
  5. end if;

4.循环结构

循环结构：某段代码在指定条件内重复执行。

  1. -- While循环（没有for循环）
  2. Where 条件判断 do
  3. -- 满足条件要执行的代码
  4. -- 变更循环条件
  5. End While;

循环控制：在循环内部进行循环判断和控制。Mysql中没有对应conrinue和break，但是有替代品。

  • Iterate：迭代，类似continue，后面的代码不执行，循环重新来过
  • Leave：离开，类似break整个循环结束
  1. --使用方式：
  2. Iterate/leave 循环名字;
  4. -- 定义循环名字
  5. 循环名字: while 条件 do
  6. -- 循环体
  7. -- 循环控制
  8. Leave/iterate 循环名字;
  9. End while；

5.函数

函数：将一段代码封装到一个结构中，在需要执行代码块的时候，调用结构执行即可（代码复用）。函数分为两类：系统函数和自定义函数

系统函数：

系统定义好的函数，直接调用即可，任何函数都有返回值，因此使用select调用。

字符串函数：

mysql中，字符串的基本操作单位（最常见的而是字节）

substring 字符串截取函数

char_length 字符串的字符长

length 字符串的字节长

instr 一个字符串中是否存在另一个字符串

lpad 左填充

insert 字符串替换

strcmp 字符串比较

自定义函数：

函数要素：函数名，参数列表（形参和实参），返回值，函数体（作用域）

  1. --创建函数：
  2. create function 函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型
  3. Begin
  4. -- 函数体
  5. -- 返回值：
  6. return 类型（指定数据类型）；
  7. end;
  9. --调用函数：
  10. select cs();
  12. --删除函数：
  13. drop function 函数名

函数形参：

参数分为两种：定义时的参数叫形参，调用时的参数叫实参（实参可以是数值也可以是变量）

形参：要求必须指定数据类型

Function 函数名（形参名字 字段类型） returns 数据类型

作用域：

Mysql中的作用域与js中的作用域完全一样，全局变量可以在任何地方使用，局部变量只能在函数体内使用。

全局变量：

使用set关键字定义，使用@符号标志

局部变量：

使用declare关键字声明，没有@符号。所有的局部变量声明，都必须在函数体开始之前。

6.存储过程

存储过程简称过程，procedure，是一种用来处理数据的方式，存储过程是一种没有返回值的函数。

  1. -- 创建过程
  2. create produce 过程名(参数列表)
  3. begin
  4. -- 过程体
  5. end;
  7. -- 查看过程 函数的查看方式完全适用于过程，关键字换成procedure
  8. --查看所有过程：
  9. show procedure status [like pattern]
  11. -- 查看过程创建语句：
  12. show create procedure 过程名
  14. --调用过程：
  15. call 过程名（参数列表）;
  17. --修改过程&删除过程：
  18. --删除过程：
  19. drop procedure 过程名;

过程参数：

函数的参数需要数据类型指定，过程比函数更严格。

过程还有自己的类型限定：三种类型

  • in：数据只是从外部传入给内部使用（值传递）：可以是数值也可以是变量
  • out：只允许过程内部使用（不用外部数据），给外部使用的.(引用传递：特殊之处，外部的数据会被先清空才才会进入内部操作)，只能是变量
  • inout：外部可以在内部使用，内部修改也可以给外部使用：典型的引用传递，只能传递变量

基本使用：

create procedure 过程名(in 参数名字 数据类型,…)

存储过程对于变量的操作是滞后的：是在存储过程调用结束后，才会重新将变量修改的

对应的sql：

  1. create table usr(
  2. name varchar(50) not null,
  3. age int not null
  4. );
  6. insert into usr values('zhangsan',20);
  7. insert into usr values('lisi',21);
  8. insert into usr values('wangwu',22);
  9. insert into usr values('zhaoliu',23);
  10. insert into usr values('shengqi',24);
  12. -- 查看所有系统变量
  13. SHOW VARIABLES;
  15. -- 查看系统变量值
  16. SELECT
  17. @@autocommit, @@version;
  19. -- session级修改系统变量
  20. SET `autocommit` = 0;
  21. SET @@autocommit = 0;
  23. -- 修改全局系统变量
  24. SET GLOBAL `autocommit` = 0;
  25. SET GLOBAL `autocommit` = 1;
  27. -- 定义自定义变量
  28. set @name = '张三';
  30. -- 为了区分=的比较作用，mysql可以这样赋值
  31. set @age := 18;
  33. -- 查看自定义变量
  34. select @name;
  35. select @age;
  37. -- 边赋值边看结果，赋值为了与比较区分只能使用:=,最终变量的值是最后一次执行语句时字段的值
  38. select @name := name ,name from usr;
  40. -- 只赋值不看结果，查询语句只能查询出一条结果
  41. select name ,age from usr where name = 'zhangsan' into @name ,@age;
  1. create table my_goods(
  2. id int primary key auto_increment,
  3. name varchar(20) not null,
  4. price decimal(10 , 2) default 1,
  5. inv int comment '库存数量'
  6. )charset utf8;
  8. insert into my_goods values(null , 'iPhone6s' , 5288 , 100);
  9. insert into my_goods values(null , 'vivo' , 3200 , 100);
  1. create table my_order(
  2. id int primary key auto_increment,
  3. g_id int not null comment '商品ID',
  4. g_number int comment '商品数量'
  5. )charset utf8;
  1. select * from my_goods;
  1. -- 创建触发器
  2. create trigger after_order_add
  3. after insert
  4. on my_order
  5. for each row
  6. begin
  7. update my_goods set inv=inv-1 where id =1;
  8. end;
  1. -- 测试触发器使用
  2. insert into my_order values(null , 1 , 2);
  3. select * from my_order;
  4. select * from my_goods;
  1. -- 查询触发器
  2. show triggers;
  3. show create trigger after_order_add;
  1. -- 查看触发器表
  2. select * from information_schema.triggers;
  1. -- 删除触发器
  2. drop trigger after_order_add;
  1. -- 解决刚才触发器的不准确问题
  2. create trigger after_order
  3. after insert
  4. on my_order
  5. for each row
  6. begin
  7. update my_goods set inv = inv-new.g_number where id = new.g_id;
  8. end;
  1. -- 测试触发器使用
  2. insert into my_order values(null , 1 , 2);
  3. select * from my_order;
  4. select * from my_goods;
  1. -- 这个时候会发现另一个问题，就是触发器如果删除得成负数，那是有问题的。
  2. -- 触发器结合if分支，判断商品库存是否足够，不够不能生成订单
  3. create trigger before_order
  4. before insert
  5. on my_order
  6. for each row
  7. begin
  8. select inv from my_goods where id = new.g_id into @inv;
  9. if
  10. @inv < new.g_number
  11. then
  12. -- 库存不够，触发器没有提供一个阻止语句执行的方法，所以报错暴力终止
  13. insert into xxx values(xxx);
  14. end if;
  15. end;
  1. insert into my_order values(null , 1 , 3);
  1. -- 定义两个变量
  2. set @cn = '世界你好';
  3. set @en = 'hello world';
  1. select substring(@cn,1,1),substring(@en,1,1); -- 世 h ，字符串下表从1开始，以字节为单位
  1. select char_length(@cn),char_length(@en),length(@cn),length(@en); -- 4 ,11 , 12 ,11 我的是utf-8编码
  1. select instr('hello','he'),instr('hello','s'); -- 1 , 0
  1. select lpad(@cn , 20, '欢迎'),lpad(@en , 20 , 'hello');
  1. select insert('hello world' , 3 , 3 ,'y'); -- key world
  1. set @fi = 'hello';
  2. set @se = 'key';
  3. set @th = 'KEY';
  4. SELECT strcmp(@fi , @se), strcmp(@se , @th), strcmp(@th , @fi); -- -1, 0 , 1 字符串不区分大小写在SQL中
  1. create trigger after_order after insert on my_order for each row
  2. begin  
  3. update my_goods  set  inv = (inv - new.g_number)  where  id = new.g_id; 
  4. end;
  6. insert into my_order values(null , 1 , 2);
  7. select * from my_goods;
  8. select * from my_order;
  10. -- 这个时候会发现另一个问题，就是触发器如果删除得成负数，那是有问题的。
  11. -- 触发器结合if分支，判断商品库存是否足够，不够不能生成订单
  13. create trigger before_order
  14.   before insert 
  15.   on my_order
  16.   for each row
  17. begin
  18.   select inv from my_goods where id = new.g_id into @inv;
  19.   if 
  20.     @inv < new.g_number 
  21.   then
  22.     -- 库存不够，触发器没有提供一个阻止语句执行的方法，所以报错暴力终止
  23.     insert into xxx values(xxx);
  24.   end if;
  25. end;
  27. insert into my_order values(null , 1 , 3);
  29. -- 系统函数
  30. -- 定义两个变量
  31. set @cn = '世界你好';
  32. set @en = 'hello world';
  34. select substring(@cn,1,1),substring(@en,1,1); -- 世 h ，字符串下表从1开始，以字节为单位
  36. select char_length(@cn),char_length(@en),length(@cn),length(@en); -- 4 ,11 , 12 ,11 我的是utf-8编码
  38. select instr('hello','he'),instr('hello','s'); -- 1 , 0
  40. select lpad(@cn , 20, '欢迎'),lpad(@en , 20 , 'hello');
  42. select insert('hello world' , 3 , 3 ,'y'); -- key world
  44. set @fi = 'hello';
  45. set @se = 'key';
  46. set @th = 'KEY';
  47. SELECT strcmp(@fi , @se), strcmp(@se , @th), strcmp(@th , @fi); -- -1, 0 , 1 字符串不区分大小写在SQL中
  49. -- 自定义函数
  50. create function cs() returns varchar(50)
  51. begin
  52.   return "hello world";
  53. end;
  54. -- 调用函数
  55. select cs();
  57. -- 查看函数 【like pattern】  -- 函数从属于数据库
  58. show function status like 'cs';
  60. -- 查看函数的创建语句
  61. show create function cs;
  63. -- 修改函数 & 删除函数
  64.  -- 函数只能先删除后新增，不能修改
  65.  drop function cs;
  68. -- 做函数，计算1~n的累加
  69. create function add1(n int) returns int
  70. begin
  71.   set @i = 1;
  72.   set @res = 0;
  73.   while @i <= n do
  74.     set @res = @res + @i;
  75.     set @i = @i + 1;
  76.   end while;
  77.   return @res;
  78. end;
  80. select add1(3);
  81. -- 在函数内部定义的变量在函数外部也可以访问
  82. select @res;
  84. -- 求和，1~n之间的数之和，5的倍数不加
  85. create function add2(n int) returns int
  86. begin
  87.   declare i int default 1;
  88.   declare res int default 0;
  89.   w1: while i <= n do
  90.     if i%5 = 0 then
  91.       set i = i + 1;
  92.       iterate w1;
  93.     else
  94.       set res = res + i ;
  95.       set i = i + 1;
  96.     end if;
  97.   end while;
  98.   return res;
  99. end;
  101. select add2(5);
  103. -- 创建存储过程
  104. create procedure p1()
  105. begin
  106.   select * from my_goods; -- 假设过程中需要显示数据，使用select
  107. end;
  109. -- 调用过程
  110. call p1();
  112. -- 查看过程
  113. show procedure status like 'p1';
  114. -- 查看过程创建语句
  115. show create procedure p1;
  117. -- 过程参数
  118. create procedure p2(in var1 int , out var2 int , inout var3 int)
  119. begin
  120.   select var1,var2,var3; -- var2的值一定是null，进入过程前会清除值
  121. end;
  123. -- call p2(1,2,3); in和inout参数只能传递变量而不能是值
  124. set @int_1 = 1;
  125. set @int_2 = 2;
  126. set @int_3 = 3;
  128. select @int_1,@int_2,@int_3; -- 1,2,3
  129. call p2(@int_1,@int_2,@int_3); -- 1, null, 3
  130. select @int_1,@int_2,@int_3; -- 1, null , 3  
  132. -- out 和inout属于引用数据类型：内部修改会影响外部
  133. -- 存储过程执行结束后，mysql将传入的引用变量修改后的值返回给实参。