SQL基础（廿二）---UNION ALL

UNION与UNION ALL的区别：

mysql> select * from t_employee;
+----------+-------+------+---------+-------------+---------------+
| fnumber  | fname | fage | fsalary | fsubcompany | fdepartment   |
+----------+-------+------+---------+-------------+---------------+
| DEV001   | Tom   |   26 | 8300    | Beijing     | Development   |
| DEV002   | Jerry |   28 | 2300.8  | ShenZhen    | Development   |
| HR001    | Jane  |   23 | 2200.88 | Beijing     | HumanResource |
| HR002    | Tina  |   26 | 5200.36 | Beijing     | HumanResource |
| IT001    | Smith |   28 | 3900    | Beijing     | InfoTech      |
| IT002    | NULL  |   27 | 2800    | ShenZhen    | InfoTech      |
| SALES001 | John  |   23 | 5000    | Beijing     | Sales         |
| SALES002 | Kerry |   28 | 6200    | Beijing     | Sales         |
| SALES003 | Stone |   22 | 1200    | ShenZhen    | Sales         |
+----------+-------+------+---------+-------------+---------------+
9 rows in set

mysql> select * from t_tempemployee;
+---------------+---------+------+
| FIdCardNumber | FName   | FAge |
+---------------+---------+------+
| 1234567890121 | Sarani  |   33 |
| 1234567890122 | Tom     |   26 |
| 1234567890123 | Yalaha  |   38 |
| 1234567890124 | Tina    |   26 |
| 1234567890125 | Konkaya |   29 |
| 1234567890126 | Fo fa   |   46 |
+---------------+---------+------+
6 rows in set

mysql> select fname,fage from t_employee
    -> union
    -> select fname,fage from t_tempemployee;
+---------+------+
| fname   | fage |
+---------+------+
| Tom     |   26 |
| Jerry   |   28 |
| Jane    |   23 |
| Tina    |   26 |
| Smith   |   28 |
| NULL    |   27 |
| John    |   23 |
| Kerry   |   28 |
| Stone   |   22 |
| Sarani  |   33 |
| Yalaha  |   38 |
| Konkaya |   29 |
| Fo fa   |   46 |
+---------+------+
13 rows in set

mysql> select fname,fage from t_employee
    -> union all
    -> select fname,fage from t_tempemployee;
+---------+------+
| fname   | fage |
+---------+------+
| Tom     |   26 |
| Jerry   |   28 |
| Jane    |   23 |
| Tina    |   26 |
| Smith   |   28 |
| NULL    |   27 |
| John    |   23 |
| Kerry   |   28 |
| Stone   |   22 |
| Sarani  |   33 |
| Tom     |   26 |
| Yalaha  |   38 |
| Tina    |   26 |
| Konkaya |   29 |
| Fo fa   |   46 |
+---------+------+
15 rows in set

员工年龄报表：

mysql> select '正式员工最高年龄', max(fage) from t_employee
    -> union
    -> select '正式员工最低年龄', min(fage) from t_employee
    -> union
    -> select '临时员工最高年龄', max(fage) from t_tempemployee
    -> union
    -> select '临时员工最低年龄', min(fage) from t_tempemployee;
+------------------+-----------+
| 正式员工最高年龄 | max(fage) |
+------------------+-----------+
| 正式员工最高年龄 |        28 |
| 正式员工最低年龄 |        22 |
| 临时员工最高年龄 |        46 |
| 临时员工最低年龄 |        26 |
+------------------+-----------+
4 rows in set

正式员工工资报表：

mysql> select fnumber,fsalary from t_employee
    -> union
    -> select '工资合计', sum(fsalary) from t_employee;
+----------+----------+
| fnumber  | fsalary  |
+----------+----------+
| DEV001   | 8300.00  |
| DEV002   | 2300.80  |
| HR001    | 2200.88  |
| HR002    | 5200.36  |
| IT001    | 3900.00  |
| IT002    | 2800.00  |
| SALES001 | 5000.00  |
| SALES002 | 6200.00  |
| SALES003 | 1200.00  |
| 工资合计 | 37102.04 |
+----------+----------+
10 rows in set

打印5以内的自然数平方数：

mysql> select 1, 1*1
    -> union
    -> select 2, 2*2
    -> union
    -> select 3, 3*3
    -> union
    -> select 4, 4*4
    -> union
    -> select 5, 5*5;
+---+-----+
| 1 | 1*1 |
+---+-----+
| 1 |   1 |
| 2 |   4 |
| 3 |   9 |
| 4 |  16 |
| 5 |  25 |
+---+-----+
5 rows in set

列出员工姓名：

mysql> select fname from t_employee
    -> union
    -> select fname from t_tempemployee;
+---------+
| fname   |
+---------+
| Tom     |
| Jerry   |
| Jane    |
| Tina    |
| Smith   |
| NULL    |
| John    |
| Kerry   |
| Stone   |
| Sarani  |
| Yalaha  |
| Konkaya |
| Fo fa   |
+---------+
13 rows in set

分别列出正式员工和临时员工的姓名：

mysql> select '以下是正式员工的姓名'
    -> union all
    -> select fname from t_employee
    -> union all
    -> select '以下是临时工的姓名'
    -> union all
    -> select fname from t_tempemployee;
+----------------------+
| 以下是正式员工的姓名 |
+----------------------+
| 以下是正式员工的姓名 |
| Tom                  |
| Jerry                |
| Jane                 |
| Tina                 |
| Smith                |
| NULL                 |
| John                 |
| Kerry                |
| Stone                |
| 以下是临时工的姓名   |
| Sarani               |
| Tom                  |
| Yalaha               |
| Tina                 |
| Konkaya              |
| Fo fa                |
+----------------------+
17 rows in set