Jhonatan Pinheiro

Cargando página...

Jhonatan Pinheiro

Cargando página...

10 de agosto de 202636 min de lectura

SQL Intermedio: 200 Comandos y Técnicas

JOINs, subconsultas, CTEs, vistas, índices, transacciones, JSON y programación en el motor: 200 ítems con ejemplos prácticos.

sql
banco-de-dados
intermediario
referencia

SQL Intermediário: 200 Comandos e Técnicas

Você já domina SELECT, WHERE e GROUP BY. Estes 200 itens são o que separa quem consulta dados de quem modela e mantém um banco: relacionamentos, índices, transações, views, JSON e código rodando dentro do próprio banco.

A referência continua sendo o PostgreSQL, com as diferenças de MySQL, SQL Server, Oracle e Firebird anotadas nos exemplos.

Se um item parecer avançado demais agora, pule e volte depois. O importante é saber que existe — na hora que o problema aparecer, você lembra onde procurar.

Índice

1–20 · JOINs: combinando tabelas
21–35 · Subconsultas
36–45 · CTEs: organizando consultas
46–53 · Operações de conjunto
54–63 · Views
64–81 · Índices
82–97 · Transações e concorrência
98–109 · Integridade e restrições
110–124 · Funções de janela: o essencial
125–138 · JSON dentro do banco
139–158 · Programando dentro do banco
159–170 · Modelagem e normalização
171–180 · Carga e movimentação de dados
181–190 · Usuários e permissões
191–200 · Metadados e manutenção

JOINs: combinando tabelas

Dados normalizados vivem separados. JOIN é como você os junta de volta.

1. INNER JOIN

Traz só as linhas que existem nos dois lados. É o JOIN mais usado.

SELECT ped.id,
       cli.nome,
       ped.total
FROM pedidos ped
INNER JOIN clientes cli
ON cli.id = ped.cliente_id;

2. LEFT JOIN

Traz todas as linhas da esquerda; onde não há par, as colunas da direita vêm NULL.

SELECT cli.nome,
       ped.id AS pedido
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id;

3. RIGHT JOIN

Espelho do LEFT. Na prática, inverta as tabelas e use LEFT — fica mais fácil de ler.

SELECT cli.nome,
       ped.id
FROM pedidos ped
RIGHT JOIN clientes cli
ON cli.id = ped.cliente_id;

4. FULL OUTER JOIN

Traz tudo dos dois lados. MySQL não tem: simule com LEFT UNION RIGHT.

SELECT cli.nome,
       ped.id
FROM clientes cli
FULL OUTER JOIN pedidos ped
ON ped.cliente_id = cli.id;

5. CROSS JOIN

Produto cartesiano: cada linha de A com cada linha de B. Útil para gerar combinações, perigoso por acidente.

SELECT tim.nome,
       mes.mes
FROM times tim
CROSS JOIN meses mes;

6. SELF JOIN

A tabela com ela mesma. Base de hierarquias como funcionário e gerente.

SELECT fun.nome AS funcionario,
       ger.nome AS gerente
FROM funcionarios fun
LEFT JOIN funcionarios ger
ON ger.id = fun.gerente_id;

7. JOIN com várias condições

O ON aceita qualquer expressão, não só igualdade de chave.

SELECT *
FROM precos pre
INNER JOIN vigencias vig
ON vig.produto_id = pre.produto_id
AND pre.data BETWEEN vig.inicio AND vig.fim;

8. JOIN de três ou mais tabelas

Encadeie os JOINs na ordem que faz sentido para o relacionamento.

SELECT cli.nome,
       ped.id,
       ite.produto,
       ite.qtd
FROM clientes cli
INNER JOIN pedidos ped
ON ped.cliente_id = cli.id
INNER JOIN itens ite
ON ite.pedido_id = ped.id;

9. USING — atalho do ON

Quando a coluna tem o mesmo nome nas duas tabelas, USING encurta e remove a duplicada do resultado.

SELECT *
FROM pedidos
INNER JOIN clientes USING (cliente_id);

10. NATURAL JOIN

Junta automaticamente por todas as colunas de mesmo nome. Evite: qualquer coluna nova muda o resultado silenciosamente.

SELECT *
FROM pedidos
NATURAL JOIN clientes;  -- prefira ON explícito

11. Anti-join com LEFT + IS NULL

Acha o que não tem par — clientes sem nenhum pedido.

SELECT cli.*
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
WHERE ped.id IS NULL;

12. Anti-join com NOT EXISTS

Mesma pergunta, geralmente com plano melhor e imune a NULL.

SELECT cli.*
FROM clientes cli
WHERE NOT EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

13. Semi-join com EXISTS

Quem tem pelo menos um relacionado, sem duplicar as linhas como um JOIN faria.

SELECT cli.*
FROM clientes cli
WHERE EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

14. Filtro no ON × no WHERE

Em LEFT JOIN faz diferença: no ON, filtra o lado direito; no WHERE, vira INNER JOIN sem querer.

-- Mantém todos os clientes
SELECT cli.nome,
       ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id AND ped.status = 'pago';

-- Vira INNER JOIN (descarta clientes sem pedido pago)
SELECT cli.nome, ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
WHERE ped.status = 'pago';

15. JOIN com agregação

Agregue antes de juntar para não multiplicar linhas e inflar somas.

SELECT cli.nome,
       ped_tot.total
FROM clientes cli
INNER JOIN (SELECT cliente_id, SUM(total) AS total
            FROM pedidos
            GROUP BY cliente_id) ped_tot
ON ped_tot.cliente_id = cli.id;

16. LATERAL JOIN

A subconsulta da direita enxerga as colunas da esquerda. Ideal para 'os 3 últimos de cada'.

SELECT cli.nome,
       ped_rec.*
FROM clientes cli
CROSS JOIN LATERAL (
  SELECT *
  FROM pedidos ped
  WHERE ped.cliente_id = cli.id
  ORDER BY ped.criado_em DESC
  LIMIT 3
) ped_rec;

17. APPLY — SQL Server

Equivalente do LATERAL no SQL Server: CROSS APPLY e OUTER APPLY.

SELECT cli.nome,
       ped_rec.*
FROM clientes cli
CROSS APPLY (SELECT TOP 3 *
             FROM pedidos ped
             WHERE ped.cliente_id = cli.id
             ORDER BY ped.criado_em DESC) ped_rec;

18. JOIN em tabela de ligação

Relacionamento muitos-para-muitos passa por uma terceira tabela.

SELECT art.titulo,
       tag.nome AS tag
