Skip to main content

MySQL Basics: A Comprehensive Guide

Table of Contents

  1. Database Operations
  2. Table Operations
  3. Data Types
  4. CRUD Operations
  5. Constraints
  6. Joins
  7. Functions
  8. Indexing

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