MySQL Database Fundamentals and Essential Commands

Database Management Concepts

Database administration involves various critical tasks including SQL operations for data manipulation (Create, Read, Update, Delete), database backup strategies, and implementing high-availability solutions like master-slave replication and read-write separation.

Database Types

Databases serve as organized repositories for data storage and management. Database Management Systems (DBMS) are software applications that enable effective organization, management, and access to data.

Popular database systems include:

• MySQL
• Oracle
• SQL Server
• MariaDB (a MySQL fork)
• PostgreSQL (also known as the elephant database)

Relational vs. Non-Relational Databases

Relational databases (MySQL, Oracle, PostgreSQL) store data in a two-dimensional table structure with rows and columns. Columns represent objects or fields, while rows contain the actual data values.

Non-relational databases include:

• In-memory cache databases: Redis
• Index-based databases: Elasticsearch
• Document-based databases: MongoDB

Non-relational databases typically use key-value pair storage structures where data is accessed via unique keys.

Comparison of Database Types

Relational Database Advantages:

• Clear table structures and logical organization
• Easier data relationship management
• Data integrity and completeness

Relational Database Disadvantages:

• Slower read/write operations
• Limited concurrency handling
• Complex data migration processes

Non-Relational Database Advantages:

• High concurrency for read/write operations
• Efficient handling of massive datasets
• Scalable architecture

Non-Relational Database Disadvantages:

• Complex data logic in key-value storage
• Data typically stored in memory (Redis), risking loss on unexpected restarts

MySQL Data Types

• CHAR: Fixed-length string type that uses a specified number of bytes regardless of actual content length.
• VARCHAR: Variable-length string type that stores only the actual content with a hidden end character, consuming one additional byte.
• INT: Integer data type for whole numbers.
• FLOAT: Single-precision floating-point number with format FLOAT(m,d), where m is total digits and d is decimal places.
• DECIMAL: Precise floating-point number with format DECIMAL(total_digits, decimal_places).

Performance considerations: CHAR offers better read/write performance due to contiguous storage, while VARCHAR is more space-efficient but may create disk fragments after modifications.

SQL Language Fundamentals

Key SQL Terminology

• Table: Data structure with rows and columns
• Row: Individual record in a table
• Column: Attribute or field in a table
• Index: Data structure for efficient data retrieval
• View: Virtual table based on SQL query
• Privilege: Permission level for database operations
• Procedure: Stored set of SQL statements
• Function: Stored routine that returns a value
• Event: Scheduled database task

SQL Syntax Guidelines

• SQL statements are case-insensitive, but best practice uses uppercase for keywords
• Statements can span multiple lines but must end with a semicolon
• Identifiers (databases, tables, columns) must start with a letter, followed by letters, numbers, or special characters
• Avoid reserved keywords (e.g., table, select, show databases)
• Database, table, and user names are case-sensitive in MySQL

SQL Categories

1. Data Definition Language (DDL)

DDL creates and modifies database objects like databases, tables, and indexes.

CREATE COMPANY_DB;
# Create a database named COMPANY_DB

CREATE TABLE employees (
    employee_id INT(4) NOT NULL,
    # Define column attributes: data type and nullability
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10,2),
    department_id INT(4)
);

CREATE TABLE departments (
    department_id INT(4) NOT NULL,
    department_name VARCHAR(50) NOT NULL,
    location VARCHAR(100)
);

2. Data Manipulation Language (DML)

DML manages data within tables through insert, update, and delete operations.

Inserting Data

# Insert specific values into specific columns
INSERT INTO employees(employee_id, first_name, last_name, salary, department_id)
VALUES(1001, 'John', 'Doe', 65000.00, 1);

# Insert values into all columns
INSERT INTO employees VALUES(1002, 'Jane', 'Smith', 72000.00, 2);

# Insert with NULL values
INSERT INTO employees VALUES(1003, 'Robert', 'Johnson', NULL, 3);

Updating Data

UPDATE employees 
SET salary = 70000.00 
WHERE employee_id = 1001;

Deleting Data

DELETE FROM employees 
WHERE employee_id = 1003;

3. Data Query Language (DQL)

DQL retrieves data from the database using SELECT statements.

# Select specific columns
SELECT first_name, last_name FROM employees;

# Select with row limitation
SELECT * FROM employees LIMIT 0, 3;
# Returns rows 1-3

# Conditional queries
SELECT first_name, last_name 
FROM employees 
WHERE department_id = 1 AND salary > 60000;

# Pattern matching (LIKE)
SELECT * FROM employees WHERE last_name LIKE '%son';
# Names ending with 'son'
SELECT * FROM employees WHERE last_name LIKE '%a%';
# Names containing 'a'
SELECT * FROM employees WHERE last_name LIKE 'J%';
# Names starting with 'J'

4. Data Control Language (DCL)

DCL manages user privileges and access control through GRANT and REVOKE statements.

5. Transaction Control Statements

Transaction control statements manage database transactions using COMMIT, ROLLBACK, and SAVEPOINT.

Table Structure and Modification

Understanding Table Structure

Key	Default	Extra
Primary/Foreign Key	Default value	Additional info (auto-increment, etc.)

Modifying Table Structure

# Rename a table
ALTER TABLE employees RENAME staff;

# Add a new column
ALTER TABLE staff ADD hire_date DATE DEFAULT '2023-01-01';

# Modify column properties
ALTER TABLE staff MODIFY salary DECIMAL(12,2);