FROM artigos art
INNER JOIN artigo_tag art_tag
ON art_tag.artigo_id = art.id
INNER JOIN tags tag
ON tag.id = art_tag.tag_id;

19. Cuidado com a duplicação

Um JOIN um-para-muitos multiplica as linhas do lado 'um'. Somar depois disso infla o resultado.

-- Errado: total repetido por item
SELECT SUM(ped.total)
FROM pedidos ped
INNER JOIN itens ite
ON ite.pedido_id = ped.id;

-- Certo
SELECT SUM(total)
FROM pedidos;

20. JOIN com intervalo de datas

Casa cada evento com a faixa vigente. Muito usado com tabelas de preço e câmbio.

SELECT ven.id,
       cot.taxa
FROM vendas ven
INNER JOIN cotacoes cot
ON ven.data >= cot.inicio AND ven.data < cot.fim;

Subconsultas

Uma consulta dentro da outra: filtra, calcula e alimenta a consulta principal.

21. Subconsulta escalar

Retorna um único valor e pode ser usada como se fosse uma coluna.

SELECT nome, (SELECT COUNT(*) FROM pedidos ped WHERE ped.cliente_id = cli.id) AS pedidos
FROM clientes cli;

22. Subconsulta no WHERE

Filtra pela lista devolvida pela subconsulta.

SELECT * FROM produtos
WHERE categoria_id IN (SELECT id FROM categorias WHERE ativa);

23. Subconsulta no FROM

A subconsulta vira uma tabela temporária (derivada). Precisa de apelido.

SELECT cidade, media
FROM (SELECT cidade, AVG(total) AS media
      FROM pedidos
      GROUP BY cidade) AS med_cidade
WHERE media > 500;

24. Subconsulta correlacionada

Referencia a linha da consulta externa; roda uma vez por linha. Poderosa, mas cara.

SELECT cli.nome
FROM clientes cli
WHERE (SELECT COUNT(*) FROM pedidos ped WHERE ped.cliente_id = cli.id) > 5;

25. EXISTS × IN

Com muitas linhas, EXISTS costuma vencer; com listas pequenas e fixas, IN é mais simples.

SELECT * FROM clientes cli WHERE EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

26. NOT IN com NULL

Se a subconsulta devolver um NULL, NOT IN não retorna nada. Use NOT EXISTS.

-- Perigoso
SELECT * FROM clientes WHERE id NOT IN (SELECT cliente_id FROM pedidos);

-- Seguro
SELECT * FROM clientes cli WHERE NOT EXISTS (SELECT 1 FROM pedidos ped WHERE ped.cliente_id = cli.id);

27. ANY / SOME

Compara com qualquer valor da lista devolvida.

SELECT * FROM produtos WHERE preco > ANY (SELECT preco FROM produtos WHERE categoria = 'games');

28. ALL

A condição precisa valer para todos os valores da lista.

SELECT * FROM produtos WHERE preco >= ALL (SELECT preco FROM produtos WHERE categoria = 'games');

29. Subconsulta no SELECT com JOIN

Às vezes um JOIN agregado substitui a subconsulta correlacionada com ganho grande de performance.

SELECT cli.nome,
       COALESCE(ped_qtd.qtd, 0) AS pedidos
FROM clientes cli
LEFT JOIN (SELECT cliente_id, COUNT(*) AS qtd
           FROM pedidos
           GROUP BY cliente_id) ped_qtd
ON ped_qtd.cliente_id = cli.id;

30. Subconsulta com LIMIT

Pega um valor específico, como o último pedido do cliente.

SELECT cli.nome,
  (SELECT ped.total FROM pedidos ped WHERE ped.cliente_id = cli.id ORDER BY ped.criado_em DESC LIMIT 1) AS ultimo
FROM clientes cli;

31. Subconsulta em UPDATE

Calcula o novo valor a partir de outra tabela.

UPDATE produtos
SET estoque = (SELECT SUM(qtd) FROM movimentos mov WHERE mov.produto_id = produtos.id);

32. Subconsulta em DELETE

Apaga com base em um critério vindo de outra tabela.

DELETE FROM carrinho
WHERE produto_id IN (SELECT id FROM produtos WHERE descontinuado);

33. Subconsulta no HAVING

Compara a agregação do grupo com um valor calculado.

SELECT cidade, AVG(total) AS media
FROM pedidos GROUP BY cidade
HAVING AVG(total) > (SELECT AVG(total) FROM pedidos);

34. Subconsulta de linha

Compara várias colunas de uma vez.

SELECT * FROM pedidos
WHERE (cliente_id, criado_em) IN (SELECT cliente_id, MAX(criado_em) FROM pedidos GROUP BY cliente_id);

35. Quando trocar por CTE

Subconsulta repetida na mesma query? Extraia para CTE: lê melhor e é avaliada uma vez só.

WITH media AS (SELECT AVG(total) AS valor FROM pedidos)
SELECT * FROM pedidos, media WHERE total > media.valor;

CTEs: organizando consultas

WITH transforma uma consulta ilegível em passos nomeados.

36. WITH — CTE simples

Nomeia um resultado intermediário e usa logo abaixo.

WITH ativos AS (
  SELECT * FROM clientes WHERE ativo
)
SELECT COUNT(*) FROM ativos;

37. Várias CTEs

Separe por vírgula e monte a consulta em etapas.

WITH pagos AS (
  SELECT * FROM pedidos WHERE status = 'pago'
), por_cliente AS (
  SELECT cliente_id, SUM(total) AS total FROM pagos GROUP BY cliente_id
)
SELECT * FROM por_cliente ORDER BY total DESC LIMIT 10;

38. CTE referenciando outra

Cada CTE enxerga as anteriores — é um pipeline de transformação.

WITH base AS (SELECT * FROM vendas WHERE ano = 2026),
     resumo AS (SELECT regiao, SUM(valor) AS total FROM base GROUP BY regiao)
SELECT * FROM resumo WHERE total > 100000;

39. CTE em UPDATE e DELETE

Prepara o conjunto alvo antes de alterar.

WITH antigos AS (
  SELECT id FROM sessoes WHERE criado_em < CURRENT_DATE - INTERVAL '90 days'
)
DELETE FROM sessoes WHERE id IN (SELECT id FROM antigos);

40. CTE recursiva

A CTE chama a si mesma: percorre hierarquias e gera séries.

WITH RECURSIVE hierarquia AS (
  SELECT id, nome, gerente_id, 1 AS nivel FROM funcionarios WHERE gerente_id IS NULL
  UNION ALL
  SELECT fun.id, fun.nome, fun.gerente_id, hie.nivel + 1
  FROM funcionarios fun
  INNER JOIN hierarquia hie
  ON fun.gerente_id = hie.id
)
SELECT *
FROM hierarquia
ORDER BY nivel;

