Reading is only one of the things SQL does. Three more statements change the data in a table.
INSERT: add a row
INSERT INTO products (name, category, price, stock)
VALUES ('Pencil Case', 'Stationery', 7.50, 40);
List the columns, then give one value for each, in the same order. The id is left out here: the database numbers new rows by itself.
UPDATE: change rows
UPDATE products
SET price = 5.00
WHERE name = 'Notebook';
SET says what to change, and WHERE says in which rows. Change several columns by separating them with commas: SET price = 5.00, stock = 100. A new value may use the old one: SET stock = stock - 1.
DELETE: remove rows
DELETE FROM products
WHERE stock = 0;
The WHERE is not optional
Technically it is, and that is the danger. UPDATE and DELETE without a WHERE apply to every row in the table. Somewhere in the world, at this moment, someone is discovering this on a real database.
A safe habit: first write a SELECT with your WHERE to see which rows it finds. Then turn it into the UPDATE or DELETE.
Several statements
You can run several statements in one go, each ending with a semicolon. They run from top to bottom. Here every run starts with a fresh copy of the database, so you cannot do any lasting harm.
Tables in the practice database
| Table | Columns |
|---|---|
customers | id, name, city, joined |
products | id, name, category, price, stock |
orders | id, customer_id, product_id, quantity, ordered |
Every run starts from a fresh copy of the database, so nothing you do can break it.
Your Task
- Insert a new product:
Travel Pillowin the categoryTravel, with a price of22and a stock of15. - The water bottles have arrived. Update the product
Water Bottleso itsstockis50. - Delete every product that still has a
stockof0. - Finish with
SELECT * FROM products;to see the result.
Travel Pillowis added, inTravel, with a price of22and a stock of15Water Bottlenow has a stock of50, and no other stock was changed- The products with no stock are deleted, and only those
- The last statement shows the whole
productstable