MySQL: An Introduction to Basic SQL Syntax and a Detailed…

Last updated: 2026-08-26

SQL is a universal language for working with databases—once you learn it, you’ll have the key to all relational databases.

This lesson provides a systematic introduction to the classification and basic syntax of SQL, laying a solid foundation for further study.

1. What You'll Learn

The Five Major Categories of SQL and Their Uses
SELECT Basic Query Syntax
Two Ways to Write SQL Comments
The Difference Between Keywords and Reserved Words
Identifier Naming Conventions

2. A True Story of an Analyst

(1) Pain Point: This wouldn't happen if we switched to a different database

A data analyst who used Oracle at their previous company now uses MySQL at their new company:

Actually, there’s no need—the core SQL syntax is universal, and the differences account for only 5%.

(2) The Solution According to the SQL Standard

SQL is an international standard (ISO/IEC 9075) that all relational databases adhere to:

SQL

-- This SQL runs on MySQL, PostgreSQL, Oracle, and SQL Server
SELECT 1 + 1 AS result;

Benefits: Learn standard SQL syntax; when switching to a different database, you’ll only need to learn 5% of the differences.

Database	Identical Syntax	Differing Syntax
MySQL	95%	LIMIT, AUTO_INCREMENT
PostgreSQL	95%	SERIAL, ILIKE
Oracle	95%	ROWNUM, SYSDATE
SQL Server	95%	TOP, GETDATE()

3. The Five Major Categories of SQL

graph TB
    A[SQL Language] --> B[DDL Data Definition]
    A --> C[DML Data Manipulation]
    A --> D[DQL Data Query]
    A --> E[DCL Data Control]
    A --> F[TCL Transaction Control]
    B --> B1[CREATE]
    B --> B2[ALTER]
    B --> B3[DROP]
    C --> C1[INSERT]
    C --> C2[UPDATE]
    C --> C3[DELETE]
    D --> D1[SELECT]
    E --> E1[GRANT]
    E --> E2[REVOKE]
    F --> F1[COMMIT]
    F --> F2[ROLLBACK]

Category	Full Name	Keywords	Purpose	Hazard Level
DDL	Data Definition Language	CREATE/ALTER/DROP/TRUNCATE	Defines database structure	⚠️ Medium
DML	Data Manipulation Language	INSERT/UPDATE/DELETE	Insert, update, delete data	⚠️ Medium
DQL	Data Query Language	SELECT	Query data	✅ Secure
DCL	Data Control Language	GRANT/REVOKE	Permission management	🔴 High
TCL	Transaction Control Language	COMMIT/ROLLBACK/SAVEPOINT	Transaction control	⚠️ Medium

(1) DDL — Data Definition Language

Define the structure of the database (databases, tables, indexes).

SQL

-- Create a Database
CREATE DATABASE mydb;

-- Create a Table
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- Modify the table structure
ALTER TABLE users ADD COLUMN email VARCHAR(100);

-- Delete Table
DROP TABLE users;

-- Clear Table Data (Keep Structure)
TRUNCATE TABLE users;

(2) DML — Data Manipulation Language

Data in the table (insert, delete, update).

SQL

-- Insert data
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- Update Data
UPDATE users SET name = 'Bob' WHERE id = 1;

-- Delete Data
DELETE FROM users WHERE id = 1;

(3) DQL — Data Query Language

Query data from the table.

SQL

-- Query All Fields
SELECT * FROM users;

-- Conditional Query
SELECT name, email FROM users WHERE id > 10;

-- Sorted Queries
SELECT * FROM users ORDER BY created_at DESC;

-- Limit Results
SELECT * FROM users LIMIT 10;

(4) DCL — Data Control Language

Manage user permissions.

SQL

-- Grant Permissions
GRANT SELECT, INSERT ON mydb.* TO 'user'@'localhost';

-- Revoke Permissions
REVOKE INSERT ON mydb.* FROM 'user'@'localhost';

(5) TCL — Transaction Control Language

Manage the submission and rollback of transactions.

SQL

-- Start Transaction
START TRANSACTION;

-- Perform an action
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit Transaction
COMMIT;

-- Or roll back the transaction
ROLLBACK;

4. Basic SELECT Syntax

(1) Complete Syntax of SELECT

SQL

SELECT [DISTINCT] column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column [ASC|DESC]]
[LIMIT offset, count];

▶ Example: Basic Query

TEXT 📖 Display only

---+---------+------------------
id | name    | email            
---+---------+------------------
1  | Alice   | alice@email.com  
2  | Bob     | bob@email.com    
3  | Charlie | charlie@email.com
---+---------+------------------
3 rows in set

-----------+-----------+-------
first_name | last_name | salary
-----------+-----------+-------
Alice      | Alice     | 25.00 
Bob        | Bob       | 50.00 
Charlie    | Charlie   | 75.00 
-----------+-----------+-------
3 rows in set

