SQL编程

变量

  • 系统变量
    • 会话变量 ，即对当前会话有效 查看所有会话show session variables;
    • 全局变量 ，即服务启动后，作用到任意连接。 查看所有全局show global variables;
      • 可以使用条件查询，如show session/global variables like '%char';
      • 查看具体的 select @@global/session.名; 全局还可以直接select @@.名 ;
      • 赋值：set global/session 名称=值 或者set @@global/session 名称=值
  • 自定义变量
    • 用户变量
    • 局部变量

varchar必须指定长度，int等可有可无

  1. #查询赋值
  2. select 字段 into 变量 ...
  3. #返回
  4. return 变量
  5. #定义变量   不要取index等容易冲突的名字,变量名尽量使用@?的形式，@开头的变量为会话变量
  6. declare 变量名  类型兼长度  [default 默认值]  变量创建后开始都是null
  7. 变量作用域仅限于自己所在的end内，end if等不清楚
  8. #设置值
  9. set 变量

流程

  • 不要在一个存储过程，函数中有多个return，即便他们逻辑上只执行一个！mysql只会执行最后一个return，不管条件是否满足
  • mysql中**0**和**null**为false，其他均为true
  • ```plsql if 条件 then 语句 [elseif 条件 then 语句] … [else 语句] end if

——————case———————-

CASE value WHEN value1 THEN 指令1 [WHEN value2 THEN 指令2 …] #如果value与value1相同，执行指令1，否则与value2对比，相同执行指令2，否则执行指令3 [ELSE 指令3] END CASE; when后除了写要比对的值，也可以写条件表达式

——————-循环———————————

WHILE x <= 5 DO SET str = CONCAT(str,x,’,’); SET x = x + 1; END WHILE;

————-do-while循环—————————

REPEAT SET str = CONCAT(str,x,’,’); SET x = x + 1; UNTIL x > 5 END REPEAT;

————-loop循环，反复执行一个代码块——————

SET x = 1; SET str = ‘’;

loop_label: LOOP IF x > 10 THEN LEAVE loop_label; END IF;

SET x = x + 1; IF (x mod 2) THEN ITERATE loop_label; ELSE SET str = CONCAT(str,x,’,’); END IF; END LOOP;
如果x的值大于10，则由于LEAVE语句，循环被终止。 如果x的值是一个奇数，ITERATE语句忽略它下面的所有内容，并开始一个新的迭代。 如果x的值是偶数，则ELSE语句中的块将使用偶数构建字符串

  1. #------------例子2----------
  2.       SET x = 1;
  3.        SET str =  '';
  4.        XX:WHILE x  <= 5 DO
  5.        SET  str = CONCAT(str,x,',');
  6.        SET  x = x + 1; 
  7.        if x=3 then 
  8.        LEAVE XX;
  9.        end if;
  10.        END WHILE;

————————循环控制语句———————

leave只能用于退出循环代码块，使用： leave 循环代码块的循环标签 等同于break iterate 相当于continue iterate还不清楚怎么在while和do-while中使用 循环标签即在代码块前加个别名和冒号 代码块是指一组标签内的全部代码

————————-游标（即循环的索引）—————-

DECLARE 游标变量名 CURSOR FOR 查询语句; #为查询语句的结果集创建一个游标 open 游标变量 #初始化游标的结果集 fetch 游标变量 检索光标的下一行，并移动到下一行（反正就是移动下一行） close 游标变量 释放游标内存 使用MySQL游标时，还必须声明一个NOT FOUND程序处理游标获取不到任何行时的情况

完整示例如下： BEGIN declare finished integer default 0; #终止变量 固定写法默认为0

concat中存在null则返回也是null

  1. declare res varchar(255) default "";  
  2. declare  field  varchar(255) default "";   #存储每一行的字段值
  3. declare indexs cursor for select caption from  class;  #定义游标/索引并关联结果集

定义not found程序，如果没有找到就设置结束变量为1

  1. declare continue handler for not found set finished=1;
  2. open indexs;  #初始化结果集
  4. forlock:loop
  5. fetch indexs into field;   #遍历当前行，把取得的值存到变量中，并下标后移，如果多个值，则创建游标时select返回多个字段，但是不能直接返回* 。赋值时   into @id,@name
  7. if finished=1 then   #找不到后就退出循环
  8. leave forlock;
  9. end if;
  10. set res=concat(field,res);
  11. end loop;
  12. RETURN res;

END

  1. <a name="ioaW5"></a>
  2. ## 内置函数
  4. - [链接](https://www.yiibai.com/mysql/functions.html)
  5. ```powershell
  6. abs(x)          -- 绝对值 abs(-10.9) = 10
  7. format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
  8. ceil(x)         -- 向上取整 ceil(10.1) = 11
  9. floor(x)        -- 向下取整 floor (10.1) = 10
  10. round(x)        -- 四舍五入去整
  11. mod(m, n)       -- m%n m mod n 求余 10%3=1
  12. pi()            -- 获得圆周率
  13. pow(m, n)       -- m^n
  14. sqrt(x)         -- 算术平方根
  15. rand()          -- 随机数
  16. truncate(x, d)  -- 截取d位小数
  1. -- 时间日期函数
  2. now(), current_timestamp();     -- 当前日期时间
  3. current_date();                 -- 当前日期
  4. current_time();                 -- 当前时间
  5. date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
  6. time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
  7. date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
  8. unix_timestamp();               -- 获得unix时间戳
  9. from_unixtime();                -- 从时间戳获得时间
  13. -- 字符串函数
  14. length(string)          -- string长度，字节
  15. char_length(string)     -- string的字符个数
  16. substring(str, position [,length])      -- 从str的position开始,取length个字符
  17. replace(str ,search_str ,replace_str)   -- 在str中用replace_str替换search_str
  18. instr(string ,substring)    -- 返回substring首次在string中出现的位置
  19. concat(var1,var2,...)   #字符串连接。会尝试将任意类型都转换为字符串，如果存在null转为"null"
  20. charset(str)            -- 返回字串字符集
  21. lcase(string)           -- 转换成小写
  22. left(string, length)    -- 从string2中的左边起取length个字符
  23. load_file(file_name)    -- 从文件读取内容
  24. locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
  25. lpad(string, length, pad)   -- 重复用pad加在string开头,直到字串长度为length
  26. ltrim(string)           -- 去除前端空格
  27. repeat(string, count)   -- 重复count次
  28. rpad(string, length, pad)   --在str后用pad补充,直到长度为length
  29. rtrim(string)           -- 去除后端空格
  30. strcmp(string1 ,string2)    -- 逐字符比较两字串大小
  31. -- 流程函数
  32. case when [condition] then result [when [condition] then result ...] [else result] end   多分支
  33. if(expr1,expr2,expr3)  双分支。
  34. -- 聚合函数
  35. count()
  36. sum();
  37. max();
  38. min();
  39. avg();
  40. group_concat()
  41. -- 其他常用函数
  42. md5();
  43. default();
  44. --// 存储函数，自定义函数 ----------
  45. -- 新建
  48. -- 删除
  49.     DROP FUNCTION [IF EXISTS] function_name;
  50. -- 查看
  51.     SHOW FUNCTION STATUS LIKE 'partten'
  52.     SHOW CREATE FUNCTION function_name;
  53. -- 修改
  54.     ALTER FUNCTION function_name 函数选项
  55. --// 存储过程，自定义功能 ----------
  56. -- 定义
  57. 存储存储过程 是一段代码（过程），存储在数据库中的sql组成。
  58. 一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。
  59. 而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。
  60. -- 创建
  61. CREATE PROCEDURE sp_name (参数列表)
  62.     过程体
  63. 参数列表：不同于函数的参数列表，需要指明参数类型
  64. IN，表示输入型
  65. OUT，表示输出型
  66. INOUT，表示混合型
  67. 注意，没有返回值。

