Essential DDL and DML Commands: Create, Select, Update, Delete

Working with MySQL usually starts with two jobs: defining your tables and working with the rows inside them. The essential DDL and DML commands are how you do that safely.

DDL commands define database objects such as tables. DML commands insert, read, update, and delete table data. The examples below use a small products table so you can see the commands in context.

Data Definition Language (DDL): Creating Tables

DDL commands are concerned with the schema and structure of your database objects. The most fundamental DDL command for data storage is CREATE TABLE, which allows you to define the blueprint for your data.

CREATE TABLE Syntax

The basic syntax for creating a table involves specifying the table name and then defining its columns, including their data types and any constraints.

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);

table_name must be unique within the current database. Each column needs a name, a data type, and optional constraints such as NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, or DEFAULT value.

CREATE TABLE Example

Let's create a simple products table to store information about items in an inventory:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL UNIQUE,
    category VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this example:

• product_id is an integer that automatically increments and serves as the primary key.
• product_name is a required string that must be unique.
• price is a required decimal number.
• stock_quantity defaults to 0 if not specified.
• created_at records the timestamp when the row was created.

Data Manipulation Language (DML): Working with Data

DML commands are used to manage the records (rows) within your database tables. They are the most frequently used commands for day-to-day database operations.

INSERT Command: Adding New Data

The INSERT command is used to add new rows of data into a table.

INSERT Syntax

There are a couple of ways to use INSERT:

1. Specifying values for all columns:

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

   The order of values must match the order of columns in the table definition.

2. Specifying values for specific columns:

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

   This is generally preferred as it's more readable and less prone to errors if the table structure changes.

INSERT Example

Using our products table:

-- Inserting values for all columns (assuming AUTO_INCREMENT for product_id)
INSERT INTO products (product_name, category, price, stock_quantity)
VALUES ('Laptop Pro', 'Electronics', 1200.50, 50);

-- Inserting values for specific columns
INSERT INTO products (product_name, price)
VALUES ('Mechanical Keyboard', 75.99);

-- Inserting multiple rows at once
INSERT INTO products (product_name, category, price, stock_quantity)
VALUES 
('Ergonomic Mouse', 'Electronics', 25.00, 120),
('Desk Lamp', 'Home Goods', 45.00, 75);

SELECT Command: Retrieving Data

The SELECT command is the cornerstone of data retrieval. It allows you to query your database and fetch specific data based on various criteria.

SELECT Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column_name [ASC|DESC]
LIMIT number;

• column1, column2, ...: The columns you want to retrieve. Use * to select all columns.
• FROM table_name: The table you are querying.
• WHERE condition: (Optional) Filters the rows based on a specified condition. Conditions can involve comparison operators (=, !=, >, <, >=, <=), logical operators (AND, OR, NOT), and pattern matching (LIKE).
• ORDER BY column_name [ASC|DESC]: (Optional) Sorts the result set by one or more columns. ASC for ascending (default), DESC for descending.
• LIMIT number: (Optional) Restricts the number of rows returned.

SELECT Examples

-- Select all columns for all products
SELECT *
FROM products;

-- Select only the name and price of products
SELECT product_name, price
FROM products;

-- Select products in the 'Electronics' category
SELECT *
FROM products
WHERE category = 'Electronics';

-- Select products with a price greater than 100, ordered by price descending
SELECT product_name, price
FROM products
WHERE price > 100
ORDER BY price DESC;

-- Select the top 5 most expensive products
SELECT product_name, price
FROM products
ORDER BY price DESC
LIMIT 5;

-- Select products whose name starts with 'L'
SELECT *
FROM products
WHERE product_name LIKE 'L%';

UPDATE Command: Modifying Existing Data

The UPDATE command allows you to change existing records in a table.

UPDATE Syntax

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

• table_name: The table containing the records to update.
• SET column1 = value1, ...: Specifies which columns to update and their new values.
• WHERE condition: Crucial! This clause determines which rows will be updated. If omitted, all rows in the table will be updated.

UPDATE Examples

-- Increase the price of the 'Laptop Pro' by 50
UPDATE products
SET price = price + 50.00
WHERE product_name = 'Laptop Pro';

-- Update the category and stock for the 'Mechanical Keyboard'
UPDATE products
SET category = 'Accessories', stock_quantity = 100
WHERE product_name = 'Mechanical Keyboard';

-- **WARNING**: This would update the price of ALL products to 10.00
-- UPDATE products
-- SET price = 10.00;

Tip: Always use a WHERE clause when updating to ensure you only modify the intended records. Test your WHERE clause with a SELECT statement first to verify it targets the correct rows.

DELETE Command: Removing Data

The DELETE command is used to remove rows from a table.

DELETE Syntax

DELETE FROM table_name
WHERE condition;

• table_name: The table from which to remove rows.
• WHERE condition: Crucial! This clause specifies which rows to delete. If omitted, all rows in the table will be deleted.

DELETE Examples

-- Delete the product with the name 'Desk Lamp'
DELETE FROM products
WHERE product_name = 'Desk Lamp';

-- Delete all products that have run out of stock (stock_quantity is 0)
DELETE FROM products
WHERE stock_quantity = 0;

-- **WARNING**: This would delete ALL records from the 'products' table!
-- DELETE FROM products;

Warning: Similar to UPDATE, omitting the WHERE clause in a DELETE statement is a destructive operation that will remove all data from the table. Use with extreme caution.

Key Takeaway

Understanding and effectively using DDL and DML commands is fundamental to database management. CREATE TABLE allows you to define the structure of your data, while INSERT, SELECT, UPDATE, and DELETE empower you to populate, query, modify, and manage that data.

By practicing these commands with clear WHERE clauses, especially for UPDATE and DELETE, you can build a solid foundation for working with MySQL databases and ensure the integrity and accuracy of your data. Always remember to back up your data before performing potentially destructive operations.