mysql dual表用法_mysql dual表的用途及案例

最新推荐文章于 2025-06-19 15:58:38 发布

原创最新推荐文章于 2025-06-19 15:58:38 发布 · 8k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql dual表用法

本文介绍了MySQL中的DUAL表，它是一个虚拟表，主要用于在不引用任何实际表的情况下执行SELECT语句。文中提供了多个使用DUAL表的实际案例，如计算、获取系统时间，以及在数据展示和导出中的应用，包括数据汇总、平均值计算和不同评分段的计数。

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

转自https://blog.csdn.net/weixin_44145478/article/details/107079176

mysql文档中对于dual表的解释：

You are allowed to specify DUAL as a dummy table name in situations

where no tables are referenced: 你可以在没有表的情况下指定一个虚拟的表名 mysql> SELECT 1 +

1 FROM DUAL;

-> 2 DUAL is purely for the convenience of people who require that all SELECT statements should have FROM and possibly other clauses. MySQL

may ignore the clauses. MySQL does not require FROM DUAL if no tables

are referenced.

DUAL是为了方便那些要求所有SELECT语句都应该具有FROM和其他子句的人。MySQL可能会忽略该条款。如果没有引用表，MySQL不需要从DUAL。

1 select 7*9 fromdual; 计算器2 SELECT SYSDATE() from dual 获取系统时间

案例

开发过程中,需要对数据进行excel展示或导出,并且需要各种类型的数据,这里提供一种通过dual表来实现

创建表teacher,插入数据

1 CREATE TABLE `teacher` (2 `id` bigint(20) NOT NULL AUTO_INCREMENT,3 `name` varchar(10) DEFAULT NULL,4 `age` int(11) DEFAULT NULL,5 `score` int(10) DEFAULT NULL,6 PRIMARY KEY (`id`),7 KEY `index_three_key` (`name`,`age`,`score`)8 ) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COMMENT=‘老师‘;

1 INSERT INTO teacher(id,name,age,score) VALUES(1,‘Alen‘,32,98),(2,‘Aony‘,43,1),2 (3,‘Dansy‘,53,78),(4,‘Fulanke‘,25,65),(5,‘Tom‘,32,54)

1 SELECT2 ‘员工汇总‘AS id,3 ‘‘AS NAME,4 ‘‘AS age,5 ‘‘AS score6 FROM7 DUAL UNION ALL8 SELECT9 *

10 FROM11 teacher UNION ALL12 SELECT13 ‘平均值‘AS id,14 ‘‘AS NAME,15 ‘‘AS age,16 cast(17 AVG( score ) AS DECIMAL ( 10, 3)) AS score18 FROM19 teacher UNION ALL20 SELECT21 ‘‘AS id,22 ‘‘AS NAME,23 ‘‘AS age,24 ‘‘AS score25 FROM26 DUAL UNION ALL27 SELECT28 ‘绩效‘AS id,29 ‘‘AS NAME,30 ‘‘AS age,31 ‘‘AS score32 FROM33 DUAL UNION ALL34 SELECT35 ‘A(评分)‘AS id,36 ‘B(评分)‘AS NAME,37 ‘C(评分)‘AS age,38 ‘D(评分)‘AS score39 FROM40 DUAL UNION ALL41 SELECT42 ( SELECT COUNT( score ) FROM teacher WHERE score >= 90) AS id,43 ( SELECT COUNT( score ) FROM teacher WHERE score >= 80 AND score < 90) AS NAME,44 ( SELECT COUNT( score ) FROM teacher WHERE score >= 60 AND score < 80) AS age,45 ( SELECT COUNT( score ) FROM teacher WHERE score < 60) AS score46 FROM47 DUAL

原文：https://www.cnblogs.com/sheseido/p/13862317.html