XOOPS Brasil

 

Capítulo 3. Tutorial de Introdução Do MySQL

Este capítulo fornece um tutorial de introdução ao MySQL demonstrando como usar o programa cliente mysql para criar e usar um banco de dados simples. mysql (algumas vezes apresentado como o ``terminal monitor'' ou apenas ``monitor'') é um programa interativo que lhe permite conectar a um servidor MySQL, executar consultas e visualizar os resultados. mysql pode também ser executado em modo batch: você coloca suas consultas em um arquivo, depois diz ao mysql para executar o conteúdo do arquivo. Cobrimos aqui ambas as formas de utilizar o mysql.

Para ver uma lista de opções conhecidas pelo mysql, chame-o com a opção --help:

shell> mysql --help

Este capítulo presume que o mysql está instalado na sua máquina e que um servidor MySQL está disponível para quem puder conectar. Se isto não for verdade, contate seu administrador MySQL. (Se você é o administrador, você precisará consultar outras seções deste manual.)

Este capítulo descreve todo o processo de configuração e uso de um banco de dados. Se você estiver interessado em apenas acessar um banco de dados já existente, podera pular as seções que descrevem como criar o banco de dados e suas respectivas tabelas.

Como este capítulo é um tutorial, vários detalhes são necessariamente omitidos. Consulte as seções relevantes do manual para mais informações sobre os tópicos cobertos aqui.

3.1. Conectando e Desconectando do Servidor

Para conectar ao servidor, normalmente você precisará fornecer um nome de usuário quando o mysql for chamado e, na maioria dos casos, uma senha. Se o servidor executa em uma máquina diferente de onde você está, você também precisará especificar um nome de máquina. Contate seu administrador para saber quais parâmetros de conexão você deve usar para conectar (isto é, qual máquina, usuário e senha usar). Uma vez que você saiba quais os parâmetros corretos, você deve estar pronto para conectar da seguinte forma:

shell> mysql -h servidor -u usuario -p
Enter password: ********

Os asteriscos (********) representam sua senha; digite-a quando o mysql mostrar o prompt Enter password:.

Se isto funcionar, você deve ver algumas informações iniciais seguidas de um prompt mysql>

shell> mysql -h host -u user -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 4.0.14-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

O prompt lhe diz que o mysql está pronto para que você digite os comandos.

Algumas instalações MySQL permitem aos usuários de se conectarem como usuários anônimos ao servidor executando na máquina local. Se isto é o caso na sua máquina, você deve conseguir conectar ao servidor chamando o mysql sem qualquer opção:

shell> mysql

Depois de você conectar com sucesso, você pode disconectar a qualquer hora digitando QUIT (ou \q) no prompt mysql>:

mysql> QUIT
Bye

No Unix, você também pode desconectar pressionando Control-D.

A maioria dos exemplos nas seções seguintes assumem que você já está conectado ao servidor. Isto é indicado pelo prompt mysql>.

3.2. Fazendo Consultas

Tenha certeza que você está conectado ao servidor, como discutido na seção anterior. Isto feito, não será selecionado nenhum banco de dados para trabalhar, mas não tem problemas. Neste momento, é mais importante saber um pouco sobre como fazer consultas do que já criar tabelas, carregar dados para elas, e recuperar dados delas. Esta seção descreve os princípios básicos da entrada de comandos, usando diversas consultas você pode tentar se familiarizar com o funcionamento do mysql.

Aqui está um comando simples que solicita ao servidor seu número de versão e a data atual. Digite-o como visto abaixo seguindo o prompt mysql> e digite a tecla RETURN:

mysql> SELECT VERSION(), CURRENT_DATE;
+--------------+--------------+
| version()    | CURRENT_DATE |
+--------------+--------------+
| 3.22.20a-log | 1999-03-19   |
+--------------+--------------+
1 row in set (0.01 sec)
mysql>

Esta consulta ilustra várias coisas sobre o mysql:

  • Um comando normalmente consiste de uma instrução SQL seguida por um ponto e vírgula. (Existem algumas exceções onde um ponto e vírgula podem ser omitidos. QUIT mencionado anteriormente, é um deles. Saberemos de outros mais tarde.)

  • Quando você emite um comando, o mysql o envia para o servidor para execução e mostra os resultados, depois imprime outro prompt mysql> para indicar que está pronto para outro comando.

  • O mysql mostra a saída da consulta em forma tabular (linhas e colunas). A primeira linha contém rótulos para as colunas. As linhas seguintes são o resultado da consulta. Normalmente, rótulos de colunas são os nomes das colunas que você busca das tabelas do banco de dados. Se você está recuperando o valor de uma expressão no lugar de uma coluna de tabela (como no exemplo já visto), o mysql rotula a coluna usando a própria expressão.

  • O mysql mostra quantas linhas foram retornadas e quanto tempo a consulta levou para executar, o que lhe dá uma vaga idéia da performance do servidor. Estes valores são impreciso porque eles representam tempo de relógio (Não tempo de CPU ou de máquina), e porque eles são afetados pelos fatores como a carga do servidor e latência de rede. (Para resumir, a linha ``rows in set'' não é mostrada nos exemplos seguintes deste capítulo.)

Palavras Chave podem ser entradas em qualquer caso de letra. As seguintes consultas são equivalentes:

mysql> SELECT VERSION(), CURRENT_DATE;
mysql> select version(), current_date;
mysql> SeLeCt vErSiOn(), current_DATE;

Aqui está outra consulta. Ela demonstra que você pode usar o mysql como uma calculadora simples:

mysql> SELECT SIN(PI()/4), (4+1)*5;
+-------------+---------+
| SIN(PI()/4) | (4+1)*5 |
+-------------+---------+
|    0.707107 |      25 |
+-------------+---------+

As consultas mostradas até agora têm sido instruções relativamente pequenas, de uma linha. Você pode também entrar com múltiplas instruções em uma única linha. Basta finalizar cada uma com um ponto e vírgula:

mysql> SELECT VERSION(); SELECT NOW();
+--------------+
| VERSION()    |
+--------------+
| 3.22.20a-log |
+--------------+

+---------------------+
| NOW()               |
+---------------------+
| 1999-03-19 00:15:33 |
+---------------------+

Um comando não necessita estar todo em uma única linha, então comandos extensos que necessitam de várias linhas não são um problema. O mysql determina onde sua instrução termina através do ponto e vírgula terminador, e não pelo final da linha de entrada. (Em outras palavras, o myqsl aceita entradas de livre formato: Ele coleta linhas de entrada mas não as executa até chegar o ponto e vírgula.)

Aqui está uma instrução simples usando múltiplas linhas:

mysql> SELECT
    -> USER()
    -> ,
    -> CURRENT_DATE;
+--------------------+--------------+
| USER()             | CURRENT_DATE |
+--------------------+--------------+
| joesmith@localhost | 1999-03-18   |
+--------------------+--------------+

Neste exemplo, note como o prompt altera de mysql> para -> depois de você entrar a primeira linha de uma consulta com múltiplas linhas. Isto é como o mysql indica que ainda não achou uma instrução completa e está esperando pelo resto. O prompt é seu amigo, porque ele fornece um retorno valioso. Se você usa este retorno, você sempre estará ciente do que o mysql está esperando.

Se você decidir que não deseja executar um comando que está no meio do processo de entrada, cancele-o digitando \c:

mysql> SELECT
    -> USER()
    -> \c
mysql>

Note o prompt aqui também. Ele troca para o mysql> depois de você digitar \c, fornecendo retorno para indicar que o mysql está pronto para um novo comando.

A seguinte tabela mostra cada dos prompts que você pode ver e resume o que ele significa sobre o estado em que o mysql se encontra:

PromptSignificado
mysql>Pronto para novo comando.
    ->Esperando pela próxima linha de comando com múltiplas linhas.
    '>Esperando pela próxima linha, coletando uma string que comece com uma aspas simples (‘'’).
    ">Esperando pela próxima linha, coletando uma string que comece com aspas duplas (‘"’).
    `>Esperando pela próxima linha, coletando uma string que comece com crase (‘`’).

É muito comum instruções multi-linhas ocorrerem por acidente quando você pretende publicar um comando em uma única linha, mas esquece o ponto e vírgula terminador. Neste caso,o mysql espera por mais entrada:

mysql> SELECT USER()
    ->

Se isto ocorrer com você (acha que entrou uma instrução mas a única resposta é um prompt ->), o mais provável é que o mysql está esperando pelo ponto e vírgula. Se você não perceber o que o prompt está lhe dizendo, você pode parar por um tempo antes de entender o que precisa fazer. Entre com um ponto e vírgula para completar a instrução, e o mysql irá executá-la:

mysql> SELECT USER()
    -> ;
+--------------------+
| USER()             |
+--------------------+
| joesmith@localhost |
+--------------------+

O prompt '> e "> ocorrem durante a coleta de strings. No MySQL, você pode escrever strings utilizando os caracteres ‘'’ ou ‘"’ (por exemplo, 'hello' ou "goodbye"), e o mysql permite a entrada de strings que consomem múltiplas linhas. Quando você ver um prompt '> ou ">, significa que você digitou uma linha contendo uma string que começa com um caracter de aspas ‘'’ ou ‘"’ mas ainda não entrou com a aspas que termina a string. Isto é bom se você realmente está entrando com uma string com múltiplas linhas, mas qual é a probalidade disto acontecer ? Não muita. Geralmente, os prompts '> e "> indicam que você, por algum descuido, esqueceu algum caracter de aspas. Por exemplo:

