MySQL&C++快速上手（九）数据联合查询

联合查询：

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION [ALL | DISTINCT]
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];
参数
expression1, expression2, ... expression_n: 要检索的列。
tables: 要检索的数据表。
WHERE conditions: 可选， 检索条件。
DISTINCT: 可选，删除结果集中重复的数据，默认为此选项。
ALL: 可选，返回所有结果集，包含重复数据。

代码范例：

	MYSQL* mysql = new MYSQL();
    MYSQL* pDB = mysql_init(mysql);
    if (pDB == NULL) {
        std::cout << "mysql_init failed" << std::endl;
        return -1;
    }

    pDB = mysql_real_connect(pDB, "localhost", "root", "root", "mysql", 3306, NULL, 0);

    if (pDB) {

        std::string sql = "CREATE DATABASE hello";

        // 创建数据库
        int ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        MYSQL_RES* res = mysql_use_result(mysql);
        if (res) {
            show_result(res);

            mysql_free_result(res);
        }


        // 授予权限
        sql = "GRANT ALL ON hello.* TO 'hello'@'localhost';";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        res = mysql_use_result(mysql);
        if (res) {
            show_result(res);

            mysql_free_result(res);
        }

        // 指定数据库
        sql = "USE hello"; 
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

		// 设置数据库字符编码
        sql = "SET NAMES 'utf8';";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

		// 设置数据库字符编码
        sql = "SET CHARACTER SET utf8;";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        // 创建表
        sql = "CREATE TABLE IF NOT EXISTS `hello` (`编号` NVARCHAR(16) PRIMARY KEY,";
        sql += "`age` INT NOT NULL DEFAULT 18";
        sql += ")ENGINE = InnoDB DEFAULT CHARSET = utf8; ";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "CREATE TABLE IF NOT EXISTS `teacher` (`编号` NVARCHAR(16) PRIMARY KEY,";
        sql += "`age` INT NOT NULL DEFAULT 18";
        sql += ")ENGINE = InnoDB DEFAULT CHARSET = utf8; ";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

		// 插入数据
        sql = "INSERT INTO `hello` (`编号`, `age`) VALUES(\"9527\", 99)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "INSERT INTO `hello` (`编号`, `age`) VALUES(\"9528\", 23)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        sql = "INSERT INTO `teacher` (`编号`, `age`) VALUES(\"9529\", 99)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }


        sql = "INSERT INTO `teacher` (`编号`, `age`) VALUES(\"9530\", 100)";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

		// 执行联合查询
        sql = "SELECT age FROM `hello` UNION SELECT age FROM `teacher`;";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }
        res = mysql_store_result(mysql);
        if (res) {
            show_result(res);
            mysql_free_result(res);
        }
        
        // 删除表
        sql = "DROP TABLE `hello`";
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        // 删除数据库
        sql = "DROP DATABASE hello"; // SQL语句可替换为自己实际需要执行的语句
        ret = mysql_real_query(mysql, sql.c_str(), (unsigned long)sql.size());
        if (ret != 0) {
            std::cout << "mysql error: " << mysql_error(pDB) << std::endl;
            return -1;
        }

        mysql_close(pDB);
        std::cout << pDB << std::endl;
        delete mysql;
    }

    mysql = nullptr;

    return 0;

执行结果：