Documentando coisas simples - SQL

Descrição

Essa publicação serve apenas como um repositório de informações sobre banco de dados.

Schemas

Um schema é uma forma de organizar um conjunto de tabelas dentro de um banco de dados. Já utilizei como forma de segregar clientes e contextos. Com contexto, me refiro a um conjunto de coisas que se relacionam.

CREATE SCHEMA billing;

Grants

Grants são basicamente comandos que concedem determinado tipo de permissão para um usuário sobre algum recurso do banco de dados.

GRANT SELECT, INSERT ON TABELA.* TO 'USER'

O inverso é o comando REVOKE, que simplesmente revoga a permissão.

REVOKE SELECT ON TABELA.* FROM 'USER'

Para listar os grants do seu usuário, basta executar

SHOW GRANTS

Index

Índices servem para otimizar a performance de queries que utilizam determinada coluna como identificador para buscas. Chaves primárias são indexadas por padrão.

Caso um dado seja buscado por uma query, e caso a coluna que a query usa para performar a busca não seja indexada, o banco de dados usará a tabela toda (type ALL) como base para performar a busca. Buscas indexadas possuem o type REF.

ALTER TABLE TABELA ADD INDEX IX_ALGUM_NOME_PARA_INDICE (COLUNA)

CREATE INDEX IX_ALGUM_NOME_PARA_INDICE ON TABELA(COLUNA)

Ademais, é possível criar index sobre uma combinação de colunas para otimizar buscas mais complexas. Ficaria algo como:

CREATE INDEX IX_ALGUM_NOME_PARA_INDICE 
ON TABELA(COLUNA1, COLUNA2);

Comandos úteis

Group By

O group by serve para agrupar determinado dado de acordo com algum valor de referencia. Na query abaixo, estou retornando os emails que possuem mais de uma ocorrencia na tabela Person.

SELECT email FROM person
group by email
having count(email) > 1

Na query abaixo, estou agrupando o valor somado das faturas para cada estado de fatura.

select invoice_state, sum(invoice_amount) 
from service_order_invoice soi 
group by soi.invoice_state 

Count + Join

select p.person_id, p.person_email, count(d.delivery_customer) as "Qtd. pedidos" from person p
join delivery d on d.delivery_customer = p.person_id  
group by p.person_id 

Subselect;

select p.person_id, p.person_email from person p
where p.person_id in (select distinct delivery.delivery_customer from delivery)

Limit e Offset

O comando Limit serve para limitar a quantidade de linhas retornadas de uma query sql. O comando offset serve para informar quantas linhas devem ser ignoradas (de cima para baixo) do que foi retornado. Na query de exemplo, estou pegando o segundo maior salário OU null.

select IFNULL (
    (SELECT distinct salary FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 1
), null) AS SecondHighestSalary

Datediff

Essa função serve para verificar a diferença de dias entre registros.

No exemplo abaixo, estou selecionando as datas que possuem temperaturas maiores se comparada com o dia anterior (Ex: se hoje está mais quente que ontem, retorna).

SELECT w1.id FROM Weather w1, Weather w2
WHERE w1.temperature > w2.temperature
AND DATEDIFF(w1.recordDate, w2.recordDate) = 1;

Min e Max

Serve para pegar o menor e o maior valor de uma determinada coluna, respectivamente.

SELECT player_id, min(event_date) as first_login from Activity group by player_id

WIP