範例
本節提供如何使用 SQL 格式化工具處理各種類型 SQL 查詢的實用範例。
基本 SELECT 查詢
簡單 SELECT
輸入：
select id, name, email from users where active = 1;
格式化輸出：
SELECT
id,
name,
email
FROM users
WHERE active = 1;
含 ORDER BY 的 SELECT
輸入：
select u.id, u.name, u.email from users u where u.active = 1 order by u.name asc, u.created_at desc;
格式化輸出：
SELECT
u.id,
u.name,
u.email
FROM users u
WHERE u.active = 1
ORDER BY
u.name ASC,
u.created_at DESC;
複雜 JOIN 查詢
INNER JOIN
輸入：
select u.id, u.name, u.email, p.title, p.content from users u inner join posts p on u.id = p.user_id where u.active = 1 and p.published = 1;
格式化輸出：
SELECT
u.id,
u.name,
u.email,
p.title,
p.content
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE u.active = 1
AND p.published = 1;
多個 JOIN
輸入：
select u.id, u.name, u.email, p.title, p.content, c.name as category_name from users u inner join posts p on u.id = p.user_id left join categories c on p.category_id = c.id where u.active = 1 and p.published = 1;
格式化輸出：
SELECT
u.id,
u.name,
u.email,
p.title,
p.content,
c.name AS category_name
FROM users u
INNER JOIN posts p ON u.id = p.user_id
LEFT JOIN categories c ON p.category_id = c.id
WHERE u.active = 1
AND p.published = 1;
聚合查詢
含 GROUP BY 的聚合
輸入：
select u.id, u.name, count(p.id) as post_count, sum(p.views) as total_views from users u left join posts p on u.id = p.user_id where u.active = 1 group by u.id, u.name having count(p.id) > 0 order by total_views desc;
格式化輸出：
SELECT
u.id,
u.name,
COUNT(p.id) AS post_count,
SUM(p.views) AS total_views
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
WHERE u.active = 1
GROUP BY
u.id,
u.name
HAVING COUNT(p.id) > 0
ORDER BY total_views DESC;
子查詢
WHERE 子句中的子查詢
輸入：
select id, name, email from users where id in (select user_id from posts where published = 1) and active = 1;
格式化輸出：
SELECT
id,
name,
email
FROM users
WHERE id IN (
SELECT user_id
FROM posts
WHERE published = 1
)
AND active = 1;
關聯子查詢
輸入：
select u.id, u.name, u.email, (select count(*) from posts p where p.user_id = u.id and p.published = 1) as post_count from users u where u.active = 1;
格式化輸出：
SELECT
u.id,
u.name,
u.email,
(
SELECT COUNT(*)
FROM posts p
WHERE p.user_id = u.id
AND p.published = 1
) AS post_count
FROM users u
WHERE u.active = 1;
資料修改查詢
INSERT 陳述式
輸入：
insert into users (name, email, active, created_at) values ('John Doe', 'john@example.com', 1, now()), ('Jane Smith', 'jane@example.com', 1, now());
格式化輸出：
INSERT INTO users (
name,
email,
active,
created_at
)
VALUES (
'John Doe',
'john@example.com',
1,
NOW()
), (
'Jane Smith',
'jane@example.com',
1,
NOW()
);
UPDATE 陳述式
輸入：
update users set name = 'John Updated', email = 'john.updated@example.com', updated_at = now() where id = 1 and active = 1;
格式化輸出：
UPDATE users
SET
name = 'John Updated',
email = 'john.updated@example.com',
updated_at = NOW()
WHERE id = 1
AND active = 1;
DELETE 陳述式
輸入：
delete from posts where user_id in (select id from users where active = 0) and published = 0;
格式化輸出：
DELETE FROM posts
WHERE user_id IN (
SELECT id
FROM users
WHERE active = 0
)
AND published = 0;
資料定義查詢
CREATE TABLE
輸入：
create table users (id int primary key auto_increment, name varchar(255) not null, email varchar(255) unique not null, active boolean default true, created_at timestamp default current_timestamp, updated_at timestamp default current_timestamp on update current_timestamp);
格式化輸出：
CREATE TABLE users (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
active BOOLEAN DEFAULT TRUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
ALTER TABLE
輸入：
alter table users add column phone varchar(20), modify column email varchar(320), add index idx_email (email), add constraint fk_user_profile foreign key (id) references user_profiles (user_id);
格式化輸出：
ALTER TABLE users
ADD COLUMN phone VARCHAR(20),
MODIFY COLUMN email VARCHAR(320),
ADD INDEX idx_email (email),
ADD CONSTRAINT fk_user_profile FOREIGN KEY (id) REFERENCES user_profiles (user_id);
視窗函數
ROW_NUMBER()
輸入：
select id, name, email, row_number() over (partition by department_id order by created_at desc) as row_num from users where active = 1;
格式化輸出：
SELECT
id,
name,
email,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY created_at DESC
) AS row_num
FROM users
WHERE active = 1;
通用資料表運算式（CTE）
WITH 子句
輸入：
with active_users as (select id, name, email from users where active = 1), user_posts as (select user_id, count(*) as post_count from posts where published = 1 group by user_id) select au.id, au.name, au.email, coalesce(up.post_count, 0) as post_count from active_users au left join user_posts up on au.id = up.user_id order by post_count desc;
格式化輸出：
WITH active_users AS (
SELECT
id,
name,
email
FROM users
WHERE active = 1
),
user_posts AS (
SELECT
user_id,
COUNT(*) AS post_count
FROM posts
WHERE published = 1
GROUP BY user_id
)
SELECT
au.id,
au.name,
au.email,
COALESCE(up.post_count, 0) AS post_count
FROM active_users au
LEFT JOIN user_posts up ON au.id = up.user_id
ORDER BY post_count DESC;
格式化選項範例
不同縮排大小
2 個空格：
SELECT
id,
name,
email
FROM users
WHERE active = 1;
4 個空格：
SELECT
id,
name,
email
FROM users
WHERE active = 1;
8 個空格：
SELECT
id,
name,
email
FROM users
WHERE active = 1;
不同關鍵字大小寫
大寫：
SELECT
id,
name,
email
FROM users
WHERE active = 1;
小寫：
select
id,
name,
email
from users
where active = 1;
保留原樣：
Select
id,
name,
email
From users
Where active = 1;
這些範例展示了 SQL 格式化工具的靈活性，以及它如何依照不同的格式偏好處理各種類型的 SQL 查詢。