Curso - PostgreSQL Essencial 2010-2x2
-
Upload
joaquin-neto -
Category
Documents
-
view
344 -
download
2
Transcript of Curso - PostgreSQL Essencial 2010-2x2
Agenda1.Introduo ao PostgreSQL 2.Interfaces de acesso ao PostgreSQL
PostgreSQL EssencialLuciano Mittmann [email protected]
3.Gerenciando tabelas 4.Selecionando dados 5.Funes e operadores 6.Alterando dados 7.Controle de transaes 8.Outros objetos de banco de dados 9.Mdulos adicionais do diretrio contrib2
O que o PostgreSQL?
PostgreSQL um
Introduo ao PostgreSQL
sistema gerenciador de banco de dados relacional (SGBDR) avanado extensvel dentro dos padres multiplataforma (Unix, Linux, Windows, Mac) de cdigo aberto com a licena mais liberal possvel (BSD)4
O que ele faz de mais?
De onde ele surgiu?
Suporta diversas features padres SQL:
De INGRES a POSTGRES: 1977-1994
chaves estrangeiras (FKs) disparadores (triggers) vises (views) controle de concorrncia (MVCC)
Michael Stonebraker, prof. da UC - Berkeley 77: desenvolveu o INGRES fundou o SGBDR! 86: continuou a pesquisa com o POSTGRES (cdigo do POSTGRES no partiu do INGRES) 94: adicionado suporte a SQL 95: liberado como Postgres95 96: reliberado como PostgreSQL 6.0 consolidao do Grupo de Desenvolvimento6
Permite a customizao e criao de:
De POSTGRES a PostgreSQL: 1994-1996
tipos de dados e operadores funes escalares e de agregao linguagens procedurais5
Quem o desenvolve hoje?
Histrico das versesPadro SQL Desempenho Corporativo Melhorias no Gerenciamento
Thomas Lockhart Jolly Chen Vadim Mikheev Jan Wieck Andrew Yu Tom Lane Bruce Momjian Marc Fournier
Sem Crash
WAL (log trans.)
left joins, schema, PL/lang
OLTP, FSM, replicao, savepoints
DW, parties, SMP, Windows
em major releases ocorre alterao na estrutura dos dados8
7
Funcionamento do PostgreSQLinstncia catlogo de dados objetos do SGBD table spaces usurios do SGBD banco esquema objeto banco esquema objeto 1. conexo ocorre em um nico banco de dados e para um usurio 2. no existem sinnimos! 3. acesso a objetos de outro esquema com o recurso de search path 4. nativamente, no h como acessar objetos de outro banco9
Interfaces de acesso ao PostgreSQL
Conexo pelo Java (via JDBC)
Exemplo de acesso via Java$javaccp~/lib/pgsql.jarConPg.java importjava.sql.*; $javacp.:~/lib/pgsql.jarConPg classConPg{ publicstaticvoidmain(Stringargs[])throwsSQLException{ DriverManager.registerDriver(neworg.postgresql.Driver());
Download do driver
http://jdbc.postgresql.org/ postgresql-.jar org.postgresql.Driver jdbc:postgresql://servidor[:porta]/banco ex: jdbc:postgresql://10.15.60.84:5434/ccb11
Biblioteca JAR
}
Classe de acesso
Connectionconn=DriverManager.getConnection( "jdbc:postgresql://localhost:5432/cidades", "rodrigo","celepar"); Statementstmt=conn.createStatement(); ResultSetrset=stmt.executeQuery( "SELECTnomeFROMcidade_internacionalLIMIT5"); while(rset.next()) System.out.println(rset.getString(1)); }12
URL de conexo
psql: cliente em linha de comando
psql: principais parmetrosUso: psql[OPES]...[NOMEBD[USURIO]]
Natureza
$psqlhelp
terminal interativo, como SQL*Plus ou sqlcmd acompanha o cliente do PostgreSQL #aptgetinstallpostgresqlclient cliente padro do PostgreSQL, acesso nativo, console, no requer servidor grfico (ie: X), rpido, pode ser usado na criao de scripts
Multiplataforma
Opesgerais: dNOMEBDespecificaonomedobancodedadosaoqualquerseconectar(padro:$USER) cCOMANDOexecutasomenteumcomando(SQLouinterno)esai fARQUIVOexecutacomandosdeumarquivoesai llistaosbancosdedadosdisponveisesai helpmostraestaajudaesai versionmostrainformaosobreaversoesai Opesdeentradaesada: Emostraconsultasqueoscomandosinternosgeram qexecutasilenciosamente(semmensagens,somentesadadaconsulta) oARQUIVOenviaresultadosdaconsultaparaumarquivo(ou|pipe) smodopassoapasso(confirmacadaconsulta) LARQUIVOenvialogdasessoparaarquivo Opesparaformatodesada: Amododesadaemtabeladesalinhada(Pformat=unaligned) HmododesadaemtabelaHTML(Pformat=html) texibesomenteosregistros(Ptuples_only) TTEXTOdefineatributosdomarcadortabledoHTML(width,border)(Ptableattr=) xhabilitasadaemtabelaexpandida(Pexpanded) FSEPARADORdefineseparadordecampos(padro:"|")(Pfieldsep=) RSEPARADORdefineseparadorderegistros(padro:nova_linha)(Precordsep=) Opesdeconexo: hMQUINAservidordebancodedadosoudiretriodosoquete(padro:"/var/run/postgresql") PPORTAportadoservidordebancodedados(padro:"5432") UUSURIOnomedeusuriodobancodedados(padro:$USER) Wperguntasenha(podeocorrerautomaticamente)
Instalao no Debian
Caractersticas
13
14
psql: exemplos de parmetros #1
psql: exemplos de parmetros #2
conectar-se ao servidor scelepar05480, porta 5432, banco de dados cursopg, usurio sa_cursopg, senha stranger$psqlhscelepar05480p5432\ cursopgsa_cursopg
executar nica linha de comando SQL$psqlcselectversion() $echoselectversion()|psql
executar arquivo de script SQL$catscript.sql|psql $psqlscript.sql Novo servidorNome - Apelido do servidor Mquina
servidores
bancos
dica: instrues para a criao ou excluso do objeto
esquemas
endereo IP ou nome de domnio do servidor banco de dados inicial32
Manuteno do DB
objetos
31
pgAdmin: efetuando consultas
pgAdmin: exportando dados via SQL
selecione o banco de dados desejado e acesse o menu Ferramentas -> Query tool
na jenela do Query tool, acesse o menu Query -> Execute to file Row separator
janela de entrada pressione [F5] para executar
LF (Unix) CR/LF (DOS)
tabela de resultados
Filename
arquivo de destino (local)34
33
pgAdmin: planos de execuo
Ferramenta: phpPgAdmin
na jenela do Query tool, acesse o menu Query -> Explain ou pressione [F7]
Home page e download
http://phppgadmin.sourceforge.net/ requer Apache e PHP #aptgetinstallphppgadmin requer apenas browser no cliente, scripts em PHP, cdigo aberto, diversas funcionalidades especficas do PostgreSQL, intuitivo
Multiplataforma
diagrama do plano de execuo
Instalao no Debian
Caractersticas
35
36
Ferramenta: TOra
Ferramenta: Aqua Data Studio
Home page e download
Home page e download
http://tora.sourceforge.net/
http://www.aquafold.com/ requer apenas Java VM (>= 1.5) #aptgetinstalldatastudio fcil instalao, intuitivo, acompanha drivers para diversos SGBDs, extrao de DDLs, IDE para criao e execuo de scripts
Plataformas
Multiplataforma
Linux e Windows #aptgetinstalltora
Toolkit for Oracle
Instalao no Debian
Instalao no Debian
Caractersticas
Caractersticas
interface desktop (compilado), licena GPL, til para criao de stored procedures, navegao de schemas e execuo de SQLs
37
38
Introduo tabela
o que uma tabela num SGBDR?
Gerenciando tabelas
uma estrutura de dados formada por linhas (registros, tuplas) e colunas (campos, atributos) nmero e ordem das colunas so fixos, e cada coluna possui um nome nico nmero de linhas varivel, em geral no garantindo a ordem destas e podendo ser no-nicas cada coluna possui um tipo de dado, que limita o conjunto de possveis valores
principais caractersticas
40
Introduo tabela
Como criar uma tabela?CREATETABLEtable_name([ {column_namedata_type[DEFAULTdefault_expr] [column_constraint[...]] |table_constraint [,...] sintaxe simplificada ]) [CONSTRAINTconstraint_name] {NOTNULL|NULL|UNIQUEindex_parameters| PRIMARYKEYindex_parameters|CHECK(expression)| REFERENCESreftable[(refcolumn)] [ONDELETEaction][ONUPDATEaction]} [CONSTRAINTconstraint_name] {UNIQUE(column_name[,...])index_parameters| PRIMARYKEY(column_name[,...])index_parameters| CHECK(expression)| FOREIGNKEY(column_name[,...]) REFERENCESreftable[(refcolumn[,...])] [ONDELETEaction][ONUPDATEaction]} 42
41
Tabelas: exemplos #1
Tabelas: exemplos #2
criao da tabela de filmesCREATETABLEfilmes( codigochar(5) CONSTRAINTfilmes_pkPRIMARYKEY, titulovarchar(40)UNIQUE, didintegerNOTNULL,distribuidor data_proddateDEFAULTcurrent_date, generovarchar(10), duracaointervalhourtominute );43
criao da tabela de distribuidoresCREATETABLEdistribuidores( didintegerCHECK(did>100), nomevarchar(40)NOTNULL, PRIMARYKEY(did) ); CREATETABLEdistribuidores2( didintegerPRIMARYKEY, nomevarchar(40)DEFAULT'LusoFilmes' );44
outra maneira
Tabelas: opes especficas #1
Tabelas: opes especficas #2
[ GLOBAL | LOCAL ] TEMP
[ DEFERRABLE | NOT DEFERRABLE ]
criao de tabelas temporrias (global ou local)
configurao de constraints adiveis alocao fsica da tabela em outra partio alocao fsica do ndice em outra partio especificao de parmetros adicionais para o armazenamento fsico da tabela (ex: fillfactor)46
INHERITS ( parent_table [, ... ] )
TABLESPACE tablespace
herana e polimorfismo de tabelas (como em Orientao a Objetos) cpia das colunas e restries, sem o mesmo vnculo da herana identificador nico para as linhas (ie: ROWID)
LIKE parent_table [ ... ]
USING INDEX TABLESPACE tablespace
WITH ( storage_parameter [= value] [, ... ] )
{ WITH | WITHOUT } OIDS45
Tabelas: qualificando o esquema
Tabelas: visualizando a estrutura
criao de tabela em outro esquemaCREATETABLEmy_schema.tab( idserial ); SETsearch_pathTOmy_schema; CREATETABLEtab( idserial );47
via psqldb=#\dfilmes Tabela"public.filmes" Coluna|Tipo|Modificadores ++ codigo|character(5)|notnull titulo|charactervarying(40)| did|integer|notnull data_prod|date|default('now'::text)::date genero|charactervarying(10)| duracao|intervalhourtominute| ndices: instrues "filmes_pk"PRIMARYKEY,btree(codigo) "filmes_titulo_key"UNIQUE,btree(titulo)
ambos os caminhos levam Roma!
DDL
via pgAdmin
abrir a rvore Servidor, Banco, Esquemas, Tabelas, Colunas
48
Como alterar a estrutura da tabela?
Como alterar a estrutura da tabela?
incluir uma colunaALTERTABLEdistribuidores ADDCOLUMNenderecovarchar(30);
alterar os valores padres de camposALTERTABLEfilmes ALTERCOLUMNdata_prodDROPDEFAULT, ALTERCOLUMNduracaoSETDEFAULT'1:20';
excluir uma colunaALTERTABLEdistribuidores DROPCOLUMNenderecoRESTRICT;
renomear uma colunaALTERTABLEfilmes RENAMECOLUMNdata_prodTOproducao;
alterar o tipo de dados de uma colunaALTERTABLEdistribuidores ALTERCOLUMNnomeTYPEvarchar(50);49
50
Como alterar a estrutura da tabela?
Como alterar a estrutura da tabela?
renomear a tabelaALTERTABLEdistribuidores2 RENAMETOdist2;
adicionar check constraintALTERTABLEdistribuidores ADDCONSTRAINTck_nome CHECK(length(nome)>=5);
tornar coluna obrigatriaALTERTABLEfilmes ALTERCOLUMNgeneroSETNOTNULL;
remover check constraintALTERTABLEdistribuidores DROPCONSTRAINTck_nome;
retirar obrigatoriedade da colunaALTERTABLEfilmes ALTERCOLUMNgeneroDROPNOTNULL;51 52
Como alterar a estrutura da tabela?
Como destruir a tabela?DROPTABLE[IFEXISTS]name[,...] [CASCADE|RESTRICT]
mover a tabela para outro tablespaceALTERTABLEdistribuidores SETTABLESPACEtbs_veloz;
mover tabela para outro esquemaALTERTABLEfilmes SETSCHEMApublic;
IF EXISTS
caso a tabela no exista, no ocorre erro automaticamente exclui os objetos que dependem da tabela o oposto da opo acima, e o default
CASCADE
dica: no psql, use
\db para exibir os tablespaces \dn para exibir os esquemas53
RESTRICT54
Comentrios em objetos
comentar bancos, esquemas, tabelas, campos e outros objetosCOMMENTONDATABASEcursopg IS'CursodePostgreSQL'; COMMENTONSCHEMApublic IS'Esquemapblico(padro)'; COMMENTONTABLEfilmesIS'Filmes'; COMMENTONCOLUMNfilmes.did IS'Cdigododistribuidor';
Selecionando dados
remover o comentrioCOMMENTONTABLEfilmesISNULL;55
Consultas: uma viso geral
Consultas: a clusula FROMFROMtable_reference[, table_reference[,...]]
o processo ou o comando para obter dados de um banco de dados chamado de query (ie: consulta ou consulta SQL) o SELECT uma instruo de DML (Data Manipulation Language) da SQL um exemplo de consulta simplesSELECT*FROMtabela;
serve para definir a lista de tabelas (uma ou mais) a serem lidas para a consulta a table_reference pode ser o nome de uma tabela (com ou sem esquema), uma subconsulta, uma juno ou combinao destas se mais de uma tabela for especificada, elas sero cruzadas numa tabela virtual58
um exemplo de consulta sem tabelaSELECTnow();
ah! no precisa de um artifcio chamado DUAL
57
Consultas: tipos de junes
Consultas: tipos de junes
preparar o seguinte caso de testeCREATETABLEt1(numint,nometext); CREATETABLEt2(numint,valortext); INSERTINTOt1VALUES(1,'a'); INSERTINTOt1VALUES(2,'b'); INSERTINTOt1VALUES(3,'c'); INSERTINTOt2VALUES(1,'xxx'); INSERTINTOt2VALUES(3,'yyy'); INSERTINTOt2VALUES(5,'zzz'); SELECT*FROMt1; SELECT*FROMt2;59
CROSS JOIN (resulta em N x M linhas)SELECT*FROMt1,t2; SELECT*FROMt1CROSSJOINt2;
INNER JOIN (condio satisfeita nas duas)SELECT*FROMt1 INNERJOINt2ONt1.num=t2.num; SELECT*FROMt1 INNERJOINt2USING(num); SELECT*FROMt1 NATURALINNERJOINt2;notao especfica do PostgreSQL60
Consultas: tipos de junes
Consultas: utilizao de apelidos
LEFT e RIGHT JOIN (buraco em um lado)SELECT*FROMt1 LEFTJOINt2ONt1.num=t2.num; SELECT*FROMt1 LEFTJOINt2USING(num); SELECT*FROMt1 RIGHTJOINt2ONt1.num=t2.num;
exemplos de utilizaoSELECT* FROMTB_UmaTabelaComNomeCompridonc JOINTB_OutraTabelaComNomePeculiarnp ONnc.id=np.num WHEREnp.campo>5;o AS opcional!
FULL JOIN (buracos nos dois lados)SELECT*FROMt1 FULLJOINt2ONt1.num=t2.num;61
SELECT* FROMpessoasASmae JOINpessoasASfilho ONmae.id=filho.id_mae;62
Consultas: a clusula WHEREWHEREsearch_condition
Consultas: lista de seleoalm do *, a lista especfica de campos pode ser utilizada na clusula SELECTSELECTa,b,cFROM... SELECTtabl.a,tab2.a,tab1.bFROM... SELECTtab1.*,tab2.aFROM...
serve para filtrar os registros obtidos da clusula FROM, mantendo apenas os que atinjam determinada condio search_condition uma expresso de valor qualquer que retorne um tipo de dados lgico (booleano)SELECT*FROMfdtWHEREc1>5; SELECT*FROMfdtWHEREc1IN(1,2);63
os campos da lista de seleo podem ser rotulados, para evitar problemas de interpretao ou redundnciaSELECTaASvalor,b+cASsomaFROM... SELECTversion()ASversao_atual;64
Consultas: operaes de conjuntosquery1UNION[ALL]query2 query1EXCEPT[ALL]query2crculo quadrado ALL define se linhas duplicadas sero consideradas
Consultas: a clusula ORDER BYORDERBYsort_expression[ASC|DESC] [,...]
query1INTERSECT[ALL]query2MINUS em outros SGBDs
serve para definir a ordem em que as linhas, j processadas, sejam retornadas sort_expression pode ser qualquer expresso que seria vlida na lista de seleo (podem ser usados nmeros)SELECTa,bFROMtab1ORDERBYa+b,c; SELECTa+bASsmFROMtablORDERBYsm;
UNION
INTERSECT
EXCEPT65
SELECTa,bFROMtab1ORDERBY2DESC,1;66
Consultas: limitando o resultadoLIMITnumber
serve para limitar a quantidade de linhas a serem retornadas de uma consultaSELECT*FROMt1ORDERBYnumLIMIT2;importante: usar em conjunto com ORDER BY!
Funes e operadores
OFFSETnumber
serve para pular as primeiras N linhas da clusula LIMIT (usado em paginaes)SELECT*FROMt1ORDERBYnum LIMIT2OFFSET1;67
Funes matemticas
Operadores matemticos
absoluto, logaritmo natural, logaritmo 10SELECTabs(17.4),ln(2.0),log(100.0);
adio, subtrao, multiplicao, divisoSELECT1+2,34,2*3,5/2;
mdulo, exponencial, potncia, raizSELECTmod(9,4),exp(1.0), power(9,0.5),sqrt(9.0);
mdulo, exponenciao, raiz, fatorialSELECT5%2,2^3,|/9,||/27,3!;
arredondar, truncarSELECTround(3.5),round(3.55,1), trunc(3.5),trunc(3.55,1);
bitwise AND, OR, XOR e NOTSELECT91&15,32|3,17#5,~1;
bitwise shift left e shift rightSELECT11; SELECT3::bit(8),(31)::bit(8),B'00000110'::int;
randmico, gerar sementeSELECTsetseed(0.54823),random();69
70
Funes matemticas Exerccio
Funes de texto - I
multiplicao de nmeros da tabelacursopg=>SELECT???FROMtabela; mul 120 (1registro)
concatenao de stringsSELECT'Post'||'greSQL','a'||null;
converso de codificaoSELECTconvert_from('codificao','LATIN1');
minsculo, maisculoSELECTlower('PARAN'),upper('PontaGrossa');
iniciais maisculasSELECTinitcap('DIRIO'),initcap('ol');
DICA: ln (a . b . c) = ln a + ln b + ln c71
72
Funes de texto - II
Funes de texto - III
localizao de substringSELECTposition('gre'in'PostgreSQL');
comprimento do textoSELECTlength('Oitocentos');
extrair substringSELECTsubstring('PRAIA'from2for3); SELECTsubstring('PRAIA'from3); SELECTsubstring('PRAIA'for3); SELECTsubstring('PRAIA'from'^..');
complementar texto (direita e esquerda)SELECTrpad('abc',5,'*'), lpad('abc',5,'*');
repetir texto diversas vezesSELECTrepeat('X',3),repeat('abc',2);
remover espaos lateraisSELECTtrim('abc'),rtrim('ab'), ltrim('ab');73
substituir substrings no textoSELECTreplace('abcabcabc','c','..');74
Funes de texto - IV
Funes de texto Exerccio
clculo de MD5SELECTmd5('senhasecreta'),md5('aaa');
reproduzir a seguinte sada via SQLcursopg=>SELECT???FROMtabela; id|capitulo + 1|Primeiro......1 2|Segundo.......2 3|Terceiro......3 4|Quarto........4 5|Quinto........5 (5registros)
citao - aspas simplesSELECTquote_literal('C\'estvrai!');
extrair texto com delimitadorSELECTsplit_part('[email protected]','@',1);
transformar texto em formato ASCIISELECTto_ascii('Aafroderis');
75
76
Funes de busca de padro: LIKEstring[NOT]LIKEpattern
Funes de busca de padro: ERoperadores de expresso regular (POSIX)SELECT*FROMtabela WHEREdes~'^qu'ANDdes~'to$'; SELECT*FROMtabela WHEREdes!~'qu(a|i)'; SELECT*FROMtabela WHEREdes~*'^Qu';op.ao ~buscapadro(CS) ~*buscapadro(CI) !~nobusca(CS) !~*nobusca(CI)77
operador padro SQL ANSISELECT*FROMtabela WHEREdesLIKE'%eiro'; SELECT*FROMtabela WHEREdesLIKE'qu__to'; SELECT*FROMtabela WHEREdesNOTLIKE'qu__to' ANDdesNOTLIKE'%eiro';
curingas: % e _
similar a ERs usadas em egrep, sed e awk
operadores
expsignificado ^inciodastring $finaldastring .qualquercaracter *0oumais +1oumais
ATENO: cuidado com o uso do LIKE pode provocar leituras do tipo full scan na tabela!
expresses regulares
78
Funes de formatao de tipo
Funes de data e hora
converter para text com o to_char()SELECTto_char(now(),'HH12:MI:SS'); SELECTto_char(125,'00000'); SELECTto_char(125.8,'999D99S');
data, hora e data/hora atualSELECTcurrent_date,current_time, current_timestamp,now();
calcular idade (duas verses)SELECTage('20010410','19570613'); SELECTage('15000422'::timestamp);
converter para date com o to_date()SELECTto_date('05122000','DDMMYYYY');
converter para numeric com o to_number()SELECTto_number('2,454.8','9G999D9S');
extrair partes da data (ano, ms, ..., ms)SELECTdate_part('hour',now());
truncar data/hora at determinado campoSELECTdate_trunc('month',now());80
valor
padro79
Operadores de data e hora
Funes para lidar com seqncias
adio de data com intervalo ou horaSELECTdate'20010928'+7; SELECTdate'20010928'+ '4months'::interval; SELECTdate'20010928'+time'03:00';
criar seqncia temporriaCREATETEMPSEQUENCEseq_temp;
buscar valor atual (com e sem atualizao)SELECTnextval('seq_temp'); SELECTcurrval('seq_temp');prximo valor ser exatamente o especificado
subtrao de data com intervalo ou horaSELECTdate'20011001' date'20010928'; SELECTdate'20011001'7::int; SELECTtimestamp'2001092823:00' interval'22hours';81
forar atualizao do valorSELECTnextval('seq_temp');
SELECTsetval('seq_temp',100); SELECTsetval('seq_temp',100,false); SELECTnextval('seq_temp');82
Expresses condicionais: caseCASEWHENconditionTHENresult [WHEN...] [ELSEresult] END
Expresses condicionais: case IICASEexpression WHENvalueTHENresult [WHEN...] [ELSEresult] END
exemplo de utilizao
ATENO: apesar de til, evite utilizar este recurso!
exemplo de utilizaoSELECTnum, CASEnum%2 WHEN0THEN'par' ELSE'mpar' END FROMt1;
SELECTnum, CASE WHENnum%2=0THEN'par' ELSE'mpar' END FROMt1;
condio: par divisvel por 2
83
84
Expresses condicionais: coalesce
Funes de agregao
retorna o primeiro argumento no-nulo similar s funes NVL e IFNULL presentes em outros SGBDsSELECTcoalesce(rg,cpf)asdocumento, age(coalesce(datnasc,now()))ASidade FROMTB_Cliente;
retornam um nico valor atravs de um conjunto de entradas podem ser criadas pelo usurio (UDAs) ex: contagem, mnimo e mximoSELECTcount(1),count(*),count(num), min(num),max(num)FROMt1;
data de nascimento pode no estar cadastrada
SELECTprimeiro_nome||''|| coalesce(nome_meio,'')||sobrenomeAS nome_completo pessoa pode no FROMTB_Pessoa; possuir sobrenome85
ex: mdia, desvio padro, varinciaSELECTavg(num),stddev(num), variance(num)FROMt2;3 abordagens distintas para o COUNT()
86
Expresses de subconsulta: existsEXISTS(subquery)
Expresses de subconsulta: in, not inexpression[NOT]IN(subquery)
exemplo de utilizaoSELECTnum FROMt1 WHEREEXISTS( SELECT1 FROMt2 WHEREt2.num=t1.num );no precisa especificar campos ou *
retornar somente registros que estejam em outra tabela
SELECT*FROMt2 WHEREnumIN(SELECTnumFROMt1);
ATENO: evite utilizar se a subconsulta retornar muitos itens!
retornar somente itens que no estejam entre os valores determinadosSELECT*FROMt2 WHEREnumNOTIN(1,2,3);88
subconsulta que referencia linhas externas87
Funes de informao do sistema
Funes Exerccio 3a
verso e data/hora de inico da instnciaSELECTversion(), pg_postmaster_start_time();
criar a seqncia seq_boletos_id alterar a numerao inicial dela para 1001 verificar sua numerao atual criar e testar a funo rand():CREATEFUNCTIONrand()RETURNSintAS$$ SELECTtrunc(random()*100)::int $$LANGUAGEsql; SELECTrand();
endereo IP e porta (servidor e cliente)SELECT inet_server_addr(),inet_server_port(), inet_client_addr(),inet_client_port();
banco, esquema e usurio atuaisSELECTcurrent_database(), current_schema(),current_user, session_user,user;89
90
Funes Exerccio 3b
Funes Exerccio 3c
criar a tabela boletos, contendo os seguintes campos:
popular a tabela boletos executando:ALTERTABLEboletos ADDdummyint; INSERTINTOboletos(dummy) SELECT*FROMgenerate_series(1,1000);
id: inteiro, PK, default = valor da seqncia cliente: inteiro, obrigatrio, default = rand() linha: caracter(24), default =to_char(current_date - rand() + current_time + (rand()::text || 'sec')::interval, 'YYYYMMDDHH12MISS') || trim(to_char((random() * 1e5)::numeric(10,2), repeat('0', 10)))
incluir os seguintes campos na tabela:
dthr: data/hora, no obrigatrio valor: nmero decimal 10;2, no obrigatrio
200701031212450000068484ano ms dia hora min seg valor (2 decimais)
exemplo de linha
91
extrair os valores do campo linha e copi-los para dthr e valor, utilizando as funes de converso de tipos de dados92
Funes Exerccio 3d
listar os 10 maiores clientes, exibindo os valores totais e ordenados de modo decrescente exibir a mdia e o desvio padro dos valores dos boletos do ms de setembro exibir o menor e o maior valor de boleto do dia 01 de outubro de 2010 exibir os boletos cujo cdigo do cliente esteja na tabela t1 (campo num), ordenando pelo id e desconsiderando os 10 primeiros registros 93
Alterando dados
Antes de prosseguir o curso...
Incluindo linhas com INSERT
execute as seguintes instruesCREATETABLEprodutos( idinteger, nometext, preconumeric ); CREATETABLEbio(valint); CREATETABLEcontas( idserialPRIMARYKEY, saldonumericDEFAULT0.0 );95
inserindo uma linha por vez (ordenada)INSERTINTOprodutos VALUES(1,'Queijo',9.99);boa prtica!
especificando todas as colunasINSERTINTOprodutos(id,nome,preco) VALUES(2,'Manteiga',2.34); INSERTINTOprodutos(nome,preco,id) VALUES('Presunto',7.55,3);
omitindo determinadas colunasINSERTINTOprodutos(id,nome) VALUES(4,'Amendocrem');ainda existe?96
Incluindo linhas com INSERT II
Incluindo linhas com SELECT
as formas a seguir so extenses do PostgreSQL (fora do padro ANSI SQL) preenchendo com valores padresINSERTINTOprodutos VALUES(5,'Mortadela',DEFAULT); INSERTINTOprodutos DEFAULTVALUES;
possvel incluir linhas com base em uma consulta SQL! criando novos produtosINSERTINTOprodutos SELECTid+10,initcap(des), (random()*10)::numeric(10,2) FROMtabela;dica: execute antes somente o SELECT
especificando diversas linhas de uma vezINSERTINTOprodutosVALUES (6,'PoSrio',3.57), (7,'PoFrancs',0.28);s a partir da verso 8.2...
aumentando a populaoINSERTINTObio SELECTgenerate_series(1,10);a tabela duplicada a cada execuo
97
INSERTINTObioSELECT*FROMbio;
98
Criando tabelas com o SELECT
Incluindo linhas com COPYCOPYtablename[(column[,...])] FROM{'filename'|STDIN}
possvel criar uma tabela j populada com base em uma consulta SQL! criando uma tabela com parte dos boletosSELECTid,cliente,dthr,valor INTObol_min a tabela de FROMboletos destino pode ser temporria WHEREdummyimpcsv.sql