文章目录
数据库函数
MySQL 数据库函数提供了能够实现各种功能的方法，使我们在查询记录时能够更高效的输出。MySQL 内建了很多函数，常用的包括数学函数、聚合函数、字符串函数和日期时间函数。
常用的函数分类为：数学函数、聚合函数、字符串函数和日期时间函数。
数学函数
数据库内存储的记录，经常要进行一系列的算术操作，所以 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…)返回集合中最小的值
常用数学函数用法
abs
mysql> select abs(-1),abs(2);
+---------+--------+
| abs(-1) | abs(2) |
+---------+--------+
| 1 | 2 |
+---------+--------+
1 row in set (0.00 sec)
rand
mysql> select rand() ;
+--------------------+
| rand() |
+--------------------+
| 0.9884572345655944 |
+--------------------+
1 row in set (0.00 sec)
mod
mysql> select mod(11,3);
+-----------+
| mod(11,3) |
+-----------+
| 2 |
+-----------+
1 row in set (0.00 sec)
power
mysql> select power(3,2);
+------------+
| power(3,2) |
+------------+
| 9 |
+------------+
1 row in set (0.00 sec)
round
mysql> select round(1.49);
+-------------+
| round(1.49) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select round(1.54);
+-------------+
| round(1.54) |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
mysql> select round(1.54,1);
+---------------+
| round(1.54,1) |
+---------------+
| 1.5 |
+---------------+
1 row in set (0.00 sec)
sqrt
mysql> select sqrt(9);
+---------+
| sqrt(9) |
+---------+
| 3 |
+---------+
1 row in set (0.00 sec)
mysql> select sqrt(22);
+------------------+
| sqrt(22) |
+------------------+
| 4.69041575982343 |
+------------------+
1 row in set (0.00 sec)
truncate
mysql> select truncate(5.345677,3);
+----------------------+
| truncate(5.345677,3) |
+----------------------+
| 5.345 |
+----------------------+
1 row in set (0.00 sec)
ceil
mysql> select ceil(1.01);
+------------+
| ceil(1.01) |
+------------+
| 2 |
+------------+
1 row in set (0.00 sec)
floor
mysql> select floor(1.01);
+-------------+
| floor(1.01) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
least and greatest
mysql> select least(1,2,3),greatest(1,2,3);
+--------------+-----------------+
| least(1,2,3) | greatest(1,2,3) |
+--------------+-----------------+
| 1 | 3 |
+--------------+-----------------+
1 row in set (0.00 sec)
聚合函数
MySQL 数据库函数中专门有一组函数是特意为库内记录求和或者对表中的数据进行集中概括而设计的，这些函数被称作聚合函数。
常用的聚合函数
- avg(）返回指定列的平均值
- count()返回指定列中非NULL值的个数
- min(）返回指定列的最小值
- max(）返回指定列的最大值
- sum()返回指定列的所有值之和
常用聚合函数用法
avg
mysql> select * from mess;
+----+------+-------+-------+----------+
| id | name | score | award | address |
+----+------+-------+-------+----------+
| 1 | zs | 90 | 1 | nanjing |
| 2 | ls | 80 | 1 | beijing |
| 3 | ww | 70 | 2 | hubei |
| 4 | zl | 60 | 2 | xiameng |
| 5 | hb | 70 | 2 | nanjing |
| 6 | ad | 60 | 0 | quanzhou |
+----+------+-------+-------+----------+
6 rows in set (0.00 sec)
mysql> select avg(score) from mess;
+------------+
| avg(score) |
+------------+
| 71.6667 |
+------------+
1 row in set (0.00 sec)
count
mysql> select count(score) from mess;
+--------------+
| count(score) |
+--------------+
| 6 |
+--------------+
1 row in set (0.00 sec)
min
mysql> select min(score) from mess;
+------------+
| min(score) |
+------------+
| 60 |
+------------+
1 row in set (0.00 sec)
max
mysql> select max(score) from mess;
+------------+
| max(score) |
+------------+
| 90 |
+------------+
1 row in set (0.00 sec)
sum
mysql> select sum(score) from mess;
+------------+
| sum(score) |
+------------+
| 430 |
+------------+
1 row in set (0.00 sec)
字符串函数
常用函数不仅包括数学函数和聚合函数，还包含字符串函数，MySQL 为字符串的相关操作设计了丰富的字符串函数。
常用的字符串函数
- 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反转
常用的字符串函数的用法
length
mysql> select length('ab cdrt');
+-------------------+
| length('ab cdrt') |
+-------------------+
| 7 |
+-------------------+
1 row in set (0.00 sec)
trim
mysql> select ' bgp';
+-------+
| bgp |
+-------+
| bgp |
+-------+
1 row in set (0.00 sec)
mysql> select trim(' bgp');
+---------------+
| trim(' bgp') |
+---------------+
| bgp |
+---------------+
1 row in set (0.00 sec)
mysql> select trim('bgp ');
+---------------+
| trim('bgp ') |
+---------------+
| bgp |
+---------------+
1 row in set (0.00 sec)
concat
mysql> select concat('abc',' dnam');
+------------------------+
| concat('abc',' dnam') |
+------------------------+
| abc dnam |
+------------------------+
1 row in set (0.00 sec)
mysql> select concat('abc',trim(' dnam'));
+------------------------------+
| concat('abc',trim(' dnam')) |
+------------------------------+
| abcdnam |
+------------------------------+
1 row in set (0.00 sec)
upper and lower
mysql> select upper('abc'),lower('ABC');
+--------------+--------------+
| upper('abc') | lower('ABC') |
+--------------+--------------+
| ABC | abc |
+--------------+--------------+
1 row in set (0.00 sec)
left and right
mysql> select concat(left('abcdefg',2),right('abcdefg',2));
+----------------------------------------------+
| concat(left('abcdefg',2),right('abcdefg',2)) |
+----------------------------------------------+
| abfg |
+----------------------------------------------+
1 row in set (0.00 sec)
repeat
mysql> select repeat('sb',2);
+----------------+
| repeat('sb',2) |
+----------------+
| sbsb |
+----------------+
1 row in set (0.00 sec)
space
mysql> select concat('a',space(2),'dmin');
+-----------------------------+
| concat('a',space(2),'dmin') |
+-----------------------------+
| a dmin |
+-----------------------------+
1 row in set (0.00 sec)
replace
mysql> select replace('dig','i','o');
+------------------------+
| replace('dig','i','o') |
+------------------------+
| dog |
+------------------------+
1 row in set (0.00 sec)
stricmp
mysql> select strcmp(4,3);
+-------------+
| strcmp(4,3) |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
mysql> select srtcmp(3,4);
ERROR 1305 (42000): FUNCTION mask.srtcmp does not exist
mysql> select strcmp(3,4);
+-------------+
| strcmp(3,4) |
+-------------+
| -1 |
+-------------+
1 row in set (0.00 sec)
mysql> select strcmp(3,3);
+-------------+
| strcmp(3,3) |
+-------------+
| 0 |
+-------------+
1 row in set (0.00 sec)
mysql> select strcmp(27,3);
+--------------+
| strcmp(27,3) |
+--------------+
| -1 |
+--------------+
1 row in set (0.00 sec)
substring
mysql> select substring('1234567',3,4);
+--------------------------+
| substring('1234567',3,4) |
+--------------------------+
| 3456 |
+--------------------------+
1 row in set (0.00 sec)
reverse
mysql> select reverse('well');
+-----------------+
| reverse('well') |
+-----------------+
| llew |
+-----------------+
1 row in set (0.00 sec)
日期时间函数
MySQL 也支持日期时间处理，提供了很多处理日期和时间的函数。
常用的日期时间函数
- 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是本年的第几天
常用的日期时间函数的用法
curdate
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-08-25 |
+------------+
1 row in set (0.00 sec)
curtime
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 16:09:28 |
+-----------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-08-25 16:09:49 |
+---------------------+
1 row in set (0.00 sec)
month week
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)
hour minute second
mysql> select hour(curtime());
+-----------------+
| hour(curtime()) |
+-----------------+
| 16 |
+-----------------+
1 row in set (0.00 sec)
mysql> select minute(curtime());
+-------------------+
| minute(curtime()) |
+-------------------+
| 13 |
+-------------------+
1 row in set (0.00 sec)
mysql> select second(curtime());
+-------------------+
| second(curtime()) |
+-------------------+
| 49 |
+-------------------+
1 row in set (0.00 sec)
dayofweek dayofmonth dayofyear
mysql> select dayofweek(curtime());
+----------------------+
| dayofweek(curtime()) |
+----------------------+
| 3 |
+----------------------+
1 row in set (0.00 sec)
mysql> select dayofmonth(curtime());
+-----------------------+
| dayofmonth(curtime()) |
+-----------------------+
| 25 |
+-----------------------+
1 row in set (0.00 sec)
mysql> select dayofyear(curtime());
+----------------------+
| dayofyear(curtime()) |
+----------------------+
| 238 |
+----------------------+
1 row in set (0.00 sec)
数据库存储过程
前面学习的 MySQL 相关知识都是针对一个表或几个表的单条 SQL 语句，使用这样的SQL 语句虽然可以完成用户的需求，但在实际的数据库应用中，有些数据库操作可能会非常复杂，可能会需要多条 SQL 语句一起去处理才能够完成，这时候就可以使用存储过程， 轻松而高效的去完成这个需求。
存储过程简介
- 是一组为了完成特定功能的SQL语句集合
- 比传统SQL速度更快、执行效率更高
存储过程的优点
- 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率
- SQL语句加上控制语句的集合，灵活性高
- 在服务器端存储，客户端调用时，降低网络负载，提高安全性
因为存储在服务端，客户端只需调用即可，不需要在网络中传输长串的命令，这样具体命令也就不会暴露在网络中，所以减低网络负载，提高安全性 - 可多次重复被调用，可随时修改，不影响客户端调用
- 可完成所有的数据库操作，也可控制数据库的信息访问权限
创建存储过程
使用 CREATE PROCEDURE 语句创建存储过程，其语法格式如下所示。输入参数、输出参数和输入/输出参数，分别用 IN、OUT 和 INOUT 三个关键字表示。其中，输入参数可以传递给一个存储过程；输出参数用于存储过程需要返回一个操作结果的情形，而输入/输出参数既可以充当输入参数也可以充当输出参数。
CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名><类型>
存储主体部分
##定义存储过程
delimiter $$
create procedure存储过程名(in参数名参数类型)
##主体部分
begin
#定义变量
declare变量名变量类型
#变量赋值
set变量名=值
sql语句1;
sql语句2;
sql语句n;
end$
delimiter;
查看存储过程
show procedure status [where db=’数据库名’]
调用存储过程
call 过程名
具体实现
mysql> select * from mess;
+----+------+-------+-------+----------+
| id | name | score | award | address |
+----+------+-------+-------+----------+
| 1 | zs | 90 | 1 | nanjing |
| 2 | ls | 80 | 1 | beijing |
| 3 | ww | 70 | 2 | hubei |
| 4 | zl | 60 | 2 | xiameng |
| 5 | hb | 70 | 2 | nanjing |
| 6 | ad | 60 | 0 | quanzhou |
+----+------+-------+-------+----------+
6 rows in set (0.00 sec)
##定义存储过程
mysql> delimiter $$
mysql> create procedure Frole()
-> begin
-> select id,award from mess;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
##查看，我们刚刚定义的Frole
mysql> show procedure status where db='mask';
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mask | Frole | PROCEDURE | root@localhost | 2020-08-25 19:01:00 | 2020-08-25 19:01:00 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.02 sec)
##调用
mysql> call Frole();
+----+-------+
| id | award |
+----+-------+
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 2 |
| 6 | 0 |
+----+-------+
6 rows in set (0.00 sec)
##传参
mysql> delimiter $$
mysql> create procedure Srole(in c_name varchar(10))
-> begin
-> select id,name,address from mess where name=c_name;
-> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> show procedure status where db='mask';
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mask | Frole | PROCEDURE | root@localhost | 2020-08-25 19:01:00 | 2020-08-25 19:01:00 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
| mask | Srole | PROCEDURE | root@localhost | 2020-08-25 19:08:36 | 2020-08-25 19:08:36 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
2 rows in set (0.00 sec)
mysql> call Srole('ad');
+----+------+----------+
| id | name | address |
+----+------+----------+
| 6 | ad | quanzhou |
+----+------+----------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
修改存储过程
特征的修改可以使用 ALTER PROCEDURE 来实现，其语法结构如下所示。
ALTER PROCEDURE <过程名> [ <特征> … ]
存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程
删除存储过程
使用 DROP PROCEDURE 语句即可删除存储过程，其语法格式具体如下。
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
mysql> drop procedure Frole;
Query OK, 0 rows affected (0.00 sec)
mysql> show procedure status where db='mask';
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mask | Srole | PROCEDURE | root@localhost | 2020-08-25 19:08:36 | 2020-08-25 19:08:36 | DEFINER | | utf8 | utf8_general_ci | utf8_general_ci |
+------+-------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
