sql.md

SQL

(comandos do linux)

exemplo avançado usando if, where e order em duas tabelas

SELECT IF(GRADES.GRADE>=8, STUDENTS.NAME, NULL),GRADES.GRADE, STUDENTS.MARKS
FROM GRADES, STUDENTS
WHERE STUDENTS.MARKS BETWEEN GRADES.MIN_MARK AND GRADES.MAX_MARK
ORDER BY GRADES.GRADE DESC, STUDENTS.NAME;

inicial junto com o computador:

sim:

sudo systemctl enable mysql

não:

sudo systemctl disable mysql

para inicial o sql server após iniciar o computador use o comando:

(caso a inicialização do seu sql não seja altomatica)

sudo systemctl start mysql

abrir linha de comando do sql:

mysql -u root -p

---

Principais tipos de dados:

- booleanos:
1 ou 0, valor padrão é NULL
.
- caracteres:
tamanho fixo = char(5) - vai ocupar os 5 espaços no armazenamento independente de quantos foram usados
tamanho variavel = varchar(5) - ocupa somente os espaços utilizados
.
- números:
unsigned = apenas valores positivos
signed = tanto valores positivos quanto negativos
.
valores exatos inteiros = tinyint, smallint, mediumint, int, bigint
valores com precisão decimal = decimal() (define a precisão (decimal(5,2) = 123,45)), float/real (precisão de 1 casa), doble (precisão de 2 casas)
.
- temporais:
date = YYYY-MM-DD
time = HH:MM:SS
datetime = YYYY-MM-DD HH:MM:SS (entre 1000-01-01 00:00:00 até 9999-12-31 23:59:59)
timestamp = datetime com base no fuso horário
year = somente ano entre 1901 e 2155

---

Queries

---

• codigos de interação com as tabelas:

/*
Backticks ou crase (``): são usadas para identificar nome de tabelas e colunas. São necessárias apenas quando o identificador for uma palavra reservada do MySQL, ou quando o nome da tabela ou coluna contiver espaços em branco.

Aspas simples (''): devem ser usadas em valores do tipo string. Aspas simples são aceitas na maioria dos Sistemas de Gerenciamento de Banco de Dados, sendo assim, é preferível usar aspas simples no lugar das aspas duplas.
*/

• constrants:

/*
- NOT NULL - Garante que aquele campo não pode conter valores nulos, ou seja, se não houver um valor padrão (DEFAULT) definido, será
sempre necessário passar um valor para esse campo durante a inserção ou alteração de dados.

- UNIQUE - Garante que o valor inserido na coluna da tabela é único, isto é, não pode haver outro valor igual para esta coluna
registrado nesta tabela.

- PRIMARY KEY - Garante que o valor seja a chave primária da tabela, ou seja, que a coluna que possui essa constraint aplicada seja o
identificador único da tabela. Ela também é, por definição, não nula (mesmo efeito da constraint NOT NULL) e única (mesmo efeito da
constraint UNIQUE).

FOREIGN KEY - Garante que o valor seja uma chave estrangeira da tabela, ou seja, faça referência à chave primária (valor em uma coluna
com a constraint PRIMARY KEY) de outra tabela, permitindo um relacionamento entre tabelas.

DEFAULT - Garante que, caso nenhum valor seja inserido na coluna (ou caso a pessoa usuária insira um valor nulo), a constraint colocará
o valor padrão passado para ela.
*/

---

DDL: Data Definition Language - Lidam com o esquema, a descrição e o modo como os dados devem existir:

Para criar bancos de dados, tabelas, índices, views, procedures, functions e triggers

CREATE DATABASE nome_do_banco_de_dados;
CREATE SCHEMA nome_do_banco_de_dados;
(cria o banco de dados)

CREATE DATABASE IF NOT EXISTS nome_do_banco_de_dados;
(cria o banco de dados caso ainda não exista)

(USE banco_de_dados)
CREATE TABLE nome_tabela(
  coluna1 tipo_de_dado,
  coluna2 tipo_de_dado NOT NULL,
  coluna3 tipo_de_dado NOT NULL,
  CONSTRAINT PRIMARY KEY(coluna3)
);
(USE banco_de_dados)
CREATE TABLE nome_tabela(
  coluna1 tipo_de_dado,
  coluna2 tipo_de_dado NOT NULL, -- NOT NULL = exemplo de constrant
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
);