41. Série de datas com CTE recursiva

Cria um calendário para preencher dias sem venda no relatório.

WITH RECURSIVE dias AS (
  SELECT DATE '2026-01-01' AS dia
  UNION ALL
  SELECT dia + 1 FROM dias WHERE dia < DATE '2026-01-31'
)
SELECT * FROM dias;

42. Cuidado com recursão infinita

Sempre garanta uma condição de parada; muitos bancos deixam limitar a profundidade.

WITH RECURSIVE r AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM r WHERE n < 100   -- parada obrigatória
)
SELECT COUNT(*) FROM r;

43. CTE materializada

No PostgreSQL você controla se a CTE vira tabela temporária ou é fundida na consulta.

WITH pesada AS MATERIALIZED (
  SELECT * FROM eventos WHERE tipo = 'compra'
)
SELECT COUNT(*) FROM pesada;

44. CTE × subconsulta

CTE ganha em legibilidade e reuso; subconsulta às vezes ganha em plano. Meça os dois.

-- Mesma resposta, formas diferentes de escrever
WITH ped_tot AS (
  SELECT cliente_id, SUM(total) AS total
  FROM pedidos
  GROUP BY cliente_id
)
SELECT *
FROM ped_tot
WHERE total > 1000;

45. CTE com dados literais

VALUES dentro de uma CTE cria uma tabela de apoio sem precisar criar tabela.

WITH faixas(nome, minimo, maximo) AS (
  VALUES ('barato', 0, 50), ('medio', 50, 200), ('caro', 200, 999999)
)
SELECT prod.nome,
       fai.nome AS faixa
FROM produtos prod
INNER JOIN faixas fai
ON prod.preco >= fai.minimo AND prod.preco < fai.maximo;

Operações de conjunto

Empilhar e comparar resultados de consultas diferentes.

46. UNION

Empilha dois resultados e remove duplicados (custa uma ordenação).

SELECT email FROM clientes
UNION
SELECT email FROM leads;

47. UNION ALL

Empilha sem remover duplicados. Bem mais rápido — use quando não houver repetição possível.

SELECT id, 'pedido' AS origem FROM pedidos
UNION ALL
SELECT id, 'orcamento' FROM orcamentos;

48. INTERSECT

Só o que aparece nos dois resultados.

SELECT email FROM clientes
INTERSECT
SELECT email FROM newsletter;

49. EXCEPT / MINUS

O que está no primeiro e não está no segundo. No Oracle chama-se MINUS.

SELECT email FROM leads
EXCEPT
SELECT email FROM clientes;

50. Regras de compatibilidade

As consultas precisam ter o mesmo número de colunas e tipos compatíveis, na mesma ordem.

SELECT nome, email FROM clientes
UNION ALL
SELECT razao_social, contato FROM empresas;

51. ORDER BY em união

Vale para o resultado inteiro e vem no fim, uma vez só.

SELECT nome FROM clientes
UNION ALL
SELECT nome FROM fornecedores
ORDER BY nome;

52. Simular FULL JOIN no MySQL

MySQL não tem FULL OUTER JOIN: use LEFT UNION RIGHT.

SELECT cli.nome,
       ped.id
FROM clientes cli
LEFT JOIN pedidos ped
ON ped.cliente_id = cli.id
UNION
SELECT cli.nome, ped.id
FROM clientes cli
RIGHT JOIN pedidos ped
ON ped.cliente_id = cli.id;

53. Comparar duas tabelas

EXCEPT nos dois sentidos mostra exatamente o que difere entre ambientes.

(SELECT * FROM producao.clientes EXCEPT SELECT * FROM homolog.clientes)
UNION ALL
(SELECT * FROM homolog.clientes EXCEPT SELECT * FROM producao.clientes);

Views

Consulta salva com nome: esconde complexidade e padroniza regra de negócio.

54. CREATE VIEW

Salva uma consulta como se fosse uma tabela virtual.

CREATE VIEW vw_pedidos_pagos AS
SELECT ped.*, cli.nome AS cliente
FROM pedidos ped JOIN clientes cli ON cli.id = ped.cliente_id
WHERE ped.status = 'pago';

55. Consultar uma view

Use como qualquer tabela — inclusive em JOIN.

SELECT * FROM vw_pedidos_pagos WHERE total > 500;

56. CREATE OR REPLACE VIEW

Atualiza a definição sem precisar dropar (as colunas precisam ser compatíveis).

CREATE OR REPLACE VIEW vw_pedidos_pagos AS
SELECT ped.*, cli.nome AS cliente, cli.cidade
FROM pedidos ped JOIN clientes cli ON cli.id = ped.cliente_id
WHERE ped.status = 'pago';

57. DROP VIEW

Remove a view. Os dados continuam intactos: view não guarda nada.

DROP VIEW IF EXISTS vw_pedidos_pagos;

58. View para simplificar relatório

Encapsula o JOIN e a regra, e o time de negócio consulta sem saber do modelo.

CREATE VIEW vw_faturamento_mensal AS
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) AS faturamento
FROM pedidos WHERE status = 'pago'
GROUP BY 1;

59. View como camada de segurança

Exponha só as colunas permitidas e dê permissão na view, não na tabela.

CREATE VIEW vw_clientes_publico AS
SELECT id, nome, cidade FROM clientes;   -- sem e-mail e telefone

GRANT SELECT ON vw_clientes_publico TO app_leitura;

60. View atualizável

Views simples (uma tabela, sem agregação) aceitam INSERT/UPDATE direto.

CREATE VIEW vw_ativos AS SELECT * FROM clientes WHERE ativo;
UPDATE vw_ativos SET cidade = 'Campinas' WHERE id = 1;

61. WITH CHECK OPTION

Impede gravar pela view uma linha que sairia do filtro dela.

CREATE VIEW vw_ativos AS
SELECT * FROM clientes WHERE ativo
WITH CHECK OPTION;

62. View materializada

Guarda o resultado em disco: leitura rápida, dado com atraso. Precisa de refresh.

CREATE MATERIALIZED VIEW mv_faturamento AS
SELECT DATE_TRUNC('month', criado_em) AS mes, SUM(total) AS total
FROM pedidos GROUP BY 1;

63. REFRESH MATERIALIZED VIEW

Recalcula. CONCURRENTLY evita travar leituras (exige índice único).

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_faturamento;

Índices

