MySQL SQL

MySQL SQL Syntax: Structure, Rules, and Execution

Structured Query Language (SQL) is the standard language used to communicate with MySQL databases. SQL commands fall into distinct operational categories.

SQL Command Categories

1. DDL (Data Definition Language): Defines and modifies database schemas (CREATE, ALTER, DROP, TRUNCATE).
2. DML (Data Manipulation Language): Manages data records within tables (SELECT, INSERT, UPDATE, DELETE).
3. DCL (Data Control Language): Manages database security and permissions (GRANT, REVOKE).
4. TCL (Transaction Control Language): Manages transactional boundaries (COMMIT, ROLLBACK, SAVEPOINT).

Practical SQL Code Example: DDL and DML in Action

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

-- DML: Insert product record
INSERT INTO products (product_name, price) 
VALUES ('Mechanical Keyboard', 129.99);

-- DML: Retrieve product record
SELECT product_id, product_name, price 
FROM products;

Expected Query Output

Best Practices & Gotchas

• End Statements with Semicolons: Always terminate SQL statements with ; to allow batch query execution.
• SQL Case Sensitivity: Keywords are case-insensitive, but table names follow host OS file system case rules. Always stick to lowercase table names.

Self-Check Challenge

Which SQL command category does the ALTER TABLE statement belong to?