Creating Databases & Tables

Every MySQL server can host several separate databases side by side — before creating any tables, you create a database and tell MySQL you want to work inside it.

CREATE DATABASE and USE

CREATE DATABASE shop;
USE shop;

Query OK, 1 row affected (0.02 sec)

Database changed

USE shop; doesn't run a query against the database — it just tells the mysql client which database your next statements should target, so you don't have to qualify every table name with shop. in front of it.

CREATE TABLE with AUTO_INCREMENT

AUTO_INCREMENT tells MySQL to generate the next integer automatically whenever you insert a row without specifying that column — the standard way to build a simple numeric primary key:

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

INSERT INTO products (name, price) VALUES ('Widget', 9.99);
INSERT INTO products (name, price) VALUES ('Gadget', 24.50);
SELECT * FROM products;

+----+--------+-------+
| id | name   | price |
+----+--------+-------+
|  1 | Widget |  9.99 |
|  2 | Gadget | 24.50 |
+----+--------+-------+
2 rows in set (0.00 sec)

Neither INSERT mentioned id at all — MySQL assigned 1 and then 2 automatically. DECIMAL(10,2) stores an exact number with up to 10 total digits and 2 after the decimal point, which is what you want for money — unlike FLOAT, it won't introduce tiny rounding errors.

Checking what you've built

SHOW TABLES;

+-----------------+
| Tables_in_shop  |
+-----------------+
| products        |
+-----------------+
1 row in set (0.00 sec)