Mysql的联合查询命令UNION和UNION ALL
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了Mysql的联合查询命令UNION和UNION ALL,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5003字,纯文字阅读大概需要8分钟。
内容图文
![Mysql的联合查询命令UNION和UNION ALL](/upload/InfoBanner/zyjiaocheng/917/61ea8ecf6e324d4882c78e2e6c526df6.jpg)
Mysql的联合查询命令UNION和UNION ALL SQL UNION 语法: SELECT column_name FROM table1 UNION SELECT column_name FROM table2 注释:默认UNION 操作符选取不同的值.如果允许重复的值,请使用 UNION ALL. 当 ALL 随 UNION 一起使用时(即 UNION ALL),不消除重复行. SQL UNION ALL 语法 SELECT column_name FROM table1 UNION ALL SELECT column_name FROM table2 注释:UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名. 注意:UNION 结果集中的列名总是等于第一个 SELECT 语句中的列名. #两张表的数据如下: mysql> select hid,bname from boy where hid=1; +-----+-------+ | hid | bname | +-----+-------+ | 1 | lisi | +-----+-------+ 1 row in set (0.00 sec) mysql> select hid,bname from boy where hid=3; +-----+--------+ | hid | bname | +-----+--------+ | 3 | 赵六 | +-----+--------+ 1 row in set (0.00 sec) mysql> select hid,bname from boy where hid=1 union select hid,bname from boy where hid=3; +-----+--------+ | hid | bname | +-----+--------+ | 1 | lisi | | 3 | 赵六 | +-----+--------+ 2 rows in set (0.00 sec) #union语句必须满足1个条件;各语句取出的列数相同. mysql> select hid,bname from boy where hid=1 -> union -> select bname from boy where hid=3; ERROR 1222 (21000): The used SELECT statements have a different number of columns #union列名称未必要一致,列名称会使用第一条sql的列名称为准. mysql> select hid,bname from boy where hid=1; +-----+-------+ | hid | bname | +-----+-------+ | 1 | lisi | +-----+-------+ 1 row in set (0.00 sec) mysql> select cno,score from sc where cno="k1"; +-----+-------+ | cno | score | +-----+-------+ | K1 | 83 | | K1 | 85 | | K1 | 92 | +-----+-------+ 3 rows in set (0.00 sec) mysql> select hid,bname from boy where hid=1 -> union -> select cno,score from sc where cno="k1"; +-----+-------+ | hid | bname | +-----+-------+ | 1 | lisi | | K1 | 83 | | K1 | 85 | | K1 | 92 | +-----+-------+ 4 rows in set (0.00 sec) #union查询支持跨表查询. mysql> select hid,bname from boy where hid=1; +-----+-------+ | hid | bname | +-----+-------+ | 1 | lisi | +-----+-------+ 1 row in set (0.01 sec) mysql> select hid,bname from girl where hid=3; +-----+--------+ | hid | bname | +-----+--------+ | 3 | 默默 | +-----+--------+ 1 row in set (0.00 sec) mysql> select hid,bname from boy where hid=1 union select hid,bname from girl where hid=3; +-----+--------+ | hid | bname | +-----+--------+ | 1 | lisi | | 3 | 默默 | +-----+--------+ 2 rows in set (0.00 sec) #girl表3条数据. mysql> select * from girl; +-----+--------+ | hid | bname | +-----+--------+ | 3 | 默默 | | 2 | 羞羞 | | 5 | 海燕 | +-----+--------+ 3 rows in set (0.00 sec) #boy表4条数据. mysql> select * from boy; +-----+--------+ | hid | bname | +-----+--------+ | 1 | lisi | | 2 | 王五 | | 3 | 赵六 | | 5 | 海燕 | +-----+--------+ 4 rows in set (0.00 sec) #查询结果只有7条数据? mysql> select * from girl -> union -> select * from boy; +-----+--------+ | hid | bname | +-----+--------+ | 3 | 默默 | | 2 | 羞羞 | | 5 | 海燕 | | 1 | lisi | | 2 | 王五 | | 3 | 赵六 | +-----+--------+ 6 rows in set (0.00 sec) 注意:使用union时,完全相等的行将会被合并,合并是比较耗时的操作,一般不让union进行合并,使用"union all"避免合并. #使用"union all"相同的行将不会被合并. mysql> select * from girl union all select * from boy; +-----+--------+ | hid | bname | +-----+--------+ | 3 | 默默 | | 2 | 羞羞 | | 5 | 海燕 | | 1 | lisi | | 2 | 王五 | | 3 | 赵六 | | 5 | 海燕 | +-----+--------+ 7 rows in set (0.00 sec) #union的子句中不写order by,如下面sql,结果并未体现出order by排序的结果. mysql> (select * from girl order by hid desc) union all (select * from boy order by hid desc); +-----+--------+ | hid | bname | +-----+--------+ | 3 | 默默 | | 2 | 羞羞 | | 5 | 海燕 | | 1 | lisi | | 2 | 王五 | | 3 | 赵六 | | 5 | 海燕 | +-----+--------+ 7 rows in set (0.00 sec) #union sql合并后得到的总的结果,可以使用order by,子句order by失去意义. mysql> select * from girl union all select * from boy order by hid desc; +-----+--------+ | hid | bname | +-----+--------+ | 5 | 海燕 | | 5 | 海燕 | | 3 | 默默 | | 3 | 赵六 | | 2 | 王五 | | 2 | 羞羞 | | 1 | lisi | +-----+--------+ 7 rows in set (0.00 sec) #下面是两张表合并的数据,将其中bname相同的行的hid求和. mysql> select * from boy union all select * from girl as tmp; +-----+--------+ | hid | bname | +-----+--------+ | 1 | lisi | | 2 | 王五 | | 3 | 赵六 | | 5 | 海燕 | | 3 | 默默 | | 2 | 羞羞 | | 5 | 海燕 | +-----+--------+ 7 rows in set (0.00 sec) #将两张表的查询结果看成一张表,使用group by分组求和. mysql> select sum(hid),bname from (select * from boy union all select * from girl) as tmp group by bname; +----------+--------+ | sum(hid) | bname | +----------+--------+ | 1 | lisi | | 10 | 海燕 | | 2 | 王五 | | 2 | 羞羞 | | 3 | 赵六 | | 3 | 默默 | +----------+--------+ 6 rows in set (0.00 sec)
内容总结
以上是互联网集市为您收集整理的Mysql的联合查询命令UNION和UNION ALL全部内容,希望文章能够帮你解决Mysql的联合查询命令UNION和UNION ALL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。