MySQL - UNION 操作符

要通过 PHP 程序使用 UNION 操作符组合表，我们需要使用名为query() 的mysql2 函数执行带有 UNION 操作符的 SQL 语句，如下所示：

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

要通过 PHP 程序使用 UNION 操作符组合表，我们需要使用名为executeQuery() 的JDBC 类型 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"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating PROSPECT table
  sql = "CREATE TABLE PROSPECT( fname varchar(400), lname varchar(400), addr varchar(200));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO PROSPECT (fname, lname, addr) VALUES ('peter', 'Jones', '482 Rush St., Apt. 402'), ('Bernice', 'Smith', '916 Maple Dr.');"
  con.query(sql);

  //Creating CUSTOMER table
  sql = "CREATE TABLE CUSTOMER( last_name varchar(400), first_name varchar(400), address varchar(200));"
  con.query(sql);
  
  //Inserting Records
  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);

  //Creating vendor table
  sql = "CREATE TABLE vendor( company varchar(400), street varchar(400));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO vendor (company, street) VALUES ('ReddyParts, Inc.', '38 Industrial Blvd.'), ('Parts-to-go, Ltd.', '213B Commerce Park.');"
  con.query(sql);

  //Using 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://127.0.0.1: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
#establishing the connection
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)
# Fetching all the rows that meet the criteria
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.')