目录
一、聚合函数
定义：聚合函数是指对一组值进行运算，最终返回是单个值，也可以被称为组合函数。
COUNT() 统计目标行数量的函数
AVG() 求平均值
SUM() 求合
MIN() 求最小值
MAX() 求最大值备注：除COUNT函数之外，其他的聚合函数都会忽略NULL值
补充知识点：COUNT(*) 和 COUNT(1) 和 COUNT(字段名) 三者区别
COUNT(*) 和 COUNT(1)
当表数据量较大时，对表进行检索，count1 时效要比 count* 慢
当表数据量较小时，对表进行检索，count1 时效要比 count* 快
count1 聚索引状
count* 自动选择索引
结论：这两个 通常 不予比较
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
COUNT(1) 和 COUNT(字段)
count1会统计表中所有的记录数,包含了字段为NULL的记录
count字段会忽略当前字段中出现null的情况,如果出现null值，不统计这条记录
三者区别
1.count(*) 包含了所有列，相当于所有行记录，在统计结果时不忽略NULL
2.count(1) 包含了所有的忽略列，用1表示代码行，在统计结果时不忽略NULL
3.count(字段) 只会包含具有列名的那一列，在统计结果时会忽略NULL
在执行效率上
如果列名<===>主键列
count(字段)>count(*)>count(1)
如果列名!<===>主键列
count(*)||count(1)>count(字段)
如果表多列都无主键
count(1)>count(*)>count(字段)
执行效率最高的
SELECT COUNT(主键列) .....二、数值型函数
| 函数名称 | 作用 |
|---|---|
| ABS() | 求绝对值 |
| SQRT() | 求平方根 |
| POW()或POWER() | 返回参数的幂次方 |
| MOD() | 求余数 |
| CEIL()或CEILING() | 向上取整 |
| FLOOR() | 向下取整 |
| ROUND() | 四舍五入 |
| RAND | 随机生成一个数字 （0-1) 之间 |
eg：
#随机生成 0-----99999的数字
#1随机生成一个数字 （0-1) 之间
SELECT RAND();
#2将生成的随机数*100000
SELECT RAND()*100000;
#3对结果进行FLOOR向下取整
SELECT FLOOR(RAND()*100000);三、字符串函数
| 函数名称 | 作用 |
|---|---|
| LENGTH() | 返回字符串长度 |
| CHAR_LENGTH() | 返回字符串的字节长度 |
| CONCAT() | 合并字符串长度,返回结果为连接后新生成的字符串，参数可以是一个或多个 |
| INSERT(str,pos,len,newstr) | 替换字符串函数 |
| LOWER() | 将字符串内所有的字符转小写 |
| UPPER() | 将字符串中所有的字符转大写 |
| LEFT(str,len) | 从字符串左侧进行截取，返回字符串左边若干长度的字符 |
| RIGHT(str,len) | 从字符串右侧进行截取，返回字符串右边若干长度的字符 |
| TRIM() | 删除字符串两次空格 |
| REPLACE(str,l1,l2) | 字符串替换函数,返回替换后的新字符串 |
| SUBSTRING(str,s,len) | 截取字符串，返回从指定位置开始指定长度的字符串 |
| REVERSE() | 字符串逆序函数,返回余元字符串顺序相反的字符串 |
| STRCMP(str1,str2) | 比较两个表达式的顺序，如果str1小于str2返回 -1 0相等 1大于 |
| LOCATE(substr,str) | 返回第一次出现目标字符串的索引位 |
| INSTR(substr,str) | 返回最后一次出现目标字符串的索引位 |
四、日期函数
| 函数名称 | 作用 |
|---|---|
| CURDATE() CURRENT_DATE() CURRENT_DATE | 返回当前系统的日期值 |
| CURTIME() CURRENT_TIME() CURRENT_TIME | 返回当前系统的时间 |
| NOW() SYSDATE() | 返回当前系统的日期及时间 |
| DATE(PAREM) | 返回指定对象的日期部分 |
| TIME(PAREM) | 返回指定对象的时间部分 |
| YEAR(PAREM) | 返回指定对象的年份(1970--2069) |
| MONTH(PAREM) | 返回指定对象的月份 |
| DAY(PAREM) | 返回指定对象的日期 |
| DAYOFWEEK(PAREM) | 获取指定日期对应的一周的索引位置值，也就是星期数，注意周日是开始日，为1 |
| WEEK(PAREM） | 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 |
| DATEDIFF(PAREM,PAREM) | 返回两个日期之间的相差天数 |
eg：
#查询A学生和当前时间的天数差
SELECT DATEDIFF(NOW(),(SELECT BornDate FROM student WHERE StudentName = '张三'));
#根据生日查询其年龄
SELECT FLOOR(DATEDIFF(NOW(),(SELECT BornDate FROM student WHERE StudentName = '张三'))/365) AS "时差";五、流程控制函数
| 函数名称 | 作用 |
|---|---|
| IF(条件,结果1,结果2) | 判断，如果条件=true 返回结果1 反之 返回结果2 |
| CASE | 搜索函数 |
| IFNULL(value1,value2) | 判断，如果value1不为NULL 则函数返回值就是value1 反之 返回value2 |
eg:
#示例1
SELECT IF(12,2,3);
SELECT IF(1<2,'YES','NO');
SELECT IF(STRCMP('TEST001','TEST001'),'NO','YES');
条件内 结果 true(非0的自然数) false(0)
#示例2 分别显示学生信息，有邮箱和没有邮箱的备注信息
SELECT StudentName "学生姓名",IF(Email IS NULL,'没有邮箱','存在邮箱') "是否具有邮箱"
FROM student;
#示例3 使用IFNULL,函数入参两个,如果入参不为空则返回第一个值，否则返回第二个值
SELECT IFNULL(1,2),IFNULL(NULL,2),IFNULL(9/3,2);
SELECT StudentName "学生姓名",IFNULL(Email,'没有邮箱') "邮箱地址"
FROM student;
#示例4
CASE<表达式>
WHEN<值1> THEN<结果1>
WHEN<值2> THEN<结果2>
WHEN<值3> THEN<结果3>
WHEN<值4> THEN<结果4>
ELSE <默认结果>
END
#需求 查询成绩表 限定考试科目 高等数学-1
# 要求如下 如果学号是1 显示成绩为原成绩
# 要求如下 如果学号是2 显示成绩为原成绩的1.5倍
# 要求如下 如果学号是3 显示成绩为原成绩的2倍
# 要求如下 其他学生成绩显示原成绩
#1-查出高数-1的科目编号
SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-1';
#2-通过科目编号找到学生的考试成绩
SELECT * FROM result WHERE SubjectNo = (SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-1');
#3-通过CASE语法修改并查看参数
SELECT StudentNo "学号",StudentResult "原成绩",
CASE StudentNo
WHEN 1 THEN StudentResult*1
WHEN 2 THEN StudentResult*1.5
WHEN 3 THEN StudentResult*2
ELSE StudentResult
END "修改后的成绩"
FROM result
WHERE SubjectNo = (SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-1');
