Aula PostgreSQL

  • View
    1.835

  • Download
    3

Embed Size (px)

Text of Aula PostgreSQL

Laboratrio de Banco de DadosIntroduo ao PostgreSQL Profs. Valria Gonalves Soares Mateus Fernandes Cunha DIMAp/UFRN

ContedoIntroduo ao PostgreSQL Conceitos Bsicos Acessando o Banco de Dados Linguagem SQL Definio de Dados Manipulao de Dados Consulta aos Dados Funes e Operadores Gatilhos e Procedimentos Administrao da Base de Dados Conceitos Bsicos Desenvolvimento de Aplicativos

Objetivo da DisciplinaOferecer aos alunos condies de trabalhar com um Banco de Dados Relacional, deixando-os aptos a: Criar uma Base de Dados; Manipular a Base de Dados; Consultar a Base de Dados. Oferecer subsdios ao desenvolvimento de um aplicativo que acesse o banco de dados e disponibilize todas as funcionalidades bsicas requeridas de um sistema de BD.

Introduo ao PostgreSQLEmbora o PostgreSQL seja um sistema gerenciador de banco de dados objetorelacional, nesta disciplina iremos abordar apenas os aspectos relacionais do mesmo. O PosgreSQL baseado no POSGRES que foi pioneiro em muitos conceitos que se tornaram disponveis posteriormente em muitos sistemas de bancos de dados comerciais. O PosgreSQL open-source e suporta tanto o SQL92 como o SQL99, alm de oferecer muitas caractersticas modernas, tais como:

Introduo ao PostgreSQLCaractersticas PostgreSQL: Consultas complexas Chaves estrangeiras Gatilhos Vises Integridade transacional Controle de concorrncia multiverso PostgreSQL pode ser estendido pelo usurio de muitas maneiras, por exemplo, pela adio de novos: Tipos de dados Funes Operadores Funes de Agregao Mtodos de Indexao Linguagens Procedurais

Arquitetura PostgreSQLO PostgreSQL usa um modelo cliente/servidor. Uma sesso PostgreSQL consiste dos seguintes programas que cooperam entre si:

Um processo servidor que gerencia os arquivos de bancos de dados, aceita as conexes ao banco a partir das aplicaes clientes, e executa aes no banco de dados solicitadas pelos clientes. O programa servidor de banco de dados chamado postmaster. Cliente(front-end): So as aplicaes que precisam acessar ao banco: Pode ser ferramentas grficas, modo texto, via Web, ferramentas de manuteno e gerenciamento de banco, ferramentas de programao de triggers e procedures, ou qualquer outra ferramenta que precise interagir com o PostgreSQL. A comunicao entre Cliente e Servidor feita via TCP/IP.

Arquitetura PostgreSQLO servidor PostgreSQL pode gerenciar mltiplas conexes de clientes de modo concorrente. Para cada conexo, o servidor inicia um novo processo servidor para a comunicao com o cliente (fork). A comunicao entre o servidor e o cliente feita sem interveno do postmaster. Quando o postmaster recebe um novo pedido de conexo, ele cria um novo fork que far a comunicao entre o cliente e o servidor PostgreSQL.

Primeiros PassosPara acessar o PostgreSQL dentro do DIMAp inicialmente precisa-se acessar a mquina louco servidora do bd: ssh nome_da_maquina ou ssh (ip da maquina) Ou direto, atravs do psql: psql meubanco U nomeusuario h nome_da_maquina

Criando um Banco de DadosUm servidor PostgreSQL pode gerenciar vrios bancos. Normalmente, um banco separado usado para cada usurio ou projeto. Para criar um novo banco de dados, que neste exemplo, estamos chamando de meubanco utiliza-se o seguinte comando: $ createdb meubanco Que ir produzir a seguinte resposta: CREATE DATABASE Um nome de banco de dados dever ter um primeiro caractere alfabtico e so limitados a 63 caracteres no mximo. Pode-se tambm criar um banco de dados com o mesmo nome no usurio corrente. Para isso basta digitar: $ createdb Para remover um banco de dados, se voc for o dono do mesmo, basta digitar: $ dropdb meubanco

Acessando um Banco de DadosUm Banco de Dados pode ser acessado por diversas maneiras: 1. Terminal interativo do PostgreSQL (psql); 2.Ferramentas grficas para a criao, administrao e manipulao de bancos (pgAdmin); 3.Aplicaes desenvolvidas pelos usurios utilizando conexes via ODBC ou JDBC.

Acessando o Banco de DadosO psql permite ao usurio entrar, editar e executar interativamente comandos SQL$ psql meubancoWelcome 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 DadosVerificando a verso do PostgreSQL:$ SELECT version();

Help: \h Sair do psql: \q

