Casamento de padrões com expressões regulares no MySQL

Saiba como usar o operador REGEXP e expressões regulares para efetuar um casamento de padrões, em vez de uma comparação literal.
O uso de padrões SQL são implementados por outros sistemas de bancos de dados — o que os torna facilmente portáveis além do MySQL.
Por outro lado, são um tanto limitados.
Um exemplo desta limitação é que é fácil criar um padrão SQL %abc% para encontrar um string que contenha “abc” — mas é muito difícil criar um padrão que identifique strings contendo um destes caracteres “a”, “b” ou “c”.
Para este tipo de operação (e muitas outras), o MySQL provê suporte a um outro tipo de casamento de padrões, baseado em expressões regulares (ou regular expressions) e o operador REGEXP (ou NOT REGEXP).
As operações pelo REGEXP usam elementos diferentes dos operadores ‘%’ e ‘_’ (veja o link no final) — que não têm muita importância aqui.

O MySQL usa a implementação de Henry Spencer, que está de acordo com o POSIX 1003.2. O banco de dados usa a versão estendida para dar suporte a operações de casamento de padrões executadas pelo operador REGEXP nas declarações SQL.

Opcionalmente, é possível usar o operador RLIKE, que é sinônimo de REGEXP — presente para manter compatibilidade com o mSQL (miniSQL), o que facilita a portabilidade entre os dois sistemas.
Conheça os padrões na tabela abaixo:

Padrão O que ele reconhece
^ O início de uma string.
$ O fim de uma string.
. Todo e qualquer caractere.
[…] Todo caractere incluido entre os colchetes.
[^…] Qualquer caractere que não esteja incluído entre os colchetes.
p1|p2|p3 Operador de alternância; encontra quaisquer padrões que sejam p1, p2 ou p3.
* Nenhuma ou mais das instâncias do elemento precedente.
+ Uma ou mais das instâncias do elemento precedente.
{n} n instâncias do elemento precedente
{m,n} de m a n instâncias do elemento precedente.

Esta tabela será melhor entendida a partir do momento em que forem dados alguns exemplos de uso dos operadores. Por enquanto, guarde-a para consulta.
Usuários mais avançados do Unix ou GNU/Linux podem estar familiarizados com o uso de caracteres de padrão de expressões regulares. Programadores Perl, PHP e Python, provavelmente também.
No artigo Como encontrar padrões entre os seus dados no MySQL mostrei como encontrar substrings no começo ou no fim de uma cadeia de caracteres (string) ou em uma posição aribtrária ou específica dentro de uma string.
O mesmo pode ser feito com expressões regulares.

Exemplos de uso de expressões regulares, para encontrar informações no MySQL

Daqui pra frente, vamos ver como fazer uso dos operadores, expostos na tabela.
O exemplo, abaixo, mostra como encontrar todos nomes que terminam com “an”:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP 'an$';
+------------------+
| NomeCliente      |
+------------------+
| Aiko Tran        |
| Karleigh Norman  |
| Lillith Sloan    |
| Christen Kaufman |
| Camilla Freeman  |
+------------------+
5 rows in set (0.00 sec)

Para encontrar os nomes de clientes, na minha tabela, que começam com “Li”…

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP '^li';
+---------------+
| NomeCliente   |
+---------------+
| Lillith Sloan |
+---------------+
1 row in set (0.00 sec)

Percebeu a insensibilidade do REGEXP à caixa das letras?
Se eu quiser listar as strings que contém a sequência “man”, em qualquer posição:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP 'man';
+------------------+
| NomeCliente      |
+------------------+
| Irene Mann       |
| Karleigh Norman  |
| Christen Kaufman |
| Camilla Freeman  |
+------------------+
4 rows in set (0.00 sec)

No exemplo que segue, mostro como encontrar as strings que contém o caractere “i” a partir da 3a. posição na sequência:

SELECT NomeCliente FROM ClientesCidades WHERE NomeCliente REGEXP '^...i';
+------------------+
| NomeCliente      |
+------------------+
| Christine Mccall |
| April Hubbard    |
| Regina Swanson   |
| Christen Kaufman |
| Madison Love     |
| Camilla Freeman  |
| Cecilia Everett  |
| Kylie Bradshaw   |
| Zenia Carr       |
| Jolie Riggs      |
| Larissa Rich     |
| Vivian Richmond  |
| Indira Parker    |
| Calista Barrett  |
+------------------+
14 rows in set (0.00 sec)

A brincadeira com as expressões regulares poderia se estender por dias a fio. O assunto é extenso e será, com certeza, abordado em outros posts — e não somente quando estiver relacionado ao MySQL.

Referências

Como casar padrões no MySQL usando os operadores ‘%’ e ‘_’: https://elias.praciano.com/2015/04/como-encontrar-padroes-entre-os-seus-dados-no-mysql/
MySQL Regular Expressions with The REGEXP Operator: http://www.regular-expressions.info/mysql.html
Oracle documentation: https://dev.mysql.com/doc/refman/5.7/en/regexp.html

Como inserir o resultado de uma query SELECT em uma tabela MySQL

Neste post vou mostrar como pegar o resultado de uma consulta MySQL e gravá-lo em uma tabela preexistente, em vez de exibi-lo.
Se você deseja criar uma nova tabela a partir de uma query SELECT, sugiro ler o artigo Como criar uma tabela a partir de uma declaração SELECT no MySQL.
Mountain high and mysql logo
O servidor MySQL normalmente retorna o resultado de um SELECT ao cliente que o requisitou.
Se você faz uma requisição a partir do terminal cliente MySQL, é para onde os resultados serão enviados e exibidos.
Neste texto vou mostrar como enviar os resultados de um SELECT para dentro de uma outra tabela.
Copiar resultados de uma tabela para outra pode ser útil em vários casos:

  1. Se você estiver desenvolvendo um algoritmo que modifica uma tabela, certamente seria mais seguro trabalhar em uma cópia — o que evita ter que lidar com as consequências de um desastre em cima de informações importantes para você ou para a empresa em que você trabalha.
  2. Em algumas operações, pode ser interessante trabalhar em uma tabela temporária e, mais tarde, copiar os dados conferidos para a tabela definitiva.
  3. Você pode usar o recurso em um script, que toma dados de uma tabela maior e cria uma pequena tabela de serviços e vai atualizando a tabela principal aos poucos (ou em horários de menor fluxo na rede).
  4. Se você está aprendendo MySQL, pode ser interessante transferir dados para tabelas “de aprendizado” e deixar as tabelas de produção em paz.

Vou mostrar como usar o INSERT ... SELECT para obter informações de uma tabela e inseri-las em outra.
Para isto, vou fazer uso das tabelas ClientesCidades e CliCred. Sugiro adequar os exemplos às suas próprias tabelas. Contudo, se quiser, você pode pegar as minhas mal acabadas tabelas aqui.

Como inserir os resultados da query em uma tabela

A tabela CliCred tem 3 campos coincidentes com a tabela ClientesCidades. Veja o que o comando EXPLAIN mostra (em destaque):

EXPLAIN CliCred;
+-----------+-----------------------+------+-----+---------+----------------+
| Field     | Type                  | Null | Key | Default | Extra          |
+-----------+-----------------------+------+-----+---------+----------------+
| id        | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| cc_nome   | varchar(255)          | YES  |     | NULL    |                |
| cc_cidade | varchar(255)          | YES  |     | NULL    |                |
| cc_cred   | mediumint(9)          | YES  |     | NULL    |                |
| cc_limite | mediumint(9)          | YES  |     | NULL    |                |
+-----------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Embaixo, após o comando EXPLAIN, destaquei os campos que correspondem aos que foram destacados em cima:

