Aprenda MySQL

Tudo sobre MySQL Parte 6

Estruturas de Repetição - While

O comando while primeiro faz a condição de teste e depois executa os comandos que estiver dentro dele, caso a condição retorne verdadeiro, senão ele não executa nada.

Para criar um procedimento com laço while, fazemos assim:


delimiter //
create procedure acumula_while(limite tinyint unsigned)
begin
    declare contador tinyint unsigned default 0;
    declare soma int default 0;
    while contador < limite do
        set contador = contador + 1;
        set soma = soma + contador;
    end while;
    select soma as 'Soma Total do While';
end//
delimiter ;

E para executar:


call acumula_while(10);

Exclua o procedimento caso não o use mais.

Estruturas de Repetição - Iterate

Iterate significa dentro de uma estrutura de repetição "inicie o loop novamente". A declaração Iterate aparece apenas dentro de estruturas loop, repeat e while.

Veja um exemplo abaixo:


delimiter //
create procedure acumula_iterate(limite tinyint unsigned)
begin
    declare contador tinyint unsigned default 0;
    declare soma int default 0;
    teste: loop
        set contador = contador + 1;
        set soma = soma + contador;
        if contador < limite then
            iterate teste;
        end if;
        leave teste;
    end loop teste;
    select soma as 'Soma Total do Iterate';
end//
delimiter ;

No caso, quando o iterate é executado, ele para o laço nesse ponto e volta pro começo do laço, que executará novamente o laço (como o continue de algumas linguagens), até ele ser interrompido pelo leave (para evitar loop infinito).

E para executar:


call acumula_iterate(10);

Veja outro exemplo, mas com o while:


delimiter //
create procedure pares(limite tinyint unsigned)
main: begin
    declare contador tinyint default 0;
    meulaco: while contador < limite do
        set contador = contador + 1;
        if mod(contador, 2) then
            iterate meulaco;
        end if;
        select concat(contador, ' é par!') as 'Valor';
    end while;
end//
delimiter ;

E para executar:


call pares(20);

Depois da utilização, exclua os procedimentos:


drop procedure if exists acumula_iterate;
drop procedure if exists pares;

Triggers - Definição, Sintaxe e Criação

Os triggers (significando gatilho) é um objeto SQL associado a uma tabela, é uma espécie de procedimento invocado quando um comando DML é executado (ou seja, comandos insert, update e delete).

Esses são alguns usos do trigger:

Verificação de integridade dos dados.
Validação dos dados.
Rastreamento e registro de logs de atividades nas tabelas.
Arquivamento de registros excluídos.

Um trigger é associado a uma tabela, armazenado no banco de dados como um arquivo separado, não são chamados diretamente, são invocados automaticamente.

Crie essa tabela como exemplo:


create table produto (
    id int not null auto_increment,
    item varchar(45) null,
    preco_normal decimal(10, 2) null,
    preco_desconto decimal(10, 2) null,
    primary key(id)
)
default charset = utf8;

E para criar o trigger, fazemos assim:


create trigger tr_desconto before insert on produto
for each row
    set new.preco_desconto = (new.preco_normal * 0.90);

Na hora que inserirmos um cadastro na table produto, o trigger vai disparar automaticamente, vai fazer o cálculo e inserir na coluna especificada da tabela.

PS: em outros bancos de dados isso pode não funcionar, no entanto podemos fazer o mesmo trigger dessa forma em outros bancos:


create trigger tr_desconto after insert on produto
begin
    update produto set preco_desconto = (new.preco_normal * 0.90) where id = (select count(id) from produto);
end

Insira um cadastro assim:


insert into produto (item, preco_normal) values ('Monitor', 350.00);

Para ver se o trigger realmente fez o cálculo, basta dar um select na tabela:


select * from produto;

PS: o new no comando trigger, significa que é para registros que ainda não foram, mas serão inseridos no banco de dados. Em casos como o delete, não é necessário usar o new.

Para excluir um trigger, faça assim:


drop trigger tr_desconto;

Gerenciamento de Usuários do sistema – Criar, Consultar, Renomear e Excluir

Abra o terminal do sistema ou do XAMPP e digite os comando para entrar nele.

Para visualizar os usuários e seus hosts cadastrados no MySQL, digite isso:


select user, host from mysql.user;

Para criar um novo usuário, podemos fazer assim:


create user usuario@localhost identified by '1234';

No caso acima, seria o nome de usuário seguido do arroba e o local a partir do qual ele vai logar. O que está dentro das aspas seria a senha, que pode ser qualquer uma.

PS: Também podemos criar usuários sem senha.

Dê outro select assim:


select user, host from mysql.user;

Para criar um usuário que possa acessar remotamente, basta não identificar de onde ele logará, por exemplo:


create user user2 identified by '1234';

Veja que o usuário criado acima, o host é identificado por um %, que significa acesso de qualquer local.

Para criar um usuário sem senha, faça assim:


create user user3@localhost;

Para inserir uma senha posteriormente nesse mesmo usuário, fazemos assim:


set password for 'user3'@'localhost' = password('1234');

