使用DQL命令查询数据(二)

  • 开发
  • 50

一、SELECT语法——排序

语法

SELECT   [ALL | DISTINCT] 
{  * |  table.* | [ table.field1 [ as  alias1] [, table.field2 [as  alias2]][, …]] }
FROM  table_name  [ as  table_ alias  ]
    [ left|out|inner  join  table_name2 ]    #多表连接查询
    [ WHERE  … ]   	#指定结果需满足的条件
    [ GROUP BY …]	#指定结果按照哪几个字段来分组
    [ HAVING …]	#过滤分组的记录必须满足的次要条件
    [ ORDER BY… ]	#指定查询记录按一个或者多个条件排序
    [ LIMIT  {   [ offset,] row_count    |   row_count OFFSET offset   }] ;  #分页查询

注意

  1. [ ]括号代表可选的
  2. { }括号代表必须的
  3. # MySQL语句中的注释符，也可以用 /*该处为注释*/

二、ORDER BY 字句

ORDER BY字句：按一定顺序显示查询结果

排序可以是升序（ASC）或者降序（DESC），如果不指定ASC或DESC，结果集默认按ASC升序排序

如果要在学生成绩排序的基础上，再按照课程编号进行排序，怎么办？

练习

代码实现

#查询学生表中年级编号为1的学生姓名、电话和出生日期，并按照出生日期降序排序
SELECT STUDENTNAME,PHONE,BORNDATE FROM STUDENT
WHERE GRADEID = 1
#按照出生日期的降序排序
ORDER BY BORNDATE DESC;

#查询成绩表中学号为10001的学号、成绩和考试日期，并按照日期先后次序显示
SELECT STUDENTRESULT,EXAMDATE FROM RESULT
WHERE STUDENTNO = 10001
ORDER BY EXAMDATE;

#查询成绩表中课程编号为1的学号、成绩和考试日期，并按照日期先后、成绩由高到低的次序显示。
SELECT STUDENTNO,STUDENTRESULT,EXAMDATE
FROM RESULT WHERE SUBJECTNO=1
ORDER BY EXAMDATE,STUDENTRESULT DESC;

三、SELECT语法——分页查询

四、LIMIT子句

LIMIT子句：MySQL查询语句中使用LIMIT子句限制结果集

练习

代码实现

#查询成绩表中2019年课程编号为1且分数大于80的信息（学号，课程编号，分数），只显示前10名
SELECT STUDENTNO,SUBJECTNO,STUDENTRESULT FROM RESULT
WHERE YEAR(EXAMDATE) = '2019'
AND SUBJECTNO = 1 AND STUDENTRESULT >= 80
ORDER BY STUDENTRESULT DESC
LIMIT 3;

#查询2023年课程编号为1的学号、考试成绩，并按照考试成绩由高到低的次序显示。要求查询出第6条-10条的记录
SELECT STUDENTNO,STUDENTRESULT FROM RESULT 
WHERE YEAR(EXAMDATE)='2023' 
AND SUBJECTNO=1 
ORDER BY STUDENTRESULT DESC
LIMIT 5,5;

五、SELECT语法——分组查询

六、GROUP BY分组

GROUO BY作用：使用GROUP BY关键字对查询结果分组

  • 对所有的数据进行分组统计
  • 分组的依据字段可以有多个，并依次分组
  • 与HAVING结合使用，进行分组后的数据筛选

WHERE与HAVING对比

WHERE子句

  • 用来筛选FROM子句中指定的操作所产生的行

GROUP BY子句

  • 用来分组WHERE子句的输出

HAVING子句

  • 用来从分组的结果中筛选行

练习

代码实现

#查询每门课程及格总人数和及格学生的平均分
SELECT SUBJECTNO,COUNT(1),AVG(STUDENTRESULT) FROM RESULT 
WHERE STUDENTRESULT >= 60
GROUP BY SUBJECTNO;

#查询每门课程及格总人数和及格平均分在80分以上的记录
SELECT SUBJECTNO,COUNT(1),AVG(STUDENTRESULT) FROM RESULT 
WHERE STUDENTRESULT >= 60
GROUP BY SUBJECTNO
HAVING AVG(STUDENTRESULT) > 80;

#查询每门课程的平均分，并按照降序排列
SELECT SUBJECTNO,AVG(STUDENTRESULT) AVG FROM RESULT
GROUP BY SUBJECTNO
ORDER BY AVG DESC;

#查询每个学生参加的所有考试的总分，并按照降序排列
SELECT STUDENTNO,SUM(STUDENTRESULT) SUM FROM RESULT
GROUP BY STUDENTNO
ORDER BY SUM DESC;

