SQL Local Setup, Dumps, and Command Types Explained

May 23, 2025 · 13 min read

Senior Software Engineer

Setting Up a Local SQL Connection

Step 1: Install a Database Server

MySQL/MariaDB

# Ubuntu/Debian
sudo apt install mysql-server

# macOS (using Homebrew)
brew install mysql

# Windows
# Download installer from mysql.com

PostgreSQL

# Ubuntu/Debian
sudo apt install postgresql

# macOS
brew install postgresql

# Windows
# Download installer from postgresql.org

Step 2: Start the Database Service

MySQL/MariaDB

# Linux
sudo systemctl start mysql

# macOS
brew services start mysql

# Windows
# Service starts automatically after installation

PostgreSQL

# Linux
sudo systemctl start postgresql

# macOS
brew services start postgresql

# Windows
# Service starts automatically after installation

Step 3: Connect to Your Database

MySQL/MariaDB

# Connect to server
mysql -u root -p

# Create a database
mysql> CREATE DATABASE mydb;

# Select database
mysql> USE mydb;

PostgreSQL

# Connect as postgres user
sudo -u postgres psql

# Create a database
postgres=# CREATE DATABASE mydb;

# Connect to specific database
postgres=# \c mydb

Working with SQL Dumps

Creating Database Dumps

MySQL/MariaDB

# Full database dump
mysqldump -u username -p database_name > backup.sql

# Specific tables
mysqldump -u username -p database_name table1 table2 > tables_backup.sql

# Structure only (no data)
mysqldump -u username -p --no-data database_name > structure.sql

# Data only (no structure)
mysqldump -u username -p --no-create-info database_name > data.sql

PostgreSQL

# Full database dump
pg_dump -U username database_name > backup.sql

# Compressed format
pg_dump -U username -Fc database_name > backup.dump

# Specific schema
pg_dump -U username -n schema_name database_name > schema_backup.sql

Restoring from Dumps

MySQL/MariaDB

# Create database first if needed
mysql -u username -p -e "CREATE DATABASE restored_db"

# Restore full dump
mysql -u username -p restored_db < backup.sql

# Alternative method
mysql> USE restored_db;
mysql> SOURCE backup.sql;

PostgreSQL

# Plain SQL format
psql -U username -d database_name -f backup.sql

# Custom format
pg_restore -U username -d database_name backup.dump

Complete Example Workflow

Let's say you have a WordPress site and want to create a development environment:

# 1. Create a dump of production database
mysqldump -u prod_user -p --add-drop-table wordpress_prod > wordpress_backup.sql

# 2. Create a new database locally
mysql -u root -p -e "CREATE DATABASE wordpress_dev"

# 3. Restore the dump to the new database
mysql -u root -p wordpress_dev < wordpress_backup.sql

# 4. Verify the restoration
mysql -u root -p -e "USE wordpress_dev; SHOW TABLES;"

# 5. Update site URL in the database (for WordPress)
mysql -u root -p wordpress_dev -e "UPDATE wp_options SET option_value='http://localhost/wordpress' WHERE option_name IN ('siteurl', 'home');"

This process lets you create an exact copy of your production database for local development without affecting the live site.

SQL Command Types: DDL, DML, DCL, TCL, DQL

SQL commands are categorized based on their purpose and effect on the database:

1. DDL – Data Definition Language

Commands that define or modify the structure of database objects (tables, schemas, etc.).

CREATE: Create new tables, databases, views, indexes, etc.

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));

ALTER: Modify existing database objects.

ALTER TABLE users ADD COLUMN email VARCHAR(100);

DROP: Delete objects from the database.
```
DROP TABLE users;
```
TRUNCATE: Remove all records from a table (structure remains).
```
TRUNCATE TABLE users;
```
RENAME: Rename database objects.
```
ALTER TABLE users RENAME TO customers;
```
CREATE INDEX: Create an index to improve query performance.
```
CREATE INDEX idx_name ON users(name);
```
Explanation:
- idx_name = The name you give to the index (you can name it anything)
- users = The table you're indexing
- (name) = The column to create the index on
Why is this useful?

