数据库知识点补充_数据库查询郭靖的所有考试信息,打印学生学号,考试科目,成绩信息-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_74818238/article/details/135831465

4.1NULL值的使用

String str = null;     
String str = "";
null指的是  不可用、未分配的值
null不等于零或空格
任意数据类型都支持null这种表达形式
包括null的任何算数表达式结果都等于空
字符串和null进行连接运算，结果也是空

4.2补充点

<==>   安全等于  等价于  =  和  IS 两者的结合

示例1：查询学号为1001的学生信息
SELECT *
FROM student
WHERE StudentNo <==> 1001;    #  WHERE StudentNo = 1001;

示例2：查询邮箱为空的学生的信息
SELECT *
FROM student
WHERE Email <==> NULL;   #  WHERE Email IS NULL;

4.3定义字段的别名

SELECT StudentName,Address,Email 
FROM student;
+--------------+------------------------------------+--------------------+
| StudentName  | Address                            | Email              |
+--------------+------------------------------------+--------------------+
| 郭靖         | 北京海淀区中关村大街1号            | test1@bdqn.cn      |
| 李文才       | 广东广州天河区                     | test1@bdqn.cn      |
| 李斯文       | 天津市和平区                       | test1@bdqn.cn      |
| 武松         | 上海市金桥区                       | test1@bdqn.cn      |
| 张三         | 北京市通州                         | test1@bdqn.cn      |
| 张秋丽       | 广西桂林市灵川                     | test1@bdqn.cn      |
| 欧阳峻峰     | 北京东城区                         | NULL               |
| 梅超风       | 河南洛阳                           | NULL               |
| 赵敏         | 西安市雁塔区                       | NULL               |
| 李寻欢       | 西安市碑林区                       | litian@qq.com      |
| 赵尧林       | 西安市雁塔区新家坡3号楼            | zhaoyaolin@163.com |
+--------------+------------------------------------+--------------------+
#查询语句获取到的结果 是以伪表形式体现 

SELECT StudentName AS '学生姓名',Address AS '家庭住址',Email AS '电子邮箱' 
FROM student;

SELECT SubjectName "科目名",ClassHour "学习时长",ClassHour*(10+10) "计算后的学习时长"
FROM subject;

4.4祛除重复的记录

#我想查看学生表的性别
#缺省情况下查询显示所有行,包括重复行
SELECT sex "性别" 
FROM student;
#可以使用关键字DISTINCT清除查询记录中的重复数据
SELECT DISTINCT sex "性别" 
FROM student;

4.5WHERE 限制所选择的横向区域

WHERE中的字符串或日期格式的内容 
需要使用单引号进行专门的标识  如   StudentName = '张三'  而不能 直接  StudentName = 张三
字符串内的数据 对大小写是敏感的   如记录中有  Louis77@163.com   我们在检索时就不能  louis77@163.com
日期值对格式是敏感的   如记录中有  2000-01-01 00:00:00   我们在检索时就不能  2000年01月01日
#示例1：查询姓名是郭靖的学生信息
SELECT * 
FROM student 
WHERE StudentName = '郭靖';
#示例2：查询生日是1986-12-31的学生信息
SELECT *
FROM student
WHERE BornDate = '1986-12-31';
#示例3：查询学号是1000的学生信息
SELECT * 
FROM student
WHERE StudentNo = 1000;

