Principais Comando MySQL

comandos.sql

-- Para deixar o nome das tabelas em CamelCase, coloque a seguinte linha no mysql.ini: 
-- lower_case_table_names = 2 

/* ****************************** */
/* DDL - Data Definition Language */
/* ****************************** */

/* Criando banco de dados com codificação e colagem correta */
CREATE DATABASE cadastro 
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

/* Apagando banco de dados */ 
DROP DATABASE cadastro;

/* Usando banco de dados */
USE cadastro;

/* Consultando usuários existentes */
SELECT Host, User FROM mysql.user;

/* Criando Usuário */
CREATE USER 'usuario'@'localhost' IDENTIFIED BY 'senha';
-- Se o host não for informado, ela acessa qualquer de local (%)

/* Alterando a senha de um usuário */
SET PASSWORD FOR 'usuario'@'localhost' = PASSWORD('senha');

/*Renomeando usuário  */
RENAME USER usuario TO novonome;

/* Excluindo usuário */
DROP USER usuario;

/* Mostrando Privilégios do usuário */
SHOW GRANTS FOR 'usuario'@'localhost';

/* Dando acesso ao Usuário*/
GRANT [tipo de permissão] ON [nome da base de dados].[nome da tabela] TO 'nome do usuário'@'localhost';
FLUSH PRIVILEGES;

/* Tipos de permissões
  
  - Trabalhar com dados:
    • INSERT            - Inserir dados em uma tabela
    • UPDATE            - Atualizar dados em uma tabaela
    • DELETE            - Deletar dados de uma tabaela
    • SELECT            - Efetutar consultas em uma tabaela
  
  - Modificar estrutura:
    • CREATE            - Criar tabela ou BD
    • ALTER             - Modificar uma tabela
    • DROP              - Excluir tabela ou BD
    • CREATE VIEWS      - Criar exibições
    • TRIGGER           - Criar ou excluir um trigger em uma tabela
    • INDEX             - Criar ou excluir um indice
    • CREATE ROUTINE    - Criar uma função ou procedimento
    • ALTER ROUTINE     - Alterar ou excluir uma função ou procedimento
  
  - Administrativos:
    • CREATE USER       - Criar conta de usuário
    • SHOW DATABASES    - Ver os nomes dos BDs
    • SHUTDOWN          - Desligar o servidor
    • RELOAD            - Recarregar as tabelas de privilegios que foram modificadas para aplicar as alterações
  
  - Outros:
    • ALL               - Todos os privilégios, exceto GRANT OPTION
    • GRANT OPTION      - Permite dar privilégios a outros usuários
    • USAGE             - Sem privilegios
 */
 
 /* Exemplos de acesso ao usuário */
GRANT ALL ON cadastro.* TO 'usuario'@'localhost'; -- Concede todos os privilégios para o BD cadastro, em todas as tabelas, para o usuário usuario de localhost
FLUSH PRIVILEGES; -- Atualiza as tabelas

GRANT ALL ON *.* TO 'usuario' WITH GRANT OPTION; -- Concede todos os privilégios (incluindo GRANT OPTION) para todos os bds do usuário 'usuario' de qq/ host (%)
FLUSH PRIVILEGES; 

GRANT SELECT, INSERT, UPDATE, DELETE ON cadastro.* TO 'usuario'@'localhost'; -- Concede privilégios de registros apenas.
FLUSH PRIVILEGES;

GRANT SELECT(coluna1, coluna2), UPDATE(coluna1) ON cadastro.tabela TO 'usuario'@'localhost'; -- Concede privilégios de consulta e atualização para colunas especificas de uma determinada tabela
FLUSH PRIVILEGES;

-- Obs: Se o usuário não existir, ou desejar alteração de senha, você pode emendar todos os comandos assim:
GRANT ALL PRIVILEGES ON cadastro.* TO 'usuario'@'localhost' IDENTIFIED BY 'senha'; 
FLUSH PRIVILEGES; 

/* Revogando privilégios */
REVOKE [tipo de permissão] FROM [nome da base de dados].[nome da tabela] TO 'nome do usuário'@'localhost';

/* Exemplo */
REVOKE DELETE ON cadastro.* FROM 'usuario'@'localhost';
FLUSH PRIVILEGES; 


