Banco de Dados SQL

INTRODUO A SISTEMAS DE BANCO DE DADOS

2010

BANCO DE DADOSAplicao da Structure Query LanguageEste e-book visa, atravs de exemplos prticos, aplicar os conceitos da Structure Query Language (SQL) na manipulao de Banco de Dados relacionais.

PARTES 1 AO 7

Ricardo R. Barcelar http://www.ricardobarcelar.com.br [email protected]

APRESENTAO

APRESENTAO

O

objetivo deste material apresentar a Structure Query Language como linguagem padro para manipulao de bancos de dados relacionais, norteando o estudo de banco de dados atravs de exemplos prticos aplicados a um Sistema Gerenciador de Banco de Dados amplamente conhecido e utilizado, o PostgreSQL.

CELSO HENRIQUE PODEROSO DE OLIVEIRA, NAVATE e ELMASRI so referncias para os estudos traados neste material. Dessa forma, o contedo apresentado certamente poder ser utilizado como referncia na vida prtica do profissional de computao que lida diariamente com Banco de Dados.

Ricardo R. Barcelar

PROJETO FSICO DE BANCO DE DADOS

1PartePROJETO FSICO DE BANCO DE DADOS

A

primeira etapa a fazer ao comea a trabalhar com banco de dados estudar as tcnicas utilizadas para se criar um projeto de banco de dados: Modelo EntidadeRelacionamento, Modelo Lgico usando ferramenta Case, Normalizao, Estratgias de Projeto de Banco de Dados, etc.

Conhecedores dos conceitos de Codd, Chen, e outros autores que definiram o padro relacional para banco de dados, podem avanar mais um passo: implementar o banco de dados em uma ferramenta apropriada. Mas antes, ainda necessrio conhecer alguns conceitos do Projeto Fsico e dos Sistemas Gerenciadores de Banco de Dados especificamente. O Modelo Fsico de Dados a especificao em SQL - Structure Query Language Linguagem de Consulta Estruturada - do esquema relacional para o Sistemas Gerenciadores de Banco de Dados escolhido. Nesta fase, as estruturas de armazenamento e de recuperao de informaes, bem como os mecanismos de acesso devem ser escolhidos, visando sempre o aprimoramento da performance dos aplicativos de Banco de Dados. Nesta fase devem ser especificados no apenas as tabelas criadas, mas tambm os ndices necessrios, as restries de integridade (checks e triggers), algumas operaes de incluso, excluso e atualizao de dados para cada tabela, bem como as consultas que a aplicao deve realizar.

1.1 FATORES QUE INFLUENCIAM O PROJETO FSICO DE BANCO DE DADOSEsta fase do projeto de BD objetiva no s propor uma apropriada estruturao de dados, mas faz-lo de maneira que garanta um bom desempenho. Sendo assim, no possvel tomar decises significativas sobre projetos fsicos e anlises de desempenho, at que conheamos as consultas, as transaes e as aplicaes que devem ser executadas no banco de dados. Dessa forma, vamos discutir alguns fatores: a. Analisar as consultas e transaes do Banco de Dados importante ter uma idia das intenes de uso do banco de dados, definindo as consultas e transaes que esperamos que sejam realizadas em alto nvel, especificando o seguinte: - Os arquivos que sero acessados pela consulta;

3


- Os atributos nos quais quaisquer condies de seleo para a consulta estejam especificados; - Os atributos nos quais quaisquer condies de juno ou condies para ligar mltiplas tabelas ou objetos para a consulta estejam especificadas; - Os atributos cujos valores sero trazidos atravs da consulta. Para cada operao ou transao de atualizao, devemos especificar o seguinte: - Os arquivos que sero atualizados; - O tipo de operao em cada arquivo (insert, update, delete) - Os atributos nos quais condies de seleo para uma excluso ou atualizao estejam especificados. - Os atributos cujos valores sero alterados atravs de uma operao de atualizao. b. Analisar a freqncia esperada de solicitao (execuo) de consultas e transaes necessrio considerar as taxas de solicitao (execuo), usadas de forma estatsticas em situaes prticas para grandes volumes de processamento. c. Analisar as restries de tempo de consulta e transaes Neste caso, algumas consultas e transaes podem ter rigorosas restries de desempenho. Por exemplo, uma transao que deve ser interrompida se no for concluda em 20 segundos d. Analisar as freqncias esperadas de operaes de atualizao Um nmero mnimo de caminhos de acesso aos dados deve ser especificado para um arquivo que seja freqentemente atualizado, uma vez que atualizar os prprios caminhos de acesso desacelera as operaes de atualizao. e. Analisar as restries de Unicidade em Atributos Caminhos de acesso aos dados devem ser especificados em todos os atributos candidatos a chave ou chave primria. A existncia de um ndice facilita a pesquisa nos arquivos de dados, pois este define um caminho de dados. Realizadas as anlises iniciais, outra deciso importante definir qual Sistema Gerenciador de Banco de Dados usar. relevante levar em considerao vrios fatores, como: - O modelo fsico, visto que determinar qual a carga de trabalho que ser exigida pelo SGBD, isso j eliminar algumas opes. - O custo. Uma locadora, por exemplo, ir adquirir uma licena Oracle ou SQL Server? - Plataforma de Sistema Operacional Contudo, esta uma deciso que no ser analisada neste momento.

4


1.2 SQL - STRUCTURE QUERY LANGUAGE - LINGUAGEM ESTRUTURADA CONSULTA

DE

Como visto no mdulo passado, muito simples criar um script SQL atravs de uma ferramenta Case. No entanto, este no ser o foco desta sesso. Aqui aprenderemos a criar, sem auxlio de ferramentas case, a gerar nosso script do Banco de Dados. Para tanto necessrio conhecermos a linguagem SQL. Apesar do nome, fazer consultas a bancos de dados no a nica funo de SQL. Ela utilizada para criar tabelas, inserir, excluir e alterar dados do banco de dados, alm de outras utilizaes. A SQL foi desenvolvida na dcada de 70 pela IBM em uma das primeiras tentativas de desenvolver um banco de dados relacional. Tornou-se padro de fato depois de 1986, quando a ANSI (American National Standards Institute) endossou como linguagem padro para Banco de Dados relacionais e desde ento, o j sofreu trs atualizaes oficiais: em 1989, 1992 e 1999. Nesta ltima, algumas das mudanas mais significativas esto relacionadas com a definio dos padres para Banco de Dados Objeto-Relacionais. Devido ao sucesso da forma de consultar e manipular dados da SQL, dentro de um ambiente de banco de dados, a utilizao da SQL foi se tornando ainda maior. Cabe ressaltar que cada implementao de SQL possui uma adaptao da sintaxe para resolver determinados problemas, portanto, qualquer comando mostrado pode ser usado de forma diferente em um determinado Sistema Gerenciador de Banco de Dados. Recomenda-se a leitura do manual do fabricante para maiores informaes sobre o uso da linguagem SQL em Sistema Gerenciador de Banco de Dados comerciais. Atualmente, a linguagem SQL assume um papel muito importante nos sistemas de gerenciamento de banco de dados, podendo ter muitos enfoques. Atravs de comandos SQL, possvel montar consultas poderosas sem a necessidade da criao de um programa, ou utilizar comandos SQL embutidos em programas de aplicao que acessam os dados armazenados. Devido ao fato de possuir vrias aplicaes, a linguagem SQL prov suporte a vrias funes. Que consiste em: - DDL (Linguagem de definio de dados), onde os dados a serem armazenados so definidos e estruturados; (Create, Alter e Drop). - DML (Linguagem de manipulao de dados), que permite a incluso, remoo, seleo ou atualizao de dados armazenados no banco de dados; Controle de acesso, permitindo proteo dos dados de manipulaes no autorizadas; (Select, Insert, UpDate, Delete, Commit e Rollback). - DCL (Linguagem de controle de dados) uma subclasse da DML responsvel pelas permisses de acesso ao banco; (Grant e Revoke). Restries de Integridade, que auxiliam no processo de definio da integridade dos dados, protegendo contra corrupes, inconsistncias e falhas do sistema de computao. Alm dessas caractersticas principais, ainda podemos citar: - Vises, onde so especificadas as consultas disponveis atravs de tabelas virtuais (Views).5


- Gatilhos a fim de automatizar processos no banco (Triggers). Outra caracterstica a capacidade de cancelar uma srie de atualizaes ou gravar depois de concluir uma srie de atualizaes.

1.3 CRIANDO UM BANCO DE DADOSEsta etapa trata da Data Definition Language (DDL). O processo bsico consiste simplesmente em atribuir tipos de dado e tamanho para cada um dos atributos que foram identificados. Observao: - Cada SGBD adota um terminador de comando. O mais comum o ponto e vrgula (;) - Quanto aos comandos apresentados, os SGBD no so sensveis a letras maisculas ou minsculas. Contudo, o contedo das colunas normalmente sensvel, portanto muito cuidado.

1.3.1 Desnormalizao de dados Em uma anlise mais profunda, muitas vezes conveniente avaliar a necessidade alguns campos redundantes na tabela. Esse processo chamado de desnormalizao de dados. Ocorre que quando idealizamos o modelo de dados, utilizamos o conceito de processador perfeito, em que as informaes so transmitidas sem custo instantaneamente. Mesmo bancos de dados avanados possuem limitaes. Esta tcnica pode ser exemplificada pelo caso dos campos calculados que so eliminados na 3 Forma normal. Quando se tem um grande banco de dados que trata de Notas Fiscais, por questes de performance costuma-se driblar um pouco a regra e coloca-se o valor total da nota como um campo na tabela. Isso pode ser feito quando o banco de dados possui mecanismo de controle de transaes, em que uma eventual gravao no banco de dados implique necessariamente a gravao na outra ponta. Esta operao deve ser feita por meio de gatilhos (triggers) para garantir a integridade das informaes. 1.3.2 Definio de dados Antes de criar as tabelas no nosso banco de dados, temos que definir quais so as caractersticas de cada um dos campos. As caractersticas que o SQL exige so o tipo do dado e o tamanho de cada campo. As informaes aqui apresentadas so utilizadas pela maioria dos bancos de dados. Eventuais mudanas devem ser consultadas na documentao do Banco de dados adotado. TIPO DE DADO Integer ou Int Smallint Numeric DESCRIO Nmero positivo ou negativo inteiro. O nmero de bytes utilizado varia de acordo com o banco de dados utilizado. Mesma funo do Inteiro, mas ocupa cerca da metade do espao Nmero positivo ou negativo de ponto flutuante. Deve-se informar tamanho do campo e a quantidade de casas decimais.

6


Decimal

Semelhante ao Numeric, mas, em alguns bancos de dados, poder ter uma maior preciso aps a vrgula. Nmero de ponto flutuante de simples preciso. A diferena bsica que os valores sero armazenados em representao exponencial, portanto ser arredondado para o nvel mais prximo de preciso. Nmero de ponto flutuante de dupla preciso. Comporta-se como o Real, mas permite maior aproximao de resultados. Nmero de ponto flutuante em que voc define o nvel de preciso (nmero de dgitos significativos) Armazenamento de um nmero fixo de bits. O nmero de bits deve ser indicado, do contrrio o padro ser 1. Igual ao Bit, permitindo armazenar valores maiores. Normalmente, utilizase para armazenar imagens. Permite armazenar datas Permite armazenar horrios Permite armazenar uma combinao de data e hora Permite armazenar cadeias de caracteres (letras, smbolos e nmeros). O tamanho informado fixo e indica o tamanho mximo da cadeia de caracteres.

Real

Double Precision Float Bit Bit Varying Date Time Timestamp Character ou char

Permite armazenar cadeias de caracteres, mas com tamanho varivel. Character Varying Neste caso, especifica-se o tamanho mximo da coluna. Se for utilizado ou Varchar menos espao que o mximo definido, o espao restante no ser ocupado. Interval Intervalo de data ou hora

1.3.3 Adaptando o modelo de dados Com base nas informaes acima, adaptaremos nosso modelo de dados. Para nossos estudos usaremos o modelo lgico abaixo:

7


Figura 1- Modelo de dados