/*
CREATE TABLE actor(
    actor_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE film(
    film_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

CREATE TABLE film_actor(
  actor_id INTEGER,
  film_id INTEGER,
  CONSTRAINT PRIMARY KEY(actor_id, film_id),
    FOREIGN KEY (actor_id) REFERENCES Actor (actor_id),
    FOREIGN KEY (film_id) REFERENCES Film (film_id)
);
*/

SHOW DATABASES;
(lista os bancos de dados existentes)

SHOW TABLES;
(lista as tabelas existentes)

USE nome_do_banco_de_dados;
(define o banco de dados ativo para uso no momento)

Para alterar a estrutura de qualquer objeto

ALTER

Permite deletar objetos

DROP

Evazia os dados dentro de uma tabela, mas a mantém no banco de dados (deleta todos os dados)

TRUNCATE 'banco_de_dados'.'nome_tabela';

---

DML: Data Manipulation Language - Comandos que são usados para manipular dados. São utilizados para armazenar, modificar, buscar e excluir dados:

C - create = INSERT
R - read = SELECT
U - update = UPDATE
D - delete = DELETE

Usado para buscar dados em um banco de dados

SELECT 'valor';
SELECT 'valor', 'valor2', 'valorN'...;

SELECT 'valor' AS 'nome_coluna';
SELECT 'valor' AS 'nome_coluna', 'valor1' AS 'nome_coluna1', 'valorN' AS 'nome_colunaN'...;

///////////////////////////////////////////////////////////////////////////////////////////////
Tudo ou por coluna

SELECT * FROM 'banco_de_dados'.'nome_tabela';
SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela';
SELECT COALESCE('nome_coluna', "valor_caso_valor_seja_null") FROM 'banco_de_dados'.'nome_tabela';

///////////////////////////////////////////////////////////////////////////////////////////////
Limitando quantidade

SELECT * FROM 'banco_de_dados'.'nome_tabela' LIMIT 10;
(apenas 10 linhas da tabela)
SELECT * FROM 'banco_de_dados'.'nome_tabela' LIMIT 10 OFFSET 3;
(apenas 10 linhas da tabela pulando aas 3 primeiras)

///////////////////////////////////////////////////////////////////////////////////////////////
Impedindo repetições

SELECT DISTINCT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela';
(todos os valores sem repetir)

///////////////////////////////////////////////////////////////////////////////////////////////
Por condição

SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' WHERE 'condição';

/*
OPERADORES LOGICOS

= igual
<> diferente de
> maior que
< menor que
>= maior igual a
<= menor igual a
AND operador 'e'
OR operador 'ou'
IS compara booleanos
NOT negação
*/

SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' WHERE 'nome_coluna' LIKE %'caracteres_finais_do_valor';
SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' WHERE 'nome_coluna' LIKE 'caracteres_iniciais_do_valor'%;
SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' WHERE 'nome_coluna' LIKE '_'; (1 underline = valores com 1 caracter)

/*
LIKE busca por meio de uma sequência específica de caracteres
 - % O sinal de percentual, que pode representar zero, um ou múltiplos caracteres
 - _ O underscore, que representa um único caractere
*/

SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' WHERE 'nome_coluna' IN ('valor1', 'valor2', 'valorN'...);
(procupra por valor1 ou valor2 ou valorN...)
SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' WHERE 'nome_coluna' BETWEEN(1) AND(7);
(retorna os valores 1, 2, 3, 4, 5, 6 e 7)

/*
IN busca os valores da coluna indicada que correspondem a um dos valores passados
BETWEEN busca os valores dentro de uma faixa inicial e final (funciona com dats, numeros e strings)
*/

///////////////////////////////////////////////////////////////////////////////////////////////
Juntando colunas

SELECT CONCAT('coluna1', 'coluna2') FROM 'banco_de_dados'.'nome_tabela';
('valor_coluna1valor_coluna2')
SELECT CONCAT('coluna1', ' ', 'coluna2') FROM 'banco_de_dados'.'nome_tabela';
('valor_coluna1 valor_coluna2')
SELECT CONCAT('coluna1', ' ', 'coluna2') AS 'nome_colunas_juntas' FROM 'banco_de_dados'.'nome_tabela';
('valor_coluna1 valor_coluna2')

///////////////////////////////////////////////////////////////////////////////////////////////
Ordenando as linhas