/* Criando Tabela */
CREATE TABLE IF NOT EXISTS pessoas ( -- Tenta cadastrar apenas se não existir. "IF NOT EXISTS" é opcional 

    id            INT NOT NULL AUTO_INCREMENT,
    nome          VARCHAR(50) NOT NULL,
    nascimento    DATE,
    sexo          ENUM('M', 'F'), 
    peso          DECIMAL(5,2) UNSIGNED, -- o opcional UNSIGNED indica sem sinal (+ ou -), o que economiza 1 byte 
    altura        DECIMAL(3,2) UNSIGNED,
    nacionalidade VARCHAR(20) DEFAULT 'Brasil',
    
    PRIMARY KEY (id)

) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- XtraDB pode ser usada no lugar de InnoDB pois XtraDB é um fork melhorado de InnoDB 

/* Tipos primitivos:

• Numérico
    - Inteiro..: TinyInt, SmallInt, Int, MediumInt, BigInt
    - Real.....: Decimal, Float, Double, Real
    - Lógico...: Bit, Boolean

• Data/Tempo...: Date, DateTime, TimeStamp, Time, Year

• Literal
    - Caractere: Char, VarChar
    - Texto....: TinyText, Text, MediumText, LongText
    - Binário..: TinyBlob, Blob, MediumBlob, LongBlob

• Espacial.....: Geometry, Point, Polygon, MultiPolygon

OBS: ENUM Permite inserção apenas da lista definada (como o exemplo acima). 
     Usar somente em casos como esse, onde não haverá necessidade de outros dados jamais 
     e nem serão usados para chaves estrangeiras. 
     O ideal para outros casos é criar um tabela contendo as opções.
     
*/

