MySQL is an open-source relational database management system (RDBMS) that uses Structured Query Language (SQL). Key features include:

• ACID Compliance: Ensures data integrity through Atomicity, Consistency, Isolation, and Durability
• Cross-platform: Runs on various operating systems (Linux, Windows, macOS)
• Storage Engines: Supports multiple storage engines like InnoDB, MyISAM, Memory
• Replication: Master-slave and master-master replication support
• Partitioning: Horizontal partitioning for large tables
• Triggers and Stored Procedures: Server-side programming capabilities
• Full-text Indexing: Advanced text search capabilities
• High Performance: Optimized for speed and reliability

MySQL supports multiple storage engines, each optimized for different use cases:

• InnoDB (Default): ACID-compliant, supports foreign keys, row-level locking, crash recovery
• MyISAM: Table-level locking, faster for read-heavy workloads, no foreign key support
• Memory/HEAP: Stores data in RAM, very fast but volatile
• Archive: Compressed storage for archival data, insert and select only
• CSV: Stores data in CSV format, useful for data exchange
• Federated: Access remote MySQL tables as local tables
• NDB: Clustered storage engine for MySQL Cluster

Example to check storage engine:

SHOW TABLE STATUS WHERE Name = 'table_name';

• DELETE: Removes specific rows based on WHERE clause, can be rolled back, triggers fire, slower
• DROP: Removes entire table structure and data, cannot be rolled back, very fast
• TRUNCATE: Removes all rows but keeps table structure, faster than DELETE, limited rollback

-- DELETE specific rows
DELETE FROM users WHERE age < 18;

-- TRUNCATE all data
TRUNCATE TABLE users;

-- DROP entire table
DROP TABLE users;

• CHAR: Fixed-length string, padded with spaces, faster for fixed-size data, uses more storage
• VARCHAR: Variable-length string, stores actual length + data, more storage efficient

-- CHAR always uses 10 bytes
name CHAR(10)

-- VARCHAR uses 1-255 bytes + length bytes
name VARCHAR(255)

MySQL numeric types include:

Integer Types:

• TINYINT: 1 byte (-128 to 127)
• SMALLINT: 2 bytes (-32,768 to 32,767)
• MEDIUMINT: 3 bytes (-8,388,608 to 8,388,607)
• INT: 4 bytes (-2,147,483,648 to 2,147,483,647)
• BIGINT: 8 bytes (very large range)

Decimal Types:

• DECIMAL/NUMERIC: Exact precision, for financial data
• FLOAT: 4 bytes, approximate precision
• DOUBLE: 8 bytes, double precision

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    price DECIMAL(10,2),
    weight FLOAT,
    quantity SMALLINT UNSIGNED
);