SELECT * FROM 'banco_de_dados'.'nome_tabela' ORDER BY 'nome_coluna';
SELECT * FROM 'banco_de_dados'.'nome_tabela' ORDER BY 'nome_coluna' ASC;
(ordena de forma crescente)
SELECT * FROM 'banco_de_dados'.'nome_tabela' ORDER BY 'nome_coluna' DESC;
(ordena de forma decrescente)
SELECT * FROM 'banco_de_dados'.'nome_tabela' ORDER BY 'nome_coluna1' ASC, 'nome_coluna2' DESC;

///////////////////////////////////////////////////////////////////////////////////////////////
Manipulando strings

SELECT UCASE('string');
('STRING')
SELECT LCASE('STRING');
('string')
SELECT REPLACE('Eu sou uma string', 'string', 'cadeia de caracteres');
('Eu sou uma cadeia de caracteres')
SELECT LEFT('Olá string', 3);
('Olá')
SELECT RIGHT('Olá string', 6);
('string')
SELECT CHAR_LENGTH('string');
('6')
SELECT SUBSTRING('Olá string', 5, 2);
('st')
SELECT SUBSTRING('Olá string', 5);
('string')

///////////////////////////////////////////////////////////////////////////////////////////////
Manipulando por condição

SELECT IF('condicao', 'valor_se_verdadeiro', 'valor_se_falso');
SELECT IF('nome_coluna' = 0, 'valor_se_verdadeiro', 'valor_se_falso') FROM 'nome_tabela';
SELECT 'nome_coluna', IF('condicao', 'valor_se_verdadeiro', 'valor_se_falso') FROM 'nome_tabela';

SELECT 'nome_coluna', IF('condicao', 'valor_se_verdadeiro', 'valor_se_falso')
AS 'nome_nova_coluna' FROM 'nome_tabela';

SELECT CASE
  WHEN 'condicao' THEN 'valor'
  ELSE 'valor_padrao'
END;

SELECT CASE
  WHEN 'condicao' THEN 'valor'
  ELSE 'valor_padrao'
END AS 'nome_nova_coluna';

///////////////////////////////////////////////////////////////////////////////////////////////
Funções matemáticas

SELECT 'valor' + 'valor2';
SELECT 'valor' - 'valor2';
SELECT 'valor' * 'valor2';
SELECT 'valor' / 'valor2';

SELECT 'nome_coluna1' + 'nome_coluna2' FROM 'banco_de_dados'.'nome_tabela';
SELECT 'nome_coluna1' - 'nome_coluna2' FROM 'banco_de_dados'.'nome_tabela';
SELECT 'nome_coluna1' * 'nome_coluna2' FROM 'banco_de_dados'.'nome_tabela';
SELECT 'nome_coluna1' / 'nome_coluna2' FROM 'banco_de_dados'.'nome_tabela';

SELECT 10 DIV 2;
(5)
SELECT 13 DIV 2;
(6)

SELECT 10 MOD 2;
(0)
SELECT 13 MOD 2;
(1)

///////////////////////////////////////////////////////////////////////////////////////////////
Trabalhando com datas

SELECT CURRENT_DATE();
(YYYY-MM-DD)
SELECT NOW();
(YYYY-MM-DD HH:MM:SS)

SELECT YEAR(CURRENT_DATE());
(ano atual)
SELECT HOUR(NOW());
(hora atual)

SELECT DATEDIFF('2020-01-31', '2020-01-01');
('30' = diferença de 30 dias)
SELECT TIMEDIFF('2021-08-11 08:30:10', '2021-08-01 09:30:10');
('239:00:00' = diferença de 239 horas)

SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' WHERE DATE('nome_coluna') = '2005-07-31';
SELECT YEAR('nome_coluna') FROM 'banco_de_dados'.'nome_tabela';

/*
DATE Possui apenas data, no formato YYYY-MM-DD na faixa de 1001-01-01 até 9999-12-31
DATETIME Possui data e tempo, no formato YYYY-MM-DD HH:MM:SS com a faixa de 1000-01-01 00:00:00 até 9999-12-31 23:59:59
 - YEAR Ano
 - MONTH Mês
 - DAY Dia
 - HOUR Hora
 - MINUTE Minuto
 - SECOND Segundo
*/

///////////////////////////////////////////////////////////////////////////////////////////////
Funções de agregação