/* Exemplo de criação com foreign key*/
CREATE TABLE pessoaAssisteCurso (
    
    id INT NOT NULL AUTO_INCREMENT,
    dataAssitida DATE,
    idPessoa INT,
    idCurso INT,
    PRIMARY KEY (id),
    FOREIGN KEY (idPessoa) REFERENCES pessoas(id),
    FOREIGN KEY (idCurso) REFERENCES cursos(id)
        
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 
/* Apagando tabela*/
DROP TABLE IF EXISTS pessoas; /* tenta apagar apenas se existir. "IF EXISTS" é opcional*/

/* Adicionando colunas na tabela */
ALTER TABLE pessoas
ADD COLUMN profissao VARCHAR(10) AFTER nome; 
-- OBS: "AFTER <campo>" é opcional. Para colocar em promeiro, use "FIRST". Se omitir ambos, o campo fica em último. 

/* Removendo Colunas */
ALTER TABLE pessoas
DROP COLUMN profissao;

/* Alterando definição do campo */
ALTER TABLE pessoas
MODIFY COLUMN profissao VARCHAR(20); 

/* Alterando definição E NOME do campo */
ALTER TABLE pessoas
CHANGE COLUMN profissao prof VARCHAR(20); 

/* Renomear tabela */
ALTER TABLE pessoas
RENAME TO clientes;

/* Adicionando chave estrangeira quando isso não foi feito na criação */
ALTER TABLE gafanhotos
ADD FOREIGN KEY(cursoPreferido)
REFERENCES cursos(id);

/* EXEMPLO Inserindo novo campo como chave primaria após a criação da tabela (no exemplo o campo "id" não existe inicialmente) */
ALTER TABLE pessoas ADD COLUMN id INT FIRST;
ALTER TABLE pessoas ADD PRIMARY KEY (id); -- Não dá pra criar o campo e já adicionar a chave ao mesmo tempo 
ALTER TABLE pessoas MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT; -- O auto incrmento não pode ser adicionado antes de indicar que é chave primária 


/* ******************************** */
/* DML - Data Manipulation Language */
/* ******************************** */

/* Inserindo dados */
INSERT INTO 
    ( campo, campo, ... ) 
VALUES 
    ( 'valor', 'valor' ), 
    ( 'valor', 'valor' ), 
    ..., 
    ( 'valor', 'valor' );

/* OBS: Pode-se usar a constraint DEFAULT para definir o valor como padrão (semelhante ao NULL para PHP) */
INSERT INTO 
    ( id, nome, nacionalidade ) 
VALUES 
    ( DEFAULT, 'Daniel', DEFAULT );

    
/* Atualizando dados */
UPDATE pessoas SET nome = 'Daniel Bispo' WHERE id = '1';

/* Apagando dados */
DELETE FROM pessoas WHERE sexo = 'F';

/* Apagando todos os dados */
TRUNCATE TABLE pessoas;


/* ************************* */
/* DQL - Data Query Language */
/* ************************* */

/* Mostrando bancos de dados */
SHOW DATABASES;

/* Mostrando tabelas */
SHOW TABLES;

/* Mostrando comando de criação de uma tabela */
SHOW CREATE TABLE pessoas;

/* Buscando dados */
SELECT <campos> FROM <tabela> WHERE <condicional> GROUP BY <campos> HAVING <filtro> ORDER BY <campos> LIMIT <offset, qtde>

/* Exemplos */
SELECT * FROM cursos WHERE ano BETWEEN '2014' AND '2016'; -- Selecione os resgitros cujo ano estão entre 2014 e 2016
SELECT * FROM cursos WHERE ano IN ('2013', '2016', '2018'); -- Selecione os resgitros cujo ano são 2013 ou 2016 ou 2018 
SELECT DISTINCT nacionalidade FROM pessoas ORDER BY nacionalidade; -- Seleciona as nacionalidades distintas (sem repetição) das pessoas cadastradas 
SELECT nacionalidade, count(*) AS qtde FROM gafanhotos GROUP BY nacionalidade HAVING qtde > 5 ORDER BY qtde DESC; -- Mostra a qtde de cada nacionalidade, filtrando pelas q possuem mais de 5 pessoas 
SELECT nome, COUNT(qtd) FROM cursos WHERE nome REGEXP '(PHP|C#)$' GROUP BY nome HAVING qtd > 1; -- Traz a qtd de cursos, por curso, que tenham mais de um curso e cujos nomes terminem com PHP ou C# 


/* 
IFNULL e COALESCE 

• IFNULL traz um valor caso o campo inicial seja nulo
• COALESCE traz o primeiro campo cujo valor não seja nulo
*/

SELECT nome, IFNULL(valor, 0) AS val FROM cursos; -- traz 0 se o valor for nulo 
SELECT nome, COALESCE(valor, valorPromo, 0) AS val FROM cursos; -- se o valor for nulo, verifica o valor promocional. Se tb for nulo, traz 0 



/* ***** */
/* VIEWS */
/* ***** */

/* *
 * Uma view serve pra se ter uma tabela montada na memória, para evitar repetição de código. 
 * Após criada a view, esta ficará como uma tabela virtual, cujo conteúdo é auterado automaticamente.
 */

 /* Criando visando */
CREATE VIEW nome_da_view AS SELECT...

/* Exemplo - Criando uma view de junção das tabelas clientes e cursos */
CREATE VIEW cursos_cliente AS
SELECT clientes.nome, cursos.nome FROM clientes INNER JOIN cursos ON clientes.curso = cursos.id;


/* Alterando uma view */
ALTER VIEW nome_da_view AS SELECT...

/* Exemplo */
ALTER VIEW cursos_cliente AS
SELECT clientes.id, clientes.nome, cursos.nome FROM clientes INNER JOIN cursos ON clientes.curso = cursos.id;


/* Excluindo uma view */
DROP VIEW nome_da_view;

/* Exemplo */
DROP VIEW cursos_cliente;


/* ******************* */
/* FUNÇÕES MATEMÁTICAS */
/* ******************* */

/*

*   - Multiplicação
/   - Divisão
+   - Soma
-   - Subtração
DIV - Parte inteira da divisão (SELECT 10 DIV 3 -> retorna 3)
MOD - Sobra da divisão (SELECT 10 MOD 3 -> retorna 1)


CEILING(x)  - Arredonda pra cima
FLOOR(x)    - Arredonda pra baixo
PI()        - Retorna o valor de PI
POW(x, y)   - Retorna X elevado a Y
SQRT(x)     - Retorna a raiz quadrada do valor
SIN(x)      - Retorna o seno de X
HEX(x)      - Retorna o valor hexadecimal de x

*/


/* ******* */
/* FUNÇÕES */
/* ******* */

/* Criando uma função */
CREATE FUNCTION nomeDaFuncao(parametros)
RETURNS tipo_de_dados_retornado
BEGIN
    código_da_função;
END;

/* Invocando função */
SELECT nomedaFuncao(parametros);

/* Exemplo Simples */
CREATE FUNCTION fnTeste(a DECIMAL(10,2), b INT)
RETURNS INT
RETURN a * b;

SELECT fnTeste(2.5, 4) AS resultado; -- Retorna 10 


/* Apagando função */
DROP FUNCTION nomeFuncao;


/* ************* */
/* PROCEDIMENTOS */
/* ************* */

/* Criando um procedimento */
CREATE PROCEDURE nomeProcedimento(parametros)
BEGIN
    declarações;
END;

/* Invocando o procedimento */
CALL nomeProcedimento(parametros);

/* Exemplo Simples */
CREATE PROCEDURE valorCurso(idCurso INT)
SELECT CONCAT('O valor é ', IFNULL(valor, 0)) AS custo FROM cursos WHERE id = idCurso;

CALL valorCurso(3);


/* Excluindo Procedimento */
DROP PROCEDURE nomeProcedimento;


/* *** */
/* OBS */
/* *** */

/* Tanto para FUNCTIONS como para PROCEDURES, ao usar um bloco de comandos, com BEGIN...END, é importante antes criar um delimitador para não confundir com o ";" dos comandos internos */

/* Exemplo */
DELIMITER § -- muda o delimitador para "§" 
CREATE FUNCTION fnTeste(a DECIMAL(10,2), b INT)
RETURNS INT
BEGIN
    RETURN a * b;
END§

CREATE PROCEDURE valorCurso(idCurso INT)
BEGIN
    SELECT CONCAT('O valor é ', IFNULL(valor, 0)) AS custo FROM cursos WHERE id = idCurso;
END§
DELIMITER ; -- volta o delimitador para ";"


/* Modificadores de parametro
• IN     - (default) indica que o parametro é apenas de entrada (não é afetado fora do procedimento)
• OUT    - indica que o parâmetro é apenas de saída - o procedimento altera seu valor, mas não é possível informar um valor de entrada
• INOUT  - combinação de ambos
 */
 
/* Exemplo OUT */
DELIMITER §
CREATE PROCEDURE nomeCurso(idCurso INT, OUT nomeCurso VARCHAR)
BEGIN
    SELECT cursos.nome INTO nomeCurso FROM cursos WHERE id = idCurso;
END§
DELIMITER ;

CALL nomeCurso(3, @nomeCurso);
SELECT @nomeCurso;

/* Exemplo INOUT */
DROP PROCEDURE IF EXISTS aumentaValor;

DELIMITER §
CREATE PROCEDURE aumentaValor(INOUT valor DECIMAL(10,2), taxa DECIMAL(10,2))
BEGIN
    SET valor = valor + taxa * valor / 100;
END§
DELIMITER ;

SET @valor = 10;
CALL aumentaValor(@valor, 15.6);
SELECT @valor;


/* Para declarar uma variável local (que será usada somente dentro da função/procedimento) use DECLARE logo após o BEGIN */
DROP FUNCTION IF EXISTS calculaDesconto;

DELIMITER §
CREATE FUNCTION calculaDesconto(idCurso INT, desconto DECIMAL(10,2))
RETURNS DECIMAL(10,2)
BEGIN
    DECLARE preco DECIMAL(10,2);
    
    -- Pesquisa o valor atual
    SELECT valor FROM cursos WHERE id = idCurso INTO preco;
    
    -- Se o desconto for maior que o preco, faz o mesmo ser igual
    IF (desconto > preco) THEN
        SET desconto = preco;
    END IF;
    
    -- Retorna o valor com o desconto
    RETURN preco - desconto;
END§
DELIMITER ;

SELECT *, calculaDesconto(id, 10.00) AS desconto FROM cursos;


/* ************ */
/* CONDICIONAIS */
/* ************ */

/* IF */
IF (condicao) THEN
    comandos;
[ELSEIF (condicao) THEN 
    comandos;]    
[ELSE 
    comandos;]    
END IF;


/* CASE */
CASE valor_referenciado
    WHEN valor_comparado1 THEN
        comandos;
    WHEN valor_comparado2 THEN
        comandos;
    WHEN valor_comparado3 THEN
        comandos;
    ELSE
        comandos;
END CASE;

/* CASE2 - Neste caso semelhante ao IF..ELSEIF..ELSE..ENDIF; */
CASE 
    WHEN (condicao1) THEN
        comandos;
    WHEN (condicao2) THEN
        comandos;
    WHEN (condicao3) THEN
        comandos;
    ELSE
        comandos;
END CASE;



/* ******************* */
/* BLOCOS DE REPETIÇÃO */
/* ******************* */

/* Exemplo de LOOP */

DROP PROCEDURE IF EXISTS acumula;

DELIMITER §
CREATE PROCEDURE acumula( limite INT )
BEGIN
    DECLARE contador INT DEFAULT 0;
    DECLARE soma INT DEFAULT 0;
    -- Iniciando o LOOP de teste
    teste: LOOP
        -- Verificador no inicio para evitar soma caso o limite seja 0 ou inferior
        IF contador >= limite THEN
            -- Deixa o LOOP (semelhante ao break do PHP)
            LEAVE teste;
        END IF;
        -- Incrementa contador e soma
        SET contador = contador + 1;
        SET soma = soma + contador;
    END LOOP teste;
    -- Mostra a soma
    SELECT soma;
END§
DELIMITER ;

CALL acumula(10);


/* Exemplo de REPEAT */

DROP PROCEDURE IF EXISTS acumula;

DELIMITER §
CREATE PROCEDURE acumula(limite TINYINT UNSIGNED)
main: BEGIN
    DECLARE contador TINYINT UNSIGNED DEFAULT 0;
    DECLARE soma INT DEFAULT 0;
    -- Se o limite fom 0, nem entra no loop
    IF limite = 0 THEN
        -- emite o aviso e sai do procedimento
        SELECT 0 AS soma;
        LEAVE main;
    END IF;
    -- Laço de repetição
    REPEAT
        SET contador = contador + 1;
        SET soma = soma + contador;
    UNTIL contador >= limite END REPEAT;
    -- Mostra o resultado
    SELECT soma;
END§
DELIMITER ;

CALL acumula(10);


/* Exemplo de WHILE */

DROP PROCEDURE IF EXISTS acumula;

DELIMITER §
CREATE PROCEDURE acumula(limite TINYINT UNSIGNED)
BEGIN
    DECLARE contador TINYINT UNSIGNED DEFAULT 0;
    DECLARE soma INT DEFAULT 0;
    -- Só entra no loop se o limite for maior que 0
    WHILE contador < limite DO
        SET contador = contador + 1;
        SET soma = soma + contador;
    END WHILE;
    -- Mostra o resultado
    SELECT soma;
END§
DELIMITER ;

CALL acumula(10);


/* Exemplo de ITERATE */

DELIMITER §
CREATE PROCEDURE acumula( limite INT )
BEGIN
    DECLARE contador INT DEFAULT 0;
    DECLARE soma INT DEFAULT 0;
    -- Iniciando o LOOP de teste
    teste: LOOP
        -- Verificador no inicio para evitar soma caso o limite seja 0 ou inferior
        IF contador < limite THEN
            SET contador = contador + 1;
            SET soma = soma + contador;
            -- Faz o loop ignorando o que está abaixo (semelhante ao continue, do PHP)
            ITERATE teste;
        END IF;
        -- Deixa o LOOP
        LEAVE teste;
    END LOOP teste;
    -- Mostra a soma
    SELECT soma;
END§
DELIMITER ;

CALL acumula(10);


/* ******************* */
/* TRIGGERS (Gatilhos) */
/* ******************* */

/*
Um trigger é um gatilho chamado automaticamente em determinadas situações 
(DML -> quando há inserção, atualização ou deleção de dados) e servem para:

    - Verificar a integridade dos dados
    - Validar dados
    - Rastrear registros de logs de atividades nas tabelas
    - Arquivar resgistros excluídos
*/

/* Sintaxe */
CREATE TRIGGER nome [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tabela FOR EACH ROW declaracoes

/* Exemplo */

-- Cria a tabela de exemplo
CREATE TABLE produto(
    id INT NOT NULL AUTO_INCREMENT,
    nome VARCHAR(15) NULL,
    valor DECIMAL(10,2) NULL,
    valorComDesconto DECIMAL(10,2) NULL,
    PRIMARY KEY (id)
);

-- Cria o trigger...
CREATE TRIGGER desconto 
-- ...após uma inserção ...
BEFORE INSERT 
-- ... em produto...
ON produto 
-- ... para cada linha ...
FOR EACH ROW 
    -- adicione o desconto baseado no valor
    SET NEW.valorComDesconto = (NEW.valor * 0.9);

-- Insere um produto
INSERT INTO produto (nome, valor) VALUES ('Monitor', 100.99);

-- Mostra o resultado (se tudo ocorreu bem, o resultado terá um desconto de 10% na coluna `valorComDesconto`
SELECT * FROM produto;