创建Student表
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for gm_student
-- ----------------------------
DROP TABLE IF EXISTS `gm_student`;
CREATE TABLE `gm_student` (
`s_id` bigint(0) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`s_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`s_sex` int(0) NULL DEFAULT NULL COMMENT '0 -女 ;1-男',
PRIMARY KEY (`s_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of gm_student
-- ----------------------------
INSERT INTO `gm_student` VALUES (1, '长孙龙龙', 1);
INSERT INTO `gm_student` VALUES (2, '司徒花', 0);
SET FOREIGN_KEY_CHECKS = 1;
创建临时表
create TEMPORARY table tempStudent(
select a.s_id,a.s_name,a.s_sex from gm_student a
); -- 这里必须要加; 要不下面执行报错
-- 进行union all 查询
select * from tempStudent
union all
select a.s_id,a.s_name,a.s_sex from gm_student a
删除临时表
默认情况下,所有的临时表都由 MySQL 在数据库连接关闭时删除。不过,有时候你还是会想要在会话期间将其删除,此时你需要使用 DROP TABLE 命令来达到目的。
DROP TABLE tempStudent;