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

来源：互联网发布：大华ipc onvif协议端口编辑：程序博客网时间：2024/06/05 17:04

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 setmysql> 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 setmysql> 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

0 0