SQL Basics

A database keeps information in tables. A table is like a spreadsheet: each column is one kind of information, and each row is one thing, for example one customer.

SQL is the language you use to talk to a database. In this lesson you add one new table to a small online shop, put rows in it, ask questions about it, change a row and remove a row.

MySQL makes its own security certificate, so the first command tells the database client to accept it. The connection is still private. The second command makes a short name, sql, for the long command that signs in to the shop database and runs one SQL statement. After this you only type sql and the statement in quotes.

The shop already has tables. SHOW TABLES lists them. Each one holds a different kind of information: customers are the people who buy, products are what they buy.

DESCRIBE shows the columns of a table: its name, its type, and more. Look at the Key column. PRI means primary key. A primary key is a column whose value is different in every row, so it names one row and no other. Here it is id.

Now you make your own table, wishlist: which customer would like which product. CREATE TABLE makes it, and the part in brackets lists its columns.

• id INT AUTO_INCREMENT PRIMARY KEY: a whole number, counted up by MySQL for each new row. It is the primary key.
• customer_id and product_id: which customer, and which product. They hold the id of a row in another table.
• priority: how much they want it, from 1 (a lot) to 5. It is 3 if you do not say.
• UNIQUE KEY: the same customer and product may not be in the table twice.
• FOREIGN KEY: the ids must exist in customers and products, so you cannot wish for a product that is not in the shop.

The statement is long, but you only run it once.

INSERT INTO adds rows. First the table and its columns, then VALUES and one bracket for each row. You do not give an id: MySQL counts for you. This adds three rows: customer 1 wants product 1 and product 5, and customer 2 wants product 5. SELECT * then shows every column of every row.

Customer 1 already wants product 1. Try to add the same pair again. MySQL refuses with an error, because of the UNIQUE KEY from step 4. This error is not your mistake: it shows the table protecting its own data. Nothing was added.

SELECT asks a question. FROM says which table. WHERE keeps only the rows that match, and ORDER BY puts them in order. This one shows what customer 1 wants, the most wanted first.

The wishlist only has numbers. The product names are in products. A join puts two tables side by side. JOIN products ON products.id = wishlist.product_id means: for each wishlist row, find the product whose id is in product_id. Now you can read it.

You can join again to add the customer's name. Same idea, one more JOIN.

UPDATE changes rows. SET says the new value. WHERE id = 2 says which row. Customer 1 now wants product 5 the most, so its priority becomes 1. Then check with a SELECT that the row really changed.

DELETE FROM removes rows. Again WHERE says which. Customer 2 changed their mind, so remove row 3. Then look at the table: row 3 is gone, and the other rows are still there.

COUNT(*) counts rows. Ask for row 3 and the answer is 0, which proves the row is gone.

On the lab bar, press phpMyAdmin to open it in a new tab. Choose shop on the left. Your new table wishlist is in the list with the others. Press wishlist, then Browse to see its rows, and Structure to see the primary key and the unique key you made.