-----------+-----------+-------
first_name | last_name | salary
-----------+-----------+-------
Alice      | Alice     | 25.00 
Bob        | Bob       | 50.00 
Charlie    | Charlie   | 75.00 
-----------+-----------+-------
3 rows in set

----------------------+--------------------
first_name first_name | last_name last_name
----------------------+--------------------
Alice                 | Alice              
Bob                   | Bob                
Charlie               | Charlie            
----------------------+--------------------
3 rows in set

-------------------
DISTINCT department
-------------------
Sales              
Engineering        
Marketing          
-------------------
3 rows in set

SQL

-- Query All Fields
SELECT * FROM employees;

-- Query a Specific Field
SELECT first_name, last_name, salary FROM employees;

-- Using Aliases
SELECT first_name AS first_name, last_name AS last_name, salary AS salary FROM employees;

-- (Omit) AS (same result)
SELECT first_name first_name, last_name last_name FROM employees;
SELECT DISTINCT department FROM employees;

TEXT 📖 Display only

+------+--------+---------+
| first_name | last_name | salary  |
+------+--------+---------+
| John | Smith  | 5000.00 |
| Jane | Doe    | 6000.00 |
+------+--------+---------+

TEXT 📖 Display only

Output displayed

▶ Example: Conditional Query

TEXT 📖 Display only

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

---+-------+----------------
id | name  | email          
---+-------+----------------
1  | Alice | alice@email.com
2  | Bob   | bob@email.com  
---+-------+----------------
2 rows in set

SQL

-- Equal Value Query
SELECT * FROM employees WHERE department = 'Engineering';

-- Range Query
SELECT * FROM employees WHERE salary BETWEEN 4000 AND 8000;

-- List Query
SELECT * FROM employees WHERE department IN ('Sales', 'Marketing');

-- Fuzzy Search
SELECT * FROM employees WHERE first_name LIKE 'J%';

-- Null Value Query
SELECT * FROM employees WHERE manager_id IS NULL;

TEXT 📖 Display only

+-------------+-----------+--------+-------------+
| first_name  | last_name | salary | department  |
+-------------+-----------+--------+-------------+
| John        | Smith     | 5000.00| Engineering |
| Jane        | Doe       | 6000.00| Engineering |
+-------------+-----------+--------+-------------+

TEXT 📖 Display only

Output displayed

5. SQL Comments

(1) Single-line comments

SQL

-- This is a single-line comment (Standard Format)
SELECT * FROM users; -- Query All Users

# This is also a single-line comment (MySQL-specific)
SELECT * FROM users;

(2) Multi-line comments

SQL

/*
This is a multi-line comment
Can span multiple lines
Commonly used to illustrate complex queries
*/
SELECT * FROM users
WHERE status = 'active';

▶ Example: Practical Applications of Comments

TEXT 📖 Display only

Query OK, 0 rows affected

SQL

/*
Features: Query statistics on active users
Author: Alice
Date: 2026-07-03
Revision History:
  - 2026-07-02 Add email_verified condition
  - 2026-07-03 Add a filter for registration date
*/
SELECT 
    COUNT(*) AS total_users,           -- Total Number of Users
    AVG(age) AS avg_age,               -- Average Age
    MIN(created_at) AS earliest_reg    -- Earliest registration date
FROM users
WHERE status = 'active'                -- Count only active users
  AND email_verified = TRUE            -- Email verified
  AND created_at >= '2025-01-01';      -- Registered after 2025

TEXT 📖 Display only

Output displayed

6. Keywords and Reserved Words

(1) What Is a Keyword?

Keywords are words that have special meanings in SQL, such as SELECT, FROM, and WHERE.

Type	Description	Example
Keyword	Has a special meaning, but can be used as an identifier (must be enclosed in backticks)	`order`, `group`
Reserved Words	Cannot be used as identifiers (not recommended even when enclosed in backticks)	`SELECT`, `TABLE`

▶ Example: Handling Keyword Conflicts

SQL

-- Error: order is a keyword
CREATE TABLE order (id INT);

-- Correct: Enclose in backticks
CREATE TABLE `order` (id INT);

-- Better: Avoid using keywords as table names
CREATE TABLE orders (id INT);

▶ Try it Yourself

TEXT 📖 Display only

Output displayed

7. Identifier Naming Conventions

(1) Naming Conventions

Rule	Description	Example
Character range	Letters, numbers, underscores	`user_name` ✅
First character	Must be a letter or underscore	`1user` ❌
Length Limit	Maximum 64 characters	—
Case Sensitivity	Windows is case-insensitive, Linux is case-sensitive	`Users` ≠ `users` (Linux)
Reserved Word	Avoid Using	`order` ❌ → `orders` ✅

