Olá!!! Finalmente arrumei tempo para publicar a segunda parte do tutorial de MySql que fiz algum tempo atrás. A primeira parte se encontra aqui. Bons estudos!!!

21)      Usando funções de agregação

Existem determinadas operações que podem ser realizadas pelo banco de dados para efetuar cálculos em cima dos dados de uma tabela, levando-se em conta uma determinada coluna. Por exemplo, podemos querer saber a quantidade de clientes que possuem um determinado nome. Para isso precisamos contar a quantidade de nomes iguais e agrupá-los por nome. Para realizar essa consulta nós usaríamos o seguinte código:

select nome, count(*) from cliente group by nome;

A seguir temos uma lista de funções, chamadas funções de agregação, que são comumente utilizadas em consultas:

• avg – retorna a média do valor da expressão na consulta.
• count – retorna a quantidade de valores não nulos da expressão na consulta.
• count(*) – retorna a quantidade de registros na consulta.
• max – retorna o valor máximo da expressão na consulta.
• min – retorna o valor mínimo da expressão na consulta.
• sum – retorna a soma do valor da expressão na consulta.

Vamos ver uma a uma, como utilizar cada função em uma consulta.

select avg(idade) from cliente;

ou

select cidade, avg(idade) from cliente group by cidade;

A primeira consulta retorna a média das idades de todos os clientes e a segunda retorna a média das idades dos clientes por cidade.

select count(*) from cliente;

ou

select cidade, count(*) from cliente group by cidade;

A primeira consulta retorna a quantidade de clientes e a segunda retorna a quantidade de clientes por cidade.

select max(idade) from cliente;

ou

select cidade, max(idade) from cliente group by cidade;

A primeira consulta retorna a maior idade entre os clientes e a segunda retorna a maior idade entre os clientes de uma cidade.

select min(idade) from cliente;

ou

select cidade, min(idade) from cliente group by cidade;

A primeira consulta retorna a menor idade entre os clientes e a segunda retorna a menor idade entre os clientes de uma cidade.

select sum(idade) from cliente;

ou

select cidade, sum(idade) from cliente group by cidade;

A primeira consulta retorna a soma das idades de todos os clientes e a segunda retorna a soma das idades de todos os clientes de uma cidade.

2)      Usando funções pré-definidas

Além das funções de agregação, existem outras funções que utilizamos para manipular os dados de uma consulta. Essas funções costumas ser divididas de acordo com os tipos de dados que manipulam. A seguir temos uma lista de algumas funções mais utilizadas:

Funções para manipulação de texto

• length – função que retorna o tamanho de um texto.
• lower – retorna um texto em minúsculas.
• upper – retorna um texto em maiúsculas.
• substring – retorna parte de um texto.
• replace – substitui parte de um texto por outro texto.
• locate – retorna a posição de um determinado texto dentro de outro texto.
• trim – remove os espaços em branco no início e no fim de um texto.
• concat – concatena dois textos.

Exemplos:

select length(c.nome) from cliente c;

select lower(c.nome) from cliente c;

select upper(c.nome) from cliente c;

select substring(c.nome,1,3) from cliente c;

select replace(‘banana’,’a’,’u’) from cliente c;

select locate(‘a’,’banana’) from cliente c;

select trim(‘    banana    ‘) from cliente c;

select concat(c.nome,’-‘,c.uf) from cliente c;

Funções para manipulação de números:

• round – arredonda um determinado número.
• truncate – trunca um determinado número.

Exemplos:

select round(10.32);

select truncate(10.32,1);

Funções para manipulação de data

• curdate – retorna a data atual.
• adddate – adiciona ou subtrai de uma data um determinado intervalo.
• datediff – retorna o intervalo entre duas datas.

Exemplos:

select curdate();

select adddate(curdate(),20);

select adddate(curdate(),INTERVAL 20 MONTH);

select datediff(curdate(),’19800726′);

select truncate(datediff(curdate(),’19800726′)/365,0) ‘Anos’,

truncate((datediff(curdate(),’19800726′)/365 – truncate(datediff(curdate(),’19800726′)/365,0))*12,0) ‘Meses’,

truncate(((datediff(curdate(),’19800726′)/365 – truncate(datediff(curdate(),’19800726′)/365,0))-

truncate((datediff(curdate(),’19800726′)/365 – truncate(datediff(curdate(),’19800726′)/365,0)),1))*365,0) ‘Dias’,