Notao dos relacionamentos 1:n FK obrigatria. Parte da chave primria 1:n FK no obrigatria n:m Muitos para muitos (cria-se a tabela associativa)

1.3.4 Criao de Tabelas Tabelas so as estruturas mais importantes de um banco de dados. Nas tabelas estar o contedo que representa cada objeto do mundo real. As prprias tabelas criadas no banco de dados ficam armazenadas em tabelas internas do gerenciador de banco de dados. o chamado Dicionrio de Dados.

8


Sintaxe: CREATE TABLE nome_da_tabela (coluna1 tipo_de_dado constraint, coluna2 tipo_de_dado constraint, coluna3 tipo_de_dado constraint

Exemplo: CREATE departamento (dep_id INTEGER NOT NULL, dep_nome VARCHAR(100), dep_atividade VARCHAR(100), empr_id INTEGER NOT NULL);

1.3.5 Constraints Integridade Referencial Restrio de Integridade (RI) Constraints so regras agregadas a colunas ou tabelas. Assim, pode-se definir um campo como obrigatrio ou no, ou aceitar apenas alguns valores predefinidos. No caso de regras aplicadas a tabelas, tem-se a definio de chaves primrias (PK) e estrangeiras (FK). Um bom Sistema Gerenciador de Banco de Dados deve evitar a entrada de informao incorreta ou inconsistente em sua base de dados, garantindo, com isso, a qualidade da informao inserida. Uma restrio de integridade (RI) uma condio especificada no esquema da base de dados para restringir a informao a ser armazenada. As RI so especificadas e conferidas em dois momentos diferentes: - Na especificao da RI: se d na definio do esquema da base de dados pelo usurio ou pelo administrador da base de dados (DBA); - Na conferncia da RI: feita pelo banco de dados toda vez que uma relao modificada por uma aplicao sendo executada.

1.3.5.1 TIPOS DE CONSTRAINTS MAIS COMUNS As constraints variam muito de um banco para outro. a) Chave Primria: a coluna identificadora de um registro na tabela. Para representla basta acrescentar a palavra chave PRIMARY KEY seguida do nome da coluna. Exemplo: ... PRIMARY KEY (cliente_id), ...

b) Chave Estrangeira: o campo que estabelece o relacionamento entre duas tabelas. Dessa forma, deve-se especificar na tabela que contm a chave estrangeira quais so essas colunas e a qual tabela est relacionada. Ao determinar este tipo de relacionamento, fica garantida a integridade das informaes. Os valores presentes na coluna definida com chave estrangeira devem ter um correspondente na outra tabela.

9


Exemplo1: ... FOREING KEY nome_chave (coluna) REFERENCES (tabela) ON UPDATE ao ON DELETE ao Ao: Determina qual ao o banco de dados deve tomar quando for excluda ou alterada uma linha da tabela que contm referncia a esta chave. Pode ser: - SET NULL: Altera o contedo da coluna para nulo, perdendo a referncia, sem deixar valores inconsistentes. - SET DEFAULT: Altera o contedo da coluna pra um valor especificado na clusula DEFAULT, se houver. - CASCATE: Exclui ou altera todos os registros que se relacionam com a ele. - NO ACTION: Em caso de alterao, no modifica os valores que se relacionam a eles. - RESTRICT: No permite a excluso da PK. Exemplo2:

CREATE TABLE funcionario (fun_id fun_admissao DATE, dept_id INTEGER, PRIMARY KEY (fun_id),

INTEGER,

fun_nome

VARCHAR(50),

FOREIGN KEY (dept_id) REFERENCES departamento ON DELETE CASCADE ON UPDATE NO ACTION

c) DEFAULT: Atribui um contedo padro a uma coluna da tabela. Exemplo: ... prod_quantidade INTEGER DEFAULT 1, ...

d) NOT NULL: Indica que o contedo de uma coluna no poder ser Nulo. Lembre-se, em banco de dados SQL, colunas sem valor atribudo possuem contedo Nulo.

10


Exemplo: ... cliente_nome VARCHAR(50) NOT NULL, ...

e) UNIQUE: Indica que no pode haver repetio no contedo da coluna. No a mesma coisa que chave primria. A chave primria, alm de no permitir repetio, no pode conter valores nulos, dentre outras caracterstica no existentes nesta restrio. Exemplo: ... cliente_CPF NUMERIC(11) UNIQUE, ...

f) CHECK: Definio de domnio: Um domnio uma expresso de valores possveis para o contedo de uma coluna. Exemplo: ... sexo CHAR(1) CHECK (UPPER(sexo) = M OR UPPER(sexo) = F),

1.3.6 Alterao da estrutura da tabela Para alterar a estrutura de uma tabela, utilizamos o comando ALTER TABLE.

1.3.6.1 ACRESCENTAR NOVAS COLUNAS O comando utilizado para acrescentar novas colunas muito semelhante ao da criao de colunas em uma tabela: Sintaxe: ALTER TABLE nome_da_tabela ADD coluna1 tipo_de_dado constraint, coluna2 ... tipo_de_dado constraint, Exemplo: ALTER TABLE departamento ADD dep_ramal NUMBER(4) UNIQUE

1.3.6.2 ACRESCENTAR NOVAS CONSTRAINTS O comando utilizado para acrescentar novas constraints muito semelhante ao da criao de constraints em uma tabela:

11


Sintaxe: ALTER TABLE nome_da_tabela ADD (constraint)

Exemplo: ALTER TABLE departamento ADD PRIMARY KEY (dep_id)

1.3.6.3 MODIFICAR COLUNAS O comando utilizado para modificar qualquer caracterstica de uma coluna. Sintaxe: ALTER TABLE nome_da_tabela MODIFY (nome-coluna constraint) Exemplo: ALTER TABLE departamento

tipo_dado MODIDY dep_ramal INTEGER NOT NULL

No necessrio repetir o que no estamos modificando.

1.3.6.4 EXCLUINDO ELEMENTOS Pelo padro SQL, deveria ser possvel excluir colunas ou constraints de uma tabela. Alguns bancos de dados no permitem a excluso de colunas. Sintaxe: ALTER TABLE nome_da_tabela DELETE elemento Exemplo: ALTER TABLE departamento DELETE dep_ramal

ALTER TABLE departamento DELETE PRIMARY KEY

Abaixo um exemplo onde vrias alteraes esto sendo realizadas em um s comando: ALTER TABLE fornecedores ADD cgc NUMBER(14), DROP tipofornecedor, ADD CONSTRAINT ck_email CHECK (e_mail CONTAINING @ OR e_mail IS NULL)

1.3.7 Eliminando uma Tabela Para eliminar uma tabela do banco de dados, utilizamos o comando DROP TABLE seguido do nome da tabela. Alguns bancos somente permitiro esta operao se esta no estiver relacionada outra tabela.

12


Sintaxe: DROP TABLE nome_da_tabela

Exemplo: DROP TABLE departamento

5. EXERCCIOSUsando a Linguagem SQL crie o Schema de banco de dados para o seguinte modelo lgico:

13

NDICE

2ParteNDICE

Q

uando criamos a estrutura de um banco de dados temos que usar artifcios que melhorem a performance na recuperao dos dados que l sero armazenados. Dentre vrias tcnicas existentes para melhorar a esta performance existe a criao de ndices.

O ndice um arquivo auxiliar associado a uma Tabela. Sua funo acelerar o tempo de acesso s linhas de uma Tabela, cria ponteiros para os dados armazenados em colunas especificas. O Banco de dados usa o ndice de maneira semelhante ao ndice remissivo de um livro, verifica um determinado assunto no ndice e depois localiza a sua posio em uma determinada pgina.

1.1 CRIAO DE NDICEO ndice serve para prover um acesso rpido a linhas das tabelas. Por meio dele possvel unir uma ou mais colunas por onde o acesso mais freqente. Exemplo: Temos uma tabela de pessoas e normalmente queremos fazer buscas em ordem alfabtica. O nome, no uma boa chave primria para a tabela por ser alfanumrica, o que deixa as pesquisas mais lentas e tambm pela repetio de nomes. Dessa forma, cria-se um ndice para o nome da pessoa. Assim, garante-se um acesso mais rpido aos nomes, visto que os dados buscados estariam fora de seqncia. Os valores indexados so armazenados em um objeto do banco de dados em ordem, o que permite ao Sistema Gerenciador de Banco de Dados pesquisar primeiro no ndice, para depois buscar na tabela. importante ressaltar que alguns bancos de dados necessitam de ndices constantemente atualizados, pois eventualmente estes perdem a referncia ao dado. 1.1.1 Quando Criar um ndice O ndice quando criado com critrios bem definidos traz uma enorme vantagem, mas por outro lado pode trazer enormes problemas, visto que todo trabalho de um Sistema Gerenciador de Banco de Dados envolve um custo. No conveniente sair criando ndices para cada um dos campos da tabela sem que haja uma razo para isto. Quando um ndice criado uma das tarefas do Sistema Gerenciador de Banco de Dados mant-lo atualizado a cada nova incluso, excluso e alterao nos dados. Alm do

14

NDICE

mais, o ndice tambm ocupa espao no banco de dados, pois criado um novo objeto que manter informaes indexadas e armazenar a referncia linha fsica da tabela. NOTA: Se so criados muitos ndices, pode-se prejudicar o desempenho do banco de dados Se so criados poucos ndices, pode-se prejudicar o desempenho da aplicao. Quando criar ndices? a) Chaves Primrias: se o banco de dados no cri-las automaticamente, necessrio faz-lo manualmente. b) Chave Estrangeira: sempre recomendvel criar, principalmente quando a tabela que faz referncia muito grande. c) Colunas utilizadas freqentemente na clusula WHERE ou JOIN do comando SELECT. Quando no criar ndices? a) Colunas com muitos valores iguais. b) Colunas com muito contedo NULO (no inicializados). c) Tabelas muito pequenas. NOTA: Sempre que uma consulta estiver muito lenta, verifique a possibilidade de criar ndice para facilitar a busca. Para criar um ndice, utilize a seguinte sintaxe: Sintaxe: Exemplo:

CREATE INDEX nome_index CREATE INDEX idx_dep_nome ON nome_tabela (coluna [ASC|DESC]) ON departamento(dep_nome) CREATE INDEX idx_dep_varios ON departamento(dep_nome,dep_ramal) Para excluir um ndice, utilize a seguinte sintaxe: Sintaxe: DROP INDEX nome_index Exemplo: DROP INDEX idx_dep_varios

No possvel modificar ndices. Para alterar um ndice, elimine-o e, depois, recrie-o. NOTA: Ao eliminar uma tabela, os ndices e as constraints so eliminados automaticamente.

15

NDICE

1.2 ACESSO A DADOS EM UM SISTEMA GERENCIADOR DE BANCO DE DADOSPara compreender melhor a utilizao de ndices importante conhecer a estrutura de acesso a dados em um Sistema Gerenciador de Banco de Dados.

Figura 2 - Organizao de um SGBD

Neste contexto, observamos que os bancos de dados so armazenados fisicamente como arquivos de registros, que so geralmente armazenados em discos magnticos. Assim, os meios de armazenamento formam uma hierarquia de armazenamento que inclui duas categorias principais: - Armazenamento Principal: Operados diretamente pela CPU, como a Memria Principal e Memria Cache. - Armazenamento Secundrio: Inclui os discos magnticos, ticos e fitas. Geralmente possuem uma capacidade maior, porm so mais lentos e no podem ser acessados diretamente pela CPU.

Figura 3 - Estrutura de armazenamento

16

NDICE

