Relacionamento de Tabelas no MySQL
Relacionamento de Tabelas no MySQL
O relacionamento de tabelas é necessário quando temos mais de uma tabela comO relacionamento de tabelas é necessário quando temos mais de uma tabela com
informações que podem e precisam ser
informações que podem e precisam ser
cruzadas, por exemplo: categorias e produtos…
cruzadas, por exemplo: categorias e produtos…
Cada registro na tabela produtos estará ligado a um registro da tabela categorias.Cada registro na tabela produtos estará ligado a um registro da tabela categorias.
Só pra vocês saberem, existem três
Só pra vocês saberem, existem três níveis de relacionamentoníveis de relacionamento: nosso exemplo será um: nosso exemplo será um
relação de
relação de
1:N
1:N
(fala- (fala-se “um pra N” ou “um para muitos”) onde cada categoria (1) contém
se “um pra N” ou “um para muitos”) onde cada categoria (1) contém
um ou mais produtos (N)… Há
um ou mais produtos (N)… Há
também o
também o
1:1
1:1
onde cada registro de uma tabela (1) está onde cada registro de uma tabela (1) estáligado a um e so
ligado a um e so
mente um registro de outra tabela (1)… E
mente um registro de outra tabela (1)… E
há outro nível de
há outro nível de
relacioname
relacionamento, mais complexo, que nto, mais complexo, que é oé o
N:N
N:N
onde um ou mais registros de uma tabela (N) onde um ou mais registros de uma tabela (N)estão relacionados a um ou mais registros de outra tabela
estão relacionados a um ou mais registros de outra tabela (N), que seria o exemplo de(N), que seria o exemplo de
duas tabelas “produtos” e “tags” onde um produto tem várias tags
duas tabelas “produtos” e “tags” onde um produto tem várias tags
e vários produtos
e vários produtos
pertencem a uma tag.pertencem a uma tag.
Para o nosso exemplo usaremos as tabelas
Para o nosso exemplo usaremos as tabelas
“categorias” e “produtos”:
“categorias” e “produtos”:
01
01CREATECREATE TABLETABLE `categorias` (`categorias` (
02
02 `id` INT`id` INTNOTNOT NULLNULL AUTO_INCREMAUTO_INCREMENT ENT PRIMARYPRIMARYKEYKEY ,,
03
03 `nome` VARCHAR( 255 ) NOT`nome` VARCHAR( 255 ) NOTNULLNULL
04
04) ENGINE = MYISAM;) ENGINE = MYISAM;
05
05
06
06CREATECREATE TABLETABLE `produtos` (`produtos` (
07
07 `id` INT`id` INTNOTNOT NULLNULL AUTO_INCREMAUTO_INCREMENT ENT PRIMARYPRIMARYKEYKEY ,,
08
08 `categoria`categoria_id` _id` INTINTNOTNOTNULLNULL,,
09
09 `nome` VARCHAR( 255 ) NOT`nome` VARCHAR( 255 ) NOTNULLNULL,,
10
10 `preco` DECIMAL( 10,2 ) NOT`preco` DECIMAL( 10,2 ) NOTNULLNULL
11
11) ENGINE = MYISAM;) ENGINE = MYISAM;
E vamos inserir alguns dados para exemplo:
E vamos inserir alguns dados para exemplo:
1
1 -- Extraindo dados da tabela `categorias`-- Extraindo dados da tabela `categorias`
2
2 INSERTINSERT INTOINTO `categorias` VALUES(1, 'Camisetas');`categorias` VALUES(1, 'Camisetas');
3
3 INSERTINSERT INTOINTO `categorias` VALUES(2, 'Canecas');`categorias` VALUES(2, 'Canecas');
4
4
5
5 -- Extraindo dados da tabela `produtos`-- Extraindo dados da tabela `produtos`
6
6 INSERTINSERT INTOINTO `produtos` VALUES(1, 1, 'Camiseta Social', 15.00);`produtos` VALUES(1, 1, 'Camiseta Social', 15.00);
7
7 INSERTINSERT INTOINTO `produtos` VALUES(2, 1, 'Camiseta Regata', 11.99);`produtos` VALUES(2, 1, 'Camiseta Regata', 11.99);
8
8 INSERTINSERT INTOINTO `produtos` VALUES(3, 2, 'Caneca Grande', 12.00);`produtos` VALUES(3, 2, 'Caneca Grande', 12.00);
Reparem que na tabela
Reparem que na tabela
produtos temos uma coluna “especial”, que é
produtos temos uma coluna “especial”, que é
a “categoria_id”
a “categoria_id”
(INT)… Ela é quem ajudará a fazer a relação das duas tabelas… Nessa coluna entrará o
(INT)… Ela é quem ajudará a fazer a relação das duas tabelas… Nessa coluna entrará o
ID da categoria a qual o produto pertence… Ou seja: as duas camisetas pertencem a
categoria “Camisetas” (ID 1) e o terceiro produto (a Caneca Grande) pertence a categoria“Canecas” (ID 2) e é na coluna “categoria_id” que armazenamos esses IDs que identificam
as categorias.Esse campo responsável pela relação é normalmente chamado de “
foreing key” (fk) ou
“chave estrangeira”.
Mas qual a utilidade dessa tal “relação”?
Sem usar o relacionamento você poderia pegar todos os produtos e depois pegar as informações das categorias com uma segunda consulta, assim:
01 <?php 02
03 // Consulta que pega todos os produtos
04 $sql = "SELECT * FROM `produtos` ORDER BY `nome` ASC"; 05 $query = mysql_query($sql);
06 while ($produto = mysql_fetch_assoc($query)) {
07 // Aqui temos o array $produto com todos os valores do produto 08
09 // Consulta para pegar os dados da categoria:
10 $sqlC = "SELECT * FROM `categorias` WHERE `id` = " .$produto['categoria_id'];
11 $queryC = mysql_query($sqlC);
12 $categoria = mysql_fetch_assoc($queryC); 13
14 echo 'Titulo: ' . $produto['nome'] . '<br />'; 15 echo 'Preço: ' . $produto['preco'] . '<br />'; 16 echo 'Categoria: ' . $categoria['nome']. '<br />'; 17 echo '<hr />';
18 } 19 20 ?>
Até aí tudo bem… Não tem nenhum pecado nisso… Mas imagine que você tem uma loja
com 1000 produtos (o que não é muito), seria executada 1 consulta para todos os produtos e, dentro do loop (while) seriam executadas outras 1000 consultas para pegar o nome dacategoria a qual o produto pertence… Ou seja, 1001 consultas, o que é um absurdo.
A mágica da relação
Agora vamos montar uma consulta que
DE UMA SÓ VEZ
irá pegar os dados de cadaproduto e também o nome da categoria… Com isso reduziremos nossas 1001 consultas
pra… uma só! Sem mistérios, sem sub
-consultas, nem consultas dentro dowhile()
!Mas antes de mostrar o script vou ajudar a vocês entenderem como a relação é feita…
Antes a nossa consulta que pega apenas os produtos era assim:SELECT * FROM `produtos` ORDER BY `nome` ASC
Sua tradução seria:
SELECIONAR todas as colunas da TABELA `produtos`
ORDENADO PELO `nome` ASCENDETEMENTE
Agora usaremos uma nova “palavra” do MySQL que é o
JOIN
(tradução: “unir”) e
serve para unir resultados de duas tabelas..Existem três tipos de JOIN mas não vou falar dos outros dois pois eles são MUITO pouco
usados… Falaremos do “
INNER JOIN
” que exige que haja um registro que corresponda a
relação nas duas tabelas, ou seja: se houver um produto sem categoria ou a categoria não existir na tabela categorias esse produto é omitido dos resultados.A nossa consulta ficará assim:
SELECT `produtos`.* FROM `produtos` INNER JOIN `categorias` ON`produtos` .`categoria_id` = `categorias`.`id` ORDER BY `produtos`.`nome` ASC
Sua tradução seria:
SELECIONAR todas as colunas [da tabela produtos] da TABELA
`produtos` UNINDO A TABELA `categorias` ONDE a coluna `categoria_id` [da tabela
produtos] É IGUAL a coluna `id` [da tabela categorias] ORDENADO PELO `nome` [da
tabela produtos] ASCENDETEMENTE
A nossa “regra de relação” acontece ali entre o ON e o ORDER BY, dizemos que a relação
entre as tabelas usará como referencia a coluna “categoria_id” da tabela “produtos” sendo
igual a coluna “id” da tabela “categorias”… Se você fosse usar algum WHERE ele entraria
depois do ON e antes do ORDER BY.Pra quem ainda não entendeu, o ON
é como o WHERE de uma consulta normal… É a
regra da relação.Repare que agora precisamos usar um formato diferente para identificar as colunas
usando:
`tabela`.`coluna`
… Isso é necessário pois agora estamos trabalhando com duas
tabelas.Da forma que a nossa consulta está ainda não estamos pegando o nome da categoria…
fazemos isso adicionando mais um campo na parte do SELECT, assim:SELECT `produtos`.*,
`categorias`.`nome` FROM `produtos` INNER JOIN`categorias` ON `produtos `.`categoria_id` = `categorias`.`id` ORDER BY`produtos`.`nome` ASC
Agora estamos pegando também o valor da coluna “nome” do registro encontrado (pela relação) na tabela “categorias”.
Só que agora temos um novo problema… Nas duas tabelas existe uma coluna chamada
“nome”, e quando estivermos lá no PHP, dentro do while, não teríamos
como identificar de qual tabela pegamos as informações (veja a próxima imagem), pois as duasseriam
$produto['nome']
… Precisamos então renomear esse novo campo que
adicionamos a busca, assim:SELECT `produtos`.*,
`categorias`.`nome` AS categoria FROM `produtos` INNERJOIN `categorias` ON `produtos`.`categoria_id` =
`categorias`.`id` ORDER BY`produtos`.`nome` ASC
Agora o resultado de `categorias`.`nome` estará presente nos resultados como “categoria”
e não “nome”… Sei que parece complicado de início mas vocês vão entender já já.
E por fim, faremos mais uma modificação, pra evitar ficar usando `tabela`.`coluna` também podemos renomear as tabelas, e com isso diminuir otamanho da consulta:
SELECT p.*,
c.`nome` AS categoria FROM `produtos` AS p INNER JOIN`categorias` AS c O N p.`categoria_id` = c.`id` ORDER BY p.`nome` ASC
Sei que parece uma consulta maior e mais complicada… Mas você fará o MySQL
trabalharmuito menos se fizer assim, com JOINS, do que fazer uma 2ª consulta dentro do
while… Essa é a forma mais correta de fazer consultas quando precisamos de
informações vindas de mais de uma tabela.
Agora vamos ao nosso novo script de PHP que, sem dúvidas, é bem mais prático e eficiente:
01 <?php 02
03// Consulta que pega todos os produtos e o nome da categoria de cada um
04
$sql = "SELECT p.*, c.`nome` AS categoria FROM `produtos` AS p INNER JOIN `categorias` AS c ON p.`categoria_id` = c.`id` ORDER BY
p.`nome` ASC";
05 $query = mysql_query($sql);
06 while ($produto = mysql_fetch_assoc($query)) {
07 // Aqui temos o array $produto com todos os dados encontrados 08 echo 'Titulo: ' . $produto['nome'] . '<br />';
09 echo 'Preço: ' . $produto['preco'] . '<br />';
10 echo 'Categoria: ' . $produto['categoria']. '<br />'; 11 echo '<hr />';
12 } 13 14 ?>
Os outros tipos de JOINs
Existem também outros dois tipos de JOIN: o
LEFT JOIN
e oRIGHT JOIN
:Se usássemos o
LEFT JOIN
seriam retornados todos os produtos, independente se eles estão ligados a uma categoria (na tabela categorias) existente ou não.Já o
RIGHT JOIN
seria exatamente o contrário: seriam retornados todos os produtos que pertencem categorias existentes e também o nome das outras categorias que não tem ligação com nenhum produto.O uso desses outros tipos de JOIN é muito raro e acho que não vale a pena ficar filosofando sobre eles enquanto se aprende sobre relacionamentos.
Só pra exemplo, essa seria a consulta que pega os produtos, as categorias e o nome do usuário que cadastrou o produto e filtrando apenas pelos produtos ativos:
SELECT p.*, c.`nome` AS categoria,
u.`nome` AS usuario FROM `produtos` AS pINNER JOIN `categorias` AS c ON p .`categoria_id` = c.`id` INNER JOIN`usuarios` AS u ON p.`usuario_id` = u.`id` WHERE (p.`ativo` = 1) ORDER BYp.`nome` ASC