Mysql常见函数与存储过程
一、常见函数
类似于java的方法、将一组逻辑语句封装在方法体中、对外暴露方法名
1.影藏了实现细节
2.提高了代码的重用性
分类:
1.单行函数：如concat length ifnull 等
2.分组函数：功能:做统计使用、又称为统计函数、聚合函数、组函数
1.字符函数
#一、字符函数
1.length获取参数的字节个数
SELECT LENGTH('hello');
SELECT LENGTH('hello张三');
查看字符集
SHOW VARIABLES LIKE '%char%';
2.concat 拼接字符串
SELECT CONCAT(city_name,'_',country) 城市 FROM tuniu_code;
3.upper、lower
SELECT UPPER('hello'); -- 小写转大写
SELECT LOWER('HELLO'); -- 大写转小写
SELECT CONCAT(UPPER(city_name),'_',LOWER(country)) 城市 FROM tuniu_code;
SELECT * FROM tuniu_code WHERE city_name LIKE '%香港%'
4.substr
SELECT SUBSTR('袁华的一剪梅',3) out_put;
SELECT SUBSTR('袁华的一剪梅',1,2) out_put;
SELECT CONCAT(UPPER(SUBSTR(city_name,1,1)),'_',LOWER(SUBSTR(country,1,1))) 城市 FROM tuniu_code;
5.instr 返回子串第一次出现的索引 找不到为0
SELECT INSTR('夏洛特烦恼','特烦恼') AS out_put;
6.trim 去除前后空格或者指定字符
SELECT TRIM(' 秋雅 ');
SELECT TRIM('A' FROM 'AAAA秋雅AAAA');
7.lpad 指定长度的左填充
SELECT LPAD('刘德华',10,'*');
8.rpad 指定长度的右填充
SELECT RPAD('刘德华',10,'*');
9.replace 替换
SELECT REPLACE('秋雅爱上袁华','袁华','夏洛');
2.数学函数
# 二、数学函数
1.round 四舍五入
SELECT ROUND(4.5);
SELECT ROUND(4.589,2);
2.ceil 向上取整
SELECT CEIL(4.1);
3.floor 向下取整
SELECT FLOOR(4.1);
4.truncate 截断
SELECT TRUNCATE(1.69999,1);
5.mod 取余 mod(a,b) a-a/b*b
SELECT MOD(10,3);
3.日期函数
# 三、日期函数
1.now 返回系统当前时间
SELECT NOW();
2.curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
3.curtime 返回当前时间，不包含日期
SELECT CURTIME();
4.获取指定的部分 年、月、日、时、分、秒
SELECT YEAR(NOW());
SELECT YEAR('1998-08-16');
SELECT MONTH(NOW());
SELECT DAY(NOW());
5.str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-8-16','%Y-%c-%d') AS out_put;
6.date_format 将日期格式转换成字符
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d');
4.流程控制函数
4.1顺序结构:程序从上往下执行
1.if if eles 的效果
SELECT IF(10>5,'大','小');
4.2分支结构:程序从两条或多条路径中选择一条执行
2.case
使用一:
case 语法:
case 要判断的字符或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
....
else 要显示的值n或语句n;
end
使用二(类似于多重if):
case
when 条件1 then 要显示的值1或语句1;
when 条件2 then 要显示的值2或语句2;
....
else 要显示的值n或语句n;
end
第一种
SELECT country 国家名称 , country_id,
CASE country_id
WHEN 100 THEN country_id+0.5
WHEN 350 THEN country_id+0.6
ELSE country_id
END AS 拼接
FROM tuniu_code;
第二种
SELECT country 国家名称 , country_id,
CASE
WHEN country_id=100 THEN country_id+0.5
WHEN country_id=350 THEN country_id+0.5
ELSE country_id
END AS 拼接
FROM tuniu_code;
4.3循环结构:程序在满足一定的条件基础上、重复执行一段代码
3.循环
循环类型:while、loop、repeat
循环控制:
iterate :类似于continue,结束本次循环、进入下一次循环
leave:类似于break，跳出，结束当前所在循环
1. while
[标签名:] while 循环条件 do
循环体
end while [标签名];
2.loop
[标签名:] loop
循环体
end loop [标签];
3.repeat
[标签名:] repeat
循环体
until 结束循环的条件
end repeat [标签];
-- 插入100条
CREATE PROCEDUCE myp6(IN num INT)
BEGIN
DECALARE i INT DEFAULT 1;
a:WHILE i<num DO
循环体
INSERT INTO demo(username,password) VALUES('张三'+i,'123456');
SET i=i+1
END WHILE a;
END
#调用
CALL myp6(100);
5.其他函数
1. version 查看数据库版本号
SELECT VERSION();
2.database 查看数据库
SELECT DATABASE();
3.user 当前用户
SELECT USER();
6.分组函数
分组函数:
功能:用作统计使用，又称为聚合函数或者统计函数或组函数
分类:
sum 求和 、 avg 平均 、 max 最大值 、 min 最小值 、 count 计算个数
特点:
1.sum、avg 一般用于处理数值型
max、min、count 一般可以处理任何类型
2.以上分组函数都可以忽略掉null值
3.可以和distinct搭配实现去重的运算
4.count(*)用作统计行数、效率无两
5.和分组函数一同查询的字段要求是group by后的字段
二、存储过程
什么是存储过程?:
在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。
为什么要使用存储过程?:
(一)、响应时间上来说有 ：
优势：如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话，就只有一次。存储过程可以给我们带来运行效率提高的好处；
(二)、从安全上使用了存储过程的系统更加稳定：
程序容易出现BUG 不稳定，而存储过程，只要数据库不出现问题，基本上是不会出现什么问题的。
什么时候需要:
1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL 语句每执行一次就编译一次所以使用存储过程可提高数据库执行速度。
2.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete 时）可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。
3.存储过程可以重复使用
4.安全性高、可设定只有某此用户才具有对指定存储过程的使用权。
5.更强的适应性：由于存储过程对数据库的访问是通过存储过程来进行的，因此数据库开发人员可以在不改动存储过程接口的情况下对数据库进行任何改动，而这些改动不会对应用程序造成影响。
6.分布式工作：应用程序和数据库的编码工作可以分别独立进行，而不会相互压制。
含义:
一组预先编译好的SQL语句的集合、理解成批处理语句\一堆SQL 的合并。中间加了点逻辑控制。
1.提高代码重用性
2.简化操作
3.减少了编译次数、并且减少了和数据库服务器的连接次数、提高了效率
缺点:
1.可维护性较差
2.可读性较差
1.创建
一、创建
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体(一组合法的SQL语句)
END
注意事项:
1.参数列表包含三部分
参数模式 参数名 参数类型
IN tuname VARCHAR(20)
参数模式有三种:
IN : 该参数可以作为输入、也就是需要调用方传入值
OUT : 该参数可以作为输出、也就是该参数可以作为返回值
INOUT :该参数既可以输入值、也可以输出值、也就是该参数需要传入值、又可以返回值
2. 如果存储过程体仅仅只有一句话 BEGIN END可以省略
存储过程体中的每条SQL语句的结尾必须加分号
存储过程的结尾可以使用 DELIMITER 重新设置
语法:
DELIMITER 结束标记
例:
DELIMITER $
2.调用
二、调用
CALL 存储过程名(实参列表);
1.无参数的存储过程的创建
CREATE PROCEDURE `myp1`()
BEGIN
#Routine body goes here...
INSERT INTO demo(lng,lat,`name`) VALUES(1,2,'张三'),(3,4,'李四'),(5,6,'王五'),(7,8,'赵六'),(9,10,'老七');
END
2.创建一个带IN 模式参数的存储过程
CREATE PROCEDURE `myp2`(IN beName VARCHAR(20))
BEGIN
#Routine body goes here...
END
3.创建多个带IN模式参数的存储过程
CREATE PROCEDURE `myp3`(IN lngs VARCHAR(20),IN lats VARCHAR(20))
BEGIN
DECLARE result INT DEFAULT 0; #变量的声明并且初始化
SELECT COUNT(*) INTO result #赋值
FROM demo
WHERE lng = lngs
AND lat = lats;
SELECT IF(result>0,'存在','不存在'); #变量的使用
END
4.创建一个带OUT 模式参数的存储过程
CREATE PROCEDURE myp4(IN lngs VARCHAR(20),OUT nameData VARCHAR(20))
BEGIN
SELECT demo.name INTO nameData
FROM demo
WHERE lng = lngs
END
#调用
CALL myp4('1',@bName);#定义一个用户变量
SELECT @bName;
5.创建多个带OUT模式参数的存储过程
CREATE PROCEDURE myp4(IN lngs VARCHAR(20),OUT nameData VARCHAR(20),OUT lat INT)
BEGIN
SELECT demo.name,demo.lat INTO nameData,lat
FROM demo
WHERE lng = lngs
END
#调用
CALL myp4('1',@bName,@blat);#定义多个用户变量
SELECT @bName,@blat;
6.创建带INOUT模式参数的存储过程 传入a和b，使其值翻倍
CREATE PROCEDURE myp5(INOUT a INT,INOUT b INT)
BEGIN
SET a = a*2;
SET b = b*2;
END
#调用
SET @a=10;
SET @b=20;
CALL myp5(@a,@b);
SELECT @a,@b;
3.存储过程的删除
三、存储过程的删除
DROP PROCEDURE 储存过程名
4.存储过程的查看
四、存储过程的查看
SHOW CREATE PROCEDURE 储存过程名
三、自定义函数
含义:
一组预先编译好的SQL语句的集合、理解成批处理语句
1.提高代码重用性
2.简化操作
3.减少了编译次数、并且减少了和数据库服务器的连接次数、提高了效率
区别:
存储过程:可以有0个返回值、也可以有多个返回值。
适合做批量插入、批量更新、批量删除
函数:有且仅有1个返回值。
适合做处理数据后返回一个结果
1.创建函数
创建函数
CREATE FUNCTION 函数名(参数列表) RETURNS 返回值类型
BEGIN
函数体
END
注意:
参数列表包含两个部分
1.参数类型
2.参数名称
函数体:
3.必须要有return语句、如果没有会报错、return可以不放在最后、但是不建议。
4.需要使用delimiter语句去设置结束标记
2.调用函数
调用函数
SELECT 函数名称(参数列表)
#--------------------案例-----------------------
1.无参有返回
CREATE FUNCTION mypNum() RETURNS INT
BEGIN
DECLARE num DEFAULT 0; #定义变量
SELECT COUNT(*) INTO num #赋值
FROM demo;
RETURN num;
END
调用
SELECT mypNum();
3.查看函数
查看函数
SHOW CREATE FUNCTION 函数名称;
4.删除函数
删除函数
DROP FUNCTION 函数名称;
扫一扫
333
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