O que transforma uma consulta de 30 segundos em 3 milissegundos — e o INSERT em algo mais lento.

64. CREATE INDEX

Cria um índice na coluna mais usada em filtros e JOINs.

CREATE INDEX idx_pedidos_cliente ON pedidos (cliente_id);

65. Índice composto

Cobre filtros por várias colunas. A ordem importa: comece pela mais seletiva/mais filtrada.

CREATE INDEX idx_pedidos_cliente_data ON pedidos (cliente_id, criado_em);

66. Regra do prefixo à esquerda

Um índice (a, b) atende filtros por a e por a + b, mas não por b sozinho.

-- Usa o índice
SELECT * FROM pedidos WHERE cliente_id = 1;
-- Não usa
SELECT * FROM pedidos WHERE criado_em > CURRENT_DATE;

67. Índice único

Garante unicidade e ainda serve como índice de busca.

CREATE UNIQUE INDEX uq_clientes_email ON clientes (email);

68. Índice parcial

Indexa só as linhas que interessam: menor, mais rápido e mais barato de manter (PostgreSQL).

CREATE INDEX idx_pedidos_abertos ON pedidos (criado_em) WHERE status = 'aberto';

69. Índice por expressão

Indexa o resultado de uma função — necessário quando o filtro usa a função.

CREATE INDEX idx_clientes_email_lower ON clientes (LOWER(email));
SELECT * FROM clientes WHERE LOWER(email) = 'ana@email.com';

70. Índice descendente

Alinha o índice à ordenação mais usada, evitando um sort a cada consulta.

CREATE INDEX idx_pedidos_recentes ON pedidos (criado_em DESC);

71. Índice coberto (covering)

Se o índice contém todas as colunas da consulta, o banco nem lê a tabela.

CREATE INDEX idx_cobertura ON pedidos (cliente_id) INCLUDE (total, status);

72. DROP INDEX

Índice que ninguém usa só custa espaço e escrita. Remova.

DROP INDEX IF EXISTS idx_pedidos_cliente;

73. Criar índice sem travar

CONCURRENTLY (PostgreSQL) e ONLINE (SQL Server/Oracle) criam sem bloquear escrita.

CREATE INDEX CONCURRENTLY idx_pedidos_status ON pedidos (status);

74. Ver os índices de uma tabela

Cada banco expõe isso em um catálogo diferente.

-- PostgreSQL
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'pedidos';

-- MySQL
SHOW INDEX FROM pedidos;

75. Índices e escrita

Todo índice precisa ser atualizado a cada INSERT/UPDATE/DELETE. Índice demais deixa a escrita lenta.

-- Regra prática: indexe o que você filtra/ordena de verdade,
-- e revise os índices sem uso periodicamente.

76. Índice não usado por conversão

Comparar coluna indexada com tipo diferente descarta o índice.

-- Não usa o índice (id é inteiro)
SELECT * FROM pedidos WHERE id::text = '42';

-- Usa
SELECT * FROM pedidos WHERE id = 42;

77. Índice para busca por texto

LIKE '%algo%' não usa índice B-tree. Prefixo ('algo%') usa; para o resto, full-text ou trigramas.

CREATE INDEX idx_produtos_nome ON produtos (nome varchar_pattern_ops);
SELECT * FROM produtos WHERE nome LIKE 'Note%';

78. Índice GIN para JSON e texto

Estrutura própria para conteúdo composto: JSONB, arrays e busca textual (PostgreSQL).

CREATE INDEX idx_config_dados ON configuracoes USING GIN (dados);

79. Índice em chave estrangeira

Bancos criam índice na PK, mas nem sempre na FK. Sem ele, o JOIN e o DELETE do pai ficam lentos.

CREATE INDEX idx_itens_pedido ON itens (pedido_id);

80. Índices duplicados

Um índice (a) é redundante se já existe (a, b). Remova o menor.

-- (cliente_id) é coberto por (cliente_id, criado_em)
DROP INDEX idx_pedidos_cliente;

81. REINDEX

Reconstrói índices inchados por muita atualização.

REINDEX TABLE pedidos;

Transações e concorrência

Garantir que operações aconteçam por inteiro — ou não aconteçam.

82. BEGIN — abrir transação

A partir daqui, nada é definitivo até o COMMIT.

BEGIN;
UPDATE contas SET saldo = saldo - 100 WHERE id = 1;
UPDATE contas SET saldo = saldo + 100 WHERE id = 2;
COMMIT;

83. COMMIT

Confirma tudo que a transação fez.

COMMIT;

84. ROLLBACK

Desfaz tudo desde o BEGIN. Sua rede de segurança.

BEGIN;
DELETE FROM clientes;   -- ops
ROLLBACK;

85. SAVEPOINT

Ponto intermediário: dá para desfazer só um trecho da transação.

BEGIN;
INSERT INTO log VALUES ('inicio');
SAVEPOINT p1;
DELETE FROM temporarios;
ROLLBACK TO p1;    -- desfaz só o DELETE
COMMIT;

86. ACID

Atomicidade, Consistência, Isolamento e Durabilidade — as garantias que um banco relacional dá.

-- Atomicidade: tudo ou nada
-- Consistência: restrições sempre válidas
-- Isolamento: transações não se atrapalham
-- Durabilidade: commitou, está gravado

87. READ COMMITTED

Nível padrão na maioria: você só enxerga o que já foi commitado.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

88. REPEATABLE READ

A mesma consulta devolve o mesmo resultado durante toda a transação.

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;

89. SERIALIZABLE

O nível mais estrito: resultado equivalente a rodar as transações em fila. Mais seguro, mais conflito.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

90. Leitura suja

Ler dado não commitado. Só acontece em READ UNCOMMITTED — evite.

-- SQL Server: NOLOCK faz leitura suja. Use com muita consciência.
SELECT * FROM pedidos WITH (NOLOCK);

91. SELECT FOR UPDATE

Trava as linhas lidas até o fim da transação, evitando que outra sessão altere no meio.

BEGIN;
SELECT * FROM estoque WHERE produto_id = 1 FOR UPDATE;
UPDATE estoque SET qtd = qtd - 1 WHERE produto_id = 1;
COMMIT;

92. SKIP LOCKED

Pula linhas travadas em vez de esperar. Base de fila de trabalho com vários consumidores.

SELECT * FROM fila WHERE status = 'pendente'
ORDER BY id LIMIT 1 FOR UPDATE SKIP LOCKED;

93. NOWAIT

Falha na hora em vez de ficar esperando o lock.

SELECT * FROM contas WHERE id = 1 FOR UPDATE NOWAIT;

94. Deadlock

