Como encontrar padrões entre os seus dados no MySQL

Esta solução se aplica à busca de padrões em meio ao banco de dados MySQL, com o uso do operador LIKE.
Padrões são strings contendo caracteres especiais.
MySQL_Positive_Energy_001
São conhecidos como meta caracteres porque representam algo diferente de si mesmos.

“Casamento de padrões é o ato de verificação da presença de um padrão em um conjunto de dados (…) usado para testar se o objeto de estudo possui a estrutura desejada (…)” Wikipedia.

O MySQL tem 2 tipos de casamento de padrões. Um, baseado em padrões SQL e outro baseado em expressões regulares.
Cada um destes usa seus próprios operadores e diferentes sets de meta caracteres.
Neste texto, vou me limitar aos padrões SQL.

Exemplos de uso do operador LIKE no MySQL

O casamento de padrões SQL usa os operadores LIKE e NOT LIKE – em vez de = ou != – para encontrar paridade entre padrões.
Os padrões podem conter dois meta caracteres especiais (ou coringas):

  • % — o sinal de percentual serve para buscar a correspondência exata de caracteres — sem se preocupar com o posicionamento deles dentro da string.
  • _ — o sinal de sublinha ou underscore procura a correspondência, só que dentro do mesmo posicionamento de caracteres.
  • São caracteres-curinga, portanto. Semelhantes aos que você usa em um terminal para se referir a vários nomes de arquivos que sejam parcialmente iguais. Ex.: mysql.*, config*.ini etc.

    A palavra curinga se origina do termo “kuringa”, em kimbundo. E significa “matar”.
    via Wikipedia.

Complicado? Então, vamos deixar os exemplos falarem por si.
Veja como fazer uma busca, dentro da tabela ClientesCidades, por todos os nomes de cidades que contenham a sequência de caracteres “co”, no início:

SELECT CidadeCliente FROM ClientesCidades WHERE CidadeCliente LIKE 'co%';
+-----------------+
| CidadeCliente   |
+-----------------+
| Corroy-le-Grand |
| Coalhurst       |
| Colonnella      |
+-----------------+
3 rows in set (0.04 sec)

Você não precisa usar as minhas tabelas para aplicar os exemplos no seu aprendizado. Pode usar qualquer outra, que você tiver disponível. Basta readequar os exemplos deste texto à sua realidade.
Só não vá brincar com os dados da produção.


Se quiser encontrar os nomes de cidade que terminem com a sequência “co”, inverta a posição do sinal de percentual (%):

SELECT CidadeCliente FROM ClientesCidades WHERE CidadeCliente LIKE '%co';
+---------------+
| CidadeCliente |
+---------------+
| Girifalco     |
| Recco         |
+---------------+

Ou, encontre todos nomes de cidade que contenham a sequência “co”, em qualquer posição na string:

SELECT CidadeCliente FROM ClientesCidades WHERE CidadeCliente LIKE '%co%';
+------------------------+
| CidadeCliente          |
+------------------------+
| Girifalco              |
| San Costantino Calabro |
| Rocourt                |
| Beaconsfield           |
| Rachecourt             |
| Vancouver              |
| Lacombe                |
| Port Lincoln           |
| Corroy-le-Grand        |
| Recco                  |
| Coalhurst              |
| San Demetrio Corone    |
| Lakeland County        |
| Beaconsfield           |
| Colonnella             |
+------------------------+
15 rows in set (0.01 sec)

Note que, neste caso, o operador sequer exige que haja a correspondência da caixa das letras — ou seja, “Colonnella” e “u>colonnella” são a mesma coisa.
Ao criar um sistema de buscas, usar coringas é uma opção para encontrar e exibir uma série de resultados relacionados.
O caractere-curinga underscore (_) é mais restritivo. Ele só serve para substituir os caracteres que se encontram na posição em que ele for colocado na busca.

Uso de curinga Correspondências possívels
__enda Brenda, Glenda
___nda Brenda, Glenda, Amanda, Chanda.
C_ntia Cíntia, Cintia, Cyntia
C_n%ia Cíntia, Cintia, Cyntia, Cynthia, Cínthia etc.