#WHERE中的比较运算符  < >  <=  >=  != = 
#示例4：查询生日在2000-01-01之后的学生信息
SELECT *
FROM student
WHERE BornDate > '2000-01-01';
#WHERE中逻辑运算符  AND  OR  NOT
#AND需要所有条件都满足
#示例5：查询班级编号是1，并且生日在1980-01-01之后，并且性别是1的学生信息
SELECT *
FROM student
WHERE GradeId = 1 AND BornDate > '1980-01-01' AND sex = 1;
#OR只要满足多条件之一即可
#示例6：查询班级编号是1，或者生日在1980-01-01之后，或者性别是1的学生信息
SELECT *
FROM student
WHERE GradeId = 1 OR BornDate > '1980-01-01' OR sex = 1;
#NOT表示取反
#示例7：查询邮箱不为空的学生的姓名,邮箱地址
SELECT StudentName "姓名",Email "邮箱地址"
FROM student
WHERE Email IS NOT NULL;
#示例8：查询生日在2000-2010之间的学生姓名
SELECT StudentName "姓名"
FROM student
WHERE BornDate >= '2000-01-01' AND BornDate <= '2010-01-01';
#示例9：使用BETWEEN关键字实现范围查询
SELECT StudentName "姓名"
FROM student
WHERE BornDate BETWEEN '2000-01-01' AND '2010-01-01';
#示例10：查询 班级是1或2或3班的学生姓名
SELECT StudentName "姓名"
FROM student
WHERE GradeId = 1 OR GradeId = 3 OR GradeId = 2;
#示例11：使用IN关键字进行匹配
SELECT StudentName "姓名"
FROM student
WHERE GradeId IN (1,2,3);
#LIKE关键字
#该关键字主要用于执行模糊查询，查询条件可以包含文字字符或占位符
#通过%表示匹配0或多个字符
#_表示匹配一个字符
#示例12：查询学生姓名 姓名以周开始，后面字符数量不定
SELECT StudentName "姓名"
FROM student
WHERE StudentName LIKE '李%';
# LIKE '%周'  以周字结束
# LIKE '%周%' 包含周字
# LIKE '周_'  以周开始且后方匹配一个字符

4.6GROUP BY 分组查询

GROUP BY 字句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组.
分组的真正含义：把表中列值相同的多条记录，当成是一条记录进行处理,最终也只输出一条记录，分组函数忽略空值
语法：
SELECT {*,列名,函数} 
FROM 表名 
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]

#示例1：统计各班人数
SELECT COUNT(*) "人数",GradeID "班级编号"
FROM student
WHERE sex = 1
GROUP BY (GradeID);
#示例2：统计每个学生的考试总分,平均分，最高分,最低分
SELECT StudentNo "学号", SUM(StudentResult) "总分",AVG(StudentResult) "平均分",MAX(StudentResult) "最高分",MIN(StudentResult) "平最低分"
FROM result
GROUP BY (StudentNo);
#示例3：考试时间在 2012年01月01日后 统计每个学生的考试总分,平均分，最高分,最低分
SELECT StudentNo "学号", SUM(StudentResult) "总分",AVG(StudentResult) "平均分",MAX(StudentResult) "最高分",MIN(StudentResult) "平最低分"
FROM result
WHERE ExamDate >= '2012-01-01'
GROUP BY (StudentNo);
#示例4：考试时间在 2012年01月01日后 统计每个学生的考试总分,平均分，最高分,最低分 过滤掉 总分在650以下的
SELECT StudentNo "学号", SUM(StudentResult) "总分",AVG(StudentResult) "平均分",MAX(StudentResult) "最高分",MIN(StudentResult) "平最低分"
FROM result
WHERE ExamDate >= '2012-01-01'
GROUP BY (StudentNo)
HAVING  SUM(StudentResult) >= 650;

PS：分组函数的重要规则
1、如果 使用了分组函数，或使用了GROUP BY (字段1，字段2，...)执行查询，那么出现在SELECT 列表后的字段 要么必须是聚合函数,要么出现过在GRUOP字句内。
2、GRUOP BY子句的字段可以不出现在SELECT内。
3、使用聚合函数但不使用分组查询时，那么所有的数据会作为一组进行显示
4、GROUP BY前面的 WHERE 表示 分组前执行的条件过滤
5、GROUP BY后面的 HAVING表示 分组后执行的条件过滤

4.7ORDER BY 排序查询

SELECT {*,列名,函数} 
FROM 表名 
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]
[ORDER BY (需要排序的字段) ASC||DESC];  #ASC升序(升序)  DESC(降序)
#示例1、查询平均成绩在80以上的学生(学号)信息,同时成绩还需要按照降序排列
SELECT StudentNo "学号",AVG(StudentResult) "成绩"
FROM result
GROUP BY (StudentNo)
HAVING AVG(StudentResult) > 80;
ORDER BY(AVG(StudentResult)) DESC;

