LinuxFocus Julho 1998: Tutorial de SQL

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

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.

SQL Tutorial Part II

Introduï¿½ï¿½o

Criaï¿½ï¿½o de uma tabela

Syntaxe

Nota

Modificaï¿½ï¿½o de uma tabela

Inserï¿½ï¿½o de dados numa tabela

Nota

Seleï¿½ï¿½o de dados

Detalhes do where

Comando update

Comando delete

Referencias

Outros artigos do autor

SQL Tutorial
Part II