SQL Conceitos Básicos. Postgres + Docker

Jennifer Omena — Wed, 29 Jan 2020 14:42:38 +0000

Criei este tutorial para aprimorar e obter meus conhecimentos na tecnologia SQL para uso no meu dia-a-dia como desenvolvedora.

Structured Query Language (SQL) é uma linguagem que usamos para “conversar” com o banco de dados. Ela é organizada em alguns subconjuntos, como:

DQL — Data Query Language A Linguagem de Consulta de Banco de Dados define qual comando utilizar para fazer a consulta (SELECT) dos dados;
DML — Data Manipulation Language A Linguagem de Manipulação de Dados define os comandos para manipular os dados já contidos no banco (INSERT, UPDATE e DELETE);
DDL — Data Definition Language A Linguagem de Definição de Dados define quais comandos usar para criar tabelas, views, índices (CREATE), atualizar (ALTER) e/ou remover (DROP);
DCL — Data Control Language A Linguagem de Controle de Dados define os comandos para controlar o acesso aos dados do banco, adicionando (GRANT) e removendo (REVOKE) permissões de acesso;
DTL — Data Transaction Language A Linguagem de Transação de Dados define quais comandos gerenciam as transações executadas no banco de dados, como iniciar uma transação (BEGIN), confirmar uma transação (COMMIT) ou desfazer uma transação (ROLLBACK).

Criação de Banco de Dados

A sintaxe do banco de dados é bem intuitiva. Para iniciarmos, precisamo criar um banco de dados:

CREATE DATABASE teste

Para criarmos um banco eficiente e útil, precisamos seguir alguns passos:

Identificar o objetivo do banco de dados, ou fazer uma análise de requisitos;
Organizar os dados em tabelas;
Especificar chaves primárias e analisar relações;
Padronizar as tabelas.

Identificar o objetivo do banco de dados.

Precisamos compreender a finalidade do banco de dados para nos orientarmos durante todo o processo de criação, reunir dados existentes que serão incluídos, listar os tipos de dados, entidades, pessoas, eventos que serão armazenados.
Exemplo: Lista Telefônica

Nome
Sobrenome
DDD
Número do telefone Depois de entender e saber quais dados serão incluídos em seu banco de dados, saber de onde esses dados vêm e como eles serão utilizados, podemos começar a planejar nosso banco de dados real.

Estruturando o banco de dados.

Neste passo, é preciso entender como os bancos de dados relacionais são estruturados. Os dados relacionados são agrupados em tabelas, que possuem colunas (campos ou atributos) e linhas (tuplas ou registros) assim como uma planilha.
Cada linha é chamada de registro e esse registro inclui dados sobre algo, como uma pessoa.
As colunas contêm um único tipo de informação que aparece em cada registro.

Os tipos de dados são para manter os dados consistentes de um registro para outro. Vejamos os tipos de dados mais comuns:

INT — inteiro positivo ou negativo;
CHAR — tamanho específico de texto;
VARCHAR — texto de tamanhos variáveis;
TEXTO — grande quantidade de texto;
BOOL — boleanos;
FLOAT, DOUBLE — números de pontos flutuantes. ‌Para criar uma visão geral do banco (diagrama entidade-relacionamento), cada tabela é uma caixinha do diagrama, o título de cada caixinha descreve o que essa tabela contém e nelas são listados seus atributos.

Após criar a visão geral da tabela, devemos decidir qual atributo será a chave primária (PK), se houver.

Primary Key (PK) — Chave Primária é um identificador para uma determinada entidade que contem um valor único, imutável e sempre presente, não podendo nunca ser nulo ou vazio.

Criando relações entre entidades.

Quando falamos em relações entre entidade falamos de cardinalidade e essa cardinalidade se refere à quantidade de elementos que interagem entre duas tabelas relacionadas. Ao identificar a cardinalidade, estamos garantindo a eficiência da divisão dos dados nas tabelas.
Temos três tipos de relacionamentos:

1:1 Cada um dos elementos de uma entidade só pode ser relacionar com apenas um elemento de outra entidade. Exemplo: Uma pessoa só pode ter um CPF e um CPF só pode pertencer a uma pessoa.

1:N Cada elemento de uma entidade pode ter um relacionamento com vários elementos de outra entidade. Exemplo: Um departamento pode ter vários funcionários, mas um funcionário só pode pertencer a um departamento.

N:N Vários elementos de uma entidade podem se relacionar com vários elementos de outra entidade. Mas diferente dos outros dois relacionamentos, é sugerido criar uma tabela intermediária que relacionada as outras duas. Exemplo: Um filme possui vários atores, e um ator pode atuar em vários filmes.

Vamos por em prática...

Primeiro, o que é um Docker?

