aulify · Sebenta

UC · Unidade de Competência · UC02830

Programar com SQL

Sebenta · DDL, DML, queries, joins, transações

50h · 4.5 pontos crédito Curso: T. Desenv. Software, T. Inform. Gestão ↗ Referencial oficial SNQ

Apresentação

UC02830 (50h · 4,5 pts) é a UC central de bases de dados. Tendo modelado em UC00602, agora aprende-se a falar com a BD via SQL — criar esquemas, inserir, actualizar, apagar, consultar, juntar tabelas, garantir integridade. Foco em MySQL/MariaDB, com notas sobre PostgreSQL e SQLite.

Categorias do SQL

DDL (Data Definition Language) — CREATE, ALTER, DROP. Define estruturas.
DML (Data Manipulation Language) — INSERT, UPDATE, DELETE. Modifica dados.
DQL (Data Query Language) — SELECT. Consulta.
DCL (Data Control Language) — GRANT, REVOKE. Permissões.
TCL (Transaction Control) — BEGIN, COMMIT, ROLLBACK.

DDL · Criar estruturas

CREATE TABLE

CREATE TABLE Cliente (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(150) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL,
  telefone VARCHAR(20),
  data_registo DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE Categoria (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(80) UNIQUE NOT NULL
);

CREATE TABLE Produto (
  id INT PRIMARY KEY AUTO_INCREMENT,
  nome VARCHAR(200) NOT NULL,
  preco DECIMAL(10,2) NOT NULL CHECK (preco >= 0),
  stock INT DEFAULT 0,
  categoria_id INT,
  FOREIGN KEY (categoria_id) REFERENCES Categoria(id) ON DELETE SET NULL
);

Tipos de dados comuns

Tipo	Uso
`INT`, `BIGINT`	inteiros
`DECIMAL(p,s)`	dinheiro, valores exactos
`FLOAT`, `DOUBLE`	aproximados (não usar para $)
`VARCHAR(n)`	texto até n caracteres
`TEXT`	texto longo
`DATE`	só data
`DATETIME` / `TIMESTAMP`	data + hora
`BOOLEAN`	true/false
`JSON`	objectos JSON (MySQL 5.7+, PG)

Restrições

PRIMARY KEY — identificador único.
FOREIGN KEY ... REFERENCES — liga a outra tabela.
NOT NULL — obrigatório.
UNIQUE — valor único na coluna.
CHECK (cond) — regra customizada.
DEFAULT valor — valor por defeito.

ALTER TABLE

-- Adicionar coluna
ALTER TABLE Cliente ADD COLUMN nif VARCHAR(15);

-- Modificar
ALTER TABLE Cliente MODIFY nome VARCHAR(200) NOT NULL;

-- Renomear
ALTER TABLE Cliente RENAME COLUMN telefone TO contacto;

-- Apagar coluna
ALTER TABLE Cliente DROP COLUMN nif;

-- Adicionar índice
ALTER TABLE Cliente ADD INDEX idx_email (email);

-- Adicionar FK
ALTER TABLE Encomenda
  ADD CONSTRAINT fk_cliente
  FOREIGN KEY (cliente_id) REFERENCES Cliente(id);

DROP

DROP TABLE Cliente;             -- apaga tabela
DROP DATABASE loja_test;        -- apaga BD inteira
DROP INDEX idx_email ON Cliente;

Operações destrutivas. Sempre backup antes em produção.

DML · Modificar dados

INSERT

-- Uma linha, colunas específicas
INSERT INTO Cliente (nome, email)
VALUES ('Ana Silva', 'ana@ex.pt');

-- Várias linhas
INSERT INTO Cliente (nome, email) VALUES
  ('João Costa', 'joao@ex.pt'),
  ('Maria Lopes', 'maria@ex.pt'),
  ('Pedro Santos', 'pedro@ex.pt');

-- Todos os campos (na ordem da tabela)
INSERT INTO Cliente
VALUES (NULL, 'Lúcia', 'lucia@ex.pt', '912345678', NOW());

-- INSERT a partir de SELECT
INSERT INTO ClientesEdu (id, nome)
SELECT id, nome FROM Cliente WHERE email LIKE '%@edu.pt';

UPDATE

-- Sempre com WHERE
UPDATE Cliente SET email = 'novo@ex.pt' WHERE id = 1;

-- Várias colunas
UPDATE Cliente
SET nome = 'Ana Maria Silva', telefone = '919...'
WHERE id = 1;

-- Cálculo
UPDATE Produto SET preco = preco * 1.10 WHERE categoria_id = 5;

-- Com JOIN (MySQL)
UPDATE Produto p
INNER JOIN Categoria c ON p.categoria_id = c.id
SET p.preco = p.preco * 1.05
WHERE c.nome = 'Eletrónica';

Regra de ouro: antes de UPDATE em produção, correr o WHERE como SELECT primeiro para confirmar quantas linhas serão afectadas.

DELETE

-- Com WHERE
DELETE FROM Cliente WHERE id = 5;

-- Múltiplas
DELETE FROM Cliente WHERE data_registo < '2020-01-01';

-- Apagar tudo (cuidado!)
DELETE FROM Cliente;
TRUNCATE TABLE Cliente;   -- mais rápido, reset auto-increment

Soft delete (alternativa):

ALTER TABLE Cliente ADD apagado_em DATETIME;

-- Em vez de DELETE:
UPDATE Cliente SET apagado_em = NOW() WHERE id = 5;

-- Listar só "vivos":
SELECT * FROM Cliente WHERE apagado_em IS NULL;

DQL · Consultar

SELECT básico

-- Todas as colunas, todas as linhas
SELECT * FROM Cliente;

-- Colunas específicas
SELECT nome, email FROM Cliente;

-- Aliases
SELECT nome AS cliente, email AS contacto FROM Cliente;

-- Únicos
SELECT DISTINCT cidade FROM Cliente;

WHERE

-- Igualdade
WHERE id = 5
WHERE nome = 'Ana'

-- Comparações
WHERE preco > 100
WHERE data >= '2026-01-01'

-- Lógicos
WHERE preco > 100 AND stock > 0
WHERE estado = 'pago' OR estado = 'enviado'
WHERE NOT estado = 'cancelado'

-- IN / NOT IN
WHERE estado IN ('pago', 'enviado', 'entregue')

-- Range
WHERE preco BETWEEN 10 AND 50

-- LIKE (padrão)
WHERE nome LIKE 'Ana%'         -- começa por Ana
WHERE email LIKE '%@gmail.com' -- termina em
WHERE nif LIKE '5%%%%%%%%'     -- começa por 5

-- NULL
WHERE telefone IS NULL
WHERE telefone IS NOT NULL

ORDER BY e LIMIT

SELECT * FROM Produto ORDER BY preco DESC;
SELECT * FROM Produto ORDER BY categoria_id ASC, preco DESC;

-- Top 5 mais caros
SELECT * FROM Produto ORDER BY preco DESC LIMIT 5;

-- Paginação (página 3, 10 por página)
SELECT * FROM Produto ORDER BY id LIMIT 10 OFFSET 20;

Funções de agregação

SELECT COUNT(*) FROM Cliente;
SELECT COUNT(DISTINCT cidade) FROM Cliente;
SELECT SUM(total), AVG(total), MIN(total), MAX(total) FROM Encomenda;

GROUP BY e HAVING

-- Quantas encomendas por cliente
SELECT cliente_id, COUNT(*) AS n_enc
FROM Encomenda
GROUP BY cliente_id;

-- Total gasto por cliente
SELECT cliente_id, SUM(total) AS gasto
FROM Encomenda
GROUP BY cliente_id;

-- Clientes que gastaram mais que 1000€
SELECT cliente_id, SUM(total) AS gasto
FROM Encomenda
GROUP BY cliente_id
HAVING SUM(total) > 1000
ORDER BY gasto DESC;

WHERE vs HAVING: - WHERE filtra antes de agregar (linhas individuais). - HAVING filtra depois de agregar (grupos).

Funções úteis

String:

SELECT UPPER(nome), LOWER(email), LENGTH(nome) FROM Cliente;
SELECT CONCAT(nome, ' <', email, '>') FROM Cliente;
SELECT SUBSTRING(nome, 1, 3) FROM Cliente;
SELECT REPLACE(email, '@old.pt', '@new.pt') FROM Cliente;

Data:

SELECT YEAR(data), MONTH(data), DAY(data) FROM Encomenda;
SELECT DATEDIFF(NOW(), data_registo) AS dias FROM Cliente;
SELECT DATE_FORMAT(NOW(), '%d/%m/%Y') AS data;

Numéricos:

SELECT ROUND(preco, 2), FLOOR(preco), CEIL(preco) FROM Produto;
SELECT ABS(-5), MOD(10, 3), POWER(2, 10);

JOINs

INNER JOIN

Devolve só linhas com correspondência em ambas as tabelas:

SELECT c.nome, e.id AS encomenda, e.total
FROM Cliente c
INNER JOIN Encomenda e ON c.id = e.cliente_id;

Clientes sem encomendas não aparecem.

LEFT JOIN

Todas as linhas da esquerda; NULL se sem correspondência:

-- Todos os clientes, com 0 se não tiverem encomendas
SELECT c.nome, COUNT(e.id) AS n
FROM Cliente c
LEFT JOIN Encomenda e ON c.id = e.cliente_id
GROUP BY c.id, c.nome;

-- Clientes que nunca compraram
SELECT c.nome
FROM Cliente c
LEFT JOIN Encomenda e ON c.id = e.cliente_id
WHERE e.id IS NULL;

RIGHT JOIN

Espelho do LEFT. Pouco usado — reescreve-se como LEFT trocando a ordem.

Self JOIN

Mesma tabela consigo:

-- Funcionários e os seus chefes
SELECT f.nome AS funcionario, c.nome AS chefe
FROM Funcionario f
LEFT JOIN Funcionario c ON f.chefe_id = c.id;

Múltiplos JOINs

SELECT c.nome AS cliente,
       p.nome AS produto,
       ei.qty,
       ei.preco_unit,
       (ei.qty * ei.preco_unit) AS subtotal
FROM Cliente c
INNER JOIN Encomenda e ON c.id = e.cliente_id
INNER JOIN EncomendaItem ei ON e.id = ei.encomenda_id
INNER JOIN Produto p ON ei.produto_id = p.id
WHERE e.id = 42
ORDER BY p.nome;

Subqueries

IN

-- Clientes que fizeram encomenda > 100€
SELECT * FROM Cliente
WHERE id IN (
  SELECT cliente_id FROM Encomenda WHERE total > 100
);

EXISTS

-- Clientes que têm pelo menos uma encomenda
SELECT * FROM Cliente c
WHERE EXISTS (
  SELECT 1 FROM Encomenda e WHERE e.cliente_id = c.id
);

Scalar subquery

SELECT nome,
  (SELECT COUNT(*) FROM Encomenda WHERE cliente_id = Cliente.id) AS n_enc,
  (SELECT MAX(total) FROM Encomenda WHERE cliente_id = Cliente.id) AS max_enc
FROM Cliente;

CTE (Common Table Expression)

Alternativa moderna e legível:

WITH gastos AS (
  SELECT cliente_id, SUM(total) AS total_gasto
  FROM Encomenda
  GROUP BY cliente_id
)
SELECT c.nome, g.total_gasto
FROM Cliente c
JOIN gastos g ON c.id = g.cliente_id
WHERE g.total_gasto > 1000
ORDER BY g.total_gasto DESC;

CTEs recursivas existem (para árvores/grafos) mas estão fora do âmbito básico.

Transações

ACID

Atomicidade — todas as operações da transação ou nenhuma.
Consistência — restrições (PK, FK, CHECK) sempre respeitadas.
Isolamento — transações concorrentes não interferem.
Durabilidade — após COMMIT, dados persistem mesmo com queda de energia.

BEGIN / COMMIT / ROLLBACK

BEGIN;  -- ou START TRANSACTION;

UPDATE Conta SET saldo = saldo - 100 WHERE id = 1;
UPDATE Conta SET saldo = saldo + 100 WHERE id = 2;

-- Se ambos OK:
COMMIT;

-- Se algo falhou:
ROLLBACK;

Transferência bancária só faz sentido como transação — debitar sem creditar deixaria o sistema inconsistente.

Isolation levels

Do menos para o mais isolado:

READ UNCOMMITTED — vê alterações não-confirmadas (dirty reads).
READ COMMITTED — vê apenas dados confirmados.
REPEATABLE READ — leituras dentro da transação são consistentes.
SERIALIZABLE — máximo isolamento, performance menor.

MySQL default: REPEATABLE READ. PostgreSQL: READ COMMITTED.

Índices

Estruturas que aceleram pesquisa em colunas frequentemente filtradas/ordenadas.

-- Índice simples
CREATE INDEX idx_email ON Cliente(email);

-- Composto
CREATE INDEX idx_enc_cli_data ON Encomenda(cliente_id, data);

-- Único
CREATE UNIQUE INDEX idx_email_uniq ON Cliente(email);

Trade-off: - Aceleram SELECT (especialmente em tabelas grandes). - Abrandam INSERT/UPDATE/DELETE (mantêm índice). - Ocupam disco.

Indexar: - PK (já indexada). - FK (recomendado, alguns BDs já fazem). - Colunas em WHERE frequente. - Colunas em ORDER BY frequente. - Colunas em JOIN.

Não indexar: - Tabelas pequenas (<10k linhas). - Colunas com poucos valores distintos (boolean). - Colunas raramente filtradas.

EXPLAIN

EXPLAIN SELECT * FROM Cliente WHERE email = 'ana@ex.pt';

Mostra plano de execução; permite ver se está a usar índices ou a fazer "full table scan".

Views

Query "guardada" como tabela virtual:

CREATE VIEW VendasPorCliente AS
SELECT c.id, c.nome, COUNT(e.id) AS n_enc, SUM(e.total) AS total
FROM Cliente c
LEFT JOIN Encomenda e ON c.id = e.cliente_id
GROUP BY c.id, c.nome;

-- Usar como tabela
SELECT * FROM VendasPorCliente WHERE total > 500;

Views simplificam queries repetidas e podem encapsular complexidade.

Stored procedures

Lógica guardada no servidor. Usar com moderação — preferível em código aplicacional moderno.

DELIMITER //
CREATE PROCEDURE TransferirSaldo(IN origem INT, IN destino INT, IN qty DECIMAL(10,2))
BEGIN
  START TRANSACTION;
  UPDATE Conta SET saldo = saldo - qty WHERE id = origem;
  UPDATE Conta SET saldo = saldo + qty WHERE id = destino;
  COMMIT;
END //
DELIMITER ;

CALL TransferirSaldo(1, 2, 100.00);

Triggers

Acções automáticas em INSERT/UPDATE/DELETE:

CREATE TRIGGER actualizar_stock
AFTER INSERT ON EncomendaItem
FOR EACH ROW
BEGIN
  UPDATE Produto SET stock = stock - NEW.qty WHERE id = NEW.produto_id;
END;

Útil para auditoria, cache, validações; usar com moderação — debugging é difícil.

Boas práticas

Performance

Índices em colunas filtradas.
EXPLAIN queries lentas.
LIMIT quando só precisas de poucas linhas.
Evita SELECT * em produção; nomeia colunas.
JOIN com índices ou problema sério.

Segurança

Prepared statements — nunca concatenar input do utilizador em SQL (SQL injection).
Permissões mínimas — utilizador da app não deve ter DROP.
Backups regulares — automatizados, testados (restore).

Estilo

-- Bom
SELECT c.nome,
       SUM(e.total) AS total_gasto
FROM   Cliente c
INNER JOIN Encomenda e ON c.id = e.cliente_id
WHERE  e.estado = 'pago'
GROUP BY c.id, c.nome
HAVING total_gasto > 1000
ORDER BY total_gasto DESC;

Palavras-chave em maiúsculas; alinhamento; aliases curtos; uma cláusula por linha.

Apêndices

A · Cheatsheet

-- DDL
CREATE TABLE x (id INT PRIMARY KEY, nome VARCHAR(50));
ALTER TABLE x ADD col VARCHAR(50);
DROP TABLE x;

-- DML
INSERT INTO x (col) VALUES ('val');
UPDATE x SET col = 'val' WHERE id = 1;
DELETE FROM x WHERE id = 1;

-- SELECT
SELECT col FROM x WHERE cond ORDER BY col LIMIT 10;
SELECT col, COUNT(*) FROM x GROUP BY col HAVING COUNT(*) > 1;
SELECT a.col, b.col FROM a JOIN b ON a.id = b.a_id;

-- TCL
BEGIN; UPDATE x ...; COMMIT;  -- ou ROLLBACK

B · Glossário

ACID. Atomicity, Consistency, Isolation, Durability. CTE. Common Table Expression — query temporária nomeada. DDL/DML/DQL. Categorias de comandos SQL. JOIN. Combinação de tabelas. View. Query guardada como tabela virtual.

C · Recursos

SQLBolt · sqlbolt.com — tutorial interactivo.
SQL Tutorial · sqltutorial.org.
Use the Index, Luke · use-the-index-luke.com — índices em profundidade.
MySQL docs · dev.mysql.com/doc/.
PostgreSQL docs · postgresql.org/docs/.