sql编程 && 存储过程

sql  结构化查询语言

     是一种编程语言   用于管理数据库的编程语言

    

元素：

    数据

     数据类型

        变量的数据类型  就是字段的数据类型 

    变量

    字段名就是变量  

    自定义变量

    

    为了区分用户变量和系统变量 需要在用户变量前增加 @ 标志

    set  变量名 = 变量值   set 是专门给变量赋值的语句

    set  @who = 'gwyy';     查看自定义变量  select @who;

    set  @a = (select count(*) from t1);   甚至给set赋值语句都可以是一条sql语句    

    select into 方法注入变量

    select 字段列表  表达式   into  变量列表

    select  19,29,39 into  @a,@b,@c;    把3个数字注入到3个变量里面

    select name from t1 where id = 1 into @d;   把查询结果注入到变量里面

    select  into var 要求只能返回一个记录

    select 方式给变量赋值

    select   @a := 'gwyy';   也可以这么写  set @a := 'gwyy';

    

    变量有效期

    会话结束就消失 连接结束

    作用域  用户定义的是全局的  函数内可用  作用于是重叠的  但是mysql也存在局部作用域变量 在函数内部定义 的

     函数

        内置函数 

数值函数

Abs(X)，绝对值abs(-10.9) = 10

Format(X，D)，格式化千分位数值 format(1234567.456, 2) = 1,234,567.46

Ceil(X),向上取整ceil(10.1) = 11

Floor(X),向下取整floor (10.1) = 10

Round(X),四舍五入去整

Mod(M,N) M%N M MOD N求余10%3=1

Pi(),获得圆周率

Pow(M,N) M^N

Sqrt(X)，算术平方根

Rand(),随机数  select floor(rand() *5 +5);

TRUNCATE(X,D) 截取D位小数

时间日期函数

Now(),current_timestamp(); 当前日期时间

Current_date();当前日期

current_time();当前时间

Date(‘yyyy-mm-dd HH;ii:ss’);获取日期部分

Time(‘yyyy-mm-dd HH;ii:ss’);获取时间部分

Date_format(‘yyyy-mm-dd HH;ii:ss’,’ %D %y %a %d %m %b %j');

Unix_timestamp();获得unix时间戳

From_unixtime();//从时间戳获得时间

字符串函数

LENGTH (string )  //string长度，字节

CHAR_LENGTH(string)   //string的字符个数

SUBSTRING (str, position [,length ])   //从str的position开始,取length个字符

REPLACE (str,search_str ,replace_str )   //在str中用replace_str替换search_str

INSTR (string ,substring )  //返回substring首次在string中出现的位置

CONCAT (string [,... ])  //连接字串

CHARSET(str)  //返回字串字符集

LCASE (string ) //转换成小写

LEFT (string ,length )  //从string2中的左边起取length个字符

LOAD_FILE (file_name)   //从文件读取内容

LOCATE (substring , string [,start_position] )   //同INSTR,但可指定开始位置

LPAD (string ,length ,pad )  //重复用pad加在string开头,直到字串长度为length lpad(1,3,0)  在1前面补3个0

LTRIM (string ) //去除前端空格

REPEAT (string ,count )  //重复count次

RPAD (string ,length ,pad)  //在str后用pad补充,直到长度为length

RTRIM (string )  //去除后端空格

STRCMP (string1 ,string2 )  //逐字符比较两字串大小

流程函数：

CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END   多分支

IF(expr1,expr2,expr3)  双分支。

 

聚合函数

Count()

Sum();

Max();

Min();

Avg();

Group_concat()

其他常用函数

Md5();

Default();

用户自定义函数

    函数名

    参数列表

    函数体

    返回值

语法：

新建：

Create function function_name (参数列表) returns 返回值类型

函数体

函数名，应该合法的标识符，并且不应该与已有的关键字冲突。

一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。

参数部分，由参数名和参数类型组成。

返回值类类型

函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。

多条语句应该使用 begin end语句块包含。

注意，一定要有return 返回值语句。

函数参数

参数 同样需要确定类型

可以有多个参数

函数内部定义局部变量

declare i  int default 0;

删除：

Drop function if exists function_name;

查看：

Show function status like ‘partten’

Show create function function_name;

修改：

Alter function function_name 函数选项。

DEMO

最简单的函数 输出 hello world

delimiter $$

  create  function sayhello() returns varchar(20)

    begin

        return 'hello world';

    end

$$

delimiter ;

稍微复杂的 算出现在是不是晚上

delimiter $$

  create  function fun1() returns varchar(20)

    begin

        --  hour 可以获得当前时间的小时部分

        if  hour(now()) >=18 then

          return   '晚安';

        else

        return  '早';

        end if;

    end

$$

delimiter ;

测试循环

delimiter $$

drop function if exists  fun2$$

  create  function fun2() returns varchar(20)

    begin

       set @i = 1;

        set @num = 0;

       w:while @i<=10 do

            if @i =5 then     -- 如果循环到了5就退出整个循环

            -- set @i = @i+1;

            leave  w;

            end if;

            set @i = @i+1;

            set @num = @num + @i;

        end while  w;

        return @num;

    end

$$

delimiter ;

带参数的

delimiter $$

drop function if exists sayhello$$

  create  function sayhello(name varchar(10)) returns varchar(20)

    begin

        return  concat( 'hello',name);

    end

$$

delimiter ;

学号函数

delimiter $$

drop function if exists sayhello$$

  create  function sno(c_id  int) returns char(20)

    begin

    declare s_no char(10);

    declare  class_name   char(10);

    select stu_no from  join_student where chass_id = c_id  into s_no

    if  isnull(s_no) then

  --  没有学生 返回001

        select c_name from join_class where id = c_id into class_name ;

        return  concat(class_name,'001');

    else 

        --  有的话 最大值+1

    return  concat(class_name, lpad(right(s_no,3) +1 ,3,'0'));

end if;

    end

$$

delimiter ;

控制流程

  分支

    if 条件1 then   

        条件1满足语句

    else if  条件2 then

        条件2满足的语句

    else 

        都不满足的语句

    End if;

    

循环

while  条件  do

循环体

循环的提前终止

break  相当于 mysql里面的  leave退出整个循环

continue  没有 但是有 iterate  退出当前循环

注意 不是根据 leave 和 iterate 的位置来跳出的 而是根据循环标签来跳出的 

给循环起个名字   ：

标签 ： while 

end while 标签

    

    

    运算符

        算数运算符  + - * /

        逻辑运算符    and  or  not   !

        关系运算符 > < >= <=  == != = ===

    注释

        行注释  #  --     块注释  /*  xxx  */

    结束符  

        默认是； 也可以是 g  也可以是 G   当然 ;和g是一样的  G是分组显示  g和G是命令行独有的

        除此之外 我们还可以用 delimiter 来修改语句结束符  delimiter  $$

存储过程 

存储过程 类似于一个函数  就是把一段sql 语句 封装成一个整体 当要使用的时候 可以调用这个存储过程来实现  

在封装的语句体里面 可以用 if  else  case while  等控制结构 

列也可以当成变量来看  所以 存储过程 可以当成一个程序来看     可以进行 sql编程

查看现有的存储过程

Show procedure  status;

删除存储过程

Drop procedure  存储过程名称；

写一个存储过程

Create  procedure p1()

Begin

    Select * from g;

End$

调用存储过程 

Call p1();

存储过程和函数的区别  一个名字不同 一个 没有return   其他都一样