MaRui

MMMM

函数

1732字约6分钟

2024-08-10

函数

汇总

函 数说 明
AVG()返回某列的平均值
COUNT()返回某列的行数
MAX()返回某列的最大值
MIN()返回某列的最小值
SUM()返回某列值之和

AVG() 会忽略 NULL 行。COUNT(*)和COUNT(列)区别在于，COUNT(列)统计会排除列为 null 的

使用 DISTINCT 可以让汇总函数值汇总不同的值。

SELECT AVG(DISTINCT col1) AS avg_col
FROM mytable;

文本处理

LEFT()从左边截取字符
CONCAT()将多个字符串连接成一个字符串
CONCAT_WS()代表 CONCAT With Separator ，是CONCAT()的特殊形式。 第一个参数是其它参数的分隔符。
RIGHT()从右边截取字符
LOWER()转换为小写字符
UPPER()转化为大写字符
LTRIM()去除左边的空格
RTRIM()去除右边的空格
LENGTH()长度
SOUNDEX()转换为语音值
-- CONCAT(str1,str2,…) 
SELECT CONCAT(Num,str) FROM logs;
-- CONCAT_WS(separator,str1,str2,…) 
SELECT CONCAT_WS(',',Num,str) FROM logs;
-- LEFT(Str,length);从左边截取； Str:字符串，length：截取的长度
SELECT LEFT(str,2) FROM logs;
-- RIGHT(Str,length);从右边截取 Str:字符串，length：截取的长度
SELECT RIGHT(str,2) FROM logs;
-- 转化为小写字符
SELECT LOWER(str) FROM logs;
-- 转换为大写字符
SELECT UPPER(str) FROM logs;
-- 去除左边的空格
SELECT LTRIM(str) FROM logs;
-- 去除右边的空格
SELECT RTRIM(str) FROM logs;
-- 长度
SELECT LENGTH(str) FROM logs;
-- SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。
SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和时间处理

  • 日期格式: YYYY-MM-DD

  • 时间格式: HH:MM:SS

ADDDATE()增加一个日期(天、周等)
ADDTIME()增加一个时间(时、分等)
CURDATE()返回当前日期
CURTIME()返回当前时间
DATE()返回日期时间的日期部分
DATEDIFF()计算两个日期之差
DATE_ADD()灵活的日期运算函数
DATE_FORMAT()返回一个格式化的日期或时间串
DAY()返回一个日期的天数部分
DAYOFWEEK()对于一个日期，返回对应的星期几
HOUR()返回一个时间的小时部分
MINUTE()返回一个日期分钟部分
MONTH()返回一个日期的月部分
NOW()获取当前日期和时间
SECOND()返回一个时间的秒部分
TIME()回一个日期时间的时间部分
YEAR()返回一个日期的年份部分
-- 增加一个日期 2021-05-13 17:47:54
SELECT ADDDATE('2021-05-12 17:47:54',INTERVAL 1 DAY);
-- 增加一个时间 2021-05-12 18:48:55
SELECT ADDTIME('2021-05-12 17:47:54', '01:01:01');
-- 返回当前日期 2021-05-12
SELECT CURDATE();
-- 返回当前时间 17:46:36
SELECT CURTIME();
-- 返回一个日期时间的日期部分 2021-05-12
SELECT DATE(NOW());
-- 计算两个日期之差 2
SELECT DATEDIFF('2021-05-12 17:43:34', '2021-05-10 15:43:34');
-- 灵活的日期运算函数 2021-05-13 17:47:54
SELECT DATE_ADD('2021-05-12 17:47:54',INTERVAL 1 DAY);
-- 返回一个格式化的日期或时间串 2021-05-12
SELECT DATE_FORMAT('2021-05-12 17:47:54', '%Y-%m-%d');
-- 返回一个日期的天数部分 12
SELECT DAY('2021-05-12 17:47:54');
-- 对于一个日期，返回对应的星期几 4 （5.12是星期三）
SELECT DAYOFWEEK('2021-05-12 17:47:54');
-- 返回一个时间的小时部分 17
SELECT HOUR('2021-05-12 17:47:54');
-- 返回一个日期分钟部分 47
SELECT MINUTE('2021-05-12 17:47:54');
-- 返回一个日期的月部分 5
SELECT MONTH('2021-05-12 17:47:54');
-- 获取当前日期和时间 2021-05-12 17:43:34
SELECT NOW();
-- 返回一个时间的秒部分 54
SELECT SECOND('2021-05-12 17:47:54');
-- 返回一个日期时间的时间部分 17:47:54
SELECT TIME('2021-05-12 17:47:54');
-- 返回一个日期的年份部分 2021
SELECT YEAR('2021-05-12 17:47:54');

