Database Guide & Reviewer
This guide provides a step-by-step explanation of essential MySQL commands using
Command Prompt (CMD) and XAMPP. These commands will help you create, modify, and
manage databases effectively.
DROP DATABASE
**Purpose:** Deletes an existing database.
**Syntax:**
DROP DATABASE database_name;
**Example:**
DROP DATABASE Bernardo_RussellMike;
CREATE DATABASE
**Purpose:** Creates a new database.
**Syntax:**
CREATE DATABASE database_name;
**Example:**
CREATE DATABASE Bernardo_RussellMike;
USE DATABASE
**Purpose:** Selects a database to work with.
**Syntax:**
USE database_name;
**Example:**
USE Bernardo_RussellMike;
SHOW DATABASES
**Purpose:** Displays all available databases.
**Syntax:**
SHOW DATABASES;
CREATE TABLE
**Purpose:** Creates a new table inside the selected database.
**Syntax:**
CREATE TABLE table_name (
column_name datatype constraints,
...
);
**Example:**
CREATE TABLE Bernardo (
ID_Number INT PRIMARY KEY AUTO_INCREMENT,
First_Name VARCHAR(50),
Middle_Name VARCHAR(50),
Last_Name VARCHAR(50),
Age INT,
Birthday DATE,
Gender ENUM('Male', 'Female'),
Relationship VARCHAR(50),
Address TEXT
);
ALTER TABLE (ADD COLUMN)
**Purpose:** Adds a new column to an existing table.
**Syntax:**
ALTER TABLE table_name ADD COLUMN column_name datatype;
**Example:**
ALTER TABLE Bernardo ADD COLUMN Contact_Number VARCHAR(15);
ALTER TABLE (MODIFY COLUMN)
**Purpose:** Modifies the data type of a column.
**Syntax:**
ALTER TABLE table_name MODIFY COLUMN column_name new_datatype;
**Example:**
ALTER TABLE Bernardo MODIFY COLUMN Age TINYINT;
ALTER TABLE (CHANGE COLUMN)
**Purpose:** Renames a column and optionally changes its data type.
**Syntax:**
ALTER TABLE table_name CHANGE old_column_name new_column_name datatype;
**Example:**
ALTER TABLE Bernardo CHANGE Contact_Number Mobile_Number VARCHAR(20);
ALTER TABLE (DROP COLUMN)
**Purpose:** Deletes a column from a table.
**Syntax:**
ALTER TABLE table_name DROP COLUMN column_name;
**Example:**
ALTER TABLE Bernardo DROP COLUMN Middle_Name;
INSERT INTO TABLE
**Purpose:** Inserts new data into a table.
**Syntax:**
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
**Example:**
INSERT INTO Bernardo (First_Name, Last_Name, Age, Birthday, Gender, Relationship,
Address)
VALUES ('Russell Mike', 'Bernardo', 25, '1999-01-15', 'Male', 'Self', 'Cabanatuan City');
UPDATE TABLE
**Purpose:** Updates existing data in a table.
**Syntax:**
UPDATE table_name SET column_name = value WHERE condition;
**Example:**
UPDATE Bernardo SET Address = 'Nueva Ecija' WHERE First_Name = 'Russell Mike';
SELECT FROM TABLE
**Purpose:** Displays all data from a table.
**Syntax:**
SELECT * FROM table_name;
**Example:**
SELECT * FROM Bernardo;
Final Notes
- Always use **USE database_name;** before performing table operations.
- Use **SHOW DATABASES;** and **SHOW TABLES;** to check available databases and
tables.
- Be cautious when using **DROP DATABASE** and **DROP COLUMN** as they
permanently delete data.
