LimaReview

***########## lima hash ##########***

X mod 7
valor | key[Posição]
7 | 0
14 | 0
21 | 0
28 | 0
35 | 0
42 | 0

X mod 14
valor | key[Posição]
7 | 7
14 | 0
21 | 7
28 | 0
35 | 7
42 | 0

X mod 5
valor | key[Posição]
7 | 2
14 | 4
21 | 1
28 | 3
35 | 0
42 | 2


***##########  10 função concatenar: ##########***

CREATE FUNCTION fn_usuario_email (a SMALLINT)
RETURNS VARCHAR(60)
RETURN
(SELECT CONCAT('O usuario id:',a,' Nome:', nm_usuario,' tem o email: ', email_usuario) FROM tb_usuario
WHERE id_usuario = a);

***########## 7 Transação: ##########***
BEGIN;
START TRANSACTION;
INSERT INTO tb_usuario(id_usuario, nm_usuario, email_usuario) VALUES (2,"vend_1","123","vend1");
INSERT INTO tb_usuario(id_usuario, nm_usuario, email_usuario) VALUES (3,"vend_2","123","vend1";
INSERT INTO tb_usuario(id_usuario, nm_usuario, email_usuario) VALUES (4,"vend_3","123","vend1");

UPDATE tb_usuario SET email_usuario = 'vend1-troca' WHERE id_usuario = 2;
UPDATE tb_usuario SET email_usuario = 'vend2-troca' WHERE id_usuario = 3;
UPDATE tb_usuario SET email_usuario = 'vend3-troca' WHERE id_usuario = 4;


DELETE FROM tb_usuario WHERE id_usuario = 2;
SAVEPOINT saveIU;
DELETE FROM tb_usuario WHERE id_usuario = 3;
DELETE FROM tb_usuario WHERE id_usuario = 4;

ROLLBACK TO saveIU ;

COMMIT;

***########## 8: STORED PROCEDURE ##########***

DELIMITER $$

CREATE PROCEDURE procedimento1()
BEGIN
DECLARE totalClientes INT DEFAULT 0;
DECLARE soma INT DEFAULT 0;
DECLARE contador INT DEFAULT 0;

SELECT COUNT(*)
INTO totalClientes
FROM tb_cliente;

if(totalClientes>0)THEN
loop_teste: LOOP
SET contador = contador + 1;
SET soma = soma + contador;
IF contador >= totalClientes THEN
LEAVE loop_teste;
END IF;
END LOOP loop_teste;
SELECT soma as resultado;
ELSE
SELECT "SEM LINHAS" as resultado;
END IF;
END$$

DELIMITER ;

***########## 5 NEW e OLD ##########***

DELIMITER $
CREATE TRIGGER upd_check AFTER UPDATE ON vendas
FOR EACH ROW
BEGIN
INSERT INTO log_vendas values(null,CONCAT("Tabela Alterada - Preço antigo:",OLD.PrecoNormal, "Preco novo:",NEW.PrecoNormal),now());
END$


***########## 6) PERMISSÕES ##########***

*1)mariarosa*

CREATE USER 'mariarosa'@'localhost' IDENTIFIED BY '1234567';
GRANT ALL PRIVILEGES ON * . * TO 'mariarosa'@'localhost';
FLUSH PRIVILEGES;

*2)analuisa*

CREATE USER 'analuisa'@'localhost' IDENTIFIED BY '1234567';
GRANT ALL PRIVILEGES ON erp_fmu.vendas TO 'analuisa'@'localhost';
FLUSH PRIVILEGES;

*3)karen*

CREATE USER 'karen'@'localhost' IDENTIFIED BY '1234567';
GRANT SELECT (Descricao), SELECT (PrecoNormal) ON erp_fmu.vendas TO 'karen'@'localhost';
FLUSH PRIVILEGES;

*4) mostrar privilegios karen*

SHOW GRANTS FOR karen@localhost;

*5)remover permissoes da karen*

REVOKE ALL, GRANT OPTION
FROM karen@localhost;

*6) remover karen completamente*

DROP USER 'karen'@'localhost';