MySQL02

Neste módulo vamos fazer uma primeira abordagem à instrução SELECT. Vamos ver:

Query simples
Sinónimos para as colunas
Cláusula WHERE
Cláusula ORDER BY
Cláusula GROUP BY
Opção ROLLUP
Cláusula HAVING
Cláusula DISTINCT
Limitar o número de linhas
Funções e operadores

Query simples

O query abaixo lista as linhas e todas as colunas da tabela CUSTOMER:

select * from customer;

O query abaixo lista as colunas SURNAME e FIRSTNAME e todas as linhas de CUSTOMER:

select surname, firstname from customer;

A escolha das colunas faz uma restrição vertical (ou projecção):

A cláusula select pode ser usada para invocar funções ou para calcular expressões aritméticas:

select now();
select curtime();
select pi()*(4*4);

As funções serão vistas mais à frente.

Topo

Sinónimos para as colunas

A instrução SELECT permite que sejam definidos sinónimos para as colunas:

select surname as Apelido, firstname as Nome
from customer;

No resultado do select não aparece o nome verdadeiro da coluna mas sim o sinónimo definido pelo utilizador.

Topo

Cláusula WHERE

A cláusula WHERE permite fazer uma restrição horizontal das linhas da tabela:

O query abaixo permite ver apenas as linhas de REGION que têm region_id igual ou inferior a 3:

select * from region where region_id <= 3;

É possível combinar a restrição vertical com a restrição horizontal:

select region_name from region where region_id <= 3;

O operador AND pode ser usado para efectuar uma interssecção entre conjuntos:

select * from customer 
where surname='Marzalla' and firstname='Sandra';

O operador OR pode ser usado para efectuar uma reunião entre conjuntos:

select * from customer 
where (surname='Marzalla' and firstname='Sandra') 
      or
      birth_date='1966-08-14';

O operador LIKE pode ser usado para pesquisar por substring. O símbolo % representa qualquer sequência de caracteres (0,1 ou mais) e pode ser colocado à esquerda ou à direita. O símbolo '_' representa um caracter qualquer, mas é obrigatório. Este símbolo também pode ser colocado à esquerda ou à direita.

select * from customer where firstname like 'Sa%';
select * from customer where firstname like '%an%';
select * from customer where firstname like 'Sa____';
select * from customer where firstname like '_a____';

Topo

Cláusula ORDER BY

A cláusula ORDER BY faz a ordenação das linhas resultantes do query. Isto significa que primeiro a base de dados extrai as linhas (obtem o conjunto resultado), depois ordena o conjunto e finalmente envia para o cliente. Todas as operações de ordenação acarretam processamento e I/O adicional.

O query abaixo lista todas as pessoas que vivem na cidade de 'Portsea' e cujo primeiro nome é 'James', ordenando o resultado por ordem crescente da coluna apelido:

select surname, firstname, city from customer
where city = 'Portsea' and firstname = 'James'
order by surname;

A ordenação pode ser feita por mais que uma coluna. O query abaixo lista todas as pessoas que vivem nas cidades de 'Coonawarra' ou 'Longwood', ordenando o resultado pela coluna FIRSTNAME. Caso haja igualdade de valores nessa coluna o desempate é feito pela coluna SURNAME. Se ainda assim há empate recorre-se à coluna INITIAL.

select firstname, surname,initial from customer
where city = 'Coonawarra' or city = 'Longwood'
order by firstname, surname, initial;

Além da ordenação crescente podemos fazer ordenação decrescente, usando DESC (descending). Na realidade os dois tipos de ordenação podem coexistir no mesmo query, como no próximo exemplo, onde a ordenação por FIRSTNAME é crescente e quando há empate passamos para a coluna SURNAME onde a ordenação é DESC.

select firstname, surname,initial from customer
where city = 'Coonawarra' or city = 'Longwood'
order by firstname, surname DESC, initial;

Topo

Cláusula GROUP BY

A cláusula GROUP BY é usada para agrupar (ou agregar) as linhas da tabela segundo um critério escolhido pelo utilizador. O processamento desta cláusula segue os seguintes passos:

A base de dados começa por seleccionar todas as linhas que satisfazem o critério de pesquisa indicado pelo utilizador (cláusula WHERE);
Em seguida faz a ordenação dessas linhas segundo o critério indicado no GROUP BY;
Os grupos são formados pelo agrupamento das linhas;
A cada grupo formado no passo anterior são aplicadas as funções de grupo (se houver);

Quando se usa GROUP BY estamos a trabalhar em "modo de grupo" o que implica deixar de considerar linhas individuais para considerar grupos de linhas. A cada grupo de linhas apenas podemos aplicar funções de grupo, tais como: média, variância, valor máximo, valor mínimo ou contagem do número de elementos.

O próximo exemplo mostra quantos clientes existem em cada CITY (cidade). As linhas da tabela CUSTOMER são ordenadas por CITY. Depois são formados conjuntos de linhas cujos elementos têm em comum o valor de CITY. Finalmente é feita a contagem do número de linhas de cada conjunto:

select city, count(*) from customer
group by city;