A maioria dos bancos de dados est armazenada sob maneira permanente (ou persistente) sob a forma de armazenamento secundrio. Geralmente, o Sistema Gerenciador de Banco de Dados possui diversas opes disponveis para organizar os dados, e o processo relativo ao projeto de banco de dados fsico envolve escolher entre as opes, as tcnicas de organizao de dados. Os dados armazenados em disco so organizados na forma de arquivos de registros. Cada qual uma coleo de valores de dados que podem ser interpretados como fatos sobre entidades, seus atributos e seus relacionamentos. Para acessar uma informao no banco de dados considera-se o tipo de requisio e a estrutura utilizada para organizao e acesso s chamadas pginas de dados. 1.2.1 Organizao dos dados em Memria Secundria - Arquivos Heap ou pile (aglomerado): Melhor opo quando o acesso tpico uma busca seqencial, sem ordem, recuperando todos os registros. Os registros so posicionados no arquivo pela ordem na qual foram includos. So utilizados pelos ndices secundrios e utilizados para coletar e armazenar registros de dados para utilizao futura. Exemplo: um simples arquivo de registros de dados. - Arquivos Ordenados: Melhor opo se os registros devem ser recuperados em alguma ordem, ou quando somente uma poro contgua dos registros ordenados necessria. Exemplo: Arquivo de dados, possivelmente ordenado, alm de vrios ndices. - Arquivos Hashed: Melhor para selees de igualdade. Uma funo hash aplicada ao valor do campo de um registro e retorna o endereo do bloco do disco no qual o registro est armazenado. Para a maioria dos registros, precisamos somente do acesso a um nico bloco para recuperar aquele registro. No adequado para acesso seqencial ordenado. Existem outras maneiras ou possibilidades para organizar arquivos, como rvores-B e arquivos de registro mistos, que implementam relacionamentos entre registros de diferentes tipos fisicamente como parte da estrutura de armazenamento.

1.3 INDEXAOComo visto, os ndices so estrutura de dados que recebem como entrada uma propriedade de registro (por exemplo, um valor de um ou mais campos) e os encontra com essa propriedade rapidamente. Um ndice permite localizar um registro sem ter que examinar mais que uma pequena frao dos registros possveis. Assim sendo, podemos concluir que os campos cujos valores o ndice se baseia formam a chave de pesquisa. NOTA ndices so estruturas de dados auxiliares cujo nico propsito tornar mais rpido o acesso a registros baseado em certos campos, chamados campos de indexao.

17

NDICE

1.3.1. Tipos de ndices - ndices primrios - baseado na chave de ordenao; - ndices de agrupamentos (clustering) - baseado no campo de ordenao no-chave de um arquivo; - ndices secundrios - baseado em qualquer campo no ordenado de um arquivo; - ndices multinveis; - rvores B e B+; - Tabelas Hash; Um dos mais antigos esquemas de ndice utilizados em sistema de banco de dados chamado de arquivo indexado seqencialmente, que so projetados para aplicaes que requerem tanto o processamento seqencial de um arquivo inteiro quanto o acesso aleatrio a registros individuais. Estes ndices podem ser: - Densos: quando o nmero de entradas do ndice igual ao nmero de registros no arquivo de dados. Cada registro no arquivo de ndice contm o endereo de um registro no arquivo de dados. PonteirosFigura 4 - ndice denso sobre um arquivo de dados seqenciais

- Esparos: Usa-se menos espao de armazenamento que o ndice denso ao custo de um tempo um pouco maior para localizar um registro dada a sua chave. quando o nmero de entradas do ndice igual ao nmero de blocos do arquivo de dados. Apenas alguns registros de dados so representados no arquivo de ndices. Em geral, aponta para o 1 registro de bloco.

18

NDICE

Figura 5 - ndice esparso sobre um arquivo de dados seqenciais

Ponteiros

1.3.1.1 NDICE PRIMRIO o ndice associado a uma chave primria. Nada mais do que um arquivo ordenado, cujos registros so de tamanho fixo com dois tampos. O primeiro, do mesmo tipo do campo chave de ordenao (chave primria) e o segundo um ponteiro para um bloco do disco ou arquivo de dados. Utiliza ndice esparo.

19

NDICE

Figura 6 - ndice Primrio

1.3.1.2 NDICE DE AGRUPAMENTO (CLUSTERING) Se os registros de um arquivo estiverem ordenados fisicamente por um campo que no seja chave, ou seja, que no possui um valor distinto, esse campo chamado de campo clustering. Semelhante ao ndice primrio possui dois campos de ordenao, no entanto o primeiro campo do mesmo tipo do campo clustering e o segundo um ponteiro para o bloco. Neste sentido sempre h a reserva de um bloco inteiro para cada valor de campo clustering, melhorando operaes de incluso e excluso. Em simples palavras um ndice associado a um campo no chave. Utiliza ndice esparo.

20

NDICE

Figura 7 - ndice de Agrupamento

1.3.1.3 NDICE SECUNDRIO No ndice secundrio o campo de indexao um campo no ordenado do arquivo de dados. Pode haver muitos ndices secundrios para um mesmo arquivo. Dois casos: - O campo de indexao um campo chave (s vezes chamado de chave secundria). - O campo de indexao no chave.

21

NDICE

possvel que mais de um registro tenha o valor da chave. Isso ocorre, por exemplo, quando a chave de pesquisa no a chave primria; Um ndice secundrio um ndice denso, normalmente com duplicatas. O ndice consiste em pares de chaves-ponteiro. Neste caso, sempre que so permitidas chaves de pesquisas duplicadas.

Figura 8 - ndice Secundrio

Outra possibilidade para ndices secundrios a entrada de ndice para um bloco de dados onde a menor chave de pesquisa nova, isto , a chave no apareceu em um bloco anterior; Se no h chave de pesquisa nova no bloco, ento sua entrada de ndice contm a nica chave de pesquisa encontrada nesse bloco; Pode-se encontrar os registros de uma chave de pesquisa K examinando-se o ndice para a primeira entrada cuja chave : - Igual a K - Menor que K, mas a prxima chave maior que K Segue-se o ponteiro da entrada. Se for encontrado pelo menos um registro com a chave de pesquisa K, ento a busca continua em blocos adicionais, at encontrar todos os registros com a chave de pesquisa K.

Figura 9 - ndice Secundrio

22

NDICE

Usa o ndice esparso indicando a nova chave de pesquisa mais baixa em cada bloco. ORGANIZANDO AS IDIAS: Campos ordenados ndice primrio ndice de agrupamento Campos no ordenados ndice Secundrio (chave) ndice Secundrio (no chave)

Campo chave Campo no chave

Tipo de ndice Primrio Agrupamento Secundrio (chave) Secundrio (no chave)

Denso ou Esparo N de blocos no arq. de dados Esparo N de valores distintos do campo de Esparo indexao N de reg. no arq. de dados Denso N de reg. no arq. de dados Denso N de valores distintos de indexao Esparo Nmero de Entradas

1.3.1.4 NDICES MULTINVEIS Uma pesquisa binria aplicada ao ndice para localizar ponteiros para um bloco do disco ou para um registro no arquivo que possui um valor de campo ndice especfico. Se o arquivo de ndices se torna muito grande para ser armazenado em bloco de disco, interessante index-lo em mais de um nvel.A vantagem que um ndice pequeno pode ser mantido em memria e o tempo de busca mais baixo; por outro lado, muitos nveis de ndices podem aumentar a complexidade do sistema, sendo mais recomendado a utilizao de outra estrutura como a que veremos mais a frente: rvore-B.

Figura 10 - ndices Multinveis

23

NDICE

NOTA Um ndice multinvel um ndice de ndice. - Primeiro nvel: arquivo ordenado pela chave de indexao, valores distintos, entradas de tamanho fixo. - Demais nveis: ndice primrio sobre o ndice do nvel anterior e assim sucessivamente at que no ltimo nvel o ndice ocupe apenas um bloco. - Nmero de acessos a bloco: um a cada nvel de ndice, mais um ao bloco do arquivo de dados.

Figura 11 - ndice Primrio de dois Nveis

A figura acima exemplifica um ndice primrio de dois nveis que se parece com a organizao ISAM (Indexed Sequential Acess Method - Mtodo de Acesso Seqencial Indexado). O problema dos ndices multinveis so arquivos fisicamente ordenados, portanto, ineficientes na insero e remoo. Para solucionar este problema podem-se adotar as seguintes solues: - Deixar algum espao em cada um dos blocos para insero de novas entradas. - Estruturas de dados: rvores B e suas variaes. a) Gerenciamento de ndices nas Modificaes de Dados Os arquivos seqenciais apresentam problemas com o tempo, visto que um bloco no mais suficiente para armazenar o contedo que era antes armazenado. Para contornar esse problema possvel adotar as seguintes solues:24

NDICE

- Criar blocos de estouro (overflow). Blocos de estouro no tm entradas em um ndice esparso; - Inserir novos blocos na ordem seqencial; - Se no houver lugar para inserir informao em um bloco, alguma informao poder ser transferida para outro bloco. De igual forma, blocos vazios podero ser combinados. 1.3.1.5 RVORE B rvores B e B+ so casos especiais de estruturas de dados bastantes conhecidas do tipo rvore. Uma rvore formada por ns, e cada n na rvore, exceto um n especial denominado raiz, possui um n pai e diversos ns filhos. Um n que no possui filhos chamado de n folha. Um modo de implementar uma rvore possuir tantos ponteiros em cada n quantos forem os ns filhos daquele n. E para percorrer todos os ns, da rvore e da sub-rvore necessrio fazer uso da recursividade.

Figura 12 - rvore de Grau 3

a) rvore de Pesquisa Uma rvore de pesquisa um tipo especial de rvore que se utilizada para guiar a pesquisa por um registro, dado o valor de um dos campos do registro. A rvore de busca projetada com dois objetivos: - Manter a rvore balanceada; e - Evitar o desperdcio de espao dentro de um n, custa de maior complexidade nos algoritmos de insero e remoo. Regras: - Os ns da sub-rvore esquerda contm valores menores que a raiz; - Os ns da sub-rvore direita contm valores maiores ou iguais a raiz; - As sub-rvores so rvores binrias de pesquisa.

25

NDICE

Figura 13 - Distribuio nos ns

b) rvore B Em computao, rvore B ou B-Tree uma estrutura de dados rvores que so muito utilizadas em banco de dados e sistema de arquivos. Para inserir ou remover variveis de um n, o n no poder ultrapassar sua ordem e nem ser menor que sua ordem dividida por dois. rvores B no precisam ser rebalanceadas como so freqentemente as rvores de busca binria com rvore AVL. rvores B tm vantagens substanciais em relao a outros tipos de implementaes quanto ao tempo de acesso e pesquisa aos ns. Uma rvore B de ordem "m" (mximo de filhos para cada n) uma rvore que atende as seguintes propriedades: - Cada n tem no mximo "m" filhos - Cada n (exceto a raiz e as folhas) tem pelo menos "m/2" filhos - A raiz tem pelo menos dois filhos se a mesma no for uma folha - Todas as folhas aparecem no mesmo nvel e no carregam informao - Um n no-folha com "k" filhos deve ter k-1 chaves As vantagens da rvore B so: - Melhor desempenho por ter um nmero menor de ns do que uma rvore binria. Por exemplo: Menos ns significa menos altura que resulta em menos acessos ao disco. - Por garantir poucos ponteiros entre os ns, h uma economia de espao. - Maior rapidez em buscas pela utilizao de chaves primrias. - Sua estrutura dinmica, ajustando automaticamente o balanceamento da rvore, a cada incluso/excluso. - Permite um tempo de acesso de dados menor, em uma busca aleatria, por causa de suas ramificaes. Algoritmos Insero 1. Primeiro pesquise a chave, para ter a certeza de que esta no existe na rvore. 2. Busque a posio onde esta ser inserida. Teste para ver se o n est cheio. 3. Se n estiver vazio, insira o valor dentro dele, seno execute uma subdiviso do n da seguinte forma: 3.1. Verifique se o n-pai est vazio, se sim execute 3.1.1. Passe o elemento do meio do n para seu pai. 3.1.2. Divida o n em dois ns iguais.

26

NDICE

