Tag: mysql

Profiling no MySql – Parte II

Olá,

há algum tempo postei aqui uma dica para habilitar o trace ou log de consultas realizadas no MySql. Entretanto hoje ao tentar realizar a mesma configuração em uma versão diferente do MySql não obtive sucesso.

Foi então que descobri que dependendo da versão do MySql a configuração do log varia. Existe agora a opção de enviar o log para uma tabela ao invés de um arquivo.

A tabela se chama general_log e se encontra na base mysql. Resumindo a história, para a versão 5.1.32 adicionei as seguintes linhas no arquivo my.ini (na seção mysqld):

log-output=TABLE
general_log=1
Feito isso e reiniciado o serviço basta realizar a seguinte consulta para obter o conteúdo do log:
SELECT * FROM mysql.general_log g;
É isso aí, abraços e até a próxima!

Tutorial MySql – Parte II

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

Entities e MySql

Olá!!! Esse post é para divulgar uma solução que ralei pra encontrar quando precisei publicar a minha primeira aplicação Asp.Net que utiliza entities. A princípio o problema era de configuração do servidor da empresa de hospedagem, portanto pode ser que outras pessoas não passem pelo mesmo problema. O erro ocorre assim que o banco de dados é acessado. A solução eu encontrei no seguinte link:

http://social.msdn.microsoft.com/Forums/pt-BR/mvcpt/thread/a091c21e-dbe6-4e64-a40e-011b61e7729f

Vamos ao erro:

“The store provider factory type ‘MySql.Data.MySqlClient.MySqlClientFactory’ does not implement the IServiceProvider interface. Use a store provider that implements this interface.”

Vamos a solução:

Adicione a seguinte configuração ao arquivo web.config (no mesmo nível system.web>):

<system.data>
<DbProviderFactories>
<remove invariant=”MySql.Data.MySqlClient” />
<add name=”MySQL Data Provider” invariant=”MySql.Data.MySqlClient”
description=”.Net Framework Data Provider for MySQL”
type=”MySql.Data.MySqlClient.MySqlClientFactory, MySql.Data,
Version=6.0.3.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d” />
</DbProviderFactories>
</system.data>
Pronto. É isso aí. Agradeço ao autor da solução por ter me poupado dias de frustração. Abraço e até a próxima!

Tutorial MySql – Parte 1

Olá! Hoje acordei de bom humor e resolvi disponibilizar um tutorial de MySql (mas que serve como tutorial de sql ansi, isto é, serve para outros bancos) que escrevi para um curso que ministrei pela minha empresa. Estarei dividindo o mesmo em duas partes para melhor legibilidade. Espero que seja útil para quem estiver iniciando.

1)      Definição de Banco de Dados

Um sistema gerenciador de banco de dados é um sistema responsável por fornecer meios para armazenagem e consulta de dados. Um banco de dados relacional organiza os dados em tabelas. Essas tabelas geralmente possuem algum tipo de relacionamento entre si. Por exemplo, em um banco de dados de um sistema de locadora nós poderíamos ter uma tabela para representar os clientes, outra tabela para representar os títulos e uma tabela para relacionar os títulos alugados por um cliente. Vejamos:

No diagrama acima podemos ver a relação entre as tabelas expressadas pelas linhas pontilhadas. Podemos perceber também que a relação entre cliente e locações é de um para vários, bem como a relação entre títulos e locações. Isso significa que para um mesmo cliente ou título pode haver várias locações.

No diagrama acima as colunas estão representadas uma abaixo da outra. Essa é uma representação da estrutura da tabela. Quando estivermos representando os dados contidos na tabela representaremos as colunas uma ao lado da outra e as linhas ou registros ficarão dispostos um abaixo do outro. Vejamos:

Ainda explorando o diagrama podemos perceber que ao lado do nome da coluna é definido o tipo do dado que essa coluna irá armazenar. Os dados possuem tipos diferentes por várias razões. A primeira é que depedendo do tipo de dado, o banco de dados irá precisar de mais ou menos espaço para armazená-lo.

Outro motivo para a existência de dierentes tipos de dados é que um banco de dados também possui uma linguagem de programação que pode executar operações com os dados e algumas operações só podem ser realizadas em determinados tipos de dados, por exemplo, uma operação que retorne os 10 primeiros caracteres de um texto só pode ser executada em dados que sejam do tipo texto e não numa data. Da mesma forma existem operações que trabalham somente com datas como, por exemplo, somar um determinado número de dias a uma data. Não faria sentido executar tal operação em um dado do tipo texto.

