MySql教程

【MySQL_学习笔记】2021.8.20

本文主要是介绍【MySQL_学习笔记】2021.8.20,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
  • 全连接与CASE
SELECT student.s_id,s_name,c_name,score
    FROM student INNER JOIN course
                 LEFT JOIN score ON score.s_id = student.s_id AND score.c_id = course.c_id
    ORDER BY student.s_id
SELECT student.s_id,s_name,c_name,IF(score>=60,'及格','不及格')
    FROM student INNER JOIN course
                 LEFT JOIN score ON score.s_id = student.s_id AND score.c_id = course.c_id     
    ORDER BY student.s_id
SELECT student.s_id,s_name,c_name,IF(score is NULL,'缺考',score) 
    FROM student INNER JOIN course
                 LEFT JOIN score ON score.s_id = student.s_id AND score.c_id = course.c_id     
    ORDER BY student.s_id
SELECT student.s_id,s_name,c_name,IFNULL(score,'缺考')
    FROM student INNER JOIN course
                 LEFT JOIN score ON score.s_id = student.s_id AND score.c_id = course.c_id     
    ORDER BY student.s_id

备注:

(1)IF 关键字的作用类似excel中的IF公式,它根据条件来输出字段的值

(2)Null与任何数字比较的结果,都是Null,而非True或False;而If关键字规定:如果比较结果为Null,则相当于结果为False,所以返回第二个值

(3)IF(字段X IS NULL, a, 字段X) 在 MySQL 中可以替换为:IFNULL(字段X, a)

SELECT student.s_id,
       s_name,
       c_name,
       (CASE WHEN score>85 THEN '优秀'
             WHEN score>=60 THEN '及格'
             WHEN score IS NULL THEN '缺考'
             ELSE '不及格'
        END) AS '评级'
    FROM student INNER JOIN course
                             LEFT JOIN score ON score.s_id = student.s_id AND score.c_id = course.c_id     
    ORDER BY student.s_id

CASE 语句分为简略格式与推荐使用的搜索模式,语法格式为:

CASE
    WHEN 情况1 THEN A
    WHEN 情况2 THEN B
    WHEN 情况3 THEN C
    ELSE D
END
这篇关于【MySQL_学习笔记】2021.8.20的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!