Guia 02 / 4
Os essenciais de MySQL
Um memorando explicado: para que serve cada elemento, como escrevê-lo e quando utilizá-lo.
Escolher e utilizar os elementos essenciais para realizar esta etapa do blogue.
01 · Ligar-se e definir o modelo
Os comandos de terminal não se colam no editor SQL; as consultas SQL não se colam diretamente na shell.
Cliente mysql e seleção da base de dados
No terminal, abrir o cliente com a sua conta. No prompt mysql>, selecionar a base de dados e consultar depois as tabelas. -p pede a palavra-passe sem a inscrever no comando.
Exemplo
Terminal : mysql -u votre_compte -p
SQL : USE blog_recettes;
SQL : SHOW TABLES;
SQL : DESCRIBE recettes;
Os prefixos «Terminal:» e «SQL:» são explicações, e não fazem parte dos comandos.
CREATE DATABASE e CREATE TABLE
Criar primeiro a base de dados, depois a tabela-mãe categories e depois recettes, que a referencia. O ficheiro completo database/schema.sql encontra-se no final da oficina.
Exemplo
CREATE TABLE categories (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
nom VARCHAR(60) NOT NULL UNIQUE
) ENGINE=InnoDB;
Tipos do miniblogue
VARCHAR convém aos títulos curtos; TEXT aos ingredientes e aos passos; SMALLINT e TINYINT aos pequenos valores inteiros; TIMESTAMP à data de criação.
Exemplo
titre VARCHAR(120)
ingredients TEXT
duree_minutes SMALLINT UNSIGNED
portions TINYINT UNSIGNED
cree_le TIMESTAMP DEFAULT CURRENT_TIMESTAMP
Neste primeiro projeto, conservam-se ingredientes e passos sob a forma de linhas de texto; a sua modelação detalhada virá num curso mais avançado.
NOT NULL, UNIQUE, CHECK e FOREIGN KEY
NOT NULL impõe uma presença; UNIQUE impede os duplicados de um valor; CHECK impõe uma condição; FOREIGN KEY verifica uma relação. As duas colunas ligadas devem ter tipos compatíveis.
Exemplo
CONSTRAINT ck_duree CHECK (duree_minutes BETWEEN 1 AND 1440),
CONSTRAINT fk_categorie FOREIGN KEY (categorie_id)
REFERENCES categories(id) ON DELETE RESTRICT
ON DELETE RESTRICT recusa a eliminação de uma categoria ainda utilizada.
02 · Inserir e interrogar
Enumerar as colunas torna as consultas legíveis e independentes da sua ordem física.
INSERT INTO e VALUES
INSERT acrescenta uma linha. As cadeias usam apóstrofos; um apóstrofo interno duplica-se. O motor produz o id se for omitido.
Exemplo
INSERT INTO categories (nom) VALUES ('Goûter');
INSERT INTO categories (nom) VALUES ('Chef d''un jour');
No código PHP do curso de PDO, os valores serão transmitidos por parâmetros em vez de se fabricarem as cadeias SQL.
SELECT, FROM e alias
SELECT escolhe as colunas; FROM indica a tabela. Um alias dá temporariamente um nome a uma tabela ou a uma coluna no resultado.
Exemplo
SELECT r.id, r.titre, r.duree_minutes AS duree
FROM recettes AS r;
WHERE, AND e OR
WHERE seleciona as linhas segundo uma condição. AND exige os dois critérios. Acrescentar parênteses torna explícitas as combinações com OR.
Exemplo
SELECT id, titre FROM recettes
WHERE duree_minutes <= 20 AND portions = 2;
Esperado nos dados iniciais: soupe e salade.
LIKE e carateres universais
LIKE procura um padrão: % representa zero ou mais carateres; _ representa um. A sensibilidade à capitalização e aos acentos depende da colação.
Exemplo
SELECT titre FROM recettes WHERE titre LIKE '%pommes%';
Esperado: Tarte aux pommes. Os símbolos % e _ têm um sentido especial mesmo quando o padrão é transmitido por um parâmetro PDO.
ORDER BY e LIMIT
ORDER BY ordena os resultados. LIMIT reduz o seu número. Acrescentar um id à ordenação desempata as igualdades para obter um resultado estável.
Exemplo
SELECT id, titre, duree_minutes FROM recettes
ORDER BY duree_minutes ASC, id ASC
LIMIT 2;
Esperado: salade (10) e depois soupe (20). Sem ORDER BY, nenhuma ordem de apresentação é garantida.
JOIN e condição ON
JOIN liga cada receita à sua categoria. Os alias r e c encurtam a consulta e eliminam as ambiguidades entre as colunas id.
Exemplo
SELECT r.titre, c.nom AS categorie
FROM recettes AS r
JOIN categories AS c ON c.id = r.categorie_id
ORDER BY r.id;
COUNT e GROUP BY
COUNT(*) conta as linhas. GROUP BY agrupa as linhas que partilham um valor para calcular um total por grupo.
Exemplo
SELECT c.nom, COUNT(r.id) AS total
FROM categories AS c
LEFT JOIN recettes AS r ON r.categorie_id = c.id
GROUP BY c.id, c.nom
ORDER BY c.id;
LEFT JOIN conserva também uma categoria sem receita; COUNT(r.id) vale então 0, ao contrário de COUNT(*).
03 · Modificar com um perímetro claro
Praticar apenas na base de dados do miniblogue.
UPDATE com WHERE
UPDATE modifica as linhas selecionadas. Fazer primeiro um SELECT com o mesmo WHERE para verificar o perímetro.
Exemplo
SELECT id, titre, duree_minutes FROM recettes WHERE id = 1;
START TRANSACTION;
UPDATE recettes SET duree_minutes = 25 WHERE id = 1;
SELECT id, titre, duree_minutes FROM recettes WHERE id = 1;
ROLLBACK;
Esperado: a duração volta a 20 depois do ROLLBACK. Sem WHERE, todas as linhas seriam afetadas.
DELETE numa transação de ensaio
DELETE retira linhas. Neste ensaio, observa-se o resultado e anula-se depois. Executar as instruções na mesma ligação.
Exemplo
START TRANSACTION;
DELETE FROM recettes WHERE id = 3;
SELECT COUNT(*) AS total FROM recettes;
ROLLBACK;
SELECT COUNT(*) AS total FROM recettes;
Esperado na base de dados inicial: 2 durante o ensaio e 3 depois da anulação. O script fornecido não elimina nenhuma tabela.