Chapter 5: Inserting Data into Tables (DML)

So far, you’ve learned how to design and create the structure of your database using DDL commands. You have your scriptbuzz_db with users and products (and hopefully orders and posts from your exercises!). But right now, these tables are empty. They’re like beautifully designed but unfurnished houses.

This chapter introduces the first and most fundamental DML (Data Manipulation Language) command: INSERT INTO. DML commands are all about interacting with the actual data stored within your tables. The INSERT INTO statement allows you to add new rows (records) of data into your existing tables. This is how you’ll populate your database with users, products, orders, and all the dynamic content your web applications need.

What is DML?

Data Manipulation Language (DML) commands are used for managing data within schema objects. These commands do not affect the schema or database objects, but rather the information contained within the schema objects.

The main DML commands are:

  • INSERT INTO: Adds new rows to a table.
  • SELECT: Retrieves data from one or more tables.
  • UPDATE: Modifies existing data in a table.
  • DELETE: Removes data from a table.

We’ll focus on INSERT INTO in this chapter.

Adding New Rows

When you insert data, you’re essentially adding a new row to your table. Each row represents a single record (e.g., one user, one product, one order). You need to provide values for the columns in that row, ensuring the values match the data types and constraints you defined when creating the table.

SQL Syntax & Examples

There are two primary ways to use the INSERT INTO statement:

This is the preferred and safest method. You explicitly list the columns you are providing values for.

Syntax:

SQL
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

  • INSERT INTO table_name: Specifies the table where data will be inserted.
  • (column1, column2, ...): A comma-separated list of the columns you are providing data for. The order here matters for mapping values.
  • VALUES (value1, value2, ...): A comma-separated list of the values to be inserted, corresponding in order to the columns listed.

Key Points:

  • You don’t need to specify columns that are AUTO_INCREMENT (the database handles it).
  • You don’t need to specify columns that have a DEFAULT value, unless you want to override that default.
  • You must specify all columns that are defined as NOT NULL and do not have a DEFAULT or AUTO_INCREMENT value.
  • String and Date/Time values must be enclosed in single quotes ('). Numeric values are not.

Example 1: Inserting into users table

Let’s insert some sample user data into our users table in scriptbuzz_db. Remember our users table structure:

SQL
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    is_admin TINYINT(1) DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login DATETIME
);

We’ll provide values for username, email, and password. user_id is AUTO_INCREMENT, is_admin has a DEFAULT, and created_at has DEFAULT CURRENT_TIMESTAMP, so we don’t have to specify them unless we want to. last_login can be NULL by default.

SQL
USE scriptbuzz_db;

INSERT INTO users (username, email, password)
VALUES ('john_doe', 'john.doe@example.com', 'hashed_password_1');

What it does:

  • A new row is added to the users table.
  • user_id will automatically get a value (e.g., 1).
  • username is set to ‘john_doe’.
  • email is set to ‘john.doe@example.com’.
  • password is set to ‘hashed_password_1’.
  • is_admin will default to 0.
  • created_at will automatically be set to the current date and time.
  • last_login will be NULL.

Let’s add another user, this time making them an admin:

SQL
USE scriptbuzz_db;

INSERT INTO users (username, email, password, is_admin)
VALUES ('admin_user', 'admin@example.com', 'hashed_password_admin', 1);

What it does: Similar to above, but is_admin is explicitly set to 1 (true).

Example 2: Inserting into products table

Our products table structure:

SQL
CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    stock_quantity INT NOT NULL DEFAULT 0,
    is_active TINYINT(1) DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

SQL
USE scriptbuzz_db;

INSERT INTO products (name, category, description, price, stock_quantity)
VALUES ('Laptop Pro', 'Electronics', 'Powerful laptop for professionals.', 1200.00, 50);

INSERT INTO products (name, price, stock_quantity)
VALUES ('Wireless Mouse', 25.50, 200); -- category and description will be NULL

What it does:

  • First query: Inserts a laptop product with all specified non-default values. product_id, is_active, created_at, updated_at will be handled by the database.
  • Second query: Inserts a mouse. Notice we skipped category and description. Since they are nullable, they will be NULL. stock_quantity overrides its default 0.

This method requires you to provide values for every single column in the table, and they must be in the exact order the columns were defined when the table was created. If you miss a column or get the order wrong, your query will fail or insert data into the wrong columns.

