🗄 SQL 格式化

What is SQL?

SQL (Structured Query Language) is a standardized programming language specifically designed for managing and manipulating relational databases. Since IBM first introduced SEQUEL in 1974, SQL has evolved into one of the most fundamental technologies in the database field. From managing user data for small websites to powering data warehouse analytics for large enterprises, SQL plays an irreplaceable role.

SQL's core capabilities include: Data Query (retrieving data from databases), Data Manipulation (inserting, updating, and deleting records), Data Definition (creating and modifying database structures), and Data Control (managing user access permissions). It is precisely because SQL provides such a complete set of data management capabilities that it has become one of the most frequently used languages in the daily work of full-stack developers, data analysts, and backend engineers.

Core Components of SQL

1. DDL (Data Definition Language)

DDL is used to define and modify the structure of databases, including creating, altering, and dropping tables, indexes, views, and other database objects. Common DDL statements include:

• CREATE TABLE — Creates a new table, defining column names, data types, and constraints
• ALTER TABLE — Modifies the structure of an existing table, such as adding, dropping, or modifying columns
• DROP TABLE — Permanently deletes a table (including all data and structure definition)
• CREATE INDEX — Creates an index on specified columns to accelerate query performance
• CREATE VIEW — Creates a virtual table (view) encapsulating complex query logic
• TRUNCATE TABLE — Quickly removes all data from a table (preserving the table structure)

2. DML (Data Manipulation Language)

DML is used for CRUD operations on data within the database and is the most frequently used part in daily development:

• SELECT — Retrieves data from tables, supporting complex filtering, sorting, grouping, and aggregation
• INSERT INTO — Inserts new rows of data into a table
• UPDATE — Modifies existing records in a table
• DELETE FROM — Removes records matching conditions from a table
• MERGE (or UPSERT) — Performs either an insert or update based on conditions

3. DCL (Data Control Language)

DCL is used to manage database access permissions and security:

• GRANT — Grants specific database permissions (e.g., SELECT, INSERT, DELETE) to users
• REVOKE — Revokes previously granted permissions
• DENY (SQL Server specific) — Explicitly denies a permission

4. TCL (Transaction Control Language)

TCL is used to manage database transactions, ensuring data consistency and integrity:

• BEGIN or START TRANSACTION — Begins a transaction
• COMMIT — Commits the transaction, making all changes permanent
• ROLLBACK — Rolls back the transaction, undoing all changes since the transaction began
• SAVEPOINT — Sets a savepoint within a transaction, enabling partial rollback

Common SQL Database Dialects

Although SQL is an ANSI/ISO standard language, different database systems add their own extensions and features on top of the standard, creating distinctive "dialects." Understanding the differences between dialects is crucial for writing portable, high-performance SQL code.

SQL JOIN Types Explained

JOIN is one of the most important operations in SQL, used to combine data from two or more tables based on association conditions. Understanding the differences and use cases of each JOIN type is essential for writing correct and efficient SQL queries.

Subqueries and CTEs (Common Table Expressions)

Beyond JOINs, SQL provides subqueries and CTEs as two powerful mechanisms for organizing and reusing data:

Subqueries

A subquery is a query nested inside another SQL statement, usable in SELECT, FROM, WHERE, HAVING, and other clauses:

• Scalar Subquery: Returns a single value, commonly used in WHERE comparisons, e.g., WHERE salary > (SELECT AVG(salary) FROM employees)
• Row Subquery: Returns one row with multiple columns, e.g., WHERE (dept_id, salary) = (SELECT dept_id, MAX(salary) FROM employees)
• Table Subquery: Returns multiple rows and columns, commonly used in the FROM clause as a derived table
• Correlated Subquery: The subquery references columns from the outer query and executes once for each outer row, e.g., WHERE salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id)

CTEs (Common Table Expressions)

CTEs use the WITH keyword to define temporary named result sets, making complex queries easier to read and maintain:

• Regular CTE: WITH avg_salary AS (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) SELECT * FROM employees e JOIN avg_salary a ON e.dept_id = a.dept_id WHERE e.salary > a.avg_sal
• Recursive CTE: A CTE can reference itself, used for processing tree structures (e.g., org charts, directory trees), graph traversal, etc. Uses WITH RECURSIVE syntax
• CTE vs Subquery: CTEs can be referenced multiple times, support recursion, and provide clearer structure; subqueries are better suited for simple single-use nesting

Window Functions

Window functions are among the most powerful features in SQL. They perform calculations across rows in a result set without collapsing rows like GROUP BY does. Window functions enable ranking, running totals, moving averages, and other analytical operations while preserving original rows:

• ROW_NUMBER() — Assigns a unique sequential number to each row
• RANK() / DENSE_RANK() — Ranking functions; RANK skips ranks after ties, DENSE_RANK does not
• NTILE(n) — Divides the result set evenly into n buckets
• LAG() / LEAD() — Accesses the row before or after the current row (without changing result set structure)
• SUM() OVER() / AVG() OVER() — Calculates running totals or moving averages
• FIRST_VALUE() / LAST_VALUE() — Gets the first or last value within the window frame

The core of window functions is the OVER() clause. You can define the window range using PARTITION BY (grouping) and ORDER BY (sorting). For example, SELECT dept_id, name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rank FROM employees can find salary rankings within each department.

Indexes and Query Performance Optimization Basics

As data volumes grow, SQL query performance becomes critical. Indexes are the most direct way to improve query efficiency:

