Introduction to Databases & MySQL

What is a Database?

A database is an organized collection of structured data stored electronically. Instead of storing data in files, databases provide efficient ways to store, retrieve, update, and manage large amounts of information.

Relational Databases

Most modern databases are relational databases, which organize data into tables with rows and columns. Each table represents an entity (like users, products, or orders).

Example: Users Table
+----+----------+-------------------+------------+
| id | username | email             | created_at |
+----+----------+-------------------+------------+
| 1  | john_doe | john@example.com  | 2024-01-15 |
| 2  | jane_s   | jane@example.com  | 2024-01-16 |
| 3  | bob_m    | bob@example.com   | 2024-01-17 |
+----+----------+-------------------+------------+

What is MySQL?

MySQL is one of the most popular open-source relational database management systems (RDBMS). It's widely used for web applications and is known for being fast, reliable, and easy to use.

Database Terminology

1. Database

A collection of related tables. Example: An e-commerce site might have a database containing tables for users, products, orders, and reviews.

2. Table

A collection of related data organized in rows and columns. Each table represents one type of entity.

3. Row (Record)

A single entry in a table. Each row represents one instance of the entity. Example: One user, one product, one order.

4. Column (Field)

A specific piece of information stored in a table. Example: username, email, price, quantity.

5. Primary Key

A unique identifier for each row in a table. Usually an id column with auto-incrementing numbers.

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,  -- Primary key
    username VARCHAR(50),
    email VARCHAR(100)
);

6. Foreign Key

A column that creates a relationship between two tables by referencing the primary key of another table.

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,                        -- Foreign key
    total DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

SQL: Structured Query Language

SQL is the language used to interact with relational databases. It allows you to create databases, tables, insert data, query data, update records, and delete information.

Main SQL Categories

MySQL Data Types

Numeric Types

INT          -- Integer: -2147483648 to 2147483647
BIGINT       -- Large integer: -9223372036854775808 to 9223372036854775807
DECIMAL(10,2)-- Fixed-point: 10 digits total, 2 after decimal (for money)
FLOAT        -- Floating-point number (approximate)
DOUBLE       -- Double-precision floating-point

String Types

CHAR(50)     -- Fixed-length string (padded with spaces)
VARCHAR(255) -- Variable-length string (up to 255 characters)
TEXT         -- Long text (up to 65,535 characters)
MEDIUMTEXT   -- Medium text (up to 16,777,215 characters)
LONGTEXT     -- Very long text (up to 4GB)

Date and Time Types

DATE         -- Date: YYYY-MM-DD
TIME         -- Time: HH:MM:SS
DATETIME     -- Date and time: YYYY-MM-DD HH:MM:SS
TIMESTAMP    -- Unix timestamp (auto-updates)
YEAR         -- Year: YYYY

Other Types

BOOLEAN      -- True/False (stored as TINYINT: 0 or 1)
ENUM         -- List of allowed values: ENUM('small', 'medium', 'large')
JSON         -- JSON data (MySQL 5.7+)
BLOB         -- Binary large object (for files)

Accessing MySQL

1. Command Line

# Connect to MySQL
mysql -u username -p

# Connect to specific database
mysql -u username -p database_name

2. phpMyAdmin

A popular web-based interface for managing MySQL databases. Usually available at http://localhost/phpmyadmin in local development environments.

3. MySQL Workbench

Official GUI application from MySQL for database design, development, and administration.

4. PHP (mysqli or PDO)

Connect and interact with MySQL from PHP scripts (covered in later lessons).

Database Design Best Practices

Example: Simple Blog Database

Here's how you might structure a basic blog database:

Database: my_blog

Table: users
+----+----------+-------------------+------------+
| id | username | email             | created_at |
+----+----------+-------------------+------------+

Table: posts
+----+---------+-------+---------+------------+
| id | user_id | title | content | created_at |
+----+---------+-------+---------+------------+

Table: comments
+----+---------+---------+---------+------------+
| id | post_id | user_id | content | created_at |
+----+---------+---------+---------+------------+

Relationships:
- posts.user_id → users.id (who wrote the post)
- comments.post_id → posts.id (which post)
- comments.user_id → users.id (who commented)

What's Next?

In the upcoming lessons, you'll learn how to:

• Create databases and tables with SQL
• Insert, select, update, and delete data
• Write complex queries with WHERE, ORDER BY, and LIMIT
• Join multiple tables to retrieve related data
• Connect PHP to MySQL and build dynamic applications