3.2. Se o n pai estiver cheio, repita as duas linhas acima recursivamente. (Caso todos os ns-pai estiverem cheios, inclusive a raiz, deve ser criada uma nova raiz aumentando assim a altura da rvore. 3.3. Somente aps satisfazer todas divises necessrias, insira nova chave. Excluso 1. Primeiro pesquise a chave para ter a certeza de que esta existe na rvore. 2. Se existir, verifique se est em folha, e faa a excluso. 3. Se existir e no estiver em folha, substitua esta chave pela menor chave do filho a direita. 3.1. Se o nmero de chave no n, for maior do que (Ordem/2 - 1), ento termine a rotina. 3.2. Seno redistribua as chaves entre os ns vizinhos. Busca 1. Indique a chave que ser procurada. 2. Pesquise desde a raiz at encontr-la, e ento retorne o n e a posio desta. 3. Se a chave no for encontrada, continue o lao at encontrar um nil das folhas. Exemplo:

Figura 14 - Operaes na rvore

1.3.1.5 HASHING So particularmente adequados para pesquisas por igualdade. A idia principal usar uma funo de hashing. Esta funo mapeia um valor da chave de pesquisa (atributo de uma tabela) em um registro ou balde (bucket) de registros.

27

NDICE

Figura 15 - Hashing

A balde uma unidade de armazenamento que contm um ou mais registros (um balde tipicamente um bloco do disco). Numa organizao de ficheiro em hashing, obtemos o balde de um registro a partir do valor da sua chave de pesquisa. Mais concretamente, o balde obtido atravs do clculo da funo de hash para aquela chave de pesquisa. A funo de hash h uma funo do conjunto de todos os valores da chave K para o conjunto dos endereos de todos os baldes B. A funo de hash usada para localizar registros para as operaes de acesso, insero, bem como eliminao. Observe os exemplos de busca, insero e excluso. A desvantagem do hashing esttico pode conduzir a longas cadeias de transbordo. Busca Insero

Excluso

28

NDICE

Longas cadeias de transbordo podem desenvolver-se e degradar o desempenho. a) Hashing Esttico O hashing esttico escolhe a funo hash com base no tamanho do arquivo atual, ou com base no tamanho antecipado do arquivo com base em um ponto no futuro. Esta tcnica reorganiza periodicamente a estrutura de hash. b) Hashing Dinmico Diferente do hashing esttico a funo hash modificada dinamicamente para acomodar o crescimento ou encolhimento do banco de dados, usando o hashing extensvel. c) Hashing Extensvel Divide e une os buckets enquanto o banco de dados cresce e encurta. A Eficincia do espao mantida e a funo hash gera valores por intervalos relativamente grandes. Outra caracterstica que os buckets so criados por demanda.

29

DATA MANIPULATION LANGUAGE - DML

3ParteDATA MANIPULATION LANGUAGE - DML

U

ma vez criada a estrutura fsica do banco de dados necessrio popular as tabelas. Para isso utilizam-se os comandos da DML (Data Manipulation Language). Nesta seara encontramos comandos como Insert, Update, Delete, Commit e Rollback.

DML(Linguagem de Manipulao de Dados): Permite a incluso, remoo, seleo ou atualizao de dados armazenados no banco de dados; Controle de acesso, permitindo proteo dos dados de manipulaes no autorizadas;

O ndice um arquivo auxiliar associado a uma Tabela. Sua funo acelerar o tempo de acesso s linhas de uma Tabela, cria ponteiros para os dados armazenados em colunas especificas. O Banco de dados usa o ndice de maneira semelhante ao ndice remissivo de um livro, verifica um determinado assunto no ndice e depois localiza a sua posio em uma determinada pgina.

3.1 INSERTComando responsvel por adicionar um ou mais registros na tabela de Banco de Dados. Os campos que forem omitidos recebem valores NULOS (NULL). Sintaxe: INSERT [TRANSACTION transaction] INTO [(col [, col ])] {VALUES ( [, ]) | }; = tablename | viewname = {:variable | | | | udf ([ [, ]]) | NULL | USER | RDB$DB_KEY | ? } Exemplo: INSERT INTO clientes (id, nome) VALUES (1, Nome do Cliente);

30


NOTE BEM: A lista de colunas opcional no comando Insert; Caso no seja definida, assume-se a seqncia da criao da tabela; Para maior clareza do cdigo e evitar erros em caso de reestruturao da tabela recomenda-se utilizar a lista de colunas. Valores alfanumricos devem ser especificados entre aspas simples exemplo; Valores numricos no devem conter esse separador. Valores do tipo data devem ser tratados de acordo com as especificaes do SGBD; Ao incluir uma linha no Banco de Dados, o gerenciador checar as restries de integridade (Constraints). Assim chaves primrias, estrangeiras, domnios, etc so checadas no momento da incluso. Caso alguma restrio for violada, a linha no ser includa e uma mensagem ser emitida pelo SGBD. As colunas que no tiverem valores atribudos tero contedo NULL ou default, caso no haja a restrio NOT NULL. Para inserir um valor nulo deve informar NULL no lugar correspondente a coluna. 3.1.1 Inserindo Vrias Linhas Para incluir diversas linhas em uma tabela, utiliza-se o comando INSERT em conjunto com o comando SELECT. Isso ir copiar as linhas de uma tabela para outra; A lista de colunas do SELECT deve corresponder a totalidade de colunas da tabela. Exemplo: INSERT INTO vendas_old CURRENTE DATE;

SELECT

*

FROM

vendas

WHERE

data_venda

=

Outra forma usando o comando INSERT por vrias vezes consecutivas acompanhado do finalizador ponto-e-virgula . Ou tambm aps o comando VALUES colocando os dados entre parnteses separados por vrgula. Exemplo: INSERT INTO vendas_old (prod_id, prov_valor) VALUES (1, 2.00), (2, 3.00), (3, 5.00), (4, 10.00); 3.1.2 Laboratrio INSERT INTO autor (aut_id, aut_nome) VALUES (1, Renato Russo); INSERT INTO autor (aut_id, aut_nome) VALUES (2, Ton Jobim); INSERT INTO musica (mus_id, mus_nome, mus_duracao) VALUES (1, sera, 00:02:28); INSERT INTO musica (mus_id, mus_nome, mus_duracao) VALUES (2, Ainda Cedo, 00:03:55); INSERT INTO gravadora (grav_id, grav_nome, grav_tel, grav_contato) VALUES (1, EMI, 1122334455, Jos Vitor); INSERT INTO cd(cd_id, cd_nome, cd_preco, dt_lancamento, grav_id) VALUES (1, Mais do Mesmo, 20.50, 21.12.1998, 1);

31


3.1.3 Exerccios a) Popule as tabelas do banco de dados conforme representado abaixo:

ATENO: Observe a sequncia de insero dos dados.

3.2 UPDATEComando responsvel alterao de ou um mais registros na tabela de Banco de Dados. Sintaxe: UPDATE [TRANSACTION transaction] {table | view} SET col = [, col = ] [WHERE | WHERE CURRENT OF cursor]; Exemplo: UPDATE CLIENTE SET DATA_INCLUSAO = CURRENT DATE;

32


3.2.1 Where Comando responsvel por especificar qual registro da tabela ser alterado quando usado em conjunto com a clusula UPDATE. NOTE BEM: O contedo a ser atualizado deve respeitar o tipo de dado da coluna. A condio expressa no comando servir para definir quais linhas devem ser atualizadas. Caso a clusula WHERE contenha a chave primria somente uma linha da ser atualizada. Todas as restries de integridade (Constraints) sero avaliadas pelo SGBD. 3.2.2 Laboratrio Se quisermos alterar o preo de um CD, podemos utilizar o seguinte comando filtrando na clusula WHERE a chave primria da tabela CD: Exemplo: UPDATE cd SET cd_preco = 21,00 WHERE cd_id = 1; Se o aumento for para todos os cds de uma determinada gravadora, utilizaremos como filtro da clusula WHERE grav_id: Exemplo: UPDATE cd SET cd_preco = 22,00 WHERE grav_id = 1; Imagine que ocorra um aumento de preo generalizado para todos os CDs em 5%. Assim multiplica-se o prprio preo do CD por 1,05. Exemplo: UPDATE cd SET cd_preco = cd_preco * 1,05; Outros Exemplos: UPDATE cd SET cd_preco = 15 WHERE cd_id = 1; UPDATE autor SET aut_nome = B. Manilow WHERE aut_id = 2; UPDATE cd SET cd_preco = cd_preco * 1,10 WHERE grav_id = 3; UPDATE gravadora SET grav_nome = E.M.I, grav_tel = 2144332211 WHERE grav_id = 1;

3.3 DELETEComando responsvel pela excluso de ou um mais registros na tabela de Banco de Dados.

33


Sintaxe: DELETE [TRANSACTION transaciona] FROM table {[WHERE ] | WHERE CURRENT OF cursor}; Exemplo: DELETE FROM VENDAS WHERE DATA_VENDA 10 cd_preco 12; NOTE BEM: Da mesma forma que podemos comparar uma coluna com um valor, podemos comparar com outra coluna. Sempre quando fazemos esse tipo de comparao, devemos obedecer ao tipo de dado que estamos comparando.

4.1.2.2 OPERADORES LGICOS Muitas vezes, apenas uma condio no suficiente para determinarmos o critrio de busca. Sempre que isso ocorrer, podemos utilizar operadores lgicos. OPERADOR AND OR NOT ou ! AND Indica que as duas condies devem ser verdadeiras para que seja mostrada a linha. Exemplo: SELECT cd_nome, cd_preco, grav_id FROM cd WHERE cd_preco > 10 AND grav_id = 2; OR Utilizamos o operador OR sempre que quisermos que o resultado final seja verdadeiro. Exemplo: SELECT cd_nome, cd_preco, grav_id FROM cd WHERE cd_preco > 11 OR grav_id = 2 NOTE BEM No h limitao no uso e na combinao de condies usando OR e AND. conveniente utilizar parnteses para determinar o que se quer comparar. SIGNIFICADO e ou no/negao EXEMPLO Condio-1 AND Condio-2 Condio-1 OR Condio-2 NOT Condio

37

PESQUISA BSICA EM T ABELAS

Exemplo: SELECT cd_nome, grav_id, cd_preco FROM cd WHERE (grav_id = 2 OR grav_id = 3) AND (cd_preco >= 17.50) NOT ou ! utilizado para inverter o resultado de uma expresso lgica, negando o resultado da condio. Caso a condio seja verdadeira, ser retornado falso e vice-versa. Exemplo: SELECT cd_nome, cd_preco FROM cd WHERE NOT (cd_preco > 15);

4.1.2.3 OPERADORES ESPECIAIS Existem alguns operadores que so utilizados para determinar melhor as linhas que queremos filtrar. So eles: IS NULL, IS NOT NULL, BETWEEN, LIKE e IN . IS NULL Sabemos que nem todas as colunas tm valores inicializados. Logo esse comando utilizado para saber os campos que no foram inicializados: Exemplo: SELECT * FROM GRAVADORA WHERE grav_tel IS NULL IS NOT NULL Compara a negao do comando anterior. Somente aqueles que tiverem contedo sero mostrados: Exemplo: SELECT * FROM GRAVADORA WHERE grav_tel IS NOT NULL BETWEEN Esse operador serve para determinar um intervalo de busca. Quando desejarmos um intervalo entre nmeros, datas, etc utilizaremos o BETWEEN para simplificar a forma de escrevermos o comando. Normalmente utilizado em conjunto com o AND. Exemplo: SELECT cd_nome, cd_dt_lancamento FROM CD WHERE cd.cd_dt_lancamento BETWEEN '01.01.1979' AND '31.12.2000';

38


LIKE Com esse operador podemos comparar cadeias de caracteres utilizando padres de comparao para um ou mais caracteres. O caractere percentual (%) substitui zero, um ou mais caracteres e sublinha (_) substitui um caractere. EXPRESSO LIKE A% LIKE %A LIKE %A% LIKE A_ LIKE _A LIKE _A_ LIKE %A_ LIKE _A% APLICAO Todas a palavras que iniciem com a letra A Todas a palavras que terminem com a letra A Todas a palavras que tenham a letra A em qualquer posio String de dois caracteres que tenha a primeira letra A String de dois caracteres que tenha o ltimo caractere letra A String de trs caracteres cuja segunda letra seja A Todas as palavras que tenham a letra A na penltima posio Todas as palavras que tenha a letra A na segunda posio

