编程之路之数据库mysql（八）- sql编程详解

一、什么是SQL编程

SQL编程可以编写存储函数、存储过程、触发器

MYSQL也提供很多内置好的函数

二、MYSQL的系统函数

1、数学函数

Max(),min(),sum(),avg(),count()

floor(),ceil(),round()

2、字符串函数

Length(),char_length()

Substring()

在substring时，字符串是从1开始计算下标的。最后一个参数是取的长度

 

3、日期时间函数

Now(),unix_timestamp()

Date(),time(),curdate(),curtime()

Date_add()

4、其它函数

Database(),user()

Md5(), password()

Last_insert_id()

重点：上述函数在mysql官方手册上都有

三、自定义函数（存储函数）

语法格式：

create function 函数名 (传递方式 参数1 数据类型1，传递方式 参数2 数据类型2，...) returns 参数类型

函数代码段

1、变量如何定义？

MYSQL的变量分为3种，

局部变量：函数内定义的变量，需要使用declare来定义，

格式：declare 变量名 数据类型 default 默认值

如： declare num int default 0;

在函数内操作变量仍然使用set，如set num=6;

全局变量：函数外用户定义的变量，需要使用@放到变量名前，

格式：set @num=5;

系统变量：MYSQL预先定义好的变量，使用时，在变量名前加@@

         如：set @@autocommit=0;

2、变量赋值运算符

在SQL编程中，使用=或:=进行变量的赋值

如：set num=5;

Set num:=5;

为什么会有:=作为赋值运算符？

原因就是=实际上是比较运算符，在某些情况下，会优先按照比较规则运算。因此就是想赋值的话，使用:=更准确。

3、流程控制语法

分支结构

If分支结构

类似switch的分支结构

循环结构

While循环结构

Loop循环结构

 

 

控制循环运行的关键字

Leave相当于PHP中的break，后面跟标签

Iterate相当于PHP中continue，后面跟标签

 

Repeat循环结构

4、参数的传递方式

在SQL语言中，参数传递分为3种方式

In：向函数或存储过程中传入参数，它是默认方式，可以省略不写。函数只能使用in方式

Out：从存储过程中向外传递的参数

Inout：参数可以向内也可以向外传递值，类似引用

5、定义函数，create

6、查看有哪些函数，show

格式：show function status like ‘条件’\G

7、查看函数创建信息，show create

格式：show create function 函数名

8、使用函数

和使用系统函数一样来使用

9、删除函数，drop

格式：drop function 函数名;

10、复杂函数时使用的语法

当函数中的SQL语句是多句时，需要

1. 加上begin和end作为SQL语句的范围，相当于PHP中的大括号
2. 还需要使用delimiter把语句结束符进行临时更换

再举一个阶乘的例子

四、存储过程，procedure

存储过程和函数相比，函数侧重于对数据进行处理和加工。

存储过程更侧重于把一组经常重复执行的SQL封装成一个存储过程名，只需要调用一个名称就可以了。简化了工作。

和函数一样，存储过程编写后，MYSQL会对它进行编译，保存到服务器上。执行效率更好。

语法格式：create procedure 存储过程名 (传递方式 参数1 数据类型, 传递方式 参数2 数据类型, ....)

         SQL语句段

说明：

存储过程没有返回值

参数是有传递方式的，in out inout

1、定义存储过程，create

如编写一个备份数据的存储过程

 

2、查看有哪些存储过程，show

格式：show procedure status\G

3、查看存储过程创建信息，show create

格式：show create procedure 存储过程名\G

4、使用存储过程，call

格式：call 存储过程名(参数列表)

5、删除存储过程，drop

格式：drop procedure 存储过程名;

再举例演示一下参数的传递方式

调用存储过程

对于inout和out的参数，必须使用变量，不能直接用值

查看out和inout变量的变化

五、触发器，trigger

通过特定的事件触发它执行的。触发器是监控数据表的行记录是否发生变化。

语法格式：create trigger 触发器名 触发时机 触发事件 on 表名 for each row

         SQL语句段

1、定义触发器，create

我们编写一个触发器，监控member表，当新插入会员记录时，如果它的钱数>2000，则将钱数设为2000。如果钱数小于50，则将钱数设为50

触发时机，有两个值

After：在某个事件之后

Before：在某个事件之前

触发事件，有3个值

Insert：向数据表中插入新数据时

Update：当数据表中的记录被修改时

Delete：当数据表中的记录被删除时

可以看出，一个表最多可以有6个触发器

Before insert

After insert

Before update

After update

Before delete

After delete

注：一个时机一个事件只允许编写一个触发器

 

触发器对于数据有old和new之分

Insert：没有old，只有new

Update：有old，也有new

Delete：有old，没有new

2、查看触发器，show

格式：show triggers like ‘条件’\G

3、查看触发器创建信息，show create

格式：show create trigger 触发器名\G

4、使用触发器

触发器是不能自己运行的，只能被动的由事件来触发。

经验证，触发器生效了，将超出范围的钱数限制了。

 

提示：

不能够编写after update触发器：它的SQL代码中再有update此条记录的情况

5、删除触发器，drop

格式：drop trigger 触发器名;

我是小咖

发个邀请：
如果你正好想学习php，可以与我一起交流，我的VX：feilueze333。下面资料免费赠送。