Baixe o app para aproveitar ainda mais
Prévia do material em texto
1 CONSULTAS AVANÇADAS Prof. Esp. Wender Cris de Souza Firmino 4 A principal linguagem utilizada para a criação de consultas em um banco de dados é a Structured Query Language ou SQL, como é mais conhecida. O SQL permite a manipulação, acesso e definição do banco de dados, seus objetos e respectivas informações. Durante o uso comum em sistemas, podemos empregá-lo de forma mais tradicional, sem aproveitar ao máximo sua potencialidade. Isso acon- tece porque, na maioria das aplicações, criamos CRUDs, sigla que representa Create, Read, Update e Delete (Criar, Ler, Atualizar e Excluir). O SQL é uma linguagem não tão complexa quanto o C++ ou Python. Contudo, per- mite a criação de estruturas lógicas complexas, as quais possibilitam analisar e obter informações, uma vez que possuem estruturas programacionais avançadas e seme- lhantes às encontradas em outras linguagens. CONSULTAS AVANÇADAS1 É possível criar um CRUD em bases NoSQL? • CRUD (Create, Read, Update, Delete) Operations on NoSQL Database (MongoDB) Using Node.js. No artigo, o autor apresenta um CRUD para MongoDB usando Node.js. Acesse aqui! • Deployment CRUD operations. O artigo apresenta um exemplo de CRUD no Elasticsearch usando JSON. Acesse aqui! SAIBA MAIS Uma expressão regular é uma string utilizada para definir um padrão de pesquisa que pode ser aplicado a um determinado texto. Vejamos o exemplo a seguir: EXPRESSÕES REGULARES E SQL2 Texto: O analista de sistema e um sistema são partes indissolúveis. Expressão: sistema. Ao realizar uma consulta dessa expressão utilizando termos regulares no texto de exemplo, seriam retornadas duas instâncias da palavra sistema, conforme demonstrado a seguir: O analista de sistema e um sistema são partes indissolúveis. O poder das expressões regulares não vem de simples pesquisas de palavras, mas do EXEMPLO https://towardsdatascience.com/crud-create-read-update-delete-operations-on-nosql-database-mongodb-using-node-js-3979573b9b24 https://towardsdatascience.com/crud-create-read-update-delete-operations-on-nosql-database-mongodb-using-node-js-3979573b9b24 https://www.elastic.co/guide/en/cloud/current/ec-api-deployment-crud.html 5 uso da sintaxe especial de expressões regulares. Por exemplo, um único ponto (.) em uma expressão regular representa qualquer caractere único. Vejamos o exemplo: Texto: O Brasil teve rapidez em optar por rastrear. Expressão: ra. Uma pesquisa no texto usando a expressão regular retornaria três instâncias de ‘ra’ adicionadas a qualquer outro caractere. Os resultados encontrados estão em destaque a seguir. O Brasil teve rapidez em optar por rastrear. Utilizando a base de dados Andes, é necessário retornar os clientes que tenham o nome iniciado pelas letras A, B, D e F. Na maneira tradicional, provavelmente se construiria uma query como a representada na Figura 1. Figura 1 – Consulta utilizando LIKE EXEMPLO Fonte: elaborado pelo autor. Compare a Figura 1 com a Figura 2, a qual utiliza expressões regulares na consulta: Figura 2 – Consulta utilizando expressão regular Fonte: elaborado pelo autor. Nesse exemplo, empregou-se a função do MySQL REGEXP_LIKE. Além dela, existem as seguintes funções: • REGEXP: usado para validar uma expressão regular; • REGEXP_LIKE: utilizado com a cláusula WHERE para selecionar registros com valo- 6 res de campo que correspondem à expressão regular; • REGEXP_SUBSTR: retorna o valor da substring correspondente; • REGEXP_INSTR: a posição do índice (a contagem começa em 1 e não em 0) da string em que a primeira correspondência for encontrada será retornada; • REGEXP_REPLACE - substituirá a substring do campo selecionado por outra subs- tring especificada na expressão. Este outro exemplo demonstra como validar os clientes com endereço de e-mail in- válido usando SQL. Para isso, será necessário criar uma expressão regular que valide um e-mail, conforme apresentado na Figura 3. Pode-se criar uma expressão própria ou utilizar um dos vários exemplos disponibilizados na internet ou no site indicado nas Mídias Integradas a seguir. Figura 3 – Consulta utilizando expressão regular para validar e-mail Fonte: elaborado pelo autor. No exemplo acima, a consulta retornará os e-mails dos clientes que não estão no padrão básico (nome@servidor.extensão). As expressões regulares utilizam um conjunto de caracteres que permitem criar expressões complexas. Por exemplo, validar se uma informação é numérica, aplicar uma máscara em um CNPJ ou validar um endereço de e-mail. • Para conhecer sua especificação, visite o site da OpenGroup. Acesse aqui! SAIBA MAIS Regular Expressions • Site dedicado ao tema das expressões regulares, com vários exemplos prontos para uso. Acesse aqui! MÍDIA INTEGRADA https://pubs.opengroup.org/onlinepubs/9699919799/basedefs/V1_chap09.html https://www.regular-expressions.info/ 7 Em sua essência, as funções PIVOT e UNPIVOT são usadas para transformar uma determinada tabela em outra. Essa função é usada para fornecer uma visão mais sim- ples da tabela e de seus dados. PIVOT E UNPIVOT3 Você já trabalhou em uma planilha e percebeu que os dados fariam muito mais sentido se as colunas estivessem listadas como linhas? Ou talvez o contrário? O SQL permite dinamizar as linhas de uma lista de dados colunares. REFLITA O PIVOT é usado para converter dados de linha em dados de coluna, enquanto o UNPIVOT executa o oposto, pois transforma colunas em linhas. A representação da sintaxe desses comandos aparece na Figura 4. Porém, no MySQL essa instrução não é suportada. Figura 4 – PIVOT e UNPIVOT Fonte: elaborado pelo autor. ROLLUP4 O ROLLUP é uma extensão da cláusula GROUP BY, que cria linhas com subtotais (além das linhas agrupadas). As linhas de subtotal são aquelas que agregam níveis intermediários de valores, os quais são derivados dos mesmos cálculos usados para criar as linhas agrupadas. 8 O ROLLUP é como a geração de vários conjuntos de resultados, em que cada um (após o primeiro) é a agregação do conjunto de resultados anteriores. Por exemplo, em uma rede de varejo, é possível ver o lucro de: • Cada departamento; • Cada cidade; • Cada estado; • Todos (todas as filiais em todos os estados). Ao agregar subtotalizações em uma consulta, o ROLLUP permite uma visualização mais analítica dos resultados. O exemplo da base Andes, representado na Figura 5, retorna a quantidade total e a média de preço dos produtos, tomando como base para totalização o produto e a data do pedido. Figura 5 – Consulta utilizando ROLLUP Fonte: elaborado pelo autor. Como resultado, a consulta retornará algo parecido como o demonstrado na Figura 6. Há uma totalização por data e, ao final, uma totalização geral. Figura 6 – Exemplo de consulta com Rollup no MySQL Fonte: elaborado pelo autor. CASE, COALESCE E IF5 A instrução CASE itera por meio de uma lista de condições e retorna um valor caso 9 uma condição seja atendida. A seguir, há um exemplo de uso da instrução CASE, com base na tabela de pedidos da base Andes. Imagine que você queira realizar uma campanha nas redes sociais enviando mensagens personalizadas para seus clientes. Para isso, é necessário destacar os que compraram mais, oferecendo um desconto, e um convite àqueles que não compraram muitos itens. Se a quantidade de itens de pedido de um cliente for superior a 100 unidades, será criada uma coluna contendo uma mensagem com a oferta do desconto, caso contrá- rio será uma mensagem de convite. A seguir, temos um exemplo do uso do CASE com essas condições: Figura 7 – Consulta utilizando CASE Fonte: elaborado pelo autor. Este é o resultado da consulta: Figura 8 – Resultado da consulta utilizando CASE Fonte: elaborado pelo autor. Além da instrução CASE, temos também a COALESCE. Ela é muito útil quando se tem uma lista de valores e se quer retornar à primeira ocorrência não nula dessa lista. Por exemplo, em um LEFT JOIN, para que o valor de uma coluna não retorne nulo, pode-se substituir por um traçoou outro sinal gráfico. Na instrução SELECT COALES- CE(NULL,'*'), o resultado seria *, pois ele é o primeiro valor não nulo da lista. Observe o exemplo: 10 Figura 9 – Consulta utilizando COALESCE Fonte: elaborado pelo autor. No exemplo a seguir, temos o uso do COALESCE. Se o cliente não possuir pedidos será exibida uma mensagem. Figura 10 – Resultado da consulta usando COALESCE Fonte: elaborado pelo autor. O IF é uma instrução suportada em todas as linguagens de programação. O SQL é si- milar, sua sintaxe é um pouco semelhante ao IF de outras linguagens. A peculiaridade é que, no MySQL, ele aparece todo em uma linha. IF (condição, valor verdadeiro, valor falso) Por exemplo, é necessário trazer o status dos clientes que estão com pedidos aber- tos. Se algo não foi pago ainda (status_pedido igual a 2), será exibido “Não OK”, caso contrário, aparecerá “OK”. Figura 11 – Consulta utilizando o IF Fonte: elaborado pelo autor. 11 Neste exemplo temos o uso do IF e de uma Subquery que alimenta a coluna cliente. O resultado é: Figura 12 – Resultado da consulta utilizando-se IF Fonte: elaborado pelo autor. CONCLUSÃO E então, sua caixa de ferramentas se ampliou? Comandos como ROLLUP, CASE e ex- pressões regulares podem economizar horas de programação e permitir uma análise mais criteriosa de dados. Além disso, são excelentes aliados na geração de informa- ções, auditorias ou em análises gerenciais em uma base de dados. REFERÊNCIAS ORACLE. MySQL Documentação Oficial. Disponível em: https://bit.ly/3BoaIqH. Acesso em: 7 out. 2021. 12 Universidade do Extremo Sul Catarinense – UNESC Reitora Luciane Bisognin Ceretta Vice-reitor Daniel Ribeiro Préve Pró-Reitoria Acadêmica: Indianara Reynaud Toreti Setor de Educação a Distância Mariesa Toldo CRÉDITO DA PRODUÇÃO DO MATERIAL Autor Prof. Esp. Wender Cris de Souza Firmino Assessoria Pedagógica Caroline Jacques Cibele Beirith Figueiredo Freitas Édina Regina Baumer Revisão Aline Ramos Amanda Kestering Tramontin Angélica Manenti Projeto gráfico e Diagramação Suamy Fujita
Compartilhar