Vale a pena lembrar também que não é possível relacionar duas colunas que possuam tipos de dados diferentes.

2)      Chave primária (Primary Key)

Uma tabela geralmente possui um ou mais campos que são chamados de chave-primária. Os valores dos campos que formam a chave primária da tabela nunca podem se repetir. Por exemplo, podemos definir para uma tabela de clientes cuja chave primária é o CPF do cliente. Sendo assim não poderá haver dois clientes com o mesmo CPF na tabela. Uma chave primária com mais de um campo é denominada chave composta. A regra é a mesma que para uma chave simples, isto é, não pode haver duas linhas em que os campos da chave primária tenham os mesmos valores. Por exemplo, podemos definir para uma tabela de usuários que a chave primária será composta pelas colunas CodigoCliente e NomeUsuario. Nesse caso poderemos ter uma linha com os valores 1 e ‘João’ e outra com o valor 1 e ‘José’. Entretanto não será permitido ter duas linhas com os valores 1 e ‘João’ e 1 e ‘João’. Nesse caso estamos querendo dizer que o que representa um usuário distinto são o código do cliente e o nome do usuário, podendo haver então usuários com o mesmo nome mas que pertençam a clientes distintos.

3)      Chave estrangeira (Foreign Key)

Uma chave estrangeira representa um relacionamento entre colunas de tabelas diferentes ou até mesmo entre colunas de uma mesma tabela. No diagrama visto anteriormente podemos ver que a coluna “Codigo” da tabela “cliente”, está relacionada com a coluna “CodigoCliente” na tabela “locacoes”. Isso significa que para inserirmos um registro na tabela de locações nós precisamos informar um código de cliente que esteja na tabela de clientes. Dessa forma evitamos que por algum descuido nós acabemos violando a integridade do banco, ou seja, tendo locações sem cliente ou com um cliente que não existe no banco de dados. Podemos dizer que as chaves estrangeiras servem para “amarrar” o banco. E isso às vezes pode até se tornar um problema, se utilizado sem critério.

4)      Valor nulo (null)

Uma coluna pode ou não conter valores nulos. Um valor nulo é um valor não definido. Quando da definição da tabela nós podemos especificar quais colunas aceitam ou não valores nulos. Para realizarmos uma consulta cujo filtro verifica se o valor de uma coluna é ou não nulo, nós devemos utilizar respectivamente os operadores “is null” e “is not null”. Caso seja necessário realizar uma consulta aonde os valores de um campo não devam retornar nulos, nós devemos utilizar a função “ifnull(Campo,Valor)”. Veremos como aplicar esses dois conceitos mais a frente.

5)      Os tipos de dados

Os tipos de dados costumam ser os mesmos entre as várias versões diferentes de banco de dados existentes no mercado. O que muda, às vezes, de uma para outra é o nome que o tipo leva e a precisão do mesmo. A seguir temos uma lista dos principais tipos de dados do MySql:

  • · integer – representa um número inteiro. Se seguido do modificador “unsigned” representa somente números positivos.
  • · double – representa um número real de ponto flutuante. Se seguido do modificador “unsigned” representa somente números positivos.
  • · varchar(X) – representa um texto de tamanho variável, aonde X corresponde ao número máximo de caracteres que serão armazenados.
  • · char(X) – representa um texto de tamanho fixo, aonde X corresponde ao número máximo de caracteres que serão armazenados.
  • datetime – representa uma data com hora, minuto e segundo.

6)      Instalando o servidor de banco de dados MySql

Nós iremos utilizar nos nossos estudos de banco de dados o servidor de banco de dados MySql. O download desse servidor pode ser feito no seguinte link:

http://dev.mysql.com/downloads/mysql/5.1.html#win32

É necessário também baixar o seguinte pacote de programas:

http://dev.mysql.com/downloads/gui-tools/5.0.html

Após instalar os dois pacotes na ordem acima, aceitando as opções padrão, nós temos duas opções para criar um novo banco de dados. Uma é utilizando o MySql Administrator e a outra é utilizando o MySql Query Browser. Para simplificar nós iremos utilizar o MySql Query Browser, pois além de fácil de usar, esse mesmo programa será utilizado para realizar as nossas consultas posteriormente. O MySql Administrator além de permitir a criação e manutenção de bancos de dados, é utilizado também para gerenciar o servidor como um todo, como por exemplo, a manutenção de usuários e controle de permissões.

7)      Conectando com o servidor utilizando o Query Browser

