正文

正常查询：

  1. MariaDB [test]> select * from test1;
  2. +---------------------------+-------------+
  3. | username                  | password    |
  4. +---------------------------+-------------+
  5. | vampire                   | mypassword  |
  6. | vampire1                  | mypassword  |
  7. | vampire                   | random_pass |
  8. +---------------------------+-------------+
  9. 3 rows in set (0.000 sec)

使用union查询：

  1. MariaDB [test]> select 1,2 union select * from test1;
  2. +---------------------------+-------------+
  3. | 1                         | 2           |
  4. +---------------------------+-------------+
  5. | 1                         | 2           |
  6. | vampire                   | mypassword  |
  7. | vampire1                  | mypassword  |
  8. | vampire                   | random_pass |
  9. +---------------------------+-------------+
  10. 4 rows in set (0.002 sec)

这样查询会得到一个虚拟表，接下来就可以使用这个虚拟表来查找数据：

  1. MariaDB [test]> select `2` from (select 1,2 union select * from test1)a;
  2. +-------------+
  3. | 2           |
  4. +-------------+
  5. | 2           |
  6. | mypassword  |
  7. | mypassword  |
  8. | random_pass |
  9. +-------------+
  10. 4 rows in set (0.001 sec)

当````被过滤时，可以使用别名来解决。

  1. MariaDB [test]> select b from (select 1,2 as b union select * from test1)a;
  2. +-------------+
  3. | b           |
  4. +-------------+
  5. | 2           |
  6. | mypassword  |
  7. | mypassword  |
  8. | random_pass |
  9. +-------------+
  10. 4 rows in set (0.031 sec)

拓展

除了直接查数据，还可以通过join、join...using来获取列名。
join之后的效果，产生一个虚拟表，并且有重复的列名。

  1. MariaDB [test]> select * from test1 as a join test1 b;
  2. +---------------------------+-------------+---------------------------+-------------+
  3. | username                  | password    | username                  | password    |
  4. +---------------------------+-------------+---------------------------+-------------+
  5. | vampire                   | mypassword  | vampire                   | mypassword  |
  6. | vampire1                  | mypassword  | vampire                   | mypassword  |
  7. | vampire                   | random_pass | vampire                   | mypassword  |
  8. | vampire                   | mypassword  | vampire1                  | mypassword  |
  9. | vampire1                  | mypassword  | vampire1                  | mypassword  |
  10. | vampire                   | random_pass | vampire1                  | mypassword  |
  11. | vampire                   | mypassword  | vampire                   | random_pass |
  12. | vampire1                  | mypassword  | vampire                   | random_pass |
  13. | vampire                   | random_pass | vampire                   | random_pass |
  14. +---------------------------+-------------+---------------------------+-------------+
  15. 9 rows in set (0.068 sec)

接着使用select 查询：

  1. MariaDB [test]> select * from (select * from test1 as a join test1 b)c;
  2. ERROR 1060 (42S21): Duplicate column name 'username'

可以看到报错，并且把重复的列名显示出来，但是只显示一个，如果要显示另一个则需要使用using，正常using的效果如下：

  1. MariaDB [test]> select * from test1 as a join test1 b using(username);
  2. +---------------------------+-------------+-------------+
  3. | username                  | password    | password    |
  4. +---------------------------+-------------+-------------+
  5. | vampire                   | mypassword  | mypassword  |
  6. | vampire                   | random_pass | mypassword  |
  7. | vampire1                  | mypassword  | mypassword  |
  8. | vampire                   | mypassword  | random_pass |
  9. | vampire                   | random_pass | random_pass |
  10. +---------------------------+-------------+-------------+
  11. 5 rows in set (0.039 sec)

可以看到重复列username没了，只剩下一个password，那么理所当然的报错只会显示password：

  1. MariaDB [test]> select * from (select * from test1 as a join test1 b using(username))c;
  2. ERROR 1060 (42S21): Duplicate column name 'password'