Home Map Index Search News Archives Links About LF
[Top Bar]
[Bottom Bar]
[Foto do Autor]
Manuel Soriano
Sobre o Autor: [escreva um pouco de sua biografia aqui]

Conteúdo:
Introdução
Criação de uma Tabela
Sintaxe
Nota
Modificação de Uma Tabela
Inserção de dados em uma Tabela
Seleção de dados
Detalhes do where
Comando update
Comando delete
Referencias
Outros artigos do autor

SQL Tutorial
Part II

[Ilustration]

Abstrato: [A segunda parte do tutorial revisa vários aspectos básicos da linguagem SQL...]




Introdução

Esta é a segunda parte do curso de SQL, nesta parte vamos nos centrar nos civersos comandos SQL, desde a criação da tabela, sua modificação e/ou deleção.

Nos centraremos, sobretudo, no comando SELECT, que é, a meu modo de ver, o mais importante de todos.

Espero que esta segunda parte seja amena e instrutiva.

Criação de uma tabela

Como vimos na primeira parte, para criar uma tabela, usamos o comando CREATE com o qualificativo TABLE, com efeito, o comando CREATE serve para criar:

- usuario : CREATE USER
- tabelas : CREATE TABLE
- indices : CREATE INDEX
- visualizar : CREATE VIEW

O comando create indica ao gerenciador que temos alguma coisa a criar, logo mostraremos aqui e como.

O que nos interessa no momento é a criação de uma tabela:

Syntaxe

CREATE TABLE nome ( coluna tipo [DEFAULT valor] [NOT NULL], ...
      [INHERITS (herda, ...)]
      [CONSTRAINT nom_cons CHECK (prova), CHECK (prova)] );
Onde :
Nome É o nome que se dá à tabela e como será referenciada por qualquer comando.
Coluna É o nome como vamos conhecer esta coluna.
Tipo É o tipo de dado (varchar, char, int, date, time, timestamp), postgres temos outros tipos de dados, mas não são compatíveis com SQL Ansi.
Valor O valor que terá por default.
Herda Este é o próprio Postgres, define uma herança de outra tabela, isto é, criará uma entidade que contém as colunas da tabela que estamos criando e as heranças.
Nom_cons Isto define uma regra de integridade a respeitar cada vez que modificamos uma tabela.
Prova Condição a comprovar.
Exemplo :

CREATE TABLE paises (
         cod_pais integer NOT NULL,
         nome varchar(30))
   CONSTRAINT cod_raro CHECK (cod_pais > 0 AND cod_pais < 154);

Com este exemplo criamos uma tabela de países, cada vez que inserimos um novo dado, cumpriremos estas condições :

- o código do país não será NULO, se tentarmos por um código NULO, o gerenciador nos retornará uma mensagem de erro :

ExecAppend: Fail to add null value in not null attribute cod_pais

- o código do pais será superior a 0 ou inferior a 154, se tentarmos inserir um código do pais diferente, o gerenciador nos retornará com uma mensagem de erro :

ExecAppend: rejected due to CHECK constraint cod_raro

Nota

O que quer dizer NULO? Em SQL existe dois estados, dados ou NÃO dados, com efeito podemos nos interessar por um campo que não tenha dados, tanto o zero (0), como o espaço são dados. SQL introduz o conceito de NULO e trabalha com NIL, um exemplo prático :

Tenho uma tabela com registro de faturas, com os seguintes campos:

cliente, importe, data_emision, data_pago

Quando crio a tabela, inserirei os dados:

cliente, importe, data_emision

Deixarei a data_pago em branco, desta forma poderei conhecer todas as faturas não pagas com o seguinte comando :

SELECT * FROM facturas WHERE data_pago IS NULL;

Alguém pode alegar que um zero (0) no campo data_pago, faria o mesmo papel, é verdade, salvo que zero (0), não é uma data e me impede de criar data_pago do tipo data e poder aplicar-lhe as funções próprias de datas.

Exemplos de criação com NULOS :