Após executarmos o programa MySql Query Browser a seguinte tela aparecerá:

Os campos que merecem atenção são o “Server Host”, que é o nome ou IP do computador no qual o servidor de banco de dados está instalado, “Username” que é o nome do usuário do banco de dados, “Password” que é a senha do usuário e “Default Schema” que é o nome da base de dados com que iremos trabalhar. Preencha estes campos comforme a imagem acima e em seguida clique em “OK”. Após realizada a conexão pressione a tecla “F11” para trabalharmos no modo maximizado.

8)      Criando um banco de dados utilizando o Query Browser

Para criar um novo banco de dados basta clicarmos com o botão direito do mouse dentro da janela “Schemata” que está localizada do lado direito superior da interface da aplicação, e em seguida selecionar a opção “Create New Schema”, conforme mostra a imagem abaixo:

O próximo passo é informar o nome do banco como vemos na imagem abaixo:

A base de dados criada será listada na janela “Schemata” como vemos na imagem abaixo:

9)      Criando tabelas utilizando o Query Browser

Para criar uma tabela devemos clicar com o botão direito do mouse na janela “Schemata” e selecionar a opção “Create New Table”, conforme mostra a imagem abaixo:

Em seguida será apresentado o editor de tabelas, conforme mostra imagem abaixo:

O nome da tabela deve ser informado no campo “Table Name”. Ao lado desse campo está o campo “Database” que indica em qual banco de dados nós iremos criar a tabela. Na aba “Columns e Indices” nós iremos especificar as colunas da nossa tabela, bem como seus tipos. Após definidas as colunas da tabela devemos clicar em “Apply Changes” para que a mesma seja criada. Uma tela de confirmação contendo o código de criação da tabela irá aparecer, conforme mostra a imagem abaixo:

Para criarmos a tabela basta clicarmos em “Execute”. A nova tabela será listada conforme mostra a imagem abaixo:

10)  Inserindo, atualizando e excluindo dados utilizando o Query Browser

Para inserirmos valores na tabela nós devemos em primeiro lugar nos certificarmos que estamos com a base de dados selecionada. Para isso basta darmos um duplo clique no nome da base de dados na janela “Schemata”. Em seguida devemos dar um duplo clique no nome da tabela na qual desejamos inserir dados. Um comando para retornar os dados da tabela será criado na janela de consulta. Para executar a consulta basta pressionar as teclas “CTRL-ENTER”. Na parte inferior esquerda da interface da aplicação aparecerá a estrutura da tabela sem nenhum dado. Para inserir algum dado na tabela basta clicarmos no botão “Edit”, que se encontra abaixo da estrutura da tabela e em seguida digitarmos os valores desejados nas células da tabela, como faríamos em uma planilha eletrônica. Vejamos um exemplo na imagem abaixo:

12)  Criando um banco de dados via código

Para criarmos um novo banco de dados via código nós devemos utilizar o comando “create database” seguido do nome da base que desejamos criar, como a seguir:

create database `aula_banco`;

Toda instrução MySql é terminada por padrão com um ponto e vírgula. Para atualizarmos a lista de banco de dados basta pressionar F5. As aspas em volta do nome do banco são opcionais.

13)  Criando tabelas via código

Para criarmos uma nova tabela via código nós devemos utilizar o comando “create table” como a seguir:

CREATE TABLE  `cliente` (

`Codigo` int(10) unsigned NOT NULL auto_increment,

`Nome` varchar(45) NOT NULL,

`Endereco` varchar(45) NOT NULL,

`Bairro` varchar(45) NOT NULL,

`Cidade` varchar(45) NOT NULL,

`UF` varchar(2) NOT NULL,

`Telefone` varchar(45) NOT NULL,

PRIMARY KEY  (`Codigo`)

);

Para atualizarmos a lista de tabelas basta pressionar F5. As aspas em volta do nome da tabela são opcionais. O comando “create table” deve ser seguido do nome da tabelas que desamos criar. Em seguida entre parênteses vem a lista das colunas da tabela. A definição de uma coluna é composta geralmente pelo nome da coluna, seguido do tipo da coluna e pelo chave que informa se a coluna aceita ou não nulos. A chave “auto_increment” só é válida para campos inteiros e informa que este campo será incrementado automaticamente. Só pode haver um campo auto_increment na tabela e geralmente é a chave primária.

A seguir temos um código que cria a tabela de locações. Além de especificar as colunas o código também define as chaves estrangeiras para as tabelas de clientes e títulos.