#查询每个年级学生的平均年龄
SELECT GRADEID,AVG(YEAR(NOW())-YEAR(BORNDATE)) FROM STUDENT 
GROUP BY GRADEID;

#查询考试不及格的学生学号和不及格的次数
SELECT STUDENTNO,COUNT(STUDENTRESULT) FROM RESULT
WHERE STUDENTRESULT < 60
GROUP BY STUDENTNO;

七、子查询

子查询是一个嵌套在SELECT、INSERT、UPDATE或DELETE语句或其他子查询中的查询

子查询在WHERE语句中的一般用法

注意

  1. 习惯上，外层查询称为父查询，圆括号中嵌入的查询称为子查询
  2. 执行SQL语句时，先执行子查询，返回所有来自子查询的结果，再执行外围的父查询，返回查询的最终结果（即求解方式为由里及外）
  3. 将子查询和比较运算符联合使用时，必须保证子查询返回的值不能多于一个（否则会出现提示信息：错误代号1242 Subquery returns more than 1 row）

IN子查询

IN后面的子查询可以返回多条记录

常用IN替换等于（=）的子查询

练习

代码实现

#查询年级名称为"大一"开设的课程信息（课程编号、课程名称、课时数）
SELECT SUBJECTNO,SUBJECTNAME,CLASSHOUR FROM `SUBJECT`
WHERE GRADEID = (SELECT GRADEID FROM GRADE WHERE GRADENAME='大一');

#查询课程名称为”高等数学-1”最近一次考试的学生信息（学号、姓名和联系电话）。
SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT
WHERE STUDENTNO IN (
	SELECT STUDENTNO FROM RESULT WHERE SUBJECTNO = (
		SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
	) AND EXAMDATE = (
		SELECT MAX(EXAMDATE) FROM RESULT
		WHERE SUBJECTNO = (
					SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
		)
	)
);

#查询没有参加课程名称为”高等数学-1”最近一次考试的学生信息（学号、姓名和联系电话）。
SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT
WHERE STUDENTNO NOT IN (
	SELECT STUDENTNO FROM RESULT WHERE SUBJECTNO = (
		SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
	) AND EXAMDATE = (
		SELECT MAX(EXAMDATE) FROM RESULT
		WHERE SUBJECTNO = (
					SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
		)
	)
);

EXISTS子查询

EXISTS子查询：带有EXISTS的子查询不返回任何记录的数据，只返回逻辑值“True”或“False”

注意

  1. 子查询有返回结果：EXISTX子查询结果为TRUE，执行外层查询
  2. 子查询无返回结果：EXISTX子查询结果为FALSE，外层查询不执行

子查询注意事项

任何允许使用表达式的地方都可以使用子查询

嵌套在父查询SELECT语句的子查询可包括：

  • SELECT子句
  • FROM子句
  • WHERE子句
  • GROUP BY子句
  • HAVING子句

只出现在子查询中而没有出现在父查询中的列不能包含在输出列中

综合代码演示

#切换到......
USE myschool;
SELECT * FROM RESULT;
SELECT * FROM STUDENT;

#查询学生表中年级编号为1的学生姓名、电话和出生日期，并按照出生日期降序排序
SELECT STUDENTNAME,PHONE,BORNDATE FROM STUDENT
WHERE GRADEID = 1
#按照出生日期的降序排序
ORDER BY BORNDATE DESC;

#查询成绩表中学号为10001的学号、成绩和考试日期，并按照日期先后次序显示
SELECT STUDENTRESULT,EXAMDATE FROM RESULT
WHERE STUDENTNO = 10001
ORDER BY EXAMDATE;

#查询成绩表中课程编号为1的学号、成绩和考试日期，并按照日期先后、成绩由高到低的次序显示。
SELECT STUDENTNO,STUDENTRESULT,EXAMDATE
FROM RESULT WHERE SUBJECTNO=1
ORDER BY EXAMDATE,STUDENTRESULT DESC;

#查询成绩表中2019年课程编号为1且分数大于80的信息（学号，课程编号，分数），只显示前10名
SELECT STUDENTNO,SUBJECTNO,STUDENTRESULT FROM RESULT
WHERE YEAR(EXAMDATE) = '2019'
AND SUBJECTNO = 1 AND STUDENTRESULT >= 80
ORDER BY STUDENTRESULT DESC
LIMIT 3;
#查询2023年课程编号为1的学号、考试成绩，并按照考试成绩由高到低的次序显示。要求查询出第6条-10条的记录
SELECT STUDENTNO,STUDENTRESULT FROM RESULT 
WHERE YEAR(EXAMDATE)='2023' 
AND SUBJECTNO=1 
ORDER BY STUDENTRESULT DESC
LIMIT 5,5;

