Ejemplos
Esta pagina muestra ejemplos practicos de uso de SQL Prettify con consultas SQL habituales. Los bloques SQL se mantienen sin cambios para que puedas comparar de forma directa la entrada y la salida formateada.
Consultas SELECT basicas
SELECT simple
Entrada:
select id, name, email from users where active = 1;
Salida formateada:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
SELECT con ORDER BY
Entrada:
select u.id, u.name, u.email from users u where u.active = 1 order by u.name asc, u.created_at desc;
Salida formateada:
SELECT
u.id,
u.name,
u.email
FROM users u
WHERE u.active = 1
ORDER BY
u.name ASC,
u.created_at DESC;
Consultas JOIN
INNER JOIN
Entrada:
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;
Salida formateada:
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;
Consultas de agregacion
GROUP BY con agregacion
Entrada:
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;
Salida formateada:
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;
Subconsultas
Subconsulta en WHERE
Entrada:
select id, name, email from users where id in (select user_id from posts where published = 1) and active = 1;
Salida formateada:
SELECT
id,
name,
email
FROM users
WHERE id IN (
SELECT user_id
FROM posts
WHERE published = 1
)
AND active = 1;
Subconsulta correlacionada
Entrada:
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;
Salida formateada:
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;
Modificacion de datos
Sentencia INSERT
Entrada:
insert into users (name, email, active, created_at) values ('John Doe', 'john@example.com', 1, now()), ('Jane Smith', 'jane@example.com', 1, now());
Salida formateada:
INSERT INTO users (
name,
email,
active,
created_at
)
VALUES (
'John Doe',
'john@example.com',
1,
NOW()
), (
'Jane Smith',
'jane@example.com',
1,
NOW()
);
Sentencia UPDATE
Entrada:
update users set name = 'John Updated', email = 'john.updated@example.com', updated_at = now() where id = 1 and active = 1;
Salida formateada:
UPDATE users
SET
name = 'John Updated',
email = 'john.updated@example.com',
updated_at = NOW()
WHERE id = 1
AND active = 1;
Sentencia DELETE
Entrada:
delete from posts where user_id in (select id from users where active = 0) and published = 0;
Salida formateada:
DELETE FROM posts
WHERE user_id IN (
SELECT id
FROM users
WHERE active = 0
)
AND published = 0;
Definicion de datos
CREATE TABLE
Entrada:
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);
Salida formateada:
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
);
Funciones de ventana
ROW_NUMBER()
Entrada:
select id, name, email, row_number() over (partition by department_id order by created_at desc) as row_num from users where active = 1;
Salida formateada:
SELECT
id,
name,
email,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY created_at DESC
) AS row_num
FROM users
WHERE active = 1;
Common Table Expressions (CTE)
Clausula WITH
Entrada:
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;
Salida formateada:
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;
Opciones de formato
Diferentes sangrias
2 espacios:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
4 espacios:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
Diferentes estilos de mayusculas y minusculas
MAYUSCULAS:
SELECT
id,
name,
email
FROM users
WHERE active = 1;
minusculas:
select
id,
name,
email
from users
where active = 1;
Estos ejemplos muestran como SQL Prettify mejora la legibilidad y mantiene un estilo coherente para consultas usadas a diario.