MySQL - UNION 运算符

要在 PHP 程序中使用 UNION 运算符合并表，我们需要使用 mysql2 函数 query() 执行包含 UNION 运算符的 SQL 语句，如下所示 -

sql= " SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
con.query(sql);

要通过 PHP 程序使用 UNION 运算符合并表，我们需要使用名为 executeQuery() 的 JDBC type 4 驱动函数执行包含 UNION 运算符的 SQL 语句，如下所示 -

String sql = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2";
statement.executeQuery(sql);

要通过 PHP 程序使用 UNION 运算符合并表，我们需要使用 MySQL Connector/Python 函数 execute() 执行包含 UNION 运算符的 SQL 语句，如下所示 -

union_query = "SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2"
cursorObj.execute(union_query);

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});
    //连接到 MySQL
    con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");
    //创建数据库
    sql = "create database TUTORIALS"
    con.query(sql);
    //选择数据库
    sql = "USE TUTORIALS"
    con.query(sql);
    //创建 PROSPECT 表
    sql = "CREATE TABLE PROSPECT( fname varchar(400), lname varchar(400), addr varchar(200));"
    con.query(sql);
    //插入记录
    sql = "INSERT INTO PROSPECT (fname, lname, addr) VALUES ('peter', 'Jones', '482 Rush St., Apt. 402'), ('Bernice', 'Smith', '916 Maple Dr.');"
    con.query(sql);
    //创建 CUSTOMER 表
    sql = "CREATE TABLE CUSTOMER( last_name varchar(400), first_name varchar(400), address varchar(200));"
    con.query(sql);
    
    //插入记录
    sql = "INSERT INTO CUSTOMER (last_name, first_name, address) VALUES ('Peterson', 'Grace', '16055 Seminole Ave.'), ('Smith', 'Bernice', '916 Maple Dr.'), ('Brown', 'Walter', '8602 1st St.');"
    con.query(sql);
    //创建 vendor 表
    sql = "CREATE TABLE vendor( company varchar(400), street varchar(400));"
    con.query(sql);
    //插入记录
    sql = "INSERT INTO vendor (company, street) VALUES ('ReddyParts, Inc.', '38 Industrial Blvd.'), ('Parts-to-go, Ltd.', '213B Commerce Park.');"
    con.query(sql);
    //使用 UNION
    sql = "SELECT fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '', street FROM vendor;"
    con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});   

输出

生成的输出如下 -

Connected!
--------------------------
[
  { fname: 'peter', lname: 'Jones', addr: '482 Rush St., Apt. 402' },
  { fname: 'Bernice', lname: 'Smith', addr: '916 Maple Dr.' },
  { fname: 'Grace', lname: 'Peterson', addr: '16055 Seminole Ave.' },
  { fname: 'Walter', lname: 'Brown', addr: '8602 1st St.' },
  { fname: 'ReddyParts, Inc.', lname: '', addr: '38 Industrial Blvd.' },
  { fname: 'Parts-to-go, Ltd.', lname: '', addr: '213B Commerce Park.' }
]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class UnionOperator {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT fname, lname, addr FROM prospect UNION SELECT first_name, last_name, address FROM customer UNION SELECT company, '', street FROM vendor";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String fname = rs.getString("fname");
                String lname = rs.getString("lname");
                String addr = rs.getString("addr");
                System.out.println("First Name: " + fname + ", Last Name: " + lname + ", Address: " + addr);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}                      

输出

获得的输出如下所示 -

Table records: 
First Name: Peter, Last Name: Jones, Address: 482 Rush St., Apt. 402
First Name: Bernice, Last Name: Smith, Address: 916 Maple Dr.
First Name: Grace, Last Name: Peterson, Address: 16055 Seminole Ave.
First Name: Walter, Last Name: Brown, Address: 8602 1st St.
First Name: ReddyParts, Inc., Last Name: , Address: 38 Industrial Blvd.
First Name: Parts-to-go, Ltd., Last Name: , Address: 213B Commerce Park.        

import mysql.connector
#建立连接
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
union_query = f"""
SELECT fname, lname, addr FROM prospect
    UNION
    SELECT first_name, last_name, address FROM customer
    UNION
    SELECT company, '', street FROM vendor;
"""
cursorObj.execute(union_query)
# 获取所有符合条件的行
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                      

输出

以下是上述代码的输出 -

('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.')