Banco de dados, SGBD e esquema

Banco de dados é o conjunto dos dados guardados; SGBD é o programa que cria as estruturas, impõe as regras e responde às consultas. No curso o SGBD é o MySQL e o banco se chama escola. O conjunto de tabelas com as regras que o banco mantém é o esquema.

Comandos de administração

Tabela, linha e coluna

A tabela agrupa fatos do mesmo tipo, a linha é cada fato e a coluna é cada

característica desse fato. Na tabela alunos, a linha é uma pessoa e as colunas

são nome, e-mail, cidade e nascimento. O tipo é decidido na criação da tabela e

vale para todas as linhas: em uma coluna VARCHAR(120) o banco corta o

centésimo vigésimo primeiro caractere de qualquer nome, sempre.

Sinônimos que aparecem em material antigo

Chave primária e chave estrangeira

A chave primária identifica a linha de forma única: no MySQL nunca aceita NULL e

nunca repete valor. A chave estrangeira é uma coluna que guarda o valor da chave

primária de outra tabela, e é ela que permite juntar as duas em uma consulta só.

O par de sintaxe que define o relacionamento

-- chave primaria de uma tabela
<coluna> <tipo> AUTO_INCREMENT PRIMARY KEY

-- chave estrangeira, com o nome dado em CONSTRAINT
CONSTRAINT <nome> FOREIGN KEY (<coluna>) REFERENCES <tabela> (<chave>)

Um aluno se matricula em vários cursos e um curso tem vários alunos: esse

relacionamento é N:N e ninguém consegue guardá-lo em uma tabela só. Quem resolve

é uma tabela de junção, no modelo matriculas, que guarda o par aluno_id e

curso_id — uma linha por matrícula.

Exemplo

<?php
declare(strict_types=1);

// Modelo do curso: um banco, tres tabelas e os relacionamentos.
// Nao ha conexao com servidor nenhum: o script imprime o SQL que cria a estrutura.

$sql = <<<'SQL'
-- o banco guarda o conjunto inteiro do curso
CREATE DATABASE IF NOT EXISTS escola
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE escola;

