예제

이 섹션에서는 다양한 유형의 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 쿼리를 어떻게 처리할 수 있는지를 보여 줍니다.

이 페이지가 도움이 되었나요?

On this page

기본 SELECT 쿼리 복잡한 JOIN 쿼리 집계 쿼리 서브쿼리 데이터 수정 쿼리 데이터 정의 쿼리 윈도우 함수 공통 테이블 식(CTE)포맷 옵션 예제