Inserting, updating and deleting data
AdvancedINSERT INTO table (fields) VALUES (values) adds; UPDATE table SET field = new WHERE condition changes; DELETE FROM table WHERE condition removes. UPDATE/DELETE always carry WHERE or they hit the whole table; SELECT before to confirm, after to verify.
- Three writing statements: INSERT INTO adds, UPDATE SET changes, DELETE FROM removes.
- The safe INSERT lists its fields, with VALUES aligned one to one.
- UPDATE and DELETE always take a WHERE; without it the whole table is hit and no warning appears.
文體小知識：編程教學課每個概念都配一段可以親手試的代碼例子，照住輸入、改一改，最快學會。
§Text Passage
1SELECT only reads; to change a database's content there are three writing statements: INSERT INTO adds a record, UPDATE SET changes existing ones, DELETE FROM removes them. A new student joins with INSERT, a class change or resit score uses UPDATE, a withdrawal uses DELETE — all daily database upkeep is these three moves.
2The safe form of INSERT names its fields: INSERT INTO students (student_id, name, class, score) VALUES (2023011, 'Amy', '3A', 88);. Listing fields explicitly keeps the sentence correct even when the table gains new fields later. Updating: UPDATE students SET score = 92 WHERE student_id = 2023011;; deleting: DELETE FROM students WHERE student_id = 2023011;.
3UPDATE and DELETE carry a life-saving rule: always write WHERE. UPDATE students SET score = 90; without WHERE changes every score in the table to 90 at once; DELETE FROM students; without WHERE empties the whole table — and the database never asks 'are you sure?'. In real incidents, many an engineer lost an entire dataset exactly here.
4So professionals follow a safety routine: run a SELECT first to see exactly which records the WHERE will hit, only then execute the UPDATE or DELETE, and SELECT again afterwards to verify; important databases get backed up beforehand. Data integrity is also guarded by the database itself: an INSERT duplicating a primary key is refused, text typed into a score field errors out — rules that make a database far more trustworthy than a spreadsheet.
1Summary 重點摘要
INSERT INTO table (fields) VALUES (values) adds; UPDATE table SET field = new WHERE condition changes; DELETE FROM table WHERE condition removes. UPDATE/DELETE always carry WHERE or they hit the whole table; SELECT before to confirm, after to verify.
2Vocabulary cards Vocabulary
The SQL statement adding a new record to a table, with VALUES supplying each field.
The part of INSERT giving actual values in field order, e.g. VALUES (2023011, 'Amy', '3A', 88).
The SQL statement changing field values of existing records; SET gives the new value, WHERE limits the scope.
The SQL statement removing records from a table; WHERE decides which — omit it and every record goes.
WHERE decides which records an UPDATE/DELETE touches; leaving it out means all records — very dangerous.
Data staying correct and consistent, guarded by primary keys, field types and range rules.
The database refusing inserts that duplicate or omit the primary key, keeping every record identifiable.
An advance copy of the data, restorable after a mistake or an incident.
Running SELECT after a write to confirm the result — the last step of the safe routine.
3Step by step Steps
- Insert one recordFor example: INSERT INTO students (student_id, name, class, score) VALUES (2023011, 'Amy', '3A', 88);. Fields and values pair one to one: number 2023011, name Amy, class 3A, score 88 — four fields, four values, aligned in order. Text values need single quotes while numbers do not; always write out the field list so the order is clear at a glance.
- Check right after insertingFor example: SELECT * FROM students WHERE student_id = 2023011;. It returns the fresh row (2023011, Amy, 3A, 88) — SELECT immediately after an insert is a good habit. Instead of the star, you may list fields such as SELECT name, score FROM ... to fetch only what you need; the star simply means every field.
- Update one recordFor example: Amy resits and scores 92: UPDATE students SET score = 92 WHERE student_id = 2023011;. The WHERE locks onto one row by primary key; only Amy's score changes, everyone else untouched. An UPDATE without WHERE would change every row, so check the condition before running; a primary key is the safest condition because it never repeats.
- Update many recordsFor example: the whole of 3B gains five marks: UPDATE students SET score = score + 5 WHERE class = '3B';. SET may use an expression (score = score + 5); WHERE picks the affected rows — everyone in 3B plus five, 3A and 3C unchanged. The expression is evaluated row by row, each student adding five to their own score; to set one fixed value instead, write SET score = 90.
- Delete one recordFor example: DELETE FROM students WHERE student_id = 2023011;. Only the row of student 2023011 goes; a SELECT afterwards should return zero rows. DELETE removes whole rows and cannot be undone, so always SELECT first to confirm the target; production databases often prefer a soft delete with a deleted flag.
- The disaster of a missing WHEREFor example: UPDATE students SET score = 90; (no WHERE) — every score in the table becomes 90; DELETE FROM students; (no WHERE) — every record disappears. No popup ever asks 'are you sure?'. Build the habit of writing the WHERE first and the rest of the statement afterwards; many companies also require a rehearsal on a test database.
- The four-step safe routineFor example: 1) SELECT * FROM students WHERE class = '3B'; to see who is affected; 2) confirm the list; 3) run UPDATE ... WHERE class = '3B';; 4) SELECT again to verify. Important systems take a backup before step three. Never reorder the four steps: look, confirm, run, verify; once the routine becomes second nature, database work is far safer.
4Diagrams Diagrams
5Worked example Example
Example: a new student Peter (number 2023012) joins 3C with a first test score of 75; after a resit it becomes 81. Write the SQL needed and verify it.
- Insert: INSERT INTO students (student_id, name, class, score) VALUES (2023012, 'Peter', '3C', 75);
- Update: UPDATE students SET score = 81 WHERE student_id = 2023012; — locked by primary key, only Peter's score changes.
- Verify: SELECT * FROM students WHERE class = '3C'; — it should return Peter's row, score now 81.
- Integrity check: running the step-one INSERT again is refused, because primary key 2023012 already exists — the primary key constraint protecting the data.
- Answer: Three SQL sentences: INSERT adds (2023012, Peter, 3C, 75); UPDATE changes score to 81; SELECT verifies the 3C list. A second insert with the same primary key is refused.
6Common mistakes Mistakes
7Quick check Quick check
Q1A new student joins and needs a new record — which SQL? A. SELECT B. INSERT INTO C. UPDATE
Q2What is missing from UPDATE students SET score = 90; and what happens?
Q3Amy moves to class 3C — write the correct UPDATE (her student number is 2023011).
Q4Why does the database refuse an INSERT repeating an existing primary key?
Q5Give the safe routine around an UPDATE/DELETE (at least three steps).
8Recap Recap
- Three writing statements: INSERT INTO adds, UPDATE SET changes, DELETE FROM removes.
- The safe INSERT lists its fields, with VALUES aligned one to one.
- UPDATE and DELETE always take a WHERE; without it the whole table is hit and no warning appears.
- Integrity is guarded by rules: primary keys never repeat, field types and ranges must be right.
- Safe four steps: SELECT to confirm → backup → execute → SELECT to verify.