(七)MySQL数据库高阶语句之函数、存储

---

一、数据库函数

1、数学函数

常用的数学函数如表所示

数学函数	描述
abs (x)	返回x的绝对值;绝对值就是永远是正数，0的绝对值是0
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 2…)	返回集合中最大的值
least (x1, x2…)	返回集合中最小的值

实例：

select abs(-2);		##返回-2的绝对值

select rand();		##0-1的随机数

select rand()*100;		##返回随机数

select mod(5,2);		##取余

select power(2,3);		##2的3次方

selct round (1.49)		##判断离1.49最近的整数

select round(1.5);		##判断离1.5最近的整数

select round(1.893,1);		##1.893保留小数点后1位；四舍五入

select sqrt(4);		##返回4的平方根

select sqrt(5);		##返回5的平方根

select truncate(1.896,2);		##保留小数点后2位，但truncate函数不会四舍五入

select ceil(5.2);		##大于等于5.2的整数

select floor(5.0)		##返回小于等于5.0的最大整数

select greatest(5,9,7);		##返回5.9.7的最大值

select least(5,9,7);		##返回5.9.7的最小值

2、聚合函数

可以对数据库内的记录求和、平均值、最大值、最小值的操作
常用的聚合函数如表所示

avg()	返回指定列的平均值
count()	返回指定列中非NULL值的个数
min()	返回指定列的最小值
max ()	返回指定列的最大值
sum(x)	返回指定列的所有值之和

实例

select sum(score) from www;		##返回分数的总和

select count(score) from www;		##进行分数的计数

select count(addr) from www;		##计数addr，null不会被计数

select avg(score)from www;		##计算成绩平均值

3、字符串函数

常用的字符串函数如表所示

length(x)	返回字符串x的长度
trim()	返回去除指定格式的值
concal (x,y)	将提供的参数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个空格.
replace(x, y, z)	将字符串z替代字符串x中的字符串y
strcmp(x, y)	比较x和y，小于返回-1，等于返回0，大于返回1，比较第一位不同的数字
substring (x,y,z)	获取从字符串x中的第y个位置开始长度为z的字符串
reverse (x)	将字符串x反转

实例：

select length('abcd');		##返回字符的长度

select trim('  lll');		##去除指定格式；头部的格式

select concat ('he','llo');		##将he和llo频次一个字符串

select concat ('he',trim(' llo'));	##还可以结合其他函数，比如trim

select upper('abC');		##将字符串x的所有字母变成大写格式

select lower('abC');		##所有字母变成小写

select right('hello',3);		##返回字符串hello的后3个字符

select concat(left('hello',2),right('hello',3));		##把字符串的前3个字母和后3个字母拼接起来

select repeat('ha',4);		##重复输出ha4次

select length(space(3));		##返回3个空格

select replace('hello','ll','aa');			##将hello中的ll替换成aa

select replace('lalhh','la','hh');

select strcmp(17,18);		##比较17,18，小于返回-1，等于返回0，大于返回1

select substring('adfgh4rf',3,4);  		##返回字符串第三个字符开始的4个字符

select reverse(left('123dfghj',3));		##返回字符串的前三个字符，然后反转输出

select left(reverse('123dfghj'),3);		##现将字符反转，再输出前三个字符

4、日期时间函数

字符串函数	描述
curdate ()	返回当前时间的年月日
curtime ()	返回当前时间的时分秒
now ()	返回当前时间的口期和时间
month (x)	返回日期x中的月份值
week (x)	返回日期x是年度第几个星期
hour (x)	返回x中的小时值
minute (x)	返回x中的分钟值
second(x)	返回x中的秒钟值
dayofweek (x)	返回x是星期几，1星期日，2星期一
replace(x, y, z)	将字符串z替代字符串x中的字符串y
dayofmonth (x)	计算日期x是本月的第几天
dayofycar (X)	计算日期x是本年的第几天,返回年月日

实例

select curdate();
select curtime();

select now();		##返回当前时间

select month('2021-07-10');		##返回月份

select week('2021-07-10');		##返回当前日期是一年中的第几周

select hour(curtime());		##返回当前时间的小时

select minute(curtime());		##返回当前的分钟

select second(curtime());		返回当前的秒

select dayofweek(curdate());		##返回本周的第几天，周日为第一天
select dayofmonth(curdate());		##当前日期是当月的几号
select dayofyear(curdate());		##当年日期是今年的第几天

二、存储过程

1、概述

一组为了完成指定功能的sql语句的集合，将这些sql语句集合存储到一个指定的名称，使用时再进行调用；在执行时比传统的sql速度更快、执行速度更快
实例

mysql> delimiter @@			##修改结束符为@@
mysql> create procedure u()		##创建存储过程，过程名为u，不带参数
    -> begin									##过程提以关键字begin开始
    -> select * from qq;					##过程体语句
    -> end @@								##过程体以关键字end结束
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;						##将语句的结束符号恢复为分号
mysql> call u();							##调用存储过程
+------+-------+
| id   | score |
+------+-------+
|    1 |   100 |
|    2 |    88 |
|    3 |    89 |
+------+-------+
3 rows in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

show create procedure u\G  ##查看存储过程

 show procedure status like 'u'\G			##查看指定存储过程；u不带%时就查找u一个，带上%会查找所有有u的内容

2、存储过程的参数

• IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
• OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
• INOUT 输入输出参数:既表示调用者向过程传入值，又表示过程向调用者传出值( 值只能是变量)

3、删除存储过程

存储过程内容的修改方法是：删除原有存储过程，再创建相同的名称的存储。

DROP PROCEDURE IF EXISTS Proc;

实例

drop procedure if exists u；