#查询每门课程及格总人数和及格学生的平均分
SELECT SUBJECTNO,COUNT(1),AVG(STUDENTRESULT) FROM RESULT 
WHERE STUDENTRESULT >= 60
GROUP BY SUBJECTNO;
#查询每门课程及格总人数和及格平均分在80分以上的记录
SELECT SUBJECTNO,COUNT(1),AVG(STUDENTRESULT) FROM RESULT 
WHERE STUDENTRESULT >= 60
GROUP BY SUBJECTNO
HAVING AVG(STUDENTRESULT) > 80;
#查询每门课程的平均分，并按照降序排列
SELECT SUBJECTNO,AVG(STUDENTRESULT) AVG FROM RESULT
GROUP BY SUBJECTNO
ORDER BY AVG DESC;
#查询每个学生参加的所有考试的总分，并按照降序排列
SELECT STUDENTNO,SUM(STUDENTRESULT) SUM FROM RESULT
GROUP BY STUDENTNO
ORDER BY SUM DESC;
#查询每个年级学生的平均年龄
SELECT GRADEID,AVG(YEAR(NOW())-YEAR(BORNDATE)) FROM STUDENT 
GROUP BY GRADEID;
#查询考试不及格的学生学号和不及格的次数
SELECT STUDENTNO,COUNT(STUDENTRESULT) FROM RESULT
WHERE STUDENTRESULT < 60
GROUP BY STUDENTNO;

#查询年级名称为"大一"开设的课程信息（课程编号、课程名称、课时数）
SELECT SUBJECTNO,SUBJECTNAME,CLASSHOUR FROM `SUBJECT`
WHERE GRADEID = (SELECT GRADEID FROM GRADE WHERE GRADENAME='大一');
#查询课程名称为”高等数学-1”最近一次考试的学生信息（学号、姓名和联系电话）。
SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT
WHERE STUDENTNO IN (
	SELECT STUDENTNO FROM RESULT WHERE SUBJECTNO = (
		SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
	) AND EXAMDATE = (
		SELECT MAX(EXAMDATE) FROM RESULT
		WHERE SUBJECTNO = (
					SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
		)
	)
);
#查询没有参加课程名称为”高等数学-1”最近一次考试的学生信息（学号、姓名和联系电话）。
SELECT STUDENTNO,STUDENTNAME,PHONE FROM STUDENT
WHERE STUDENTNO NOT IN (
	SELECT STUDENTNO FROM RESULT WHERE SUBJECTNO = (
		SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
	) AND EXAMDATE = (
		SELECT MAX(EXAMDATE) FROM RESULT
		WHERE SUBJECTNO = (
					SELECT SUBJECTNO FROM `SUBJECT` WHERE SUBJECTNAME = '高等数学-1'
		)
	)
);
阅读全部

相关推荐

  3. DQL( 数据查询语言)

    2024-01-06 04:12:02       32 阅读

  6. SQL语句详解四-DQL(数据查询语言-多表查询二)

    2024-01-06 04:12:02       65 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-01-06 04:12:02       94 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-01-06 04:12:02       100 阅读

  9. 在Django里面运行非项目文件

    2024-01-06 04:12:02       82 阅读

  19. Python语言-面向对象

    2024-01-06 04:12:02       91 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-01-06 04:12:02       85 阅读

热门阅读

  1. 博客摘录「 什么是QPS、TPS、吞吐量？- 高并发名词概念」2024年1月5日

    2024-01-06 04:12:02       60 阅读

  4. 我的Spring Cloud学习之旅：原因、过程和收获

    2024-01-06 04:12:02       62 阅读

  6. Ubuntu安装和配置ssh教程

    2024-01-06 04:12:02       59 阅读

  11. 【设计模式之美】理论一：怎么才算是单一原则、如何取舍单一原则

    2024-01-06 04:12:02       55 阅读

  12. c# Avalonia 绘图

    2024-01-06 04:12:02       54 阅读

  13. Flutter中的StatelessWidget和StatefulWidget简介与使用

    2024-01-06 04:12:02       69 阅读

  16. 2024阿里云服务器配置推荐方案

    2024-01-06 04:12:02       70 阅读

  17. 【leetcode100-028】【链表】两数相加

    2024-01-06 04:12:02       59 阅读

  18. LeetCode_2_中等_两数相加

    2024-01-06 04:12:02       53 阅读

  21. python中多线程的使用

    2024-01-06 04:12:02       51 阅读