apostila - banco - dados - postgresql, Notas de estudo de Informática

Baixe apostila - banco - dados - postgresql e outras Notas de estudo em PDF para Informática, somente na Docsity!

Apostila sobre o

Banco de Dados Postgre

Autor: Vanessa Rocha Solgate

Ano 2005

• Introdução
• Parte A: Básico...................................................................................................
• 1. Convenções para a Linguagem SQL
• 2. Criar Banco de Dados
• 3. Criar um Schema
• 4. Tipos de Dados
  • 4.1. Tipo Numérico......................................................................................
  • 4.2. Tipos para caracteres
  • 4.3. Tipos para data e hora
  • 4.4. Tipo Booleano
• 5. Criar a estrutura de uma tabela.................................................................
  • 5.1. Regras de Nomeação
  • 5.2. Como criar uma tabela.......................................................................
  • 5.3. Campos com restrição de não-nulo
  • 5.4. Coluna com valor padrão
  • 5.5. Restrição de unicidade.......................................................................
  • 5.6. Restrição de verificação.....................................................................
  • 5.7. Chave Primária
  • 5.8. Chave estrangeira..............................................................................
• 6. Alterando a estrutura de uma tabela
  • 6.1. Adicionando novas colunas
  • 6.2. Removendo coluna da tabela
  • 6.3. Adicionando restrição.........................................................................
  • 6.4. Removendo restrição
• 7. Excluindo uma tabela
• 8. Inclusão de dados em uma tabela
• 9. Consultando dados da tabela....................................................................
  • 9.1. Alias de coluna...................................................................................
  • 9.2. Filtrando os dados..............................................................................
  • 9.3. Removendo linhas duplicadas na consulta
  • 9.4. Ordenação de linhas
  • 9.5. Operadores lógicos
  • 9.6. Operadores de comparação
  • 9.7. Funções e operadores matemáticos
  • 9.8. Funções e operadores para cadeia de caracteres
  • 9.9. Usando a condição LIKE....................................................................
  • 9.10. Funções para formatar tipos de dados
• 10. Atualizando linhas da tabela..................................................................
  • 10.1. Transações.....................................................................................
• 11. Excluindo linhas da tabela
• Parte B: Avançado
• 12. Expressão condicional...........................................................................
  • 12.1. CASE..............................................................................................
  • 12.2. COALESCE....................................................................................
  • 12.3. NULLIF
• 13. Combinação de consultas
• 14. Junção de tabelas
  • 14.1. Junção INNER JOIN (juncão interna).............................................
  • 14.2. Junção OUTER JOIN (junção externa)
  • 14.3. Junção FULL OUTER JOIN (junção externa completa)
  • 14.4. Junção SELF JOIN (Autojunção)....................................................
• 15. Agrupando os dados..............................................................................
  • 15.1. GROUP BY
  • 15.2. Funções de Agregação
  • 15.3. A cláusula HAVING
• 16. Expressões de subconsulta...................................................................
  • 16.1. EXISTS...........................................................................................
  • 16.2. IN....................................................................................................
  • 16.3. NOT IN
• 17. View (Visões).........................................................................................

Introdução

O PostgreSQL é um sistema de gerenciamento de banco de dados objeto- relacional (SGBDOR) , ele foi o pioneiro em muitos conceitos objeto-relationais que agora estão se tornando disponíveis em alguns bancos de dados comerciais.

Desenvolvido no Departamento de Ciência da Computação da Universidade da Califórnia em Berkeley. O projeto POSTGRES, liderado pelo Professor Michael Stonebraker, foi patrocinado pelas seguintes instituições: Defense Advanced Research Projects Agency (DARPA); Army Research Office (ARO); National Science Foundation (NSF); e ESL, Inc.

O PostgreSQL descende deste código original de Berkeley, possuindo o código fonte aberto. Fornece suporte às linguagens SQL92/SQL99, além de outras funcionalidades modernas.

Os Sistemas de Gerenciamento de Bancos de Dados Relacionais (SGBDR) tradicionais suportam um modelo de dados que consiste em uma coleção de relações com nome, contendo atributos de um tipo específico. Nos sistemas comerciais em uso, os tipos possíveis incluem número de ponto flutuante, inteiro, cadeia de caracteres, monetário e data.