EXPLAIN ClientesCidades;
+---------------+-----------------------+------+-----+---------+----------------+
| Field         | Type                  | Null | Key | Default | Extra          |
+---------------+-----------------------+------+-----+---------+----------------+
| id            | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| NomeCliente   | varchar(255)          | YES  |     | NULL    |                |
| IdadeCliente  | mediumint(9)          | YES  |     | NULL    |                |
| CidadeCliente | varchar(255)          | YES  |     | NULL    |                |
| CredCliente   | mediumint(9)          | YES  |     | NULL    |                |
+---------------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

Cada uma destas 2 tabelas tem 100 registros.
Ao final da operação, a tabela ClientesCidades terá recebido os 100 registros da tabela CliCred — e passará a ter 200 registros.
Veja:

INSERT INTO ClientesCidades (NomeCliente, CidadeCliente, CredCliente) SELECT cc_nome, cc_cidade, cc_cred FROM CliCred; 

Vamos analisar o comando dado:

  • INSERT INTO ClientesCidades — Determina em que tabela ocorrerá a alteração (inserção de novos dados).
  • (NomeCliente, CidadeCliente, CredCliente) — Estes são os campos (as colunas) que serão alterados na tabela ClientesCidades.
  • SELECT cc_nome, cc_cidade, cc_cred FROM CliCred — Seleciona os valores contidos nos campos cc_nome, cc_cidade, cc_cred na tabela CliCred, para inserir na primeira tabela.

Veja uma listagem parcial (apenas os 10 últimos registros) da nova tabela ClientesCidades:

SELECT * FROM ClientesCidades where id > 190;
+-----+-------------------+--------------+----------------+-------------+
| id  | NomeCliente       | IdadeCliente | CidadeCliente  | CredCliente |
+-----+-------------------+--------------+----------------+-------------+
| 191 | Paula Wiley       |         NULL | St. Catharines |         187 |
| 192 | Cathleen Thornton |         NULL | Rouvroy        |         289 |
| 193 | Laurel Espinoza   |         NULL | Boneffe        |         131 |
| 194 | Amber Sparks      |         NULL | Kailua         |         256 |
| 195 | Elizabeth Bean    |         NULL | Beaconsfield   |         147 |
| 196 | Eleanor Cleveland |         NULL | Rosciano       |         295 |
| 197 | Virginia Dillon   |         NULL | Metairie       |         209 |
| 198 | Jana Dyer         |         NULL | Birori         |         135 |
| 199 | Kendall Reid      |         NULL | Westkapelle    |         274 |
| 200 | Katell Robinson   |         NULL | Colonnella     |         149 |
+-----+-------------------+--------------+----------------+-------------+
10 rows in set (0.03 sec)

Em uma análise do resultado, note que:

  1. o último valor do campo id, em ClientesCidades, reflete o acréscimo de mais 100 registros. Agora são 200 registros;
  2. 100 nomes de clientes, de cidades e valores de Crédito foram adicionados nos campos NomeCliente, CidadeCliente e CredCliente;
  3. A tabela CliCred não tem o campo IdadeCliente, por isto o valor NULL (nulo) foi gravado nos campos correspondentes.

Como copiar todos os valores de uma tabela para outra

Isto é bem mais fácil e pode realizado com um comando mais enxuto e menor preocupação com a sintaxe.
Vamos supor a existência das tabelas tabela_Origem e tabela_Destino, ambas com estruturas idênticas.
Para copíar os valores da primeira para dentro da segunda, use o seguinte comando:

INSERT INTO tabela_Destino SELECT * FROM tabela_Origem;

E se eu quisesse copiar apenas as informações sobre clientes com créditos superiores a 300?
Vamos retomar (e alterar) o exemplo acima:

INSERT INTO ClientesCidades (NomeCliente, CidadeCliente, CredCliente) SELECT cc_nome, cc_cidade, cc_cred FROM CliCred WHERE cc_cred > 300; 

Como, no caso da minha tabela, nenhum cliente tem valor de crédito superior a 300, o resultado foi este:

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Uma última dica: Você pode usar a mesma tabela como origem e destino.