Para renomear um usuário, fazemos assim:


rename user user2 to fulano;

PS: Pode ser necessário colocar o host também com um arroba, após o nome.

Para excluir usuários, fazemos assim:


drop user 'user3'@'localhost';
drop user fulano;

Definindo Privilégios de Acesso com Grant e Revoke

Ainda no terminal do MySQL, dê um select nos usuários do banco. Depois use o comando show grants para ver os privilégios deles, assim:


select user, host from mysql.user;
show grants for usuario@localhost;

Ali vemos dados como o hash da senha, o privilégio (definido como usage) e os locais onde ele tem esse privilégios (sempre é o nome banco de dados seguido do nome da tabela, tipo banco.tabela, quando é total, é mostrado *.*).

Esses são os privilégios mais comuns em MySQL:

Privilégio	Descrição
INSERT	Inserir dados em uma tabela
UPDATE	Atualizar dados em uma tabela
DELETE	Excluir dados de uma tabela
EXECUTE	Executar funções ou procedimentos armazenados
SELECT	Efetuar consultas em uma tabela

Privilégios para modificar a estrutura do banco de dados:

Privilégio	Descrição
CREATE	Criar tabela ou banco de dados
ALTER	Modificar uma tabela
DROP	Excluir uma tabela ou um banco de dados
CREATE VIEWS	Criar exibições
TRIGGER	Criar ou excluir um trigger em uma tabela
INDEX	Criar ou excluir um índice
CREATE ROUTINE	Criar uma função ou um procedimento armazenado
ALTER ROUTINE	Alterar ou excluir uma função ou procedimento armazenado

Privilégios Administrativos:

Privilégio	Descrição
CREATE USER	Criar contas de usuários
SHOW DATABASES	Ver os nomes dos bancos de dados no servidor
SHUTDOWN	Desligar o servidor
RELOAD	Recarregar as tabelas que armazenam os privilégios dos usuários dos bancos de dados. Assim elas são atualizadas se tiverem sido modificadas.

Outros:

Privilégio	Descrição
ALL	Todos os privilégios disponíveis em um determinado nível, exceto GRANT OPTION
GRANT OPTION	Permite dar privilégios a outros usuários
USAGE	Não altera privilégios; usado para tarefas administrativas na conta do usuário.

Sabendo disso, sabemos que o usuário criado por nós não tem privilégio nenhum.

No MySQL os privilégios são atribuídos em quatro níveis diferentes:

Global - O usuário tem acesso a todas as tabelas de todos os bancos de dados.
Database - Esse privilégio dá ao usuário acesso a todas as tabelas de um banco de dados específico.
Table - O usuário tem acesso a todas as colunas de uma tabela específica em um banco de dados.
Column - O usuário possui acesso apenas a colunas especificadas em uma determinada tabela.

O MySQL utiliza tabelas especiais denominada grant tables para armazenar informações sobre os privilégios dos usuários, em um banco de dados interno de nome mysql. A tabela a seguir detalha essas tabelas de privilégios:

Tabela	Descrição
user	Armazena nomes e senhas de todos os usuários do servidor. Também armazena os privilégios globais que são aplicados a todos os bancos de dados do servidor.
db	Armazena privilégios dos bancos de dados
tables_priv	Armazena privilégios das tabelas
columns_priv	Armazena privilégios de colunas
procs_priv	Armazena privilégios de acesso a funções e stored procedures (procedimentos armazenados).

Podemos também criar um usuário sem privilégio algum dessa forma:


grant usage on *.* to novouser@localhost identified by '1234';

E depois, para ver:


show grants for novouser@localhost;

Para criar um usuário com todos os privilégios, ao invés de usage, usamos all, assim:


grant all on *.* to usertotal identified by '1234' with grant option;

PS: No caso acima, o usuário criado tem acesso a todos os bancos e tabelas, por padrão, mesmo com all ele não cria com o privilégio option, mas o with especifica que a criação é com esse privilégio também (ou seja, completão mesmo).

Agora crie um usuário novo assim:


create user userqualquer@localhost;

Para que esse usuário possa consultar, inserir, alterar e excluir dados num banco especifico, fazemos assim:


grant select, insert, update, delete on cadastro.* to userqualquer@localhost;

E verifique os privilégios assim:


show grants for userqualquer@localhost;

Para apenas dar privilégios de alterar apenas algumas colunas especificas em uma tabela, fazemos assim:


grant select(nome, descricao), update(descricao) on cadastro.cursos to usuario@localhost;

E verificar assim:


show grants for usuario@localhost;

Para revogar privilégios específicos, basta usar o revoke, assim, por exemplo:


revoke delete on cadastro.* from userqualquer@localhost;

Dá pra remover privilégios de vários usuários também, assim:


revoke all, grant option from usertotal, usuario@localhost;

Depois podemos dar um show grants para verificar se os privilégios foram removidos.

Se quiser, pode deletar os usuários criados pro exemplo, assim:


drop user usertotal;
drop user 'novouser'@'localhost';
drop user 'userqualquer'@'localhost';
drop user 'usuario'@'localhost';