本文介绍了SQL中的加号操作及其在数值和字符串类型中的应用，详细讲解了MySQL中的单行函数，包括数学、字符串、日期时间及流程控制函数的使用。特别提到了日期时间函数如DATE_ADD和DATE_FORMAT，以及分组函数如COUNT、MAX、MIN、SUM和AVG的用法。总结了COUNT函数的不同用法，并强调了NULL值在聚合函数中的处理。
一、SQL中的 + 号操作
加号的作用:
a.java中+的作用：
- ①加法运算：两个操作数都是数值型
- ②拼接运算：只要有一个操作数为String类型
b.sql中+的作用：加法运算
- 1)两个操作数都是数值 ，直接作加法运算
- 2)至少有一个操作数为字符，强制将字符进行转换
- 如果转换数值成功,则继续加法运算——字符本身就是个数字，如‘123’
- 如果转换数值失败，则将该字符转换成0，然后再作加法运算
- 3)操作数中含有null，则结果直接为null;
select '123' + 100; # 223
SELECT 'abc' + 100; # 100
select null + 123; # null
二、单行函数
MySQL数据库提供了很多函数包括：
- 数学函数；
- 字符串函数；
- 日期和时间函数；
- 条件判断函数；
- 流程控制函数；
- 系统信息函数；
- 加密函数；
- 格式化函数；
函数之间允许嵌套
1.数学函数
| 函数 | 功能 |
|---|---|
| ABS（x） | 返回x的绝对值 |
| CEIL（x） | 返回大于x的最小整数值 |
| FLOOR（x） | 返回大于x的最大整数值 |
| MOD(x,y) | 返回x/y的模 |
| RAND(x) | 返回0~1的随机值 |
| ROUND(x,y) | 返回参数x的四舍五入的有y位的小数的值 |
| TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
| SQRT(x) | 返回x的平方根 |
| POW(x,y) | 返回x的y次方 |
2.字符串函数
| 函数 | 功能 |
|---|---|
| CONCAT(S1,S2,…,Sn) | 连接S1,S2,…,Sn为一个字符串 |
| CONCAT_WS(s, S1,S2,…,Sn) | 同CONCAT(s1,s2,…)函数，但是每个字符串之间要加上s |
| CHAR_LENGTH(s) | 返回字符串s的字符数 |
| LENGTH(s) | 返回字符串s的字节数，和字符集有关 |
| INSERT(str, index , len, instr) | 将字符串str从第index位置开始，len个字符长的子串替换为字符串instr |
| INSTR(str1,str2) | 获取子串str2在str1中第一次出现的位置，没有返回 0 |
| UPPER(s) 或 UCASE(s) | 将字符串s的所有字母转成大写字母 |
| LOWER(s) 或LCASE(s) | 将字符串s的所有字母转成小写字母 |
| LEFT(s,n) | 返回字符串s最左边的n个字符 |
| RIGHT(s,n) | 返回字符串s最右边的n个字符 |
| LPAD(str, len, pad) | 用字符串pad对str最左边进行填充，直到str的长度为len个字符 |
| RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充，直到str的长度为len个字符 |
| LTRIM(s) | 去掉字符串s左侧的空格 |
| RTRIM(s) | 去掉字符串s右侧的空格 |
| TRIM(s) | 去掉字符串s开始与结尾的空格 |
| TRIM([BOTH] s1 FROM s) | 去掉字符串s开始与结尾的s1 |
| TRIM(LEADING s1 FROM s) | 去掉字符串s开始处的s1 |
| TRIM(TRAILING s1 FROM s) | 去掉字符串s结尾处的s1 |
| REPEAT(str, n) | 返回str重复n次的结果 |
| REPLACE（str, a, b） | 用字符串b替换字符串str中所有出现的字符串a |
| STRCMP(s1,s2) | 比较字符串s1,s2 |
| SUBSTRING(s,index,len) | 返回从字符串s的index位置其len个字符 |
注意：
- SQL中索引开始位是 1 （Limit 从 0 开始）
- SQL中长度都是指字符数，除了LENGTH()函数是指字节数外
- concat:拼接字符，但如果其中一个参数为null，则结果为null
3.日期时间函数
| 函数 | 功能 |
|---|---|
| CURDATE() 或 CURRENT_DATE() | 返回当前日期 |
| CURTIME() 或 CURRENT_TIME() | 返回当前时间 |
| NOW() | 返回当前系统（日期+时间） |
| SYSDATE() | |
| CURRENT_TIMESTAMP() | |
| LOCALTIME() | |
| LOCALTIMESTAMP() | |
| YEAR(date) | 返回具体的时间值 |
| MONTH(date) | |
| DAY(date) | |
| HOUR(time) | |
| MINUTE(time) | |
| SECOND(time) | |
| WEEK(date) | 返回一年中的第几周 |
| WEEKOFYEAR(date) | |
| DAYOFWEEK() | 返回周几 注意：周日是1，周一是2，。。。周六是7 |
| WEEKDAY(date) | 返回数值周几 注意：周1是0，周2是1，。。。周日是6 |
| DAYNAME(date) | 返回星期：MONDAY,TUESDAY…SUNDAY |
| MONTHNAME(date) | 返回月份：January，… |
| DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔 |
| TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
| DATE_ADD(datetime, INTERVALE expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
| DATE_FORMAT(datetime ,fmt) | 按照字符串fmt格式化日期datetime值 |
| STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析，解析为一个日期 |
1.DATE_ADD(datetime,INTERVAL expr type)
# 当前时间： 2019-08-16
SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR); # 2020-08-16 21:14:23
SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR); # 2018-08-16 21:15:22
SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH); # 2020-09-16 21:30:32
| 表达式类型-type | YEAR_MONTH |
|---|---|
| YEAR | DAY_HOUR |
| MONTH | DAY_MINUTE |
| DAY | DAY_SECOND |
| HOUR | HOUR_MINUTE |
| MINUTE | HOUR_SECOND |
| SECOND | MINUTE_SECOND |
2.DATE_FORMAT(datetime ,fmt)和STR_TO_DATE(str, fmt)
| 格式符 | 说明 | 格式符 | 说明 |
|---|---|---|---|
| %Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
| %M | 月名表示月份（January,…） | %m | 两位数字表示月份（01,02,03。。。） |
| %b | 缩写的月名（Jan.，Feb.，…） | %c | 数字表示月份（1,2,3,…） |
| %D | 英文后缀表示月中的天数（1st,2nd,3rd,…） | %d | 两位数字表示月中的天数(01,02…) |
| %e | 数字形式表示月中的天数（1,2,3,4,5…） | ||
| %H | 两位数字表示小数，24小时制（01,02…） | %h和%I | 两位数字表示小时，12小时制（01,02…） |
| %k | 数字形式的小时，24小时制(1,2,3) | %l | 数字形式表示小时，12小时制（1,2,3,4…） |
| %i | 两位数字表示分钟（00,01,02） | %S和%s | 两位数字表示秒(00,01,02…) |
| %W | 一周中的星期名称（Sunday…） | %a | 一周中的星期缩写（Sun.，Mon.,Tues.，…） |
| %w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
| %j | 以3位数字表示年中的天数(001,002…) | %U | 以数字表示年中的第几周，（1,2,3。。）其中Sunday为周中第一天 |
| %u | 以数字表示年中的第几周，（1,2,3。。）其中Monday为周中第一天 | ||
| %T | 24小时制 | %r | 12小时制 |
| %p | AM或PM | %% | 表示% |
4.*流程函数
| 函数 | 功能 |
|---|---|
| IF(value,t ,f) | 如果value是真，返回t，否则返回f |
| IFNULL(value1, value2) | 如果value1不为null，返回value1，否则返回value2 |
| CASE WHEN | 语句判断 |
1.Case 条件判断
- 相当于Java的if…else if…
CASE
WHEN 条件1 THEN result1
WHEN 条件2 THEN result2
....
[ELSE resultn]
END
2.Case 常量匹配
- 相当于Java的switch
CASE expr
WHEN 常量值1 THEN 值1
WHEN 常量值1 THEN 值1
....
[ELSE 值n]
END
注意：如果result为字符常量时，需要加单引号
三、多行函数
1.分组(聚合)函数
聚合函数是用来做纵向运算的函数：
- COUNT()：统计指定列不为NULL的记录行数；
- MAX()：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
- MIN()：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
- SUM()：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
- AVG()：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
count()用法：
- 1）count(字段名):返回该列中不为NULL的记录行数
- 2)count(*)：返回查询出的所有记录(结果集)的行数
- 3)count(value):加入一列，这列值全部为value，并返回该列行数（等同于count(*)）
- 4)count(distinct 字段名)：返回去重的结果集的行数
注意：
- null不参与聚合函数的计算；
- 分组函数不能随意与任意字段放在同一select语句中；
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
