Skip to main content

MySQL Basics: A Comprehensive Guide

Database Operations

Create Database

CREATE DATABASE bookstore;
USE bookstore;

Show Databases

SHOW DATABASES;

Drop Database

DROP DATABASE IF EXISTS bookstore;

Table Operations

Create Table

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    author VARCHAR(100),
    price DECIMAL(10,2),
    publication_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Show Tables

SHOW TABLES;
DESCRIBE books;  -- Show table structure

Modify Table

-- Add column
ALTER TABLE books ADD COLUMN category VARCHAR(50);

-- Modify column
ALTER TABLE books MODIFY COLUMN title VARCHAR(150);

-- Drop column
ALTER TABLE books DROP COLUMN category;

-- Rename table
RENAME TABLE books TO library_books;

Data Types

Numeric Types

-- Integers
TINYINT    -- -128 to 127
SMALLINT   -- -32768 to 32767
INT        -- -2^31 to 2^31-1
BIGINT     -- -2^63 to 2^63-1

-- Decimals
DECIMAL(10,2)  -- Total 10 digits, 2 after decimal
FLOAT          -- Approximate numeric
DOUBLE         -- Double precision floating point

String Types

CHAR(10)       -- Fixed length
VARCHAR(100)   -- Variable length
TEXT           -- Long text
ENUM('S','M','L') -- Enumerated list

Date/Time Types

DATE           -- YYYY-MM-DD
TIME           -- HH:MM:SS
DATETIME       -- YYYY-MM-DD HH:MM:SS
TIMESTAMP      -- Stored as UTC, converted to current timezone

CRUD Operations

Insert Data

-- Single row
INSERT INTO books (title, author, price) 
VALUES ('The Great Gatsby', 'F. Scott Fitzgerald', 29.99);

-- Multiple rows
INSERT INTO books (title, author, price) VALUES 
    ('1984', 'George Orwell', 24.99),
    ('Pride and Prejudice', 'Jane Austen', 19.99);

Select Data

-- Basic select
SELECT * FROM books;

-- Specific columns
SELECT title, author, price FROM books;

-- With conditions
SELECT * FROM books 
WHERE price > 20.00 
AND publication_date >= '2020-01-01';

-- Sorting
SELECT * FROM books 
ORDER BY price DESC;

-- Limit results
SELECT * FROM books 
LIMIT 10;

-- Grouping
SELECT author, COUNT(*) as book_count 
FROM books 
GROUP BY author;

Update Data

-- Update single column
UPDATE books 
SET price = 25.99 
WHERE id = 1;

-- Update multiple columns
UPDATE books 
SET price = price * 1.1, 
    updated_at = CURRENT_TIMESTAMP 
WHERE publication_date < '2020-01-01';

Delete Data

-- Delete specific records
DELETE FROM books 
WHERE id = 1;

-- Delete all records
TRUNCATE TABLE books;

Constraints

Primary Key

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,
    name VARCHAR(100) NOT NULL
);

Foreign Key

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    total DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

Check Constraint

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2) CHECK (price > 0)
);

Joins

Inner Join

SELECT o.id, u.name, o.total 
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

Left Join

SELECT u.name, COUNT(o.id) as order_count 
FROM users u
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.id;

Right Join

SELECT u.name, o.* 
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

Functions

String Functions

SELECT 
    CONCAT(first_name, ' ', last_name),
    UPPER(email),
    LENGTH(name),
    SUBSTRING(description, 1, 100)
FROM users;

Numeric Functions

SELECT 
    ROUND(price, 2),
    CEIL(price),
    FLOOR(price),
    ABS(balance)
FROM products;

Date Functions

SELECT 
    DATE_FORMAT(created_at, '%Y-%m-%d'),
    DATEDIFF(NOW(), created_at),
    DATE_ADD(NOW(), INTERVAL 1 MONTH),
    YEAR(created_at)
FROM orders;

Aggregate Functions

SELECT 
    COUNT(*) as total_records,
    AVG(price) as avg_price,
    SUM(quantity) as total_quantity,
    MAX(price) as highest_price,
    MIN(price) as lowest_price
FROM products;

Indexing

Create Index

-- Single column index
CREATE INDEX idx_email ON users(email);

-- Composite index
CREATE INDEX idx_name_email ON users(name, email);

-- Unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

Show Indexes

SHOW INDEX FROM users;

Drop Index

DROP INDEX idx_email ON users;

Best Practices

  1. Always Use Proper Data Types

    • Choose appropriate column types
    • Use INT for IDs instead of VARCHAR

  2. Indexing Guidelines

    • Index frequently searched columns
    • Don't over-index (slows down writes)
    • Consider composite indexes for multiple column searches

  3. Query Optimization

    • Use EXPLAIN to analyze queries
    • Avoid SELECT *
    • Use appropriate JOINs
    • Add WHERE clauses before GROUP BY

  4. Security

    • Use prepared statements
    • Regular backups
    • Proper user permissions
    • Encrypt sensitive data
Back to top