MySQL 内置函数

MySQL中提供了许多内置函数，以下是一些常用的函数及其示例：

一、数学函数

1. ROUND(x, y)

• 返回参数x的四舍五入的有y位小数的值。

SELECT ROUND(123.456, 2);  -- 结果：123.46

2. RAND()

• 返回0到1内的随机值，可以通过提供一个参数（种子）使RAND()随机数生成器生成一个指定的值。

SELECT RAND(); -- 结果：随机数

二、聚合函数（常用于GROUP BY从句的SELECT查询中）

1. AVG(col) 返回指定列的平均值。
2. COUNT(col) 返回指定列中非NULL值的个数。
3. MIN(col) 返回指定列的最小值。
4. MAX(col) 返回指定列的最大值。
5. SUM(col) 返回指定列的所有值之和。
6. GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果。

SELECT AVG(salary) FROM employee;
SELECT COUNT(id) FROM employee;
SELECT MIN(salary) FROM employee;
SELECT MAX(salary) FROM employee;
SELECT SUM(salary) FROM employee;
SELECT GROUP_CONCAT(name) FROM employee GROUP BY department_id;

三、字符串函数

1. CHAR_LENGTH(str)

• 返回字符串str的长度，长度的单位为字符。

SELECT CHAR_LENGTH('Hello World');  -- 结果：11

2. CONCAT(str1, str2, …)

• 字符串拼接，如有任何一个参数为NULL，则返回值为NULL。

SELECT CONCAT('Hello', ' ', 'World');  -- 结果：Hello World

3. CONCAT_WS(separator, str1, str2, …)

• 字符串拼接（自定义连接符）。

SELECT CONCAT_WS('-', '2024', '06', '18');  -- 结果：2024-06-18

4. CONV(N, from_base, to_base)

• 进制转换。

SELECT CONV('a', 16, 2);  -- 把16进制数‘a’转换成2禁止，结果：1010

5. FORMAT(X, D)

• 将数字X的格式写为’#,###,###.##’，以四舍五入的方式保留小数点后D位，并将结果以字符串的形式返回。

SELECT FORMAT(12332.1, 4);  -- 结果：12,332.1000

6. INSERT(str, pos, len, newstr)

• 在str的指定位置插入字符串。

SELECT INSERT('Hello World', 7, 5, 'MySQL');  -- 结果：Hello MySQL

7. INSTR(str, substr)

• 返回字符串str中子字符串的第一个出现位置。

SELECT INSTR('Hello World', 'World');  -- 结果：7

8. LEFT(str, len)

• 返回字符串str从开始的len位置的子序列字符。

SELECT LEFT('Hello World', 5);  -- 结果：Hello

9. LOWER(str)

• 变小写。

SELECT LOWER('Hello World');  -- 结果：hello world

10. UPPER(str)

• 变大写。

SELECT UPPER('Hello World');  -- 结果：HELLO WORLD

11. REVERSE(str)

• 返回字符串str，顺序和字符顺序相反。

SELECT REVERSE('Hello World');  -- 结果：dlroW olleH

12. SUBSTRING(str, pos)

• 返回从字符串str的pos位置开始的子字符串。

SELECT SUBSTRING('Quadratically', 5);  -- 结果：ratically

四、***日期和时间函数（重点：DATE_FORMA）

1. CURDATE() 或 CURRENT_DATE()

• 返回当前的日期。

SELECT CURDATE();  -- 结果：当前日期

2. CURTIME() 或 CURRENT_TIME()

• 返回当前的时间。

SELECT CURTIME();  -- 结果：当前时间

3. DAYOFWEEK(date)

• 返回date所代表的一星期中的第几天(1~7)。

SELECT DAYOFWEEK('2024-06-18');  -- 结果：3（星期二）

4. ***DATE_FORMAT(date, format)

• 根据format字符串格式化date值。

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');  -- 结果：Sunday October 2009

五、加密函数

1. MD5()

• 计算字符串str的MD5校验和。

SELECT MD5('password');  -- 结果：5f4dcc3b5aa765d61d8327deb882cf99

2. PASSWORD(str)

• 返回字符串str的加密版本，这个加密过程是不可逆转的。

SELECT PASSWORD('password');  -- 结果：*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19

六、控制流函数

1. CASE WHEN [test1] THEN [result1]… ELSE [default] END

• 如果testN是真，则返回resultN，否则返回default。

SELECT CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END FROM persons;

2. IF(test, t, f)

• 如果test是真，返回t；否则返回f。

SELECT IF(salary > 5000, 'High', 'Low') FROM employee;

3. IFNULL(arg1, arg2)

• 如果arg1不是空，返回arg1，否则返回arg2。

SELECT IFNULL(email, 'noemail@example.com') FROM employee;

4. NULLIF(arg1, arg2)

• 如果arg1=arg2返回NULL；否则返回arg1。

SELECT NULLIF(1, 1);  -- 结果：NULL
SELECT NULLIF(1, 2);  -- 结果：1

控制流函数小练习

