詳解MySQL中UNION的用法。本站提示廣大學習愛好者:(詳解MySQL中UNION的用法)文章只能為提供參考,不一定能成為您想要的結果。以下是詳解MySQL中UNION的用法正文
假如想選擇其他幾個表中的行或從一個單一的表作為一個零丁的成果集行的幾個聚會會議,那末可使用的UNION。
UNION在MySQL4.0以上版本能力可使用。本節解釋若何應用它。
假定有兩個表,潛伏和現實的客戶列表,供給商購置耗材歸並一切三個表中的姓名和地址,來創立一個單一的郵件列表。UNION供給了一種辦法做到這一點。假定三個表有以下內容:
mysql> SELECT * FROM prospect; +---------+-------+------------------------+ | fname | lname | addr | +---------+-------+------------------------+ | Peter | Jones | 482 Rush St., Apt. 402 | | Bernice | Smith | 916 Maple Dr. | +---------+-------+------------------------+ mysql> SELECT * FROM customer; +-----------+------------+---------------------+ | last_name | first_name | address | +-----------+------------+---------------------+ | Peterson | Grace | 16055 Seminole Ave. | | Smith | Bernice | 916 Maple Dr. | | Brown | Walter | 8602 1st St. | +-----------+------------+---------------------+ mysql> SELECT * FROM vendor; +-------------------+---------------------+ | company | street | +-------------------+---------------------+ | ReddyParts, Inc. | 38 Industrial Blvd. | | Parts-to-go, Ltd. | 213B Commerce Park. | +-------------------+---------------------+
這沒關系,假如一切的三個表具有分歧的列名。上面的查詢演示了若何選擇一會兒從三個表的稱號和地址:
mysql> SELECT fname, lname, addr FROM prospect -> UNION -> SELECT first_name, last_name, address FROM customer -> UNION -> SELECT company, '', street FROM vendor; +-------------------+----------+------------------------+ | fname | lname | addr | +-------------------+----------+------------------------+ | Peter | Jones | 482 Rush St., Apt. 402 | | Bernice | Smith | 916 Maple Dr. | | Grace | Peterson | 16055 Seminole Ave. | | Walter | Brown | 8602 1st St. | | ReddyParts, Inc. | | 38 Industrial Blvd. | | Parts-to-go, Ltd. | | 213B Commerce Park. | +-------------------+----------+------------------------+
假如想選擇一切記載,包含反復的,請ALL的第一個UNION症結字:
mysql> SELECT fname, lname, addr FROM prospect -> UNION ALL -> SELECT first_name, last_name, address FROM customer -> UNION -> SELECT company, '', street FROM vendor; +-------------------+----------+------------------------+ | fname | lname | addr | +-------------------+----------+------------------------+ | Peter | Jones | 482 Rush St., Apt. 402 | | Bernice | Smith | 916 Maple Dr. | | Grace | Peterson | 16055 Seminole Ave. | | Bernice | Smith | 916 Maple Dr. | | Walter | Brown | 8602 1st St. | | ReddyParts, Inc. | | 38 Industrial Blvd. | | Parts-to-go, Ltd. | | 213B Commerce Park. | +-------------------+----------+------------------------+