CREATE DATABASE & TABLE

Until now you have worked with tables that already existed. In this lesson you build your own. First you create a database, the container that holds a project's tables. Then you create tables inside it, choosing a name and a data type for every column.

Good table design saves you from painful fixes later, so this lesson also covers character sets, naming rules and the table options every MySQL table should have.

Creating a database

One MySQL server can hold many databases, usually one per application: shop, school, library. Create one with CREATE DATABASE:

CREATE DATABASE shop
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

SHOW DATABASES;
USE shop;

• CHARACTER SET utf8mb4 lets the database store every character in Unicode: English, Hindi (हिंदी), Tamil, the ₹ sign and even emoji. Always use utf8mb4. The older name utf8 in MySQL means a limited three-byte version that cannot store some characters.
• A collation is the set of rules for comparing and sorting text. utf8mb4_0900_ai_ci is MySQL's default: ci means case-insensitive ('rohtak' equals 'Rohtak') and ai means accent-insensitive.
• SHOW DATABASES lists the databases on the server, and USE shop makes shop the current database, so later statements do not need to say shop.customers.

If the database might already exist, write CREATE DATABASE IF NOT EXISTS shop to avoid the error Can't create database 'shop'; database exists.

The parts of CREATE TABLE

A CREATE TABLE statement gives the table a name and then lists its columns inside brackets, separated by commas. Each column has a name, a data type and optional constraints (rules). Here is a complete MySQL table for the shop's suppliers:

CREATE TABLE suppliers (
  supplier_id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  company_name  VARCHAR(100) NOT NULL,
  contact_name  VARCHAR(80),
  phone         VARCHAR(15) NOT NULL,
  city          VARCHAR(40) NOT NULL,
  gst_number    CHAR(15) UNIQUE,
  is_active     BOOLEAN NOT NULL DEFAULT TRUE,
  created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_0900_ai_ci;

Reading it line by line:

• supplier_id is the primary key, the column that uniquely identifies each row. AUTO_INCREMENT makes MySQL fill it with 1, 2, 3 and so on, and UNSIGNED means it can never be negative.
• VARCHAR(100) holds text of up to 100 characters. CHAR(15) always holds exactly 15, which suits a GST number.
• NOT NULL means the column must always have a value. Columns without it, like contact_name, may be left empty.
• UNIQUE stops two suppliers from having the same GST number.
• DEFAULT TRUE and DEFAULT CURRENT_TIMESTAMP fill in a value when an insert leaves the column out.
• ENGINE = InnoDB chooses the storage engine that supports transactions and foreign keys. It is the default, but writing it makes the intention clear.

Phone numbers are stored as text, not numbers, because they can start with +91 or 0 and you never do arithmetic on them. The next two lessons explain every type and constraint in detail: MySQL Data Types and Constraints & Keys.

Try it: a reviews table

AUTO_INCREMENT does not run in the browser editor, so the runnable examples give IDs by hand. This creates a table for product reviews, adds three reviews and joins them to the existing tables:

CREATE TABLE reviews (
  review_id INTEGER PRIMARY KEY,
  product_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL,
  rating INTEGER NOT NULL,
  comment VARCHAR(500),
  reviewed_on DATE NOT NULL,
  FOREIGN KEY (product_id) REFERENCES products (product_id),
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

INSERT INTO reviews (review_id, product_id, customer_id, rating, comment, reviewed_on) VALUES
  (1, 3, 2, 5, 'Sharp picture, great for coding.', '2025-01-25'),
  (2, 1, 1, 4, 'Good mouse for the price.', '2025-01-15'),
  (3, 7, 6, 3, NULL, '2025-03-20');

SELECT p.product_name, c.first_name, r.rating, r.comment
FROM reviews r
JOIN products p ON p.product_id = r.product_id
JOIN customers c ON c.customer_id = r.customer_id
ORDER BY r.rating DESC;

product_name      | first_name | rating | comment
------------------+------------+--------+---------------------------------
27-inch Monitor   | Priya      | 5      | Sharp picture, great for coding.
Wireless Mouse    | Aarav      | 4      | Good mouse for the price.
Bluetooth Speaker | Meera      | 3      | NULL
(3 rows)

The two FOREIGN KEY lines link each review to a real product and a real customer. Your new table works with joins exactly like the tables that came with the practice database.

Copying a table

You can create a table from the result of a query with CREATE TABLE ... AS SELECT. The new table gets the columns and rows of the result:

CREATE TABLE audio_products AS
SELECT product_id, product_name, price
FROM products
WHERE category = 'Audio';

SELECT * FROM audio_products;

product_id | product_name                | price
-----------+-----------------------------+------
6          | Noise-Cancelling Headphones | 8999
7          | Bluetooth Speaker           | 2499
(2 rows)

This is handy for quick snapshots and experiments, but note that the copy has no primary key, indexes or foreign keys. In MySQL, CREATE TABLE products_backup LIKE products; does the opposite: it copies the full structure, keys included, but no rows. You can then fill it with INSERT INTO products_backup SELECT * FROM products;.

Looking at tables you created

MySQL has statements to inspect the current database. They are MySQL-only, so they do not run in the browser:

SHOW TABLES;                   -- every table in the current database
DESCRIBE suppliers;            -- each column with its type, NULL rule, key and default
SHOW CREATE TABLE suppliers;   -- the full CREATE TABLE statement MySQL stored

SHOW CREATE TABLE is especially useful: it shows the exact definition, including table options and constraint names that MySQL created for you.

Naming tables and columns

Clear names make every later query easier to read. The practice database follows these habits, and you should too:

• Use lowercase words joined with underscores (order_items, joined_on). On Linux servers, table names are case-sensitive, so Orders and orders can be different tables.
• Name tables in the plural (customers) and give the key a matching name (customer_id), so foreign keys are obvious.
• Avoid SQL keywords as names. That is why the table is orders and not order: ORDER is a reserved word, and CREATE TABLE order (...) is a syntax error. If you must use such a name, wrap it in backticks: `order`.
• Do not use spaces or hyphens in names.

Common mistakes

• No database selected. Running CREATE TABLE before USE shop gives ERROR 1046 (3D000): No database selected.
• A comma after the last column. rating INTEGER NOT NULL,) is a syntax error (error 1064). The last item in the list has no comma.
• Using utf8 instead of utf8mb4. Some characters, including many emoji, cannot be stored. Always write utf8mb4.
• Storing phone numbers or PIN codes as numbers. Leading zeros and + signs are lost. Use VARCHAR or CHAR.

Key points

• CREATE DATABASE name CHARACTER SET utf8mb4 creates a database; USE name selects it.
• CREATE TABLE lists each column with a name, a data type and optional constraints.
• Every table needs a primary key; AUTO_INCREMENT numbers it for you in MySQL.
• Use the InnoDB engine and the utf8mb4 character set for every table.
• CREATE TABLE ... AS SELECT copies data without keys; CREATE TABLE ... LIKE copies structure without data.
• Use lowercase, plural, underscore-separated names and avoid reserved words.

CREATE TABLE deliveries (
  delivery_id INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL,
  courier VARCHAR(40) NOT NULL,
  tracking_code VARCHAR(30) UNIQUE,
  delivered_on DATE,
  FOREIGN KEY (order_id) REFERENCES orders (order_id)
);

INSERT INTO deliveries (delivery_id, order_id, courier, tracking_code, delivered_on) VALUES
  (1, 101, 'Speedy Couriers', 'SPD1001', '2025-01-08'),
  (2, 102, 'India Post', 'EP123456789IN', '2025-01-17');

SELECT d.delivery_id, c.first_name, d.courier, d.delivered_on
FROM deliveries d
JOIN orders o ON o.order_id = d.order_id
JOIN customers c ON c.customer_id = o.customer_id;