Divirta-se e tenha um bom aprendizado!

Como usar expressões com variáveis para calcular os valores da cláusula LIMIT no MySQL

A possibilidade de paginar ou seccionar os resultados de suas queries ou consultas MySQL, tornam a cláusula LIMIT uma das mais requisitadas por desenvolvedores.
logo PHP mesclado ao logo MySQL
Enquanto outras cláusulas são usadas para filtrar a exibição de resultados, LIMIT é usado para inibir o envio de resultados do servidor — motivo pelo qual ele ajuda a poupar o fluxo de informações inúteis a trafegar pela rede.
Se você quiser entender melhor o uso da cláusula LIMIT e das variáveis no MySQL, sugiro usar o quadro de pesquisa, no topo desta página, para encontrar outros posts sobre estes assuntos — vale a pena!

Como criar expressões usando a cláusula LIMIT do MySQL

O MySQL só vai aceitar inteiros literais em expressões — a menos que você envie suas consultas de dentro de um script. Neste caso, você tem mais flexibilidade para elaborar suas expressões e inserir os valores resultantes nas strings das consultas.
Se vocẽ tentar incluir uma expressão matemática, direto na linha de comando do seu cliente MySQL, vai obter um erro:

SELECT * FROM ClientesCidades LIMIT 2+2;

Isto, simplesmente, não é permitido — e vai provavelmente retornar um erro como o reproduzido abaixo:

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 '+2' at line 1

O uso de variáveis, também não é permitido, deste jeito:

SELECT * FROM profile LIMIT @pula_contagem, @exibir_contagem;

A expressão precisa ser criada, primeiro. Depois, seu resultado, pode ser inserido em uma query.
Mesmo de dentro do PHP, isto não é aceito:

$str = "SELECT * FROM ClientesCidades LIMIT $a + $b";

Para evitar o problema, tente elaborar a expressão primeiro:

$c = $a + $b;
$str = "SELECT * FROM ClientesCidades LIMIT $c";

Uma outra abordagem possível (também de dentro do PHP), seria esta:

$str = "SELECT * FROM ClientesCidades LIMIT ".($a + $b);

Fique atento para as aspas e para os parênteses.

Como seccionar uma lista, usando a cláusula LIMIT do MySQL

A cláusula LIMIT pode ser usada para obter informações contidas no meio de uma lista, e não apenas nas extremidades (início ou fim) da consulta.
Capa do tutorial MySQL - a cláusula LIMIT
O uso básico da cláusula LIMIT, já foi discutido no artigo Como usar LIMIT para selecionar registros do começo ou do fim de uma tabela no MySQL — sugiro-o como leitura complementar.
Neste texto, vou mostrar como obter determinadas linhas, no “miolo” de uma relação e como é possível fazer paginação, com LIMIT — ou seja, dividir uma relação em múltiplas páginas.
Para que o LIMIT possa realizar a tarefa é necessário que você diga em que posição, na lista, ele deve começar, além da quantidade de linhas que você deseja que sejam exibidas.
A cláusula LIMIT aceita 2 argumentos:

  • O primeiro determina quantas linhas devem ser “puladas” ou ignoradas.
  • O segundo determina quantas linhas da lista devem ser exibidas.

Isto significa que você pode usar a cláusula LIMIT para pular 2 linhas e mostram a próxima, por exemplo. Isto permite responder perguntas um pouco mais complexas, como “Qual o quinto maior valor num determinado campo?” ou “Qual o terceiro menor valor?” — que seria mais difíceis de responder com as funções MIN() ou MAX().
Vamos aos exemplos.

Como paginar resultados com a cláusula LIMIT, no MySQL

Distribuir em várias páginas os resultados de uma query MySQL com o LIMIT é muito fácil — ele é feito pra isso.
Veja um exemplo básico de paginação de 2 em 2 (para não ficar muito extenso):

SELECT idCli,nomeCli,emailCli FROM CadCli LIMIT 2;

