MySQL 数据库函数提供了能够实现各种功能的方法,使我们在查询记录时能够更高效的输出。MySQL 内建了很多函数,常用的包括数学函数、聚合函数、字符串函数和日期时间函数。
数据库内存储的记录,经常要进行一系列的算术操作,所以 MySQL 支持很多数学函数。常用的数学函数如表 所示。
表 MySQL 数学函数
mysql> select abs(-3),(3.23),(0);
+---------+------+---+
| abs(-3) | 3.23 | 0 |
+---------+------+---+
| 3 | 3.23 | 0 |
+---------+------+---+
1 row in set (0.00 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.5349133028973949 |
+--------------------+
1 row in set (0.01 sec)
mysql> select rand();
+--------------------+
| rand() |
+--------------------+
| 0.7889129741014103 |
+--------------------+
1 row in set (0.00 sec)
mysql> select mod(23,3);
+-----------+
| mod(23,3) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
mysql> select mod(24,3);
+-----------+
| mod(24,3) |
+-----------+
| 0 |
+-----------+
1 row in set (0.00 sec)
mysql> select power(3,2);
+------------+
| power(3,2) |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)
mysql> select power(3,0);
+------------+
| power(3,0) |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
mysql> select power(3,-2);
+--------------------+
| power(3,-2) |
+--------------------+
| 0.1111111111111111 |
+--------------------+
1 row in set (0.00 sec)
mysql> select round(4.356,2); '保留两位 四舍五入看第三位'
+----------------+
| round(4.356,2) |
+----------------+
| 4.36 |
+----------------+
1 row in set (0.00 sec)
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
mysql> select sqrt(3);
+--------------------+
| sqrt(3) |
+--------------------+
| 1.7320508075688772 |
+--------------------+
1 row in set (0.00 sec)
mysql> select sqrt(-3);
+----------+
| sqrt(-3) |
+----------+
| NULL |
+----------+
1 row in set (0.00 sec)
mysql> select truncate(3.1415926,3);
+-----------------------+
| truncate(3.1415926,3) |
+-----------------------+
| 3.141 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select truncate(3.1415926,0);
+-----------------------+
| truncate(3.1415926,0) |
+-----------------------+
| 3 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select truncate(3.1415926,-1);
+------------------------+
| truncate(3.1415926,-1) |
+------------------------+
| 0 |
+------------------------+
1 row in set (0.00 sec)
Ceil(x)返回大于或等于x的最小整数
Ceil 向上取整
mysql> select ceil(1.4);
+-----------+
| ceil(1.4) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
mysql> select ceil(-1);
+----------+
| ceil(-1) |
+----------+
| -1 |
+----------+
1 row in set (0.00 sec)
mysql> select ceil(-2.1);
+------------+
| ceil(-2.1) |
+------------+
| -2 |
+------------+
1 row in set (0.00 sec)
Floor 向下取整
mysql> select floor(1.09);
+-------------+
| floor(1.09) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select floor(-2.1);
+-------------+
| floor(-2.1) |
+-------------+
| -3 |
+-------------+
1 row in set (0.00 sec)
mysql> select greatest (10,20,30);
+---------------------+
| greatest (10,20,30) |
+---------------------+
| 30 |
+---------------------+
1 row in set (0.00 sec)
mysql> select least(10,20,30);
+-----------------+
| least(10,20,30) |
+-----------------+
| 10 |
+-----------------+
1 row in set (0.00 sec)
MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的,这些函数被称作聚合函数。常见的聚合函数如表 5-8 所示。
表 MySQL 聚合函数
例如,MySQL 聚合函数的使用方法,具体操作如下所示。
聚合函数中最常用到的是 count()函数,用于统计表中的总记录数。、 统计年龄大于23的人数
mysql> select count(name) from zhu where age>23;
+-------------+
| count(name) |
+-------------+
| 5 |
+-------------+
1 row in set (0.00 sec)
查询表中最小年纪的
mysql> select min(age) from zhu;
+----------+
| min(age) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
mysql> select max(age) from zhu;
+----------+
| max(age) |
+----------+
| 28 |
+----------+
1 row in set (0.00 sec)
mysql> select avg(age) from zhu;
+----------+
| avg(age) |
+----------+
| 24.2857 |
+----------+
1 row in set (0.00 sec)
mysql> select sum(age) from zhu; ±---------+ | sum(age) | ±---------+ | 170 | ±---------+ 1 row in set (0.00 sec)
常用函数不仅包括数学函数和聚合函数,还包含字符串函数,MySQL 为字符串的相关操作设计了丰富的字符串函数。常用的字符串函数如表 5-9 所示。
表 MySQL 字符串函数
MySQL 字符串函数的使用方法,具体操作如下所示。
length(x)返回字符串x的长度 中文占用三个字节长度
mysql> select length('ab c');
+----------------+
| length('ab c') |
+----------------+
| 4 |
+----------------+
1 row in set (0.00 sec)
mysql> select length('尚');
+---------------+
| length('尚') |
+---------------+
| 3 |
+---------------+
1 row in set (0.00 sec)
mysql> select trim( 'addf' );
+-------------------+
| trim( 'addf' ) |
+-------------------+
| addf |
+-------------------+
1 row in set (0.00 sec)
mysql> select trim( 'ad df' );
+------------------------+
| trim( 'ad df' ) |
+------------------------+
| ad df |
+------------------------+
1 row in set (0.00 sec)
mysql> select concat('abc',' def');
+----------------------+
| concat('abc',' def') |
+----------------------+
| abc def |
+----------------------+
1 row in set (0.00 sec)
mysql> select concat('abc',trim(' bdqn'));
+-----------------------------+
| concat('abc',trim(' bdqn')) |
+-----------------------------+
| abcbdqn |
+-----------------------------+
mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)
mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)
mysql> select right('abcdefg',3);
+--------------------+
| right('abcdefg',3) |
+--------------------+
| efg |
+--------------------+
1 row in set (0.00 sec)
mysql> select left('abcdefg',3);
+-------------------+
| left('abcdefg',3) |
+-------------------+
| abc |
+-------------------+
1 row in set (0.00 sec)
MySQL 也支持日期时间处理,提供了很多处理日期和时间的函数。一些常用的日期时间函数如表 5-10 所示。
表 日期时间函数
例如,MySQL 日期时间函数的使用方法,具体操作如下所示。
Database changed
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-08-30 |
+------------+
1 row in set (0.00 sec)
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 08:43:21 |
+-----------+
1 row in set (0.00 sec)
)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-08-30 08:44:58 |
+---------------------+
1 row in set (0.01 sec)
mysql> select month('2020-08-25');
+---------------------+
| month('2020-08-25') |
+---------------------+
| 8 |
+---------------------+
1 row in set (0.00 sec)
mysql> select week('2020-08-25') ;
+--------------------+
| week('2020-08-25') |
+--------------------+
| 34 |
+--------------------+
1 row in set (0.00 sec)
mysql> select hour(curtime()) ;
+-----------------+
| hour(curtime()) |
+-----------------+
| 8 |
+-----------------+
1 row in set (0.00 sec)
mysql> select hour(now()) ;
+-------------+
| hour(now()) |
+-------------+
| 8 |
+-------------+
1 row in set (0.00 sec)
mysql> select minute(now()) ;
+---------------+
| minute(now()) |
+---------------+
| 49 |
+---------------+
1 row in set (0.00 sec)
mysql> select second(now()) ;
+---------------+
| second(now()) |
+---------------+
| 23 |
+---------------+
1 row in set (0.00 sec)
mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 1 |
+------------------+
1 row in set (0.00 sec)
mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
| 243 |
+------------------+
1 row in set (0.00 sec)
mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
| 30 |
+-------------------+
1 row in set (0.00 sec)
因为 MySQL 函数的数量比较多,存在很多使用频率不是很高的函数,所以本章仅列举了一些具有代表性、比较常用的函数。在实际的工作中,需要什么类型和功能的函数可以通 过手册去查找,了解实际功能后再使用。
MySQL 5.0 版本开始支持存储过程。 存储过程(Stored Procedure)是一种在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。 存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 存储过程思想上很简单,就是数据库 SQL 语言层面的代码封装与重用。
存储过程可封装,并隐藏复杂的商业逻辑。 存储过程可以回传值,并可以接受参数。 存储过程无法使用 SELECT 指令来运行,因为它是子程序,与查看表,数据表或用户定义函数不同。 存储过程可以用在数据检验,强制实行商业逻辑等。
存储过程,往往定制化于特定的数据库上,因为支持的编程语言不同。当切换到其他厂商的数据库系统时,需要重写原有的存储过程。 存储过程的性能调校与撰写,受限于各种数据库系统。
mysql> delimiter $$
mysql> create procedure MYSQL()
-> begin
-> select name,age from zhu;
-> end $$
Query OK, 0 rows affected (0.01 sec)
mysql> delimiter ; '结束的时候加空格'
通过存储过程查询 info 表中某一条记录,存储过程是带参数的,具体操作如下所示。
mysql> show procedure status where db='dog'; '这里要指向库的名字 而不是表的名字'
+-----+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-----+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| dog | MYSQL | PROCEDURE | root@localhost | 2020-08-30 09:14:55 | 2020-08-30 09:14:55 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+-----+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
mysql> call MYSQL();
+----------+-----+
| name | age |
+----------+-----+
| gousehng | 24 |
| goupeng | 28 |
| ergouzi | 27 |
| gouyan | 23 |
| goushi | 24 |
| gougou | 24 |
| gouduzi | 20 |
+----------+-----+
7 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter $$
mysql> create procedure mytest(in my_name varchar(10))
-> begin
-> select name,score from info where name=my_name;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> show procedure status where db='school';
+--------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | DatabaseCollation |
+--------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| school | MyRole | PROCEDURE | root@localhost | 2020-08-25 05:30:12 | 2020-08-25 05:30:12 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| school | mytest | PROCEDURE | root@localhost | 2020-08-25 08:00:30 | 2020-08-25 08:00:30 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+--------+--------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)
传参数进去,进行查询
mysql> call mytest('zhangsan');
Empty set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> call mytest('lisi');
+------+-------+
| name | score |
+------+-------+
| lisi | 98.00 |
+------+-------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
存储过程在创建之后,随着开发业务的不断推进,业务需求难免有所调整,相应的存储 过程也会发生变动,这个时候就需要修改存储过程。存储过程的修改分为特征的修改和业务 内容的修改。特征的修改可以使用 ALTER PROCEDURE 来实现,其语法结构如下所示。
语法格式
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic……]
参数说明
Sp_name,表示存储过程或函数的名称
characteristic,表示要修改存储过程的哪个部分
Characteristic的取值如下
CONTAINS SQL,表示子程序包含SQL语句,但是,不包含读或写数据的语句
NO SQL,表示子程序中,不包含SQL语句
READS SQL DATA,表示子程序中,包含读数据的语句
MODIFIES DATA,表示子程序中,包含写数据的语句
SQL SECURITY {DEFINER | INVOKER},指明谁有权限来执行
DEFINER,表示只有定义者,自己才能够执行
INVOKER,表示调用者可以执行
COMMENT’string’,表示注释信息
ALTER PROCEDURE <过程名> [ <特征> … ]
修改存储过程 mytest 的定义
将读写权限,改为MODIFIES SQL DATA,并指明调用者可以执行
mysql> alter procedure mytest
-> modifies sql data
-> sql security INVOKER;
Query OK, 0 rows affected (0.00 sec)
存储过程内容的修改方法是通过删除原有存储过程,之后再以相同的名称创建新的存储 过程。
存储过程创建之时是存储到 MySQL 数据库中的,当程序不在调用这个存储过程时,也就意味这个存储过程被废弃了,废弃的存储过程需要从数据库中将其删除。使用 DROP PROCEDURE 语句即可删除存储过程,其语法格式具体如下。
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
从以上语法结构可以看出,在删除时存储过程的名字是放到最后的,前面可以添加 IF EXISTS 这个关键字,其主要作用是防止因删除不存在的存储过程而引发的错误。删除存储过程的具体操作如下所示。
mysql> drop procedure MyRole;
Query OK, 0 rows affected (0.00 sec)
需要注意的是:存储过程名称后面没有参数列表,也没有括号。在删除之前,必须确认 该存储过程没有任何依赖关系,否则会导致与之关联的存储过程无法运行。