数值

函数说明
CEILING()向上取整
FLOOR()向下取整
FORMAT()保留小数位数(四舍五入)
SIN()正弦
COS()余弦
TAN()正切
ABS()绝对值
SQRT()平方根
MOD()余数
EXP()指数
PI()圆周率
RAND()随机数
-- 向上取整 2
SELECT CEILING(1.1) FROM DUAL;
-- 向下取整 1
SELECT FLOOR(1.1) FROM DUAL;
-- 保留小数位数 1.23
SELECT FORMAT(1.2253, 2) FROM DUAL;

加密函数

函数说明
USER()查看登录到 mysql 的有哪些用户
DATABASE()查看当前使用数据库名称
MD5()加密函数
PASSWORD()加密函数
-- 查看登录到 mysql 的有哪些用户 root@localhost
SELECT USER() FROM DUAL;
-- 查看当前使用数据库名称 test
SELECT DATABASE();
-- 为字符串算出一个 MD5 32 的字符串，常用(用户密码)加密 a92297e6d0aa8a8a8de4577bac57b151
SELECT MD5('MaRui') FROM DUAL;
-- 加密函数，mysql数据库的用户密码就是这个加密的 *DD96FABBE22AD37B00F42800BA28217901253AD3
SELECT PASSWORD('MaRui') FROM DUAL;

流程控制函数

-- IF(expr1, expr2, expr3)如果expr1为真返回expr2，否则返回expr3 真
SELECT IF(TRUE, '真', '假') FROM DUAL;
-- IFNULL(expr1, expr2)如果expr1不为null，返回expr1，否则返回expr2 MaRui
SELECT IFNULL(NULL, 'MaRui') FROM DUAL;
-- 对一个值进行多分支判断
SELECT
CASE 'apple'
	WHEN 'banana' THEN '香蕉'
	WHEN 'apple' THEN '苹果'
	ELSE'水果'
	END;

分组

分组就是把具有相同的数据值的行放在同一组中。

可以对同一分组数据使用汇总函数进行处理，例如求分组数据的平均值等。

指定的分组字段除了能按该字段进行分组，也会自动按该字段进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col;

GROUP BY 自动按分组字段进行排序，ORDER BY 也可以按汇总字段来进行排序。

SELECT col, COUNT(*) AS num
FROM mytable
GROUP BY col
ORDER BY num;

WHERE 过滤行，HAVING 过滤分组，行过滤应当先于分组过滤。

SELECT col, COUNT(*) AS num
FROM mytable
WHERE col > 2
GROUP BY col
HAVING num >= 2;

分组规定:

  • GROUP BY 子句出现在 WHERE 子句之后，ORDER BY 子句之前

  • 除了汇总字段外，SELECT 语句中的每一字段都必须在 GROUP BY 子句中给出

  • NULL 的行会单独分为一组

  • 大多数 SQL 实现不支持 GROUP BY 列具有可变长度的数据类型

子查询

子查询中只能返回一个字段的数据。 可以将子查询的结果作为 WHRER 语句的过滤条件:

SELECT *
FROM mytable1
WHERE col1 IN (SELECT col2
               FROM mytable2);

下面的语句可以检索出客户的订单数量，子查询语句会对第一个查询检索出的每个客户执行一次:

SELECT cust_name, (SELECT COUNT(*)
                   FROM Orders
                   WHERE Orders.cust_id = Customers.cust_id)
                   AS orders_num
FROM Customers
ORDER BY cust_name;

连接

  • 内连接：内连接又称等值连接，使用 INNER JOIN 关键字

  • 自连接：自连接可以看成内连接的一种，只是连接的表是自身而已

  • 自然连接：NATURAL JOIN，自然连接是把同名列通过等值测试连接起来的，同名列可以有多个

  • 外连接：外连接保留了没有关联的那些行。分为左外连接，右外连接以及全外连接，左外连接就是保留左表没有关联的行

贡献者: MaRui