Baixe o app para aproveitar ainda mais
Prévia do material em texto
® IPM Informática Pública Municipal Programa Jovens Talentos 1 Módulo III – Banco de Dados ® IPM Informática Pública Municipal Programa Jovens Talentos 2 SUMÁRIO O que espera-se que o aluno saiba ao final deste módulo ................................................................................................................. 1 Conceitos de sgbdr e postgresql ......................................................................................................................................................... 1 Conceitos do sgbdr.......................................................................................................................................................................... 1 PosTgresql ....................................................................................................................................................................................... 2 Instalação do postgresql no windows ............................................................................................................................................. 2 Os arquivos postgresql.conf e pg_hba.conf ................................................................................................................................ 3 A ferramenta PGADMIN .................................................................................................................................................................. 3 Conectando a um servidor .......................................................................................................................................................... 3 Possíveis problemas de conexão ................................................................................................................................................ 4 Visualizando objetos ................................................................................................................................................................... 4 Criando um usuário ..................................................................................................................................................................... 5 Criando um database .................................................................................................................................................................. 5 Criando um schema .................................................................................................................................................................... 5 A ferramenta de edição de consultas ......................................................................................................................................... 5 Modelagem de dados ......................................................................................................................................................................... 7 O diagrama entidade relacionamento ............................................................................................................................................ 7 Dicas de modelagem de dados ................................................................................................................................................... 7 O padrão p006 – padrão de modelagem de dados da ipm ............................................................................................................. 9 Sql ....................................................................................................................................................................................................... 9 Conceitos gerais .............................................................................................................................................................................. 9 Técnicas de organização de código sql ......................................................................................................................................... 10 GERAL ........................................................................................................................................................................................ 10 Cláusula SELECT......................................................................................................................................................................... 11 Cláusula FROM .......................................................................................................................................................................... 11 Cláusula JOIN ............................................................................................................................................................................ 11 Cláusula WHERE ........................................................................................................................................................................ 11 Outras Dicas .............................................................................................................................................................................. 12 SQL - DML .......................................................................................................................................................................................... 12 Seleção de informações - SELECT .................................................................................................................................................. 12 Rótulos de colunas .................................................................................................................................................................... 14 Cláusula FROM/JOIN ................................................................................................................................................................. 14 SUB-SELECT ............................................................................................................................................................................... 16 SUB-SELECT X JOIN ............................................................................................................................................................................ 17 Operadores de agregação ......................................................................................................................................................... 19 Agrupamento – GROUP BY ....................................................................................................................................................... 20 HAVING ..................................................................................................................................................................................... 21 ® IPM Informática Pública Municipal Programa Jovens Talentos 3 Ordenação - ORDER BY ............................................................................................................................................................. 22 LIMIT e OFFSET .......................................................................................................................................................................... 23 Exercícios .................................................................................................................................................................................. 23 A cláusula where ....................................................................................................................................................................... 24 Operadores lógicos ........................................................................................................................................................................... 25 Operadores de comparação .............................................................................................................................................................25 Operador in ....................................................................................................................................................................................... 26 Operador EXISTS ............................................................................................................................................................................... 26 Expressão condicional – CASE ................................................................................................................................................... 27 Expressão condicional – COALESCE .......................................................................................................................................... 29 Exercícios .................................................................................................................................................................................. 29 Conversão de tipos ....................................................................................................................................................................... 30 Operadores de formação de dados .............................................................................................................................................. 31 Operações com conjuntos - UNION, INTERSECT E EXCEPT ........................................................................................................... 31 Exercícios ...................................................................................................................................................................................... 32 Inserção de informações – INSERT................................................................................................................................................ 32 Exercícios .................................................................................................................................................................................. 33 Alteração de informações – UPDATE ............................................................................................................................................ 37 Exercícios .................................................................................................................................................................................. 38 Exclusão de dados – DELETE / TRUNCATE .................................................................................................................................... 38 Exercícios .................................................................................................................................................................................. 38 SQL – DDL .......................................................................................................................................................................................... 39 Organização de objetos no POSTGRESQL ..................................................................................................................................... 39 Usuários .................................................................................................................................................................................... 39 Database ................................................................................................................................................................................... 40 SCHEMA .................................................................................................................................................................................... 41 O schema publico (PUBLIC) ............................................................................................................................................................... 41 Tabela ........................................................................................................................................................................................ 41 Exercícios .................................................................................................................................................................................. 43 Chaves primárias (PK) ............................................................................................................................................................... 43 Chave estrangeria (FK) .............................................................................................................................................................. 44 Índices ....................................................................................................................................................................................... 46 Exercícios .................................................................................................................................................................................. 48 Views ......................................................................................................................................................................................... 48 Funções e gatilhos ..................................................................................................................................................................... 50 POSTGRESQL – Administração básica ............................................................................................................................................... 51 ® IPM Informática Pública Municipal Programa Jovens Talentos 4 Server status ............................................................................................................................................................................. 51 Explicando uma consulta - modo gráfico .................................................................................................................................. 52 Colunas do sistema ................................................................................................................................................................... 53 Controle de transações ............................................................................................................................................................. 54 backup ....................................................................................................................................................................................... 55 restore ....................................................................................................................................................................................... 55 O QUE ESPERA-SE QUE O ALUNO SAIBA AO FINAL DESTE MÓDULO Espera-se que o aluno conheça os conceitos básicos de um SGBD e seus componentes. Entenda o que é um modelo no paradigma relacional baseado em um DER e o padrão P006 da IPM para modelagem de dados. Conheça os conceitos da linguagem SQL, suas divisões (DDL e DML), além da estrutura básica dos comandos e a melhor forma de organização dos mesmos. Deverá também conhecer a fundo as principais instruções SQL (INSERT, UPDATE, DELETE, TRUNCATE, SELECT, JOIN, DISTINCT, GROUP BY, ORDER BY, HAVING, LIMIT, OFFSET, UNION, INTERSECT, EXCEPT, CREATE, ALTER, DROP, EXPLAIN, CURSOR, SAVEPOINT). Conceitos e diferenças de triggers e functions para o banco de dados. Entenda como funciona o controle de transações do SGBD e conceitos de administração básica (como backup, restore, explain). CONCEITOS DE SGBDR E POSTGRESQL CONCEITOS DO SGBDR A Sigla SGBD vem de Sistema Gerenciador de Banco de Dados. Trata-se de um software que exclusivamente irá comandar o relacionamento entre o Software aplicativo e os dados do banco de dados, principalmenteatravés da interpretação e execução de comandos SQL (linguagem que é exposta mais à frente). Banco de dados (ou base de dados), é um conjunto de registros dispostos em estrutura regular que possibilita a reorganização dos mesmos e produção de informação. Um banco de dados normalmente agrupa registros utilizáveis para um mesmo fim. Um banco de dados é usualmente mantido e acessado por meio de um software conhecido como Sistema Gerenciador de Banco de Dados (SGBD). Normalmente um SGBD adota um modelo de dados, de forma pura, reduzida ou estendida. ® IPM Informática Pública Municipal Programa Jovens Talentos 5 O modelo de dados mais adotado hoje em dia é o modelo relacional, onde as estruturas têm a forma de tabelas, compostas por tuplas(linhas) e colunas (veja mais de DER). Um Sistema de Gestão de Bases de Dados, (SGBD) não é nada mais do que um conjunto de programas que permitem armazenar, modificar e extrair informação de um banco de dados. Há muitos tipos diferentes de SGBD. Desde pequenos sistemas que funcionam em computadores pessoais a sistemas enormes que estão associados a mainframes. Um Sistema de Gestão de Base de Dados implica a criação e manutenção de bases de dados, elimina a necessidade de especificação de definição de dados, age como interface entre os programas de aplicação e os arquivos de dados físicos e separa as visões lógica e de concepção dos dados. Assim sendo, são basicamente três as componentes de um SGBD: 1. Linguagem de definição de dados (DDL) (especifica conteúdos, estrutura a base de dados e define os elementos de dados); 2. Linguagem de manipulação de dados (DML) (para poder alterar os dados na base); 3. Dicionário de dados (guarda definições de elementos de dados e respectivas características – descreve os dados, quem os acede, etc.) Por outro lado, um SGBD é um software muito complexo que não apenas se preocupa em armazenar ou buscar os dados dos mais diversos locais, mas também de todo o comportamento definido para o banco de dados durante a sua implementação, como os relacionamentos entre as tabelas, os gatilhos que devem ser disparados para cada evento que ocorre, etc. POSTGRESQL O PostgreSQL é um SGBDR – Sistema Gerenciador de Base de Dados Relacional (ou mais comumente indicado, do inglês RDBMS – Relational Database Management System) que está baseado nos padrões SQL ANSI-92, 96 e 99, de alta performance, de fácil administração e utilização em projetos. Ele permite a utilização de SQL, Triggers e toda a gama de programação e construção pertinente aos mais famosos RDBMs do mercado (como o Oracle). Além de permitir “Embedded SQL” como pré-compilação (com as linguagens C e C++) e possuir drivers ODBC e JDBC para interface com ambientes e linguagens de programação. O PostgreSQL começa a sua construção em meados de 1996, na Universidade Norte-Americana da Califórnia, a Universidade de Berkley (a partir do seu descendente o Ingres). Desde então, com quase 18 anos de experiência o PostgreSQL vem sendo aprimorado com muitos benefícios e funcionalidades. O PostgreSQL é um descendente de código fonte aberto deste código original de Berkeley. É suportada grande parte do padrão SQL:2003 Devido à sua licença liberal, o PostgreSQL pode ser utilizado, modificado e distribuído por qualquer pessoa para qualquer finalidade, seja privada, comercial ou acadêmica, livre de encargos. INSTALAÇÃO DO POSTGRESQL NO WINDOWS A instalação do PostgreSQL no windows é bem simples, através de um assistente todo o processo de instalação é automatizado, bastando informar a este instalador para prosseguir nas etapas que são exibidas. A seguir são mostradas as principais telas que fazem interface entre a instalação e o usuário: Na primeira etapa informe apenas o idioma de preferência, o idioma Português do Brasil está disponível. O idioma selecionado aqui é apenas para uso do instalador. As duas próximas etapas são apenas informativas, sendo que a terceira etapa mostra um termo com informações sobre o PostgreSQL e o grupo postgresql.org, clique em próximo para prosseguir. ® IPM Informática Pública Municipal Programa Jovens Talentos 6 Na etapa seguinte são exibidas opções de instalação, por padrão as opções que já vem marcadas são utilizadas, não selecionando nenhuma outra opção. Da mesma forma a pasta de instalação que é informada pelo instalador pode ser utilizada para instalação, que por padrão fica em C:\ARQUIVOS DE PROGRAMAS\POSTGRESQL\8.X\. Na etapa que segue, deverá ser informada uma senha que serve para configurar um usuário de sistema operacional no computador local. Este usuário ficará invisível e será utilizado apenas pelo PostgreSQL para iniciar os serviços necessários. A senha a ser informada poderá ser definida no momento da instalação, como se trata de um usuário do sistema operacional que ficará invisível pode-se informar uma senha básica (por exemplo: 123456). Quando clica-se em Próximo e a senha não foi definida o instalador perguntará se ele deve definir uma, caso seja escolhida a opção sim, a senha a ser criada poderá ser anotada na mensagem que será exibida pelo instalador. Etapa de criação do usuário de SO. Na etapa seguinte serve basicamente para iniciar o serviço do PostgreSQL, selecionando configurações básicas de cluster, como porta, se aceita conexões externas, o locale (que é a definição de regionalismo para a instalação do postgreSQL, o padrão é C), a codificação do banco de dados (padrão LATIN1, para banco de dados IPM) e o nome do superusuário (do banco de dados) que deve ser postgres. Informe a senha para este usuário. A senha aqui deve ser sigilosa pois o usuário postgres possuirá todos os privilégios sobre todos os bancos de dados do cluster. ® IPM Informática Pública Municipal Programa Jovens Talentos 7 Etapa de criação de usuário de BD. Na etapa final são apresentadas informações coletadas durante as etapas anteriores do instalador e opção de concluir deve ser acionada. Pronto! A instalação do PostgreSQL foi realizada com sucesso, neste momento já é possível fazer acesso ao banco de dados utilizando a ferramenta pgAdminII. A instalação do PostgreSQL no Linux é feita seguindo outras instruções que estão disponíveis em ftp.ipm.com.br/util. OS ARQUIVOS POSTGRESQL.CONF E PG_HBA.CON F O arquivo PostgreSQL.conf e o arquivo pg_hba.conf ficam localizados na pasta onde a basta DATA está localizada. Esta pasta geralmente fica dentro da pasta de instalação do SGBD, mas pode ser modificada. São arquivos de configuração que possuem todos os parâmetros disponíveis e possíveis de serem customizados para a instância do SGBD instalada. No arquivo PostgreSQL.conf estão definidos dados genéricos de configuração, os principais itens de configuração estão listados abaixo: Parâmetro Descrição listen_addresses Indica qual a regra geral para escuta IPs de entrada. Pode-se definir um host específico que será o único a acessar o SGBD ou então todos através do * Port Porta que o SGBD está escutando no servidor. Por padrão é 5432 max_connections Indica o número máximo de conexões simultâneas disponíveis para o servidor. Padrão 100 No arquivo pg_hba.conf estão definidas as regras de conexão ao SGBD. Pode-se através dele determinar quais as máquinas (hosts) que podem fazer acesso ao SGBD, incluindo definir a política de quais databases podem acessar e através de quais usuários do SGBD. Para cada linha de configuração pode-se definir o método de autenticação, podendo ser TRUST (confiável, não exige senha para acesso) ou MD5 (seguro, exige senha para autenticação). A FERRAMENTA PGADMIN ftp://ftp.ipm.com.br/util ® IPM Informática Pública Municipal Programa Jovens Talentos 8 Toda aplicação utilizada para acessar a camada de softwares do SGBD é conhecida por cliente ou console de acesso. Os consoles de acesso geralmente permitem que sejam registrados um ou vários servidorese permite a navegação entre os databases e seus objetos. Além disso pode-se executar instruções SQL como forma de consulta e/ou manutenção do banco. Abaixo é mostrada a tela principal do pgAdminIII. Neste caso é mostrado que existem 45 databases (Bancos de Dados) cadastrados, 3 TableSpaces (Espaços de Tabelas) configurados e 388 Users (Utilizadores ou Usuários), na área marcada como 1. Na área 6, são mostradas informações detalhadas sobre o servidor selecionado, como descrição, nome da máquina (IP) e porta. Na parte inferior da tela são exibidos códigos SQL quando for o caso. 1. Indica qual o servidor que está conectado no momento; 2. Serve para selecionar um ou mais bancos de dados para utilização bem como informa a quantidade de bancos de dados que estão disponíveis para visualização na seção; 3. Os tablespaces criados estão disponíveis nesta visualização com possibilidade de manutenção; 4. Mostra os grupos criados e/ou disponíveis para utilização no PostgreSQL; 5. Lista de usuários que estão criados no servidor disponíveis para utlização. Na lista de usuários existirão tanto usuários de sistema como usuários comuns do banco de dados. Para qualquer objeto selecionado pode-se acessar funções disponíveis através do menu pop-down, visível através do botão direito do mouse sobre o objeto. Para cada objeto um menu é acionado, com as funções disponíveis. CONECTANDO A UM SERVIDOR Para registrar um novo servidor no pgAdmin, clique no menu "Ficheiro/Adicionar Servidor...", conforme demonstrado abaixo: ® IPM Informática Pública Municipal Programa Jovens Talentos 9 Será exibida a seguinte tela solicitando informações para conexão: 1. Informe o nome do servidor, este nome pode ser qualquer um, serve para identificar o servidor posteriomente; 2. Informe neste campo o IP do servidor; 3. Neste campo é solicitada a porta em que o PostgreSQL está instalado e sendo executado, a porta padrão do PostgreSQL é 5432, porém esta pode mudar de instalação para instalação. 4. Informe neste campo a configuração para SSL (acesso seguro), por padrão esta opção fica em branco; 5. Informe o nome do banco de dados de manutenção, que é o primeiro banco de dados que será acessado quando conecta-se ao servidor. Por padrão coloca-se pgipm ou então pode-se colocar postgres, conforme mostra na figura; 6. Informe o nome do usuário de acesso ao banco de dados (não o usuário do servidor). Se a configuração do servidor não exige autenticação por senha, pode-se utilizar o usuário postgres, caso exija deverá ser informada a senha do usuário postgres; 7. Neste campo deve-se inserir a senha de acesso do usuário do banco de dados. Se for o primeiro acesso a senha e o nome do usuário (6) devem ser os que foram informados na instalação do banco de dados; 8. Selecione esta opção caso deseja armazenar a senha de acesso para uma próxima seção; ® IPM Informática Pública Municipal Programa Jovens Talentos 10 9. Caso você deseja que o pgAdmin restaure informações da última seção, selecione esta opção; 10. Informe neste campo uma condição (SQL) válida para seleção de database na seção do pgAdmin. Ex: datname IN ('live_db', 'test_db'); 11. Serviço é utilizado para especificar parâmetros de controle de serviços do processo do banco de dados (depende do sistema operacional). Por padrão fica em branco; 12. Marque esta opção caso deseja conectar imediatamente no banco de dados quando clicar em OK. POSSÍVEIS PROBLEMAS DE CONEXÃO Ao realizar a primeira conexão podem acontecer alguns problemas, conforme relacionado abaixo: could not connect to Server. Connection refused: Este erro ocorre por que o servidor especificado não está aceitando conexão seja por que o ip foi informado errado ou na configuração do servidor não está aceitando conexões esternas, verifique a configuração da instalação do servidor de banco de dados; FATAL: no pg_hba.conf entry: Este erro é exibido caso no arquivo de configuração de acesso pg_hba.conf, não esteja informada uma regra que possibilite o cliente (computador local) se conectar ao host (servidor). Em caso de erro o pgAdminIII exibe mensagens como abaixo, que exibem o erro com clareza, e explicam os motivos pelos quais os erros ocorrem. VISUALIZANDO OBJETOS Uma parte muito importante que compete aos administradores de bancos de dados é a visualização dos objetos do banco e sua manutenção, além dos processos de Vacuum e Backup já comentados, existem ainda outras importantes estratégias de manutenção que devem ser seguidas como a constante verificação de índices, chaves primárias e constraints dos objetos do banco de dados. O pgAdmin fornece visualização simples e rápida do estado dos objetos, para que medidas possam ser adotadas pelo administrador no sentido de corrigir falhas. Na imagem mostrada abaixo é exibida uma tabela e suas constraints (chave ® IPM Informática Pública Municipal Programa Jovens Talentos 11 primária, índice e campos) (1), são exibidas também informações sobre o objeto atualmente selecionado (2) e o código SQL de criação do objeto (3). Para cada objeto selecionado pode-se clicar com o botão direito do mouse sobre o mesmo para que um menu pop- down seja mostrado com as opções disponíveis. CRIANDO UM USUÁRIO Por padrão o PostgreSQL possui o usuário postgres para acesso ao sgbd, mas pode-se estabelecer e manter uma política de acesso e usuários a nível de SGBD conforme necessidade. Para criar um usuário utilizando a ferramenta pgAdmin, na visualização de objetos, vá até o item usuários e clique com o botão direito do mouse sobre o mesmo, escolha a opção Novo Usuário... Informe o nome do usuário, no campo ID deixe em branco, pois o banco criará um ID automático, informe a senha e, se necessário, informe uma data para expiração da senha. Na parte inferior da tela informe se é usuário supervisor (marcando a opção Superusuário) e se o mesmo tem privilégios necessários para criação de banco de dados. CRIANDO UM DATABASE Para criar um database no pgAdmin, basta clicar com o botão direito do mouse sobre o item Banco de Dados, na visualização de objetos e em seguida na opção Novo Banco de Dados..., ou então clicar no botão (na barra de ferramentas) com o item banco de dados selecionado. ® IPM Informática Pública Municipal Programa Jovens Talentos 12 Ao clicar na opção Novo Banco de Dados, a seguinte tela será exibida: 1. Informe um nome para o banco de dados, não podem ser utilizados caracteres acentuados nem espaços. Ao informar um nome com letras maiúsculas e minúsculas, o nome do banco passa a ser case sensitive (sensitivo ao texto), ou seja toda vez que o banco for referenciado em códigos SQL deve ser informado com o nome igual (considerando letras maiúsculas e minúsculas). Por padrão recomenda-se criar um nome com todas as letras em minúsculo para evitar problemas futuros; 2. Informe o dono do banco de dados (owner) que deverá ser um usuário previamente configurado; 3. Na codificação utilizamos padrão SQL_ASCII, que irá aceitar todos os caracteres possíveis, outros formados (como UTF8 ou LATIN) podem não aceitar determinados conjuntos de caracteres; 4. Modelo a ser utilizado como base para criação do database, pode ser escolhido qualquer outro database previamente configurado no servidor, utiliza-se por padrão template1 ou template0 (quando template1 está sendo utilizado); 5. Informe o tablespace a ser utilizado para gravar os dados fisicamente do banco de dados. O PostgreSQL ao ser instalado por padrão já cria um tablespace chamado default, este tablespace é definido como padrão para utilização, dependendo da configuração do servidor podem existir outros tablespaces configurados. Veja mais informações no tópico Tablespace; 6. No campo schema restriction informe nomes de schemas que não podem ser visualizados. Já no campo comentário informe um texto para identificação do bancode dados, este texto é exportado com os dados em backups. CRIANDO UM SCHEMA ® IPM Informática Pública Municipal Programa Jovens Talentos 13 Para criar um schema utilizando o pgAdmin, clique com o botão direito do mouse sobre o item selecionado banco de dados, na visualização de objetos e selecione a opção Novo Objeto, em seguida no menu da direta clique em Novo Esquema... Para criar um schema basta informar o seu nome e o dono, o nome do schema vale a mesma regra de nomes do banco de dados. A FERRAMENTA DE EDIÇÃO DE CONSULTAS O pgAdminIII disponibiliza uma ferramenta completa para edição de consultas (queryes), com possibilidades avançadas como verificação de performance (explain), mensagems detalhadas de erros, e histórico de execução de códigos SQL. Existem também outras funcionalidades como, Macros, Favoritos, etc. 1. Barra de ferramentas (menu), disponibiliza as funções para execução de códigos SQL; 2. Mostra em qual conexão (banco de dados) está se executando o comando; ® IPM Informática Pública Municipal Programa Jovens Talentos 14 3. Área de execução de códigos SQL. Conforme mostrado na imagem os quadros mostram o final de linha, isto pode ser habilitado/desabilitado no menu Visualizar/Fins de linha. Outras opções de visualização estão disponíveis para edição de códigos; 4. Neste painel podem ser inseridos comentários ou textos para anotações ref. ao trabalho atual; 5. Abas, que mostram as opções disponíveis para esta tela. Na aba Saída de Dados estão disponíveis os dados que retornaram da consulta, na ava Mensagens são exibidas mensagens de erro e na ava Histórico são exibidos os códigos recentemente executados; 6. Na aba Explain pode ser exibida uma explicação do banco de dados sobre a execução do comando atual, para exibir a explicação pressione F7 ou vá no menu Consultas/Explicação. Para executar apenas um bloco de comandos basta selecioná-lo e clicar no botão para executar ou pressionar CTRL+E, conforme mostrado abaixo: ® IPM Informática Pública Municipal Programa Jovens Talentos 15 MODELAGEM DE DADOS O DIAGRAMA ENTIDADE RELACIONAMENTO As estruturas necessárias para armazenar os dados no banco de dados são conhecidas como estruturas de dados e podem ser representadas por diagramas como o DER (Diagrama Entidade Relacionamento). Por motivos de facilidade de manuseio/uso, performance, pesquisas, etc., faz-se necessária uma boa modelagem de dados para armazenamento dos mesmos no banco de dados. A estruturação de um banco de dados começa com uma definição básica (porém objetiva) dos dados necessários para armazenamento no banco de dados. Isto é conseguido em parte através da análise realizada pelos analistas, na fase de concepção do software aplicativo de banco de dados. Em seguida deve-se estabelecer um primeiro modelo de dados, chamado modelo conceitual, onde leva-se para um diagrama exatamente a estrutura que está definida na análise - bruta - sem qualquer normalização. O próximo passo é a definição de um modelo lógico de dados, onde devem ser definidas as entidades mais importantes e aplicadas regras de relacionamento entre elas, deve-se também nesta fase resolver problemas de normalização. Por fim, deve-se criar o modelo físico que considere todas as características do SGBD alvo. Muitos problemas encontrados após a implementação de um banco de dados têm sua origem em uma modelagem de dados feita sem uma análise mais profunda do escopo do projeto. Outro fator que colabora para uma má modelagem é o desconhecimento de técnicas como normalização e desnormalização de tabelas, bem como a não adoção de integridade referencial, que auxiliam na definição mais ajustada do modelo à realidade que atenderá. O modelo de dados (conhecido como MER ou DER), é muito importante para guiar o processo de construção de queryes de consulta, dentre as funções do MER destacamos: Tabelas/Entidades e Campos/Atributos Tipos de dados Ligações/Relacionamentos entre tabelas Chaves Primárias Chaves Estrangeiras DICAS DE MODELAGEM DE DADOS 1. Sempre faça modelagem de dados, isso ajuda no entendimento do problema e no planejamento de uma solução mais aderente aos seus objetivos. O objetivo do modelo conceitual é a definição do problema, não a solução; 2. Elimine qualquer redundância de dados. Redundâncias permitidas são aquelas relativas as chaves estrangeiras, que fazem referência à chave primária de outra tabela. Por exemplo, não se deve repetir nomes/descrições em tabelas relacionadas, pois eles podem ser facilmente obtidos através de relacionamentos; 3. Utilizar um padrão para dar nomes a entidades. Normalmente, nomes de entidades são no singular; 4. Atribuir nomes significativos a entidades, atributos e relacionamentos. Nomes que não representam seu real objetivo dificultam a compreensão do modelo; 5. Deve-se determinar os relacionamentos e, decidir como é a relação de dependência entre cada entidade é sempre importante. Os tipos de relacionamentos podem ser: a. 0:1 (mínimo: nenhum - máximo: um); b. 0:N (mínimo: nenhum - máximo: muitos); c. 1:1 (mínimo: um - máximo: um); d. 1:N (mínimo: um - máximo: muitos); e. N:N (mínimo: muitos - máximo: muitos); 6. Relacionamentos N:N ou que possuem atributos normalmente geram novas tabelas no modelo lógico. O nome do relacionamento pode ser utilizado como nome da tabela e deve ser cuidadosamente escolhido; ® IPM Informática Pública Municipal Programa Jovens Talentos 16 7. Atenção especial para entidades desconectadas no diagrama. Podem não ser um problema, mas precisam ser verificadas. Por exemplo, é comum que entidades que representem parâmetros estejam soltas no diagrama. 8. Definir corretamente a obrigatoriedade para atributos das entidades de forma a retratar o objetivo da entidade. Por exemplo, o nome de cliente deve ser obrigatoriamente preenchido, pois não faz sentido cadastrar um cliente sem seu nome. Muitas vezes preocupa-se apenas com obrigatoriedade de atributos chave, mas esta questão é importante para todos os atributos, tomando-se o devido cuidado de não impor restrições demais que impeçam que novos registros possam ser inseridos; 9. Não é recomendado armazenar o resultado de cálculos nas tabelas. O correto é que o cálculo seja gerado sob demanda, normalmente em consultas, relatórios, etc; 10. Toda tabela deve ter uma chave primária, que pode ser simples ou composta. A chave primária é o identificador do registro e deve ser única dentro de uma tabela; 11. Ao determinar a chave primária, deve-se escolher, em cada tabela, quais colunas formarão a chave primária. Para uma coluna ser candidata à chave primária, deverá atender aos principais requisitos: a. Deverá ser a menor possível; b. O seu valor deverá ser diferente de vazio ou zero (not null); c. Deverá ser de preferência numérica; d. O seu valor deverá ser único para toda a tabela; 12. Chaves estrangeiras devem corresponder a chaves primárias da relação associada ou ser nulas, quando não for um campo obrigatório; 13. Relacionamentos são representados por chaves estrangeiras, ou Foreign Keys (FK), atributos correspondentes à chave primária de outra tabela, estabelecendo a base para a integridade referencial. No exemplo abaixo o atributo PESCODIGO chave primária da tabela TBPESSOA, foi repetido na tabela TBPESSOACONTATO para representar o relacionamento. Desta forma, um contato deve estar presente na tabela TBPESSOA através do código da pessoa. 14. Relacionamentos 1:1 podem ser mapeados numa única tabela (quando possuem a mesma chave primária), em duas tabelas (quando as chaves primárias são diferentes e um dos lados do relacionamento é obrigatório) ou em três tabelas (quando o relacionamento é opcional em ambos os lados). Caso a pessoa possa possuir apenas três contatos pré-determinados, poderiamos resolver a situação na própria tabela TBPESSOA. Quando uma pessoapode possuir vários contatos, precisaremos de uma tabela a mais para armazenar esses contatos. No caso 2 um imóvel deverá ser de uma pessoa. Neste caso pode-se utilizar uma tabela para armazenar os dados do imóvel e outra para os dados da pessoa: ® IPM Informática Pública Municipal Programa Jovens Talentos 17 No caso 3 teremos várias pessoas que devem possuir vários imóveis (N:N), porém o imóvel não precisa ser de alguém e a pessoa não precisa ter imóvel, neste caso se faz necessária a criação de uma terceira tabela entre TBPESSOA e TBIMOVEL para armazenar este relacionamento: 15. Sempre utilizar normalização dos dados. É uma forma de verificar sua qualidade, diminuindo redundâncias e incoerências no modelo de dados. O processo de normalização aplica uma série de regras sobre as tabelas de um modelo para verificar se estas estão corretamente planejadas. Embora existam seis formas normais, ou regras de normalização (são cinco Formas Normais e a Forma normal de Boyce-codd), normalmente utiliza-se as três primeiras formas normais; 16. A primeira forma normal, preocupa-se em eliminar atributos compostos. Assim, atributos como Endereçco devem ser decompostos em Logradouro, Número, Complemento, Bairro, Cidade, Estado, CEP; 17. A primeira forma norma também se refere a eliminar valores multivalorados, no caso de pessoa e contatos da pessoa, se a tabela TBPESSOA possuísse apenas o campo PESTelContato, como mostrado no exemplo anterior, estaria errado, neste caso poderiam ser adotadas duas soluções: 1 - criar vários campos com telefones úteis para cada pessoa; 2 - criar uma outra tabela que armazene os dados de contato (como é o caso do nosso modelo); 18. A segunda forma normal determina que atributos não chave devem ser funcionalmente dependentes apenas da chave primária, ou seja, deve-se analisar se todo atributo que não é chave primária depende totalmente dela. Assim, não é necessário se preocupar com esta forma normal para tabelas que tenham chaves primárias simples, com apenas um atributo. No exemplo abaixo esta situação fica evidente, não está correto os campos TAXLARGURA e TAXCOMPRIMENTO, na tabela TBTAXA, já que são informações do imóvel, devem estar em TBIMOVEL. 19. A terceira forma normal refere-se a atributos não chave mutuamente independentes, ou seja, que não dependam um do outro. Desta forma, é verificado se existe dependência funcional entre atributos não chave. No exemplo abaixo, a primeira representação de TBPESSOA possui os campos LOGCODIGO e LOGNOME, são campos dependentes (considere que a tabela TBLOGRADOURO não exista neste momento), esta representação está nas primeira e segunda formas normais. A segunda representação, porém é a correta, pois aplicando-se a terceira forma normal, os atributos LOGCODIGO e LOGNOME transforma-se em uma outra tabela TBLOGRADOURO. ® IPM Informática Pública Municipal Programa Jovens Talentos 18 20. Utilizar uma ferramenta CASE para modelagem de dados. Caso contrário, o modelo tenderá a ficar desatualizado rapidamente, pois dificilmente as modificações que são necessárias no esquema de dados são refletidas para a documentação sem o auxilio de uma ferramenta apropriada. O PADRÃO P006 – PADRÃO DE MODELAGEM DE DADOS DA IPM A IPM possui o documento P006 que trata de um padrão de modelagem de dados adotado por toda a fábrica do novo software Atende.NET Web. Este documento tem por objetivo definir padrões para o desenvolvimento de modelos de dados, lógico e conceitual, quanto a sua definição, ferramenta de desenvolvimento e deverá ser utilizado por todos os desenvolvedores e analistas como base na construção de modelagem. O documento está logicamente dividido em áreas de interesse. O padrão P006 encontra-se disponível através do arquivo p006_-_padro_para_modelagem_de_dados.pdf e também no repositório central do dicionário. SQL CONCEITOS GERAIS SQL vem do inglês Structured Query Language (em português Linguagem Estruturada de Consulta), linguagem desenvolvida pela IBM que usa comandos simples, baseados em palavras em inglês, para realizar buscas em bancos de dados. É suportado por várias plataformas de bancos de dados. Uma entrada SQL é constituída por uma seqüência de comandos. Um comando é composto por uma seqüência de termos (tokens), terminada por um ponto-e-vírgula (“;”). O fim do fluxo de entrada também termina o comando. Quais termos são válidos depende da sintaxe particular de cada comando. Nos exemplos abaixo, o comando com ";" irá executar corretamente. Já o comando sem o ";" poderá não funcionar se não for o único comando da seqüência a ser executado. SELECT * FROM SCHEMA.TABELA; SELECT * FROM SCHEMA.TABELA Um termo pode ser uma palavra chave, um identificador, um identificador entre aspas, um literal (ou constante), ou um caractere especial. Geralmente os termos são separados por espaço em branco (espaço, tabulação ou nova-linha), mas não há necessidade se não houver ambigüidade (normalmente só acontece quando um caractere especial está adjacente a um termo de outro tipo). Exemplos de identificador. SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ADD, ETC. Além disso, podem existir comentários na entrada SQL. Os comentários não são termos, na realidade são equivalentes a espaço em branco. --EXEMPLO DE COMENTÁRIO EM SQL Abaixo é exibida uma estrutura padrão de um comando SQL, podendo este variar de acordo com a seleção de dados necessária bem como a estrutura de tabelas do banco de dados em questão. 1 - Definição da lista de campos da seleção SELECT CAMPO1, ...., CAMPON 2 - Definição da origem dos campos/dados ® IPM Informática Pública Municipal Programa Jovens Talentos 19 FROM TABELA1, ..., TABELAN 3 - Definição das junções com outras tabelas (quando não definido no FROM) [LEFT|INNER|RIGTH|FULL] JOIN TABELAX ON TABELAX.CAMPO1 = TABELA1.CAMPO1 4 - Definição de restrições de seleção WHERE TABELA1.CAMPO1 > VALORX AND [CONDICAON] 5 - Definição de agrupamentos GROUP BY TABELA1.CAMPO1, TABELAX.CAMPOX 6 - Definição de restrição sobre operador de agregação HAVING COUNT(*) > 0 7 - Definição da ordem dos dados ORDER BY TABELA1.CAMPO1 8 - União/Interseção/Exclusão com outro comando SQL com o mesmo número de colunas [UNION|INTERSECT|EXCEPT] [ALL] Para os nomes de objetos (Tabelas, Colunas, etc), vale a regra de literal, ou seja, um campo com o nome "Opção", informado entre aspas (") tem seu nome exatamente igual a "Opção", neste caso o campo opcao não existe para o banco de dados. Porém se o campo é criado com o nome OPCAO, poderá ser identificado por opcao normalmente. Os códigos SQL dividem-se em 2 (usualmente) 4 (tecnicamente) grandes grupos: Dois grupos: DDL e DML; Quatro grupos: DDL, DCL, DML e DQL. DDL: Data Definition Language. Exemplo de comandos: CREATE, ALTER e DROP; DML: Data Manipulation Language. Exemplo de comandos: INSERT, UPDATE e DELETE; DCL: Data Control Language, também conhecido como DDL. Exemplo de comandos: GRANT, REVOKE; DQL: Data Query Language, também conhecido como DML: Exemplo de comandos: SELECT. TÉCNICAS DE ORGANIZAÇÃO DE CÓDIGO SQL Abaixo segue uma listagem com algumas técnicas reconhecidas como boas para a escrita de instruções SQL: GERAL 1. Código sempre em maiúsculo. 2. Respeitar 7 espaços iniciais para palavras reservadas do código SQL. Tem como objetivo indicar onde começa cada cláusula. Ex: SELECT CAST(TABELA1.CAMPO1 AS VARCHAR(100)) AS CAMPO1, CAST(TABELA2.CAMPO2 AS VARCHAR(100)) AS CAMPO2 FROM SCHEMA1.TABELA1 LEFT JOIN SCHEMA1.TABELA2 ON TABELA2.CODIGO = TABELA1.CODIGO WHERE TABELA1.CODIGO < 100 CLÁUSULA SELECT ® IPM Informática Pública Municipal Programa Jovens Talentos 20 1. Para cada campo deve-se respeitar uma linha. 2. Cada campo deve ser nomeado sem caracteres especiais através do comando “AS”. 3. Para cada campo deve-se usar o comando“CAST” para definir o seu tipo. 4. A referencia de cada campo deve-ser precedida do nome da sua tabela mais o caractere ponto(.). CLÁUSULA FROM 1. Todas as tabelas devem ser referencias através do seu schema ou usuario (dependendo do banco de dados), mais o nome da tabela. CLÁUSULA JOIN 1. Cada relação que o SQL possuir deve ser declarado da seguinte maneira: TIPO [LEFT, INNER, RIGHT, FULL] + JOIN + SCHEMA OU USUÁRIO + ON Obs: Deve-se respeitar uma linha para esta declaração. 2. As condições devem ser declaradas da seguinte maneira: CAMPO OU VALOR + OPERADOR [=, <, >, <>] + CAMPO OU VALOR + OPERADOR DE LIGAÇÃO [AND, OR] Obs: Deve-se respeitar uma linha para esta declaração. CLÁUSULA WHERE 1. Cada condições deve ser declarada da seguinte maneira CAMPO OU VALOR + OPERADOR [=, <, >, <>] + CAMPO OU VALOR + OPERADOR DE LIGAÇÃO [AND, OR] OUTRAS DICAS 1. Usar sempre SUBSTR em vez de SUBSTRING, pois o SUBSTRING não é reconhecido pelo banco de dados Oracle. 2. Ao relacionar tabelas no quesito performance é sempre melhor usar JOIN, porém quando há necessidade de retornar apenas uma coluna ou apenas um valor somado, pode-se utilizar sub-select, pois deixará o código mais organizado. 3. Ao relacionar tabelas deve-se sempre observar as chaves primárias das tabelas envolvidas ou a utilização de índices, tendo como objetivo melhorar a performance. SQL - DML SELEÇÃO DE INFORMAÇÕES - SELECT O comando SELECT retorna linhas de uma ou mais tabelas. As linhas que satisfazem a condição WHERE são candidatas para seleção; se WHERE for omitido, todas as linhas são candidatas. Veja abaixo a utilização de forma geral: SELECT [ ALL | DISTINCT [ ON ( EXPRESSÃO [, ...] ) ] ] * | EXPRESSÃO [ AS NOME_SAÍDA ] [, ...] [ FROM ITEM_DE [, ...] ] [ WHERE CONDIÇÃO ] [ GROUP BY EXPRESSÃO [, ...] ] [ HAVING CONDIÇÃO [, ...] ] [ { UNION | INTERSECT | EXCEPT } [ ALL ] SELECT ] [ ORDER BY EXPRESSÃO [ ASC | DESC | USING OPERADOR ] [, ...] ] [ FOR UPDATE [ OF NOME_DA_TABELA [, ...] ] ] [ LIMIT { CONTADOR | ALL } ] [ OFFSET INÍCIO ] onde item_de pode ser: ® IPM Informática Pública Municipal Programa Jovens Talentos 21 [ ONLY ] NOME_DA_TABELA [ * ] [ [ AS ] ALIÁS [ ( LISTA_COLUNA_ALIAS ) ] ] | ( SELECT ) [ AS ] ALIÁS [ ( LISTA_COLUNA_ALIAS ) ] | ITEM_DE [ NATURAL ] TIPO_DE_JUNÇÃO ITEM_DE [ ON CONDIÇÃO_DE_JUNÇÃO | USING ( LISTA_COLUNA_JUNÇÃO ) ] Entradas: Expressão: O nome de uma coluna da tabela ou uma expressão. Nome_saída: Especifica outro nome para uma coluna retornada utilizando a cláusula AS. Este nome é utilizado, principalmente, como o título da coluna exibida. Também pode ser utilizados para fazer referência ao valor da coluna nas cláusulas ORDER BY e GROUP BY. Mas o nome_saída não pode ser usado nas cláusulas WHERE e HAVING; nestes casos deve-se escrever novamente a expressão. Item_de: A referência a uma tabela, uma subconsulta ou uma cláusula de junção. Veja abaixo para obter detalhes. Condição: Uma expressão booleana produzindo um resultado falso ou verdadeiro. Veja a descrição das cláusulas WHERE e HAVING abaixo. Select: Um comando SELECT com todas as suas funcionalidades, exceto as cláusulas ORDER BY, FOR UPDATE e LIMIT (mesmo estas podem ser utilizadas quando o SELECT está entre parênteses). Os itens da cláusula FROM podem conter: Nome_da_tabela: O nome de uma tabela ou de uma visão existente. Se ONLY for especificado, somente esta tabela é consultada. Se ONLY não for especificado a tabela, e todas as suas tabelas descendentes porventura existentes, serão consultadas. O * pode ser apensado ao nome da tabela para indicar que as tabelas descendentes devem ser consultadas, mas na versão corrente este é o comportamento padrão (Nas versões anteriores a 7.1 ONLY era o comportamento padrão). Alias: Um nome substituto para o nome_da_tabela precedente. Um aliás é utilizado para abreviar ou para eliminar ambigüidade em autojunções (onde a mesma tabela é referenciada várias vezes). Se um, aliás, for escrito, uma lista de aliás de coluna também pode ser escrita para fornecer nomes substitutos para uma ou mais colunas da tabela. Select: Uma subconsulta pode aparecer na cláusula FROM, agindo como se sua saída fosse criada como uma tabela temporária pela duração do comando SELECT. Observe que a subconsulta deve estar entre parênteses, e que um aliás deve ser fornecido para esta subconsulta. Tipo_de_junção: Um entre [ INNER ] JOIN, LEFT [ OUTER ] JOIN, RIGHT [ OUTER ] JOIN, FULL [ OUTER ] JOIN, ou CROSS JOIN. Para os tipos de junção INNER e OUTER, exatamente um entre NATURAL, ON condição_de_junção, ou USING (lista_coluna_junção) deve estar presente. Para CROSS JOIN, nenhum destes itens pode aparecer. Condição_de_junção: Uma condição de qualificação, similar à condição WHERE, exceto que somente se aplica aos dois itens sendo unidos por esta cláusula JOIN. Lista_coluna_junção: A condição USING lista de colunas ( a, b, ... ) é uma forma abreviada da condição ON tabela_esquerda.a = tabela_direita.a AND tabela_esquerda.b = tabela_direita.b... Na verdade, as linhas retornadas não são as linhas produzidas pelas cláusulas FROM/WHERE/GROUP BY/HAVING diretamente; mais precisamente, as linhas da saída são formadas computando-se as expressões de saída do SELECT para cada linha selecionada. O * pode ser escrito na lista de saída como uma abreviação para todas as colunas das linhas selecionadas. Pode-se escrever também nome_da_tabela.* como uma abreviação para as colunas provenientes de apenas uma tabela, ex: ® IPM Informática Pública Municipal Programa Jovens Talentos 22 SELECT TBCLIENTE.CLICODIGO, TBTELEFONE.TELNUMERO FROM TBCLIENTE, TBTELEFONE WHERE TBCLIENTE.CLICODIGO = TBTELEFONE.CLICODIGO A opção DISTINCT elimina as linhas repetidas do resultado, enquanto que a opção ALL (o padrão) retorna todas as linhas candidatas, incluindo as repetidas. DISTINCT ON elimina as linhas que correspondem a todas as expressões especificadas, mantendo apenas a primeira linha de cada conjunto de repetidas. As expressões do DISTINCT ON são interpretadas usando as mesmas regras dos itens do ORDER BY; veja abaixo. Observe que a "primeira linha" de cada conjunto não pode ser prevista, a menos que ORDER BY seja usado para garantir que a linha desejada apareça primeiro. Por exemplo, SELECT DISTINCT ON (CLICODIGO) CLICODIGO, TELNUMERO FROM TBTELEFONE ORDER BY CLICODIGO, TELNUMERO; RÓTULOS DE COLUNAS Podem ser atribuídos nomes para as entradas da lista de seleção para processamento posterior. Neste caso "processamento posterior" é uma especificação opcional de ordenação e a aplicação cliente (por exemplo, os títulos das colunas para exibição). Por exemplo: SELECT A AS VALOR, B + C AS SOMA FROM ... Se nenhum nome de coluna de saída for especificado utilizando AS, o sistema atribui um nome padrão. Para referências simples a colunas é o nome da coluna referenciada. Para chamadas de função é o mesmo nome da função. Para expressões complexas o sistema gera um nome genérico. A nomeação das colunas de saída neste caso é diferente daquela feita na cláusula FROM. Este duto na verdade permite renomear a mesma coluna duas vezes, mas o nome escolhido na lista de seleção é que será passado adiante. Nomes temporários podem ser atribuídos a tabelas, e a referências de tabela complexas, para ser utilizado como referência à tabela derivada em processamentos posteriores. Isto é chamado de aliás de tabela. Para criar um aliás de tabela deve ser escrito FROM REFERÊNCIA_TABELA AS ALIÁS ou FROM REFERÊNCIA_TABELA ALIÁS A palavra chave AS é opcional. O aliás pode ser qualquer identificador. Uma aplicação típica de aliases de tabelas é atribuir identificadores curtos para nomes longos de tabelas, para manter a cláusula de junção legível. Por exemplo: SELECT * FROM UM_NOME_MUITO_COMPRIDOU JOIN OUTRO_NOME_MUITO_COMPRIDO O ON U.ID = O.NUM; CLÁUSULA FROM/JOIN A cláusula FROM especifica uma ou mais tabelas de origem para o SELECT. Se múltiplas tabelas de origem forem especificadas o resultado será, conceitualmente, o produto Cartesiano de todas as linhas de todas estas tabelas -- mas, geralmente, condições de qualificação são adicionadas para restringir as linhas retornadas a um pequeno subconjunto do produto Cartesiano. ® IPM Informática Pública Municipal Programa Jovens Talentos 23 Quando o item da cláusula FROM é simplesmente o nome de uma tabela, implicitamente são incluídas as linhas das subtabelas desta tabela (filhas que herdam). Especificando-se ONLY causa a supressão das linhas das subtabelas da tabela. Um item da cláusula FROM pode ser também uma subconsulta entre parênteses (note que uma cláusula aliás é exigida para a subconsulta!). Esta característica é extremamente útil porque esta é a única maneira de se obter múltiplos níveis de agrupamento, agregação ou ordenação em uma única consulta. Finalmente, um item da cláusula FROM pode ser uma cláusula JOIN, que combina dois itens do FROM (Use parênteses, se for necessário, para determinar a ordem de aninhamento). O CROSS JOIN e o INNER JOIN são um produto Cartesiano simples, o mesmo que seria obtido listando-se os dois itens no nível superior do FROM. O CROSS JOIN é equivalente ao INNER JOIN ON (TRUE), ou seja, nenhuma linha é removida pela qualificação. Estes tipos de junção são apenas uma notação conveniente, porque não fazem nada que não poderia ser feito usando simplesmente o FROM e o WHERE. O LEFT OUTER JOIN retorna todas as linhas do produto Cartesiano qualificado (i.e., todas as linhas combinadas que passam pela condição ON), mais uma cópia de cada linha da tabela à esquerda para a qual não há uma linha da tabela à direita que tenha passado pela condição ON. Esta linha da tabela à esquerda é estendida por toda a largura da tabela combinada inserindo-se nulos para as colunas da tabela à direita. Observe que somente as condições ON ou USING do próprio JOIN são consideradas na hora de decidir quais linhas possuem correspondência. Condições ON ou WHERE externas são aplicadas depois. De forma inversa, o RIGHT OUTER JOIN retorna todas as linhas da junção, mais uma linha para cada linha da tabela à direita sem correspondência (estendida com nulos na tabela à esquerda). Isto é apenas uma conveniência da notação, porque poderia ser convertida em um LEFT OUTER JOIN trocando-se a tabela à direita pela tabela à esquerda. O FULL OUTER JOIN retorna todas as linhas da junção, mais uma linha para cada linha da tabela à esquerda sem correspondência (estendida com nulos na tabela à direita), mais uma linha da tabela à direita sem correspondência (estendida com nulos na tabela à esquerda) Verifique a imagem a seguir, ela especifica como seria o funcionamento do JOIN em cada uma de suas possibilidades de acordo com os dados contidos nas tabelas TBPESSOA e TBPESSOACONTATO. ® IPM Informática Pública Municipal Programa Jovens Talentos 24 Existe uma outra forma de se fazer join com o uso de NATURAL onde não se faz necessário o uso do ON, este tipo de join faz a junção de todas as colunas de mesmo nome em ambas das tabelas envolvidas. Deve-se tomar cuidado ao usar o NATURAL pois colunas que não fazem parte da ligação, podem ser utilizadas equivocadamente para fazer isto. Ex: SELECT * FROM TBPESSOA NATURAL LEFT JOIN TBPESSOACONTATO Neste caso irá funcionar corretamente, agora considere a seguinte situação: SELECT * FROM TBPESSOA NATURAL LEFT JOIN TBIMOVEL Neste caso existe além dos campos PESCODIGO em comum nas duas tabelas, também o campo LOGCODIGO (coincidentemente), o que fará com que a junção não seja realizada de forma correta. Pode-se utilizar como alternativa à este problema do NATURAL o USING, conforme exemplo abaixo: SELECT * FROM TBPESSOA LEFT JOIN TBIMOVEL USING(PESCODIGO) Neste caso o using especifica qual é a(s) coluna(s) que deve(m) ser utilizada(s) na junção das tabelas. Para todos os tipos de JOIN, exceto CROSS JOIN, deve-se escrever exatamente um entre ON condição_de_junção, USING (lista_coluna_junção), ou NATURAL. A cláusula ON é o caso mais geral: pode ser escrita qualquer expressão de qualificação envolvendo as duas tabelas da junção. A forma USING lista de colunas (a, b, ...) é uma abreviação para a condição ON tabela_esquerda.a = tabela_direita.a AND tabela_esquerda.b = tabela_direita.b... Além disso, USING implica em que somente uma coluna de cada par de colunas equivalentes será incluída na saída do JOIN, e não as duas. NATURAL é uma abreviação para USING quando a lista menciona todas as colunas das tabelas com mesmo nome. Observe o exemplo abaixo: SELECT * FROM SPUNICO.UNICO LEFT JOIN SPUNICO.LOGRADOU ON LOGRADOU.CODIGO = UNICO.LOGRADOURO WHERE UNICO.CODIGO BETWEEN 19 AND 200 Utilizando-se da opção explicação da consulta no pgAdmimIII, verifica-se que a execução está correta pois a ligação foi realizada utilizando-se a chave primária da tabela logradou. Isto faz com que a consulta seja executada em menor tempo. Quando não é possível utilizar campos da chave primária para fazer a ligação entre as tabelas, tente utilizar campos que façam parte de algum índice. Caso existam índices parciais (que possuam os campos exigidos pela ligação, porém possuem mais campos) insira condições obvias mas que utilizem esses campos a mais para que o índice seja utilizado. Ex: Observe o exemplo abaixo: SELECT HISLIQ.HISTORICO FROM CPARQ.HISLIQ LEFT JOIN CPARQ.LIQEMP ON HISLIQ.CODCLI = LIQEMP.CODCLI AND HISLIQ.ANO = LIQEMP.ANO AND HISLIQ.SEQLIQ = LIQEMP.SEQUENCIA AND HISLIQ.TIPEMP = 'E' AND HISLIQ.SEQ > 0 ® IPM Informática Pública Municipal Programa Jovens Talentos 25 No caso acima, para uma ligação entre as tabelas hisliq e liqemp, não existem todos os campos da chave primária disponíveis, neste caso foram adicionadas condições explícitas onde a chave primária seja evidenciada para que o banco de dados utilize o índice da chave primária para executar a consulta. SUB-SELECT Uma subconsulta escalar é um comando SELECT comum, entre parênteses, que retorna exatamente uma linha com uma coluna. O comando SELECT é executado e o único valor retornado é utilizado na expressão de valor. É errado utilizar uma consulta que retorne mais de uma linha ou mais de uma coluna como subconsulta escalar. Por exemplo, a consulta abaixo retorna a maior população de cidade de cada estado: SELECT NOME, (SELECT MAX(POPULACAO) FROM CIDADES WHERE CIDADES.ESTADO = ESTADOS.NOME) FROM ESTADOS; Pode-se utilizar também uma sub-consulta na cláusula from, como sendo uma "tabela virtual", que pode retornar várias linhas e várias colunas. Trata-se de um SELECT normal. Neste caso sempre deve-se nomear a sub-consulta. SELECT ORIGEM.LOGCODIGO AS "CÓDIGO", ORIGEM.LOGTIPO AS "TIPO", ORIGEM.LOGDESCRICAO AS "DESCRIÇÃO" FROM (SELECT * FROM TBLOGRADOURO WHERE TBLOGRADOURO.LOGCODIGO > 10) ORIGEM WHERE ORIGEM.LOGCODIGO < 15 Resultado da consulta: 11 Rua Logr. 11 12 Beco Logr. 12 13 Rua Logr. 13 14 Travessa Logr. 14 Observe as tabelas abaixo: Processamento do Banco de dados: Para cada linha processada de TBPESSOA o sub-select irá realizar um outro comando SQL em TBLOGRADOURO, para isto deve-se especificar a chave de junção dos dois comandos SELECT. Ex: SELECT PESNOME, (SELECT LOGDESCRICAO FROM TBLOGRADOURO WHERE TBLOGRADOURO.LOGCODIGO = TBPESSOA.LOGCODIGO) AS ENDERECO FROM TBPESSOA SUB-SELECT X JOIN Como apresentado anteriormente existe a possibilidade de se utilizar sub-select como origem de dados de consulta e também para buscar informações sobre uma única coluna. Essas situações também são possíveis através do uso do comandoJOIN. ® IPM Informática Pública Municipal Programa Jovens Talentos 26 Qual opção é a melhor ? Tudo depende da quantidade de colunas que se deseja retornar de outra tabela relacionada. Quanto mais colunas forem necessárias melhor é com a utilização de JOIN. Exemplos: SELECT PESNOME, PESTIPO, (SELECT LOGDESCRICAO FROM TBLOGRADOURO WHERE TBLOGRADOURO.LOGCODIGO = TBPESSOA.LOGCODIGO) AS LOGRADOURO FROM TBPESSOA Neste caso a utilização de uma sub-consulta pode ser mais eficiente do que o uso de JOIN, pois trata-se do retorno de apenas uma coluna (LOGDESCRICAO) e utilizando-se a chave primária da tabela TBLOGRADOURO como critério de pesquisa. SELECT PESNOME, PESTIPO, (SELECT LOGDESCRICAO FROM TBLOGRADOURO WHERE TBLOGRADOURO.LOGCODIGO = TBPESSOA.LOGCODIGO) AS LOGRADOURO_NOME, (SELECT LOGTIPO FROM TBLOGRADOURO WHERE TBLOGRADOURO.LOGCODIGO = TBPESSOA.LOGCODIGO) AS LOGRADOURO_TIPO FROM TBPESSOA No primeiro caso executando-se a explicação da consulta obtêm-se os seguintes resultados: SEQ SCAN ON TBPESSOA (COST=100000000.00..100593070.65 ROWS=147965 WIDTH=38) (ACTUAL TIME=6.937..92.395 ROWS=10000 LOOPS=1) SUBPLAN -> INDEX SCAN USING TBLOGRADOURO_PKEY ON TBLOGRADOURO (COST=0.00..3.99 ROWS=1 WIDTH=32) (ACTUAL TIME=0.004..0.005 ROWS=1 LOOPS=10000) INDEX COND: ((LOGCODIGO)::INTEGER = ($0)::INTEGER) TOTAL RUNTIME: 97.761 MS No segundo caso obtêm-se os seguintes resultados: SEQ SCAN ON TBPESSOA (COST=100000000.00..101183446.65 ROWS=147965 WIDTH=38) (ACTUAL TIME=6.894..163.446 ROWS=10000 LOOPS=1) SUBPLAN -> INDEX SCAN USING TBLOGRADOURO_PKEY ON TBLOGRADOURO (COST=0.00..3.99 ROWS=1 WIDTH=14) (ACTUAL TIME=0.004..0.005 ROWS=1 LOOPS=10000) INDEX COND: ((LOGCODIGO)::INTEGER = ($0)::INTEGER) -> INDEX SCAN USING TBLOGRADOURO_PKEY ON TBLOGRADOURO (COST=0.00..3.99 ROWS=1 WIDTH=32) (ACTUAL TIME=0.004..0.005 ROWS=1 LOOPS=10000) INDEX COND: ((LOGCODIGO)::INTEGER = ($0)::INTEGER) TOTAL RUNTIME: 168.968 MS Utilizando-se Join: SELECT PESNOME, PESTIPO, LOGDESCRICAO, LOGTIPO ® IPM Informática Pública Municipal Programa Jovens Talentos 27 FROM TBPESSOA NATURAL LEFT JOIN TBLOGRADOURO Com join obtêm-se os seguintes resultados: HASH LEFT JOIN (COST=100000154.00..100005438.04 ROWS=147965 WIDTH=80) (ACTUAL TIME=9.456..41.341 ROWS=10000 LOOPS=1) HASH COND: (("OUTER".LOGCODIGO)::INTEGER = ("INNER".LOGCODIGO)::INTEGER) -> SEQ SCAN ON TBPESSOA (COST=100000000.00..100002694.65 ROWS=147965 WIDTH=38) (ACTUAL TIME=6.856..15.894 ROWS=10000 LOOPS=1) -> HASH (COST=143.81..143.81 ROWS=4074 WIDTH=50) (ACTUAL TIME=2.577..2.577 ROWS=0 LOOPS=1) -> INDEX SCAN USING TBLOGRADOURO_PKEY ON TBLOGRADOURO (COST=0.00..143.81 ROWS=4074 WIDTH=50) (ACTUAL TIME=0.018..1.845 ROWS=1000 LOOPS=1) TOTAL RUNTIME: 46.485 MS Verifica-se com os resultados obtidos que a sub-select é uma opção viavel quando se trata de retorno de no máximo uma coluna por tabela relacionada, quando trata-se de dois ou mais campos a melhor solução é o uso de JOIN. O uso de JOIN mostrou-se (neste caso) mais eficiente em qualquer situação, porém cada caso é um caso e deve-se utilizar a explicação da consulta para verificar os melhores caminhos. OPERADORES DE AGREGAÇÃO Uma expressão de agregação representa a aplicação de uma função de agregação nas linhas selecionadas pela consulta. Uma função de agregação reduz vários valores de entrada a um único valor de saída, tal como a soma ou a média dos valores entrados. A sintaxe da expressão de agregação é uma das seguintes: NOME_DA_AGREGAÇÃO (EXPRESSÃO) NOME_DA_AGREGAÇÃO (ALL EXPRESSÃO) NOME_DA_AGREGAÇÃO (DISTINCT EXPRESSÃO) NOME_DA_AGREGAÇÃO ( * ) onde nome_da_agregação é uma agregação definida anteriormente (possivelmente qualificado pelo nome do esquema), e expressão é qualquer expressão de valor que não contenha uma expressão de agregação. Por exemplo, count(*) retorna o número total de linhas de entrada; count(f1) retorna o número de linhas de entrada onde f1 não é nulo; count(distinct f1) retorna o número de valores distintos não nulos de f1. Uma expressão de agregação pode aparecer apenas na lista de resultados ou na cláusula HAVING do comando SELECT. Seu uso é proibido nas outras cláusulas, tal como WHERE, porque estas cláusulas são avaliadas logicamente antes dos resultados das agregações estarem formados. A Tabela abaixo mostra as funções de agregação internas. Função Tipo do argumento Tipo retornado Descrição avg(expressão) smallint, integer, bigint, real, double precision, numeric ou interval numeric para qualquer argumento de tipo inteiro, double precision para argumento de tipo ponto flutuante, caso contrário o mesmo tipo de dado do argumento a média (média aritmética) de todos os valores de entrada count(*) Bigint número de valores de entrada count (expressão) any Bigint número de valores de entrada para os quais o valor da expressão não é ® IPM Informática Pública Municipal Programa Jovens Talentos 28 nulo max(expressão) qualquer tipo de dado matriz, numérico, cadeia de caracteres, data ou hora o mesmo tipo de dado do argumento valor máximo da expressão entre todos os valores de entrada min(expressão) qualquer tipo de dado matriz, numérico, cadeia de caracteres, data ou hora o mesmo tipo de dado do argumento valor mínimo da expressão entre todos os valores de entrada stddev (expressão) Smallint, integer, bigint, real, double precision ou numeric double precision para argumentos de ponto flutuante, caso contrário numeric desvio padrão da amostra dos valores de entrada sum(expressão) smallint, integer, bigint, real, double precision, numeric ou interval bigint para argumentos smallint ou integer, numeric para argumentos bigint, double precision para argumentos de ponto flutuante, caso contrário o mesmo tipo de dado do argumento somatório da expressão para todos os valores de entrada variance (expressão) smallint, integer, bigint, real, double precision ou numeric double precision para argumentos de ponto flutuante, caso contrário numeric. variância da amostra dos valores de entrada (quadrado do desvio padrão da amostr a) AGRUPAMENTO – GROUP BY A cláusula GROUP BY permite dividir a tabela em grupos de linhas que correspondem a um ou mais valores. SELECT LISTA_SELEÇÃO FROM ... [WHERE ...] GROUP BY REFERÊNCIA_COLUNA_AGRUPAMENTO A cláusula GROUP BY é utilizada para agrupar linhas de uma tabela que compartilham os mesmos valores em todas as colunas listadas. Em que ordem as colunas são listadas não faz diferença. A finalidade é reduzir cada grupo de linhas compartilhando valores comuns a uma única linha agrupada representando todas as linhas do grupo. Isto é feito para eliminar redundância na saída, e/ou para calcular agregações aplicáveis a estes grupos. Por exemplo: => SELECT * FROM TESTE1; X | Y ---+--- A | 3 C | 2 B | 5 A | 1 (4 ROWS) => SELECT X FROM TESTE1 GROUP BY X; X --- ® IPM Informática Pública Municipal Programa Jovens Talentos 29 A B C (3 ROWS) Na segunda consulta não poderia ser escrito SELECT * FROM teste1 GROUP BY x, porque não existe um único valor da coluna y que poderia ser associado com cada grupo. As colunas agrupadas podem ser referenciadas na lista de seleção, porque possuem um valor constante conhecido para cada grupo. HAVINGA cláusula HAVING permite selecionar somente os grupos de linhas que atendem a uma condição específica. Se uma tabela for agrupada utilizando a cláusula GROUP BY, mas há interesse em alguns grupos apenas, a cláusula HAVING pode ser utilizada, da mesma forma que a cláusula WHERE, para remover grupos da tabela agrupada. A sintaxe é: SELECT LISTA_SELEÇÃO FROM ... [WHERE ...] GROUP BY ... HAVING EXPRESSÃO_BOOLEANA As expressões na cláusula HAVING podem fazer referência tanto a expressões agrupadas quanto a expressões não agrupadas (as quais necessariamente envolvem uma função de agregação). Exemplo: => SELECT X, SUM(Y) FROM TESTE1 GROUP BY X HAVING SUM(Y) > 3; X | SUM ---+----- A | 4 B | 5 (2 ROWS) => SELECT X, SUM(Y) FROM TESTE1 GROUP BY X HAVING X < 'C'; X | SUM ---+----- A | 4 B | 5 (2 ROWS) Agora vamos fazer um exemplo mais próximo da realidade: SELECT CHAVE, COUNT(*) FROM SPTABELA HAVING COUNT(*) > 1 Neste exemplo irá retornar todos os registros de sptabela cuja chave seja igual e exista mais do que 1. ORDENAÇÃO - ORDER BY A cláusula ORDER BY faz com que as linhas retornadas sejam classificadas na ordem especificada. Se ORDER BY não for especificado, as linhas retornam na ordem que o sistema considera mais fácil de gerar. ® IPM Informática Pública Municipal Programa Jovens Talentos 30 Após a consulta ter produzido uma tabela de saída (após a lista de seleção ter sido processada) esta tabela pode, opcionalmente, ser ordenada. Se nenhuma ordenação for especificada, as linhas retornam em uma ordem aleatória. Na verdade, neste caso a ordem depende dos tipos de plano de varredura e de junção e da ordem no disco, mas não se deve confiar nisto. Uma determinada ordem de saída somente pode ser garantida se a etapa de ordenação for explicitamente especificada. A cláusula ORDER BY especifica a ordenação: SELECT LISTA_SELEÇÃO FROM EXPRESSÃO_TABELA ORDER BY COLUNA1 [ASC | DESC] [, COLUNA2 [ASC | DESC] ...] Onde: Coluna1, etc: fazem referência às colunas da lista de seleção. Pode ser tanto o nome de saída de uma coluna, quanto o número da coluna. Alguns exemplos: SELECT A, B FROM TABELA1 ORDER BY A; SELECT A + B AS SOMA, C FROM TABELA1 ORDER BY SOMA; SELECT A, SUM(B) FROM TABELA1 GROUP BY A ORDER BY 1; Como uma extensão do padrão SQL, o PostgreSQL também permite a ordenação por expressões arbitrárias: SELECT A, B FROM TABELA1 ORDER BY A + B; Também é permitido fazer referência a nomes de colunas da cláusula FROM que foram renomeados na lista de seleção: SELECT A AS B FROM TABELA1 ORDER BY A; ou SELECT A AS B FROM TABELA1 ORDER BY B; Mas estas extensões não funcionam nas consultas que envolvem UNION, INTERSECT ou EXCEPT, e não são portáveis para outros bancos de dados SQL. Cada coluna especificada pode ser seguida pela palavra opcional ASC ou DESC, para determinar a forma de ordenação como ascendente ou descendente. A forma ASC é o padrão. A ordenação ascendente coloca os valores menores na frente, sendo que "menor" é definido nos termos do operador <. De forma semelhante, a ordenação descendente é determinada pelo operador >. Se mais de uma coluna de ordenação for especificada, as últimas colunas são utilizadas para ordenar as linhas iguais na ordem imposta pelas primeiras colunas ordenadas. LIMIT E OFFSET A cláusula LIMIT permite que retorne para o usuário apenas um subconjunto das linhas produzidas pela consulta. LIMIT e OFFSET permitem trazer apenas uma parte das linhas geradas pela consulta: SELECT LISTA_SELEÇÃO FROM EXPRESSÃO_TABELA [LIMIT { NÚMERO | ALL }] [OFFSET NÚMERO] Se o limite for fornecido, não mais que esta quantidade de linhas será retornada (mas possivelmente menos, se a consulta produzir menos linhas). LIMIT ALL é o mesmo que omitir a cláusula LIMIT. OFFSET informa para saltar esta quantidade de linhas antes de começar a retornar as linhas para o cliente. OFFSET 0 é o mesmo que omitir a cláusula OFFSET. Se tanto OFFSET quando LIMIT forem especificados, então são saltadas OFFSET linhas antes de começar a contar as LIMIT linhas que serão retornadas. ® IPM Informática Pública Municipal Programa Jovens Talentos 31 É necessário possuir o privilégio SELECT na tabela para poder ler seus valores (Consulte os comandos GRANT e REVOKE). EXERCÍCIOS 1. Crie uma seleção de dados da tabela TBIMOVEIS fazendo uma contagem de registros, agrupando por LOGRADOURO, retornando uma coluna com a contagem dos registros e outra com o código do logradouro; 2. Crie uma seleção de dados da tabela TBLOGRADOURO retornando os campos LOGCODIGO, LOGTIPO e LOGDESCRICAO, ordenando a mesma por LOGTIPO, de forma ASCENDENTE; 3. Crie uma seleção de dados da tabela TBPESSOA buscando os campos PESNOME, PESCODIGO, PESSEXO, PESTIPO e LOGCODIGO. Para o nome das colunas selecionadas deverá retornar os seguintes nomes (respectivamente): "Nome ou Razão", "Código", "Sexo", "Tipo", "Cód. Logradouro"; 4. Crie uma seleção de dados da tabela TBPESSOA retornando os campos PESNOME, PESCODIGO, LOGCODIGO e o nome do LOGRADOURO relacionado, que deverá ser buscado da tabela TBLOGRADOURO; 5. Crie uma seleção de dados da tabela TBIMOVEL fazendo junção com a tabela TBPESSOA, retornando as seguintes colunas:TBIMOVEL.IMVCODIGO, TBIMOVEL.IMVLARGURA, TBIMOVEL.IMVCOMPRIMENTO e TBPESSOA.PESNOME, com os nomes originais para as colunas. 6. Crie uma seleção dos 10 (dez) maiores imóveis, considerando o M², retorne juntamente o nome dos proprietários dos imóveis exibidos; A CLÁUSULA WHERE A sintaxe da Cláusula WHERE é WHERE EXPRESSÃO_BOOLEANA A expressão_booleana pode ser qualquer expressão que retorna um valor booleano. Após o processamento da cláusula FROM ter sido feito, cada linha da tabela virtual derivada é verificada com relação à condição de pesquisa. Se o resultado da condição for verdade, a linha é mantida na tabela de saída, senão (ou seja, se o resultado for falso ou nulo) a linha é desprezada. Normalmente a condição de pesquisa faz referência a pelo menos uma coluna da tabela gerada pela cláusula FROM; embora isto não seja requerido, se não for assim a cláusula WHERE não terá utilidade. Em muitos casos esta expressão possui a forma: EXPRESSÃO OP_CONDIÇÃO EXPRESSÃO Ou OP_LOGICO EXPRESSÃO Nota: A condição de junção de uma junção interna pode ser escrita tanto na cláusula WHERE quanto na cláusula JOIN. Por exemplo, estas duas expressões de tabela são equivalentes: FROM A, B WHERE A.ID = B.ID AND B.VAL > 5 e FROM A INNER JOIN B ON (A.ID = B.ID) WHERE B.VAL > 5 Qual destas formas deve ser utilizada é principalmente uma questão de estilo. A sintaxe do JOIN na cláusula FROM provavelmente não é muito portável para outros sistemas gerenciadores de banco de dados SQL. Para as junções externas não existe escolha em nenhum caso: devem ser feitas na cláusula FROM. A cláusula ON/USING da junção externa não é equivalente à condição WHERE, porque determina a adição de linhas (para as linhas de entrada sem correspondência) assim como a remoção de linhas do resultado final. Abaixo estão mostrados alguns exemplos de cláusulas WHERE: SELECT ... FROM FDT WHERE C1 > 5 ® IPM Informática Pública Municipal Programa Jovens Talentos 32 SELECT ... FROM FDT WHERE C1 IN (1, 2, 3) SELECT ... FROM FDT WHERE C1 IN (SELECT C1 FROM T2) SELECT ... FROM FDT WHERE C1 IN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10) SELECT ... FROM FDT WHERE C1 BETWEEN (SELECT C3 FROM T2 WHERE C2 = FDT.C1 + 10) AND 100 SELECT ... FROM FDT WHERE EXISTS (SELECT C1 FROM T2 WHERE C2 > FDT.C1) Sendo que FDT é a tabela derivada da cláusula FROM. As linhas que não aderem à condição de pesquisa da cláusula WHERE são eliminadas de fdt. Deve ser observada a utilização de subconsultas escalares como expressões de valor. Assim como qualquer outra consulta, as subconsultas
Compartilhar