MySQL Introdução

Nesta semana veremos MySQL, ferramenta SGBD para administração e confecção de Bancos de Dados. Vamos!

Figura 1: Logomarca do MySQL

Introdução ao MySQL

MySQL é conhecido por ser de fácil utilização, e usado por empresas que trabalham com grandes volumes de dados, tais como, NASA, Bradesco, HP, Sony entre outras grandes empresas de renome. Possui uma interface extremamente simples e é compatível com grande parte dos sistemas operacionais. Podemos dizer que essas são duas das grandes características que fazem o MySQL ser tão utilizado atualmente e estar em constante crescimento. Mesmo sendo um dos bancos de dados mais utilizando em todo o mundo, MySQL continua em constante desenvolvimento, com atualizações frequentes.

Compatibilidade

MySQL é compatível com a maioria dos sistemas operacionais existentes atualmente no mercado. Pelo fato de ser desenvolvido em C e C++, isso faz com que seja extremamente fácil sua acessibilidade entre os diversos sistemas operacionais. Vejamos alguns desses sistemas.

Windows: Compatível com todas as versões

Linux: Sendo compatível com as principais versões, como Fedora, Core, Debian, SuSE e RedHat.

Unix: Sendo compatível com as versões Solaris, HP-UX, AIX, SCO.

FreeBSD.

Mac OS X Server.

Licença

O MySQL é de Código Aberto (Open-Source), desenvolvido e distribuído sob as licenças GNU/GLP (General Public Licence, ou traduzindo, Licença Publica Geral), q qual determina o que se pode ou não fazer à ferramenta e demais recursos. Além do programa, o seu código-fonte também é disponibilizado para que qualquer usuário possa edita-lo de forma que atenda suas necessidades. Os princípios básicos da licença GNU/GLP são:

Utilização: Permite que o usuário faça uso do software para qualquer finalidade.

Distribuição: Livre distribuição do software entre quaisquer pessoas.

Didática: Permite que seu funcionamento seja estudado através de seu código-fonte

Colaboração: Possibilita que seu código-fonte seja modificado para evoluir a ferramenta. Como regra seu novo código-fonte tem que permanecer sendo livre segundo essa licença.

Características

Portabilidade: Devido o MySQL ter sido desenvolvido em C e C++, tornou-se extremamente fácil a portabilidade entre os diferentes sistemas, plataformas e compiladores. Possui também módulos de interface para múltiplas linguagens, tais como Delphi, Java, Python, PHP, ASP, Ruby e entre outras linguagens mais. 

Formas de Armazenamento: O MySQL possibilita diversos tipos de tabela para o armazenamento dos dados, tendo em conta que cada tipo tem suas próprias características. Dessa maneira temos a possibilidade de escolhermos o tipo de acordo com cada situação diferente. Enquanto um tipo tem como prioridade a velocidade, outro da prioridade ao volume de dados, entre outras características.

Velocidade: Alta velocidade no acesso dos dados em razão de diversos motivos em seu desenvolvimento com tabelas ISAM, que foi substituído pelo novo sistema MyISAM na versão 5 do MySQL, além de utilização de caches em consultas, utilização de indexação BTREE para as tabelas do tipo HEAP, algoritmos de busca, entre outros recursos.

Capacidade: O MySQL possui um alto poder de execução e de armazenamento. De acordo com a plataforma em que seja usado, suas tabelas poderão armazenar grandes volumes de dados, o limite ficará por conta somente do tamanho máximo de arquivos que a plataforma que estiver sendo utilizada puder manipular. Já no caso de tabelas do tipo InooDB, onde o armazenamento pode ser realizado em um ou vários arquivos separados, fica possível armazenar volumes de dados equivalentes a TB (Terabytes) de tamanho. E referente a expressões SQL, o MySQL suporta execuções de script SQL com até 61 milhões de tabelas “joins”. E no quesito de velocidade de execução, o MySQL pode ser considerado um dos mais velozes, isso é, se não podemos dizer que é o mais veloz.

