第二十六篇（DQL）_想精通dql语句有什么好的-优快云博客

本文链接：https://blog.youkuaiyun.com/wp_930420/article/details/90679365

本文围绕MySQL的DQL展开，介绍了排序查询，可按成绩、时间排序；限制查询用于分页，给出页码和起始索引计算公式；子查询可嵌套在多种操作中；还讲解了常用的字符串、数学、时间、聚合函数，以及分组查询，对比了HAVING和WHERE的区别。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DQL

还是先创建五张表进行测试！

DROP TABLE IF EXISTS `category`;
CREATE TABLE `category`  (
  `id` int(11) NOT NULL COMMENT '游戏分类编号',
  `categoryName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '游戏分类名称',
  `pid` int(11) NULL DEFAULT NULL COMMENT '分类的父分类编号',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;


INSERT INTO `category` VALUES (1, '角色扮演', NULL);
INSERT INTO `category` VALUES (2, 'FPS射击', NULL);
INSERT INTO `category` VALUES (3, '休闲益智', NULL);
INSERT INTO `category` VALUES (4, '系统游戏', NULL);
INSERT INTO `category` VALUES (5, 'DNF', 1);
INSERT INTO `category` VALUES (6, '和平精英', 2);
INSERT INTO `category` VALUES (7, '连连看', 3);


DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`  (
  `gradeId` int(11) NOT NULL,
  `gradeName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`gradeId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;


INSERT INTO `grade` VALUES (1, '大学一年级');
INSERT INTO `grade` VALUES (2, '大学二年级');
INSERT INTO `grade` VALUES (3, '大学三年级');


DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`  (
  `stuId` int(11) NULL DEFAULT NULL COMMENT '学生编号',
  `subjectId` int(11) NULL DEFAULT NULL COMMENT '课程编号',
  `result` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '考试成绩',
  `examDate` datetime(0) NULL DEFAULT NULL COMMENT '考试时间'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;


INSERT INTO `result` VALUES (3, 3, '65', '2019-05-26 10:22:09');
INSERT INTO `result` VALUES (3, 4, '90', '2019-05-26 10:22:18');
INSERT INTO `result` VALUES (1, 1, '86', '2019-05-26 10:22:57');
INSERT INTO `result` VALUES (2, 2, '90', '2019-05-26 10:23:10');


DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `stuId` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `stuName` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '学生姓名',
  `stuPwd` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '12321' COMMENT '学生密码',
  `gender` char(2) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '男' COMMENT '性别',
  `gradeId` int(11) NULL DEFAULT NULL COMMENT '年级编号',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '电话',
  `email` varchar(25) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `address` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '地址不详' COMMENT '地址',
  `identityId` varchar(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `bornDate` datetime(0) NULL DEFAULT NULL COMMENT '出生日期',
  PRIMARY KEY (`stuId`) USING BTREE,
  UNIQUE INDEX `identityCard`(`identityId`) USING BTREE,
  INDEX `fk_stu_grade`(`gradeId`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '学生表' ROW_FORMAT = Compact;


INSERT INTO `student` VALUES (1, '张三', '123', '男', 1, NULL, NULL, '北京西城', NULL, NULL);
INSERT INTO `student` VALUES (2, '张丰三', '123', '女', 2, NULL, NULL, '北京西城', NULL, NULL);
INSERT INTO `student` VALUES (3, '田七', '123', '男', 4, NULL, NULL, NULL, NULL, NULL);


DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`  (
  `subjectId` int(11) NOT NULL COMMENT '课程编号',
  `subjectName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '课程名称',
  `gradeId` int(11) NULL DEFAULT NULL COMMENT '年级编号',
  PRIMARY KEY (`subjectId`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;


INSERT INTO `subject` VALUES (1, '高等数学', 1);
INSERT INTO `subject` VALUES (2, '数据结构设计', 2);
INSERT INTO `subject` VALUES (3, 'MySQL数据库', 3);
INSERT INTO `subject` VALUES (4, 'Java高级特性', 3);

DQL标准语法结构:编写DQL一定要严格按照此语法的顺序来实现！

SELECT [ALL | DISTINCT] ALL表示查询出所有的内容 DISTINCT 去重
			{* | 表名.* | 表名.字段名[ AS 别名][,...]} 指定查询出的字段的
		FROM
			表名[AS 别名][,表1... AS 别名]
		[INNER | [LEFT | RIGHT] [OUTER] JOIN 另一张表名 [AS 别名] ON 关联条件]
		[WHERE 条件]
		[GROUP BY 分组字段[,...]] 
		[HAVING 给分组后的数据进行条件筛选]
		[ORDER BY 排序字段[,...]]
		[LIMIT [startIndex,]pageSize]

1.掌握排序查询 `ORDER BY`

ORDER BY 默认情况下为升序排序即ASC(Ascend) 降序DESC(Descend)

1.1 需求：查询出学生姓名、课程名称、成绩并且按照成绩进行降序排序。

SELECT
	stu.stuName,sub.subjectName,r.result
FROM 
	result r,student stu,`subject` sub
WHERE
	r.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY r.result DESC;

在这里插入图片描述

1.2 需求：在上方基础进行改动要求按照成绩进行降序且如果成绩相同按照时间降序

SELECT
	stu.stuName,sub.subjectName,r.result,r.examDate
FROM 
	result r,student stu,`subject` sub
WHERE
	r.stuId = stu.stuId AND r.subjectId = sub.subjectId
ORDER BY 
	r.result DESC , r.examDate DESC;

在这里插入图片描述

2.掌握限制查询(分页)] `LIMIT`

LIMIT offset,row;
LIMIT startIndex起始索引<从0开始>,pageSize 分页场景

需求：只想看前两条学生信息

SELECT
	*
FROM
	student
LIMIT 0,2;  # 如果offset是从0开始 那么可以省略 LIMIT 2

在这里插入图片描述
分页：因为数据量比较大的时候如果把所有数据显示在一页上。

那么将不利于阅读，且不利于定位查找。所以要对数据进行分页。

假设有20条数据现在可以将数据拆分成4条每页则有5页 [后续在Java Web对其进行业务实现]。

假设有21条数据现在可以将数据拆分成4条每页则有6页。

LIMIT可以在我们的MySQL中实现分页的数据查询/指定页码的数据查询。

需求：现在学生信息要求每页显示2条想查看第一页的数据

SELECT * FROM student LIMIT 0,2;

要求：查看第二页的数据

SELECT * FROM student LIMIT 2,2;

要求：查看第三页的数据

SELECT * FROM student LIMIT 4,2;

页码和起始索引的计算公式：(页码 - 1) * 显示条数

startIndex = (currentPage - 1) * pageSize;

3.掌握MySQL子查询

子查询：在一个查询中又嵌套了其他的查询，那么嵌套的查询就被称为子查询，而外层的查询被称为父查询。

子查询可以任意嵌套！可以出现在INSERT UPDATE DELETE WHERE等中…

建议在初期写子查询时，先将查询进行步骤化。

需求：查询在高等数学考试中，成绩比张三高的学生的姓名信息

如果不考虑连表查询

SELECT
	stuId
FROM
	result
WHERE
	subjectId = 1
	AND
	result > 86;

如果利用子查询，需要分步骤整合

1.查询高等数学的课程编号：

SELECT
	subjectId
FROM
	`subject`
WHERE 
	subjectName = '高等数学';

整合：

SELECT
	stuId
FROM
	result
WHERE
	subjectId = (
		SELECT
			subjectId
		FROM
			`subject`
		WHERE 
			subjectName = '高等数学'
	)
	AND
	result > 86;

2.查询张三的高数考试成绩：

SELECT
	result
FROM
	result
WHERE
	stuId = (SELECT stuId FROM student WHERE stuName = '张三')
	AND subjectId = (SELECT subjectId FROM `subject` WHERE subjectName = '高等数学');

整合：

SELECT
	r.stuId,stu.stuName
FROM
	result r,student stu
WHERE
	r.stuId = stu.stuId
	AND
	subjectId = (
		SELECT subjectId FROM `subject` WHERE subjectName = '高等数学'
	)
	AND
	result > (
		SELECT result FROM result WHERE stuId = (
				SELECT stuId FROM student WHERE stuName = '张三'
		)
		AND subjectId = (
				SELECT subjectId FROM `subject` WHERE subjectName = '高等数学'
		)
	);

使用子查询解决连表查询

要求查询学生姓名、年级名称

不利用子查询的写法：

SELECT 
	stu.stuName,g.gradeName 
FROM 
	student stu,grade g 
WHERE 
	stu.gradeId = g.gradeId;

子查询的写法：

SELECT
	stu.stuName,
	(SELECT g.gradeName FROM grade g WHERE g.gradeId = stu.gradeId) AS gradeName
FROM
	student stu;

4.掌握MySQL常用函数

常用的字符串函数

4.1 字符串拼接
CONCAT(str1,str2,…)

SELECT CONCAT('这是','MySQL','数据库');

在这里插入图片描述

4.2 字符串内容替换 REPLACE(str,from_str,to_str)

SELECT REPLACE('这是MySQL数据库','MySQL','Oracle');

在这里插入图片描述
4.3 去除左侧空格

SELECT LTRIM('     Hello World    ');

在这里插入图片描述

去除右侧空格

SELECT RTRIM('     Hello World    ');

在这里插入图片描述

4.4 获取字符串长度

SELECT LENGTH('Hello');

在这里插入图片描述
4.5 截取字符串SUBSTR(str,pos) 索引从1开始

SELECT SUBSTR('Hello World',5);

在这里插入图片描述
SUBSTR(str,pos,len)

SELECT SUBSTR('Hello World',5,3);

在这里插入图片描述

常用的数学函数

4.6 获取随机数 Math.random();

SELECT RAND();

4.7 向上取整 结果为21

SELECT CEIL(20.4);

向下取整 结果为20

SELECT FLOOR(20.7);

4.8 四舍五入

SELECT ROUND(20.5); 

SELECT ROUND(20.76,1);

常用的时间函数

4.9 获取当前时间

SELECT NOW();	# 获取 当前时间的年月日时分秒

SELECT CURRENT_DATE();	# 获取 当前时间的年月日 

SELECT CURRENT_TIME();	# 获取 当前时间的时分秒

4.10 获取各种时间信息的函数

SELECT MONTH(NOW());	 # 年

SELECT YEAR(NOW()); 	 # 月

SELECT DAY(NOW()); 		 # 日

SELECT HOUR(NOW()); 	 # 时

SELECT MINUTE(NOW()); 	 # 分

SELECT SECOND(NOW());	 # 秒

4.11 日期转换

SELECT DATE_FORMAT(NOW(),'%Y-%m-%d'); # 年月日

4.12 计算时间差额

SELECT DATEDIFF(NOW(),'2019-05-25');

常用的聚合/统计函数

求年级编号最大的

SELECT MAX(gradeId) FROM grade;

在这里插入图片描述

求年级编号最小的

SELECT MIN(gradeId) FROM grade;

在这里插入图片描述
求平均分

SELECT AVG(result) FROM result WHERE subjectId = 1;

在这里插入图片描述
求和

SELECT SUM(result) FROM result WHERE subjectId = 1;

计算学生总人数

SELECT COUNT(*) FROM student;

SELECT COUNT(stuId) FROM student;

SELECT COUNT(1) FROM student;

在这里插入图片描述

5.掌握分组查询 `GROUP BY`

注意事项：在分组查询的查询字段中，不要出现与分组查询中无关的字段值。

需求：计算各个年级的学生人数

SELECT gradeId,COUNT(1) FROM student GROUP BY gradeId;

在这里插入图片描述

如果希望查看各个年级的总人数，且要求查看对应的学生姓名列表。

SELECT gradeId,COUNT(1),GROUP_CONCAT(stuName) FROM student GROUP BY gradeId;

在这里插入图片描述

计算各个年级各个性别的人数

SELECT gradeId,gender,COUNT(1) FROM student GROUP BY gradeId,gender;

在这里插入图片描述

需求：查询年级的学生人数大于1人的年级名称

SELECT
	stu.gradeId,count(1),g.gradeName
FROM
	student stu,grade g
WHERE
	stu.gradeId = g.gradeId
GROUP BY 
	gradeId
HAVING 
	count(1) > 1;

HAVING和WHERE的区别？

相同点：

都可以用来进行条件判断筛选行数。

不同点：

位置不同：WHERE是在分组之前 HAVING是在分组之后。
条件筛选不同：WHERE是给FROM查询到的数据进行条件筛选，而HAVING是对分组后的数据进行筛选。

第二十六篇（DQL）

DQL

1.掌握排序查询 ORDER BY

1.1 需求：查询出学生姓名、课程名称、成绩 并且按照成绩进行降序排序。

1.2 需求：在上方基础进行改动 要求按照成绩进行降序 且如果成绩相同按照时间降序

2.掌握限制查询(分页)] LIMIT

3.掌握MySQL子查询

4.掌握MySQL常用函数

常用的字符串函数

常用的数学函数

常用的时间函数

常用的聚合/统计函数

5.掌握分组查询 GROUP BY

HAVING和WHERE的区别？

1.掌握排序查询 `ORDER BY`

1.1 需求：查询出学生姓名、课程名称、成绩并且按照成绩进行降序排序。

1.2 需求：在上方基础进行改动要求按照成绩进行降序且如果成绩相同按照时间降序

2.掌握限制查询(分页)] `LIMIT`

5.掌握分组查询 `GROUP BY`