insert into paises values (15, NULL);

ou :

insert into paises (cod_pais) values (27);

a ausencia do campo (nome) implica que este receberá o valor NULO.

Modificação de uma tabela

No PostgreSQL a modificação só sera completa com o acrescimo de nova(s) coluna(s).

ALTER TABLE tabela ADD nome tipo;

Onde :

Tabela Nome da tabela a modificar
Nome Nome da coluna a acrescentar
Tipo Tipo de dados (ver CREATE TABLE)

Inserção de dados numa tabela

Agora vamos inserir dados na nossa tabela :

SINTAXE :

INSERT INTO tabela [(coluna, coluna, ...)] VALUES (valor-1, valor-2, ...)

ou :

INSERT INTO tabela [(coluna, coluna, ...)] SELECT ....

Como vimos existe duas maneiras de inserirmos dados numa tabela, seja linha a linha ou o resultado de uma sub-seleção que pode devolver uma ou várias tabelas.

Quando inserimos linhas em uma tabela, SEMPRE poremos dados em todas as colunas inclusive nas que mencionamos, estas foram criadas com valores NULOS.

Se no comando não especificamos que colunas vamos preencher, entendemos que vamos por dados em todas, exemplo :

INSERT INTO paises VALUES (34, 'Espaqa');

Isto estaria errado :

INSERT INTO paises VALUES (34);
Mas, isto sim, estaria correto:

INSERT INTO paises (cod_pais) VALUES (34);

eu recomendo que em comandos embutidos em programas em C ou em funções do banco de dados SEMPRE especifiquemos as colunas que vamos trabalhar, e se acrescentamos uma nova coluna na tabela (ALTER TABLE), o próximo INSERT, acusará um erro, Exemplo :


INSERT INTO paises VALUES (34, 'Espaqa');

INSERT 18301 1

ALTER TABLE paises add poblacion integer

INSERT INTO paises VALUES (34, 'Espaqa');

Isto daria um erro, já que falta o dado para população.

Nota

PostgreSQL, não gera erro, cria a linha com o campo (população) NULO, e isto é só uma particularidade do PostgreSQL, qualquer outro gerenciador SQL acusará um erro.

Logo, nos sobra o outro tipo de INSERT, o que utiliza uma sub-seleção.

Este tipo de insert realiza, muito frequentemente, para criar tabelas temporais ou tabelas para realizar uma tarefa muito concreta de calculos especulativos.

A parte substituida é a que toca os dados, eles vem dados por uma instrução SELECT que se realizava previamente com a inserção dos dados. A instrução SELECT pode devolver uma ou várias tabelas, esta instrução SELECT tem as mesmas restrições que a própria SELECT.

Seleção de dados

Era aqui que eu queria chegar! :-))

Temos topado com a fortaleza dos comandos SQL, a linguagem SQL sem SELECT seria como as lentilhas sem choriço (olha que pus dificuldades para os tradutores :-)

O comando SELECT nos permite acessar aos dados, mas com a ressalva que pode realizar buscas, união de tabelas, funções sobre os dados ou sobre as regras de busca (predicado)

Um exemplo :

select * from paises;

Outro exemplo :

SELECT a.nombre, SUM(população)
    FROM paises a, provincias b, municipios c
    WHERE b.cod_pais = a.cod_pais
        AND (c.cod_pais = b.cod_pais
        AND c.cod_provincia = b.cod_provincia)
        AND população IS NOT NULL
        GROUP BY a.nombre
        ORDER BY sum ASC;

Eu explico, pedí a população de todos os países em ordem por população em modo ascendente, simples.! NÃO ! 8-O

Bem, para isso, acrescentei uma nova coluna (população) a tabela municipios.

Isto fica assim :
create table municipios (cod_pais int, 
			cod_provincia int, 
			cod_municipio int,
			nombre_municipio varchar(60),
			população int);