(((datediff(curdate(),’19800726′)/365 – truncate(datediff(curdate(),’19800726′)/365,0))-

truncate((datediff(curdate(),’19800726′)/365 – truncate(datediff(curdate(),’19800726′)/365,0)),1))*365-

truncate(((datediff(curdate(),’19800726′)/365 – truncate(datediff(curdate(),’19800726′)/365,0))-

truncate((datediff(curdate(),’19800726′)/365 – truncate(datediff(curdate(),’19800726′)/365,0)),1))*365,0))*24 ‘Horas’;

Operadores aritméticos

• +, -, *, / – realizam as quatro operações matemáticas.
• div – divisão de inteiros.
• % – resto da divisão de inteiros.

Exemplos:

select 10 + 10, 10 – 10, 10 * 10, 10 / 10;

select 10 div 3, 10 % 3;

3)      Relacionamento entre tabelas

Um banco de dados relacional, como o próprio nome diz, é composto por tabelas que se relacionam através de colunas em comum. Em um relacionamento entre tabelas, geralmente uma delas é chamada de tabela pai e a outra de tabela filha. A tabela pai é tida como principal e a filha como secundária. Nós podemos inserir um registro na tabela pai sem nos preocuparmos com o relacionamento com a tabela filha. Já o inverso não é verdade. Nós só podemos inserir registros na tabela filha caso haja um registro correspondente na tabela pai. Esse relacionamento pode ou não ser representado através de chaves estrangeiras, que informam quais são as colunas de cada tabela que se correspondem e são utilizadas para manter a integridade do banco, isto é, não permitindo que existam registros filhos sem um registro pai correspondente.

4)      Realizando consultas em múltiplas tabelas

Entretanto, independentemente da existência ou não de chaves estrangeiras, nós podemos realizar consultas em várias tabelas simultaneamente, bastando para isso especificar na consulta a relação existente entre as tabelas. Por exemplo:

select * from cliente c, locacoes l

where c.codigo = l.codigocliente

Na consulta acima nós teremos como resultado um registro composto por todas as colunas da tabela cliente e da tabela locações, aonde haja para cada registro da tabela cliente, um registro correspondente na tabela locações. O servidor do banco de dados irá utilizar o filtro da cláusula where para fazer relacionar as tabelas consultadas. O servidor não tem como avaliar se um filtro está correto ou não, ficando a cargo do desenvolvedor verificar possíveis erros na consulta.

Ainda analisando a consulta anterior, se presumirmos que a tabela cliente possui cinco registros e a tabela locações possui vinte registros, e ainda que a integridade do banco está mantida, nós teremos como resultado dessa consulta um total de vinte registros. Caso não haja nenhum registro na tabela de locações, o filtro especificado não será satisfeito, e portanto nenhum registro será retornado.

Vamos alterar a nossa consulta adicionado mais um filtro:

select * from cliente c, locacoes l

where c.codigo = l.codigocliente

and c.codigo = 1

Nessa nova consulta a relação entre as tabelas foi mantida, mas foi adicionado um filtro que especifica um determinado cliente. Nesse caso o número total de registros retornado irá depender da quantidade de locações em que o código do cliente seja igual a um. Caso não existam locações para esse cliente, ou ainda, esse cliente não exista, nenhum registro será retornado.

Vamos agora alterar a consulta removendo todos os filtros:

select * from cliente c, locacoes p

Como não há nenhum filtro que especifique o relacionamento entre as tabelas, o resultado da consulta será o arranjo registro a registro entre as duas tabelas, ou seja, para cada registro da tabela cliente será retornado todos os registros da tabela locações. Caso a tabela cliente possua cinco registros e a tabela cliente possua vinte registros, a consulta retornará um total de cem registros.

Nesse caso como não esse resultado não faz sentido essa consulta pode ser considerada como incorreta. Entretanto, embora raro, existem certos casos em que uma consulta entre várias tabelas pode não conter filtros de relacionamento. Um exemplo seria uma consulta que retornasse todos os modelos de carro de uma concessionária juntamente com todas as cores disponíveis. Supondo que exista nesse banco uma tabela de modelos de carro e outra tabela de cores, uma consulta direta nessas duas tabelas sem especificar o relacionamento, até mesmo porque não há nenhum nesse caso, poderia ser considerada correta.

Foi dito anteriormente que no caso da primeira consulta aonde foi especificado o relacionamento entre cliente e locações, que se não existisse registros na tabela locações nenhum registro seria retornado. Entretanto existem casos em que queremos que os registros da tabela principal ou tabela pai, sejam sempre retornados, mesmo que não exista nenhum registros correspondente na tabela filha. Nesses casos é necessário realizar a consulta de forma diferente, utilizando um left join, ou junção pela esquerda. Vamos ver como isso funciona na prática:

select * from cliente c left join locacoes l on c.codigo = l.codigocliente

Uma consulta com left join é montada da seguinte forma: a esquerda da cláusula left join fica a tabela principal e a direita a tabela secundária. Após a tabela secundária nós temos a cláusula on que funciona como a cláusula where especificando o filtro ou o relacionamento entre as tabelas. Entretanto é recomendado que a cláusula on seja utilizada somente para especificar os relacionamentos entre as tabelas e a clásula where para filtrar os registros desejados.

Só a título de curiosidade existem ainda as cláusulas right join que invertem o sentido das tabelas, isto é, traz os registros da tabela da direita mesmo que não existam correspodentes na tabela da esquerda e a cláusula inner join que funciona da mesma forma da consulta tradicional que separa as tabelas por vírgulas.

5)      Realizando sub-consultas

Sub consultas são consultas normais que são inseridas dentro de uma outra consulta no lugar de uma coluna. Vamos ver alguns exemplos de sub consultas:

select c.nome, (select count(*) from locacoes l where l.codigocliente = c.codigo)

from cliente c

Na consulta acima nós teremos como resultado um registro contendo o nome do cliente e a quantidade de locações que ele possui. Entretanto sub consultas não estão restritas a retornar campos calculados e podem ser utilizadas também em filtros, como por exemplo:

select c.nome

where (select count(*) from locacoes l where l.codigocliente = c.codigo) > 0

Na consulta acima estamos querendo os nomes dos clientes que possuem pelo menos uma locação. Apesar de funcionar, a consulta acima é geralmente feita da seguinte forma, por questões de desempenho.

select c.nome

where exists (select count(*) from locacoes l where l.codigocliente = c.codigo)

A cláusula exists testa a sub consulta para saber se a mesma retournou algum registro. Ainda existe a variação not exists que testa se a sub consulta retornou zero registros:

select c.nome

where not exists (select count(*) from locacoes l where l.codigocliente = c.codigo)

A consulta acima retorna os nomes dos clientes que não possuem locações.

Vale a pena deixar claro que é possível a partir de uma sub consulta referenciar uma tabela fora da consulta, isto é, uma tabela da consulta principal. Isto é feito nos exemplos acima quando referenciamos a coluna código da tabela cliente.

6)      Função Case

Imagine uma tabela que armazene em uma coluna de nome “Sexo” o valor ‘M’ para o sexo masculino e o valor ‘F’ para o sexo feminino. Caso realizássemos uma consulta nesta tabela os valores retornados para a coluna Sexo seriam obviamente “M”e “F”, o que pode ser considerado não muito significativo. E se no lugar de “M” e “F” retornássemos as palavras “Masculino” e “Feminino” respectivamente, mas sem alterar o conteúdo da tabela?

Para isso devemos utilizar o comando “case” que tem a seguinte sintaxe:

select case when Sexo = ‘M’ then ‘Masculino’ else ‘Feminino’ end ‘Sexo’

from cliente

Caso haja mais de duas opções a serem consideradas podemos realizar a consulta do seguite modo:

select case when Sexo = ‘M’ then ‘Masculino’

when Sexo = ‘F ‘then ‘Feminino’ else ‘???’ end ‘Sexo’

from cliente

Vale ressaltar que o comando case pode ser utilizado não só como valor de retorno, mas também em filtros de consultas.

7)      Utilizando índices

Imagine que você precisa buscar um nome de um contato em uma agenda de celular com 500 nomes. Como as agendas de celular são, geralmente, ordenadas alfabeticamente, nós podemos digitar a primeira letra do nome do contato que estamos procurando e em seguida avançar um a um até achar o contato desejado. Caso haja muitos contatos com a mesma letra inicial, nós digitamos uma segunda letra, e assim por diante. Podemos dizer que os contatos da agenda do celular possuem um índice baseado no nome.

As tabelas possuem por padrão uma ordenação baseada nas colunas que compõe a chave primária. Isto é, sempre que inserimos um registro numa tabela, esse registro será posicionado levando-se em conta o valor dos campos desse registro que fazem parte da chave primária da tabela. No momento em que realizamos uma consulta utilizando como filtro os campos da chave primária da tabela, o banco de dados pode operar da mesma forma como fazemos na agenda de celular, isto é, ele realiza uma busca binária, e não uma busca seqüencial, na tabela o que faz com que a consulta se torne bem mais rápida.