mysql> SELECT * FROM minha_tabela WHERE nome = "Smith AND idade < 30;
    ">

Se você entrar esta sentença SELECT, apertar ENTER e esperar pelo resultado, nada irá acontecer. Em vez de se perguntar o porquê desta query demorar tanto tempo, perceba a pista fornecida pelo prompt ">. Ele lhe diz que o mysql espera pelo resto de uma string não terminada. (Você ve o erro na declaração? Falta a segunda aspas na string "Smith.)

O que fazer neste ponto ? A coisa mais simples é cancelar o comando. Entretanto, você não pode simplesmente digitar \c neste caso, porque o mysql o intrerpreta como parte da string que está coletando! Digite o caracter de aspas para fechar (então o mysql sabe que você fechou a string), então digite \c:

mysql> SELECT * FROM minha_tabela WHERE nome = "Smith AND idade < 30;
    "> "\c
mysql>

O prompt volta para mysql>, indicando que o mysql está pronto para um novo comando.

O prompt `> é similar aos prompts '> e ">, mas indica que você começou mas não completou um identificados citado com o sinal de crase.

É importante saber o que os prompts '>, "> e `> significam, porque se você entrar sem querer com uma string sem terminação, quaisquer linhas seguintes que forem digitadas serão ignoradas pelo mysql --- incluindo uma linha contendo QUIT! Isto pode ser um pouco confuso, especialmente se você não sabe que você precisa fornecer as aspas finais antes poder cancelar o comando atual.

3.3. Criação e Utilização de um Banco de Dados

Agora que você já sabe como entrar com os comandos, é hora de acessar um banco de dados.

Suponha que você tenha diversos animais de estimação em sua casa (menagerie) e você gostaria de ter o registro de vários tipos de informações sobre eles. Você pode fazer isto criando tabelas para armazenar seus dados e carregá-los com a informação desejada. Depois você pode responder diferentes tipos de questões sobre seus animais recuperando dados das tabelas. Esta seção mostrará como:

  • Criar um banco de dados

  • Criar uma tabela

  • Carregar dados na tabela

  • Recuperar dados de uma tabela de várias maneiras

  • Usar múltiplas tabelas

