MySQL basics for web developers

MySQL is the most popular relational database for web applications. It stores data in tables, similar to spreadsheets, with relationships between them. This guide covers the essentials you need to know as a web developer: creating databases and tables, writing queries, and understanding relationships.

MySQL data types

MySQL supports many data types. Here are the most common:

Best practice: Choose the smallest appropriate type. Use INT for IDs, VARCHAR(255) for names/emails, DECIMAL for money, TIMESTAMP for auto-tracking.

Create and use databases

A database is a container for tables. Most applications have one database:

# Create a new database
CREATE DATABASE myapp_db;

# See all databases
SHOW DATABASES;

# Select which database to use
USE myapp_db;

# See current database
SELECT DATABASE();

On UnderHost shared hosting: Use cPanel → MySQL Databases to create databases and users. The database name will be prefixed with your username (e.g., username_myapp).

On UnderHost VPS/Dedicated: Create databases via MySQL command line or control panel (aaPanel/CloudPanel).

Create tables with keys

A table is like a spreadsheet with rows and columns. Every table needs a primary key (unique identifier):

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Key constraints explained:

• PRIMARY KEY: Unique identifier for each row. Usually an ID number
• UNIQUE: All values must be unique (like email—no duplicates)
• NOT NULL: This field must always have a value (can't be empty)
• DEFAULT: If no value provided, use this default
• AUTO_INCREMENT: Automatically increment the number on each insert

CRUD queries (Create, Read, Update, Delete)

All database operations fall into four categories:

CREATE (INSERT) - Add new rows

# Insert one row
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 28);

# Insert multiple rows at once
INSERT INTO users (name, email, age) VALUES
  ('Jane Smith', 'jane@example.com', 32),
  ('Bob Johnson', 'bob@example.com', 45);

READ (SELECT) - Retrieve data

# Get all rows and columns
SELECT * FROM users;

# Get specific columns
SELECT id, name, email FROM users;

# Filter with WHERE
SELECT * FROM users WHERE age > 30;

# Get one specific row
SELECT * FROM users WHERE id = 5;

# Order results
SELECT * FROM users ORDER BY name ASC;

# Limit results (pagination)
SELECT * FROM users LIMIT 10;

# Count rows
SELECT COUNT(*) FROM users;

UPDATE - Modify existing rows

# Update specific columns
UPDATE users SET age = 29 WHERE id = 1;

# Update multiple columns
UPDATE users SET age = 30, name = 'John Updated' WHERE id = 1;

# Warning: If you forget WHERE, it updates ALL rows!
UPDATE users SET age = 0;  # DON'T DO THIS!

DELETE - Remove rows

# Delete specific row
DELETE FROM users WHERE id = 5;

# Warning: Delete all rows if you forget WHERE
DELETE FROM users;  # DON'T DO THIS!

Table relationships

Real applications have multiple tables that relate to each other. For example, a blog has posts and comments:

# Users table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  email VARCHAR(255)
);

# Posts table references users
CREATE TABLE posts (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  title VARCHAR(255),
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

# Comments table references both
CREATE TABLE comments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  post_id INT,
  user_id INT,
  comment TEXT,
  FOREIGN KEY (post_id) REFERENCES posts(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Foreign keys: The user_id in the posts table points to the id in the users table. This creates a relationship.

JOIN queries - Combine related tables

# Get posts with author names
SELECT posts.title, users.name, posts.created_at
FROM posts
JOIN users ON posts.user_id = users.id;

# Get posts with comment count
SELECT posts.title, COUNT(comments.id) as comment_count
FROM posts
LEFT JOIN comments ON posts.id = comments.post_id
GROUP BY posts.id;

Web developer best practices

1. Use prepared statements (prevent SQL injection)

// Bad - vulnerable to SQL injection
$sql = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";

// Good - use prepared statements
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$_POST['email']]);

2. Always hash passwords

// Hash password before storing
$hashed = password_hash($password, PASSWORD_DEFAULT);
INSERT INTO users (email, password) VALUES (?, ?)

3. Use transactions for multi-step operations

// If any step fails, rollback entire transaction
START TRANSACTION;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

4. Index frequently searched columns

// Email is searched frequently, so index it
CREATE INDEX idx_email ON users(email);

5. Backup regularly

Use cPanel Backups or mysqldump command to backup databases regularly. See our Backup strategy article.

Related: cPanel database management | phpMyAdmin advanced | Database optimization

Related articles