Docker é uma plataforma open source de alto desempenho, desenvolvida em Go e criada pelo Google, que garante maior facilidade em criar e administrar ambientes isolados, assegurando rápida disponibilização de programas para o usuário final.
Seu objetivo é criar, testar e implementar aplicações em um ambiente separado, chamado container, permitindo que o desenvolvedor consiga empacotar o software de maneira padronizada, já que a plataforma disponibiliza código, bibliotecas, runtime e ferramentas do sistema.

E por quê usar Docker?

O maior benefício do uso da plataforma é agilizar até 7 vezes mais a disponibilização do software do que em virtualizações convencionais. Outros benefícios que podemos citar são:

Possibilidade de configurar diferentes ambientes de forma rápida, diminuindo incompatibilidades entre sistemas disponíveis;
Modularizar, permitindo desabilitar uma parte do dispositivo, para realizar atualizações de reparo ou adição de funcionalidades sem interromper todo o programa;
Formar diversas camadas diferentes, onde se dividem em dois grupos: Imagens: o usuário pode compartilhar um aplicativo ou um conjunto de serviços em diversos ambientes. Quando há qualquer alteração na imagem, ou uso de um comando como executar ou copiar, é criada uma camada.

Containers: é o local onde estão as modificações da aplicação que está em execução. Nele é onde o usuário pode modificar uma imagem.

Reverter para recuperar a versão anterior. É possível por conta das camadas que foram criadas;
Implantação rápida, pois o tempo e desempenho ocorrem simultaneamente.

PostgreSQL

O Postgres oferece opção de criar e executar instâncias por meio de containers Docker.

E por que escolhi o Postgres?

Pela possibilidade de execução de sua ferramenta gráfica de administração a partir de um container.
Antes de mais nada, precisamos ter uma imagem presente na máquina onde os containers existirão. Para isso vamos executar o seguinte código no terminal:

docker pull postgres

Caso queira listar todas as imagens disponíveis no seu sistema:

docker images

Para manter o estado à medida que você vai trabalhando com o container, precisa commitar as alterações:

//docker commit [ID do container] [nome da imagem]
docker commit afd8110f1813 postgres

Trabalhando com containers.

Listando todos os seus containers atuais (tantos os running quanto os non-running):

docker ps -l

Iniciando uma instância.

//docker run --name [Nome do seu banco de dados] -e POSTGRES_PASSWORD=[Senha do seu banco de dados] -d postgres
docker run --name teste-postgres -e POSTGRES_PASSWORD=Postgres2019! -d postgres

E verifica se o docker foi instanciado em sua máquina:
O comando docker ps -a lista todas as instâncias.

Agora dê um start com docker start [CONTAINER ID] e docker ps para conferir se foi:

Plataforma de banco de dados

Eu uso o dbeaver-ce e vou utilizá-lo neste tutorial.

Abra o dbeaver e clique em New database connection.

Selecione PostgreSQL

E configure sua conexão, nesse caso precisa apenas colocar a senha que colocamos ao dar docker run, lá em iniciando uma instância.
Password: Postgres2019!

Antes de concluir, teste a conexão. Logo após pode dar ok e mãos a obra.

Criando Schemas.

Você pode fazer de duas maneiras, uma é clicando com o botão direito sobre Schema > New Schema; ou clicar com o botão direito sobre PostgreSQL — postgres > SQL Editor.

Vamos com a segunda opção.

Vai abrir uma janela igual da imagem abaixo:

Nesta janela é onde vamos escrever nossos scripts.

CREATE SCHEMA lista_telefonica

‌Selecione tudo, dê Ctrl + Enter, clique com o botão direito sobre PostgreSQL — postgres > Refresh.
Note que seu Schema foi criado:

Criando Tabelas.

CREATE SEQUENCE sq_lista_telefonica START WITH 1;
CREATE TABLE lista_telefonica(
    id           INT8             NOT NULL,
    nome         VARCHAR(20)      NOT NULL,
    sobrenome    VARCHAR(20)      NOT NULL,
    ddd          CHAR(2)          NOT NULL,
    telefone     CHAR(9)          NOT NULL,
    CONSTRAINT pk_lista_telefonica PRIMARY KEY (ID),
    CONSTRAINT uk_lista_telefonica  UNIQUE (ddd, telefone)
);
CREATE UNIQUE INDEX ix_lista_telefonica ON lista_telefonica (ddd, telefone);

Primeiro vamos entender o script acima.