insert into municipios values (1, 1, 1, 'Pais 1, Provincia 1, Municipio 1', 5435);
insert into municipios values (2, 1, 1, 'Pais 2, Provincia 1, Municipio 1', 7832);
insert into municipios values (3, 1, 1, 'Pais 3, Provincia 1, Municipio 1', 4129);
insert into municipios values (1, 2, 1, 'Pais 1, Provincia 2, Municipio 1', 76529);
insert into municipios values (2, 2, 1, 'Pais 2, Provincia 2, Municipio 1', 9782);
insert into municipios values (3, 2, 1, 'Pais 3, Provincia 2, Municipio 1', 852);
insert into municipios values (1, 3, 1, 'Pais 1, Provincia 3, Municipio 1', 3433);
insert into municipios values (2, 3, 1, 'Pais 2, Provincia 3, Municipio 1', 7622);
insert into municipios values (3, 3, 1, 'Pais 3, Provincia 3, Municipio 1', 2798);
insert into municipios values (1, 1, 2, 'Pais 1, Provincia 1, Municipio 2', 7789);
insert into municipios values (2, 1, 2, 'Pais 2, Provincia 1, Municipio 2', 76511);
insert into municipios values (3, 1, 2, 'Pais 3, Provincia 1, Municipio 2', 98);
insert into municipios values (1, 2, 2, 'Pais 1, Provincia 2, Municipio 2', 123865);
insert into municipios values (2, 2, 2, 'Pais 2, Provincia 2, Municipio 2', 886633);
insert into municipios values (3, 2, 2, 'Pais 3, Provincia 2, Municipio 2', 982345);
insert into municipios values (1, 3, 2, 'Pais 1, Provincia 3, Municipio 2', 22344);
insert into municipios values (2, 3, 2, 'Pais 2, Provincia 3, Municipio 2', 179);
insert into municipios values (3, 3, 2, 'Pais 3, Provincia 3, Municipio 2', 196813);
insert into municipios values (1, 1, 3, 'Pais 1, Provincia 1, Municipio 3', 491301);
insert into municipios values (2, 1, 3, 'Pais 2, Provincia 1, Municipio 3', 166540);
insert into municipios values (3, 1, 3, 'Pais 3, Provincia 1, Municipio 3', 165132);
insert into municipios values (1, 2, 3, 'Pais 1, Provincia 2, Municipio 3', 0640);
insert into municipios values (2, 2, 3, 'Pais 2, Provincia 2, Municipio 3', 65120);
insert into municipios values (3, 2, 3, 'Pais 3, Provincia 2, Municipio 3', 1651462);
insert into municipios values (1, 3, 3, 'Pais 1, Provincia 3, Municipio 3', 60650);
insert into municipios values (2, 3, 3, 'Pais 2, Provincia 3, Municipio 3', 651986);
insert into municipios values (3, 3, 3, 'Pais 3, Provincia 3, Municipio 3', NULL);
commit work;

Eu sei que poderíamos fazer por ALTER TABLE, mas deveria usar o UPDATE e ainda não os expliquei, assim se quiserem usem "cut & paste" e felicidades :-))

Agora vamos executar a pesquisa (QUERY), e o resultado deveria ser :

nome|    sum
------+-------
pais 1| 705559
pais 2|1212418
pais 3|2804018
(3 rows)
Agora verificamos :

select sum(população) from municipios where cod_pais = 1;

Resultado :
   sum
------
791986
(1 row)

!!!!!! UMA DIFERENÇA !!!!!!!!!!

Olhemos a tabela das provincias, falta a provincia 3, façamos :

INSERT INTO PROVINCIAS VALUES (3, 1, 'Provincia 3, Pais 1');
INSERT INTO PROVINCIAS VALUES (3, 2, 'Provincia 3, Pais 2');
INSERT INTO PROVINCIAS VALUES (3, 3, 'Provincia 3, Pais 3');

E repetimos o comando, resultado :
nome|    sum
------+-------
pais 1| 791986
pais 2|1872205
pais 3|3003629 

Nos faltava a provincia 3 de cada pais.

