MySQL高级SQL语句（三）

目录

一、数据库函数

    1、数学函数

     2、聚合函数

     3、字符串函数

     4、日期时间函数

 二、存储过程

     1、概述

      2、简介

      3、存储过程的优点

      4、语法

      5、示例(不带参数的创建)

---

一、数据库函数

    1、数学函数

       数据库内存储的记录，经常要进行一系列的算术操作，所以 MySQL 支持很多数学函数。 常用的数学函数如表所示

数学函数	描述
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…)	返回集合中最小的值

       1-1、-2的绝对值 返回 x 的绝对值

        1-2、0-1的随机数（0<=x<1） 返回 0 到 1 的随机数

         1-3、5除以2的余数   返回 x 除以 y 以后的余数

          1-4、 2的3次方

           1-5、离1.49、1.89最近的整数

            1-6、1.893保留小数点后2位，1.896保留小数点后2位，这里会四舍五入

             1-7、返回平方根

              1-8、保留小数点后2位，但truncate函数不会四舍五入（截断）

               1-9、返回大于或等于5.2的最小整数 

、            1-10、返回小于或等于5.2的最大整数

                1-11、返回最大值

                 1-12、 返回最小值

     2、聚合函数

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

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

        2-1、返回分数的总和

        2-2、返回分数字段的个数

        2-3、 返回分数的最小值

        2-4、 返回分数的最大值

        2-5、 返回分数的平均值

     3、字符串函数

字符串函数	描述
length(x)	返回字符串 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
substring(x,y,z)	获取从字符串 x 中的第 y 个位置开始长 度为 z 的字符串
reverse(x)	将字符串 x 反转

        3-1、length(x) 返回字符串 x 的长度

        3-2、trim()    返回去除格式的值

        3-3、concat(x,y) 将提供的参数 x 和 y 拼接成一个字符串

        3-4、upper(x) 将字符串 x 的所有字母变成大写字母

        3-5、lower(x) 将字符串 x 的所有字母变成小写字母

        3-6、 left(x,y) 返回字符串 x 的前 y 个字符

        3-7、repeat(x,y) 将字符串 x 重复 y 次

        3-8、 space(x) 返回 x 个空格

        3-9、replace(x,y,z) 将字符串 z 替代字符串 x 中的字符串 y

        3-10、strcmp(x,y) 比较 x 和 y，返回的值可以为-1,0,1

        3-11、substring(x,y,z) 获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串

        3-12、reverse(x) 将字符串 x 反转

     4、日期时间函数

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

        4-1、返回年月日

        4-2、返回当前时间

        4-3、返回当前完整时间

        4-4、返回月份

        4-5、返回当前日期是一年中的第几周

        4-6、 返回当前时间的小时

        4-7、 返回当前时间的分钟

        4-8、 返回当前时间的秒

        4-9、 当前是星期几

        4-10、当前日期是本月的第几天

        4-11、 当前日期是今年的第几天

 二、存储过程

      1、概述

         前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求，有点类似shell脚本里的函数

       2、简介

1、存储过程是一组为了完成特定功能的SQL语句集合。
2、存储过程这个功能是从5.0版本才开始支持的，它可以加快数据库的处理速度，增强数据库在实际应用中的灵活性。存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。操作数据库的传统 SQL 语句在执行时需要先编译，然后再去执行，跟存储过程一对比，明显存储过程在执行上速度更快，效率更高

存储过程在数据库中创建并保存，它不仅仅是 SQL 语句的集合，还可以加入一些特殊的控制结构，也可以控制数据的访问方式。存储过程的应用范围很广，例如封装特定的功能、 在不同的应用程序或平台上执行相同的函数等等。

       3、存储过程的优点

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

       4、语法

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
<过程名>：尽量避免与内置的函数或字段重名
<过程体>：语句
[ IN | OUT | INOUT ] <参数名><类型>

       5、示例(不带参数的创建)

          5-1、创建存储过程

mysql> delimiter $$
mysql> create procedure proc ()
    -> begin
    -> create table mk (id int (10),name char(10),score int(10));
    -> insert into mk values (1,'wang',13);
    -> select * from mk;
    -> end $$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

          5-2、调用存储过程

mysql> call proc();
+------+------+-------+
| id   | name | score |
+------+------+-------+
|    1 | wang |    13 |
+------+------+-------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

1、    存储过程的主体都分,被称为过程体
2、    以BEGIN开始，以END结束，若只有一条sQL语句，则可以省略BEGIN-END
3、    以DELIMITER开始和结束
mysgl>DEL工M工TER $$      $$是用户自定义的结束符 
省略存储过程其他步骤
mysql>DELIMITER ;  分号前有空格

          5-3、查看存储过程

格式：
SHOW CREATE PROCEDURE [数据库.]存储过程名;        #查看某个存储过程的具体信息

          5-4、查看指定存储过程信息

#查看存储过程
SHOW PROCEDURE STATUS 

mysql> show procedure status like '%proc%'\G

          5-5、存储过程的参数

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

即表示调用者向过程传入值，又表示过程向调用者传出值（只能是变量)

举例：

mysql> delimiter @@
mysql> create procedure proc2 (in inname varchar(50)) #行参，建立以名字为筛选条件的存储过程
    -> begin
    -> select * from info where name=inname;                                  
    -> end @@                                                                 
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call proc2('wangwu');               #实参，调用出wangwu的存储信息
+----+--------+-------+------------+-------+
| id | name   | score | address    | hobby |
+----+--------+-------+------------+-------+
|  2 | wangwu | 60.00 | shengzheng | 2     |
+----+--------+-------+------------+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

          5-6、修改存储过程

ALTER PROCEDURE <过程名>[<特征>... ]
ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
MODIFIES sQLDATA:表明子程序包含写数据的语句
SECURITY:安全等级
invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行。

          5-7、删除存储过程

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

DROP PROCEDURE IF EXISTS Proc;