MySQL讲义第 42 讲

2023-07-26 13:09| 来源: 网络整理| 查看: 265

MySQL讲义第 42 讲——select 查询之动态查询

文章目录 MySQL讲义第 42 讲——select 查询之动态查询step1：把 SQL 语句中动态变化的部分保存到变量中。step2：使用 CONCAT 函数拼接 SQL 语句，把最终拼接好的 SQL 语句保存到一个变量中。step3：执行如下命令：一、数据准备二、动态查询举例1、使用动态查询实现行列转换2、查询【C102】课程比【C103】课程分数低的学生的学号，姓名、课程名称及分数

在执行查询的过程中，有时需要动态生成 SQL 语句。遇到这种情况可以使用以下方法：

step1：把 SQL 语句中动态变化的部分保存到变量中。 step2：使用 CONCAT 函数拼接 SQL 语句，把最终拼接好的 SQL 语句保存到一个变量中。 step3：执行如下命令： SET @string = CONCAT(....); PREPARE stmt FROM @string; EXECUTE stmt; DROP PREPARE stmt;

根据以上的思路举例如下：

一、数据准备

查询使用到的三张表，分别是：student、course 和 score，表结构如下：

CREATE TABLE student( s_id char(5) primary key, s_name char(20), birth datetime, phone char(20), addr varchar(100) ); INSERT INTO student VALUES('S2011','张晓刚','1999-12-3','13163735775','信阳市'), ('S2012','刘小青','1999-10-11','13603732255','新乡市'), ('S2013','曹梦德','1998-2-13','13853735522','郑州市'), ('S2014','刘艳','1998-6-24','13623735335','郑州市'), ('S2015','刘岩','1999-7-6','13813735225','信阳市'), ('S2016','刘若非','2000-8-31','13683735533','开封市'), ('S2021','董雯花','2000-7-30','13533735564','开封市'), ('S2022','周华建','1999-5-25','13243735578','郑州市'), ('S2023','特朗普','1999-6-21','13343735588','新乡市'), ('S2024','奥巴马','2000-10-17','13843735885','信阳市'), ('S2025','周健华','2000-8-22','13788736655','开封市'), ('S2026','张学有','1998-7-6','13743735566','郑州市'); CREATE TABLE course( c_id char(4) primary key, c_name char(20) ); INSERT INTO course VALUES('C101','高等数学'), ('C102','线性代数'), ('C103','临床医学'), ('C104','传染病学'), ('C105','解剖学'), ('C106','有机化学'); CREATE TABLE score( s_id char(5), c_id char(4), score int, primary key(s_id, c_id) ); INSERT INTO score VALUES('S2011','C101',84),('S2011','C102',90),('S2011','C103',79),('S2011','C104',65), ('S2012','C101',67),('S2012','C102',52),('S2012','C103',55),('S2012','C104',86), ('S2013','C101',97),('S2013','C102',68),('S2013','C103',66),('S2013','C104',68), ('S2014','C101',90),('S2014','C102',85),('S2014','C103',77),('S2014','C104',96), ('S2015','C101',69),('S2015','C102',66),('S2015','C103',88),('S2015','C104',69), ('S2016','C101',65),('S2016','C102',69),('S2016','C103',82),('S2016','C104',56), ('S2021','C101',72),('S2021','C102',90),('S2021','C105',90),('S2021','C106',57), ('S2022','C101',88),('S2022','C102',93),('S2022','C105',47),('S2022','C106',62), ('S2023','C101',68),('S2023','C102',86),('S2023','C105',56),('S2023','C106',91), ('S2024','C101',87),('S2024','C102',97),('S2024','C105',80),('S2024','C106',81), ('S2025','C101',61),('S2025','C102',62),('S2025','C105',87),('S2025','C106',82), ('S2026','C101',59),('S2026','C102',48),('S2026','C105',90),('S2026','C106',73); 二、动态查询举例 1、使用动态查询实现行列转换 SET @string = ''; --########################## 提取字段名 ############################################### SELECT GROUP_CONCAT(DISTINCT CONCAT(CHAR(10), ' SUM(IF(c.c_id = \'', c.c_id, '\', sc.score, 0))', ' AS ', c.c_name) ) INTO @string FROM score sc JOIN course c ON sc.c_id = c.c_id; --########################## 拼接完整的 SQL 语句 ########################################### SET @string = CONCAT(' SELECT s.s_id AS 学号, s.s_name AS 姓名, ', @string, ' FROM student s JOIN score sc ON s.s_id = sc.s_id JOIN course c ON sc.c_id = c.c_id GROUP BY s.s_id, s.s_name; ' ) ; --########################## 显示 SQL 语句 ############################################### select @string; /* SELECT s.s_id AS 学号, s.s_name AS 姓名, SUM(IF(c.c_id = 'C101', sc.score, 0)) AS 高等数学, SUM(IF(c.c_id = 'C102', sc.score, 0)) AS 线性代数, SUM(IF(c.c_id = 'C103', sc.score, 0)) AS 临床医学, SUM(IF(c.c_id = 'C104', sc.score, 0)) AS 传染病学, SUM(IF(c.c_id = 'C105', sc.score, 0)) AS 解剖学, SUM(IF(c.c_id = 'C106', sc.score, 0)) AS 有机化学 FROM student s JOIN score sc ON s.s_id = sc.s_id JOIN course c ON sc.c_id = c.c_id GROUP BY s.s_id, s.s_name; */ --########################## 执行 SQL 语句 ############################################### PREPARE stmt FROM @string; EXECUTE stmt; DROP PREPARE stmt; mysql> EXECUTE stmt; +-------+---------+------------+-------------+------------+------------+----------+-----------+ | 学号 | 姓名 | 高等数学 | 线性代数 | 临床医学 | 传染病学 | 解剖学 | 有机化学 | +-------+---------+------------+-------------+------------+------------+----------+-----------+ | S2011 | 张晓刚 | 84 | 90 | 79 | 65 | 0 | 0 | | S2012 | 刘小青 | 67 | 52 | 55 | 86 | 0 | 0 | | S2013 | 曹梦德 | 97 | 68 | 66 | 68 | 0 | 0 | | S2014 | 刘艳 | 90 | 85 | 77 | 96 | 0 | 0 | | S2015 | 刘岩 | 69 | 66 | 88 | 69 | 0 | 0 | | S2016 | 刘若非 | 65 | 69 | 82 | 56 | 0 | 0 | | S2021 | 董雯花 | 72 | 90 | 0 | 0 | 90 | 57 | | S2022 | 周华建 | 88 | 93 | 0 | 0 | 47 | 62 | | S2023 | 特朗普 | 68 | 86 | 0 | 0 | 56 | 91 | | S2024 | 奥巴马 | 87 | 97 | 0 | 0 | 80 | 81 | | S2025 | 周健华 | 61 | 62 | 0 | 0 | 87 | 82 | | S2026 | 张学有 | 59 | 48 | 0 | 0 | 90 | 73 | +-------+---------+------------+-------------+------------+------------+----------+-----------+ 12 rows in set (0.00 sec) 2、查询【C102】课程比【C103】课程分数低的学生的学号，姓名、课程名称及分数

