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!