Syntax:

SQL
INSERT INTO table_name
VALUES (value1, value2, value3, ...);

Example: (Using Method 2 for users – NOT RECOMMENDED for general use)

SQL
USE scriptbuzz_db;

-- This assumes user_id, username, email, password, is_admin, created_at, last_login
-- are the exact order and number of columns in the users table.
INSERT INTO users
VALUES (NULL, 'jane_doe', 'jane.doe@example.com', 'hashed_password_jane', 0, NOW(), NULL);
-- NULL for user_id lets AUTO_INCREMENT work
-- NOW() is a MySQL function for current datetime

Why this is generally not recommended:

  • Fragile: If you ALTER TABLE later (add, remove, or reorder columns), this query will break.
  • Hard to Read: Without column names, it’s difficult to understand which value corresponds to which column.
  • Error Prone: Easy to miss a column or misorder values.

Stick to Method 1 (INSERT INTO table_name (columns) VALUES (values);) for robustness and readability.

Inserting Multiple Rows

You can insert multiple rows with a single INSERT INTO statement using the first method.

Syntax:

SQL
INSERT INTO table_name (column1, column2, ...)
VALUES
(valueA1, valueA2, ...),
(valueB1, valueB2, ...),
(valueC1, valueC2, ...);

Example: Inserting multiple products

SQL
USE scriptbuzz_db;

INSERT INTO products (name, description, price, stock_quantity, is_active)
VALUES
('Mechanical Keyboard', 'RGB backlit, tactile switches.', 99.99, 30, 1),
('USB-C Hub', 'Multi-port adapter for laptops.', 39.00, 100, 1),
('Webcam 1080p', 'Full HD video conferencing.', 65.00, 0, 0); -- stock_quantity 0, not active

What it does: Inserts three new product records in one go.

Common Pitfalls and Tips

  • Missing NOT NULL Columns: If you try to insert a row and omit a column that is defined as NOT NULL (and doesn’t have a DEFAULT or AUTO_INCREMENT), the query will fail with an error.
  • Data Type Mismatch: Trying to insert text into an INT column, or a non-numeric string into a DECIMAL column, will result in an error.
  • String vs. Numeric Values: Remember to enclose string and date/time values in single quotes ('value'). Numbers (123, 1.5) do not need quotes.
  • AUTO_INCREMENT Values: When manually inserting, you can use NULL for AUTO_INCREMENT columns to let the database generate the next value, or simply omit the column from your list.
  • Security (SQL Injection): When building web applications, NEVER directly insert user-provided input into SQL queries. This opens you up to SQL Injection attacks. Always use prepared statements with parameter binding (e.g., PDO in PHP) to safely handle user input. We’ll touch on this in a later chapter, but it’s crucial for real-world applications.

Notes:

  • DML (Data Manipulation Language) commands are used to interact with the data itself.
  • The INSERT INTO statement is used to add new rows (records) to a table.
  • Best practice: Always explicitly list the column names you are inserting into (INSERT INTO table (col1, col2) VALUES (val1, val2);). This makes your queries robust and readable.
  • You don’t need to specify AUTO_INCREMENT columns or columns with DEFAULT values unless you want to override the default.
  • String and Date/Time values require single quotes. Numeric values do not.
  • Multiple rows can be inserted in a single INSERT statement by providing multiple VALUES clauses separated by commas.
  • Be mindful of NOT NULL constraints and data type compatibility.

Small Exercise

  1. If you created the orders table in the previous chapter, insert at least two sample orders into it.
    • Make sure one order uses a user_id that exists in your users table (e.g., 1 for ‘john_doe’).
    • Set appropriate values for total_amount and status.
  2. If you created the posts table, insert at least two sample blog posts into it.
    • Ensure each post has a title, content, and a user_id (again, try using 1 for ‘john_doe’).
    • Set the published flag (your TINYINT(1) column) appropriately for each.

After completing these insertions, you won’t immediately see the data using just INSERT INTO. That’s where the next chapter comes in! Get ready to retrieve the data you’ve just inserted.

📘 Continue to the Next Chapter

Retrieving Data: The SELECT Statement

Master the SELECT statement in MySQL and learn how to fetch records from your database using PHP and SQL queries.

➡️

🚀 Explore Popular Learning Tracks