Upload
thiago-peregrino
View
1.917
Download
5
Embed Size (px)
Citation preview
Laboratório de Banco de Dados
Introdução ao PostgreSQL
Profs. Valéria Gonçalves SoaresMateus Fernandes Cunha
DIMAp/UFRN
Conteúdo
� Introdução ao PostgreSQL� Conceitos Básicos
� Acessando o Banco de Dados� Linguagem SQL
� Definição de Dados� Manipulação de Dados� Consulta aos Dados� Funções e Operadores
� Gatilhos e Procedimentos� Administração da Base de Dados
� Conceitos Básicos� Desenvolvimento de Aplicativos
Objetivo da Disciplina
� Oferecer aos alunos condições de trabalharcom um Banco de Dados Relacional, deixando-osaptos a:� Criar uma Base de Dados;� Manipular a Base de Dados;� Consultar a Base de Dados.
� Oferecer subsídios ao desenvolvimento de umaplicativo que acesse o banco de dados edisponibilize todas as funcionalidades básicasrequeridas de um sistema de BD.
Introdução ao PostgreSQL
� Embora o PostgreSQL seja um sistemagerenciador de banco de dados objeto-relacional, nesta disciplina iremos abordarapenas os aspectos relacionais do mesmo.� O PosgreSQL é baseado no POSGRES que foi
pioneiro em muitos conceitos que se tornaramdisponíveis posteriormente em muitossistemas de bancos de dados comerciais.
� O PosgreSQL é open-source e suporta tantoo SQL92 como o SQL99, além de oferecermuitas características modernas, tais como:
Introdução ao PostgreSQL
� Características PostgreSQL:� Consultas complexas� Chaves estrangeiras� Gatilhos� Visões� Integridade transacional� Controle de concorrência
multiversão
� PostgreSQL pode serestendido pelo usuário demuitas maneiras, porexemplo, pela adição denovos:� Tipos de dados� Funções� Operadores� Funções de Agregação� Métodos de Indexação� Linguagens Procedurais
Arquitetura PostgreSQL
� O PostgreSQL usa um modelo cliente/servidor. Umasessão PostgreSQL consiste dos seguintes programasque cooperam entre si:� Um processo servidor que gerencia os arquivos de bancos de
dados, aceita as conexões ao banco a partir das aplicaçõesclientes, e executa ações no banco de dados solicitadas pelosclientes. O programa servidor de banco de dados é chamadopostmaster.
� Cliente(front-end): São as aplicações que precisam acessar aobanco:
� Pode ser ferramentas gráficas, modo texto, via Web,ferramentas de manutenção e gerenciamento de banco,ferramentas de programação de triggers e procedures, ouqualquer outra ferramenta que precise interagir com oPostgreSQL.
� A comunicação entre Cliente e Servidor é feita via TCP/IP.
Arquitetura PostgreSQL
� O servidor PostgreSQL pode gerenciarmúltiplas conexões de clientes de modoconcorrente.
� Para cada conexão, o servidor inicia um novoprocesso servidor para a comunicação com ocliente (“fork”).
� A comunicação entre o servidor e o cliente éfeita sem intervenção do postmaster.
� Quando o postmaster recebe um novo pedido deconexão, ele cria um novo fork que fará acomunicação entre o cliente e o servidorPostgreSQL.
Primeiros Passos
� Para acessar o PostgreSQL dentro do DIMApinicialmente precisa-se acessar a máquina loucoservidora do bd:ssh nome_da_maquina ou ssh (ip da maquina)
� Ou direto, através do psql:psql meubanco –U nomeusuario –h
nome_da_maquina
Criando um Banco de Dados
� Um servidor PostgreSQL pode gerenciar vários bancos.Normalmente, um banco separado é usado para cada usuário ouprojeto. Para criar um novo banco de dados, que neste exemplo,estamos chamando de meubanco utiliza-se o seguinte comando:
$ createdbcreatedbcreatedbcreatedb meubancomeubancomeubancomeubancoQue irá produzir a seguinte resposta:
CREATE DATABASE� Um nome de banco de dados deverá ter um primeiro caractere
alfabético e são limitados a 63 caracteres no máximo. Pode-setambém criar um banco de dados com o mesmo nome no usuáriocorrente. Para isso basta digitar:
$ createdbcreatedbcreatedbcreatedb� Para remover um banco de dados, se você for o dono do mesmo,
basta digitar:$ dropdbdropdbdropdbdropdb meubancomeubancomeubancomeubanco
Acessando um Banco de Dados
� Um Banco de Dados pode ser acessado pordiversas maneiras:1. Terminal interativo do PostgreSQL (psql);2.Ferramentas gráficas para a criação,
administração e manipulação de bancos(pgAdmin);
3.Aplicações desenvolvidas pelos usuáriosutilizando conexões via ODBC ou JDBC.
Acessando o Banco de Dados
� O psql permite ao usuário entrar, editar e executarinterativamente comandos SQL
$ psql meubanco
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms\h for help with SQL commands\? for help on internal slash commands\g or terminate with semicolon to execute query\q to quit
treino=#
Acessando o Banco de Dados
� Verificando a versão do PostgreSQL:$ SELECT version();
� Help:\h� Sair do psql:\q
Comandos Básicos psql
� Pode-se criar um arquivo texto com todas asoperações de criação de tabelas e de consultas,e posteriormente executa-lo da seguinte forma:\i caminho/nomedoarquivosql
� Para ver a descrição da Tabela\d nomedatabela
� Para ver todas as tabelas criadas\d
PostgreSQL
Linguagem SQL
Introdução
� A Structured Query Language (SQL) permite aosusuários acessar, definir e manipular dados em bancos dedados relacionais, como PostgreSQL, Oracle, Sybase,Informix, Microsoft SQL Server, MySql, entre outros.
� Nos RDBMS os dados são armazenados em tabelas.� Uma tabela pode ser definida como uma coleção de linhas,
onde cada linha em uma tabela é formada pelo mesmoconjunto de colunas.
� Cada coluna deve possuir um tipo de dado especifico.� Exemplos de tipos de dados no PostgreSQL:
� int, smallint, double precision, char(n), varchar(n), real,date, time, timestamp e interval.
Comandos Básicos
� DDL - Data Definition Language (Linguagem de Definição deDados):� CREATE TABLE� DROP TABLE
� DML – Data Manipulation Language (Linguagem de Manipulaçãode Dados):� SELECT� INSERT� UPDATE� DELETE
PostgreSQL
Definição de Dados
Linguagem de Definição de Dados
� Criação das estruturas do banco de dados.� Criação, modificação e eliminação de tabelas.� Controle dos dados armazenados nas tabelas.� Privilégios associados a tabelas.
Definição de Tabelas
� Para se criar uma nova tabela especifica-se o nome damesma e os nomes de seus atributos colunas com os seusrespectivos tipos.� O comando não acaba enquanto o psql não encontrar
o ponto-e-vírgula.� Espaços em branco podem ser usados livremente em
comandos SQL.� Como exemplos de tipos permitidos, o tipo varchar(80)
especifica um tipo que pode armazenar strings de até 80caracteres no seu tamanho, o tipo int é o tipo inteironormal, o tipo real armazena números de ponto flutuante desimples precisão, o tipo date armazena datas.
Criando uma Nova TabelaCREATE TABLE
CREATE TABLE pessoa (nome VARCHAR(20),cpf CHAR(11)endereco VARCHAR(30),cidade VARCHAR(15),estado CHAR(2),nascimento DATE
);
Use o comando \d pessoa para verificar a estrutura da tabela.
Table "public.pessoa"Column | Type | Modifiers
------------+-----------------------+-----------nome | character varying(20) |cpf | character(11) |endereco | character varying(30) |cidade | character varying(15) |estado | character(2) |nascimento | date |
Eliminando uma Tabela
� Se não se desejar mais a tabela, por fim, pode-seremovê-la com o seguinte comando:
DROP TABLE tablename;
� O comando DROP TABLE elimina permanentemente umatabela do banco de dados. Exemplo:
DROP TABLE pessoa;
Valores Default
� Um coluna pode receber um valor padrão toda vez queuma nova linha for inserida no sistema, e nenhum valorfor especificado para ela.
CREATE TABLE pessoa (idpessoa integer,nome text,...estado char(2) DEFAULT ‘NI’);
Restrições
� Check Constraints;� Not-Null Constraints;� Unique Constraints;� Primary Keys;� Foreign Keys.
Check Constraints
� Ao inserir ou atualizar uma coluna, o novovalor só será aceito se satisfazer aexpressão de restrição.
CREATE TABLE pessoa (idpessoa integer,nome text,...sexo char(1)CHECK sexo IN (‘M’, ‘F’)
);
Check Constraints
CREATE TABLE pessoa (idpessoa integer,nome text,...sexo char(1)CONSTRAINT ck_sexo CHECK (sexo IN (‘M’, ‘F’))
);
Check Constraints
� Outro Exemplo de Restrição.
CREATE TABLE produtos (idproduto integer,preco numeric,desconto numeric,CONSTRAINT ck_preco CHECK (preco > 0)CONSTRAINT ck_desconto CHECK (desconto > 0 AND
preco > desconto));
Restrição Not Null
� Não permite que a coluna assuma um valor nulo.
CREATE TABLE pessoa (idpessoa integer NOT NULL,nome text NOT NULL,...sexo char(1),CHECK sexo IN (‘M’, ‘F’)
);
Restrição UNIQUE
� Garante que o valor de uma coluna ou grupo de colunas éúnico para todas as linhas da tabela.
CREATE TABLE pessoa (idpessoa integer UNIQUE,nome text);
Restrição UNIQUE: Outros Formatos
CREATE TABLE pessoa (idpessoa integer,nome text,UNIQUE (idpessoa));
CREATE TABLE pessoa (a integer,b integer,c integerUNIQUE (a,b));
PRIMARY KEY (Chave Primária)
� Normalmente usada como identificador únicopara linhas na tabela.
� Tecnicamente pode ser considerada como umajunção das restrições Not-Null e Unique.
� Uma tabela só pode ter uma única chaveprimária, que pode ser simples ou composta.
CREATE TABLE pessoa (idpessoa integer PRIMARY KEY,nome text);
FOREIGN KEY (Chave Estrangeira)
� Especifica que os valores em uma coluna ou grupo decolunas dependem da existência em alguma linha deoutra tabela.
� Mantém a integridade referencial entre as duastabelas.
CREATE TABLE aluno (matricula numeric PRIMARY KEYidpessoa integer REFERENCES pessoa(idpessoa),ira real);
FOREIGN KEY (Chave Estrangeira)
� ON DELETE CASCADE� ON DELETE SET NULL� ON DELETE SET DEFAULT
CREATE TABLE aluno (matricula numeric PRIMARY KEY,idpessoa integerREFERENCES pessoa(idpessoa) ON DELETE CASCADE,
ira real);
Modificando Tabelas
� Adicionar Colunas.� Remover Colunas.� Adicionar Restrições.� Remover Restrições.� Mudar Valor Default.� Renomear Colunas.� Renomear Tabelas.
Adicionando Uma Nova Coluna
� Adiciona uma nova coluna em uma tabela existente.ALTER TABLE pessoa ADD COLUMN email text;
� A definição da restrição pode ser feita no momento dacriação da coluna, com exceção de Not-Null.
ALTER TABLE pessoaADD COLUMN sexo char(1)CHECK (sexo IN (‘M’,’F’));
Removendo Uma Coluna
� Também pode-se remover uma coluna de uma tabela. Ocomando abaixo especificado remove uma coluna databela pessoa.
ALTER TABLE pessoa DROP COLUMN email;
Adicionando Uma Nova Restrição
� Pode-se também adicionar uma nova restrição em umatabela existente. Os exemplos abaixo alteram asdefinições das tabelas pessoa e aluno.
ALTER TABLE pessoa ADD CHECK (nome <> ‘ ’);
ALTER TABLE aluno ADD FOREIGN KEY (idpessoa)REFERENCES pessoa;
Removendo Uma Restrição
� É possível remove uma restrição existente.� O nome da restrição deve ser informado no
comando. Se ela tiver sido gerada pelo sistemapode ser consultada através do comando \dnome_da_tabela.
ALTER TABLE pessoa DROP CONSTRAINT restricao;
ALTER TABLE pessoaALTER COLUMN pessoa DROP NOT NULL;
Alterando o Valor Default
� Alterando o valor Default de uma coluna:ALTER TABLE pessoa ALTER COLUMN estado SET
DEFAULT ‘RN’;
� Removendo o valor Default de uma coluna:ALTER TABLE pessoa ALTER COLUMN estado DROP
DEFAULT;
Renomeando Coluna e Tabela
� Para renomear uma coluna:
ALTER TABLE pessoaRENAME COLUMN telefne TO telefone;
� Para renomear uma tabela:
ALTER TABLE pssoa RENAME TO pessoa;
PostgreSQL
Manipulação de Dados
Inserindo Dados
INSERT INTO pessoaVALUES ('JOSE MARIA DA SILVA', '12345678901', 'RUA DAS FLORES 22','NATAL', 'RN', TO_DATE('23/03/1961','DD/MM/YYYY'));
� Para inserir dados em uma tabela, utiliza-se o comandoINSERT , da seguinte forma:
INSERT INTO pessoa (nome, cpf, endereco, cidade, estado, nascimento)VALUES ('MARIA DAS DORES', '01234567890', 'RUA SOL 85',
'NATAL', 'RN', TO_DATE('15/11/1956','DD/MM/YYYY'));
� Aconselha-se listar as colunas explicitamente para facilitara compreensão:
Inserindo Dados
INSERT INTO pessoa (cpf, nome, nascimento, endereco, cidade, estado)
VALUES ('9876543210', 'RITA DE CASSIA', TO_DATE('15/11/1956','DD/MM/YYYY'),'RUA SOL 85', 'NATAL', 'RN');
� Também pode-se listar as colunas em diferente ordem:
Inserindo Dados
INSERT INTO marias (nome,nascimento)SELECT nome, nascimentoFROM pessoaWHERE nome like ‘%MARIA%’;
� Pode-se copiar os dados de uma tabela diretamentepara outra:
Inserindo dados na Tabela
� Pode-se utilizar o comando COPY para copiaruma grande quantidade de dados em arquivostexto previamente formatados. Istonormalmente é bem rápido porque o comandoCOPY é bem mais otimizado do que o comandotradicional INSERT.
COPY pessoa FROM ´home/user/pessoa.txt´;
Removendo Linhas
� A instrução DELETE é utilizada para removeruma ou mais linhas de uma tabela.
� A forma básica da instrução é:DELETE FROM tabela;
� A instrução acima removerá todas as linhas databela, portanto, deve ser usado com muitacautela.
Removendo Linhas
� A clausula WHERE pode ser usada pararestringir as linhas que serão eliminadas. Comopor exemplo:
DELETE FROM automovel
WHERE modelo = 'GOL';
Modificando Dados
� O comando UPDATE permite a modificação dedados existentes no Banco de Dados.
� Pode-se atualizar uma ou mais colunas de umaou mais linhas de uma tabela.
� A estrutura do comando é composta de trêspartes:
1. O nome da tabela e coluna(s) a serematualizadas;
2. O novo valor da(s) coluna(s) atualizadas;3. Qual(is) linha(s) atualizar.
Modificando Dados
� Forma básica:UPDATE tabela SET coluna = novo_valor;
� A instrução acima atualizará o valor da coluna emtodas as linhas da tabela, portanto deve serusado com cautela.
Modificando Dados
� Também pode-se usar a clausula WHERE pararestringir as linhas que serão atualizadas.
UPDATE automovelSET valor = 10000WHERE modelo = 'UNO' AND ano = 2001;
Modificando Dados
� Pode-se atualizar mais de uma coluna ao mesmotempo:
UPDATE automovelSET valor = valor*1.1, proprietario = 'JORGE FILHO'
WHERE modelo = 'UNO' AND ano = 2001;
Modificando Dados
� Também pode-se usar subconsultas no comandoupdate.
UPDATE automovelSET valor = valor*1.1
WHERE proprietario IN (SELECT nomeFROM pessoa
WHERE cidade = ‘NATAL’);
PostgreSQL
SELECT
Consultando uma Tabela
� A instrução SELECT é utilizada para recuperarinformações de uma ou mais tabelas.
� A estrutura básica do SELECT é composta detrês partes principais:1. Lista das colunas a serem retornadas;2.Lista das tabelas a serem consultadas;3.Restrições (opcional).
O Comando SELECT
� A instrução SELECT tem a seguinte estruturabásica:SELECT select_list FROM tabela(s);
� Também pode compor a instrução as seguintescláusulas opcionais:� WHERE� GROUP BY� HAVING� ORDER BY
Consultando uma Tabela
SELECT * FROM pessoa;* indica que todas as colunas da tabela serão apresentadas.
nome | cpf | endereco | cidade | estado | nascimento
---------------------+-------------+-------------------+--------+--------+------------
JOSE MARIA DA SILVA | 12345678901 | RUA DAS FLORES 22 | NATAL | RN | 1961-03-23
MARIA DAS DORES | 01234567890 | RUA SOL 85 | NATAL | RN | 1956-11-15
RITA DE CASSIA | 9876543210 | RUA SOL 85 | NATAL | RN | 1956-11-15
(3 rows)
Consultando uma Tabela
� Pode-se especificar na consulta apenas os camposdesejados:
SELECT nome, cidade, estadoFROM pessoa;
nome | cidade | estado---------------------+--------+--------JOSE MARIA DA SILVA | NATAL | RNMARIA DAS DORES | NATAL | RNRITA DE CASSIA | NATAL | RN(3 rows)
A Cláusula SELECT
A cláusula SELECT pode:● Listar todas as colunas de uma tabela:
SELECT * FROM pessoa;● Listar colunas especificas de uma tabela:
SELECT nome, cidade FROM pessoa;● Executar operações entre colunas listadas:
SELECT matricula, (nota1 + nota2 + nota3)/3 FROM notas;
SELECT nome || sobrenome FROM pessoa;
A Cláusula SELECT
● Executar operações aritméticas como umacalculadora:
SELECT 3*4;● Chamar funções nativas ou programadas pelo
usuário:SELECT random();
● Aplicar funções sobre as colunas listadas:SELECT substr(nome,1,4) FROM pessoa;SELECT length(nome) FROM pessoa;
A Cláusula SELECT
● O nome, de uma coluna, mostrado na saídapode ser alterado através do uso de aliases:
SELECT matricula, (nota1 + nota2 + nota3)/3 AS mediaFROM notas;
matricula | media-----------+------------------200123810 | 6.79999987284342200278231 | 3.70000012715658200144609 | 6.73333358764648(3 rows)
A Cláusula SELECT
● Pode-se eliminar resultados duplicados, nasaída, usando a palavra reservada DISTINCT:
SELECT DISTINCT modelo FROM automovel;treino=# SELECT modelo
FROM automovel;
modelo
--------
UNO
PALIO
CORSA
GOL
UNO
PALIO
CORSA
GOL
(8 rows)
treino=# SELECT DISTINCT modelo
modelo
FROM automovel;
modelo
--------
CORSA
GOL
PALIO
UNO
(4 rows)
A Cláusula FROM
A cláusula FROM indica a origem dos dados de umaconsulta, que pode ser:
● Uma tabela:SELECT * FROM pessoa;
● Duas ou mais tabelas (Joined Tables);● Subqueries;● Views.
A Cláusula WHERE
● Após ser processada pela cláusula FROM, cadalinha da tabela virtual derivada é checada nacondição de busca.
● Se o resultado da condição for verdadeiro, alinha é colocada na tabela de saída.
● Caso contrário, a linha é descartada doresultado.
A Cláusula WHERE
� A clausula WHERE restringe as linhas retornadasem uma consulta.
SELECT modelo, ano, cor, valorFROM automovelWHERE modelo = 'GOL';
modelo | ano | cor | valor--------+------+--------+-------GOL | 1998 | AZUL | 9000GOL | 2002 | BRANCO | 14000(2 rows)
A Cláusula WHERE
� Os operadores AND, OR e NOT são permitidosna qualificação de uma Consulta.SELECT modelo, ano, cor, valor
FROM automovelWHERE modelo = 'GOL' AND ano < 2000;
modelo | ano | cor | valor--------+------+------+-------GOL | 1998 | AZUL | 9000(1 row)
A Cláusula WHERE
SELECT modelo, ano, cor, valorFROM automovel
WHERE modelo = 'GOL' OR modelo = 'PALIO';
modelo | ano | cor | valor--------+------+--------+--------GOL | 1998 | AZUL | 9000PALIO | 2000 | BRANCO | 10500GOL | 2002 | BRANCO | 14000(3 rows)
A Cláusula WHERE
� Outros Exemplos de WHERE:
A Cláusula GROUP BY
● A cláusula GROUP BY é usada para agrupar todasas linhas que compartilham os mesmos valores emtodas as colunas listadas.
● A ordem das colunas não é importante.● O objetivo é reduzir cada grupo de linhas que
compartilham os mesmos valores em comum numúnico grupo que representa todas as linha nogrupo.
A Cláusula GROUP BYEXEMPLO:
SELECT modeloFROM automovel;
SELECT modeloFROM automovel
GROUP BY modelo;modelo--------UNOPALIOCORSAGOLUNOPALIOCORSAGOL(8 rows)
modelo--------CORSAGOLPALIOUNO(4 rows)
A Cláusula GROUP BY
EXEMPLO 2:SELECT modelo, SUM(valor)FROM automovelGROUP BY modelo;
modelo | sum--------+-------UNO | 22000CORSA | 12000GOL | 23000PALIO | 21000(4 rows)
A Cláusula HAVING
● Em muitas consultas nem todos os gruposretornados pela cláusula GROUP BY sãoimportantes para o usuário.
● A cláusula HAVING serve para eliminar gruposde uma tabela agrupada.
● Expressões na cláusula HAVING podem sereferir a expressões agrupadas ou nãoagrupadas.
A Cláusula HAVINGEXEMPLOS:
Subqueries
● SUBQUERIES:● Uma consulta dentro de outra consulta.● O resultado da subquery retorna uma tabela
virtual derivada que será usada pela consultaprincipal.
● Deve ser escrita entre parênteses.● Deve ser identificada por um alias.
Subqueries
● SUBQUERIES - Exemplo:● Retorne todos os modelos de carro que
possuem mais de duas unidades no estoque.SELECT a.modelo, quantidadeFROM automovel a INNER JOIN (SELECT modelo AS m,
count(*) AS quantidadeFROM automovelGROUP BY modelo) AS contagem
ON a.modelo = mWHERE quantidade > 2;
Joined Tables
� Joined Tables● Uma joined table é uma tabela derivada de
duas outras (real ou derivada) tabelas.● Os tipos de junção disponíveis no PostgreSQL
são:� Inner Join;� Outer Join;� Cross Join.
Tabelas Exemplo
As tabelas abaixo serão usadas nos próximosexemplos.
T1 T2
Joined Tables : CROSS JOIN
� T1 CROSS JOIN T2;� Para cada combinação de linhas de T1 e T2, a
tabela derivada irá conter uma linha consistindode todas as colunas de T1 seguidas por todas ascolunas de T2.
� Se as tabelas possuem N e M linhasrespectivamente, a tabela resultante terá N*Mlinhas.
� O CROSS JOIN é normalmente executado pordefault, com a seguinte consulta simples:
SELECT * FROM T1,T2;
Joined Tables
Joined Tables● INNER JOIN:
● Para cada linha R1 de T1, a tabela resultantetem uma linha para cada linha em T2 quesatisfaz a condição de junção com R1.
Joined Tables● LEFT OUTER JOIN:
● Primeiro, um inner join é executado. Então,para cada linha em T1 que não satisfaz acondição com nenhuma linha em T2, a linha éadicionada com valores nulos nas colunas deT2.
Joined Tables● RIGHT OUTER JOIN:
● Primeiro, um inner join é executado. Então,para cada linha em T2 que não satisfaz acondição com nenhuma linha em T1, a linha éadicionada com valores nulos nas colunas deT1.
Joined Tables● FULL OUTER JOIN:
● É a união do RIGHT OUTER JOIN e LEFTOUTER JOIN.
Operações em Conjuntos
Tabelas Exemplo
� Tabela Clima � Tabela Cidades
cidade | data | precp--------+------------+--------Natal | 18-03-2004 | 3.2 Caico | 18-03-2004 | 4.7Touros | 19-03-2004 | 18.5Patu | 20-03-2004 | 7.9
cidade | ano | temp_media--------+------------+--------Natal | 2004 | 30 Macau | 2003 | 33 Caico | 2004 | 32Touros | 2004 | 29Apodi | 2003 | 31Buzios | 2004 | 25
Operações em Conjunto
� Assim como na álgebra relacional, as relaçõesparticipantes das operações de união, interseção ediferença em SQL precisam ser compatíveis entre si, ouseja, elas precisam ter o mesmo conjunto de atributos.� Vamos dar exemplos dessas operações, usando dois
conjuntos básicos: (a) o conjunto de todos os nomesdas cidades que possuem valores de precipitação, e(b) o conjunto de todos os nomes das cidades quepossuem valores de temperatura.
� Onde o conjunto (a) pode E o conjunto (b) podeser obtido com: ser obtido com:
SELECT cidade SELECT cidadeFROM clima; FROM cidades;
Operação União
� Para se obter todos os nomes de cidades quepossuem valores de precipitação ou detemperatura, ou ambos, escreve-se:
Resultado obtido:(SELECT cidadeFROM clima)UNION(SELECT cidadeFROM cidades);
cidade--------NatalCaicoTourosPatuMacauApodiBuzios
Combinando Consultas
� Consulta1 UNION Consulta2:� Retorna a união do resultado da consulta1 com
o resultado da consulta2, onde as linhaduplicadas são eliminadas.
� Consulta1 UNION ALL Consulta2:� Semelhante ao UNION sendo que as linhas
duplicadas não são eliminadas
Operação Interseção
� Para encontrar todas as cidades que contenhamtanto valores de precipitação como valores detemperatura, escreve-se:
(SELECT cidade
FROM clima)
INTERSECT
(SELECT cidade
FROM cidades);
Resultado obtido:cidade--------NatalCaicoTouros
Combinando Consultas
� Consulta1 INTERSECT Consulta2:� Retorna a interseção do resultado da
consulta1 com o resultado da consulta2, ondeas linha duplicadas são eliminadas.
� Consulta1 INTERSECT ALL Consulta2:� Semelhante ao INTERSECT sendo que as
linhas duplicadas não são eliminadas.
Operador EXCEPT
� Para encontrar todas as cidades que contenhamvalores de precipitação e não contenham valoresde temperatura, escreve-se:
(SELECT cidade
FROM clima)
EXCEPT
(SELECT cidade
FROM cidades);
cidade--------Patu
Resultado obtido:
Combinando Consultas
� Consulta1 EXCEPT Consulta2:� Retorna todas as linhas que estão no resultado
da consulta1 mas não estão no resultado daconsulta2, as linha duplicadas são eliminadas.
� Consulta1 EXCEPT ALL Consulta2:� Semelhante ao EXCEPT sendo que as linhas
duplicadas não são eliminadas.
PostgreSQL
Funções e Operadores
Operadores Lógicos
� Os operadores lógicos disponíveis no PostgreSQL são:AND, OR e NOT.
Operadores de Comparação
Operadores de Comparação
� BETWEEN:a BETWEEN x AND yEquivalente a: a >= x AND a <= y
� NOT BETWEENa NOT BETWEEN x AND yEquivalente a: a < x OR a > y
Operadores de Comparação
� IS NULL: Retorna TRUE se o valor da coluna fornulo.SELECT nome
FROM pessoaWHERE cpf IS NULL;
� IS NOT NULL: Retorna TRUE se o valor dacoluna não for nulo.SELECT COUNT(*)
FROM pessoaWHERE cpf IS NOT NULL;
Operadores e FunçõesMatemáticas
Operadores e FunçõesMatemáticas
Operadores e FunçõesMatemáticas
Operadores e Funções de String
Funções Para Formatação de Datas
Padrões de Template Para Formataçãode Data e Hora
Funções de Agregação
PostgreSQL
Índices
Índices
� Usados para melhorar a performance deconsultas ao banco de dados.
� Úteis em consultas que retornam linhasespecificas e utilizam na cláusula WHERE acoluna ou colunas nas quais o índice foiconstruído.
� Ao se criar um índice, o servidor constrói umaárvore de busca para a(s) coluna(s) indexada(s).
� Inúteis no caso de FULL TABLE SCANS oucolunas não indexadas na cláusula WHERE.
Índices
EXEMPLO:SELECT nome FROM pessoa WHERE idpessoa = 3145;
� Se a coluna idpessoa não for indexada o servidor faráuma busca em toda a tabela, linha por linha, retornandoa(s) linha(s) com idpessoa 3145.
� Caso a coluna seja indexada, o servidor fará a buscadiretamente na árvore, tornando a consulta bem maiseficiente.
Índices
� Exemplo de criação de índice:CREATE INDEX idpessoa_index ON pessoa(idpessoa);
� Para remover o índice:DROP INDEX idpessoa_index;
Índices
Por que não criar índices em todas as colunas?1. Espaço: A criação de um índice implica na
criação de uma árvore de busca, aumentandoassim a necessidade de espaço físico paraarmazenar o banco de dados;
2. Overhead nas operações de INSERT,UPDATE e DELETE: Toda vez que um valor nacoluna indexada é atualizada ou uma linha éinserida ou apagada a árvore precisa ser“rebalanceada”, causando um consideráveloverhead no desempenho do sistema.
Índices Multicoluna
� Até 32 colunas podem ser especificadas no índice.CREATE INDEX tb_xy_idx ON tb(x,y);
� Útil em consultas que utilizam o operador AND nascolunas do índice:SELECT x, y FROM tb WHERE x = 3 AND y = 8;
� Inútil no caso do operador OR:SELECT x, y FROM tb WHERE x = 3 OR y = 8;
� Também é usado no caso:SELECT x, y FROM tb WHERE x = 3;
� Inútil no caso:SELECT x, y FROM tb WHERE y = 8;
Unique Indexes
� Não permite valores duplicados em uma coluna:CREATE UNIQUE INDEX idpessoa_index ON pessoa(idpessoa);
� No caso de um índice multicoluna, serão rejeitados oscasos onde todas as colunas indexadas são iguais emmais de uma linha.
� Toda vez que uma “Unique Constraint” ou uma chaveprimária é criada, um unique index é criadoautomaticamente.
Índice de Expressão
� Um índice também pode ser baseado em uma função ouexpressão escalar.
CREATE INDEX test1_lower_col1_idx ON test1(lower(col1));
� Útil para consultas como:
SELECT * FROM test1 WHERE lower(col1) = 'value';
Seqüências
� Objeto usado para gerar números inteirosexclusivos.
� Pode ser compartilhada por vários usuários.� Normalmente usadas para gerar
automaticamente valores de chave primária.� Os números são gerados por uma rotina interna
do PostgreSQL.� Uma mesma seqüência pode ser usado por várias
tabelas.
Seqüências
� Uma seqüência é criada através do comandoCREATE SEQUENCE:
Seqüências
Parâmetros:1. Temporary: O objeto seqüência é criado
somente para a sessão e apagado no fim dasessão.
2. Increment by i: Especifica o intervalo entrenúmeros de seqüência onde i é um númerointeiro, o valor default é 1, caso o valor de iseja negativo a seqüência serádecrementada.
Seqüências
Parâmetros:3. Minvalue: Especifica o menor valor possível
na seqüência.4. No Minvalue: Usa o valor default (1 ou-2^63
-1).5. Maxvalue: Especifica o valor máximo que a
seqüência pode gerar.6. No Maxvalue: Usa o valor default (2^63 -1
ou -1).
Seqüências
Parâmetros:7. Start With: Especifica o primeiro número a
ser gerado pela seqüência.8. Cache: Especifica quantos valores serão
gerados previamente e alocados na memória.9. Cycle: Especifica o valor máximo que a
seqüência pode gerar (Default 1).
Seqüências
Parâmetros:10. Cycle: Permite a seqüência continuar a gerar
valores depois de atingir o maxvalue(ascendente) ou minvalue (descendente).Caso o limite seja atingido, o próximonúmero a ser gerado será o minvalue oumaxvalue. Deve ser usado com cuidado paragerar valores de chave primária. O valordefault é No Cycle.
Seqüências
Funções de Manipulação:� nextval: Avança e seqüência e retorna um novo
número.� nextval(‘sequencia’)
� currval: Retorna o valor atual da seqüência.� currval(‘sequencia’)
� setval: Seta um novo valor atual para aseqüência.� setval(‘sequencia’,valor)
Seqüências
� Para listar todas as seqüências do banco:� \ds
� Para ver todos os parâmetros da sequencia:� SELECT * FROM nome_da_sequencia;
Seqüências
Exemplo:
INSERT INTO pessoa (idpessoa, nome)VALUES (nextval(‘seq_pessoa’, ‘ROSANGELA MARIA’);
INSERT INTO aluno (idpessoa, matricula)VALUES (currval(‘seq_pessoa’, 200425484);
SELECT currval(‘seq_pessoa’);
Seqüência
� Para remover uma sequencia:DROP SEQUENCE sequence_name;
� Para alterar uma seqüência: ALTER SEQUENCE.
PostgreSQL
Funções e Triggers
Funções
� Também conhecidas como Stored Procedures, sãofunções escritas em linguagem procedural, que sãoarmazenadas e executadas dentro do próprio banco.
� Se muitas aplicações utilizam uma mesma função, évantagem armazená-la dentro do servidor de banco,evitando assim cópias redundantes da mesma nasdiferentes aplicações.
Funções
� A transferência dos códigos SQL, como SELECT, INSERT,UPDATE e DELETE, ou de funções que necessitam de acessosa banco, para dentro do servidor pode resultar em um grandeganho de performance. Dentre as vantagens podemos citar:1. Normalmente o DBA possui um melhor conhecimento
da linguagem SQL que os programadores.2.Evita redundância de código, evitando que a mesma
instrução seja escrita várias vezes em diferentespontos da aplicação.
Funções
3.Ao invés de se passar o código SQL inteiro para oservidor, a aplicação passa apenas os parâmetros daconsulta e espera o resultado, diminuído assim acomunicação interprocessos e o tráfego de rede.
Em um sistema com centenas ou milhares de usuáriospor exemplo, esse ganho pode ser imenso.
Funções
4. Toda vez que o servidor “recebe” uma instruçãoSQL diferente da aplicação, ele necessita fazer oparse e gerar o plano de execução antes deexecutar a busca na base, que são etapas queconsomem um tempo considerável.
No caso de uma stored procedure, o parser e o planode execução são gerados somente na primeira vezque a função é chamada, ficando armazenados paraas chamadas subseqüentes, gerando assim umaconsiderável economia de tempo.
Funções
� A atual versão do PostgreSQL (7.4.1) suporta as seguinteslinguagens procedurais: PL/pgSQL, PL/Tcl, PL/Perl, PL/TclU,PL/PerlU, e PL/PythonU.
� A linguagem PL/psSQL possui um padrão bastante semelhanteao PL/SQL do Oracle e do SQL/Server, sendo portanto a maiscomum de ser utilizada.
Funções PL/pgSQL
� As linguagens procedurais não vem instaladas por Default nosbancos de dados.
� Para instalar a linguagem PL/pgSQL o usuário deve executar oseguinte comando:$ createlang plpgsql meu_banco
� Caso se deseje que todas os bancos que venham a ser criadossuportem o PL/pgSQL:$ createlang plpgsql template1
Funções PL/pgSQL
Sintaxe básica para a criação de funções PL/pgSQL.
CREATE [OR REPLACE] FUNCTION nome(parametro1, parametro2…)RETURNS tipo_de_dado_retornado AS '
DECLAREvariaveis;
BEGINcorpo da procedure
END;' LANGUAGE plpgsql;
Funções PL/pgSQL
� Para se escrever uma função o usuário pode usar qualquereditor de texto, ou alguma ferramenta gráfica como o PgAdminou o PgAccess.
CREATE OR REPLACE FUNCTION helloworld()RETURNS text AS '
DECLAREa text;
BEGINa := ''Hello World'';RETURN a;
END;' LANGUAGE plpgsql;
Funções PL/pgSQL
treino=# \i ‘helloworld.sql’CREATE FUNCTIONtreino=# select helloworld();helloworld
-------------Hello World
(1 row)
Funções PL/pgSQL - Estrutura
Declaração:� Todas as variáveis que serão usadas no bloco, devem ser
declaradas no sessão DECLARATION.� A única exceção são as variáveis definidas implicitamente num
loop FOR.� As variáveis podem assumir os mesmos tipos suportados na
linguagem SQL do PostgreSQL, como; integer, varchar,numeric, float e date.
Funções PL/pgSQL - Estrutura
Declaração:� Quando se quer utilizar o mesmo tipo de uma coluna de uma
tabela, aconselha-se usar o %TYPE. Pois garante a consistênciacaso o tipo da coluna venha a mudar.
� Ao invés dev_nome varchar(30);
� É melhor usarv_nome pessoa.nome%TYPE;
Funções PL/pgSQL - Estrutura
Declaração:� Uma variável composta pode ser declarada com todas as
colunas de uma tabela %ROWTYPE (semelhante a um registro).
DECLARE
r_pessoa pessoa%ROWTYPE;BEGIN
r_pessoa.nome := 'CREMILDA';
Funções PL/pgSQL - Estrutura
Declaração:� Os parâmetros de entrada da função são acessados no corpo
como $1 (parametro1), $2 (parametro2). Pode-se usarALIASES para se tornar mais amigável.
CREATE FUNCTION teste(real) RETURNS real AS ‘DECLARE
v_valor ALIAS FOR $1;BEGIN
RETURN 10*v_valor;END; ‘ LANGUAGE plpgsql;
Funções PL/pgSQL - Estrutura
Instruções básicas: Atribuição� Um valor pode ser atribuído a uma variável ou um registro.� Exemplos:
v_idade := 23;v_taxa := v_preco * 0.1
Funções PL/pgSQL - Estrutura
Instruções básicas: SELECT INTO� Permite que o resultado de uma consulta select seja atribuído
a uma variável, lista de variáveis ou um registro.� A consulta só pode retornar uma única linha.
SELECT nome, cpfINTO v_nome, v_cpf
FROM pessoaWHERE idpessoa = 12548;
Funções PL/pgSQL - Estrutura
� Estruturas de Controle: RETURN� Sintaxe:
RETURN <expressao>;� Retorna o valor da expressão e finaliza a função.
Funções PL/pgSQL - Estrutura
Estruturas de Controle: Condicionais IF� A instrução IF permite a execução de comandos se uma
determinada condição for satisfeita.� O postgreSQL tem 4 formas de IF.
� IF ... THEN� IF ... THEN ... ELSE� IF ... THEN ... ELSE IF� IF ... THEN ... ELSIF ... THEN ... ELSE
Funções PL/pgSQL - Estrutura
Loops Simples: LOOP
Funções PL/pgSQL - Estrutura
Loops Simples: WHILE� Repete a execução das instruções enquanto a condição
for verdadeira.
Funções PL/pgSQL - Estrutura
Loops Simples: FOR� Repete a execução sobre uma escala de valores
inteiros.
Funções PL/pgSQL - Estrutura
Loop sobre o resultado de uma consulta: FOR� Existe uma variação do FOR que permite interagir com os
resultados retornados de uma consulta.
FOR c_aluno IN SELECT matricula, iraFROM aluno WHERE status = 'A' LOOP
IF c_aluno.ira >= 9 THENINSERT INTO elite (matricula, ira)
VALUES (c_aluno.matricula, c_aluno.ira);END IF;END LOOP;
Funções PL/pgSQL - Estrutura
Erros e Mensagens� A instrução RAISE reporta mensagens e erros.� Exemplos:
RAISE EXCEPTION '‘Preço nao pode ser negativo %'', NEW.minprice;�
RAISE NOTICE ''O valor de i neste ponto é %'', i;
Triggers
� Triggers são utilizadas para definir regras de restriçõescomplexas, ou executar alguma ação ou função depois de algumevento especifico.
� Uma trigger pode ser disparada antes ou depois de umaoperação INSERT, UPDATE ou DELETE, tanto a cada linhamodificada como após uma instrução SQL inteira.
Triggers
� Uma trigger é criada através do comando CREATE FUNCTIONcomo uma função sem argumentos e retorno do tipo trigger.
� Exemplo de utilização de Triggers:� Chamar uma função que verifique se o número do
CPF é válido toda vez que uma linha é inserida natabela pessoa ou o valor do campo CPF é atualizado.
Triggers
� O exemplo a seguir ilustra a criação de uma triggerprocedure que não permite preços negativos natabela de preços.
CREATE OR REPLACE FUNCTION public.preco_min()RETURNS trigger AS
'BEGINIF NEW.minprice < 0 THEN
RAISE EXCEPTION ‘‘Preço nao pode ser negativo %’', NEW.minprice;END IF;RETURN NEW;
END;'LANGUAGE 'plpgsql' VOLATILE;
�
Triggers
� Na tabela, deve-se criar o gatilho que disparará a triggerprocedure nas atualizações e inserções na tabela price.
CREATE TRIGGER price_minimoBEFORE INSERT OR UPDATEON public.priceFOR EACH ROWEXECUTE PROCEDURE public.preco_min();�
Usuários e Privilégios de Banco deDados
Usuários
� Todo cluster de banco de dados possui um conjunto deum ou mais usuários.
� Os usuários do banco não possuem nenhumacorrespondência com os do S.O. onde o servidor roda.
� Um nome de usuário é global para todo o cluster debanco, e não para cada banco individual.
Usuários
� Usuários podem possuir objetos (como tabelas),conceder acesso a seus objetos à outros usuários, ereceber autorização de acesso a objetos de outrosusuários.
� Durante a instalação, um usuário predefinido é criado eatravés dele pode-se conectar ao cluster para criarnovos usuários ou executar tarefas administrativas.Normalmente o nome do usuário inicial é postgres.
Usuários
� Para criar um novo usuário:CREATE USER nome;
� Para remover um usuário:DROP USER nome;
� Também podem ser usados, a partir do S.O. osprogramas createuser e dropuser:createuser nomedropuser nome
Atributos de Usuários
� Um usuário pode possuir determinados atributos quedeterminam seus privilégios e interage com o sistema deautenticação com o cliente.
� Superuser: Possui todos os privilégios do banco,somente um superusuário pode criar novos usuários.CREATE USER valeria CREATEUSER
Atributos de Usuários
� Database creation: Concede ao usuário a permissão decriar novos bancos de dados.CREATE USER valeria CREATEDB
� Password: Significativo somente se o método deautenticação requerer um password. Os métodospassword, md5 e crypt requerem passwords.CREATE USER valeria PASSWORD ‘senha’
� Todos os atributos podem ser modificados através doALTER USER.
Grupos
� Para simplificar a administração, pode-se criar gruposde usuário.
� Cada privilégio concedido ou revogado a um grupo, éconcedido ou revogado a todos os usuários do grupo.CREATE GROUP turma01;ALTER GROUP turma01 ADD USER xico, maria;ALTER GROUP turma01 DROP USER xico, maria;
Privilégios
� Quando um usuário cria um novo objeto no banco, ele setorna o proprietário do objeto.
� Por default, somente o proprietário e o superusuáriotem acesso total ao objeto.
� O proprietário pode conceder privilégios sobre seusobjetos a outros usuários.
� Exemplos de privilégios: SELECT, INSERT, UPDATE, DELETE,RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE,USAGE e ALL PRIVILEGES.
Privilégios
� Concedendo um privilégio:GRANT UPDATE ON pessoa TO maria;GRANT SELECT ON pessoa TO GROUP turma01;GRANT EXECUTE ON function_name TO PUBLIC;GRANT ALL PRIVILEGES ON pessoa TO coordenador;
� Revogando um privilégio:REVOKE ALL ON pessoa FROM maria;REVOKE INSERT ON pessoa FROM GROUP turma01;
Privilégios� WITH GRANT OPTION permite ao usuário que recebe o privilégio
concedê-lo a outros usuários:
GRANT REFERENCES
ON pessoa TO benedita WITH GRANT OPTION;
Transações em PostgreSQL
Transações
� As transações são um conceito fundamental em sistemasde bancos de dados. Uma transação envolve váriospassos em uma operação de tudo-ou-nada.
� Os passos intermediários de uma transação não sãovisíveis por outras transações, porque se alguma coisafalhar, nenhum passo será efetivamente executado.
Transações
� Considere um exemplo que se deseja fazer umatransferência bancária da conta de Alice para a contade Bob.UPDATE conta SET saldo=saldo-100.00
WHERE nome=´Alice´;UPDATE conta SET saldo=saldo+100.00WHERE nome=´Bob´;
� Desta maneira, não existe nenhuma garantia que as duas operaçõesserão efetivamente executadas ou nenhuma delas. Isto só é obtidoagrupando-se as duas operações em uma transação.
Transações
� Uma transação é atômica: ou ela acontececompletamente ou não acontece.
� Quando uma operação é completada ela é efetivamentepercebida pelo sistema de bd e suas alterações sãoverdadeiramente armazenadas no bd.
� Transações concorrentes não enxergam resultadosparciais umas das outras.� Uma transação em PostgreSQL é denotada pelos
comandos BEGIN e COMMIT.
Transações
� A transação bancária pode ser escrita como:BEGIN;
UPDATE conta SET saldo=saldo-100.00WHERE nome=´Alice´;UPDATE conta SET saldo=saldo+100.00WHERE nome=´Bob´;
COMMIT;� Pode-se desejar que a transação não seja efetivamente executada.
Neste caso, utiliza-se o comando ROLLBACK no lugar do COMMIT.
Manutenção de Rotina
Manutenção de Rotina
� No PostgreSQL existem algumas rotinas de manutençãoque devem ser executadas rotineiramente, afim demanter o servidor rodando eficientemente.
� Essas tarefas podem ser agendadas para seremexecutadas regulamente através de ferramentas do SO,como o CRON, por exemplo.
Vacuuming
� O comando VACUUM tem a função de:1. Recuperar espaço em disco ocupador por linhas
apagadas ou atualizadas;2. Atualizar as estatísticas usadas pelo PostgreSQL
query planner (gerador de planos de consultas doPostgreSQL).
� A freqüência do uso do comando VACUUM vaidepender da necessidade de cada base.
Reindexação
� Em algumas situações é necessário reconstruir osíndices periodicamente através do comando REINDEX.
� No PostgreSQL 7.4, a necessidade de reconstrução deíndices foi reduzida drasticamente em relação a versõesanteriores do PostgreSQL.
Backup e Restore
SQL Dump
� Gera um arquivo texto com comandos SQL, que quandoexecutado no servidor de bancos, recria a base dedados no mesmo estado do momento que o arquivo foigerado.pg_dump nome_do_banco > arquivo
� Por default o pg_dump se conecta ao banco usando omesmo nome de usuário do SO. Para usar outro usuáriouse o parâmetro –U.
� O usuário precisa ter acesso a todos os objetos, porisso aconselha-se usar um superusuário para executar obackup.
Recuperando
� O arquivo texto gerado pelo pg_dump pode ser lido pelopsql.
� Recuperando o backup.psql nome_do_banco < arquivo
� O banco terá que ter sido previamente criado atravésdo comando createdb (createdb –T template0nome_do_banco).
Pg_dumpall
� O comando pg_dump faz o backup de um banco por vez,para se fazer o backup de todo o database cluster,deve-se usar o programa pg_dumpall.pg_dumpall > arquivo
� Recuperando o Backup:psql template1 < arquivo
Compressão de Backups
� Para poupar espaço em disco, pode-se gerar os backupscomprimidos.pg_dump dbname | gzip > filename.gz
pg_dump dbname | split -b 1m - filename� Recuperando:
createdb dbname gunzip -c filename.gz | psql dbnamecreatedb dbname cat filename* | psql dbname
File System Level Backup
� Outra forma de backup é simplesmente copiar todos osarquivos de dados que o PostgreSQL usa paraarmazenar os dados.
� Só pode ser executado com o banco off-line, ou seja,deve-se dar um shutdown no banco antes de começar obackup.tar -cf backup.tar /usr/local/pgsql/data
Bibliografia
� PostgreSQL 7.4.1 Documentation by The PostgreSQLGlobal Development Group Copyright © 1996-2003 ThePostgreSQL Global Development Group