mysql函数分别系统函数和自定义函数_Mysql函数(内置函数，自定义函数)

简述

SQL：结构化查询语言，是一门编程语言，是用于管理数据库的编程语言。

元素：数据，数据类型，变量，函数，流程控制，运算符，注释。

注释：

行：

#

–[空格]

块：

/* */

select * from swpu_stu #where id=2;

;

select * from swpu_stu -- where id=2;

;

结束符：

select * from swpu_stu where id=2\g

select * from swpu_stu where id=2\G

可以使用delimiter来修改语句结束符，eg：delimiter $$。

变量：

字段名就是变量。

系统默认变量

show variables like ‘char%‘;

用户自定义变量：

如何定义一个变量？

set 变量名=变量值

注意：为了区分系统变量和字段与用户自定义变量，需要在用户变量前，[email protected]

通过select语句可以或得当前变量的值。

set @who="李国冬";

select @who;

定义一个变量select into

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

select 10,15,20 into @a,@b,@c;

select @a,@b,@c;

select stu_money from swpu_stu where id=1 into @stu_money;

select @stu_money;

注意：select into @var要求只能返回一行，如果返回多行，会语法错误，或者只将最后一行的

数据注入到变量内。

利用select语句的部分表达式达到为变量赋值的目的

使用:=的形式

select @who := ‘张国荣‘;

select @who;

注意：

=应该赋值，但是在select语句中，就成了关系等于，使用专门的赋值运算符:=。

:=同样适用于set

set @i := ‘周杰伦‘;

select @i;

使用变量是在表达式或者使用select查询到即可。

set @total = (select count(*) from swpu_stu);

select @total;

1、作用域。

用户定义的函数，是全局的(函数内可用)。

存在局部作用域变量，函数内定义的变量。

2、有效期。会话结束(连接结束)。

内置函数

数值：

rand()得到0-1之间的随机数

得到5至10

select 5+5*rand();

取整

select floor(5+5*rand());

格式化：format

select format(1234567.1234,2);

时间和日期：

now()当前时间

select unix_timestamp();

select from_unixtime(12345);

select from_unixtime(unix_timestamp());

字符串：

concat()字符串连接

length()

char_length()

select length("李国冬");

select char_length("李国冬");

select substring(‘中华人民共和国‘,2,2);

select substring(‘中华人民共和国‘,-2,2);

左边补足

lpad(需要补足的字符串，补足后的长度，补字符串);

select lpad(‘1‘,3,‘0‘);

其他：

md5

sha1

passwd

select md5(‘1‘);

select sha1(‘1‘);

select password(‘1‘);

自定义函数

要素：函数名，参数列表，函数体，返回值。

语法：

create function 函数名 (参数列表) 返回值类型

函数体

delimiter $$

create function sayHello() returns varchar(20)

begin

return ‘hello world!‘;

end

$$

delimiter ;

select sayHello();

注意：

函数与当前的数据库绑定的，在其他数据库使用该函数是不存在的，可以使用 数据库名.函数名 。

select testthings.sayHello();

sql中的流程控制—分支

if 条件1 then

条件1满足执行的语句

elseif 条件2 then

条件2满足执行的语句

...

else

上面的条件都不满足，执行的语句

end if;

else if和else都是可以省略的。

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

delimiter $$

create function func() returns varchar(20)

begin

if hour(now())>=11 then

return ‘晚‘;

else

return ‘早‘;

end if;

end

$$

delimiter ;

select func();

drop function func1;

delimiter $$

create function func1() returns varchar(20)

begin

if hour(now())>=11 then

return ‘晚‘;

elseif hour(now())>=9 then

return ‘中‘;

else

return ‘早‘;

end if;

end

$$

delimiter ;

select func1();

sql中的流程控制—循环

drop function func1;

delimiter $$

create function func1() returns varchar(20)

begin

set @i=1;

set @sum=0;

while @i<10 do

set @sum = @sum + @i;

set @i = @i+1;

end while;

return @sum;

end

$$

delimiter ;

select func1();

循环的提前终止：

leave 终止循环，类似于break

iterate 终止循环，类似于continue

注意：

不是根据leave和iterate所在的位置来决定终止那个循环，而是由循环的标签来决定的。

循环的标签，给循环取名字。

标签: while

drop function func1;

delimiter $$

create function func1() returns varchar(20)

begin

set @i=0;

set @sum=0;

w:while @i<10 do

set @i = @i+1;

if @i=5 then

iterate w;

end if;

set @sum = @sum + @i;

end while w;

return @sum;

end

$$

delimiter ;

select func1();

函数内部使用的变量：

@var的形式，相当于全局变量，函数内和函数外通用。

函数的参数：

参数同样需要确定类型(参数名 类型)

drop function sayHello;

delimiter $$

create function sayHello(user_name varchar(10)) returns varchar(20)

begin

return concat(‘hello, ‘,user_name);

end

$$

delimiter ;

select sayHello(‘李国冬‘);

一个函数可以有多个参数，使用逗号分隔。

函数声明的局部变量；

使用declare声明局部变量，需要指定类型，可以指定默认值default

drop function func1;

delimiter $$

create function func1() returns varchar(20)

begin

declare i int default 0;

declare total int default 0;

while i<10 do

set i = i+1;

set total = total + i;

end while;

return total;

end

$$

delimiter ;

select func1();

原始数据

create table class_join(

id int primary key auto_increment,

c_name char(7)

);

insert into class_join values(null,‘lol0011‘);

insert into class_join values(null,‘lol0022‘);

insert into class_join values(null,‘lol0033‘);

insert into class_join values(null,‘lol0044‘);

create table student_join(

stu_id int primary key auto_increment,

stu_no char(10),

class_id int not null,

stu_name varchar(10),

stu_info text

);

insert into student_join values(null,‘lol0033003‘,‘3‘,‘李小龙‘,‘info‘);

如何获取当前班级内最大的学号？。如果有，增加1；如果没有，从001开始。已知条件，班级id。

drop function sno;

delimiter $$

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

begin

declare s_no char(10);#保存当前班级最大的学号，如果没有就是null。

declare class_name char(7);

select stu_no from student_join where class_id = c_id order by stu_no desc limit 1 into s_no;

if isnull(s_no) then

-- 没有学生，从1开始，获得班级名字

select c_name from class_join where id=c_id into class_name;

return concat(class_name, ‘001‘);

else

-- 有，最大值加1

return concat(left(s_no,7),lpad(right(s_no,3)+1,3,‘0‘));

end if;

end

$$

delimiter ;

select sno(2);

随机产生名字

delimiter $$

create function sname() returns char(2)

begin

declare first_name char(16) default ‘赵钱孙李周吴郑王冯陈诸卫蒋沈韩杨‘;

declare last_name char(5) default ‘甲乙丙丁戊‘;

declare full_name char(2);

set full_name = concat(substring(first_name,floor(rand()*16+1),1),

substring(last_name,floor(rand()*5+1),1));

return full_name;

end

$$

delimiter ;

select sname();