MySQL SQL Syntax

MySQL SQL Syntax: Command Groups and Core Rules

Structured Query Language (SQL) is the standard language for communicating with a MySQL server. Every query you run belongs to one of four command groups that define tables, edit rows, control access, or manage transactions.

The Four SQL Command Categories

1. DDL (Data Definition Language): Builds and changes table structures using CREATE, ALTER, DROP, and TRUNCATE.
2. DML (Data Manipulation Language): Reads and modifies stored rows using SELECT, INSERT, UPDATE, and DELETE.
3. DCL (Data Control Language): Grants and revokes user privileges with GRANT and REVOKE.
4. TCL (Transaction Control Language): Groups write operations safely with START TRANSACTION, COMMIT, and ROLLBACK.

Essential MySQL Syntax Rules

SQL statements are easy to read when you format keywords consistently. Follow these core parsing rules so your scripts work across every environment:

• End Every Statement with a Semicolon: The ; character tells MySQL where one command ends and the next begins.
• Use Uppercase for Keywords: Keywords like SELECT and FROM are case-insensitive, so writing them in uppercase separates syntax from column names.
• Keep Table Names Lowercase: On Linux servers, table names are case-sensitive because InnoDB maps them to filesystem identifiers.

Practical SQL Example

This script mixes DDL to create demo_products and DML to insert and read a row.

-- DDL: Define table structure
CREATE TABLE demo_products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) NOT NULL
);

-- DML: Add one product
INSERT INTO demo_products (product_name, price)
VALUES ('Mechanical Keyboard', 129.99);

-- DML: Read stored products
SELECT product_id, product_name, price
FROM demo_products;

Common Syntax Mistakes

• Using Double Quotes for Strings: Standard SQL uses single quotes 'text' for string literals; double quotes can behave as identifiers under ANSI_QUOTES.
• Forgetting Commas Between Columns: Always check trailing commas before FROM—MySQL throws a syntax error on an extra comma before FROM.

Frequently Asked Questions

Are SQL keywords case-sensitive in MySQL?

No, select and SELECT work identically, though uppercase keywords make queries much easier to scan during code reviews.

Can I run multiple SQL statements in one batch?

Yes, separate each statement with a semicolon ; so the client or migration runner executes them sequentially.

Is SELECT part of DDL or DML?

SELECT belongs to DML (Data Manipulation Language) because it queries row data rather than changing table definitions.