• B-Tree Index: The most versatile index type, suitable for most scenarios by default. Works well for equality queries (=), range queries (BETWEEN, >, <), and sorting (ORDER BY / GROUP BY)
• Hash Index: Only suitable for equality lookups, extremely fast but does not support range queries. Used by MySQL's Memory engine and PostgreSQL's Hash index
• Full-Text Index (FULLTEXT): Used for fuzzy text content searches, far more efficient than LIKE '%keyword%'. Supported by both MySQL and PostgreSQL
• Composite Index (Multi-Column Index): Indexes spanning multiple columns, following the leftmost prefix principle — query conditions must start from the leftmost column of the composite index to effectively utilize it
• Covering Index: When all columns needed by a query are contained in the index, the database can return data directly from the index without needing to access the table, dramatically improving efficiency

Practical tips for optimizing SQL queries: choose appropriate data types (avoid VARCHAR for numeric columns), use EXISTS or JOIN instead of IN subqueries, avoid using functions on indexed columns in WHERE clauses (as this disables index usage), and use EXPLAIN to analyze query execution plans.

Why SQL Formatting Matters

In real-world projects, developers frequently encounter SQL statements with wildly inconsistent styles and poor readability — some entire lines crammed together without indentation, some with mixed keyword casing, some lacking line breaks that obscure logical structure. Formatting SQL is about far more than just "looking nice":

• Improved Readability: Consistent indentation and line breaks make SQL's logical hierarchy clear at a glance, with nesting relationships between subqueries, JOINs, and WHERE conditions readily visible
• Fewer Bugs: Well-structured SQL makes it easier to spot logic errors, such as missing parentheses, omitted JOIN conditions, and WHERE clause precedence confusion
• Team Collaboration: A uniform formatting standard eliminates style debates in code reviews, allowing team members to focus on the logic itself
• Faster Debugging: Well-structured SQL allows for easy segment-by-segment commenting, testing, and identification of performance bottlenecks
• Knowledge Transfer: Clearly formatted SQL is much easier for future maintainers to understand and modify

SQL Formatting Best Practices

1. Keyword Casing

The industry mainstream approach writes SQL keywords (SELECT, FROM, WHERE, JOIN, etc.) in UPPERCASE and identifiers like table names and column names in lowercase. This style was first advocated by SQL pioneers like Joe Celko and is now recommended by most database documentation and style guides. Some teams prefer all lowercase (e.g., in certain Ruby/Python communities), but the key is consistency across the entire team.

2. Indentation & Alignment

Each major clause (SELECT, FROM, WHERE, GROUP BY, ORDER BY) should start on a new line and maintain vertical alignment. Content within subqueries should be indented one additional level (typically 2 or 4 spaces). Multiple columns after SELECT can be line-broken with commas at the beginning or end of each line — leading-comma style makes it easier to view changes in Git diffs when adding or removing columns.

3. JOIN Writing Conventions

Each JOIN should start on a new line, with the ON condition immediately after the JOIN or indented on the next line. Always use explicit JOIN syntax (rather than writing join conditions in WHERE), as JOIN syntax more clearly expresses the intended association between tables: FROM users u INNER JOIN orders o ON u.id = o.user_id.

4. Strings & Aliases

Per the SQL standard, strings should be enclosed in single quotes. Double quotes in standard SQL are used for identifiers (such as table names and column names); although MySQL also allows double quotes for strings, this is not portable practice. Table aliases and column aliases should use meaningful short names (e.g., u for users, o for orders) with the AS keyword for readability.

5. Conditions & Operators

Complex WHERE conditions should place each condition on a separate line, aligned by AND/OR at the beginning or end. Within the WHERE clause, place conditions most likely to filter out the most data first (taking advantage of short-circuit evaluation), and prioritize conditions that can utilize indexes.

6. Comments

Add brief comments in complex queries to explain business logic, using -- (line comments) or /* */ (block comments). Comments should explain "why this approach" rather than "what it does," as the latter should be expressed through clear code itself.

Common SQL Pitfalls & Debugging Tips

Why Use Our Online SQL Formatter?

In daily development, developers often encounter scenarios where they need to organize and beautify SQL — copying a long query statement from logs for analysis, exporting stored procedure code from a database management tool that needs tidying, or formatting inherited code as the first step to understanding its logic. Manually adjusting indentation and keyword casing line by line is not only inefficient but also prone to missing details.

Our online SQL Formatter supports the following core capabilities:

• Multi-Dialect Support: Covers major database dialects including MySQL, MariaDB, PostgreSQL, Redshift, SQLite, SQL Server (T-SQL), Oracle (PL/SQL), BigQuery, DB2, Hive, Spark SQL, N1QL, automatically recognizing dialect-specific keywords and syntax structures
• Intelligent Formatting: Automatically handles indentation, line breaks, keyword case conversion (UPPER/lower/preserve), with support for both space and tab indentation
• Real-Time Syntax Highlighting: Formatted results are displayed with syntax highlighting, where keywords, strings, numbers, comments, and function names are distinguished in different colors
• One-Click Minify: Compresses SQL into a minimal single-line form, ideal for embedding into code strings or transmission
• Privacy Protection: All processing happens entirely in your browser — no data is uploaded to any server
• Ready to Use: No software installation required — just open your browser and start using, completely free

Whether you are a database administrator, backend developer, data analyst, or a student learning SQL, this tool helps you quickly transform messy SQL into clean, maintainable code.