#准备表
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `c_id` int(11) NOT NULL,
  `c_name` varchar(255) DEFAULT NULL,
  `t_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`c_id`),
  KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');

DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `s_id` int(10) DEFAULT NULL,
  `c_id` int(11) DEFAULT NULL,
  `num` double DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

INSERT INTO `score` VALUES 
('1', '1', '1', '79'),
('2', '1', '2', '78'),
('3', '1', '3', '35'),
('4', '2', '2', '32'),
('5', '3', '1', '66'),
('6', '4', '2', '77'),
('7', '4', '1', '68'),
('8', '5', '1', '66'),
('9', '2', '1', '69'),
('10', '4', '4', '75'),
('11', '5', '4', '66.7');

DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `s_id` varchar(20) NOT NULL,
  `s_name` varchar(255) DEFAULT NULL,
  `s_age` int(10) DEFAULT NULL,
  `s_sex` char(1) DEFAULT NULL,
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES 
('1', '鲁班', '12', '男'),
('2', '貂蝉', '20', '女'),
('3', '刘备', '35', '男'),
('4', '关羽', '

34', '男'),
('5', '张飞', '33', '女');

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `t_id` int(10) NOT NULL,
  `t_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` VALUES 
('1', '大王'),
('2', 'alex'),
('3', 'egon'),
('4', 'peiqi');

# 统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT  
    score.c_id,
    course.c_name, 
    SUM(CASE WHEN num BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100-85]',
    SUM(CASE WHEN num BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS '[85-70]',
    SUM(CASE WHEN num BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS '[70-60]',
    SUM(CASE WHEN num < 60 THEN 1 ELSE 0 END) AS '[ <60]'
FROM 
    score
JOIN 
    course 
ON 
    score.c_id = course.c_id 
GROUP BY 
    score.c_id;

DATE_FORMAT函数

1. 基本使用

1. 将日期格式化为星期、月份和年份

SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-- 结果：'Sunday October 2009'

2. 将时间格式化为小时、分钟和秒

SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-- 结果：'22:23:00'

3. 将日期和时间格式化为多个格式

SELECT DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j');
-- 结果：'4th 00 Thu 04 10 Oct 277'

4. 将时间格式化为不同的时间表示

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w');
-- 结果：'22 22 10 10:23:00 PM 22:23:00 00 6'

5. 将日期格式化为年份和周数

SELECT DATE_FORMAT('1999-01-01', '%X %V');
-- 结果：'1998 52'

6. 将日期格式化为天

SELECT DATE_FORMAT('2006-06-00', '%d');
-- 结果：'00'

2. DATE_FORMAT函数实验

1. 创建表和插入数据

CREATE TABLE blog (
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME CHAR (32),
    sub_time DATETIME
);

INSERT INTO blog (NAME, sub_time) VALUES
    ('第1篇','2015-03-01 11:31:21'),
    ('第2篇','2015-03-11 16:31:21'),
    ('第3篇','2016-07-01 10:21:31'),
    ('第4篇','2016-07-22 09:23:21'),
    ('第5篇','2016-07-23 10:11:11'),
    ('第6篇','2016-07-25 11:21:31'),
    ('第7篇','2017-03-01 15:33:21'),
    ('第8篇','2017-03-01 17:32:21'),
    ('第9篇','2017-03-01 18:31:21');

2. 提取sub_time字段的值，按照格式后的结果即”年月”来分组

SELECT DATE_FORMAT(sub_time,'%Y-%m'), COUNT(1) #COUNT(col) 返回指定列中非NULL值的个数
FROM blog 
GROUP BY DATE_FORMAT(sub_time, '%Y-%m');

结果

+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03                       |        2 |
| 2016-07                       |        4 |
| 2017-03                       |        3 |
+-------------------------------+----------+
3 rows in set (0.00 sec)

更多关于MySQL函数的详细信息，可以参考MySQL官方文档。

---

自定义函数

注意事项

• 函数中不要写SQL语句（否则会报错），函数仅仅只是一个功能，是一个在SQL中被应用的功能。
• 若要想在BEGIN...END...中写SQL，请用存储过程。

1. 基本自定义函数

1. 创建简单的加法函数

DELIMITER //
CREATE FUNCTION f1(i1 INT, i2 INT) RETURNS INT
BEGIN
    DECLARE num INT;
    SET num = i1 + i2;
    RETURN num;
END //
DELIMITER ;

2. 调用自定义函数

SELECT f1(10, 20);  -- 结果：30

2. 条件判断自定义函数

1. 创建带有条件判断的函数

DELIMITER //
CREATE FUNCTION f5(i INT) RETURNS INT
BEGIN
    DECLARE res INT DEFAULT 0;
    IF i = 10 THEN
        SET res = 100;
    ELSEIF i = 20 THEN
        SET res = 200;
    ELSEIF i = 30 THEN
        SET res = 300;
    ELSE
        SET res = 400;
    END IF;
    RETURN res;
END //
DELIMITER ;

2. 调用带有条件判断的函数

SELECT f5(10);  -- 结果：100
SELECT f5(20);  -- 结果：200
SELECT f5(30);  -- 结果：300
SELECT f5(40);  -- 结果：400

3. 删除函数

DROP FUNCTION IF EXISTS func_name;

4. 执行函数

1. 获取返回值

SELECT UPPER('egon') INTO @res;
SELECT @res;

2. 在查询中使用

SELECT f1(11, nid), name FROM tb2;