触发器(trigger)

创建

  • create trigger 触发器名 触发时间 触发事件 on 监听的表 for each row 触发执行的语句
    • 触发时间：before/after 表示在事件前/后执行
    • 触发事件：可以是update/delete/insert
    • 监听的表：不能是视图或者临时表
    • 多个执行语句时使用begin...end 涉及更新和删除的执行语句不能使用普通的sql语法
  • 以上可以看出，有6中触发器，即触发时间与触发事件组合共6种
  • 可以使用old和new代替旧的和新的数据,如new.列名即可获取新数据的字段值
    • 更新操作，更新前是old，更新后是new.
    • 删除操作，只有old.
    • 增加操作，只有new.
  • 对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
  1. 1. 只要添加记录，就会触发程序。
  2. 2. Insert into on duplicate key update 语法会触发：
  3. 如果没有重复记录，会触发 before insert, after insert;
  4. 如果有重复记录并更新，会触发 before insert, before update, after update;
  5. 如果有重复记录但是没有发生更新，则触发 before insert, before update
  6. 3. Replace 语法 如果有记录，则执行 before insert, before delete, after delete, after insert

其他操作

  • drop trigger [schema_name.]触发器名

    事件

  • 触发器是被触发时执行某些操作，而事件是定时执行某些操作。自己要用时自己找

    • 个人觉得不追求性能不如使用springboot的定时器

      存储过程

  • 存储过程类似于函数，但是更偏向于业务功能。即不像函数那样更加通用

  • 缺点：

    • 存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。（难以移植）
    • 存储过程的性能调校与撰写，受限于各种数据库系统。

      使用

  • 声明并定义存储过程create procedure 存储过程名(参数) begin ... end

    • begin end中为存储过程体，begin-end中的语句必须以分号结尾。
      • begin-end可以添加标签，增加可读性或者指代,如
      • begin-end可以嵌套
    • 参数括号形式：([in/out/input ] 参数名 数据类形兼长度...])
      • in相当于入参，可以是变量或者常量
      • out返回值，只能是变量
      • input即既有入参又有返回值
      • 输出入参select 参数名 设置入参set @参数名
  • 调用call 存储过程名(参数);
    ```plsql delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义) CREATE PROCEDURE delete_matches(IN p_playerno INTEGER) BEGIN 　 END$$ delimiter;

    —————————调用——————

    call delete_matches(57);

    即相当于给delete语句传了个参数

——————-begin嵌套与标签——————

label1: BEGIN 　　label2: BEGIN 　　　　label3: BEGIN 　　　　　　statements; 　　　　END label3 ; 　　END label2; END label1 ```

更多操作

错误与异常处理

函数

  • create funnction 函数名(入参 数据类型兼长度) returns 返回类型兼长度 函数主体
    • 如果报This function has none of DETERMINISTIC 在函数主体前加上DETERMINISTIC表示
  • 调用select 函数([参数]);
  • 函数必须有返回值