Exemplos: SELECT * FROM autor WHERE aut_nome LIKE 'R%'; SELECT * FROM gravadora WHERE grav_nome LIKE '_o%'; Um problema que pode surgir quando queremos fazer buscas utilizando os caracteres de substituio t-los na cadeia de caracteres que est sendo pesquisada. Neste caso devemos usar um caractere especial denominado ESCAPE. Exemplo: SELECT * FROM cd WHERE cd_nome LIKE '%\_%'ESCAPE '\'; IN Permite comparar o valor de uma coluna com um conjunto de valores. Utilizamos para substituir uma srie de comparaes seguidas da clusula OR. Exemplo: SELECT * FROM I WHERE aut_id IN (1, 3) ; Sua maior utilizao so em subquerys (ser visto posteriormente).

4.2. EXERCCIOSa) Liste todos os campos e linhas da tabela GRAVADORA;39


b) Liste todas as linhas dos campos CD_ID, CD_NOME, CD_PRECO da tabela CD; c) Liste todas as linhas dos campos AUT_ID, AUT_NOME da tabela AUTOR em ordem alfabtica; d) Repita o comando anterior em ordem alfabtica decrescente; e) Liste todos os CDs da gravadora 3; f) Liste as colunas CD_NOME, CD_PRECO dos CDs cujos preos de venda sejam inferiores a 20,00 e sejam da GRAVADORA 3; g) Liste as colunas da tabela gravadora cujo GRAV_CONTATO seja nulo; h) Repita o comando anterior desta vez listando GRAV_CONTADO no nulo; i) j) Liste os CDs cujos CD_PRECO esteja entre 15,00 e 30,00; Liste todos os CDs cuja CD_DT_LANCAMENTO seja posterior ao ano 01/01/2000;

k) Liste as MUSICAS cujo nome comece com A da tabela MUSICA; l) Liste os CDs cuja segunda letra do CD_NOME seja a letra E;

m) Liste os CDs que possuam a letra O em qualquer posio do CD_NOME; n) Liste os CDs que possuam CD_PRECO inferior a 30,00 em ordem decrescente de CD_DT_LANCAMENTO; o) Liste as msicas cuja MUS_ID seja 1, 3, 5;

40

CLCULOS E FUNES USUAIS

5ParteCLCULOS E FUNES USUAIS

U

m recurso bastante til e importante de SQL a possibilidade de se realizar clculos e totalizaes de valores unitrios. Podemos realizar clculos quando realizamos buscas no banco de dados simplesmente aplicando um dos operadores aritmticos a coluna.

Exemplo: Um aumento de 5% em todos os CDs: SELECT cd_id, cd_nome, cd_preco, preco_venda * 1.05 FROM cd; Em alguns Sistemas Gerenciadores de Banco de Dados existem outras funes alm das aritmticas, como a funo POWER do Oracle e Sysbase que permite calcular exponenciao.

5.1 OPERADORESEstes so os operadores utilizados no SQL: OPERADOR + * / SIGNIFICADO Soma Subtrao Multiplicao Diviso

A precedncia igual a da matemtica, ou seja, a multiplicao e a diviso tm prioridade sobre a soma e subtrao. Para alterar a prioridade deve-se utilizar parnteses (Operao). Exemplo: 15 / 5 * 3, Resultado = 9 15 / (5 * 3), Resultado = 1 SELECT cd_id, cd_nome, cd_preco - (cd_preco * 0.10) FROM cd

41


5.1.1. CHARACTER_LENGTH / LENGTH Retorna o nmero de caracteres contidos em uma cadeia de caracteres. Esta funo no existe em todos os SGBD, como acontece no Firebird. Exemplo: SELECT CHARACTER_LENGTH (Renato Russo); SELECT CHARACTER_LENGTH (cd_nome) FROM cd WHERE cd_id = 10; 5.1.2. Alfanumricos Em SQL possvel concatenar alfanumricos e existem algumas funes para manipul-los. Para concatenar caracteres, utiliza-se dois pipes ( || ). Exemplo: SELECT grav_id || ' - ' || grav_contato FROM gravadora;

5.1.3. UPPER e LOWER Ao realizar buscas alfanumricas no banco de dados notamos que os parmetros so case sensitives. Assim sendo, possvel usar os comandos UPPER E LOWER para transformar a cadeia de caracteres para maisculo e/ou minsculo. Exemplo: SELECT * FROM autor WHERE UPPER(aut_nome) = RENATO RUSSO; SELECT UPPER(aut_nome) FROM autor; 5.1.4. SUBSTRING Essa funo requisito para SQL-92 ao nvel intermedirio. Retorna uma parte da cadeia de caracteres. Exemplo: SELECT SUBSTRING(aut_nome from 1 FOR 3), aut_nome FROM autor; 5.1.5. Manipulao de Datas Em colunas do tipo data podemos realizar uma srie de clculos e operaes cronolgicas, como calcular o nmero de dias entre duas datas, somar, subtrair dias, meses etc. O padro especifica 04 (quatro) tipos de dados relacionados data e hora:42


Tipo Date Time Timestamp Interval

Descrio Apenas data Apenas Hora Data/Hora Intervalo entre dois tipos

O padro SQL definiu algumas funes acrescentando CURRENT_: - CURRENT_DATE - CURRENT_TIME - CURRENT_TIMESTAMP Exemplos: SELECT * FROM cd WHERE cd_dt_lancamento = CURRENT_DATE; Para saber a quantidade de dias que j se passaram depois do lanamento do CD usaramos o comando: SELECT CURRENT_DATE, cd_dt_lancamento, (CURRENT_DATE cd_dt_lancamento) FROM cd Adicionar dias em uma data: SELECT cd_dt_lancamento + 7 FROM cd; SELECT cd_dt_lancamento + INTERVAL 7 DAY FROM cd; 5.1.6. EXTRACT Essa funo extrai e retorna um valor de um campo do tipo data. possvel extrair apenas o dia, o ms, o ano, a hora, etc. Exemplo: SELECT cd_dt_lancamento, EXTRACT(MONTH FROM cd_dt_lancamento) FROM cd; SELECT cd_dt_lancamento, EXTRACT(DAY FROM cd_dt_lancamento) FROM cd; Caso seja necessrio realizar a concatenao de dados do tipo DATE necessrio utilizar o comando CAST para converter o tipo do dado para um tipo STRING.

SELECT cd_dt_lancamento, CAST(EXTRACT(MONTH FROM43


cd_dt_lancamento) AS VARCHAR) ||'/'|| CAST(EXTRACT(DAY FROM cd_dt_lancamento) AS VARCHAR) FROM cd;

5.2. EXERCCIOSa. Monte uma pesquisa que mostre a data atual. b. Escreva uma busca que mostre CD_NOME, CD_PRECO e CD_PRECO com 20% de aumento. c. Escreva uma busca igual anterior, porm acrescente uma coluna mostrando a diferena entre o CD_PRECO e CD_PRECO com 20% de aumento. d. Escreva uma busca que mostre GRAV_NOME, GRAV_CONTATO em uma nica coluna separados por um hfen. e. Escreva uma busca que mostre todos os autores que tenham a letra A no nome. f. Escreva uma busca que mostre a primeira letra da MUS_NOME e a MUS_DURACAO da tabela msica. g. Escreva uma busca que mostre o CD_NOME e o nmero de dias entre a data atual e a data de lanamento. h. Escreva uma busca semelhante anterior que mostre uma coluna com 15 dias aps a CD_DT_LANCAMENTO. i. Escreva uma busca que retorne o ano de lanamento do CD_ID = 1, 2 e 3; j. Escreva uma consulta que retorne os 05 primeiro caracteres de MUS_NOME e outra coluna com MUS_NOME.

44

PESQUISA EM MLTIPLAS T ABELAS

6PartePESQUISA EM MLTIPLAS TABELAS

N

os captulos anteriores as pesquisas foram realizadas em apenas uma tabela. No entanto, observamos no modelo criado, assim como acontece sempre, que uma tabela est relacionada outra, sendo necessrio extrair informaes de duas ou mais tabelas relacionadas ao mesmo tempo. Esta tarefa recebe o nome de unio ou juno de tabelas. Isso se d por meio das chaves primrias e chaves estrangeiras, que so no modelo fsico as colunas que as tabelas tm em comum. Para os exemplos utilizados nesta aula fundamental fazer uso do Modelo Lgico para melhor visualizar os relacionamentos.

Figura 16 - Modelo Lgico de Dados

45


6.1. ALIASAo realizar pesquisa em diversas tabelas so referenciadas colunas pertencentes s tabelas da relao. Dessa forma, necessrio distinguir a qual tabela pertence dada coluna. Podemos fazer isso de duas formas: a) Colocando o nome da tabela na frente do nome da coluna. Sintaxe: SELECT tabela.coluna FROM tabela; b) Utilizando um alias ou apelido para a tabela e colocando-o na frente da coluna. Sintaxe: SELECT t.coluna FROM tabela t; ainda possvel usar alias em nome de colunas, fazendo uso da clusula AS; Sintaxe: SELECT coluna AS nome_desejado FROM tabela;

6.2. UNIO DE TABELASPara realizar a unio de tabelas basta acrescentar aps a clusula FROM do comando SELECT as tabelas que queremos unir utilizando aps a clusula WHERE a condio de unio entre elas (Chave Primria e Chave Estrangeira). Sintaxe: SELECT [tabela1.]coluna [, [tabela2.]coluna, ...] FROM tabela1, tabela2 [, ...] WHERE tabela1.PK = tabela2.FK Uma prtica recomendvel utilizar o mesmo nome nos campos de chave primria e chave estrangeira, pois dessa forma facilita a identificao dos campos ao realizar a unio das tabelas. possvel colocar diversas tabelas na clusula FROM. No devemos esquecer, porm, que necessrio especificar as chaves primrias e chaves estrangeiras de cada relacionamento. Em caso de dvidas, consulte o Modelo de Dados para estabelecer quais so as colunas comuns entre as tabelas. Da a importncia de se ter um bom projeto de Banco de Dados claro e preciso. A clusula WHERE deve conter todo canal de relacionamento entre as tabelas listadas na clusula FROM sob pena de ocorrer o Produto Cartesiano.

46


6.3. PRODUTO CARTESIANOOcorrer um produto cartesiano sempre que: - A condio de unio entre as tabelas for omitida (no houver clusula WHERE); - Condio de unio entre as tabelas for invlida (clusula WHERE incorreta); - Todas as linhas da primeira tabela estiverem unidas a todas as linhas da segunda tabela. - Nesta situao, as linhas da primeira tabela sero combinadas com as linhas da segunda, demonstrando um resultado na maior parte das vezes indesejado. - Esse procedimento muito utilizado para efetuar testes de performance no banco devido a grande quantidade dedados. Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM gravadora, cd Ao invs de se mostrar todos os dados, deseja-se visualizar os CDs e suas respectivas gravadoras. Emprega-se ento, a Unio entre tabelas. Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM gravadora, cd WHERE cd.grav_id = gravadora.grav_id Usando alias: SELECT c.cd_id, c.cd_nome, g.grav_nome FROM gravadora g, cd c WHERE c.grav_id = g.grav_id

6.4. UNIO REGULAR (INNER JOIN OU EQUI-JOIN)Denomina-se unio regular as unies que tm a clusula WHERE unindo chave primria e chave estrangeira como visto anteriormente. O padro determina uma sintaxe alternativa para esse comando. Quando a chave primria e a chave estrangeira tm o mesmo nome em ambas as tabelas possvel simplificar o comando usando o comando NATURAL JOIN. Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM cd NATURAL JOIN gravadora; Outras duas maneiras definidas no padro SQL determinar qual(is) coluna(s) utilizar na unio usando a clusula USING e caso o nome das colunas no sejam iguais, determinar quais so as colunas com a clusula ON. Ambas produzindo o mesmo efeito.47


Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM cd JOIN gravadora USING (grav_id); SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM cd JOIN gravadora ON gravadora.grav_id = cd.grav_id;

