MySql

Creating and managing databases

️ Creating and Managing Databases in MySQL

1. Creating a Database

You can create a new database using the CREATE DATABASE statement:

sql
CREATE DATABASE my_database;

✅ Example:

sql
CREATE DATABASE bookstore;

This creates an empty database named bookstore.

2. Viewing Existing Databases

To list all databases on the server:

sql
SHOW DATABASES;

3. Selecting a Database to Use

Before creating tables or inserting data, you must select the database:

sql
USE bookstore;

4. Creating a Table Inside a Database

sql
CREATE TABLE books (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(100),
price DECIMAL(6,2),
published_date DATE
);

5. Viewing All Tables in a Database

sql
SHOW TABLES;

6. Describing a Table (Structure)

To view columns, data types, and constraints:

sql
DESCRIBE books;

7. Renaming a Database (Workaround)

MySQL doesn’t support direct renaming of a database. Instead:

  1. Create a new database:

    sql
    CREATE DATABASE new_name;

  2. Export data from the old one and import into the new one (via phpMyAdmin or mysqldump command).

  3. Drop the old database if needed:

    sql
    DROP DATABASE old_name;

8. Dropping (Deleting) a Database

⚠️ This permanently deletes the database and all its tables. Use with caution.

sql
DROP DATABASE bookstore;

9. Backing Up a Database (Using CLI)

If you’re using the command line:

bash
mysqldump -u username -p bookstore > bookstore_backup.sql

To restore:

bash
mysql -u username -p bookstore < bookstore_backup.sql

Best Practices

Practice Why it Matters
Use descriptive names Improves clarity and organization
Always backup before deleting Prevents accidental data loss
Normalize your tables Avoids redundant data
Use appropriate data types Saves space and ensures accuracy
Add constraints (PK, FK) Enforces data integrity

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *