Chapter 1: Environment Setup

1 Installing MySQL 8

macOS (Homebrew)

The easiest way to install MySQL on macOS is through Homebrew. If you don't have Homebrew installed, visit brew.sh first.

# Install MySQL 8
brew install mysql

# Start MySQL service
brew services start mysql

# Secure installation (set root password)
mysql_secure_installation

Ubuntu / Debian

On Ubuntu/Debian systems, use the APT package manager to install MySQL from the official repository.

# Update package index
sudo apt update

# Install MySQL Server
sudo apt install mysql-server

# Start and enable MySQL
sudo systemctl start mysql
sudo systemctl enable mysql

# Run security script
sudo mysql_secure_installation

Windows

On Windows, download the MySQL Installer from the official website and follow the GUI wizard.

# Download from:
# https://dev.mysql.com/downloads/installer/

# Or use winget (Windows Package Manager)
winget install Oracle.MySQL

# Or use Chocolatey
choco install mysql

Docker (Cross-platform)

Docker provides an isolated, reproducible environment regardless of your operating system.

# Pull and run MySQL 8 container
docker run --name mysql8 \
  -e MYSQL_ROOT_PASSWORD=yourpassword \
  -p 3306:3306 \
  -d mysql:8

# Connect to the container
docker exec -it mysql8 mysql -uroot -p

Verify Installation

After installation, verify that MySQL is running correctly.

# Check MySQL version
mysql --version
# Expected output: mysql  Ver 8.x.x for Linux on x86_64 (MySQL Community Server - GPL)

# Check service status (Linux)
sudo systemctl status mysql

# Check service status (macOS)
brew services list | grep mysql

2 Connecting to MySQL

CLI Login

The MySQL command-line client is the most direct way to interact with your database server.

# Connect as root (will prompt for password)
mysql -u root -p

# Connect with host and port specified
mysql -u root -p -h 127.0.0.1 -P 3306

# Connect and execute a single command
mysql -u root -p -e "SELECT VERSION();"

Basic Database Operations

Once connected, you can explore and manage databases with these fundamental commands.

-- Show all databases
SHOW DATABASES;

-- Expected output:
-- +--------------------+
-- | Database           |
-- +--------------------+
-- | information_schema |
-- | mysql              |
-- | performance_schema |
-- | sys                |
-- +--------------------+

-- Create a new database
CREATE DATABASE sql_tutorial
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

-- Switch to the new database
USE sql_tutorial;

-- Confirm current database
SELECT DATABASE();
-- +---------------+
-- | DATABASE()    |
-- +---------------+
-- | sql_tutorial  |
-- +---------------+

Creating a Dedicated User

For security, create a dedicated user instead of using root for daily development.

-- Create a new user
CREATE USER 'dev'@'localhost' IDENTIFIED BY 'dev_password';

-- Grant all privileges on the tutorial database
GRANT ALL PRIVILEGES ON sql_tutorial.* TO 'dev'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

-- Now connect as the new user
-- mysql -u dev -p sql_tutorial

3 Client Tools

While the CLI is powerful, graphical clients can boost productivity significantly. Here are the most popular options.

4 Creating Your First Table

Let's create a users table that we'll use throughout this tutorial series. This table demonstrates the most common column types and constraints.

-- Make sure you're in the right database
USE sql_tutorial;

-- Create the users table
CREATE TABLE users (
    id         INT           AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(50)   NOT NULL,
    email      VARCHAR(100)  NOT NULL UNIQUE,
    age        INT           DEFAULT NULL,
    created_at DATETIME      DEFAULT CURRENT_TIMESTAMP
);

-- Verify table structure
DESCRIBE users;
-- +------------+--------------+------+-----+-------------------+-------------------+
-- | Field      | Type         | Null | Key | Default           | Extra             |
-- +------------+--------------+------+-----+-------------------+-------------------+
-- | id         | int          | NO   | PRI | NULL              | auto_increment    |
-- | name       | varchar(50)  | NO   |     | NULL              |                   |
-- | email      | varchar(100) | NO   | UNI | NULL              |                   |
-- | age        | int          | YES  |     | NULL              |                   |
-- | created_at | datetime     | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
-- +------------+--------------+------+-----+-------------------+-------------------+

Insert Sample Data

Populate the table with some test data for upcoming chapters.

INSERT INTO users (name, email, age) VALUES
('Alice Johnson', 'alice@example.com', 28),
('Bob Smith', 'bob@example.com', 35),
('Charlie Lee', 'charlie@example.com', 22),
('Diana Wang', 'diana@example.com', 31),
('Eve Brown', 'eve@example.com', NULL);

-- Verify the data
SELECT * FROM users;
-- +----+---------------+---------------------+------+---------------------+
-- | id | name          | email               | age  | created_at          |
-- +----+---------------+---------------------+------+---------------------+
-- |  1 | Alice Johnson | alice@example.com   |   28 | 2025-01-01 10:00:00 |
-- |  2 | Bob Smith     | bob@example.com     |   35 | 2025-01-01 10:00:00 |
-- |  3 | Charlie Lee   | charlie@example.com |   22 | 2025-01-01 10:00:00 |
-- |  4 | Diana Wang    | diana@example.com   |   31 | 2025-01-01 10:00:00 |
-- |  5 | Eve Brown     | eve@example.com     | NULL | 2025-01-01 10:00:00 |
-- +----+---------------+---------------------+------+---------------------+

Understanding the Column Definitions

5 Chapter Summary