PostgreSQL Prático/DML/Consultas
DML editar
É o conjunto de comandos SQL responsáveis pela manipulação dos dados: inserir, consultar, atualizar e excluir.
Consultas
Atente para que suas consultas sejam:
- simples e claras
- contenham somente campos estritamente necessários
- sejam otimizadas para o desempenho máximo
SQL (Structure Query Language) - É uma linguagem declarativa, onde você diz ao computador o que deseja fazer e deixa a máquina decidir a forma correta de chegar ao resultado.
Para o primeiro contato com o PostgreSQL e para ter certeza de que o mesmo está corretamente instalado e configurado, podemos digitar na linha de comando do sistema operacional (como usuário do postgresql):
psql --version
psql -l
O psql é o programa de gerenciamento e uso do PostgreSQL pelo usuário local.
Com ele podemos fazer praticamente tudo que se pode fazer com o PG.
Alguns programas estão disponíveis na linha de comando do sistema operacional, permitindo criar e excluir bancos, criar e excluir usuários, entre outros. Os programas aí disponíveis dependem da versão instalada, do sistema operacional e da forma que foi instalado.
Quem instala através dos fontes (sources) tem um sub-diretório chamado contrib, onde estão os demais programas desenvolvidos pela comunidade de programadores do PG. Neste caso para instalar um destes programas execute "make; make install" estando no respectivo diretório. Um exemplo é o pgbench.
Os comandos via linha de comandos do SO, normalmente terminam com "db" e são formados com apenas uma palavra, createdb, por exemplo. Já de dentro do psql, eles normalmente são formados por duas palavras, como por exemplo,
CREATE DATABASE.
Os comandos a seguir serão executados na linha de comando do SO. Supondo que o super-usuário seja "postgres".
Forma mais geral de uso:
nome_comando opção -U nomeuser
Criar um banco de dados:
createdb controle_estoque -U postgres
Visualizar o banco criado:
psql -l -U postgres
Excluir o banco criado:
dropdb controle_estoque -U postgres
Ajuda sobre os comandos:
nome_comando --help
Acessar o banco criado através do terminal interativo de gerenciamento do PostgreSQL (psql):
psql controle_estoque -U postgres
D:\Arquivos de programas\PostgreSQL\8.1\bin>psql controle_estoque -U postgres
Bem vindo ao psql 8.1.3, o terminal iterativo do PostgreSQL.
Digite: \copyright para mostrar termos de distribuição
\h para ajuda com comandos SQL
\? para ajuda com comandos do psql
\g ou terminar com ponto-e-vírgula para executar a consulta
\q para sair
controle_estoque=#
Este é o prompt do psql. Veja que já nos recebe com boas vindas e com dicas de como podemos a qualquer momento receber ajuda. Especialmente atente para os comandos:
\h - para receber ajuda sobre comandos SQL. \h comando - ajuda sobre um comando
\? - ajuda sobre os comandos de operação do terminal psql
- - é o comando para indicar ao PG que execute nossa seqüência de comandos
\q - para sair do psql
Obs.: Aceita quebras de linha para uma seqüência de comandos.
Mesmo que possamos utilizar ferramentas gráficas ou Web para gerenciar o PG, é altamente recomendado que nos familiarizemos com a sintaxe dos comandos para entender como os comandos são executados internamente e ter maior domínio sobre o PG. Depois dessa fase, os que resistem aos encantos do psql :) podem usar uma das citadas ferramentas.
Vamos executar alguns comandos do psql e algumas pequenas consultas para ficarmos mais à vontade.
\l -- lista bancos, donos e codificação
\d -- descreve tabela, índice, seqüência ou view (visão)
\du -- lista usuários e permissões
\dg -- lista grupos
\dp -- lista privilégios de acesso à tabelas, views (visões) e sequências
psql controle_estoque -U postgres
controle_estoque=# SELECT version();
version
PostgreSQL 8.1.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2 (mingw-special)
Para distinguir convencionou-se que as palavras chave do SQL sejam escritas em maiúsculas, mas podem ser escritas em minúsculas sem problema para o interpretador de comandos.
SELECT 25*4;
SELECT current_date;
4.1 - Consultas Básicas em SQL
SELECT – selecionar registros de tabelas
banco=# \h select -- da ajuda via psql
Comando: SELECT
Descrição: recupera (retorna) registros de uma tabela ou visão (view)
Sintaxe:
SELECT [ ALL | DISTINCT [ ON ( expressão [, ...] ) ] ]
* | expressão [ AS nome_saída ] [, ...]
[ FROM item_de [, ...] ]
[ WHERE condição ]
[ GROUP BY expressão [, ...] ]
[ HAVING condição [, ...] ]
[ { UNION | INTERSECT | EXCEPT } [ ALL ] select ]
[ ORDER BY expressão [ ASC | DESC | USING operador ] [, ...] ]
[ LIMIT { contador | ALL } ]
[ OFFSET início ]
[ FOR { UPDATE | SHARE } [ OF nome_tabela [, ...] ] [ NOWAIT ] ]
ASC é o default
Item_de pode ser um dos:
[ ONLY ] nome_tabela [ * ] [ [ AS ] alias [ ( alias_coluna [, ...] ) ] ]
( select ) [ AS ] alias [ ( alias_coluna [, ...] ) ]
nome_função ( [ argumento [, ...] ] ) [ AS ] alias [ ( alias_coluna [, ...] | definição_coluna [, ...] ) ]
nome_função ( [ argumento [, ...] ] ) AS ( definição_coluna [, ...] )
item_de [ NATURAL ] tipo_junção item_de [ ON condição_junção | USING ( coluna_junção [, ...] ) ]
Sintaxe resumida:
SELECT * FROM tabela; -- retorna todos os registros da tabela com todos os campos
A lista_de_campos é o retorno da consulta.
Exemplos:
1) SELECT siape AS “Matricula do Servidor” FROM pessoal;
2) SELECT pessoal.siape, pessoal.senha, locacoes.lotacao
FROM pessoal, lotacoes WHERE pessoal.siape = lotacoes.siape
ORDER BY lotacoes.lotacao;
DISTINCT – Escrita logo após SELECT desconsidera os registros duplicados, retornando apenas registros exclusivos.
SELECT DISTINCT email FROM clientes;
ALL é o contrário de DISTINCT e é o padrão, retornando todos os registros, duplicados ou não.
Ao fazer uma consulta, um registro será considerado igual a outro se pelo menos um campo for diferente. E os todos os valores NULL serão considerados iguais.
CLÁUSULA WHERE - Filtra o retorno de consultas.
Operadores aceitos: =, >, <, <>, !=, >=, <=
SELECT nome FROM clientes WHERE email = 'ribafs@ribafs.org';
SELECT nome FROM clientes WHERE idade > 18;
SELECT nome FROM clientes WHERE idade < 21;
SELECT nome FROM clientes WHERE idade >= 18;
SELECT nome FROM clientes WHERE idade <= 21;
SELECT nome FROM clientes WHERE UPPER(estado) != 'CE';
SELECT nome FROM clientes WHERE email = 'ribafs@ribafs.org';
BETWEEN, LIKE, OR, AND, NOT, EXISTS, IS NULL, IS NOT NULL, IN
SELECT nome FROM clientes WHERE idade BETWEEN 18 and 45;
SELECT nome FROM clientes WHERE email LIKE '%@gmail.com';
SELECT nome FROM clientes WHERE idade >18 21 OR idade < 21; -- entre 18 e 21
SELECT nome FROM clientes WHERE idade >= 18 AND UPPER(estado) = 'CE';
SELECT nome FROM clientes WHERE idade NOT BETWEEN 18 AND 21;
SELECT * FROM datas WHERE EXISTS(SELECT * FROM datas2 WHERE datas.data = datas2.data);
SELECT nome FROM clientes WHERE estado IS NULL;
SELECT nome FROM clientes WHERE estado IS NOT NULL;
SELECT nome FROM clientes WHERE estado IN ('CE', 'RN');
GROUP BY - Geralmente utilizada com funções de agrupamento (de agregação), como também com HAVING. Agrupa o resultado dos dados por um ou mais campos de uma tabela. Utilizado para agrupar registros (linhas) da tabela que compartilham os mesmos valores em todas as colunas (campos) da lista.
Exemplos:
SELECT SUM(horas) FROM empregados; -- Traz a soma das horas de todos os empregados
SELECT empregado, SUM(horas) FROM empregados GROUP BY empregado; -- Traz a soma das horas de cada empregado. Veja que “empregado” deve aparecer em GROUP BY, já que os campos de retorno diferentes do usado na função de agrupamento devem vir no GROUP BY.
Dica: Quando se utiliza uma função de agrupamento num campo da lista do SELECT, os demais campos da lista deverão ser agrupados. Exemplo:
SELECT codigo, nome, count(valor) FROM vendas GROUP BY codigo, nome.
Exemplo:
SELECT c.nome, COUNT(p.quant) AS quantos
FROM clientes c, pedidos p
WHERE c.codigo = p.cod_cliente
GROUP BY (p.cod_cliente);
HAVING - Filtra o retorno de GROUP BY. Não altera o resultado,apenas filtra.
Exemplo:
SELECT cliente, SUM(quant) AS total
FROM pedidos GROUP BY cliente
HAVING total > 50; -- ou HAVING SUM(quant) > 50;
ORDER BY - Ordena o resultado da consulta por um ou mais campos em ordem ascendente (ASC, default) ou descendente (DESC).
Exemplos:
ORDER BY cliente; -- pelo cliente e ascendente
ORDER BY cliente DESC; -- descendente
ORDER BY cliente, quantidade; -- pelo cliente e sub ordenado pela quantidade
ORDER BY cliente DESC, quant ASC;
No exemplo ordenando por dois campos:
SELECT * FROM pedidos ORDER BY cliente, quantidade; A saída ficaria algo como:
Antônio – 1
Antônio – 2
João - 1
Pedro - 1
Pedro - 2
INSERT – Inserir registros em tabelas.
banco=# \h insert
Comando: INSERT
Descrição: insere novos registros em uma tabela
Sintaxe:
INSERT INTO tabela [ ( lista_de_campos ) ]
{ DEFAULT VALUES | VALUES ( { expressão | DEFAULT } [, ...] ) | consulta }
DEFAULT - Se ao criar a tabela definirmos campos com valor default, ao inserir registros e omitir o valor para estes campos, o servidor os cadastrará com o valor default.
Exemplo (forma completa):
Na tabela o campo idade tem valor default 18.
INSERT INTO clientes (codigo, nome, idade) VALUES (1, “Ribamar FS”);
Neste exemplo será cadastrado para a idade o valor 18.
Forma Abreviada:
INSERT INTO clientes VALUES (1, “Ribamar FS”);
Não é recomendada, por não ser clara nem adequada para trabalho em grupo. Caso utilizemos esta forma somos obrigados a inserir os campos na ordem original em que estão na tabela.
Inserindo com SubConsulta:
INSERT INTO clientes (codigo, nome, idade) VALUES
(SELECT fnome, fidade FROM funcionarios WHERE cli = 'S');
SELECT firstname, lastname, city, state INTO newfriend FROM friend;
UPDATE - Atualizar registros de tabelas
banco=# \h update
Comando: UPDATE
Descrição: atualiza registros de uma tabela
Sintaxe:
UPDATE [ ONLY ] tabela SET coluna = { expressão | DEFAULT } [, ...]
[ FROM lista_de ]
[ WHERE condição ]
Exemplos:
UPDATE clientes SET idade = idade + 1; -- Todos os registros de clientes serão atualizados
UPDATE pedidos SET quant = quant + 3
WHERE cliente IN (SELECT codigo FROM clientes WHERE idade > 18);
DELETE - Remover registros de tabelas
banco=# \h delete
Comando: DELETE
Descrição: apaga registros de uma tabela
Sintaxe:
DELETE FROM [ ONLY ] tabela
[ USING lista_util ]
[ WHERE condição ]
Exemplos:
DELETE FROM pedidos; -- Cuidado, excluirá todos os registros da tabela pedidos
DELETE FROM pedidos WHERE (codigo IS NULL); - - Remove sem confirmação nem com opção de desfazer.
Trabalhando corretamente com select
Bom artigo do DeCo no PHPAvancado