mysql函数及存储+视图

1、数据库函数

■ MySQL提供了实现各种功能的函数
■ 常用的函数分类
● 数学函数
● 聚合函数
● 字符串函数
● 日期时间函数

1.1、数学函数

■ 常用的数学函数

1.1.1、 abs(x)返回x的绝对值

mysql> select abs(3);
mysql> select abs(-2);

1.1.2、 rand() 返回0到1的随机数

mysql> select rand();

1.1.3、 mod(x,y) 返回x除以y以后的余数

mysql> select mod(1,2);
mysql> select mod(4,2);

1.1.4、 power(x,y)返回x的y次方

mysql> select power(1,2);
mysql> select power(2,3);

1.1.5、 round(x)返回离x最近的整数

mysql> select round(3.4);
mysql> select round(3.5);
mysql> select round(3.6);

1.1.6、 round(x,y)保留x的y位小数四舍五入后的值

mysql> select round(3.654,2);
mysql> select round(3.656,2);

1.1.7、 sqrt(x)返回x的平方根

mysql> select sqrt(16);
mysql> select sqrt(64);

1.1.8、 truncate(x,y) 返回数字x截断为y位小数的值

mysql> select truncate(4.762,2);
mysql> select truncate(4.769,2);

1.1.9、 ceil(x) 返回大于或等于x的最小整数

mysql> select ceil(3.0001);
mysql> select ceil(2.908);

1.1.10、 floor(x)返回小于或等 于x的最大整数

mysql> select floor(3.541);
mysql> select floor(4.128);

1.1.11、 greatest(x1,x2…) 返回集合中最大的值

mysql> select greatest(5,10,50);
mysql> select greatest(18,66,88);

1.1.12、 least(x1,x2…)返回集合中最小的值

mysql> select least(17,22,45);
mysql> select least(99,6,32);

1.2、聚合函数

■ 对表中数据记录进行集中概括而设计的一类函数
■ 常用的聚合函数

1.2.1、avg()返回指定列的平均值

mysql> select * from cj1;
mysql> select avg(score) from cj1;

1.2.2、count()返回指定列中非NULL值的个数

mysql> select * from cj1;
mysql> select count(NULL) from cj1;
mysql> select count(score) from cj1;

1.2.3、 min()返回指定列的最小值

mysql> select min(score) from cj1;

1.2.4、max()返回指定列的最大值

mysql> select max(score) from cj1;

1.2.5、sum()返回指定列的所有值之和

mysql> select  sum(score) from cj1;

1.3、字符串函数

■ 常用的字符串函数

1.3.1、 length(x) 返回字符串x的长度

mysql> select length('abc');
mysql> select length('abcde');

1.3.2、trim()返回去除指定格式的值

mysql> select trim('abcd');

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

mysql> select concat('abc','def');

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

mysql> select upper('Goodnight');

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

mysql> select lower('HELLO');

1.3.6、 left(x,y)返回字符串x的前y个字符

mysql> select left('flowers',2);

1.3.7、 right(x,y) 返回字符串x的后y个字符

mysql> select right('flowers',2);

1.3.8、 repeat(x,y)将字符串x重复y次

mysql> select repeat('night',2);

1.3.9、 space(x) 返回x个空格

mysql> select space(5);
mysql> select space(20);

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

mysql> select replace('goodnight','o','a');

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

mysql> select strcmp(2,3);
mysql> select strcmp(2,2);
mysql> select strcmp(3,2);

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

mysql> select substring('abceagting',3,4);

1.3.13、reverse(x) 将字符串x反转

mysql> select reverse('night');

1.4、日期时间函数

■ 常用的日期时间函数

1.4.1、curdate() 返回当前时间的年月日

mysql> select curdate();

1.4.2、curtime() 返回当前时间的时分秒

mysql> select curtime();

1.4.3、now() 返回当前时间的日期和时间

mysql> select now();

1.4.4、month(x) 返回日期x中的月份值

mysql> select month('2020-12-27');

1.4.5、week(x)返回日期x是年度第几个星期

mysql> mysql> select week('2020-12-27');

1.4.6、hour(x)返回x中的小时值

mysql> select hour('01:05:26');

1.4.7、minute(x) 返回x中的分钟值

mysql> select minute('01:05:26');

1.4.8、second(x) 返回x中的秒钟值

mysql> select second('01:05:26');

