目录
一、存储过程
1.1 什么是存储过程
存储过程是一组为了完成特定功能的SQL语句集合。
存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来,这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时,只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
1.2 存储过程的优点
1、执行一次后,会将生成的二进制代码驻留缓冲区,提高执行效率。
2、SQL语句加上控制语句的集合,灵活性高。
3、在服务器端存储,客户端调用时,降低网络负载。
4、可多次重复被调用,可随时修改,不影响客户端调用。
5、可完成所有的数据库操作,也可控制数据库的信息访问权限。
1.3 创建删除存储过程
##创建存储过程:
DELIMITER $$ #将语句的结束符号从分号;临时改为两个$$ (可以是自定义)
CREATE PROCEDURE Proc () #创建存储过程,过程名为Proc,不带参数
-> BEGIN #过程体以关键字BEGIN开始
-> select * from store_info; #过程体语句
-> END $$ #过程体以关键字END结束
DELIMITER; #将语句的结束符号恢复为分号
##调用存储过程##
CALL Proc;
##查看存储过程##
SHOW CREATE PROCEDURE [数据库.]存储过程名; #查看某个存储过程的具体信息
SHOW CREATE PROCEDURE Proc;
SHOW CREATE PROCEDURE Proc\G
SHOW PROCEDURE STATUS [LIKE '%Proc%'] \G
##删除存储过程##
#存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储过程。
DROP PROCEDURE IF EXISTS Proc;
#仅当存在时删除,不添加If EXISTS 时,如果指定的过程不存在,则产生一个错误。
不加参数的存储过程
带参数的存储过程
in输入参数:in 表示调用者向过程传入值(传入值可以是字面量或变量)
out输出参数:out表示过程向调用者传出值(可以返回多个值)(传出值之只能是变量)
inout输出/输出参数:inout,即表示调用者向过程传入值,又表示过程向调用者传入值(只能是变量)
删除存储过程
show procedure status like '%pro%'\G
#可以查看当前库中存储过程信息
drop procedure if exists pro;
#删除pro存储过程
show create procedure pro\G
#查看确认
二、函数
注意:函数名和括号之间不能有空格。
2.1 数学函数
abs(x) | 返回x的绝对值 |
rand() | 返回0到1的随机数 |
mod(x, y) | 返回x除以y以后的余数 |
power(x, y) | 返回x的y次方 |
round(x) | 返回离x最近的整数 |
round(x, y) | 保留x的y位小数四舍五入后的值 |
sqrt(x) | 返回x的平方根 |
truncate(x, y) | 返回数字x截断为y位小数的值 #不四舍五入 |
ceil(x) | 返回大于或等于x的最小整数 |
floor(x) | 返回小于或等于x的最大整数 |
greatest(x1,x2,...) | 返回集合中最大的值 |
least(x1,x2,...) | 返回集合中最小的值 |
示例:
SELECT abs(-1),rand(), mod(5,3) ,power(2,3);
#返回:1, 0-1之间的随机数,2,8
SELECT round(1.89),sqrt(2);
#返回:2,1.414
SELECT round(1.8937,3),truncate (1.235,2);
#返回:1.894,1.23
SELECT ceil(5.2) ,floor(2.1) ,least(1.89,3,6.1,2.1);
#返回:6,2,1.89
2.2 聚合函数
聚合函数 | 含义 |
---|---|
avg() | 返回指定列的平均值 |
count() | 返回指定列中非 NULL 值的个数 |
min() | 返回指定列的最小值 |
max() | 返回指定列的最大值 |
sum(x) | 返回指定列的所有值之和 |
注意:
- count(列名):只包括列名那一列的行数,在统计结果的时候,会忽略列值为 NULL 的行。
- count(*) :包括了所有的列的行数,在统计结果的时候,不会忽略列值为 NULL。
2.3 字符串函数
字符串函数 | 作用 |
---|---|
trim() | 返回去除指定格式的值 |
concat(x,y) | 将提供的参数 x 和 y 拼接成一个字符串 |
substr(x,y) | 获取从字符串 x 中的第 y 个位置开始的字符串,跟substring()函数作用相同 |
substr(x,y,z) | 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串 |
length(x) | 返回字符串 x 的长度 |
replace(x,y,z) | 将字符串 z 替代字符串 x 中的字符串 y |
upper(x) | 将字符串 x 的所有字母变成大写字母 |
lower(x) | 将字符串 x 的所有字母变成小写字母 |
left(x,y) | 返回字符串 x 的前 y 个字符 |
right(x,y) | 返回字符串 x 的后 y 个字符 |
repeat(x,y) | 将字符串 x 重复 y 次 |
space(x) | 返回 x 个空格 |
strcmp(x,y) | 比较 x 和 y,返回的值可以为-1,0,1 |
reverse(x) | 将字符串 x 反转 |
示例:
length(x) 返回字符串x的长度
返回abcd的长度,空格也算一个字符
select length('abcd');
select length('abc d');
trim() 返回去除格式的值
select trim(' sheng');
select ' sheng';
concat(x,y) 将提供的参数x和y拼接成一个字符串
select concat('abc', 'def');
select concat('abc',' def');
还可以结合其他函数,如trim(将后面的函数删除格式)
select concat('abc',trim('def'));
upper(x) 将字符串x的所有字母变成大写字母
select upper('abc');
lower(x) 将字符串x的所有字母变成小写字母
select lower('ABC');
left (x,y) 返回字符串x的前y个字符
select left('abcdefg',3);
right (x,y) 返回字符串x的后y个字符
select right ('abcdefg',3);
把字符串的前3个字母和后3个字母拼接起来
select concat (left('abcdefg' ,3),right ('abcdefg',3));
repeat(x,y) 将字符串x重复y 次
select repeat ('abc',2);
space(x) 返回x个空格
select length (space(3));
replace(x,y,z) 将字符串z替代字符串x中的字符串y
select replace('hello','ll','aa');
strcmp(x,y) 比较x和y,返回的值可以为-1,0,1
比较17和18,小于返回-1,等于返回0,大于返回1,只会返回这3个值,它是比较第一位不同的数字
select strcmp(17,18);
select strcmp(18,18);
select strcmp(19,18);
select strcmp(17,1);
substring(x,y,z) 获取从字符串x中的第y个位置开始长度为z的字符串,返回从字符串中第三个字符开始的4个字符。
select substring('abcdefg',3,4);
reverse (x) 将字符串x反转
select reverse('gfedcba');
返回字符串的前3个字符,然后反转输出
select reverse (left ('gfedcba',3));
先将字符串反转,再输出前3个字符
select left (reverse ('gfedcba'),3);
2.4 日期时间函数
字符串函数 | 描述 |
---|---|
curdate() | 返回当前时间的年月日 |
curt ime{) | 返回当前时间的时分秒 |
now() | 返回当前时间的日期和时间 |
month (x) | 返回日期x中的月份值 |
week (x) | 返回日期x是年度第几个星期 |
hour (x) | 返回x中的小时值 |
minute (x) | 返回x中的分钟值 |
second (x) | 返回x中的秒钟值 |
dayofweek(x) | 返回x是星期几,1星期日,2星期一 |
dayo fmonth (x) | 计算日期x是本月的第几天 |
dayofyear (x) | 计算日期x是本年的第几天 |