CREATE SEQUENCE sq_lista_telefonica START WITH 1; -> Vai fazer com que o id da sua tabela seja auto incrementado a partir do 1. Mas há uma observação sobre o sequence: rodando o script direto no banco pelo dbeaver, o sequence não é usado, mas na aplicação onde tem explicitamente essa ligação, funciona. Para funcionar tanto na aplicação quanto direto no banco, teríamos que usar default nextval('nome_da_sequencia') quando a tabela fosse criada ;
CREATE TABLE lista_telefonica -> cria uma tabela;
( ... ) -> Tudo o que estiver entre parêntesis serão dados de sua tabela;
id / nome / sobrenome / ddd / telefone -> são suas colunas;
INT8 -> tipo inteiro com 8 bytes; VARCHAR(20) -> comprimento variável com limite e o limite fica entre ( );
CHAR(2) -> comprimento fixo, completado com brancos;
NOT NULL -> ao usar not null, permitimos que valores sejam adicionados posteriormente;
CONSTRAINT pk_lista_telefonica PRIMARY KEY (ID) -> A restrição PRIMARY KEY (Chave Primária) indica que uma coluna ou grupo de colunas identifica de forma única cada registro em uma tabela;
CONSTRAINT uk_lista_telefonica UNIQUE (ddd, telefone) -> A restrição - UNIQUE assegura que os dados sejam únicos.

Puts, coloquei o tipo errado, e agora? Vamos supor que coloquei o tipo do id errado, ao invés de INT8, quero INT2:

ALTER TABLE lista_telefonica.lista_telefonica ALTER COLUMN id TYPE int2
USING id::int2;

lista_telefonica.lista_telefonica
Porque está duplicado?
O primeiro 'lista_telefonica' é o nome do Schema e o segundo é o nome da tabela.

Ou você pode alterar clicando duas vezes sobre a coluna id(int8) e em Data type, selecionar int2:

Vamos ver as mudanças:

Inserção de Registros.

INSERT
  INTO
  lista_telefonica.lista_telefonica (id,
  nome,sobrenome,
  ddd,
  telefone)
VALUES (1,
  'Jennifer',
  'Omena',
  '21',
  '977778888');

INSERT
  INTO
  lista_telefonica.lista_telefonica (id,
  nome,sobrenome,
  ddd,
  telefone)
VALUES (2,
  'Leonardo',
  'Omena',
  '21',
  '988887777');

Exclusão de Registros.

Digamos que adicionei um registro erroneamente e quero excluir do meu banco.

Vamos deletar o quinto registro:

DELETE
FROM
  lista_telefonica.lista_telefonica
WHERE
  ID = 5;

Select Básico.

O Select serve para buscar os dados de uma tabela do banco de dados, retornando dados em forma de tabela.
Digamos que quero saber o nome de todo mundo que está salvo na minha lista telefônica:

SELECT
    nome,
    sobrenome
FROM
    lista_telefonica.lista_telefonica;

Atualização de Registro

Vamos fazer de conta que colocamos o sobrenome do Luciano e da Raquel errado, e precisamos atualizar esses dados. Quero mudar de Ferraz para Mascarenhas.

UPDATE
    lista_telefonica.lista_telefonica
SET
    sobrenome = 'Mascarenhas'
WHERE
    sobrenome = 'Ferraz'

Traduzindo o script acima fica: Atualize no schema lista_telefonica na tabela lista_telefonica para sobrenome Mascarenhas onde sobrenome está Ferraz.

Condicionais

Antes de mais nada, vou inserir mais dois registros na nossa lista telefônica. Observe:

Mas descobrimos que o ddd de Jessica e Giselle foi salvo como 22, mas é ddd 21. Vamos corrigir usando condicional, where:

UPDATE
    lista_telefonica.lista_telefonica
SET
    ddd = '21'
WHERE
    ddd = '22'

Ordenação

Vamos pegar todas as colunas e registros e ordenar pelos nomes por ordem alfabética decrescente:

select
    *
from
    lista_telefonica.lista_telefonica
order by
    nome desc;

Agrupamentos

Antes de mais nada, vamos inserir mais alguns registros na nossa lista telefônica:

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (7,'Ana','Souza', '91', '922221111');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (8,'Amanda','Silva', '81', '922223333');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (9,'Amadel','Ferreira', '71', '944443333');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (10,'Jorge','Cabral', '91', '977773333');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (11,'Xandi','Lira', '81', '988882222');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (12,'Leandro','Mariani', '91', '900007777');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (13,'Fábio','Lopes', '51', '966661111');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (14,'Manuel','Freitas', '21', '955559999');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (15,'Sonia','Carvalho', '71', '988880000');

INSERT INTO lista_telefonica.lista_telefonica (id, nome,sobrenome,ddd,telefone) VALUES (16,'Marta','Estevez', '81', '911110000');

Agora quero agrupar todo mundo que tenha o mesmo sobrenome, ordernar por ddd e quero saber quantas pessoas têm o mesmo sobrenome. Para isso, vamos precisar de um contador.

SELECT
    sobrenome,
    ddd,
    count(id) "total agrupado"
FROM
    lista_telefonica.lista_telefonica lt
GROUP BY
    ddd, sobrenome
ORDER BY  
    ddd

Bom, é isso. Espero que tenham curtido. Aceito todo tipo de feedback.
Compartilhem com seus amigos.

Até a próxima!

DEV Community: Jennifer Omena