Exemples
Cette page montre des exemples pratiques d'utilisation de SQL Prettify sur des requetes SQL frequentes. Les blocs SQL restent inchanges afin de comparer directement l'entree et la sortie formatee.
Requetes SELECT de base
SELECT simple
Entree :
select id, name, email from users where active = 1;
Sortie formatee :
SELECT
id,
name,
email
FROM users
WHERE active = 1;
SELECT avec ORDER BY
Entree :
select u.id, u.name, u.email from users u where u.active = 1 order by u.name asc, u.created_at desc;
Sortie formatee :
SELECT
u.id,
u.name,
u.email
FROM users u
WHERE u.active = 1
ORDER BY
u.name ASC,
u.created_at DESC;
Requetes JOIN
INNER JOIN
Entree :
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;
Sortie formatee :
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;
Requetes d'agregation
GROUP BY avec agregation
Entree :
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;
Sortie formatee :
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;
Sous-requetes
Sous-requete dans WHERE
Entree :
select id, name, email from users where id in (select user_id from posts where published = 1) and active = 1;
Sortie formatee :
SELECT
id,
name,
email
FROM users
WHERE id IN (
SELECT user_id
FROM posts
WHERE published = 1
)
AND active = 1;
Sous-requete correlee
Entree :
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;
Sortie formatee :
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;
Modification des donnees
Instruction INSERT
Entree :
insert into users (name, email, active, created_at) values ('John Doe', 'john@example.com', 1, now()), ('Jane Smith', 'jane@example.com', 1, now());
Sortie formatee :
INSERT INTO users (
name,
email,
active,
created_at
)
VALUES (
'John Doe',
'john@example.com',
1,
NOW()
), (
'Jane Smith',
'jane@example.com',
1,
NOW()
);
Instruction UPDATE
Entree :
update users set name = 'John Updated', email = 'john.updated@example.com', updated_at = now() where id = 1 and active = 1;
Sortie formatee :
UPDATE users
SET
name = 'John Updated',
email = 'john.updated@example.com',
updated_at = NOW()
WHERE id = 1
AND active = 1;
Instruction DELETE
Entree :
delete from posts where user_id in (select id from users where active = 0) and published = 0;
Sortie formatee :
DELETE FROM posts
WHERE user_id IN (
SELECT id
FROM users
WHERE active = 0
)
AND published = 0;
Definition des donnees
CREATE TABLE
Entree :
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);
Sortie formatee :
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
);
Fonctions fenetre
ROW_NUMBER()
Entree :
select id, name, email, row_number() over (partition by department_id order by created_at desc) as row_num from users where active = 1;
Sortie formatee :
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)
Clause WITH
Entree :
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;
Sortie formatee :
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;
Options de formatage
Indentation differente
2 espaces :
SELECT
id,
name,
email
FROM users
WHERE active = 1;
4 espaces :
SELECT
id,
name,
email
FROM users
WHERE active = 1;
Casse des mots-cles
MAJUSCULES :
SELECT
id,
name,
email
FROM users
WHERE active = 1;
minuscules :
select
id,
name,
email
from users
where active = 1;
Ces exemples montrent comment SQL Prettify rend les requetes plus lisibles et plus coherentes pour un usage quotidien en equipe.