SQL函数（持续更新函数的应用）

妍妍的宝贝

已于 2024-05-16 17:36:02 修改

阅读量687

点赞数 11

分类专栏： MySQL 文章标签：数据库 mysql

于 2024-05-15 16:17:46 首次发布

本文链接：https://blog.csdn.net/weixin_68398469/article/details/138912731

版权

MySQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

1、COUNT(*) 和 COUNT(1) 和 COUNT(字段名) 三者区别

聚合函数是指对一组值进行运算，最终返回是单个值。也可以被称为 组合函数
COUNT()     统计目标行数量的函数
AVG()       求平均值
SUM()       求合
MIN()       求最大值
MAX()       求最小值
PS: 除COUNT函数之外，其他的聚合函数都会忽略NULL值
配套的示例 详见前文

**1、COUNT(*) 和 COUNT(1) 和 COUNT(字段名) 三者区别**

COUNT(*)  和 COUNT(1) 
当表数据量较大时，对表进行检索， count(1) 时效要比  count(*)  慢
当表数据量较小时，对表进行检索， count(1)  时效要比  count(*) 快
count(1) 聚索引状 
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(主键列) .....

2、数值型函数

数值型函数

函数名称	作用
ABS()	求绝对值
SQRT()	求平方根
POW()或POWER()	返回参数的幂次方
MOD()	求余数
CEIL()或CEILING()	向上取整
FLOOR()	向下取整
ROUND()	四舍五入
RAND	随机生成一个数字（0-1) 之间

#随机生成 0-----99999的数字
#1随机生成一个数字 (0-1)之间
    SELECT RAND();
#2将生成的随机数*100000
    SELECT RAND()*100000;
#3对结果进行FLOOR向下取整
    SELECT FLOOR(RAND()*100000);

3、字符串函数

函数名称	作用
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)	返回最后一次出现目标字符串的索引位

4、日期函数

函数名称	作用
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)	返回两个日期之间的相差天数

#查询A学生和当前时间的天数差
SELECT DATEDIFF(NOW(),(SELECT BornDate FROM student WHERE StudentName = '张三'));

#根据生日查询其年龄
SELECT FLOOR(DATEDIFF(NOW(),(SELECT BornDate FROM student WHERE StudentName = '张三'))/365)  AS "时差";

5、流程控制函数

函数名称	作用
IF(条件,结果1,结果2)	判断，如果条件=true 返回结果1 反之返回结果2
CASE	搜索函数
IFNULL(value1,value2)	判断，如果value1不为NULL 则函数返回值就是value1 反之返回value2

#示例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 ->搜索函数
CASE<表达式>
    WHEN<值1>  THEN<结果1>
    WHEN<值2>  THEN<结果2>
    WHEN<值3>  THEN<结果3>
    WHEN<值4>  THEN<结果4>
ELSE <默认结果>
END
#需求 查询成绩表  限定考试科目 高等数学-1 
#    要求如下   如果学号是1000   显示成绩为原成绩的 1.5倍
#    要求如下   如果学号是1001   显示成绩为原成绩的 1.3倍
#    要求如下   如果学号是1002   显示成绩为原成绩的 1.1倍
#    要求如下   其他学生成绩显示原成绩

#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 1000 THEN StudentResult*1.5
    WHEN 1001 THEN StudentResult*1.3
    WHEN 1002 THEN StudentResult*1.1
    ELSE StudentResult
    END "修改后的成绩"
FROM result 
WHERE SubjectNo = (SELECT SubjectNo FROM subject WHERE SubjectName = '高等数学-1');

#练习 为所有成绩进行评分 要求体现的内容有 学生姓名,参考科目,考试成绩,综合评分(>=90优 >=80良好 >=70中等 >=60较差 不及格)

#通过内连接 完成3表的关联  学生  成绩  科目
#步骤1、得到学生姓名 参考科目  考试成绩
SELECT s.StudentName "学生姓名",su.SubjectName "参考科目",r.StudentResult "考试成绩",
CASE 
    WHEN r.StudentResult>=90 THEN "优秀"
    WHEN r.StudentResult>=80 THEN "良好"
    WHEN r.StudentResult>=70 THEN "中等"
    WHEN r.StudentResult>=60 THEN "较差"
    ELSE "不及格"
    END "综合评分"
FROM student s INNER JOIN result r ON s.StudentNo = r.StudentNo
INNER JOIN subject su ON su.SubjectNo = r.SubjectNo;