O banco de dados menagerie será simples (deliberadamente), mas não é difícil pensar em situações na vida real em que um tipo similar de banco de dados pode ser usado. Por exemplo, um banco de dados deste tipo pode ser usado por um fazendeiro para gerenciar seu estoque de animais, ou por um veterinário para gerenciar registros de seus pacientes. Uma distribuição do menagerie contendo algumas das consultas e dados de exemplos usados nas seções seguintes podem ser obtidas do site Web do MySQL. Estão disponíveis tanto no formato tar comprimido (http://downloads.mysql.com/docs/menagerie-db.tar.gz) como no formato Zip (http://downloads.mysql.com/docs/menagerie-db.zip).

Utilize a instrução SHOW para saber quais bancos de dados existem atualmente no servidor:

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
| tmp      |
+----------+

A lista de bancos de dados provavelmente será diferente na sua máquina, mas os bancos de dados mysql e test provavelmente estarão entre eles. O banco de dados mysql é necessário porque ele descreve privilégios de acessos de usuários. O banco de dados test é geralamente fornecido como um espaço para que os usuários possam fazer testes.

Note que você não pode ver todos os banco de dados se você nãi tiver o privilégio SHOW DATABASES. See Seção 4.4.1, “A Sintaxe de GRANT e REVOKE.

Se o banco de dados test existir, tente acessá-lo:

mysql> USE test
Database changed

Perceba que o USE, como o QUIT, não necessitam de um ponto e vírgula. (Você pode terminar tais declarações com uma ponto e vírgula se gostar; isto não importa) A instrução USE é especial em outra maneira, também: Ela deve ser usada em uma única linha.

Você opde usar o banco de dados test (Se você tiver acesso a ele) para os exemplos que seguem mas qualquer coisa que você criar neste banco de dados pode ser removido por qualquer um com acesso a ele. Por esta razão, você provavelmente deve pedir permissão ao seu administrador MySQL para usar um banco de dados próprio. Suponha que você o chame de menagerie. O administrador precisar executar um comando como este:

mysql> GRANT ALL ON menagerie.* TO 'your_mysql_name'@'your_client_host';

onde seu_usuário_mysql é o nome do usuário MySQL atribuido a você e your_client_host é a máquina da qual você se conecta ao servidor.

3.3.1. Criando e Selecionando um Banco de Dados

Se o administrador criar seu banco de dados quando configurar as suas permissões, você pode começar a usá-lo. Senão, você mesmo precisa criá-lo:

mysql> CREATE DATABASE menagerie;

No Unix, nomes de bancos de dados são caso sensitivo (ao contrário das palavras chave SQL), portanto você deve sempre fazer referência ao seu banco de dados como menagerie e não Menagerie, MENAGERIE ou outra variação. Isto também é verdade para nomes de tabelas. (No Windows, esta restrição não se aplica, entiretanto você deve referenciar os bancos de dados e tabelas usando o mesmo caso em toda a parte da consulta.)

Criar um bancos de dados não o seleciona para o uso; você deve fazer isso de forma explícita. Para fazer o menagerie o banco de dados atual, use o comando:

mysql> USE menagerie
Database changed

Seu banco de dados necessita ser criado somente uma única vez, mas você deve selecioná-lo para o uso cada vez que você iniciar uma seção mysql. Você pode fazer isso usando a instrução USE como visto no exemplo. Uma forma alternativa é selecionar o banco de dados na linha de comando quando você chamar o mysql. Apenas especifique seu nome depois de qualquer parâmetro de conexão que você pode precisar fornecer. Por exemplo:

shell> mysql -h servidor -u usuario -p menagerie
Enter password: ********

Perceba que menagerie não é sua senha no comando mostrado. Se você precisar passar sua senha na linha de comando depois da opção -p, você deve fazê-lo sem usar espaços (por exemplo, -pminhasenha e não como em -p minhasenha). Entretando, colocando sua senha na linha de comando não é recomendado, porque isto expõe sua senha permitindo que outro usuário utilize a sua máquina.

3.3.2. Criando uma Tabela

Criar o banco de dados é a parte fácil, mas neste ponto ele está vazio, como o SHOW TABLES mostrará:

mysql> SHOW TABLES;
Empty set (0.00 sec)

A parte mais difícil é decidir qual a estrutura que seu banco de dados deve ter: quais tabelas você precisará e que colunas estarão em cada uma delas.

Você irá precisar de uma tabela para guardar um registro para cada um de seus animais de estimação. Esta tabela pode ser chamada pet, e ela deve conter, pelo menos, o nome de cada animal. Como o nome por si só não é muito interessante, a tabela deverá conter outras informações. Por exemplo, se mais de uma pessoa na sua família também tem animais, você pode desejar listar cada dono. Você pode também desejargravar algumas informações descritivas básicas como espécie e sexo.

Que tal a idade? Pode ser do interesse, mas não é uma boa coisa para se armazenar em um banco de dados. A idade muda à medida em que o tempo passa, o que significa que você sempre terá de atualizar seus registros. Em vez disso, é melhor armazenar um valor fixo como a data de nascimento. Então, sempre que você precisar da idade, basta você calculá-la como a diferença entre a data atual e a data de aniversário. O MySQL fornece funções para fazer aritmética de datas, então isto não é difícil. Armazenando datas de aniversário no lugar da idade também oferece outras vantagens:

  • Você pode usar o banco de dados para tarefas como gerar lembretes para aniversários que estão chegando. (Se você pensa que este tipo de query é algo bobo, perceba que é a mesma questão que você perguntar no contexto de um banco de dados comercial para identificar clientes para quais você precisará enviar cartão de aniversário, para um toque pessoal assistido pelo computador.)

  • Você pode calcular a idade em relação a outras datas diferente da data atual. Por exemplo, se você armazenar a data da morte no banco de dados, você poderá facilmente calcular qual a idade que o bicho tinha quando morreu.

Você provavelmente pode pensar em outros tipos de informações que poderão ser úteis na tabela pet, mas as identificadas até o momento são suficientes por agora: nome(name), dono(owner), espécie(species), sexo(sex), data de nascimento(birth) e data da morte(death).

Utilize a senteça CREATE TABLE para especificar o layout de sua tabela:

mysql> CREATE TABLE pet (nome VARCHAR(20), owner VARCHAR(20),
    -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

VARCHAR é uma boa escolha para os campos name, owner, e species porque os valores da coluna são de tamanho variável. Os tamanhos destas colunas não precisam necessáriamente de ser os mesmos e não precisam ser 20. Você pode escolher qualquer tamanho de 1 a 255, o que você achar melhor. (Se você não fizer uma boa escolha e depois precisar de um campo maior, o MySQL fornece o comando ALTER TABLE.)

O sexo dos animais podem ser representados em várias formas, por exemplo, "m" e "f" ou mesmo "macho" e "fêmea". É mais simples usar os caracteres "m" e "f".

O uso do tipo de dados DATE para as colunas birth e death são obviamente a melhor escolha.

Agora que você criou uma tabela, a instrução SHOW TABLES deve produzir alguma saída:

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| pet                 |
+---------------------+

Para verificar se sua tabela foi criada da forma que você esperava, utilize a instrução DESCRIBE:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

Você pode usar DESCRIBE a qualquer hora, por exemplo, se você esquecer os nomes das colunas na sua tabela ou de que tipos elas têm.

3.3.3. Carregando dados em uma tabela

Depois de criar sua tabela, você precisará povoá-la. As instruções LOAD DATA e INSERT são úteis para isto.

Suponha que seu registro de animais possa ser descrito como é abaixo: (Observe que o MySQL espera datas no formato AAAA-MM-DD; isto pode ser diferente do que você está acostumado.)

nameownerspeciessexbirthdeath
FluffyHaroldcatf1993-02-04 
ClawsGwencatm1994-03-17 
BuffyHarolddogf1989-05-13 
FangBennydogm1990-08-27 
BowserDianedogm1979-08-311995-07-29
ChirpyGwenbirdf1998-09-11 
WhistlerGwenbird 1997-12-09 
SlimBennysnakem1996-04-29 

Como você está começando com uma tabela vazia, uma forma simples de povoá-la é criar um arquivo texto contendo uma linha para cada um de seus animais, e depois carregar o conteúdo do arquivo para a tabela com uma simples instrução.

Você pode criar um arquivo texto pet.txt contendo um registro por linha, com valores separado por tabulações e na mesma ordem em que as colunas foram listadas na instrução CREATE TABLE. Para valores em falta (como sexo desconhecido ou data da morte para animais que ainda estão vivos), você pode usar valores NULL. Para representá-lo em seu arquivo texto, use \N (barra invertidam N maíusculo). Por exemplo, o registro para Whistler the bird podem parecer com isto (onde o espaço em branco entre os valores é um simples caractere de tabulação):

nameownerspeciessexbirthdeath
WhistlerGwenbird\N1997-12-09\N

Para carregar o arquivo texto pet.txt na tabela pet, use este comando:

mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;

Você pode especificar o valor do separador de colunas e o marcador de final de linha explicitamente na instrução LOAD DATA se você desejar. Mas os valores omitidos são suficientes para a instrução ler o arquivo pet.txt corretamente.

Se a instrução falhar, é desejável que a sua instalação do MySQL não tenha a capacidade do arquivo local habilitada por padrão. Veja Seção 4.3.4, “Detalhes de Segurança com LOAD DATA LOCAL para informações sobre como alterar isto.

Quando você desejar adicionar novos registros um a um, a instrução INSERT é usada. Na sua forma mais simples, você fornece valores para cada coluna, na ordem em que as colunas foram listadas na instrução CREATE TABLE. Suponha que Diane tenha um novo hamster chamado Puffball. Você pode adicionar um registro utilizando uma instrução INSERT desta forma:

mysql> INSERT INTO pet
    -> VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL);

Perceba que os valores de string e datas são especificados aqui como strings com aspas. Com o INSERT você também pode inserir NULL diretamente para representar um valor em falta. Não pode ser usado \N como você fez com LOAD DATA.

A partir deste exemplo, você deverá perceber que existem várias outras formas envolvidas para carregar seus registros inicialmente utilizando diversas instruções INSERT do que uma simples instrução LOAD DATA.

3.3.4. Recuperando Informações de uma Tabela

A instrução SELECT é usada para recuperar informações de uma tabela. A forma geral da instrução é:

SELECT o_que_mostrar
FROM de_qual_tabela
WHERE condições_para_satisfazer;

o_que_mostrar indica o que você deseja ver. Isto pode ser uma lista de colunas ou * para indicar ``todas colunas.'' de_qual_tabela indica a tabela de onde você deseja recuperar os dados. A cláusula WHERE é opcional. Se estiver presente, condições_para_satisfazer especificam as condições que os registros devem satisfazer para fazer parte do resultado.

3.3.4.1. Selecionando Todos os Dados

A forma mais simples do SELECT recuperar tudo de uma tabela:

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+

Esta forma do SELECT é útil se você deseja ver sua tabela inteira como agora, depois de você acabar de carregá-la com os dados iniciais. Por exempo, você pode pensar que a data de nascimento do Bowser não está correta. Consultando seus papéis originais de pedigree, descobriu que o ano correto do nascimento deve ser 1989, não 1979.

Existem pelo menos duas formas de corrigir isto:

  • Edite o arquivo pet.txt para corrigir o erro, depois limpe a tabela e recarregue-o usando DELETE e LOAD DATA:

    
    
    mysql> DELETE FROM pet;
    mysql> LOAD DATA LOCAL INFILE "pet.txt" INTO TABLE pet;
    

    Entretanto, se você fizer isto, você também deve refazer a entrada para Puffball.

  • Corrigir somente o registro errado com uma instrução UPDATE:

    mysql> UPDATE pet SET birth = "1989-08-31" WHERE name = "Bowser";
    

    O UPDATE altera apenas o registro em questão e não exige que você recarregue a tabela.

3.3.4.2. Selecionando Registros Específicos

Como foi mostrado na seção anterior, é fácil recuperar uma tabela inteira. Apenas omita a cláusula WHERE da instrução SELECT. Mas normalmente você não quer ver toda a tabela, particularmente quando a tabela ficar grande. Em vez disso, você estará mais interessado em ter a resposta de uma questão em particular, no qual você especifica detalhes da informação que deseja. Vamos ver algumas consultas de seleção nos termos das questões sobre seus animais.

Você pode selecionar apenas registros específicos da sua tabela. Por exemplo, se você deseja verificar a alteração que fez na data de nascimento do Bowser, selecione o registro desta forma:

mysql> SELECT * FROM pet WHERE name = "Bowser";
+--------+-------+---------+------+------------+------------+
| name   | owner | species | sex  | birth      | death      |
+--------+-------+---------+------+------------+------------+
| Bowser | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+-------+---------+------+------------+------------+

A saída confirma que o ano foi gravado corretamente agora como 1989 e não 1979.

Comparações de strings normalmente são caso insensitivo, então você pode especificar o nome como "bowser", "BOWSER", etc. O resultado da pesquisa será o mesmo.

Você pode especificar condições em qualquer coluna, não apenas no name. Por exemplo, se você deseja saber quais foram os animais que nasceram depois de 1998, teste o campo birth:

mysql> SELECT * FROM pet WHERE birth >= "1998-1-1";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Puffball | Diane | hamster | f    | 1999-03-30 | NULL  |
+----------+-------+---------+------+------------+-------+

Você pode combinar condições, por exemplo, para encontrar cadelas (dog/f):

mysql> SELECT * FROM pet WHERE species = "dog" AND sex = "f";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

A consulta anterior utiliza o operador lógico AND (e). Existe também um operador OR (ou):

mysql> SELECT * FROM pet WHERE species = "snake" OR species = "bird";
+----------+-------+---------+------+------------+-------+
| name     | owner | species | sex  | birth      | death |
+----------+-------+---------+------+------------+-------+
| Chirpy   | Gwen  | bird    | f    | 1998-09-11 | NULL  |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL  |
| Slim     | Benny | snake   | m    | 1996-04-29 | NULL  |
+----------+-------+---------+------+------------+-------+

AND e OR podem ser misturados, embora AND tem maior precedência que OR. Se você usar ambos os operadores, é uma ótima idéia usar parênteses para indicar explicitamente quais condições devem ser agrupadas:

mysql> SELECT * FROM pet WHERE (species = "cat" AND sex = "m")
    -> OR (species = "dog" AND sex = "f");
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

3.3.4.3. Selecionando Colunas Específicas

Se você não desejar ver todo o registro de sua tabela, especifique as colunas em que você estiver interessado, separado por vírgulas. Por exemplo, se você deseja saber quando seus animais nasceram, selecione as colunas name e birth:

mysql> SELECT name, birth FROM pet;
+----------+------------+
| name     | birth      |
+----------+------------+
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Buffy    | 1989-05-13 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Puffball | 1999-03-30 |
+----------+------------+

Para saber quem são os donos dos animais, use esta consulta:

mysql> SELECT owner FROM pet;
+--------+
| owner  |
+--------+
| Harold |
| Gwen   |
| Harold |
| Benny  |
| Diane  |
| Gwen   |
| Gwen   |
| Benny  |
| Diane  |
+--------+

Entretanto, perceba que a query simplesmente retornou o campo owner de cada registro, e alguns deles apareceram mais de uma vez. Para minimizar a saída, recupere cada registro apenas uma vez, adicionando a palavra chave DISTINCT:

mysql> SELECT DISTINCT owner FROM pet;
+--------+
| owner  |
+--------+
| Benny  |
| Diane  |
| Gwen   |
| Harold |
+--------+

Você pode usar uma cláusula WHERE para combinar seleção de registros com seleção de colunas. Por exemplo, para obter a data de nascimento somente dos gatos e cachorros, utilize esta query:

mysql> SELECT name, species, birth FROM pet
    -> WHERE species = "dog" OR species = "cat";
+--------+---------+------------+
| name   | species | birth      |
+--------+---------+------------+
| Fluffy | cat     | 1993-02-04 |
| Claws  | cat     | 1994-03-17 |
| Buffy  | dog     | 1989-05-13 |
| Fang   | dog     | 1990-08-27 |
| Bowser | dog     | 1989-08-31 |
+--------+---------+------------+

3.3.4.4. Ordenando Registros

Você deve ter percebido nos exemplos anteriores que os registros retornados não são mostrados de forma ordenada. Normalmente é mais fácil examinar a saída da consulta quando os registros são ordenados com algum sentido. Para ordenar o resultado, utilize uma cláusula ORDER BY.

Aqui está o dia de nascimento dos animais, ordenado por data:

mysql> SELECT name, birth FROM pet ORDER BY birth;
+----------+------------+
| name     | birth      |
+----------+------------+
| Buffy    | 1989-05-13 |
| Bowser   | 1989-08-31 |
| Fang     | 1990-08-27 |
| Fluffy   | 1993-02-04 |
| Claws    | 1994-03-17 |
| Slim     | 1996-04-29 |
| Whistler | 1997-12-09 |
| Chirpy   | 1998-09-11 |
| Puffball | 1999-03-30 |
+----------+------------+

Em colunas de tipo de caracter, ordenaição ¯ como qualquer outra operação de comparação ¯ é normalmente realizada no modo caso insensitivo. Isto significa que a ordem será indefinida para colunas que são idênticas exceto quanto ao caso da letra. Você pode forçar uma ordenação em caso senitivo para uma coluna usando a coerção BINARY: ORDER BY BINARY(campo).

A ordenação padrão é crescente, com os valores menores em primeiro. Para ordenação na ordem reversa, adicione a palavra chave DESC (descendente) ao nome da coluna que deve ser ordenada:

mysql> SELECT name, birth FROM pet ORDER BY birth DESC;
+----------+------------+
| name     | birth      |
+----------+------------+
| Puffball | 1999-03-30 |
| Chirpy   | 1998-09-11 |
| Whistler | 1997-12-09 |
| Slim     | 1996-04-29 |
| Claws    | 1994-03-17 |
| Fluffy   | 1993-02-04 |
| Fang     | 1990-08-27 |
| Bowser   | 1989-08-31 |
| Buffy    | 1989-05-13 |
+----------+------------+

Você pode ordenar por múltiplas colunas e você pode classificar colunas em direções diferentes. Por exemplo, para ordenar o tipo de animal em ordem crescente, depois por dia de nascimento dentro do tipo de animal em ordem decrescente (com os mais novos primeiro), utilize a seguinte consulta:

mysql> SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
+----------+---------+------------+
| name     | species | birth      |
+----------+---------+------------+
| Chirpy   | bird    | 1998-09-11 |
| Whistler | bird    | 1997-12-09 |
| Claws    | cat     | 1994-03-17 |
| Fluffy   | cat     | 1993-02-04 |
| Fang     | dog     | 1990-08-27 |
| Bowser   | dog     | 1989-08-31 |
| Buffy    | dog     | 1989-05-13 |
| Puffball | hamster | 1999-03-30 |
| Slim     | snake   | 1996-04-29 |
+----------+---------+------------+

Perceba que a palavra chave DESC aplica somente para o nome da coluna precedente (birth); ela não afeta a ordenação da coluna species.

3.3.4.5. Cálculo de Datas

O MySQL fornece várias funções que você pode usar para realizar cálculos em datas, por exemplo, para calcular idades ou extrair partes de datas.

Para determinar quantos anos cada um do seus animais tem, compute a diferença do ano da data atual e a data de nascimento (birth), depois subtraia se a o dia/mês da data atual for anterior ao dia/mês da data de nascimento. A consulta seguinte, mostra, para cada animal, a data de nascimento, a data atual e a idade em anos.

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
+----------+------------+------------+------+

Aqui, YEAR() separa a parte do ano de uma data e RIGHT() separa os cinco caracteres mais a direita que representam a parte da data MM-DD. A parte da expressão que compara os valores MM-DD resulta em 1 ou 0, o qual ajusta a diferença do ano um ano abaixo se CURDATE ocorrer mais cedo, no ano, que birth. A expressão completa é um tanto deselegante, então um apelido (age) é usado para obter uma saída mais significativa.

A consulta funciona, mas o resultado pode ser mais compreensível se os registros forem apresentados em alguma ordem. Isto pode ser feito adicionando uma cláusula ORDER BY name para ordenar a saída pelo nome:

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY name;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
+----------+------------+------------+------+

Para ordenar a saída por age em vez de name, é só utilizar uma cláusua ORDER BY diferente:

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY age;
+----------+------------+------------+------+
| name     | birth      | CURDATE()  | age  |
+----------+------------+------------+------+
| Chirpy   | 1998-09-11 | 2003-08-19 |    4 |
| Puffball | 1999-03-30 | 2003-08-19 |    4 |
| Whistler | 1997-12-09 | 2003-08-19 |    5 |
| Slim     | 1996-04-29 | 2003-08-19 |    7 |
| Claws    | 1994-03-17 | 2003-08-19 |    9 |
| Fluffy   | 1993-02-04 | 2003-08-19 |   10 |
| Fang     | 1990-08-27 | 2003-08-19 |   12 |
| Bowser   | 1989-08-31 | 2003-08-19 |   13 |
| Buffy    | 1989-05-13 | 2003-08-19 |   14 |
+----------+------------+------------+------+

Uma consulta similar pode ser usada para determinar a idade na morte para animais que morreram. Para determinar quais são os animais, confira se o valor de death não é NULL. Depois para estes com valores não-NULL, compute a diferença entre os valores dos campos death e birth:

mysql> SELECT name, birth, death,
    -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;
+--------+------------+------------+------+
| name   | birth      | death      | age  |
+--------+------------+------------+------+
| Bowser | 1989-08-31 | 1995-07-29 |    5 |
+--------+------------+------------+------+

A consulta usa death IS NOT NULL em vez de death != NULL porque NULL é um valor especial que não pode ser comparada usando operadores comuns de comparação. Isto será explicado depois. Veja mais informações sobre isto na Seção 3.3.4.6, “Trabalhando com Valores Nulos (NULL)”.

E se você desejar saber quais animais fazem aniversário no próximo mês? Para este tipo de cálculo, ano e dia são irrelevantes; você simplesmente deseja extrair a parte do mês da coluna birth. O MySQL fornece diversas funções para extrair partes da data, como em YEAR(), MONTH() e DAYOFMONTH(). MONTH é a função apropriada aqui. Para ver como ela funciona, execute uma consulta simples que mostre o valor de birth e MONTH(birth):

mysql> SELECT name, birth, MONTH(birth) FROM pet;
+----------+------------+--------------+
| name     | birth      | MONTH(birth) |
+----------+------------+--------------+
| Fluffy   | 1993-02-04 |            2 |
| Claws    | 1994-03-17 |            3 |
| Buffy    | 1989-05-13 |            5 |
| Fang     | 1990-08-27 |            8 |
| Bowser   | 1989-08-31 |            8 |
| Chirpy   | 1998-09-11 |            9 |
| Whistler | 1997-12-09 |           12 |
| Slim     | 1996-04-29 |            4 |
| Puffball | 1999-03-30 |            3 |
+----------+------------+--------------+

Encontrar animais com aníversário no próximo mês também é fácil. Suponha que o mês atual é abril. Então o valor do mês é 4 e você procura por animais nascidos em Maio (mês 5) assim:

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;
+-------+------------+
| name  | birth      |
+-------+------------+
| Buffy | 1989-05-13 |
+-------+------------+

Existe uma pequena complicação se o mês atual é Dezembro, é claro. Você não pode apenas adicionar um para o número do mês (12) e procurar por animais nascidos no mês 13, porque não existe tal mês. O certo seria procurar por animais nascidos em Janeiro (mês 1).

Você pode também escrever uma consulta para que funcione sem importar qual é o mês atual. Assim você não têm quee usar um número de mês em particular na consulta. DATE_ADD() permite adicionar um intervalo de tempo para uma data fornecida. Se você adicionar um mês para o valor de CURDATE, então extrair a parte do mês com MONTH(), o resultado é o mês no qual você deseja procurar por aniversários:

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(), INTERVAL 1 MONTH));