CREATE TABLE  `locacoes` (

`Codigo` int(10) unsigned NOT NULL auto_increment,

`CodigoCliente` int(10) unsigned NOT NULL,

`CodigoTitulo` int(10) unsigned NOT NULL,

`DataSaida` datetime NOT NULL,

`DataRetorno` datetime NULL,

PRIMARY KEY  (`Codigo`),

KEY `FK_locacoes_cliente` (`CodigoCliente`),

KEY `FK_locacoes_titulo` (`CodigoTitulo`),

CONSTRAINT `FK_locacoes_cliente` FOREIGN KEY (`CodigoCliente`) REFERENCES `cliente` (`Codigo`),

CONSTRAINT `FK_locacoes_titulo` FOREIGN KEY (`CodigoTitulo`) REFERENCES `titulo` (`Codigo`)

);

14)  Inserindo dados via código

Existem algumas forma diferentes de se inserir dados ou registros ou ainda linhas em uma tabela. Todas utilizam o comando insert. O código a seguir insere uma única linha na tabela de clientes:

insert cliente values (1,’Joao’,’Rua 1′,’Centro’,’Fortaleza’,’CE’,’99999999′);

No exemplo acima, não informamos os nomes das colunas, só os valores. Para isto é necessário que informemos os valores para todas as colunas da tabela.

O código a seguir insere uma linha, mas definindo quais colunas desejamos inserir:

insert cliente (Codigo, Nome ) values (4,’Joao’);

Para esse exemplo funcionar no nosso banco, devemos alterar a tabela de clientes para que ela permita nulos nos campos que não estão sendo informados.

O exemplo a seguir insere a mesma linha, mas sem informar o valor para a coluna “Codigo”. Isso pode ser feito pois a coluna “Codigo” foi marcada como auto_increment, isto é, o banco irá gerar um valor seqüencial para este campo para cada linha que for inserida:

insert cliente (Nome ) values (‘Joao’);

O próximo exemplo insere múltiplas linhas de uma só vez:

insert cliente (Codigo, Nome ) values

(1,’Joao’),

(2,’José’);

Por último vamos inserir uma linha a partir de uma consulta.

insert cliente

select  * from clientes_antigos

ou ainda

insert cliente (Nome, Endereco)

select  Nome, Endereco from cliente_antigo

Nós veremos mais a frente como realizar consultas, mas por hora o importante é saber que é possível inserir dados a partir de uma consulta.

15)  Atualizando dados via código

Para atualizarmos os dados de uma tabela nós utilizamos o comando update. No exemplo a seguir nós atualizamos o nome e o endereço de todos os clientes para um mesmo valor:

update cliente set

Nome = ‘Cliente’,

Endereco = ‘Rua 1’

No próximo exemplo nós atualizamos o endereço de um cliente com um determinado código:

update cliente set

Nome = ‘Cliente 1’,

Endereco = ‘Rua 1’

Where Codigo = 1

Neste último exemplo nós iremos atualizar duas tabelas simultâneamente:

update cliente, locacoes set

cliente.Endereco = ‘Rua 2’, locacoes.DataRetorno = ‘20080110’

where cliente.Codigo = locacoes.CodigoCliente;

16)  Excluindo dados via código

Para exlcuir todos os registros de uma tabela nós utilizamos o comando delete, como a seguir:

delete from cliente;

Para excluir um determinado registro de uma tabela nós definimos um filtro com a cláusula where:

delete from cliente where Codigo = 1;

Lembrando que geralmente, caso uma tabela esteja relacionada a outra através de chaves estrangeiras, nós só poderemos excluir registros da tabela “filha”, isto é, da tabela aonde está definida a chave estrangeira. Se tentarmos excluir um registro de uma tabela que está sendo referenciada por uma chave estrangeira, pode ser que ocorra um erro, caso haja um registro em alguma tabela que esteja fazendo referência a tabela  a qual estamos excluindo registros.

17)  Realizando consultas em uma única tabela

Finalmente chegamos ao tópico mais interessante, e provavelmente o mais extenso, que é a realização de consultas ao banco de dados. Vamos começar com uma consulta simples que irá trazer todos os campos e registros de uma tabela:

select * from cliente;

