SQL Local Setup, Dumps, and Command Types Explained

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;

---

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;

---

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;

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	Define/modify structure
DML	Data Manipulation Language	INSERT, UPDATE, DELETE	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	Query data

The typical order of SQL command execution is:

1. DDL (Data Definition Language)
   First, define the structure of the database (tables, schemas, etc.).

2. DCL (Data Control Language)
   Next, set up permissions and access control for users.

3. DML (Data Manipulation Language)
   Then, insert, update, or delete data within the tables.

4. TCL (Transaction Control Language)
   Use transaction commands (COMMIT, ROLLBACK) to manage changes made by DML commands.

5. DQL (Data Query Language)
   Finally, query and retrieve data from the database.

info

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