Tutorial de Introdução Do MariaDB - Databases - Software - Computers

Tutorial de Introdução Do MariaDB

Índice

Conectando e Desconectando do Servidor
Fazendo Consultas
Criação e Utilização de um Banco de Dados
Criando e Selecionando um Banco de Dados
Criando uma Tabela
Carregando dados em uma tabela
Recuperando Informações de uma Tabela
Obtendo Informações Sobre Bancos de Dados e Tabelas
Utilizando MariaDB em Modo Batch
Exemplos de Consultas Comuns
O Valor Máximo para uma Coluna
O Registro que Armazena o Valor Máximo para uma Coluna Determinada
Máximo da Coluna por Grupo
As Linhas Armazenando o Group-wise Máximo de um Certo Campo
Utilizando Variáveis de Usuário
Utilizando Chaves Estrangeiras
Pesquisando em Duas Chaves
Calculando Visitas Diárias
Usando AUTO_INCREMENT
Consultas de Projetos Gêmeos
Encontrando Todos Gêmeos Não-distribuídos
Mostrando uma Tabela sobre a Situação dos Pares Gêmeos
Utilizando MariaDB com Apache

Este fornece um tutorial de introdução ao MariaDB demonstrando como usar o programa cliente MariaDB para criar e usar um banco de dados simples. MariaDB (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. MariaDB pode também ser executado em modo batch: você coloca suas consultas em um arquivo, depois diz ao MariaDB para executar o conteúdo do arquivo. Cobrimos aqui ambas as formas de utilizar o MariaDB.

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

shell> mysql --help

Este presume que o MariaDB está instalado na sua máquina e que um servidor MariaDB 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 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 é 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.

Conectando e Desconectando do Servidor

Para conectar ao servidor, normalmente você precisará fornecer um nome de usuário quando o MariaDB 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 MariaDB 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 MariaDB monitor. Commands end with ; or \g.
Your MariaDB 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 MariaDB está pronto para que você digite os comandos.

Algumas instalações MariaDB 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 MariaDB sem qualquer opção:

shell> MariaDB

Depois de você conectar corretamente, 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>.

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 MariaDB.

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 MariaDB:

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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB se encontra:

Prompt Significado
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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB 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 MariaDB o intrerpreta como parte da string que está coletando! Digite o caracter de aspas para fechar (então o MariaDB 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 MariaDB 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 MariaDB --- 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.

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

Criando e Selecionando um Banco de Dados
Criando uma Tabela
Carregando dados em uma tabela
Recuperando Informações de uma Tabela

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:

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 MariaDB. 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 MariaDB e test provavelmente estarão entre eles. O banco de dados MariaDB é 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. Leia '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 MariaDB 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 MariaDB atribuido a você e your_client_host é a máquina da qual você se conecta ao servidor.

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 MariaDB. 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 MariaDB. 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.

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 MariaDB 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ê 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 MariaDB 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.

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 MariaDB espera datas no formato AAAA-MM-DD; isto pode ser diferente do que você está acostumado.)

name owner species sex birth death
Fluffy Harold cat f 1993-02-04
Claws Gwen cat m 1994-03-17
Buffy Harold dog f 1989-05-13
Fang Benny dog m 1990-08-27
Bowser Diane dog m 1979-08-31 1995-07-29
Chirpy Gwen bird f 1998-09-11
Whistler Gwen bird 1997-12-09
Slim Benny snake m 1996-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):

name owner species sex birth death
Whistler Gwen bird \N 1997-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 MariaDB não tenha a capacidade do arquivo local habilitada por padrão. Veja '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.

Recuperando Informações de uma Tabela

Selecionando Todos os Dados
Selecionando Registros Específicos
Selecionando Colunas Específicas
Ordenando Registros
Cálculo de Datas
Trabalhando com Valores Nulos (NULL)
Combinação de padrões
Contando Registros
Utilizando Múltiplas Tabelas

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.

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:

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 |
+-------+--------+---------+------+------------+-------+

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 |
+--------+---------+------------+

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.

