前端小册组合查询
多条语句查询的结果集就是组合查询
涉及单表的组合查询
使用 or 连接
sql
mysql> select m1 from t1 where m1 > 2 or m1<2;
+------+
| m1 |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.06 sec)
mysql>使用 union 连接
sql
mysql> select m1 from t1 where m1 < 2 union select m1 from t1 where m1 > 2;
+------+
| m1 |
+------+
| 1 |
| 3 |
+------+
2 rows in set (0.00 sec)
mysql>sql
mysql> select m1,n1 from t1 where m1 < 2 union select m1, n1 from t1 where m1 > 2;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 3 | c |
+------+------+
2 rows in set (0.00 sec)
mysql>sql
mysql> SELECT m1, n1 FROM t1 WHERE m1 < 2 UNION SELECT n1, m1 FROM t1 WHERE m1 > 2;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| c | 3 |
+------+------+
2 rows in set (0.01 sec)
mysql>涉及不同表的查询
sql
mysql> select m1,n1 from t1 where m1 < 2 union select m2, n2 from t2 where m2 >2;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 3 | c |
| 4 | d |
+------+------+
3 rows in set (0.00 sec)
mysql>包含或去重
sql
mysql> SELECT m1, n1 FROM t1;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
+------+------+
3 rows in set (0.00 sec)
mysql> SELECT m2, n2 FROM t2;
+------+------+
| m2 | n2 |
+------+------+
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
3 rows in set (0.00 sec)
mysql>使用组合查询后
sql
mysql> select m1, n1 from t1 union select m2, n2 from t2;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
4 rows in set (0.00 sec)
mysql>少了两条记录，这是由于合并的结果集默认去除了重复的行，如果不去除则使用 union all
sql
mysql> select m1, n1 from t1 union all select m2, n2 from t2;
+------+------+
| m1 | n1 |
+------+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 2 | b |
| 3 | c |
| 4 | d |
+------+------+
6 rows in set (0.00 sec)
mysql>组合查询中的 order by 和 limit 子句
sql
mysql> select m1, n1 from t1 union select m2, n2 from t2 order by m1 desc limit 2;
+------+------+
| m1 | n1 |
+------+------+
| 4 | d |
| 3 | c |
+------+------+
2 rows in set (0.00 sec)
mysql>