数据库函数
数学函数
abs(x):返回x的绝对值
mysql> select abs(1),abs(-1);
+--------+---------+
| abs(1) | abs(-1) |
+--------+---------+
| 1 | 1 |
+--------+---------+
1 row in set (0.00 sec)
rand():返回0到1的随机数(0-0.9999…,1是取不到的)
mysql> select rand();
+-------------------+
| rand() |
+-------------------+
| 0.875569282915042 |
+-------------------+
1 row in set (0.00 sec)
mod(x,y):返回x除以y以后的余数
mysql> select mod(3,2),mod(3,7);
+----------+----------+
| mod(3,2) | mod(3,7) |
+----------+----------+
| 1 | 3 |
+----------+----------+
1 row in set (0.00 sec)
-power(x,y):返回x的y次方
mysql> select power(2,3),power(4,2);
+------------+------------+
| power(2,3) | power(4,2) |
+------------+------------+
| 8 | 16 |
+------------+------------+
1 row in set (0.00 sec)
round(x):返回离x最近的整数(四舍五入,只看小数点后第一位)
mysql> select round(2.4),round(2.5);
+------------+------------+
| round(2.4) | round(2.5) |
+------------+------------+
| 2 | 3 |
+------------+------------+
1 row in set (0.00 sec)
round(x,y):保留x的y位小数四舍五入后的值
mysql> select round(3.14159,1),round(3.14159,2);
+------------------+------------------+
| round(3.14159,1) | round(3.14159,2) |
+------------------+------------------+
| 3.1 | 3.14 |
+------------------+------------------+
1 row in set (0.00 sec)
sqrt(x):返回x的平方根
mysql> select sqrt(9),sqrt(3);
+---------+--------------------+
| sqrt(9) | sqrt(3) |
+---------+--------------------+
| 3 | 1.7320508075688772 |
+---------+--------------------+
1 row in set (0.00 sec)
truncate(x,y):返回数字x截断为y位小数的值
mysql> select truncate(3.456,1),truncate(3.456,2);
+-------------------+-------------------+
| truncate(3.456,1) | truncate(3.456,2) |
+-------------------+-------------------+
| 3.4 | 3.45 |
+-------------------+-------------------+
1 row in set (0.00 sec)
ceil(x):返回大于或等于x的最小整数(向上取整数)
mysql> select ceil(2.3),ceil(3.5);
+-----------+-----------+
| ceil(2.3) | ceil(3.5) |
+-----------+-----------+
| 3 | 4 |
+-----------+-----------+
1 row in set (0.00 sec)
floor(x):返回小于或等于x的最大整数(向下取整数)
mysql> select floor(2.4),floor(1.9);
+------------+------------+
| floor(2.4) | floor(1.9) |
+------------+------------+
| 2 | 1 |
+------------+------------+
1 row in set (0.00 sec)
greatest(x1,x2…):返回集合中最大的值
mysql> select greatest(11,55,39);
+--------------------+
| greatest(11,55,39) |
+--------------------+
| 55 |
+--------------------+
1 row in set (0.00 sec)
least(x1,x2…):返回集合中最小的值
mysql> select least(11,55,39);
+-----------------+
| least(11,55,39) |
+-----------------+
| 11 |
+-----------------+v
1 row in set (0.00 sec)
聚合函数
1、对表中数据记录进行集中概括而设计的一类函数
2、常用的聚合函数(只会产生一个值)
avg(字段名) 返回指定字段的平均值
mysql> select avg(age) from aa;
+----------+
| avg(age) |
+----------+
| 19 |
+----------+
1 row in set (0.00 sec)
count(字段名) 返回指定字段中非NULL值的个数
mysql> select * from aa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 16 |
| 3 | wangwu | 20 |
| 4 | chanwi | 20 |
| 5 | zhaomi | NULL |
+------+----------+------+
5 rows in set (0.00 sec)
mysql> select count(age) from aa;
+------------+
| count(age) |
+------------+
| 4 |
+------------+
1 row in set (0.00 sec)
min(字段名) 返回指定字段的最小值
mysql> select min(age) from aa;
+----------+
| min(age) |
+----------+
| 16 |
+----------+
1 row in set (0.04 sec)
max(字段名) 返回指定字段的最大值
mysql> select max(age) from aa;
+----------+
| max(age) |
+----------+
| 20 |
+----------+
1 row in set (0.00 sec)
sum(字段名) 返回指定字段的所有值之和
mysql> select sum(age) from aa;
+----------+
| sum(age) |
+----------+
| 76 |
+----------+
1 row in set (0.00 sec)
字符串函数
length(x):返回字符串x的长度(空格也算)
mysql> select length('abc d');
+-----------------+
| length('abc d') |
+-----------------+
| 5 |
+-----------------+
1 row in set (0.00 sec)
trim():返回去除指定格式的值(只能去除前后的空格)
mysql> select trim(' a bc d');
+-----------------+
| trim(' a bc d') |
+-----------------+
| a bc d |
+-----------------+
1 row in set (0.00 sec)
concat(x,y):将提供的参数x和y拼接成一个字符串
mysql> select concat('ab','cd');
+-------------------+
| concat('ab','cd') |
+-------------------+
| abcd |
+-------------------+
1 row in set (0.00 sec)
upper(x):将字符串x的所有字母变成大写字母
mysql> select upper('abc');
+--------------+
| upper('abc') |
+--------------+
| ABC |
+--------------+
1 row in set (0.00 sec)
lower(x):将字符串x的所有字母变成小写字母
mysql> select lower('ABC');
+--------------+
| lower('ABC') |
+--------------+
| abc |
+--------------+
1 row in set (0.00 sec)
left(x,y):返回字符串x的前y个字符
mysql> select left('abcde',2);
+-----------------+
| left('abcde',2) |
+-----------------+
| ab |
+-----------------+
1 row in set (0.00 sec)
right(x,y):返回字符串x的后y个字符
mysql> select right('abcde',2);
+------------------+
| right('abcde',2) |
+------------------+
| de |
+------------------+
1 row in set (0.00 sec)
repeat(x,y):将字符串x重复y次
mysql> select repeat('ab',2);
+----------------+
| repeat('ab',2) |
+----------------+
| abab |
+----------------+
1 row in set (0.00 sec)
space(x):返回x个空格(结合concat使用)
mysql> select concat('hello',space(2),'woeld');
+----------------------------------+
| concat('hello',space(2),'woeld') |
+----------------------------------+
| hello woeld |
+----------------------------------+
1 row in set (0.00 sec)
replace(x,y,z):将字符串z替代字符串x中的字符串y
mysql> select replace('abcde','a','z');
+--------------------------+
| replace('abcde','a','z') |
+--------------------------+
| zbcde |
+--------------------------+
1 row in set (0.00 sec)
strcmp(x,y):比较x和y,返回的值可以为-1 <,0 =,1 >
mysql> select strcmp(6,5),strcmp(5,5),strcmp(10,5);
+-------------+-------------+--------------+
| strcmp(6,5) | strcmp(5,5) | strcmp(10,5) |
+-------------+-------------+--------------+
| 1 | 0 | -1 |
+-------------+-------------+--------------+
1 row in set (0.00 sec)
substring(x,y,z):获取从字符串x中的第y个位置开始长度为z的字符串
格式:substring(完整字符串,起始位置,长度); #起始位置从1开始
mysql> select substring('abcde',2,2);
+------------------------+
| substring('abcde',2,2) |
+------------------------+
| bc |
+------------------------+
1 row in set (0.00 sec)
reverse(x):将字符串x反转
mysql> select reverse('hello');
+------------------+
| reverse('hello') |
+------------------+
| olleh |
+------------------+
1 row in set (0.00 sec)
日期时间函数
curdate():返回当前时间的年月日
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2020-12-28 |
+------------+
1 row in set (0.00 sec)
curtime():返回当前时间的时分秒
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 00:12:15 |
+-----------+
1 row in set (0.04 sec)
now():返回当前时间的日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-12-28 00:12:25 |
+---------------------+
1 row in set (0.00 sec)
month(x):返回日期x中的月份值
mysql> select month(curdate());
+------------------+
| month(curdate()) |
+------------------+
| 12 |
+------------------+
1 row in set (0.00 sec)
hour(x):返回x中的小时值
mysql> select hour(curdate());
+-----------------+
| hour(curdate()) |
+-----------------+
| 0 |
+-----------------+
1 row in set (0.00 sec)
minute(x):返回x中的分钟值
ysql> select minute(now());
+---------------+
| minute(now()) |
+---------------+
| 16 |
+---------------+
1 row in set (0.00 sec)
second(x):返回x中的秒钟值
mysql> select second(now());
+---------------+
| second(now()) |
+---------------+
| 55 |
+---------------+
1 row in set (0.00 sec)
dayofweek(x):返回x是星期几,1星期日,2星期一,3星期二…
mysql> select dayofweek(now());
+------------------+
| dayofweek(now()) |
+------------------+
| 2 |
+------------------+
1 row in set (0.00 sec)
mysql> select dayofweek('2020-12-28');
+-------------------------+
| dayofweek('2020-12-28') |
+-------------------------+
| 2 |
+-------------------------+
1 row in set (0.00 sec)
dayofmonth(x):计算日期x是本月的第几天
mysql> select dayofmonth(now());
+-------------------+
| dayofmonth(now()) |
+-------------------+
| 28 |
+-------------------+
1 row in set (0.00 sec)
mysql> select dayofmonth('2020-12-28');
+--------------------------+
| dayofmonth('2020-12-28') |
+--------------------------+
| 28 |
+--------------------------+
1 row in set (0.00 sec)
dayofyear(x):计算日期x是本年的第几天
mysql> select dayofyear(now());
+------------------+
| dayofyear(now()) |
+------------------+
| 363 |
+------------------+
1 row in set (0.00 sec)
mysql> select dayofyear('2020-12-28');
+-------------------------+
| dayofyear('2020-12-28') |
+-------------------------+
| 363 |
+-------------------------+
1 row in set (0.00 sec)
MySQL存储过程
存储过程简介
1、从 5.0 版本才开始支持
2、是一组为了完成特定功能的SQL语句集合(封装)
3、比传统SQL速度更快、执行效率更高
4、存储过程的优点
(1)执行一次后,会将生成的二进制代码驻留缓冲区(便于下次执行),提高执行效率
(2)SQL语句加上控制语句的集合,灵活性高
(3)在服务器端存储,客户端调用时,降低网络负载
(4)可多次重复被调用,可随时修改,不影响客户端调用
(5)可完成所有的数据库操作,也可控制数据库的信息访问权限
5、为什么要用存储过程?
(1)减轻网络负载;
(2)增加安全性
创建存储过程
1、使用create procedure语句创建存储过程
2、参数分为
输入参数:in
输出参数:out
输入/输出参数:inout
3、存储过程的主体部分,被称为过程体;以begin开始,以end$$结束
4、具体格式
delimiter $$
create procedure 存储过程名(in 参数名 参数类型)
begin
#定义变量
declare 变量名 变量类型
#变量赋值
set 变量名 = 值
sql 语句1;
sql 语句2;
...
end$$
delimiter ;(有空格)
举例:
mysql> delimiter $$
mysql> create procedure aa ()
-> begin
-> select * from aa;
-> end $$
Query OK, 0 rows affected (0.04 sec)
调用存储过程
call 存储过程名(实际参数);
举例:
mysql> delimiter ;
mysql> call aa;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 20 |
| 2 | lisi | 16 |
| 3 | wangwu | 20 |
| 4 | chanwi | 20 |
| 5 | zhaomi | NULL |
+------+----------+------+
5 rows in set (0.00 sec)
查询存储过程
show procedure status where db=‘数据库’;
修改存储过程
存储过程的修改分为特征的修改和业务内容的修改。
特征的修改语法结构如下:
alter procedure 存储过程名 [ <特征> … ]
删除存储过程
删除存储过程的语法:
drop {procedure|function|if exits} <过程名>
举例:
mysql> drop procedure aa;
Query OK, 0 rows affected (0.04 sec)
mysql> call aa;
ERROR 1305 (42000): PROCEDURE ttt.aa does not exist
传递参数过程
栗子1:
mysql> delimiter $$
mysql> create procedure aa(in inname varchar(16))
-> begin
-> select * from aa where name=inname;
-> end $$
Query OK, 0 rows affected (0.04 sec)
mysql> delimiter ;
mysql> call aa('zhangsan');
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 20 |
+------+----------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
栗子2:
mysql> set @num1=1;@num2=2,@num3=3;
Query OK, 0 rows affected (0.00 sec)
mysql> create procedure b(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.04 sec)
mysql> delimiter ;
mysql> call b(@num1,@num2,@num3);
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 1 | NULL | NULL |
+------+------+------+
1 row in set (0.00 sec)
+------+------+------+
| num1 | num2 | num3 |
+------+------+------+
| 10 | 20 | 30 |
+------+------+------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
总结:in和inout参数会将全局变量的值传入存储过程中,而out参数不会将全局变量的值传入存储过程中。在存储过程使用中,参数值in,out,inout都会发生改变。
栗子3:
mysql> select @num1,@num2,@num3;
+-------+-------+-------+
| @num1 | @num2 | @num3 |
+-------+-------+-------+
| 1 | 20 | 30 |
+-------+-------+-------+
1 row in set (0.00 sec)
总结:
1、调用完存储过程后,发现in参数不会对全局变量的值引起变化,而out和inout参数调用完存储过程后,会对全局变量的值产生变化,会将存储过程引用后的值赋值给全局变量。
2、in参数赋值类型可以是变量还有定值,而out和inout参数赋值类型必须为变量。