Vamos examinar esse código em partes. Podemos perceber que o comando utilizado para realizar consultas é o “select”. Após a cláusula select devemos especificar quais campos (colunas) desejamos retornar. Como estamos consultando todas as colunas nós podemos utilizar o caractere * para representá-las. Após especificarmos as colunas nós devemos especificar qual tabela nós estamos consultando. Isso é feito através do uso da cláusula “from” seguida do nome da tabela. Lembrando que podemos consultar mais de uma tabela, o que veremos mais a frente. A consulta acima poderia ter sido escrita da seguinte forma:

select Codigo, Nome, Endereco, Bairro, Cidade, UF, Telefone from cliente;

Podemos ainda alterar a descrição de uma campo caso não estejamos satisfeitos com o nome original do mesmo. Podemos realizar a consulta anterior da seguinte forma:

select Codigo as CodigoAluno, Nome as NomeAluno, Endereco as EnderecoAluno, Bairro as BairroAluno, Cidade as CidadeAluno, UF as UFALuno, Telefone as TelefoneAluno from cliente;

ou ainda:

select Codigo `CodigoAluno`, Nome `NomeAluno`, Endereco `EnderecoAluno`, Bairro  `BairroAluno`, Cidade `CidadeAluno`, UF `UFALuno`, Telefone `TelefoneAluno` from cliente;

Essa técnina é muito utilizada quando utilizamos funções para manipular os campos de uma consulta. Isso será visto mais adiante.

18)  Filtrando registros de uma consulta

Embora seja fácil consultar todos os registros de uma tabela, é altamente recomendável que não o façamos. Em se tratando de sistemas com tabelas pequenas não encontramos muitos problemas, mas para sistemas com tabelas maiores devemos sempre procurar filtrar as nossas consultas para evitar problemas de performance. Para realizar filtros em consultas utilizamos a cláusula “where” seguida de uma expressão de teste que deverá sempre retornar verdadeiro ou falso. Quando tal expressão for verdadeira o registro em questão será retornado. Nós já utilizamos a clásula where antes na atualização e exclusão de registros. Vejamos um exemplo agora em uma consulta:

select * from cliente where Codigo = 1

Na consulta acima nós estamos solicitando que o banco nos traga todos os campos de todos os registros da tabela cliente aonde o código do cliente seja igual a 1. A expressão “Codigo = 1” só será verdade quando o campo código de um registro for igual a 1. Como a nossa tabela cliente tem como chave primária o campo Codigo, ou seja, este campo não se repete, nós podemos garantir que essa consulta retornará nenhum ou apenas um registro.

É claro que existem outros operadores além do operador de igualdade. A seguir temos uma lista dos operadores mais utilizados para realizar filtros em consultas:

  • = (Igual a) – Este operador retorna verdadeiro caso os dois lados da expressão sejam iguais.
  • > (Maior que) – Este operador retorna verdadeiro caso o lado esquerdo da expressão seja maior que o lado direito.
  • < (Menor que) – Este operador retorna verdadeiro caso o lado esquerdo da expressão seja menor que o lado direito.
  • > (Maior ou igual a) – Este operador retorna verdadeiro caso o lado esquerdo da expressão seja maior ou igual ao lado direito.
  • < (Menor ou igual a) – Este operador retorna verdadeiro caso o lado esquerdo da expressão seja menor ou igual ao lado direito.
  • between (Entre) – Este operador retorna verdadeiro caso a expressão esteja dentro da faixa de valores expecificada.
  • like (Como) – Utilizado com texto. Retorna verdadeiro caso um determinado texto (char ou varchar) comece, termine ou contenha uma determinada sequência de caracteres.

Vamos ver um a um, como utilizamos os operadores listados:

select * from cliente where codigo = 1

select * from cliente where codigo > 1

select * from cliente where codigo >= 1

select * from cliente where codigo < 10

select * from cliente where codigo <= 10

select * from cliente where codigo between 1 and 10

select * from cliente where nome like ‘j%’

select * from cliente where nome like ‘%o’

select * from cliente where nome like ‘%oa%’

Acredito que os exemplos que necessitam de maiores explicações são os que utilizam a cláusula “like”. Quando utilizamos essa cláusula é comum utilizarmos o caractere % que representa “qualquer coisa”, aonde “nada” também é “qualquer coisa”. Nesse caso no primeiro exemplo queremos os registros aonde o campo nome comece com a letra ‘j’ e seja seguido por qualquer sequência de caracteres. Nesse caso os registros que contenham os valores ‘joão’ e ‘josé’ seriam retornados. No segundo caso, queremos todos os registros aonde o  campo nome termine com a letra ‘o’. No terceiro e último exemplo queremos todos os registros aonde o campo nome possua os caracteres ‘oa’ consecutivamente.

