Understanding Relational Databases with MySQL

A relational database organizes data into structured tables composed of rows and columns. MySQL is a widely used open-source relational database management system (RDBMS) that implements this model.

Core Concepts

• Database: A container holding related tables.
• Table: A grid-like structure storing records as rows and attributes as columns.
• Column: Represents a specific attribute (e.g., email, salary) with consistent data types.
• Row: A single record containing values for all columns in a table.
• Primary Key: A unique identifier for each row; only one per table.
• Foreign Key: Enforces relationships between tables by referencing another table’s primary key.
• Composite Key: A primary key made from multiple columns.
• Index: A performance optimization structure that speeds up data retrieval, analogous to a book index.
• Referential Integrity: Ensures foreign key values correspond to existing primary keys, maintaining data consistency.
• Redundancy: Duplicate data storage that can enhance reliability at the cost of efficiency.

MySQL Administration Basics

MySQL provides both interactive SQL commands and command-line utilities like mysqladmin for administrative tasks.

Connecting to MySQL

mysql -u username -p

This prompts for a password and opens an interactive session.

Database Management Commands

List All Databases

SHOW DATABASES;

Create a Database

CREATE DATABASE IF NOT EXISTS company_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

Delete a Database

DROP DATABASE IF EXISTS company_db;

Select a Database for Use

USE company_db;

Exit the MySQL Shell

EXIT;

Using mysqladmin for Administration

The mysqladmin utility allows performing administrative operations from the terminal without entering the MySQL shell.

Create a Database via mysqladmin

mysqladmin -u username -p create company_db

Delete a Database via mysqladmin

mysqladmin -u username -p drop company_db

Note: This command will prompt for confirmation before deletion.

Check Server Status

mysqladmin -u username -p status

Key Configuration Logs for Performance Tuning

MySQL logs help diagnose performance issues and query behavior:

• log_error: Path to the error log file.
• general_log: Logs all client connections and queries.
• slow_query_log: Captures queries exceeding a defined execution time threshold.
• log_queries_not_using_indexes: Records queries that bypass index usage—useful for identifying optimization opportunities.

Data Types in MySQL Tables

Defining appropriate data types ensures data integrity and efficient storage.

Numeric Types

Type	Storage (Bytes)	Signed Range	Unsigned Range	Use Case
TINYINT	1	-128 to 127	0 to 255	Small integers (e.g., flags)
SMALLINT	2	-32,768 to 32,767	0 to 65,535	Medium-sized counts
MEDIUMINT	3	-8,388,608 to 8,388,607	0 to 16,777,215	Larger integers
INT	4	-2,147,483,648 to 2,147,483,647	0 to 4,294,967,295	General-purpose integers
BIGINT	8	-9.22e18 to 9.22e18	0 to 1.84e19	Very large numbers (e.g., IDs)

Floating-Point and Fixed-Point Types

Type	Storage	Description
FLOAT	4 bytes	Single-precision floating point
DOUBLE	8 bytes	Double-precision floating point (higher accuracy)
DECIMAL(M,D)	Variable	Exact numeric values; ideal for financial data

Date and Time Types

Type	Storage (Bytes)	Range	Format	Purpose
DATE	3	1000-01-01 to 9999-12-31	YYYY-MM-DD	Calendar dates
TIME	3	-838:59:59 to 838:59:59	HH:MM:SS	Time durations or daily times