先介绍下MySQL自带的一些函数:
结构:sql --> 结果 #作用
以下都是运行结果:
字符串相关函数
select concat('zhou','yi'); --> zhouyi #字符串拼接,可多参数
select concat_ws('&',zhou'','yi'); --> zhou&yi #指定分割符号进行字符串链接
# FOMRAT(N,D,locale); N对象,D小数位数,locale可选
select format(2019.011,1); --> 2019.0 #数字格式化
select lower('ZY'); --> zy # 转小写;upper 转大写
select replace('zhouyibaba','zhouyi',''); --> baba# 替换
SELECT left('chinese', 4); --> chin # 左截取; right(str, len)右截取;substring指定截取
select length('zhouyi'); --> 6 # 返回长度
select char_length("周毅"); --> 2 # 如果是length返回6
select ltrim(' space A'); --> space A #删除前空格; rtrim删除后空格;trim 删除前后空格
select trim(both '?'from '??zY?zy??'); --> zY?zy
select trim(leading '?'from '??zY?zy??'); --> zY?zy??
select trim(trailing '?'from '??zY?zy??'); --> ??zY?zy
判断子串:
select locate('ho','zhouyi');---> 2 #从字符串zhouyi中获取ho的开始位置,0代表无
日期时间函数
select now(); --> 2019-12-03 09:11:53 #当前日期和时间
select curdate(); --> 2019-12-03 # 当前日期; curtime()当前时间
SELECT DATE_ADD(now(),INTERVAL 2 DAY); --> 2019-12-05 09:19:48 #向日期添加指定的时间间隔 时间间隔有很多
select DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p'); --> Dec 03 2019 09:15 AM # 时间格式化
数值运算(用的叫少带过)
div() 整数除法 mod() 取余数 power() 幂运算 truncate() 数字截取 ceil() 进一取整 floor 舍1取整
以及基本的聚合函数:avg() count() max() min() sum()
1,自定义函数:
创建自定义函数语法格式如下:
CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
RETURNS <类型>
<函数主体>
语法说明如下:
<函数名>:指定自定义函数的名称。注意,自定义函数不能与存储过程具有相同的名称。
<参数><类型>:用于指定自定义函数的参数。这里的参数只有名称和类型,不能指定关键字 IN、OUT 和 INOUT。
RETURNS<类型>:用于声明自定义函数返回值的数据类型。其中,<类型>用于指定返回值的数据类型。
<函数主体>:自定义函数的主体部分,也称函数体。所有在存储过程中使用的 SQL 语句在自定义函数中同样适用,包括前面所介绍的局部变量、SET 语句、流程控制语句、游标等。除此之外,自定义函数体还必须包含一个 RETURN<值> 语句,其中<值>用于指定自定义函数的返回值。
即是:
CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
Routine_body
删除自定义函数语法如下:
DROP FUNCTION [ IF EXISTS ] <自定义函数名>
实际举例:
1,无参数举例:
mysql> create function getName()
-> returns varchar(45)
-> return
-> (select 'zhouyi');
Query OK, 0 rows affected (0.34 sec)
mysql> select getname();
+-----------+
| getname() |
+-----------+
| zhouyi |
+-----------+
1 row in set (0.11 sec)
mysql>
2,有参数举例:
mysql> CREATE FUNCTION twoAvg(num1 int,num2 int)
-> RETURNS FLOAT(10,2)
-> RETURN (num1+num2)/2;
Query OK, 0 rows affected (0.00 sec)
mysql> select twoAvg(2,3);
+-------------+
| twoAvg(2,3) |
+-------------+
| 2.50 |
+-------------+
1 row in set (0.12 sec)
mysql>
这里遗留问题。字符串的操作,尝试了好几遍都失败了。有空看到这一定要里,一定要补充完整。
更进一步,带复合结构的函数体。
注意:当使用 DELIMITER 命令时,应该避免使用反斜杠“\”字符,因为反斜杠是 MySQL 的转义字符。
定义语法:
DELIMITER //
CREATE FUNCTION function_name(parameter_nametype,[parameter_name type,...])
RETURNS {STRING|INTEGER|REAL}
BEGIN
//body
END
// /* 此处的”//“为告诉系统函数定义结束 */
当函数体内需要执行的是多条语句时,要使用BEGIN…END语句,复合结构可以包括声明、循环、控制结构(包括IF语句、CASE语句、LOOP语句、LEAVE语句、ITERATE语句、REPEAT语句和WHILE);且当编写函数体内容的时候,需要使用DELIMITER关键字将分隔符先修改为别的,否则编写语句的时候写到’;’的时候会直接执行,导致函数编写失败。
另外还需要了解自定函数内常量和变量定义:
用户变量:以”@”开始,形式为”@变量名”。用户变量跟mysql客户端是绑定的,设置的变量,只对当前用户使用的客户端生效
语法:
set @name = 值;
全局变量:定义时,以如下两种形式出现,set GLOBAL 变量名 或者 set @@global.变量名,对所有客户端生效。只有具有super权限才可以设置全局变量
语法:
set GLOBAL name = 值;
会话变量:只对连接的客户端有效。
局部变量:作用范围在begin到end语句块之间。在该语句块里设置的变量。declare语句专门用于定义局部变量。set语句是设置不同类型的变量,包括会话变量和全局变量
局部变量:
变量定义的语法:
DECLARE var_name[,varname]...date_type [DEFAULT VALUE];
为变量赋值的语法:
SET parameter_name = value[,parameter_name = value...]
SELECT INTO parameter_name
3,复杂一点的自定义函数。
- 自定义随机生成姓名的自定义函数。
mysql> #自定义开始结束符
mysql> DELIMITER $$
mysql> drop function if exists createRandomName$$
Query OK, 0 rows affected (0.00 sec)
mysql> create function createRandomName()
-> returns varchar(3)
-> begin
-> # 用来存可随机姓 名 形如LMN
-> declare ln varchar(500);
-> declare mn varchar(500);
-> declare nn varchar(500);
-> # 用来存随机ln mn nn的长度。char_length
-> declare ln_le int;
-> declare mn_le int;
-> declare nn_le int;
-> # 变量赋值
-> set ln='ABCDEFGH';
-> set mn='IJKLMN';
-> set nn='OPQRYZST';
-> set ln_le=char_length(ln);
-> set mn_le=char_length(mn);
-> set nn_le=char_length(nn);
-> return (concat(substring(ln,ceil(rand()*ln_le),1),
-> substring(mn,ceil(rand()*mn_le),1),substring(nn,ceil(rand()*nn_le),1)));
-> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> DELIMITER ;
mysql> select createRandomName();
+--------------------+
| createRandomName() |
+--------------------+
| EMR |
+--------------------+
1 row in set (0.00 sec)
mysql>
解释:
#自定义开始结束符
DELIMITER $$
drop function if exists createRandomName$$
create function createRandomName()
returns varchar(3)
begin
# 申明局部变量,用来存可随机姓 名 形如LMN
declare ln varchar(500);
declare mn varchar(500);
declare nn varchar(500);
# 用来存随机ln mn nn的长度。char_length
declare ln_le int;
declare mn_le int;
declare nn_le int;
# 变量赋值 ,这里由于本人创建数据库时,未指定UTF-8,不能使用。
set ln='ABCDEFGH';
set mn='IJKLMN';
set nn='OPQRYZST';
set ln_le=char_length(ln);
set mn_le=char_length(mn);
set nn_le=char_length(nn);
return (concat(substring(ln,ceil(rand()*ln_le),1),
substring(mn,ceil(rand()*mn_le),1),substring(nn,ceil(rand()*nn_le),1)));
end $$
DELIMITER ;
进一步深入就必须了解控制结构,因为这是学一个东西能实际运用的基础。
IF语句语法:
IF…END IF为一组
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
search_condition表示条件判断语句;statement_list表示条件成立执行的语句。
CASE语句语法:
CASE...END CASE为一组
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
case_value表示条件判断的变量;when_value表示变量的取值;
statement_list表示不同when_value值执行的语句。
loop语句语法:
[begin_label:] LOOP
statement_list
#LEAVE语句 主要用于跳出整个循环控制
LEAVE begin_label
END LOOP [end_label]
ITERATE语句语法:
ITERATE语句是跳出本次循环,然后直接进入下一次循环。
ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内
ITERATE label
begin_label和end_label分别表示循环开始和结束的标志,两个标志必须相同,而且都可以省略;
statement_list表示需要循环执行的语句。
while语句语法:
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
WHILE循环需要使用END WHILE来结束。
其中,search_condition参数表示循环执行的条件,满足该条件时循环执行;
repeat语句语法:
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
简单举个栗子:
判断员工优秀?
#自定义开始结束符
DELIMITER $$
drop function if exists getGreade$$
create function getGreade(grade int)
returns varchar(10)
begin
declare ret varchar(10);
CASE
WHEN grade=1 THEN SET ret='优秀';
ELSE SET ret='非常优秀';
END CASE ;
return ret;
end $$
DELIMITER ;
# 以;结束
单从逻辑而言,作为程序员的你理解不难。以上你了解,但是这种东西实际中会需要很多问题,主要时sql报错需要经验。没有相关经验你基本看不出来,而且百度也没有什么效果,网上学习课程教的又很浅,我学习这部分花了很大的时间去调通各种代码问题。且行且珍惜。
2,存储过程
为什么我写了自定义函数。其实我们可以发现这两者有很多相似之处。他就是一种为了方便快捷的一组具有特定功能的SQL语句集组成的可编程的、经编译创建并保存在数据库的函数。(**注:**不同的数据库,语法上存在的差异)。
上面自定函数都可以用到这里来。
存储过程的语法:
CREATE
#指定当前用户
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
( #上面的参数细化
proc_parameter:
[ IN | OUT | INOUT ] param_name type
#上面指定数据库引擎细化
characteristic:
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
#上面主体细化
routine_body:
Valid SQL routine statement )
[begin_label:] BEGIN
[statement_list]
END [end_label]
IN/OUT/INOUT 字面意思in只做输入不可改变,out只做输出可变,inout两者都可万能。
删除及查看存储过程:
#删除
drop procedure sp_name;
#查看
show procedure status;/create procedure sp_name;
前期数据表准备:
mysql> create table `grade`(
-> `id` int(11) not null auto_increment primary key,
-> `stu_number` varchar(11) not null,
-> `math` int(4) not null,
-> `chinese` int(4) not null,
-> `english` int(4) not null
-> )engine=innodb default charset=utf8 comment='学生成绩表';
Query OK, 0 rows affected (0.76 sec)
mysql> drop table if exists student;
Query OK, 0 rows affected, 1 warning (0.13 sec)
mysql> create table `student`(
-> `id` int(11) not null auto_increment primary key,
-> `stu_number` varchar(11) not null,
-> `stu_name` varchar(12) not null,
-> `class` int(4) not null
-> )engine=innodb default charset=utf8 comment='学生信息表';
Query OK, 0 rows affected (0.15 sec)
mysql> show tables; +-----------------------------+
| Tables_in_activiti_database |
+-----------------------------+
| grade |
| student |
+-----------------------------+
2 rows in set (0.00 sec)
// sql+数据预置
#学生信息表
drop table if exists student;
create table `student`(
`id` int(11) not null auto_increment primary key,
`stu_number` varchar(11) not null,
`stu_name` varchar(12) not null,
`class` int(4) not null
)engine=innodb default charset=utf8 comment='学生信息表';
#学生成绩表
drop table if exists grade;
create table `grade`(
`id` int(11) not null auto_increment primary key,
`stu_number` varchar(11) not null,
`math` int(4) not null,
`chinese` int(4) not null,
`english` int(4) not null,
`total` int(5) not null
)engine=innodb default charset=utf8 comment='学生成绩表';
insert into student(stu_number,stu_name,class) values
('10000','张一',101),
('10001','张二',101),
('10002','张三',101),
('10003','张四',101),
('10004','张二',102),
('10005','张三',102),
('10006','张四',102);
insert into grade(stu_number,math,chinese,english,total) values
('10000',80,90,101,271),
('10001',82,70,101,253),
('10002',80,60,101,241),
('10003',85,10,101,196),
('10004',80,88,101,269),
('10005',87,77,101,265),
('10006',83,90,101,274);
1,创建一个存储过程找到前全年级总分最高前三名。
drop procedure if exists getavgthree;
#不带参数
create procedure getavgthree()
begin
select s.stu_name,s.class from student s left join grade g on s.stu_number=g.stu_number order by total desc limit 3;
end;
2,找到学号最大的学生,将学号存储到输出参数。
create procedure getMaxSIDByClass(IN classname int, out maxid varchar(11))
BEGIN
select MAX(stu_number) into maxid from student where class=classname;
END;
当然除了上述查询,存储过程更删改查都是可以的,此外也可以使用存储过程备份还原数据(简单来说就是将一张表的数据复制到另一张表)