XOOPS Brasil

 

3.6. Exemplos de Consultas Comuns

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

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

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

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

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

Você pode criar e popular a tabela exemplo assim:

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

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

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

3.6.1. O Valor Máximo para uma Coluna

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

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

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

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

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

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

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

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

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

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

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

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

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

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

3.6.3. Máximo da Coluna por Grupo

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

SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
+---------+-------+
| article | price |
+---------+-------+
| 0001 | 3.99 |
| 0002 | 10.99 |
| 0003 | 1.69 |
| 0004 | 19.95 |
+---------+-------+

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

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

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

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

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

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

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

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

CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL);
LOCK TABLES shop READ;
INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;
SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;
UNLOCK TABLES;
DROP TABLE tmp;

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

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

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

SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
+---------+--------+-------+

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

3.6.5. Utilizando Variáveis de Utilizador

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

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

select @min_price:=min(price),@max_price:=max(price) from shop;
select * from shop where price=@min_price or price=@max_price;
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0003 | D | 1.25 |
| 0004 | D | 19.95 |
+---------+--------+-------+

3.6.6. Utilizando Chaves Estrangeiras

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

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

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
colour ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
PRIMARY KEY (id)
);
INSERT INTO person VALUES (NULL, 'Antonio Paz');
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');
INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());
SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+
SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | colour | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+
SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.colour <> 'white';
+----+-------+--------+-------+
| id | style | colour | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+

3.6.7. Pesquisando em Duas Chaves

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

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

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

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

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

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

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

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

3.6.8. Calculando Visitas Diárias

O seguinte exemplo mostra como você pode usar as funções binárias de agrupamento para calcular o número de dias por mês que um utilizador 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 utilizadores a página. Para determinar quantos quantos dias diferentes em cada mês estas visitas ocorriam, use esta consulta:

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

que retornará:

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

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

3.6.9. Usando AUTO_INCREMENT

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

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

Que retorna:

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

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

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

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

Que retorna:

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

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