Introduction to DML

Imagine our OnlineStore database. We’ve set up the structure with tables for products, customers, and orders. But what good is an empty store? We need to add new products as they arrive, update customer information when they move, and perhaps remove items that are no longer sold. This is where Data Manipulation Language (DML) comes into play. It’s the set of tools we use to bring our database to life by adding, changing, and removing the data itself. 

By the end of this lesson, we will understand 

• What is DML?

• Why is it essential? 

• How to use its core commands, such as INSERT, UPDATE, and DELETE to manage data effectively within our MySQL databases?

We’ll explore how these commands allow us to interact with and modify the data stored in our tables, making our database dynamic and responsive to real-world changes.

What is data manipulation language (DML)?

Data is the heart of any database. While data definition language (DDL), which we’ve touched upon when creating tables and databases, helps us define the structure where data lives, DML commands are crucial for interacting with the data within those structures. Without DML, our database would just be an empty shell. We need DML to populate our tables with information, to keep that information current as things change, and to remove data that’s no longer needed. For instance, in our OnlineStore, DML allows us to add new customer sign-ups, update product stock levels after a sale, or remove orders that have been cancelled. It’s the active, day-to-day language of data management.

Data manipulation language (DML) is a subset of SQL (Structured Query Language) used to add, modify, or delete data within database tables. Think of it as the set of action verbs for your data. Once we have our tables created (using DDL), DML statements are what we use to fill them up, change their contents, and clean them out. The three primary DML commands that we’ll focus on are:

1. INSERT: Adds new rows (records) to a table.

2. UPDATE: Modifies existing rows in a table.

3. DELETE: Removes existing rows from a table.

These commands form the backbone of how applications interact with the database to manage information. Let’s dive into each of these and see how they work with our OnlineStore database.

The INSERT statement

The INSERT statement is our gateway to adding new information into the database. Every time a new customer registers on our OnlineStore, a new product is added to our inventory, or a new order is placed, an INSERT operation is performed. Without it, our database would remain static and empty, unable to capture new events or entities. ...