-- tabela: linha e curso, coluna e atributo do curso
CREATE TABLE IF NOT EXISTS cursos (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  nome          VARCHAR(120) NOT NULL,
  carga_horaria INT NOT NULL DEFAULT 40,
  valor         DECIMAL(10,2) NOT NULL,
  ativo         TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS alunos (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  nome       VARCHAR(120) NOT NULL,
  email      VARCHAR(180) NOT NULL,
  cidade     VARCHAR(80),
  nascimento DATE,
  criado_em  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- tabela de juncao: e ela que resolve o relacionamento N:N entre as duas
CREATE TABLE IF NOT EXISTS matriculas (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  aluno_id       INT NOT NULL,
  curso_id       INT NOT NULL,
  data_matricula DATE NOT NULL,
  situacao       ENUM('ativa', 'cancelada', 'concluida') NOT NULL DEFAULT 'ativa',
  CONSTRAINT fk_matricula_aluno FOREIGN KEY (aluno_id) REFERENCES alunos (id),
  CONSTRAINT fk_matricula_curso FOREIGN KEY (curso_id) REFERENCES cursos (id)
);
SQL;

echo $sql . "\n";

Saída real

-- o banco guarda o conjunto inteiro do curso
CREATE DATABASE IF NOT EXISTS escola
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE escola;

-- tabela: linha e curso, coluna e atributo do curso
CREATE TABLE IF NOT EXISTS cursos (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  nome          VARCHAR(120) NOT NULL,
  carga_horaria INT NOT NULL DEFAULT 40,
  valor         DECIMAL(10,2) NOT NULL,
  ativo         TINYINT(1) NOT NULL DEFAULT 1
);

CREATE TABLE IF NOT EXISTS alunos (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  nome       VARCHAR(120) NOT NULL,
  email      VARCHAR(180) NOT NULL,
  cidade     VARCHAR(80),
  nascimento DATE,
  criado_em  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- tabela de juncao: e ela que resolve o relacionamento N:N entre as duas
CREATE TABLE IF NOT EXISTS matriculas (
  id             INT AUTO_INCREMENT PRIMARY KEY,
  aluno_id       INT NOT NULL,
  curso_id       INT NOT NULL,
  data_matricula DATE NOT NULL,
  situacao       ENUM('ativa', 'cancelada', 'concluida') NOT NULL DEFAULT 'ativa',
  CONSTRAINT fk_matricula_aluno FOREIGN KEY (aluno_id) REFERENCES alunos (id),
  CONSTRAINT fk_matricula_curso FOREIGN KEY (curso_id) REFERENCES cursos (id)
);

Tipos numéricos

O tipo da coluna decide o que o banco guarda, quanto espaço ocupa e como compara.

Em SQL, 1234 e '1234' não são o mesmo valor: o primeiro é inteiro, o segundo

é texto. DECIMAL é o tipo obrigatório para dinheiro, porque FLOAT e DOUBLE

guardam o número em notação científica e 0.1 + 0.2 não dá exatamente 0.3.

Tipos numéricos e o que cada um guarda

Tipos de texto

CHAR(n) ocupa sempre n bytes e serve para código de tamanho fixo; VARCHAR(n)

ocupa só o que foi gravado, até o limite de n caracteres. ENUM limita a coluna

a uma lista de valores e tem um preço: o conjunto fica gravado na própria tabela,

e incluir um valor novo exige ALTER TABLE.

Tipos de texto e limite de tamanho

Tipos de data, hora e o fim da definição de coluna

DATE guarda só a data e basta para nascimento e matrícula. DATETIME grava o

que foi digitado, sem conversão; TIMESTAMP grava um número inteiro de

segundos, ocupa menos espaço, mas fica em UTC e tem intervalo curto, de 1970 a

2038.

A forma de fechar a definição de uma coluna

<coluna> <tipo> [AUTO_INCREMENT] [NOT NULL] [DEFAULT <valor>]
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = <collation>;

AUTO_INCREMENT só entra em coluna inteira que seja chave primária. O conjunto

de caracteres vem no final da tabela e vale para todas as colunas de texto:

utf8mb4 é a única opção que guarda qualquer caractere Unicode, e o utf8 do

próprio MySQL é incompleto e quebra em texto com emoji.

Exemplo

<?php
declare(strict_types=1);

// Os tipos de coluna na pratica: uma tabela que usa quase todos.
// Vale para ler a coluna e saber o que o banco vai aceitar nela.

$sql = <<<'SQL'
CREATE TABLE IF NOT EXISTS professores (
  -- id numerico gerado pelo proprio banco
  id            INT AUTO_INCREMENT PRIMARY KEY,
  -- nome variavel: o maior tamanho que ja foi gravado e o limite
  nome          VARCHAR(120) NOT NULL,
  -- sigla tem tamanho fixo: ocupa sempre os 4 bytes
  sigla         CHAR(4) NOT NULL,
  -- email nao se repete: e o que o login usa
  email         VARCHAR(180) NOT NULL,
  -- dinheiro em exato, nunca FLOAT
  salario       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  -- contagem pequena cabe em 2 bytes
  horas_semana  SMALLINT UNSIGNED NOT NULL DEFAULT 40,
  -- booleano e TINYINT(1) gravado como 0 ou 1
  ativo         TINYINT(1) NOT NULL DEFAULT 1,
  -- lista controlada
  area          VARCHAR(60) NOT NULL,
  formacao      ENUM('licenciatura', 'pos', 'tecnologo') NOT NULL,
  -- data sem hora e suficiente
  admissao      DATE NOT NULL,
  -- data e hora sem fuso: o que entra e o que sai
  atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- documento livre, util para configuracao que muda de forma
  preferencias  JSON NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- forma de gravar e ler os tipos no dia a dia
INSERT INTO professores (nome, sigla, email, salario, horas_semana, area, formacao, admissao, preferencias)
VALUES ('Helena Prado', 'HP', '[email protected]', 7200.00, 40, 'Banco de dados', 'pos', '2019-03-04', '{"turno": "noite", "aulas_online": true}'),
       ('Igor Salles',   'IS', '[email protected]',   6100.50, 30, 'Programacao',   'licenciatura', '2021-08-16', NULL);
SQL;

echo $sql . "\n";

Saída real

CREATE TABLE IF NOT EXISTS professores (
  -- id numerico gerado pelo proprio banco
  id            INT AUTO_INCREMENT PRIMARY KEY,
  -- nome variavel: o maior tamanho que ja foi gravado e o limite
  nome          VARCHAR(120) NOT NULL,
  -- sigla tem tamanho fixo: ocupa sempre os 4 bytes
  sigla         CHAR(4) NOT NULL,
  -- email nao se repete: e o que o login usa
  email         VARCHAR(180) NOT NULL,
  -- dinheiro em exato, nunca FLOAT
  salario       DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  -- contagem pequena cabe em 2 bytes
  horas_semana  SMALLINT UNSIGNED NOT NULL DEFAULT 40,
  -- booleano e TINYINT(1) gravado como 0 ou 1
  ativo         TINYINT(1) NOT NULL DEFAULT 1,
  -- lista controlada
  area          VARCHAR(60) NOT NULL,
  formacao      ENUM('licenciatura', 'pos', 'tecnologo') NOT NULL,
  -- data sem hora e suficiente
  admissao      DATE NOT NULL,
  -- data e hora sem fuso: o que entra e o que sai
  atualizado_em DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  -- documento livre, util para configuracao que muda de forma
  preferencias  JSON NULL
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- forma de gravar e ler os tipos no dia a dia
INSERT INTO professores (nome, sigla, email, salario, horas_semana, area, formacao, admissao, preferencias)
VALUES ('Helena Prado', 'HP', '[email protected]', 7200.00, 40, 'Banco de dados', 'pos', '2019-03-04', '{"turno": "noite", "aulas_online": true}'),
       ('Igor Salles',   'IS', '[email protected]',   6100.50, 30, 'Programacao',   'licenciatura', '2021-08-16', NULL);

A sintaxe do CREATE TABLE

CREATE TABLE descreve a estrutura: nome da tabela, nome e tipo de cada coluna e

as regras que valem para o conjunto. Entre parênteses, uma coluna por linha,

separadas por vírgula, e a vírgula sobrando é erro de sintaxe.

A forma geral, com regra junto da coluna e regra no fim

CREATE TABLE [IF NOT EXISTS] <tabela> (
  <coluna> <tipo> [PRIMARY KEY] [NOT NULL] [UNIQUE] [DEFAULT <valor>],
  ...
  [CONSTRAINT <nome> UNIQUE (<colunas>)]
) ENGINE = InnoDB;

As regras que valem linha a linha

NOT NULL proíbe o vazio, UNIQUE proíbe valor repetido e DEFAULT define o

que entra quando a coluna é omitida. UNSIGNED tira os negativos da faixa, e

ZEROFILL ainda completa com zeros à esquerda — raramente é o que se quer num

identificador.

Restrições mais usadas

Chave estrangeira e o que acontece ao apagar

A chave estrangeira precisa do ON DELETE e do ON UPDATE definidos, senão o

MySQL recusa a tabela. O que está entre parênteses é a reação do banco quando

alguém apaga a linha na tabela referenciada — e escolher CASCADE em

matriculas significa que apagar o aluno apaga as matrículas dele junto.

O par que não pode faltar

CONSTRAINT <nome>
    FOREIGN KEY (<coluna>) REFERENCES <tabela> (<chave>)
    ON DELETE <acao> ON UPDATE <acao>

O motor tem de ser InnoDB para tudo isso funcionar: o MyISAM ignora chave

estrangeira e não tem transação.

Exemplo

<?php
declare(strict_types=1);

// CREATE TABLE com todas as restricoes que o modelo usa.
// ON DELETE e ON UPDATE nao sao opcionais: sem eles o MySQL recusa a tabela.

$sql = <<<'SQL'
CREATE TABLE IF NOT EXISTS matriculas (
  -- chave primaria simples, gerada pelo banco
  id             INT AUTO_INCREMENT,
  -- chave estrangeira para os dois lados do relacionamento
  aluno_id       INT NOT NULL,
  curso_id       INT NOT NULL,
  -- mesma matricula nao pode existir duas vezes
  data_matricula DATE NOT NULL,
  situacao       ENUM('ativa', 'cancelada', 'concluida') NOT NULL DEFAULT 'ativa',
  -- valor tem que estar entre 0 e 100
  nota_final     DECIMAL(5,2) NULL,
  -- data da ultima mudanca, mexida sozinha a cada UPDATE
  atualizado_em  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY ux_matricula (aluno_id, curso_id),
  CONSTRAINT ck_nota CHECK (nota_final IS NULL OR (nota_final >= 0 AND nota_final <= 100)),

  -- apagar o aluno leva as matriculas dele junto
  CONSTRAINT fk_matricula_aluno FOREIGN KEY (aluno_id) REFERENCES alunos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  -- apagar o curso bloqueia enquanto houver matricula
  CONSTRAINT fk_matricula_curso FOREIGN KEY (curso_id) REFERENCES cursos (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- conferir o que o banco gravou, antes de confiar no papel
DESCRIBE matriculas;
SQL;

echo $sql . "\n";

Saída real

CREATE TABLE IF NOT EXISTS matriculas (
  -- chave primaria simples, gerada pelo banco
  id             INT AUTO_INCREMENT,
  -- chave estrangeira para os dois lados do relacionamento
  aluno_id       INT NOT NULL,
  curso_id       INT NOT NULL,
  -- mesma matricula nao pode existir duas vezes
  data_matricula DATE NOT NULL,
  situacao       ENUM('ativa', 'cancelada', 'concluida') NOT NULL DEFAULT 'ativa',
  -- valor tem que estar entre 0 e 100
  nota_final     DECIMAL(5,2) NULL,
  -- data da ultima mudanca, mexida sozinha a cada UPDATE
  atualizado_em  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  PRIMARY KEY (id),
  UNIQUE KEY ux_matricula (aluno_id, curso_id),
  CONSTRAINT ck_nota CHECK (nota_final IS NULL OR (nota_final >= 0 AND nota_final <= 100)),

  -- apagar o aluno leva as matriculas dele junto
  CONSTRAINT fk_matricula_aluno FOREIGN KEY (aluno_id) REFERENCES alunos (id)
    ON DELETE CASCADE ON UPDATE CASCADE,
  -- apagar o curso bloqueia enquanto houver matricula
  CONSTRAINT fk_matricula_curso FOREIGN KEY (curso_id) REFERENCES cursos (id)
    ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- conferir o que o banco gravou, antes de confiar no papel
DESCRIBE matriculas;

Índice

Índice é uma estrutura auxiliar que o banco mantém ordenada para achar a linha

mais rápido, do mesmo jeito que o índice remissivo de um livro. Ele não

organiza o resultado da consulta — isso é ORDER BY — e sim economiza tempo de

procura. O custo é espaço em disco e mais trabalho a cada INSERT, UPDATE e

DELETE.

Os dois tipos de índice

CREATE UNIQUE INDEX ix_alunos_email ON alunos (email);
CREATE INDEX ix_matriculas_curso ON matriculas (curso_id);
DROP INDEX ix_alunos_email ON alunos;

Onde o índice ajuda e onde atrapalha

O índice é usado quando a coluna está no WHERE, no JOIN, no ORDER BY ou no

GROUP BY. EXPLAIN mostra se ele foi usado: a coluna key da saída traz o nome

do índice, e type: ALL significa varredura completa, que é o sinal de que a

tabela precisa de índice.

Leitura do EXPLAIN

EXPLAIN SELECT * FROM matriculas WHERE curso_id = 3;

Índice em coluna que quase todo mundo deixa nula só ocupa espaço. Índice

composto é usado só pelo lado esquerdo: (curso_id, situacao) não serve para

filtrar só por situacao, e a ordem das colunas importa.

Normalização

Normalizar é dividir a tabela para que cada dado fique guardado uma vez só, em uma

tabela que o descreve. Sem isso o mesmo nome do curso se repete em

cada linha de matriculas, e corrigir o nome exige acertar todas de uma vez.

As três formas normais, resumidas

Nem toda base precisa chegar à 3FN. Tabela de log ou de relatório é lida com

freqência e desnormalizar nela é uma decisão válida, desde que consciente.

Exemplo

<?php
declare(strict_types=1);

// Indices e normalizacao: o que o banco faz sozinho e o que a modelagem resolve.
// EXPLAIN aparece aqui como texto; a execucao dele depende de um banco com dados.

$sql = <<<'SQL'
-- 1) indice unico: dois alunos nao podem ter o mesmo email
CREATE UNIQUE INDEX ux_alunos_email ON alunos (email);

-- 2) indice composto: acelera filtrar por curso e sobe a situacao
CREATE INDEX ix_matriculas_curso_situacao ON matriculas (curso_id, situacao);

-- 3) 2FN: cidade e estado dependem da cidade, nao da matricula.
--    Nao sao dados do aluno: sao dados do lugar onde ele mora.
CREATE TABLE IF NOT EXISTS cidades (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  nome   VARCHAR(80) NOT NULL,
  estado CHAR(2) NOT NULL,
  UNIQUE KEY ux_cidade_estado (nome, estado)
);

-- 4) 3FN: o nome do curso nao e dado da matricula, e dado do curso.
--    Depois desta separacao, renomear o curso e um UPDATE so.
ALTER TABLE matriculas DROP COLUMN nome_curso;

-- como se descobre qual indice a consulta esta usando
EXPLAIN SELECT id, aluno_id FROM matriculas WHERE curso_id = 3;
EXPLAIN SELECT id FROM alunos WHERE email = '[email protected]';
SQL;

echo $sql . "\n";

Saída real

-- 1) indice unico: dois alunos nao podem ter o mesmo email
CREATE UNIQUE INDEX ux_alunos_email ON alunos (email);

-- 2) indice composto: acelera filtrar por curso e sobe a situacao
CREATE INDEX ix_matriculas_curso_situacao ON matriculas (curso_id, situacao);

-- 3) 2FN: cidade e estado dependem da cidade, nao da matricula.
--    Nao sao dados do aluno: sao dados do lugar onde ele mora.
CREATE TABLE IF NOT EXISTS cidades (
  id     INT AUTO_INCREMENT PRIMARY KEY,
  nome   VARCHAR(80) NOT NULL,
  estado CHAR(2) NOT NULL,
  UNIQUE KEY ux_cidade_estado (nome, estado)
);

-- 4) 3FN: o nome do curso nao e dado da matricula, e dado do curso.
--    Depois desta separacao, renomear o curso e um UPDATE so.
ALTER TABLE matriculas DROP COLUMN nome_curso;

-- como se descobre qual indice a consulta esta usando
EXPLAIN SELECT id, aluno_id FROM matriculas WHERE curso_id = 3;
EXPLAIN SELECT id FROM alunos WHERE email = '[email protected]';