Uma maneira diferente para realizar a mesma tarefa é adicionar 1 para obter o mês seguinte ao atual (depois de usar a função módulo (MOD) para o valor do mês retornar 0 se ele for 12):

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

Perceba que MONTH retorna um número entre 1 e 12. E MOD(alguma_coisa,12) retorna um número entre 0 e 11. Então a adição tem que ser feita depois do MOD(), senão iríamos de Novembro (11) para Janeiro (1).

3.3.4.6. Trabalhando com Valores Nulos (NULL)

O valor NULL pode ser supreendente até você usá-lo. Conceitualmente, NULL significa valor em falta ou valor desconhecido e é tratado de uma forma diferente de outros valores. Para testar o valor NULL, você não pode usar os operadores de comparações aritméticas como em =, <, ou !=. Para demonstrar para você mesmo, tente executar a seguinte consulta:

mysql> SELECT 1 = NULL, 1 != NULL, 1 < NULL, 1 > NULL;
+----------+-----------+----------+----------+
| 1 = NULL | 1 != NULL | 1 < NULL | 1 > NULL |
+----------+-----------+----------+----------+
|     NULL |      NULL |     NULL |     NULL |
+----------+-----------+----------+----------+

Claramente você não obterá resultados significativos destas comparações. Utilize os operadores IS NULL e IS NOT NULL no lugar:

mysql> SELECT 1 IS NULL, 1 IS NOT NULL;
+-----------+---------------+
| 1 IS NULL | 1 IS NOT NULL |
+-----------+---------------+
|         0 |             1 |
+-----------+---------------+

No MySQL, 0 ou NULL significa falso e o resto é verdadeiro. O valor verdadeiro por o padrão em uma operação booleana é 1.

Este tratamento especial de NULL é porque, na seção anterior, foi necessário determinar quais animais não estavam mais vivos usando death IS NOT NULL no lugar de death <> NULL.

Dois valores NULL são considerados como iguais em um GROUP BY.