Até agora só vimos exemplos que utilizam apenas um filtro. Entretanto é possível realizar tantos filtros quanto desejarmos. Para isto basta utilizarmos as cláusulas “and” e/ou “or” para ligarmos os filtros. Vejamos o seguinte exemplo:

select * from cliente where codigo between 1 and 10

and nome like ‘j%’

select * from cliente where codigo between 1 and 10

or nome like ‘j%’

No primeiro caso estamos retornando todos os cliente que possuam o código entre 1 e 10 e cujos nomes comecem com a letra ‘j’.

No primeiro caso estamos retornando todos os cliente que possuam o código entre 1 e 10 ou cujos nomes comecem com a letra ‘j’.

Conforme os filtros vão se tornando mais complexos, se faz necessário o uso de parênteses para agrupar os filtros em unidades lógicas. Vejamos os seguintes exemplos:

select * from cliente where codigo between 1 and 10

and nome like ‘j%’

or bairro = ’centro’

and cidade = ‘fortaleza’

select * from cliente where (codigo between 1 and 10

and nome like ‘j%’)

or (bairro = ’centro’

and cidade = ‘fortaleza’)

select * from cliente where codigo between 1 and 10

and (nome like ‘j%’

or bairro = ’centro’

and cidade = ‘fortaleza’)

Perceba que com o uso de parênteses nós devemos efetuar os testes “de dentro para fora”. Isto é, devemos primeiro testar o que está dentro dos parênteses como se fossem uma unidade lógica e depois realizar o testes entre essas unidades.

19)  Ordenando registros de uma consulta

É possível ordernar uma consulta por uma ou mais colunas, crescente ou decrescentemente. Para isto basta utilizarmos a cláusula “order by” ao final da consulta, seguida dos nomes das colunas. Vejamos um exemplo:

select * from cliente

order by nome, codigo desc

No exemplo acima, estamos ordenando a consulta pelo nome do cliente de forma crescente e em seguida pelo código de forma decrescente.

20)  Limitando a quantidade de registros retornados

Para limitar não só a quantidade de registros, mas também a partir de que registros queremos a nossa consulta, basta utilizar a cláusula “limit” seguida do registro de início e da quantidade de registros, por exemplo:

select * from cliente

limit 1, 3

ou

select * from cliente

limit 3

No primeiro caso estamos dizendo que queremos 3 registros a partir do primeiro. Podemos também omitir o registro de início e especificar somente a quantidade de registros desejados.

Por enquanto é isso aí… assim que for possível estarei disponibilizando a parte 2.

Abraços!

Profiling no MySql

Olá!!! Ando cheio de assunto, mas meio sem tempo pra escrever. Entretanto resolvi dar uma pausa pra dar uma dica sobre um recurso importante de banco de dados que estava utilizando neste momento. Estou falando do “Profiling”, isto é, a capacidade de bisbilhotar o que está acontecendo por detrás dos panos, seja na execução de um programa, ou nesse caso, de um banco de dados.

O SQL Server da Microsoft possui uma ferramenta, o Profiler, que realiza muito bem essa tarefa, permitindo ao desenvolvedor monitorar o que ocorre no banco enquanto o mesmo está em uso. Eu utilizo este recurso principalmente para verificar os erros que cometo nas consultas que não resultam em exceções, isto é, erros lógicos.

Entretanto como tenho clientes que utilizam o MySql, sentia muita falta de uma ferramenta similar. Após muito pesquisar, encontrei uma forma simples de obter o mesmo resultado sem o uso de nenhuma ferramenta específica. Basta habilitar o log do MySql. É isso, simples assim. Para isso, no meu caso (MySql no Windows), basta editar o arquivo “C:\Arquivos de programas\MySQL\MySQL Server 5.0\my.ini” e inserir a linha:

log=”C:/Arquivos de programas/MySQL/MySQL Server 5.0/Data/sqllog.log”

Eu inseri essa linha logo abaixo da linha:

port=3306

O nome e o caminho do arquivo podem variar, é claro. Feito isso, basta reiniciar o serviço do MySql para que todas as consultas realizadas sejam armazenadas nesse arquivo.

Vale a pena lembrar que em um servidor de produção esse arquivo poderá crescer rapidamente, então não se esqueça de desabilitar essa opção caso não haja mais a necessidade do profiling. Para isso basta inserir o caractere # no início da linha que foi adicionada.

É isso aí, em breve espero estar de volta com um assunto muito mais interessante relacionado à performance de sistemas.