Four commands: INSERT, SELECT, UPDATE, DELETE
SQL has four commands for records. INSERT adds a record: you name the table, the fields and the values in the same order. SELECT shows records, and WHERE limits them. UPDATE changes values: SET says what to change and WHERE says which records. DELETE removes records, and WHERE says which. SQL words are not case-sensitive, but capitals make a command easier to read. Strings go in single quotes. Now the most important warning: UPDATE and DELETE without WHERE act on every row. DELETE FROM Terms; with no condition empties the whole table. Always write the WHERE first and check it. In the INSERT below we give the id ourselves; if you leave it out, the database must be set to count the id up by itself, otherwise the command fails.
| Command | Job | WHERE? |
|---|---|---|
| INSERT | adds a record | not used |
| SELECT | shows records | limits what is shown |
| UPDATE | changes records | without it, every row changes |
| DELETE | removes records | without it, every row goes |
1INSERT INTO Terms (id, Term, Trans, Defe, Picture) 2VALUES (4, 'keyboard', 'لوحة مفاتيح', 'Keys for typing letters and numbers.', 'pic/keyboard.webp');
1 row affected
1SELECT id, Term FROM Terms WHERE id = 2;id | Term 2 | cable
| id | Term | Trans | Defe | Picture |
|---|---|---|---|---|
| 1 | router | موجه | Passes data between networks. | pic/router.webp |
| 2 | cable | كابل | A wire that carries signals or power. | pic/cable.webp |
| 4 | keyboard | لوحة مفاتيح | Keys for typing letters and numbers. | pic/keyboard.webp |
1UPDATE Terms SET Picture = 'pic/none.webp';3 rows affected
On paper, copy the Terms table with three records. Write the commands for: adding a term of your own, correcting one definition, and removing one term. After each command draw the table again. Then write the same UPDATE without WHERE and explain what would happen.