Mysql Queries | Alter Table, Drop, Truncate, Rename, Backup and Restore

MySQL DBMS Practical on Windows – Alter Table, Drop, Truncate, Rename, Backup and Restore

In this practical for FYBSc IT, we will perform some important operations in MySQL for Windows. These include altering tables, dropping/truncating/renaming tables, and backing up as well as restoring a database. Each step is explained with commands that you can directly try in your Windows system.

Step 1: Open MySQL on Windows

Open the MySQL Command Line Client on your Windows system and log in with your root password. You can also use MySQL Workbench if you prefer a GUI-based tool.

Step 2: Altering a Table

The ALTER TABLE command is used to add, modify, or delete columns in an existing table. 

-- Add a new column
ALTER TABLE Students ADD email VARCHAR(100);

-- Modify an existing column
ALTER TABLE Students MODIFY course VARCHAR(100);

   
  




   -- Drop a column
ALTER TABLE Students DROP age;

Step 3: Dropping, Truncating, and Renaming Tables

1. Dropping a Table: This removes the table completely along with all its data.

DROP TABLE Students;

2. Truncating a Table: This deletes all rows from the table but keeps the structure.

TRUNCATE TABLE Students;

3. Renaming a Table: This changes the table name.

RENAME TABLE Students TO Learners;

Step 4: Backing Up a Database

In MySQL for Windows, you can back up a database using the mysqldump command. 

mysqldump -u root -p CollegeDB > C:\backup\CollegeDB.sql

Here, CollegeDB is the database name and the backup file will be stored as CollegeDB.sql in the C:\backup\ folder.

Step 5: Restoring a Database

To restore a database from a backup, use the following command: 

mysql -u root -p CollegeDB < C:\backup\CollegeDB.sql

This will restore all tables and data into the CollegeDB database from the backup file.

Step 6: Example – Working on Database

Let’s create an example database and perform all operations step by step in MySQL for Windows. 

-- Create Database
CREATE DATABASE CollegeDB;
USE CollegeDB;

-- Create Table
CREATE TABLE Students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    course VARCHAR(50),
    age INT
);

-- Insert Records
INSERT INTO Students VALUES (1, 'Rahul Sharma', 'BSc IT', 20);
INSERT INTO Students VALUES (2, 'Priya Patel', 'BSc IT', 19);

-- Alter Table (Add Email Column)
ALTER TABLE Students ADD email VARCHAR(100);

-- Update Data
UPDATE Students SET email = 'rahul@example.com' WHERE id = 1;

-- Rename Table
RENAME TABLE Students TO Learners;

-- Truncate Table
TRUNCATE TABLE Learners;
-- Drop Table
DROP TABLE Learners;

-- Backup Database (Windows Command Prompt)
mysqldump -u root -p CollegeDB > C:\backup\CollegeDB.sql;

-- Restore Database (Windows Command Prompt)
mysql -u root -p CollegeDB < C:\backup\CollegeDB.sql;

Conclusion

In this MySQL for Windows practical, you learned how to alter tables, drop, truncate, rename tables, and perform database backup and restore. We also saw an example database (CollegeDB) where all queries were executed step by step. These commands are very important for your FYBSc IT DBMS practicals.

👉 Keep practicing these queries on Windows to strengthen your SQL skills.

🔗