Databases and Tables

What is a Database?

A database is an organized collection of data that is stored and accessed electronically. In the context of MySQL (and other relational database systems), a database is a container that holds related tables, and each table stores specific types of information in a structured format.

Think of a database as a digital filing cabinet, where:

• Each folder inside the cabinet represents a table.
• Each sheet of paper inside a folder represents a record (or row).
• Each column on the sheet holds a specific type of information (such as a name, ID number, or date).

Why Use a Database?

Before databases, data was often stored in spreadsheets or simple files. However, databases offer many advantages:

• Data is organized and consistent.
• Data can be searched and filtered quickly.
• Relationships between different sets of data can be defined and managed.
• Access control and security can be applied.
• Large amounts of data can be handled efficiently.

MySQL and Relational Databases

MySQL is a relational database management system (RDBMS), meaning it organizes data into tables with rows and columns, and allows relationships between tables.

Each database in MySQL can contain:

• One or more tables
• Views
• Stored procedures
• Triggers
• Other database objects

What is a Table?

A table is the fundamental building block of a relational database. It represents a collection of related data in rows and columns.

Example Table: students

• Each row is called a record.
• Each column is called a field or attribute.
• Each column has a specific data type, like INT, VARCHAR, DATE, etc.

Creating a Database

To create a new database in MySQL, use the following command:

CREATE DATABASE college;

This creates an empty database named college.

To switch to that database:

USE college;

Now all subsequent operations will happen inside the college database.

Creating a Table

Once inside a database, you can create a table using the CREATE TABLE command. For example:

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

Explanation:

• id: an integer field that uniquely identifies each student
• name: a text field up to 100 characters
• age: a number representing the student’s age
• email: a text field up to 100 characters

Inserting Data into the Table

You can add rows (records) into the table using the INSERT INTO statement:

INSERT INTO students (id, name, age, email)
VALUES (1, 'Alice Smith', 20, 'alice@example.com');

Viewing the Data

To view the data inside the table:

SELECT * FROM students;

This will return all rows and columns from the students table.

Summary

• A database stores and organizes data.
• A table is a structure inside the database that holds data in rows and columns.
• Each table has a defined structure with named columns and specific data types.
• MySQL allows creation, modification, and querying of databases and tables using simple commands.

Learning how databases and tables work is the first and most important step toward understanding how data is stored, structured, and used in real-world applications.