Database Fundamentals

A database represents a structured collection of organized information. Contemporary applications—from e-commerce platforms to enterprise resource systems and social media applications—depend on databases for persistent data storage and retrieval. The visual presentation in browsers or mobile apps merely reflects data that resides within these storage systems.

Database Management Systems (DBMS) provide the software layer for interacting with databases. Rather than manipulating data directly, developers send structured commands to the DBMS, which handles actual data operations. These commands are expressed in SQL (Structured Query Language), the standardized syntax for relational database operations.

Relational databases organize data into interconnected two-dimensional tables composed of rows and columns. This tabular structure ensures consistent formatting and enables complex queries using standardized SQL syntax. MySQL, PostgreSQL, Oracle, and SQL Server all exemplify this architecture.

MySQL Architecture Overview

Installation and Connection

MySQL offers two primary distributions:

• Commercial Edition: Licensed version with official technical support
• Community Edition: Open-source version without official support (used in this guide)

Connection to a MySQL server utilizes the command-line client:

mysql -u<username> -p<password> [-h<host> -P<port>]

Default values: host=127.0.0.1, port=3306

Data Organization Hierarchy

MySQL organizes information in a hierarchical structure:

1. Database Server: Physical MySQL instance
2. Database: Logical container (multiple databases per server)
3. Table: Structured entities within databases
4. Record: Individual data rows within tables

This architecture enables isolated data management across different applications or modules.

SQL Language Structure

Syntax Conventions

• Statements terminate with semicolons
• Formatting whitespace enhances readability
• Case-insensitive keywords (UPPERCASE recommended for clarity)
• Comment styles: -- inline comment, # MySQL-specific comment, /* multi-line comment */

Command Categories

SQL commands classify into four functional groups:

Category	Purpose	Scope
DDL	Data Definition Language	Database objects (databases, tables, columns)
DML	Data Manipulation Language	Data modification (INSERT, UPDATE, DELETE)
DQL	Data Query Language	Data retrieval (SELECT)
DCL	Data Control Language	Access control and permissions

DDL Operations

Database-Level Commands

List all databases:

SHOW DATABASES;

Check current database:

SELECT DATABASE();

Create data base:

CREATE DATABASE [IF NOT EXISTS] database_name;

Example:

CREATE DATABASE IF NOT EXISTS sample_app;

Switch database context:

USE database_name;

Remove database:

DROP DATABASE [IF EXISTS] database_name;

Table Creation and Design

Basic Table Syntax

CREATE TABLE table_name (
    column1 data_type [constraints] [COMMENT 'description'],
    column2 data_type [constraints] [COMMENT 'description'],
    ...
) [COMMENT = 'table_description'];

Constraint Types

MySQL enforces data integrity through constraints:

Constraint	Purpose	Keyword
NOT NULL	Prohibits NULL values	NOT NULL
UNIQUE	Enforces distinct values	UNIQUE
PRIMARY KEY	Uniquely identifies rows (implies NOT NULL + UNIQUE)	PRIMARY KEY
DEFAULT	Specifies automatic default values	DEFAULT value
FOREIGN KEY	Maintains referential integrity	FOREIGN KEY

Example: User Accounts Table

CREATE TABLE user_accounts (
    account_id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'Unique identifier',
    login_name VARCHAR(50) NOT NULL UNIQUE COMMENT 'Login credential',
    display_name VARCHAR(100) NOT NULL COMMENT 'User full name',
    birth_year YEAR COMMENT 'Year of birth',
    status TINYINT DEFAULT 1 COMMENT 'Account status: 1=active, 0=inactive'
) COMMENT = 'Application user directory';

The AUTO_INCREMENT attribute automatically generates sequential numeric identifiers, eliminating manual key management.

Data Type Reference

Numeric Types

TINYINT        -- Range: -128 to 127 (signed), 0-255 (unsigned)
SMALLINT       -- Range: -32768 to 32767
INT            -- Standard integer
BIGINT         -- Large integer
DECIMAL(M,D)   -- Fixed-point precision
FLOAT/DOUBLE   -- Floating-point values

String Types

CHAR(L)        -- Fixed-length string
VARCHAR(L)     -- Variable-length string (max 65,535 bytes)
TEXT           -- Long text data
BLOB           -- Binary large objects