SELECT AVG('nome_coluna') FROM 'banco_de_dados'.'nome_tabela'; 
(Média entre todos registros)
SELECT MIN('nome_coluna') FROM 'banco_de_dados'.'nome_tabela';
(Menor valor encontrado)
SELECT MAX('nome_coluna') FROM 'banco_de_dados'.'nome_tabela';
(Maior valor encontrado)
SELECT SUM('nome_coluna') FROM 'banco_de_dados'.'nome_tabela';
(Soma de todos registros)
SELECT COUNT('nome_coluna') FROM 'banco_de_dados'.'nome_tabela';
(Quantidade)

///////////////////////////////////////////////////////////////////////////////////////////////
Agrupando dados

SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela';
('1','2','3','4','3','2','1','0')
SELECT 'nome_coluna' FROM 'banco_de_dados'.'nome_tabela' GROUP BY 'nome_coluna';
('1','2','3','4','0')

SELECT 'nome_coluna1', 'funcao_de_agregacao' FROM 'banco_de_dados'.'nome_tabela' GROUP BY 'nome_coluna';
SELECT 'nome_coluna1', COUNT(*) FROM 'banco_de_dados'.'nome_tabela' GROUP BY 'nome_coluna';
('1','2','3','4','0' = retorno sem repetições)
('2','2','2','1','1' = quantidade de repetições de cada item)

///////////////////////////////////////////////////////////////////////////////////////////////
Filtrando dados agrupados

SELECT 'nome_coluna1' FROM 'banco_de_dados'.'nome_tabela' GROUP BY 'nome_coluna' HAVING COUNT(*) >= 2;
('1','2','3' = retorno sem repetições filtrado)

///////////////////////////////////////////////////////////////////////////////////////////////
Juntando tabelas

SELECT t1.coluna, t2.coluna === (usa os apelidos para identificar de que tabela a coluna vem)
FROM tabela1 AS t1 === (apelida tabela1 de t1)
INNER JOIN tabela2 AS t2 === (apelida tabela2 de t2)
ON t1.coluna_em_comum = t2.coluna_em_comum; === (identifica em que se basear para relacionar as tabelas)

SELECT t1.coluna, t2.coluna
FROM tabela1 AS t1
LEFT JOIN tabela2 AS t2
ON t1.coluna_em_comum = t2.coluna_em_comum;

SELECT t1.coluna, t2.coluna
FROM tabela1 AS t1
RIGHT JOIN tabela2 AS t2
ON t1.coluna_em_comum = t2.coluna_em_comum;

/*
INNER JOIN usado para relacionar tabelas apartir de uma coluna em comum
LEFT JOIN relaciona tabelas priorizando a da esquerda
RIGHT JOIN relaciona tabelas priorizando a da direita
*/

SELECT CONCAT(Employee.FIRST_NAME, " ", Employee.LAST_NAME) AS "Nome da Pessoa Colaboradora",
CONCAT(Manager.FIRST_NAME, " ", Manager.LAST_NAME) AS "Nome Gerente"
FROM employees AS Employee
INNER JOIN employees AS Manager
ON Employee.MANAGER_ID = Manager.EMPLOYEE_ID;

/*
SELF JOIN relaciona a tabela com ela mesma
*/

Insere dados em uma tabela

Inserindo valores na tabela

INSERT INTO nome_tabela (coluna1, coluna2) VALUES ('valor_coluna1', 'valor_coluna2');
INSERT INTO nome_da_tabela (coluna1, coluna2) VALUES
('valor_1','valor_2'),
('valor_3','valor_4'),
('valor_5','valor_6');

///////////////////////////////////////////////////////////////////////////////////////////////
ignorando valores já existentes na tabela

antes: 1,2,3,4

INSERT IGNORE INTO pessoas (id) VALUES
(4), -- primary key repetida
(5);

depois: 1,2,3,4,5

/*
IGNORE faz com que a query ignore o resto da tabela evitando erros como por exemplo a repetição de um primary key
*/

///////////////////////////////////////////////////////////////////////////////////////////////

/*
Colunas com incremento automatico não precisão ser inseridas
*/

///////////////////////////////////////////////////////////////////////////////////////////////
inserindo valores de outra tabela

INSERT INTO tabelaA (coluna1, coluna2)
    SELECT tabelaB.coluna1, tabelaB.coluna2
    FROM tabelaB
    ORDER BY tabelaB.coluna_de_ordenacao;