Duas transações esperando uma pela outra. O banco mata uma delas. Previna acessando as tabelas sempre na mesma ordem.

-- Sessão A: contas -> pedidos
-- Sessão B: pedidos -> contas   <- risco de deadlock
-- Padronize a ordem de acesso

95. Atualização perdida

Ler, calcular na aplicação e gravar sobrescreve o trabalho de outro. Atualize no próprio SQL.

-- Frágil
SELECT saldo FROM contas WHERE id = 1;   -- app soma
UPDATE contas SET saldo = 150 WHERE id = 1;

-- Seguro
UPDATE contas SET saldo = saldo + 50 WHERE id = 1;

96. Bloqueio otimista

Uma coluna de versão detecta alteração concorrente sem manter lock.

UPDATE produtos SET preco = 99, versao = versao + 1
WHERE id = 1 AND versao = 7;   -- 0 linhas = alguém alterou antes

97. Transação curta

Transação aberta segura locks e trava outros. Abra tarde, feche cedo, nunca espere I/O externo dentro dela.

-- Evite: BEGIN; ... chamada HTTP ... COMMIT;
-- Faça a chamada externa fora da transação.

Integridade e restrições

Regras que o banco garante sozinho — valem para qualquer aplicação que escrever nele.

98. Nomear as constraints

Nome explícito faz o erro do banco dizer exatamente qual regra foi violada.

ALTER TABLE pedidos
ADD CONSTRAINT ck_pedidos_total_positivo CHECK (total >= 0);

99. Chave estrangeira com ação

Defina o que acontece com os filhos quando o pai muda ou some.

ALTER TABLE itens ADD CONSTRAINT fk_itens_pedido
FOREIGN KEY (pedido_id) REFERENCES pedidos (id)
ON DELETE CASCADE ON UPDATE CASCADE;

100. ON DELETE SET NULL

Mantém o filho, mas limpa a referência.

ALTER TABLE funcionarios ADD CONSTRAINT fk_gerente
FOREIGN KEY (gerente_id) REFERENCES funcionarios (id) ON DELETE SET NULL;

101. ON DELETE RESTRICT

Impede apagar o pai enquanto houver filhos. Costuma ser o padrão mais seguro.

ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_cliente
FOREIGN KEY (cliente_id) REFERENCES clientes (id) ON DELETE RESTRICT;

102. CHECK com várias colunas

Valida a coerência entre campos da mesma linha.

ALTER TABLE reservas
ADD CONSTRAINT ck_periodo CHECK (fim > inicio);

103. CHECK com lista de valores

Alternativa simples ao tipo ENUM.

ALTER TABLE pedidos
ADD CONSTRAINT ck_status CHECK (status IN ('pendente','pago','cancelado'));

104. UNIQUE composta

A combinação precisa ser única, não cada coluna isolada.

ALTER TABLE inscricoes
ADD CONSTRAINT uk_aluno_turma UNIQUE (aluno_id, turma_id);

105. UNIQUE parcial

Unicidade só para um subconjunto — um registro ativo por cliente, por exemplo.

CREATE UNIQUE INDEX uq_assinatura_ativa ON assinaturas (cliente_id) WHERE ativa;

106. Desabilitar e reabilitar

Em cargas grandes, desabilitar temporariamente acelera — mas revalide depois.

ALTER TABLE pedidos DROP CONSTRAINT fk_pedidos_cliente;
-- carga
ALTER TABLE pedidos ADD CONSTRAINT fk_pedidos_cliente
FOREIGN KEY (cliente_id) REFERENCES clientes (id);

107. Constraint adiada

DEFERRABLE adia a checagem para o COMMIT — resolve inserções circulares.

ALTER TABLE a ADD CONSTRAINT fk_a_b FOREIGN KEY (b_id) REFERENCES b (id)
DEFERRABLE INITIALLY DEFERRED;

108. Achar órfãos

Antes de criar a FK, encontre as linhas que quebrariam a regra.

SELECT ped.*
FROM pedidos ped
LEFT JOIN clientes cli
ON cli.id = ped.cliente_id
WHERE cli.id IS NULL;

109. ENUM

Tipo com lista fixa de valores. PostgreSQL e MySQL têm nativo; nos outros, use CHECK.

CREATE TYPE status_pedido AS ENUM ('pendente','pago','cancelado');
ALTER TABLE pedidos ALTER COLUMN status TYPE status_pedido USING status::status_pedido;

Funções de janela: o essencial

Agregam sem colapsar as linhas. Depois que você entende, não consegue mais viver sem.

110. OVER() — a janela

Calcula sobre um conjunto de linhas, mas mantém cada linha no resultado.

SELECT nome, total, SUM(total) OVER () AS total_geral FROM pedidos;

111. PARTITION BY

Divide em grupos e calcula dentro de cada um, sem GROUP BY.

SELECT cliente_id, total,
       SUM(total) OVER (PARTITION BY cliente_id) AS total_cliente
FROM pedidos;

112. ROW_NUMBER

Numera as linhas dentro da partição. Base para 'o mais recente de cada'.

SELECT ped.cliente_id,
       ped.criado_em,
       ROW_NUMBER() OVER (PARTITION BY ped.cliente_id ORDER BY ped.criado_em DESC) AS ordem
FROM pedidos ped;

113. Pegar o último de cada grupo

Numere e filtre pelo número 1 — o padrão mais usado com window function.

SELECT *
FROM (
  SELECT ped.*,
         ROW_NUMBER() OVER (PARTITION BY ped.cliente_id ORDER BY ped.criado_em DESC) AS ordem
  FROM pedidos ped
) ped_num
WHERE ordem = 1;

114. RANK

Classificação com empate: dois primeiros lugares pulam o segundo.

SELECT nome, total, RANK() OVER (ORDER BY total DESC) AS posicao FROM vendedores;

115. DENSE_RANK

Igual ao RANK, mas sem pular posições após empate.

SELECT nome, total, DENSE_RANK() OVER (ORDER BY total DESC) AS posicao FROM vendedores;

116. NTILE

Divide as linhas em N faixas de tamanho parecido — quartis, decis.

SELECT nome, total, NTILE(4) OVER (ORDER BY total) AS quartil FROM clientes;

117. LAG — linha anterior

Traz o valor da linha anterior. Comparação com o mês passado sai em uma linha.

SELECT mes, total, LAG(total) OVER (ORDER BY mes) AS mes_anterior FROM faturamento;

118. LEAD — próxima linha

Mesma ideia, olhando para frente.

SELECT mes, total, LEAD(total) OVER (ORDER BY mes) AS proximo FROM faturamento;

119. Variação percentual

LAG + aritmética resolve o clássico 'cresceu quanto?'.

SELECT mes, total,
  ROUND(100.0 * (total - LAG(total) OVER (ORDER BY mes)) / NULLIF(LAG(total) OVER (ORDER BY mes), 0), 1) AS variacao
FROM faturamento;

120. Soma acumulada

Soma progressiva ao longo da ordenação.

SELECT dia, valor,
       SUM(valor) OVER (ORDER BY dia ROWS UNBOUNDED PRECEDING) AS acumulado
FROM caixa;

121. Média móvel

Média das últimas N linhas — suaviza a série do gráfico.

SELECT dia, valor,
  AVG(valor) OVER (ORDER BY dia ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS media_7d
FROM metricas;

122. FIRST_VALUE e LAST_VALUE

Primeiro e último valor da janela. Cuidado: LAST_VALUE precisa do frame explícito.

SELECT cliente_id, criado_em,
  FIRST_VALUE(total) OVER (PARTITION BY cliente_id ORDER BY criado_em) AS primeiro
FROM pedidos;

123. WINDOW nomeada

Definiu a mesma janela três vezes? Nomeie uma vez e reutilize.

SELECT cliente_id,
       SUM(total) OVER w AS soma,
       AVG(total) OVER w AS media
FROM pedidos
WINDOW w AS (PARTITION BY cliente_id);

124. Janela × GROUP BY

GROUP BY reduz o número de linhas; a janela preserva. Use a janela quando precisar do detalhe e do total juntos.

SELECT cliente_id, total,
       ROUND(100.0 * total / SUM(total) OVER (PARTITION BY cliente_id), 1) AS pct_do_cliente
FROM pedidos;

JSON dentro do banco

Campos semiestruturados sem abrir mão do SQL — desde que usados com critério.

125. Coluna JSON e JSONB

No PostgreSQL, JSONB é binário, indexável e mais rápido de consultar. Prefira JSONB.

CREATE TABLE eventos (id BIGSERIAL PRIMARY KEY, payload JSONB NOT NULL);

126. Inserir JSON

Passe o documento como texto: o banco valida a sintaxe.

INSERT INTO eventos (payload) VALUES ('{"tipo":"compra","valor":199.9}');

127. Ler um campo

-> devolve JSON; ->> devolve texto (que é o que você quer para comparar).

SELECT payload->>'tipo' AS tipo, (payload->>'valor')::numeric AS valor FROM eventos;

128. Caminho aninhado

#>> navega por vários níveis de uma vez.

SELECT payload#>>'{cliente,email}' AS email FROM eventos;

129. Filtrar por campo do JSON

Funciona como qualquer WHERE — mas indexe se for consulta frequente.

SELECT * FROM eventos WHERE payload->>'tipo' = 'compra';

130. Operador de contenção

@> pergunta se o JSON contém aquele trecho. É o que o índice GIN acelera.

SELECT * FROM eventos WHERE payload @> '{"tipo":"compra"}';

131. Verificar existência de chave

? testa se a chave existe no documento.

SELECT * FROM eventos WHERE payload ? 'cupom';

132. Indexar JSONB

Sem índice, filtrar JSON varre a tabela inteira.

CREATE INDEX idx_eventos_payload ON eventos USING GIN (payload);

133. Índice em campo específico

Mais enxuto que indexar o documento todo, quando você filtra sempre pelo mesmo campo.

CREATE INDEX idx_eventos_tipo ON eventos ((payload->>'tipo'));

134. Atualizar um campo

jsonb_set troca um valor sem reescrever o documento na aplicação.

UPDATE eventos SET payload = jsonb_set(payload, '{status}', '"processado"') WHERE id = 1;

135. Remover uma chave

O operador - devolve o documento sem a chave.

UPDATE eventos SET payload = payload - 'temporario' WHERE id = 1;

136. Expandir array em linhas

Transforma um array JSON em linhas para tratar com SQL normal.

SELECT eve.id, item
FROM eventos eve, jsonb_array_elements(eve.payload->'itens') AS item;

137. Montar JSON no SELECT

Devolve o resultado já no formato que a API precisa.

SELECT jsonb_build_object('id', id, 'nome', nome, 'cidade', cidade) FROM clientes;

138. Quando NÃO usar JSON

Se o campo é consultado, filtrado e relacionado toda hora, ele merece ser uma coluna de verdade.

-- Ruim: preço dentro do JSON, usado em todo relatório
-- Bom: coluna preco DECIMAL(12,2) + índice

Programando dentro do banco

Procedures, funções e triggers: lógica que roda perto do dado.

139. CREATE FUNCTION

Função que recebe parâmetros e devolve um valor, usável dentro do SELECT.

CREATE FUNCTION total_do_cliente(p_id INTEGER) RETURNS NUMERIC AS $$
  SELECT COALESCE(SUM(total), 0) FROM pedidos WHERE cliente_id = p_id;
$$ LANGUAGE SQL;

140. Usar a função

Chame como qualquer função nativa.

SELECT nome, total_do_cliente(id) AS total FROM clientes;

141. Função em PL/pgSQL

Para lógica com variáveis, laços e condicionais.

CREATE FUNCTION reajuste(p_preco NUMERIC, p_pct NUMERIC) RETURNS NUMERIC AS $$
BEGIN
  IF p_pct > 50 THEN RAISE EXCEPTION 'Reajuste acima do permitido';
  END IF;
  RETURN ROUND(p_preco * (1 + p_pct / 100), 2);
END;
$$ LANGUAGE plpgsql;

142. CREATE PROCEDURE

Procedure executa ações (e pode controlar transação); não devolve valor como função.

CREATE PROCEDURE limpar_sessoes() LANGUAGE SQL AS $$
  DELETE FROM sessoes WHERE criado_em < CURRENT_DATE - INTERVAL '90 days';
$$;

143. CALL

Executa a procedure.

CALL limpar_sessoes();

144. Parâmetros de saída

Procedures podem devolver valores por parâmetros INOUT.

CREATE PROCEDURE contar(INOUT total INTEGER) LANGUAGE plpgsql AS $$
BEGIN
  SELECT COUNT(*) INTO total FROM clientes;
END; $$;

145. Variáveis e SELECT INTO

Guarda o resultado de uma consulta em uma variável.

DECLARE v_total NUMERIC;
SELECT SUM(total) INTO v_total FROM pedidos;

146. IF / ELSE

Condicional dentro do código do banco.

IF v_total > 1000 THEN
  RAISE NOTICE 'Meta batida';
ELSE
  RAISE NOTICE 'Faltam %', 1000 - v_total;
END IF;

147. LOOP e FOR

Laços para processar linha a linha — use só quando não der para resolver em conjunto.

FOR ped IN SELECT id FROM pedidos WHERE status = 'pendente' LOOP
  UPDATE pedidos SET status = 'processando' WHERE id = ped.id;
END LOOP;

148. RAISE — mensagens e erros

Loga aviso ou interrompe com exceção.

RAISE NOTICE 'Processando cliente %', v_id;
RAISE EXCEPTION 'Saldo insuficiente para o cliente %', v_id;

149. Tratamento de exceção

Captura o erro e decide o que fazer.

BEGIN
  INSERT INTO clientes (email) VALUES ('duplicado@email.com');
EXCEPTION WHEN unique_violation THEN
  RAISE NOTICE 'E-mail já cadastrado';
END;

150. CREATE TRIGGER

Código disparado automaticamente por INSERT, UPDATE ou DELETE.

CREATE TRIGGER trg_atualiza_data
BEFORE UPDATE ON produtos
FOR EACH ROW EXECUTE FUNCTION set_atualizado_em();

151. Função de trigger

No PostgreSQL, a trigger chama uma função que retorna TRIGGER.

CREATE FUNCTION set_atualizado_em() RETURNS TRIGGER AS $$
BEGIN
  NEW.atualizado_em := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

152. BEFORE × AFTER

BEFORE pode alterar a linha antes de gravar; AFTER serve para efeitos colaterais (log, fila).

CREATE TRIGGER trg_log AFTER INSERT ON pedidos
FOR EACH ROW EXECUTE FUNCTION registrar_log();

153. NEW e OLD

Dentro da trigger, NEW é a linha nova e OLD é a antiga.

IF NEW.preco <> OLD.preco THEN
  INSERT INTO historico_precos (produto_id, de, para) VALUES (OLD.id, OLD.preco, NEW.preco);
END IF;

154. Trigger de auditoria

Guarda quem mudou o quê e quando, sem depender da aplicação.

CREATE FUNCTION auditar() RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO auditoria (tabela, operacao, dados, quando)
  VALUES (TG_TABLE_NAME, TG_OP, to_jsonb(NEW), CURRENT_TIMESTAMP);
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

155. Desabilitar trigger

Em cargas grandes, desligue temporariamente — e lembre de religar.

ALTER TABLE pedidos DISABLE TRIGGER trg_log;
-- carga
ALTER TABLE pedidos ENABLE TRIGGER trg_log;

156. DROP FUNCTION / TRIGGER

Remova o que não é mais usado: código morto no banco é pior que na aplicação.

DROP TRIGGER IF EXISTS trg_log ON pedidos;
DROP FUNCTION IF EXISTS registrar_log();

157. Cuidado com trigger

Trigger é invisível para quem lê a aplicação. Use para integridade e auditoria, não para regra de negócio complexa.

-- Bom: preencher atualizado_em, auditar
-- Ruim: calcular comissão, enviar e-mail, chamar API

158. Função determinística

Marque como IMMUTABLE quando o resultado depende só dos parâmetros: permite indexar e cachear.

CREATE FUNCTION slug(t TEXT) RETURNS TEXT AS $$
  SELECT LOWER(REPLACE(t, ' ', '-'));
$$ LANGUAGE SQL IMMUTABLE;

Modelagem e normalização

Decisões de modelo que você toma em uma tarde e sustenta por anos.

159. 1FN — primeira forma normal

Nada de lista dentro de uma coluna: cada campo guarda um valor só.

-- Ruim: telefones = '1199..., 1198...'
CREATE TABLE telefones (cliente_id INT, numero VARCHAR(20));

160. 2FN — segunda forma normal

Nenhuma coluna depende só de parte da chave composta.

-- Nome do produto não depende do pedido: sai da tabela de itens
CREATE TABLE itens (pedido_id INT, produto_id INT, qtd INT, PRIMARY KEY (pedido_id, produto_id));

161. 3FN — terceira forma normal

Coluna não pode depender de outra coluna comum, só da chave.

-- cidade e estado dependem do CEP, não do cliente
CREATE TABLE ceps (cep CHAR(8) PRIMARY KEY, cidade VARCHAR(80), estado CHAR(2));

162. Desnormalizar de propósito

Duplicar dado para acelerar leitura é válido — desde que seja decisão consciente e com rotina de sincronização.

ALTER TABLE pedidos ADD COLUMN cliente_nome VARCHAR(120);  -- snapshot histórico

163. Um-para-muitos

A chave estrangeira mora no lado 'muitos'.

CREATE TABLE pedidos (
  id SERIAL PRIMARY KEY,
  cliente_id INTEGER NOT NULL REFERENCES clientes (id)
);

164. Muitos-para-muitos

Precisa de uma tabela de ligação com as duas chaves.

CREATE TABLE aluno_curso (
  aluno_id INT REFERENCES alunos (id),
  curso_id INT REFERENCES cursos (id),
  PRIMARY KEY (aluno_id, curso_id)
);

165. Um-para-um

Separe dados opcionais ou sensíveis em outra tabela com a mesma chave.

CREATE TABLE cliente_documentos (
  cliente_id INT PRIMARY KEY REFERENCES clientes (id),
  cpf VARCHAR(14)
);

166. Chave natural × substituta

Chave substituta (id sequencial) é estável; chave natural (CPF, e-mail) muda mais do que parece.

CREATE TABLE clientes (
  id BIGSERIAL PRIMARY KEY,      -- substituta
  cpf VARCHAR(14) UNIQUE         -- natural, com unicidade garantida
);

167. Soft delete

Marcar em vez de apagar preserva histórico — mas exige filtrar em toda consulta.

ALTER TABLE clientes ADD COLUMN excluido_em TIMESTAMP;
SELECT * FROM clientes WHERE excluido_em IS NULL;

168. Tabela de histórico

Guarda o estado ao longo do tempo, em vez de sobrescrever.

CREATE TABLE precos_historico (
  produto_id INT, preco DECIMAL(12,2),
  inicio DATE NOT NULL, fim DATE
);

169. Padrão de nomes

Escolha um padrão (snake_case, singular ou plural) e mantenha. Consistência vale mais que a escolha em si.

-- tabelas no plural, colunas em snake_case, FK como <tabela>_id
CREATE TABLE pedidos (id SERIAL PRIMARY KEY, cliente_id INT);

170. Tipos coerentes entre tabelas

FK e PK precisam do mesmo tipo, senão o JOIN converte e perde o índice.

-- clientes.id BIGINT  ->  pedidos.cliente_id BIGINT (não INTEGER)

Carga e movimentação de dados

Importar, exportar e mover volume sem derrubar o banco.

171. COPY — importar CSV

Carga em massa nativa do PostgreSQL, ordens de grandeza mais rápida que INSERT linha a linha.

COPY clientes (nome, email) FROM '/tmp/clientes.csv' WITH (FORMAT csv, HEADER true);

172. COPY TO — exportar

Mesma via, no sentido contrário.

COPY (SELECT * FROM pedidos WHERE status = 'pago') TO '/tmp/pagos.csv' WITH (FORMAT csv, HEADER true);

173. \copy no cliente

Variante do psql que lê/escreve na máquina do cliente, sem precisar de acesso ao disco do servidor.

\copy clientes FROM 'clientes.csv' WITH (FORMAT csv, HEADER true)

174. LOAD DATA INFILE — MySQL

Equivalente do COPY no MySQL.

LOAD DATA INFILE '/tmp/clientes.csv'
INTO TABLE clientes FIELDS TERMINATED BY ',' IGNORE 1 LINES;

175. BULK INSERT — SQL Server

Carga em massa no SQL Server.

BULK INSERT clientes FROM 'C:\dados\clientes.csv'
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',');