1.4.9、dayofweek(x) 返回x是星期几，1星期日，2星期一

mysql> select dayofweek('2020-12-27');

1.4.10、dayofmonth(x) 计算日期x是本月的第几天

mysql> select dayofmonth('2020-12-27');

1.4.11、dayofyear(x) 计算日期x是本年的第几天

mysql> select dayofyear('2020-12-27');

2、存储过程简介

■ 是一组为了完成特定功能的SQL语句集合
■ 比传统SQL速度更快、执行效率更高

2.1、 存储过程的优点

■ 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
■ SQL语句加上控制语句的集合，灵活性高
■ 在服务器端存储，客户端调用时，降低网络负载
■ 可多次重复被调用，可随时修改，不影响客户端调用
■ 可完成所有的数据库操作，也可控制数据库的信息访问权限
■ 要创建存储过程，必须要具有CREATE ROUTINE权限
■ 存储过程被创建后，可以多次重复调用，它将多条SQL封装到了一起，可随时针对SQL语句进行修改，不影响调用它的客户端

2.2、创建存储过程

■ 使用CREATE PROCEDURE语句创建存储过程
■ 创建存储过程的语法结构

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

■ 参数分为
● 输入参数: IN
● 输出参数: OUT
● 输入/输出参数: INOUT
■ 存储过程的主体部分，被称为过程体
■ 以BEGIN开始，以END结束，若只有一-条SQL语句,则可以省略BEGIN-END
■ 以DELIMITER开始和结束

mysql> DELIMITER $$                   //$$是用户自定义的结束符
//省略存储过程其他步骤
mysql> DELIMITER ;      //分号前有空格

2.2.1、不带参数的存储过程

mysql> use aa;
Database changed
mysql> delimiter $$                     #定义结束符
mysql> create procedure cr()     #定义存储过程名字
    -> begin
    -> create table c1(name varchar(64),score int(3));
    -> insert into c1 values('zhangsan',90),('lisi',60);
    -> select * from c1;
    -> end $$                                  
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call cr();

2.2.2、带参数的存储过程

mysql> use aa;
Database changed
mysql> delimiter &&
mysql> create procedure getscore(IN a varchar(64))
    -> begin
    -> select * from c1 where name=a;
    -> end &&
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call getscore('lisi');

2.3、修改存储过程

■ 存储过程的修改分为特征修改和内容修改
■ 特征修改的方法

ALTER PROCEDURE <过程名> [ <特征> ... ]

■ 内容修改可先删除原有存储过程，之后再创建的方法

2.4、删除存储过程

■ 删除存储过程的语法

DROP { PROCEDURE | FUNCTION}[ IF EXISTS ] <过程名>   #防止因删除不存在的存储过程而引发的错误

■ 删除的具体用法

mysql> DROP PROCEDURE PlayerRole;          #删除之前确认有无依赖关系
Query OK, 0 rows affected (0.0C sec)
mysql> CALL PlayerRole;
ERROR 1305 (42000): PROCEDURE test.PlayerRole does not exist

2.4.1、删除

mysql> drop procedure cr;
Query OK, 0 rows affected (0.01 sec)

mysql> call cr();
ERROR 1305 (42000): PROCEDURE aa.cr does not exist
mysql> 

2.5、存储过程in、out、inout参数实例与总结

mysql> use aa;
mysql> set @num1=1, @num2=2, @num3=3;
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure p(in num1 int, out num2 int, inout num3 int)
    -> begin
    -> select num1,num2,num3;
    -> set num1=10,num2=20,num3=30;
    -> select num1,num2,num3;
    -> end $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call p(@num1,@num2,@num3);
mysql> select @num1,@num2,@num3;   

总结1: in和inout参数会将全局变量的值传入存储过程中，而out参数不会将全局变量的值传入存储过程中。在存储过程使用中，参数值in、 out、 inout都会发生改变。
总结2:调用完存储过程后，发现in参数不会对全局变量的值引起变化，而out和inout参数调用完存储过程后，会对全局变量的值产生变化，会将存储过程引用后的值赋值给全局变量。
in参数赋值类型可以是变量还有定值，而out和inout参数赋值类型必须是变量。

3、视图

mysql> create table sushe(id int(10),num int(3));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into sushe values(1,101),(2,102),(3,103),(4,104),(5,105);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create view stu as select cj.name,sushe.num from cj inner join sushe where cj.xuehao=sushe.id;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from stu;