6.5. UNIO DE M AIS DE DUAS TABELASFreqentemente necessrio unir mais de duas tabelas para obter uma informao consistente. Suponha que queira saber o nome da msica, a faixa e o nome do CD em que est a msica. No modelo de dados observamos a necessidade de se consultar trs tabelas no Banco de Dados. Exemplo: SELECT c.cd_nome, f.fai_numero, m.mus_nome FROM cd c, faixa f, musica m WHERE c.cd_id = f.cd_id AND m.mus_id = f.mus_id O cdigo abaixo produziria o mesmo efeito: SELECT c.cd_nome, f.fai_numero, m.mus_nome FROM faixa f INNER JOIN cd c ON c.cd_id = f.cd_id INNER JOIN musica m ON m.mus_id = f.mus_id Observe que possvel realizar a unio de tantas tabelas quanto forem necessrias. SELECT a.aut_nome, g.grav_nome FROM autor a, cd c, gravadora g, faixa f, musica_autor ma, musica m WHERE a.aut_id = ma.aut_id AND m.mus_id = ma.mus_id AND m.mus_id = f.mus_id AND c.cd_id = f.mus_id AND g.grav_id = c.grav_id

6.6. UNIES EXTERNAS (OUTER-JOIN)Observa-se na unio entre tabelas que quando uma linha no satisfaz a condio de unio entre as tabelas, ela no ser mostrada no resultado da busca. Isto acontece porque o banco de dados, no podendo estabelecer a relao entre as colunas que esto sendo unidas na busca, coloca NULL onde o dado no existe.

48


Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM gravadora, cd WHERE cd.grav_id = gravadora.grav_id No resultado observamos que alguns dados de gravadora so omitidos. Define-se Unio Externa como aquela que inclui linhas no resultado da busca mesmo que no haja relao entre as duas tabelas. Para que os dados omitidos sejam mostrados utilizaremos o comando SELECT utilizando Unio Externa. 6.6.1. Unio Externa Esquerda (Left Outer-Join) Como o nome diz, a unio pela esquerda incluir todas as linhas da primeira tabela na expresso, ou seja a tabela da esquerda ser a tabela base para a unio. Observe esse comando e depois inverta a posio das tabelas GRAVADORA e CD. Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM gravadora LEFT OUTER JOIN cd ON (gravadora.grav_id = cd.grav_id) Neste caso, todas as gravadoras so mostradas independente de haver ou no dados relacionados na tabela CD. 6.6.2. Unio Externa Direita (Right Outer-join) Ao contrrio da anterior, a unio realizada pela direita, pois incluir todas as linhas da tabela da direita na expresso da unio, ou seja a tabela da direita ser a tabela base para a unio. Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM gravadora RIGHT OUTER JOIN cd ON (gravadora.grav_id = cd.grav_id) NOTA: Se a tabela da referncia (direita ou esquerda) for a que contm a chave estrangeira, ocorrer uma unio regular entre as tabelas. 6.6.3. Unio Externa Total (Full Outer-join) Realiza uma unio independente da coluna opcional estar direita ou esquerda. Neste caso, so geradas linhas que no existem em uma ou em ambas as tabelas.

49


Exemplo: SELECT cd.cd_id, cd.cd_nome, gravadora.grav_nome FROM gravadora FULL OUTER JOIN cd ON (gravadora.grav_id = cd.grav_id) Neste caso a consulta age como uma unio externa a direita ou a esquerda por no haver campos nulos na coluna de chave estrangeira de CD.

6.7. EXERCCIOSa. Faa uma busca que mostre todos os CDs e o nome de suas respectivas gravadoras. b. Faa uma pesquisa que mostre o produto cartesiano das tabelas MUSICA e CD. c. Realize uma consulta se utilizando da sintaxe JOIN que mostre todas MUSICAS e o nome de seus respectivos CDs. d. Crie uma pesquisa que retorne como resultado todas as gravadoras e seus respectivos CDs mesmo que estes no possuam CDs associados. e. Crie uma pesquisa que mostre o AUTOR e suas MUSICAS ordenadas por AUTOR. f. Acrescente a pesquisa anterior o nome do CD que foi gravado a msica. g. Crie uma pesquisa que mostre em quais gravadoras os autores tem realizado suas gravaes.

50

FUNES DE GRUPO E AGRUPAMENTO

7ParteFUNES DE GRUPO E AGRUPAMENTO

A

t agora trabalhamos com funes que tratavam apenas de uma linha. Contudo, em diversas situaes necessrio trabalhar com vrias linhas da consulta. Dessa forma, necessrio conhecer funes especiais denominadas funes de grupo e agrupamento.

7.1. FUNES DE GRUPOFunes de grupo operam conjuntos de linhas visando a fornecer um resultado para o grupo. Estes grupos podem ser constitudos desde toda a tabela at subgrupos da tabela. Existem diversas funes de grupo que so implementadas pelo padro SQL. As principais funes so: FUNO COUNT SUM AVG MIN MAX STDDEV VARIANCE 7.1.1. COUNT Diferente das outras funes de grupo, o COUNT retorna o nmero de linhas que atende a uma determinada condio. Podemos utiliz-lo com um asterisco entre parnteses, para indicar que queremos saber a quantidade total de linhas independente de haver linhas com colunas nulas ou no. Exemplos: SELECT COUNT(*) FROM gravadora; SELECT COUNT(grav_contato) FROM gravadora; Efeito semelhante pode ser conseguido com o seguinte comando: DESCRIO Retorna nmero de linhas afetadas pelo comando. Retorna o somatrio do valor das colunas especificadas Retorna a mdia aritmtica dos valores das colunas Retorna o menor valor da coluna de um grupo de linhas Retorna o maior valor da coluna de um grupo de linhas Retorna o desvio-padro da coluna Retorna a varincia da coluna

51


Exemplos: SELECT COUNT(grav_contato) FROM gravadora WHERE grav_contato IS NOT NULL; Diferente de: SELECT COUNT(*) FROM gravadora WHERE grav_contato IS NULL; Outra maneira utilizando o comando DISTINCT. Observe a diferena entre os dois comandos: Exemplos: SELECT COUNT(DISTINCT aut_id) FROM musica_autor; SELECT COUNT(aut_id) FROM musica_autor; 7.1.2. SUM Retorna o valor total de uma determinada coluna em um determinado grupo de linhas. Assim se quisermos saber o total do preo de venda dos CDs, utilizamos o comando: Exemplo: SELECT SUM(cd_preco) FROM cd; possvel realizar clculos baseados na somatria ou mesmo incluir outras colunas e operaes no comando. Exemplo: SELECT SUM(cd_preco) * 1.2 FROM cd; 7.1.3. AVG Extrai a mdia aritmtica de um determinado grupo de linhas. Para saber o preo mdio dos CDs da loja execute o seguinte comando: Exemplo: SELECT AVG(cd_preco) FROM cd;

52


7.1.4. MIN Retorna o menor valor de uma coluna em um grupo de linhas. Podemos utiliz-la em colunas do tipo data ou alfanumricas. Para saber o preo de venda do CD mais barato execute o seguinte comando: Exemplos: SELECT MIN(cd_preco) FROM cd; SELECT MIN(cd_dt_lancamento) FROM cd; SELECT MIN(cd_nome) FROM cd; 7.1.5. MAX Retorna o maior valor de uma coluna em um grupo de linhas. Podemos utiliz-la em colunas do tipo data ou alfanumricas. Para saber o preo de venda do CD mais caro execute o seguinte comando: Exemplos: SELECT MAX(cd_preco) FROM cd; SELECT MAX(cd_dt_lancamento) FROM cd; SELECT MAX(cd_nome) FROM cd; 7.1.6. STDDEV (No funciona em alguns bancos de dados.) Retorna o desvio padro de uma determinada coluna. Para saber o desvio padro dos preos de venda dos CDs da loja execute o seguinte comando: Exemplo: SELECT STDDEV(cd_preco) FROM cd; 7.1.7. VARIANCE (No funciona em alguns bancos de dados.) Retorna a varincia de uma determinada coluna.

53


Para saber a varincia do preo de venda dos CDs da loja execute o seguinte comando: Exemplo: SELECT VARIANCE(cd_preco) FROM cd;

7.2. AGRUPANDO RESULTADOS possvel agrupar valores com base em determinadas colunas. Desta forma, estaremos trabalhando um pequeno grupo de dados. Para isso usamos a clusula GROUP BY. 7.2.1. GROUP BY O comando GROUP BY deve vir antes da clusula ORDER BY e depois do WHERE (se houver necessidade de utiliz-lo). utilizado com uma funo de grupo (COUNT, SUM, AVG, MIN ou MAX). Para saber quantas msicas h em cada CD: Exemplo: SELECT cd_id, COUNT(*) FROM faixa GROUP BY cd_id; Para o preo mdio de venda de cada CD agrupado por gravadora: Exemplo: SELECT grav_id, AVG(cd_preco) FROM cd GROUP BY grav_id; possvel realizar mais de uma funo de grupo dentro de um mesmo SELECT. Neste exemplo, alm do preo de venda, temos a quantidade de CDs de cada gravadora: Exemplo: SELECT grav_id, AVG(cd_preco), COUNT(*) FROM cd GROUP BY grav_id;

7.3. AGRUPAMENTOS COM MAIS DE UMA TABELAPodemos unir mais de uma tabela com as regras j citadas.

54


Exemplo: SELECT c.grav_id, g.grav_nome, AVG(c.cd_preco) FROM cd c, gravadora g WHERE c. grav_id = g.grav_id GROUP BY c.grav_id, g.grav_nome NOTA necessrio colocar todas as colunas que fazem parte do comando SELECT na clusula GROUP BY. Caso se deseje ordenar o resultado, basta acrescentar o comando ORDER BY no final do comando aps o comando GROUP BY: Exemplo: SELECT c.grav_id, g.grav_nome, AVG(c.cd_preco) FROM cd c, gravadora g WHERE c. grav_id = g.grav_id GROUP BY c.grav_id, g.grav_nome ORDER BY g.grav_nome desc

7.4. RESTRINGINDO O RESULTADOEm algumas situaes os dados agrupados podem ser restringidos ou filtrados. At agora vimos que toda tabela era afetada pelo comando GROUP BY. Contudo, nem sempre isso desejvel. Para isso podemos utilizar a clusula WHERE em conjunto com GROUP BY ou utilizar a clusula HAVING. Retornar o total de autores agrupados por MUS_ID sendo este menor que 3: Exemplo: SELECT mus_id, COUNT(*) FROM musica_autor WHERE mus_id < 3 GROUP BY mus_id; ou SELECT mus_id, COUNT(*) FROM musica_autor GROUP BY mus_id HAVING mus_id < 3; NOTE BEM Usando o filtro da clusula WHERE as linhas so filtradas antes do agrupamento; Usando HAVING, as linhas so filtradas depois do agrupamento. A nica restrio que a clusula HAVING s pode usar as colunas que fazem parte do GROUP BY. Para o WHERE isso no acontece. Nunca use na clusula WHERE uma funo de grupo para filtrar os grupos. Dessa forma, o comando abaixo invlido:55


Exemplos: SELECT GRAV_ID, AVG(cd_preco) FROM cd WHERE AVG(cd_preco) > 12 GROUP BY grav_id A forma correta seria: SELECT grav_id, AVG(cd_preco) FROM cd GROUP BY grav_id HAVING AVG(cd_preco) > 12

7.5. EXERCCIOSa. Verifique o maior e menor valor entre os preos de venda dos CDs. b. Verifique a diferena entre o maior e menor valor dos preos de venda dos CDs. c. Verifique a quantidade de dias compreendidos entre a data mais atual e a data mais antiga das datas de lanamento dos CDs; d. Mostre atravs de uma consulta o tempo total do CD 1 (cd_id = 1); e. Mostre atravs de uma consulta a mdia de durao das msicas cujo nome comece com a letra A; f. Faa uma consulta que retorne a quantidade de CDs cadastrados. g. Faa uma consulta que retorne a o nome do CD e a mdia de durao das msicas em cada CD, desde que esta mdia no seja superior a 4. h. Faa uma consulta que retorne os autores que so autores de mais de uma msica.

56

SUBCONSULTAS

8ParteSUBCONSULTAS SUBQUERIES

N

as sesses anteriores exploramos largamente o comando SELECT utilizando-o em conjunto com uma gama de funes. Ainda neste contexto, possvel incluir subconsultas dentro das consultas principais.

