3. Tutorial de Introdução Do MySQL
3.6. Exemplos de Consultas Comuns
Aqui estão os exemplos de como resolver problemas comuns com o MySQL.
Alguns dos exemplos usam a tabelashoppara 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 comandomysqle 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 dadostest). 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 Determina-
da
``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çãoSELECT.
+---+ | 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áu- sula específica do MySQLLIMIT:
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çãoLIMITmostra 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
UNLOCK TABLES; DROP TABLE tmp;
Se você não usar uma tabelaTEMPORÁRIA, você deve bloquear também a tabelatmp. ``Posso fazer isto com uma única query?''
Sim, mas somente com um truque ineficiente chamado ``truque MAX-CONCAT'':
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer, 0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price FROM shop
GROUP BY article;
+---+---+---+ | article | dealer | price | +---+---+---+ | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +---+---+---+
O último exemplo pode, é claro, ser feito de uma maneira mais eficiente fazendo a separação da coluna concatenada no cliente.
3.6.5. Utilizando Variáveis de Usuário
Você pode usar variáveis de usuários no MySQL para lembrar de resultados sem a necessidade de armazená-las em variáveis no cliente. SeeSecção 6.1.4, “Variáveis de Usuário”.
Por exemplo, para encontrar os ítens com os preços mais altos e mais baixos você pode fazer isto:
select @min_price:=min(price),@max_price:=max(price) from shop; select * from shop where price=@min_price or price=@max_price; +---+---+---+
| article | dealer | price | +---+---+---+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---+---+---+
3.6.6. Utilizando Chaves Estrangeiras
No MySQL 3.23.44 e acima, tabelasInnoDBsuportam verificação de restrições de chaves estrangerias. SeeSecção 7.5, “Tabelas
InnoDB”. Veja tambémSecção 1.8.4.5, “Chaves Estrangeiras”.
Você não precisa de chaves estrangeiras para unir 2 tabelas. Para outros tipos de tabela diferentes deInnoDB, 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 re- ferenciadas 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, '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 comOR(Pesquisa em uma chave com diferentes partesORé 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 umUNIONque combina a saída de duas ins- truçõesSELECTseparadas. SeeSecção 6.4.1.2, “SintaxeUNION”. CadaSELECTbusca 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 tabelaTEMPORARYe instruçõesSE- LECTseparadas. Este tipo de otimização também é muito boa se você estiver utilizando consultas muito complicadas no qual o ser- vidor SQL faz as otimizações na ordem errada.
CREATE TEMPORARY TABLE tmp
SELECT field1_index, field2_index FROM test_table WHERE field1_index = '1'; INSERT INTO tmp
SELECT field1_index, field2_index FROM test_table WHERE field2_index = '1'; SELECT * from tmp;
DROP TABLE tmp;
A maneira descrita acima para resolver esta consulta é uma união (UNION) de duas consultas.
3.6.8. Calculando Visitas Diárias
O seguinte exemplo mostra como você pode usar as funções binárias de agrupamento para calcular o número de dias por mês que um usuário tem visitado uma página web.
CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),(2000,2,23),(2000,2,23);
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 atributoAUTO_INCREMENTpode 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 valorAUTO_INCREMENTmais recente com a função SQLLAST_INSERT_ID()ou a função da API C
mysql_insert_id(). Nota: para uma inserção de várias linhasLAST_INSERT_ID()/mysql_insert_id()retornará atualmente aAUTO_INCREMENTchave 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 tabelasMyISAMeBDBvocê pode especificarAUTO_INCREMENTem uma coluna secundária em um índice multi-coluna. Neste caso, o valor gerado para a colunaAUTO_INCREMENTé calculado comoMAX(auto_increment_column)+1) WHE- RE 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 valorAUTO_INCREMENTé parte de um índice multi-coluna), o valor deAUTO_INCREMENTserá reutilizado se você deletar a linha com o maior valorAUTO_INCREMENTem qualquer grupo. Isto caontece mesmo para tabelas
MyISAM, para as quais os valoresAUTO_INCREMENTnormalmente não são reusados.)