查询代码如下：

--######################################################################################## --############## 方法一、使用交叉表 ###################################################### --######################################################################################## SET @string = ''; SET @c1 = ''; SET @c2 = ''; --############## 查询 c102 课程的课程名，并保存到变量 @c1 中 ################################ SELECT c_name INTO @c1 FROM course WHERE c_id = 'c102'; --############## 查询 c103 课程的课程名，并保存到变量 @c2 中 ################################ SELECT c_name INTO @c2 FROM course WHERE c_id = 'c103'; --############## 拼接用于查询的字符串 ###################################################### SET @string = CONCAT(' SELECT s.s_id AS `学号`, s.s_name AS `姓名`, sc.c102 AS `', @c1,'`, sc.c103 AS `', @c2,'`', ' FROM student s JOIN (SELECT s_id, SUM(IF(c_id = \'C102\', score, 0)) AS c102, SUM(IF(c_id = \'C103\', score, 0)) AS c103 FROM score WHERE c_id in (\'C102\', \'C103\') GROUP BY s_id HAVING C102 < C103 ) sc ON s.s_id = sc.s_id ; ' ); --################ 查看生成的询结字符串 ################################ SELECT @string; SELECT s.s_id AS `学号`, s.s_name AS `姓名`, sc.c102 AS `高等数学`, sc.c103 AS `线性代数` FROM student s JOIN (SELECT s_id, SUM(IF(c_id = 'C102', score, 0)) AS c102, SUM(IF(c_id = 'C103', score, 0)) AS c103 FROM score WHERE c_id in ('C102', 'C103') GROUP BY s_id HAVING C102

【本文地址】

公司简介

联系我们