Книга: MySQL. Comece com o principal banco de dados open source do mercado
Casa do Código
Prefácio
Escrevo este livro pensando nas pessoas que estão começando a estudar banco
de dados, a trabalhar com ele, e queiram algo bem prático para iniciar; e para os desenvolvedores que necessitam de uma ajuda no dia a dia, que queiram
migrar suas aplicações para o MySQL, ou se aperfeiçoar utilizando, na prá-
tica, os benefícios deste poderoso Sistema Gerenciador de Banco de Dados
(SGBD).
i
Casa do Código
Agradecimentos
Ao lado de um grande homem existe uma grande mulher. E ao meu lado,
desde a época em que estava estudando para ingressar na universidade, te-
nho uma mulher fantástica. Minha musa inspiradora, que sempre apoiou
e incentivou minha carreira profissional e minha busca por conhecimento.
Muito obrigado.
Gostaria também de agradecer a minha família, principalmente minha
mãe. Mesmo não entendendo muito o que eu faço, ela sempre apoiou minhas
decisões e me ajudou no que estava ao seu alcance.
A todas as pessoas com que tive a experiência de trabalhar, aprender, ensi-
nar e conviver. Saiba que tentei absorver o melhor de cada uma e todas foram
importantes à sua maneira.
Também, a todos os professores que tive em minha vida, pois, sem eles,
nada de que já conquistei profissionalmente seria possível. Sempre me mos-
traram o caminho para que eu conseguisse buscar conhecimento.
Por último, não poderia deixar de agradecer à Casa do Código, que me
proporcionou esta maravilhosa experiência de escrever este livro. Muito obri-
gado!
iii
Casa do Código
Sobre o autor
Vinicius Carvalho teve seu primeiro contato com o computador em um curso
de MS-DOS com Windows 95 e, desde então, apaixonou-se pela computação.
Ao longo da adolescência, procurou aperfeiçoar-se e fazer alguns cursos até
chegar a hora de escolher sua formação na faculdade. Essa parte foi fácil!
Formou-se em Sistemas de Informações, pós-graduou-se em Engenharia de
Software e não parou de aprender coisas novas.
Apaixonado pela busca pelo conhecimento, procura manter-se atualizado
nas tendências de desenvolvimento de software, tecnologia e tem como meta
aprender algo novo todos os dias.
Na sua carreira profissional, teve oportunidades de trabalhar como ana-
lista de suporte, desenvolvedor, gerente de projetos, consultor e como um em-
preendedor incansável, arriscando-se a ter seu próprio negócio. Atualmente
é analista de sistemas sênior do maior grupo de ensino do mundo, a Kroton,
além de também fazer algumas consultorias na área de desenvolvimento de
software e participar de grupos de discussão sobre empreendedorismo em sua
cidade.
Teve chance de palestrar em congresso de software livre como o VOL
DAY, evento criado pela comunidade Viva o Linux; publicar artigos em diver-
sos congressos no Brasil; e ministrar aulas de graduação no Centro Universi-
tário Filadélfia (UniFil), faculdade que é referência em cursos de graduação e pós-graduação no Paraná, na qual se formou.
Sua página pessoal é http://www.viniciuscdes.net. Lá você pode conferir
seu currículo e o link para seu blog, que aborda diversos temas, como: tecno-
logia, computação, produtividade, informação, entre outros.
v
Casa do Código
Sumário
Sumário
1
Introdução
1
1.1
Sobre o MySQL
. . . . . . . . . . . . . . . . . . . . . . . . . .
2
1.2
Banco de dados
. . . . . . . . . . . . . . . . . . . . . . . . . .
3
1.3
Começando a utilizar o MySQL . . . . . . . . . . . . . . . . .
6
2
Iniciando o projeto
15
2.1
Criando nosso primeiro banco de dados . . . . . . . . . . . .
15
2.2
Criando e manipulando usuários . . . . . . . . . . . . . . . .
17
2.3
Criando nosso banco . . . . . . . . . . . . . . . . . . . . . . .
19
2.4
Requisitos para o projeto . . . . . . . . . . . . . . . . . . . . .
21
2.5
(Minhas) Boas maneiras . . . . . . . . . . . . . . . . . . . . .
22
2.6
Tipos de dados . . . . . . . . . . . . . . . . . . . . . . . . . . .
25
2.7
Modelando o projeto . . . . . . . . . . . . . . . . . . . . . . .
28
3
Mão na massa: criando nossos códigos
33
3.1
Criando as tabelas do projeto
. . . . . . . . . . . . . . . . . .
34
3.2
Cuidando da integridade do banco de dados . . . . . . . . . .
38
3.3
Alterando as tabelas . . . . . . . . . . . . . . . . . . . . . . . .
39
3.4
Excluindo (dropando) as tabelas . . . . . . . . . . . . . . . . .
41
4
Manipulando registros
43
4.1
Inserindo registros . . . . . . . . . . . . . . . . . . . . . . . . .
43
4.2
Alterando registros
. . . . . . . . . . . . . . . . . . . . . . . .
45
4.3
Excluindo registros . . . . . . . . . . . . . . . . . . . . . . . .
46
vii
Sumário
Casa do Código
5
Temos registros: vamos consultar?
49
5.1
Estrutura básica das consultas . . . . . . . . . . . . . . . . . .
50
5.2
Subquery ou subconsulta . . . . . . . . . . . . . . . . . . . . .
55
5.3
Traga informação de várias tabelas com Joins . . . . . . . . .
62
5.4
Select em: create table, insert, update e delete . . . . . . . . .
64
6
Consultas com funções
71
6.1
Funções . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
71
6.2
Funções de agregação . . . . . . . . . . . . . . . . . . . . . . .
72
6.3
Funções de string . . . . . . . . . . . . . . . . . . . . . . . . .
77
6.4
Funções de cálculos e operadores aritméticos . . . . . . . . .
82
6.5
Operadores aritméticos . . . . . . . . . . . . . . . . . . . . . .
86
6.6
Funções de data . . . . . . . . . . . . . . . . . . . . . . . . . .
88
7
Deixar o banco processar: procedures e functions
93
7.1
Deixando o banco processar com stored procedures . . . . .
94
7.2
Processando e retornando com functions
. . . . . . . . . . .
99
7.3
Tabela dual . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
101
7.4
Automatizando o processo através de event scheduler . . . .
102
8
Criando gatilhos
107
8.1
Triggers nas rotinas . . . . . . . . . . . . . . . . . . . . . . . .
107
8.2
Triggers before insert e before update . . . . . . . . . . . . . .
108
8.3
Triggers after insert e after update . . . . . . . . . . . . . . . .
111
8.4
Triggers before delete e after delete . . . . . . . . . . . . . . .
113
8.5
Status das triggers . . . . . . . . . . . . . . . . . . . . . . . . .
116
9
Obtendo performance e criando visões
117
9.1
Ganhando performance com índices . . . . . . . . . . . . . .
117
9.2
Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
122
9.3
Criando Views . . . . . . . . . . . . . . . . . . . . . . . . . . .
123
viii
Casa do Código
Sumário
10 Criando, exportando e importando backups: ele poderá te salvar
um dia
129
10.1
Segurança dos seus dados . . . . . . . . . . . . . . . . . . . . .
129
10.2 Criando backups . . . . . . . . . . . . . . . . . . . . . . . . . .
131
10.3 Importando backups
. . . . . . . . . . . . . . . . . . . . . . .
132
11 MySQL avançado
135
11.1
Variáveis de sistema . . . . . . . . . . . . . . . . . . . . . . . .
136
11.2
Visualizando as conexões ativas . . . . . . . . . . . . . . . . .
137
11.3
Exportar e importar consultas para arquivos .csv e .txt . . . .
138
11.4
Localizar uma coluna no seu banco . . . . . . . . . . . . . . .
140
11.5
Ferramentas para MySQL . . . . . . . . . . . . . . . . . . . . .
142
12 Guia de consulta rápida
145
12.1
O guia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
145
12.2
Comandos ddl e dml . . . . . . . . . . . . . . . . . . . . . . .
146
12.3
Tipos de dados . . . . . . . . . . . . . . . . . . . . . . . . . . .
147
12.4 Consultas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
148
12.5
Programando rotinas . . . . . . . . . . . . . . . . . . . . . . .
152
12.6 Desempenho . . . . . . . . . . . . . . . . . . . . . . . . . . . .
154
12.7
Manutenção do banco . . . . . . . . . . . . . . . . . . . . . . .
154
13 Conclusão
157
13.1
O guia . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
157
ix
Capítulo 1
Introdução
“Para mim, o computador é a mais extraordinária ferramenta que já tivemos.
É o equivalente à bicicleta para nossa mente.”
– Steve Jobs
Quando vamos iniciar o desenvolvimento de um novo projeto, um grande
ponto de interrogação surge em nossa cabeça a respeito de qual tecnologia
utilizar. Algo que deve ser levado em consideração é o seu orçamento. Esco-
lher ferramentas livres de taxas, de qualidade e que o suportem é de grande
importância.
Ao escolher o MySQL como opção de Sistema Gerenciador de Banco de
Dados (SGBD), além de uma ferramenta gratuita criada na base da licença de
software livre, você também está optando por qualidade, robustez e segurança.
Estes são adjetivos que um gerenciador deve ter, pois guardar seus dados ou
de seus clientes com segurança é o mais importante.
1.1. Sobre o MySQL
Casa do Código
Além das ferramentas, você também deve preocupar-se com o planeja-
mento do projeto que está desenvolvendo. A modelagem e a construção do
banco de dados de um sistema é o coração dele. O banco de dados vai impac-
tar o processo inteiro: desde o início da criação do projeto, o desempenho do
sistema durante seu desenvolvimento e até sua manutenção e expansão pos-
teriormente. Por isso, volto a frisar a importância de uma boa modelagem e
de um bom gerenciador.
É isto que farei ao decorrer deste livro: apresentar a modelagem do pro-
jeto, boas práticas, tudo isso de uma forma bem prática, para que, no final,
você seja capaz de criar um banco de dados relacional para qualquer aplica-
ção ou sistemas comerciais que deseja desenvolver.
1.1
Sobre o MySQL
Quando você digita ‘MySQL’ no Google, o primeiro resultado mostra que ele
é o banco de dados open source mais popular do mundo. Preciso dizer mais
alguma coisa? As maiores empresas de tecnologia utilizam e muitas delas
contribuem para o projeto. Em vez de escrever algo teórico ou histórico para
explicar o que é o MySQL e elencar suas qualidades, eu escolhi criar um mapa
mental para listar suas vantagens de forma clara e visual. Você pode acessar o link http://www.mysql.com/why-mysql/topreasons.html para ler um pouco
mais sobre essas características e, no demais, deixo a parte histórica como
dever de casa para você pesquisar.
2
Casa do Código
Capítulo 1. Introdução
Fig. 1.1: Características do MySQL
1.2
Banco de dados
Apesar de banco de dados ser um termo técnico, a maioria das pessoas nos dias de hoje tem contato direto com ele. De fato, grande parte da população
atualmente tem acesso a equipamentos, cuja função (principal ou secundária)
é o armazenamento de informações. Quem, hoje em dia, não usa um telefone
celular?
Desde o seu surgimento, esse tipo de aparelho possui uma agenda, na qual
podemos gravar nomes e telefones para, em um segundo momento, acessá-
los. Uma lista telefônica impressa também é um exemplo válido disso, pois
nela são relatados todos os nomes, endereços e números de telefone das em-
presas e dos moradores da sua cidade e, eventualmente, dos arredores.
Tudo isso remete ao conceito de banco de dados, ou seja, um local no qual
é possível armazenar informações para consulta ou utilização, quando ne-
3
1.2. Banco de dados
Casa do Código
cessário. O próprio banco vai gerenciar a estrutura dos registros e se encarregará de criar espaço para novos registros, alterando seu conteúdo de acordo
com as solicitações da aplicação que o está acessando.
Esses bancos de dados que gerenciam os registros de forma automatizada,
além de serem apenas um conjuntos de dados, são chamados Sistemas Geren-
ciadores de Banco de Dados Relacionais (SGBDR), ou Relational Database
Management Systems (RDMS).
Há diversas razões para o modelo de banco de dados relacional ser o mais
utilizado entre outros modelos existentes. Uma delas é a facilidade da alte-
ração da estrutura das tabelas, como adicionar e excluir colunas e linhas de
acordo com as necessidades, sem comprometer sua funcionalidade.
Introdução ao banco de dados relacional
Independentemente do aplicativo que se deseja usar para o armazena-
mento e manipulação das informações, todos os bancos de dados são consti-
tuídos por elementos básicos: campos, colunas, linhas ou tuplas e tabelas.
Campos são os espaços reservados para inserção de um determinado dado;
as colunas são os registros de um determinado campo; as tuplas são as linhas
de registros de um conjunto de campos; e as tabelas são os conjuntos de li-
nhas, campos e colunas. Para visualizar melhor, se tivéssemos uma tabela de
clientes em nosso banco, seria da seguinte maneira:
4
Casa do Código
Capítulo 1. Introdução
Fig. 1.2: Composição de uma tabela
Cada banco é um conjunto de tabelas relacionadas. Também são chama-
dos de relações, daí o nome banco de dados relacional. Cada tabela é uma representação física de uma entidade ou objeto que está em um formato ta-bular, como vimos anteriormente na figura 1.2.
Como todos os bancos de dados, o relacional também tem sua estrutura
baseada em registros relacionados e organizados em tabelas. Essas relações
tornam os registros integrados. Esse relacionamento é possível através das
chaves: primária (primary key PK), estrangeira (foreign key FK) e da chave
candidata ou alternativa, que vou explicar mais à frente.
Introdução à linguagem SQL
SQL significa Structured Query Language e é a linguagem padrão utilizada
pelos banco de dados relacionais. Os principais motivos disso resultam de
sua simplicidade e facilidade de uso. Mais uma vez não entrarei no mérito
histórico; mas algo relevante que você precisa conhecer são suas categorias de comandos. Alguns autores divergem entre exatamente quais são. Eu separei
3. Você pode encontrar ao pesquisar que alguns comandos citados por mim
em uma categoria talvez estejam em outra, em um estudo diferente. Elas são:
5
1.3. Começando a utilizar o MySQL
Casa do Código
• DML Linguagem de Manipulação de Dados: esses comandos indi-
cam uma ação para o SGBD executar. Utilizados para recuperar, in-
serir e modificar um registro no banco de dados. Seus comandos são:
INSERT, DELETE, UPDATE, SELECT e LOCK;
• DDL Linguagem de Definição de Dados: comandos DDL são respon-
sáveis pela criação, alteração e exclusão dos objetos no banco de dados.
São eles: CREATE TABLE, CREATE INDEX, ALTER TABLE, DROP
TABLE, DROP VIEW e DROP INDEX;
• DCL Linguagem de Controle de Dados: responsável pelo controle de
acesso dos usuários, controlando as sessões e transações do SGBD. Al-
guns de seus comandos são: COMMIT, ROLLBACK, GRANT e REVOKE.
Cada um dos comandos aqui citados será explicado ao longo do livro
e aplicado em nosso projeto!
1.3
Começando a utilizar o MySQL
Neste livro, utilizaremos a versão MySQL Commuty Server 5.6. Atualmente,
há versões para download para 9 plataformas do Linux, Windows e Mac OS.
Todos poderão ser feitos no site http://dev.mysql.com/downloads/mysql/. Es-
colha sua plataforma e o tipo do seu sistema operacional (32 ou 64 bit).
Instalação e configuração no Windows
Para o Windows, há possibilidade de baixar a versão Windows(x86,
640bit), MySQL Installer MSI e será este que vou instalar, pois ele fornece um assistente de instalação que facilita bastante, além de downloads extras
que poderão ser úteis. Se você estiver começando agora no mundo de MySQL,
esta opção será bastante proveitosa. Você precisará de conexão com a internet
durante a instalação.
Depois de ter feito o download, execute o arquivo. Logo em seguida, apa-
recerá uma tela para você aceitar os termos de utilização do MySQL, na qual
6
Casa do Código
Capítulo 1. Introdução
você deve clicar na caixa I accept the license terms e no botão Next para avançar para a próxima tela.
Como fizemos o download desse tipo de instalação, podemos escolher o
que queremos instalar na tela a seguir.
Fig. 1.3: Instalação: versão de instalação
As opções são:
• Developer Default: tudo o que precisamos para iniciar a trabalhar com o MySQL. Além do servidor, ao escolher essa opção, podemos também
instalar o MySQL Workbench, que é uma IDE (Integrated Development
Environment) para o banco de dados para trabalhar com SQL, como
também possibilitar a criação de entidade e relacionamento, como ve-
remos mais à frente;
• Server Only: instala apenas o servidor do MySQL. Prefira esta opção
7
1.3. Começando a utilizar o MySQL
Casa do Código
quando você for colocar o banco de dados em uma rede. Assim, este
servidor deverá ser acessado através da versão Client;
• Client Only: instala a versão que você deve implantar nas máquinas
que vão acessar o servidor na rede. Com esta versão, você não conse-
guirá criar um banco de dados, apenas acessar algum existente;
• Full: instala todos os produtos que estiverem disponíveis. Além de instalar o Server e Client, também instalará as bibliotecas necessárias para
conexão de algumas linguagens de programação, como, por exemplo,
as bibliotecas para acessar o MySQL utilizando o Java;
• Custom: com esta opção, você poderá escolher manualmente quais
produtos quer instalar. Se você já estiver familiarizado com essas fer-
ramentas, poderá escolher aquelas que realmente vai utilizar.
Vamos escolher a primeira opção: Developer Default, pois nosso intuito é
desenvolver e utilizar o banco da mesma máquina, neste primeiro momento.
A próxima tela mostrará quais produtos serão instalados.
8
Casa do Código
Capítulo 1. Introdução
Fig. 1.4: Instalação: produtos a serem instalados
Estou instalando a versão 5.6. Pode ser que, quando você for instalar,
outros produtos estejam disponíveis. Agora, é só clicar em Execute.
Na tela seguinte, inicia-se a configuração do seu gerenciador. Em Type
and Networking, você deve dizer em qual máquina você está instalando: em
uma máquina de desenvolvimento ou em uma que será 100% dedicada ao ge-
renciador de banco de dados. Escolheremos a primeira opção, Development
Machine (máquina de desenvolvimento).
Nesta mesma tela, devemos configurar qual será o tipo de conexão e porta
utilizada. Normalmente, estará selecionado, TCP/IP e Port Number com 3306.
Vamos deixar nesta mesma porta, a não ser que você tenha alguma aplicação
já a utilizando. Se estiver tudo bem, clicamos em Next. A tela estará da se-
guinte maneira:
9
1.3. Começando a utilizar o MySQL
Casa do Código
Fig. 1.5: Instalação: tipo de máquina e rede
Na sequência, temos a configuração da senha padrão para o usuário
root, que é o usuário principal do gerenciador. Nos campos MySQL Root
Password e Repeat Password, vamos colocar como senha cursomysql. Você
pode escolher a senha que desejar, eu estou padronizando esta que será a
mesma para todo o projeto. Você também pode criar outros usuários para
acessar seu banco de dados, porém, nesta etapa, ficaremos apenas com o usuá-
rio root. A criação de usuários e permissões serão apresentadas no capítulo
2. Com isso, a tela estaria da seguinte maneira:
10
Casa do Código
Capítulo 1. Introdução
Fig. 1.6: Instalação: usuário root
Agora, na última tela de configuração, deixamos as configurações pa-
drões, clicamos em Next e vamos para a próxima, que mostrará os passos a
serem executados pelo assistente de instalação. Clicando em Execute, as ações
necessárias serão feitas e seu gerenciador de banco de dados será instalado.
Instalação e configuração no Linux (Ubuntu)
Para a instalação no Linux, utilizaremos o Ubuntu. Você pode optar por
baixar a versão para esse sistema no site do MySQL supracitado ou direta-
mente no seu gerenciador de pacotes. Esta é a forma mais simples de instalar.
Atualize o gerenciador de pacotes com:
$> sudo apt-get update
Após atualizado, podemos baixar e instalar o mysql server:
$> sudo apt-get install mysql-server
11
1.3. Começando a utilizar o MySQL
Casa do Código
Ao finalizar a instalação, para você abrir o MySQL e começar a criar suas
tabela, digite:
$> sudo mysql -u root -p
Mais à frente, explicarei como configurar uma senha para o usuário root.
Instalação e configuração no Mac-OS
Para a instalação no Mac, escolhi o pacote DMG, mas fique à vontade
para escolher outra versão. Eu estou usando o Mac OS X 10.9. Agora, com o
download feito, vamos à instalação.
Nessa versão que baixei, devemos montar em forma de disco. Este pacote
terá os arquivos que utilizaremos na instalação. Primeiro, vamos instalar o
arquivo, mysql-5.6.22-osx10.9-x86_64.pkg. Ao clicar nele, poderá
surgir uma mensagem de erro, dizendo que você não tem permissão para
instalar programas de desenvolvedores desconhecidos, como mostra a figura
1.7.
Fig. 1.7: Permissão para instalar software de desenvolvedores desconhecidos
Para resolver este problema, vá até System Preferences > Security & Pri-vacy. Quando abrir uma janela, clique no cadeado que se encontra no canto
esquerdo inferior para desbloquear, e marque a opção Anywhere da lista que
12
Casa do Código
Capítulo 1. Introdução
diz Allow apps downloaded from. Em seguida, abrirá um pop-up para você
confirmar a permissão. Desta vez, clique no botão Allow From Anywhere,
como mostra a imagem 1.8.
Fig. 1.8: Configuração e segurança
Feito isso uma vez, você conseguirá instalar qualquer software baixado da
internet. Feche a janela, volte até o nosso arquivo de instalação e execute-o. A instalação é bem simples, siga as instruções e aceite os termos de licença que aparecerem nas janelas, até receber a mensagem de conclusão.
Junto ao nosso pacote de instalação, há o arquivo MySQL.prefPane.
Ele vai instalar um painel de configurações para auxiliar o início e/ou a finalização do serviço do MySQL Server e também a configurar sua inicialização
automática. Dois cliques no arquivo e a tela, como vemos na figura 1.9, abrirá.
13
1.3. Começando a utilizar o MySQL
Casa do Código
Fig. 1.9: Painel de configurações do MySQL
Clique no botão Install para iniciar a instalação. Após a conclusão, vai
aparecer um novo icone do MySQL em System Preferences. Clique nele e, em
seguida, em Start MySQL Server. Pronto, o serviço do seu servidor MySQL já
esta rodando e está pronto para você começar a trabalhar! Para abri-lo, vá até o terminal e digite o seguinte comando:
$ /usr/local/mysql/bin/mysql -u root -h localhost -p
Se aparecer a mensagem Enter Password, apenas tecle enter e a mensagem
de boas-vindas surgirá. No capítulo 2, como falei anteriormente na instalação
da versão Linux, explicarei como adicionar uma senha para o usuário root.
Daqui para frente, tanto no Linux, Windows e MacOS, os comandos serão
os mesmos. Durante o projeto, eu utilizarei o Windows, mas não se preocupe.
Eu mostrarei como proceder nos outros sistemas operacionais para caso sur-
gir algo diferente.
Concluímos a primeira missão: a instalação! Já poderíamos criar um
banco de dados e as tabelas. Porém, antes, precisamos conhecer alguns outros
conceitos e especificar um pouco mais o projeto que vamos desenvolver. Até
o próximo capítulo.
14
Capítulo 2
Iniciando o projeto
“Faça as coisas o mais simples que você puder, porém não as mais simples.”
– Albert Einstein
2.1
Criando nosso primeiro banco de dados
Fomos contratados por algum cliente para desenvolver um sistema para ven-
das. No 3 descreverei mais detalhes sobre o projeto. Como usaremos o mesmo
exemplo em todo o livro, também utilizaremos um único banco de dados.
Quando instalamos e configuramos o MySQL, nós criamos o usuário
root e configuramos uma senha. Com eles, nós podemos criar nosso banco
de dados. Para acessar o MySQL, utilizaremos o console que se instala junto
à instalação que fizemos. No Windows, ele fica na mesma estrutura de pas-
tas do MySQL e se chama MySQL 5.6 Command Line Client, na versão
2.1. Criando nosso primeiro banco de dados
Casa do Código
que estou utilizando.
Ao clicar, abrirá uma tela preta, igual à tela do prompt do Windows. Ela
solicitará sua senha, aquela configurada na instalação, como mostra a figura
2.1.
Fig. 2.1: Console do MySQL
Ao realizar o login com sucesso, deverá aparecer a mensagem de boas-
vindas do MySQL e o cursor do mouse estacionado sobre a linha mysql>,
aguardando seus comandos.
Fig. 2.2: Login no prompt
16
Casa do Código
Capítulo 2. Iniciando o projeto
2.2
Criando e manipulando usuários
Por padrão, temos o usuário root para acessar o MySQL e trabalharmos.
Podemos continuar utilizando-o ou podemos criar um novo. Como as boas
práticas de desenvolvimento de software aconselham a criação de usuários
diferentes do root para acessarmos os bancos de dados e para utilização por
aplicações, seguiremos esse conselho. Além disso, elas também orientam a
criação de, no mínimo, três bancos de dados para um projeto: um de desen-
volvimento, um para testes e um outro para produção. Entretanto, em nosso
projeto, vamos criar apenas um banco e um novo usuário para acessá-lo.
O nosso novo usuário terá o nome de usermysql e sua senha será
cursomysql. Vamos utilizar o comando create user, da seguinte ma-
neira:
mysql> create user usermysql@'%' identified by 'cursomysql';
Quando utilizamos o
% em nosso código, estamos dizendo que este
usuário poderá acessar o nosso banco a partir de qualquer host. Poderíamos
ter limitado ao acesso do local apenas, substituindo o % por localhost.
Ele já está criado, porém não tem nenhuma permissão. Como não precisa-
mos limitá-lo, vamos conceder direito total a ele. Faremos isso com o seguinte comando:
mysql> grant all privileges on *.* to usermysql@'%'
with grant option;
Utilizamos grant para conceder o acesso de usuários. Porém, se quisés-
semos revogá-lo, faríamos da seguinte maneira:
mysql> revoke all on *.* from usermysql;
Controle de acesso
Quando trabalhamos com desenvolvimento de software, poderá surgir a
necessidade de dar acesso a alguma pessoa ou aplicação, no banco de dados.
Para não liberar um acesso completo, você utiliza os direitos de usuário para
fazer esta limitação.
17
2.2. Criando e manipulando usuários
Casa do Código
Os comandos grant e revoke permitem os administradores do sistema
criar usuários e conceder e revogar direitos aos usuários do MySQL em seis
níveis de privilégios:
• Nível global: privilégios globais aplicam-se para todos os bancos de
dados em um determinado servidor. São concedidos e revogados por
meio dos comandos a seguir, que concederão e revogarão apenas pri-
vilégios globais, respectivamente:
mysql> grant all on *.* to usermysql@localhost;
mysql> revoke all on *.* from usermysql;
• Nível dos bancos de dados: privilégios de bancos de dados aplicam-se
a todas as tabelas em um determinado banco de dados. Os comando
para conceder e revogar apenas privilégios de banco de dados serão:
mysql> grant all to comercial.* to usermysql@localhost
mysql> revoke all on comercial.*;
• Nível das tabelas: privilégios de tabelas aplicam-se a todas as colunas em uma determinada tabela. São concedidos ou revogados utilizando
os comandos:
mysql> grant all on comercial.nome_tabela;
mysql> revoke all on comercial.nome_tabela;
• Nível das colunas: privilégios de colunas aplicam-se a uma única co-
luna em uma determinada tabela. Podem ser utilizados para os coman-
dos de seleção, inserção e atualização de determinadas colunas que de-
sejar. São concedidos utilizando os comandos:
mysql>grant select(nomecoluna1),
insert(nomecoluna1),
update(nomecoluna1)
on comercial.nome_tabela
to usermysql@localhost
identified by senha;
18
Casa do Código
Capítulo 2. Iniciando o projeto
• Nível stored routine: a rotina de alterar, criar rotina, executar e privilégios de concessão de opção aplica-se a stored procedures (procedi-
mentos e funções). Eles podem ser concedidos aos níveis globais e de
banco de dados. Também podem ser usados no nível de rotina para ro-
tinas individuais, exceto para criar uma. Se você não sabe o que é uma
store procedure, não se preocupe. No capítulo 6, você verá várias expli-
cações sobre o assunto. Esses privilégios são concedidos ou revogados
utilizando os comandos:
## para rotinas
mysql> grant routine on comercial.* to usermysql@localhost;
## para procedures
mysql> grant execute on procedure comercial.nomeprocedure
to usermysql@localhost;
• Nível proxy user: o privilégio de proxy permite que um usuário seja
proxy de outro. O usuário externo de um outro host assume os privi-
légios de um usuário. Utilizando os comandos:
mysql> grant PROXY on usermysql@localhost to
'usuarioexterno'@'hostexterno';
Como já temos o usuário que vamos utilizar durante o projeto, devemos
conectar ao MySQL usando-o.
Para isso, devemos abrir o prompt do Windows e navegar até a pasta bin
da instalação do MySQL, que, no meu caso, está na pasta c:\mysql\bin.
c:\mysql --user=root -psenha
Agora, conectado com o novo usuário, podemos criar o nosso primeiro
banco de dados!
2.3
Criando nosso banco
Daremos o nome ao nosso projeto de Comercial. Geralmente, nomeamos o
banco de dados com nome ou função executada pelo sistema. Desta maneira,
ele também se chamará comercial.
19
2.3. Criando nosso banco
Casa do Código
mysql> create database comercial;
mysql>
Atenção! O Windows não é case sensitive, ou seja, ele não faz dis-
tinção entre maiúscula e minúscula; mas alguns sistemas operacionais
baseados em Unix são. Se no futuro você desejar migrar seu banco para
outro sistema, isso pode causar sérios problemas. Por isso, desde o início,
utilize apenas letras maiúsculas ou minúsculas. Utilizarei apenas as mi-
núsculas durante todo o projeto. Existem maneiras de se contornar esses
problemas usando variáveis do MySQL e do próprio sistema operacional
Unix. No capítulo 11, voltarei a este assunto e explicarei como proceder.
Para verificar se o banco foi criado com sucesso, utilize o comando show
databases da seguinte maneira:
Fig. 2.3: Características do MySQL
20
Casa do Código
Capítulo 2. Iniciando o projeto
Verificando isso, temos que dizer para ao SGBD que queremos usar o
banco, com o seguinte comando:
mysql> use comercial;
Pronto! Agora poderemos criar as tabelas. É hora de planejar o que pre-
cisamos criar para o projeto.
2.4
Requisitos para o projeto
No decorrer deste livro, vamos simular que fomos contratados para desen-
volver um projeto para um cliente, que solicitou um sistema para vendas de
produtos. Nesse sistema, ele gostaria de fazer os seguintes cadastros:
• Clientes
• Fornecedores
• Vendedores
• Produtos
• Vendas
Não há nada melhor para aprender a programar do que começar um pro-
jeto prático e de preferência que tenha alguma aplicação real. Dessa forma,
você consegue iniciar o aprendizado pelo básico e ir gradualmente adicio-
nando elementos mais complexos, uma vez que os sistemas continuam evo-
luindo e tornando-se mais complicados.
Nas boas práticas de desenvolvimento e engenharia de software, após o
levantamento dos requisitos, a próxima etapa é o desenvolvimento da mode-
lagem do banco de dados, que consiste na sua criação. Isso pode evitar alguns
problemas que podem por em risco seu projeto. Algum deles são a falta de
campos na tela e, o pior, a inconsistência de dados. Uma vantagem de se fazer
a modelagem antes das telas é a agilidade que ganharemos ao desenvolvê-las,
pois os campos já foram definidos anteriormente.
21
2.5. (Minhas) Boas maneiras
Casa do Código
2.5
(Minhas) Boas maneiras
Depois de alguns anos programando, você acaba desenvolvendo manias e
métodos próprios. E não há nada melhor do que um banco de dados pa-
dronizado e organizado. Para isso, adoto um padrão sempre que vou fazer
a modelagem de um novo banco de dados, pois fica mais fácil a leitura das
consultas posteriormente. Além disso, o aprendizado para novas pessoas que
forem trabalhar no mesmo banco que você torna-se muito mais fácil.
Repositórios
Repositórios são softwares que fazem o controle de versão de seus arqui-
vos. Por exemplo, você tem um arquivo de texto, fez uma alteração nele e o
salvou. Depois de um mês e de várias outras alterações, você deseja saber o
que foi alterado nele desde a primeira vez que você escreveu. Se ele estivesse versionado em algum repositório, você poderia consultar todas suas mudan-
ças. Essa é a função do repositório.
Caso você queria conhecer um pouco mais sobre isso, você pode adquirir
o livro Controlando versões com Git e GitHub da Casa do Código (http://www.
casadocodigo.com.br/products/livro-git-github) sobre Git e Github, um dos
repositórios mais utilizados no mundo.
Algo que eu também tenho como padrão de desenvolvimento é fazer o
versionamento de todos os arquivos e scritps gerados durante um projeto.
Todos os arquivos que eu citar no livro estão disponíveis em meu repositório
particular no GitHub. Você pode acessá-lo através do link: https://github.
com/viniciuscdes/mysqlbook. Veja a figura . O programa mostra todas as
alterações que fiz no arquivo popula_banco.sql.
22
Casa do Código
Capítulo 2. Iniciando o projeto
Fig. 2.4: GitHub armazena todas as modificações de seus arquivos
Padronização do nome das tabelas
Para criação dos nomes das tabelas, eu faço uma relação com o sistema e
com aquilo a que ela vai se referir. Em nosso sistema Comercial, a tabela
de clientes ficaria COMCLIEN. Se ele se chamasse Financeiro, ela seria
FINCLIEN.
Não fica mais fácil para saber de qual sistema faz parte e a que a tabela
se refere? Assim, podemos montar o esqueleto do nosso padrão. Além da
uniformização dos nomes, padronizo também o número de letras: apenas 8
caracteres para a criação das tabelas. Volto a repetir que esta é uma regra que eu utilizo. Logo, caso queira, você pode seguir o seu padrão para ambos os
casos.
23
2.5. (Minhas) Boas maneiras
Casa do Código
Fig. 2.5: Padrão para criação de nome das tabelas
Pensando sempre no longo prazo, quando seu sistema atingir um grande
número de tabelas, essa padronização fará toda a diferença, pois ficará mais
fácil saber o que as consultas estão querendo dizer, como também a manu-
tenção no banco de dados.
Padronização do nome dos campos
Além de uniformizar as tabelas, vamos padronizar também seus nomes
dos campos. Esta padronização é até mais importante do que a anterior, pois,
além de saber a qual tabela o campo pertence, conseguiremos ver qual o seu
tipo: se é um campo caractere, um campo numérico etc.
Ao olhar para uma consulta pela primeira vez com n campos e n tabelas,
você fica perdido tentando saber de qual tabela é cada coluna e qual é seu
tipo. Utilizando nossa tabela COMCLIEN, a coluna de nome do cliente se-
ria C_NOMECLIEN. Mesmo sem conhecer a padronização, você já consegue
identificar que o campo é da tabela de clientes, além de saber também que se
trata do nome dele. Vamos adotar o C_ para identificar que o campo é do
tipo caractere.
Não é interessante? Em uma palavra, conseguimos dizer qual o tipo do
campo, a que se refere e a qual tabela pertence. Os campos N_NUMECLIEN
e D_DATACLIEN seriam o número de identificação do cliente e a data do
seu cadastro, respectivamente. Muito simples, não? O padrão completo de
campos será da seguinte maneira, como apresentado na figura 2.6:
24
Casa do Código
Capítulo 2. Iniciando o projeto
Fig. 2.6: Padrão para criação de campos
A letra que identifica o tipo do campo é a letra inicial do nome do tipo.
No exemplo, o campo era caractere, logo a letra foi C_. Para os demais tipos
de dados do MySQL, temos:
• C_: para campo do tipo caractere;
• D_: para campo do tipo data;
• N_: para campo do tipo numérico;
• B_: para campo do tipo blob.
Não se preocupe em reconhecer os tipo de dados agora. Na sequência,
vamos tratar sobre esse assunto e conhecer cada um.
2.6
Tipos de dados
Conhecer todos os tipos de dados existentes do MySQL é muito importante,
uma vez que será algo que vai impactar no funcionamento de seu sistema.
Com experiência, ficará automático decidir qual tipo de dado utilizar em cada
coluna. No começo, você vai pensar um pouco em qual tipo usar em cada
campo, mas não se preocupe. Por exemplo, como você escolheria o tipo do
campo para salvar o telefone do cliente? Vale lembrar que campos do tipo
25
2.6. Tipos de dados
Casa do Código
numérico não salvam zeros à esquerda. Este é um dos cuidados que você
deve ter.
O MySQL, como a maioria dos outros SGBD, possui 3 categorias de tipos
de dados: texto, número e data/tempo.
Tipo texto
• CHAR(tamanho) : guarda um número fixo de caracteres. Pode conter
letras, números e caracteres especiais. O tamanho deve ser declarado
entre parênteses. Guarda até 255 caracteres.
• VARCHAR(tamanho): ele possui as características do tipo CHAR, com
a diferença de que, se você criar com mais de 255 caracteres, ele trans-
forma para o tipo TEXT. Ou seja, se for criar algum campo com mais
de 255, já crie como TEXT.
• TEXT: guarda uma string: com o tamanho máximo de 65.535 caracte-
res.
• BLOB: é o tipo de dado medido pela quantidade de bytes, em vez de
pela quantidade de caracteres, conforme a maioria. Pode salvar por
imagens, por exemplo, com o máximo de 65.535 bytes de arquivo.
Dica: em muito lugares você encontrará exemplos que salvam as ima-
gens diretamente no banco de dados. Mas, em vez de salvá-las nele, pre-
fira utilizar um campo TEXT para salvar apenas o caminho em que a
imagem se encontra e, por meio da programação de sua aplicação, linká-
la. Assim, você ganhará em desempenho de banco de dados, pois não
vai salvá-la no banco. Também, se você for desenvolver em duas plata-
formas que usem bancos de dados distintos, isso facilitará quando quiser
recuperá-las.
26
Casa do Código
Capítulo 2. Iniciando o projeto
Tipo numérico
• TINYINT: guarda números do tipo inteiro. Suporta de -128 até 127
caracteres.
• SMALLINT: guarda números do tipo inteiro. Suporta de -32768 até
32767 caracteres.
• MEDIUMINT: guarda números do tipo inteiro. Suporta de -8388608
até 8388607 caracteres.
• INT(tamanho): guarda números inteiros. Suporta de -2147483648 até
2147483647 caracteres. O número máximo de caracteres pode ser espe-
cificado entre parênteses.
• BIGINT:
guarda
números
do
tipo
inteiro.
Suporta
de
-
9223372036854775808 até 9223372036854775807 caracteres.
• FLOAT(tamanho,decimal): guarda números REAIS. O número má-
ximo de caracteres pode ser especificado entre parênteses. Deve-se es-
pecificar o tamanho inteiro e o tamanho numérico da coluna.
• DOUBLE(tamanho,decimal): guarda números REAIS. O número
máximo de caracteres pode ser especificado entre parênteses. Deve-
se especificar o tamanho inteiro e o tamanho numérico da coluna. Esse
tipo armazena uma quantidade maior de número do que os campos do
tipo FLOAT.
Fique atento ao colocar a quantidade de casas decimais, pois, se incorreto,
afetará os cálculos que seu sistema efetuará.
Tipo date/time
Colunas de data e hora são uma grande pedra no sapato de muito de-
senvolvedores, pois cada SGBD e cada linguagem de programação tratam de
maneiras diferentes. Tenha muito cuidado. Procure conhecer o formato que
você vai utilizar no sistema. Para saber qual o formato de data e hora que o seu SGBD exige, consulte o 6, no qual apresento uma função para você verificar
isso.
27
2.7. Modelando o projeto
Casa do Código
• DATE(): tipo de campo que vai armazenar datas no: YYYY-MM-DD,
onde Y refere-se ao ano, M ao mês e D ao dia;
• DATETIME(): a combinação de data e tempo, no formato YYYY-MM-
DD HH:MI:SS;
• TIME(): armazena horas, minutos e segundos no formato HH:MI:SS.
Tente utilizar os tipos de dados corretamente para cada tipo de infor-
mação. Repetirei várias vezes no livro: É importante pensar no futuro
de sua aplicação. Pense que ela evoluirá e a complexidade vai aumentar.
Um fato curioso que aconteceu em 2013 é que o vídeo PSY - GANGNAM
STYLE no YouTube estourou a capacidade do campo que apresentava
o número de visualizações, obrigando o Google a alterar o tipo desse
campo. Imagine o risco para um sistema não prever a capacidade de in-
formações que vai armazenar. Repare que estamos falando do Google.
2.7
Modelando o projeto
Para modelar o banco de dados, existem no mercado vários mecanismos. Eu,
particularmente, gosto de utilizar o Workbench. Ele é uma ferramenta visual
unificada para arquitetos de banco de dados, desenvolvedores e DBAs.
MySQL Workbench fornece modelagem de dados, desenvolvimento de
SQL e ferramentas de administração abrangentes para a configuração do ser-
vidor, administração de usuários, backup e muito mais. Ele está disponível
para Windows, Linux e Mac OS X. Quando fizemos a nossa instalação, se
você não desmarcou a opção para instalar o Workbench, ele já deve estar ins-
talado. Se não estiver, você pode baixá-lo pelo link: http://www.mysql.com/
products/workbench/.
É comum, ao iniciar-se o aprendizado sobre programação, achar que
basta saber uma linguagem de programação e já sair programando sem ne-
nhum planejamento. É assim que muitos projetos falham ou causam proble-
mas, pois não foi feito um estudo do que deveria ser desenvolvido. Isso pro-
28
Casa do Código
Capítulo 2. Iniciando o projeto
vavelmente se deve às pressões por sistemas em prazos cada vez mais curtos
e com menores custos de produção.
Porém, por outro lado, isso acaba por prejudicar, e muito, o entendimento
correto do problema e, consequentemente, a construção de um sistema que
atenda às reais expectativas do usuário. Esta situação muitas vezes origina
sistemas de baixa qualidade com elevada necessidade de modificação e de
difícil manutenção. Por isso, procure entender e planejar muito bem o que
você deverá desenvolver.
Como estamos trabalhando em um projeto no qual vamos criar apenas
a parte do banco de dados, devemos pensar na modelagem dos dados. Ela é
composta de três etapas.
• Fase conceitual: na qual temos um cenário da vida real, e, baseado
nele, faremos o levantamento de requisitos que o projeto deve atender.
Nesta etapa, devemos explorar todas as necessidades do problema que
vamos resolver e, com essas informações, conseguiremos criar um mo-
delo conceitual, que será independente da tecnologia que utilizaremos.
Registraremos que dados podem aparecer no banco, mas não como es-
tes dados estão armazenados. Por exemplo: cadastro de clientes (da-
dos necessários: nome fantasia, razão social, endereço, CNPJ, cidade,
estado, telefone etc.).
• Fase lógica: ao contrário dos modelos conceituais, os lógicos são os
modelos em que os objetos, suas características e seus relacionamentos
têm suas representações de acordo com as regras de implementação e
limitantes impostos por alguma tecnologia. Ele é utilizado já na fase de
projeto mais independente de dispositivo físico, implementando con-
ceitos de construção de um banco de dados. Por exemplo: a figura 2.7;
• Fase física: elaborada a partir do modelo lógico, leva em consideração limites impostos por dispositivo físico e por requisitos não funcionais
dos programas que acessam os dados. Um SGBD diferente poderá de-
finir um modo diferente de implementação física das características e
dos recursos necessários para o armazenamento e a manipulação das
estruturas de dados. Para exemplificar, apresento-o na figura 2.8 que é
o diagrama do nosso projeto.
29
2.7. Modelando o projeto
Casa do Código
Fig. 2.7: Exemplo de modelo lógico
Não aprofundarei muito nos assuntos sobre modelagem de dados, uma
vez que é conteúdo para vários livros. Se você ainda não aprendeu muito
sobre o assunto, aconselho a pesquisar, pois isso auxiliará na hora de criar seu projeto.
Diagrama de Entidade e Relacionamento
O Diagrama de Entidade e Relacionamento (DER) do nosso projeto nada
mais é que uma representação gráfica (modelo físico) das tabelas do projeto
que vamos desenvolver. É muito importante que você desenvolva o DER, pois
ficará mais fácil você comunicar de forma visual as alterações no banco de
dados para os outros envolvidos nele. O nosso DER inicial está representado
na figura 2.8.
30
Casa do Código
Capítulo 2. Iniciando o projeto
Fig. 2.8: Diagrama de Entidade e Relacionamento
Conforme o projeto cresce, o diagrama deve manter-se atualizado. Assim,
você terá o controle visão do projeto.
Chega de teoria, por enquanto. Vamos à prática! Agora que sabemos
quais tabelas e campos devemos criar, podemos escrever as instruções para
aplicar no banco de dados.
31
Capítulo 3
Mão na massa: criando nossos
códigos
“Sempre escolha uma pessoa preguiçosa para realizar uma tarefa difícil. Ela
sempre irá achar a maneira mais simples de se fazer.”
– Bill Gates
A fase da criação do banco é uma das mais importantes do processo de
desenvolvimento de software. A integridade dos dados dependerá dela. Volto
a frisar: gaste um bom tempo nessa etapa!
Como já criamos o banco, agora poderemos criar as tabelas, alterá-las, in-
serir e manipular registros. Trabalharemos durante todo o livro com as mes-
mas tabelas.
3.1. Criando as tabelas do projeto
Casa do Código
3.1
Criando as tabelas do projeto
Agora, baseado nas tabelas que nós criamos na modelagem, nós devemos
criar os scripts (instruções na linguagem SQL), para que elas sejam criadas
no banco. A instrução create table() é o script que utilizaremos. Entre
parênteses, você deve colocar os campos que você quer na tabela, definindo
qual o tipo de cada um.
Vamos utilizar os padrões sugeridos anteriormente. A tabela de clientes
em nosso padrão fica comclien, e, utilizando a instrução de criação, temos
o nosso primeiro código para a montagem de uma:
mysql>
create table comclien(
n_numeclien int not null auto_increment,
c_codiclien varchar(10),
c_nomeclien varchar(100),
c_razaclien varchar(100),
d_dataclien date,
c_cnpjclien varchar(20),
c_foneclien varchar(20),
primary key (n_numeclien));
Você pode verificar se realmente a tabela foi criada corretamente utili-
zando o desc (describe). Para isso, no terminal, digite:
mysql> desc comclien;
Algo parecido com a figura 3.1 deve ser mostrado no terminal do MySQL.
Fig. 3.1: Descrevendo o conteúdo da tabela de clientes
Observe que, após todos os campos, na última linha, temos: primary
key(n_numeclien). Nela estamos informando para o banco de dados o
34
Casa do Código
Capítulo 3. Mão na massa: criando nossos códigos
campo n_numeclien, que é a chave primária da tabela e seu registro será
único.
Introdução à chave primária
A chave primária é o que torna a linha ou o registro de uma tabela únicos.
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 n_numeclien será
único, isto é, nenhum par de linhas possuirá o mesmo valor na mesma coluna.
Será uma sequência de preferência numérica que identificará um registro.
Dica: procure usar um campo para chave primária que não seja mos-
trado na tela de seu sistema. Crie um campo específico para ela e um
outro para você poder manipular e mostrar na tela, como o código do
cliente, por exemplo. Isso ajudará na flexibilidade do seu sistema, na ma-
nutenibilidade e na performance.
Auto_increment
A cláusula auto_increment é utilizada para incrementar automatica-
mente o valor da chave primária da tabela. Você pode retornar o próximo
valor do campo de outras maneiras, porém com o incremento automático
fica mais simples e mais seguro. Por padrão, o auto_increment inicia-se
do 1. Porém, se houver a necessidade de iniciar por outro valor você pode
alterá-lo, fazendo:
mysql>
ALTER TABLE comclien AUTO_INCREMENT=100;
Da mesma forma que criamos a tabela para clientes, faremos para as ou-
tras tabelas do nosso projeto.
mysql>
create table comforne(
n_numeforne
int not null auto_increment,
c_codiforne
varchar(10),
c_nomeforne
varchar(100),
c_razaforne
varchar(100),
35
3.1. Criando as tabelas do projeto
Casa do Código
c_foneforne
varchar(20),
primary key(n_numeforne));
mysql>
create table comvende(
n_numevende
int not null auto_increment,
c_codivende
varchar(10),
c_nomevende
varchar(100),
c_razavende
varchar(100),
c_fonevende
varchar(20),
n_porcvende
float(10,2),
primary key(n_numeforne));
mysql>
create table comprodu(
n_numeprodu
int not null auto_increment,
c_codiprodu
varchar(20),
c_descprodu
varchar(100),
n_valoprodu
float(10,2),
c_situprodu
varchar(1),
n_numeforne
int,
primary key(n_numeprodu));
mysql>
create table comvenda(
n_numevenda int not null auto_increment,
c_codivenda varchar(10),
n_numeclien int not null,
n_numeforne int not null,
n_numevende int not null,
n_valovenda float(10,2),
n_descvenda float(10,2),
n_totavenda float(10,2),
d_datavenda date,
primary key(n_numevenda));
mysql>
create table comvendas(
n_numevenda int not null auto_increment,
c_codivenda varchar(10),
n_numeclien int not null,
n_numeforne int not null,
n_numevende int not null,
36
Casa do Código
Capítulo 3. Mão na massa: criando nossos códigos
n_valovenda float(10,2),
n_descvenda float(10,2),
n_totavenda float(10,2),
d_datavenda date,
primary key(n_numevenda));
mysql>
create table comivenda(
n_numeivenda int not null auto_increment,
n_numevenda int not null,
n_numeprodu int not null,
n_valoivenda float(10,2),
n_qtdeivenda int,
n_descivenda float(10,2),
primary key(n_numeivenda));
Pronto! Criamos as tabelas do nosso projeto. Observe que comvendas,
comivenda e comprodu contêm campos de outras tabelas. É o que chama-
mos de foreign key ou chave estrangeira.
Introdução à chave estrangeira
A chave estrangeira define um relacionamento entre tabelas, comumente
chamado de integridade referencial. Esta regra baseia-se no fato de que uma
chave estrangeira em uma tabela é a chave primária em outra. Na imagem
1.2 que mostrei no início do livro como exemplo, uma tabela tem o campo
id_estado, que é uma chave estrangeira. Isto é, ele pode se repetir na tabela
de clientes. No entanto, deve ser único na tabela de estados, pois assim
terá uma referência exclusiva. Exemplificando:
37
3.2. Cuidando da integridade do banco de dados
Casa do Código
Fig. 3.2: Relacionamento entre duas tabelas
A imagem mostra o relacionamento referencial entre a tabela clientes
e a estados, no qual o campo id_estado referencia o campo id_estado
na tabela estados. Assim, podemos identificar de qual estado é cada cliente.
3.2
Cuidando da integridade do banco de da-
dos
Quando criamos a tabela comvenda, nós incluímos colunas de outras tabe-
las, como n_numeclien, n_numeforne e n_numeprodu. Essas colunas
estão referenciando um registro em sua tabela de origem. Porém, como ape-
nas criamos o campo, mas nada que informe o banco sobre essa referência,
devemos fazer isso, passando uma instrução ao nosso SGBD por meio das
constraints, como mostram os códigos na sequência.
mysql> alter table comvenda add constraint fk_comprodu_comforne
foreign key(n_numeforne)
references comforne(n_numeforne)
on delete no action
on update no action;
mysql> alter table comvenda add constraint fk_comprodu_comvende
foreign key(n_numevende)
references comvende(n_numevende)
on delete no action
38
Casa do Código
Capítulo 3. Mão na massa: criando nossos códigos
on update no action;
mysql> alter table comvenda add constraint fk_comvenda_comclien
foreign key(n_numeclien)
references comclien(n_numeclien)
on delete no action
on update no action;
mysql> alter table comivenda add constraint fk_comivenda_comprodu
foreign key(n_numeprodu)
references comprodu (n_numeprodu)
on delete no action
on update no action;
mysql> alter table comivenda add constraint fk_comivenda_comvenda
foreign key(n_numevenda)
references comvenda (n_numevenda)
on delete no action
on update no action;
Com a criação das constraints de chave estrangeira, demos mais se-
gurança à integridade de nossos dados. Agora, se você tentar deletar algum
registro da tabela de clientes que possui um registro referenciado na tabela
de vendas, o banco de dados barrará a deleção, impedindo que a integridade
se perca. Quando declaramos a chave primária em nossas tabelas, o SGBD
criará as constraints automaticamente.
Se tivéssemos criado uma constraint errada, poderíamos deletá-la
utilizando a instrução irreversível:
mysql> alter table comivenda drop foreign key
fk_comivenda_comprodu;
3.3
Alterando as tabelas
Com o crescimento de seu sistema, há a necessidade de criação de novas ta-
belas. Se você reparar em nossa tabela de clientes, não criamos campos para
cidade ou para estados. Para não precisar excluí-la e criá-la novamente, fazemos uma alteração nela com o comando alter table.
39
3.3. Alterando as tabelas
Casa do Código
Acrescentaremos um campo para informar a cidade no cadastro de clien-
tes.
mysql> alter table comclien add column c_cidaclien varchar(50);
E um campo para informar o estado.
mysql>
alter table comclien add column c_estclien varchar(50);
Ops! Um erro. Criamos o campo para estado fora do padrão. Para cri-
armos na norma correta, vamos deletar o que geramos errado. Utilizando
novamente o comando alter table, só que agora com o drop column:
mysql>
alter table comclien drop column c_estclien;
Agora aprendemos a utilizar a instrução drop column. Sempre que
existir alguma coluna incorreta, podemos deletar. Só não podemos excluir as
colunas que são foreign key em outra tabela e possuir registros. O SGBD
não permitirá isso, para não corromper a integridade dos dados, uma vez que
perderá o ponto a que outras tabelas estão referindo-se.
Vamos criar o campo novamente, agora corretamente.
mysql> alter table comclien add column c_estaclien varchar(50);
Fácil, não? E podemos utilizar o alter table para alterar o tipo do
campo. Se quiséssemos mudar o tipo do campo c_estaclien para o tipo
numérico, usaríamos o alter table agora com o modify. Vamos exem-
plificar:
mysql> alter table comclien modify column c_estaclien int;
Porém, lembre de nosso padrão para os campos numéricos e de caracte-
res. No caso do campo de c_estaclien, poderíamos alterar seu tamanho
e manter seu tipo como varchar, utilizando também o modify.
mysql>
alter table comclien modify column c_estaclien
varchar(100);
40
Casa do Código
Capítulo 3. Mão na massa: criando nossos códigos
3.4
Excluindo (dropando) as tabelas
Quando criamos nossas tabelas, nós fizemos uma a mais por engano. Foi a
tabela comvendas, sendo a comvenda a correta. Para deletarmos a indese-
jada, utilizaremos o drop table.
mysql>
drop table comvendas;
Você reparou que usamos o drop para excluir qualquer objeto no banco
dados? Agora, se você desejar excluir os registros sem excluir a tabela, deverá utilizar uma outra instrução SQL, a qual mostrarei no capítulo 4, entre outros comandos.
Com isso já podemos começar a trabalhar com os dados, pois aprende-
mos a criar ( reate), alterar ( alter), deletar ( rop) e modificar ( modify)
os objetos no banco de dados.
Este capítulo foi apenas o primeiro da parte prática. Muitos outros virão.
Não se preocupe em decorar as instruções SQL. Comece copiando para criar
novas tabelas, campos etc. Pratique bastante, que passará a ser algo natural
com o tempo.
41
Capítulo 4
Manipulando registros
“Mova-se rapidamente e quebre as coisas. Ao menos que você não esteja
quebrando coisas, você não está se movendo rápido o suficiente.”
– Mark Zuckerberg
4.1
Inserindo registros
Aprendemos a modelar, criar, alterar e excluir tabelas. Precisamos agora de
registros em nosso banco de dados, pois seu intuito são suas manipulações.
Porém, para isso, precisamos aprender como inseri-los através do SGBD, e
não por meio de uma aplicação, uma vez que, independente da linguagem
de programação em que você estiver trabalhando para desenvolver seu sis-
tema, você poderá inserir registros diretamente no banco de dados através de
comando SQL.
4.1. Inserindo registros
Casa do Código
Você utilizará essa prática constantemente em sua vida de desenvolve-
dor, seja para carregar uma tabela com dados novos, em uma migração, para
testar algum processo que necessita de informações ou para corrigir algum
problema. Com certeza, você vai se deparar com uma situação que demande
a necessidade de fazer uma inserção manual.
Vamos fazer o primeiro insert na tabela COMCLIEN com o comando
insert into COMCLIEN. Entre parênteses, informaremos em quais colu-
nas queremos inserir os registros. Depois, devemos informar qual o valor
para cada coluna, da seguinte maneira:
mysql>insert into comclien(n_numeclien,
c_codiclien,
c_nomeclien,
c_razaclien,
d_dataclien,
c_cnpjclien,
c_foneclien,
c_cidaclien,
c_estaclien)
values (1,
'0001',
'AARONSON',
'AARONSON FURNITURE LTDA',
'2015-02-17',
'17.807.928/0001-85',
'(21) 8167-6584',
'QUEIMADOS',
'RJ');
Quando você executa um comando, tudo está correto e a operação é
concluída, uma mensagem do tipo (Query Ok...) é mostrada logo após.
Se ocorrer um erro com o seu código, será exibida uma mensagem (ER-
ROR...). Leia com atenção as mensagem de erros, pois são bem explica-
tivas e ficará fácil para você corrigi-lo.
Muito simples! 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 con-
44
Casa do Código
Capítulo 4. Manipulando registros
ferir se os valores estão na sequência correta, como a seguir, onde omitimos
estes campos. O SGBD subentende que todos os campos serão populados.
mysql>
insert into comclien
values (1,
'0001',
'AARONSON',
'AARONSON FURNITURE LTDA',
'2015-02-17',
'17.807.928/0001-85,
'(21) 8167-6584',
'QUEIMADOS',
'RJ');
Lembrete: você se lembra das constraints que criamos no ca-
pítulo 3? Quando formos inserir, por exemplo, um cliente na tabela
comvenda, ele deve estar na tabela comclien. Afinal, se ele não existir
na tabela, o SGBD retornará um erro e não deixará você inserir, porque
quando criamos a constraint na tabela de vendas, queremos dizer que
deve haver um relacionamento de dados entre ambas. Caso não haja,
não conseguiremos consultar os dados dos clientes que estão na tabela
comclien.
4.2
Alterando registros
Da mesma maneira que conseguimos incluir registros no banco de dados, po-
demos alterá-los. Uma vez que temos um sistema em produção com pessoas
utilizando-o, não podemos excluir os registros para inseri-los corretamente.
Por isso, devemos alterá-lo usando o comando update.
Você fez a inserção no registro de clientes e errou o nome fantasia. No
exemplo que eu descrevi anteriormente, coloquei um incorretamente. Agora,
quero corrigi-lo.
mysql> update comclien set c_nomeclien = 'AARONSON FURNITURE'
where n_numeclien = 1;
45
4.3. Excluindo registros
Casa do Código
mysql> commit;
Podemos atualizar mais de um campo de uma vez só, separando com ,,
fazendo:
mysql> update comclien set c_nomeclien = 'AARONSON FURNITURE'
, c_cidaclien = 'LONDRINA'
, c_estaclien = 'PR'
where n_numeclien = 1;
mysql> commit;
Perceba que, além do update, utilizei o set para informar qual campo
que eu quero alterar, o where para indicar a condição para fazer a alteração e, em seguida, o commit para dizer para o 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 maneira:
mysql>
update comclien set c_nomeclien = 'AARONSON'
where n_numeclien = 1;
mysql> 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.
Atenção! Ao utilizar o update para alterar um ou mais registros,
não se esqueça de usar o where para informar quais registros você deseja
mudar. Sem ele, o comando é aplicado a todos registros da tabela.
4.3
Excluindo registros
Incluímos e alteramos registros. Porém, e se quisermos deletar algum? Para
isso, devemos utilizar uma outra instrução SQL: o delete. Diferente do
46
Casa do Código
Capítulo 4. Manipulando registros
drop, ele deleta os registros das colunas do banco de dados. O drop é usado
para excluir objetos do banco, como tabelas, colunas, views, procedures etc.); enquanto, o delete deletará os registros das tabelas, podendo excluir apenas
uma linha ou todos os registros, como você desejar.
Desta maneira, vamos apagar o primeiro registro da tabela comclien.
mysql> delete from comclien
where n_numeclien = 1;
mysql> commit;
Agora, vamos deletar todos os registros da tabela de clientes.
mysql> delete from comcilen;
mysql> 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 deleção de dados incorretos.
Além do delete, podemos fazer a deleção de dados usando uma ins-
trução SQL chamada de truncate. Este é um comando que não necessita
de commit e não é possível a utilização de cláusulas where. Logo, só o use
se você tem certeza do que estiver querendo excluir, uma vez que ele é irre-
versível. Nem o rollback pode reverter a 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 deleção direta. Para usar
esse comando, faça do seguinte modo:
mysql> truncate table comclien;
Lembre-se: nunca se esqueça de criar as constraints de chave es-
trangeira das tabelas, pois ao tentar excluir um registro, se houver uma
constraint nela e ele estiver sendo utilizado em outra tabela, o SGBD
não deixará você excluí-lo com intuito de manter a integridade dos da-
dos.
47
4.3. Excluindo registros
Casa do Código
No repositório que citei no início do livro,
existe o arquivo
popula_banco.sql, que possui os scripts de inserção ( insert) e de
alteração ( update) para você aplicar em seu banco de dados e acompanhar
os exemplos no decorrer da leitura.
Inserimos, alteramos e deletamos. Caso você tenha aplicado o arquivo
que indiquei ou inserido seus próprios registros, também possuímos vários
deles em nosso banco. Agora podemos começar a fazer suas manipulações e
seleções.
48
Capítulo 5
Temos registros: vamos
consultar?
“Inspecionar para prevenir defeitos é bom; Inspecionar para encontrar defeitos é desperdício.”
– Shigeo Shingo
O objetivo de armazenar registros em um banco de dados é a possibili-
dade de recuperar e utilizá-los em relatórios para análises mais profundas,
processamento dessas informações etc. Essa recuperação é feita através de
consultas.
5.1. Estrutura básica das consultas
Casa do Código
5.1
Estrutura básica das consultas
O comando SQL utilizado para fazer consultas é o select. Nada mais óbvio,
já que vamos fazemos consultas, ou seja, selecionar dados. Junto ao select,
devemos dizer ao SGBD de onde você quer selecioná-los; no caso, de qual
tabela queremos os registros. Por isso usamos o from. Com isso, temos
a sintaxe básica para fazer a primeira consulta. Vamos selecionar todos os
registros da tabela de cliente.
Quando não queremos selecionar um ou vários campos específicos, uti-
lizamos o asterisco (*). Ficaria da seguinte maneira:
mysql> select * from comclien;
+-------------+-------------+-----------------------
| n_numeclien | c_codiclien | c_nomeclien
+-------------+-------------+-----------------------
|
1 | 0001
| AARONSON FURNITURE
|
2 | 0002
| LITTLER
|
3 | 0003
| KELSEY NEIGHBOURHOOD
|
4 | 0004
| GREAT AMERICAN MUSIC
|
5 | 0005
| LIFE PLAN COUNSELLING
|
6 | 0006
| PRACTI-PLAN
|
7 | 0007
| SPORTSWEST
|
8 | 0008
| HUGHES MARKETS
|
9 | 0009
| AUTO WORKS
|
10 | 00010
| DAHLKEMPER
+-------------+-------------+-----------------------
+------------------------+-------------+--------------------
| c_razaclien
| d_dataclien | c_cnpjclien
+------------------------+-------------+--------------------
| AARONSON FURNITURE LTD | 2015-02-17 | 17.807.928/0001-85
| LITTLER LTDA
| 2015-02-17 | 55.643.605/0001-92
| KELSEY NEIGHBOURHOOD | 2015-02-17 | 05.202.361/0001-34
| GREAT AMERICAN MUSIC
| 2015-02-17 | 11.880.735/0001-73
| LIFE PLAN COUNSELLING | 2015-02-17 | 75.185.467/0001-52
| PRACTI-PLAN LTDA
| 2015-02-17 | 32.518.106/0001-78
| SPORTSWEST LTDA
| 2015-02-17 | 83.175.645/0001-92
| HUGHES MARKETS LTDA
| 2015-02-17 | 04.728.160/0001-02
50
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
| AUTO WORKS LTDA
| 2015-02-17 | 08.271.985/0001-00
| DAHLKEMPER LTDA
| 2015-02-17 | 49.815.047/0001-00
+------------------------+-------------+--------------------
+----------------+-------------------------+-------------+
| c_foneclien
| c_cidaclien
| c_estaclien |
+----------------+-------------------------+-------------+
| (21) 8167-6584 | QUEIMADOS
| RJ
|
| (27) 7990-9502 | SERRA
| ES
|
| (11) 4206-9703 | BRAGANÇA PAULISTA
| SP
|
| (75) 7815-7801 | SANTO ANTÔNIO DE JESUS | BA
|
| (17) 4038-9355 | BEBEDOURO
| SP
|
| (28) 2267-6159 | CACHOEIRO DE ITAPEMIRI | ES
|
| (61) 4094-7184 | TAGUATINGA
| DF
|
| (21) 7984-9809 | RIO DE JANEIRO
| RJ
|
| (21) 8548-5555 | RIO DE JANEIRO
| RJ
|
| (11) 4519-7670 | SÃO PAULO
| SP
|
+----------------+-------------------------+-------------+
10 rows in set (0.01 sec)
Se quiséssemos selecionar apenas o código e a razão social do cliente,
no lugar do *, colocaríamos os campos n_numeclien, c_codiclien e
c_razaclien.
mysql> select n_numeclien, c_codivenda, c_razaclien
from comclien;
+------------+-------------+-----------------------+
|n_numeclien | c_codiclien | c_nomeclien
|
+------------+-------------+-----------------------+
|
1 | 0001
| AARONSON FURNITURE
|
|
2 | 0002
| LITTLER
|
|
3 | 0003
| KELSEY NEIGHBOURHOOD |
|
4 | 0004
| GREAT AMERICAN MUSIC |
51
5.1. Estrutura básica das consultas
Casa do Código
|
5 | 0005
| LIFE PLAN COUNSELLING |
|
6 | 0006
| PRACTI-PLAN
|
|
7 | 0007
| SPORTSWEST
|
|
8 | 0008
| HUGHES MARKETS
|
|
9 | 0009
| AUTO WORKS
|
|
10 | 00010
| DAHLKEMPER
|
+------------+-------------+-----------------------+
10 rows in set (0.00 sec)
Ainda podem surgir situações que necessitem selecionar apenas um re-
gistro. Neste caso, utilizamos o where, da mesma maneira que o usamos no
capítulo anterior.
Vamos selecionar o cliente com uma cláusula que deve ter c_codiclien
= ‘00001’. Note que coloquei o código dele entre aspas simples. Devemos
fazer dessa forma para dizer ao SGBD que estamos querendo comparar uma
coluna do tipo texto. Para coluna do tipo numérico, não há necessidade.
mysql> select n_numeclien, c_codiclien, c_razaclien
from comclien
where c_codiclien = '0001';
+-------------+-------------+------------------------+
| n_numeclien | c_codiclien | c_razaclien
|
+-------------+-------------+------------------------+
|
1 | 0001
| AARONSON FURNITURE LTD |
+-------------+-------------+------------------------+
1 row in set (0.00 sec)
E se quiséssemos o contrário? Todos os clientes que sejam diferentes de
‘0001’? Faríamos uma consulta utilizando o operador do MySQL que sig-
nifica diferente: <>. Ficaria assim:
mysql> select n_numeclien, c_codiclien, c_razaclien
from comclien
where c_codiclien <> '0001';
+-------------+-------------+------------------------+
| n_numeclien | c_codiclien | c_razaclien
|
+-------------+-------------+------------------------+
52
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
|
2 | 0002
| LITTLER LTDA
|
|
3 | 0003
| KELSEY NEIGHBOURHOOD |
|
4 | 0004
| GREAT AMERICAN MUSIC
|
|
5 | 0005
| LIFE PLAN COUNSELLING |
|
6 | 0006
| PRACTI-PLAN LTDA
|
|
7 | 0007
| SPORTSWEST LTDA
|
|
8 | 0008
| HUGHES MARKETS LTDA
|
|
9 | 0009
| AUTO WORKS LTDA
|
|
10 | 00010
| DAHLKEMPER LTDA
|
+-------------+-------------+------------------------+
9 rows in set (0.00 sec)
Observe que ele trouxe todos os clientes,
exceto aquele cujo
c_codiclien é igual a ‘0001’.
Além dos operadores de comparação = e <>, temos os seguintes:
• > : maior;
• < : menor;
• >=: maior e igual;
• <=: menor e igual.
Em vez de utilizarmos o = para comparar uma string, também po-
demos utilizar o like. Ele também é usado para isso e, excepcionalmente,
para quando queremos consultar uma e só conhecemos uma parte dela. Por
exemplo, se quisermos retornar todos os clientes que se iniciam com a letra
B, montaríamos nossa consulta da seguinte maneira:
mysql> select n_numeclien, c_codiclien, c_razaclien
from comclien
where c_razaclien like 'L%';
+-------------+-------------+-----------------------+
| n_numeclien | c_codiclien | c_razaclien
|
+-------------+-------------+-----------------------+
|
5 | 0005
| LIFE PLAN COUNSELLING |
|
2 | 0002
| LITTLER LTDA
|
53
5.1. Estrutura básica das consultas
Casa do Código
+-------------+-------------+-----------------------+
2 rows in set (0.01 sec)
O símbolo de % (porcento) é um curinga no SQL. Quando não sabemos
uma parte da string, podemos utilizá-lo no início, no meio ou no fim dela.
Distinct()
E se fizéssemos uma lista de todos os clientes que compraram algo? Se
fosse apenas a consulta:
mysql> select n_numeclien from comvenda;
Isso retornaria lista de clientes e de vendas e, se o cliente possuir mais de
uma venda, apareceria repetido no resultado. Para não selecionar um registro
igual ao outro, utilizamos o DISTINCT. Com o seguinte código, teremos a
lista de clientes que fizeram ao menos uma compra e sem nenhuma repetição.
mysql> select distinct n_numeclien
from comvenda;
+-------------+
| n_numeclien |
+-------------+
|
1 |
|
2 |
|
3 |
|
4 |
|
5 |
|
6 |
|
7 |
|
8 |
|
9 |
+-------------+
9 rows in set (0.00 sec)
No arquivo que disponibilizei para inserir os registros, o cliente com o
n_numeclien igual a 10 não fez nenhuma compra e outros fizeram mais de
uma. Mais à frente, vou mostrar como podemos contar as vendas de cada um.
54
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
5.2
Subquery ou subconsulta
As subconsultas são alternativas para as joins, que vamos ver logo a seguir.
Utilizando-as, conseguimos ter um select dentro de outro select para
nos ajudar a recuperar registros que estão referenciados em outras tabelas.
Antes de demonstrar o uso da subquery, vamos aprender a utilização das
cláusulas in, not in, exists e not exists, pois precisaremos delas
para criar verificações para nossas consultas.
Cláusulas in e not in
Até agora, utilizamos os sinais =, <>, >= e <== para as condições das consultas. Para fazermos comparações com n valores, não conseguiríamos
fazer com esses que aprendemos até agora, pois eles aceitam apenas um valor
para a comparação. Para exemplificarmos, vamos escrever uma consulta para
retornar simultaneamente os clientes que possuem n_numeclien igual a 1
e 2.
mysql> select c_codiclien, c_razaclien
from comclien
where n_numeclien = 1,2;
ERROR 1241 (21000): Operand should contain 1 column(s)
Ops! Temos um erro. Apenas utilizaremos o sinal de = quando a compa-
ração for só com um valor. Já as cláusulas in e not in surgem para fornecer
apoio quando queremos testar um ou mais. Vamos fazer a mesma consulta
utilizando o in. Lembre-se de colocar os valores entre parênteses e separados
por vírgula. Se forem valores do tipo string, será entre aspas simples.
mysql> select c_codiclien, c_razaclien
from comclien
where n_numeclien in (1,2);
+-------------+------------------------+
| c_codiclien | c_razaclien
|
+-------------+------------------------+
| 0001
| AARONSON FURNITURE LTD |
55
5.2. Subquery ou subconsulta
Casa do Código
| 0002
| LITTLER LTDA
|
+-------------+------------------------+
2 rows in set (0.63 sec)
Ou podíamos consultar clientes que possuem o n_nnumeclien dife-
rente de 1 e 2. Nesta ocasião, devemos utilizar o not in. Vamos ao código.
mysql> select c_codiclien, c_razaclien
from comclien
where n_numeclien not in (1,2);
+-------------+-----------------------+
| c_codiclien | c_razaclien
|
+-------------+-----------------------+
| 0003
| KELSEY NEIGHBOURHOOD |
| 0004
| GREAT AMERICAN MUSIC |
| 0005
| LIFE PLAN COUNSELLING |
| 0006
| PRACTI-PLAN LTDA
|
| 0007
| SPORTSWEST LTDA
|
| 0008
| HUGHES MARKETS LTDA
|
| 0009
| AUTO WORKS LTDA
|
| 00010
| DAHLKEMPER LTDA
|
+-------------+-----------------------+
8 rows in set (0.00 sec)
Nas duas últimas consultas, nós sabíamos os números dos clientes que
queríamos ou não consultar. Entretanto, em nosso projeto, surgiu a neces-
sidade de criar uma para retornar a razão social dos clientes que possuem
registro na tabela comvenda. Para esta situação, vamos utilizar uma sub-
consulta. A principal retornará a razão social do cliente e vai comparar o
n_numeclien que será retornado pela subconsulta. Esta, por sua vez, retor-
nará todos n_nnumeclien da tabela comvenda. Vamos utilizar a cláusula
in e a subconsulta entre parênteses. Vamos ao código.
mysql> select c_razaclien
from comclien
where n_numeclien in (select n_numeclien
from comvenda
where n_numeclien);
56
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
+------------------------+
| c_razaclien
|
+------------------------+
| AARONSON FURNITURE LTD |
| AUTO WORKS LTDA
|
| GREAT AMERICAN MUSIC
|
| HUGHES MARKETS LTDA
|
| KELSEY NEIGHBOURHOOD |
| LIFE PLAN COUNSELLING |
| LITTLER LTDA
|
| PRACTI-PLAN LTDA
|
| SPORTSWEST LTDA
|
+------------------------+
9 rows in set (0.00 sec)
Utilizando a mesma situação, vamos buscar os clientes que ainda não fi-
zeram nenhuma venda. Para isso, utilizaremos o not in. Você verá que o
único cliente que ainda não possui registro de venda retornará, pois a consulta principal vai consultar todos os registros que não possuem o n_nnumeclien
na tabela comvenda.
mysql> select c_razaclien
from comclien
where n_numeclien not in (select n_numeclien
from comvenda);
+------------------+
| c_razaclien
|
+------------------+
| DAHLKEMPER LTDA |
+------------------+
1 row in set (0.01 sec)
Você utilizará bastante as subconsultas em diversos cenários que surgirão
em seu dia a dia. Ainda podemos ter uma com a característica de um campo
da tabela, que retornará uma ou mais colunas de lugares diferentes. Exempli-
ficando: vamos supor que, em nosso sistema, surgiu a necessidade de desen-
volver uma consulta para retornar o código das vendas e a razão social dos
respectivos clientes que as fizeram.
57
5.2. Subquery ou subconsulta
Casa do Código
A consulta principal será um select na tabela comvenda junto com
uma subconsulta. Esta terá uma vírgula separando-a do primeiro campo e o
n_nnumeclien sendo passado da consulta principal, para realizar a compa-
ração e buscar a razão social do respectivo cliente. Vamos ao código.
mysql> select c_codivenda Cod_Venda,
(select c_razaclien
from comclien
where n_numeclien = comvenda.n_numeclien) Nome_Cliente
from comvenda;
+-------------+------------------------+
| Cod_Venda
| Nome_Cliente
|
+-------------+------------------------+
| 1
| AARONSON FURNITURE LTD |
| 2
| LITTLER LTDA
|
| 3
| KELSEY NEIGHBOURHOOD |
| 4
| GREAT AMERICAN MUSIC
| 5
| LIFE PLAN COUNSELLING |
| 6
| PRACTI-PLAN LTDA
|
| 7
| SPORTSWEST LTDA
|
| 8
| HUGHES MARKETS LTDA
|
| 9
| AUTO WORKS LTDA
|
| 10
| AARONSON FURNITURE LTD |
| 11
| AARONSON FURNITURE LTD |
| 12
| LITTLER LTDA
|
| 13
| KELSEY NEIGHBOURHOOD |
| 14
| KELSEY NEIGHBOURHOOD |
| 15
| LIFE PLAN COUNSELLING |
| 16
| PRACTI-PLAN LTDA
|
| 17
| SPORTSWEST LTDA
|
| 18
| HUGHES MARKETS LTDA
|
| 19
| AUTO WORKS LTDA
|
| 20
| AUTO WORKS LTDA
|
+-------------+------------------------+
20 rows in set (0.00 sec)
Essa maneira não é muito usada, porque há perda de performance e o
código não fica legal. Por isso, aprenderemos a fazer JOINS: a forma correta
58
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
para retornamos valores de uma ou mais tabelas em um único select.
Criação de alias (apelidos das tabelas)
Observe o nosso último código. No cabeçalho do resultado, em vez de
retornar os nomes das colunas, apareceram os que colocamos na frente das
que estamos consultando. Ou seja, criamos apelidos. Você pode fazer isso
em qualquer coluna em uma consulta. Em vez de mostrar seu nome no resul-
tado, você pode exibir o título que quiser. Dizemos que estamos apelidando
as colunas e isso é chamado de alias.
Para exemplificar, vamos consultar a c_codiclien e a c_nomeclien,
colocando os alias CODIGO e CLIENTE respectivamente. Vamos ao código:
myql> select c_codiclien CODIGO, c_nomeclien CLIENTE
from comclien
where n_numeclien not in(1,2,3,4);
+-------------+-----------------------+
|
CODIGO
|
CLIENTE
|
+-------------+-----------------------+
| 0005
| LIFE PLAN COUNSELLING |
| 0006
| PRACTI-PLAN
|
| 0007
| SPORTSWEST
|
| 0008
| HUGHES MARKETS
|
| 0009
| AUTO WORKS
|
| 00010
| DAHLKEMPER
|
+-------------+-----------------------+
6 rows in set (0.00 sec)
Utilizamos os alias quando temos muitas colunas com nomes iguais, que
estão retornando algum nome diferente ou que não faça sentido para quem
você apresentará o retorno da consulta. Neste último caso, temos como exem-
plo o nome de alguma função, como veremos no capítulo 6. Já que teremos
funções que serão longas, não será legal apresentar um relatório para um cli-
ente mostrando seu nome em vez do que a coluna representa.
Por exemplo: pegaremos a consulta onde tivemos uma subconsulta fa-
zendo o papel de uma coluna. Se nós não tivéssemos utilizado um alias, o
59
5.2. Subquery ou subconsulta
Casa do Código
resultado de seu cabeçalho seria o que está escrito na subconsulta. Vamos ao
código.
mysql> select c_codivenda,
(select c_razaclien
from comclien
where n_numeclien = comvenda.n_numeclien)
from comvenda;
+-------------+-----------------------------------------------+
| c_codivenda | (select c_razaclien
from comclien
where n_numeclien = comvenda.n_numeclien) |
+-------------+-----------------------------------------------+
| 1
| AARONSON FURNITURE LTD
|
| 2
| LITTLER LTDA
|
| 3
| KELSEY NEIGHBOURHOOD
|
| 4
| GREAT AMERICAN MUSIC
|
| 5
| LIFE PLAN COUNSELLING
|
| 6
| PRACTI-PLAN LTDA
|
| 7
| SPORTSWEST LTDA
|
| 8
| HUGHES MARKETS LTDA
|
| 9
| AUTO WORKS LTDA
|
| 10
| AARONSON FURNITURE LTD
|
| 11
| AARONSON FURNITURE LTD
|
| 12
| LITTLER LTDA
|
| 13
| KELSEY NEIGHBOURHOOD
|
| 14
| KELSEY NEIGHBOURHOOD
|
| 15
| LIFE PLAN COUNSELLING
|
| 16
| PRACTI-PLAN LTDA
|
| 17
| SPORTSWEST LTDA
|
| 18
| HUGHES MARKETS LTDA
|
| 19
| AUTO WORKS LTDA
|
| 20
| AUTO WORKS LTDA
|
+-------------+-----------------------------------------------+
20 rows in set (0.02 sec)
Observe agora ao utilizar o alias:
mysql> select c_codivenda Cod_Venda,
(select c_razaclien
60
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
from comclien
where n_numeclien = comvenda.n_numeclien) Nome_Cliente
from comvenda;
+-------------+------------------------+
| Cod_Venda
| Nome_Cliente
|
+-------------+------------------------+
| 1
| AARONSON FURNITURE LTD |
| 2
| LITTLER LTDA
|
| 3
| KELSEY NEIGHBOURHOOD |
| 4
| GREAT AMERICAN MUSIC
|
| 5
| LIFE PLAN COUNSELLING |
| 6
| PRACTI-PLAN LTDA
|
| 7
| SPORTSWEST LTDA
|
| 8
| HUGHES MARKETS LTDA
|
| 9
| AUTO WORKS LTDA
|
| 10
| AARONSON FURNITURE LTD |
| 11
| AARONSON FURNITURE LTD |
| 12
| LITTLER LTDA
|
| 13
| KELSEY NEIGHBOURHOOD |
| 14
| KELSEY NEIGHBOURHOOD |
| 15
| LIFE PLAN COUNSELLING |
| 16
| PRACTI-PLAN LTDA
|
| 17
| SPORTSWEST LTDA
|
| 18
| HUGHES MARKETS LTDA
|
| 19
| AUTO WORKS LTDA
|
| 20
| AUTO WORKS LTDA
|
+-------------+------------------------+
20 rows in set (0.00 sec)
Portanto, se você for mostrar para um usuário esse resultado, utilize o
alias, pois, muito provavelmente, ele não saberá o que significa a sintaxe de
um select.
61
5.3. Traga informação de várias tabelas com Joins
Casa do Código
5.3
Traga informação de várias tabelas com
Joins
Até agora, selecionamos dados de apenas uma tabela. Ao fazer um relatório,
as informações possivelmente estarão em várias delas. Para fazer um consulta
em mais de uma, nós utilizamos os chamados JOINs. Há sintaxes diferentes
para escrevê-lo. Utilizarei a mais comum e mais simples. No repositório, há
o arquivo consultas.sql, que contém várias outras consultas para você
estudar e utilizar como exemplo.
Nos bancos de dados relacionais, ao você consultar duas tabelas que pos-
suem algum tipo de relacionamento, você deve especificar de qual tabela são
esses campos. Vamos pegar como exemplo as tabelas de vendas e de clien-
tes. Temos uma coluna que é igual entre elas: a chave primária da tabela de
cliente e o n_numeclien. Com isso, temos comvenda.n_numeclien =
comclien.n_numeclien. Veja que, além de fazer a igualdade entre as co-
lunas, deve-se especificar também à qual tabela pertence cada campo.
Entendemos o funcionamento do JOIN, então agora podemos fazer uma
extração de dados, relacionando as vendas com os clientes. Ao retirar um rela-
tório de um sistema com muitos registros, eles devem estar organizados por
alguma sequência, seja esta ordenada pelo código ou pelo nome do cliente,
uma vez que fica estranho e ruim de ler um relatório que não esteja organi-
zado. No SQL, também temos um comando para ordenar as consultas. Para
isso, temos o order by. Ordenando pela razão social do cliente, o nosso
código ficará da seguinte maneira:
mysql> select c_codiclien, c_razaclien, c_codivenda Cod_Venda
from comvenda, comclien
where comvenda.n_numeclien = comclien.n_numeclien
order by c_razaclien;
+-------------+------------------------+-----------+
| c_codiclien | c_razaclien
| Cod_Venda |
+-------------+------------------------+-----------+
| 0001
| AARONSON FURNITURE LTD | 10
|
| 0001
| AARONSON FURNITURE LTD | 1
|
| 0001
| AARONSON FURNITURE LTD | 11
|
62
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
| 0009
| AUTO WORKS LTDA
| 20
|
| 0009
| AUTO WORKS LTDA
| 19
|
| 0009
| AUTO WORKS LTDA
| 9
|
| 0004
| GREAT AMERICAN MUSIC
| 4
|
| 0008
| HUGHES MARKETS LTDA
| 18
|
| 0008
| HUGHES MARKETS LTDA
| 8
|
| 0003
| KELSEY NEIGHBOURHOOD | 13
|
| 0003
| KELSEY NEIGHBOURHOOD | 3
|
| 0003
| KELSEY NEIGHBOURHOOD | 14
|
| 0005
| LIFE PLAN COUNSELLING | 5
|
| 0005
| LIFE PLAN COUNSELLING | 15
|
| 0002
| LITTLER LTDA
| 12
|
| 0002
| LITTLER LTDA
| 2
|
| 0006
| PRACTI-PLAN LTDA
| 16
|
| 0006
| PRACTI-PLAN LTDA
| 6
|
| 0007
| SPORTSWEST LTDA
| 7
|
| 0007
| SPORTSWEST LTDA
| 17
|
+-------------+------------------------+-----------+
20 rows in set (0.01 sec)
A maneira mais formal de escrever uma consulta com JOIN é como
está apresentado a seguir. Porém, não é a mais comum e utilizada no
dia a dia, pois o código fica um pouco mais complexo. Há uma grande
discussão sobre desempenho das consultas na maneira como é escrita.
Não entrarei no mérito desta. Durante o livro, utilizarei a sintaxe mais
popular, que é a que apresentei anteriormente.
mysql> select c_codiclien codigo, c_razaclien razao_social,
c_codivenda codi_venda
from comvenda
join comclien on
comvenda.n_numeclien = comclien.n_numeclien
order by c_razaclien;
63
5.4. Select em: create table, insert, update e delete
Casa do Código
Atenção: ao utilizar várias tabelas para fazer uma consulta, você deve
sempre fazer a igualdade entre as que possuem constraint, pois, caso
contrário, o SGBD se perderá e retornará os dados duplicados.
5.4
Select em: create table, insert, update e de-
lete
Aprendemos a criar tabelas e a inserir e deletar registros no banco de dados
por meio de comandos adequados. Porém, agora que aprendemos a realizar
consultas, podemos utilizar o select para nos auxiliar nessas operações.
Algo que pode ser muito útil em nosso dia a a dia, em que buscamos o máximo
de produtividade.
Criando tabelas por meio de select
Surgiu a necessidade de criarmos uma tabela chamada comclien_bkp
com a mesma estrutura e dados da comclien, onde o c_estaclien seja
igual a ‘SP’. Podemos realizar algumas operações com esses registros e, por
segurança, não usaremos os dados da tabela original.
Essa situação, na qual você precisa isolar alguns registros utilizando al-
gum tipo de filtro para trabalhar com eles sem afetar a tabela que está em
produção, é muito comum de encontrar. Em nosso cenário, este filtro será o
c_estaclien igual a ‘SP’. Se você ainda não inseriu os registros, baixe o
arquivo popula_banco.sql do repositório e aplique-os em seu banco.
mysql> create table comclien_bkp as(
select *
from comclien
where c_estaclien = 'SP');
Query OK, 3 rows affected (0.70 sec)
Records: 3 Duplicates: 0 Warnings: 0
Inserindo registros por meio de select
Constantemente, surge a necessidade de inserir registros em alguma ta-
64
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
bela, a fim de realizar algum processo no banco de dados. Às vezes, já temos
esses dados em outra tabela e, com isso, em vez de criarmos scripts para inseri-los, nós podemos utilizar um select para buscar o que temos e colocar em
nossa nova tabela.
Em nosso projeto, apareceu a necessidade da criação uma tabela para
agenda telefônica. Ela terá como base alguns campos da tabela de clientes e
todos eles serão cadastrados nela também. Veremos como esse processo será
feito automaticamente no capítulo 7, mas para iniciarmos, vamos inserir os
clientes que possuímos atualmente por meio de um select. Observe tam-
bém que criarei um campo n_nnumeclien que será a foreign key da tabela
de clientes, porém vou criá-la sem a obrigatoriedade de ser preenchida, uma
vez que podemos ter contatos que não serão necessariamente um cliente. Isso
é comum em sistemas.
mysql> create table comcontato(
n_numecontato int not null auto_increment,
c_nomecontato varchar(200),
c_fonecontato varchar(30),
c_cidacontato varchar(200),
c_estacontato varchar(2),
n_numeclien
int,
primary key(n_numecontato));
Query OK, 0 rows affected (2.07 sec)
Agora vamos popular as colunas da nossa tabela comcontato com essas
informações que temos da tabela comclien.
mysql> insert into comcontato(
select n_numeclien,
c_nomeclien,
c_foneclien,
c_cidaclien,
c_estaclien,
n_numeclien
from comclien);
Query OK, 10 rows affected (0.16 sec)
Records: 10 Duplicates: 0 Warnings: 0
65
5.4. Select em: create table, insert, update e delete
Casa do Código
Para visualizar os registros da nossa tabela, faça um select simples para
listá-los.
mysql> select * from comcontato;
+---------------+-----------------------+----------------
| n_numecontato | c_nomecontato
| c_fonecontato
+---------------+-----------------------+----------------
|
1 | AARONSON FURNITURE
| (21) 8167-6584
|
2 | LITTLER
| (27) 7990-9502
|
3 | KELSEY NEIGHBOURHOOD | (11) 4206-9703
|
4 | GREAT AMERICAN MUSIC | (75) 7815-7801
|
5 | LIFE PLAN COUNSELLING | (17) 4038-9355
|
6 | PRACTI-PLAN
| (28) 2267-6159
|
7 | SPORTSWEST
| (61) 4094-7184
|
8 | HUGHES MARKETS
| (21) 7984-9809
|
9 | AUTO WORKS
| (21) 8548-5555
|
10 | DAHLKEMPER
| (11) 4519-7670
+---------------+-----------------------+----------------
+-------------------------+---------------+-------------+
| c_cidacontato
| c_estacontato | n_numeclien |
+-------------------------+---------------+-------------+
| QUEIMADOS
| RJ
|
1 |
| SERRA
| ES
|
2 |
| BRAGANÇA PAULISTA
| SP
|
3 |
| SANTO ANTÔNIO DE JESUS | BA
|
4 |
| BEBEDOURO
| SP
|
5 |
| CACHOEIRO DE ITAPEMIRI | ES
|
6 |
| TAGUATINGA
| DF
|
7 |
| RIO DE JANEIRO
| RJ
|
8 |
| RIO DE JANEIRO
| RJ
|
9 |
| SÃO PAULO
| SP
|
10 |
+-------------------------+---------------+-------------+
10 rows in set (0.00 sec)
Alterando registros por meio de select
Neste momento, descobrimos que os contatos dos cliente que estão
na tabela comclien_bkp, na verdade, possuem o contato em outra ci-
66
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
dade e estado, diferente dos dados que estão na comclien. Ainda utili-
zando um select, faremos um update nos campos c_cidacontato
e c_estacontato, buscando os registros da tabela comclien_bkp e alte-
rando a comcontato.
mysql> update comcontato set c_cidacontato = 'LONDRINA',
c_estacontato = 'PR'
where n_numeclien in ( select n_numeclien
from comclien_bkp);
Query OK, 3 rows affected (0.31 sec)
Rows matched: 3 Changed: 3 Warnings: 0
Deletando registros por meio de select
Como eu sempre digo: você deve estar preparado para realizar mudanças
em seu projeto. Para isso, devemos conhecer o que podemos fazer com o que o
MySQL nos fornece. Por exemplo, agora temos a necessidade de deletar todos
os registros da tabela comcontato, pois os contatos não possuem registros
na tabela comvenda; ou seja, aqueles que não possuem nenhuma venda.
mysql> delete from comcontato
where n_numeclien not in (select n_numeclien
from comvenda );
Query OK, 1 rows affected (0.09 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
Agora, se consultarmos a tabela comcontato, não veremos o contato
que não possuía nenhum registro na comvenda.
mysql> select * from comcontato;
+---------------+-----------------------+----------------
| n_numecontato | c_nomecontato
| c_fonecontato
+---------------+-----------------------+----------------
|
1 | AARONSON FURNITURE
| (21) 8167-6584
|
2 | LITTLER
| (27) 7990-9502
67
5.4. Select em: create table, insert, update e delete
Casa do Código
|
3 | KELSEY NEIGHBOURHOOD | (11) 4206-9703
|
4 | GREAT AMERICAN MUSIC | (75) 7815-7801
|
5 | LIFE PLAN COUNSELLING | (17) 4038-9355
|
6 | PRACTI-PLAN
| (28) 2267-6159
|
7 | SPORTSWEST
| (61) 4094-7184
|
8 | HUGHES MARKETS
| (21) 7984-9809
|
9 | AUTO WORKS
| (21) 8548-5555
+---------------+-----------------------+----------------
+-------------------------+
| c_cidacontato
|
+-------------------------+
| QUEIMADOS
|
| SERRA
|
| LONDRINA
|
| SANTO ANTÔNIO DE JESUS |
| LONDRINA
|
| CACHOEIRO DE ITAPEMIRI |
| TAGUATINGA
|
| RIO DE JANEIRO
|
| RIO DE JANEIRO
|
+-------------------------+
+---------------+---------------+-------------+
| n_numecontato | c_estacontato | n_numeclien |
+---------------+---------------+-------------+
|
1 | RJ
|
1 |
|
2 | ES
|
2 |
|
3 | PR
|
3 |
|
4 | BA
|
4 |
|
5 | PR
|
5 |
|
6 | ES
|
6 |
|
7 | DF
|
7 |
|
8 | RJ
|
8 |
|
9 | RJ
|
9 |
+---------------+---------------+-------------+
9 rows in set (0.00 sec)
68
Casa do Código
Capítulo 5. Temos registros: vamos consultar?
Utilize o máximo que seu SGBD tem a oferecer. Não perca tempo mon-
tando scripts desnecessários se você tem de usar dados que já estão no banco.
Todos esses comandos, além de estarem em nosso repositório, estão no
final do livro reunidos no guia de consulta rápida, cada um com sua sintaxe
e seu significado.
Neste capítulo, aprendemos a consultar os registro do nosso SGBD, algu-
mas consultas simples e outras um pouco mais complexas.
A dica que deixo é praticar bastante. Como todas as linguagens de pro-
gramação, o SQL também é uma questão de prática. Com o tempo, você não
precisará pensar qual comando deve utilizar, apenas vai fazê-lo. Agora, no
próximo capítulo, vamos aprender algumas funções para usarmos em nossas
consultas.
69
Capítulo 6
Consultas com funções
“A vida é como um jardim. Momentos perfeitos podem ser desfrutados, mas
não preservados, exceto na memória.”
– Leonard Nimoy
6.1
Funções
No MySQL, existem várias funções nativas que nos possibilitam fazer diversas
operações, dentre elas: realizar cálculos, manipular strings, trabalhar com datas, realizar opções lógicas, extrair informações dos nossos registros etc. Elas estão divididas nos seguintes tipo: numéricas, lógica, manipulação de string
e funções de data e hora.
Explicarei as funções principais e quais você mais utilizará em seu dia a
dia. Exemplificarei conforme surgir a necessidade de cada uma. Vale lembrar
6.2. Funções de agregação
Casa do Código
que essas funções e operadores podem ser utilizados em qualquer cláusula
sql, exceto na from.
6.2
Funções de agregação
As funções de agregação são responsáveis por agrupar vários valores e retor-
nar somente um único para um determinado grupo. Por exemplo, se fizermos
um select em todos registros da tabela de vendas com join com a tabela
de clientes, vamos ter como resultado clientes repetidos. Veja:
mysql> select c_codiclien, c_razaclien
from comvenda, comclien
where comvenda.n_numeclien = comclien.n_numeclien
order by c_razaclien;
+-------------+------------------------+
| c_codiclien | c_razaclien
|
+-------------+------------------------+
| 0001
| AARONSON FURNITURE LTD |
| 0001
| AARONSON FURNITURE LTD |
| 0001
| AARONSON FURNITURE LTD |
| 0009
| AUTO WORKS LTDA
|
| 0009
| AUTO WORKS LTDA
|
| 0009
| AUTO WORKS LTDA
|
| 0004
| GREAT AMERICAN MUSIC
|
| 0008
| HUGHES MARKETS LTDA
|
| 0008
| HUGHES MARKETS LTDA
|
| 0003
| KELSEY NEIGHBOURHOOD |
| 0003
| KELSEY NEIGHBOURHOOD |
| 0003
| KELSEY NEIGHBOURHOOD |
| 0005
| LIFE PLAN COUNSELLING |
| 0005
| LIFE PLAN COUNSELLING |
| 0002
| LITTLER LTDA
|
| 0002
| LITTLER LTDA
|
| 0006
| PRACTI-PLAN LTDA
|
| 0006
| PRACTI-PLAN LTDA
|
| 0007
| SPORTSWEST LTDA
|
| 0007
| SPORTSWEST LTDA
|
72
Casa do Código
Capítulo 6. Consultas com funções
+-------------+------------------------+
20 rows in set (0.01 sec)
Alguns clientes repetem-se, pois existem aqueles que possuem mais de
uma venda. Desta maneira, poderíamos utilizar uma função de agregação
para retorná-los, evitando a repetição.
Group by
O comando SQL para fazer essa operação de agregação é o group by.
Ele deverá ser utilizado logo após as cláusulas de condições where ou and,
e antes do order by, se a sua consulta possuí-lo.
Vamos ao nosso código:
mysql> select c_codiclien, c_razaclien
from comclien, comvenda
where comvenda.n_numeclien = comclien.n_numeclien
group by c_codiclien, c_razaclien
order by c_razaclien;
+-------------+------------------------+
| c_codiclien | c_razaclien
|
+-------------+------------------------+
| 0001
| AARONSON FURNITURE LTD |
| 0009
| AUTO WORKS LTDA
|
| 0004
| GREAT AMERICAN MUSIC
|
| 0008
| HUGHES MARKETS LTDA
|
| 0003
| KELSEY NEIGHBOURHOOD |
| 0005
| LIFE PLAN COUNSELLING |
| 0002
| LITTLER LTDA
|
| 0006
| PRACTI-PLAN LTDA
|
| 0007
| SPORTSWEST LTDA
|
+-------------+------------------------+
9 rows in set (0.00 sec)
O MySQL agrupou o código e a razão social, trazendo apenas um registro
de cada. Porém, essa consulta poderia ser melhor se tivéssemos a quantidade
de vendas de cliente. Podemos utilizar uma outra função de agregação cha-
mada count() para contar os registros que estão agrupados. Ela só pode
73
6.2. Funções de agregação
Casa do Código
ser utilizada na cláusula select, pois contará os registros da coluna que
está sendo selecionada. Complementando o código anterior, teremos:
mysql> select c_codiclien, c_razaclien, count(n_numevenda) Qtde
from comclien, comvenda
where comvenda.n_numeclien = comclien.n_numeclien
group by c_codiclien, c_razaclien
order by c_razaclien;
+-------------+------------------------+------+
| c_codiclien | c_razaclien
| Qtde |
+-------------+------------------------+------+
| 0001
| AARONSON FURNITURE LTD |
3 |
| 0009
| AUTO WORKS LTDA
|
3 |
| 0004
| GREAT AMERICAN MUSIC
|
1 |
| 0008
| HUGHES MARKETS LTDA
|
2 |
| 0003
| KELSEY NEIGHBOURHOOD |
3 |
| 0005
| LIFE PLAN COUNSELLING |
2 |
| 0002
| LITTLER LTDA
|
2 |
| 0006
| PRACTI-PLAN LTDA
|
2 |
| 0007
| SPORTSWEST LTDA
|
2 |
+-------------+------------------------+------+
9 rows in set (0.00 sec)
O count pode ser usado apenas para contar a quantidade de registro em
uma tabela. Vamos substituir a coluna que estava entre parênteses no exemplo
anterior por * (asterisco), para contar todas as linhas da tabela de clientes.
mysql> select count(*)
from comclien;
+----------+
| count(*) |
+----------+
|
10 |
+----------+
1 row in set (0.05 sec)
74
Casa do Código
Capítulo 6. Consultas com funções
Having count()
Agora, em nosso projeto, temos a necessidade de fazer um relatório que
traga como resultado os clientes que tiveram mais do que duas vendas. Para
isso, utilizaremos a função having count(), que será a condição para o
seu count(). Exemplificando, temos:
mysql> select c_razaclien, count(n_numevenda)
from comclien, comvenda
where comvenda.n_numeclien = comclien.n_numeclien
group by c_razaclien
having count(n_numevenda) > 2;
+------------------------+--------------------+
| c_razaclien
| count(n_numevenda) |
+------------------------+--------------------+
| AARONSON FURNITURE LTD |
3 |
| AUTO WORKS LTDA
|
3 |
| KELSEY NEIGHBOURHOOD |
3 |
+------------------------+--------------------+
3 rows in set (0.00 sec)
Percebeu que utilizamos todos os comando SQL que aprendemos em uma
única consulta? Sempre um complementará o outro. Com o tempo, escrevê-
los ficará natural e automático, conforme sua necessidade. Tudo é uma ques-
tão de prática, como qualquer linguagem de programação.
max() e min()
Depois de uma consulta mais complexa que pode ter feito você desistir
de estudar banco de dados, vamos para uma que é um pouco mais simples.
Muitas vezes, por n motivos, surge a necessidade de retornar o maior ou me-
nor registro de uma tabela. Fazemos isso com as funções MAX() e MIN(),
respectivamente. Nos parênteses deverá ir a coluna que você deseja recuperar.
São funções simples do SQL que são de grande utilidade.
Se quisermos recuperar o valor da maior venda, nossa consulta seria:
mysql> select max(n_totavenda) maior_venda
from comvenda;
75
6.2. Funções de agregação
Casa do Código
+-------------+
| maior_venda |
+-------------+
|
25141.02 |
+-------------+
1 row in set (0.00 sec)
Já para a menor:
mysql> select min(n_totavenda) menor_venda, max(n_totavenda)
maior_venda from comvenda;
+-------------+
| menor_venda |
+-------------+
|
4650.64 |
+-------------+
Ou ainda podemos retornar os dois valores ao mesmo tempo, da seguinte
maneira:
mysql> select min(n_totavenda) menor_venda, max(n_totavenda)
maior_venda from comvenda;
+-------------+-------------+
| menor_venda | maior_venda |
+-------------+-------------+
|
4650.64 |
25141.02 |
+-------------+-------------+
1 row in set (0.00 sec)
Sum()
Temos os valores das vendas, mas é óbvio que, em algum momento, te-
remos que consultar seu total. No MySQL, podemos somar todos os valores
de uma coluna utilizando a função sum(). Como exemplo, vamos somar
os valores individualmente das colunas: n_valovenda, n_descvenda e
n_totavenda no intervalo de 01/01/2015 a 31/01/2015.
76
Casa do Código
Capítulo 6. Consultas com funções
mysql> select sum(n_valovenda) valor_venda,
sum(n_descvenda) descontos,
sum(n_totavenda) total_venda
from comvenda
where d_datavenda between '2015-01-01' and '2015-01-01';
+-------------+-----------+-------------+
| valor_venda | descontos | total_venda |
+-------------+-----------+-------------+
|
75830.72 |
0.00 |
75830.72 |
+-------------+-----------+-------------+
1 row in set (0.00 sec)
Observe que utilizamos a condição between, que serve para verificar
um intervalo entre duas variáveis, seja de datas ou numérico.
Avg()
Conseguimos extrair os valores das vendas, de sua quantidade, as maiores
e as menores etc. Porém, e se quisermos saber sua média, para comparar
períodos de datas? No MySQL temos o avg(), que busca a coluna cuja média
você deseja saber e realiza o cálculo. Vamos exemplificar consultando o valor
médio de todas as vendas:
mysql> select format(avg(n_totavenda),2)
from comvenda;
+----------------------------+
| format(avg(n_totavenda),2) |
+----------------------------+
| 12,213.96
|
+----------------------------+
1 row in set (0.00 sec)
6.3
Funções de string
As funções de string (caracteres) podem ser utilizadas para modificar os da-
dos, no que diz respeito aos valores selecionados, como também na forma
77
6.3. Funções de string
Casa do Código
como são apresentados. Ou ainda, modificá-los para uma validação. Temos
uma variedade de funções que são muito úteis, e que nos ajudam a resolver
problemas do dia a dia, como tento mostrar em cada exemplo a seguir.
substr() e length()
Agora, em nosso projeto, surgiu a necessidade de consultar os produtos
que iniciam seu código com ‘123’ e que possuem uma descrição com mais
de 4 caracteres, pois foram cadastrados de maneira errada. Poderíamos bus-
car todos os produtos, colocá-los em uma planilha e no ‘olhômetro’ encontrar
todos eles. Mas e se eu lhe disser que existem funções no SQL que podemos
utilizar para fazer esse filtro? São duas: a função SUBSTR() e a length().
Imaginem um cenário com uma tabela com mais de 1 milhão de registros.
Precisamos ter ferramentas para nos auxiliar. Diferente das outras funções
para as quais apenas passamos a coluna, para esta devemos também passar
qual o intervalo de caracteres que queremos de um determinado campo.
Por exemplo, substr(c_codiprodu,1,3) = ‘123’. Com este co-
mando, falamos para o SGBD que queremos os registros que possuem o có-
digo da posição 1 até a posição 3 com a sequência de caracteres 123. Com
a função LENGTH(), vamos contar quantos caracteres o código do produto
tem.
mysql> select c_codiprodu, c_descprodu
from comprodu
where substr(c_codiprodu,1,3) = '123'
and length(c_codiprodu) > 4;
+-------------+-------------+
| c_codiprodu | c_descprodu |
+-------------+-------------+
| 123131
| NOTEBOOK
|
| 123223
| SMARTPHONE |
+-------------+-------------+
2 rows in set (0.03 sec)
78
Casa do Código
Capítulo 6. Consultas com funções
Percebeu a importância de trabalhar com uma coluna de chave pri-
mária e uma para o código que aparecerá na tela? Você consegue traba-
lhar de diversas maneiras, pois, dependendo da regra de negócio, pode
ocorrer de dois produtos terem o mesmo código. Além de poder alterar
sem problema algum, as comparações sempre serão pela chave primária.
Utilizamos, no exemplo, o substr() e o length() para fazermos uma
validação. Poderíamos ter utilizado para apresentar os valores. Vamos seleci-
onar apenas os cinco primeiros caracteres do campo c_razaclien e contar
quantos deles temos no código do cliente. Vamos ao código:
mysql> select substr(c_razaclien,1,5) Razao_Social,
length(c_codiprodu)
Tamanho_Cod
from comclien
where n_numeclien = 1;
+-------------+--------------+
| Razao_Social | Tamanho_Cod |
+-------------+--------------+
| AARON
|
6 |
+-------------+--------------+
1 rows in set (0.00 sec)
Concat() e concat_ws()
Queremos agora listar os clientes concatenando a razão social e o telefone.
Temos a função concat() que concatena dois ou mais campos. Deve-se
apenas colocá-los entre parênteses, separados por vírgula.
mysql> select concat(c_razaforne,' - fone: ', c_foneforne)
from comforne
order by c_razaforne;
+-------------------------------------------------------+
| concat(c_razaforne,' - fone: ', c_foneforne)
|
+-------------------------------------------------------+
79
6.3. Funções de string
Casa do Código
| DUN RITE LAWN MAINTENANCE LTDA - fone: (85) 7886-8837 |
| SEWFRO FABRICS LTDA - fone: (91) 5171-8483
|
| WISE SOLUTIONS LTDA - fone: (11) 5347-5838
|
+-------------------------------------------------------+
3 rows in set (0.04 sec)
Por alguma necessidade, precisamos fazer consultas e concatenar mais de
um campo. O MySQl nos permite fazer isso através das funções concat()
e concat_ws(). Com o concat() será para concatenar todos os cam-
pos da consulta sem especificar um separador entre os campos; já com o
concat_ws() devemos dizer qual será o separador entre eles. Vamos aos
exemplos:
mysql> select
concat(c_codiclien,' ',c_razaclien, ' ', c_nomeclien)
from comclien
where c_razaclien like 'GREA%';
+---------------------------------------------------------+
| concat(c_codiclien,' ',c_razaclien, ' ', c_nomeclien) |
+---------------------------------------------------------+
| 0004 GREAT AMERICAN MUSIC GREAT AMERICAN MUSIC
|
+---------------------------------------------------------+
1 row in set (0.00 sec)
Olhando para o resultado, observe que nós separamos os campos com du-
plo espaço. Poderíamos fazer isso utilizando algum caractere especial, como
com ponto e vírgula ( ;):
mysql> select
concat_ws(';',c_codiclien, c_razaclien, c_nomeclien)
from comclien
where c_razaclien like 'GREA%';
+------------------------------------------------------+
| concat_ws(';',c_codiclien, c_razaclien, c_nomeclien) |
+------------------------------------------------------+
| 0004;GREAT AMERICAN MUSIC;GREAT AMERICAN MUSIC
|
+------------------------------------------------------+
1 row in set (0.00 sec)
80
Casa do Código
Capítulo 6. Consultas com funções
Observe que agora apenas declaramos qual o separador queríamos e o
SGBD colocou-o entre os campos.
Lcase() e lower()
Se você fizer uma consulta em nosso banco, vai perceber que alguns re-
gistros estão em letras maiúsculas. Se você necessitar, em algum lugar de sua
aplicação, dos registros em letras minúsculas, o MySQL também tem uma
função para auxiliá-lo. Utilize o lcase ou o lower da seguinte maneira:
mysql> select lcase(c_razaclien)
from comclien;
+------------------------+
| lcase(c_razaclien)
|
+------------------------+
| aaronson furniture ltd |
| auto works ltda
|
| dahlkemper ltda
|
| great american music
|
| hughes markets ltda
|
| kelsey neighbourhood |
| life plan counselling |
| littler ltda
|
| practi-plan ltda
|
| sportswest ltda
|
+------------------------+
10 rows in set (0.00 sec)
Ucase()
Da mesma maneira que podemos retornar os registros de forma minús-
cula, podemos também de forma maiúscula. Utilize a função ucase. Vamos
consultar:
mysql> select ucase('banco de dados mysql')
from dual;
+-------------------------------+
81
6.4. Funções de cálculos e operadores aritméticos
Casa do Código
| ucase('banco de dados mysql') |
+-------------------------------+
| BANCO DE DADOS MYSQL
|
+-------------------------------+
1 row in set (0.07 sec)
6.4
Funções de cálculos e operadores aritmé-
ticos
Funções de cálculos, como a descrição já diz, são utilizadas para realizar ope-rações de cálculos. É de grande utilidade ter essas funções, pois, assim, não
é preciso realizá-los usando apenas operadores comuns. Por exemplo, temos
desde uma função para realizar o cálculo da raiz quadrada até uma que re-
torna a tangente de Pi!
Round()
Quando criamos a tabela, nós especificamos que os campos do tipo
float() seriam limitados em duas casas decimais. Porém, você pode se de-
parar com outros banco de dados que não estão limitados e possuem campos
com registros com mais de duas casas. Para esses casos, nós temos a fun-
ção round(), utilizada para arredondar valores. Você pode especificar para
quantas casas decimais quer arredondar. Vamos exemplificar utilizando no-
vamente a tabela dual:
mysql> select round('213.142',2)
from dual;
+--------------------+
| round('213.142',2) |
+--------------------+
|
213.14 |
+--------------------+
1 row in set (0.00 sec)
Ou ainda, outra alternativa para arredondar um valor é utilizando o
format. Ele não faz um arredondamento, mas formata o valor para pare-
82
Casa do Código
Capítulo 6. Consultas com funções
cer com apenas as casas decimais desejadas. Apenas substitua o round por
format, pois além de formatá-las, ele formatará também todo o número.
mysql> select format('21123.142',2) from dual;
+-----------------------+
| format('21123.142',2) |
+-----------------------+
| 21,123.14
|
+-----------------------+
1 row in set (0.00 sec)
Truncate
Utilizamos o round para arredondar e o format para arredondar e
formatar o número. Temos a opção de utilizar uma função que vai truncar as
casas decimais, ou seja, omiti-las. Exemplificando:
mysql> select truncate(max(n_totavenda),0) maior_venda
from comvenda;
+-------------+
| maior_venda |
+-------------+
|
25141 |
+-------------+
1 row in set (0.01 sec)
Dependendo da situação com que você está lidando, você poderá deixar
alguma casa decimal. Basta substituir o número zero que coloquei pelo nú-
mero de casas decimais que deseja truncar. Por exemplo:
mysql> select truncate(min(n_totavenda),1) menor_venda
from comvenda;
+-------------+
| menor_venda |
+-------------+
|
4650.6 |
83
6.4. Funções de cálculos e operadores aritméticos
Casa do Código
+-------------+
1 row in set (0.01 sec)
Sqrt()
Dependendo do projeto com que você está trabalhando, a necessidade de
se obter a raiz quadrada para realizar alguma operação pode aparecer. Graças
ao MySQL, não precisamos escrever mais que duas linhas de código para se
obter a raiz quadrada de um número. Isso não é fantástico? Vamos ao código:
mysql> select sqrt(4);
+---------+
| sqrt(4) |
+---------+
|
2 |
+---------+
1 row in set (0.00 sec)
Pi, seno, cosseno e tangente
Outras funções interessantes de que o MySQL dispõe são as funções: seno,
cosseno e tangente. Lembra-se delas? E do número Pi? Temos as calculadoras
que nos fornecem esses valores ao apertar de um botão, mas, muitas vezes, é
necessário a utilização desses cálculos no desenvolvimento de sistemas. Para
facilitar nossas vidas, temos essas funções em apenas uma linha de código.
Vamos ao código:
Para consultar o valor de Pi:
mysql> select pi();
+----------+
|
pi()
|
+----------+
| 3.141593 |
+----------+
1 row in set (0.00 sec)
Para consultar o valor de seno de Pi:
84
Casa do Código
Capítulo 6. Consultas com funções
mysql> select sin(pi());
+------------------+
| round(sin(pi())) |
+- ----------------+
|
0 |
+------------------+
1 row in set (0.00 sec)
Para consultar o valor de cosseno de Pi:
mysql> select cos(pi());
+-----------+
| cos(pi()) |
+-----------+
|
-1 |
+-----------+
1 row in set (0.00 sec)
Para consultar o valor da tangente de Pi + 1:
mysql> select tan(pi()+1);
+-----------------+
|
tan(pi()) |
+-----------------+
| 1.5574077246549 |
+-----------------+
1 row in set (0.00 sec)
Caso você tente fazer uma operação que não é permitida pela função,
o MySQL retornará um erro de sintaxe, como esse da próxima consulta de
exemplo, na qual tentei retornar a tangente de pi()+(a). Visto que é uma
função numérica, não aceitará caracteres string.
mysql> select tan(pi()+(A));
ERROR 1064 (42000): You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the
right syntax to use near '' at line 1
85
6.5. Operadores aritméticos
Casa do Código
Dica: este erro aparecerá em qualquer consulta quando um erro
de sintaxe ocorrer.
Uma dica é copiar seu nome,
(ERROR 1064
(42000)), e procurá-lo no Google com a ação que você estiver fazendo.
Exemplo: ERROR 1064 (42000) tan MySQL. Por ser um erro genérico, ao
pesquisar também o que você está tentando fazer, a chance de encontrar
a resposta será maior.
6.5
Operadores aritméticos
Utilizamos os operadores aritméticos quando temos que realizar cálculos para
os quais não possuímos uma função para a operação, ou se for necessário
fazer cálculos de operações para os quais já exista uma função. A seguir, veja os operadores em sua sequência de prioridade:
• * : multiplicação;
• / : divisão;
• + : adição;
• - : subtração.
Para realizar os cálculos, devemos utilizá-los na cláusula select, como
as demais funções. Exemplificando: vamos multiplicar a quantidade de um
produto de uma venda por seu valor. Assim, teremos o valor total de um item.
mysql> select (n_qtdeivenda * n_valoivenda) multiplicação
from comivenda
where n_numeivenda = 4;
+-----------------+
| multiplicação
|
+-----------------+
|
41038.72 |
+-----------------+
1 row in set (0.00 sec)
86
Casa do Código
Capítulo 6. Consultas com funções
Agora vamos somar todos os valores de produtos dos itens das vendas e
dividir pelo número de itens vendidos.
mysql> select truncate((sum(n_valoivenda) /
count(n_numeivenda)),2) divisão
from comivenda;
a
+----------+
| divisão |
+----------+
| 6855.35 |
+----------+
1 row in set (0.00 sec)
Utilizando o item de venda com o n_numeivenda igual a 4, vamos so-
mar o valor do item com o valor do desconto:
mysql> select (n_valoivenda + n_descivenda) adição
from comivenda
where n_numeivenda = 4;
+----------+
| adição
|
+----------+
| 10259.68 |
+----------+
1 row in set (0.00 sec)
Fazendo o inverso, vamos subtrair o valor do desconto do item:
mysql> select (n_valoivenda - n_descivenda) subtração
from comivenda
where n_numeivenda = 4;
+-------------+
| subtração
|
+-------------+
|
10259.68 |
+-------------+
1 row in set (0.00 sec)
87
6.6. Funções de data
Casa do Código
6.6
Funções de data
Trabalhar com data em banco de dados pode tornar-se um grande problema,
principalmente pela questão do padrão de datas em alguns sistemas. É muito
comum você conhecer desenvolvedores que já tiveram problemas ou estão
tendo, porque cada linguagem de programação trata a data de maneira dife-
rentes. Se você souber manipulá-la da maneira correta, você terá sucesso.
Para nos auxiliar, o MySQL fornece funções com as quais podemos
manipulá-las juntamente com o tempo. Vale lembrar que o MySQL utiliza
o padrão americano: YYYY-MM-DD (ano, mês e dia). Na sequência, mostro
como utilizar uma função para selecionar as datas em outro padrão. Vamos
conhecer algumas delas.
Para retornar a data, hora ou data/hora atual, existem algumas maneiras
de se fazer:
• CURDATE(): para retornar a data atual, somente. Por exemplo:
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2015-03-03 |
+------------+
1 row in set (0.02 sec)
• now(): para retornar a data e a hora atual. Por exemplo:
mysql>select now();
+---------------------+
| now()
|
+---------------------+
| 2015-03-03 13:03:11 |
+---------------------+
1 row in set (0.00 sec)
• sysdate(): igualmente ao now(), sua consulta retorna a data e a
hora juntos. Por exemplo:
88
Casa do Código
Capítulo 6. Consultas com funções
mysql>select sysdate();
+---------------------+
| sysdate()
|
+---------------------+
| 2015-03-03 13:03:11 |
+---------------------+
1 row in set (0.00 sec)
• curtime(): para retornar somente o horário atual. Por exemplo:
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 12:56:36 |
+-----------+
1 row in set (0.00 sec)
Podemos também retornar o intervalo entre duas datas:
mysql> select datediff('2015-02-01 23:59:59','2015-01-01');
+----------------------------------------------+
| datediff('2015-02-01 23:59:59','2015-01-01') |
+----------------------------------------------+
|
31 |
+----------------------------------------------+
1 row in set (0.00 sec)
E adicionar dias a uma data:
mysql>select date_add('2013-01-01', interval 31 day);
+-----------------------------------------+
| date_add('2013-01-01', interval 31 day) |
+-----------------------------------------+
| 2013-02-01
|
+-----------------------------------------+
1 row in set (0.00 sec)
89
6.6. Funções de data
Casa do Código
A função de selecionar o nome do dia da semana é muito útil. Você re-
tornará o nome do dia da semana em vez de apenas a data com números, na
tela para o seu usuário.
mysql> select dayname('2015-01-01');
+-----------------------+
| dayname('2015-01-01') |
+-----------------------+
| thursday
|
+-----------------------+
1 row in set (0.00 sec)
Para retornar o dia do mês:
mysql> select dayofmonth('2015-01-01');
+--------------------------+
| dayofmonth('2015-01-01') |
+--------------------------+
|
1 |
+--------------------------+
1 row in set (0.02 sec)
Extrair o ano de uma data:
mysql> select extract(year from '2015-01-01');
+---------------------------------+
| extract(year from '2015-01-01') |
+---------------------------------+
|
2015 |
+---------------------------------+
1 row in set (0.00 sec)
Extrair o último dia do mês:
mysql> select last_day('2015-02-01');
+------------------------+
90
Casa do Código
Capítulo 6. Consultas com funções
| last_day('2015-02-01') |
+------------------------+
| 2015-02-28
|
+------------------------+
1 row in set (0.00 sec)
Formatando datas
Podemos fazer algumas formatações para apresentar as datas nas consul-
tas.
Um padrão de data que utilizaremos bastante é o EUR (DD.MM.YYYY),
pois ele é parecido com o nosso. Porém, lembre-se que é apenas para for-
matarmos durantes nossas consultas. Veja o exemplo onde vamos formatar a
data ‘2015-01-10’:
mysql> select date_format('2015-01-10',get_format(date,'EUR'));
+--------------------------------------------------+
| date_format('2015-01-01',get_format(date,'EUR')) |
+--------------------------------------------------+
| 10.01.2015
|
+--------------------------------------------------+
1 row in set (0.00 sec)
Você pode deparar-se com situações nas quais, por exemplo, tem de fazer
uma migração de dados para o seu banco MySQL, mas os campos de data
deste outro estão em um formato diferente e como tipo texto. Com isso,
você terá de converter o campo para tipo data e para um formato compa-
tível com o seu banco. Para converter de texto para data, utilizaremos a fun-
ção str_to_date e, em seguida, passaremos para o nosso formato. Veja o
exemplo:
mysql> select str_to_date('01.01.2015',get_format(date,'USA'));
+--------------------------------------------------+
| str_to_date('01.01.2015',get_format(date,'USA')) |
+--------------------------------------------------+
| 2015-01-01
|
+--------------------------------------------------+
1 row in set (0.00 sec)
91
6.6. Funções de data
Casa do Código
Não se preocupe em aprender todas as funções de uma só vez. Você as-
similará conforme forem surgindo suas necessidades, pois esta é a melhor
forma de se aprender a programar. Neste capítulo, aprendemos as funções
mais utilizadas, o que já lhe possibilita fazer n tipos de consultas. Agora vamos aprender algo um pouco mais complexo e deixar o processamento das
informações no nosso potente SGBD.
92
Capítulo 7
Deixar o banco processar:
procedures e functions
“Notícias ruins não são como os vinhos. Não melhoram com a idade.”
– Colin Powell
Os SGBDs são uma poderosa ferramenta de processamento de dados.
Além de sua capacidade de gerenciar uma grande quantidade de dados, ele
também é o mais rápido em comparação com aplicações escritas em outras
linguagens.
Dependendo da rotina a ser executada, isso pode requerer várias consul-
tas e atualizações na base, o que acarretará um maior consumo de recursos
pela aplicação. No caso de aplicações web, isso se torna ainda mais visível, de-vido a maior quantidade de informações que precisam trafegar pela rede e de
requisições ao servidor. Uma boa forma de contornar ou, ao menos atenuar,
7.1. Deixando o banco processar com stored procedures
Casa do Código
esse consumo de recursos é transferir parte do processamento direto para o
banco de dados, considerando que as máquinas servidoras geralmente têm
configurações de hardware mais robustas. Entretanto, nada se pode garantir
em relação às máquinas dos clientes.
A questão em que esbarramos é: como executar várias ações no banco de
dados a partir de uma única instrução? A resposta para essa pergunta resume-
se a stored procedures. Stored procedures são rotinas definidas no banco de
dados, identificadas por um nome pelo qual podem ser invocadas. Este pro-
cedimento pode receber parâmetros, retornar valores e executar uma sequên-
cia de instruções, por exemplo: fazer update em uma tabela e, em sequência,
inserir em outra e retornar um resultado de uma conta para sua aplicação.
7.1
Deixando o banco processar com stored
procedures
Agora, em nosso sistema, temos a necessidade de criar um campo para ar-
mazenar o valor da comissão para cada venda. Esse valor será baseado na
porcentagem de comissão que cada vendedor tem que ganhar, que estará ca-
dastrada em um outro campo que também vamos criar na tabela de vendedo-
res. Devemos criar esses dois campos utilizando o comando alter table
que já aprendemos: o campo n_porcvende na tabela de vendedores e o
n_vcomvenda na de vendas:
mysql> alter table comvende add n_porcvende float(10,2);
mysql> alter table comvenda add n_vcomvenda float(10,2);
Gerados os campos, vamos criar a nossa storage procedure que de-
verá buscar o valor da porcentagem de cada vendedor, realizar o processa-
mento e, na sequência, fazer um update na coluna de valor da comissão na
tabela de vendas.
Utilizaremos a procedure para fazer esse update, pois, em nosso cená-
rio, já tínhamos criado o banco sem essas colunas e o nosso sistema já está em produção, isto é, estamos supondo que há pessoas utilizando-o. Isso ocorre a
todo momento no desenvolvimento de sistemas. Há sempre a necessidade de
novas colunas e novos processos. Por isso, devemos criar meios para adequar
o nosso sistema. Veja como ficará:
94
Casa do Código
Capítulo 7. Deixar o banco processar: procedures e functions
mysql> delimiter $$
mysql>create procedure processa_comissionamento(
in data_inicial
date,
in data_final
date ,
out total_processado int )
begin
declare total_venda
float(10,2) default 0;
declare venda
int
default 0;
declare vendedor
int
default 0;
declare comissao
float(10,2) default 0;
declare valor_comissao float(10,2) default 0;
declare aux
int
default 0;
## cursor para buscar os registros a serem
## processados entre a data inicial e data final
## e valor total de venda é maior que zero
declare busca_pedido cursor for
select n_numevenda,
n_totavenda,
n_numevende
from comvenda
where d_datavenda between data_inicial
and data_final
and n_totavenda > 0 ;
## abro o cursor
open busca_pedido;
## inicio do loop
vendas: LOOP
##recebo o resultado da consulta em cada variável
fetch busca_pedido into venda, total_venda,
vendedor;
## busco o valor do percentual de cada vendedor
select n_porcvende
into comissao
95
7.1. Deixando o banco processar com stored procedures
Casa do Código
from comvende
where n_numevende = vendedor;
## verifico se o percentual do vendedor é maior
## que zero logo após a condição deve ter o then
if (comissao > 0 ) then
## calculo o valor da comissao
set valor_comissao =
((total_venda * comissao) / 100);
## faço o update na tabela comvenda com o
## valor da comissão
update comvenda set
n_vcomvenda = valor_comissao
where n_numevenda = vendedor;
commit;
## verifico se o percentual do vendedor é igual
## zero na regra do nosso sistema se o vendedor
## tem 0 ele ganha 0 porcento de comissão
elseif(comissao = 0) then
update comvenda set n_vcomvenda = 0
where n_numevenda = vendedor;
commit;
## se ele não possuir registro no percentual de
## comissão ele irá ganhar 1 de comissão
## isso pela regra de negócio do nosso sistema
else
set comissao = 1;
set valor_comissao =
((total_venda * comissao) / 100);
update
comvenda set n_vcomvenda = valor_comissao
where n_numevenda = vendedor;
commit;
## fecho o if
96
Casa do Código
Capítulo 7. Deixar o banco processar: procedures e functions
end if;
set comissao = 0;
##utilizo a variável aux para contar a quantidade
set aux
= aux +1 ;
end loop vendas;
## atribuo o total de vendas para a variável de
## saída
set total_processado = aux;
## fecho o cursor
close busca_pedido;
##retorno o total de vendas processadas
end$$
mysql>delimiter ;
Em nossa procedure, teremos parâmetros de entrada declarados com
in, e de saída declarados com out, ambos na frente. Observe no código que,
logo após o begin, eu faço a declaração das variáveis que utilizarei usando
o declare. Já o declare que possui uma consulta embaixo é o que cha-
mamos de cursor. Vamos utilizá-lo para recuperar registros por meio de
consultas dentro de uma procedure ou function, como veremos mais à
frente, e para fazer a busca das vendas que queremos processar.
Os passos para utilizar o cursor são:
• open nome_cursor: para abrir o cursor, que fará com que ele execute
a consulta;
• fetch nome_cursor into: para atribuir o retorno do cursor a uma
variável;
• close nome_cursor:
quando terminarmos de utilizá-lo, devemos
fechá-lo.
Observe na procedure que utilizo uma estrutura de controle: o loop.
Ele permitirá, pela quantidade de registros, meu cursor abrir, fazer o cálculo 97
7.1. Deixando o banco processar com stored procedures
Casa do Código
necessário para se obter o resultado da comissão e executar update na tabela.
Vamos ver também o que ainda não tínhamos visto em SQL: a utilização de
condições if, elesif e else.
Diferentemente de outras linguagens de programação que só possuem o
if e else, no SQL também temos o elesif, que deve ser utilizado quando
você precisa colocar mais uma condição antes do else. Além disso, não
podemos esquecer que, ao encerrar a cláusula if, é preciso colocar end
if;.
Observação: como padrão, o delimiter do MySQL é o ponto e
vírgula ( ;). Se você também estiver utilizando o prompt do MySQL
para executar seus códigos, deve alterá-lo para outro caractere, uma vez
que, no meio da procedure, temos vários ponto e vírgulas. Assim,
precisamos dizer ao gerenciador onde é o final da nossa instrução. Em
nosso exemplo, eu escolhi o $$ para ser o delimitador temporário e, logo
em seguida, voltei para o bom e velho ponto e vírgula.
Temos três parâmetros que devem ser passados ao executar a nossa
procedure: dois de entrada e um de saída. O de saída poderá ser recuperado
utilizando o select. Para executar este processo, utilizaremos o comando
call, passaremos os parâmetros entre parênteses e, em seguida, recuperare-
mos o retorno, da seguinte maneira:
mysql> call
processa_comissionamento('2015-01-01','2015-05-30' ,@a);
mysql> select @a;
Para você recriar essa procedure ou excluí-la, aplicando no SGBD no-
vamente, você deve fazê-lo utilizando o mesmo comando para deletar. Em
vez de drop table, será drop procedure. Assim, temos:
mysql> drop procedure processa_comissionamento;
Nossa primeira procedure ficou grande, pois queria colocar a maior
quantidade de elementos possíveis: alguns novos e outros que aprendemos
durante o projeto. Se você tiver a necessidade de fazer uma menor, apenas
98
Casa do Código
Capítulo 7. Deixar o banco processar: procedures e functions
deve ignorar os elementos de que você não necessita. A maior dificuldade
quando estamos começando a programar em uma nova linguagem é juntar
todos os elementos em um único bloco ou uma consulta, como estamos vendo
no MySQL.
7.2
Processando e retornando com functions
Com as procedures, conseguimos realizar processamentos, e ainda, se qui-
sermos, obter algum retorno. As funções são utilizadas especificamente para
retornar algo. Podemos passar algum parâmetro com o mesmo tipo de decla-
ração que fazemos na procedure e informar o tipo de retorno que teremos.
Se você quiser criar algo para ter algum retorno, aconselho a utilização
de uma function, pois poderemos utilizá-las no meio de uma consulta, ao
contrário da procedure, que temos que executar com um comando espe-
cífico.
Vamos criar uma function para retornar o nome do cliente. Podería-
mos fazer uma consulta por meio de um join para realizar esse retorno,
o mesmo que fizemos no capítulo 5, quando queríamos retornar o nome do
cliente da venda. Porém, imagine que você está fazendo uma consulta com
muitas tabelas e joins em seu sistema. Ter uma função para ter o nome do
cliente facilitaria. Então, mão na massa! Aliás, teclado!
mysql>delimiter $$
mysql> create function rt_nome_cliente(vn_numeclien int)
returns varchar(50)
begin
declare nome varchar(50);
select c_nomeclien into nome
from comclien
where n_numeclien = vn_numeclien;
return nome;
99
7.2. Processando e retornando com functions
Casa do Código
end $$
mysql> delimiter ;
Para fazer como uma consulta, só que passando um parâmetro entre pa-
rênteses:
mysql> ## estou passando como parâmetro o id do cliente igual a 1
mysql> select rt_nome_cliente(1);
+--------------------+
| rt_nome_cliente(1) |
+--------------------+
| AARONSON FURNITURE |
+--------------------+
1 row in set (0.02 sec)
E para utilizar a tabela comvenda, passamos o id do cliente de cada linha:
mysql> ##irei retornar o código da venda, nome do cliente e a
mysql> ##data da venda ordenando pelo nome e em seguida pela data
mysql> select c_codivenda,
rt_nome_cliente(n_numeclien),
d_datavenda
from comvenda
order by 2,3;
+-------------+------------------------------+-------------+
| c_codivenda | rt_nome_cliente(n_numeclien) | d_datavenda |
+-------------+------------------------------+-------------+
| 11
| AARONSON FURNITURE
| 2015-01-01 |
| 1
| AARONSON FURNITURE
| 2015-01-01 |
| 10
| AARONSON FURNITURE
| 2015-01-02 |
| 19
| AUTO WORKS
| 2015-01-01 |
| 9
| AUTO WORKS
| 2015-01-01 |
| 20
| AUTO WORKS
| 2015-01-02 |
| 4
| GREAT AMERICAN MUSIC
| 2015-01-04 |
| 18
| HUGHES MARKETS
| 2015-01-04 |
| 8
| HUGHES MARKETS
| 2015-01-04 |
| 3
| KELSEY NEIGHBOURHOOD
| 2015-01-03 |
| 13
| KELSEY NEIGHBOURHOOD
| 2015-01-03 |
100
Casa do Código
Capítulo 7. Deixar o banco processar: procedures e functions
| 14
| KELSEY NEIGHBOURHOOD
| 2015-01-04 |
| 15
| LIFE PLAN COUNSELLING
| 2015-01-01 |
| 5
| LIFE PLAN COUNSELLING
| 2015-01-01 |
| 2
| LITTLER
| 2015-01-02 |
| 12
| LITTLER
| 2015-01-02 |
| 6
| PRACTI-PLAN
| 2015-01-02 |
| 16
| PRACTI-PLAN
| 2015-01-02 |
| 7
| SPORTSWEST
| 2015-01-03 |
| 17
| SPORTSWEST
| 2015-01-03 |
+-------------+------------------------------+-------------+
20 rows in set (0.00 sec)
7.3
Tabela dual
Em alguns bancos de dados, como Oracle, a cláusula from é obrigatória. Em
consultas onde queremos retornar um único valor, devemos utilizar a tabela
chamada dual. Por exemplo: o valor de uma constante, expressão, retornar
o resultado de cálculos numéricos e de datas, ou seja, algo que não se origina de tabelas de dados comuns etc. Ela nada mais é que uma tabela, a qual possui
uma coluna chamada DUMMY e um único registro com o valor X.
No MySQL, não temos essa obrigatoriedade, mas, se você quiser utilizá-la,
não tem problema. O SGBD vai ignorá-la, já que o MySQL não tem a obri-
gatoriedade da cláusula from. Como exemplo, vamos utilizar a function:
rt_nome_cliente(), mas só que agora utilizando a tabela dual.
mysql> select rt_nome_cliente(1) from dual;
+--------------------+
| rt_nome_cliente(1) |
+--------------------+
| AARONSON FURNITURE |
+--------------------+
1 row in set (0.02 sec)
É igual se tivéssemos feito:
mysql> select rt_nome_cliente(1);
101
7.4. Automatizando o processo através de event scheduler
Casa do Código
+--------------------+
| rt_nome_cliente(1) |
+--------------------+
| AARONSON FURNITURE |
+--------------------+
1 row in set (0.02 sec)
Observe que o retorno é o mesmo. Você poderá utilizar como achar me-
lhor, com ou sem o dual. Quando eu for utilizar consultas sem tabelas, farei
das duas maneiras para você se acostumar.
Agora, você já pode criar uma procedure ou uma function para
otimizar seu sistema. Use a criatividade, pois essas duas instruções podem
ser muito úteis no dia a dia.
7.4
Automatizando
o
processo
através
de
event scheduler
Criamos uma procedure para fazer o processamento das comissões. Po-
rém, executar esse processamento pode se tonar uma atividade muito chata.
Podemos agendar eventos para fazê-lo automática e periodicamente. Para
isso, utilizamos o event scheduler.
Esses eventos agendados são bastante utilizados para fazer rotinas fora
do horário de trabalho, principalmente de madrugada, ou se o sistema for
utilizado 24 horas por dia. Escolha o horário de menor utilização, pois, geralmente, são processamentos grandes que requerem do servidor um amplo uso
do processador de memória. Se realizado quando há muitas pessoas usando
o sistema, pode atrapalhar a performance e até levar à queda do serviço de
banco de dados. Mas nada impede de utilizá-lo para automatizar pequenas
rotinas durante o dia.
Vamos programar a procedure processa_comissionamento para
executar uma vez por semana. Por isso, utilizaremos on schedule every
1 week, que vai executar a primeira vez no dia ‘2015-03-01’ ás 23:00 horas.
Primeiro, devemos habilitar o event_scheduler em nosso SGBD, pois,
por padrão, ele fica desabilitado. Abra o prompt e digite o comando:
mysql> set global event_scheduler = on;
102
Casa do Código
Capítulo 7. Deixar o banco processar: procedures e functions
Query OK, 0 rows affected (0.03 sec)
Nos parâmetros na chamada da procedure, utilizarei a função de data
current_date() que retorna a data atual. Como o evento vai executar uma
vez por semana, quero processar as vendas da semana. Logo, vou subtrair sete
dias da data atual.
mysql> delimiter $$
mysql> create event processa_comissao
on schedule every 1 week starts '2015-03-01 23:38:00'
do
begin
call processa_comissionamento(
current_date() - interval 7 day,
current_date(), @a );
end
mysql> $$
mysql> delimiter ;
Para vermos o resultado, vamos consultar as vendas desse período.
mysql> select c_codivenda Codigo,
n_totavenda Total,
n_vcomvenda Comissao
from comvenda
where
d_datavenda between current_date() - interval 60 day
and current_date();
+--------+----------+----------+
| Codigo | Total
| Comissao |
+--------+----------+----------+
| 2
| 12476.58 | 1743.99 |
| 3
| 16257.32 |
0.00 |
| 4
| 8704.55 |
0.00 |
| 6
| 6079.19 |
0.00 |
| 7
| 7451.26 |
0.00 |
| 8
| 15380.47 |
0.00 |
103
7.4. Automatizando o processo através de event scheduler
Casa do Código
| 10
| 20315.07 |
0.00 |
| 12
| 11198.05 |
0.00 |
| 13
| 4967.84 |
0.00 |
| 14
| 7451.26 |
0.00 |
| 16
| 13502.34 |
0.00 |
| 17
| 22222.99 |
0.00 |
| 18
| 15465.69 |
0.00 |
| 20
| 6975.96 |
0.00 |
+--------+----------+----------+
14 rows in set (0.00 sec)
Podemos também executar os eventos com outras periodicidades, entre
elas:
• on schedule every 1 year: uma vez por ano;
• on schedule every 1 month: uma vez por mês;
• on schedule every 1 day: uma vez ao dia;
• on schedule every 1 hour: uma vez por hora;
• on schedule every 1 minute: uma vez por minuto;
• on schedule every 1 second: uma vez por segundo.
Você pode utilizar o tempo que desejar. Eu exemplifiquei com o número
1, mas você pode utilizar, por exemplo, 3 hour.
Além de escolher quando ela começará, você também pode decidir
quando parará de executar. Para exemplificar, vamos criar um evento para
iniciar a nossa procedure a cada 10 minutos e parar depois de uma hora.
mysql> delimiter $$
mysql> create event processa_comissao_event
on schedule every 10 minute
starts current_timestamp
ends current_timestamp + interval 30 minute
do
begin
104
Casa do Código
Capítulo 7. Deixar o banco processar: procedures e functions
call processa_comissionamento(
current_date() - interval 7 day,
current_date(),
@a);
end
mysql> $$
Query OK, 0 rows affected (0.02 sec)
mysql> delimiter ;
Você pode utilizar os eventos de uma maneira versátil. Por exemplo:
utilizá-los para realizar insert, update, delete e executar procedures
e functions.
Ao criar um evento, ele fica habilitado automaticamente. Pode aconte-
cer que, depois de um período, você não queira mais que o processo execute
maquinalmente. Em vez de excluí-lo, você pode apenas desabilitá-lo com o
seguinte comando:
mysql> alter event processa_comissao_event disable;
Query OK, 0 rows affected (0.00 sec)
E para habilitá-lo novamente:
mysql> alter event processa_comissao_event enable;
Query OK, 0 rows affected (0.00 sec)
Automatizar é preciso, pois desempenho é necessário. Essas duas coisas
você consegue com procedures, funtions e schedules. Utilize-as com
sabedoria e de uma forma que aumente a performance de seu sistema e sua
produtividade, pois, assim, você ganhará no futuro em qualidade do sistema
e em sua manutenção.
No repositório, você vai encontrar os arquivos
schedules.sql,
procedures.sql e functions.sql. Neles, há outros exemplos com ou-
tras variações. Agora, no próximo capítulo, vamos automatizá-lo ainda mais,
através do SGBD.
105
Capítulo 8
Criando gatilhos
“Quando os fatos mudam, é preciso mudar as ideias.”
– Tony Judt
8.1
Triggers nas rotinas
Criamos um processo para gerar a comissão, que pode ser executada manu-
almente ou com eventos programados. Porém, o nosso sistema não é muito
grande e não tem uma numerosa quantidade de registros. Poderíamos, en-
tão, fazer esta operação em tempo real, no exato momento em que a venda é
lançada. Conseguiremos isso através das triggers.
A trigger é um conjunto de operações que são executadas automatica-
mente quando uma alteração é feita em um registro que está relacionado a
8.2. Triggers before insert e before update
Casa do Código
uma tabela. Ela pode ser invocada antes ou depois de uma alteração em um
insert, update ou delete, podendo haver até 6 triggers por tabela.
Alguns benefícios de sua utilização são:
• Verificar a integridade dos dados, pois é possível fazer uma verificação
antes da inserção do registro;
• Contornar erros na regra de negócio do sistema no banco de dados;
• Utilizar como substituta para event_scheduler. Entretanto, ela não
o substitui em processos que não são disparados a partir de uma tabela;
• Auditar as mudanças nas tabelas.
8.2
Triggers before insert e before update
Vamos colocar na prática. Como queremos realizar o cálculo da comis-
são automaticamente, devemos criar duas triggers: uma quando você insere
uma nova venda e outra quando a atualizarmos. Utilizaremos as condições
before insert (antes da inserção) e before update (antes da atualiza-
ção). Além dessas duas, existem outras que mostrarei na sequência.
Para realizar a nossa operação, devemos consultar o percentual da comis-
são do cadastro de vendedores para gerar o cálculo. Colocando em prática o
que já aprendemos, vamos criar uma function para ter esse percentual.
mysql> delimiter $$
mysql> create function rt_percentual_comissao(vn_n_numevende int)
returns float
deterministic
begin
declare percentual_comissao float(10,2);
select n_porcvende
into percentual_comissao
from convende
where n_numevende = vn_n_numevende;
return percentual_comissao;
108
Casa do Código
Capítulo 8. Criando gatilhos
end;
mysql> $$
mysql> delimiter ;
Vamos agora ao código para criar a trigger antes da inserção. Observe que
vou utilizar o mesmo cálculo que usei na procedure.
mysql> delimiter $$
mysql> create trigger tri_vendas_bi
before insert on comvenda
for each row
begin
declare percentual_comissao float(10,2);
## busco o percentual de comissão que o vendedor deve
## receber
select rt_percentual_comissao(new.n_numevende)
into percentual_comissao;
## calculo a comissão
set valor_comissao = ((total_venda * comissao) / 100);
## recebo no novo valor de comissão
set new.n_vcomvenda = valor_comissao;
end
mysql> $$
mysql> delimiter ;
Agora, quando você inserir um novo registro na tabela comvendas, o
cálculo do valor da comissão do vendedor vai ser realizado e o campo será
preenchido.
Porém, o valor total da venda pode ser alterado e, caso ocorra a inser-
ção ou retirada de um item dela, o valor da comissão a ser paga ao vende-
dor também mudará. Por isso, devemos criar mais uma trigger na tabela
comvendas para fazer um update nesse valor para quando isso acontecer.
No lugar do insert, utilizaremos o update.
109
8.2. Triggers before insert e before update
Casa do Código
mysql> delimiter $$
mysql> create trigger tri_vendas_bu
before update on comvenda
for each row
begin
declare percentual_comissao float(10,2);
declare total_venda
float(10,2);
declare valor_comissao
float(10,2);
## No update, verifico se o valor total novo da venda
## é diferente do total anterior, pois se forem iguais,
## não há necessidade do cálculo
if (old.n_totavenda <> new.n_totavenda) then
select rt_percentual_comissao(new.n_numevende)
into percentual_comissao;
## cálculo da comissão
set
valor_comissao = ((total_venda * comissao) / 100);
## recebo no novo valor de comissão
set new.n_vcomvenda = valor_comissao;
end if;
end
mysql> $$
mysql> delimiter ;
Quando você alterar o valor total da venda, a comissão será gerada. Utilize
os scripts que estão no arquivo triggers.sql no repositório para inserir e
atualizar.
Dica: observe que também padronizei os nomes das triggers, colo-
cando no final do nome principal o seu tipo. Onde era before update,
coloquei bu e para before insert, bi. Farei assim para os outros tipos também.
110
Casa do Código
Capítulo 8. Criando gatilhos
8.3
Triggers after insert e after update
Fizemos triggers para realizar o cálculo baseado no valor do total de ven-
das. Porém, estamos somando manualmente seus itens e inserindo no campo
n_totavenda. Isso pode fazer com que algum erro ocorra, diferentemente
de quando se insere a partir de uma aplicação; entretanto, podemos fazer com
que esse cálculo seja realizado automaticamente, utilizando uma trigger.
Desta vez, vamos usar os tipos after insert (depois de inserir) e
after update (depois de alterar) na tabela comivenda (itens da venda),
para que, depois de inserir os produtos, o valor do seu total seja calculado e o campo n_totavenda seja atualizado.
mysql> delimiter $$
mysql> create trigger tri_vendas_ai
after insert on comivenda
for each row
begin
## declaro as variáveis que utilizarei
declare vtotal_itens float(10,2);
declare vtotal_item float(10,2);
declare total_item
float(10,2);
## cursor para buscar os itens já registrados da venda
declare busca_itens cursor for
select n_totaivenda
from comivenda
where n_numevenda = new.n_numevenda;
## abro o cursor
open busca_itens;
## declaro e inicio o loop
itens : loop
fetch busca_itens into total_item;
## somo o valor total dos itens(produtos)
set vtotal_itens = vtotal_itens + total_item;
end loop itens;
111
8.3. Triggers after insert e after update
Casa do Código
close busca_itens;
## atualizo o total da venda
update comvenda set n_totavenda = vtotal_itens
where n_numevenda = new.n_numevenda;
end
mysql> $$
mysql> delimiter ;
Com isso, ao inserir um registro na tabela de vendas, não precisamos pre-
encher o campo n_totavenda, pois ele será preenchido automaticamente.
Agora temos a mesma situação que tínhamos anteriormente, pois a tabela
de itens da venda pode ser atualizada e, se isso ocorrer, o valor de seu total ficará incorreto. Por isso, devemos criar uma trigger que o atualizará se o
valor do item for alterado; mas somente na condição de o novo ser diferente
do antigo. Esse não sendo o caso, não é necessário executar os cálculos.
mysql> delimiter $$
mysql> create trigger tri_ivendas_au
after update on comivenda
for each row
begin
## declaro as variáveis que utilizarei
declare vtotal_itens float(10,2);
declare vtotal_item float(10,2);
declare total_item float(10,2);
## cursor para buscar os itens já registrados da venda
declare busca_itens cursor for
select n_totaivenda
from comivenda
where n_numevenda = new.n_numevenda;
## verifico se há necessidade de alteração
## faço somente se o novo valor for alterado
if new.n_valoivenda <> old.n_valoivenda then
112
Casa do Código
Capítulo 8. Criando gatilhos
## abro o cursor
open busca_itens;
## declaro e inicio o loop
itens : loop
fetch busca_itens into total_item;
## somo o valor total dos itens(produtos)
set vtotal_itens = vtotal_itens + total_item;
end loop itens;
close busca_itens;
## atualizo o total da venda
update comvenda set n_totavenda = vtotal_itens
where n_numevenda = new.n_numevenda;
end if;
end
mysql> $$
mysql> delimiter ;
8.4
Triggers before delete e after delete
Agora, você pode estar com a seguinte dúvida: e se nós excluirmos um item
de uma venda? Realmente, se isso ocorrer neste momento, o seu valor total
estará incorreto, pois fizemos apenas as triggers para insert e update.
Para corrigir este problema, vamos criar uma para o delete também.
mysql> delimiter $$
mysql> create trigger tri_ivendas_af
after delete on comivenda
for each row
begin
## declaro as variáveis que utilizarei
declare vtotal_itens float(10,2);
declare vtotal_item float(10,2);
declare total_item float(10,2);
113
8.4. Triggers before delete e after delete
Casa do Código
## cursor para buscar os itens já registrados da venda
declare busca_itens cursor for
select n_totaivenda
from comivenda
where n_numevenda = old.n_numevenda;
## abro o cursor
open busca_itens;
## declaro e inicio o loop
itens : loop
fetch busca_itens into total_item;
## somo o valor total dos itens(produtos)
set vtotal_itens = vtotal_itens + total_item;
end loop itens;
close busca_itens;
## atualizo o total da venda
update comvenda set n_totavenda = vtotal_itens
where n_numevenda = old.n_numevenda;
end
mysql> $$
mysql> delimiter ;
Agora, quando você excluir um item de uma venda, a trigger buscará os
itens que ainda restam e fará o seu cálculo. No caso de vendas, eles serão todos automáticos.
Temos mais uma situação que podemos resolver utilizando a trigger: on
delete. Lembra-se das questões de integridade de dados? Uma tabela que
possui uma foreign key não pode deletar um registro sem antes fazer a exclu-
são do registro primário.
Portanto, utilizando uma trigger, ao fazer um delete na tabela vendas
antes de excluí-la ( before delete), vamos deletar os itens. Desta maneira,
114
Casa do Código
Capítulo 8. Criando gatilhos
respeitaremos a integridade, evitando erros no sistema e deletando os itens
com um único comando. Você apenas precisará fazê-lo e a ela se encarregará
do resto. Mãos ao teclado!
mysql> delimiter $$
mysql> create trigger tri_vendas_bf
before delete on comvenda
for each row
begin
## declaro as variáveis que utilizarei
declare vtotal_itens float(10,2);
declare vtotal_item float(10,2);
declare total_item float(10,2);
## verifico se há necessidade de alteração
## faço somente se o novo valor for alterado
## cursor para buscar os itens já registrados da venda
declare busca_itens cursor for
select n_totaivenda
from comivenda
where n_numevenda = old.n_numevenda;
## abro o cursor
open busca_itens;
## declaro e inicio o loop
itens : loop
fetch busca_itens into total_item;
## somo o valor total dos itens(produtos)
set vtotal_itens = vtotal_itens + total_item;
end loop itens;
close busca_itens;
## atualizo o total da venda
delete from comivenda where n_numevenda = venda;
end
mysql> $$
115
8.5. Status das triggers
Casa do Código
mysql> delimiter ;
Ao executarmos a seguinte instrução:
mysql> delete from comvenda where n_numevenda = 415;
Automaticamente, os itens serão deletados e a venda também!
8.5
Status das triggers
Por algumas razões, você pode querer não utilizar mais uma trigger. É pos-
sível escolher entre desabilitá-la ou excluí-la definitivamente. A vantagem da desabilitação é que você não precisará criá-la posteriormente, caso precise
usá-la novamente. Por exemplo, se não quisermos mais que a comissão seja
calculada automaticamente, podemos apenas invalidá-la.
Para desabilitar uma trigger, fazemos:
mysql> alter trigger tri_vendas_bi desable;
E se formos utilizar novamente o cálculo, habilitaremos novamente assim:
mysql> alter trigger tri_vendas_bi enable;
E para excluí-la:
mysql> drop trigger tri_vendas_bi;
Conhecemos mais uma ferramenta para nos auxiliar na automação de
processos. Agora, conseguimos disparar processos automaticamente, aumen-
tando a velocidade das operações do banco de dados, e utilizar mais um pouco
da potência do nosso SGBD.
116
Capítulo 9
Obtendo performance e criando
visões
“Sozinhos podemos ver pouco do futuro, porém o suficiente para darmos conta
de que há muito que se fazer.”
– Allan Turing
9.1
Ganhando performance com índices
Aprendemos a criar processos e a otimizá-los, utilizando a potência do SGBD.
Fazemos isso pensando em sua performance e desempenho, pois, cada vez
que a quantidade de dados do banco aumentar, você deverá pensar em méto-
dos para fazer essa otimização.
9.1. Ganhando performance com índices
Casa do Código
Para nos auxiliar no aprimoramento das consultas, o MySQL nos fornece
os chamados índices. Quando criamos as nossas tabelas, nós já criamos um índice de chave primária (primary key). Porém, ele não é utilizado para fazer
essa otimização de performance, pois serve apenas para cuidar da integridade
dos dados.
A medida inicial que devemos tomar para melhorar o tempo das consul-
tas é a criação de índices para as tabelas. Se elas estão demorando para serem concluídas, as primeiras suspeitas são: ou eles não foram feitos ou estão mal
criados. A adequação ou criação dos índices necessários pode resolver o pro-
blema na maioria das vezes; porém não sempre, pois seu banco de dados pode
estar lento por outros motivos. Entretanto, como seu uso é o mais eficaz na
questão de otimização, pode ser um desperdício de tempo tentar esse mesmo
resultado por outros meios.
Mas o que acontece para ocorrer esse ganho de desempenho? Quando
uma tabela não tem índices, os seus registros são desordenados e uma con-
sulta terá que percorrer todos eles. Se adicionarmos um índice, uma nova
tabela é gerada. A quantidade de registros desta nova é a mesma que a da ori-
ginal, a diferença é que eles são organizados. Isso implica que uma consulta
percorrerá a tabela para encontrar os registros que casem com a sua condição
e a busca é cessada quando um valor imediatamente maior é encontrado.
Se uma tabela possui 1000 registros, será, pelo menos, 100 vezes mais rá-
pido do que ler todos eles sequencialmente. Porém, note que, se você precisar
acessar quase todos eles, seria mais rápido acessá-los sequencialmente, por-
que evitaria acessos ao disco a cada verificação.
Criando index
Vamos demonstrar como se faz dos dois jeitos, começando pelo create
table. Utilizarei como exemplo a nossa tabela de clientes, tanto na criação
como depois dela. Criarei dois índices. Mais à frente, explicarei a escolha
desses dois campos.
mysql> create table comclien(
n_numeclien int not null auto_increment,
c_codiclien varchar(10),
c_nomeclien varchar(200),
118
Casa do Código
Capítulo 9. Obtendo performance e criando visões
c_razaclien varchar(200),
d_dataclien date,
c_cnpjclien varchar(15),
c_foneclien varchar(15),
primary key (n_numeclien),
index idx_comclien_2(c_codiclien));
Agora, vamos ver como criar índices com alter table, uma vez que
já criamos nossas tabelas no banco.
mysql> alter table comclien add
index idx_comclien_3(c_razaclien);
mysql> alter table comclien add
index idx_comclien_4(c_codiclien);
Quando utilizar
Você deve dar preferência para colunas que usamos para pesquisa, or-
denação ou agrupamento, em cláusulas:
where, joins, order by ou
group by. Por isso, escolhi as colunas n_numeclien, c_codiclien e
c_razaclien, já que são as que mais usamos durante o livro para fazer as
consultas e buscas de registros. Porém, o fato de uma coluna aparecer na lista de colunas que serão exibidas em um select não a descarta de ser uma com
index, pois ela pode estar na listagem e também estar na cláusula where,
por exemplo.
Outro fator que você deve levar em consideração é a cardinalidade de uma
coluna que é referenciada em outras tabelas, como foreign key, por ela conter
uma grande quantidade de números distintos. Índices funcionam melhor em
colunas com um alto número de cardinalidade relativa do que de registros da
tabela; isto é, colunas que têm muitos valores únicos e poucos duplicados.
Quando não utilizar
Se uma coluna contém valores muito diferentes (por exemplo, a que
guarda as idades), um índice vai diferenciar os registros rapidamente. En-
tretanto, ele não ajudará em uma que é usada para armazenar registros de
gênero (sexo) e contém somente os valores M ou F. Se os registros têm, apro-
ximadamente, o mesmo número de homens e mulheres, o índice percorrerá
119
9.1. Ganhando performance com índices
Casa do Código
quase metade dos registros, qualquer que seja o valor buscado. Com isso, po-
demos dizer que é melhor criar índices em colunas com grande variação de
registros.
Até agora, citei as vantagens da utilização de index, pois não existem
grandes desvantagens. Eu costumo dizer que os benefícios compensaram-
nas. Com a criação dos índices nas tabelas, suas operações de insert,
update e delete perderão velocidade. Isso ocorrerá, pois, ao realizar uma
dessas alterações, a reordenação dos registros acontecerá. Não será nada per-
ceptível ou que causará grande prejuízo de performance (por isso, digo que
os benefícios são maiores). Em cada insert ou update, o SGBD ordenará
os registros pelo index para suas consultas serem mais rápidas.
Nós podemos verificar os índices criados em uma tabela utilizando o
mesmo show que usamos para ver o conteúdo de uma. Aqui, utilizaremos o
show indexes, como no código na sequência.
mysql> show indexes from comclien;
+----------+------------+----------------+--------------
| Table
| Non_unique | Key_name
| Seq_in_index
+----------+------------+----------------+--------------
| comclien |
0 | PRIMARY
|
1
| comclien |
1 | idx_comclien_3 |
1
| comclien |
1 | idx_comclien_4 |
1
+----------+------------+----------------+--------------
+-------------+-----------+-------------+----------+--------
| Column_name | Collation | Cardinality | Sub_part | Packed
+-------------+-----------+-------------+----------+--------
| n_numeclien | A
|
10 |
NULL | NULL
| c_razaclien | A
|
10 |
NULL | NULL
| c_codiclien | A
|
10 |
NULL | NULL
+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
| Null | Index_type | Comment | Index_comment |
+------+------------+---------+---------------+
|
| BTREE
|
|
|
| YES | BTREE
|
|
|
| YES | BTREE
|
|
|
+------+------------+---------+---------------+
120
Casa do Código
Capítulo 9. Obtendo performance e criando visões
3 rows in set (0.02 sec)
O MySQL ainda possui outros tipos de índices. Outro mais comum é o
unique index. Como o nome já diz, é um índice único que também serve
para restringir a duplicação de dados. Ao criá-lo em uma coluna, você está
dizendo ao SGBD que ele não pode aceitar registros duplicados lá. Portanto,
tenha muito cuidado se for escolher trabalhar com esse tipo de índice.
Por exemplo, se criarmos um unique index na tabela de produtos no
campo de c_codiprodu, não conseguiremos cadastrar dois deles com o
mesmo código. Porém, é muito comum termos essa situação por conta de
fornecedores diferentes. Analise bem sua regra de negócio antes de sair cri-
ando índices únicos.
Poderíamos criar um índice desse em nossa tabela de vendas no campo
c_codivenda, para que não haja nenhum código duplicado. Desta maneira,
fazemos:
mysql> alter table comvenda add unique
index idx_comvenda_1(c_codivenda);
Query OK, 0 rows affected (0.92 sec)
Records: 0 Duplicates: 0 Warnings: 0
Vamos verificar se o índice foi criado.
mysql> show indexes from comvenda;
+----------+------------+----------------+--------------
| Table
| Non_unique | Key_name
| Seq_in_index
+----------+------------+----------------+--------------
| comvenda |
0 | PRIMARY
|
1
| comvenda |
0 | idx_comvenda_1 |
1
+----------+------------+----------------+--------------
+-------------+-----------+-------------+----------+--------
| Column_name | Collation | Cardinality | Sub_part | Packed
+-------------+-----------+-------------+----------+--------
| n_numevenda | A
|
20 |
NULL | NULL
| c_codivenda | A
|
20 |
NULL | NULL
121
9.2. Views
Casa do Código
+-------------+-----------+-------------+----------+--------
+------+------------+---------+---------------+
| Null | Index_type | Comment | Index_comment |
+------+------------+---------+---------------+
|
| BTREE
|
|
|
| YES | BTREE
|
|
|
+------+------------+---------+---------------+
2 rows in set (0.00 sec)
Tente inserir uma venda com um código que já exista. Você verá que o
banco retornará um erro, acusando sua existência e que esse campo possui
um índice único.
Caso você tenha criado um index incorretamente, você poderá excluí-lo
utilizando a seguinte instrução:
mysql> alter table comvenda drop index idx_comvenda_1;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
Utilize índices em todas as tabelas e obtenha um pouco mais da potência
do MySQL. Em nosso repositório, existe o arquivo indices.sql, no qual
você pode ver a criação para as demais tabelas do projeto.
9.2
Views
Quando você trabalha com desenvolvimento de software e administração de
dados em um SGBD, você escreve algumas determinadas consultas todos os
dias e várias vezes. Muitas destas são derivadas de várias tabelas, o que nos
dá um retrabalho ao montar todas aquelas joins.
Sabemos também que, toda vez que reescrevemos uma mesma consulta,
conseguiremos os mesmos resultados de antes. Isso é uma tarefa séria, já que
existem diversas formas de se escrever uma mesma consulta. Para amenizar
essa situação e também pensando em performance e tempo economizado,
podemos rapidamente transformar estas consultas em uma view. A partir
122
Casa do Código
Capítulo 9. Obtendo performance e criando visões
disso, ela permanecerá armazenada no servidor de bancos de dados em forma
de tabela para que possamos consultá-la todas as vezes que precisarmos, sem
ter que reescrevê-la.
Uma view é um objeto que pertence a um banco de dados, definida e ba-
seada em declarações selects, retornando uma determinada visualização
de dados de uma ou mais tabelas. Esses objetos são chamados, por vezes, de
virtual tables, formados a partir de outras tabelas que, por sua vez, são deno-minadas de based tables ou ainda outras Views. Em alguns casos, estas são
atualizáveis e podem ser alvos de declaração insert, update e delete,
que, na verdade, modificam sua based tables.
Os benefícios de sua utilização, além dos já salientados, são:
• Uma view pode ser utilizada, por exemplo, para retornar um valor de
apenas uma coluna na tabela;
• Também para promover restrições em dados para aumentar sua segu-
rança e definir políticas de acesso em nível de tabela e coluna;
• Podem ser configuradas para mostrar colunas diferentes para diferen-
tes usuários do banco de dados;
• Também podem ser usadas com um conjunto de tabelas unido a outros
conjuntos de tabelas com a utilização de joins.
9.3
Criando Views
Uma consulta que já utilizamos algumas vezes durante o livro foi a da tabela
de clientes junto com a de vendas. Sabendo disso, vamos criar uma view
para essa consulta, para que possamos utilizar posteriormente, em vez de
escrevê-la todas as vezes. Observe que sua criação será com create or
replace, pois, se você quiser criá-la novamente, apenas precisa executar o
código no SGBD e, assim, a nova view contendo o mesmo nome será reela-
borada. Neste exemplo, eu a chamei de clientes_vendas.
mysql> create or replace view clientes_vendas as
select c_razaclien,
123
9.3. Criando Views
Casa do Código
c_nomeclien,
c_cnpjclien,
c_codivenda,
n_totavenda,
d_datavenda
from comclien,
comvenda
where comclien.n_numeclien = comvenda.n_numeclien
order by 1;
Dessa maneira, podemos fazer uma consulta utilizando agora a nossa
view, em vez da consulta convencional.
mysql> select * from clientes_vendas;
+------------------------+-----------------------
| c_razaclien
| c_nomeclien
+------------------------+-----------------------
| AARONSON FURNITURE LTD | AARONSON FURNITURE
| LITTLER LTDA
| LITTLER
| KELSEY NEIGHBOURHOOD | KELSEY NEIGHBOURHOOD
| GREAT AMERICAN MUSIC
| GREAT AMERICAN MUSIC
| LIFE PLAN COUNSELLING | LIFE PLAN COUNSELLING
| PRACTI-PLAN LTDA
| PRACTI-PLAN
| SPORTSWEST LTDA
| SPORTSWEST
| HUGHES MARKETS LTDA
| HUGHES MARKETS
| AUTO WORKS LTDA
| AUTO WORKS
| AARONSON FURNITURE LTD | AARONSON FURNITURE
| AARONSON FURNITURE LTD | AARONSON FURNITURE
| LITTLER LTDA
| LITTLER
| KELSEY NEIGHBOURHOOD | KELSEY NEIGHBOURHOOD
| KELSEY NEIGHBOURHOOD | KELSEY NEIGHBOURHOOD
| LIFE PLAN COUNSELLING | LIFE PLAN COUNSELLING
| PRACTI-PLAN LTDA
| PRACTI-PLAN
| SPORTSWEST LTDA
| SPORTSWEST
| HUGHES MARKETS LTDA
| HUGHES MARKETS
| AUTO WORKS LTDA
| AUTO WORKS
| AUTO WORKS LTDA
| AUTO WORKS
+------------------------+-----------------------
124
Casa do Código
Capítulo 9. Obtendo performance e criando visões
+--------------------+-------------+-------------+
| c_cnpjclien
| c_codivenda | n_totavenda |
+--------------------+-------------+-------------+
| 17.807.928/0001-85 | 1
|
25141.02 |
| 55.643.605/0001-92 | 2
|
12476.58 |
| 05.202.361/0001-34 | 3
|
16257.32 |
| 11.880.735/0001-73 | 4
|
8704.55 |
| 75.185.467/0001-52 | 5
|
13078.81 |
| 32.518.106/0001-78 | 6
|
6079.19 |
| 83.175.645/0001-92 | 7
|
7451.26 |
| 04.728.160/0001-02 | 8
|
15380.47 |
| 08.271.985/0001-00 | 9
|
13508.34 |
| 17.807.928/0001-85 | 10
|
20315.07 |
| 17.807.928/0001-85 | 11
|
8704.55 |
| 55.643.605/0001-92 | 12
|
11198.05 |
| 05.202.361/0001-34 | 13
|
4967.84 |
| 05.202.361/0001-34 | 14
|
7451.26 |
| 75.185.467/0001-52 | 15
|
10747.36 |
| 32.518.106/0001-78 | 16
|
13502.34 |
| 83.175.645/0001-92 | 17
|
22222.99 |
| 04.728.160/0001-02 | 18
|
15465.69 |
| 08.271.985/0001-00 | 19
|
4650.64 |
| 08.271.985/0001-00 | 20
|
6975.96 |
+--------------------+-------------+-------------+
20 rows in set (0.09 sec)
Atualizando views
Para você conseguir inserir, atualizar ou deletar um registro através de
uma view, ela não pode possuir joins e funções agregadoras, como o
group by. Vamos criar uma para a tabela de produtos.
mysql> create or replace view produtos as
select n_numeprodu,
c_codiprodu,
c_descprodu,
n_valoprodu,
c_situprodu,
n_numeforne
125
9.3. Criando Views
Casa do Código
from comprodu;
Agora conseguiremos executar operações com ela. Da mesma maneira
que aprendemos a fazer insert na tabela, faremos utilizando na view. Va-
mos exemplificar:
mysql> insert into produtos
values (6,'0006','SMART WATCH','2412.98','A',1);
Query OK, 1 row affected (0.08 sec)
O mesmo vale para alteração e exclusão de registros.
mysql> update produtos set n_valoprodu = '1245.99'
where n_numeprodu = 6;
Query OK, 1 row affected (0.13 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> delete from produtos where n_numeprodu = 6;
Query OK, 1 row affected (0.09 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)a
Se você criou uma view que não utilizará novamente e precisa ser ex-
cluída, você deve fazer como se fosse deletar uma tabela. Utilizando o co-
mando drop, da seguinte maneira:
mysql> drop view produtos;
Query OK, 0 rows affected (0.02 sec)
Conseguimos otimizar um pouco mais o nosso trabalho no banco de da-
dos, melhorando a performance e criando views das nossas consultas mais
comuns e de tabelas. Neste momento, você começa a perceber o quanto o
126
Casa do Código
Capítulo 9. Obtendo performance e criando visões
MySQL é potente em termos de ferramentas e flexibilidade. Você pode e deve
extrair tudo o que ele tem a oferecer. Com o tempo, você vai otimizando
seus próprios processos e, cada vez mais, extraindo, ao máximo, a potência
do SGBD.
127
Capítulo 10
Criando, exportando e
importando backups: ele poderá
te salvar um dia
“Seja curioso. Leia de tudo. Tente coisas novas. O que as pessoas chamam de
inteligência se resume a curiosidade.”
– Aaron Swartz
10.1
Segurança dos seus dados
Imagine a quantidade de dados que o sistema sendo desenvolvido gera todos
os dias. Agora, imagine que por consequência de uma falha de hardware, você
possa perder um HD, no qual está seu banco de dados. O prejuízo seria muito
10.1. Segurança dos seus dados
Casa do Código
grande se você não possuísse uma cópia de segurança para substituir esse que
estragou.
O backup de qualquer arquivo é importante, sendo o de bases de dados
mais ainda. Não adianta você investir em segurança da informação para pro-
teger sua rede e esquecer-se de proteger os dados com backups. Além de fa-
lhas de hardwares e de sistemas, também é bem comum usuários e desenvol-
vedores deletarem os dados por engano. Para atualizar a versão do MySQL
ou trocar o banco, você também precisará de uma cópia da base. Por esses e
outros motivos, devemos fazer backups sempre que possível: diariamente, se-
manalmente ou mensalmente, dependendo da sua preferência e necessidade.
No MySQl, você pode fazer um backup físico ou lógico. O primeiro con-
siste em fazer uma cópia das pastas de instalação do MySQL, que salvará toda
a estrutura de arquivos de instalação, inclusive os dados. O segundo faz uma
cópia da estrutura lógica, na qual estão inclusos os scripts de criação do
banco de dados, das tabelas etc., além dos registros. Esta é a mais utilizada.
Além dessas diferenças, o processo de backup pode ser realizado offline ou
online.
Backup online
Os backups online ocorrem enquanto o servidor MySQL está em execu-
ção, para que as informações do banco de dados possam ser obtidas a partir
do servidor.
Podemos destacar suas seguintes características:
• É menos intrusivo para outros clientes, que podem se conectar ao ser-
vidor MySQL durante o backup e pode ser capaz de acessar os dados,
dependendo do que as operações necessitam para executar;
• Cuidados devem ser tomados para impor bloqueio apropriado para que
as modificações de dados não ocorram de forma a comprometer a sua
integridade.
Backup offline
Backups offline ocorrem enquanto o servidor está parado. Esta distinção
entre eles também pode ser descrita como backups quentes versus backups 130
Casa do Código
Capítulo 10. Criando, exportando e importando backups: ele poderá te. . .
frios. Uma cópia de segurança quente é aquela em que o servidor continua funcionando, mas fica bloqueado contra modificação de dados enquanto você
acessar arquivos de banco de dados externamente.
Também podemos destacar as seguintes características:
• Os clientes podem ser afetados negativamente, porque o servidor não
está disponível durante o backup. Por essa razão, eles são, muitas vezes,
tomados a partir de um servidor paralelo de replicação que pode ser
tirado do ar, sem prejudicar a disponibilidade;
• O processo de cópia de segurança é simples, porque não existe qualquer
possibilidade de interferência da atividade do cliente.
Essa questão de on ou off vai depender do cenário em que seu sistema
estiver inserido, assim como várias que citei durante o livro. Você deve ob-
servar e resolver o que é melhor. Nós vamos aprender como fazer um backup
lógico utilizando o console do seu sistema operacional. Como estamos em
um ambiente de desenvolvimento, não importa se é offline ou online, pois
não temos usuários acessando a nossa base de dados.
10.2
Criando backups
Diferentemente da instalação, que tem suas particularidades para cada sis-
tema operacional, o comando da criação do backup será o mesmo. Utilizare-
mos variações do comando mysqldump. Vale lembrar que será apenas para
exportar a base, pois, para importar, veremos mais à frente que é um comando
diferente. Pode haver situações em que você apenas necessite exportar uma
ou algumas tabelas do banco. Você tem a possibilidade de criar vários banco
de dados e não fazer backup de todos. Por isso, também temos a possibilidade
de exportar uma ou todas as bases de dados do banco.
Vamos criar um backup de todas as tabelas do banco do nosso projeto.
Este criará um arquivo com a extensão .sql, que vai conter os scripts de
criação e inserção de registros das tabelas exportadas. Vamos abrir o console
e navegar até a pasta bin da instalação do MySQL.
mysql\bin> mysqldump -u root -p
comercial > c:/bkp_tables_views.sql
131
10.3. Importando backups
Casa do Código
Agora, abra o arquivo gerado em um editor de texto e você verá que o
backup que foi gerado contém apenas as tabelas e os inserts. Mas e nossas
procedures, functions e triggers? Para exportá-los juntos ao nosso
arquivo, devemos especificar que também as queremos nessa exportação, com
as instruções --routines para incluir a exportação das procedures e
functions, e --triggers para incluir as triggers.
mysql\bin> mysqldump -u root -p --routines --triggers
comercial > c:/bkp_full.sql
Você também tem a possibilidade de exportar apenas uma ou várias ta-
belas. Você só precisa descrever quais delas você quer após o nome do banco
de dados.
mysql\bin> mysqldump -u root -p comercial
comclien > c:/bkp_clien.sql
Até agora, nós criamos apenas um banco de dados. No entanto, há a possi-
bilidade da criação de vários, como vimos durante a criação do nosso projeto.
Aprendemos também como podemos exportar apenas um banco; mas tam-
bém é possível criar um comando que exportará todos os bancos em apenas
um arquivo. Faremos assim:
mysql\bin> mysqldump -u root -p --all-databases > c:/bkp_all.sql
Percebeu o quanto é fácil gerar um arquivo de backup? Você perderá ape-
nas alguns minutos fazendo-o. Se ocorrer alguma falha e você não o tiver feito, vamos falar de dias, meses ou até anos perdido. Sem contar o dinheiro.
10.3
Importando backups
Para a importação, seguiremos passos parecidos com a exportação. Porém,
antes, vamos criar um segundo banco de dados chamado comercial2.
Nele, não nos preocuparemos com usuários, uma vez que utilizamos o usuá-
rio root para a exportação e também usaremos o arquivo bkp_full.sql
que geramos na exportação.
mysql> create database comercial2;
132
Casa do Código
Capítulo 10. Criando, exportando e importando backups: ele poderá te. . .
Agora, vamos abrir o console e utilizar o comando:
mysql\bin> mysql -h localhost -u root -p -d
comercial2 < c:/bkp_full.sql
Volte ao console do MySQL para testar se a importação foi feita com su-
cesso. Escolha o banco comercial2 para utilizar e dê o comando para listar
as tabelas. Se você seguiu todas as instruções, as listas delas deverão ser mostradas.
mysql> use comercial2;
mysql> show tables;
Agora você está apto para exportar e importar os seus dados em forma
de arquivo de backup. Desta maneira, faça-o periodicamente pela segurança
de seu sistema. O MySQL tem uma compactação muito boa para eles, pois
não ficam muito grande, em comparação com o do Oracle, supondo o mesmo
número de tabelas. Se pensarmos em espaço em disco para guardar todos os
arquivos de backups, aconselho a fazê-lo, no mínimo, duas vezes ao dia.
Muitos desenvolvedores usam arquivos .bat juntamente com scripts es-
pecíficos do sistema operacional que estão utilizando para fazer uma automa-
tização desse processo no servidor de banco de dados. Você pode pesquisar
sobre isso e adicioná-los aos nossos comandos de backup, como também pode
usar ferramentas para fazê-lo. Em meu blog, fiz um post sobre esse processo
no MySQL e sobre alguns instrumentos que você pode utilizar. O post pode
ser acessado em: http://viniciuscdes.net/blog/2015/04/backup-mysql.
No próximo capítulo, você aprenderá a exportar e importar seus regis-
tros de uma maneira diferente: sem mexer na estrutura de seu banco, apenas
neles.
133
Capítulo 11
MySQL avançado
“Se andarmos apenas por caminhos já traçados, chegaremos apenas aonde os
outros chegaram.”
– Alexander Graham Bell
Se você pesquisar sobre MySQL avançado em muitas bibliografias, poderá
encontrar assuntos que englobam desde a criação de procedures, event
scheduler, view e administração do SGBD. Essas partes que citei e que
nós aprendemos durante o livro realmente fogem do básico. Entretanto, o
que considero de fato avançado no MySQL é algo que não faz parte da rotina
de desenvolvimento de software, esta que vai desde a criação de usuário ao
monitoramento das atividades do SGBD.
Nós já vimos a criação de usuários lá no início do livro. Agora aprofunda-
remos em outros aspectos gerenciais, uma vez que estas funções fogem do dia
11.1. Variáveis de sistema
Casa do Código
a dia do desenvolvedor e ficam a cargo do administrador do banco de dados.
E, caso ele for fazê-las, será de forma esporádica.
11.1
Variáveis de sistema
Em todos os sistemas operacionais e em muitos softwares existem as variáveis
de ambiente, que são configurações que podem ser alteradas. Aqui mostrarei
como listá-las e alterá-las. Sua lista completa pode ser encontrada no site do manual oficial do MySQL: http://tinyurl.com/c9ymzek.
Primeiro, listarei todas as variáveis do MySQL para você saber como pro-
curar uma e ver seu status. Vamos usar o comando show, o mesmo que
utilizamos para elencar diversas coisas, como tables.
mysql> show variables;
Observe que a lista de variáveis fica um pouco bagunçada e, por isso, você
pode acabar não encontrando aquela que deseja. O melhor é consultar como
se fossem uma tabela, desde que você saiba o nome completo ou parcial. Va-
mos consultar as variáveis que possuem table em alguma parte do nome. As-
sim, ficaria:
mysql> show variables like '%table%';
Observe que ele listará as variáveis com os seus respectivos valores. Desta
maneira, como disse anteriormente, podemos alterá-las.
No capítulo 2, falei brevemente sobre case sensitive, propriedade na qual
faz o sistema operacional distinguir entre maiúsculas e minúsculas. Como
você está mais familiarizado com as variáveis de ambiente, posso dizer que
existe uma que executa esse controle, como um botão que liga e desliga essa
propriedade do banco de dados.
A variável chama-se lower_case_table_names. Por padrão, no Li-
nux possui valor 0, porque ele salva as tabelas como se elas estivessem escritas tanto em minúsculas ou maiúsculas, sendo case sensitive. Já no Windows, terá
valor 1, pois as salvará em minúsculas e não será case sensitive; e no MacOS
será 2, porque elas são salvas como estão escritas, não sendo também case
sensitive. Logo, temos 3 status padrão para cada sistema operacional.
136
Casa do Código
Capítulo 11. MySQL avançado
• 0: tabelas serão criadas como estiverem escritas e o SGBD fará distin-
ção entre maiúsculas e minúsculas;
• 1: tabelas serão criadas em letras minúsculas e o SGBD não fará distin-
ção entre maiúsculas e minúsculas;
• 2: tabelas serão criadas como estiverem escritas e o SGBD não fará distinção entre maiúsculas e minúsculas.
Atenção: se essa variável for alterada após você ter criado as tabelas, poderá ocasionar erros e falhas em seu sistema. As consultas já feitas em
procedures, triggers etc. vão parar de funcionar. Então, cuidado!
Aconselho utilizar o padrão, ou como fiz durante todo o nosso projeto:
todas as criações em letra minúscula, pois, assim, independente do seu
sistema operacional, o resultado será o mesmo.
Voltando para o que eu queria demonstrar: como mudar uma variável.
Vamos alterar a lower_case_table_names para 2. Aconselho que, após
mudar o seu valor, você altere novamente para o valor padrão do seu sistema
operacional.
mysql> set lower_case_table_names = 2;
Não se preocupe em conhecer todas as variáveis ou configurá-las antes
de utilizar o SGBD. Vá alterando-as conforme suas necessidades ou deman-
das dos projetos. A não ser que você venha a ser um administrador de banco
de dados, pois então, seu papel será só preocupar-se com esses tipos de con-
figurações.
11.2
Visualizando as conexões ativas
Quando criamos os agendamentos de eventos ( event scheduler) e as
procedures, descrevi que a melhor hora para executar um processo em uma
grande quantidade de registros é quando o número de conexões for menor.
Isso ocorre geralmente de madrugada. Entretanto, você pode listar todas as
conexões ativas em seu SGBD a qualquer momento.
137
11.3. Exportar e importar consultas para arquivos .csv e .txt
Casa do Código
Para fazer esta consulta, também utilizaremos o comando show, que nos
retornará valores, como: PID (número do processo da conexão), a quantidade
de conexões do seu usuário à base, o processo que a conexão está executando
no momento ou se ela está ociosa (sleep).
mysql> show processlist;
Quando estamos fazendo a administração de um banco de dados,
preocupamo-nos em manter o seu desempenho e performance. Visualizando
as conexões ativas, você pode se deparar com alguma que esteja travada ou até
mesmo com algum usuário que esteja utilizando todas as disponíveis. Desse
modo, você pode matar as conexões e processos que desejar. Primeiro, liste
os processos e anote os números ID, pois utilizaremos o comando kill para
matá-los, da seguinte maneira:
mysql> kill numero_id;
Muito cuidado ao utilizar a instrução kill, pois matar um processo in-
correto pode gerar transtornos para seus usuários. Por exemplo, caso algum
usuário do seu sistema esteja inserindo um novo registro no banco de dados
ou emitindo uma venda, ao matar sua sessão, seu processo será interrompido
antes que conclua a operação. Utilize-o com cautela.
11.3
Exportar e importar consultas para arqui-
vos .csv e .txt
Para incluir registros no banco de dados, disponibilizei um arquivo que pos-
suía uma série de scripts. No entanto, também poderíamos ter populado o
banco de dados utilizando arquivos com as extensões .csv ou .txt, as
mais utilizadas para se trabalhar com dados em exportações e importações.
Exportação
Como exemplo, vamos salvar todos os registros da tabela de clientes em
um arquivo .txt. Na exportação, diremos para o SGBD que queremos salvar
o arquivo na unidade c:/ com o nome de lista_clientes.txt. Quere-
mos separar cada registro por vírgula e limitar cada coluna com aspas simples.
138
Casa do Código
Capítulo 11. MySQL avançado
mysql> select * from comclien
into outfile 'c:/lista_clientes.txt'
fields terminated by ','
enclosed by '''';
Para você criar um arquivo .csv, apenas troque no código a extensão.
Observe que exportei todas as colunas da tabela. Você também pode esco-
lher em sua consulta quais delas quer exportar. A exportação pode ser muito
útil em seu dia a dia para extrair dados de uma forma rápida, sem precisar
de uma aplicação. Com algumas linhas de código, você consegue extrair as
informações de que tem necessidade. Além da consulta simples para realizar
a exportação, você pode também criá-las com joins, funções de agrupa-
mento ( group by) etc.
Importação
A importação via arquivo pode ser muito útil em ações de popular dados
em um banco de dados ou importá-los de outros sistemas ou bancos, como
descrevi anteriormente. Da mesma forma que a exportação, ela não vai de-
pender de uma aplicação ou de uma ferramenta.
Para realizar este processo, vamos criar uma tabela chamada comuser,
que se referenciará aos usuários do nosso projeto.
mysql> create table comuser(
n_numeuser int not null auto_increment,
n_nomeuser varchar(100),
n_nascuser date,
primary key(n_numeuser));
Em nosso repositório, deixei um arquivo pronto para você utilizar nesta
importação, o import_user.txt. Você mesmo pode também criar um
arquivo com os registros, seguindo o padrão em que eles estão dispostos. Ele
deve possuir as colunas que você quer popular.
Em nosso código, vamos descrever o nome do arquivo que queremos im-
portar. Precisamos que ele esteja em alguma pasta em nosso computador.
Coloque-o no diretório c:/ ou onde preferir, contanto que não se esqueça
de alterar o local no código.
139
11.4. Localizar uma coluna no seu banco
Casa do Código
Além de informarmos qual será o arquivo importado, devemos também
dizer para qual tabela queremos fazer a importação. Sabendo disso, vamos
indicar em qual delas queremos inserir os registros e com qual caractere eles
estão separados e limitados, da mesma maneira como os exportamos.
mysql> load data infile 'c:/import_user.txt'
into table comuser
fields terminated by ','
enclosed by '''';
11.4
Localizar uma coluna no seu banco
Algo muito útil é uma consulta para saber a quais tabelas um campo pertence.
Particularmente, utilizo-a todos os dias, uma vez que trabalho em um cená-
rio de mais de 1000 tabelas e fica difícil decorá-las. Por exemplo, em nosso
projeto, criamos a coluna n_numeclien em duas tabelas. Desta maneira, é
mais fácil saber em quais tabelas estão. Porém, como sempre friso, pense no
longo prazo e tenha em mente que seu projeto crescerá.
Cada SGBD possui tabelas, nas quais são armazenados os objetos cria-
dos, tais como: outras tabelas, views, procedures, triggers etc. No
MySQL, temos o information_schema, que é uma base de dados que
possui as tabelas de metadados. São informações sobre o que temos cria-
dos no banco. A tabela que armazena as informações das outras criadas no
SGBD é a columns. Para exemplificar, vamos utilizar o nosso banco de da-
dos comercial e pesquisar quais delas possuem o campo n_numeclien.
mysql> select table_schema Banco_Dados,
table_name tabela,
column_name nome_coluna
from information_schema.columns
where table_schema = 'comercial'
and column_name = 'n_numeclien';
O resultado para a consulta que fizemos deve ser:
140
Casa do Código
Capítulo 11. MySQL avançado
Fig. 11.1: Busca de Colunas
Algo que faço para otimizar o mais meu tempo é salvar essa consulta em
um arquivo com a extensão .sql e apenas chamá-la pelo prompt, em vez
de digitá-la toda vez ou copiar e colá-la. Isso é algo que você pode fazer com todas as que são constantemente utilizadas.
A primeira coisa que devemos fazer é salvar a nossa consulta. Porém,
antes vamos substituir o nome do banco e o da coluna que buscamos ante-
riormente para que essas variáveis sejam inseridas antes de executá-la. No
lugar do nome do banco, vamos colocar @banco e no da coluna, @coluna.
O símbolo de arroba diz para o SGBD que serão variáveis que serão recebidas
em execução. Nossa consulta ficará assim:
mysql> select table_schema Banco_Dados,
table_name tabela,
column_name nome_coluna
from information_schema.columns
where table_schema = @banco
and column_name = @coluna;
Como possuo várias consultas que são utilizadas frequentemente, criei
uma pasta chamada scripts no diretório c:\ para armazenar os seus ar-
quivos. Vamos nomeá-la como busca_campo.sql. Agora, abra o console
do MySQL para atribuirmos os valores para as variáveis e, em seguida, fazer-
mos a chamada.
mysql> set @banco = 'comercial';
mysql> set @coluna = 'n_numeclien';
mysql> source c:/scripts/campo_tabela.sql;
141
11.5. Ferramentas para MySQL
Casa do Código
Você pode utilizar a criação de arquivos para qualquer tipo de consulta.
Acho interessante você utilizar esse recurso para os comandos que aprende-
mos no início desse capítulo, pois seu uso ficará mais ágil. Em nosso repositó-
rio, há uma pasta scripts, na qual existem vários arquivos com as consul-
tas que já utilizamos, inclusive uma chamada info_banco.sql. Ele, ao ser
executado, trará várias informações sobre o seu banco, tais como: quantidade
de tables, de views e outros objetos. Baixe e utilize-o em seu dia a dia!
11.5
Ferramentas para MySQL
Ferramentas com interface
Durante todo o livro, utilizei o próprio console do MySQL para fazer as opera-
ções. Ele é um pouco limitado, mas se você aprender a utilizá-lo, conseguirá
usar qualquer outro tipo de ferramenta. As ferramentas com interface são
como IDEs. Segue uma lista que pode deixar a utilização do MySQL mais
eficiente.
• Workbench: é uma ferramenta tudo-em-um para tarefas, como: ge-
renciar seu servidor, escrever consultas, desenvolver procedimentos e
trabalhar com diagramas. MySQL Workbench possui versão gratuita
e comercial. Ela é mais do que suficiente para a maioria das neces-
sidades. Você pode saber mais em http://www.mysql.com/products/
workbench.
• SQLyog: SQLyog é uma das ferramentas visuais mais populares para
MySQL com muitas características interessantes. É da mesma classe
que o MySQL Workbench, mas algumas ferramentas tem funcionali-
dades que a outra não tem. Ela está disponível apenas para Windows
e possui uma edição limitada gratuita, e uma cheia de recursos, que é
paga. Mais informações em https://www.webyog.com.
• phpMyAdmin: é uma ferramenta de administração de servidores web
mais utilizada. Oferece uma interface baseada em browser para seus
servidores MySQL. Deve-se ter cuidado em utilizá-la, principalmente
com a segurança de seu sistema, uma vez que, se o seu banco estiver
142
Casa do Código
Capítulo 11. MySQL avançado
hospedado em um servidor web, o acesso ao phpMyAdmin possivel-
mente também dará acesso via web. Mais informações estão disponí-
veis em http://www.phpmyadmin.net.
Escolha aquela que mais lhe agrade e a que melhor se adapte, e tenha um
ótimo desenvolvimento.
Ferramentas open source para monitoramento
Se você tornar-se responsável pelo desempenho do servidor de banco de
dados e pelo monitoramento dos processos, você precisará de uma ferramenta
para lhe auxiliar nessas tarefas. Existem várias para monitorar a infraestru-
tura de serviços, porém poucas ferramentas boas e gratuitas que sejam exclu-
sivas para o MySQL. Por isso, aconselho apenas uma: o Monyog. Ele é excelente para data base administrators (DBAs) administrar seus banco de dados.
Ele monitora o desempenho de consultas, espaço em disco, automatização de
backups e muitas outras automatizações. Mais informações estão disponíveis
em https://www.webyog.com/product.
Se você tem interesse por infraestrutura, pode pesquisar mais sobre es-
tas excelentes ferramentas: Nagios, Opsview e Icinga. Além do MySQL, você
também terá o monitoramento de todos os serviços do seu servidor.
Conforme aprendemos uma tecnologia, observamos que não existe uma
separação muito bem definida sobre básico, intermediário e avançado. O que
existe são categorias de determinados assuntos.
Durante o livro, vimos tópicos que são considerados avançados por al-
guns, mas fazem parte da rotina do desenvolvimento de software. Isso faz
com que o assunto seja apenas algo a se praticar. Diferente de tópicos de ad-
ministração do SGBD, que não farão parte do seu trabalho e que são uma
categoria muito grande, nada impede que você se aprofunde no assunto de
administração. Ou que também vá para a área de administração de banco de
dados, se torne um DBA ou que continue no desenvolvimento e saiba bastante
de administração. As possibilidades são infinitas.
143
Capítulo 12
Guia de consulta rápida
“A genialidade é 1% inspiração e 99% transpiração.”
– Thomas Edson
12.1
O guia
Todos os guias rápidos que encontro são apenas palavras com significados,
mas nunca um exemplo com a sintaxe completa. Por isso, resolvi colocar no
final do livro algo que pudesse realmente lhe ajudar.
Aqui você encontrará a sintaxe de todos os comandos que aprendemos
durante nosso projeto e também alguns novos. Utilize o guia depois da leitura
do livro e de praticar todos os exemplos. Ele lhe ajudará a lembrar as sintaxe que você ainda não decorou.
12.2. Comandos ddl e dml
Casa do Código
12.2
Comandos ddl e dml
Comandos ddl
mysql> create table nome_tabela(
nome_coluna type,
primary key(coluna_primaria);
mysql> alter table nome_tabela add nome_coluna type;
mysql> alter table nome_tabela drop column nome_coluna;
mysql> alter table nome_tabela modify nome_coluna type;
mysql> drop table nome_tabela;
mysql> alter table nome_tabela add constraint primary key
nome_constraint(nome_coluna);
mysql> alter table nome_tabela add constraint nome_constraint
foreign key(nome_coluna)
references nome_tabela_referenciada(
nome_coluna_referenciada)
on delete no action
on update no action;
mysql> alter table nome_tabela drop constraint nome_constraint;
Comandos dml
mysql> insert into nome_tabela(nome_coluna)
values (valores);
mysql> delete from nome_tabela
where codicoes;
mysql> update nome_tabela set nome_coluna = valor
where codicoes;
146
Casa do Código
Capítulo 12. Guia de consulta rápida
12.3
Tipos de dados
Escolha o tipo de texto que precisa para guardar cada informação.
Tipo texto
• CHAR(tamanho) : guarda um número fixo de caracteres. Pode conter
letras, números e caracteres especiais. O tamanho deve ser declarado
entre parênteses. Guarda até 255 caracteres.
• VARCHAR(tamanho): possui as características do tipo CHAR, com
a diferença de que, se você criar com mais de 255 caracteres, ele
transforma-se no tipo TEXT. Ou seja, se for criar algum campo com
mais de 255, já crie como TEXT.
• TEXT: guarda uma string com o tamanho máximo de 65.535 caracteres.
• BLOB: é o tipo de dado medido pela quantidade de bytes, em vez de
pela quantidade de caracteres, conforme a maioria. Pode salvar por
imagens, por exemplo, com o máximo de 65.535 bytes de arquivo.
Tipo numérico
• TINYINT: guarda números do tipo inteiro. Suporta de -128 até 127
caracteres.
• SMALLINT: guarda números do tipo inteiro. Suporta de -32768 até
32767 caracteres.
• MEDIUMINT: guarda números do tipo inteiro. Suporta de -8388608
até 8388607 caracteres.
• INT(tamanho): guarda números inteiros. Suporta de -2147483648 até
2147483647 caracteres. O número máximo de caracteres pode ser espe-
cificado entre parênteses.
• BIGINT:
guarda
números
do
tipo
inteiro.
Suporta
de
-
9223372036854775808 até 9223372036854775807 caracteres.
147
12.4. Consultas
Casa do Código
• FLOAT(tamanho,decimal): guarda números REAIS. O número má-
ximo de caracteres pode ser especificado entre parênteses. Deve-se es-
pecificar o tamanho inteiro e o tamanho numérico da coluna.
• DOUBLE(tamanho,decimal): guarda números REAIS. O número
máximo de caracteres pode ser especificado entre parênteses. Deve-
se especificar o tamanho inteiro e o tamanho numérico da coluna. Esse
tipo armazena uma quantidade maior de número do que os campos do
tipo FLOAT.
Tipo data e tempo
• DATE(): tipo de campo que armazenará datas no formato YYYY-MM-
DD, onde Y refere-se ao ano, M ao mês e D ao dia.
• DATETIME(): a combinação de data e tempo no formato YYYY-MM-
DD HH:MI:SS.
• TIME(): armazena horas, minutos e segundos no formato HH:MI:SS.
12.4
Consultas
Consultas básicas
mysql> select * from nome_tabela;
mysql> select * from nome_tabela order by 1;
mysql> select * from nome_tabela order by 1;
mysql> select *
from nome_tabela
where codicoes
order by 1;
mysql> select *
from nome_tabela
148
Casa do Código
Capítulo 12. Guia de consulta rápida
where nome_coluna = valor;
mysql> select *
from nome_tabela
where nome_coluna <> valor;
mysql> select *
from nome_tabela
where nome_coluna in (select nome_coluna
from nome_tabela2);
mysql> select *
from nome_tabela
where nome_coluna not in (select nome_coluna
from nome_tabela2);
mysql> select campo1,
campo2
from nome_tabela
group by campo1
order by campo1;
Consultas com funções
Aprendemos algumas funções no capítulo 6 e outras durante o livro. Além
das quais já aprendemos, incluí algumas novas.
Funções de agregação
## calcula o valor médio referente a uma coluna - avg()
mysql>select format(avg(campo_numerico),2) 'avarage price'
from nome_tabela;
## para contar registros - count()
mysql> select count(*) from nome_tabela;
## verificar quantidade - having count()
mysql> select campo1, count(campo2)
from nome_tabela
149
12.4. Consultas
Casa do Código
having count(campo2) > 1;
## valor máximo e valor mínimo - max() / min()
mysql> select max(campo1), min(campo1)
from nome_tabela;
## somar campos - sum()
mysql> select sum(campo1) from nome_tabela;
## selecionar registros distintos de uma coluna - distinct()
mysql> select distinct(campo1)
from nome_tabela;
Funções numéricas
## retorna o arco co-seno de número acos(numero)
## ou null se x não estiver entre -1 e 1
mysql> select acos(numero) from nome_tabela;
## retorna o arco seno de número, asin(numero)
## ou null se número não estiver entre -1 e 1
mysql> select asin(numero) from nome_tabela;
## retorna o arco da tangente
mysql> select atan(numero) from nome_tabela;
## retorna o valor exponencial
mysql> select exp(numero) from nome_tabela;
## retorna o logaritmo natural base e
mysql> select log(3) from nome_tabela;
## retorna o logaritmo natural base 10
mysql> select log10(3) from nome_tabela;
## retorna a divisão de x por y.
mysql> select mod(x,y) from nome_tabela;
150
Casa do Código
Capítulo 12. Guia de consulta rápida
## retorna um valor aleatório
mysql> select rand(numero) from nome_tabela;
## arredondar números - round()
mysql> select round(campo_numerico)
from nome_tabela;
## tirar a raiz quadrada de um número - sqrt()
mysql> select sqrt(campo_numerico)
from nome_tabela;
Funções de string
## selecionar caracteres de uma string - substr
mysql> select substr(campo1,2)
from nome_tabela;
## contar quantidade de caracteres em uma string - length(campo)
mysql>select length(campo,2)
from nome_tabela;
## concatenar registros - concat() / concat_ws
mysql> select concat_ws(;,c_codiclien, c_razaclien, c_fantclien)
from comclien;
where c_razaclien like 'PEDR%';
## registros em mínusculo - lcase() / lower()
mysql> select lcase(c_razaclien)
from comclien;
## registros em maiúsculo - ucase()
mysql> select ucase(c_razaclien)
from comclien;
## completa uma string à direita com um caractere desejado
## na quantidade desejada
mysql> select rpad(string,10,' ')
from nome_coluna;
151
12.5. Programando rotinas
Casa do Código
Funcões de data
## retornar a diferença entre datas - datediff()
mysql>select ('2015-03-15','2015-03-17');
## converter de string para data - str_to_date()
mysql>select str_to_date('2013','%y');
## consulta a data e hora atual - now()
mysql>select now();
## retorna o dia do mês de uma data
mysql> select dayofmonth(data) from nome_tabela;
## retorna o valor numérico do dia da semana
mysql> select dayofweek(data) from nome_tabela;
12.5
Programando rotinas
Procedure
Para criar processos para o SGBD automatizar tarefas.
mysql> delimiter $$
mysql> create procedure
processa_comissionamento(in|out|inout parametro tipo)
begin
instruções;
end
mysql> $$
mysql> delimiter ;
Function
Crie para facilitar o seu dia a dia, retornando exatamente o que você pre-
cisa nas consultas.
152
Casa do Código
Capítulo 12. Guia de consulta rápida
mysql> create function rt_nome_cliente(vn_numeclien int)
returns varchar(50)
begin
declare variavel_retorno datatype;
instruções;
return retorno;
end
mysql> $$
mysql> delimiter ;
Event scheduler
Crie agendamentos para otimização de seus processos.
mysql> create event processa_comissao
on schedule every 1 [year|week|day|hour|minute|second]
starts 'data_hora_qualquer'
do
begin
instruções;
Trigger
Uma forma de disparar processos automaticamente através de alterações
em registros de uma determinada tabela, como gatilhos.
mysql> create or replace trigger nome_trigger
[before|after insert|update|delete] on nome_tabela
for each row
begin
intruções;
end;
mysql> drop trigger nome_trigger;
153
12.6. Desempenho
Casa do Código
12.6
Desempenho
Index
Dê mais desempenho às suas consultas.
mysql> alter table nome_tabela
add index nome_index(nome_coluna);
mysql> alter table nome_tabela
add unique index nome_index(nome_coluna);
mysql> show index from nome_tabela;
mysql> alter table nome_tabela drop index nome_index;
View
Não fique reescrevendo códigos repetitivos. Crie views.
mysql> create or replace view nome_view as
select campos
from tabelas
where codições;
mysql> drop view nome_view;
12.7
Manutenção do banco
Backup
Crie backups com frequência. Ele poderá salvar seu sistema um dia!
## Exportando uma tabela
mysql\bin> mysqldump -u root -p
comercial comclien > c:/bkp_clien.sql
## Exportando tudo
mysql\bin> mysqldump -u root -p --routines --triggers
154
Casa do Código
Capítulo 12. Guia de consulta rápida
comercial > c:/bkp_full.sql
## Importando tudo
mysql> mysql\bin> mysql -h localhost -u root -p -d
comercial2 < c:/bkp_full.sql
Variáveis do ambiente
Para visualizarmos e alterarmos as variáveis do nosso ambiente. Use-as
com cuidado.
## Listar as variáveis
mysql> show VARIABLES;
mysql> set nome_da_variavel = novo_valor;
Conexões
Visualizando e matando as conexões ativas em nosso banco de dados.
## Visualizando conexões ativas
mysql> show processlist;
## Matando conexões ativas
mysql> kill numero_PID;
Como esse guia, sua consulta ficará mais rápida. Também coloquei-o em
meu blog para ajudá-lo quando estiver longe do livro. Para acessar, utilize o
link http://www.viniciuscdes.net/blog/guiarapidomysql. Espero que o auxilie
a escrever vários códigos legais.
155
Capítulo 13
Conclusão
“A jornada é a recompensa.”
– Steve Jobs
13.1
O guia
Este livro o ajudou a escrever desde códigos simples até os mais avançados.
Nos primeiros dois capítulos, tivemos uma introdução sobre o MySQL e inici-
amos o planejamento do projeto que desenvolvemos ao seu decorrer. Nunca
esqueça de planejar e documentar seus projetos. Já no capítulo 3, começamos
a criar os códigos de nossas tabelas, conforme planejamos inicialmente.
Em seguida, quando já tínhamos nosso banco de dados e tabelas criados,
faltavam-nos registros para serem manipulados. Portanto, no capítulo 4, os
inserimos para popular nossas tabelas. Assim, começamos a brincar com eles
por meio das consultas criadas no capítulo 5.
13.1. O guia
Casa do Código
Aumentamos a dificuldade dos códigos e criamos consultas aprimoradas
no capítulo 6, por meio do uso das functions. Aprendemos que o SGBD
é uma poderosa ferramenta para processamento de cálculos e registros e que
podemos usar esse poder para otimizar tarefas através das procedures, as-
sunto tratado no capítulo 7. Além de aprender a programar automaticamente
esses processos, no capítulo 8, vimos também como criar rotinas que eram
disparadas por outras ações: as nossas triggers.
Quando há muitas informações e rotinas no banco de dados, devemos
começar a pensar no aprimoramento da performance. No capítulo 9, mostrei
como podemos otimizar as consultas através dos índices e também como não
perder tempo criando a mesma diversas vezes por meio das views.
No capítulo 10, vimos o importante papel dos backups. Exportamos e
importamos diversos deles do nosso banco de dados. Coloque-os em sua
rotina de trabalho e nunca tenha problemas com perda de dados.
No capítulo 11, mostrei uma breve e importante introdução ao MySQL
avançado e algumas instruções de seu gerenciamento. Para finalizar, no ca-
pítulo 12 criei um guia de consulta rápida com os principais comandos SQL,
especialmente para auxiliá-lo no dia a dia!
Explorei ao máximo a aplicabilidade do MySQL no cotidiano do desen-
volvedor com exemplos reais, para que você consiga desenvolver o que pre-
cisar. Contudo, como todas as linguagens de programação, ele também exige
dedicação e treinamento para você se aperfeiçoar e otimizar seus códigos.
Não pare de estudar e programar. Keep coding!
Criei o fórum http://tinyurl.com/p38plj4 para que possamos manter contato e
trocar experiências. Nele, poderemos discutir sobre banco de dados e, espe-
cialmente, sobre MySQL. Postem suas perguntas, dificuldades, sugestões e até
soluções para os problemas do dia a dia. Estarei sempre à disposição para tirar dúvidas ou ajudar naqueles obstáculos que quase nos deixam loucos. Então,
é isso. Até breve!
158