4.8LIMIT 区间查询

SELECT {*,列名,函数} 
FROM 表名 
[WHERE 基础条件]
[GROUP BY 分组条件]
[HAVING 过滤条件]
[ORDER BY (需要排序的字段) ASC||DESC]  #ASC升序(升序)  DESC(降序)
[LIMIT A,B];

LIMIT 连续区间查询
LIMIT A,B      A表示需要查询的行的索引位    B所查询的容量
LIMIT 0,5      第一行---第五
LIMIT 5,5      第六行---第十
LIMIT 10,5     第十一---第十五
分页查询的前置
以baidu热搜为例
 >>>>>>>>>>>>>    第一页    LIMIT 0,6
 >>>>>>>>>>>>>    第二页    LIMIT 6,6
 >>>>>>>>>>>>>    第三页    LIMIT 12,6
 >>>>>>>>>>>>>    第四页    LIMIT 18,6
 >>>>>>>>>>>>>    第五页    LIMIT 24,6
 >>>>>>>>>>>>>    第六页    LIMIT 30,6
 >>>>>>>>>>>>>             LIMIT (当前页码数-1)*容量,容量
 #示例1：求学校学生中 三甲学生的信息
 #分析 学生总分 降序排列 区间取前三
 SELECT StudentNo "学号",SUM(StudentResult) "总成绩"
 FROM result
 GROUP BY (StudentNo)
 ORDER BY(SUM(StudentResult)) DESC
 LIMIT 0,3;

4.9GROUP_CONCAT 分组数据合并

#示例1 根据班级进行分组，要求查看各班人数，以及各班学员姓名。
SELECT GradeID "班级编号",COUNT(*) "班级人数",GROUP_CONCAT(StudentName) "学员姓名"
FROM student
GROUP BY(GradeID);

注意事项：
1、使用GROUP_CONCAT()函数时必须要对数据源进行分组，如果不分组，所有数据都将合并成一行。
2、对结果集排序 查询语句执行的查询结果,数据是按照插入时顺序进行排序。
3、实际上需要按照某列大小值进行拍讯的话，建议只针对于数值或日期通过 ORDER BY函数进行排序
4、在语句最后也可以通过LIMIT控制容量大小

4.10多表关联查询

1、交叉连接查询
#示例1：查询所有的学生+所有的班级信息
SELECT * 
FROM student,grade; 
这样查询最终得道的数据有11*7=77条数据
通过笛卡尔积获取的数据，异常过多，无法匹配具体的内容，于是我们需要补充条件以提高查询的精度

2、等值连接查询
#示例1：查询所有的学生+所有的班级信息
#语法：SELECT * FROM 表1，表2，... WHERE 表1.列 = 表2.列 [AND...];
SELECT * 
FROM student,grade
WHERE student.GradeID = grade.GradeID;
#示例2：查询所有的学生姓名，住址，班级名称
SELECT StudentName "姓名",Address "住址",GradeName "班级名称"
FROM student,grade
WHERE student.GradeID = grade.GradeID;
#更为规范化的写法
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM student s,grade g
WHERE s.GradeID = g.GradeID;
#练习查询学生姓名，参考科目，考试时间，考试成绩
#分析思路：1找到要查啥   2查的东西来自于哪   3表关系
SELECT s.StudentName "学生姓名",su.SubjectName "参考科目",r.ExamDate "考试时间",r.StudentResult "考试成绩"
FROM student s,subject su,result r
WHERE r.StudentNo = s.StudentNo AND r.SubjectNo = su.SubjectNo;

感想：等值链接确实能够帮助我们完成表于表之间的联系,但是WHERE这个关键字一开始是作为基础条件关键字出现的,而我们把表与表之间关系的描述通过WHERE去实施，难免大材小用.于是我们决定释放WHERE关于等值连接的操作。

3、内连接查询  INNER JOIN
#语法 SELECT * FROM 表1 INNER JOIN 表2 ON 表1.列 = 表2.列 [INNER JOIN 表3 ON 关系 .....][WHERE 基础条件];
#示例3：练习查询学生姓名，参考科目，考试时间，考试成绩
SELECT s.StudentName "学生姓名",su.SubjectName "参考科目",r.ExamDate "考试时间",r.StudentResult "考试成绩" 
FROM student s INNER JOIN result r ON r.StudentNo = s.StudentNo
INNER JOIN subject su ON r.SubjectNo = su.SubjectNo;

