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

最新推荐文章于 2024-07-19 03:47:03 发布

睿思达DBA_WGX

最新推荐文章于 2024-07-19 03:47:03 发布

阅读量3.5k

点赞数 4

分类专栏： MySQL基本操作 MySQL讲义文章标签：数据库 mysql select 查询动态查询

本文链接：https://blog.csdn.net/weixin_44377973/article/details/109546051

版权

MySQL基本操作同时被 2 个专栏收录

169 篇文章 96 订阅

订阅专栏

MySQL讲义

57 篇文章 27 订阅

订阅专栏

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

文章目录

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

在执行查询的过程中，有时需要动态生成 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 < C103
        ) sc
    ON s.s_id = sc.s_id
;
--################     利用拼接生成的字符串进行查询   ################################
PREPARE stmt FROM @string;
EXECUTE stmt;
DROP PREPARE stmt;

--################     查询结果   ################################
+--------+-----------+--------------+--------------+
| 学号   | 姓名      | 高等数学     | 线性代数     |
+--------+-----------+--------------+--------------+
| S2021  | 董雯花    |           72 |           90 |
| S2022  | 周华建    |           88 |           93 |
| S2023  | 特朗普    |           68 |           86 |
| S2024  | 奥巴马    |           87 |           97 |
+--------+-----------+--------------+--------------+
4 rows in set (0.00 sec)