Nesta apostila iremos aprender a como criar um banco de dados e sobre DDL (Data Definition Language) – Linguagem que os objetos que irão compor o banco de dados (comandos de criação e atualização da estrutura dos campos da tabela, por exemplo) e DML (Data Manipulation Language) - Linguagem que define os comandos de manipulação e operação dos dados (comandos de consulta e atualização dos dados das tabelas).

2. Criar Banco de Dados

Representa o arquivo físico de dados, armazenado em dispositivos periféricos, onde estão armazenados os dados de diversos sistemas, para consulta e atualização pelo usuário. No nosso treinamento coloque o seu nome como Database.

Exemplo:

CREATE DATABASE vanessa

3. Criar um Schema

Um schema é uma coleção de objetos. Os objetos do schema são as estruturas lógicas que consultam diretamente aos dados em uma base de dados. Os objetos de um schema incluem tabelas, views, sinônimos, procedures, etc.

Exemplo:

CREATE SCHEMA sui;

Se uma tabela não pertence ao usuário, o nome do proprietário deve ser prefixado à tabela.

Exemplo:

SELECT * FROM sui .tsuitipoorgaocolegiado;

4.2. Tipos para caracteres

Nome do tipo Descrição

character varying( n ), varchar( n ) comprimento variável com limite

character( n ), char( n ) comprimento fixo, completado com brancos

text comprimento variável não limitado

O SQL define dois tipos básicos para caracteres: character varying( n ) e character( n ), onde n é um número inteiro positivo. Estes dois tipos podem armazenar cadeias de caracteres com até n caracteres de comprimento. A tentativa de armazenar uma cadeia de caracteres mais longa em uma coluna de um destes tipos resulta em erro, a não ser que os caracteres excedentes sejam todos espaços. Neste caso a cadeia de caracteres será truncada em seu comprimento máximo (Esta exceção um tanto bizarra é requerida pelo padrão SQL). Se a cadeia de caracteres a ser armazenada for mais curta que o comprimento declarado, os valores do tipo character serão completados com espaço; os valores do tipo character varying simplesmente vão armazenar uma cadeia de caracteres mais curta.

As notações varchar( n ) e char( n ) são aliases para character varying( n ) e character( n ), respectivamente. O character sem especificação de comprimento é equivalente a character(1); se character varying for utilizado sem especificação de comprimento, este tipo aceita cadeias de caracteres de qualquer tamanho. Este último é uma extensão do PostgreSQL.

Além desses, o PostgreSQL suporta o tipo mais geral text, que armazena cadeias de caracteres de qualquer comprimento. Diferentemente de character varying, text não requer um limite superior explicitamente declarado de seu tamanho. Embora o tipo text não esteja no padrão SQL, muitos outros RDBMS também o incluem.

4.3. Tipos para data e hora

Tipo Descrição Armazenamento Mais cedo Mais tarde Resolução

timestamp [ ( p ) ] [ without time zone ]

tanto data quanto hora

8 bytes 4713 AC 1465001 DC

microssegundo / 14 dígitos

timestamp [ ( p ) ] with time zone

tanto data quanto hora

8 bytes 4713 BC AD 1465001

microssegundo / 14 dígitos

interval [ ( p ) ]

intervalos de tempo 12 bytes

anos

anos

microssegundo

date somente datas 4 bytes 4713 AC 32767 DC 1 dia

time [ ( p ) ] [ without time zone ]

somente a hora do dia

8 bytes 00:00:00.00 23:59:59.

microssegundo

time [ ( p ) ] with time zone

somente a hora do dia

12 bytes 00:00:00.00+

microssegundo

time, timestamp, e interval aceitam um valor opcional de precisão p , que especifica o número de dígitos fracionários presentes no campo de segundos. Por padrão não existe limite explícito para a precisão. O intervalo permitido para p é de 0 a 6 para os tipos timestamp e interval.

4.4. Tipo Booleano

O PostgreSQL disponibiliza o tipo boolean padrão do SQL. O tipo boolean pode possuir apenas dois estados: "verdade" ou "falso". O terceiro estado “desconhecido" é representado pelo valor nulo do SQL.

Os valores literais válidos para o estado "verdade" são:

TRUE

't' 'true' 'y' 'yes' '1'