Espero que a tabela ajuda a entender melhor o funcionamento do underscore no SQL.
Veja alguns exemplos de uso do LIKE, combinado a este caractere-curinga:

SELECT Nome FROM Clientela WHERE Nome LIKE 'Al__a';
+----------+
| Nome     |
+----------+
| Alisa    |
| Alana    |
| Alexa    |
| Alana    |
+----------+
4 rows in set (0.00 sec)

Combine o uso dos caracteres-curinga ‘%’ e ‘_’ para enriquecer seus resultados — o que pode ser necessário, se a coluna incluir nome e sobrenome:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente LIKE 'I__ana%' ORDER BY NomeCliente;
+------------------+
| NomeCliente      |
+------------------+
| Iliana Cleveland |
| Illana Cannon    |
+------------------+
2 rows in set (0.00 sec)

Como usar o NOT LIKE em uma query MySQL

Se eu quiser uma relação de todos os nomes que não contenham uma determinada letra ou sequência de caracteres, posso usar o NOT LIKE para fazer o trabalho. Veja um exemplo, que exclui todos os nomes (e sobrenomes) que contenham a letra ‘a’:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente NOT LIKE '%a%' ORDER BY NomeCliente;
+--------------------+
| NomeCliente        |
+--------------------+
| Bo Simpson         |
| Emily Holmes       |
| Emily Tyson        |
| Giselle Rice       |
| Helen Burke        |
| Melodie Levy       |
| Mercedes Hendricks |
| Michelle Whitley   |
| Simone Everett     |
| Yvonne Wilcox      |
+--------------------+
10 rows in set (0.00 sec)

Como combinar as buscas com LIKE e outras funções de manipulação de strings no MySQL

Use funções específicas para retirar o que te interessa nas strings:

SELECT NomeCliente FROM ClientesCidades WHERE LEFT(Nomecliente,3) LIKE 'wil%' ORDER BY NomeCliente;
+-----------------+
| NomeCliente     |
+-----------------+
| Willa Kerr      |
| Willow Graham   |
| Willow Mitchell |
| Willow Simmons  |
| Wilma Hill      |
+-----------------+
5 rows in set (0.00 sec)

Leia mais sobre funções para decompor strings ou use o quadro de busca desta página para se aprofundar mais no assunto.

Referências: Casamentos de padrão na Wikipedia: http://pt.wikipedia.org/wiki/Casamento_de_padr%C3%B5es.
Como pesquisar sequências de caracteres dentro de strings, com a função LOCATE, no MySQL.

Como pesquisar sequências de caracteres dentro de strings, com a função LOCATE, no MySQL

A função LOCATE() do MySQL permite encontrar sequência dentro de uma cadeia de caracteres. Ou seja, a função pode ser usada para identificar uma substring dentro de uma string.
A função LOCATE() recebe dois argumentos: a substring que você deseja encontrar e o texto dentro do qual ela será procurada.
Capa do tutorial - como encontrar sequências de caracteres no MySQL
Se a busca for bem sucedida, a função retorna a posição em que a substring desejada se encontra ou o valor 0 (zero), caso ela não esteja presente.
Opcionalmente, é possível fornecer um terceiro argumento à função — para indicar em que posição, dentro da string principal, a busca deve ter início.

Exemplos de uso da função locate no MySQL

Neste primeiro exemplo, vou mostrar como criar uma variável no MySQL e, em seguida, como efetuar uma busca dentro dela com a função LOCATE().

SET @minhavar="O mysqladmin é um cliente para executar operações administrativas no MySQL. Ele pode ser usado para verificar a configuração do servidor e seu estado atual, além de criar e apagar bancos de dados - entre outras coisas.";
SELECT LOCATE("cliente",@minhavar);
+-----------------------------+
| LOCATE("cliente",@minhavar) |
+-----------------------------+
|                          19 |
+-----------------------------+
1 row in set (0.00 sec)

A busca efetuada é insensível a caixa das letras, ou seja, tanto faz procurar por “cliente” como “ClIEnTE” — para a função LOCATE(), as duas strings são a mesma coisa.