Na primeira parte, peço a lista limitada aos 2 primeiros registros:

+-------+-----------------+--------------------------------+
| idCli | nomeCli         | emailCli                       |
+-------+-----------------+--------------------------------+
|     1 | Tana Mclaughlin | eget.tincidunt@magnisdis.net   |
|     2 | Jakeem Klein    | mauris.eu@Nuncmaurissapien.com |
+-------+-----------------+--------------------------------+
2 rows in set (0.00 sec)

A lista segue, pulando 2 resultados, limitada a 2 linhas:

SELECT idCli,nomeCli,emailCli FROM CadCli LIMIT 2,2;
+-------+-----------------+-------------------------------+
| idCli | nomeCli         | emailCli                      |
+-------+-----------------+-------------------------------+
|     3 | Wylie Carroll   | convallis.in.cursus@libero.ca |
|     4 | Colton Franklin | dui.lectus.rutrum@eusem.org   |
+-------+-----------------+-------------------------------+
2 rows in set (0.00 sec)

Concluo o exemplo, exibindo a próxima página, depois do quarto registro, limitado a 2 linhas:

 SELECT idCli,nomeCli,emailCli FROM CadCli LIMIT 4,2;
+-------+-----------------+------------------------------------------+
| idCli | nomeCli         | emailCli                                 |
+-------+-----------------+------------------------------------------+
|     5 | Amity Whitfield | risus.Quisque.libero@Praesenteudui.co.uk |
|     6 | Sade Carver     | Donec@Innec.com                          |
+-------+-----------------+------------------------------------------+
2 rows in set (0.00 sec)

Dentro de um script, podemos usar um loop para fazer este trabalho.


Nota: Você pode usar qualquer tabela disponível para você para praticar. Se você fizer questão de usar a mesma tabela deste artigo, é possível obtê-la aqui.

Como obter o maior ou menor valor de um campo, usando o LIMIT

Para realizar este trabalho, vou acrescentar a cláusula DESC em uma das queries.
Veja como obter o maior valor em uma coluna da tabela:

SELECT nomeCli, creditoCli FROM CadCli ORDER BY creditoCli DESC LIMIT 1;
+---------------+------------+
| nomeCli       | creditoCli |
+---------------+------------+
| Steven Stokes |      84831 |
+---------------+------------+
1 row in set (0.00 sec)

Se a pergunta fosse “Qual o email do cliente com o menor valor de crédito?”, a resposta poderia ser obtida assim:

SELECT nomeCli AS Cliente, emailCli AS "Email p/contato", creditoCli FROM CadCli ORDER BY creditoCli LIMIT 1;
+-----------------+----------------------------------+------------+
| Cliente         | Email p/contato                  | creditoCli |
+-----------------+----------------------------------+------------+
| Hanna Mccormick | molestie.tortor.nibh@indolor.org |        600 |
+-----------------+----------------------------------+------------+
1 row in set (0.00 sec)

E, se a pergunta for “Qual o nome do cliente com o segundo maior crédito?”

SELECT nomeCli AS Cliente, emailCli AS "Email p/contato", creditoCli FROM CadCli ORDER BY creditoCli DESC LIMIT 1,1;
+-------------+-----------------------+------------+
| Cliente     | Email p/contato       | creditoCli |
+-------------+-----------------------+------------+
| Yvonne Wise | habitant@odioEtiam.ca |      84531 |
+-------------+-----------------------+------------+
1 row in set (0.00 sec)

Espero que este post resolva as suas dúvidas sobre como seccionar relatórios no MySQL, como a cláusula LIMIT. Você pode usar a caixa de buscas, no topo do site, para encontrar tutoriais e receitas MySQL.

Como combinar nomes de colunas com a função CONCAT() do MySQL

