3. Introdução à linguagem SQL

Nesta secção do capítulo, apresentamos os primeiros comandos SQL para criar e trabalhar com uma única tabela. Geralmente, fornecemos uma versão simplificada destes comandos. A sua sintaxe completa está disponível nos guias de referência do Firebird (ver Secção 2.2).

Uma base de dados é utilizada por pessoas com diferentes níveis de competências:

o administrador da base de dados é geralmente alguém proficiente em SQL e bases de dados. São eles que criam as tabelas, uma vez que esta operação é normalmente realizada apenas uma vez. Com o tempo, podem precisar de modificar a estrutura. Uma base de dados é uma coleção de tabelas ligadas por relações. O administrador da base de dados define estas relações. Também concede permissões aos vários utilizadores da base de dados. Por exemplo, pode especificar que um determinado utilizador tem o direito de visualizar o conteúdo de uma tabela, mas não de a modificar.
O utilizador da base de dados é a pessoa que dá vida aos dados. Dependendo das permissões concedidas pelo administrador da base de dados, irá adicionar, modificar e eliminar dados nas várias tabelas da base de dados. Também irá analisar os dados para extrair informações úteis para o bom funcionamento do negócio, da administração, etc.

Na secção 2.6, apresentámos o editor SQL da ferramenta [IB-Expert]. Esta é a ferramenta que iremos utilizar. Vamos rever alguns pontos:

O Editor SQL pode ser acedido através da opção de menu [Ferramentas/Editor SQL] ou premindo a tecla [F12]

Isto abre uma janela [Editor SQL] onde podemos escrever um comando SQL:

A captura de ecrã acima é frequentemente representada pelo texto abaixo:

SQL> select * from BIBLIO

3.1. Tipos de dados do Firebird

Ao criar uma tabela, deve especificar o tipo de dados que uma coluna da tabela pode conter. Aqui, apresentamos os tipos de dados mais comuns do Firebird. Note que estes tipos de dados podem variar de um SGBD para outro.

`SMALLINT`	inteiro no intervalo [-32768, 32767]: 4
`INTEGER`	inteiro no intervalo [–2 147 483 648, 2 147 483 647]: -100
`NUMERIC(n,m) DECIMAL(n,m)`	número real com n dígitos, dos quais m são decimais NUMERIC(5,2): -100,23, +027,30
`FLOAT`	número real aproximado a 7 dígitos significativos: 10,4
`DUPLA PRECISÃO`	número real aproximado a 15 dígitos significativos: -100,89
`CHAR(N) CARÁCTER(N)`	Uma cadeia de caracteres com exatamente N caracteres. Se a cadeia armazenada tiver menos de N caracteres, é preenchida com espaços. CHAR(10): 'ANGERS ' (4 espaços à direita)
`VARCHAR(N) CARÁCTER VARIÁVEL(N)`	cadeia de até N caracteres VARCHAR(10): 'ANGERS'
`DATE`	uma data: '2006-01-09' (formato AAAA-MM-DD)
`TIME`	uma hora: '16:43:00' (formato HH:MM:SS)
`CARIMBO DE DATA E HORA`	data e hora: '2006-01-09 16:43:00' (formato AAAA-MM-DD HH:MM:SS)

A função CAST() permite converter de um tipo para outro quando necessário. Para converter um valor V declarado como tipo T1 para o tipo T2, escreve-se: CAST(V,T2). É possível realizar as seguintes conversões de tipo:

número para cadeia de caracteres. Esta conversão de tipo é implícita e não requer a utilização da função CAST. Assim, a operação 1 + '3' não requer a conversão do caractere '3'. O seu resultado é o número 4.
DATE, TIME, TIMESTAMP para cadeias de caracteres e vice-versa. Assim
TIMESTAMP para TIME ou DATE e vice-versa

Numa tabela, uma linha pode ter colunas sem valor. Dizemos que o valor da coluna é a constante NULL. Pode verificar a presença deste valor utilizando os operadores

IS NULL / IS NOT NULL

3.2. Criar uma tabela

Para aprender a criar uma tabela, começaremos por criar uma no modo [Design] com o IBExpert. Para tal, seguiremos o método descrito na secção 2.3. Isto irá criar a seguinte tabela:

Esta tabela será utilizada para registar os livros adquiridos por uma biblioteca. O significado dos campos é o seguinte:

Nome	Tipo	Restrição	Significado
`ID`	`INTEGER`	`Chave primária`	`ID do livro`
`TÍTULO`	`VARCHAR(30)`	`NÃO NULO ÚNICO`	`Título do livro`
`AUTOR`	`VARCHAR(20)`	`NÃO NULO`	`Autor`
`GÉNERO`	`VARCHAR(30)`	`NÃO NULO`	`Género (Romance, Poesia, Mistério, Banda Desenhada, etc.)`
`COMPRAR`	`DATA`	`NÃO NULO`	`Data em que o livro foi comprado`
`PREÇO`	`NUMÉRICO(6,2)`	`NÃO NULO`	`Preço`
`DISPONÍVEL`	`CHAR(1)`	`NÃO NULO`	`Está disponível? S (sim), N (não)`

Esta tabela, que foi criada utilizando o assistente IBEXPERT, poderia ter sido criada diretamente utilizando instruções SQL. Para as visualizar, basta consultar o separador [DDL] da tabela:

O código SQL utilizado para criar a tabela [BIBLIO] é o seguinte:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;


CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL,
    TITRE VARCHAR(30) NOT NULL,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);
ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

linha 1: proprietário Firebird - indica o nível de dialeto SQL utilizado
linha 2: específico do Firebird - especifica o conjunto de caracteres utilizado
linhas 6–14: padrão SQL: cria a tabela BIBLIO definindo o nome e o tipo de dados de cada uma das suas colunas.
linha 16: padrão SQL: cria uma restrição especificando que a coluna TITLE não permite duplicados
linha 17: padrão SQL: especifica que a coluna [ID] é a chave primária da tabela. Isto significa que não podem existir duas linhas na tabela com o mesmo ID. Isto é semelhante à restrição [UNIQUE NOT NULL] na coluna [TITLE] e, na verdade, a coluna TITLE poderia ter servido como chave primária. A tendência atual é utilizar chaves primárias que não têm um significado específico e são geradas pelo SGBD.

A sintaxe do comando [CREATE TABLE] é a seguinte:

sintaxe

CREATE TABLE tabela (nome_coluna1 tipo_coluna1 restrição_coluna1, nome_coluna2 tipo_coluna2 restrição_coluna2, ..., nome_colunaN tipo_colunaN restrição_colunaN, outras restrições)

ação

cria a tabela table com as colunas especificadas

`nome_da_coluna_i`	nome da coluna i a ser criada
`tipo_da_coluna_i`	tipo de dados da coluna i: char(30) numeric(6,2) date timestamp ...
`restrição_da_coluna_i`	Restrição que os dados na coluna i devem satisfazer. Aqui estão alguns exemplos: PRIMARY KEY: a coluna é uma chave primária. Isto significa que não podem existir duas linhas na tabela com o mesmo valor nesta coluna e, além disso, é obrigatório que haja um valor nesta coluna. Uma chave primária é utilizada principalmente para identificar uma linha de forma única. NOT NULL : Não são permitidos valores nulos na coluna. UNIQUE : nenhum valor pode aparecer mais de uma vez na coluna. CHECK (condição): O valor na coluna deve satisfazer a condição.
`outras restrições`	Pode especificar aqui - restrições em várias colunas: check(col1>col2) - restrições de chave estrangeira

A tabela [BIBLIO] também poderia ter sido criada com a seguinte instrução SQL:

CREATE TABLE BIBLIO (
    ID INTEGER NOT NULL PRIMARY KEY,
    TITRE VARCHAR(30) NOT NULL UNIQUE,
    AUTEUR VARCHAR(20) NOT NULL,
   GENRE VARCHAR(30) NOT NULL,
   ACHAT DATE NOT NULL,
   PRIX NUMERIC(6,2) NOT NULL,
   DISPONIBLE  CHAR(1) NOT NULL
);

Vamos demonstrar isto. Vamos abrir esta consulta num editor SQL (F12) para criar uma tabela a que chamaremos [BIBLIO2]:

Após a execução, é necessário confirmar a transação para ver o resultado na base de dados:

Assim que isso estiver feito, a tabela aparece na base de dados:

Ao clicar duas vezes no seu nome, podemos visualizar a sua estrutura:

Podemos ver a definição que criámos para a tabela [BIBLIO2]

3.3. Eliminar uma tabela

A instrução SQL para eliminar uma tabela é a seguinte:

`sintaxe`	DROP TABLE tabela
`ação`	Elimina [tabela]

Para eliminar a tabela [BIBLIO2] que acabámos de criar, executamos agora o seguinte comando SQL:

e confirmamos com [Commit]. A tabela [BIBLIO2] é eliminada:

3.4. Preenchimento de uma tabela

Vamos inserir uma linha na tabela [BIBLIO] que acabámos de criar:

Confirme a adição da linha com [Commit] e, em seguida, clique com o botão direito do rato na linha adicionada:

e, como mostrado acima, copie a linha inserida para a área de transferência como uma instrução SQL INSERT. Em seguida, abra qualquer editor de texto e cole o que acabámos de copiar. Obtemos o seguinte código SQL:

INSERT INTO BIBLIO (ID,TITRE,AUTEUR,GENRE,ACHAT,PRIX,DISPONIBLE) VALUES (1,'Candide','Voltaire','Essai','18-OCT-1985',140,'o');

A sintaxe de uma instrução SQL INSERT é a seguinte:

`sintaxe`	insert into table [(coluna1, coluna2, ..)] values (valor1, valor2, ....)
`ação`	adiciona uma linha (valor1, valor2, ...) à tabela. Estes valores são atribuídos à coluna1, coluna2, ... caso existam; caso contrário, às colunas da tabela pela ordem em que foram definidas.

Para inserir novas linhas na tabela [BIBLIO], iremos digitar as seguintes instruções INSERT no editor SQL. Iremos executar e confirmar estas instruções uma a uma. Iremos utilizar o botão [Nova Consulta] para avançar para a próxima instrução INSERT.

insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (2,'Les fleurs du mal','Baudelaire','Poème','01-jan-78',120,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (3,'Tintin au Tibet','Hergé','BD','10-nov-90',70,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (4,'Du côté de chez Swann','Proust','Roman','08-dec-78',200,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (5,'La terre','Zola','roman','12-jun-90',50,'n');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (6,'Madame Bovary','Flaubert','Roman','12-mar-88',130,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (7,'Manhattan transfer','Dos Passos','Roman','30-aug-87',320,'o');
insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (8,'Tintin en Amérique','Hergé','BD','15-may-91',70,'o');

Após confirmar [Commit] as várias instruções SQL, obtemos a seguinte tabela:

3.5. Consultar uma tabela

3.5.1. Introdução

No editor SQL, digite o seguinte comando:

e execute-o. Obtemos o seguinte resultado:

A instrução SELECT é utilizada para recuperar dados de tabelas de bases de dados. Esta instrução possui uma sintaxe muito rica. Aqui, iremos concentrar-nos na sintaxe para consultar uma única tabela. Abordaremos a consulta de várias tabelas simultaneamente mais tarde. A sintaxe da instrução SQL [SELECT] é a seguinte:

sintaxe

SELECT [ALL|DISTINCT] [*|expressão1 alias1, expressão2 alias2, ...]

FROM tabela

ação

exibe os valores de expressão1 para todas as linhas da tabela. expressão1 pode ser uma coluna ou uma expressão mais complexa. O símbolo * denota todas as colunas. Por predefinição, são exibidas todas as linhas da tabela (ALL). Se DISTINCT estiver presente, as linhas selecionadas idênticas são exibidas apenas uma vez. Os valores de expressão1 são exibidos numa coluna intitulada expressão1 ou alias1, caso este último tenha sido utilizado.

Exemplos:

SQL > select titre, auteur from biblio

SQL> select titre,prix from biblio

SQL> select titre TITRE_DU_LIVRE, prix PRIX_ACHAT from biblio

Nos exemplos acima, atribuímos aliases (TÍTULO_DO_LIVRO, PREÇO_DE_COMPRA) às colunas solicitadas.

3.5.2. Exibir linhas que satisfazem uma condição

`sintaxe`	SELECT .... WHERE condição
`ação`	Apenas as linhas que satisfazem a condição são apresentadas

Exemplos

SQL> select titre,prix from biblio where prix>100

SQL> select titre,prix,genre from biblio where genre='Roman'

Um dos livros tem o género «novel» e não «Novel». Utilizamos a função UPPER, que converte uma cadeia de caracteres para maiúsculas, para obter todos os romances.

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN'

Podemos combinar condições utilizando operadores lógicos

`AND`	AND lógico
`OU`	OU lógico
`NÃO`	Negação lógica

SQL> select titre,prix,genre from biblio where upper(genre)='ROMAN' and prix<100

SQL> select titre,genre from biblio

SQL> select titre,genre from biblio where upper(genre)='ROMAN' or upper(genre)='BD'

SQL> select titre,genre from biblio where not( upper(genre)='ROMAN' or upper(genre)='BD')

SQL> select titre,achat from biblio

SQL>select titre,achat from biblio where achat>'31-dec-1987'

SQL> select titre,prix from biblio where prix between 100 and 150

3.5.3. Exibir linhas numa ordem específica

Às sintaxes anteriores, pode adicionar uma cláusula ORDER BY especificando a ordem de exibição desejada:

`sintaxe`	SELECT .... ORDER BY expressão1 [asc\|desc], expressão2 [asc\|desc], ...
`ação`	As linhas de resultado da seleção são apresentadas na ordem de 1: ordem ascendente (asc / ascendente, que é o padrão) ou descendente (desc / descendente) da expressão1 2: se a expressão1 for igual, a exibição baseia-se nos valores da expressão2 etc.

Exemplos:

SQL>select titre, genre,prix,achat from biblio order by achat desc

SQL>select titre, genre,prix,achat from biblio order by prix

SQL>select titre, genre,prix,achat from biblio order by genre desc

SQL >select titre, genre,prix,achat from biblio order by genre desc, prix

SQL>select titre, genre,prix,achat from biblio order by genre desc, prix desc

3.6. Eliminar linhas de uma tabela

`sintaxe`	DELETE FROM tabela [WHERE condição]
`Ação`	Elimina as linhas da tabela que satisfazem a condição. Se não for especificada nenhuma condição, todas as linhas são eliminadas.

Exemplos:

SQL> select titre from biblio

Os dois comandos abaixo são executados um após o outro:

SQL> delete from biblio where titre='Candide'

SQL> select titre from biblio

3.7. Modificar o conteúdo de uma tabela

`sintaxe`	update table set coluna1 = expressão1, coluna2 = expressão2, ... [onde condição]
`ação`	Para as linhas da tabela que satisfazem a condição (todas as linhas, se não houver condição), a coluna1 é definida com o valor da expressão1.

Exemplos:

SQL> select genre from biblio

Escrevemos todos os géneros com maiúscula:

SQL> update biblio set genre=upper(genre)

Verificamos:

SQL> select genre from biblio

Exibir os preços:

SQL> select genre,prix from biblio;

O preço dos romances aumenta 5%:

SQL> update biblio set prix=prix*1.05 where genre='ROMAN';

Vamos verificar:

SQL> select genre,prix from biblio

3.8. Atualização permanente de uma tabela

Quando são feitas alterações numa tabela, o Firebird aplica-as, na verdade, a uma cópia da tabela. Estas alterações podem então ser tornadas permanentes ou revertidas utilizando os comandos COMMIT e ROLLBACK.

`Sintaxe`	COMMIT
`ação`	Torna permanentes as atualizações feitas nas tabelas desde o último COMMIT.

`sintaxe`	ROLLBACK
`ação`	Reverte todas as alterações feitas nas tabelas desde o último COMMIT.

Nota

Um COMMIT é executado implicitamente nas seguintes ocasiões:

a) Ao sair do Firebird

b) Após cada comando que afete a estrutura das tabelas: CREATE, ALTER, DROP.

Exemplos

No editor SQL, pode restaurar a base de dados para um estado conhecido, confirmando todas as operações realizadas desde o último COMMIT ou ROLLBACK:

SQL> commit

Recuperamos a lista de títulos:

SQL> select titre from biblio

Eliminar um título:

SQL> delete from biblio where titre='La terre'

Verificação:

SQL> select titre from biblio

O título foi eliminado com sucesso. Agora vamos reverter todas as alterações feitas desde o último COMMIT / ROLLBACK:

SQL> rollback

Verificação:

SQL> select titre from biblio

O título eliminado reapareceu. Agora, vamos recuperar a lista de preços:

SQL> select prix from biblio

Vamos definir todos os preços como zero.

SQL> update biblio set prix=0

Vamos verificar os preços:

SQL> select prix from biblio

Vamos reverter as alterações feitas na base de dados:

SQL> rollback

e verifique os preços novamente:

SQL> select prix from biblio

Restaurámos os preços originais.

3.9. Adicionar linhas de uma tabela para outra

É possível adicionar linhas de uma tabela para outra quando as suas estruturas são compatíveis. Para demonstrar isto, vamos começar por criar uma tabela [BIBLIO2] com a mesma estrutura que [BIBLIO].

No explorador de bases de dados do IBExpert, clique duas vezes na tabela [BIBLIO] para aceder ao separador [DDL]:

Nesta guia, encontrará a lista de instruções SQL utilizadas para gerar a tabela [BIBLIO]. Copie todo este código para a área de transferência (CTRL-A, CTRL-C). Em seguida, abra uma ferramenta chamada [Script Executive] que permite executar uma lista de instruções SQL:

Abre-se um editor de texto, no qual podemos colar (CTRL-V) o texto previamente copiado para a área de transferência:

Uma lista de comandos SQL é frequentemente chamada de script SQL. O [Script Executive] permite-nos executar esse script, enquanto o editor SQL só permitia a execução de um único comando de cada vez. O script SQL atual cria a tabela [BIBLIO]. Vamos fazer com que crie uma tabela chamada [BIBLIO2]. Para isso, basta alterar [BIBLIO] para [BIBLIO2]:

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(20) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

ALTER TABLE BIBLIO2 ADD CONSTRAINT UNQ1_BIBLIIO2 UNIQUE (TITRE);

ALTER TABLE BIBLIO2 ADD CONSTRAINT PK_BIBLIIO2 PRIMARY KEY (ID);

Vamos executar este script utilizando o botão [Executar Script] abaixo:

O script é executado:

e podemos ver a nova tabela no explorador da base de dados:

Se clicarmos duas vezes em [BIBLIO2] para verificar o seu conteúdo, verificamos que está vazia, o que é normal:

Uma variante da instrução SQL INSERT permite inserir linhas de uma tabela noutra:

`sintaxe`	INSERT INTO tabela1 [(coluna1, coluna2, ...)] SELECT coluna1, coluna2, ... FROM tabela2 WHERE condição
`ação`	As linhas da tabela2 que satisfazem a condição são adicionadas à tabela1. As colunas coluna1, coluna2, ... da tabela2 são atribuídas, por ordem, às colunas coluna1, coluna2, ... na tabela1 e, por isso, devem ser de tipos compatíveis.

Voltemos ao editor SQL:

e executar a seguinte instrução SQL:

SQL> insert into BIBLIO2 select * from BIBLIO where upper(genre)='ROMAN'

o que insere em [BIBLIO2] todas as linhas de [BIBLIO] correspondentes a um romance. Após executar a instrução SQL, vamos confirmá-la com um [Commit]:

SQL> commit

Agora, vamos visualizar os dados na tabela [BIBLIO2]:

SQL> select * from BIBLIO2

3.10. Eliminar uma tabela

`sintaxe`	DROP TABLE tabela
`ação`	elimina a tabela

Exemplo: Eliminar a tabela BIBLIO2

SQL> drop table BIBLIO2

Confirme a alteração:

SQL> commit

No explorador da base de dados, atualize a visualização da tabela:

Vemos que a tabela [BIBLIO2] foi eliminada:

3.11. Modificar a estrutura de uma tabela

`Sintaxe`	ALTER TABLE tabela [ ADD nome_coluna1 tipo_coluna1 restrição_coluna1] [ALTER nome_da_coluna2 TYPE tipo_da_coluna2] [DROP nome_da_coluna3] [ADD restrição] [DROP CONSTRAINT nome_da_restrição]
`ação`	permite adicionar (ADD), modificar (ALTER) e eliminar (DROP) colunas de tabelas. A sintaxe nome_da_coluna1 tipo_da_coluna1 restrição_da_coluna1 é a mesma que a de CREATE TABLE. Também é possível adicionar ou eliminar restrições de tabelas.

Exemplo: Execute os dois comandos SQL seguintes sequencialmente no editor de SQL

SQL > alter table biblio add nb_pages numeric(4), alter genre type varchar(30)

SQL> commit

No explorador da base de dados, vamos verificar a estrutura da tabela [BIBLIO]:

As alterações foram aplicadas. Vamos ver como o conteúdo da tabela mudou:

SQL> select * from biblio

A nova coluna [NB_PAGES] foi criada, mas não tem valores. Vamos eliminar esta coluna:

SQL> alter table biblio drop nb_pages

SQL> commit

Vamos verificar a nova estrutura da tabela [BIBLIO]:

A coluna [NB_PAGES] desapareceu de facto.

3.12. Vistas

É possível ter uma vista parcial de uma tabela ou de várias tabelas. Uma vista comporta-se como uma tabela, mas não contém dados. Os seus dados são extraídos de outras tabelas ou vistas. Uma vista tem várias vantagens:

Um utilizador pode estar interessado apenas em determinadas colunas e linhas de uma determinada tabela. A vista permite-lhe ver apenas essas linhas e colunas.
O proprietário de uma tabela pode querer conceder apenas acesso limitado a outros utilizadores. Uma vista permite-lhe fazê-lo. Os utilizadores que tiver autorizado terão apenas acesso à vista que tiver definido.

3.12.1. Criar uma vista

`sintaxe`	CREATE VIEW nome_da_vista AS SELECT coluna1, coluna2, ... FROM tabela WHERE condição [ COM OPÇÃO DE VERIFICAÇÃO ]
`ação`	cria a vista view_name. Trata-se de uma tabela com a estrutura coluna1, coluna2, ... da tabela e, no que diz respeito às linhas, as linhas da tabela que satisfazem a condição (todas as linhas, caso não haja condição)
`WITH CHECK OPTION`	Esta cláusula opcional especifica que as inserções e atualizações na vista não devem criar linhas que a vista não possa selecionar.

Nota A sintaxe de CREATE VIEW é, na verdade, mais complexa do que a apresentada acima e permite, em particular, a criação de uma vista a partir de várias tabelas. Para tal, a instrução SELECT precisa apenas de referenciar várias tabelas (ver o capítulo seguinte).

Exemplos

Criamos uma vista a partir da tabela biblio que inclui apenas romances (seleção de linhas) e apenas as colunas título, autor e preço (seleção de colunas):

SQL> create view romans as select titre,auteur,prix from biblio where upper(genre)='ROMAN';

SQL> commit

No explorador da base de dados, atualize a vista (F5). É apresentada uma vista:

Podemos visualizar a instrução SQL associada à vista. Para tal, clique duas vezes na vista [ROMANS]:

Uma vista é semelhante a uma tabela. Tem uma estrutura:

e conteúdo:

Uma vista é utilizada como uma tabela. É possível executar consultas SQL nela. Aqui estão alguns exemplos para experimentar no editor SQL:

SQL> select * from romans

SQL> insert into biblio values (10,'Le père Goriot','Balzac','Roman','01-sep-91',200,'o')

O novo romance está visível na vista [ROMANS]?

SQL> select * from romans

Vamos adicionar algo que não seja um romance à tabela [BIBLIO]:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (11,'Poèmes saturniens','Verlaine','Poème','02-sep-92',200,'o');

Vamos verificar a tabela [BIBLIO]:

SQL> select titre, auteur from BIBLIO

Vamos verificar a vista [ROMANS]:

SQL> select titre, auteur from ROMANS

O livro adicionado não está na vista [ROMANS] porque não tinha upper(genre)='ROMAN'.

3.12.2. Atualizar uma vista

Pode atualizar uma vista tal como faria com uma tabela. Todas as tabelas das quais os dados da vista são extraídos são afetadas por esta atualização. Aqui estão alguns exemplos:

SQL> insert into biblio(id,titre,auteur,genre,achat,prix,disponible) values (13,'Le Rouge et le Noir','Stendhal','Roman','03-oct-92',110,'o')

SQL> select * from romans

SQL> select titre, auteur from biblio

Eliminamos uma linha da vista [ROMANS]:

SQL> delete from ROMANS where titre='Le Rouge et le Noir'

SQL> select * from romans

SQL> select auteur, titre from BIBLIO

A linha eliminada da vista [NOVELS] também foi eliminada da tabela [BIBLIO]. Vamos agora aumentar o preço dos livros na vista [NOVELS]:

SQL> update romans set prix=prix*1.05

Vamos verificar na [NOVELS]:

SQL> select * from romans

Qual foi o impacto na tabela [BIBLIO]?

SQL> select titre, auteur, prix from biblio

Os preços dos romances foram, de facto, aumentados em 5% também na tabela [BIBLIO].

3.12.3. Eliminar uma vista

`sintaxe`	DROP VIEW nome_da_vista
`ação`	elimina a vista denominada

Exemplo

SQL> drop view romans

SQL> commit

No explorador da base de dados, pode atualizar a vista (F5) para ver que a vista [ROMANS] desapareceu:

3.13. Utilização de funções de grupo

Existem funções que, em vez de atuarem em cada linha de uma tabela, atuam em grupos de linhas. Trata-se essencialmente de funções estatísticas que nos permitem calcular a média, o desvio padrão, etc., dos dados de uma coluna.

`sintaxe1`	SELECT f1, f2, .., fn FROM tabela [ WHERE condição ]
`ação`	Calcula as funções estatísticas fi em todas as linhas da tabela que satisfazem a condição.

sintaxe2

SELECT f1, f2, .., fn FROM tabela

[ WHERE condição ]

[ GROUP BY expr1, expr2, ..]

ação

A palavra-chave GROUP BY divide as linhas da tabela em grupos. Cada grupo contém as linhas para as quais as expressões expr1, expr2, ... têm o mesmo valor.

Exemplo: GROUP BY género agrupa livros do mesmo género. A cláusula GROUP BY autor,género agruparia livros com o mesmo autor e o mesmo género. A condição WHERE remove primeiro as linhas da tabela que não satisfazem a condição. Em seguida, os grupos são formados pela cláusula GROUP BY. As funções de agregação são então calculadas para cada grupo de linhas.

`sintaxe3`	SELECT f1, f2, .., fn FROM tabela [ WHERE condição ] [ GROUP BY expressão] [ COM a condição do grupo]
`ação`	A cláusula HAVING filtra os grupos formados pela cláusula GROUP BY. Por isso, está sempre associada à presença da cláusula GROUP BY. Exemplo: GROUP BY género HAVING género!='NOVELA'

As funções estatísticas disponíveis são as seguintes:

`AVG(expressão)`	média da expressão
`COUNT(expressão)`	número de linhas para as quais a expressão tem um valor
`COUNT(*)`	número total de linhas na tabela
`MAX(expressão)`	Valor máximo da expressão
`MIN(expressão)`	Mínimo da expressão
`SUM(expressão)`	soma da expressão

Exemplos

SQL> select prix from biblio

Preço médio? Preço máximo? Preço mínimo?

SQL> select avg(prix), max(prix), min (prix) from biblio

SQL> select titre, prix,genre from biblio

Preço médio de um romance? Preço máximo?

SQL> select avg(prix) moyenne, max(prix) prix_maxi from biblio where upper(genre)='ROMAN'

Quantos livros de banda desenhada?

SQL> select count(*) from biblio where upper(genre)='BD'

Quantos romances custam menos de 100 F?

SQL> select count(*) from biblio where upper(genre)='ROMAN' and prix<100

SQL> select genre, prix from biblio

Quantos livros existem e qual é o preço médio por livro para livros do mesmo género?

SQL> select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE from biblio group by upper(genre)

A mesma pergunta, mas apenas para livros que não sejam romances:

SQL>
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having upper(GENRE)!='ROMAN'

A mesma consulta, mas apenas para livros com menos de 150 F:

SQL> 
select upper(genre) GENRE,avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
where prix<150
group by upper(genre)
having upper(GENRE)!='ROMAN'

A mesma consulta, mas mantemos apenas os grupos com um preço médio dos livros >100 F

SQL> 
select upper(genre) GENRE, avg(prix) PRIX_MOYEN,count(*) NOMBRE
from biblio
group by upper(genre)
having avg(prix)>100

3.14. Criação de um script SQL para uma tabela

O SQL é uma linguagem padrão que pode ser utilizada com muitos SGBDs. Para poder mudar de um SGBD para outro, é útil exportar uma base de dados ou simplesmente certos elementos da mesma sob a forma de um script SQL que, quando executado novamente noutro SGBD, será capaz de recriar os elementos exportados no script.

Aqui, iremos exportar a tabela [BIBLIO]. Selecionemos a opção [Extrair Metadados]:

Note que deve estar na base de dados da qual pretende exportar elementos. A opção inicia um assistente:

`1`	onde gerar o script SQL: num ficheiro (Ficheiro) para a Área de Trabalho na ferramenta Script Executive
`2`	nome do ficheiro, se a opção [Ficheiro] estiver selecionada
`3`	o que exportar
`4`	Botões para selecionar (->) ou desmarcar (<-) os objetos a exportar

Se quiséssemos exportar toda a base de dados, marcaríamos a opção [Extract All] acima. Queremos apenas exportar a tabela BIBLIO. Para tal, utilizando [4], selecionamos a tabela [BIBLIO] e, utilizando [2], especificamos um ficheiro:

Se pararmos por aqui, apenas a estrutura da tabela [BIBLIO] será exportada. Para exportar o seu conteúdo, precisamos de utilizar o separador [Tabelas de dados]:

Utilize [1] para selecionar a tabela [BIBLIO]:

Utilize [2] para gerar o script SQL:

Vamos aceitar o prompt. Isto permite-nos visualizar o script gerado no ficheiro [biblio.sql]:

/******************************************************************************/
/****         Generated by IBExpert 2004.06.17 22/01/2006 15:06:13         ****/
/******************************************************************************/

SET SQL DIALECT 3;

SET NAMES ISO8859_1;

CREATE DATABASE 'D:\data\serge\travail\2005-2006\polys\sql\DBBIBLIO.GDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET ISO8859_1;



/******************************************************************************/
/****                                Tables                                ****/
/******************************************************************************/



CREATE TABLE BIBLIO (
ID          INTEGER NOT NULL,
TITRE       VARCHAR(30) NOT NULL,
AUTEUR      VARCHAR(20) NOT NULL,
GENRE       VARCHAR(30) NOT NULL,
ACHAT       DATE NOT NULL,
PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (3, 'Tintin au Tibet', 'Hergé', 'BD', '1990-11-10', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (4, 'Du côté de chez Swann', 'Proust', 'ROMAN', '1978-12-08', 220.5, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (5, 'La terre', 'Zola', 'ROMAN', '1990-06-12', 55.13, 'n');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (6, 'Madame Bovary', 'Flaubert', 'ROMAN', '1988-03-12', 143.33, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (7, 'Manhattan transfer', 'Dos Passos', 'ROMAN', '1987-08-30', 352.8, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (8, 'Tintin en Amérique', 'Hergé', 'BD', '1991-05-15', 70, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (10, 'Le père Goriot', 'Balzac', 'Roman', '1991-09-01', 210, 'o');
INSERT INTO BIBLIO (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (11, 'Poèmes saturniens', 'Verlaine', 'Poème', '1992-09-02', 200, 'o');

COMMIT WORK;



/******************************************************************************/
/****                          Unique Constraints                          ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT UNQ1_BIBLIO UNIQUE (TITRE);


/******************************************************************************/
/****                             Primary Keys                             ****/
/******************************************************************************/

ALTER TABLE BIBLIO ADD CONSTRAINT PK_BIBLIO PRIMARY KEY (ID);

As linhas 1 a 3 são comentários
As linhas 5 a 12 são SQL específico do Firebird
as linhas restantes são SQL padrão que deve ser executável num SGBD que suporte os tipos de dados declarados na tabela BIBLIO.

Vamos executar este script no Firebird para criar uma tabela BIBLIO2 que será um clone da tabela BIBLIO. Para tal, utilize [Script Executive] (Ctrl-F12):

Vamos carregar o script [biblio.sql] que acabámos de gerar:

Modifique-o para manter apenas as partes relativas à criação da tabela e à inserção de linhas. A tabela é renomeada para [BIBLIO2]:

CREATE TABLE BIBLIO2 (
    ID          INTEGER NOT NULL,
    TITRE       VARCHAR(30) NOT NULL,
    AUTEUR      VARCHAR(20) NOT NULL,
    GENRE       VARCHAR(30) NOT NULL,
    ACHAT       DATE NOT NULL,
    PRIX        NUMERIC(6,2) DEFAULT 10 NOT NULL,
    DISPONIBLE  CHAR(1) NOT NULL
);

INSERT INTO BIBLIO2 (ID, TITRE, AUTEUR, GENRE, ACHAT, PRIX, DISPONIBLE) VALUES (2, 'Les fleurs du mal', 'Baudelaire', 'POèME', '1978-01-01', 120, 'n');
...

COMMIT WORK;

Vamos executar este script:

Podemos verificar no explorador de bases de dados que a tabela [BIBLIO2] foi criada e que possui a estrutura e o conteúdo esperados: