Understanding MySQL Basics: Databases and Tables

The two building blocks of MySQL: databases and tables. How to create them, add data, and read it back.

Databases

A database is a named container for your data. Think of it as a folder that holds related tables.

Creating a database

Create one with a single command:

CREATE DATABASE my_database;

That creates a database called my_database. Pick names that describe what the data is for.

Selecting a database

Before you can store data, tell MySQL which database to use:

USE my_database;

Everything after this runs against my_database until you switch again.

Tables

Inside a database, data lives in tables. Each table holds one kind of record – customers, products, orders, and so on.

Creating a table

You define each column and its data type. Here is a customers table:

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    join_date DATE
);

Four columns:

• id: a unique number for each customer. AUTO_INCREMENT generates it automatically.
• name: the customer’s name. VARCHAR(255) holds strings up to 255 characters.
• email: same type, for the email address.
• join_date: when they signed up, stored as a DATE.

Inserting data

Add a customer:

INSERT INTO customers (name, email, join_date) VALUES ('John Doe', 'john.doe@example.com', '2022-01-01');

That adds one row for John Doe with his email and join date.

Retrieving data

View everything in the table:

SELECT * FROM customers;

That returns all rows in customers.

Updating data

Change an existing row:

UPDATE customers SET email = 'new.john.doe@example.com' WHERE id = 1;

That updates the email for the row where id is 1.

Deleting data

Remove a row:

DELETE FROM customers WHERE id = 1;

DELETE is permanent. Double-check your WHERE clause before you run it.

Best practices for database and table management

• Naming conventions: use clear, descriptive names for databases and tables.
• Normalisation: design tables to reduce duplicate data and keep things consistent.
• Regular backups: back up your databases. You will need them eventually.

Databases and tables are the foundation of everything else in MySQL. Practice creating tables and moving data around before moving on to more complex queries.