A função LOCATE() era sensível à caixa das letras nas versões do MySQL anteriores a 4.0.0
— MySQL Cookbook by Paul DuBois

O exemplo abaixo mostra como fazer a busca dentro do banco de dados:

SELECT CidadeCliente FROM ClientesCidades WHERE LOCATE('co',CidadeCliente);

O comando, acima, ordena uma pesquisa dentro da tabela ClientesCidades por CidadeCliente, em cujo texto, tenha a substring “co”. Veja o resultado:

+------------------------+
| CidadeCliente          |
+------------------------+
| Girifalco              |
| San Costantino Calabro |
| Rocourt                |
| Beaconsfield           |
| Rachecourt             |
| Vancouver              |
| Lacombe                |
| Port Lincoln           |
| Corroy-le-Grand        |
| Recco                  |
| Coalhurst              |
| San Demetrio Corone    |
| Lakeland County        |
| Beaconsfield           |
| Colonnella             |
+------------------------+
15 rows in set (0.01 sec)

Como escrever strings incluindo aspas e caracteres especiais no MySQL

Se você tiver alguma dificuldade de digitar queries MySQL, por conterem caracteres especiais ou aspas simples, conheça algumas maneiras de contornar o problema.
O assunto já foi abordado no artigo Como incluir caracteres especiais e valores NULL nas consultas MySQL. Sugiro sua leitura, caso as informações dadas aqui não sejam suficientes.
Teclado luminoso azul

Aspas simples e apóstrofos no MySQL

Este tipo de caractere pode causar problemas, não somente, para usuários e desenvolvedores de língua portguesa — mas também aos de língua inglesa, que é o idioma original dos criadores deste banco de dados.

Na programação de computadores, uma cadeia de caracteres ou string é uma sequência de caracteres, geralmente utilizada para representar palavras, frases ou textos de um programa.
— Wikipedia.

As aspas simples podem ser usadas para envolver strings no MySQL, para indicar que a aquela sequência não é um comando, cláusula ou algum parâmetro de execução.
O caractere de “aspa” também é usado como apóstrofo e aí a confusão começa — quando o sistema “imagina” que aquele seja um sinal de início de uma string e fica esperando que uma “segunda aspa” apareça para indicar o fim.

I’m here!

Faça o teste, dentro do cliente MySQL.

SELECT "Santa Bárbara d'Oeste";

Quando tudo está bem, o comando SELECT não irá retornar erro:

+------------------------+
| Santa Bárbara d'Oeste  |
+------------------------+
| Santa Bárbara d'Oeste  |
+------------------------+
1 row in set (0.00 sec)

No exemplo, que segue, um “apóstrofo” atrapalhou o outro.

SELECT 'Santa Bárbara d'Oeste';
    '> ';

O cliente MySQL não entendeu a presença de 3 aspas simples:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '';
'' at line 1

O problema, acima, pode ser resolvido com o uso de uma ‘\’ (barra):

SELECT 'Santa Bárbara d\'Oeste';
+------------------------+
| Santa Bárbara d'Oeste  |
+------------------------+
| Santa Bárbara d'Oeste  |
+------------------------+
1 row in set (0.00 sec)

Entenda melhor o uso dos caracteres de escape no MySQL, neste artigo.
Em vez do caractere \, é possível duplicar as aspas. observe:

SELECT 'Santa Bárbara d''Oeste';
+------------------------+
| Santa Bárbara d'Oeste  |
+------------------------+
| Santa Bárbara d'Oeste  |
+------------------------+
1 row in set (0.00 sec)

O caractere de acentuação pode ser usado como aspas simples — mas deve ser evitado, uma vez que sua aplicabilidade varia de acordo com o teclado em uso.

Uma string é uma sequência de bytes ou caracteres envoltos por aspas simples (‘) ou duplas (“).
— Documentação do MySQL.

Se quiser que as aspas sejam exibidas e façam parte da sua string, usa-as duplicadas ou faça uso do caractere de escape ‘\’:

SELECT "Ela disse ""Olá!""", "E eu disse \"Oi!\"";
+-------------------+------------------+
| Ela disse "Olá!"  | E eu disse "Oi!" |
+-------------------+------------------+
| Ela disse "Olá!"  | E eu disse "Oi!" |
+-------------------+------------------+
1 row in set (0.00 sec)

A própria ‘\’ (barra) é um caractere especial. É chamada de caractere de escape por que tem o efeito de retirar temporariamente uma string das regras de processamento normais. Portanto, para conseguir fazer uso da barra literalmente, você precisa duplicá-la:

SELECT 'No Windows, o MySQL fica instalado em C:\\mysql';
+------------------------------------------------+
| No Windows, o MySQL fica instalado em C:\mysql |
+------------------------------------------------+
| No Windows, o MySQL fica instalado em C:\mysql |
+------------------------------------------------+
1 row in set (0.00 sec)

Outras sequencias de escape reconhecidas pelo MySQL

  • \b — corresponde ao backspace delete.
    SELECT 'No W\bindows, o MySQL fica instalado em C:\\mysql';
  • \n — Insere uma linha.
  • \r — Equivale ao Return ou Enter — insere um parágrafo.
  • \t — Insere um caractere de tabulação.
  • \0 — insere um caractere ASCII NULL.

Faça suas próprias experiências e descubra mais sobre o que pode ou não ser feito na hora de fornecer suas próprias strings ao MySQL.

Referências:

Tipos de dados no MySQL: https://elias.praciano.com/2014/01/mysql-tipos-de-dados/
Strings e cadeias de caracteres, na Wikipedia: http://pt.wikipedia.org/wiki/Cadeia_de_caracteres
Uso das aspas, na Wikipedia: http://pt.wikipedia.org/wiki/Aspas#Representa.C3.A7.C3.A3o_correta_das_aspas
Documentação oficial do MySQL String literals: https://dev.mysql.com/doc/refman/5.1/en/string-literals.html

Como encontrar e listar os maiores arquivos e diretórios no sistema.

Algumas técnicas simples podem ser usadas para listar os maiores arquivos no seu sistema Linux ou FreeBSD, sem o uso de ferramentas gráficas — usando apenas um terminal.
Magic wand
Às vezes é necessário encontrar arquivos e/ou diretórios inteiros, que estão ocupando um espaço precioso em seu disco — e, a partir daí, determinar se sua existência é imprescindível ou não.
Alguns arquivos podem apresentar (pelos mais variados motivos) um crescimento expressivo em seu tamanho. É possível encontrar várias versões do kernel no seu sistema, quando você só precisa de uma.
Arquivos temporários, normalmente, são apagados automaticamente. Mas, se você desligou seu micro em algum momento abruptamente, estes e outros arquivos ainda podem estar lá, impedindo que você expanda o uso do seu HD.
Não há um comando simples para mostrar quais são os maiores arquivos presentes no seu sistema — mas há uma combinação deles, que resolve o problema.
Veja quais são:

  • du — mostra uma estimativa do uso do espaço em um dispositivo de armazenamento (HD, cartão flash, pendrive etc.)
  • sort — ordena as linhas de texto de arquivos ou de dados.
  • head — exibe as primeiras linhas de um arquivo ou de uma lista. Vamos usá-lo para exibir apenas os 10 maiores arquivos no sistema.
  • find — pesquisa o sistema para encontrar determinados arquivos. O comando find é explicado em detalhes no tutorial Como encontrar arquivos no Linux com o comando find — recomendo sua leitura!

arquivos em computador

Como usar o comando du, para ver os maiores diretórios no seu sistema

O comando du pode ser usado para obter estatísticas de ocupação dos diretórios e dos arquivos.
Experimente:

du -h /var

O nome do aplicativo utilitário du é a abreviatura de sua função: disc usage ou uso do disco.

A opção -h torna a leitura mais agradável (human readable) ao mostrar os valores em kilobytes, megabytes, gigabytes etc.
Para ver os 10 maiores arquivos/diretórios dentro de ~/.config, use o seguinte comando:

du -h ~/.config/ | sort -n -r | head -n 10

O meu resultado foi este:

1016K	/home/justin/.config/libreoffice/4/user/database/biblio
804K	/home/justin/.config/google-chrome/Default/Extensions/pokjppmpccggefgcenpngoleemajgnmo/0.2.27_0/libraries
784K	/home/justin/.config/transmission/torrents
760K	/home/justin/.config/google-chrome/Default/Extensions/nmmhkkegccagdldgiimedpiccmgmieda
756K	/home/justin/.config/google-chrome/Default/Extensions/nmmhkkegccagdldgiimedpiccmgmieda/0.0.6.1_0
744K	/home/justin/.config/google-chrome/Default/Extensions/hbdpomandigafcibbmofojjchbcdagbl/3.9.671_0/web/assets/global
744K	/home/justin/.config/google-chrome/Default/Extensions/hbdpomandigafcibbmofojjchbcdagbl/3.9.625_0/web/assets/global
704K	/home/justin/.config/google-chrome/Default/Extensions/hbdpomandigafcibbmofojjchbcdagbl/3.9.671_0/web/assets/global/backgrounds
704K	/home/justin/.config/google-chrome/Default/Extensions/hbdpomandigafcibbmofojjchbcdagbl/3.9.625_0/web/assets/global/backgrounds
696K	/home/justin/.config/google-chrome/Default/Extensions/pokjppmpccggefgcenpngoleemajgnmo/0.2.27_0/styles

Com esta lista, é possível saber onde estão os arquivos com maior extensão e, se for o caso, dar algum encaminhamento a eles.
Use a opção que segue, para ter uma lista mais compacta:

du -hsx ~/.config/* | sort -rh | head -10

Dissecando o comando:

  • du -h — quebra os números e os representa na forma de Kb, Mb, Gb etc.
  • du -s — apresenta apenas um total sumarizado de cada item.
  • du -x — pula os diretórios que, porventura, se encontrem em sistemas de arquivos diferentes.
  • sort -r — inverte a ordem de exibição dos valores.
  • sort -h — torna mais amigável a exibição da lista.
  • head -10 — restringe a exibição da lista aos 10 primeiros itens.

Com o comando find, é possível obter um resultado semelhante. Observe:

find ~/.config/ -printf '%s %p\n'| sort -nr | head -10
14021508 /home/justin/.config/google-chrome/Safe Browsing Bloom
10028176 /home/justin/.config/google-chrome/pnacl/0.1.0.13992/_platform_specific/x86_64/pnacl_public_x86_64_pnacl_llc_nexe
4202496 /home/justin/.config/google-chrome/Default/Storage/ext/chrome-signin/def/Cache/data_3
4202496 /home/justin/.config/google-chrome/Default/Application Cache/Cache/data_3
3923495 /home/justin/.config/google-chrome/Dictionaries/pt-BR-3-0.bdic
3853312 /home/justin/.config/google-chrome/Default/Local Storage/http_www.technobezz.com_0.localstorage
2783258 /home/justin/.config/google-chrome/Safe Browsing Bloom Prefix Set
2711552 /home/justin/.config/google-chrome/Default/Sync Data/SyncData.sqlite3
2392064 /home/justin/.config/google-chrome/Default/Local Storage/https_twitter.com_0.localstorage
2163856 /home/justin/.config/google-chrome/pnacl/0.1.0.13992/_platform_specific/x86_64/pnacl_public_x86_64_ld_nexe

ou esta variação:

find ~/.config/ -printf '%s %p\n'| sort -nr | head -10

Com o comando abaixo, você pode pular nomes de diretórios e exibir apenas os nomes de arquivos mais volumosos:

find ~/.config/ -type f -printf '%s %p\n'| sort -nr | head -10

Os comandos vistos, neste artigo, são apenas o primeiro passo para você conseguir solucionar problemas. Eles podem e devem ser adaptados para se adequar às suas necessidades e ao seu sistema.
Você pode aprender mais sobre Linux, neste site, clicando nos links dados no decorrer do texto ou fazendo buscas personalizadas na caixa de pesquisa, no canto superior do site.
Sinta-se à vontade, também para compartilhar com seus amigos os artigos do site.
Divirta-se!

O comando grep

O grep pode ser visto como uma forma simplificada de consulta a um banco de dados em texto puro, em que cada linha representa um registro.
O utilitário pode ser usado para retirar um conjunto de strings (cadeias de caracteres) do resultado de um comando dado ou de um arquivo texto, por mais longo que seja.
Este artigo reúne uma coletânea de exemplos, que irão ajudar a entender bem o funcionamento do grep.

LEIA MAIS:

Use a caixa de busca para descobrir outros artigos que fazem uso de exemplos com o comando grep.

Exemplo básico de uso do comando grep

Para pedir uma lista dos processos em execução na máquina, podemos usar o comando ‘ps aux’.
Mas a lista pode ser muito extensa e você só quer saber (por exemplo) do ‘NetworkManager’:


ps aux | grep -i net

Observe que ele exibe e destaca todas as strings contendo a cadeia de caracteres “net” (veja a imagem, abaixo).
Captura de tela de 2013-02-22 13:42:27
Entenda o que foi feito:

  • O comando ps aux exibe todos os processos em execução.
  • O operador | redireciona os resultados do comando ps para o grep, que filtra as ocorrências de “net” do resultado.
  • O parâmetro -i, pede ao programa que ignore se as letras estão em maiúsculas/minúsculas.

Use o grep para filtrar resultados de arquivos de log do sistema

Certos arquivos de log do sistema são muito extensos e podem conter uma quantidade imensurável de informações irrelevantes pra sua situação.
O grep pode ajudar a encontrar “uma agulha no palheiro”.
Vejamos, como exemplo o arquivo de log /var/log/dmesg que contém informações atuais do que está ocorrendo no seu sistema.
Vamos visualizar seu conteúdo – deixando de fora tudo o que não se refere à nossa interface de rede eth0:


cat /var/log/dmesg | grep eth0

Captura de tela de 2013-02-22 14:09:19

Use o egrep para filtrar resultados com operadores lógicos

Outros comandos, como lshw, também retornam uma lista muito extensa e podem demorar muito para finalizar.
O interessante, aqui, é direcionar o resultado do comando para um arquivo texto, que será usado com muito mais facilidade.
Com o seguinte comando, você cria o arquivo lshw.log contendo o resultado do lshw:


sudo lshw > lshw.log

Este procedimento traz as vantagens de não precisar mais invocar o sudo, para obter o resultado do lshw e a velocidade com que o sistema lê e exibe o texto do lshw.log.
A seguir vamos usar uma versão diferente do grep, o egrep, para filtrar os resultados que se referem às nossas interfaces de rede eth0 e wlan0:


egrep -C 2 -i '(eth|wlan)0' lshw.log

Olha a explicação do comando acima:

  • -C 2 — inclui as 2 linhas acima e as 2 linhas abaixo daquela em que foi encontrada a string desejada.
    Assim você sabe em que contexto o resultado está inserido.
  • -i — desativa a sensibilidade às ‘caixas’, ou seja, tanto faz se os caracteres estiverem em minúsculas ou maiúsculas.
  • o operador lógico | ou pipe — Desde que a string termine com ‘0’, tanto faz que seja ‘eth0’ ou ‘wlan0’.
    Assim, ficam incluídos os resultados referentes à interface de rede a cabo e sem fio.

Captura de tela de 2013-02-22 14:34:04

LEIA MAIS:

Como encontrar arquivos com o comando grep

Você pode usar o utilitário também para encontrar arquivos de texto pelo seu conteúdo.
Para pesquisar recursivamente no diretório atual por arquivos que contenham a palavra “firefox”, use-o da seguinte forma:


grep -iR 'firefox' *.conf

comando grep para encontrar arquivos por conteúdo

Conclusão

Como dicas finais, ao fazer a pesquisa dentro de arquivos grandes, gosto de usar os parâmetros ‘-n’ e ‘--color‘.
O primeiro numera as linhas, tornando mais fácil, ao abrir o arquivo, encontrar algo que eu desejo alterar.
O segundo mostra a saída do comando em cores, o que também ajuda a discernir melhor os resultados.
Você pode obter mais informações sobre o comando ao executar o seu manual:


man grep

Divirta-se!