Para o estado "falso" os seguintes valores podem ser utilizados:

5. Criar a estrutura de uma tabela

5.1. Regras de Nomeação

Nomes de tabelas e nomes de colunas:

Devem começar com uma letra

Devem conter somente A_Z, a_z, 0_9, _, $, e #

Não devem duplicar o nome de um outro objeto de um mesmo proprietário.

5.2. Como criar uma tabela

Uma tabela em um banco de dados relacional é muito semelhante a uma tabela no papel: é composta por linhas e colunas. O número e a ordem das colunas são fixos, e cada coluna possui um nome. O número de linhas é variável, refletindo a quantidade de dados armazenados em um determinado instante. O SQL não dá nenhuma garantia relativa à ordem das linhas na tabela. Quando uma tabela é lida, as linhas aparecem em uma ordem aleatória, a não ser que uma ordenação seja explicitamente requisitada.

Cada coluna possui um tipo de dado. O tipo de dado restringe o conjunto de valores que podem ser atribuídos à coluna.

Para criar uma tabela é utilizado o comando CREATE TABLE , próprio para esta tarefa. Neste comando são especificados ao menos o nome da nova tabela, os nomes das colunas, e os tipos de dado de cada coluna.

Exemplo:

CREATE TABLE tsuitipoorgaocolegiado ( numtipoorgaocol INTEGER , strtipoorgaocol VARCHAR(200) );

O tipo numeric pode armazenar a parte fracionária, usual em valores monetários.

5.3. Campos com restrição de não-nulo

Uma restrição de não-nulo simplesmente especifica que uma coluna não pode conter o valor nulo. Um exemplo da sintaxe:

Exemplo:

CREATE TABLE tsuitipoorgaocolegiado ( numtipoorgaocol INTEGER NOT NULL , strtipoorgaocol VARCHAR(200) NOT NULL

);

Uma restrição de não-nulo é sempre escrita como restrição de coluna.

5.4. Coluna com valor padrão

Uma coluna pode possuir um valor padrão. Quando uma nova linha é criada, e nenhum valor é especificado para algumas colunas, o valor padrão de cada uma destas colunas é atribuído à mesma.

Na definição da tabela, o valor padrão é posicionado após o tipo de dado da coluna. Por exemplo:

CREATE TABLE sui.tsuientidade ( numentidade int4 NOT NULL, chrnatent char(1) NOT NULL DEFAULT ‘I’, chrtpent char(1) NOT NULL DEFAULT ‘U’ )

5.5. Restrição de unicidade

A restrição de unicidade garante que os dados contidos na coluna, ou no grupo de colunas, é único em relação a todas as outras linhas da tabela.

Exemplo:

CREATE TABLE tsuitipoorgaocolegiado ( numtipoorgaocol INTEGER NOT NULL, strtipoorgaocol VARCHAR(200) NOT NULL UNIQUE );

Quando escrita como restrição de coluna, e

CREATE TABLE tsuitipoorgaocolegiado ( numtipoorgaocol INTEGER NOT NULL, strtipoorgaocol VARCHAR(200) NOT NULL, UNIQUE (strtipoorgaocol) );

Quando escrita como restrição de tabela.

Somente uma chave primária pode ser especificada para uma tabela, seja como uma restrição de coluna ou como uma restrição de tabela.

A restrição de chave primária deve abranger um conjunto de colunas que seja diferente de outro conjunto de colunas abrangido por uma restrição de unicidade definida para a mesma tabela.

A teoria de banco de dados relacional determina que toda tabela deve ter uma chave primária.

Exemplo:

CREATE TABLE tsuitipoorgaocolegiado ( numtipoorgaocol INTEGER NOT NULL PRIMARY KEY , strtipoorgaocol VARCHAR(200) NOT NULL );

Você poderá criar uma chave primária com duas ou mais colunas:

CREATE TABLE exemplo ( a integer, b integer, c integer, PRIMARY KEY (a, c) );

5.8. Chave estrangeira

A restrição de chave estrangeira especifica que o valor da coluna (ou grupo de colunas) deve corresponder a algum valor que existe em uma linha de outra tabela. Diz-se que este comportamento mantém a integridade referencial entre duas tabelas relacionadas.

Supondo que já temos a tabela de Tipo de Órgão Colegiado utilizada diversas vezes anteriormente:

CREATE TABLE tsuitipoorgaocolegiado ( numtipoorgaocol INTEGER NOT NULL PRIMARY KEY, strtipoorgaocol VARCHAR(200) NOT NULL );

Agora vamos supor, também, que existe uma tabela armazenando os Órgãos Colegiados destes Tipos de Órgãos Colegiados, e desejamos garantir que a tabela de Órgãos Colegiados somente contenha tipos de órgão de colegiado que realmente existem. Para isso é definida uma restrição de chave estrangeira na tabela Órgão Colegiado, fazendo referência à tabela Tipo de Órgão Colegiado.

Exemplo:

CREATE TABLE tsuiorgaocolegiado ( numorgaocol INTEGER NOT NULL, tipoorgaocoleg_numtipoorg INTEGER NOT NULL REFERENCES tsuitipoorgaocolegiado (numtipoorgaocol), strnomeorgaocol VARCHAR(100) NOT NULL, datativorgaocol DATE NOT NULL, strsiglaorgaocol VARCHAR(10) , datdesativorgaocol DATE strfunlegalorgaocol VARCHAR(200), strstatus CHAR(1) NOT NULL );

Isto torna impossível criar Órgaos Colegiados com ocorrências de tipoorgaocoleg_numtipoorg que não existam na tabela Tipo de Órgão Colegiado.

Nesta situação é dito que a tabela Órgaos Colegiados é a tabela que faz referência , e a tabela Tipo de Órgão Colegiado é a tabela referenciada. Da mesma forma existem colunas fazendo referência e sendo referenciadas.

O comando acima pode ser abreviado escrevendo-se:

CREATE TABLE tsuiorgaocolegiado ( numorgaocol INTEGER NOT NULL, tipoorgaocoleg_numtipoorg INTEGER NOT NULL REFERENCES tsuitipoorgaocolegiado , strnomeorgaocol VARCHAR(100) NOT NULL, datativorgaocol DATE NOT NULL, strsiglaorgaocol VARCHAR(10) , datdesativorgaocol DATE strfunlegalorgaocol VARCHAR(200), strstatus CHAR(1) NOT NULL );

Na ausência da lista de colunas, a chave primária da tabela referenciada é assumida como sendo a coluna referenciada.

Obviamente, o número e o tipo das colunas na restrição precisam corresponder ao número e tipo das colunas referenciadas.

ALTER TABLE tsuitipoorgaocolegiado ADD CONSTRAINT uk_strtipoorgaocol UNIQUE (strtipoorgaocol);

ALTER TABLE tsuiorgaocolegiado ADD FOREIGN KEY (tipoorgaocoleg_numtipoorg) REFERENCES tsuitipoograocolegiado;

Para adicionar uma restrição de não nulo, que não pode ser escrita na forma de restrição de tabela, deve ser utilizada a sintaxe:

ALTER TABLE tsuitipoorgaocolegiado ALTER COLUMN chrtipo SET NOT NULL;

A restrição será verificada imediatamente, portanto os dados da tabela devem satisfazer a restrição antes desta ser criada.

6.4. Removendo restrição

Para excluir uma restrição é necessário conhecer seu nome. Quando o usuário atribuiu um nome à restrição é fácil, caso contrário o sistema atribui para a restrição um nome gerado que precisa ser descoberto. O comando \d nome_da_tabela do psql pode ser útil nesta situação; outras interfaces também podem oferecer um modo de inspecionar os detalhes das tabelas. O comando a ser utilizado para excluir a restrição é:

ALTER TABLE tsuitipoorgaocolegiado DROP CONSTRAINT uk_strtipoorgaocol;

Esta sintaxe serve para todos os tipos de restrição exceto não-nulo. Para excluir uma restrição de não-nulo deve ser utilizado o comando:

ALTER TABLE tsuitipoorgaocolegiado ALTER COLUMN chrtipo DROP NOT NULL;

(Lembre-se que as restrições de não-nulo não possuem nome).

7. Excluindo uma tabela

Finalmente, deve ser mencionado que se a tabela não for mais necessária, ou se deseja recriá-la de uma forma diferente, esta pode ser removida através do seguinte comando:

DROP TABLE tsuitipoorgaocolegiado;