Mas e quando queremos realizar consultas com filtros que não a chave primária da tabela? Nesse caso utilizamos os índices. Cada tabela do banco de dados pode conter um ou mais índices. Cada índice pode conter um ou mais campos (colunas) da tabela. Quando criamos um índice em uma tabela, estamos dizendo ao banco de dados que armazene os registros dessa tabela ordenados pelas colunas que compõe o índice. É claro que o banco não precisa armazenar uma cópia de todas as colunas, somente as que participam do índice, por exemplo, imagine que temos a tabela a baixo:

Codigo	Nome	Idade
1	João	20
2	José	30
3	Maria	12

Podemos ver que os dados da coluna Idade estão desordenados. Se essa tabela possuísse milhares de registros seria muito complicado pesquisar por exemplo, os registros com idade entre 10 e 15. Para resolver isso vamos criar um índice baseado na coluna Idade. O banco de dados irá criar uma tabela similar a que veremos a seguir para armazenar as colunas indexadas:

Idade	Codigo
12	3
20	1
30	2

Agora quando realizarmos uma consulta pelo campo Idade o banco irá verificar que existe um índice baseado nesse campo e não precisará realizar uma consulta seqüencial, isto é, linha por linha, e sim irá utilizar o índice. Como no índice os registros estão ordenados por idade, fica muito fácil localizar as linhas que contém as idade desejadas, e em seguida localizar as mesmas na tabela.

8)      Criando visões de dados

Uma visão é como se fosse uma tabela, cuja estrutura depende dos campos retornados pela consulta utilizada na sua definição. Embora seja possível inserir dados em uma tabela através de uma visão, o mais comum é que elas sejam utilizadas para realizar e simplificar consultas. Quando identificamos uma consulta como sendo uma entidade do nosso negócio, nós podemos criar uma visão que irá representar essa entidade. Tomemos como exemplo a seguinte consulta:

select c.codigo, c.nome, t.nome, t.datasaida

from cliente c, locacoes l, titulo t

where c.codigo = l.codigocliente

and t.codigo = l.codigotitulo

and l.dataretorno is null

A consulta acima retorna todos os títulos que não possuem data de retorno, isto é, que estão locados. Se entendermos que “Títulos Locados” é uma entidade do nosso negócio e que iremos realizar várias consultas utilizando essa mesma estrutura, nós podemos criar uma visão chamada TitulosLocados que irá facilitar o nosso trabalho, vejamos:

create view TitulosLocados as

select c.codigo, c.nome, t.nome, t.datasaida

from cliente c, locacoes l, titulo t

where c.codigo = l.codigocliente

and t.codigo = l.codigotitulo

and l.dataretorno is null

Para criar uma visão nós utilizamos o comando create view seguido do nome da visão e da cláusula “as”. Após a cláusula “as” nós especificamos a consulta que irá definir a visão. Após criada a visão nós podemos realizar consultas na mesma como se ela fosse uma tabela:

select * from TitulosLocados

Além disso nós podemos também realizar filtros como em qualquer consulta:

select * from TitulosLocados

where c.codigo = 1

Perceba que toda a complexidade da consulta ficou encapsulada na visão. Podemos dessa forma, permitir que usuários menos experientes realizem consultas complexas, utilizando visões criadas por outros mais experientes.

9)      Criando stored procedures

Uma stored procedure (procedimento armazenado) é um conjunto de instruções SQL que podem ser armazenadas no banco de dados. Uma vez armazenada, não há mais a necessidade de se executar tais instruções individualmente, ao invés disso, nós podemos executar a stored procedure que contém tais instruções. Algumas situações aonde o uso de stored procedures pode ser útil é quando temos aplicações escritas em diferentes linguagens ou para plataformas diferentes, mas que realizam as mesmas operações no banco de dados. Outro aspecto é o da segurança. Bancos, por exemplo, utilizam stored procedures para quase todas as operações básicas, o que fornece um ambiente consistente e seguro, aonde todas as transações são devidamente registradas. Num ambiente assim os usuários não possuem acesso direto às tabelas, ao invés disso, possuem acesso somente a stored procedures específicas.

A seguir temos um exemplo de criação de uma stored procedure que retorna uma consulta:

DELIMITER $$

DROP PROCEDURE IF EXISTS  `ConsultaCliente` $$

CREATE PROCEDURE  `ConsultaCliente` (CodigoCliente int)

BEGIN

set @CodigoCliente = CodigoCliente;

select * from cliente

