Java优学网MySQL子查询实战指南：从基础到高级应用，轻松掌握高效查询技巧

SELECT employee_name FROM employees WHERE department_id IN (

SELECT department_id 
FROM departments 
WHERE location = '北京'

);

SELECT employee_name, salary, department_id FROM employees e1 WHERE salary = (

SELECT MAX(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id

);

-- 错误写法 SELECT employee_name, salary FROM employees WHERE salary = (

SELECT AVG(salary) 
FROM employees 
GROUP BY department_id

);

SELECT department_id, employee_name, salary FROM (

SELECT department_id, employee_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank_num
FROM employees

) ranked_employees WHERE rank_num <= 3;

String sql = "SELECT employee_name, salary, department_id " +

        "FROM employees e " +
        "WHERE salary > (SELECT AVG(salary) FROM employees WHERE department_id = ?) " +
        "AND department_id = ?";

try (PreparedStatement pstmt = connection.prepareStatement(sql)) {

pstmt.setInt(1, departmentId);
pstmt.setInt(2, departmentId);

try (ResultSet rs = pstmt.executeQuery()) {
    while (rs.next()) {
        String name = rs.getString("employee_name");
        double salary = rs.getDouble("salary");
        // 处理结果...
    }
}

}

DELIMITER $$ CREATE PROCEDURE GenerateTestData() BEGIN

DECLARE i INT DEFAULT 0;
WHILE i < 100000 DO
    INSERT INTO employees (name, salary, department_id, hire_date)
    VALUES (CONCAT('Employee', i), 
            RAND() * 10000 + 3000,
            FLOOR(RAND() * 10) + 1,
            DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 3650) DAY));
    SET i = i + 1;
END WHILE;

END$$ DELIMITER ;