- MySQL
- Ambiente de Banco de Dados
- Relacional
- Índices
- Chaves Primárias
- Chaves Estrangeiras
- Constraints
- CODD/Date
- Conceitos-Chave:
- Tabela:
- definição
- valores, agrupados em
- exemplo: agenda
- campos:
- nome, varchar(32)
- telefone, varchar(32)
- email, varchar(64)
- registros:
- registro
- nome: "Aldrin Leal"
- telefone: "555-5555"
- email: "aldrin@leal.eng.br"
- CREATE TABLE agenda (nome VARCHAR(32), telefone VARCHAR(32), email VARCHAR(64));
- SQL
- Expressa comandos em bases de dados
- DML (Manutenção dos Dados da Tabela)
- CRUD
- Comandos:
- Sigla para as Operações Básicas:
- DDL (Manutenção da Estrutura da Tabela)
- Padronizado
- Livre
- Multiplataforma
- Extensível
- Moderno
- Popular
- Multi-Formato
- Tabelas podem ser armazenadas como:
- Mecanismo Não-Transacional (MyISAM)
- Mecanismo Transacional-Relacional (InnoDB)
- Mecanismo BerkeleyDB (BDB)
- Tabelas em Formato CSV (CSV)
- Transacional
- Suporte a ACID, aonde transações são:
- Atômicas
- Consistentes
- Isoladas
- Duráveis
- Site Principal: http://www.mysql.com/
- Exemplo Prático em LAMP
- LAMP?
- Tarefas:
Analise / Definição do Problema / Estudo de Caso: Favoritos- Escrever sentença dos casos de uso previstos
- "No site, o usuário poderá:"
- Fazer Login
- Fazer Logout
- Cadastrar-se
- Pesquisar por outros usuários
- Inserir favorito, com:
- Nome
- URL
- "Tags"
- Exemplo:
- mnemetica
- Nome: mnemetica
- URL: http://www.leal.eng.br/mnemetica/
- Tags:
- leal.eng.br
- aldrin
- weblog
- mnemetica
- Pesquisar
- Nos seus
- No Dos outros
- Por Tags
- Excluir Favorito
- Ver favoritos
Classificação Semântica:- Verbo é Operação
- Login
- Logout
- Cadastrar
- Pesquisar
- Inserir
- Excluir
- Substantivo é Entidade (i.e., tabela) ou Atributo (i.e., campo):
Identificar Possíveis Relações de Chave Primária / Chave Estrangeira:- "Um usuário pode possuir de zero a infinitos Favoritos"
- Relacionamento, onde:
- Usuário
- Pode possuir de zero a infinitos
- "Um favorito *DEVE* possuir de um a infinitas tags"
- Relacionamento, onde:
- Favorito
- Deve possuir de um a infinitas
Modele o Esquema ou Diretamente os CREATE TABLE- Tabelas:
- usuario
- referenciado por:
- campos:
- id
- INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
- nome
- email
- senha
- favorito
- referencia:
- referenciado por:
- campos:
- id
- INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
- nome
- url
- descricao
- usuario_id
- INT NOT NULL REFERENCES usuario (id)
- campo com chave estrangeira
- usuario_id só é válido para valores que estejam contidos no valores do campo id para os registros da tabela usuário
- relacionamento muitos-para-um, onde:
- muitos favoritos pertencem a um único usuário
- índices:
- indx0
- campos
- UNIQUE
- interpretação: vários usuários podem ter o mesmo URL, porém é proibido a um usuário possuir dois favoritos com o mesmo URL
- tag
- referenciado por:
- campos:
- id
- INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
- nome
- índices:
- tag_favorito
- referencia:
- campos:
- id
- INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
- tag_id
- INTEGER NOT NULL REFERENCES tag(id)
- favorito_id
- INTEGER NOT NULL REFERENCES favorito(id)
- índices:
- "JOIN TABLE": Tabela específica para relacionamentos um-para-muitos, onde:
- Um favorito pode possuir várias tags diferentes
Desenhe o Esquema:- Ferramentas? Inúmeras
- Visio
- ERWin
- DBDesigner
- SQL Server Studio
- Resultado Final:
- Onde:
- As Setas Representam Relações de Dependência:
- Unicidades:
- Que nem o "Highlander" (sim, aquele filme): Só pode haver um!
- Só pode haver um e-mail distinto na tabela usuario
- Só pode haver um favorito com um mesmo valor de URL e de usuario_id
- Só pode haver um tag_favorito com o mesmo tag_id e favorito_id
- Só pode haver uma única tag com o mesmo nome
- As FK representam "Foreign Keys", onde:
- Um favorito precisa de um usuário
- Um registro tag_favorito:
- Precisa de um registro favorito
- Precisa de um registro tag
- Quadrados representam Entidades (ou Tabelas):
- usuario
- favorito
- tag
- tag_favorito
Dica Óbvia: Baseada no Yahoo! del.icio.us (http://del.icio.us/)
- Criar / Alterar / Popular Tabelas
- Boas Práticas
- ID Autoincremento
- Boas Constraints
- Escolher bem a cardinalidade
- Índices e Constraints de Unicidade
- Integridade Referencial Bem-Feita
- Comandos a Usar:
- Comando CREATE TABLE
- Comando ALTER TABLE
- Comando DROP TABLE
- Comando CREATE INDEX
- Comando DROP INDEX
- Comando INSERT
- Comando SELECT
- Comando INSER SELECT
- Implementando:
- Iniciar XAMPP
- Iniciar | Todos os Programas | ApacheFriends | XAMPP | CONTROL XAMPP SERVER PANEL
- Conferir / Ligar:
- Apache
- MySQL
- Abrir Site do XAMPP em http://localhost/xampp/
- Na Barra de Navegação à Esqueda, em "Tools", escolher "phpMyAdmin"
- Criar o Banco de Dados "Favoritos", Collation: utf8_unicode_ci
- Criar nova tabela no Banco de Dados:
- usuario, 4 campos
- id
- tipo: INT
- extra: AUTO_INCREMENT
- primary key: sim
- email:
- login:
- tipo: VARCHAR
- tamanho: 32
- unico: sim
- senha:
- Extra:
- aproveite e insira um usuário:
- Aba "SQL":
- Comando SQL para inserir (INSERT):
INSERT INTO usuario (email, login, senha) VALUES ('aldrin@leal.eng.br', 'aldrinleal', MD5('senha'));
Explicação?
"*INSIRA UM NOVO REGISTRO* *NA TABELA usuario* CUJO VALOR DOS REGISTROS (email, login, e senha) seja, respectivamente, ('aldrin@leal.eng.br', 'aldrinleal', e o resultado da função MD5('senha'))
- Confira o usuário que foi inserido: SELECT id, email, login, senha FROM usuario WHERE email='aldrin@leal.eng.br'
- Explicação?
SELECIONE login, email, e senha DA TABELA usuario AONDE o email SEJA IGUAL A 'aldrin@leal.eng.br' - Resultado: Uma única linha, aonde:
- id: 1
- email: aldrin@leal.eng.br
- login: aldrinleal
- senha: e8d95a51f3af4a3b134bf6bb680a213a
- Para entender mais sobre:
- criar índice único adicional para email
- nome: idx0
- campos: 1 (email)
- tipo: UNIQUE
- ao preencher, execute e salve
- resultado: ALTER TABLE `usuario` ADD UNIQUE `idx0` ( `email` )
- favorito, 5 campos
- id:
- tipo: INT
- extra: AUTO_INCREMENT
- primary key: sim
- nome:
- tipo: VARCHAR
- tamanho: 256
- url:
- tipo: VARCHAR
- tamanho: 1024
- md5_url:
- usuario_id:
- Extra:
- criar um índice em 2 campos:
- idx1:
- nome: idx1
- tipo de índice: UNIQUE
- campos:
- criar outro índice, desta vez de integridade referencial:
- na estrutura da tabela, escolha "Ver Relações"
- criar o índice idx2:
- nome do índice: idx2
- campo: usuario_id
- Na estrutura da Tabela, "Ver Relações"
- Mapear idx2 para usuario.id:
- em usuario_id, escolher usuario.id
- "ON DELETE": CASCADE
- "ON UPDATE": CASCADE
- Hora de Inserir um Registro:
-
Teste #1 (Vai Falhar): Forçando Erro na Integridade Referencial:
INSERT INTO favorito (nome, url, md5_url, usuario_id)
VALUES
('mnemetica', 'http://www.leal.eng.br/mnemetica/', MD5('http://www.leal.eng.br/mnemetica/'), NULL);- Bate contra um constraint (not-null em usuario_id)!
#1048 - Column 'usuario_id' cannot be null
- Teste #2 (Vai Falhar): Forçando outro erro na Integridade Referencial:
INSERT INTO favorito (nome, url, md5_url, usuario_id)
VALUES
('mnemetica', 'http://www.leal.eng.br/mnemetica/', MD5('http://www.leal.eng.br/mnemetica/'), 0);- Bate contra outro constraint (chave estrangeira)!
#1452 - Cannot add or update a child row: a foreign key constraint fails (`favoritos/favorito`, CONSTRAINT `favorito_ibfk_1` FOREIGN KEY (`usuario_id`) REFERENCES `usuario` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
Teste #3 (Agora vai funcionar): Obedecendo a Integridade Referencial:
INSERT INTO favorito (nome, url, md5_url, usuario_id) VALUES ('mnemetica', 'http://www.leal.eng.br/mnemetica/', MD5('http://www.leal.eng.br/mnemetica/'), 1);-
Funciona!
- Teste #4 (Vai funcionar): Testando o índice de unicidade e adicionando outro bookmark
- Sem problemas!
- Teste #5 (Vai Falhar): Duplicando aonde existe uma constraint de unicidade!
Repita a penúltima consulta:
INSERT INTO favorito (nome, url, md5_url, usuario_id) VALUES ('mnemetica', 'http://www.leal.eng.br/mnemetica/', MD5('http://www.leal.eng.br/mnemetica/'), 1);- Vai falhar!
#1062 - Duplicate entry 'affa04834c3f1b44a864bf55a7f15d96-1' for key 2
-
Tudo ok então!
- tag, 2 campos:
- criar a tabela:
- id:
- tipo: INT
- extra: AUTO_INCREMENT
- primary key: sim
- nome:
- tipo: VARCHAR
- tamanho: 32
- unique: sim
- inserir vários registros em um único insert:
INSERT INTO tag (nome) VALUES ('weblog'), ('leal.eng.br'), ('mnemetica'), ('aldrin'), ('leal'), ('photo'), ('album'); - testar constraint de unicidade:
INSERT INTO tag (nome) VALUES ('weblog');- Vai Falhar:
#1062 - Duplicate entry 'weblog' for key 2
- tag_favorito, 4 campos:
- criar a tabela:
- id:
- tipo: INT
- extra: AUTO_INCREMENT
- primary key: sim
- tag_id:
- favorito_id:
- md5_tag_favorito:
- tipo: CHAR
- tamanho: 32
- unique: sim
- adicionar índices:
- tag_id (1 campo):
- tipo: INDEX
- campo: tag_id
- nome: em branco
- favorito_id (1 campo):
- tipo: INDEX
- campo: favorito_id
- nome: em branco
- referencia-los:
- na estrutura da tabela, escolha "Ver Relações"
- Mapear tag_id para tag.id:
- em tag_id, escolher "tag.id"
- "ON DELETE": CASCADE
- "ON UPDATE": CASCADE
- Mapear favorito_id para favorito.id:
- em favorito_id, escolher "favorito.id"
- "ON DELETE": CASCADE
- "ON UPDATE": CASCADE
- inserir usando insert select e campos:
INSERT INTO
tag_favorito (tag_id, favorito_id, md5_tag_favorito)
SELECT
tag.id,
favorito.id,
MD5(CONCAT(tag.id, ':', favorito.id))
FROM
tag,
favorito
WHERE
(favorito.url = 'http://www.leal.eng.br/mnemetica/' AND tag.nome IN ('weblog', 'leal.eng.br', 'aldrin', 'leal', 'mnemetica')) OR
(favorito.url = 'http://www.leal.eng.br/gallery2/' AND tag.nome IN ('album', 'aldrin', 'leal', 'photo', 'album'))- Analizando o Resultado:
- Executa o SELECT da Seguinte Forma:
- Varre a Tabela tag
- Varre a Tabela favorito
- Cria todas as permutações possíveis de resultados nas duas tabelas
- Para cada permutação, aplica a expressão contida na cláusula WHERE
- Quando a expressão for verdadeira naquela permutação, retorna o resultado e aplica as funções descrita na relação de campos SELECT a, b, c, como a função CONCAT e depois a função MD5
- De posse do resultado do SELECT, efetua o INSERT
- Pra entender melhor:
Execute somente a consulta, mas especificando mais detalhes pra ajudar o entendimento:
SELECT tag.id AS "tag.id", favorito.id AS "favorito.id", tag.nome AS "tag.nome", favorito.url AS "favorito.url", CONCAT( tag.id, ':', favorito.id ) AS "chave_sintetica", MD5( CONCAT( tag.id, ':', favorito.id ) ) AS "md5_chave_sintetica"
FROM tag, favorito
WHERE (
favorito.url = 'http://www.leal.eng.br/mnemetica/'
AND tag.nome
IN (
'weblog', 'leal.eng.br', 'aldrin', 'leal', 'mnemetica'
)
)
OR (
favorito.url = 'http://www.leal.eng.br/gallery2/'
AND tag.nome
IN (
'album', 'aldrin', 'leal', 'photo', 'album'
)
)- Explicação Adicional:
Eu posso especificar uma tabela, e ele vai varrer ela toda. Eu posso especificar duas tabelas, e ele vai fazer produto cartesiano delas. Quando tenho índices, eles serão usados - O que é bom. Por isso, é importante checar se os campos que espeficicamos na cláusula WHERE estão sendo indexados. Quem diz isso é o EXPLAIN, que informa pro usuário como uma consulta SQL será executada. Repara que não necessariamente o que precisa ser retornado é uma coluna: Eu posso aplicar expressões como sendo resultado de uma coluna (CONCAT...) e aplicar aliases ao resultado de uma coluna (AS "nome_da_coluna")
- Teste os Constraints: Reexecute a consulta de INSERT SELECT:
- Erro!
#1062 - Duplicate entry 'd0bd571dc19c083d82f023c9666c5574' for key 2
- Quando encontrar problemas...
- Leia a Documentação da Versão que Rodas:
- Criar as Consultas Principais de Dados:
- Obter Dados de um Usuário:
- Por e-mail:
SELECT id, email, login, senha FROM usuario WHERE 1 /* AND email='%[EMAIL]%'*/
- Por login:
SELECT id, email, login, senha FROM usuario WHERE 1 /* AND login='%[LOGIN]%'*/
- Validar a Senha de um Usuário: ("AUTENTICAR: USUARIO")
SELECT id, email, login, senha FROM usuario WHERE 1 /*AND MD5('%[senha]%')=usuario.senha AND usuario.id=%[id]% */ - Cadastrar um Usuario:
INSERT INTO usuario (email, login, senha)
VALUES
('email', 'login', MD5('senha'))
