54
Introdução ao PostgreSQL: um pouco além dos comandos MARCOS THOMAZ DA SILVA

Curso de PostgreSQL: Um pouco Além dos Comandos

Embed Size (px)

DESCRIPTION

Minicurso de PostgreSQL, ministrado durante o evento Erin2013, explicando a criação de bancos de dados, triggers, procedures, views e comentando um pouco sobre melhoria de desempenho. Tudo com exercícios de Fixação

Citation preview

  • 1. Introduo ao PostgreSQL: um poucoalm dos comandosMARCOS THOMAZ DA SILVA

2. Breve HistricoDerivado do pacote POSTGRES escrito naUniversidade da Califrnia em Berkeley;Implementao do POSTGRES comeou em 1986;Em 1994 foi adicionado um interpretador SQL aoPostgres (Postgres95, antes usava PostQUEL);Deu origem ao Informix da IBM;Em 1996 recebeu o nome de PostgreSQL,ganhando diversos outros recursos. 3. No BrasilCaixa, BB e Serpro;Comunidade PGBR;Timbira e EnterpriseDB; 4. RecursosSub-Consultas;Integridade Referencial;Linguagem Procedural para Functions e Procedurespl/pgsql, pl/python, pl/perl, pl/java, pl/pascal, pl/ruby, pl/C,pl/RGatilhos (Triggers);Tipos Definidos Por Usurio;Esquemas (schemas);reas de Armazenamento (Tablespaces);Savepoints;Dados geoespaciais;Herana de tabelas; 5. Ambientes de GerenciamentoModo texto: psql;Modo grfico:pgAdmin III;EMS PostgreSQL Manager;Acqua Studio;Web: phpPgAdmin; 6. Criando o banco de dadosCREATE DATABASE [ WITH[ OWNER = ][TEMPLATE = ][ TABLESPACE = [ ENCODING = ][ LC_COLLATE = ][ LC_CTYPE = ] ];Exemplo:CREATE DATABASE testeerinWITH OWNER=curso_erinTEMPLATE = template0TABLESPACE = pg_defaultENCODING = 'UTF8'LC_COLLATE = 'Portuguese_Brazil.1252'LC_CTYPE = 'Portuguese_Brazil.1252'; 7. Criao de Usurios (Roles)Porque criar:Segurana (mesma regra do root);Separao de ambientes;Controle de acesso e execuo;O DBA pode realmente gerenciar o banco. 8. Criao de Usurios (Roles)Comando:CREATE ROLE LOGINPASSWORD [SUPERUSER] [INHERIT][CREATEDB/NOCREATEDB][CREATEROLE/NOCREATEROLE][REPLICATION/NOREPLICATION];Exemplo:CREATE ROLE cursopgerin LOGINPASSWORD cursopgerinSUPERUSER INHERIT CREATEDB CREATEROLENOREPLICATION; 9. Exerccio de Fixao 01Criar um banco de dados com o template0,encoding UTF8, collate (LC_CTYPE eLC_COLLATE) Portuguese_Brazil.1252, cujo nomeseja erin_. O usurio (role) criador dobanco deve ter o seu nome (apenas o primeironome), deve ser superusurio, poder criar outrosusurios, criar bancos, mas no deve poder realizarreplicao. A senha fica a sua escolha. 10. Tipos de Dadoschar / varchar (character / character varying);text;integer, smallint, bigint (2, 4 e 8 bytesrespectivamente);decimal, numeric, real e money;serial;date, time, timestamp (with/without timezone);boolean;bytea, lo;array;line, point, polygon;json, macaddr; 11. Tipo de dados cada um na sua...Como armazenar...Datas de Nascimento?Nomes de pessoas?Textos variveis (observaes)?Horrios?Valores monetrios?Nmero de CPF?Nmeros de Telefone? 12. Criao de TabelasO que so? Excel????Comando bsico...CREATE TABLE ( [] [DEFAULT ], [] [DEFAULT ],[CONSTRAINT ( )]);Exemplo:CREATE TABLE categorias(id serial NOT NULL,descricao character varying(80),CONSTRAINT categorias_pk PRIMARY KEY (id) )WITH (OIDS=TRUE); 13. Exerccio de Fixao 02No banco criado no exerccio 01, crie uma tabelachamada contatos com os seguintes campos:CAMPO TIPONOME CHARACTER VARYING(80)CIDADE CHARACTER VARYING(80)TELEFONE CHARACTER VARYING(20)VL_ULT_COMPRA NUMERIC(10,2)VL_DEBITO MONEY 14. Inserindo informaes nas tabelasA instruo utilizada para inserir informaes INSERTPermite adicionar (quase) qualquer tipo de dado dentrode uma tabela;Comando:INSERT INTO [ (Campo1, Campo2[, Campo3] )VALUES (,[,]) [ RETURNING ];ExemploINSERT INTO categorias (descricao) VALUES (Cermicas);OuINSERT INTO categorias (descricao) VALUES(Cermicas),(Roupas),(Frios),(Ferramentas) 15. Exerccio de Fixao 03Usando como base o exerccio anterior, insira osseguintes registros na tabela criada:NOME CIDADE VL_ULT_COMPRA VL_DEBITOFULANO RIO BRANCO 100 100CICLANO RBCO 80 80BELTRANO RIO BRANCO-AC 120.50 20.50NOCEGO RIO BRANCO-AC 30 60NOCEGO RIO BRANCO-AC 120 120Alm destes registros, insira mais 5 registros nessatabela, com o VL_DEBITO igual a 0. 16. Removendo Registros das TabelasComando DELETE utilizado;Comando:DELETE FROM [ WHERE ]ExemploDELETE FROM categorias;DELETE FROM contatos WHERE VL_DEBITO < 10;DELETE FROM clientes WHERE nome likeFULANO* 17. Exerccio de Fixao 04Usando como base a tabela utilizada noEXERCCIO 03, Resolva a seguinte situao:O dono da loja quer fazer uma limpeza em seu banco dedados e quer remover todos os registros de contatos queno possuam dbitos.Que comando deve ser executado???delete from Contatos where vl_debito=0delete from Contatos where vl_debito=0::money 18. Constraints / RestriesPRIMARY KEY Chave primria;UNIQUE KEY Chave nica;FOREIGN KEY Chave Estrangeira;CHECK Validao;QUAIS AS DIFERENAS ENTRE CHAVE PRIMRIA E CHAVENICA? 19. Alterando Estrutura de Uma TabelaMudanas Gerais na Tabela:Necessidade de Acrscimo de Campos;Necessidade de Retirada de Campos;Mudana de Tipos de Campos;Acrscimo de Constraints (Primary Key, ForeignKey, Unique Key, Check); 20. Alterando Estrutura de Uma tabelaExemplos:ALTER TABLE ct DROP COLUMN tipo;ALTER TABLE ct ADD COLUMN testescharacter(1);ALTER TABLE ct ADD CONSTRAINT ct_chk2CHECK (testes = 'S' or testes='N');ALTER TABLE ct ALTER COLUMN valor TYPEmoney;ALTER TABLE ct RENAME vlr TO valor; 21. Estrutura de Tabela Testando...A estrutura apresentada no Exerccio 02 atende aosdados que persistem nela??Selecione todos os registros que correspondam a RioBranco?Existem usurios duplicados na tabela?Os valores do campo cidade esto corretos? Soconfiveis?Na tabela existem 2 contatos chamados NOCEGO,como ficaria para excluir apenas um deles?Execute os seguintes comandos na tabela e veja o queocorre:insert into contatos (nome, VL_DEBITO) values (Teste01,1.250,25); 22. Exerccio de Fixao 05Modifique a tabela usada no exerccio anterior,realizando as seguintes operaes:Adicionando uma coluna para armazenar o estado (UF);Adicionar um campo para armazenar a data denascimento;Adicionar um campo do tipo SERIAL com o nome ID etorn-lo chave primria;Colocar o campo nome como Chave nica (realizando osajustes necessrios);Modifique o campo VL_ULT_COMPRA para o tipoMONEY; 23. Alterando Registros de Uma TabelaComando:UPDATE SET= [WHERE ]Exemplos:UPDATE categorias SET observacao=NULL;UPDATE contatos SET VL_DEBITO=0 WHEREVL_DEBITO < 10;UPDATE clientes SET nome=FULANO DE TALWHERE nome like FULANO*;UPDATE clientes SETVL_DEBITO=VL_DEBITO*1.1 WHERE VL_DEBITO >100 AND VL_ULT_COMPRA > 80; 24. Exerccio de Fixao 06Com base na tabela existente, altere os registrosexistentes, informando as datas de nascimento doscontatos;Adicione um juros de 20% para todos os contatos comVL_DEBITO maior ou igual a 100;Adicione um juros de 10% para todos os contatos comVL_DEBITO entre 60 e 100;Adicione um juros de 5% para todos com valoresmenores que 60;Crie uma tabela chamada cidades com os campos id(serial), nome (character varying(80) ) e uf (character(2)), adicione as cidades usadas na tabela contatos. Emseguida, insira na tabela contatos um campo chamadocidade_id. Altere-o para o valor das cidadescorrespondentes. Remova o campo cidade da tabelacontatos e ento defina o campo cidade_id como chaveestrangeira apontando para a tabela cidades. 25. Buscando Registros Parte 01Comando:SELECT FROM [WHERE ]ONDE: - Campos, Expresses ou * - Uma ou mais tabelas (separadas porvrgula) que tero seus valores representados nalistagem - Restries de filtragem dos registros.Se omitido, exibe todos os registros. 26. Exemplos SimplesSELECT * FROM categorias;SELECT ID, NOME FROM CIDADESSELECT * FROM CIDADES WHERE UF = ACSELECT nome, vl_debito,(vl_debito*1.10) as vl_deb_com_jurosFROM contatos;SELECT upper(a.nome) as contato,b.nome||-||b.uf as cidade FROMcontatos a, cidades b WHEREa.cidade_id=b.id AND b.uf = AC; 27. SELECT.... JOIN...O que so os JOINs ???Tipos:INNER JOIN;LEFT / RIGHTFULL JOIN; 28. SELECT ... JOIN .... - EXEMPLOSSELECT upper(contatos.nome) as contato,cidades.nome||-||cidades.uf as cidadeFROM contatos INNER JOIN cidades ONcidade_id=id WHERE b.uf = AC;SELECT * FROM CLIENTES C LEFT OUTERJOIN COMPRAS C2 ON C2.ID_CLIENTE = C.ID 29. SELECT...GROUP BYRealiza agrupamento de valores;Utiliza funes de agregao: SUM, MAX, MIN,COUNT, AVERAGE (AVG);As dimeses que aparecem na lista do select,devem ser adicionadas ao GROUP BYAgrupamento aceita funes condicionais (CASE); 30. SELECT ... GROUP BY - EXEMPLOSSELECT COUNT(*) FROM contatos;SELECT a.nome as cidade, COUNT(*) FROM cidadesa, contatos b WHERE A.id=b.cidade_id;SELECT a.nome as cidade, COUNT(*) FROM cidadesa INNER JOIN contatos b WHERE a.id =b.cidade_id;SELECT a.nome as cidade, COUNT(*) FROM cidadesa LEFT JOIN contatos b WHERE a.id =b.cidade_id;SELECT a.nome as cidade, COUNT(*) FROM cidadesa RIGHT JOIN contatos b WHERE a.id =b.cidade_id;SELECT a.nome, (select count(*) from contatosb WHERE a.id=b.cidade_id) FROM cidades a 31. SELECT.... E MAIS ALGUNS EXEMPLOSListar a quantidade de pessoas de uma determinadacidade, separando por sexo:select a.nome, SUM( case b.sexo WHEN M then 1else 0 end) as M, SUM( case b.sexo WHEN F then 1else 0 end) as F FROM cidades a LEFT JOIN clientesb ON b.cidade_id = a.cidade_id WHERE a.id=1Listar os clientes e seu dbito total, cujo dbito total maior que R$ 1.000,00:Select c.nome, sum(vl_debito) from clientes cgroup by c.nome having sum(vl_debito) > 1000 32. Mais do mesmo???Onde esto Schemas, Triggers, Procedures,Functions, ndices e TableSpaces???Pra que serve cada um??? 33. Cenrio 01A Universidade Federal do Acre possui um ERP quedentre seus mdulos podemos destacar: acadmico,financeiro, oramentrio, compras, frotas, RH,licitao, almoxarifado, espao fsico e contratos. Obanco de dados possui cerca de 800 tabelas.Como organizar essas tabelas para facilitar amanuteno?Como no se perder, nem inverter o nome detabelas com um ambiente to complexo?A tabela estrutura se refere a parte organizacionalou fsica???SCHEMAS 34. SchemasOs schemas so estruturas lgicas, usadas paraseparar o contedo de bancos de dados;Podem estar contidos dentro de schemas tables,views, procedures, etc;Se a tabela est em um schema, esse deve estar nopath do usurio que acessa o banco de dados.Comando:Create schema Financeiro;Create schema Academico; 35. Schemas select entre schemasPublic Visvel automaticamente para todos ; - Visvel apenas ao usurio (de formaexplcita);Exemplo, se tivermos os schemas academico e rh,criao de tabelas:Create table academico.pessoas (...);Create table rh.pessoas (...);Exemplo de select:select * from academico.pessoas p1,rh.pessoas p2 where p1.id=p2.id 36. SchemasPerguntas:E se eu quiser dar um select sem especificar o schema?set search_path=$user, public, rh, academico;Se eu estiver usando um schema, posso ter chavesestrangeiras apontando para uma tabela de outroschema?Posso ter tabelas com o mesmo nome e estrutura?E nesse caso, se eu tenho duas tabelas iguais, adicioneiambos os schemas no search_path, e digitar select *from tabela, como saber de qual schema so os dados? 37. Preparando para as regras de negcio...TABELA: categoriasCAMPO TIPO PK UK FK REFID SERIAL XDESCRICAO VARCHAR(80)TABELA: clientesCAMPO TIPO PK UK FK REFID SERIAL XNOME VARCHAR(80) XDTN DATEATIVO BOOLEANSEXO CHAR(1) 38. Preparando para as regras de negcio...TABELA: produtosCAMPO TIPO PK UK FK REFID SERIAL XNOME VARCHAR(80) XVL_COMPRA MONEYLUCRO NUMERIC(7,3)VL_VENDA MONEYEST_MINIMO INTEGERCATEGORIA_ID INTEGER X CATEGORIAS 39. Preparando para as regras de negcio...TABELA: pedidosCAMPO TIPO PK UK FK REFID SERIAL XCLIENTE_ID INTEGER X CLIENTESVL_COMPRA MONEYFPGTO CHAR(1)DESCONTO MONEYVL_TOTAL MONEYFECHADO BOOLEAN 40. TABELA: pedidos_itensCAMPO TIPO PK UK FK REFID SERIAL XPEDIDO_ID INTEGER X PEDIDOSPRODUTO_ID INTEGER X PRODUTOSQTD INTEGERVL_UN MONEY 41. REGRAS ----Tabela categoriasNo pode ter categorias com descries iguais;Tabela clientes:No podem haver clientes com o mesmo nome, nascidosno mesmo dia;A data de nascimento no pode ser nem maior nem iguala data atual;Se um cliente estiver inativo, ele no pode sofreralteraes;Sexo obrigatoriamente deve ser: M, m, F ou f 42. REGRAS ----Tabela produtos:No podem haver produtos com o nome repetido;Valor da compra deve ser menor ou igual ao valor davenda;Lucro deve ser maior ou igual a zero;Estoque mnimo deve ser maior ou igual a zero;Deve ser uma categoria vlida;Se o valor de venda no for informado, deve ser pego ovalor de compra e calculado o juros. 43. REGRAS -----Tabela pedidos:Fpagto s pode ser V (a vista) ou P (a prazo);Se o pedido estiver fechado porque foi quitado pelocliente e no pode sofrer alteraes;Valor total deve ser calculado pegando o valor dacompra, menos o desconto.O desconto deve ser maior ou igual a zero;O valor da compra deve ser pego automaticamente,conforme so adicionados os itens.Tabela pedidos_itens:Quantidade deve ser maior que zero;Valor Unit. Deve ser pego automaticamente da tabela deprodutos. 44. E AGORA??!Regras de negcio no banco? Simples... Triggers eprocedures! 45. TRIGGERSo automatizaes do banco de dados;So disparados a partir de eventos como insert,update e delete;Permitem a execuo de diversas outras operaes; 46. PROCEDURES / FUNCTIONSSo funes executadas dentro do banco de dados;Pode ser desenvolvidas em diversas linguagensPor padro, utilizasse PL/pgsql;Linguagem simples com selects dentro doscomandos;Permite retornos simples ou listas;No postgreSQL todos as triggers executam umafuno especial (trigger functions). 47. Modelo de Triggers no PostgreSQLTabelaComandoINSERTDispara aTrigger OnInsertExecuta a TriggerFunction 48. Functions / ProceduresComando:CREATE OR REPLACE FUNCTION ( ) RETURNS AS$BODY$;BEGINEND;$BODY$ LANGUAGE plpgsql; 49. Trigger FunctionsComando:CREATE OR REPLACE FUNCTION() RETURNS trigger AS$BODY$;BEGINRETURN ;END;$BODY$ LANGUAGE plpgsql; 50. TriggerCREATE TRIGGER {AFTER|BEFORE} {INSERT|UPDATE|DELETE}ON FOR EACH{ROW|STATEMENT}EXECUTE PROCEDURE(); 51. ExemploCreate table fornecedores(id serial not null,nome varchar(80),saldo money,CONSTRAINT pk_forne PRIMARY KEY (id));Create table venda(id serial not null,fornecedor_id integer,data date,valor money,CONSTRAINT pk_venda PRIMARY KEY (id),CONSTRAINT fk_venda FOREIGN KEY (fornecedor_id)REFERENCES fornecedores (id) ON UPDATE NO ACTION ONDELETE CASCADE); 52. Exemplo ContinuaoCreate or replace function ajusta_saldo()returns trigger AS$BODY$BEGINupdate fornecedorset saldo = coalesce(saldo,0)+NEW.valorwhere id = NEW.fornecedor_id;RETURN NEW;END$BODY$AS LANGUAGE plpgsql; 53. Exemplo ContinuaoCREATE TRIGGER tr_ajusta_saldo AFTERINSERT ON venda FOR EACH ROW EXECUTEPROCEDURE ajusta_saldo(); 54. PENSANDO EM DESEMPENHO...Criao de tablespacesO que so?Como cri-las?Quais as vantagens?Como us-las?ndices:Explain;Tipos de ndice;Views:Melhorando modelosRules;Desempenho ainda incomoda: Particionamento detabelas;