PS 内连接查询的本质和等值实际上没有区别,但是内连接可以释放WHERE关键字,使表与表之间关系更加清晰.

4、外连接查询
4.1 左外连接 >>>>> LEFT JOIN   获取相交数据+左外关键字以左表的全部数据
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM grade g LEFT JOIN student s ON s.GradeID = g.GradeID;
4.2 右外连接 >>>>> RIGHT JOIN  获取相交数据+右外关键字以右的全部数据
SELECT s.StudentName "姓名",s.Address "住址",g.GradeName "班级名称"
FROM student s RIGHT JOIN grade g ON s.GradeID = g.GradeID;
5、自然连接查询  自己和自己形成主外键关系
+------------+-----+-----------------+
| categoryId | pid | categoryName    |
+------------+-----+-----------------+
|          2 |   1 | 美术设计        |
|          3 |   1 | 软件开发        |
|          4 |   3 | 数据库基础      |
|          5 |   2 | Photoshop基础   |
|          6 |   2 | 色彩搭配学      |
|          7 |   3 | PHP基础         |
|          8 |   3 | 一起学JAVA      |
+------------+-----+-----------------+

假设 1 意味着是根目录 
编号为2的美术设计 和编号为3的软件开发 父级都是 1 根目录
编号为3的数据库基础 是软件开发的一部分

SELECT c1.categoryName "父级目录",c2.categoryName "子栏目"
FROM category c1 INNER JOIN category c2 ON c1.categoryId = c2.pid;

4.11子查询

1 将一个查询语句的结果充当下一个查询语句的条件
2 核心在于通过小括号以提高优先级别
3 子查询中可以包含的关键字 IN  NOT  ALL
4 子查询中可以包含的运算符 逻辑+算数

#示例1：查询大一的男生姓名及家庭住址
#1--大一对应的班级编号
SELECT GradeID FROM grade WHERE  GradeName = '大一';     ======>    1
#2--以一年级一班对应的班级编号作为线索，去找适配的学生信息
SELECT StudentName "姓名",Address "住址"
FROM student WHERE GradeID = (SELECT GradeID FROM grade WHERE  GradeName = '大一');
#3--加入我们的基础条件
SELECT StudentName "姓名",Address "住址"
FROM student WHERE GradeID = (SELECT GradeID FROM grade WHERE  GradeName = '大一') AND sex = 1;
#示例2：查询班级名称是大一（学生信息==>学号信息），科目是高等数学-1（科目编号） 的学生的平均分
>>>>需求1 根据班级名 找出班级 编号
SELECT GradeID
FROM grade WHERE GradeName = '大一';
>>>>需求2 根据对应的班级编号 找到适配的学生学号
SELECT StudentNo
FROM student WHERE GradeID = (SELECT GradeID
FROM grade WHERE GradeName = '大一')
>>>>需求3 根据科目名找到对应的科目编号
SELECT subjectNo
FROM subject WHERE subjectName = '高等数学-1';
>>>>编辑最后的命令
SELECT AVG(result.StudentResult)
FROM result
WHERE result.SubjectNo = (SELECT subjectNo
FROM subject WHERE subjectName = '高等数学-1')
AND result.StudentNo IN (SELECT StudentNo
FROM student WHERE GradeID = (SELECT GradeID
FROM grade WHERE GradeName = '大一'));

#课后作业：查询科目是高数1,2的总分和平均分(用子查询实现)

4.12 SQL函数

聚合函数

聚合函数是指对一组值进行运算，最终返回是单个值。也可以被称为 组合函数
COUNT()     统计目标行数量的函数
AVG()       求平均值
SUM()       求合
MIN()       求最大值
MAX()       求最小值
PS：除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) 之间

#随机生成 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)	返回两个日期之间的相差天数

#查询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

#示例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 
#    要求如下   如果学号是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；