MySQL基本语法（CRUD、联结查询）JDBC连接、PreparedStatement防SQL注入

一、MySQL基础CRUD操作

1. 数据库和表操作

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 创建表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 查看表结构
DESCRIBE users;

2. INSERT 插入数据

-- 插入单条数据
INSERT INTO users (username, email, age) 
VALUES ('john_doe', 'john@example.com', 28);

-- 插入多条数据
INSERT INTO users (username, email, age) VALUES 
('jane_smith', 'jane@example.com', 32),
('mike_jones', 'mike@example.com', 25);、

 

3. SELECT 查询数据

-- 查询所有数据
SELECT * FROM users;

-- 条件查询
SELECT username, email FROM users WHERE age > 25;

-- 排序
SELECT * FROM users ORDER BY age DESC;

-- 分页查询
SELECT * FROM users LIMIT 5 OFFSET 0;  -- 第一页，每页5条
SELECT * FROM users LIMIT 5 OFFSET 5;  -- 第二页

-- 聚合函数
SELECT COUNT(*) AS total_users FROM users;
SELECT AVG(age) AS average_age FROM users;

4. UPDATE 更新数据

-- 更新单条记录
UPDATE users SET age = 29 WHERE username = 'john_doe';

-- 批量更新
UPDATE users SET created_at = '2023-01-01' WHERE created_at IS NULL;

5. DELETE 删除数据

-- 删除特定记录
DELETE FROM users WHERE username = 'mike_jones';

-- 清空表(小心使用！)
-- DELETE FROM users;

二、MySQL高级查询：联结(JOIN)

当数据分布在多个表中时，我们需要使用JOIN操作来关联查询。

1. 创建示例表

CREATE TABLE departments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

 2. 内连接(INNER JOIN)

-- 基本内连接
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- 带条件的内连接
SELECT e.name, e.salary, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE e.salary > 5000;

 3. 左连接(LEFT JOIN)   

           

-- 左连接会返回左表的所有记录，即使右表没有匹配
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

 4. 右连接(RIGHT JOIN)

-- 右连接会返回右表的所有记录，即使左表没有匹配
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

 5. 全连接(FULL JOIN)

-- MySQL不直接支持FULL JOIN，但可以通过UNION实现
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL;

  6. 自连接(SELF JOIN)       

         

-- 假设员工表中有manager_id字段指向其他员工
SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

 三、JDBC连接MySQL

Java Database Connectivity (JDBC) 是Java连接数据库的标准API。

1. 基本JDBC连接流程

import java.sql.*;

public class JdbcExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/mydb";
        String username = "root";
        String password = "password";
        
        try (Connection conn = DriverManager.getConnection(url, username, password);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
            
            // 处理结果集
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") 
                    + ", Name: " + rs.getString("username"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

 2. JDBC事务处理

try (Connection conn = DriverManager.getConnection(url, username, password)) {
    // 关闭自动提交
    conn.setAutoCommit(false);
    
    try (PreparedStatement pstmt = conn.prepareStatement(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?")) {
        
        // 转账操作：从账户1转100到账户2
        pstmt.setDouble(1, 100);
        pstmt.setInt(2, 1);
        pstmt.executeUpdate();
        
        pstmt.setDouble(1, -100);
        pstmt.setInt(2, 2);
        pstmt.executeUpdate();
        
        // 提交事务
        conn.commit();
    } catch (SQLException e) {
        // 回滚事务
        conn.rollback();
        e.printStackTrace();
    }
}

 四、PreparedStatement防SQL注入

SQL注入是一种常见的安全漏洞，使用PreparedStatement可以有效防止

1. SQL注入示例

// 危险！容易受到SQL注入攻击
String userInput = "admin'; DROP TABLE users; --";
String sql = "SELECT * FROM users WHERE username = '" + userInput + "'";
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql);

 2. 使用PreparedStatement防止注入

String userInput = "admin'; DROP TABLE users; --";
String sql = "SELECT * FROM users WHERE username = ?";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    pstmt.setString(1, userInput);  // 安全，参数会被正确转义
    ResultSet rs = pstmt.executeQuery();
    // 处理结果...
}

3. PreparedStatement批量操作

String sql = "INSERT INTO users (username, email, age) VALUES (?, ?, ?)";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
    // 批量添加用户
    pstmt.setString(1, "user1");
    pstmt.setString(2, "user1@example.com");
    pstmt.setInt(3, 25);
    pstmt.addBatch();
    
    pstmt.setString(1, "user2");
    pstmt.setString(2, "user2@example.com");
    pstmt.setInt(3, 30);
    pstmt.addBatch();
    
    // 执行批量操作
    int[] results = pstmt.executeBatch();
    System.out.println("插入了 " + results.length + " 条记录");
}