Ao fazer um ORDER BY, valores NULL são apresentados primeiro se você fizer ORDER BY ... ASC e por último se você fizer ORDER BY ... DESC.

Note que o MySQL 4.0.2 a 4.0.10 sempre ordenam, incorretamente, valores NULL em primeiro independente da ordem escolhida.

3.3.4.7. Combinação de padrões

O MySQL fornece combinação de padrões do SQL bem como na forma de combinação de padrões baseado nas expressões regulares extendidas similares àquelas usadas pelos utilitários Unix como o vi, grep e sed.

A combinação de padrões SQL lhe permite você usar _ para coincidir qualquer caractere simples e % para coincidir um número arbitrário de caracteres (incluindo zero caracter). No MySQL, padrões SQL são caso insensitivo por padrão. Alguns exemplos são vistos abaixo. Perceba que você não usa = ou != quando usar padrões SQL; use os operadores de comparação LIKE ou NOT LIKE neste caso.

Para encontrar nomes começando com ‘b’:

mysql> SELECT * FROM pet WHERE name LIKE "b%";
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

Para encontrar nomes com o final 'fy':

mysql> SELECT * FROM pet WHERE name LIKE "%fy";
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

Para encontrar nomes contendo um ‘w’:

mysql> SELECT * FROM pet WHERE name LIKE "%w%";
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

Para encontrar nomes contendo exatamente cinco caracteres, use cinco instâncias do caracter ‘_’:

mysql> SELECT * FROM pet WHERE name LIKE "_____";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

O outro tipo de combinação de padrões fornecido pelo MySQL usa expressões regulares extendidas. Quando você testa por uma combinação para este tipo de padrão, utilize os operadores REGEXP e NOT REGEXP (ou RLIKE e NOT RLIKE, que são sinônimos).

Algumas características das expressões regulares extendidas são:

  • .’ combina qualquer caractere único

  • Uma classe de caracteres '[...]' combina qualquer caractere que consta dentro dos colchetes. Por exemplo, '[abc]' combina com ‘a’, ‘b’, ou ‘c’. Para nomear uma sequência de caracteres utilize um traço. '[a-z]' combina com qualquer letra e '[0-9]' combina com qualquer dígito.

  • *’ combina com nenhuma ou mais instâncias de sua precedência. Por exemplo, 'x*' combina com qualquer número de caracteres ‘x’, '[0-9]*' combina com qualquer número de dígitos e '.*' combina com qualquer número de qualquer coisa.

  • Um padrão REGEXP casa com sucesso se ele ocorre em algum lugar no valor sendo testado. (Ele difere do padrão LIKE, que só obtem suceeso se eles combinarem com todo o valor.)

  • Para fazer com que um padrão deva combinar com o começo ou o fim de um valor sendo testado, utilize ‘^’ no começo ou ‘$’ no final do padrão.

Para demonstrar como expressões regulares extendidas funcionam, as consultas com LIKE mostradas acima foram reescritas abaixo usando REGEXP.

Para encontrar nomes começando com ‘b’, utilize ‘^’ para combinar com o começo do nome:

mysql> SELECT * FROM pet WHERE name REGEXP "^b";
+--------+--------+---------+------+------------+------------+
| name   | owner  | species | sex  | birth      | death      |
+--------+--------+---------+------+------------+------------+
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL       |
| Bowser | Diane  | dog     | m    | 1989-08-31 | 1995-07-29 |
+--------+--------+---------+------+------------+------------+

Antes da versão 3.23.4 do MySQL, REGEXP era caso sensitivo, e a consulta anterior não iria retornar nenhum registro. Neste caso, para combinar letras ‘b’ maiúsculas e minúsculas, utilize esta consulta:

mysql> SELECT * FROM pet WHERE name REGEXP "^[bB]";

A partir do MySQL 3.23.4, se você realmente deseja forçar uma comparação REGEXP com caso sensitivo, utilize a palavra-chave BINARY para tornar uma das strings em uma string binárias. Esta consulta irá combinar somente com ‘b’s minúsculos no começo de um nome:

mysql> SELECT * FROM pet WHERE name REGEXP BINARY "^b";

Para encontrar nomes finalizados com 'fy', utilize ‘$’ para combinar com o final do nome:

mysql> SELECT * FROM pet WHERE name REGEXP "fy$";
+--------+--------+---------+------+------------+-------+
| name   | owner  | species | sex  | birth      | death |
+--------+--------+---------+------+------------+-------+
| Fluffy | Harold | cat     | f    | 1993-02-04 | NULL  |
| Buffy  | Harold | dog     | f    | 1989-05-13 | NULL  |
+--------+--------+---------+------+------------+-------+

Para encontrar nomes contendo um ‘w’, utilize esta consulta:

mysql> SELECT * FROM pet WHERE name REGEXP "w";
+----------+-------+---------+------+------------+------------+
| name     | owner | species | sex  | birth      | death      |
+----------+-------+---------+------+------------+------------+
| Claws    | Gwen  | cat     | m    | 1994-03-17 | NULL       |
| Bowser   | Diane | dog     | m    | 1989-08-31 | 1995-07-29 |
| Whistler | Gwen  | bird    | NULL | 1997-12-09 | NULL       |
+----------+-------+---------+------+------------+------------+

Como uma expressão regular extendida encontra padrões coincidentes se eles ocorrem em qualquer lugar no valor comparado, não é necessário utiliar, na consulta anterior, nenhum metacaracter em nenhum dos lados do padrão para fazê-lo coincidir com todo o valor, como seria feito se fosse utilizado o padrão SQL.

Para encontrar nomes contendo exatamente cinco caracteres, utilize ‘^’ e ‘$’ para combinar com o começo e fim do nome e cinco instâncias de ‘.’ entre eles.

mysql> SELECT * FROM pet WHERE name REGEXP "^.....$";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

Você pode também escrever a consulta anterior utilizando o operador '{n}' ``repete-n-vezes'':

mysql> SELECT * FROM pet WHERE name REGEXP "^.{5}$";
+-------+--------+---------+------+------------+-------+
| name  | owner  | species | sex  | birth      | death |
+-------+--------+---------+------+------------+-------+
| Claws | Gwen   | cat     | m    | 1994-03-17 | NULL  |
| Buffy | Harold | dog     | f    | 1989-05-13 | NULL  |
+-------+--------+---------+------+------------+-------+

3.3.4.8. Contando Registros

Bancos de dados normalmente são usados para responder a perguntas, ``Qual a frequência que certo tipo de dados ocorre em uma tabela?'' Por exemplo, você deve querer saber quantos animais tem, ou quantos animais cada dono tem, ou você pode querer fazer vários outros tipos de operações de censo com seus animais.

Contando o número total de animais que você tem é a mesma questão como em ``Quantos registros existem na tabela pet?'' porque existe um registro por animal. COUNT(*) conta o número de resultados não-NULL, portanto a pesquisa para contar seus animais parecerá com isto:

mysql> SELECT COUNT(*) FROM pet;
+----------+
| COUNT(*) |
+----------+
|        9 |
+----------+

Logo, você recuperará os nomes das pessoas que possuam animais. Você pode usar COUNT() se você desejar encontrar quantos animais cada dono possui:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;
+--------+----------+
| owner  | COUNT(*) |
+--------+----------+
| Benny  |        2 |
| Diane  |        2 |
| Gwen   |        3 |
| Harold |        2 |
+--------+----------+

Perceba o uso de GROUP BY para agrupar todos os registros para cada owner (dono). Sem ele, você teria uma mensagem de erro:

mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140: Mixing of GROUP columns (MIN(),MAX(),COUNT()...)
with no GROUP columns is illegal if there is no GROUP BY clause

COUNT() e GROUP BY são úteis para personalizar seus dados de diversas maneiras. Os seguintes exemplos mostram diferentes maneiras para realizar operações de censo nos animais.

Número de animais por espécie:

mysql> SELECT species, COUNT(*) FROM pet GROUP BY species;
+---------+----------+
| species | COUNT(*) |
+---------+----------+
| bird    |        2 |
| cat     |        2 |
| dog     |        3 |
| hamster |        1 |
| snake   |        1 |
+---------+----------+

Número de animais por sexo:

mysql> SELECT sex, COUNT(*) FROM pet GROUP BY sex;
+------+----------+
| sex  | COUNT(*) |
+------+----------+
| NULL |        1 |
| f    |        4 |
| m    |        4 |
+------+----------+

(Nesta saída, NULL indica que o sexo é desconhecido.)

Número de animais combinando espécie e sexo:

mysql> SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | NULL |        1 |
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

Não é necessário selecionar uma tabela inteira quando estiver usando COUNT(). Por exemplo, a consulta anterior, quando realizada apenas procurando por cachorros e gatos, se parece com isto:

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE species = "dog" OR species = "cat"
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
+---------+------+----------+

Ou se você desejar saber o número de animais por sexo somente de animais com sexo conhecido:

mysql> SELECT species, sex, COUNT(*) FROM pet
    -> WHERE sex IS NOT NULL
    -> GROUP BY species, sex;
+---------+------+----------+
| species | sex  | COUNT(*) |
+---------+------+----------+
| bird    | f    |        1 |
| cat     | f    |        1 |
| cat     | m    |        1 |
| dog     | f    |        1 |
| dog     | m    |        2 |
| hamster | f    |        1 |
| snake   | m    |        1 |
+---------+------+----------+

3.3.4.9. Utilizando Múltiplas Tabelas

