SQL: Data Manipulation Basics

The INSERT INTO statement is your primary tool for adding new rows to a table. In professional environments, data is rarely static; it grows constantly. When writing an insert statement, you must ensure a 1-to-1 mapping between the columns you specify and the values you provide. If a table has a primary key that is auto-incremented, you typically omit that column from your statement, allowing the database engine to handle the numbering. For example, if we have $n$ columns, we must provide $n$ corresponding values. String values must be enclosed in single quotes, while numeric values like integers or decimals do not require them.

The UPDATE statement modifies existing records. The most critical component of an update is the WHERE clause. Without it, the database will apply your change to every single row in the table—a common and costly mistake. You can update multiple columns at once by separating them with commas. For instance, if you want to increase a player's score by 10%, you would use a mathematical expression like $score = score * 1.10$. This allows for dynamic updates based on current values rather than just static replacements.

The DELETE statement removes rows from a table entirely. Like the update statement, it relies heavily on the WHERE clause for safety. It is important to distinguish between `DELETE` and `DROP`. While `DROP` deletes the entire table structure, `DELETE` only removes the data inside the rows. In advanced systems, we often use a Soft Delete (updating a 'status' column to 'inactive') instead of a hard delete to preserve data history, but knowing how to perform a permanent removal is essential for database maintenance and privacy compliance (like GDPR).