Database Data Evolution

The history of data storage methods can be illlustrated with a simple ATM example.

# Phase 1: Storing data in individual files.
# Files named inconsistently like 'kevin|123', 'kevin@123', 'kevin*123'.
# This leads to file management issues.

# Phase 2: Organizing data files into a dedicated 'db' folder.
# As user registrations increase, the number of files grows, consuming significant storage space and leading to inefficient data retrieval.

# Phase 3: Database software solves these problems by providing structured, efficient data storage and access.

Database Classification

1. Relational Databases (SQL)

   • Examples: MySQL, Oracle, PostgreSQL, SQL Server, DB2, MariaDB, SQLite.
   • Characteristics: Structured data with fixed table schemas; tables can establish relationships.

2. Non-relational Databases (NoSQL)

   • Examples: Redis (caching), Memcache, MongoDB (often used in web scraping).
   • Characteristics: Schema-less storage using key-value pairs or document formats; Redis has largely superseded Memcache.

MySQL Database Overview

MySQL is a widely used open-source relational database management system. Its architecture follows a client-server model.

Version Selection

• MySQL 5.6 and 5.7 are stable and widely adopted versions.
• MySQL 8.0 is latest major release; however, careful consideration is advised for production environments.

Installation and Setup

Primary Directory Structure

After installation, key files and directories include:

bin/
    mysql.exe      # MySQL client executable
    mysqld.exe     # MySQL server executable
data/              # Directory for database files
my-default.ini    # Configuration file template
README            # Documentation

Starting the MySQL Server

1. Navigate to the bin directory via command line.
2. Execute mysqld.exe to start the server process.
3. In a spearate command line window, execute mysql.exe to start the client and connect to the local server.

Configuring MySQL as a Windows Service

For convenience, MySQL can be installed as a Windows service.

# Install the MySQL service (requires administrator privileges)
mysqld --install

# Start the MySQL service
net start mysql

# Stop the MySQL service
net stop mysql

# Remove the MySQL service (ensure the service is stopped first)
mysqld --remove

Connecting to MySQL

By default, the initial installation has no password for the root user.

# Connect to the local MySQL server as root
mysql -u root

# Connect with a full command specifying host and port
mysql -h 127.0.0.1 -P 3306 -u root -p

Setting the Root Password

After connecting, set a password for the root user.

# Use the mysqladmin command to set the password
mysqladmin -u root password 'new_password'

# Subsequent connections will require the password
mysql -u root -p

Password Recovery Procedure

If the root password is forgotten, follow these steps to reset it:

1. Stop the MySQL service.
2. Start the MySQL server with the --skip-grant-tables option to bypass authentication.

   mysqld --skip-grant-tables
   

3. Connect to the server without a password and update the root user's password.

   -- Connect to the server
   mysql -u root
   
   -- Switch to the mysql system database
   USE mysql;
   
   -- Update the password for the root user (method varies by MySQL version)
   -- For MySQL 5.7:
   UPDATE user SET authentication_string = PASSWORD('new_password') WHERE User='root' AND Host='localhost';
   FLUSH PRIVILEGES;
   

4. Stop the server and restart it normally.

Fundamental SQL Operations

Core concepts: A database is analogous to a folder, a table to a file within that folder, a record to a row in the file, and a field to a column header.

Database-Level Operations

-- Create a new database
CREATE DATABASE database_name;

-- List all databases
SHOW DATABASES;

-- View the creation statement for a specific database
SHOW CREATE DATABASE database_name;

-- Modify database properties (e.g., character set)
ALTER DATABASE database_name CHARACTER SET utf8;

-- Permanently delete a database
DROP DATABASE database_name;

Table-Level Operations

First, select the database to work within.

-- Switch to a specific database
USE database_name;

-- Create a new table with defined columns and data types
CREATE TABLE table_name (
    column1_name DATA_TYPE,
    column2_name DATA_TYPE,
    column3_name DATA_TYPE
);
-- Example:
CREATE TABLE employee (
    id INT,
    name VARCHAR(32),
    department VARCHAR(32)
);

-- Rename an existing table
ALTER TABLE old_table_name RENAME TO new_table_name;

-- List all tables in the current database
SHOW TABLES;

-- View the detailed structure of a table
SHOW CREATE TABLE table_name;

-- View a formatted description of the table structure
DESC table_name;
DESCRIBE table_name;

-- Permanently delete a table
DROP TABLE table_name;