A tabela pet mantém informações de quais animais você tem. Se você deseja gravar outras informações sobre eles como eventos em suas vidas, tais como visitas ao veterinário ou sobre suas crias, você necessitará de outra tabela. Como esta tabela deve se parecer ? Ela precisa:

  • Conter o nome do animal para que você saiba a qual animal pertence o evento.

  • Uma data para que você saiba quando ocorreu o evento.

  • Um campo para descrever o evento.

  • Um campo com o tipo de evento, se você desejar classificá-los por categoria.

Dadas estas considerações, a instrução CREATE TABLE para a tabela event deve se parecer com isto:

mysql> CREATE TABLE event (name VARCHAR(20), date DATE,
    -> type VARCHAR(15), remark VARCHAR(255));

Como na tabela pet, é mais fácil carregar os registros iniciais criando um arquivo texto delimitado por tabulações contendo a informação:

namedatetyperemark
Fluffy1995-05-15litter4 kittens, 3 female, 1 male
Buffy1993-06-23litter5 puppies, 2 female, 3 male
Buffy1994-06-19litter3 puppies, 3 female
Chirpy1999-03-21vetneeded beak straightened
Slim1997-08-03vetbroken rib
Bowser1991-10-12kennel 
Fang1991-10-12kennel 
Fang1998-08-28birthdayGave him a new chew toy
Claws1998-03-17birthdayGave him a new flea collar
Whistler1998-12-09birthdayFirst birthday

Carregue os registros usando:

mysql> LOAD DATA LOCAL INFILE "event.txt" INTO TABLE event;

Baseado no que você já aprendeu com as consultas realizadas na tabela pet, você deve estar apto para realizar pesquisas na tabela event; os princípios são o mesmo. Mas quando a tabela event, sozinha, é insuficiente para responder às suas questões?

Suppose you want to find out the ages at which each pet had its litters. We saw earlier how to calculate ages from two dates. The litter date of the mother is in the event table, but to calculate her age on that date you need her birth date, which is stored in the pet table. This means the query requires both tables:

Suponha que você deseje descobrir as idades de cada animal quando eles tiveram cria. Nós vemos logo que é possível calcular a idade a partir das duas datas. A idade dos filhotes está na tabela event, mas para calcular a idade da mãe, você precisará da data de nascimento dela, que está armazenado na tabela pet. Isto significa que você precisará das duas tabelas para a consulta:

mysql> SELECT pet.name,
    -> (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age,
    -> remark
    -> FROM pet, event
    -> WHERE pet.name = event.name AND type = "litter";
+--------+------+-----------------------------+
| name   | age  | remark                      |
+--------+------+-----------------------------+
| Fluffy |    2 | 4 kittens, 3 female, 1 male |
| Buffy  |    4 | 5 puppies, 2 female, 3 male |
| Buffy  |    5 | 3 puppies, 3 female         |
+--------+------+-----------------------------+

Existem várias coisas que devem ser percebidas sobre esta consulta:

  • A cláusula FROM lista as duas tabelas porque a consulta precisa extrair informação de ambas.

  • Quando combinar (unir) informações de múltiplas tabelas, você precisa especificar como registros em uma tabela podem ser coincididas com os registros na outra. Isto é simples porque ambas possuem uma coluna name. A consulta utiliza a cláusula WHERE para coincidir registros nas duas tabelas baseadas nos valores de name.

  • Como a coluna name ocorre em ambas tabelas, você deve especificar qual a tabela a que você está se referindo. Isto é feito usando o nome da tabela antes do nome da coluna separados por um ponto (.).

Você não precisa ter duas tabelas diferentes para realizar uma união. Algumas vezes é útil unir uma tabela a ela mesma, se você deseja comparar registros em uma tabela com outros registros na mesma tabela. Por exemplo, para encontrar pares entre seus animais, você pode unir a tabela pet com ela mesma para produzir pares candidatos de machos e fêmeas de acordo com as espécies:

mysql> SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
    -> FROM pet AS p1, pet AS p2
    -> WHERE p1.species = p2.species AND p1.sex = "f" AND p2.sex = "m";
+--------+------+--------+------+---------+
| name   | sex  | name   | sex  | species |
+--------+------+--------+------+---------+
| Fluffy | f    | Claws  | m    | cat     |
| Buffy  | f    | Fang   | m    | dog     |
| Buffy  | f    | Bowser | m    | dog     |
+--------+------+--------+------+---------+

Nesta consulta, nós especificamos apelidos para os nomes das tabelas para conseguir referenciar às colunas e manter com qual instância da tabela cada coluna de referência está associdada.

3.4. Obtendo Informações Sobre Bancos de Dados e Tabelas

E se você esquecer o nome de um banco de dados ou tabela, ou como é a estrutura de uma certa tabela (por exemplo, como suas colunas são chamadas)? O MySQL resolve este problema através de diversas instruções que fornecem informações sobre os bancos de dados e as tabelas que ele suporta.

Você já viu SHOW DATABASES, que lista os bancos de dados gerenciados pelo servidor. Para saber qual banco de dados está sendo usado atualmente, utilize a função DATABASE():

mysql> SELECT DATABASE();
+------------+
| DATABASE() |
+------------+
| menagerie  |
+------------+

Se você ainda não selecionou nenhum banco de dados ainda, o resultado é NULL. (ou a string vazia antes do MySQL 4.1.1).

Para saber quais tabelas o banco de dados atual contêm (por exemplo, quando você não tem certeza sobre o nome de uma tabela), utilize este comando:

mysql> SHOW TABLES;
+---------------------+
| Tables in menagerie |
+---------------------+
| event               |
| pet                 |
+---------------------+

Se você deseja saber sobre a estrutura de uma tabela, o comando DESCRIBE é útil; ele mostra informações sobre cada uma das colunas da tabela:

mysql> DESCRIBE pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+

A coluna Field (campo) indica o nome da coluna, Type é o tipo de dados para a coluna, Null indica se a coluna pode conter valores nulos (NULL), key indica se a coluna é indexada ou não e Default especifica o valor padrão da coluna.

Se você tem índices em uma tabela, SHOW INDEX FROM tbl_nome traz informações sobre eles.

3.5. Utilizando mysql em Modo Batch

Nas seções anteriores, você usou mysql interativamente para fazer consultas e ver os resultados. Você pode também executar mysql no modo batch. Para fazer isto, coloque os comando que você deseja executar em um arquivo, e diga ao mysqld para ler sua entrada do arquivo:

shell> mysql < batch-file

Se você estiver executando o mysql no Windows e tiver algum caracter especial no arquivo que provocou o problema, você pode fazer:

dos> mysql -e "source batch-file"

Se você precisa especificar parâmetros de conexão na linha de comando, o comando deve parecer com isto:

shell> mysql -h host -u user -p < batch-file
Enter password: ********

Quando você utilizar o mysql desta forma, você estará criando um arquivo script, depois executando o script.

Se você quiser que o script continue mesmo se hopuver erros, você deve usar a opção de linha de comando --force.

Por que usar um script? Existem várias razões:

  • Se você executa uma query repetidamente (digamos, todos os dias ou todas as semanas), transformá-lo em um script permite que você não o redigite toda vez que o executa.

  • Você pode gerar novas consultas a partir das já existentes copiando e editando os arquivos de script.

  • O modo batch pode também ser útil quando você estiver desenvolvendo uma consulta, particularmente para comandos de múltiplas linhas ou sequências de comandos com várias instruções. Se você cometer um erro, não será necessário redigitar tudo. Apenas edite seu arquivo script e corrija o erro, depois diga ao mysql para executá-lo novamente.

  • Se você tem uma query que produz muita saída, você pode encaminhar a saída através de um páginador.

    shell> mysql < batch-file | more
    
  • Você pode capturar a saída em um arquivo para processamento posterior:

    shell> mysql < batch-file > mysql.out
    
  • Você pode distribuir seu script para outras pessoas para que elas possam executar os comandos também.

  • Algumas situações não permitem uso interativo, por exemplo, quando você executa uma consulta através de um processo automático (cron job). Neste caso, você deve usar o modo batch.

A formato padrão de saída é diferente (mais conciso) quando você executa o mysql no modo batch do que quando você o usa interativamente. Por exemplo, a saída de SELECT DISTINCT species FROM pet se parece com isto quando você o executa interativamente:

+---------+
| species |
+---------+
| bird    |
| cat     |
| dog     |
| hamster |
| snake   |
+---------+

Mas fica assim quando você o executa no modo batch:

species
bird
cat
dog
hamster
snake

Se você desejar obter o formato de saída interativa no modo batch, utilize mysql -t. Para mostrar a saída dos comandos que são executados, utilize mysql -vvv.

Você também pode utilizar scripts no prompt de linha de comando mysql usando o comando source:

mysql> source filename;

3.6. Exemplos de Consultas Comuns

Aqui estão os exemplos de como resolver problemas comuns com o MySQL.

Alguns dos exemplos usam a tabela shop para armazenar o preço de cada ítem (article) para certas revendas (dealers). Supondo que cada revenda tenha um preço fixo por artigo, então (article, dealer) é uma chave primária para os registros.

Inicie a ferramenta de linha de comando mysql e selecione um banco de dados:

shell> mysql o-nome-do-seu-banco-de-dados

(Na maioria das instalações do MySQL, você pode usar o banco de dados test).

Você pode criar e popular a tabela exemplo assim:

mysql> CREATE TABLE shop (
    -> article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    -> dealer  CHAR(20)                 DEFAULT ''     NOT NULL,
    -> price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL,
    -> PRIMARY KEY(article, dealer));
mysql> INSERT INTO shop VALUES
    -> (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69),
    -> (3,'D',1.25),(4,'D',19.95);

Depois de executar as instruções a tabela deve ter o seguinte conteúdo:

mysql> SELECT * FROM shop;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | A      |  3.45 |
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | B      |  1.45 |
|    0003 | C      |  1.69 |
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.1. O Valor Máximo para uma Coluna

``Qual é o maior número dos ítens?''

SELECT MAX(article) AS article FROM shop;

+---------+
| article |
+---------+
|       4 |
+---------+

3.6.2. O Registro que Armazena o Valor Máximo para uma Coluna Determinada

``Encontre o número, fornecedor e preço do ítem mais caro.''

No SQL ANSI isto é feito fácilmente com uma sub-consulta:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

No MySQL (que ainda não suporta sub-selects), faça isto em dois passos:

  1. Obtenha o valor do preço máximo da tabela com uma instrução SELECT.

    mysql> SELECT MAX(price) FROM shop;
    +------------+
    | MAX(price) |
    +------------+
    |      19.95 |
    +------------+
    

  2. Usando o valor 19.95 mostrado pela consulta anterior como o preço máximo do artigo, grave uma consulta para localizar e mostrar o registro correspondente:

    mysql> SELECT article, dealer, price
        -> FROM   shop
        -> WHERE  price=19.95;
    +---------+--------+-------+
    | article | dealer | price |
    +---------+--------+-------+
    |    0004 | D      | 19.95 |
    +---------+--------+-------+
    
    

Outra solução é ordenar todos os registros por preço de forma descendente e obtenha somente o primeiro registro utilizando a cláusula específica do MySQL LIMIT:

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1;

NOTA: Se existir diversos ítens mais caros, cada um com um preço de 19.95, a solução LIMIT mostra somente um deles !

3.6.3. Máximo da Coluna por Grupo

``Qual é o maior preço por ítem?''

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

3.6.4. As Linhas Armazenando o Group-wise Máximo de um Certo Campo

``Para cada ítem, encontre o(s) fornecedor(s) com o maior preço.''

No SQL-99 (e MySQL 4.1 ou superior), o problema pode ser solucionado com uma subconsulta como esta:

SELECT article, dealer, price
FROM   shop s1
WHERE  price=(SELECT MAX(s2.price)
              FROM shop s2
              WHERE s1.article = s2.article);

Em versões anteriores a do MySQL 4.1 é melhor fazê-lo em diversos passos:

  1. Obtenha a lista de pares (article,maxprice).

  2. Para cada ítem, obtenha os registros correspondentes que tenham o maior preço.

Isto pode ser feito facilmente com uma tabela temporária e um join:

CREATE TEMPORARY TABLE tmp (
        article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
        price   DOUBLE(16,2)             DEFAULT '0.00' NOT NULL);

LOCK TABLES shop READ;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

Se você não usar uma tabela TEMPORÁRIA, você deve bloquear também a tabela tmp.

``Posso fazer isto com uma única query?''

Sim, mas somente com um truque ineficiente chamado ``truque MAX-CONCAT'':

SELECT article,
       SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
  0.00+LEFT(      MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM   shop
GROUP BY article;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0001 | B      |  3.99 |
|    0002 | A      | 10.99 |
|    0003 | C      |  1.69 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

O último exemplo pode, é claro, ser feito de uma maneira mais eficiente fazendo a separação da coluna concatenada no cliente.

3.6.5. Utilizando Variáveis de Usuário

Você pode usar variáveis de usuários no MySQL para lembrar de resultados sem a necessidade de armazená-las em variáveis no cliente. Veja mais informações sobre isto na Seção 6.1.4, “Variáveis de Usuário”.

Por exemplo, para encontrar os ítens com os preços mais altos e mais baixos você pode fazer isto:

select @min_price:=min(price),@max_price:=max(price) from shop;
select * from shop where price=@min_price or price=@max_price;

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0003 | D      |  1.25 |
|    0004 | D      | 19.95 |
+---------+--------+-------+

3.6.6. Utilizando Chaves Estrangeiras

No MySQL 3.23.44 e acima, tabelas InnoDB suportam verificação de restrições de chaves estrangerias. Veja mais informações sobre isto na Seção 7.5, “Tabelas InnoDB. Veja também Seção 1.8.4.5, “Chaves Estrangeiras”.

Você não precisa de chaves estrangeiras para unir 2 tabelas. Para outros tipos de tabela diferentes de InnoDB, As únicas coisas que o MySQL atualmente não faz são 1) CHECK, para ter certeza que as chaves que você usa realmente existem na tabela ou tabelas referenciadas e 2) apagar automaticamente registros da tabela com uma definição de chave estrangeira. Usando suas chaves para unir a tabela funcionará bem:

CREATE TABLE person (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name CHAR(60) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE shirt (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
    colour  ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
    owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
    PRIMARY KEY (id)
);


INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());


INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());


SELECT * FROM person;
+----+---------------------+
| id | name                |
+----+---------------------+
|  1 | Antonio Paz         |
|  2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style   | colour | owner |
+----+---------+--------+-------+
|  1 | polo    | blue   |     1 |
|  2 | dress   | white  |     1 |
|  3 | t-shirt | blue   |     1 |
|  4 | dress   | orange |     2 |
|  5 | polo    | red    |     2 |
|  6 | dress   | blue   |     2 |
|  7 | t-shirt | white  |     2 |
+----+---------+--------+-------+


SELECT s.* FROM person p, shirt s
 WHERE p.name LIKE 'Lilliana%'
   AND s.owner = p.id
   AND s.colour  <> 'white';

+----+-------+--------+-------+
| id | style | colour | owner |
+----+-------+--------+-------+
|  4 | dress | orange |     2 |
|  5 | polo  | red    |     2 |
|  6 | dress | blue   |     2 |
+----+-------+--------+-------+

3.6.7. Pesquisando em Duas Chaves

O MySQL ainda não otimiza quando você pesquisa em duas chaves diferentes combinadas com OR (Pesquisa em uma chave com diferentes partes OR é muito bem otimizadas).

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

A razão é que nós ainda não tivemos tempos para fazer este tratamento de uma maneira eficiente no caso geral. (A manipulação do AND é, em comparação, completamente geral e funciona muito bem).

No MySQL 4.0 e acimo, você pode solucionar este problema eficientemente usando um UNION que combina a saída de duas instruções SELECT separadas. Veja mais informações sobre isto na Seção 6.4.1.2, “Sintaxe UNION. Cada SELECT busca apenas uma chave e pode ser otimizada.

SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'
UNION
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';

Em versões do MySQL anteirores a 4.0, você pode conseguir o mesmo efeito usando uma tabela TEMPORARY e instruções SELECT separadas. Este tipo de otimização também é muito boa se você estiver utilizando consultas muito complicadas no qual o servidor SQL faz as otimizações na ordem errada.

CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1';
INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1';
SELECT * from tmp;
DROP TABLE tmp;

A maneira descrita acima para resolver esta consulta é uma união (UNION) de duas consultas.

3.6.8. Calculando Visitas Diárias

O seguinte exemplo mostra como você pode usar as funções binárias de agrupamento para calcular o número de dias por mês que um usuário tem visitado uma página web.

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);

A tabela exemplo contém valores ano-mês-dia representando visitas feitas pelos usuários a página. Para determinar quantos quantos dias diferentes em cada mês estas visitas ocorriam, use esta consulta:

SELECT year,month,BIT_COUNT(BIT_OR(1<<day)) AS days FROM t1 GROUP BY year,month;

que retornará:

+------+-------+------+
| year | month | days |
+------+-------+------+
| 2000 |    01 |    3 |
| 2000 |    02 |    2 |
+------+-------+------+

O exemplo acima calcula quantos dias diferentes foram usados para uma combinação fornecida de mês/ano, com remoção automática de entradas duplicadas.

3.6.9. Usando AUTO_INCREMENT

O atributo AUTO_INCREMENT pode ser usado para gerar uma identificação única para um novo registro:

CREATE TABLE animals (
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (id)
             );
INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"),
                                  ("lax"),("whale"),("ostrich");
SELECT * FROM animals;

Que retorna:

+----+---------+
| id | name    |
+----+---------+
|  1 | dog     |
|  2 | cat     |
|  3 | penguin |
|  4 | lax     |
|  5 | whale   |
|  6 | ostrich |
+----+---------+

Você pode recuperar o valor AUTO_INCREMENT mais recente com a função SQL LAST_INSERT_ID() ou a função da API C mysql_insert_id(). Nota: para uma inserção de várias linhas LAST_INSERT_ID()/mysql_insert_id() retornará atualmente a AUTO_INCREMENT chave da primeira linha inserida. Isto permite que inserções multi-linhas sejam reproduzidas corretamente em outros servidores em uma configração de replicação.

Para tabelas MyISAM e BDB você pode especificar AUTO_INCREMENT em uma coluna secundária em um índice multi-coluna. Neste caso, o valor gerado para a coluna AUTO_INCREMENT é calculado como MAX(auto_increment_column)+1) WHERE prefix=given-prefix. Isto é útil quando você quer colocar dados em grupos ordenados.

CREATE TABLE animals (
             grp ENUM('fish','mammal','bird') NOT NULL,
             id MEDIUMINT NOT NULL AUTO_INCREMENT,
             name CHAR(30) NOT NULL,
             PRIMARY KEY (grp,id)
             );