O exemplo abaixo mostra como ORDER BY pode ser usado em conjunto com GROUP BY. Neste caso pretendemos obter o número de clientes por cidade e ordenar o resultado, mostrando primeiro as cidades que têm mais clientes (ordenação decrescente):

select city, count(*) from customer
group by city
order by count(*) desc;

O exemplo abaixo determina quantas encomendas (ORDERS) foram feitas por cada cliente:

select cust_id, count(*)
from orders
group by cust_id
order by count(*) desc;

O exemplo abaixo determina a data mais antiga e a data mais recente das encomentas de cada cliente:

select cust_id, min(date), max(date)
from orders
group by cust_id;

O exemplo faz uma consulta à tabela ITENS, que representa a "linha de encomenda". Cada linha de encomenda contem um determinado número de garrafas de um determinado vinho. Para cada vinho o query determina:

Quantas vezes foi encomendado;
O número total de garrafas que foram encomendadas;
O número mínimo de garrafas que o cliente levou numa encomenda;
O número médio de garrafas que o cliente levou numa encomenda
O número máximo de garrafas que o cliente levou numa encomenda

select wine_id, count(*), sum(qty), min(qty), max(qty), avg(qty)
from items
group by wine_id;

Exercício: Escreva um query que para cada vinho determine o valor mínimo, médio e máximo a que foi vendido.

Topo

Opção ROLLUP

A opção ROLLUP pode ser usada em conjunto com GROUP BY para obter somas agregadas sobre grupos.

O query abaixo determina o as receitas obtidas para cada vinho:

select wine_id, sum(price)
from items
group by wine_id;

A soma total de receitas pode ser obtida por um segundo query. A cláusula ROLLUP adiciona ao query anterior permite obtê-la no mesmo query:

select wine_id, sum(price)
from items
group by wine_id with rollup;

A verdadeira vantagem vem quando o query complica. No exemplo abaixo obtemos as receitas por vinho e por cliente, com as respectivas somas parciais e totais:

select wine_id, cust_id, sum(price)
from items
group by wine_id, cust_id with rollup;

Topo

Cláusula HAVING

Enquanto a cláusula WHERE é utilizada no "modo de linha" para filtrar linhas, a cláusula HAVING é usada no "modo de grupo" para filtrar grupos. Esta clásula e usada depois de um GROUP BY.

O exemplo abaixo mostra as cidades onde temos mais de 12 clientes:

select city, count(*)
from customer
group by city
having count(*)>12;

O exemplo abaixo mostra os vinhos dos quais foram encomendadas mais de 80 garrafas:

select wine_id, count(*), sum(qty)
from items
group by wine_id
having sum(qty) > 80;

Algumas consultas com grupos tanto podem ser resolvidas recorrendo a uma cláusula WHERE como a uma cláusula HAVING. Considere que pretende determinar o preço médio de venda de cada vinho, excluíndo os vinhos 1, 2, 3, 4 e 5. As duas soluções apresentadas abaixo resolvem o problema:

select wine_id, avg(price)
from items
where wine_id not in (1,2,3,4,5)
group by wine_id
order by wine_id;

select wine_id, avg(price)
from items
group by wine_id
having wine_id not in (1,2,3,4,5)
order by wine_id;

Embora ambos queries tenham resolvido o problema (foram eficazes), as duas consultas não são igualmente eficientes. A primeira consulta segue o seguinte plano de execução:

Identificar todas as linhas da tabela excluíndo as que se referem a vinhos 1, 2, 3, 4 e 5;
Ordenar as linhas por wine_id;
Criar os conjuntos por wine_id (agrupar);
Determinar a média de cada conjunto;

A segunda consulta segue este plano de execução:

Identificar todas as linhas da tabela;
Ordenar as linhas por wine_id;
Criar os conjuntos por wine_id (agrupar);
Determinar a média de cada conjunto;
Eliminar os conjuntos pertencentes aos vinhos 1, 2, 3, 4 e 5;

Comparando os dois planos de execução verificamos que no primeiro as linhas são filtradas no inicio, o que significa que as operações de ordenação e agrupamento são feitas sobre menos linhas. No segundo plano os grupos são formados tendo em conta todas as linhas, sendo depois eliminado um dos grupos. Quando as tabelas têem grande dimensão esta diferença é significativa, tornando a primeira consulta mais eficiente pelos seguintes motivos:

Tem menos linhas para ordenar;
Tem menos contas para fazer, já que há menos linhas e menos grupos;
Não deita fora trabalho já feito;

Topo

Cláusula DISTINCT

O query abaixo mostra as cidades onde vivem os nossos clientes:

select city from customer order by city;

Os nomes das cidades aparecem repetidos porque temos vários clientes na mesma cidade. Para evitar as repetições podemos usar a cláusula DISTINCT:

select distinct city from customer order by city;

A opção DISTINCT elimina linhas repetidas considerando todas as colunas como um todo, como mostra o exemplo abaixo:

select distinct surname, country_id from customer order by city;

Este comando tem um plano de execução semelhante ao ORDER BY, sendo portanto pesado para a base de dados.

Topo

Limitar o número de linhas