O MySQL, por ser um banco de dados poderoso, tem a capacidade de realizar bilhões de consultas em um único dia em um site e também fazer o processamento de milhões de transações por minuto.

SQL: Como já sabemos, o MySQL trabalha com a linguagem SQL (Structured Query Language, ou traduzindo, Linguagem de Consulta Estruturada), sendo extremamente rápido. E isso foi possível devido a SQL ter sido implementada no MySQL através de códigos e funções altamente customizadas pelos seus desenvolvedores. Isso gerou a grande vantagem de velocidade no processamento dos códigos SQL, porém, ao mesmo tempo trouxe um ponto negativo, sendo ele o fato de que com essa customização, nem todos os padrões das versões mais atuais do SQL tenham sido trazidos para o MySQL, porque poderiam prejudicar consideravelmente a velocidade do banco de dados. Entretanto, essa desvantagem não influencia em nada na aplicação.

Instalando o MySQL

Para instalar o banco, precisamos acessar o site oficinal do MySQL (Link: https://dev.mysql.com/downloads/file/?id=479861) e clique no botão de download, de acordo com a sua arquitetura (32 ou 64bits).
Ao executar a instalação, escolha a opção “Developer Default”, como na Figura 1.




Figura 1. Opção selecionada “Developer Default” e botão “Next”.

Após a instalação dos componentes, aparecerá a mesma tela da Figura 2 para escolher configurações. Não deixe de marcar a opção “Enable TCP/IP Networking” e colocar a porta 3306.




Figura 2. Escolha das opções demarcadas e botão “Next”.

Em seguida aparecerá a tela de inserção dos dados de login do usuário root, como mostra a Figura 3.




Figura 3. Definição de senha e botão “Next”.

Na tela seguinte é pedido para definir o nome do seu servidor. Após mais alguns “next” conclui-se a instalação do MySQL.
Podemos utilizá-lo de duas maneiras: A primeira seria utilizando o MySql 5.6 Command Line Client, que tem a mesma aparência do terminal do MS-DOS (Figura 4), ou através da ferramenta MySQL Workbench (Figura 5), que é um gerenciador do MySQL com integração ao usuário e totalmente visual.






Figura 4. Aparência do MySQL 5.6 Command Line Client.




Figura 5. Aparência do MySQL 5.6 Workbench.

Utilizando o MySQL Workbench

Para começarmos a trabalhar, vamos nos conectar a nossa DataBase usando a ferramenta gráfica. Para isso, acesse o menu “DataBase” e clique na opção “Connect to Database...”, como na Figura 6.Use as configurações padrão vistas na Figura 7.







Figura 6. Acessando o menu “Database”.




Figura 7. Configurações

Linguagem de Definição de Dados (DDL)

Ao criamos nosso banco de dados com as tabelas explicitando seus tipos de dados a cada campo, sua(s) chave(s) primaria(s) e estrangeiras, índices, regras e etc., temos para isso a criação e alteração de estruturas que definem como os dados serão armazenados. Logo, quando falamos de comando do tipo DDL estamos falando de comandos do tipo CREATE, ALTER e DROP (criar, alterar e excluir, respectivamente).

Para criar o banco de dados DBDevMedia utilizaremos a sintaxe CREATE, conforme o código a seguir:




CREATE DATABASE DBDevMedia;

Ao executá-lo teremos o mesmo resultado da Figura 8.







Figura 8. Criando o banco de dados.




Podemos complementar o nosso código com a sintaxe opcional IF NOT EXISTS, que permite ao MySQL verificar se o nome escolhido esteja sendo utilizando no servidor, evitando que retorne um erro com a possível existência de dois bancos com o mesmo nome em um mesmo servidor MySQL:




CREATE DATABASE IF NOT EXISTS DBDevMedia;

Para visualizar uma lista com todos os bancos de dados existentes no servidor, use o comando:




SHOW DATABASES;

Observe que todos os comandos em MySQL sempre termina com “;” no final. Essa sintaxe é obrigatória para que o MySQL possa entender o termino do comando.

Para remover os bancos de dados existentes no servidor, utilize o comando a seguir, mas atenção, pois uma vez executado, a ação é irreversível (Figura 9):




DROP DATABASE DBDevMedia;







Figura 9. Removendo o banco de dados.

Criando tabelas no MySQL

Dada a grande quantidade de parâmetros aceitos, a declaração CREATE TABLE é uma das maiscomplexas no MySQL.

Vamos começar selecionando o banco de dados que ganhará a nova tabela usando a sintaxe:




USE DBDevMedia;

De acordo com a documentação disponível pela Oracle, a sintaxe simplificada seria:




CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name   (create_definition, ...)

A parte de declaração que se encontra entre colchetes é opcional:

• TEMPORARY: Indica que a tabela criada será temporária, ou seja, ela expira assim que sua sessão no MySQL terminar. Use-a sempre que estiver fazendo testes.
• IF NOT EXISTS: Verifica a prévia existência da tabela e evita uma interrupção do script causada por erro. Como o MySQL é case sensitive, tabelas com nomes iguais, mas usando letras em caixa alta, como em tbl_name e Tbl_name, são consideradas tabelas totalmente diferentes.

Uma tabela é composta por uma ou mais colunas, cada qual com suas definições.

Vamos começar pela criação de uma agenda telefônica. A tabela contatos terá a seguinte estrutura da Listagem 1.

Listagem 1. Tabela Contatos




CREATE TABLE contatos (     nome VARCHAR(50) NOT NULL,     telefone VARCHAR(25) NOT NULL   );

Para verificar se a tabela foi criada use o comando (Figura 10):




SHOW TABLES;







Figura 10. Listando tabelas existentes.




Podemos melhorar um pouco mais a tabela contatos, ao acrescentar mais alguns campos, como sobrenome dos contatos, DDD, data de nascimento e e-mail. Antes de criar uma nova tabela, com o mesmo nome, vamos remover a anterior usando o comando:




DROP TABLE contatos;

Agora, vamos criar a nova tabela, conforme a Listagem 2.

Listagem 2. Tabela contatos




CREATE TABLE IF NOT EXISTS contatos (   nome VARCHAR(20) NOT NULL,   sobrenome VARCHAR(30) NOT NULL,   ddd INT(2) NOT NULL,   telefone VARCHAR(9) NOT NULL,   data_nasc DATE NULL,   email VARCHAR(30) NULL);

A chave primária é o que torna a linha ou o registro de uma tabela único. Geralmente, é utilizada uma sequência automática para a geração dessa chave para que ela não venha a se repetir. Em nosso caso, o nro_contato será único, com uma sequência numérica que identificará o registro.

A cláusula auto_increment é utilizada para incrementar automaticamente o valor da chave primária. Por padrão, essa cláusula inicia com 1. Porém, se houver a necessidade de iniciar por outro valor, podemos fazer como no exemplo a seguir:




CREATE TABLE contatos AUTO_INCREMENT=100;

ALTER TABLE

Imagine que sua tabela já contenha dados armazenados e você precisa acrescentar mais um campo (chamado Ativo) na tabela de contatos.

Conhecidamente pensaríamos em usar o drop table para excluir a tabela e recriá-la com o novo campo, mas perder os dados é algo inviável.

Nossa solução é utilizar a sintaxe ALTER TABLE, que permite alterar a estrutura da tabela existente. Por exemplo, você pode adicionar ou deletar colunas, criar ou remover índices, alterar o tipo de coluna existentes, ou renomear coluna ou tabelas. Você também pode alterar o comentário para a tabela e tipo de tabela.

Para adicionar colunas use o comando ADD, seguido do nome e dos atributos da coluna que será adicionada e, da sua posição dentro da tabela com o auxílio do parâmetro AFTER. Assim, para adicionarmos a coluna ativo, usaremos o código a seguir:




ALTER TABLE contatos   ADD ativo SMALLINT NOT NULL AFTER email;

Para ver o resultado das alterações, dê o comando:




DESCRIBE contatos;

Para alterar os atributos e nome de colunas usamos o parâmetro CHANGE, seguido da denominação da coluna a ser alterada e dos novos atributos. Para mudar os atributos da coluna nome, utilizaremos a seguinte sintaxe:




ALTER TABLE contatos   CHANGE telefone telefone CHAR(9) NOT NULL;

Vocês devem ter percebido que a palavra “telefone” foi utilizada duas vezes. Isso ocorre porque se indica primeiro a coluna e depois seus novos atributos, e o nome da coluna é um de seus atributos.

Para mudar o nome da coluna e manter seus demais atributos usamos a sintaxe a seguir:




ALTER TABLE contatos   CHANGE telefone fone VARCHAR(9) NOT NULL;

Linguagem de Manipulação de Dados (DML) e Linguagem de Transação de Dados (DTL)

Inserindo registros

Depois da tabela pronta precisamos agora de registros em nosso banco de dados. Para esse exemplo não vamos usar nenhuma aplicação para inserir esses dados, mas sim diretamente pelo SGBD através de comando SQL.

Vamos fazer o primeiro INSERT na tabela contatos com o comando INSERT INTO contatos. Entre parênteses informaremos em quais colunas queremos inserir os registros e depois devemos informar qual o valor para cada coluna, como mostra a Listagem 3.

Listagem 3. Inserindo dados




INSERT INTO contatos (nome    ,sobrenome    ,ddd    ,telefone    ,data_nasc    ,email    ,ativo)   VALUES(‘Bruno’    ,‘Santos’    ,11    ,999999999    ,‘2015-08-22’    ,‘contato@dominio.com.br’    ,1);

Se você quiser inserir em todos os campos da tabela, não é necessário descrever quais serão populados. Apenas não se esqueça de conferir se os valores estão na sequência correta, como na Listagem 4, onde omitimos estes campos. O SGBD subentende que todos os campos serão populados.

Listagem 4. Inserindo dados sem descrever




INSERT INTO contatos VALUES(‘Bruno’    ,‘Santos’    ,11    ,999999999    ,‘2015-08-22’    ,‘contato@dominio.com.br’    ,1);

Observe que em nenhum momento foi mencionado o campo nro_contato ou acrescentado um valor diretamente, isso por que este campo foi definido como auto_increment, desta forma, o campo recebe o valor automaticamente.

Alterando registros

Para alterar os registros usamos o comando UPDATE.

No exemplo anterior inserimos um sobrenome errado. Para corrigir usamos a sintaxe da Listagem 5.

Listagem 5. Alterando dados




UPDATE contatos SET    sobrenome= ‘Nascimento’ WHERE  nro_contato= 100;   commit;

Podemos atualizar mais de um campo de uma vez só, separando com “,”, como mostra a Listagem 6.

Listagem 6. Alterando mais de um dado




UPDATE contatos SET    sobrenome= ‘Nascimento’    , ddd= 015      ,  telefone= ‘0123456789’   WHERE   nro_contato = 100   commit;

Perceba que, além do UPDATE utilizamos o SET para informar qual campo que queremos alterar. O WHERE indica a condição para fazer a alteração e, em seguida, o commit diz ao SGBD que ele pode realmente salvar a alteração do registro. Se, por engano, fizermos o UPDATE incorreto, antes do commit podemos reverter a situação usando a instrução SQL rollback, da seguinte e maneira:




UPDATE contatos SET    sobrenome= ‘Nascimento’ WHERE   nro_contato= 100;   rollback;

Com isso, o nosso SGBD vai reverter a última instrução. Porém, se tiver a intenção de utilizar o rollback, faça-o antes de aplicar o commit, pois se você aplicar o UPDATE ou qualquer outro comando que necessite do commit, não será possível reverter.

As instruções commit e rollback são tratadas pela Linguagem de Transação de Dados (DTL).

Excluindo registros

Para deletar algum registro usamos a instrução SQL DELETE. Diferente do DROP, ele deleta os registros das colunas do banco de dados.

O DROP é usado para excluir objetos do banco, como tabelas, colunas, views e procedures, enquanto, o delete deletará os registros das tabelas, podendo excluir apenas uma linha ou todos os registros. Desta maneira, vamos apagar o primeiro registro da tabela contatos usando o seguinte comando:




DELETE FROM contatos WHERE  nro_contato= 100;   commit;

Para deletar todos os registros da tabela de clientes usamos o comando:




DELETE FROM contatos;   commit;

Observe que, ao empregar o DELETE você também deve usar o commit logo após a instrução. Da mesma maneira, podemos também utilizar o rollback para não efetivar uma exclusão de dados incorretos.

Além do DELETE, podemos eliminar os dados usando a instrução SQL TRUNCATE, que não necessita de commit. Nem o rollback pode reverter à operação.

Isso ocorre porque, quando você utiliza o DELETE, o SGBD salva os seus dados em uma tabela temporária e, quando aplicamos o rollback, ele a consulta e restaura os dados. Já o TRUNCATE não a utiliza, o SGBD faz a eliminação direta. Para usar esse comando utilizar a sintaxe a seguir:




TRUNCATE TABLE contatos;

Essa instrução não pode ser usada dentro da cláusula WHERE.

Linguagem de Consulta de Dados (DQL)

O objetivo de armazenar registros em um banco de dados é a possibilidade de recuperar e utilizá-los em relatórios para análises mais profundas. Essa recuperação é feita através de consultas.

O comando SQL utilizado para fazer consultas é o SELECT. Selecionando os dados, devemos dizer ao SGBD de onde queremos selecionar, através do comando FROM.

Como exemplo, vamos selecionar os registros da tabela de contato (Figura 11). Quando não queremos selecionar um ou vários campos específicos, utilizamos o asterisco (*):




SELECT * FROM contatos;







Figura 11. Consultando contatos.




Se quisermos selecionar os registros dos campos nome e sobrenome (Figura 12), usamos a sintaxe:




SELECT nome, sobrenome FROM contatos;







Figura 12. Consultando o nome e o sobrenome da tabela contatos.




Ainda podem surgir situações que necessitem selecionar apenas um registro. Neste caso, utilizamos o WHERE

Vamos selecionar o cliente com uma cláusula que deve ter nro_contato= 101:




SELECT nome, sobrenome   FROM contatos   WHERE nro_contato= 100;

Para colunas do tipo texto será necessário colocar o valor entre aspas simples, assim dizemos ao SGBD que estamos querendo comparar o valor com uma coluna do tipo texto:




SELECT nome, sobrenome FROM contatos WHERE nome= ‘Bruno’;

E se quiséssemos todos os clientes que sejam diferentes de ‘100’? Faríamos uma consulta utilizando o operador do MySQL diferente <> (Figura 13):




SELECT nome, sobrenome   FROM contatos   WHERE nro_contato<> 100;







Figura 13. Utilizando a clausula “WHERE nro_contato <> 100”.




Além dos operadores de comparação = e <>, temos os seguintes operadores:

• >: maior;
• <: menor;
• >=: maior e igual;
• <=: menor e igual.

A clausula DISTINCT retorna apenas uma linha de dados para todo o grupo de linhas que tenha o mesmo valor. Por exemplo, executando a consulta a seguir:




SELECT DISTINCT sobrenome FROM contatos;

Os valores retornados são apenas três, pois Santos se repete duas vezes:

Santos Carvalho Silva
Já a clausula ALL é o oposto de DISTINCT, pois retorna todos os dados. Observe a consulta a seguir:



SELECT ALL sobrenome FROM contatos;

Repare que o resultado a seguir apresenta o sobrenome Santos duas vezes:



Santos  Carvalho  Santos  Silva

A clausula ORDER BY retorna os comandos em ordem ascendente (ASC) ou descendente (DESC), sendo o padrão ascendente. Vejamos um exemplo:



SELECT nome FROM contatos ORDER BY nome DESC;

Repare que os nomes são retornados em ordem descrescente



Isabelle  Elaine  Cauã  Bruno

A clausula LIMIT [inicio,] linhas retorna o número de linhas especificado. Se o valor inicio for fornecido, aquelas linhas são puladas antes do dado ser retornado. Lembre-se que a primeira linha é 0.



SELECT * FROM contatos LIMIT 3,1;

O resultado da consulta será:



103 Isabelle Silva 11 999999999 2013-11-20 contato@rh.com.br

Para incrementar as consultas podemos usar algumas funções. A seguir apresentaremos as mais comuns:
• A função ABS retorna o valor absoluto do número, ou seja, só considera a parte numérica, não se importando com o sinal de positivo ou negativo do mesmo. Por exemplo: ABS(-145) retorna 145;
• A função BIN considera o binário de número decimal. Por exemplo: BIN(8) retorna 1000;
• A função CURDATE() / CURRENTDATE() retorna a data atual na forma YYYY/MM/DD. Por exemplo: CURDATE() retorna 2002/04/04;
• A função CURTIME() / CURRENTTIME() retorna a hora atual na forma HH:MM:SS. Por exemplo: CURTIME() retorna 13:02:43;
• A função DATABASE retorna o ome do banco de dados atual: Por exemplo: DATABASE() retorna DBDevMedia;
• A função DAYOFMONTH retorna o dia do mês para a data dada, na faixa de 1 a 31. Por exemplo: DAYOFMONTH('2004-04-04') retorna 04;
• A função DAYNAME retorna o dia da semana para a data dada. Por exemplo: DAYNAME('2004-04-04') retorna Sunday;
• A função DAYOFWEEK retorna o dia da semana em número para a data dada, na faixa de 1 a 7, onde o 1 é domingo. Por exemplo: DAYOFWEEK('2004-04-04') retorna 1;
• A função DAYOFYEAR retorna o dia do ano para a data dada, na faixa de 1 até 366. Por exemplo: DAYOFYEAR('2004-04-04') retorna 95;
• A função FORMAT(NÚMERO, DECIMAIS) formata o número nitidamente com o número de decimais dado. Por exemplo: FORMAT(5543.00245,2) retorna 5.543.002,45
A função LIKE merece um destaque especial, pois faz uma busca sofisticada por uma substring dentro de uma string informada. Temos, dentro da função LIKE, os seguintes caracteres especiais utilizados em substrings:
• %: busca zero ou mais caracteres;
• _: busca somente um caractere.
Vamos a alguns exemplos:



SELECT nome From contatos Where nome like ‘B%’;

O caractere ‘%’ nessa consulta indica que estamos procurando nomes que possuem a inicial B, ou seja, com base na nossa tabela contatos, o retorno será apenas Bruno.



SELECT nome From contato Where nome like ‘_a%’;

O caractere ‘_’ na consulta indica que estamos procurando nomes nos quais a letra A é a segunda letra do nome, ou seja, o retorno será apenas Cauã.



SELECT nome From contato Where nome like ‘%o’;

A consulta buscou nomes em que a última letra é o caractere ‘O’, ou seja, teremos como retorno apenas Bruno.
Outra função importante para retorno de consultas é Left, que retorna os primeiros caracteres à esquerda de uma string. Sua sintaxe é apresentada a seguir:



LEFT(string,tamanho)

A consulta a seguir retornará os três primeiros caracteres à esquerda dos registros da coluna nome:



SELECT LEFT(nome,3) from contatos

O resultado será:



Bru  Ela  Cau  Isa

A função Right é semelhante a função Left, mas esta retorna os últimos caracteres à direita de uma string. Sua sintaxe também é semelhante:



RIGHT(string1,tamanho)

Repare que na consulta a seguir são retornados os quatro últimos caracteres à direita dos nomes da tabela contatos:



SELECT RIGHT(nome,4) From contatos;

O resultado será:



runo  aine  Cauã  ele







PRONTO! PRONTO!


Fontes:
devmedia, forumMySQL, MySqlCore