mysql高阶语句第三章

一、数据库函数

1、数学函数

数据库内存储的记录，经常会进行一系列的算术操作，所以mysql支持很多数学函数。

常用的数学函数：
数学函数         描述
abs(×)         输出×的绝对值
rand()         输出o到1的随机数
mod(x,y)       输出x除以y以后的余数
power(x,y)     输出x的y次方
round(×)       输出离x最近的整数
round(x,y)     保留x的y位小数四舍五入后的值
sqrt(x)        输出x的平方根
truncate(x,y)  输出数字x截断为y位小数的值
ceil(×)        输出大于或等于x的最小整数
floor(x)       输出小于或等于x的最大整数
greatest(x1,x2..)   输出集合中最大的值
least(x1,x2..)      输出集合中最小的值

2、聚合函数

MySQL数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。

聚合函数      描述
avg()        输出指定列的平均值
count()      输出指定列中非NULL值的个数
min()        输出指定列的最小值
max()        输出指定列的最大值
sum(x)       输出指定列的所有值之和返回分数的总和

示例：
select count(score) from info;   # 返回指定列中非NULL值的个数
select count(min) from info;     # 返回指定列的最小值
select count(max) from info;     #返回指定列的最大值

3、字符串函数

字符串函数        描述
length(×)       输出字符串x的长度
trim()          输出去除格式的值(去除括号内容左边格式)
concat(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(小于返回-1，等于返回0，大于返回1，只会返回这3个值，只比较第一位不同的数，位数不一样时空值为最小)
substring(x,y,z)   获取从字符串x中的第y个位置开始长度为z的字符串
reverse(x)         将字符串x反转

示例：
select concat('abc',trim('  xyz'));   #将xyz前面空格去除，再和abc拼接
select concat(left('abcxyz',2),right('abcxyz',2));  #将第一段和第二段前两个字符拼接输出
select length(space(3));         #返回三个空格并记录空格长度
select replace('hello','h','a'); #hello中的h被替换成a

4、日期时间函数

字符串函数      描述
curdate()     输出当前时间的年月日
curtime()     输出当前时间的时分秒
now()         输出当前时间的年月日和时分秒
month(x)      输出日期x中的月份值
week(x)       输出日期x是年度第几个星期
hour(×)       输出x 中的小时值
minute(x)     输出x中的分钟值
second(x)     输出x中的秒钟值
dayofweek(×)  输出x是星期几，1是星期日，2是星期一,以此类推
dayofmonth(x)计算日期x是本月的第几天
dayofyear(x) 计算日期x是本年的第几天

示例：
select week('2020-5-5');   #输出为2020的第几周
select hour(curtime());    #返回当前时间的小时
select dayofweek(curdate());   #当前周几
select dayofmonth(curdate());   #当前本月第几天
select dayofyear(curdate());   #当前当年第几天

二、存储过程⭐⭐⭐

1、概述

简单的sql语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条sql语句一起去处理才能够完成，这时候就可以使用存储过程，轻松而高效的去完成这个需求，有点类似shell脚本里的函数。

2、简介

(1)、存储过程是一组为了完成特定功能的sql语句集合。
(2)、存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。
存储过程在执行上比传统SQL速度更快、执行效率更高。

3、存储过程的优点:

(1）执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
(2) SQL语句加上控制语句的集合，灵活性高
(3）在服务器端存储，客户端调用时，降低网络负载
(4）可多次重复被调用，可随时修改，不影响客户端调用
(5）可完成所有的数据库操作，也可控制数据库的信息访问权限

**创建存储过程:**
delimiter $$   #将语句的结束符号从分号临时改为两个$$(自定义符号)
create procedure Proc() #创建存储过程,过程名为Proc，不带参数（自定义）
-> begin                    #过程体以关键字BEGIN开始
-> select * from store_Info;  #过程体语句
-> end $$         #过程体以关键字END结束
delimiter;        #将语句的结束符号恢复为分号

**调用存储过程：**
call proc();

**查看存储火过程：**
格式：show create procedure [数据库名.]存储过程名\G；#查看某个存储过程的具体信息
格式：show procedure status [like '%过程名%']\G  #查看所有存储过程
格式：show procedure status like '%过程名%'\G   #查看指定存储信息过程

**删除存储过程：**存储过程内容的修改方法是通过删除原有存储过程，然后再以相同的名称创建新的存储过程。
格式：drop procedure if exists 过程名;

存储过程的参数：
in 输入参数:   表示调用者向过程传入值（传入值可以是字面量或变量)
out 输出参数:  表示过程向调用者传出值(可以返回多个值)（传出值只能是变量)
inout 输入输出参数:  既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量)

示例：
创建：delimiter $$
create procedure proc (in inname varchar(20))
begin
select * from info where name=inname;
end $$
delimiter $$
调用：call proc('lisi')