XOOPS Brasil

 

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 utilizadores. O banco de dados test é geralamente fornecido como um espaço para que os utilizadores 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. Veja mais informações sobre isto na 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_utilizador_mysql é o nome do utilizador 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 utilizador 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.