(2) Recommended Naming Conventions

Style	Example	Recommendation
snake_case	`user_name`, `order_id`	⭐⭐⭐
camelCase	`userName`, `orderId`	⭐⭐
PascalCase	`UserName`, `OrderId`	⭐

💡 Tip: The MySQL community recommends using snake_case, which is consistent with the style of the official documentation.

▶ Example: Good Naming Practices

TEXT 📖 Display only

Query OK, 0 rows affected

Query OK, 0 rows affected

SQL

-- Good Naming Practices
CREATE TABLE user_orders (
    id INT PRIMARY KEY,
    user_id INT,
    order_number VARCHAR(20),
    total_amount DECIMAL(10,2),
    created_at TIMESTAMP
);

-- Poor Naming
CREATE TABLE t1 (
    c1 INT,
    c2 VARCHAR(20),
    c3 DECIMAL(10,2)
);

TEXT 📖 Display only

Output displayed

8. Guidelines for Writing SQL Statements

(1) Formatting Guidelines

SQL

-- Recommended Format: Capitalize keywords, one field per line
SELECT 
    first_name,
    last_name,
    salary
FROM employees
WHERE department = 'Engineering'
ORDER BY salary DESC
LIMIT 10;

(2) The Importance of Semicolons

SQL

-- A semicolon is a statement terminator.
SELECT * FROM users;  -- Statement 1
SELECT * FROM orders; -- Statement 2

-- Multiple statements must be separated by semicolons when executed.

❓ FAQ

Q Do SQL statements have to be in uppercase?

A No, they don't have to be, but it's recommended to capitalize keywords (SELECT/FROM/WHERE) to improve readability. MySQL is case-insensitive.

Q Which is better, * or a specific field?

A We recommend using a specific field in production environments (better performance, clearer semantics). You can use * during the learning phase.

Q Do SQL comments affect performance?

A No. The MySQL parser ignores comments, and comments are not executed.

Q Can Chinese characters be used for table names or field names?

A Technically, yes (backticks are required), but it is strongly discouraged. Use English column names e.g. user_name.

Q Which is more dangerous, DDL or DML?

A DDL (DROP/ALTER) is more dangerous—dropping a table deletes all its data, and altering the table structure may affect the application. Be sure to back up your data before performing these operations.

📖 Summary

SQL is the standard language for manipulating relational databases and is divided into five categories: DDL, DML, DQL, DCL, and TCL.
DDL defines structures (CREATE/ALTER/DROP), DML manipulates data (INSERT/UPDATE/DELETE)
DQL queries data (SELECT), DCL manages permissions (GRANT/REVOKE), and TCL controls transactions (COMMIT/ROLLBACK)
There are two types of SQL comments: -- single-line comments and /* */ multi-line comments
We recommend using snake_case as an identifier; avoid using keywords.
SQL statements end with a semicolon ;

📝 Exercises

Basic Question (Difficulty ⭐): Write the DDL statement to create the students table, which includes the fields id (primary key), name (not null), age, and grade.
Advanced Question (Difficulty ⭐⭐): Write a SELECT statement to retrieve employees from the employees table whose salary is greater than 5,000 and whose department is 'Sales,' sorted in descending order by salary, and display only the first 5 rows.
Challenge (Difficulty: ⭐⭐⭐): Write a complete transaction that transfers 100 yuan from Account A to Account B, including a transaction start, two UPDATE statements, and a commit or rollback.

Web-Tutorial Tech Team

A team of developers maintaining programming tutorials. Each tutorial is written and reviewed by developers with expertise in that field. We work to keep our content accurate and reliable — if you spot an issue, please let us know.

About Us

MySQL: An Introduction to Basic SQL Syntax and a Detailed…

1. What You'll Learn

2. A True Story of an Analyst

(1) Pain Point: This wouldn't happen if we switched to a different database

(2) The Solution According to the SQL Standard

3. The Five Major Categories of SQL

(1) DDL — Data Definition Language

(2) DML — Data Manipulation Language

(3) DQL — Data Query Language

(4) DCL — Data Control Language

(5) TCL — Transaction Control Language

4. Basic SELECT Syntax

(1) Complete Syntax of SELECT

▶ Example: Basic Query

▶ Example: Conditional Query

5. SQL Comments

(1) Single-line comments

(2) Multi-line comments

▶ Example: Practical Applications of Comments

6. Keywords and Reserved Words

(1) What Is a Keyword?

▶ Example: Handling Keyword Conflicts

7. Identifier Naming Conventions

(1) Naming Conventions

(2) Recommended Naming Conventions

▶ Example: Good Naming Practices

8. Guidelines for Writing SQL Statements

(1) Formatting Guidelines

(2) The Importance of Semicolons

❓ FAQ

📖 Summary

📝 Exercises

🙏 帮我们做得更好