MySQL数据库基础学习(五)-函数
1 运算符与函数
1.1 字符函数
函数名称 | 描述 |
---|---|
CONCAT() | 字符连接 |
CONCAT_WS() | 使用指定的分隔符进行字符连接 |
FORMAT() | 数字格式化 |
LOWER() | 转换成小写字母 |
UPPER() | 转换成大写字母 |
LEFT() | 获取左侧字符 |
RIGHT() | 获取右侧字符 |
LENGTH() | 获取字符段长度 |
LTRIM() | 删除前导空格 |
RTRIM() | 删除后序空格 |
TRIM() | 删除前导和后序字符 |
SUBSTRING() | 字符串截取 |
[NOT] LIKE() | 模式匹配 |
REPLACE() | 字符串替换 |
案例分析
mysql>USE imooc;
mysql>SELECT CONCAT('imooc','MySQL'); % imoocMySQL
将多个字符连接在一起
mysql>SELECT CONCAT('imooc','_','MySQL'); % imooc-MySQL
mysql>CREATE TABLE test1(
first_name VARCHAR(20),
last_name VARCHAR(10));
mysql>INSERT test1(first_name,last_name) VALUES('A','B'),('C','D'),('tom%','123'),(NULL,'11');
mysql>SELECT * FROM test1;
mysql>SELECT CONCAT(first_name,last_name) AS fullname FROM test1;
mysql>SELECT CONCAT_WS('1','A','C'); % 第一个字符为分隔符,至少三个参数
mysql>SELECT CONCAT_WS('_','imooc','MySQL','Function');
数字格式化
mysql>SELECT FORMAT(12560.78,2);
mysql>SELECT FORMAT(12560.78,1);
mysql>SELECT FORMAT(12560.78,0);
mysql>SELECT LOWER('MySQL');
mysql>SELECT UPPER('mysql');
mysql>SELECT LOWER(left('MySQL',2)); //先从左开始截取2个字符,然后将其转换为小写字母
mysql>SELECT LENGTH('MySQL'); //5
mysql>SELECT LENGTH('mY sql'); //6
mysql>SELECT LTRIM(' MySQL ');
mysql>SELECT LENGTH(RTRIM(' MySQL '));
mysql>SELECT LENGTH(LTRIM(' MySQL '));
mysql>SELECT LENGTH(TRIM(' MySQL '));
mysql>SELECT TRIM(LEADING '?' FROM '??MySQL???');
mysql>SELECT TRIM(TRAILING '?' FROM '??MySQL???');
mysql>SELECT TRIM(BOTH '?' FROM '??MySQL???');
mysql>SELECT TRIM(BOTH '?' FROM '??My??SQL???');
mysql>SELECT REPLACE('??My??SQL???', '?', ' '); //MySQL
mysql>SELECT REPLACE('??My??SQL???', '?', '!');
mysql>SELECT REPLACE('??My??SQL???', '?', '!!');
mysql>SELECT REPLACE('??My??SQL???', '??', '*');
mysql>SELECT SUBSTRING('MySQL',1,2); //从第一位开始截取两个字符 第三个参数不能为负数
mysql>SELECT SUBSTRING('MySQL',-1);
mysql>SELECT 'MySQL' LIKE 'M%'; // %为通配符 返回1
mysql>SELECT * FROM test1 WHERE first_name LIKE '%o%';
mysql>SELECT * FROM test1 WHERE first_name LIKE '%%%'; //任意字符 需要查找字符 任意字符
mysql>SELECT * FROM test1 WHERE first_name LIKE '%1%';
% (百分号):代表任意个字符
_(下划线): 代表任意1个字符
1.2 数值运算符
名称 | 描述 |
---|---|
CEIL() | 进一取整 |
DIV() | 整数除法 |
FLOOR() | 舍一取整 |
MOD() | 取余数(取模) |
POWER() | 幂运算 |
ROUND() | 四舍五入 |
TRUNCATE() | 数字截取 |
案例分析
mysql>SELECT 1 + 4;
mysql>SELECT CEIL(3.01); //4
mysq>SELECT FLOOR(3.99); //
mysql>SELECT 3 / 4; //0.7500
mysql>SELECT 3 DIV 4; //0
mysql>SELECT 5 % 3; //2
mysql>SELECT 5 MOD 3; //2
mysql>SELECT 5.3 MOD 3; //2.3
mysql>SELECT ROUND(3.652,2);
mysql>SELECT ROUND(125.89,2); //125.89
mysql>SELECT ROUND(125.89,1); //125.9
mysql>SELECT ROUND(125.89,0); //126
mysql>SELECT ROUND(125.89,-1); //130
1.3 比较运算符和函数
名称 | 描述 |
---|---|
[NOT] BETWEEN...AND... | [不]在范围之内 |
[NOT] IN() | [不] 在列出值范围内 |
IS [NOT] NULL | [不]为空 |
案例分析
mysql>SELECT 15 BETWEEN 1 AND 22; //1
mysql>SELECT 35 BETWEEN 1 AND 22; //0
mysql>SELECT 10 IN(5,10,15,20); //1
mysql>SELECT 12 IN(5,10,15,20); //0
mysql>SELECT NULL IS NULL; //1
mysql>SELECT ' ' IS NULL; //0
mysql>SELECT 0 IS NULL; //0
mysql>SELECT * FROM test1 WHERE first_name [NOT] IS NULL;
1.4 日期时间函数
名称 | 描述 |
---|---|
NOW() | 当前时间和日期 |
CURDATE() | 当前日期 |
CURTIME() | 当前时间 |
DATE_ADD() | 日期变化 |
DATEDIFF() | 日期差值 |
DATE_FORMAT() | 日期格式化 |
案例分析
mysql>SELECT NOW();
mysql>SELECT CURDATE();
mysql>SELECT CURTIME();
mysql>SELECT DATE_ADD('2018-11-24',INTERVAL 365 DAY);
mysql>SELECT DATE_ADD('2018-11-24',INTERVAL -365 DAY);
mysql>SELECT DATE_ADD('2018-11-24',INTERVAL 1 YEAR);
mysql>SELECT DATE_ADD('2018-11-24',INTERVAL 3 WEEK);
mysql>SELECT DATEDIFF('2018-11-24','2019-11-24');
mysql>SELECT DATE_FORMAT('2018-11-24','%m/%d/%Y');
1.5 信息函数
名称 | 描述 |
---|---|
CONNECTION_ID() | 连接ID |
DATABASE() | 当前数据库 |
LAST_INSERT_ID() | 最后插入记录的ID |
USER() | 当前用户 |
VERSION() | 版本信息 |
案例分析
mysql>SELECT CONNECT_ID();
mysql>SELECT DATABASE();
mysql>DESC test1;
mysql>ALTER TABLE test1 ADD id SMALLINT UNSIGNED KEY AUTO_INCREMENT FIRST;
mysql>DESC test1;
mysql>SELECT * FROM test1;
mysql>INSERT test1(first_name,last_name) VALUES('11','22');
mysql>LAST_INSERT_ID(); //5
同时写入多条记录
mysql>INSERT test1(first_name,last_name) VALUES('AA','BB'),('cc','DD');
mysql>LAST_INSERT_ID(); //6
1.6 聚合函数
名称 | 描述 |
---|---|
AVG() | 平均值 |
COUNT() | 计数 |
MAX() | 最大值 |
MIN() | 最小值 |
SUM() | 求和 |
案例分析
mysql>SELECT AVG(3,4,5);
1.7加密函数
名称 | 描述 |
---|---|
MD5() | 信息摘要算法 |
PASSWORD() | 密码算法 |
案例分析
mysql>SELECT MD5('admin')
mysql>SELECT PASSWORD('admin');
mysql>SET password = PASSWORD('dimiter'); %修改密码
2 自定义函数
2.1 自定义概述
用户自定义函数(user-defined function, UDF)是一种对MySQL扩展的途径,其用法与内置函数相同
必要条件:参数 返回值
函数可以返回任意类型的值,同样可以接受这些类型的参数
创建自定义函数
CREATE FUNCTION function_name
RETURNS
{STRING | INTEGER | REAL | DECIMAL}
routine_body
2.1.1 关于函数体
-
函数体有合法的SQL语句组成
-
函数体可是是简单的SELECT或INSERT语句
-
函数体如果为复合结构则使用BEGIN...END语句
-
符合结构可以包含声明、循环、控制结构
2.2 创建不带参数的自定义函数
mysql>SET NAMES gbk;
mysql>USE test;
mysql>SELECT NOW();
mysql>SELECT DATE_FORMAT(now(), '%Y年%m月%d日 %H点:%i分:%s秒');
mysql>CREATE FUNCTION f1()
RETURNS VARCHAR(30)
RETURN DATE_FORMAT(NOW(), '%Y年%m月%d日 %H点:%i分:%s秒');
mysql>SELECT f1();
2.3 创建带有参数的自定义函数
mysql>CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED
RETURN (num1 + num2)/2;
mysql>SELECT f2(10,15);
2.4 创建具有符合结构函数体的自定义函数
mysql>CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
RETURN
INSERT test1(uername) VALUES(username);
错误,原因是 ;(分号)是SQL命令的结束
修改SQL默认的分隔符
mysql>DELIMITER //
mysql>SELECT VERSION(); % 不管用
// %结束
mysql>CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
RETURN
INSERT test1(uername) VALUES(username);
LAST_INSERT_ID();
//
错误,因为是多条语句执行,需添加BEGIN...AND构合结构体
mysql>CREATE FUNCTION adduser(username VARCHAR(20))
RETURNS INT UNSIGNED
BEGIN
INSERT test1(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
mysql>SELECT adduser('Rose');
//
mysql>DELIMITER ;
mysql>SELECT adduser('Tom');
mysql>SELECT * FROM test1;
2.5 删除函数
DROP FUNCTION [IF EXISTS] function_name;
小结
-
运算符与函数
-
自定义函数