Agora para os que tenham se perdido, lembremo-nos que as conjunções entre tabelas eram EXATAS, quer dizer, somente extrai dados se a condição dentro do predicado é exato.

Olhemos a primeira parte do WHERE : b.cod_pais = a.cod_pais

Isto quer dizer que junto da tabela paises até provincia sempre que o código do país seja igual, agora recordemos os dados dos países que inserimos :

Não o execute, é só ilustração.

create table paises (cod_pais integer, nome varchar(30));
insert into paises values (1, 'pais 1');
insert into paises values (2, 'pais 2');
insert into paises values (3, 'pais 3');
commit work;
Agora os dados das provinvias :
create table provincias (cod_provincia int, 
			cod_pais int, 
			nom_provincia varchar(30));
insert into provincias values (1, 1, 'Provincia 1, Pais 1');
insert into provincias values (2, 1, 'Provincia 2, Pais 1');
insert into provincias values (1, 2, 'Provincia 1, Pais 2');
insert into provincias values (2, 2, 'Provincia 2, Pais 2');
insert into provincias values (1, 3, 'Provincia 1, Pais 3');
insert into provincias values (2, 3, 'Provincia 2, Pais 3');
commit work;
Faltam todas as provincias 3 de cada país, mas na tabela de município estão todos os dados das provinvias com código 3, assim que é normal que não some-se os dados dos municípios das províncias com código 3, pode ser descartada a segunda parte do where :
        AND (c.cod_pais = b.cod_pais
        AND c.cod_provincia = b.cod_provincia)

A provincia existia na tabela dos municipios mas NÃO na tabela das provincias.

Para os que não entenderam, tome uma aspirina, ou vá passear com o cachorro (se não tem cachorro vá passear sem cachorro), respirar um pouco de ar puro e volte a começar desde a primeira parte.

É muito importante comprender como realizamos as conjunções de dados, sem ele, os desenvolvimentos que fizemos podem ter resultados imprevisiveis.

Fechemos os parenteses e comecemos com a sintaxe do comando SELECT.

SELECT [DISTINCT] expresion1 [AS nom-atributo] {, expresion-i [as nom-atributo-i]} 
       [INTO TABLE classname] 
       [FROM from-list] 
       [WHERE where-clause] 
       [GROUP BY attr_name1 {, attr_name-i....}] 
       [ORDER BY attr_name1 [ASC | DESC ] [USING op1 ] {, nom-atributo-i...}] 
       [UNION {ALL} SELECT ...] 
Passo a passo :
DISTINCT : isto é para eliminar tabelas duplicadas na saída.
expresion1 : queremos na saída, normalmente uma coluna de uma tabela da lista FROM.
AS nom-atributo : um alias para o nome da coluna, exemplo:
manu=> select cod_pais from paises;
cod_pais
--------
       1
       2
       3
(3 rows)          
Agora con o alias :
manu=> select cod_pais as pasi from paises;
pasi
----
   1
   2
   3
(3 rows)
INTO TABLE : permite inserir as tabelas resultantes diretamente em outra tabela . (ver INSERT ... SELECT...)
FROM : lista das tabelas na entrada
WHERE : predicado da seleção (critérios de união e seleção).
GROUP BY : Critério de agrupamento, certas funções que usamos na (expresion) podemos precisar de um agrupamento, quer dizer, um critério de discriminação e resultado.
ORDER BY : Criterio de ordenação das tabelas de saídas. ASC ordem ascendente, DESC ordem descendente, USING define se a ordem da coluna não está na lista (expressão...)
UNION ALL SELECT : Isto define se acrescento al resultado do primeiro SELECT ao segundo SELECT que pode ser de tabelas distintas, mas devolvendo o mesmo número de colunas.

Vimos que os comandos SELECT não só devolvem os dados do BD, sim que também pode modificá-los :


SELECT SUM(saldo * 1.1) - SUM(saldo) AS incremento FROM empregados;

Nos mostra o incremento de 10% a pagar no saldo.

Vamos ver quais as funções que temos a nossa disposição :

