Essential MySQL Commands Explained with Diagrams for PHP

 

MySQL Commands

---

1. SHOW

Command Name: SHOW
Description: Displays databases or tables in MySQL.
Syntax:

SHOW DATABASES;
SHOW TABLES;

Example:

Input:
SHOW TABLES;

Output:
users
drivers
vehicles

Summary: Used to view existing database information.

---

2. USE

Command Name: USE
Description: Selects a database to work with.
Syntax:

USE database_name;

Example:

Input:
USE transport_db;

Output:
Database changed.

Summary: Chooses the active database.

---

3. CREATE

Command Name: CREATE
Description: Creates a database or table.
Syntax:

CREATE DATABASE dbname;
CREATE TABLE table_name (...);

Example:

Input:
CREATE TABLE driver(id INT, name VARCHAR(50));

Output:
Table created successfully.

Summary: Used to create new database objects.

---

4. DESCRIBE

Command Name: DESCRIBE
Description: Shows table structure.
Syntax:

DESCRIBE table_name;

Example:

Input:
DESCRIBE driver;

Output:
id INT
name VARCHAR(50)

Summary: Displays column details.

---

5. SELECT

Command Name: SELECT
Description: Retrieves data from a table.
Syntax:

SELECT * FROM table_name;

Example:

Input:
SELECT * FROM driver;

Output:
1  John
2  Ravi

Summary: Used to read data.

---

6. INSERT

Command Name: INSERT
Description: Adds new records into table.
Syntax:

INSERT INTO table_name VALUES(...);

Example:

Input:
INSERT INTO driver VALUES(1,'John');

Output:
1 row inserted.

Summary: Used to add data.

---

7. UPDATE

Command Name: UPDATE
Description: Modifies existing records.
Syntax:

UPDATE table_name SET column=value WHERE condition;

Example:

Input:
UPDATE driver SET name='Ravi' WHERE id=1;

Output:
1 row updated.

Summary: Used to change existing data.

---

8. DELETE

Command Name: DELETE
Description: Removes records from table.
Syntax:

DELETE FROM table_name WHERE condition;

Example:

Input:
DELETE FROM driver WHERE id=1;

Output:
1 row deleted.

Summary: Used to remove data.

---

9. JOIN

Command Name: JOIN
Description: Combines data from multiple tables.
Syntax:

SELECT * FROM table1
JOIN table2
ON condition;

Example:

Input:
SELECT driver.name, vehicle.number
FROM driver
JOIN vehicle
ON driver.id = vehicle.driver_id;

Output:
John  TN01AB1234

Summary: Used to connect related tables.

---

10. LOADING (Import Database)

Command Name: Loading
Description: Import .sql file into database.
Syntax:

mysql -u root -p dbname < file.sql

Example:

Input:
mysql -u root -p transport_db < transport.sql

Output:
Tables and data imported.

Summary: Used to restore database.

---

11. DUMPING (Export Database)

Command Name: Dumping
Description: Export database into .sql file.
Syntax:

mysqldump -u root -p dbname > backup.sql

Example:

Output:
backup.sql file created.

Summary: Used to backup database.

---

FINAL OVERALL SUMMARY

SHOW / DESCRIBE → View structure
USE → Select database
CREATE → Make database/table
SELECT → Read data
INSERT → Add data
UPDATE → Modify data
DELETE → Remove data
JOIN → Combine tables
LOAD & DUMP → Backup and Restore