MySQL数据库基础学习（五）-函数

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 关于函数体

1. 函数体有合法的SQL语句组成

2. 函数体可是是简单的SELECT或INSERT语句

3. 函数体如果为复合结构则使用BEGIN...END语句

4. 符合结构可以包含声明、循环、控制结构

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;

小结

1. 运算符与函数

2. 自定义函数