INSERT INTO animals (grp,name) VALUES("mammal","dog"),("mammal","cat"),
                  ("bird","penguin"),("fish","lax"),("mammal","whale"),
                        ("bird","ostrich");

SELECT * FROM animals ORDER BY grp,id;

Que retorna:

+--------+----+---------+
| grp    | id | name    |
+--------+----+---------+
| fish   |  1 | lax     |
| mammal |  1 | dog     |
| mammal |  2 | cat     |
| mammal |  3 | whale   |
| bird   |  1 | penguin |
| bird   |  2 | ostrich |
+--------+----+---------+

Note que neste caso (quando o valor AUTO_INCREMENT é parte de um índice multi-coluna), o valor de AUTO_INCREMENT será reutilizado se você deletar a linha com o maior valor AUTO_INCREMENT em qualquer grupo. Isto caontece mesmo para tabelas MyISAM, para as quais os valores AUTO_INCREMENT normalmente não são reusados.)

3.7. Consultas de Projetos Gêmeos

Em Analytikerna e Lentus, nós estamos fazendo os sistemas e trabalho de campo para um grande projeto de pesquisa. Este projeto é uma colaboração entre o Institudo de Medicina Ambiental em Karolinksa Institutet Stockholm e a Seção de Pesquisa Clínica em Envelhecimento e Psicologia na University of Southern California.

O projeto envolve uma parte de seleção onde todos os gêmeos na Suécia mais velhos que 65 anos são entrevistados por telefone. Gêmeos que preenchem certos critérios passam para o próximo estágio. Neste estágio posterior, gêmeos que desejam participar são visitados por uma equipe de doutores/enfermeiros. Alguns dos consultas incluem exames físicos e neuropsicológico, testes de laboratório, imagem neural, determinação do estado psicológico e coletas de histórico familiar. Adicionalmente, dados são coletados em fatores de riscos médicos e ambientais.

Mais informações sobre o estudos dos gêmeos pode ser encontrados em: http://www.mep.ki.se/twinreg/index_en.html

A parte posterior do projeto é administrada com uma interface Web escrita utilizando a linguagem Perl e o MySQL.

Cada noite todos dados das entrevistas são movidos para um banco de dados MySQL.

3.7.1. Encontrando Todos Gêmeos Não-distribuídos

A seguinte consulta é usada para determinar quem vai na segunda parte do projeto:

SELECT
        CONCAT(p1.id, p1.tvab) + 0 AS tvid,
        CONCAT(p1.christian_name, " ", p1.surname) AS Name,
        p1.postal_code AS Code,
        p1.city AS City,
        pg.abrev AS Area,
        IF(td.participation = "Aborted", "A", " ") AS A,
        p1.dead AS dead1,
        l.event AS event1,
        td.suspect AS tsuspect1,
        id.suspect AS isuspect1,
        td.severe AS tsevere1,
        id.severe AS isevere1,
        p2.dead AS dead2,
        l2.event AS event2,
        h2.nurse AS nurse2,
        h2.doctor AS doctor2,
        td2.suspect AS tsuspect2,
        id2.suspect AS isuspect2,
        td2.severe AS tsevere2,
        id2.severe AS isevere2,
        l.finish_date
FROM
        twin_project AS tp
        /* For Twin 1 */
        LEFT JOIN twin_data AS td ON tp.id = td.id
                  AND tp.tvab = td.tvab
        LEFT JOIN informant_data AS id ON tp.id = id.id
                  AND tp.tvab = id.tvab
        LEFT JOIN harmony AS h ON tp.id = h.id
                  AND tp.tvab = h.tvab
        LEFT JOIN lentus AS l ON tp.id = l.id
                  AND tp.tvab = l.tvab
        /* For Twin 2 */
        LEFT JOIN twin_data AS td2 ON p2.id = td2.id
                  AND p2.tvab = td2.tvab
        LEFT JOIN informant_data AS id2 ON p2.id = id2.id
                  AND p2.tvab = id2.tvab
        LEFT JOIN harmony AS h2 ON p2.id = h2.id
                  AND p2.tvab = h2.tvab
        LEFT JOIN lentus AS l2 ON p2.id = l2.id
                  AND p2.tvab = l2.tvab,
        person_data AS p1,
        person_data AS p2,
        postal_groups AS pg
WHERE
        /* p1 gets main twin and p2 gets his/her twin. */
        /* ptvab is a field inverted from tvab */
        p1.id = tp.id AND p1.tvab = tp.tvab AND
        p2.id = p1.id AND p2.ptvab = p1.tvab AND
        /* Just the sceening survey */
        tp.survey_no = 5 AND
        /* Skip if partner died before 65 but allow emigration (dead=9) */
        (p2.dead = 0 OR p2.dead = 9 OR
         (p2.dead = 1 AND
          (p2.death_date = 0 OR
           (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) / 365)
            >= 65))))
        AND
        (
        /* Twin is suspect */
        (td.future_contact = 'Yes' AND td.suspect = 2) OR
        /* Twin is suspect - Informant is Blessed */
        (td.future_contact = 'Yes' AND td.suspect = 1
                                   AND id.suspect = 1) OR
        /* No twin - Informant is Blessed */
        (ISNULL(td.suspect) AND id.suspect = 1
                            AND id.future_contact = 'Yes') OR
        /* Twin broken off - Informant is Blessed */
        (td.participation = 'Aborted'
         AND id.suspect = 1 AND id.future_contact = 'Yes') OR
        /* Twin broken off - No inform - Have partner */
        (td.participation = 'Aborted' AND ISNULL(id.suspect)
                                      AND p2.dead = 0))
        AND
        l.event = 'Finished'
        /* Get at area code */
        AND SUBSTRING(p1.postal_code, 1, 2) = pg.code
        /* Not already distributed */
        AND (h.nurse IS NULL OR h.nurse=00 OR h.doctor=00)
        /* Has not refused or been aborted */
        AND NOT (h.status = 'Refused' OR h.status = 'Aborted'
        OR h.status = 'Died' OR h.status = 'Other')
ORDER BY
        tvid;

Algumas explicações:

  • CONCAT(p1.id, p1.tvab) + 0 AS tvid

    N queremos ordenar o id e o tvab concatenados na ordem numérica. Adicionando 0 ao resultado faz o MySQL tratar o resultado como um número.

  • coluna id

    Esta identifica um par de gêmeos. Ela é uma chave em todas as tabelas.

  • column tvab

    Esta identifica um gêmeo em um par. Ela pode ter um valor de 1 ou 2.

  • column ptvab

    Esta é o inverso de tvab. Quando tvab é 1 este campo é 2 e vice versa. Ela existe para poupar digitação e tornar mais fácil para o MySQL otimizar a query.

Esta consulta demonstra, entre outras coisas, como fazer buscas em uma tabela a partir da mesma tabela com uma uniao (p1 e p2). No exemplo, isto é usado para conferir se um par de um gêmeo morreu antes de 65 anos. Se for verdade, a linha não é retornada.

Tudo acima existe em todas as tabelas com informações relacionada aos gêmeos. Nós temos uma chave em ambos id,tvab (todas as tabelas) e id,ptvab (person_data) para tornar as consultas mais rápidas.

Na nossa máquina de produção (Um UltraSPARC 200MHz), esta consulta retorna entre 150-200 linhas e gasta menos que um segundo.

O número atual de registros nas tabelas usadas acima:

TabelaRegistros
person_data71074
lentus5291
twin_project5286
twin_data2012
informant_data663
harmony381
postal_groups100

3.7.2. Mostrando uma Tabela sobre a Situação dos Pares Gêmeos

Cada entrevista termina com um código da situação chamado event. A consulta mostrada abaixa é usada para mostrar uma tabela sobre todos pares gêmeos combinados por evento. Ela indica em quantos pares ambos gêmeos terminaram, em quantos pares um gêmeo terminou e o outro foi recusado e assim por diante.

SELECT
        t1.event,
        t2.event,
        COUNT(*)
FROM
        lentus AS t1,
        lentus AS t2,
        twin_project AS tp
WHERE
        /* We are looking at one pair at a time */
        t1.id = tp.id
        AND t1.tvab=tp.tvab
        AND t1.id = t2.id
        /* Just the sceening survey */
        AND tp.survey_no = 5
        /* This makes each pair only appear once */
        AND t1.tvab='1' AND t2.tvab='2'
GROUP BY
        t1.event, t2.event;

3.8. Utilizando MySQL com Apache

Existem programas que lhe permite autenticar seus usuários a partir de um banco de dados MySQL e também permite gravar seus arquivos de log em uma tabela MySQL.

Você pode alterar o formato de log do Apache para ser facilmente lido pelo MySQL colocando o seguinte no arquivo de configuração do Apache:

LogFormat \
        "\"%h\",%{%Y%m%d%H%M%S}t,%>s,\"%b\",\"%{Content-Type}o\",  \
        \"%U\",\"%{Referer}i\",\"%{User-Agent}i\""

Para carregar uma arquivo de log naquele formato dentro do MySQL, você pode usar uma instrução deste tipo:

LOAD DATA INFILE '/local/access_log' INTO TABLE nome_tabela
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\'

A tabela chamada deve ser criada para ter colunas que correpondem a aquelas que a linha LogFormat gravam no arquivo de log.


This is a translation of the MySQL Reference Manual that can be found at dev.mysql.com. The original Reference Manual is in English, and this translation is not necessarily as up to date as the English version.