O objetivo deste capítulo é fazer uma rápida introdução à linguagem SQL.
Subintende-se que você leu a parte 1 deste guia e conhece a ferramenta psql, bem como criar, acessar e excluir um banco de dados PostgreSQL.
PostgreSQL é um sistema de gerenciamento de banco de dados relacional (SGBDR), ou seja, visa gerenciar dados armazenados em tabelas e relacionados de alguma forma.
Nota: A noção de dados armazenados em tabelas é tão comum que, às vezes, parece ser o único modelo existente, mas não é verdade. Existem várias arquiteturas diferentes, como por exemplo bancos de dados orientados a documentos e objetos.
Uma tabela é composta de um conjunto de colunas (propriedades) e linhas (valores). Vale lembrar que as linhas de uma tabela não são necessariamente ordenadas (logicamente), mas poderemos ordenar-las como quisermos durante uma consulta.
Essas tabelas são agrupadas em um banco de dados e um conjunto de bancos de dados gerenciados por uma única instância do servidor PostgreSQL, constituindo assim uma instância de banco de dados ou o que chamamos de cluster.
Você pode criar uma nova tabela especificando o nome dela, seguido pelo nome das colunas e seus respectivos tipos:
CREATE TABLE usuarios (
id int,
nome varchar(80), -- Um comentario daora
dt_nasc date -- Outro comentario daora
);Nota: É possível executar este comando no psql, a ferramenta reconhecerá a quebra de linha até encontrar o ponto e vírgula.
O PostgreSQL suporta os tipos padrão de SQL: int, smallint, real, double, char(N), varchar(N), date, time, timestamp e interval, além de outros tipos de utilitários gerais e um rico conjunto de tipos geométricos.
Como visto no exemplo, comentários podem ser feitos usando --, todo o conteúdo escrito após esse sinal será ignorado.
Por fim, mas não menos importante, se não precisar mais de uma tabela, poderá excluí-la usando o seguinte comando:
DROP TABLE usuarios;O comando INSERT é usado para preencher uma tabela com linhas:
INSERT INTO usuarios VALUES (1, 'John Joe', '1997-02-26');
INSERT INTO usuarios VALUES (2, 'Yara Kazman', '1996-02-26');
INSERT INTO usuarios VALUES (3, 'Gilles Riquier', '1995-02-26');As constantes que não são valores numéricos simples geralmente devem estar entre aspas simples, como no exemplo. O tipo de data é um dos mais flexíveis e aceita diversos formatos, neste caso, usaremos o formato americano.
Note que, essa sintaxe nos força a lembrar a ordem das colunas, bem como informar um valor para cada uma delas. Uma sintaxe alternativa permite listar explicitamente as colunas desejadas:
INSERT INTO usuarios (id, nome)
VALUES (1, 'John Joe');Muitos desenvolvedores consideram a segunda alternativa um estilo melhor do que depender de ordem implícita.
Os valores de uma tabela podem ser consultados através de uma instrução chamada SELECT.
Por exemplo, para consultar todas as linhas na tabela usuarios:
SELECT * FROM usuarios;O sinal * quer dizer "todas as colunas", trata-se de outra forma de escrever o comando a seguir:
SELECT id, nome, dt_nasc FROM usuarios;O resultado obtido deve ser algo como:
id | nome | dt_nasc
----+----------------+-----------
1 | John Joe | 1997-02-26
2 | Yara Kazman | 1996-02-26
3 | Gilles Riquier | 1997-02-26
(3 rows)É possivel escrever expressões e/ou usar métodos SQL existentes, como count(), por exemplo:
SELECT count(id) AS count FROM usuarios;O resultado esperado é:
count
------
3
(1 row)Observe como AS é usado para nomear a coluna gerada a partir do método count.
É possivel adicionar condiçoes a consulta por meio da cláusula WHERE, ela é baseada em uma expressao booleana e somente as linhas que atenderem essa condiçao serao retornadas. Podemos ainda utilizar os operadores AND, OR e NOTpara complementar, por exemplo:
SELECT * FROM usuarios
WHERE id > 1 AND id <> 2;O resultado esperado é:
id | nome | dt_nasc
----+----------------+-----------
3 | Gilles Riquier | 1997-02-26
(1 row)Você pode solicitar que os resultados de uma consulta sejam retornados em uma ordem específica:
SELECT * FROM usuarios
ORDER BY nome; id | nome | dt_nasc
----+----------------+-----------
3 | Gilles Riquier | 1997-02-26
1 | John Joe | 1997-02-26
2 | Yara Kazman | 1996-02-26
(3 rows)E que as linhas duplicadas sejam removidas do resultado de uma consulta:
SELECT DISTINCT dt_nasc
FROM usuarios; dt_nasc
-----------
1997-02-26
1996-02-26
(2 rows)Neste tópico, criaremos uma segunda tabela chamada veículos, na qual atribuiremos veículos de um determinado tipo aos usuários.
CREATE TABLE veiculos (
id int,
tipo varchar(80), -- carro, moto
dono varchar(80)
);Nota: Leia um pouco sobre Enumerated Types, poderia ser util no campo tipo.
Em seguida, vamos inserir alguns registros:
INSERT INTO veiculos VALUES (1, 'carro', 'John Joe');
INSERT INTO veiculos VALUES (2, 'moto', 'Camille Rivard');
INSERT INTO veiculos VALUES (3, 'carro', 'Delphine Chartier');Agora, podemos descobrir quais usuários do nosso sistema possuem (ou não) um veículo!
Provavelmente o método mais conhecido e utilizado, retorna somente os registros comuns às duas (ou mais) tabelas.
SELECT usuarios.nome, veiculos.tipo
FROM usuarios
INNER JOIN veiculos
ON usuarios.nome = veiculos.dono nome | tipo
---------------+-----------
John Joe | carro
(1 row)Retorna todos os registros da tabela base (LEFT) e os registros comuns às duas (ou mais) tabelas.
SELECT usuarios.nome, veiculos.tipo
FROM usuarios
LEFT JOIN veiculos
ON usuarios.nome = veiculos.dono nome | tipo
-----------------+----------
John Joe | carro
Yara Kazman | NULL
Gilles Riquier | NULL
(3 rows)Retorna todos os registros das demais tabelas (RIGHT) e os registros comuns às duas (ou mais) tabelas.
SELECT usuarios.nome, veiculos.tipo
FROM usuarios
RIGHT JOIN veiculos
ON usuarios.nome = veiculos.dono nome | tipo
-----------------+----------
John Joe | carro
NULL | moto
NULL | carro
(3 rows)Retorna todos os registros nas tabelas relacionadas, independentemente de registros comuns ou não.
SELECT usuarios.nome, veiculos.tipo
FROM usuarios
FULL OUTER JOIN veiculos
ON usuarios.nome = veiculos.dono nome | tipo
-----------------+----------
John Joe | carro
Yara Kazman | NULL
Gilles Riquier | NULL
NULL | moto
NULL | carro
(5 rows)Vale ressaltar que ainda existem três outros tipos de ligação:
- LEFT EXCLUDING JOIN
- RIGHT EXCLUDING JOIN
- OUTER EXCLUDING JOIN
Embora sejam usados com menos frequência, vale a pena pesquisar um pouco sobre isso.
É possivel atualizar registros usando o comando UPDATE.
Suponha que John Joe trocou seu carro por uma moto, podemos corrigir os dados da seguinte maneira:
UPDATE veiculos
SET tipo = 'moto'
WHERE dono = 'John Joe'Se tudo occorreu bem:
SELECT usuarios.nome, veiculos.tipo
FROM usuarios
INNER JOIN veiculos
ON usuarios.nome = veiculos.dono nome | tipo
---------------+-----------
John Joe | moto
(1 row)Agora supondo que John Joe foi atingido pela crise e precisou vender sua moto, podemos utilizar o comando DELETE para deletar o registro.
DELETE FROM veiculos WHERE dono = 'John Joe';Nota: Se John Joe tivesse mais de um veículo, esse comando teria removido todos eles! Portanto, é recomendável usar id's ao invés de nomes.
Nota (2): Nenhum tipo de confirmação sera solicitada antes de executar uma query, pense com cuidado antes de executar um comando!