Creating and Managing Databases and Tables

Tutorial Overview

This tutorial guides you through creating, managing, and altering databases and tables in MySQL, with examples for common database and table operations.

Prerequisites

Root or sudo access to MySQL.

Steps

Step 1: Log into MySQL

1. Access MySQL with root or a privileged user:

sudo mysql -u root -p

Step 2: Create a New Database

1. To create a new database, use:

CREATE DATABASE database_name;

Replace database_name with your desired name.

2. Verify the Database Creation:

List all databases to confirm:

SHOW DATABASES;

Step 3: Create a New Table

1. Select the database you just created:

USE database_name;

2. Create a table with a specified schema. For example, a students table:

CREATE TABLE students (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

This creates a table with columns for id, name, age, email, and created_at.
AUTO_INCREMENT automatically assigns a unique ID to each record.

3. View the Table Schema:

DESCRIBE students;

Step 4: Insert and Query Data in the Table

1. Insert sample data into the students table:

INSERT INTO students (name, age, email) VALUES
('Alice', 20, 'alice@example.com'),
('Bob', 22, 'bob@example.com');

2. Query the data to confirm insertion:

SELECT * FROM students;

Step 5: Modify the Table Structure

1. Add a New Column:

ALTER TABLE students ADD COLUMN phone VARCHAR(15);

2. Rename an Existing Column:

ALTER TABLE students CHANGE phone phone_number VARCHAR(15);

3. Delete a Column:

ALTER TABLE students DROP COLUMN phone_number;

Step 6: Delete the Table or Database

1. Delete a Table:

DROP TABLE students;

2. Delete a Database:

DROP DATABASE database_name;

Database Management