MYSQL relationship between tables

Asked

Viewed 367 times

1

I’m not very good with database relationship and am starting my studies.

I wonder why developers shorten when calling tables or if this is the mandatory?

SELECT p.*, c.`nome` AS categoria, u.`nome` AS usuario FROM `produtos` AS p
INNER 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 BY p.`nome` ASC

this is very confusing, I know that there he is joining 2 tables in a query but I do not understand this: 

 p.* , p.´nome´,  c.`nome`, u.`nome`
mysql

  • Rafael, it’s not mandatory! I prefer to put names in the table because I don’t need to write all your text. The idea is to abbreviate.

3 answers

4

This "alias" is usually created for tables and columns to make it easier to reference them again in the query.

For example, instead of writing category every time, just write c. This is not required, but when there are tables you want to relate that have columns with the same name, when you use these columns you need to tell which table and then it makes sense to use the alias.

3


Item p.* means to display all fields in the product table. Right after FROM, has written products as p, this is a short name so you don’t need to write products.. How was created the nickname p, you simply reference using p..

The item p.name, references the product table and name column, says it is to sort the result by the product table name column. It is only a short name, could be written product.name, but as I mentioned above, it was assigned a nickname "p" and can be referred to as p.name.

Smart to have helped.

  • 1

    Now I understand.. Thank you very much!

  • Remember that if you did not define the nickname (alias) (produtos as p, categorias c), select should bring the full name of the tables (produtos.nome, categorias.nome), which makes it a little difficult to read when dealing with many fields in select.

2

Tables are usually called to make it easier to write the Script that by syntax requests the use of "Table name" and "Field name" (e.g., category.name) in cases where the field name exists in more than one table.

SELECT p.*, c.nome AS categoria, u.nome AS usuario  
FROM produtos p  
INNER JOIN categorias c ON p.categoria_id = c.id  
INNER JOIN usuarios u ON p.usuario_id = u.id  
WHERE (p.ativo = 1)  
ORDER BY p.nome  
ASC

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.