SQL 中有许多常用的函数，可以用于处理和操作数据。以下是一些常见的SQL 函数：
1. 字符串函数：
- CONCAT(str1, str2, …): 用于把多个文本字符串合并成一个长字符串(参数中有null时返回null)。
select concat('一起','学', 'SQL');
-- 输出结果:'一起学SQL'
select concat('一起','学',null,'SQL');
-- 输出结果:''
- UPPER(str) 将字符串转换为大写。
select upper('sql');
-- 输出结果:'SQL'
select upper('sQl');
-- 输出结果:'SQL'
- LOWER(str): 将字符串转换为小写。
select lower('SQL');
-- 输出结果:'sql'
select lower('sQl');
-- 输出结果:'sql'
- TRIM(str)： 去除字符串两端空格。
select trim(' 一起学SQL ');
-- 输出结果:'一起学SQL'
- LTRIM(str): 去除字符串左端的空格。
select ltrim(' 一起学SQL ');
-- 输出结果:'一起学SQL '
- RTRIM(str):: 去除字符串右端的空格。
select rtrim(' 一起学SQL ');
-- 输出结果:' 一起学SQL'
- LEFT(str,length)： 返回字符串的左侧指定长度的部分。
select left('一起学SQL',3);
-- 输出结果:'一起学'
- RIGHT(str,length): 返回字符串的右侧指定长度的部分。
select right('一起学SQL',3);
-- 输出结果:'SQL'
- MID(str, start, length): 返回字符串的指定起始位置和长度的部分。
select mid('一起学SQL',3,3);
-- 输出结果:'学SQ'
- INSTR(str,substr)： 返回子字符串substr在文本字符串str中第一次出现的位置(文本字符串中不包含该子字符串时返回0)
select instr('EABCDDBCA','A');
-- 输出结果:'2'
- LENGTH(str): 返回字符串的长度。
select length('sql');
-- 输出结果:'3'
- SUBSTRING(str, start, length): 返回字符串的子字符串。
str 是要操作的字符串。
start 是子字符串的起始位置，索引从 1 开始。
length 是要提取的子字符串的长度。
注： SUBSTRING 函数在提取子字符串时，索引是基于字符位置的，索引从 1 开始而不是从 0 开始。如果提供了负数的起始位置，则表示从字符串末尾开始计数。
SELECT SUBSTRING('一起学SQL', 4, 3);
-- 输出结果:'SQL'
SELECT SUBSTRING('一起学SQL', -3);
-- 输出结果:'SQL'
- **REPEAT(str, num): **返回重复多次的字符串。
SELECT REPEAT('SQL', 3);
-- 返回结果： 'SQLSQLSQL'
- **REVERSE(str): **反转字符串。
SELECT reverse('SQL');
-- 返回结果： 'LQS'
- REPLACE(str, old_str, new_str): 替换字符串中的子字符串。
SELECT replace('一起学SQL','SQL','sql');
-- 返回结果： '一起学sql'
2. 数学函数：
- ABS(num): 返回一个数的绝对值。
select abs(-15);
-- 返回结果：'15'
- ROUND(num, d): 返回保留d位小数(d的默认值为0)的四舍五入值
select round(3.1415926,3);
-- 返回结果：'3.142'
- CEILING(num) ： 向上取整，返回大于或等于给定数字 x 的最小整数。
SELECT ceiling(5.3);
-- 返回结果：'6'
- **FLOOR(num)😗*向下取整，返回小于或等于给定数字 x 的最大整数。
SELECT floor(5.3);
-- 返回结果：'5'
- RAND(): 返回在范围0到1.0内的随机浮点值
select rand();
-- 返回结果：'0.7459539144727904'
3. 日期时间函数：
3.1提取
- NOW(): 返回当前日期和时间。
返回值所处上下文是字符串：以 ‘yyyy-mm-dd hh:mm:ss’ 格式返回当前日期时间
返回值所处上下文是数字：以 ‘yyyymmddhhmmss’ 格式返回当前日期时间
select now();
-- 返回结果：'2024-05-20 15:21:08'
select now()+0;
-- 返回结果：'20240520152119'
- CURDATE(): 返回当前日期。
select curdate();
-- 返回结果：'2024-05-20'
- CURTIME(): 返回当前时间。
select curtime();
-- 返回结果：'16:10:49'
- DATE(): 提取日期部分。
SELECT DATE(‘2024-05-20 12:34:56’);
select curtime();
-- 返回结果：'16:10:49'
- TIME(): 提取时间部分。
select time('2024-05-20 12:34:56');
-- 返回结果：'12:34:56'
- WEEK(date,n): 提取指定日期是一年中的第几周。
①默认(n为0或空值)：
周从周日开始，周六结束。
1月1日所在的周被认为是第1周。
如果1月1日是周四或更晚，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
②n=1：
周从周一开始，周日结束。
1月1日所在的周被认为是第1周。
如果1月4日是周一或更早，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
③n=2：
周从周日开始，周六结束。
1月1日所在的周被认为是第1周。
如果1月4日是周一或更早，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
④n=3：
周从周一开始，周日结束。
1月1日所在的周被认为是第1周。
如果1月1日是周四或更晚，该周被认为是一年中的第1周；否则，它被认为是一年中的最后一周。
select week('2024-05-20 12:34:56');
-- 返回结果：'20'
select week('2024-05-20 12:34:56',1);
-- 返回结果：'21'
- YEAR(): 提取年份。
select year('2024-05-20 12:34:56');
-- 返回结果：'2024'
- QUARTER(): 提取季度。
select quarter('2024-05-20 12:34:56');
-- 返回结果：'2'
- MONTH(): 提取月份。
select month('2024-05-20 12:34:56');
-- 返回结果：'5'
- DAY(): 提取日期中的天数。
select day('2024-05-20 12:34:56');
-- 返回结果：'20'
- HOUR(): 提取小时部分。
select hour('2024-05-20 12:34:56');
-- 返回结果：'12'
- MINUTE(): 提取分钟部分。
select minute('2024-05-20 12:34:56');
-- 返回结果：'34'
- SECOND(): 提取秒数部分。
select second('2024-05-20 12:34:56');
-- 返回结果：'56'
3.2加减运算
DATE_ADD(date,interval expr type)
ADDDATE(date,interval expr type)
DATE_SUB(date,interval expr type)
SUBDATE(date,interval expr type)
- date是一个datetime或date值
- expr对date进行加减法的一个表达式字符串
- type指明表达式expr应该如何展示
- DATE_ADD(date, INTERVAL expr type) 、 ADDDATE(date, INTERVAL expr type)： 都用于在日期上执行加法操作。可以根据需要选择使用其中之一。
select date_add('2024-05-20 12:34:56',INTERVAL 1 day);
-- 返回结果：'2024-05-21 12:34:56'
select adddate('2024-05-20 12:34:56',INTERVAL 1 day);
-- 返回结果：'2024-05-21 12:34:56'
- DATE_SUB(date, INTERVAL expr type) 和 SUBDATE(date, INTERVAL expr type)： 都用于在日期上执行减法操作。可以根据需要选择使用其中之一。
select date_sub('2024-05-20 12:34:56',INTERVAL 1 day);
-- 返回结果：'2024-05-19 12:34:56'
select subdate('2024-05-20 12:34:56',INTERVAL 1 day);
-- 返回结果：'2024-05-19 12:34:56'
- DATEDIFF(date1, date2): 返回两个日期之间的天数差。
select datediff('2024-05-20', '2024-05-10');
-- 返回结果：'10'
3.3格式化日期
- **DATE_FORMAT(date, format): **根据format字符串格式化date值。
在format字符串中可用标志符
年份:
%Y: 4 位的年份 (例如: 2024)
%y: 2 位的年份 (例如: 24)
月份:
%m: 2 位的月份 (01-12)
%c: 月份 (1-12)
%M: 月份名称 (January - December)
%b: 缩写的月份名称 (Jan - Dec)
日期:
%d: 2 位的日期 (01-31)
%e: 日期 (1-31)
时间:
%H: 24 小时制的小时 (00-23)
%h: 12 小时制的小时 (01-12)
%I: 12 小时制的小时 (01-12)
%k: 24 小时制的小时 (0-23)
%l: 12 小时制的小时 (1-12)
%i: 分钟 (00-59)
%s: 秒 (00-59)
%f: 微秒 (000000-999999)
%p: AM 或 PM
其他:
%w: 一周中的第几天 (0=周日, 1=周一, …, 6=周六)
%a: 缩写的星期名称 (Sun - Sat)
%W: 完整的星期名称 (Sunday - Saturday)
%j: 一年中的第几天 (001-366)
%T: 24 小时制的时间 (hh:mm:ss)
%r: 12 小时制的时间 (hh:mm:ss AM/PM)
%D: 带有英文后缀的日期 (1st, 2nd, 3rd, …)
这些格式化符号可以组合使用, 以满足不同的日期和时间显示需求。
select date_format('2024-05-20 12:34:56','%Y%M');
-- 返回结果：'2024May'
select date_format('2024-05-20 12:34:56','%Y%m%d');
-- 返回结果：'20240520'
3.4时间戳
- UNIX_TIMESTAMP(): 返回一个unix时间戳(从’1970-01-01 00:00:00’开始的秒数,date默认值为当前时间)
select unix_timestamp();
-- 返回结果：'1716196161'
- FROM_UNIXTIME(unix_timestamp): 以’yyyy-mm-dd hh:mm:ss’或yyyymmddhhmmss格式返回时间戳的值。
select from_unixtime(1716196161);
-- 返回结果：'2024-05-20 17:09:21'
4.转换函数
CAST(expression AS data_type): 用于将某种数据类型的表达式显式转换为另一种数据类型。
- expression：任何有效的SQServer表达式。
- AS：用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
- data_type：目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。
可以转换的类型： - 二进制，同带binary前缀的效果 : BINARY
- 字符型，可带参数 : CHAR()
- 日期 : DATE
- 时间: TIME
- 日期时间型 : DATETIME
- 浮点数 : DECIMAL
- 整数 : SIGNED
- 无符号整数 : UNSIGNED
select cast('10.0' AS decimal);
-- 返回结果：'10'
5.逻辑函数：
- IF(condition, value_if_true, value_if_false): 根据条件返回不同的值。
select ename,sal,if(sal>=3000,'高',if(sal>=1500,'中','低')) 工资级别 from emp;
+--------+------+--------------+
| ename | sal | 工资级别 |
+--------+------+--------------+
| smith | 800 | 低 |
| allen | 1600 | 中 |
| ward | 1250 | 低 |
| jones | 2975 | 中 |
| martin | 1250 | 低 |
| blake | 2850 | 中 |
| clark | 2450 | 中 |
+--------+------+--------------+
- IFNULL(val, default_val): 如果值为 NULL，则返回默认值。如果不为 NULL 则返回第一个参数的值。
select ifnull(销量,0) from emp;
+----------------+
| ifnull(销量,0) |
+----------------+
| 0 |
| 123|
| 1000 |
| 0 |
| 500 |
+----------------+
- CASE WHEN expr1 THEN expr2 [WHEN expr3 THEN expr4…ELSE expr] END: 如果expr1的值为true，则返回expr2的值，如果expr3的值为false，则返回expr4的值…
select ename,sal,case when sal>=3000 then '高' when sal>=1500 then '中' else '低' end 工资级别 from emp;
+--------+------+--------------+
| ename | sal | 工资级别 |
+--------+------+--------------+
| smith | 800 | 低 |
| allen | 1600 | 中 |
| ward | 1250 | 低 |
| jones | 2975 | 中 |
| martin | 1250 | 低 |
| blake | 2850 | 中 |
| clark | 2450 | 中 |
| scott | 3000 | 高 |
| king | 5000 | 高 |
| turner | 1500 | 中 |
| adams | 1100 | 低 |
| james | 950 | 低 |
| ford | 3000 | 高 |
| miller | 1300 | 低 |
+--------+------+--------------+
- COALESCE(val1, val2, …): 返回参数列表中第一个非 NULL 值。
select coalesce(NULL,'a',NULL,'c');
-- 返回结果：'a'
6.聚合函数：
聚合函数用于对一组行进行计算并返回单个值作为结果。可以与 GROUP BY 子句结合使用，用于按特定条件对数据进行分组计算。通常用于数据分析和报告中，帮助汇总数据并进行统计计算。
以下是一些常用的 SQL 聚合函数：
- COUNT(): 统计行数或非 NULL 值的数量。
- SUM() : 求和。
- AVG(): 求平均值。
- MAX() : 求最大值。
- MIN() : 求最小值。
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) FROM table_name WHERE condition;
SELECT SUM(column_name),AVG(column_name),MAX(column_name),MIN(column_name) FROM table_name;
- GROUP_CANCAT([distinct] str [order by str asc/desc] [separator]): 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
-- 查询每个部门的员工姓名
select deptno,group_concat(name) from emp group by deptno;
+--------+--------------------------------------+
| deptno | name |
+--------+--------------------------------------+
| 10 | clark,king,miller |
| 20 | smith,jones,scott,adams,ford |
| 30 | allen,ward,martin,blake,turner,james |
+--------+--------------------------------------+
3550
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
