MySQL数据操作

七、数据增删改操作（单元7）

1. SQL核心操作与实践

• 增删改语法
  • 插入：INSERT INTO table (col1, col2) VALUES (val1, val2) 或 INSERT INTO table SET col1=val1, col2=val2
  • 修改：UPDATE table SET col=val WHERE condition
  • 删除：DELETE FROM table WHERE condition（逐条删除）、TRUNCATE TABLE table（清空表，不可回滚）
• 批量操作优化
  • 批量插入：INSERT INTO table VALUES (1,'a'), (2,'b'), (3,'c')
  • 案例：批量导入10万条数据时，关闭自动提交：

    START TRANSACTION;  
    FOR i IN 1..100000 DO  
        INSERT INTO log_table VALUES (i, NOW());  
    END FOR;  
    COMMIT;  
    

2. Java代码实现与事务管理

• JDBC批量操作

  public void batchInsert(List<Data> dataList) {  
      String sql = "INSERT INTO data_table (id, name, create_time) VALUES (?, ?, ?)";  
      try (Connection conn = dataSource.getConnection();  
           PreparedStatement pstmt = conn.prepareStatement(sql)) {  
          conn.setAutoCommit(false);  // 关闭自动提交  
          for (Data data : dataList) {  
              pstmt.setLong(1, data.getId());  
              pstmt.setString(2, data.getName());  
              pstmt.setTimestamp(3, new Timestamp(data.getCreateTime().getTime()));  
              pstmt.addBatch();  
          }  
          pstmt.executeBatch();  
          conn.commit();  
      } catch (SQLException e) {  
          conn.rollback();  // 出错回滚  
          e.printStackTrace();  
      }  
  }  
  

• Spring Data JPA事务案例

  @Service  
  public class UserService {  
      @Autowired  
      private UserRepository userRepo;  
      
      @Transactional  
      public void updateUserAndLog(Long userId, String newName) {  
          User user = userRepo.findById(userId)  
              .orElseThrow(() -> new EntityNotFoundException("用户不存在"));  
          user.setName(newName);  
          userRepo.save(user);  
          
          // 记录操作日志（同一事务中）  
          Log log = new Log();  
          log.setUserId(userId);  
          log.setOperation("修改用户名");  
          logRepo.save(log);  
      }  
  }  
  

八、数据查询操作（单元8）

1. SQL查询核心语法

• 基础查询
  • 字段计算：SELECT sno, score*0.9 AS new_score FROM score
  • 别名与排序：SELECT sname AS 姓名, class FROM student ORDER BY class, sname DESC
• 条件与聚合查询
  • 范围查询：WHERE score BETWEEN 90 AND 100
  • 分组过滤：SELECT sno, AVG(score) FROM score GROUP BY sno HAVING AVG(score)>=85
• 高级查询
  • 连接查询：

    -- 内连接：查询学生成绩  
    SELECT s.sno, s.name, c.cno, sc.score  
    FROM student s INNER JOIN score sc ON s.sno=sc.sno  
    INNER JOIN course c ON sc.cno=c.cno;  
    

  • 子查询：

    -- 查询选修"数据库技术"的学生成绩  
    SELECT sno, score FROM score  
    WHERE cno=(SELECT cno FROM course WHERE cname='数据库技术');  
    

2. Java项目中的查询优化

• MyBatis动态SQL

  <select id="getStudentsByCondition" resultType="Student">  
      SELECT * FROM student  
      <where>  
          <if test="name != null">name LIKE CONCAT('%', #{name}, '%')</if>  
          <if test="class != null">AND class = #{class}</if>  
          <if test="minAge != null">AND YEAR(NOW()) - YEAR(birthday) >= #{minAge}</if>  
      </where>  
      ORDER BY create_time DESC  
      LIMIT #{offset}, #{limit}  
  </select>  
  

• 分页与性能优化
  • 大数据量分页：SELECT * FROM table ORDER BY id LIMIT 100000, 10 性能较差，可优化为：

    SELECT * FROM table t1  
    JOIN (SELECT id FROM table ORDER BY id LIMIT 100000, 10) t2 ON t1.id = t2.id;  
    

  • Java代码中使用流式查询（Cursor）处理海量数据：

    @Repository  
    public interface StudentRepository {  
        @Query("SELECT s FROM Student s WHERE s.class = :class")  
        Stream<Student> findByClassStream(@Param("class") String class);  
    }