mysql联合查询语句

发布时间: 2023-11-21 10:58 阅读: 文章来源:1MUMB631PS
前言:

(我发文旨在让您对着电脑直接复制粘贴就能上手的目的,如有不对的地方还望大佬多多批评指教,互相学习,共同进步)

在数据库中除了外连查询(left join ,right join,inner join)之外,我们还会经常用到union联合查询,尤其是做报表统计时候,需要你使用各种case when then,union ,union all,order by等关键字。本文我们说下union的使用。

正文:1.建立表test_hydra_file和test_hydra_file_history

完整sql语句如下:

--test_hydra_file.sql:SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for test_hydra_file-- ----------------------------drop TABLE IF EXISTS `test_hydra_file`;CREATE TABLE `test_hydra_file` ( `id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT ‘主键id‘, `upload_user_account` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT ‘上传人员域账号‘, `upload_user_name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT ‘上传人员姓名‘, `upload_time` datetime DEFAULT NULL COMMENT ‘上传时间‘) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;SET FOREIGN_KEY_CHECKS=1;-- test_hydra_file_history.sql:SET FOREIGN_KEY_CHECKS=0;-- ------------------------------ Table structure for test_hydra_file_history-- ----------------------------drop TABLE IF EXISTS `test_hydra_file_history`;CREATE TABLE `test_hydra_file_history` ( `id` varchar(50) COLLATE utf8_bin NOT NULL COMMENT ‘主键id‘, `upload_user_account` varchar(50) COLLATE utf8_bin DEFAULT NULL COMMENT ‘上传人员域账号‘, `upload_user_name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT ‘上传人员姓名‘, `upload_time` datetime DEFAULT NULL COMMENT ‘上传时间‘) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;SET FOREIGN_KEY_CHECKS=1;2.准备数据:

往 test_hydra_file表插入数据三条:

insert INTO `test_hydra_file` VALUES (‘00255ef360024167b6711387f2e039fc‘, ‘1236589‘, ‘uiokm‘, ‘2018-12-10 11:22:42‘);insert INTO `test_hydra_file` VALUES (‘00e0954f0457423d8888483589e372f2‘, ‘2018121010173400999374‘, ‘test‘, ‘2018-12-10 10:17:31‘);insert INTO `test_hydra_file` VALUES (‘01e6dad574224f9ab624c986529c659e‘, ‘10000‘, ‘test‘, ‘2018-12-10 10:57:41‘);往 test_hydra_file_history表插入两条:insert INTO `test_hydra_file_history` VALUES (‘25214c49d4054ed99b05573bd2640385‘, ‘1236589‘, ‘uiokm‘, ‘2018-12-10 09:50:25‘);insert INTO `test_hydra_file_history` VALUES (‘e59f5ddad93a47ca84c396936ad12d83‘, ‘1236589‘, ‘uiokm‘, ‘2018-12-10 09:43:49‘);

现在两个表的数据不一样

再往test_hydra_file_history表插入一条test_hydra_file里面的数据,以备后续测试

insert into test_hydra_file_history (select * from test_hydra_file where id=‘00255ef360024167b6711387f2e039fc‘); 3.测试union查询:select * from test_hydra_file UNIONSELECT * from test_hydra_file_history;

截图如下:

union

4.测试union all查询:select * from test_hydra_fileunionALLSELECT * from test_hydra_file_history;

截图如下:

union all

5.小结:

通过以上可以看出,union 查询是去重了,只能查出五条记录,但是union all则查出了全部的记录,查出六条记录,并且union all 效率要高出很多。

关注➕私信回复【学习】获取最新技术干货,每天都有更新。
•••展开全文