Cálculo de Datas

O MariaDB 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. Leia '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 MariaDB 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).

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 MariaDB 4.0.2 a 4.0.10 sempre ordenam, incorretamente, valores NULL em primeiro independente da ordem escolhida.

Combinação de padrões

O MariaDB 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 MariaDB 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:

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 MariaDB, 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 MariaDB 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 |
+-------+--------+---------+------+------------+-------+

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 |
+---------+------+----------+

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:

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:

name date type remark
Fluffy 1995-05-15 litter 4 kittens, 3 female, 1 male
Buffy 1993-06-23 litter 5 puppies, 2 female, 3 male
Buffy 1994-06-19 litter 3 puppies, 3 female
Chirpy 1999-03-21 vet needed beak straightened
Slim 1997-08-03 vet broken rib
Bowser 1991-10-12 kennel
Fang 1991-10-12 kennel
Fang 1998-08-28 birthday Gave him a new chew toy
Claws 1998-03-17 birthday Gave him a new flea collar
Whistler 1998-12-09 birthday First 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:

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.

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 MariaDB 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 MariaDB).

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.

Utilizando MariaDB em Modo Batch

Nas seções anteriores, você usou MariaDB interativamente para fazer consultas e ver os resultados. Você pode também executar MariaDB 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 MariaDB 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 MariaDB 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:

A formato padrão de saída é diferente (mais conciso) quando você executa o MariaDB 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 MariaDB usando o comando source:

mysql> source filename;

Exemplos de Consultas Comuns

O Valor Máximo para uma Coluna
O Registro que Armazena o Valor Máximo para uma Coluna Determinada
Máximo da Coluna por Grupo
As Linhas Armazenando o Group-wise Máximo de um Certo Campo
Utilizando Variáveis de Usuário
Utilizando Chaves Estrangeiras
Pesquisando em Duas Chaves
Calculando Visitas Diárias
Usando AUTO_INCREMENT

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

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 MariaDB e selecione um banco de dados:

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

(Na maioria das instalações do MariaDB, 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 |
+---------+--------+-------+

O Valor Máximo para uma Coluna

Qual é o maior número dos ítens?

SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
| 4 |
+---------+

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 MariaDB (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 MariaDB 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 !

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 |
+---------+-------+

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 MariaDB 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 MariaDB é 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.

Utilizando Variáveis de Usuário

Você pode usar variáveis de usuários no MariaDB para lembrar de resultados sem a necessidade de armazená-las em variáveis no cliente. Leia '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 |
+---------+--------+-------+

Utilizando Chaves Estrangeiras

No MariaDB 3.23.44 e acima, tabelas InnoDB suportam verificação de restrições de chaves estrangerias. Leia 'Tabelas InnoDB'. Veja também '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 MariaDB 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 |
+----+-------+--------+-------+

Pesquisando em Duas Chaves

O MariaDB 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 MariaDB e acimo, você pode solucionar este problema eficientemente usando um UNION que combina a saída de duas instruções SELECT separadas. Leia '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 MariaDB 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.

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.

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.)

Consultas de Projetos Gêmeos

Encontrando Todos Gêmeos Não-distribuídos
Mostrando uma Tabela sobre a Situação dos Pares 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 MariaDB.

Cada noite todos dados das entrevistas são movidos para um Banco de Dados MariaDB.

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:

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:

Tabela Registros
person_data 71074
lentus 5291
twin_project 5286
twin_data 2012
informant_data 663
harmony 381
postal_groups 100

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;

Utilizando MariaDB com Apache

Existem programas que lhe permite autenticar seus usuários a partir de um banco de dados MariaDB 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 MariaDB 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 MariaDB, 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.

Anterior Próximo
Instalação do MariaDB Início Administração do Bancos de Dados MySQL