8.1. SUBQUERYAs subqueries fazem parte do padro SQL-86 logo, todos os bancos de dados relacionais que utilizam SQL devem permitir essa utilizao. Sintaxe: SELECT colunas FROM tabela WHERE expresso operador (SELECT colunas FROM tabela WHERE ...) H trs tipos de subquery: a) Subquery de uma linha: o retorno do SELECT interno ser uma nica linha. b) Subquery de mltiplas linhas: o retorno do SELECT interno ser mais de uma linha. c) Subquery de mltiplas colunas: o retorno do SELECT interno ser conter mais de uma linha e coluna. 8.1.1. Subquery de uma linha Neste caso, primeiro realizada a busca em uma tabela e, com base no resultado, pesquisada a outra tabela. Exemplo: SELECT cd_nome, cd_preco FROM cd WHERE cd_preco > (SELECT AVG(cd_preco) FROM cd) Acima utilizada uma subquery para verificar a mdia de preo dos CDs e, com base nesse resultado, extrado o resultado da busca do primeiro SELECT.

57

SUBCONSULTAS

Exemplo: SELECT grav_id, cd_nome, cd_preco FROM cd c WHERE cd_preco > (SELECT AVG(cd_preco) FROM cd WHERE grav_id = c.grav_id) Acima so buscados apenas os CDs que tenham preo de venda superior a mdia da prpria gravadora. Muito cuidado ao usar essa construo, porque ele tende a consumir muito recurso do banco de dados, pois a cada linha do primeiro SELECT ser extrado a mdia no segundo SELECT. Assim necessrio saber qual a gravadora do primeiro SELECT, para poder calcular o segundo SELECT. NOTA - Coloque as subqueries entre parnteses. - Coloque a subquery direita do operador. - No use a clusula ORDER BY em uma subquery. Se for necessrio ordenar faa-o no SELECT principal. - Use operadores de grupo apenas em buscas que potencialmente retornem mais de uma linha. - Use operadores de linha apenas em buscas que retornem uma nica linha. Exemplo: SELECT grav_id, cd_nome, cd_preco FROM cd WHERE grav_id = (SELECT grav_id FROM cd WHERE cd_id = 2) AND cd_preco > (SELECT cd_preco FROM cd WHERE cd_id = 5);

8.1.1.1. SUBQUERY EM CLUSULA HAVING A utilizao semelhante a clusula WHERE. A subquery ser executada primeiro e o resultado da busca servir de base para filtrar as linhas do GROUP BY. Exemplos: SELECT grav_id, MIN(cd_preco) FROM cd GROUP BY grav_id HAVING MIN(cd_preco) > (SELECT cd_preco FROM cd WHERE cd_id = 2) SELECT grav_id, MAX(cd_preco) FROM cd c GROUP BY grav_id HAVING MAX(cd_preco) > (SELECT AVG(cd_preco) FROM cd WHERE grav_id = c.grav_id)

58

SUBCONSULTAS

8.1.1.2. COMANDO EXISTS Verifica o nmero de linhas retornadas pela subquery. Caso contenha uma ou mais linhas, ento o resultado ser mostrado. Exemplo: SELECT grav_id, grav_nome FROM gravadora WHERE EXISTS (SELECT * FROM cd WHERE cd.grav_id = gravadora.grav_id) Note que quando se est utilizando esse operador, no importa o que o comando SELECT interno ir buscar. Interessa apenas se ele retorna ou no linhas, Por esse motivo usado o * em vez de uma coluna em especial. 8.1.2. Subquery de mltiplas linhas Neste caso o SELECT interno retorna mais de uma linha. No possvel utilizar operadores simples como igualdade, diferena, maior ou menor. Utilizam-se operadores de grupo para realizar a comparao, como: - ANY - ALL - IN 8.1.2.1. IN Imagine que queremos saber quais CDs tm o preo igual ao menor preo de cada gravadora. Inicialmente devemos saber qual o menor preo de cada gravadora. SELECT MIN(cd_preco) FROM cd GROUP BY grav_id Para saber quais so os CDs escreveramos o seguinte comando: SELECT cd_id, cd_nome, cd_preco FROM cd WHERE cd_preco IN (19, 23, 11) Para fazer isso em um nico comando, escreveramos o comando da seguinte forma: SELECT cd_id, cd_nome, cd_preco FROM cd WHERE cd_preco IN (SELECT MIN(cd_preco) FROM cd GROUP BY grav_id)

59

SUBCONSULTAS

8.1.2.2. ANY (Compara com qualquer valor) Esse operador permite comparar operadores simples (=,,!=) com um grupo de linhas. Imagine que desejamos saber quais CDs tm preo inferior a qualquer outro da gravadora com cdigo 2, mas que no sejam da gravadora 2. Inicialmente preciso saber qual o preo de venda de cada CD que no seja da gravadora 2: SELECT cd_id, cd_nome, cd_preco FROM cd WHERE grav_id != 2 Depois precisamos saber quais os preos dos CDs da gravadora 2 com o comando da seguinte forma: SELECT cd_preco FROM cd WHERE grav_id = 2 Agora realizamos a pesquisa na tabela de CD para comparar os preos inferiores a esses: SELECT cd_id, cd_nome, cd_preco FROM cd WHERE cd_preco < ANY (SELECT cd_preco FROM cd WHERE grav_id = 2) AND grav_id != 2 8.1.2.3. ALL Esse operador usado em combinao com operadores simples () para que os valores retornados de todas as linhas do SELECT interno sejam comparados com o SELECT externo. Imagine que queiramos saber quais CDs tm o preo de venda menor que a mdia de preo de venda de todas as gravadoras. Inicialmente necessrio saber a mdia de preos por gravadora: SELECT AVG(cd_preco) FROM cd GROUP BY grav_id; Depois verificamos quais so os CDs com preo inferior a todos anteriores. SELECT cd_id, cd_nome, cd_preco FROM cd WHERE cd_preco < ALL (SELECT AVG(cd_preco) FROM cd GROUP BY grav_id)

60

SUBCONSULTAS

8.1.3. Subquery de mltiplas colunas A tcnica consiste em colocar as colunas unidas na clusula WHERE do SELECT externo e realizar a busca no SELECT interno dessas colunas unidas da mesma forma. Essa tcnica tende a ser muito lenta, mas pode ser feita em qualquer banco de dados. Temos vrios CDs indicados no cadastro e queremos conhecer os dados do CD dentre os menores CDs indicados de cada gravadora. Para isso temos que saber o menor CD indicado de cada gravadora. Utilizamos o || para unir as colunas. Exemplo: SELECT grav_id || cast(MIN(cd_indicado) as varchar) FROM cd GROUP BY grav_id Agora realizamos a busca dos dados que atendem essas caractersticas: SELECT cd_id, cd_nome, grav_id, cd_indicado FROM cd WHERE (grav_id || cast (cd_indicado as varchar)) IN (SELECT grav_id || cast(MIN(cd_indicado) as varchar) FROM cd GROUP BY grav_id) Outra tcnica consiste em colocar as colunas de busca entre parnteses no SELECT externo, entretanto no est disponvel em alguns bancos de dados. Exemplo: SELECT cd_id, cd_nome, grav_id, cd_indicado FROM cd WHERE (grav_id, cd_indicado) IN (SELECT grav_id, MIN(cd_indicado) FROM cd GROUP BY grav_id) NOTA: Quando um SELECT interno contiver valores nulos, no deve ser utilizado NOT IN como operador de comparao. Isso porque qualquer comparao com nulo retorna nulo.

8.1.4. Subquery na Clusula FROM Podemos utilizar uma subquery na clusula FROM de um comando SELECT. Essa estrutura no est disponvel para todos os bancos de dados, mas faz parte do padro SQL. Sua utilizao muito parecida com as vises (Views). Imagine que queremos extrair o nome do CD, seu preo e o preo mdio da gravadora, e o preo mdio da gravadora extrado por uma subquery na prpria clusula FROM:

61

SUBCONSULTAS

Exemplo: SELECT a.cd_nome, a.cd_preco, b.preco_medio FROM cd a, (SELECT grav_id, AVG(cd_preco) as preco_medio FROM cd GROUP BY grav_id) b WHERE a.grav_id = b.grav_id AND a.cd_preco > b.preco_medio Veja que foi substituda a segunda tabela por um comando SELECT e que a tabela recebeu o apelido de b.

8.2. EXERCCIOSa) Faa uma lista de CDs usando subquery que foram gravados pela gravadora 2. b) Faa uma lista que mostre os CDs que custam mais que a mdia de preo dos CDs gravados pela gravadora 1. c) Faa uma consulta usando a clusula HAVING que retorne o CD mais barato que a mdia de preo dos CDs. d) Faa uma lista que mostre os CDs cujas msicas tem durao maior que 00:02:00. e) Faa uma lista que mostre quais CDs tem preos superiores a qualquer CD da gravadora 1 excluindo os da prpria gravadora 1.

62

VISES/VIEW

9ParteVISES/VIEW

A

nteriormente aprendemos a criar consultas em bancos de dados com a linguagem SQL usando uma srie de funes que permitem a extrao dos dados de diversas formas. Nesta sesso veremos a utilizao de vises (Views), tambm chamadas de tabelas virtuais em sistemas de bancos de dados. As views so um modo especial de enxergar dados de uma ou vrias tabelas. um objeto de banco de dados criado a partir de um comando SELECT. importante saber que ela armazenada no dicionrio de dados e possui a mesma estrutura de uma tabela. As views permitem restringir o acesso aos dados, por exemplo, utilizando o comando SELECT podemos filtrar linhas e coluna que no devam ser mostradas a todos os usurios. As buscas complexas tornam-se simples, pois ao DBA possvel criar consultas complexas em forma de vises e ao usurio realizar as buscas necessrias nessa viso. Outro aspecto a independncia de dados: as vises podem ser criadas visando atender necessidades genricas e no apenas s relacionadas a pessoa ou programas. Podemos buscar dados em diversas tabelas. A eliminao de cdigos outra razo: normalmente os cdigos no devem ser mostrados aos usurios, pois so utilizados apenas para estabelecer relacionamento entre as tabelas. Dessa forma facilita a compreenso dos dados pelo usurio.

9.1. CLASSIFICAOAs vises podem ser classificadas de duas formas: a) Simples: - Os dados so extrados de uma nica tabela; - No contm funes. - No possuem dados agrupados. - Podem utilizar comandos DML para manipular os dados. b) Complexas: - Os dados so extrados de vrias tabelas. - Podem conter funes. - Podem conter dados agrupados. - No podem utilizar comandos DML para manipulao de dados, exceto no Oracle, pois possui recursos especficos para isso.

63

VISES/VIEW

9.2. CRIAO DA VISOPara criar uma viso, utilizamos o comando CREATE VIEW: Sintaxe: CREATE VIEW nome AS query/subquery Exemplo: CREATE VIEW vCD AS SELECT cd_id, cd_nome, cd_preco FROM cd; Como visto anteriormente esta uma viso simples. Como uma viso sempre baseada em um comando SELECT, podemos criar vises extremamente complexas. Exemplos: CREATE VIEW vPRECO_CD AS SELECT grav_id, cd_nome, cd_preco FROM cd WHERE grav_id = (SELECT grav_id FROM cd WHERE cd_preco > 10.00) CREATE VIEW vCD_FAIXA AS SELECT cd_id, item_faixa FROM item_cd WHERE mus_id IN (SELECT mus_id FROM musica WHERE UPPER(mus_nome) LIKE %A) CREATE VIEW VGRAV_MAX AS SELECT grav_id, cd_preco FROM cd a WHERE cd_preco > (SELECT AVG(cd_preco) FROM cd WHERE grav_id = a.grav_id)

9.3. BUSCANDO CONTEDO DE VISESUtilizamos o comando SELECT para buscar o contedo de vises exatamente como fazemos com qualquer tabela. Exemplo: SELECT * FROM vCD; SELECT * FROM vCD_FAIXA; Podemos, ainda, incluir qualquer outra clusula do comando SELECT, mesmo em pesquisa realizadas em vises.

64

VISES/VIEW

Exemplo: SELECT * FROM VPRECO_CD WHERE cd_preco > 8;

