MySQL 常用 SQL 命令大全：從建表到 C++ 連接池實戰

一、DDL：庫表結構管理

建庫建表

CREATE DATABASE test_db CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
USE test_db;

CREATE TABLE test_info (
  id INT NOT NULL,
  content VARCHAR(64) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

修改表結構

-- 增加列
ALTER TABLE t_icf_day ADD new_field_id INT(5);
ALTER TABLE t_icf_day ADD COLUMN day_id BIGINT PRIMARY KEY AUTO_INCREMENT;

-- 修改列類型/重命名
ALTER TABLE t1 CHANGE a b BIGINT NOT NULL;
ALTER TABLE infos CHANGE list list TINYINT NOT NULL DEFAULT 0;

-- 設置/刪除主鍵
ALTER TABLE userinfo ADD PRIMARY KEY (userId);
ALTER TABLE t2 DROP COLUMN c;

-- 重命名表
ALTER TABLE t1 RENAME t2;

-- 刪除表
DROP TABLE IF EXISTS `table_name`;

索引管理

-- 創建
CREATE INDEX idx_employee ON employee(name);
CREATE TABLE test1 (test1_id CHAR(4), name CHAR(20), INDEX idx_name(name(10)));

-- 查看
SHOW INDEX FROM employee;

-- 刪除
DROP INDEX idx_employee ON employee;
ALTER TABLE products DROP INDEX idx_products;

二、DML：數據增刪改查

插入數據

-- 單條
INSERT INTO employee VALUES ('200301', 'zhangsan', 'm', '1978-05-08');

-- 批量
INSERT INTO test_info VALUES (2010, 'hello, world');

-- 導入 CSV
LOAD DATA LOCAL INFILE '/tmp/test.csv' INTO TABLE test_info
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '"'
LINES TERMINATED BY '\r\n';

查詢與去重

-- 多表關聯
SELECT c.nom, e.nom 
FROM consultant c, affaire a, besoin b, salarie sa, site s, entreprise e
WHERE c.consultant_id=a.consultant_id
  AND a.besoin_id=b.besoin_id
  AND b.salarie_id=sa.salarie_id
  AND sa.site_id=s.site_id
  AND s.entreprise_id=e.entreprise_id;

-- 條件插入
INSERT INTO gansu_icf_hist_d 
SELECT b.* FROM gansu_t_icf_day a, T_ICF_HIST_D b
WHERE a.c_kisyu=b.c_kisyu AND a.c_gouki=b.c_gouki;

-- 去重查詢
SELECT * FROM gansu_day 
GROUP BY c_kisyu, d_hassei, c_gouki 
HAVING COUNT(*) > 1;

更新與刪除

UPDATE employee SET name='lisi' WHERE id='200301';
DELETE FROM test_info WHERE id=2010;

三、進階：遠程訪問與權限

-- 創建遠程用戶
CREATE USER 'app_user'@'%' IDENTIFIED BY 'StrongPass123!';
GRANT SELECT,INSERT,UPDATE,DELETE ON app_db.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

-- 修改綁定地址 (my.cnf)
bind-address = 0.0.0.0
# 重啟生效

四、C/C++ 連接 MySQL 實戰

編譯鏈接配置

# g++ 編譯參數
g++ main.cpp -o app -I/usr/include/mysql -L/usr/lib/mysql -lmysqlclient

# Eclipse CDT 配置
Properties → C/C++ Build → Settings:
  Libraries (-l): mysqlclient
  Library search path: /usr/lib/mysql
  Includes: /usr/include/mysql

通用查詢封裝 (返回 MYSQL_RES)

MYSQL_RES* QuerySQL(const char* sql) {
    MYSQL* conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, "localhost", "root", "password", "dbname", 0, NULL, 0)) {
        fprintf(stderr, "Connect failed: %s\n", mysql_error(conn));
        return NULL;
    }
    if (mysql_query(conn, sql)) {
        fprintf(stderr, "Query failed: %s\n", mysql_error(conn));
        mysql_close(conn);
        return NULL;
    }
    MYSQL_RES* res = mysql_store_result(conn);
    mysql_close(conn);
    return res;
}

非查詢執行 (INSERT/UPDATE/DELETE)

void ExecSQL(const char* sql) {
    MYSQL* conn = mysql_init(NULL);
    if (!mysql_real_connect(conn, "localhost", "root", "password", "dbname", 0, NULL, 0)) {
        fprintf(stderr, "Connect failed: %s\n", mysql_error(conn));
        return;
    }
    if (mysql_query(conn, sql)) {
        fprintf(stderr, "Exec failed: %s\n", mysql_error(conn));
    } else {
        printf("Affected rows: %lld\n", mysql_affected_rows(conn));
    }
    mysql_close(conn);
}

五、避坑指南

總結

掌握DDL 規範、DML 模式、索引策略、C API 封裝，是 MySQL 開發與運維的基本功。建議將常用 SQL 整理為團隊代碼片段庫，統一風格，減少低級錯誤。