数据库编程：MySQL数据库编程常用语句

MySQL数据库编程中，常用语句可以大致分为几类：数据查询语言（DQL）、数据操作语言（DML）、数据定义语言（DDL）、数据控制语言（DCL）以及事务控制语言（TCL）。

数据库操作

-- 创建数据库
CREATE DATABASE dbname;

-- 删除数据库
DROP DATABASE dbname;

-- 选择数据库
USE dbname;

-- 显示所有数据库
SHOW DATABASES;

表操作

-- 创建表
CREATE TABLE tablename (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 删除表
DROP TABLE tablename;

-- 显示表结构
DESCRIBE tablename;
DESC tablename;

-- 显示所有表
SHOW TABLES;

-- 修改表结构
ALTER TABLE tablename ADD COLUMN email VARCHAR(100);
ALTER TABLE tablename DROP COLUMN age;
ALTER TABLE tablename MODIFY COLUMN name VARCHAR(100);
ALTER TABLE tablename RENAME TO new_tablename;

数据操作

增删改查(CRUD)

-- 插入数据
INSERT INTO tablename (name, age) VALUES ('张三', 25);
INSERT INTO tablename (name, age) VALUES ('李四', 30), ('王五', 28);

-- 查询数据
SELECT * FROM tablename;
SELECT name, age FROM tablename;
SELECT * FROM tablename WHERE age > 25;
SELECT * FROM tablename WHERE name LIKE '张%';
SELECT * FROM tablename ORDER BY age DESC;
SELECT * FROM tablename LIMIT 10;
SELECT * FROM tablename LIMIT 5, 10; -- 从第5条开始，取10条

-- 更新数据
UPDATE tablename SET age = 26 WHERE name = '张三';
UPDATE tablename SET age = age + 1;

-- 删除数据
DELETE FROM tablename WHERE id = 1;
DELETE FROM tablename; -- 删除所有数据，但保留表结构

高级查询

-- 分组查询
SELECT age, COUNT(*) FROM tablename GROUP BY age;
SELECT age, COUNT(*) FROM tablename GROUP BY age HAVING COUNT(*) > 1;

-- 连接查询
SELECT a.name, b.order_id 
FROM users a 
JOIN orders b ON a.id = b.user_id;

-- 子查询
SELECT name FROM users WHERE id IN (SELECT user_id FROM orders);

-- 联合查询
SELECT name FROM users WHERE age < 20
UNION
SELECT name FROM users WHERE age > 30;

索引操作

-- 创建索引
CREATE INDEX idx_name ON tablename(name);
CREATE UNIQUE INDEX idx_unique_email ON tablename(email);

-- 删除索引
DROP INDEX idx_name ON tablename;

事务操作

-- 开始事务
START TRANSACTION;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

-- 设置自动提交
SET autocommit = 0; -- 关闭自动提交
SET autocommit = 1; -- 开启自动提交

用户和权限管理

-- 创建用户
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

-- 授予权限
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
GRANT SELECT, INSERT ON dbname.* TO 'username'@'localhost';

-- 撤销权限
REVOKE ALL PRIVILEGES ON dbname.* FROM 'username'@'localhost';

-- 刷新权限
FLUSH PRIVILEGES;

-- 删除用户
DROP USER 'username'@'localhost';

常用函数

-- 字符串函数
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;
SELECT UPPER(name), LOWER(name) FROM users;
SELECT SUBSTRING(name, 1, 3) FROM users;
SELECT TRIM('  hello  ');

-- 数值函数
SELECT ROUND(price, 2) FROM products;
SELECT CEIL(price), FLOOR(price) FROM products;
SELECT RAND(); -- 随机数

-- 日期函数
SELECT NOW(), CURDATE(), CURTIME();
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s');
SELECT DATEDIFF('2023-12-31', NOW());

-- 聚合函数
SELECT COUNT(*) FROM users;
SELECT AVG(age) FROM users;
SELECT MAX(age), MIN(age) FROM users;
SELECT SUM(price) FROM orders;

存储过程和函数

-- 创建存储过程
DELIMITER //
CREATE PROCEDURE get_user_by_age(IN age_param INT)
BEGIN
    SELECT * FROM users WHERE age = age_param;
END //
DELIMITER ;

-- 调用存储过程
CALL get_user_by_age(25);

-- 创建函数
DELIMITER //
CREATE FUNCTION get_user_count() RETURNS INT
BEGIN
    DECLARE user_count INT;
    SELECT COUNT(*) INTO user_count FROM users;
    RETURN user_count;
END //
DELIMITER ;

-- 调用函数
SELECT get_user_count();

视图

-- 创建视图
CREATE VIEW user_orders AS
SELECT u.name, o.order_id, o.order_date
FROM users u JOIN orders o ON u.id = o.user_id;

-- 使用视图
SELECT * FROM user_orders;

-- 删除视图
DROP VIEW user_orders;

这些是MySQL数据库编程中最常用的语句，涵盖了数据库管理、表操作、数据操作、索引、事务、用户权限等方面。

MySQL Connector/C++ 常用函数操作

MySQL Connector/C++ 是 MySQL 提供的官方 C++ 接口，用于连接和操作 MySQL 数据库。以下是常用函数操作及其对应 SQL 语句的实现。

1. 连接数据库

#include <mysql_driver.h>
#include <mysql_connection.h>

using namespace sql;

// 创建连接
mysql::MySQL_Driver *driver;
Connection *con;

driver = mysql::get_mysql_driver_instance();
con = driver->connect("tcp://127.0.0.1:3306", "username", "password");

// 选择数据库
con->setSchema("database_name");

// 关闭连接
delete con;

2. 执行查询

#include <cppconn/statement.h>
#include <cppconn/resultset.h>

// 创建Statement对象
Statement *stmt = con->createStatement();

// 执行查询 - 对应 SELECT 语句
ResultSet *res = stmt->executeQuery("SELECT * FROM users");

// 遍历结果集
while (res->next()) {
    cout << "ID: " << res->getInt("id");
    cout << ", Name: " << res->getString("name");
    cout << ", Age: " << res->getInt("age") << endl;
}

// 释放资源
delete res;
delete stmt;

3. 执行更新操作

// 执行INSERT, UPDATE, DELETE - 返回受影响的行数
int updateCount = stmt->executeUpdate("INSERT INTO users(name, age) VALUES('John', 30)");

// 预处理语句防止SQL注入
PreparedStatement *pstmt = con->prepareStatement("INSERT INTO users(name, age) VALUES(?, ?)");
pstmt->setString(1, "John");
pstmt->setInt(2, 30);
updateCount = pstmt->executeUpdate();

delete pstmt;

4. 事务处理

// 关闭自动提交
con->setAutoCommit(false);

try {
    // 执行多个操作
    stmt->executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
    stmt->executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
    
    // 提交事务
    con->commit();
} catch (SQLException &e) {
    // 出错时回滚
    con->rollback();
    cerr << "SQLException: " << e.what() << endl;
}

// 恢复自动提交
con->setAutoCommit(true);

5. 获取元数据

// 获取数据库元数据
DatabaseMetaData *dbmeta = con->getMetaData();

// 获取表信息
ResultSet *tables = dbmeta->getTables(NULL, NULL, "%", {"TABLE"});
while (tables->next()) {
    cout << "Table: " << tables->getString("TABLE_NAME") << endl;
}

// 获取列信息
ResultSet *columns = dbmeta->getColumns(NULL, NULL, "users", "%");
while (columns->next()) {
    cout << "Column: " << columns->getString("COLUMN_NAME");
    cout << " Type: " << columns->getString("TYPE_NAME") << endl;
}

delete tables;
delete columns;
delete dbmeta;

6. 批处理操作

// 创建预处理语句
PreparedStatement *pstmt = con->prepareStatement("INSERT INTO users(name, age) VALUES(?, ?)");

// 添加批处理
pstmt->setString(1, "Alice");
pstmt->setInt(2, 25);
pstmt->addBatch();

pstmt->setString(1, "Bob");
pstmt->setInt(2, 30);
pstmt->addBatch();

// 执行批处理
vector<int> updateCounts = pstmt->executeBatch();

delete pstmt;

7. 处理结果集元数据

ResultSet *res = stmt->executeQuery("SELECT * FROM users");
ResultSetMetaData *meta = res->getMetaData();

int columns = meta->getColumnCount();
for (int i = 1; i <= columns; i++) {
    cout << "Column " << i << ": " << meta->getColumnName(i);
    cout << " (" << meta->getColumnTypeName(i) << ")" << endl;
}

delete meta;
delete res;

8. 存储过程调用

// 调用存储过程
CallableStatement *cstmt = con->prepareCall("{CALL get_user_by_age(?)}");
cstmt->setInt(1, 25); // 设置输入参数

// 执行并获取结果
ResultSet *res = cstmt->executeQuery();

// 如果有输出参数
// cstmt->registerOutParameter(2, Types::INTEGER);
// cstmt->execute();
// int output = cstmt->getInt(2);

delete res;
delete cstmt;

9. 错误处理

try {
    Statement *stmt = con->createStatement();
    ResultSet *res = stmt->executeQuery("SELECT * FROM non_existent_table");
    delete res;
    delete stmt;
} catch (SQLException &e) {
    cerr << "SQL Error: " << e.what() << endl;
    cerr << "Error code: " << e.getErrorCode() << endl;
    cerr << "SQL state: " << e.getSQLState() << endl;
}

10. 连接池使用

#include <cppconn/connection.h>
#include <cppconn/driver.h>
#include <cppconn/exception.h>
#include <cppconn/resultset.h>
#include <cppconn/statement.h>
#include <cppconn/prepared_statement.h>
#include <cppconn/connection_pool.h>

// 创建连接池
ConnectionPool *pool = mysql::get_mysql_driver_instance()->createConnectionPool();

// 配置连接池
pool->setURL("tcp://127.0.0.1:3306");
pool->setUser("username");
pool->setPassword("password");
pool->setSchema("database_name");
pool->setPoolSize(5); // 设置连接池大小

// 从连接池获取连接
Connection *con = pool->getConnection();

// 使用连接...

// 将连接返回连接池
pool->releaseConnection(con);

// 销毁连接池
delete pool;

以上是 MySQL Connector/C++ 中常用的函数操作，涵盖了数据库连接、查询执行、事务处理、元数据获取等常见操作。使用时需要注意及时释放资源（如 Connection、Statement、ResultSet 等对象），避免内存泄漏。