SQL函数和存储过程

SQL函数和存储过程

自己在工作中总结的，暂时还比较少，后期补充。

函数：

1. 创建

   CREATE
       FUNCTION sp_name ([func_parameter[,...]])
       RETURNS type
   [characteristic ...] routine_body
   
   

   1. func_parameter存储过程的参数列表type-----函数返回的数据类型
      • 格式：
        • [ IN | OUT | INOUT ] param_name type
      • 介绍：
        • in----输入，out----输出，inout----可以输入也可以输出
        • param_name 参数名称
        • type 参数的类型（MySQL的任意类型）

2. 调用

   	select 函数名字（[函数的参数[,……]])
   

3. 删除

   DROP FUNCTION [IF EXISTS] sp_name
   

存储过程：

1. 创建：

   CREATE
       PROCEDURE sp_name ([proc_parameter[,...]])
       [characteristic ...] routine_body
   

   1. proc_parameter：存储过程的参数列表characteristic存储过程的特性
      • 格式：
        • [ IN | OUT | INOUT ] param_name type
      • 介绍：
        • in----输入，out----输出，inout----可以输入也可以输出
        • param_name：参数名称
        • type：参数的类型（MySQL的任意类型）

2. 调用

   call 存储过程名字（[存储过程的参数[,……]])
   

3. 删除

   DROP PROCEDURE [IF EXISTS] sp_name
   

小计

1. 变量声明

   DECLARE 变量名 数据类型;
   

2. 变量赋值

   SET 变量名= ;
   

流程控制语句

1. if语句

   IF search_condition THEN statement_list
       [ELSEIF search_condition THEN statement_list] ...
       [ELSE statement_list]
   END IF
   

2. case语句

   CASE case_value
       WHEN when_value THEN statement_list
       [WHEN when_value THEN statement_list] ...
       [ELSE statement_list]
   END CASE
   

3. while语句

   [begin_label:] WHILE search_condition DO
       statement_list
   END WHILE [end_label]
   

   • begin_label:表示标注名称
   • search_condition条件表达式

查看存储过程和函数

1. show status语句查看存储过程和函数的状态

   show {procedure|function}status[like’存储过程或存储函数的名称’]
   

2. show create语句查看存储过程和函数的定义

   show create{procedure|function}存储过程或存储函数的名称
   

3. 从information_schema.Routines表查看存储过程和函数的信息

   select * from information_schema.Routines
   where routine_name=’存储过程或存储函数的名称’;
   

Mysql常用函数

1. 字符串截取

   SUBSTRING_INDEX(str,delim,count)

   str:要做处理的字符串对象。
   delim:处理时用来做标识符的东西。
   count:怎么取值。
   count是正值：顺着数，标识符第count次出现时在它前面的所有的数据。
   count是负值：倒着数，标识符第count次出现时在它后面的所有的数据。

   select SUBSTRING_INDEX('30,30,30,123', ',', -1)
   -- 返回123
   select SUBSTRING_INDEX('30,30,30,123', ',', 1)
   -- 返回30
   

2. MySQL LPAD 函数

   LPAD - 填充字符串左边。

   LPAD(str,len,padstr)

   select lpad('7',4,'0')
   -- 返回0007
   

3. 复制表结构

   -- 复制表结构+索引
   create table table_name1 like table_name2;
   -- 只是复制表结构
   create table table_name1 as table_name2;
   

4. case 和group by同用

   -- 只能group by一项，若项目数多的话报错
   group by 
   CASE 
   	WHEN _result_type = '1' THEN 
   		 city_code 
   	ELSE 
   		 pk_project 
   END;
   

5. 若在存储过程中想要进行if判断，可使用下面的语句替代

   (IFNULL(_type,'') = '' or _type = type) 
   -- 相当于mapper.xml中的
   -- <if test="projectId != null  and projectId != ''  ">project_id = #{projectId}</if>
   

6. 根据中文首字母排序

   # 如果数据库表字段的字符编码是latin1_swedish_ci
   select * from tablename order by birary(name) asc ;
      #tablename：数据库表名
      #name：排序字段名
      #birary不是函数，是类型转换运算符，它用来强制它后面的字符串为一个二进制字符串，可以理解为在字符串比较的时候区分大小写
   
   # 如果数据库表字段的字符编码是utf8_general_ci（常用）
    select * from tablename WHERE 1=1 order by CONVERT( name USING gbk ) COLLATE gbk_chinese_ci ASC
   

7. 修改表或表结构常用sql语句

   alter table old_name rename new_name; --修改表名
   
   alter table test add  column add_name varchar(10); --添加表列
   
   alter table test drop  column del_name; --删除表列
   
   alter table test modify address char(10) --修改表列类型
   # alter table test change address address  char(40)
   
   alter table test change  column address address1 varchar(30)--修改表列名