第六章：常见 SQL —— 柜员每天到底在户籍总账楼里做什么

新增  INSERT
查询  SELECT
修改  UPDATE
删除  DELETE

INSERT INTO citizens (
  household_id, national_id, full_name, gender, birth_date
) VALUES (
  1, '310101199905080022', '周宁', 'F', '1999-05-08'
);

SELECT citizen_id, full_name, birth_date
FROM citizens
WHERE household_id = 1
  AND status = 'active'
ORDER BY birth_date;

UPDATE citizens
SET status = 'moved'
WHERE citizen_id = 2;

DELETE FROM citizens
WHERE citizen_id = 999999;

SELECT household_id, COUNT(*) AS citizen_count
FROM citizens
WHERE status = 'active'
GROUP BY household_id;

SELECT household_id, COUNT(*) AS citizen_count
FROM citizens
WHERE status = 'active'
GROUP BY household_id
HAVING COUNT(*) >= 3;

SELECT citizen_id, full_name, created_at
FROM citizens
ORDER BY created_at DESC
LIMIT 10;

DESCRIBE citizens;
SHOW INDEX FROM citizens;
SHOW CREATE TABLE citizens;