Without an index, searching for a name means checking every row (slow). With an index, the database creates a sorted lookup (like a book's index) making searches much faster.

Example: Finding a user by name
```
SELECT * FROM users WHERE name = 'Alice';
```
- Without index: Checks all 1 million rows → Slow ❌
- With index: Jumps directly to 'Alice' → Fast ✅
When to use: On columns frequently used in WHERE, JOIN, or ORDER BY clauses.
DROP INDEX: Remove an index.
```
DROP INDEX idx_name;
```

Views and Materialized Views

Views are virtual tables based on SQL queries. They don't store data themselves but show data from underlying tables.

Think of it as:

“A window to see data from one or more tables.”

CREATE VIEW: Create a virtual table.

CREATE VIEW active_users AS 
SELECT id, name FROM users WHERE status = 'active';

When you query the view:
```
SELECT * FROM active_users;
```

👉 SQL runs the original SELECT query every time.

Uses of Views:
- Simplify complex queries by encapsulating them
- Provide data security by limiting access to specific columns/rows
- Present data in a different format without changing base tables
- Maintain backward compatibility when table structure changes

Materialized Views (MVIEW) store the query results physically, unlike regular views. A Materialized View is a physical copy of query results stored in the database.

Think of it as:

“A snapshot of data stored like a table.”

CREATE MATERIALIZED VIEW: Create a stored result set (PostgreSQL).

CREATE MATERIALIZED VIEW user_stats AS
SELECT status, COUNT(*) as count 
FROM users GROUP BY status;

When you query the view:
```
SELECT * FROM user_stats;
```

👉 Data is read directly from storage, not recalculated.

REFRESH MATERIALIZED VIEW: Update the stored data.
```
REFRESH MATERIALIZED VIEW user_stats;
```
What the above MVIEW query does:

"Group users by their status and count how many users are in each status."

Example users table:

id name status
1 A ACTIVE
2 B ACTIVE
3 C INACTIVE
4 D ACTIVE

Result stored in user_stats:

status count
ACTIVE 3
INACTIVE 1

What does COUNT(*) as count mean?
- COUNT(*) = Counts all rows in each group
- as count = Names the result column "count" (you can use any name)
COUNT(*) vs COUNT(column_name):

Command What it counts Handles NULL?
COUNT(*) All rows Yes (includes NULLs)
COUNT(email) Only rows where email is NOT NULL No (ignores NULLs)

Example with NULL values:

id status email
1 ACTIVE a@mail.com
2 ACTIVE NULL
3 ACTIVE b@mail.com
```
SELECT COUNT(*) FROM users;      -- Result: 3 (all rows)
SELECT COUNT(email) FROM users;  -- Result: 2 (NULL ignored)
```
Why use COUNT(*) here?
- We want total users per status
- Every row = one user
- Best practice for counting rows

id	name	status
1	A	ACTIVE
2	B	ACTIVE
3	C	INACTIVE
4	D	ACTIVE

status	count
ACTIVE	3
INACTIVE	1

Command	What it counts	Handles NULL?
`COUNT(*)`	All rows	Yes (includes NULLs)
`COUNT(email)`	Only rows where email is NOT NULL	No (ignores NULLs)

id	status	email
1	ACTIVE	a@mail.com
2	ACTIVE	NULL
3	ACTIVE	b@mail.com

Key Differences:

Aspect	View	Materialized View
Storage	No physical storage	Stores data physically
Performance	Slower (executes query each time)	Faster (pre-computed results)
Data Freshness	Always current	Needs manual/scheduled refresh
Disk Space	Minimal	Uses disk space
Best For	Real-time data, simple queries	Complex queries, reporting

2. DML – Data Manipulation Language

Commands that manage data within schema objects.

INSERT: Add new records.

INSERT INTO users (id, name) VALUES (1, 'Alice');

UPDATE: Modify existing records.

UPDATE users SET name = 'Bob' WHERE id = 1;

DELETE: Remove records.
```
DELETE FROM users WHERE id = 1;
```

MERGE (UPSERT): Insert or update records based on a condition.

-- PostgreSQL syntax
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON CONFLICT (id) DO UPDATE SET name = 'Alice';

-- MySQL syntax
INSERT INTO users (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = 'Alice';

3. DCL – Data Control Language

Commands that control access to data in the database.

GRANT: Give user access privileges.

GRANT SELECT, INSERT ON users TO 'user1';

REVOKE: Remove user access privileges.
```
REVOKE INSERT ON users FROM 'user1';
```

4. TCL – Transaction Control Language

Commands to manage transactions in the database.

COMMIT: Save all changes made in the transaction.
```
COMMIT;
```
ROLLBACK: Undo changes since the last COMMIT.
```
ROLLBACK;
```
SAVEPOINT: Set a point within a transaction to which you can later roll back.
```
SAVEPOINT sp1;
```
SET TRANSACTION: Specify transaction characteristics.
```
SET TRANSACTION READ ONLY;
```

5. DQL – Data Query Language

Commands to query and retrieve data from the database. clauses JOINs, along with SELECT, WHERE, GROUP BY, HAVING, and ORDER BY, are all integral parts of DQL in SQL.

SELECT: Retrieve data from one or more tables.
```
SELECT * FROM users;
```

Additional DQL Operators and Clauses

LIKE: Pattern matching in WHERE clause.

-- Find names starting with 'A'
SELECT * FROM users WHERE name LIKE 'A%';

-- Find names containing 'bob'
SELECT * FROM users WHERE name LIKE '%bob%';

-- Wildcards: % (any characters), _ (single character)
SELECT * FROM users WHERE name LIKE 'J_hn';  -- Matches John, Jahn

IN: Check if value matches any in a list.

SELECT * FROM users WHERE status IN ('active', 'pending');

BETWEEN: Check if value is within a range.

SELECT * FROM orders WHERE price BETWEEN 100 AND 500;

EXISTS: Check if subquery returns any rows.

SELECT * FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

CASE WHEN: Conditional logic in queries.

SELECT name,
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age >= 18 AND age < 65 THEN 'Adult'
    ELSE 'Senior'
  END as age_group
FROM users;

Aggregate Functions: Perform calculations on data.

SELECT COUNT(*) FROM users;              -- Count rows
SELECT AVG(price) FROM orders;           -- Average value
SELECT SUM(amount) FROM transactions;    -- Sum of values
SELECT MAX(score), MIN(score) FROM tests; -- Max and Min

UNION / UNION ALL: Combine results from multiple queries.

-- UNION removes duplicates
SELECT name FROM customers
UNION
SELECT name FROM suppliers;

-- UNION ALL keeps duplicates (faster)
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

DISTINCT: Return unique values only.
```
SELECT DISTINCT country FROM users;
```

LIMIT / OFFSET: Paginate results.

-- First 10 records
SELECT * FROM users LIMIT 10;

-- Skip 20, then get 10 (pagination)
SELECT * FROM users LIMIT 10 OFFSET 20;

Understanding SQL JOINs

Imagine you and a friend are sharing items from your collections. You have some items (like books or toys), and your friend has others. A "JOIN" in SQL is like combining these items based on something you both have in common, such as matching labels or categories. This helps you see a combined list without losing track of who owns what.

Let's break it down using the analogy of "items from my side (your friend's collection)" and "items from your side (your collection)." We'll assume:

Your side (left table): Your collection of items.
My side (right table): Your friend's collection of items.
The common thing: A matching "ID" or "category" that links the items.

For a practical example, consider two lists:

List A: Names of people and their phone numbers.
List B: Names of people and their addresses.

We'll use these lists to show how JOINs work.

1. Basic JOIN (INNER JOIN)

This is like showing only the items that both of you have in common. If you both have a "red ball," it shows up. But if only you have a "blue car," it doesn't appear in the combined list.

Logic: Only items that match between your side and my side.

Example Query (using List A and List B):

SELECT A.name, A.phone, B.address
FROM ListA A
INNER JOIN ListB B ON A.name = B.name;

Imagine you have two lists:
- ListA: Contains people’s names and their phone numbers.
- ListB: Contains people’s names and their addresses.
- This query combines both lists, but only for people who appear in both lists (i.e., their name is in both ListA and ListB).
- The result will show:
  - The person’s name,
  - Their phone number (from ListA),
  - Their address (from ListB).

In short:
This SQL query finds people who are present in both lists and shows their name, phone number, and address together.

2. LEFT JOIN (LEFT OUTER JOIN)

This is like listing ALL items from your side (your collection), and adding matching items from my side (your friend's collection) where possible. If something from your side doesn't match anything on my side, it still shows up, but with a blank spot for my side.

Logic: Everything from your side, plus any matching items from my side. If no match, my side shows as "nothing" (NULL).
Analogy: "Show all my items, and if you have something similar, add it; otherwise, leave it blank."
Example Query:
```
SELECT A.name, A.phone, B.address
FROM ListA A
LEFT JOIN ListB B ON A.name = B.name;
```
- Shows all people from List A (with phones), and adds addresses from List B if available. If a person has no address, address is NULL.

3. RIGHT JOIN (RIGHT OUTER JOIN)

This is the opposite of LEFT JOIN. It lists ALL items from my side (your friend's collection), and adds matching items from your side (your collection) where possible. If something from my side doesn't match, it still shows up, but with a blank spot for your side.

Logic: Everything from my side, plus any matching items from your side. If no match, your side shows as "nothing" (NULL).
Analogy: "Show all my items, and if you have something similar, add it; otherwise, leave it blank."
Example Query:
```
SELECT A.name, A.phone, B.address
FROM ListA A
RIGHT JOIN ListB B ON A.name = B.name;
```
- Shows all people from List B (with addresses), and adds phones from List A if available. If a person has no phone, phone is NULL.

Quick Summary Table for JOINs

Join Type	What It Shows	Analogy Example
INNER JOIN	Only matching items from both sides	Shared toys only
LEFT JOIN	All from your side + matches from my side	Your full list, with friend's matches
RIGHT JOIN	All from my side + matches from your side	Friend's full list, with your matches

These JOINs help combine data from different tables in a database, like merging lists in a spreadsheet. Practice with small examples to get the hang of it!

Summary Table

Category	Full Form	Examples	Purpose
DDL	Data Definition Language	CREATE, ALTER, DROP, TRUNCATE, INDEX, VIEW	Define/modify structure
DML	Data Manipulation Language	INSERT, UPDATE, DELETE, MERGE	Manage table data
DCL	Data Control Language	GRANT, REVOKE	Control access/permissions
TCL	Transaction Control Language	COMMIT, ROLLBACK, SAVEPOINT	Manage transactions
DQL	Data Query Language	SELECT, LIKE, IN, BETWEEN, JOIN, UNION	Query data

The typical order of SQL command execution is:

DDL (Data Definition Language)
First, define the structure of the database (tables, schemas, etc.).
DCL (Data Control Language)
Next, set up permissions and access control for users.
DML (Data Manipulation Language)
Then, insert, update, or delete data within the tables.
TCL (Transaction Control Language)
Use transaction commands (COMMIT, ROLLBACK) to manage changes made by DML commands.
DQL (Data Query Language)
Finally, query and retrieve data from the database.

Summary

DDL → DCL → DML → TCL → DQL
(Structure → Permissions → Data → Transactions → Query)

Setting Up a Local SQL Connection​

Step 1: Install a Database Server​

MySQL/MariaDB​

PostgreSQL​

Step 2: Start the Database Service​

MySQL/MariaDB​

PostgreSQL​

Step 3: Connect to Your Database​

MySQL/MariaDB​

PostgreSQL​

Working with SQL Dumps​

Creating Database Dumps​

MySQL/MariaDB​

PostgreSQL​

Restoring from Dumps​

MySQL/MariaDB​

PostgreSQL​

Complete Example Workflow​

SQL Command Types: DDL, DML, DCL, TCL, DQL​

1. DDL – Data Definition Language​

Views and Materialized Views​

2. DML – Data Manipulation Language​

3. DCL – Data Control Language​

4. TCL – Transaction Control Language​

5. DQL – Data Query Language​

Additional DQL Operators and Clauses​

Understanding SQL JOINs​

1. Basic JOIN (INNER JOIN)​

2. LEFT JOIN (LEFT OUTER JOIN)​

3. RIGHT JOIN (RIGHT OUTER JOIN)​

Quick Summary Table for JOINs​

Setting Up a Local SQL Connection

Step 1: Install a Database Server

MySQL/MariaDB

PostgreSQL

Step 2: Start the Database Service

MySQL/MariaDB

PostgreSQL

Step 3: Connect to Your Database

MySQL/MariaDB

PostgreSQL

Working with SQL Dumps

Creating Database Dumps

MySQL/MariaDB

PostgreSQL

Restoring from Dumps

MySQL/MariaDB

PostgreSQL

Complete Example Workflow

SQL Command Types: DDL, DML, DCL, TCL, DQL

1. DDL – Data Definition Language

Views and Materialized Views

2. DML – Data Manipulation Language

3. DCL – Data Control Language

4. TCL – Transaction Control Language

5. DQL – Data Query Language

Additional DQL Operators and Clauses

Understanding SQL JOINs

1. Basic JOIN (INNER JOIN)

2. LEFT JOIN (LEFT OUTER JOIN)

3. RIGHT JOIN (RIGHT OUTER JOIN)

Quick Summary Table for JOINs