代码片段仓库

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';

-- Pagination query using LIMIT/OFFSET
SELECT * FROM products
  ORDER BY id
  LIMIT 10 OFFSET 20;  -- Page 3 (assuming 10 items per page)

WITH RECURSIVE emp_hierarchy (id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employees WHERE id = 100  -- Anchor
  UNION ALL
   SELECT e.id, e.name, e.manager_id FROM employees AS e
   JOIN emp_hierarchy ON e.manager_id = emp_hierarchy.id
)
SELECT * FROM emp_hierarchy;

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$stmt = $mysqli->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
$stmt->bind_param('ss', $name, $email);
$stmt->execute();
$stmt->close();