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 amdinistrative tasks.

Connecting to MySQL

mysql -u username -p

This prompts for a password and opens an interactive session.

Data base 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 isues 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

Floating-Point and Fixed-Point Types

Date and Time Types