176. Tabela de staging

Importe cru numa tabela temporária, valide, e só então mova para a tabela final.

CREATE TABLE stg_clientes (nome TEXT, email TEXT);
-- COPY para stg_clientes
INSERT INTO clientes (nome, email)
SELECT nome, LOWER(email) FROM stg_clientes WHERE email LIKE '%@%';

177. Deduplicar na carga

DISTINCT ON (PostgreSQL) ou ROW_NUMBER resolvem duplicatas do arquivo de origem.

INSERT INTO clientes (email, nome)
SELECT DISTINCT ON (email) email, nome FROM stg_clientes ORDER BY email, nome;

178. Atualizar em lotes

UPDATE de milhões de linhas de uma vez trava a tabela. Divida em blocos.

UPDATE pedidos SET processado = true
WHERE id IN (SELECT id FROM pedidos WHERE NOT processado LIMIT 10000);

179. Tabela temporária

Existe só na sessão. Ideal para passos intermediários de ETL.

CREATE TEMP TABLE tmp_resultado AS
SELECT cliente_id, SUM(total) AS total FROM pedidos GROUP BY cliente_id;

180. Gerar massa de teste

Dados sintéticos para medir performance com volume real.

INSERT INTO pedidos (cliente_id, total, criado_em)
SELECT (random()*1000)::int, (random()*500)::numeric(12,2),
       CURRENT_DATE - (random()*365)::int
