variables in mysql: declare, set, how to get an element from a table

Given its structure, it is necessary to find the quantity of products purchased by each customer, this involves the field QTD_VEDIDA of the ITEM_NOTA_FISCAL table so a simple count in the product will not return the correct information. >

First I used your own query to list the quantity of products purchased per customer.

select p.nome as nomeProd,
       p.codigo, 
       c.nome as cli,
       c.codigo as codcli,
       sum(inf.qtd_vedida) as qtdComprProd
  from cliente c
       inner join nota_fiscal nf on c.codigo = nf.cod_cliente
       inner join item_nota_fiscal inf on inf.numero_nf = nf.numero_nf
       inner join produto p on p.codigo = inf.cod_produto
group by p.nome, p.codigo, c.nome, c.codigo
order by c.nome, qtdComprProd desc;

From here I've created a VIEW to facilitate the query that will give you the desired data, the most purchased product per client:

create or replace view vw_cli_prod_qtd as 
select p.nome as nomeProd,
       p.codigo, 
       c.nome as cli,
       c.codigo as codcli,
       sum(inf.qtd_vedida) as qtdComprProd
  from cliente c
       inner join nota_fiscal nf on c.codigo = nf.cod_cliente
       inner join item_nota_fiscal inf on inf.numero_nf = nf.numero_nf
       inner join produto p on p.codigo = inf.cod_produto
group by p.nome, p.codigo, c.nome, c.codigo
order by c.nome, qtdComprProd desc;

Then just make a join of the view with the MAX of the quantity purchased per customer, remembering that if a customer buys two different products in the same amount the query will bring both records.

select v1.* 
  from vw_cli_prod_qtd v1
        inner join (
            select codcli, max(qtdComprProd) qtd
              from vw_cli_prod_qtd
             group by codcli 
        ) v2 on (v1.codcli = v2.codcli and v1.qtdComprProd = v2.qtd)

You can see everything working here: link

EDIT

A view is nothing more than a database object that represents a dataset from a select .

As an example, imagine that you have a query that involves many tables, many joins, many left joins, etc. And it's a query you use often. Instead of writing this giant query every time you need to use it, you'll create a view that represents that query. Let's take a practical example:

Imagine the following query:

select a.campo1, b.campo2, c.campo3, d.campo4, e.qtd
  from tabelaA a 
         INNER JOIN tabelaB ON (a.id = b.aid)
         INNER JOIN tabelaC ON (b.id = c.bid)
         INNER JOIN tabelaD ON (c.id = d.did)
         LEFT JOIN (select campo1, count(*) qtd 
                      from outraTabela
                     group by campo1) e 
                ON (a.campo1 = e.campo1)
 where d.data between a.dt_inicio and a.dt_fim

Instead of retyping all this query, every time you need it, you will simply create a VIEW to represent the entire query, like this:

create or replace view MINHA_VIEW_EVITA_MUITA_DIGITACAO AS
select a.campo1, b.campo2, c.campo3, d.campo4, e.qtd
  from tabelaA a 
         INNER JOIN tabelaB ON (a.id = b.aid)
         INNER JOIN tabelaC ON (b.id = c.bid)
         INNER JOIN tabelaD ON (c.id = d.did)
         LEFT JOIN (select campo1, count(*) qtd 
                      from outraTabela
                     group by campo1) e 
                ON (a.campo1 = e.campo1)
 where d.data between a.dt_inicio and a.dt_fim        

And when you need to use this query again, you just need to make the select in your view, like this:

select campo1, campo2, campo3, campo4, qtd
  from MINHA_VIEW_EVITA_MUITA_DIGITACAO

Your view becomes a table from a query and can be used as a table, of course not for insert, update or delete there are other types of view for this.

A good reference on views: Working with Views

As for the variables, they are used to store values temporarily during the execution cycle of a procedure.

They function exactly like a variable in any programming language.

As for your definition, they can be defined in the ways you have used in different situations:

When you define a variable with set @a := 1 or set @a = 1 (either = or := ) you are assigning the 1 value to the @a variable and it will exist in the session scope of your connection , which means that you can execute an assignment and use this variable in a sql command in the same session (connection) for example:

mysql> SET @t1=1, @t2=2, @t3:=4;
mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3;
+------+------+------+--------------------+
| @t1  | @t2  | @t3  | @t4 := @t1+@t2+@t3 |
+------+------+------+--------------------+
|    1 |    2 |    4 |                  7 | 
+------+------+------+--------------------+

A variable created without @ is usually in an anonymous block ( begin ... código end; ) or within a procedure or function, in these two cases, in MySql declare is to indicate that the variable exists and set to assign a value to it, for example:

delimiter $$ --sintaxe obrigatória para mysql
begin
    declare varA int; -- criei a variável varA
    set varA = 1; -- atribui o valor 1 a varA
end$$
delimiter;

Another example:

delimiter //
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
   declare x int;
   SELECT COUNT(*) INTO param1 FROM t; --digamos que retorne 3
   set x = 10;
   set param1 = param1 + x;
END//
delimiter ;

CALL simpleproc(@a);
SELECT @a;
+------+
| @a   |
+------+
| 13   |
+------+

See that I defined a session variable and a procedure variable in this second example, the session variable is passed to the procedure by reference and returns with the value filled in after the select.

I hope you have understood.