Skip to content

Instantly share code, notes, and snippets.

@szagot
Last active May 17, 2024 01:29
Show Gist options
  • Save szagot/d9cc0bb32072bf97eb7d275f882ddff0 to your computer and use it in GitHub Desktop.
Save szagot/d9cc0bb32072bf97eb7d275f882ddff0 to your computer and use it in GitHub Desktop.
Principais Comando MySQL
-- 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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment