MySQL Databases

A database is an organized collection of information that can be stored, accessed, and managed efficiently.

For example, a school database may contain information about students, courses, teachers, and fees.

School Database
      ↓
Students
Courses
Teachers
Fees

A MySQL database is a database managed by the MySQL Database Management System.

It can contain tables, views, stored procedures, functions, and other database objects.

A database can contain multiple tables.

school
   |
   ├── students
   ├── teachers
   ├── courses
   └── fees

Each table stores a specific type of related information.

The CREATE DATABASE statement is used to create a new database.

CREATE DATABASE school;

This creates a database named school.

You can use IF NOT EXISTS to avoid an error if the database already exists.

CREATE DATABASE IF NOT EXISTS school;

If the database already exists, MySQL does not create another copy with the same name.

The SHOW DATABASES command displays databases that the current MySQL user can access.

SHOW DATABASES;

The USE command selects a database for the current session.

USE school;

After this command, SQL operations can be performed on objects inside the school database.

The DATABASE() function can be used to check the currently selected database.

SELECT DATABASE();

If no database has been selected, the result can be NULL.

Choose clear and meaningful names for databases.

Examples:

school
library
hospital
shop
company

Meaningful names make database management easier.

After selecting a database, you can create tables inside it.

USE school;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

Use SHOW TABLES to display tables in the currently selected database.

SHOW TABLES;

The command does not display tables from every database. It works with the selected database.

A simple MySQL database structure can look like this:

Database
   |
   ├── Table 1
   │     ├── Column
   │     ├── Column
   │     └── Column
   |
   └── Table 2
         ├── Column
         ├── Column
         └── Column

A MySQL server can contain multiple databases.

MySQL Server
   |
   ├── school
   ├── library
   ├── hospital
   └── company

A user can work with a database according to their permissions.

You can switch from one database to another using the USE command.

USE school;

USE library;

The second command makes library the current database.

CREATE DATABASE college;

USE college;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

CREATE TABLE courses (
    id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

Here, the college database contains two tables.

Data is normally stored inside tables rather than directly inside the database.

INSERT INTO students
(id, name, course)
VALUES
(1, 'Rahul', 'Python');

Use the SELECT statement to retrieve information from tables.

SELECT * FROM students;

The query retrieves all columns and rows from the students table.

You can specify the database name before the table name.

SELECT *
FROM school.students;

This is useful when working with multiple databases.

Web applications often use databases to store application data.

For example:

PHP Application
      ↓
MySQL Database
      ↓
Students Table
      ↓
Student Records

A library application could use a database named library.

library
   |
   ├── books
   ├── students
   ├── issues
   └── returns

Each table can store information about one part of the library system.

A school management system could contain:

school
   |
   ├── students
   ├── teachers
   ├── courses
   ├── attendance
   └── fees

MySQL users may have different permissions on databases.

For example, one user may be allowed to read data while another user may be allowed to create, update, or delete database objects.

Permissions help control access to database resources.

Database name behavior can depend on the operating system and MySQL configuration.

For portable applications, it is a good practice to use simple and consistent database naming conventions.

school_db
library_db
company_db

Before creating tables or running queries, check which database is currently selected.

SELECT DATABASE();

This helps prevent accidentally working in the wrong database.

Create databases according to the actual requirements of your application.

For example, a single school management application normally does not need a separate database for every table.

school
   ├── students
   ├── teachers
   ├── fees
   └── attendance

1. Connect to MySQL
2. Create database
3. Select database
4. Create tables
5. Insert data
6. Read data
7. Update data
8. Delete data

This is a common basic workflow when developing a MySQL application.

Some common database-related errors include:

• Database does not exist
• Database name is misspelled
• User does not have permission
• No database has been selected
• Trying to create an existing database without suitable handling

CREATE DATABASE school;

SHOW DATABASES;

USE school;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    course VARCHAR(100)
);

INSERT INTO students
(id, name, course)
VALUES
(1, 'Rahul', 'Python'),
(2, 'Amit', 'Java');

SELECT * FROM students;

Suppose you are creating a student management system. You can create a database called student_management.

CREATE DATABASE student_management;

USE student_management;

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    mobile VARCHAR(15),
    course VARCHAR(100)
);

INSERT INTO students
(id, name, mobile, course)
VALUES
(1, 'Rahul Kumar', '9876543210', 'Python'),
(2, 'Amit Kumar', '9876501234', 'Java');

SELECT * FROM students;

This example demonstrates how a database can act as the main container for application tables and data.