9.4. UTILIZAO DE COMANDOS DML EM VISESSomente em vises simples possvel utilizar comandos DDL. O padro SQL determina as condies em que uma viso pode ser atualizada: - Deve ser criada em uma nica tabela; - Deve conter apenas um SELECT; - Se foi criada com base em outra viso, a primeira viso dever ser passvel de atualizao; - O comando SELECT no pode conter colunas calculadas; - No deve utilizar GROUP BY; - No deve conter a clusula DISTINCT; - Pode conter uma subquery, desde que o SELECT interno tenha como base a mesma tabela do SELECT externo; - Incluses s podem ser feitas caso a viso contenha a chave primria da tabela base. - Excluses e alteraes em vises sem a chave primria da tabela base so permitidas, porm desaconselhveis, pois no se sabe ao certo o resultado que pode aparecer. Isso chamado de Materializao de Views.

9.5. EXCLUINDO UMA VISOPara excluir uma viso, utilizamos o comando DROP VIEW; Ao excluir uma viso no estamos excluindo os dados (linhas e colunas), visto que apenas uma tabela virtual. Sintaxe: DROP VIEW nome; Exemplo: DROP VIEW vCD;

9.6. EXERCCIOSa) Crie uma viso que mostre uma lista de CDs que foram gravados pela gravadora 2. b) Crie uma viso que mostre os CDs e suas respectivas gravadoras. c) Crie uma viso que mostre as msicas e seus respectivos autores. d) Construa uma viso que mostre as msicas sua durao e a que CD pertence. e) Construa uma viso que mostre os autores e em quais gravadoras possuem CD gravado.65

PROCEDIMENTOS ARMAZENADOS/STORED PROCEDURES

10PartePROCEDIMENTOS ARMAZENADOSm muitas situaes ser necessrio armazenar procedimentos escritos com a finalidade de se utilizar recursos como loop, estruturas de deciso, etc. Para isso, utiliza-se os procedimentos armazenados o qual definimos como um programa escrito em uma linguagem prpria que armazenado como parte do banco de dados. Em outras palavras Procedimento Armazenado ou Stored Procedure uma coleo de comandos em SQL disponveis em Banco de dados. Encapsula tarefas repetitivas, aceita parmetros de entrada e retorna um valor de status (para indicar aceitao ou falha na execuo). O procedimento armazenado pode reduzir o trfego na rede, melhorar a performance, criar mecanismos de segurana, etc. (Wikipdia, a enciclopdia livre, 2009).

E

10.1. VANTAGENS DO USO DE STORED PROCEDURES- Reduo de trfico de rede. - Aumenta a performance da aplicao, particularmente em uma WAN ou em uma conexo de baixa velocidade. - Utilizando stored procedure, elimina-se o processo de parsed, ou seja, da query ter que ser analisada gramaticalmente e submetido ao otimizador para formulao de um plano de execuo. - So analisadas, otimizadas e armazenadas em uma forma executvel no momento em que so adicionadas ao banco de dados. - possvel executar operaes muito mais complexas que uma simples query. - Pode ser usada por aplicaes distintas. - Facilita a manuteno, pois possvel alter-la sem ter que recompilar a aplicao. - Proporciona mais segurana ao banco de dados, acessando tabelas que o usurio no tem acesso.

10.2. QUANDO USAR STORED PROCEDURES- Sempre que a metodologia de desenvolvimento do software permitir. - No existem desvantagens em usar procedimentos armazenados, entretanto existem duas limitaes: - Deve ser possvel passar qualquer informao varivel para a stored procedure (parmetros). - Operaes complexas podem ser limitadas.

66


10.3. STORED PROCEDURES NO POSTGRESQLComo visto, Stored Procedures so programas desenvolvidos em determinada linguagem de script e armazenados no servidor, local onde sero processados. Tambm so conhecidos como funes, este o motivo pelo qual as Stored Procedures no PostgreSQL so referenciadas como Function. O PostgreSQL conta com trs formas diferentes de criar funes: a) Funes em Linguagem SQL: So funes que utilizam a sintaxe SQL e se caracterizam por no possurem estruturas de condio (if, else, case), estruturas de repetio (while, do while, for), no permitirem a criao de variveis e utilizam sempre algum dos seguintes comandos SQL: SELECT, INSERT, DELETE ou UPDATE. b) Funes de Linguagens Procedurais: Ao contrrio das funes SQL, aqui permitido o uso de estruturas de condio e repetio e o uso de variveis. As funes em linguagens procedurais caracterizam-se tambm por no possurem apenas uma possibilidade de linguagem, mas vrias. Normalmente a mais utilizada conhecida como PL/PgSQL, linguagem semelhante ao conhecido PL/SQL da Oracle. Existem outras linguagens como, por exemplo, o PL/Perl, PL/Python e PL/Tcl, que possuem sintaxe igual ou semelhante s linguagens que lhes deram origem. c) Funes em Linguagens Externas ou de Rotinas Complexas: So funes normalmente escritas em C++ que trazem consigo a vantagem de utilizarem uma linguagem com diversos recursos, na qual pode-se implementar algoritmos com grande complexidade. Tais funes so empacotadas e registradas no Sistema Gerenciador de Banco de Dados para seu uso futuro. Existem ainda outras linguagens como PL/Ruby, PL/sh e PL/Java, no entanto, estas so definidas por projetos independentes. Dentre elas destacam-se as funes criadas com PL/Java devido a sua crescente demanda entre usurios do PostgreSQL. Para criar uma funo utilizando SQL no PostgreSQL utiliza-se o comando CREATE FUNCTION: Sintaxe: CREATE [OR REPLACE] FUNCTION nome ([tipo_do_parametro1],[...]) RETURNS tipo_retornado AS ' Implementao_da_funo; ' LANGUAGE 'SQL'; - CREATE FUNCTION o comando que define a criao de uma funo, [OR REPLACE] informa que se acaso existir uma funo com este nome, a atual funo dever sobrescrever a antiga. - RETURNS tipo_retornado informa o tipo de dado que ser retornado ao trmino da funo. Tais tipos de retornos so os convencionais como o INTEGER, FLOAT, VARCHAR, etc. As funes em SQL tambm permitem o retorno de mltiplos valores e para isso informase como retorno SETOF. Na implementao haver as linhas de programao para a implementao da stored procedure. - LANGUAGE indica para a funo em qual linguagem ela est sendo implementada.

67


Na passagem de parmetros funo no se utiliza o nome nas variveis declaradas nos parnteses da assinatura da funo. Utiliza-se o tipo da varivel de parmetro separado por vrgula. Para acessar o valor dos parmetros, utiliza-se o $ mais o nmero da posio que ocupa nos parmetros, seguindo a ordem da esquerda para a direita. Exemplo: CREATE FUNCTION soma(INTEGER, INTEGER) RETURNS INTEGER AS ' SELECT $1 + $2; ' LANGUAGE 'SQL'; O fato de que as funes utilizando SQL sempre retornam valor faz com que seja sempre necessrio que a ltima linha de comando da funo utilize o comando SELECT. Exemplo: CREATE FUNCTION cubo(INTEGER) RETURNS FLOAT AS ' SELECT $1 ^ 3; ' LANGUAGE 'SQL'; Tambm possvel criar funes que fazem interao entre uma determinada consulta e parmetros utilizados na funo. Na funo abaixo se obtm o total de CDs gravados por determinada gravadora, passando como parmetro o id da gravadora (grav_id): Exemplos: CREATE FUNCTION cdGravados(INTEGER) RETURNS INTEGER AS ' SELECT COUNT(cd_id) FROM cd WHERE grav_id = $1; ' LANGUAGE 'SQL'; CREATE OR REPLACE FUNCTION cdGravados2(in INT, out f1 INT, out f2 TEXT, out f3 NUMERIC) AS $$ SELECT CAST(cd_id as integer), CAST(cd_nome AS text), CAST(cd_preco AS numeric) FROM cd WHERE GRAV_id = $1 $$ LANGUAGE SQL; NOTA: Dependendo da configurao escolhida na instalao do PostgreSQL ser necessrio ajustar o tipo do retorno fazendo uso da funo CAST. Exemplo: CAST(COUNT(cd_id) as INTEGER) Para usar a funo utiliza-se a clusula SELECT.

68


Exemplo: SELECT cdGravados(4); Como mencionado, tambm possvel retornar vrias linhas de uma consulta em uma funo. Para isso, utilizamos o tipo de retorno SETOF. No prximo exemplo criada uma funo em que retorna todos os CDs mais caros que determinado valor passado por parmetro. Exemplo: CREATE OR REPLACE FUNCTION precoMaior(NUMERIC(10,2)) RETURNS SETOF cd AS' SELECT * FROM cd WHERE cd_preco > $1; ' LANGUAGE 'SQL'; Quando as funes possuem o seu retorno referenciado em uma tabela ou uma View, ou seja, quando a funo retorna um resultset, devemos utilizar a funo da seguinte maneira: Exemplo: SELECT * FROM precoMaior(5.2); A excluso de uma funo se faz atravs do comando DROP, como visto na DDL. Sintaxe: DROP FUNCTION nome_da_funcao(); Quando a funo, na sua assinatura contiver parmetros no ser possvel sua excluso atravs do comando DROP FUNCTION nome_da_funcao(), ou seja, para excluir uma funo necessrio passar toda a sua assinatura. Exemplo: DROP FUNCTION precoMaior(NUMERIC(10,2)); Ainda existe o fato que no momento da excluso poder excluir a funo passando mais um parmetro, como no exemplo a seguir: Exemplo: DROP FUNCTION cd_grav (psql INTEGER) RESTRICT; DROP FUNCTION cd_grav (psql INTEGER) CASCADE; Passando o RESTRICT como parmetro, a excluso da funo ser recusada caso existam dependncias de objetos em torno da funo (como por exemplo, Triggers e operadores). Com o CASCADE esses objetos sero excludos juntamente com a funo.

69


Nota: Se a funo for removida e recriada, a nova funo no mais a mesma entidade que era antes. Ficaro invlidas as regras, vises, gatilhos, etc. existentes que fazem referncia antiga funo. Use o comando CREATE OR REPLACE FUNCTION para mudar a definio de uma funo, sem invalidar os objetos que fazem referncia funo. 10.4.1. PL/pgSQL A PL/pgSQL uma linguagem estrutural estendida da SQL que tem por objetivo auxiliar as tarefas de programao no PostgreSQL. Ela incorpora SQL caractersticas procedurais, como os benefcios e facilidades de controle de fluxo de programas que as melhores linguagens possuem como loops estruturados (for, while) e controle de deciso (if, then, else). Dessa forma, programar em PL/pgSQL significa ter a disposio um ambiente procedural totalmente desenvolvido para aplicaes de bancos de dados, beneficiando-se do controle transacional inerente das aplicaes deste tipo. 10.4.2. Elementos da Linguagem A criao de Stored Procedures inclui todas as construes de uma linguagem de programao estruturada, como: - Comentrios: -- Este um comentrio - Bloco de comandos (BEGIN/END): possvel usar construes como IF-THEN-ELSE e loops WHILE que somente podem conter um comando. - Comandos de atribuio: var1 := var2 * var3 - Operador de concatenao: || (duas barras verticais) Alm desses elementos possvel usar as expresses condicionais: - BETWEEN - LIKE IN - EXISTS - ANY - ALL - IS NULL - IS NOT NULL 10.4.3. Estrutura da Linguagem A PL/pgSQL estruturada em blocos, assim, todo o texto de definio de uma funo precisa estar em um bloco. Sintaxe: [] [DECLARE declaraes] BEGIN contedo 1; contedo 2; contedo n; END;70


- DECLARE a rea para declarao de variveis. As variveis declaradas nessa sesso so inicializadas recebendo seus valores default sempre que existe uma entrada nesse bloco. Como na maior parte das linguagens existentes, na PL/pgSQL, variveis com o mesmo nome possuem o seu escopo definido para o bloco imediatamente posterior sua declarao. Exemplo: CREATE OR REPLACE FUNCTION mostra_valor(valor INT) RETURNS INTEGER AS $$ DECLARE valor INTEGER := $1; BEGIN RAISE NOTICE 'O valor da varivel aqui %', Valor; RETURN valor; END; $$ LANGUAGE 'PLPGSQL'; Executando a funo: SELECT mostra_valor(12); Como visto possvel usar

Documents

Banco de Dados SQL