MySQL - Queries

MySQL - Core Data Management Queries

Introduction to DDL and DML

MySQL queries are commands used to interact with a database. They are broadly categorized into two types:

• Data Definition Language (DDL): Commands that define or modify the database structure. Examples: CREATE, ALTER, DROP.
• Data Manipulation Language (DML): Commands that manage the data within the database. Examples: INSERT, SELECT, UPDATE, DELETE.

This tutorial will walk you through the most essential queries for managing a database, using a products table as our example. For readability, we will write SQL keywords in uppercase, though MySQL is case-insensitive.

Setting Up: CREATE DATABASE and USE

First, we create a new database to hold our tables. Then, we select it to make it the active database for subsequent commands.

Syntax

CREATE DATABASE database_name;
USE database_name;

Example

-- Creates a new database named 'my_store'
CREATE DATABASE my_store;

-- Sets 'my_store' as the active database
USE my_store;

Defining Structure: CREATE TABLE

The CREATE TABLE statement defines a new table, its columns, and the data types and constraints for each column.

Syntax

CREATE TABLE table_name (
    column1_name data_type [constraints],
    column2_name data_type [constraints],
    ...
    PRIMARY KEY (primary_key_column)
);

Example with Modern Best Practices

This example includes common constraints for data integrity:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sku VARCHAR(50) NOT NULL UNIQUE,
    name VARCHAR(255) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    in_stock INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

• AUTO_INCREMENT: Automatically generates a unique number for each new row.
• PRIMARY KEY: Uniquely identifies each record in the table.
• NOT NULL: Ensures a column cannot have a NULL value.
• UNIQUE: Ensures that all values in a column are different.
• DEFAULT: Sets a default value for a column when no value is specified.

Adding Data: INSERT INTO

The INSERT INTO statement adds new rows of data to a table.

Syntax

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

Example (Inserting Multiple Rows)

INSERT INTO products (sku, name, price, in_stock)
VALUES
('BK-001', 'Modern JavaScript', 29.99, 100),
('DB-002', 'SQL for Beginners', 24.99, 50),
('FW-003', 'Learning React', 39.99, 75);

Retrieving Data: SELECT

The SELECT statement is used to query the database and retrieve data that matches criteria that you specify.

Example: Get All Products

SELECT * FROM products;

Example: Get Specific Columns with a Condition

-- Find all products cheaper than $30
SELECT name, price FROM products
WHERE price < 30.00;

Modifying Data: UPDATE

The UPDATE statement modifies existing records in a table.

Warning: Always use a WHERE clause with UPDATE. Without it, you will update all rows in the table.

Syntax

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

Example

-- Put the 'SQL for Beginners' book on sale
UPDATE products
SET price = 19.99
WHERE sku = 'DB-002';

Changing Structure: ALTER TABLE

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Example: Add, Modify, and Drop a Column

-- Add a new column for product description
ALTER TABLE products
ADD COLUMN description TEXT;

-- Modify a column's data type
ALTER TABLE products
MODIFY COLUMN name VARCHAR(300) NOT NULL;

-- Drop the description column
ALTER TABLE products
DROP COLUMN description;

Removing Data: DELETE vs. TRUNCATE

Both DELETE and TRUNCATE remove data from a table, but they work differently.

DELETE Statement

Removes one or more rows based on a WHERE clause. It is a DML operation, so it’s slower, logs each row deletion, and can be rolled back within a transaction. It does not reset AUTO_INCREMENT counters.

-- Warning: Be very careful with the WHERE clause!
DELETE FROM products WHERE in_stock = 0;

TRUNCATE TABLE Statement

Removes all rows from a table quickly. It is a DDL operation, cannot be easily rolled back, and resets any AUTO_INCREMENT values. It’s much faster than DELETE for clearing an entire table.

TRUNCATE TABLE products;

Removing Structure: DROP TABLE

The DROP TABLE statement completely removes a table’s structure and all its data. This action is irreversible.

Syntax

DROP TABLE table_name;

Example

-- This will permanently delete the products table.
DROP TABLE products;