where Codigo = @CodigoCliente;

END $$

DELIMITER ;

A seguir vemos como executar essa stored procedure:

CALL ConsultaCliente(1);

Vamos examinar o código anterior em detalhes. Na primeira linha nós trocamos o delimitador de instruções de “;” para “$$”. Isso é feito porque a stored procedure pode conter várias instruções que por padrão são finalizadas com o “;”. Se tentássemos executar o código que criar a procedure sem trocar o delimitador, um erro seria gerado, pois ao encontrar o primeiro “;” o banco de dados iria entender que o nosso comando tinha terminado.

A segunda linha contém a instrução que apaga a procedure caso a mesma já tenha sido criada. A terceira linha cria a procedure, informando o nome da mesma e a lista de parâmetros que deverão ser passados no momento da execução. Na quarta linha temos a definição do início do código da procedure. Na quinta linha nós criamos uma variável @CodigoCliente para armazenar o valor do parâmetro CodigoCliente. Embora não seja necessário, essa abordagem evita erros ao utilizarmos nomes de parâmetros iguais aos nomes de colunas de uma tabela. Na quinta e sexta linhas nós executamos a consulta. Na sétima linha nós finalizamos o código da procedure. Repare o uso do delimitador “$$”. Na última linha nós trocamos de volta o delimitador de instruções para o “;” que é o padrão do MySql.

10)  Criando um projeto de banco de dados (Fica como exercício)

Vamos utilizar o conteúdo visto até aqui para criarmos um banco de dados de uma loja virtual. Baseado numa lista de requisitos, vamos modelar as tabelas, inserir alguns dados e criar as consultas necessárias ao funcionamento da loja. Vamos imaginar que um cliente nos solicitou que o seu sistema seja capaz de armazenar as seguintes informações:

Dados do cliente

• Nome
• Endereço
• Telefone

Dados do produto

• Nome
• Tipo
• Quantidade em estoque
• Preço de custo
• Preço de revenda

Dados do Pedido

• Cliente do pedido
• Data do pedido
• Forma de pagamento
• Itens do pedido
• Quantidade de cada item
• Valor total de cada item

Nós teremos que criar as tabelas necessárias para armazanar os dados solicitados.

O sistema também deverá realizar as seguintes consultas:

• Lista de clientes
• Lista de produtos
• Lista de produtos por tipo
• Lista de produtos em estoque
• Lista de pedidos no período
• Lista de pedidos por cliente
• Lista de itens do pedido
• Lucro no período
• Lucro por tipo de produto
• Lista dos melhores compradores
• Lista de clientes que não realizaram compras nos últimos 3 meses
• Lista de produtos comprados nos últimos 3 meses com as respectivas quantidades e agrupados por tipo.

Nós teremos que criar os comandos SQL que irão realizar tais consultas.

Nós teremos também que criar uma visão e uma stored procedure para pelo menos 3 das consultas acima.

11)  Usando o Excel

Vamos criar uma conexão com o banco de dados, para que o Excel possa trazer as informações. Vá ao painel de controle, clique em ferramentas administrativas, clique em fontes de dados (ODBC). Agora vá para a guia Fonte de dados de sistema, clique em adicionar…. Estamos procurando o driver MySql ODBC 5.1 Driver, caso não encontre baixe desse endereço http://dev.mysql.com/downloads/connector/odbc/5.1.html, continuando clique em Concluir. Conforme figura abaixo.

Preencha conforme esteja configurado seu banco e a senha. No final clique em Test para testar a conexão conforme imagem abaixo.  Confirme sempre clicando em OK.

Pronto. Agora vamos para o Excel, na guia Dados clique em De Outras Fontes, depois em Do Assistente de Conexão de Dados.

Escolha o tipo DSN ODBC. Agora clique em Avançar. Escolha a fonte criada recentemente e clique em Avançar.

Você pode se conectar a uma tabela específica ou pode fazer uma consulta especifica. Clique em Concluir. Na segunda imagem de Importar Dados clicando em no botão OK os dados da tabela saíra na planilha. Mas querendo fazer uma consulta especifica clique em Propriedades…

Aqui temos opção de atualização. Clique na guia Definição para fazer uma consulta personalizada. Depois do select pronto é só confirmar clicando em OK.

Querendo mais consultas é só clicar em De Outras Fontes, depois em Do Assistente de Conexão de Dados conforme foi explicado acima.

É isso aí… Se alguém encontrar algum erro ou tiver alguma dúvida é só entrar em contato.

Abraços!!!