MySQL.

MySQL is an open-source relational database management system (RDBMS) that you query with Structured Query Language (SQL). Oracle develops it and releases it under the GNU General Public License (GPL).

Its key features:

• ACID compliance through the default InnoDB storage engine. ACID stands for atomicity, consistency, isolation and durability, the guarantees that keep data intact even after a crash.
• Storage engines chosen per table, such as InnoDB, MyISAM and Memory.
• Replication from a source server to replicas, and Group Replication, whose multi-primary mode lets every member accept writes.
• Horizontal partitioning, which splits a large table by rows.
• Triggers and stored procedures for logic on the server.
• Full-text indexes for text search.
• It runs on Linux, Windows and macOS.

MySQL's main storage engines are InnoDB, the default, and MyISAM. A storage engine stores and reads a table's data, and you choose one per table. Oracle recommends InnoDB except for specialized cases.

• InnoDB: ACID transactions with crash recovery. It also locks rows, not tables, and enforces foreign keys.
• MyISAM: table-level locking, with no transactions or foreign keys, for read-mostly data.
• Memory, formerly HEAP: rows kept in RAM, very fast for single-value lookups, but lost at shutdown.
• Archive: compressed storage for archival data. It supports INSERT and SELECT, but not UPDATE or DELETE.
• CSV: rows stored as comma-separated text, for data exchange.
• Federated: remote MySQL tables used as local ones.
• NDB: the clustered engine of MySQL NDB Cluster.

SHOW ENGINES lists the engines your server supports. To check one table's engine:

SHOW TABLE STATUS WHERE Name = 'table_name';

DELETE removes chosen rows, TRUNCATE TABLE empties the whole table at once, and DROP TABLE removes the table itself along with its data.

• DELETE removes the rows its WHERE clause matches, fires ON DELETE triggers and can be rolled back. It is slower because it deletes rows one by one.
• TRUNCATE drops and re-creates the table, which is much faster than DELETE on large tables. In MySQL it causes an implicit commit, so it cannot be rolled back. It does not fire ON DELETE triggers, and it resets the AUTO_INCREMENT counter.
• DROP removes the table definition, all its data and its triggers. It also causes an implicit commit and is very fast.

For InnoDB, TRUNCATE fails if a foreign key in another table references the table.

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

-- TRUNCATE all data
TRUNCATE TABLE users;

-- DROP entire table
DROP TABLE users;

CHAR(n) is a fixed-length string and VARCHAR(n) is a variable-length string. In both, n counts characters, not bytes.

• CHAR pads values with spaces to the full length when stored, and MySQL strips trailing spaces on read. The length can be 0 to 255. It is a little faster for fixed-size values such as country codes, but wastes space on shorter ones.
• VARCHAR stores only the characters used, plus a length prefix, so it is more storage efficient.

You can declare a VARCHAR length up to 65,535, but the real limit is lower. All columns share a 65,535-byte row size limit, and the character set decides how many bytes each character takes. The prefix is 1 byte when values need at most 255 bytes, and 2 bytes otherwise. With the default utf8mb4, a character can take up to 4 bytes, so a VARCHAR(255) value can need 1,020 bytes and a 2-byte prefix.

-- CHAR(10) always holds 10 characters, padded with spaces
name CHAR(10)

-- VARCHAR(255) stores only the characters used, plus a length prefix
name VARCHAR(255)

MySQL has exact integer types, the exact fixed-point type DECIMAL, and the approximate floating-point types FLOAT and DOUBLE.

Integer types, with their signed ranges:

• TINYINT: 1 byte, from -128 to 127
• SMALLINT: 2 bytes, from -32,768 to 32,767
• MEDIUMINT: 3 bytes, from -8,388,608 to 8,388,607
• INT: 4 bytes, from -2,147,483,648 to 2,147,483,647
• BIGINT: 8 bytes, from -2^63 to 2^63 - 1

UNSIGNED allows only nonnegative values, so an INT UNSIGNED column holds 0 to 4,294,967,295.

Decimal and floating-point types:

• DECIMAL, also called NUMERIC, stores exact values, for financial data.
• FLOAT uses 4 bytes for single precision, and DOUBLE uses 8 bytes for double precision, so DOUBLE is more precise. Both are approximate, so exact comparisons can fail.

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