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

Question

Speaking of variables that can be declared within the begin, I saw that you have Declare e set only that I don’t know when to use them. I saw that you have set @x, set x, set x = x+1, set x := x+1; they all confuse me and I don’t know what they’re for.

I have this code that is to get the code of the product that each customer more bought. I wanted him to take the first client of each code and show it, because I ordered it down.

You’re leaving as follows: product name, code Product, client, qtdComprada;

ABAJUR             15    JOSE TAVARES DE OLUVEIRA    2

LAPISEIRA 1.2      10    JOSE TAVARES DE OLUVEIRA    1

CAIXA DE SOM       16    JOSE TAVARES DE OLUVEIRA    1

CANETA VERMELHA     3    MANOEL JOAQUIM PURTUGA      2

LAPISEIRA 0.9       9    MANOEL JOAQUIM PURTUGA      1

I wanted to show the first line of each customer.

   /*Crie uma store procedure que informe o produto mais comprado de cada cliente*/
    DELIMITER $$
    DROP PROCEDURE IF EXISTS uspProdutoMaisCompradoCliente $$
    CREATE PROCEDURE uspProdutoMaisCompradoCliente () 

    BEGIN

    DECLARE done BOOLEAN DEFAULT FALSE;
    DECLARE vNomeProd VARCHAR(250);
    DECLARE vNomeCli VARCHAR(250);
    DECLARE vQtdComprProd INT(11);
    DECLARE contador int;

    DECLARE cursor_a CURSOR FOR (
    select produto.nome as nomeProd, cliente.nome as cliente,
    count(produto.codigo) as qtdComprProd
    from cliente
    inner join nota_fiscal
    on cliente.codigo = nota_fiscal.cod_cliente
    inner join item_nota_fiscal
    on item_nota_fiscal.numero_nf = nota_fiscal.numero_nf
    inner join produto
    on produto.codigo = item_nota_fiscal.cod_produto
    group by produto.nome , produto.codigo , cliente.nome , cliente.codigo
    order by cliente.nome, qtdComprProd desc
    );

    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = TRUE;

    DROP TEMPORARY TABLE IF EXISTS tempTabela;
    CREATE TEMPORARY TABLE tempTabela (
       tNomeProd VARCHAR(250),
      tNomeCli VARCHAR(250),
       tQtdComprProd INT(11)
    );

    OPEN cursor_a;

    REPEAT
    FETCH  cursor_a INTO vNomeProd,vNomeCli, vQtdComprProd;

    IF NOT done THEN

         INSERT INTO tempTabela VALUES (vNomeProd, vNomeCli, vQtdComprProd);

    END IF;

    UNTIL done END REPEAT;
    close cursor_a;
    SELECT * FROM tempTabela;
    END $$
    DELIMITER ;

`tabelas`:
`CREATE TABLE ESTADO (
  ESTADO VARCHAR(02) NOT NULL,
  NOME VARCHAR(100) NOT NULL,
  PRIMARY KEY (ESTADO)
) ;`

`CREATE TABLE CIDADE (
  CODIGO INTEGER NOT NULL,
  NOME VARCHAR(250) NOT NULL,
  ESTADO VARCHAR(02),
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (ESTADO) REFERENCES ESTADO (ESTADO)
) ;`

`CREATE TABLE CLIENTE (
  CODIGO INT NOT NULL AUTO_INCREMENT,
  NOME VARCHAR(250) NOT NULL,
  RUA VARCHAR(250),
  NUMERO VARCHAR(10),
  COD_CIDADE INTEGER,
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (COD_CIDADE) REFERENCES CIDADE (CODIGO)
);`

`CREATE TABLE FORNECEDOR (
  CODIGO INTEGER NOT NULL,
  NOME VARCHAR(250) NOT NULL,
  RUA VARCHAR(250),
  NUMERO VARCHAR(10),
  COD_CIDADE INTEGER,
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (COD_CIDADE) REFERENCES CIDADE (CODIGO)
);`

`CREATE TABLE PRODUTO (
  CODIGO INTEGER NOT NULL,
  NOME VARCHAR(250) NOT NULL,
  PCO_ATUAL_CPA DECIMAL(10,2),
  PCO_ATUAL_VDA DECIMAL(10,2),
  QTD_ESTOQUE INTEGER,
  QTD_EST_MIN INTEGER,
  QTD_EST_MAX INTEGER,
  QTD_PTO_CPA INTEGER,
  IDF_ATIVO_SN VARCHAR(01) DEFAULT 'S',
  COD_FORNECEDOR INTEGER,
  PRIMARY KEY (CODIGO),
  FOREIGN KEY (COD_FORNECEDOR) REFERENCES FORNECEDOR (CODIGO)
) ;`

`CREATE TABLE NOTA_FISCAL (
  NUMERO_NF INTEGER NOT NULL,
  DTA_VENDA DATE NOT NULL,
  COD_CLIENTE INTEGER,
  VALOR_TOTAL DECIMAL(10,2),
  PRIMARY KEY (NUMERO_NF),
  FOREIGN KEY (COD_CLIENTE) REFERENCES CLIENTE (CODIGO)
) ;`

`CREATE TABLE ITEM_NOTA_FISCAL (
  NUMERO_NF INTEGER NOT NULL,
  COD_PRODUTO INTEGER NOT NULL,
  QTD_VEDIDA INTEGER,
  PCO_RECEBIDO DECIMAL(10,2),
  PRIMARY KEY (NUMERO_NF, COD_PRODUTO),
  FOREIGN KEY (NUMERO_NF) REFERENCES NOTA_FISCAL (NUMERO_NF),
  FOREIGN KEY (COD_PRODUTO) REFERENCES PRODUTO (CODIGO)
) ;`

Answer 1

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

First I used your own query to list the amount 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 there I created a VIEW to facilitate the query that will give you the desired data, the most purchased product per customer:

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 view Join with the MAX of the quantity purchased per customer, remembering that if a customer buys two different products in the same quantity the query will bring the two 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: http://sqlfiddle.com/#! 9/82ac0/1

EDIT

A view is nothing more than a database object that represents a set of data 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 that you use often. Instead of writing this giant query every time you need to use it, you will 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 you rewriting this entire query, every time you need it, you will simply create a VIEW to represent this entire query, that way:

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 will only need to select in your view, that way:

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 view types for this.

A good reference on views: Working with Views

For variables, they are used to store values temporarily during the runcycle of a trial.

They work exactly like a variable in any programming language.

As to their definition, they can be defined in the ways you used in different situations:

When you set a variable with set @a := 1 or set @a = 1 (whatever = or :=) you are assigning the value 1 variable @a and it will exist in the session scope of your connection, which means that you can execute an assignment and use this variable in an 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 the @ is usually in an anonymous block (begin ... código end;) or within a precedent or function, in these two cases, in Mysql declare serves to indicate that the variable exists and the 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 set a session variable and a Procedure variable in this second example, the session variable is passed to the Procedure by reference and comes back with the value filled after select.

I hope you understand.