---

( no MySQL Server é que, por padrão, existe uma configuração chamada safe updates mode que só vai te permitir executá-los caso eles incluam quais IDs devem ser modificados.

Para evitar essa restrição, rode o seguinte comando em uma janela de query dentro do MySQL Workbench sempre que abri-lo para desabilitar essa funcionalidade, antes de executar seus comandos de UPDATE ou DELETE:

SET SQL_SAFE_UPDATES = 0;

Com o MySQL, o --safe-updates pode ser uma configuração segura para utilizar operadores de alteração de dados.

A opção --safe-updates exige que o mysql execute a seguinte instrução ao se conectar ao servidor:

SET sql_safe_updates=1, sql_select_limit=1000, max_join_size=1000000;

• sql_select_limit=1000: limita o conjunto de resultados SELECT a 1.000 linhas, a menos que a instrução inclua LIMIT.
• max_join_size=1.000.000: faz com que as instruções SELECT de várias tabelas produzam um erro se o servidor estimar que deve examinar mais de 1.000.000 combinações de linhas.

Você pode desabilitar o --safe-updates utilizando o comando SET:

SET SQL_SAFE_UPDATES = 0;

Ou configurar para um modo mais conveniente para você, alterando os valores das variáveis:

SET sql_safe_updates=1, sql_select_limit=/*aqui*/, max_join_size=/*aqui*/;

)

Altera dados dentro de uma tabela

modificando algum valor

UPDATE 'banco_de_dados'.'nome_tabela' SET 'nome_coluna' = 'valor' WHERE 'condicao';
UPDATE 'banco_de_dados'.'nome_tabela' SET 'nome_coluna1' = 'valor1', nome_coluna2 = 'valor2'
WHERE condicao;

UPDATE 'banco_de_dados'.'nome_tabela'
SET 'nome_coluna' = 'valor1'
WHERE id IN (1,2,3);

/*
nos ids 1, 2 e 3 terá o valor 'valor1'
*/

UPDATE 'banco_de_dados'.'nome_tabela'
SET 'nome_coluna' = (
CASE id WHEN 1 THEN 'valor1'
              WHEN 2 THEN 'valor2'
              WHEN 3 THEN 'valor3'
	      ELSE 'nome_coluna' -- em todos os outros casos, mantém-se os valores originais

Exclui dados de uma tabela

deletando valor da tabela

DELETE FROM 'banco_de_dados'.'nome_tabela'
WHERE 'nome_coluna' = 'valor';

/*
WHERE é opcional. Porém, sem ele, todas as linhas da tabela seriam excluídas
*/

///////////////////////////////////////////////////////////////////////////////////////////////
Caso haja relações entre as tabelas (primary key e foreign keys) e existam restrições aplicadas a elas, ao executar o DELETE ocorrerá uma ação de acordo com a restrição que tiver sido imposta na criação da foreign key. Essas restrições podem ser as seguintes:

ON DELETE NO ACTION; -- Rejeita o comando DELETE.

ON DELETE RESTRICT; -- Rejeita o comando DELETE.

ON DELETE SET NULL; -- Permite a exclusão dos registros da tabela pai,
e seta para NULL os registros da tabela filho.

ON DELETE CASCADE; -- Exclui a informação da tabela pai e registros relacionados.

---

DCL: Data Control Language - Mais focado nos comandos que concedem direitos, permissões e outros tipos de controle ao sistema de banco de dados.

Concede acesso a um usuário

GRANT

Remove acessos concedidos através do comando GRANT

REVOKE

---

TCL: Transactional Control Language - Lida com as transações dentro de suas pesquisas

Muda suas alterações de temporárias para permanentes no seu banco de dados

COMMIT

Desfaz todo o impacto realizado por um comando

ROLLBACK

Define pontos para os quais uma transação pode voltar. É uma maneira de voltar para pontos específicos de sua query

SAVEPOINT

Comandos que definem onde, como e em que escopo suas transações são executadas

TRANSACTION

---

---

lista de comandos para desinstalar MySQL em sequencia:

sudo apt-get remove mysql-server mysql-client mysql-common
sudo apt-get autoremove
sudo apt-get autoclean
sudo rm -rf /var/lib/mysql
sudo rm -rf /etc/mysql