SQL基础介绍

结构化查询语言(Structured Query Language)是操作关系型数据库的标准语言

学习目标

理解SQL在Java应用中的作用，掌握核心操作语句的基本结构

SQL在Java应用中的角色

  • Java应用通过JDBC驱动与数据库交互
  • SQL是Java与数据库沟通的语言
  • CRUD操作(增删改查)是数据库交互的核心
  • SQL语句通过Statement/PreparedStatement执行

示例数据表结构

我们将使用员工表(employees)作为示例：

employees

id INT PK
name VARCHAR(50)
department VARCHAR(50)
salary DECIMAL(10,2)
hire_date DATE

学习小贴士

SQL语句不区分大小写，但为了可读性，通常将SQL关键字大写（SELECT, FROM, WHERE等），表名和列名保持与数据库一致的大小写。

SELECT查询语句

从数据库中检索数据的基本操作

学习目标

掌握SELECT语句的各种用法，包括条件查询、排序、聚合和连接

1

基本SELECT查询

检索所有列和行：

SELECT * FROM employees;

检索特定列：

SELECT id, name, department FROM employees;
2

条件查询(WHERE)

使用WHERE子句过滤结果：

SELECT * FROM employees WHERE department = 'IT';

多条件查询：

SELECT * FROM employees WHERE department = 'IT' AND salary > 5000;
3

排序(ORDER BY)

按单个列排序：

SELECT * FROM employees ORDER BY hire_date DESC;

按多列排序：

SELECT * FROM employees ORDER BY department ASC, salary DESC;
4

聚合函数

常用聚合函数：

-- 计算平均值 SELECT AVG(salary) AS avg_salary FROM employees; -- 统计数量 SELECT COUNT(*) FROM employees WHERE department = 'HR';
5

分组查询(GROUP BY)

按部门分组统计：

SELECT department, AVG(salary) AS avg_salary, COUNT(*) AS employee_count FROM employees GROUP BY department;
预期结果：
department
avg_salary
employee_count
IT
6500.00
5
HR
5200.00
3

SELECT练习

  • 查询所有工资大于6000的员工姓名和部门
  • 统计每个部门的最高工资和最低工资
  • 查询2023年入职的所有员工，按入职日期倒序排列
  • 计算公司所有员工的平均工资

INSERT插入语句

向数据库表中添加新记录

学习目标

掌握INSERT语句的各种形式，理解批量插入和参数化查询

1

基本INSERT语句

插入完整记录：

INSERT INTO employees (name, department, salary, hire_date) VALUES ('张三', 'IT', 7500.00, '2023-01-15');

插入部分字段（允许NULL的字段）：

INSERT INTO employees (name, department) VALUES ('李四', 'HR');
2

批量插入

单条语句插入多条记录：

INSERT INTO employees (name, department, salary) VALUES ('王五', 'Finance', 8200.00), ('赵六', 'IT', 6800.00), ('钱七', 'Marketing', 7100.00);
3

INSERT INTO SELECT

从其他表复制数据：

-- 将IT部门员工复制到IT_employees表 INSERT INTO IT_employees (name, salary) SELECT name, salary FROM employees WHERE department = 'IT';

性能优化建议

插入大量数据时，使用批量插入或PreparedStatement的addBatch()方法可以显著提高性能。对于10,000行以上的数据插入，考虑使用LOAD DATA INFILE(MySQL)或COPY命令(PostgreSQL)。

UPDATE更新语句

修改数据库表中的现有记录

学习目标

掌握UPDATE语句的用法，理解事务安全更新

1

基本UPDATE语句

更新所有记录（谨慎使用）：

UPDATE employees SET salary = salary * 1.1;

带条件的更新：

UPDATE employees SET salary = salary * 1.15, department = 'Senior ' || department WHERE salary < 8000 AND hire_date < '2020-01-01';
2

基于子查询的更新

使用子查询更新数据：

UPDATE employees e SET salary = ( SELECT AVG(salary) FROM employees WHERE department = e.department ) WHERE salary < ( SELECT AVG(salary) FROM employees WHERE department = e.department );
3

事务安全更新

Java中使用事务确保数据一致性：

try (Connection conn = DriverManager.getConnection(url, user, password)) { // 关闭自动提交 conn.setAutoCommit(false); // 执行更新操作 try (PreparedStatement stmt = conn.prepareStatement( "UPDATE employees SET salary = ? WHERE id = ?")) { stmt.setBigDecimal(1, new BigDecimal("8500.00")); stmt.setInt(2, 101); int rowsUpdated = stmt.executeUpdate(); // 提交事务 conn.commit(); System.out.println("更新了 " + rowsUpdated + " 行数据"); } catch (SQLException e) { // 回滚事务 conn.rollback(); e.printStackTrace(); } }

重要安全提示

UPDATE语句如果没有WHERE条件会更新整个表！生产环境中执行UPDATE前，最好先用SELECT语句确认要更新的记录。在Java中，使用PreparedStatement可以防止SQL注入攻击。

常见错误与解决方案

识别并解决SQL操作中的常见问题

错误1：语法错误

  • 表现：SQLException: Syntax error
  • 原因：关键字拼写错误、缺少逗号、引号不匹配等
  • 解决方案：仔细检查SQL语句，使用IDE的SQL格式化功能

错误2：违反约束

  • 表现：SQLException: Integrity constraint violation
  • 原因：插入重复主键、违反外键约束、非空字段为空等
  • 解决方案：检查数据是否符合表约束，捕获异常后回滚事务

错误3：SQL注入风险

  • 表现：拼接用户输入到SQL语句中
  • 原因：使用Statement而非PreparedStatement
  • 解决方案：永远使用PreparedStatement和参数化查询
错误类型 示例 正确写法
SQL注入风险 String sql = "SELECT * FROM users WHERE username='" + input + "'"; PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE username=?");
stmt.setString(1, input);
缺少引号 UPDATE products SET name = New Product WHERE id = 123 UPDATE products SET name = 'New Product' WHERE id = 123
隐式类型转换 SELECT * FROM orders WHERE order_date > '2023-01-01' SELECT * FROM orders WHERE order_date > DATE '2023-01-01'

总结与最佳实践

高效安全地使用SQL操作数据库

SQL操作最佳实践

  • 始终使用PreparedStatement而不是Statement
  • 在UPDATE/DELETE操作前使用SELECT确认目标数据
  • 批量操作时使用addBatch()和executeBatch()
  • SELECT时指定需要的列而不是使用SELECT *
  • 为WHERE条件和JOIN字段创建索引

安全注意事项

  • 永远不要拼接用户输入到SQL语句中
  • 使用最小权限原则配置数据库用户
  • 生产环境避免使用动态SQL
  • 定期审计SQL查询日志
  • 使用参数化查询或存储过程

综合练习

创建一个Java程序，完成以下操作：

  1. 连接到数据库
  2. 插入3条新的员工记录
  3. 查询所有IT部门员工并按工资排序
  4. 给工作超过5年的员工加薪10%
  5. 处理可能发生的异常
  6. 使用事务确保所有操作要么全部成功要么全部回滚

下一步学习

掌握了基本SQL操作后，可以继续学习：JOIN操作、子查询、事务管理、存储过程、数据库连接池(如HikariCP)、以及ORM框架(如Hibernate和MyBatis)。