No MySQL, a função CONCAT tem a função de concatenar strings, ou seja, juntar cadeias de caracteres com o objetivo de formar novos nomes para as suas colunas e enriquecer a apresentação dos dados da sua tabela.
Capa do tutorial da função concat no MySQL
No artigo Como nomear as colunas das tabelas no MySQL, mostrei como melhorar a apresentação das colunas ao exibir as informações da sua tabela — recomendo dar uma olhada.
Neste texto, vou mostrar como usar a função CONCAT() para juntar valores alfanuméricos e formar novos nomes de campos em uma tabela.

Como usar a função CONCAT, do MySQL, para juntar cadeias de caracteres

Em conjunto com o alias (ou AS) é possível criar nomes mais interessantes e significativos para as colunas exibidas de uma tabela.
Veja a sintaxe da função CONCAT():
CONCAT(string1,string2,string3...);
Pode ficar mais fácil de entender, dentro de um exemplo prático.
Veja como concatenar as strings “just”, “in” e “case”:
CONCAT("just","in","case");
O resultado da operação acima é justincase. Foi fácil?
Tome, como exemplo, a seguinte tabela:

SELECT * FROM ClientesCidades WHERE Id<10;
+----+-----------------+--------------+------------------+-------------+
| id | NomeCliente     | IdadeCliente | CidadeCliente    | CredCliente |
+----+-----------------+--------------+------------------+-------------+
|  1 | Dara Chase      |           40 | Buti             |          21 |
|  2 | Hanae Kane      |           26 | Whitehorse       |          28 |
|  3 | Jaden Moon      |           29 | Bertiolo         |          17 |
|  4 | Cathleen Harvey |           39 | Exeter           |          12 |
|  5 | Marcia Cole     |           49 | Fortune          |          13 |
|  6 | Winter Fischer  |           47 | Girifalco        |          53 |
|  7 | Irene Mann      |           48 | Rocca San Felice |          78 |
|  8 | Yetta Wood      |           46 | Doues            |          89 |
|  9 | Alfreda Church  |           38 | Tarnów           |          96 |
+----+-----------------+--------------+------------------+-------------+
9 rows in set (0.00 sec)

Vamos usar o CONCAT() e o AS para exibir a idade de cada cliente entre colchetes e ao lado de cada nome:

SELECT Id AS Ordem, CONCAT(NomeCliente,'[',IdadeCliente,']') AS 'Cliente[Idade]' FROM ClientesCidades WHERE Id<10;
+-------+---------------------+
| Ordem | Cliente[Idade]       |
+-------+---------------------+
|     1 | Dara Chase[40]      |
|     2 | Hanae Kane[26]      |
|     3 | Jaden Moon[29]      |
|     4 | Cathleen Harvey[39] |
|     5 | Marcia Cole[49]     |
|     6 | Winter Fischer[47]  |
|     7 | Irene Mann[48]      |
|     8 | Yetta Wood[46]      |
|     9 | Alfreda Church[38]  |
+-------+---------------------+
9 rows in set (0.00 sec)

Vamos elaborar melhor este exemplo, incluindo informações sobre sua cidade e o valor de seu crédito:

SELECT CONCAT(NomeCliente,', tem ',IdadeCliente,' anos de idade. Mora em ',CidadeCliente,' e possui R$ ',CredCliente,' em créditos.') AS 'Maiores correntistas' FROM ClientesCidades WHERE CredCliente > 97;
+------------------------------------------------------------------------------------+
| Maiores correntistas                                                               |
+------------------------------------------------------------------------------------+
| Cynthia Levy, tem 21 anos de idade. Mora em Pukekohe e possui R$ 99 em créditos.   |
| Tasha Walters, tem 19 anos de idade. Mora em Goutroux e possui R$ 98 em créditos.  |
| Zelenia Moss, tem 18 anos de idade. Mora em Elx e possui R$ 99 em créditos.        |
+------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

Experimente outras combinações, para obter resultados diferentes e fixar melhor o aprendizado.
Se quiser aprofundar o aprendizado, use caixa de busca, no topo deste site, para encontrar outros exemplos MySQL.
Divirta-se!