Beyond the core CRUD (Create, Read, Update, Delete) operations you've just practiced, MySQL offers several other specialized data manipulation commands.
Here are the most useful ones to know as you move to an intermediate level:
1. REPLACE INTO
This is a MySQL-specific extension. It works like a "Smart Insert."
- If the record is new, it inserts it.
- If a record with the same Primary Key already exists, it deletes the old record and inserts the new one.
REPLACE INTO products (id, name, price)
VALUES (1, 'Premium Coffee Maker', 65.00);2. INSERT ... ON DUPLICATE KEY UPDATE
This is often preferred over REPLACE because it doesn't delete the old row; it just updates the columns you specify if the record already exists. This is great for hitting a "save" button where you don't know if the item is new or existing.
INSERT INTO products (id, name, price)
VALUES (1, 'Coffee Maker', 54.99)
ON DUPLICATE KEY UPDATE price = 54.99;3. INSERT INTO ... SELECT
This allows you to copy data from one table directly into another. It’s very common for creating backups or moving data to archives.
-- Move expensive products to a luxury_items table
INSERT INTO luxury_items (name, price)
SELECT name, price FROM products WHERE price > 100;4. LOAD DATA INFILE
When you have thousands or millions of rows in a CSV file, using INSERT statements is too slow. LOAD DATA is a high-speed command to import data from a text file into a table.
LOAD DATA LOCAL INFILE 'products.csv'
INTO TABLE products
FIELDS TERMINATED BY ','
IGNORE 1 LINES;5. CALL
In MySQL, you can write Stored Procedures (a group of SQL statements saved for later use). You use the CALL command to execute them.
CALL apply_discount_to_all_products(0.10); -- A custom procedure to lower prices by 10%Summary of Data Commands:
- DML (Data Manipulation Language):
SELECT,INSERT,UPDATE,DELETE,REPLACE. - DDL (Data Definition Language):
CREATE,ALTER,DROP,TRUNCATE(These change the structure, not the content).
Which of these would you like to see an example of in your current terminal?