FROM generate_series(1, 100000);

Usuários e permissões

Menor privilégio possível: a aplicação não precisa ser dona do banco.

181. CREATE USER

Cria um usuário/role com senha.

CREATE USER app_web WITH PASSWORD 'senha-forte-aqui';

182. GRANT em tabela

Concede apenas as operações necessárias.

GRANT SELECT, INSERT, UPDATE ON pedidos TO app_web;

183. GRANT somente leitura

Perfil clássico para BI e relatórios.

GRANT CONNECT ON DATABASE loja TO bi_leitura;
GRANT USAGE ON SCHEMA public TO bi_leitura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO bi_leitura;

184. REVOKE

Remove uma permissão concedida.

REVOKE DELETE ON pedidos FROM app_web;

185. Roles e herança

Agrupe permissões numa role e conceda a role aos usuários.

CREATE ROLE leitura;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO leitura;
GRANT leitura TO analista1, analista2;

186. Permissão padrão para tabelas futuras

Sem isso, toda tabela nova precisa de GRANT manual.

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO leitura;

187. Permissão por coluna

Dá para liberar só algumas colunas da tabela.

GRANT SELECT (id, nome, cidade) ON clientes TO app_web;

188. Row Level Security

Cada usuário enxerga só as linhas dele — multi-tenant garantido pelo banco.

ALTER TABLE pedidos ENABLE ROW LEVEL SECURITY;
CREATE POLICY p_tenant ON pedidos
  USING (tenant_id = current_setting('app.tenant')::int);

189. Ver permissões

Auditoria rápida de quem pode o quê.

SELECT grantee, privilege_type FROM information_schema.role_table_grants
WHERE table_name = 'pedidos';

190. Nunca use o superusuário na aplicação

A conta da aplicação não deve poder criar/dropar tabela nem ler dados de outros esquemas.

-- Errado: DATABASE_URL com o usuário postgres
-- Certo: usuário dedicado, com GRANT mínimo

Metadados e manutenção

Perguntar ao banco sobre ele mesmo — e mantê-lo saudável.

191. Listar tabelas

O catálogo padrão information_schema funciona na maioria dos bancos.

SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

192. Listar colunas

Tipos, nulidade e default de cada coluna.

SELECT column_name, data_type, is_nullable
FROM information_schema.columns WHERE table_name = 'pedidos';

193. Ver a definição de uma tabela

Atalhos de cliente que economizam consulta ao catálogo.

\d pedidos            -- psql
SHOW CREATE TABLE pedidos;   -- MySQL
sp_help 'pedidos';           -- SQL Server

194. Tamanho das tabelas

Descobre quem está ocupando o disco.

SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS tamanho
FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;

195. Contagem estimada de linhas

COUNT(*) em tabela gigante é caro; a estimativa do catálogo é instantânea.

SELECT reltuples::bigint AS estimativa FROM pg_class WHERE relname = 'pedidos';

196. ANALYZE — atualizar estatísticas

O otimizador decide o plano com base nelas. Estatística velha gera plano ruim.

ANALYZE pedidos;

197. VACUUM

Recupera espaço de linhas removidas no PostgreSQL (MVCC).

VACUUM ANALYZE pedidos;

198. Conexões ativas

Ver quem está conectado e o que está rodando.

SELECT pid, usename, state, query FROM pg_stat_activity WHERE state <> 'idle';

199. Encerrar consulta travada

Cancela (ou mata) uma sessão que está segurando o banco.

SELECT pg_cancel_backend(12345);    -- pede para cancelar
SELECT pg_terminate_backend(12345); -- encerra a conexão

200. Consultas mais lentas

A extensão pg_stat_statements mostra onde o tempo está indo de verdade.

SELECT query, calls, mean_exec_time
FROM pg_stat_statements ORDER BY mean_exec_time DESC LIMIT 10;

SQL Intermedio: 200 Comandos y Técnicas · Jhonatan Pinheiro