第二十六篇（DQL）

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是对分组后的数据进行筛选。