PostgreSQL Prático/DML/Consultas

É 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

http://www.phpavancado.net/node/165