COUNT() : mostra a quantidade de tabelas não NULAS
SUM() : mostra a soma total de uma coluna numérica.
AVG() : mostra o preço medio de uma coluna numérica.
MIN() : mostra o valor mínimo de uma coluna
MAX() : mostra o valor máximo de uma coluna
FLOAT(int) : mostra um FLOAT8, FLOAT(12345)
FLOAT4(int) : mostra um FLOAT4, FLOAT4(12345)
INT(float) : mostra um INT de um FLOAT/4, INT(123.456)
LOWER(texto) : mostra um texto em minúsculas.
UPPER(texto) : mostra texto em maiúsculas.
LPAD(texto, long, char) : preenche a esquerda com "char" no comprimeiro "long" da coluna "texto"
RPAD(texto, long, char) : preenche a direita com "char" no comprimento "long" da coluna "texto"
LTRIM(texto, char) : apaga a esquerda de "texto" todo caractere "char"
RTRIM(texto, char) : apaga a direita de "texto" todo caractere "char"
POSITION(cadeia IN texto) : extrai do "texto" a posição da "cadeia", mas NÃO FUNCIONA
SUBSTR(texto,desde[,até]) : extrai a sub-cadeia de "texto", da primeira posiçao "de sde" até a posição "até" o final da cadeia
DATETIME(data, hora) : converte o formato datetime em data (AAAA-MM-DD) em hora (HH:MM)

Estas são algumas funções em SQL, são as que estão definidas em SQL ANSI e estão presentes no Postgres95.

Detalhes do where

Até agora vimos que na sessão WHERE do SELECT colocávamos coisas como :

AND coluna = valor

Isto é uma pequena mostra do que podemos por ou combinar :

AND, OR, NOT, IN, IN ALL, =, !=, >, <, (SELECT....), LIKE lembrando que os parenteses tem relevancia, exemplos :

WHERE 
   coluna IN (SELECT DISTINCT coluna FROM tabela WHERE ....)
   coluna IN ('valor1','valor2','valor3',...)
   (coluna = 'valor' and coluna = 'outro_valor' OR coluna != 'valor')
!= é igual que dizer NOT EQUAL

LIKE permite procurar uma cadeia dentro de uma coluna com comodines :

WHERE coluna LIKE '%Pepito%'

El % é um comodim, no exemplo, será verdadeiro se "Pepito" está na string.

WHERE coluna LIKE 'Pepito%'


WHERE coluna LIKE '%Pepito'

será verdeiro se "Pepito" está no final da string.

Por aqui todas as opções possíveis do WHERE sai de minhas possíbilidades e tempo, só cabe aqui dizer que o limite está na máquina do programador e os limites do próprio tradutor do gerenciador.

Agora podemos deixar o comando SELECT e centrarmos nos dois últimos.

Comando update

O comando update permite modificar uma ou várias tabelas, dependendo da condição no WHERE.

SINTAXE
UPDATE tabela SET coluna-1 = expresão-1 
                 [, coluna-i = expresão-i] 
       [WHERE condição] 
Onde : tabela : é a tabela para modificar, só poderemos modificar uma tabela por vez coluna : é a coluna a ser modificada expresão : é o valor que vai receber a coluna, este valor pode ser estático ou o resultado de uma função condição : é a condição que define o ambito do trabalho da modificação, aqui são aplicadas todas as regras definidas para o comando SELECT

Comando delete

O comando DELETE permite modificar uma ou várias colunas de uma tabela.

SINTAXE

DELETE FROM tabela
       [WHERE condição] 
Onde :
tabela : é a tabela onde vamos apagar, só podemos apagar uma tabela por vez
condição : é a condição que define o ambito de trabalho de exclusão, aqui são aplicáveis todas as regras definidas para o SELECT

NOTA : se não existir WHERE, DELETE eliminará todas as colunas da tabela.

Referencias

Outros artigos do autor


Esta página web é mantida por Miguel Angel Sepulveda
© Author 1998
LinuxFocus 1998