Temporal Types

DATE           -- YYYY-MM-DD format
TIME           -- HH:MM:SS format
DATETIME       -- Combined date and time
TIMESTAMP      -- Unix timestamp (2038 limit)
YEAR           -- 4-digit year

Practical Table Design Example

Designing an employee management table:

CREATE TABLE employee_records (
    emp_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT 'Employee number',
    username VARCHAR(30) NOT NULL UNIQUE COMMENT 'System login',
    pwd_hash VARCHAR(64) NOT NULL DEFAULT 'default_hash' COMMENT 'Password hash',
    full_name VARCHAR(100) NOT NULL COMMENT 'Legal name',
    gender ENUM('M', 'F', 'O') NOT NULL COMMENT 'Gender identity',
    photo_url VARCHAR(500) COMMENT 'Profile picture URL',
    position ENUM('manager', 'developer', 'analyst', 'hr') COMMENT 'Job role',
    hire_date DATE COMMENT 'Start date',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Record creation',
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last modified'
) COMMENT = 'Corporate employee directory';

Table Modification Commands

Add column:

ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];

Modify column type:

ALTER TABLE table_name MODIFY COLUMN column_name new_data_type;

Rename column:

ALTER TABLE table_name CHANGE COLUMN old_name new_name data_type;

Drop column:

ALTER TABLE table_name DROP COLUMN column_name;

Rename table:

RENAME TABLE old_name TO new_name;

Remove table:

DROP TABLE [IF EXISTS] table_name;

DML Operations

Inserting Data

Insert specific columns:

INSERT INTO table_name (col1, col2) VALUES (val1, val2);

Insert all columns:

INSERT INTO table_name VALUES (val1, val2, val3, ...);

Batch insertion:

INSERT INTO table_name (col1, col2) 
VALUES (val1a, val2a), (val1b, val2b), (val1c, val2c);

Practical examples:

-- Insert single record with timestamp
INSERT INTO employee_records (username, full_name, gender, created_at, updated_at)
VALUES ('jdoe', 'John Doe', 'M', NOW(), NOW());

-- Insert complete record
INSERT INTO employee_records 
VALUES (NULL, 'asmith', 'hash123', 'Alice Smith', 'F', NULL, 'developer', '2023-01-15', NOW(), NOW());

-- Batch insert multiple records
INSERT INTO employee_records (username, full_name, gender, created_at, updated_at)
VALUES ('bjones', 'Bob Jones', 'M', NOW(), NOW()),
       ('cwilson', 'Carol Wilson', 'F', NOW(), NOW());

Insertion guidelines:

• Column order must match value order
• String and date literals require quotation marks
• Values must respect column constraints and data types

Updating Records

Update syntax:

UPDATE table_name 
SET column1 = value1, column2 = value2, ... 
[WHERE condition];

Conditional update:

UPDATE employee_records 
SET full_name = 'Jonathan Doe', updated_at = NOW() 
WHERE emp_id = 101;

Bulk update:

UPDATE employee_records 
SET position = 'senior_developer', updated_at = NOW();

Important considerations:

• Omitting the WHERE clause affects all rows
• Always update timestamp columns to reflect modification time
• Use transactions for critical bulk updates

Deleting Records

Delete syntax:

DELETE FROM table_name [WHERE condition];

Single record deletion:

DELETE FROM employee_records WHERE emp_id = 105;

Complete table truncation:

DELETE FROM employee_records;

Critical warnings:

• Unconditional deletion removes all table data
• DELETE cannot target specific columns (use UPDATE to NULLify values)
• Consider TRUNCATE TABLE for faster complete deletion on empty tables
• Always verify WHERE clauses in production environments

Development Workflow Integration

Database operations typically occur across three project phases:

1. Design Phase: Translate requirements into normalized table structures with appropriate data types and constraints
2. Implementation Phase: Execute DDL to create schemas and DML to populate initial data
3. Maintenance Phase: Optimize performance through indexing, query refinement, and periodic archiving

Understanding DDL and DML fundamentals establishes the foundation for effective database-driven application development. Mastery of these operations enables developers to build robust data layers that enforce integrity while supporting business logic requirements.