Comandos Bsicos psqlPode-se criar um arquivo texto com todas as operaes de criao de tabelas e de consultas, e posteriormente executa-lo da seguinte forma: \i caminho/nomedoarquivosql Para ver a descrio da Tabela \d nomedatabela Para ver todas as tabelas criadas \d

PostgreSQL

Linguagem SQL

IntroduoA Structured Query Language (SQL) permite aos usurios acessar, definir e manipular dados em bancos de dados relacionais, como PostgreSQL, Oracle, Sybase, Informix, Microsoft SQL Server, MySql, entre outros. Nos RDBMS os dados so armazenados em tabelas. Uma tabela pode ser definida como uma coleo de linhas, onde cada linha em uma tabela formada pelo mesmo conjunto 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 BsicosDDL - Data Definition Language (Linguagem de Definio de Dados):CREATE TABLE DROP TABLE

DML Data Manipulation Language (Linguagem de Manipulao de Dados): SELECT INSERT UPDATE DELETE

PostgreSQL

Definio de Dados

Linguagem de Definio de DadosCriao das estruturas do banco de dados. Criao, modificao e eliminao de tabelas. Controle dos dados armazenados nas tabelas. Privilgios associados a tabelas.

Definio de TabelasPara se criar uma nova tabela especifica-se o nome da mesma e os nomes de seus atributos colunas com os seus respectivos tipos. O comando no acaba enquanto o psql no encontrar o ponto-e-vrgula. Espaos 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 80 caracteres no seu tamanho, o tipo int o tipo inteiro normal, o tipo real armazena nmeros de ponto flutuante de simples preciso, o tipo date armazena datas.

Criando uma Nova TabelaCREATE TABLECREATE TABLE pessoa ( nome cpf endereco cidade estado nascimento ); VARCHAR(20), CHAR(11) VARCHAR(30), VARCHAR(15), CHAR(2), 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 TabelaSe no se desejar mais a tabela, por fim, pode-se remov-la com o seguinte comando: DROP TABLE tablename; O comando DROP TABLE elimina permanentemente uma tabela do banco de dados. Exemplo: DROP TABLE pessoa;

Valores DefaultUm coluna pode receber um valor padro toda vez que uma nova linha for inserida no sistema, e nenhum valor for especificado para ela.CREATE TABLE pessoa ( idpessoa integer, nome text, ... estado char(2) DEFAULT NI);

RestriesCheck Constraints; Not-Null Constraints; Unique Constraints; Primary Keys; Foreign Keys.

Check ConstraintsAo inserir ou atualizar uma coluna, o novo valor s ser aceito se satisfazer a expresso de restrio.CREATE TABLE pessoa (idpessoa nome ... sexo CHECK ); integer, text, char(1) 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 ConstraintsOutro Exemplo de Restrio.CREATE TABLE produtos ( idproduto integer, preco numeric, desconto numeric, CONSTRAINT ck_preco CHECK (preco > 0) CONSTRAINT ck_desconto CHECK (desconto > 0 AND preco > desconto) );

Restrio Not NullNo 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) );

Restrio UNIQUEGarante 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);

Restrio UNIQUE: Outros FormatosCREATE TABLE pessoa ( idpessoa integer, nome text, UNIQUE (idpessoa)); CREATE TABLE pessoa ( a integer, b integer, c integer UNIQUE (a,b));

PRIMARY KEY (Chave Primria)Normalmente usada como identificador nico para linhas na tabela. Tecnicamente pode ser considerada como uma juno das restries Not-Null e Unique. Uma tabela s pode ter uma nica chave primria, 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 de colunas dependem da existncia em alguma linha de outra tabela. Mantm a integridade referencial entre as duas tabelas.CREATE TABLE aluno ( matricula numeric PRIMARY KEY idpessoa integer REFERENCES pessoa(idpessoa), ira real );

FOREIGN KEY (Chave Estrangeira)

ON DELETE CASCADE ON DELETE SET NULL ON DELETE SET DEFAULTCREATE TABLE aluno ( matricula numeric PRIMARY KEY, idpessoa integer REFERENCES pessoa(idpessoa) ON DELETE CASCADE, ira real );

Modificando TabelasAdicionar Colunas. Remover Colunas. Adicionar Restries. Remover Restries. Mudar Valor Default. Renomear Colunas. Renomear Tabelas.

Adicionando Uma Nova ColunaAdiciona uma nova coluna em uma tabela existente. ALTER TABLE pessoa ADD COLUMN email text; A definio da restrio pode ser feita no momento da criao da coluna, com exceo de Not-Null. ALTER TABLE pessoa ADD COLUMN sexo char(1) CHECK (sexo IN (M,F));

Removendo Uma ColunaTambm pode-se remover uma coluna de uma tabela. O comando abaixo especificado remove u