A opção LIMIT permite limitar as linhas resultantes do query. O critério para limitar o resultado é uma contagem das linhas à medida que são extraídas da tabela. Embora não seja uma opção standard é muito útil para o programador e poupa recursos na base de dados.

Esta opção é sempre a última a ser colocada no query, sempre de pois de WHERE, GROUP BY, HAVING e ORDER BY.

Os queries abaixo mostram apenas as primeiras 5 linhas. O segundo exemplo evidência que a primeira linha tem o número de ordem zero.

select * from customer limit 5;
select * from customer limit 0,5;

O query abaixo mostra apenas as primeiras 5 linhas começando na centésima, ou seja, mostra as linhas 100, 101, 102, 103 e 104.

select * from customer limit 100,5;

O query abaixo mostra da linha 600 até ao final:

select * from customer limit 600,12345678901234567890;

Topo

Funções e Operadores

O MySQL disponibiliza uma grande variedade de funções e operadores, cuja sintaxe e descrição pode ser consultada na ajuda do MySQL Query Browser ou no manual do MySQL. Segue um resumo:

Funções Artitméticas

select 8+3*2;
select (8+3)*2;
select sin(PI());
select sin(PI()/2);
select degrees(PI());
select ceiling(3.14159);
select floor(3.14159);
select format(12345.6435,0);
select format(12345.6435,1);
select format(12345.6435,2);
select ln(10);
select log(10);
select log(10,2);
select log(10,3);
select log(10,10);
select log10(10);
select mod(10,3);
select 10%3;
select pow(4,2);
select rand();
select round(3.14159);
select sqrt(36);
select truncate(3.14159,0);
select truncate(3.14159,3);

Topo

Operadores de comparação

Os operadores de comparação são usados em expressões lógicas que devolvem 1 quando é VERDADEIRO e 0 quando é FALSO.

select 2=2;
select 2!=3;
select 2<>3;
select 2<3;
select 2<=2;
select 3>2;
select 2>=2;
select 'Banana' = 'Banana';
select 'Banana' < 'Maça';

Topo

Operadores do SQL

A linguagem SQL define um conjunto de operadores de comparação específicos:

select 5 between 2 and 7;
select 7 between 2 and 7;
select 'Laranja' between 'Banana' and 'Maça';
select 8 not between 2 and 7;
select 6 in (1,2,3,4,'5','6','cat',3.14159);
select 6 not in (1,2,3,4,'5','cat',3.14159);
select null is null;
select 0 is not null;

Uma comparação com NULL feita com outro operador diferente de IS NULL ou IS NOT NULL devolve sempre NULL:

select null > 0;
select null < 0;
select null = 0;
select null != 0;

O MySQL possui um operador adicional para fazer comparações com NULL: <=>. Se houver NULL dos dois lados devolve 1 (verdade). No caso contrário devolve 0 (falso):

select null <=> null;
select not (null <=> 0);

Topo

Funções para strings

Seguem alguns exemplos de funções que trabalham com strings:

select 'Apple' like 'A%';
select 'Apple' like 'A_p__';
select concat('con','cat');
select concat('con','c','at');
select concat_ws('-','aaa','bbb','ccc','ddd');
select length('Apple');
select locate('p','Apple');
select locate('p','Apple',3);
select lower('APPLE');
select upper('apple');
select ltrim('   Apple');
select rtrim('Apple  ');
select trim('   Apple  ');
select strcmp('a','a');
select strcmp('A','a');
select strcmp('a','b');
select strcmp('b','a');
select substring('Apple',2,3);

Topo

Funções para data e tempo

Seguem alguns exemplos de funções que trabalham com datas e tempos:

select now();
select curdate();
select curtime();
select date('2005-05-03 12:22:54');
select date_add('2005-05-03', INTERVAL 1 DAY);
select date_format(now(), "%W, %e %M, %Y.");
select dayofweek('2000-05-03');
select dayname('2000-05-03');
select dayofyear('2000-05-03');
select monthname('2000-05-03');
select extract(YEAR FROM '2005-05-03 12:22:54');
select quarter('2005-05-03 12:22:54');
select time('2005-05-03 12:22:54');
select timestamp('2005-05-03 12:22:54');
select week('2005-05-03 12:22:54');
select weekday('2005-05-03 12:22:54');

Topo

Funções para cifra de dados

Seguem alguns exemplos de funções que podem ser usadas para cifrar dados:

A função password é usada internamente pelo MySQL para cifrar as passwords:

select password('palavrasecreta');

A função MD5():

select md5('palavrasecreta');

A sequência de comandos abaixo permite cifrar e decifrar usando uma solução mais segura que o MD5 anterior:

set @Name = 'ABCDE';
set @returnedName = ' ';
select hex(aes_encrypt(@Name,UNHEX(SHA2('aabbccddeeffGG',512))))  into @returnedName;
select @returnedName;
select convert(aes_decrypt(unhex(@returnedName),UNHEX(SHA2('aabbccddeeffGG',512))) using utf8mb4);

Topo

Funções especiais

select if(0<1, "sim", "nao");
select database();
select user();

Topo

Realizado por Turtle Learning ®. Última alteração em 2011-02-26