SQL e modelagem com bando de dados

Alura
Sumário
Sumário
1Objetivosdocurso
1
1.1Oqueérealmenteimportante?
1
1.2Sobreosexercícios
1
1.3Tirandodúvidaseindoalém
2
2Meuproblema
3
2.1Criandoonossobancodedados
5
2.2Começandoumcadernonovo:criandoobanco
5
2.3Opadrãoutilizadonestecurso
6
2.4Atabeladecompras
6
2.5Conferindoaexistênciadeumatabela
8
2.6Inserindoregistrosnobancodedados
9
2.7Selecãosimples
10
2.8Aformataçãodenúmerosdecimais
10
2.9Achaveprimária
11
2.10Recriandoatabeladozero
13
2.11Consultascomfiltros
14
2.12Modelandotabelas
19
2.13Resumindo
21
2.14Exercícios
21
3Atualizandoeexcluindodados
23
3.1UtilizandooUPDATE
24
3.2Atualizandováriascolunasaomesmotempo
25
3.3Utilizandoumacolunacomoreferênciaparaoutracoluna
25
3.4UtilizandooDELETE
26
3.5CuidadoscomoDELETEeUPDATE
27
3.6Resumindo
27
4Alterandoerestringindooformatodenossastabelas
29
Sumário
Alura
4.1Restringindoosnulos
30
4.2AdicionandoConstraints
30
4.3ValoresDefault
31
4.4Evoluçãodobanco
31
4.5Resumindo
33
5Agrupandodadosefazendoconsultasmaisinteligentes
35
5.1Ordenandoosresultados
37
5.2Resumindo
41
6Juntandodadosdeváriastabelas
42
6.1Normalizandonossomodelo
47
6.2OnetoMany/ManytoOne
50
6.3FOREIGNKEY
50
6.4Determinandovaloresfixosnatabela
58
6.5ServerSQLModes
59
6.6Resumindo
60
7AlunossemmatrículaeoExists
62
7.1Subqueries
65
7.2Resumindo
70
8AgrupandodadoscomGROUPBY
8.1Resumindo
9FiltrandoagregaçõeseoHAVING
72
79
81
9.1CondiçõescomHAVING
83
9.2Resumindo
86
10MúltiplosvaloresnacondiçãoeoIN
10.1Resumindo
11Sub-queries
11.1Resumindo
87
93
94
101
12EntendendooLEFTJOIN
103
12.1RIGHTJOIN
109
12.2JOINouSUBQUERY?
111
12.3Resumindo
119
13MuitosalunoseoLIMIT
120
13.1Limitandoebuscandoapartirdeumaquantidadeespecífica
121
13.2Resumindo
123
Alura
Versão:19.4.23
Sumário
CAPÍTULO1
OBJETIVOSDOCURSO
que o aluno saia apto a utilizar qualquer sistema de banco de dados relacional (exemplos:
MySQL, Oracle, PostgreSQL, SQL Server). Para isso usamos sempre que possível o padrão
SQLquetodoselesaceitam.ParaacompanharaapostilasugerimosoMySQL,paraquepasse
pelosmesmosdesafiosesoluçõesqueencontramosnessecurso.Apósaprenderabaseatodos
eles,estudardetalhesespecíficosdecadabancopassaaserbemmaissimples.
salientarqueousodealgunsconceitos,comoasvantagensedesvantagensdamodelagem,são
entendidosporcompletodepoisdeumtempodeprática,alémdemudaremcomotempo.
mostrarquedecorarcomandosnãoéimportante.
1.1OQUEÉREALMENTEIMPORTANTE?
Muitoslivros,aopassardoscapítulos,mencionamtodososdetalhesdeumaferramentajuntamentecom
seus princípios básicos. Isso acaba criando muita confusão, em especial porque o estudante não
conseguedistinguirexatamenteoqueéprimordialaprendernoinício,daquiloquepodeserestudado
maisadiante.Essetipodeinformaçãoseráadquiridacomotempo,enãoénecessárionoinício:essetipo
de filtragem já fizemos e aplicamos aqui para você aprender a medida certa, e se aprofundar no
momentoquefizersentidocontinuandoseusestudosemoutroscursosnossosnoAlura.
Nestecurso,separamosessasinformaçõesemquadrosespeciais,jáquesãoinformaçõesextras.Ou
então, apenas citamos num exercício e deixamos para o leitor procurar informações se for de seu
interesse.
Porfim,faltamencionaralgosobreaprática,quedevesertratadaseriamente:todososexercíciossão
muitoimportantes.
Ocurso
Paraaquelesqueestãofazendoocursoonlinenowww.alura.com.br,recomendamosestudaremem
casaaquiloquefoivistoduranteaaula,tentandoresolverosexercíciosedesafiosapresentados.
1.2SOBREOSEXERCÍCIOS
1OBJETIVOSDOCURSO
1
Os exercícios do curso variam de práticos até pesquisas na Internet, ou mesmo consultas sobre
assuntosavançadosemdeterminadostópicosparaincitaracuriosidadedoaprendiznatecnologia.
1.3TIRANDODÚVIDASEINDOALÉM
Paratirardúvidasdosexercícios,oudedesenvolvimentoemgeral,recomendamosofórumdoGUJ
(http://www.guj.com.br/).OGUJfoifundadopordesenvolvedoresdoAluraedaCaelum,ehojeconta
commaisdeummilhãodemensagens.
Quandoterminaressaaventuraesperoencontrá-lonoAlura,ondepoderácontinuarcomdiversos
outroscursosnaáreadedesenvolvimentoetecnologia.
http://www.alura.com.br/
Seoquevocêestábuscandosãolivrosdeapoio,sugerimosconheceraeditoraCasadoCódigo:
http://www.casadocodigo.com.br
SOBREOCURSOBANCODEDADOSEMODELAGEM
BemvindoaocursodeBancodeDadoseModelagemdoAlura.Nessaapostilavocêvaiencontrara
transcriçãodocursoquepodeserencontradoemsuaversãomaisrecenteemwww.alura.com.br.
Comodesenvolvedorpasseimaisde15anosprocurandoexemplosreaisdeensinoparapoderajudar
meus colegas desenvolvedores que estagiavam na minha equipe, e junto da equipe de instrutores do
Alura,agrupeiessesexemplosnessecurso.
Nosso foco aqui é mostrar o que é um banco de dados relacional, como pensar a modelagem de
nossosdadosecomocriar,inserir,atualizarepesquisarinformaçõesemumabasededados.Tudoisso
usandoexemplosdomundoreal,nadadeabstraçõesgenéricasquenoslevamaquestionarousorealda
tecnologianodiaadia.
ComousamosoMySQLcomosoftwaredebancodedadosnestecurso,vocêpoderáexecutartodos
osexemplosnasuamáquinaemcasamesmo.
2
1.3TIRANDODÚVIDASEINDOALÉM
CAPÍTULO2
MEUPROBLEMA
Chegou o fim do mês e não sei direito onde meu dinheiro foi parar, no meu bolso não está. Nem na
contabancária.Éumproblemacomum,ecomopodemosfazerparacontrolarosgastos?
A maneira mais simples de entender onde o dinheiro está indo é anotar todos os nossos gastos,
sendoumadasmaistradicionaiseantigasescrevê-losemumcaderninho.Porexemplodia05/01/2016
gasteiR$20emumaLanchonete,escrevoissobonitinhoemumalinha.
R$2005/01/2016Lanchonete
Tiveumoutrogastodelanchonetenodia06/01/2016novalordeR$15,voueanotonovamente:
R$2005/01/2016Lanchonete
R$1506/01/2016Lanchonete
Porfim,minhaesposacomprouumguarda-roupaproquartoeeleaindanãochegou,entãoanotei:
R$2005/01/2016Lanchonete
R$1506/01/2016Lanchonete
R$915,506/01/2016Guarda-roupa(nãorecebi)
Reparaquedojeitoqueanotei,aslinhasacabamseassemelhandoaumatabelinha:
+---------+------------+--------------+--------------+
+R$20+05/01/2016+Lanchonete+recebida+
+R$15+06/01/2016+Lanchonete+recebida+
+R$915,5+06/01/2016+Guarda-roupa+nãorecebida+
+---------+------------+--------------+--------------+
Masoquesignificaaprimeiracolunadessatabelamesmo?Ovalorgasto?Ok.Easegunda?Éadata
dacompra?Eaterceira,éolugarquecompreiousãoanotaçõesrelativasaosgastos?Reparaquesemdar
nomeascolunas,minhascomprasficamconfusas,atabelaficaestranha,epossocadavezpreenchercom
algoqueachoquedevo,aoinvésdetercertezadoquecadacamposignifica.Comoidentificarcadaum
deles? Vamos dar um nome aos três campos que compõem a minha tabela: o valor, a data e as
observações:
+---------+------------+--------------+--------------+
+valor+data+observacoes+recebida+
+---------+------------+--------------+--------------+
+R$20+05/01/2016+Lanchonete+recebida+
+R$15+06/01/2016+Lanchonete+recebida+
+R$915,5+06/01/2016+Guarda-roupa+nãorecebida+
+---------+------------+--------------+--------------+
2MEUPROBLEMA
3
Possotambémsimplificarosdadosdacolunarecebidasomenteindicandoseelafoirecebida(sim)
ounão(não):
+---------+------------+--------------+--------------+
+valor+data+observacoes+recebida+
+---------+------------+--------------+--------------+
+R$20+05/01/2016+Lanchonete+sim+
+R$15+06/01/2016+Lanchonete+sim+
+R$915,5+06/01/2016+Guarda-roupa+não+
+---------+------------+--------------+--------------+
Para montar essa tabela em um caderno eu tenho que ser capaz de traçar linhas bonitinhas e eu,
pessoalmente,souhorrívelparaisso.Nomundotecnológicodehojeemdia,anotaríamosessatabelaem
umaplanilhaeletrônicacomooExcel:
Figura2.1:Planilhaexemplo
UMBANCOPARAOSDADOS
Porém, além de guardar as informações, eu quero manter um histórico, tirar média semestral ou
mensal,saberquantoqueeugasteicomlanchoneteoumercadoduranteumperíodoerealizartarefas
futuras como relatórios complexos. O Excel é uma ferramenta bem poderosa e flexível, porém,
dependendodaquantidadederegistros,amanipulaçãodosdadoscomeçaaficarumpoucocomplicada
emumaplanilhaeletrônica.
Parafacilitaronossotrabalho,existemsoftwaresparaarmazenar,guardarosdados.Essesbancosde
dados são sistemas que gerenciam desde a forma de armazenar a informação como também como
consultá-lademaneiraeficiente.
OschamadosSistemadeGerenciamentodeBancodeDados(SGBD),nospermitemrealizartodas
essastarefassemnospreocuparmoscomocaderninhoouacaneta.Claroquesevamosnoscomunicar
com alguém que vai gerenciar nossos dados precisamos falar uma língua comum, conversar em uma
linguagempadrãoqueváriosbancosutilizem.
Umpadrãoquesurgiuparaacessarmosepesquisarmosdadosarmazenadoseestruturadosdeuma
forma específica é uma linguagem de consultas estruturadas, Structured Query Language, ou
simplesmenteSQL.Paraconseguirmosutilizaressalinguagem,precisamosinstalarumSGBDqueseráo
nossoservidordebancodedados,comoporexemplooMySQL,OracleouSQLServer.
4
2MEUPROBLEMA
NestecursoutilizaremosoMySQL,umSGBDgratuitoquepodeserinstaladoseguindoasmaneiras
tradicionaisdecadasistemaoperacional.Porexemplo,noWindows,oprocessoérealizadoatravésdo
downloaddeumarquivo.msi,enquantonoLinux(versõesbaseadasemDebian),podeserfeitoatravés
de um simples comando apt-get, e no MacOS pode ser instalado com um pacote .dmg. Baixe o seu
instaladoremhttp://dev.mysql.com/downloads/mysql/
2.1CRIANDOONOSSOBANCODEDADOS
Durante todo o curso usaremos o terminal do MySQL. Existe também a interface gráfica do
WorkbenchdoMySQL,quenãoutilizaremos.Aoutilizarmosoterminalnãonospreocupamoscomo
aprendizadodemaisumaferramentaqueéopcional,podendonosconcentrarnoSQL,queéoobjetivo
destecurso.
Abraoterminaldoseusistemaoperacional.NoWindows,digitecmdnoExecutar.NoMaceLinux,
abraoterminal.Nele,vamosentrarnoMySQLusandoousuárioroot:
mysql-uroot-p
O -u indica o usuário root, e o -p é porque digitaremos a senha. Use a senha que definiu
duranteainstalaçãodoMySQL,notequeporpadrãoasenhapodeserembrancoe,nessecaso,basta
pressionarenter.
2.2COMEÇANDOUMCADERNONOVO:CRIANDOOBANCO
AgoraqueestamosconectadosaoMySQLprecisamosdizeraelequequeremosumcadernonovo.
Começaremos com um que gerenciará todos os dados relativos aos nossos gastos, permitindo que
controlemos melhor nossos gastos, portanto quero um banco de dados chamado ControleDeGastos,
podeparecerestranho,masnãoexisteumpadrãoparanomearumbancodedados,porissoutilizamoso
padrãoCamelCase.ParacriarumbancodedadosbastamandaroMySQLcriarumbanco:
mysql>CREATEDATABASE
Masqualonomemesmo?ControleDeGastos?Então:
mysql>CREATEDATABASEControleDeGastos
DouentereoMySQLficaesperandomaisinformações:
mysql>CREATEDATABASEControleDeGastos
->
AcontecequeemgeralprecisamosnotificaroMySQLqueocomandoquedesejamosjáfoidigitado
porcompleto.Parafazerissousamosocaracterepontoevírgula(;):
mysql>CREATEDATABASEControleDeGastos
->;
QueryOK,1rowaffected(0.01sec)
2.1CRIANDOONOSSOBANCODEDADOS
5
Agorasim,elepercebeuqueacabamosnossocomando(quecomeçounaprimeiralinha)eindicou
queopedido(aquery)foiexecutadacomsucesso(OK),demorou0.01segundoegerou1resultado(1
rowaffected).
Damesmamaneiraquecriamosessebanco,nãoéumaregra,masécomumterumbancodedados
paracadaprojeto.Porexemplo:
mysql>CREATEDATABASEcaelum
->;
QueryOK,1rowaffected(0.01sec)
mysql>CREATEDATABASEalura
->;
QueryOK,1rowaffected(0.01sec)
mysql>CREATEDATABASEcasadocodigo
->;
QueryOK,1rowaffected(0.01sec)
Agoraquetenhodiversosbancos,comopossodizeraoMySQLquequeremosusaraqueleprimeiro
de todos? Quero dizer para ele, por favor, use o banco chamado ControleDeGastos. Usar em inglês, é
USE,portanto:
mysql>USEControleDeGastos;
Databasechanged
2.3OPADRÃOUTILIZADONESTECURSO
Reparequeaspalavrasquesãodeminhaautoria,minhaescolha,deixamosemminúsculo,enquanto
as palavras que são específicas da linguagem SQL deixamos em maiúsculo. Esse é um padrão como
existem diversos outros padrões. Adotamos este para que a qualquer momento que você veja um
comandoSQLnestecurso,identifiqueoqueépalavraimportante(palavrachave)doSQLeoqueéo
nome de um banco de dados, uma tabela, uma coluna ou qualquer outro tipo de palavra que é uma
escolhalivreminha,comousuáriodobanco.
2.4ATABELADECOMPRAS
Agoraquejátenhoumcaderno,jáestouloucoparaescreveromeuprimeirogasto,quefoinuma
lanchonete.Porexemplo,pegueiumapartedomeucaderno,deverdade,de2016:
+---------+------------+--------------+--------------+
+valor+data+observacoes+recebida+
+---------+------------+--------------+--------------+
+R$20+05/01/2016+Lanchonete+sim+
+R$15+06/01/2016+Lanchonete+sim+
+R$915,5+06/01/2016+Guarda-roupa+não+
+...+...+...+...+
+---------+------------+--------------+--------------+
Ok.Querocolocaraminhaprimeiraanotaçãonobanco.Masseobancoéocaderno...comoqueo
6
2.3OPADRÃOUTILIZADONESTECURSO
bancosabequeexisteumatabeladevalor,dataeobservações?Aliás,atémesmoquandocomeceicomo
meucaderno,fuieu,Guilherme,quetivequedesenharatabelacomsuastrêscoluninhasemcadauma
daspáginasqueeuquiscolocarseusdados.
Então vamos fazer a mesma coisa com o banco. Vamos dizer a ele que queremos ter uma tabela:
descrevemosaestruturadelaatravésdeumcomandoSQL.Nessecomandodevemosdizerquaissãoos
campos(valor,dataeobservações)queserãoutilizados,paraqueelesepareoespaçoespecíficoparacada
um deles toda vez que inserimos uma nova compra, toda vez que registramos um novo dado (um
registronovo).
Paracriarumatabela,novamentefalamosinglês,porfavorsenhorMySQL,crieumatabela(CREATE
TABLE)chamadacompras:
mysql>CREATETABLEcompras;
ERROR1113(42000):Atablemusthaveatleast1column
Como assim erro 1113? Logo após o código do erro, o banco nos informou que toda tabela deve
conterpelomenosumacoluna.Verdade,nãofaleiascolunasquequeriacriar.Vamosolharnovamente
nossasinformações:
+---------+------------+--------------+--------------+
+valor+data+observacoes+recebida+
+---------+------------+--------------+--------------+
+R$20+05/01/2016+Lanchonete+sim+
+R$15+06/01/2016+Lanchonete+sim+
+R$915,5+06/01/2016+Guarda-roupa+não+
+...+...+...+...+
+---------+------------+--------------+--------------+
A estrutura da tabela é bem clara: são 4 campos distintos, valor que é um número com ponto
decimal,dataéumadata,observaçõesqueéumtextolivreerecebidaqueéousimounão.
Portanto vamos dizer ao banco que queremos esses 4 campos na nossa tabela, com uma única
ressalva, que para evitar problemas de encoding usamos sempre nomes que usam caracteres simples,
nadadeacentoparaosnomesdenossastabelasecolunas:
mysql>CREATETABLEcompras(
valor,
data,
observacoes,
recebida);
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourM
ySQLserverversionfortherightsyntaxtousenear'
data,
observacoes,
recebida)'atline2
Ainda não foi dessa vez. Repara que o erro 1064 indica um erro de sintaxe no comando CREATE
TABLE.Naverdadeobancodedadosestáinteressadoemsaberqualotipodecadacampoejustamente
por isso devemos explicar campo a campo qual o tipo de cada coluna nossa. A coluna, campo, valor
recebeumvalorcompossíveiscasasdecimais.Paraissopodemosusarumtipoquerepresentanúmeros
2.4ATABELADECOMPRAS
7
decimais.Nonossocasosuportandoaté18casasantesdavírgulae2depoisdela:
valorDECIMAL(18,2),
Jáocampodataédotipodedata:
dataDATE,
A coluna observações é um texto livre, que costuma ser chamado por um conjunto variável de
caracteres, por isso VARCHAR. Assim como no caso do número decimal, devemos falar o tamanho
máximodessacoluna.Nonossocasoserãoaté255caracteres:
observacoesVARCHAR(255),
Porfimchegamosacolunarecebida,quedeveserrepresentadacomvaloresdotipoverdadeiro ou
falso,algocomosimounão.Nopadrãodebancorelacionaisnãoexisteumcampoqueaceiteosvalores
verdadeiro e falso, um campo booleano. O que fazemos então é utilizar um campo numérico com os
valores0pararepresentaronegativoe1paraocasopositivo.Umacolunacomumnumerozinho,um
TINYINT:
recebidaTINYINT
Sevamosutilizar0e1paraorecebida,nossosdadosficariamagora:
+---------+------------+--------------+----------+
+valor+data+observacoes+recebida+
+---------+------------+--------------+----------+
+R$20+05/01/2016+Lanchonete+1+
+R$15+06/01/2016+Lanchonete+1+
+R$915,5+06/01/2016+Guarda-roupa+0+
+...+...+...+...+
+---------+------------+--------------+----------+
Agoraquejásabemoscomodeclararcadacampo,vamosanovatentativadecriarnossatabela:
mysql>CREATETABLEcompras(
valorDECIMAL(18,2),
dataDATE,
observacoesVARCHAR(255),
recebidaTINYINT);
QueryOK,0rowsaffected(0.04sec)
Atabelacomprasfoicriadacomsucesso.
2.5CONFERINDOAEXISTÊNCIADEUMATABELA
Masvocêconfiaemtudoquetodomundofala?Vamosverserealmenteatabelafoicriadaemnosso
bancodedados.Sevocêpossuisseumcaderninhodetabelas,oqueeupoderiafazerparapedirparame
mostrarsuatabeladecompras?Porfavor,medescrevesuatabeladecompras?
mysql>desccompras;
+-------------+---------------+------+-----+---------+-------+
|Field|Type|Null|Key|Default|Extra|
+-------------+---------------+------+-----+---------+-------+
8
2.5CONFERINDOAEXISTÊNCIADEUMATABELA
|valor|decimal(18,2)|YES||NULL||
|data|date|YES||NULL||
|observacoes|varchar(255)|YES||NULL||
|recebida|tinyint(4)|YES||NULL||
+-------------+---------------+------+-----+---------+-------+
4rowsinset(0.01sec)
Eaíestánossatabeladecompras.Elapossui4colunas,quesãochamadosdecampos(fields). Cada
campoédeumtipodiferente(decimal,date,varcharetinyint)epossuiatémesmoinformaçõesextras
queutilizaremosnodecorrerdocurso!
2.6INSERINDOREGISTROSNOBANCODEDADOS
Chegouahoradeusarnossatabela:queremosinserirdados,entãocomeçamospedindoparainserir
algoemcompras:
INSERTINTOcompras
Nãovamoscometeromesmoerrodaúltimavez.Sedesejamosfazeralgocomos4camposdatabela,
temos que falar os valores que desejamos adicionar. Por exemplo, vamos pegar as informações da
primeiralinhadaplanilha:
Figura2.2:Planilhaexemplo
Então nós temos uma compra com valor 20, observação Lanchonete, data 05/01/2016, e que foi
recebidacomsucesso(1):
mysql>INSERTINTOcomprasVALUES(20,'Lanchonete','05/01/2016',1);
ERROR1292(22007):Incorrectdatevalue:'Lanchonete'forcolumn'data'atrow1
Parece que nosso sistema pirou. Ele achou que a Lanchonete era a data. Lanchonete era o campo
observacoes.Mas...comoelepoderiasaberisso?Eunãomencioneiparaeleaordemdosdados,entãoele
assumiu uma ordem determinada, uma ordem que eu não prestei atenção, mas foi fornecida quando
executamosoDESCcompras:
|valor|decimal(18,2)|YES||NULL||
|data|date|YES||NULL||
|observacoes|varchar(255)|YES||NULL||
|recebida|tinyint(4)|YES||NULL||
Tentamosnovamente,agoranaordemcorretadoscampos:
mysql>INSERTINTOcomprasVALUES(20,'05/01/2016','Lanchonete',1);
ERROR1292(22007):Incorrectdatevalue:'05/01/2016'forcolumn'data'atrow1
Outro erro? Agora ele está dizendo que o valor para data está incorreto, porém, aqui no Brasil,
usamosesseformatoparadatas...Oquefaremosagora?Quetaltentarmosutilizaroformatoquejávem
2.6INSERINDOREGISTROSNOBANCODEDADOS
9
porpadrãonoMySQLqueéano-mês-dia?
Vamostentarmaisumavez,porém,dessavez,comadata'2016-01-05':
mysql>INSERTINTOcomprasVALUES(20,'2016-01-05','Lanchonete',1);
QueryOK,1rowaffected(0.01sec)
Agora sim os dados foram inseridos com sucesso. Temos um novo registro em nossa tabela. Mas
porque será que o MySQL adotou o formato ano-mês-dia ao invés de dia/mês/ano? O formato
ano-mês-diaéutilizadopelopadrãoSQL,ouseja,porquestõesdepadronização,eleéoformatomais
adequadoparaquefuncioneparatodos.
2.7SELECÃOSIMPLES
Como fazemos para conferir se ele foi inserido? Pedimos para o sistema de banco de dados
selecionartodososcampos(asterisco)danossatabeladecompras.Queremosfazerumaconsulta(uma
query)deseleção(SELECT)na(FROM)tabelacompras:
mysql>SELECT*FROMcompras;
+-------+------------+-------------+----------+
|valor|data|observacoes|recebida|
+-------+------------+-------------+----------+
|20.00|2016-01-05|Lanchonete|1|
+-------+------------+-------------+----------+
1rowinset(0.00sec)
Perfeito! Registro inserido e selecionado com sucesso. Hora de revisar um errinho que deixamos
paratrás.
2.8AFORMATAÇÃODENÚMEROSDECIMAIS
Desejamosinserirosdoispróximosregistrosdenossatabela:
+---------+------------+--------------+----------+
+valor+data+observacoes+recebida+
+---------+------------+--------------+----------+
+R$20+05/01/2016+Lanchonete+1+
+R$15+06/01/2016+Lanchonete+1+
+R$915,5+06/01/2016+Guarda-roupa+0+
+...+...+...+...+
+---------+------------+--------------+----------+
PortantooprimeiroINSERT:
mysql>INSERTINTOcomprasVALUES(15,'2016-01-06','Lanchonete',1);
QueryOK,1rowaffected(0.01sec)
Easegundalinha:
mysql>INSERTINTOcomprasVALUES(915,5,'2016-01-06','Guarda-roupa',0);
ERROR1136(21S01):Columncountdoesn'tmatchvaluecountatrow1
10
2.7SELECÃOSIMPLES
Oerroindicaqueonúmerodecolunasnãocondizcomonúmerodecolunasquetemosnatabela.
VamosdarumaolhadadentrodeVALUES?
(915,5,'2016-01-06','Guarda-roupa',0)
Avírgulaapareceu4vezes,portantoteríamos5camposdiferentes!Reparequeovalor 915,5está
formatadonoestiloportuguêsdoBrasil,diferentedoestiloinglêsamericano,queusapontoparadefinir
ovalordecimal,comoem915.5.Alteramoseexecutamos:
mysql>INSERTINTOcomprasVALUES(915.5,'2016-01-06','Guarda-roupa',0);
QueryOK,1rowaffected(0.00sec)
Podemosconferiros3registrosqueinserimosatravésdoSELECTquefizemosantes:
mysql>SELECT*FROMcompras;
+--------+------------+--------------+----------+
|valor|data|observacoes|recebida|
+--------+------------+--------------+----------+
|20.00|2016-01-05|Lanchonete|1|
|15.00|2016-01-06|Lanchonete|1|
|915.50|2016-01-06|Guarda-roupa|0|
+--------+------------+--------------+----------+
3rowsinset(0.01sec)
Maseseeuquisesseinserirosdadosemumaordemdiferente?Porexemplo,primeiroinformara
data,depoisas observacoes,valor,eporfimsefoi recebidaounão?Seráquepodemosfazer
isso? Quando estamos fazendo um INSERT, podemos informar o que estamos querendo inserir por
meiodeparênteses:
mysql>INSERTINTOcompras(data,observacoes,valor,recebida)
Notequeagoraestamosinformandoexplicitamenteaordemdosvaloresqueinformaremosapósa
instruçãoVALUES:
mysql>INSERTINTOcompras(data,observacoes,valor,recebida)
VALUES('2016-01-10','Smartphone',949.99,0);
QueryOK,1rowaffected(0,00sec)
Seconsultarmosanossatabela:
mysql>SELECT*FROMcompras;
+--------+------------+--------------+----------+
|valor|data|observacoes|recebida|
+--------+------------+--------------+----------+
|20.00|2016-01-05|Lanchonete|1|
|15.00|2016-01-06|Lanchonete|1|
|915.50|2016-01-06|Guarda-roupa|0|
|949.99|2016-01-10|Smartphone|0|
+--------+------------+--------------+----------+
4rowsinset(0,00sec)
Anossacomprafoiinseridacorretamente,porémcomumaordemdiferentedaestruturadatabela.
2.9ACHAVEPRIMÁRIA
2.9ACHAVEPRIMÁRIA
11
Agoraseeuolharessaminhatabeladepoisde6meseseencontraressaslinhas,comovoufalarsobre
umadasminhascompras?Tenhoquefalar"lembradacompranalanchonetefeitanodia2016-01-05no
valorde20.00quejáfoirecebida?".Ficadifícilreferenciarcadalinhadatabelaportodososvaloresdela.
Seriamuitomaisfácilseeupudessefalar:"sabeacompra15?"ou"sabeacompra37654?".
Fica difícil falar sobre um registro se eu não tenho nada identificando ele de maneira única. Por
exemplo, como você sabe que eu sou o Guilherme, e não o Paulo? Pois meu CPF é um identificador
único, (por exemplo) 222.222.222-22. Só eu tenho esse CPF e ele me identifica. O Paulo tem o dele,
333.333.333-33.
O mesmo vale para computadores de uma marca, por exemplo, o meu tem número de série
16X000015, e o computador que está ao lado do meu tem o número de série 16X000016. A única
maneiradeidentificarunicamenteumacoisaétendoumachaveimportantíssima,umachavequeétão
importante,queéprimáriaaosbrasileiros,aoscomputadores...easminhascompras.
Mas como definir esses números únicos? Devo usar o ano de fabricação como no caso do
computadorquecomeçacom16(ano2016)?Ousequenciascomnúmerosdiferentescomonocasodo
CPF?
Existem diversas maneiras de gerar esses números únicos, mas a mais simples de todas é: começa
com 1. A primeira compra é a compra 1. A segunda compra é a 2. A terceira é a 3. Uma sequencia
natural,queéincrementadaautomaticamenteacadanovacompra,acadanovoregistro.
Éissoquequeremos,umcampoquesejanossachaveprimária(PRIMARYKEY),queéumnúmero
inteiro(INT),equesejaautomaticamenteincrementado(AUTO_INCREMENT).Sófaltadefinironome
dele, que como identifica nossa compra, usamos a abreviação id. Esse é um padrão amplamente
utilizado.PortantoqueremosumcampoidINTAUTO_INCREMENTPRIMARYKEY.
Para fazer isso vamos alterar nossa tabela ( ALTER_TABLE ) e adicionar uma coluna nova
(ADD_COLUMN):
mysql>ALTERTABLEcomprasADDCOLUMNidINTAUTO_INCREMENTPRIMARYKEY;
QueryOK,0rowsaffected(0.07sec)
Records:0Duplicates:0Warnings:0
Everificamosoresultadoemnossatabela:
mysql>SELECT*FROMcompras;
+--------+------------+--------------+----------+----+
|valor|data|observacoes|recebida|id|
+--------+------------+--------------+----------+----+
|20.00|2016-01-05|Lanchonete|1|1|
|15.00|2016-01-06|Lanchonete|1|2|
|915.50|2016-01-06|Guarda-roupa|0|3|
|949.99|2016-01-10|Smartphone|0|4|
+--------+------------+--------------+----------+----+
4rowsinset(0,00sec)
Reparequeagoratodasascompraspodemseridentificadasunicamente!
12
2.9ACHAVEPRIMÁRIA
2.10RECRIANDOATABELADOZERO
Claroqueopadrãonãoécriaro iddepoisdeumano.Emgeralcriamosumatabelajácomoseu
campo id. Vamos então adaptar nossa query para criar a tabela com todos os campos, inclusive o
campoid:
CREATETABLEcompras(
idINTAUTO_INCREMENTPRIMARYKEY,
valorDECIMAL(18,2),
dataDATE,
observacoesVARCHAR(255),
recebidaTINYINT);
ERROR1050(42S01):Table'compras'alreadyexists
Opa, como a tabela já existe não faz sentido recriá-la. Primeiro vamos jogar ela fora (DROP ),
inclusivecomtodososdadosquetemos:
DROPTABLEcompras;
Agoracriamosatabelacomtudooqueconhecemos:
CREATETABLEcompras(
idINTAUTO_INCREMENTPRIMARYKEY,
valorDECIMAL(18,2),
dataDATE,
observacoesVARCHAR(255),
recebidaTINYINT);
QueryOK,0rowsaffected(0.04sec)
Vamosverificarseficoualgumregistro:
mysql>SELECT*FROMcompras;
Emptyset(0,00sec)
Ótimo!Conseguimoslimparanossatabela,agorapodemosreenserirnossosdadosnovamente:
mysql>INSERTINTOcomprasVALUES(20,'2016-01-05','Lanchonete',1);
ERROR1136(21S01):Columncountdoesn'tmatchvaluecountatrow1
Ops,agoraanossatabelanãotemapenas4colunascomoantes,incluímosoidtambém,vejamos
adescriçãodatabelacompras:
mysql>desccompras;
+-------------+---------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+---------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|valor|decimal(18,2)|YES||NULL||
|data|date|YES||NULL||
|observacoes|varchar(255)|YES||NULL||
|recebida|tinyint(4)|YES||NULL||
+-------------+---------------+------+-----+---------+----------------+
5rowsinset(0,00sec)
Entãoprecisaremosdeixarexplícitooqueestamosinserindo:
2.10RECRIANDOATABELADOZERO
13
mysql>INSERTINTOcompras(valor,data,observacoes,recebida)
VALUES(20,'2016-01-05','Lanchonete',1);
QueryOK,1rowaffected(0,01sec)
Agorasimanossacomprafoicadastrada!Porém,temalgoumpoucoestranho,eoid?Seráquefoi
inseridotambém?Vejamos:
mysql>SELECT*FROMcompras;
+----+-------+------------+-------------+----------+
|id|valor|data|observacoes|recebida|
+----+-------+------------+-------------+----------+
|1|20.00|2016-01-05|Lanchonete|1|
+----+-------+------------+-------------+----------+
1rowinset(0,00sec)
Ele foi inserido automaticamente! Mas como isso aconteceu? Lembra que definimos o id como
AUTO_INCREMENT? Aquela propriedade que incrementa automaticamente a cada inserção. Então, é
exatamenteelaqueincrementouoidpranós!Agorabastaapenasinserirasdemaiscompraseverificar
oresultado:
mysql>INSERTINTOcompras(valor,data,observacoes,recebida)
VALUES(15,'2016-01-06','Lanchonete',1);
QueryOK,1rowaffected(0,00sec)
mysql>INSERTINTOcompras(valor,data,observacoes,recebida)
VALUES(915.50,'2016-01-06','Guarda-roupa',0);
QueryOK,1rowaffected(0,01sec)
mysql>INSERTINTOcompras(valor,data,observacoes,recebida)
VALUES(949.99,'2016-01-10','Smartphone',0);
QueryOK,1rowaffected(0,00sec)
Vamosverificarcomoficaramosregistrosdasnossascompras:
mysql>SELECT*FROMcompras;
+----+--------+------------+--------------+----------+
|id|valor|data|observacoes|recebida|
+----+--------+------------+--------------+----------+
|1|20.00|2016-01-05|Lanchonete|1|
|2|15.00|2016-01-06|Lanchonete|1|
|3|915.50|2016-01-06|Guarda-roupa|0|
|4|949.99|2016-01-10|Smartphone|0|
+----+--------+------------+--------------+----------+
4rowsinset(0,00sec)
Excelente!Agoraonossosistemacadastranossascomprasdamaneiraesperada!
2.11CONSULTASCOMFILTROS
Aprendemosacriarasnossastabelaseinserirregistrosnelas,entãovamosadicionartodasasnossas
compras. Eu já tenho um arquivo com as minhas compras irei executá-lo, não se preocupe, nos
exercíciosforneceremosolinkdoarquivo.Saiadoterminalcomocomandoexit:
mysql>exit
14
2.11CONSULTASCOMFILTROS
Executeocomando:
mysql-uroot-pControleDeGastos<compras.sql
Agora todas as compras foram registradas no nosso banco de dados! Vamos consultar todas as
comprasnovamente:
mysql>SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+
|id|valor|data|observacoes|recebida|
+----+----------+------------+------------------------------+----------+
|1|20.00|2016-01-05|Lanchonete|1|
|2|15.00|2016-01-06|Lanchonete|1|
|3|915.50|2016-01-06|Guarda-roupa|0|
|4|949.99|2016-01-10|Smartphone|0|
|5|200.00|2012-02-19|Materialescolar|1|
|6|3500.00|2012-05-21|Televisao|0|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|
|8|163.45|2012-12-15|Pizzaprafamilia|1|
|9|4780.00|2013-01-23|Saladeestar|1|
|10|392.15|2013-03-03|Quartos|1|
|11|1203.00|2013-03-18|Quartos|1|
|12|402.90|2013-03-21|Copa|1|
|13|54.98|2013-04-12|Lanchonete|0|
|14|12.34|2013-05-23|Lanchonete|0|
|15|78.65|2013-12-04|Lanchonete|0|
|16|12.39|2013-01-06|Sorvetenoparque|0|
|17|98.12|2013-07-09|HopiHari|1|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|
|19|3212.40|2013-11-13|Comprasdomes|1|
|20|223.09|2013-12-17|Comprasdenatal|1|
|21|768.90|2013-01-16|Festa|1|
|22|827.50|2014-01-09|Festa|1|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|
|24|678.43|2014-05-21|PassagempraBahia|1|
|25|10937.12|2014-04-30|CarnavalemCancun|1|
|26|1501.00|2014-06-22|Presentedasogra|0|
|27|1709.00|2014-08-25|Parceladacasa|0|
|28|567.09|2014-09-25|Parceladocarro|0|
|29|631.53|2014-10-12|IPTU|1|
|30|909.11|2014-02-11|IPVA|1|
|31|768.18|2014-04-10|GasolinaviagemPortoAlegre|1|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|
|33|115.90|2014-06-12|Diadosnamorados|0|
|34|98.00|2014-10-12|Diadascrianças|0|
|35|253.70|2014-12-20|Natal-presentes|0|
|36|370.15|2014-12-25|Comprasdenatal|0|
|37|32.09|2015-07-02|Lanchonete|1|
|38|954.12|2015-11-03|ShowdaIveteSangalo|1|
|39|98.70|2015-02-07|Lanchonete|1|
|40|213.50|2015-09-25|Roupas|0|
|41|1245.20|2015-10-17|Roupas|0|
|42|23.78|2015-12-18|LanchonetedoZé|1|
|43|576.12|2015-09-13|Sapatos|1|
|44|12.34|2015-07-19|Canetas|0|
|45|87.43|2015-05-10|Gravata|0|
|46|887.66|2015-02-02|Presenteparaofilhao|1|
+----+----------+------------+------------------------------+----------+
46rowsinset(0,00sec)
Anossaconsultadevolveutodasasnossascompras,maseuquerosaberadatadetodasascompras
2.11CONSULTASCOMFILTROS
15
"baratas" ou no caso, com valor abaixo de 500. Em SQL podemos adicionar filtros com o argumento
WHERE:
mysql>SELECT*FROMcomprasWHEREvalor<500;
+----+--------+------------+------------------------------+----------+
|id|valor|data|observacoes|recebida|
+----+--------+------------+------------------------------+----------+
|1|20.00|2016-01-05|Lanchonete|1|
|2|15.00|2016-01-06|Lanchonete|1|
|5|200.00|2012-02-19|Materialescolar|1|
|8|163.45|2012-12-15|Pizzaprafamilia|1|
|10|392.15|2013-03-03|Quartos|1|
|12|402.90|2013-03-21|Copa|1|
|13|54.98|2013-04-12|Lanchonete|0|
|14|12.34|2013-05-23|Lanchonete|0|
|15|78.65|2013-12-04|Lanchonete|0|
|16|12.39|2013-01-06|Sorvetenoparque|0|
|17|98.12|2013-07-09|HopiHari|1|
|20|223.09|2013-12-17|Comprasdenatal|1|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|
|33|115.90|2014-06-12|Diadosnamorados|0|
|34|98.00|2014-10-12|Diadascrianças|0|
|35|253.70|2014-12-20|Natal-presentes|0|
|36|370.15|2014-12-25|Comprasdenatal|0|
|37|32.09|2015-07-02|Lanchonete|1|
|39|98.70|2015-02-07|Lanchonete|1|
|40|213.50|2015-09-25|Roupas|0|
|42|23.78|2015-12-18|LanchonetedoZé|1|
|44|12.34|2015-07-19|Canetas|0|
|45|87.43|2015-05-10|Gravata|0|
+----+--------+------------+------------------------------+----------+
24rowsinset(0,00sec)
Verificamosascomprasmaisbaratas,maseparaverificarascomprasmaiscaras?Porexemplo,com
valor acima de 1500? Usamos o WHERE novamente indicando que agora queremos valores acima de
1500:
mysql>SELECT*FROMcomprasWHEREvalor>1500;
+----+----------+------------+------------------------+----------+
|id|valor|data|observacoes|recebida|
+----+----------+------------+------------------------+----------+
|6|3500.00|2012-05-21|Televisao|0|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|
|9|4780.00|2013-01-23|Saladeestar|1|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|
|19|3212.40|2013-11-13|Comprasdomes|1|
|25|10937.12|2014-04-30|CarnavalemCancun|1|
|26|1501.00|2014-06-22|Presentedasogra|0|
|27|1709.00|2014-08-25|Parceladacasa|0|
+----+----------+------------+------------------------+----------+
8rowsinset(0,00sec)
Sabemostodasascomprasmaiscaras,porémeusóquerosabertodascomprasmaiscarasequenão
foram entregues ao mesmo tempo. Para isso precisamos adicionar mais um filtro, ou seja, filtrar por
comprasacimade1500etambémquenãoforamentregues:
mysql>SELECT*FROMcomprasWHEREvalor>1500ANDrecebida=0;
+----+---------+------------+-------------------+----------+
16
2.11CONSULTASCOMFILTROS
|id|valor|data|observacoes|recebida|
+----+---------+------------+-------------------+----------+
|6|3500.00|2012-05-21|Televisao|0|
|26|1501.00|2014-06-22|Presentedasogra|0|
|27|1709.00|2014-08-25|Parceladacasa|0|
+----+---------+------------+-------------------+----------+
3rowsinset(0,00sec)
Agoraquepodemosutilizarmaisdeumfiltrovamosverificartodasascomprasmaisbaratas(abaixo
de500)emaiscaras(acimade1500):
mysql>SELECT*FROMcomprasWHEREvalor<500ANDvalor>1500;
Emptyset(0,00sec)
Parecequenão funcionouadicionar maisdeum filtropara amesmacoluna... Vamosanalisar um
pouco a nossa query, será que realmente está fazendo sentido esse nosso filtro? Observe que estamos
tentandopegarcomprasquetenhamovalorabaixode500eaomesmotempotenhaumvaloracimade
1500.Seovalorfor300éabaixode500,porémnãoéacimade1500,seovalorfor1800éacimade1500,
porémnãoéabaixode500,ouseja,éimpossívelqueessefiltrosejaválido.Podemosfazerumpequeno
ajustenessefiltro,podemosindicarquequeremosvaloresquesejammenoresque500oumaioresque
1500:
mysql>SELECT*FROMcomprasWHEREvalor<500ORvalor>1500;
+----+----------+------------+------------------------------+----------+
|id|valor|data|observacoes|recebida|
+----+----------+------------+------------------------------+----------+
|1|20.00|2016-01-05|Lanchonete|1|
|2|15.00|2016-01-06|Lanchonete|1|
|5|200.00|2012-02-19|Materialescolar|1|
|6|3500.00|2012-05-21|Televisao|0|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|
|8|163.45|2012-12-15|Pizzaprafamilia|1|
|9|4780.00|2013-01-23|Saladeestar|1|
|10|392.15|2013-03-03|Quartos|1|
|12|402.90|2013-03-21|Copa|1|
|13|54.98|2013-04-12|Lanchonete|0|
|14|12.34|2013-05-23|Lanchonete|0|
|15|78.65|2013-12-04|Lanchonete|0|
|16|12.39|2013-01-06|Sorvetenoparque|0|
|17|98.12|2013-07-09|HopiHari|1|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|
|19|3212.40|2013-11-13|Comprasdomes|1|
|20|223.09|2013-12-17|Comprasdenatal|1|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|
|25|10937.12|2014-04-30|CarnavalemCancun|1|
|26|1501.00|2014-06-22|Presentedasogra|0|
|27|1709.00|2014-08-25|Parceladacasa|0|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|
|33|115.90|2014-06-12|Diadosnamorados|0|
|34|98.00|2014-10-12|Diadascrianças|0|
|35|253.70|2014-12-20|Natal-presentes|0|
|36|370.15|2014-12-25|Comprasdenatal|0|
|37|32.09|2015-07-02|Lanchonete|1|
|39|98.70|2015-02-07|Lanchonete|1|
|40|213.50|2015-09-25|Roupas|0|
|42|23.78|2015-12-18|LanchonetedoZé|1|
|44|12.34|2015-07-19|Canetas|0|
|45|87.43|2015-05-10|Gravata|0|
2.11CONSULTASCOMFILTROS
17
+----+----------+------------+------------------------------+----------+
32rowsinset(0,00sec)
Perceba que agora temos todas as compras que possuem valores abaixo de 500 e acima de 1500.
Suponhamosquefizemosumacompracomumvalorespecífico,porexemplo3500,equeremossaber
apenasascomprasquetiveramessevalor.Ofiltroquequeremosutilizaréoigualaovalordesejado:
mysql>SELECT*FROMcomprasWHEREvalor=3500;
+----+---------+------------+-------------+----------+
|id|valor|data|observacoes|recebida|
+----+---------+------------+-------------+----------+
|6|3500.00|2012-05-21|Televisao|0|
+----+---------+------------+-------------+----------+
1rowinset(0,00sec)
Conseguimos realizar várias queries com muitos filtros, porém, eu quero saber dentre as compras
realizadas,quaisforamemLanchonete.Entãovamosverificartodasasobservaçõesquesejamiguaisa
Lanchonete:
mysql>SELECT*FROMcomprasWHEREobservacoes='Lanchonete';
+----+-------+------------+-------------+----------+
|id|valor|data|observacoes|recebida|
+----+-------+------------+-------------+----------+
|1|20.00|2016-01-05|Lanchonete|1|
|2|15.00|2016-01-06|Lanchonete|1|
|13|54.98|2013-04-12|Lanchonete|0|
|14|12.34|2013-05-23|Lanchonete|0|
|15|78.65|2013-12-04|Lanchonete|0|
|37|32.09|2015-07-02|Lanchonete|1|
|39|98.70|2015-02-07|Lanchonete|1|
+----+-------+------------+-------------+----------+
7rowsinset(0,00sec)
Agora preciso saber todas as minhas compras que foram Parcelas. Então novamente eu usarei o
mesmofiltro,porémparaParcelas:
mysql>SELECT*FROMcomprasWHEREobservacoes='Parcelas';
Emptyset(0,00sec)
Queestranhoeulembrodeteralgumregistrodeparcela...Sim,existemregistrosdeparcelas,porém
não existe apenas uma observação "Parcela" e sim "Parcela do carro" ou "Parcela da casa", ou seja,
precisamos filtrar as observações verificando se existe um pedaço do texto que queremos na coluna
desejada.ParaverificarumpedaçodotextoutilizamosoargumentoLIKE:
mysql>SELECT*FROMcomprasWHEREobservacoesLIKE'Parcela%';
+----+---------+------------+------------------+----------+
|id|valor|data|observacoes|recebida|
+----+---------+------------+------------------+----------+
|27|1709.00|2014-08-25|Parceladacasa|0|
|28|567.09|2014-09-25|Parceladocarro|0|
+----+---------+------------+------------------+----------+
2rowsinset(0,00sec)
Perceba que utilizamos o "%". Quando adicionamos o "%" durante um filtro utilizando o LIKE
significa que queremos todos os registros que iniciem com Parcela e que tenha qualquer tipo de
18
2.11CONSULTASCOMFILTROS
informação a direita, ou seja, se a observação for "Parcela da casa" ou "Parcela de qualquer coisa" ele
retornaráparanós,massuponhamosquequiséssemossabertodasascomprascomobservaçõesemque
o"de"estivessenomeiodotexto?Bastariaadicionaro"%"tantonoinícioquantonofinal:
mysql>SELECT*FROMcomprasWHEREobservacoesLIKE'%de%';
+----+---------+------------+------------------------------+----------+
|id|valor|data|observacoes|recebida|
+----+---------+------------+------------------------------+----------+
|7|1576.40|2012-04-30|Materialdeconstrucao|1|
|9|4780.00|2013-01-23|Saladeestar|1|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|
|20|223.09|2013-12-17|Comprasdenatal|1|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|
|36|370.15|2014-12-25|Comprasdenatal|0|
+----+---------+------------+------------------------------+----------+
6rowsinset(0,00sec)
Vejaqueagoraforamdevolvidastodasascomprasquepossuemum"DE"nacoluna observacoes
independentedeondeesteja.
2.12MODELANDOTABELAS
Mascomofoiquechegamosnatabeladecomprasmesmo?Começamoscomumaidéiadosdados
que gostaríamos de armazenar: nossas compras. Cada uma delas tem um valor, uma data, uma
observação e se ela já foi recebida ou não. Seguindo essa idéia moldamos uma estrutura que compõe
umacompra,omoldedeumacompra:
Compra
==>temumvalor,comcasasdecimais
==>temumadata
==>temumaobservação,texto
==>podetersidorecebidaounão
Costumamosabreviaromodelocomo:
Compra
-valor
-data
-observacoes
-recebida
Eexistemdiversasmaneirasderepresentartalmodeloatravésdediagramas.Nossofocomaioraqui
seráempensarmosomodeloecomotrabalharmoscomoSQL,porissonãonospreocuparemostanto
comcriarcentenasdediagramas.
Após definirmos os campos importantes para nosso negócio, nossa empresa (o business),
percebemos que era necessário identificar cada compra de maneira única, e para isso definimos uma
chaveprimária,oid.
Tenho um novo cliente em minha empresa de desenvolvimento de software e este cliente é uma
escolaprimária.Elatemalunosbemnovoseprecisamanterumbancocomasinformaçõesdelespara
2.12MODELANDOTABELAS
19
fazerdiversaspesquisas.
Pensandosozinho,nomeutrabalho,tentomodelarumaluno:
Aluno
-id
-nome
-serie
-sala
-email
-telefone
-endereco
Pronto,apresentoomodelodomeubancoparaoclienteeelereclamamuito.Adiretoracomenta
quequandotemquesecomunicararespeitodoalunonãoéotelefoneouemaildelequeelaprecisa.Ela
precisaconversarcomospaiseporissoénecessáriooscamposdecontatodospais.Alteramosentão
nossomodelo:
Aluno
-id
-nome
-serie
-sala
-email
-telefone
-endereco
-nomeDoPai
-nomeDaMae
-telefoneDoPai
-telefoneDaMae
Um outro detalhe que sumiu é a questão da vacinação. A diretora gosta de conversar com os pais
para indicar quando é época de vacinação. Ela sabe da importância do efeito de vacinação em grupo
(todosdevemservacinados,seumnãoé,existechancedeinfectarmuitos).Porissoelasempreenvia
cartas (campo endereco) para os pais (campo nomeDoPai e nomeDaMae). Mas... como saber qual
vacinaeleprecisatomar?Dependedaidade,sugerimosentãoarmazenaradatadenascimentodoaluno:
Aluno
-id
-nascimento
-nome
-serie
-sala
-email
-telefone
-endereco
-nomeDoPai
-nomeDaMae
-telefoneDoPai
-telefoneDaMae
Além disso, ela comenta que este ano o aluno está na terceira série, mas ano que vem estará na
quartasérie!Tantoasériequantoasalasãovaloresquemudamanualmente,eprecisamosatualizá-los
sempre, quase que todos os alunos tem sua série e sala atualizadas de uma vez só (algumas escolas
permitem a troca de salas no meio do ano letivo). Para deixar isso claro, alteramos nossos campos,
20
2.12MODELANDOTABELAS
refletindoquetantoasériequantoasalasãoatuais:
Aluno
-id
-nascimento
-nome
-serieAtual
-salaAtual
-email
-telefone
-endereco
-nomeDoPai
-nomeDaMae
-telefoneDoPai
-telefoneDaMae
Essenossoprocessodecriar,demoldar,demodelarumatabelaéoquechamamosdemodelagemde
dados. Na verdade estamos modelando a estrutura que será capaz de receber os dados. E não existem
regras 100% fixas nesse processo, por isso mesmo é importantíssimo levantarmos as necessidades, os
requisitosdosnossosclientesjuntoaeles.Conversandocomeles,vendootrabalhodelesnodiaadia,
entendemosoqueelesprecisamecomomodelaressasinformaçõesnobanco.Emesmotendomodelado
umavez,podeserquedaquiumanotemosqueevoluirnossomodelo.
2.13RESUMINDO
Nósaprendemosacriarumbancodedados,criartabelascomoasplanilhasdoExceleagoraestamos
aprendendoamanipularinformaçõesfazendofiltrocom WHERE, AND, ORe LIKE.Vamosparaos
exercícios?
2.14EXERCÍCIOS
1. Instale o servidor do MySQL em sua máquina. Qual o sistema operacional em que você fez sua
instalação? Sentiu que algo podia ser simplificado no processo de instalação? Lembre-se que você
poderealizarodownloaddeambosemhttp://MySQL.com/downloads/MySQL.
Vocêpodeoptarporbaixaraversãomaisatual.
1. LoguenoMySQL,ecomececriandoobancodedados:
mysql-uroot-p
CREATEDATABASEControleDeGastos;
USEControleDeGastos;
Agoravamoscriaratabela.Elaprecisaterosseguintescampos: idinteiro, valornúmerocom
vírgula,data,observacoeseumbooleanoparamarcarseacomprafoirecebida.Atabeladeve-se
chamar"compras".
1. Cliqueaquiefaçaodownloaddoarquivo.sql,eimportenoMySQL:
2.13RESUMINDO
21
mysql-uroot-pControleDeGastos<compras.sql
Emseguida,executeoSELECTparagarantirquetodasasinformaçoesforamadicionadas:
SELECT*FROMcompras;
DICA: Salve o arquivo compras.sql em uma pasta de fácil acesso na linha de comando. Além
disso,oarquivodeveestarnomesmolugarondevocêexecutaráocomando.
1. Selecione valor e observacoes de todas as compras cuja data seja maior-ou-igual que
15/12/2012.
2. Qual o comando SQL para juntar duas condições diferentes? Por exemplo, SELECT * FROM
TABELAWHEREcampo>1000campo<5000.Façaotesteevejaoresultado.
3. Vimosquetodotextoépassadoatravésdeaspassimples(').Possopassaraspasduplas(")nolugar?
4. Selecione todas as compras cuja data seja maior-ou-igual que 15/12/2012 e menor do que
15/12/2014.
5. SelecionetodasascomprascujovalorestejaentreR$15,00eR$35,00eaobservaçãocomececoma
palavra'Lanchonete'.
6. Selecionetodasascomprasquejáforamrecebidas.
7. Selecionetodasascomprasqueaindanãoforamrecebidas.
8. VimosqueparaguardarovalorVERDADEIROparaacolunarecebida,devemospassarovalor1.
ParaFALSO,devemospassarovalor0.Equantoaspalavrasjáconhecidasparaverdadeiroefalso:
TRUEeFALSE.Elasfuncionam?Ouseja:
INSERTINTOcompras(valor,data,observacoes,recebida)VALUES(100.0,'2015-09-08','COMIDA',TRUE)
;
Funciona?Façaoteste.
1. Selecionetodasascomprascomvalormaiorque5.000,00ouquejáforamrecebidas.
2. Selecionetodasascomprasqueovalorestejaentre1.000,00e3.000,00ousejamaiorque5.000,00.
22
2.14EXERCÍCIOS
CAPÍTULO3
ATUALIZANDOEEXCLUINDODADOS
Cadastramos todas as nossas compras no banco de dados, porém, no meu rascunho onde eu coloco
todasasminhascomprasalgunsvaloresnãoestãobatendo.Vamosselecionarovaloreaobservaçãode
todasascomprascomvaloresentre1000e2000:
mysql>SELECTvalor,observacoesFROMcompras
WHEREvalor>=1000ANDvalor<=2000;
+---------+------------------------+
|valor|observacoes|
+---------+------------------------+
|1576.40|Materialdeconstrucao|
|1203.00|Quartos|
|1501.00|Presentedasogra|
|1709.00|Parceladacasa|
|1245.20|Roupas|
+---------+------------------------+
5rowsinset(0,00sec)
Fizemosumfiltroparaumintervalodevalor,ouseja,entre1000e2000.EmSQL,quandoqueremos
filtrarumintervalo,podemosutilizarooperadorBETWEEN:
mysql>SELECTvalor,observacoesFROMcompras
WHEREvalorBETWEEN1000AND2000;
+---------+------------------------+
|valor|observacoes|
+---------+------------------------+
|1576.40|Materialdeconstrucao|
|1203.00|Quartos|
|1501.00|Presentedasogra|
|1709.00|Parceladacasa|
|1245.20|Roupas|
+---------+------------------------+
5rowsinset(0,00sec)
Oresultadoéomesmoqueanossaqueryanterior,porémagoraestámaisclaraeintuitura.
Nomeurascunhoinformaqueacomprafoinoanode2013,porémnãofoifeitoessefiltro.Então
vamosadicionarmaisumfiltropegandoointervalode01/01/2013e31/12/2013:
mysql>SELECTvalor,observacoesFROMcompras
WHEREvalorBETWEEN1000AND2000
ANDdataBETWEEN'2013-01-01'AND'2013-12-31';
+---------+-------------+
|valor|observacoes|
+---------+-------------+
|1203.00|Quartos|
+---------+-------------+
1rowinset(0,00sec)
3ATUALIZANDOEEXCLUINDODADOS
23
3.1UTILIZANDOOUPDATE
Encontreiacompraquefoifeitacomovalorerrado,nomeurascunhoacompradeQuartosovalor
éde1500.Entãoagoraprecisamosalteraressevalornanossatabela.Paraalterar/atualizarvaloresem
SQLutilizamosainstruçãoUPDATE:
UPDATEcompras
AgoraprecisamosadicionaroquequeremosalterarpormeiodainstruçãoSET:
UPDATEcomprasSETvalor=1500;
Precisamos sempre tomar cuidado com a instrução UPDATE , pois o exemplo acima executa
normalmente,porémoqueelevaiatualizar?Nãoinformamosemmomentoalgumqualcompraprecisa
seratualizar,ouseja,eleiriaatualizarTODASascomprasenãoéissoquequeremos.Antesdeexecutar
oUPDATE,precisamosverificaroiddacompraquequeremosalterar:
mysql>SELECTid,valor,observacoesFROMcompras
WHEREvalorBETWEEN1000AND2000
ANDdataBETWEEN'2013-01-01'AND'2013-12-31';
+----+---------+-------------+
|id|valor|observacoes|
+----+---------+-------------+
|11|1203.00|Quartos|
+----+---------+-------------+
1rowinset(0,00sec)
Agoraquesabemosqualéoiddacompra,bastainformá-lopormeiodainstruçãoWHERE:
mysql>UPDATEcomprasSETvalor=1500WHEREid=11;
QueryOK,1rowaffected(0,01sec)
Rowsmatched:1Changed:1Warnings:0
Vamosverificarseovalorfoiatualizado:
mysql>SELECTvalor,observacoesFROMcompras
WHEREvalorBETWEEN1000AND2000
ANDdataBETWEEN'2013-01-01'AND'2013-12-31';
+---------+-------------+
|valor|observacoes|
+---------+-------------+
|1500.00|Quartos|
+---------+-------------+
1rowinset(0,00sec)
Analisando melhor essa compra eu sei que se refere aos móveis do quarto, mas uma observação
como"Quartos"quedizeroque?Nãoéclaraosuficiente,podeserquedaquia6meses,1anooumais
tempoeunemsaibamaisoqueserefereessaobservaçãoevouacabarpensando:"Seráqueeucomprei
móveisoucompreiumquartotodo?".Umtantoconfuso...Entãovamosalterartambémasobservaçõese
deixaralgomaisclaro,comoporexemplo:"Reformadequartos".
UPDATEcompraSETobservacoes='Reformadequartos'WHEREid=11;
Verificandoanovaobservação:
24
3.1UTILIZANDOOUPDATE
mysql>SELECTvalor,observacoesFROMcompras
->WHEREvalorBETWEEN1000AND2000
->ANDdataBETWEEN'2013-01-01'AND'2013-12-31';
+---------+--------------------+
|valor|observacoes|
+---------+--------------------+
|1500.00|Reformadequartos|
+---------+--------------------+
1rowinset(0,00sec)
3.2ATUALIZANDOVÁRIASCOLUNASAOMESMOTEMPO
AssimcomousamosoUPDATEparaatualizarumaúnicacoluna,poderíamosatualizardoisoumais
valores,separandoosmesmoscomvírgula.Porexemplo,sedesejasseatualizarovaloreasobservações:
mysql>UPDATEcomprasSETvalor=1500,observacoes='Reformadequartoscara'
->WHEREid=11;
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
Eoresultado:
mysql>SELECTvalor,observacoesFROMcomprasWHEREid=11;
+---------+-------------------------+
|valor|observacoes|
+---------+-------------------------+
|1500.00|Reformadequartoscara|
+---------+-------------------------+
1rowinset(0,00sec)
Muitocuidadoaqui!DiferentementedoWHERE,nocasodo SETnãoutilizeooperador ANDpara
atribuirvalores,comonoexemploaseguir,queNÃOéoquequeremos:
errado:UPDATEcomprasSETvalor=1500ANDobservacoes='Reformadequartosnovos'WHEREid=8;
certo:UPDATEcomprasSETvalor=1500,observacoes='Reformadequartosnovos'WHEREid=8;
3.3 UTILIZANDO UMA COLUNA COMO REFERÊNCIA PARA OUTRA
COLUNA
Esqueci de adicionar 10% de imposto que paguei na compra de id 11... portanto quero fazer algo
como:
mysql>SELECTvalorFROMcomprasWHEREid=11;
+---------+
|valor|
+---------+
|1500.00|
+---------+
1rowinset(0,00sec)
Descobriovaloratual,agoramultiplicopor 1.1paraaumentar 10%: 1500 * 1.1 são 1650
reais.Entãoatualizoagoramanualmente:
UPDATEcomprasSETvalor=1650ANDobservacoes='Reformadequartosnovos'WHEREid=11;
3.2ATUALIZANDOVÁRIASCOLUNASAOMESMOTEMPO
25
Maseseeuquisessefazeromesmoparadiversaslinhas?
mysql>SELECTvalorFROMcomprasWHEREid>11ANDid<=14;
+--------+
|valor|
+--------+
|402.90|
|54.98|
|12.34|
+--------+
3rowsinset(0,00sec)
UPDATE....eagora???
E agora? Vou ter que calcular na mão cada um dos casos, com 10% a mais, e fazer uma linha de
UPDATEparacadaum?AsoluçãoéfazerumúnicoUPDATEemquedigoquequeroalterarovalorpara
ovalordelemesmo,vezesosmeus1.1quejáqueriaantes:
UPDATEcomprasSETvalor=valor*1.1
WHEREid>=11ANDid<=14;
Nessaqueryficaclaroqueeupossousarovalordeumcampo(qualquer)paraatualizarumcampo
damesmalinha.Nonossocasousamosovalororiginalparacalcularonovovalor.Masestoulivrepara
usaroutroscamposdamesmalinha,desdequefaçasentidoparaomeuproblema,claro.Porexemplo,se
eutenhoumatabelade produtoscomoscampos precoLiquidoeupossoatualizaro precoBruto
quandooimpostomudarpara15%:
UPDATEprodutosSETprecoBruto=precoLiquido*1.15;
3.4UTILIZANDOODELETE
Observei o meu rascunho e percebi que essa compra eu não devia ter sido cadastrada na minha
tabela de compras, ou seja, eu preciso excluir esse registro do meu banco de dados. Em SQL, quando
queremosexcluiralgumregistro,utilizamosainstruçãoDELETE:
DELETEFROMcompras;
ODELETEtemocomportamentosimilarao UPDATE,ouseja,precisamossempretomarcuidado
quando queremos excluir algum registro da tabela. Da mesma forma que fizemos com o UPDATE,
precisamos adicionar a instrução WHERE para informa o que queremos excluir, justamente para
evitamosaexclusãodetodososdados:
mysql>DELETEFROMcomprasWHEREid=11;
QueryOK,1rowaffected(0,01sec)
Severificarmosnovamenteseexisteoregistrodessacompra:
mysql>SELECTvalor,observacoesFROMcompras
WHEREvalorBETWEEN1000AND2000
ANDdataBETWEEN'2013-01-01'AND'2013-12-31';
Emptyset(0,00sec)
26
3.4UTILIZANDOODELETE
Acomprafoiexcluídaconformeoesperado!
3.5CUIDADOSCOMODELETEEUPDATE
VimoscomooDELETEeUPDATEpodemserperigososembancodedados,senãodefinirmosuma
condição para cada uma dessas instruções podemos excluir/alterar TODAS as informações da nossa
tabela.Aboapráticaparaexecutaressasinstruçõesésempreescreverainstrução WHEREantes,ouseja,
definirprimeiroqualseráacondiçãoparaexecutaressasinstruções:
WHEREid=11;
EntãoadicionamosoDELETE/UPDATE:
DELETEFROMcomprasWHEREid=11;
UPDATEcomprasSETvalor=1500WHEREid=11;
Dessaformagarantimosqueonossobancodedadosnãotenharisco.
3.6RESUMINDO
NessecapítuloaprendemoscomofazerqueriespormeiodeintervalosutilizandooBETWEENecomo
alterar/excluirosdadosdanossatabelautilizandooDELETEeoUPDATE.Vamosparaosexercícios?
EXERCÍCIOS
1. Altereascompras,colocandoaobservação'preparandoonatal'paratodasasqueforamefetuadasno
dia20/12/2014.
2. AltereoVALORdascomprasfeitasantesde01/06/2013.SomeR$10,00aovaloratual.
3. Atualizetodasascomprasfeitasentre01/07/2013e01/07/2014paraqueelastenhamaobservação
'entregueantesde2014'eacolunarecebidacomovalorTRUE.
4. EmumcomandoWHEREépossívelespecificarumintervalodevalores.Paratanto,éprecisodizer
qualovalormínimoeovalormáximoquedefineointervalo.Qualéooperadorqueéusadopara
isso?
5. Qualoperadorvocêusapararemoverlinhasdecomprasdesuatabela?
6. Excluaascomprasrealizadasentre05e20marçode2013.
7. Existe um operador lógico chamado NOT. Esse operador pode ser usado para negar qualquer
condição.Porexemplo,paraselecionarqualquerregistrocomdatadiferentede03/11/2014,podeser
construídooseguinteWHERE:
3.5CUIDADOSCOMODELETEEUPDATE
27
WHERENOTDATA='2011-11-03'
UseooperadorNOTemonteumSELECTqueretornatodasascomprascomvalordiferentedeR$
108,00.
28
3.6RESUMINDO
CAPÍTULO4
ALTERANDOERESTRINGINDOO
FORMATODENOSSASTABELAS
Muitasvezesqueinserimosdadosemumbancodedados,precisamossaberquaissãoostiposdedados
de cada coluna da tabela que queremos popular. Vamos dar uma olhada novamente na estrutura da
nossatabelapormeiodainstruçãoDESC:
mysql>DESCcompras;
+-------------+---------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+---------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|valor|decimal(18,2)|YES||NULL||
|data|date|YES||NULL||
|observacoes|varchar(255)|YES||NULL||
|recebida|tinyint(4)|YES||NULL||
+-------------+---------------+------+-----+---------+----------------+
5rowsinset(0,00sec)
Como já vimos, o MySQL demonstra algumas características das colunas da nossa tabela. Vamos
verificaracolunaNull,oqueseráqueelasignifica?Seráqueestádizendoquenossascolunasaceitam
valoresvazios?Entãovamostentarinserirumacompracom observacoesvazia,ouseja,nula,como
valornull:
INSERTINTOcompras(valor,data,recebida,observacoes)
VALUES(150,'2016-01-04',1,NULL);
QueryOK,1rowaffected(0,01sec)
Vamosverificaroresultado:
mysql>SELECT*FROMcomprasWHEREdata='2016-01-04';
+----+--------+------------+-------------+----------+
|id|valor|data|observacoes|recebida|
+----+--------+------------+-------------+----------+
|47|150.00|2016-01-04|NULL|1|
+----+--------+------------+-------------+----------+
1rowinset(0,00sec)
Onossobancodedadospermitiuoregistrodeumacomprasemobservação.Masemqualmomento
informamosaoMySQLquequeriámosvaloresnulos?Emnenhummomento!Porém,quandocriamos
uma tabela no MySQL e não informamos se queremos ou não valores nulos para uma determinada
coluna,porpadrão,oMySQLaceitaosvaloresnulos.
Notequeumtextovazioédiferentedenãoternada!Édiferentedeumtextovaziocomo''.Nuloé
4ALTERANDOERESTRINGINDOOFORMATODENOSSASTABELAS
29
nada,éainexistênciadeumvalor.Umtextosemcaractereséumtextocomzerocaracteres.Umvalor
nulo nem texto é, nem nada é. Nulo é o não ser. Uma questão filosófica milenar, discutida entre os
melhoresfilósofoseprogramadores,alémdeserfontedemuitosbugs.Cuidado.
4.1RESTRINGINDOOSNULOS
Para resolver esse problema podemos criar restrições, Constraints, que tem a capacidade de
determinarasregrasqueascolunasdenossastabelasterão.AntesdeconfiguraroConstraints,vamos
verificartodososregistrosquetiveremobservaçõesnulasevamosapagá-los.Queremosselecionartodas
asobservaçõesquesãonulas,sãonulas,SÃONULAS,ISNULL:
mysql>SELECT*FROMcomprasWHEREobservacoesISNULL;
+----+--------+------------+-------------+----------+
|id|valor|data|observacoes|recebida|
+----+--------+------------+-------------+----------+
|47|150.00|2016-01-04|NULL|1|
+----+--------+------------+-------------+----------+
1rowinset(0,00sec)
Vamosexcluirtodasascomprasquetenhamasobservaçõesnulas:
DELETEFROMcomprasWHEREobservacoesISNULL;
QueryOK,1rowaffected(0,01sec)
4.2ADICIONANDOCONSTRAINTS
PodemosdefinirConstraintsnomomentodacriaçãodatabela,comonocasodedefinirquenosso
valoredatanãopodemsernulos(NOTNULL):
CREATETABLEcompras(
idINTAUTO_INCREMENTPRIMARYKEY,
valorDECIMAL(18,2)NOTNULL,
dataDATENOTNULL,
...
Porém,atabelajáexiste!Enãoéumaboapráticaexcluiratabelaecria-lanovamente,poispodemos
perder registros! Para fazer alterações na estrutura da tabela, podemos utilizar a instrução ALTER
TABLEquevimosantes:
ALTERTABLEcompras;
Precisamosespecificaroquequeremosfazernatabela,nessecasomodificarumacolunaeadicionar
umaConstraints:
mysql>ALTERTABLEcomprasMODIFYCOLUMNobservacoesVARCHAR(255)NOTNULL;
QueryOK,45rowsaffected(0,04sec)
Records:45Duplicates:0Warnings:0
Observequeforamalteradastodasas45linhasexistentesnonossobancodedados.Severificarmosa
estruturadanossatabelanovamente:
30
4.1RESTRINGINDOOSNULOS
mysql>DESCcompras;
+-------------+---------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+---------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|valor|decimal(18,2)|YES||NULL||
|data|date|YES||NULL||
|observacoes|varchar(255)|NO||NULL||
|recebida|tinyint(4)|YES||NULL||
+-------------+---------------+------+-----+---------+----------------+
5rowsinset(0,01sec)
Na coluna Null e na linha das observacoes está informando que não é mais permitido valores
nulos.Vamostestar:
mysql>INSERTINTOcompras(valor,data,recebida,observacoes)
VALUES(150,'2016-01-04',1,NULL);
ERROR1048(23000):Column'observacoes'cannotbenull
4.3VALORESDEFAULT
Vamossuporqueamaioriadascomprasqueregistramosnãosãoentreguesequequeremosqueo
próprioMySQLentendaque,quandoeunãoinformaracolunarecebida,elasejapopuladacomovalor
0. No MySQL, além de Constraints, podemos adicionar valores padrões, no inglês Default, em uma
colunautilizandoainstruçãoDEFAULT:
mysql>ALTERTABLEcomprasMODIFYCOLUMNrecebidatinyint(1)DEFAULT0;
QueryOK,0rowsaffected(0,00sec)
Records:0Duplicates:0Warnings:0
Inserindoumacompranovaseminformaacolunarecebida:
INSERTINTOcompras(valor,data,observacoes)
VALUES(150,'2016-01-05','Compradeteste');
QueryOK,1rowaffected(0,01sec)
Verificandoovalorinseridonacolunarecebida:
mysql>SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+
|id|valor|data|observacoes|recebida|
+----+----------+------------+------------------------------+----------+
|1|20.00|2016-01-05|Lanchonete|1|
|2|15.00|2016-01-06|Lanchonete|1|
|3|915.50|2016-01-06|Guarda-roupa|0|
|...|...|...|...|...|
|48|150.00|2016-01-05|Compradeteste|0|
+----+----------+------------+------------------------------+----------+
46rowsinset(0,00sec)
Sequisemosinformarqueovalorpadrãodeveriaserentregue,bastariautilizaromesmocomando,
porémmodificandoo0para1.
4.4EVOLUÇÃODOBANCO
4.3VALORESDEFAULT
31
Nossa escola deseja agora entrar em contato por email com todos os pais de alunos de um
determinado bairro devido a maior taxa de ocorrência de dengue. Como fazer isso se o campo de
endereçonãoobrigavaobairro?Podemosapartirdeagoraadicionarumnovocampo:
Aluno
-id
-nascimento
-nome
-serieAtual
-salaAtual
-email
-telefone
-bairro
-endereco
-nomeDoPai
-nomeDaMae
-telefoneDoPai
-telefoneDaMae
Alémdisso,depois deum anodescobrimosum bugno sistema.Algunsalunos sairamdo mesmo,
comorepresentarquenãoexistemaissérieatualenemsalaatualparaessesalunos?Podemosescolher
trêsabordagens:
1. marcaroscamposserieAtualesalaAtualcomoNULL
2. marcaroscamposcomoNULLecolocarumcampoativocomo0ou1
3. colocarumcampoativocomo0ou1
Reparequeoprimeiroeoúltimoparecemsersimplesdeimplementar,claramentemaissimplesque
implementar os dois ao mesmo tempo (item 2). Mas cada um deles implica em um problema de
modelagemdiferente.
Seassumimos(1)ecolocamoscamposserieAtualesalaAtualcomoNULL,todavezquequeremos
saberquemestáinativotemosumaquerybemestranha,quenãoparecefazerisso:
select*fromAlunoswhereserieAtualisnullandsalaAtualisnull
Sériomesmo?Issosignificaqueoalunoestáinativo?Doisnulos?Alémdisso,seeupermitonulos,eu
possoterumcasomuitoestranhocomooalunoaseguir:
GuilhermeSilveira,seriaAtual8,salaAtualnull
Como assim? É um aluno novo que alguém/o sistema esqueceu de colocar a sala? Ou é um aluno
antigoquealguém/osistemaesqueceuderemoverasérie?
Poroutrolado,naterceiraabordagem,adocampoativo,solto,geraumpossívelalunocomo:
GuilhermeSilveira,serieAtual8,salaAtualB,ativo0
Comoassim,oGuilhermeestáinativomasestána8Baomesmotempo?Oueleestána8Bounão
está,nãotemasduascoisasaomesmotempo.Modeloestranho.
32
4.4EVOLUÇÃODOBANCO
Por fim, a abordagem de modelagem 2, que utiliza o novo campo e o valor nulo, garante que as
queriessejamsimples:
select*fromAlunoswhereativo=false;
Masaindapermitecasosestranhoscomo:
GuilhermeSilveira,serieAtual8,salaAtualB,ativo0
GuilhermeSilveira,serieAtualNULL,salaAtualB,ativo0
GuilhermeSilveira,serieAtualNULL,salaAtualNULL,ativo1
Poderíamosusarrecursosbemmaisavançadosparatentarnosprotegerdealgunsdessescasos,mas
essesrecursossão,emgeral,específicosdeumbancodeterminado.Porexemplo,funcionamnoOracle
masnãonoMySQL.OunoMySQLmasnãonoPostgreSQL.NessecursofocamosempadrõesdoSQL
queconseguimosaplicar,emgeral,paratodoseles.
E agora? Três modelagens diferentes, todas resolvem o problema e todas geram outros problemas
técnicos.Modelarbancosétomardecisõesqueapresentamvantagensedesvantagens.Nãoháregraem
qual das três soluções devemos escolher, escolha a que você julgar melhor e mais justa para sua
empresa/sistema/ocasião. O mais importante é tentar prever os possíveis pontos de erro como os
mencionadosaqui.Conhecerasfalhasemnossodesenhodemodelagem(design)deantemãopermite
que não tenhamos uma surpresa desagradável quando descobrirmos elas de uma maneira infeliz no
futuro.
4.5RESUMINDO
Vimosquealémdecriarastabelasemnossobancodedadosprecisamosverificarsealgumacoluna
pode receber valores nulos ou não, podemos determinar essas regras por meio de Constraints no
momento da criação da tabela ou utilizando a instrução ALTER TABLE se caso a tabela exista e
queremos modificar sua estrutura. Também vimos que em alguns casos os valores de cada INSERT
podeserepetirmuitoeporissopodemosdefinirvalorespadrõescomainstruçãoDEFAULT.
EXERCÍCIOS
1. Configureovalorpadrãoparaacolunarecebida.
2. Configureacolunaobservacoesparanãoaceitarvaloresnulos.
3. Nonossomodeloatual,qualcampovocêdeixariacomvalores DEFAULTequaisnão?Justifiquesua
decisão. Note que como estamos falando de modelagem, não existe uma regra absoluta, existe
vantagensedesvantagensnadecisãoquetomar,tentecitá-las.
4. NOTNULLe DEFAULT podem ser usados também no CREATE TABLE? Crie uma tabela nova e
adicioneConstraintsevaloresDAFAULT.
4.5RESUMINDO
33
5. Reescreva o CREATE TABLE do começo do curso, marcando observacoes como nulo e valor
padrãodorecebidacomo1.
34
4.5RESUMINDO
CAPÍTULO5
AGRUPANDODADOSEFAZENDO
CONSULTASMAISINTELIGENTES
Jáadicionamosmuitosdadosemnossobancodedadoseseriamaisinteressantefazermosqueriesmais
robustas,comoporexemplo,saberototalquejágastei.OMySQLforneceafunção SUM()quesoma
todosdosvaloresdeumacoluna:
mysql>SELECTSUM(valor)FROMcompras;
+------------+
|SUM(valor)|
+------------+
|43967.91|
+------------+
1rowinset(0,00sec)
Vamosverificarototaldetodasascomprasrecebidas:
mysql>SELECTSUM(valor)FROMcomprasWHERErecebida=1;
+------------+
|SUM(valor)|
+------------+
|31686.75|
+------------+
1rowinset(0,00sec)
Agoratodasascomprasquenãoforamrecebidas:
mysql>SELECTSUM(valor)FROMcomprasWHERErecebida=0;
+------------+
|SUM(valor)|
+------------+
|12281.16|
+------------+
1rowinset(0,00sec)
Podemostambém,contarquantascomprasforamrecebidaspormeiodafunçãoCOUNT():
SELECTCOUNT(*)FROMcomprasWHERErecebida=1;
+----------+
|COUNT(*)|
+----------+
|26|
+----------+
Agoravamosfazercomquesejaretornadoasomadetodasascomprasrecebidasenãorecebidas,
porémretornaremosacolunarecebida,ouseja,emumalinhaestaráascomprasrecebidaseasuasomae
emoutraasnãorecebidasesuasoma:
5AGRUPANDODADOSEFAZENDOCONSULTASMAISINTELIGENTES
35
mysql>SELECTrecebida,SUM(valor)FROMcompras;
+----------+------------+
|recebida|SUM(valor)|
+----------+------------+
|1|43967.91|
+----------+------------+
1rowinset(0,00sec)
Observe que o resultado não saiu conforme o esperado... Mas por que será que isso aconteceu?
Quandoutilizamosafunção SUM()doMySQLelasomatodososvaloresdacolunaeretornaapenas
umaúnicalinha,poiséumafunçãodeagregração!Pararesolvermosesseproblema,podemosutilizara
instrução GROUP BY que indica como a soma precisa ser agrupada, ou seja, some todas as compras
recebidaseagrupeemumalinha,sometodasascomprasnãorecebidaseagrupeemoutralinha:
mysql>SELECTrecebida,SUM(valor)FROMcomprasGROUPBYrecebida;
+----------+------------+
|recebida|SUM(valor)|
+----------+------------+
|0|12281.16|
|1|31686.75|
+----------+------------+
2rowsinset(0,00sec)
Oresultadofoiconformeoesperado,porémnotequeonomedacolunaparaasomaestáumpouco
estranho,poisestamosaplicandoumafunçãoaoinvésderetornarumacoluna,seriamelhorseonome
retornadofosseapenas"soma".PodemosnomearascolunaspormeiodainstruçãoAS:
mysql>SELECTrecebida,SUM(valor)ASsomaFROMcompras
GROUPBYrecebida;
+----------+----------+
|recebida|soma|
+----------+----------+
|0|12281.16|
|1|31686.75|
+----------+----------+
2rowsinset(0,00sec)
Tambémpodemosaplicarfiltrosemqueriesqueutilizamfunçõesdeagregação:
mysql>SELECTrecebida,SUM(valor)ASsomaFROMcompras
WHEREvalor<1000
GROUPBYrecebida;
+----------+---------+
|recebida|soma|
+----------+---------+
|0|4325.96|
|1|8682.83|
+----------+---------+
2rowsinset(0,00sec)
Suponhamos uma query mais robusta, onde podemos verificar em qual mês e ano a compra foi
entregueounãoeovalordasoma.PodemosretornarainformaçãodeanoutilizandoafunçãoYEAR()
eainformaçãodemêsutilizandoafunçãoMONTH():
mysql>SELECTMONTH(data)asmes,YEAR(data)asano,recebida,
SUM(valor)ASsomaFROMcompras
GROUPBYrecebida;
36
5AGRUPANDODADOSEFAZENDOCONSULTASMAISINTELIGENTES
+------+------+----------+----------+
|mes|ano|recebida|soma|
+------+------+----------+----------+
|1|2016|0|12281.16|
|1|2016|1|31686.75|
+------+------+----------+----------+
2rowsinset(0,00sec)
Lembre-sequeestamoslidandocomumafunçãodeagregação!Porissoprecisamosinformartodas
ascolunasquequeremosagrupar,ouseja,acolunademêsedeano:
mysql>SELECTMONTH(data)asmes,YEAR(data)asano,recebida,
SUM(valor)ASsomaFROMcompras
GROUPBYrecebida,mes,ano;
+------+------+----------+----------+
|mes|ano|recebida|soma|
+------+------+----------+----------+
|1|2013|0|12.39|
|1|2016|0|2015.49|
|4|2013|0|54.98|
|4|2014|0|434.00|
|5|2012|0|3500.00|
|5|2013|0|12.34|
|5|2015|0|87.43|
|6|2014|0|1616.90|
|7|2015|0|12.34|
|8|2014|0|1709.00|
|9|2014|0|567.09|
|9|2015|0|213.50|
|10|2014|0|98.00|
|10|2015|0|1245.20|
|12|2013|0|78.65|
|12|2014|0|623.85|
|1|2013|1|8046.90|
|1|2014|1|827.50|
|1|2016|1|35.00|
|2|2012|1|200.00|
|2|2014|1|921.11|
|2|2015|1|986.36|
|3|2013|1|795.05|
|4|2012|1|1576.40|
|4|2014|1|11705.30|
|5|2014|1|678.43|
|7|2013|1|98.12|
|7|2015|1|32.09|
|9|2015|1|576.12|
|10|2014|1|631.53|
|11|2013|1|3212.40|
|11|2015|1|954.12|
|12|2012|1|163.45|
|12|2013|1|223.09|
|12|2015|1|23.78|
+------+------+----------+----------+
35rowsinset(0,00sec)
5.1ORDENANDOOSRESULTADOS
Conseguimos criar uma query bem robusta, mas perceba que as informações estão bem
desordenadas,aprimeiravistaédifícildeverasomadomêsdejaneiroemtodososanos,comotambém
5.1ORDENANDOOSRESULTADOS
37
a soma de todos os meses em um único ano. Para podermos ordernar as informações das colunas,
podemos utilizar a instrução ORDER BY passando as colunas que queremos que sejam ordenadas.
Vamosordenarpormêsprimeiro:
mysql>SELECTMONTH(data)asmes,YEAR(data)asano,recebida,
SUM(valor)ASsomaFROMcompras
GROUPBYrecebida,mes,ano
ORDERBYmes;
+------+------+----------+----------+
|mes|ano|recebida|soma|
+------+------+----------+----------+
|1|2013|1|8046.90|
|1|2014|1|827.50|
|1|2016|1|35.00|
|1|2016|0|2015.49|
|1|2013|0|12.39|
|2|2014|1|921.11|
|2|2012|1|200.00|
|2|2015|1|986.36|
|3|2013|1|795.05|
|4|2014|0|434.00|
|4|2013|0|54.98|
|4|2014|1|11705.30|
|4|2012|1|1576.40|
|5|2013|0|12.34|
|5|2014|1|678.43|
|5|2015|0|87.43|
|5|2012|0|3500.00|
|6|2014|0|1616.90|
|7|2015|0|12.34|
|7|2015|1|32.09|
|7|2013|1|98.12|
|8|2014|0|1709.00|
|9|2014|0|567.09|
|9|2015|0|213.50|
|9|2015|1|576.12|
|10|2014|1|631.53|
|10|2015|0|1245.20|
|10|2014|0|98.00|
|11|2013|1|3212.40|
|11|2015|1|954.12|
|12|2012|1|163.45|
|12|2013|1|223.09|
|12|2015|1|23.78|
|12|2014|0|623.85|
|12|2013|0|78.65|
+------+------+----------+----------+
35rowsinset(0,01sec)
Jáestámelhor!Masagoravamosordernarpormêseporano:
mysql>SELECTMONTH(data)asmes,YEAR(data)asano,recebida,
SUM(valor)ASsomaFROMcompras
GROUPBYrecebida,mes,ano
ORDERBYmes,ano;
+------+------+----------+----------+
|mes|ano|recebida|soma|
+------+------+----------+----------+
|1|2013|1|8046.90|
|1|2013|0|12.39|
|1|2014|1|827.50|
38
5.1ORDENANDOOSRESULTADOS
|1|2016|1|35.00|
|1|2016|0|2015.49|
|2|2012|1|200.00|
|2|2014|1|921.11|
|2|2015|1|986.36|
|3|2013|1|795.05|
|4|2012|1|1576.40|
|4|2013|0|54.98|
|4|2014|0|434.00|
|4|2014|1|11705.30|
|5|2012|0|3500.00|
|5|2013|0|12.34|
|5|2014|1|678.43|
|5|2015|0|87.43|
|6|2014|0|1616.90|
|7|2013|1|98.12|
|7|2015|0|12.34|
|7|2015|1|32.09|
|8|2014|0|1709.00|
|9|2014|0|567.09|
|9|2015|0|213.50|
|9|2015|1|576.12|
|10|2014|1|631.53|
|10|2014|0|98.00|
|10|2015|0|1245.20|
|11|2013|1|3212.40|
|11|2015|1|954.12|
|12|2012|1|163.45|
|12|2013|1|223.09|
|12|2013|0|78.65|
|12|2014|0|623.85|
|12|2015|1|23.78|
+------+------+----------+----------+
35rowsinset(0,00sec)
Ficoumaisfácildevisualizar,sóqueaindanãoconseguimosverificardeumaformaclaraasomade
cadamêsduranteumano.Percebaqueainstrução ORDERBYpriorizaascolunaspelaordememque
sãoinformadadas,ouseja,quandofizemos:
ORDERBYmes,ano;
Informamosquequeremosquedêprioridadeàordenaçãodacolunamêseasdemaiscolunassejam
ordenadasdeacordocomomês!Issosignificaqueparaordenarmosoanoedepoisomêsbastaapenas
colocaroanonoiníciodoORDERBY:
mysql>SELECTMONTH(data)asmes,YEAR(data)asano,recebida,
SUM(valor)ASsomaFROMcompras
GROUPBYrecebida,mes,ano
ORDERBYano,mes;
+------+------+----------+----------+
|mes|ano|recebida|soma|
+------+------+----------+----------+
|2|2012|1|200.00|
|4|2012|1|1576.40|
|5|2012|0|3500.00|
|12|2012|1|163.45|
|1|2013|1|8046.90|
|1|2013|0|12.39|
|3|2013|1|795.05|
|4|2013|0|54.98|
5.1ORDENANDOOSRESULTADOS
39
|5|2013|0|12.34|
|7|2013|1|98.12|
|11|2013|1|3212.40|
|12|2013|1|223.09|
|12|2013|0|78.65|
|1|2014|1|827.50|
|2|2014|1|921.11|
|4|2014|0|434.00|
|4|2014|1|11705.30|
|5|2014|1|678.43|
|6|2014|0|1616.90|
|8|2014|0|1709.00|
|9|2014|0|567.09|
|10|2014|1|631.53|
|10|2014|0|98.00|
|12|2014|0|623.85|
|2|2015|1|986.36|
|5|2015|0|87.43|
|7|2015|0|12.34|
|7|2015|1|32.09|
|9|2015|0|213.50|
|9|2015|1|576.12|
|10|2015|0|1245.20|
|11|2015|1|954.12|
|12|2015|1|23.78|
|1|2016|1|35.00|
|1|2016|0|2015.49|
+------+------+----------+----------+
35rowsinset(0,00sec)
Agora conseguimos verificar de uma forma clara a soma dos meses em cada ano. Além da soma
podemos também utilizar outras funções de agragação como por exemplo, a AVG() que retorna a
médiadeumacoluna:
mysql>SELECTMONTH(data)asmes,YEAR(data)asano,recebida,
AVG(valor)ASsomaFROMcompras
GROUPBYrecebida,mes,ano
ORDERBYano,mes;
+------+------+----------+-------------+
|mes|ano|recebida|soma|
+------+------+----------+-------------+
|2|2012|1|200.000000|
|4|2012|1|1576.400000|
|5|2012|0|3500.000000|
|12|2012|1|163.450000|
|1|2013|0|12.390000|
|1|2013|1|2682.300000|
|3|2013|1|397.525000|
|4|2013|0|54.980000|
|5|2013|0|12.340000|
|7|2013|1|98.120000|
|11|2013|1|3212.400000|
|12|2013|1|223.090000|
|12|2013|0|78.650000|
|1|2014|1|827.500000|
|2|2014|1|460.555000|
|4|2014|0|434.000000|
|4|2014|1|5852.650000|
|5|2014|1|678.430000|
|6|2014|0|808.450000|
|8|2014|0|1709.000000|
40
5.1ORDENANDOOSRESULTADOS
|9|2014|0|567.090000|
|10|2014|1|631.530000|
|10|2014|0|98.000000|
|12|2014|0|311.925000|
|2|2015|1|493.180000|
|5|2015|0|87.430000|
|7|2015|1|32.090000|
|7|2015|0|12.340000|
|9|2015|1|576.120000|
|9|2015|0|213.500000|
|10|2015|0|1245.200000|
|11|2015|1|954.120000|
|12|2015|1|23.780000|
|1|2016|1|17.500000|
|1|2016|0|671.830000|
+------+------+----------+-------------+
35rowsinset(0,00sec)
5.2RESUMINDO
Vimos como podemos fazer queries mais robustas e inteligentes utilizando funções de agregação,
comoporexemplo,a SUM()parasomarea AVG() para tirar a média. Vimos também que quando
queremos retornar outras colunas ao utilizar funções de agregação, precisamos utilizar a instrução
GROUPBY para determinar quais serão as colunas que queremos que seja feita o agrupamento e que
nem sempre o resultado vem organizado e por isso, em determinados casos, precisamos utilizar a
instruçãoORDERBYparaordenaranossaquerypormeiodeumacoluna.
EXERCÍCIOS
1. Calculeamédiadetodasascomprascomdatasinferioresa12/05/2013.
2. Calculeaquantidadedecomprascomdatasinferioresa12/05/2013equejáforamrecebidas.
3. Calculeasomadetodasascompras,agrupadasseacomprarecebidaounão.
5.2RESUMINDO
41
CAPÍTULO6
JUNTANDODADOSDEVÁRIASTABELAS
Anossatabeladecomprasestábempopulada,commuitasinformaçõesdascomprasrealizadas,porém
está faltando uma informação muito importante, que são os compradores. Por vezes foi eu quem
comprou algo, mas também o meu irmão pode ter comprado ou até mesmo o meu primo... Como
podemos fazer para identificar o comprador de uma compra? De acordo com o que vimos até agora
podemosadicionarumanovacolunachamadacompradorcomotipovarchar(200):
mysql>ALTERTABLEcomprasADDCOLUMNcompradorVARCHAR(200);
QueryOK,0rowsaffected(0,04sec)
Records:0Duplicates:0Warnings:0
Vamosverificarcomoficouaestruturadanossatabela:
mysql>DESCcompras;
+-------------+---------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+---------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|valor|decimal(18,2)|YES||NULL||
|data|date|YES||NULL||
|observacoes|varchar(255)|NO||NULL||
|recebida|tinyint(1)|YES||0||
|comprador|varchar(200)|YES||NULL||
+-------------+---------------+------+-----+---------+----------------+
6rowsinset(0,00sec)
Ótimo! Agora podemos adicionar os compradores de cada compra! Mas, antes de adicionarmos
precisamos verificar novamente verificar as informações das compras e identificar quem foi que
comprou:
mysql>SELECTid,valor,observacoes,dataFROMcompras;
+----+----------+------------------------------+------------+
|id|valor|observacoes|data|
+----+----------+------------------------------+------------+
|1|20.00|Lanchonete|2016-01-05|
|2|15.00|Lanchonete|2016-01-06|
|3|915.50|Guarda-roupa|2016-01-06|
|4|949.99|Smartphone|2016-01-10|
|5|200.00|Materialescolar|2012-02-19|
|6|3500.00|Televisao|2012-05-21|
|7|1576.40|Materialdeconstrucao|2012-04-30|
|8|163.45|Pizzaprafamilia|2012-12-15|
|9|4780.00|Saladeestar|2013-01-23|
|10|392.15|Quartos|2013-03-03|
|12|402.90|Copa|2013-03-21|
|13|54.98|Lanchonete|2013-04-12|
|14|12.34|Lanchonete|2013-05-23|
42
6JUNTANDODADOSDEVÁRIASTABELAS
|15|78.65|Lanchonete|2013-12-04|
|16|12.39|Sorvetenoparque|2013-01-06|
|17|98.12|HopiHari|2013-07-09|
|18|2498.00|Comprasdejaneiro|2013-01-12|
|19|3212.40|Comprasdomes|2013-11-13|
|20|223.09|Comprasdenatal|2013-12-17|
|21|768.90|Festa|2013-01-16|
|22|827.50|Festa|2014-01-09|
|23|12.00|Salgadonoaeroporto|2014-02-19|
|24|678.43|PassagempraBahia|2014-05-21|
|25|10937.12|CarnavalemCancun|2014-04-30|
|26|1501.00|Presentedasogra|2014-06-22|
|27|1709.00|Parceladacasa|2014-08-25|
|28|567.09|Parceladocarro|2014-09-25|
|29|631.53|IPTU|2014-10-12|
|30|909.11|IPVA|2014-02-11|
|31|768.18|GasolinaviagemPortoAlegre|2014-04-10|
|32|434.00|RodeiointeriordeSaoPaulo|2014-04-01|
|33|115.90|Diadosnamorados|2014-06-12|
|34|98.00|Diadascrianças|2014-10-12|
|35|253.70|Natal-presentes|2014-12-20|
|36|370.15|Comprasdenatal|2014-12-25|
|37|32.09|Lanchonete|2015-07-02|
|38|954.12|ShowdaIveteSangalo|2015-11-03|
|39|98.70|Lanchonete|2015-02-07|
|40|213.50|Roupas|2015-09-25|
|41|1245.20|Roupas|2015-10-17|
|42|23.78|LanchonetedoZé|2015-12-18|
|43|576.12|Sapatos|2015-09-13|
|44|12.34|Canetas|2015-07-19|
|45|87.43|Gravata|2015-05-10|
|46|887.66|Presenteparaofilhao|2015-02-02|
|48|150.00|Compradeteste|2016-01-05|
+----+----------+------------------------------+------------+
46rowsinset(0,00sec)
Agora que já sei as informações das compras posso adicionar os seus compradores. Começaremos
pelas5primeirascompras:
|1|20.00|Lanchonete|2016-01-05|
|2|15.00|Lanchonete|2016-01-06|
|3|915.50|Guarda-roupa|2016-01-06|
|4|949.99|Smartphone|2016-01-10|
|5|200.00|Materialescolar|2012-02-19|
Deacordocomasinformaçõesdomeurascunho,aprimeiracomprafuieumesmoquecomprei:
mysql>UPDATEcomprasSETcomprador='AlexFelipe'WHEREid=1;
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
AsegundaomeuprimoexaráAlexVieiraquecomprou:
mysql>UPDATEcomprasSETcomprador='AlexVieira'WHEREid=2;
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
Oguarda-roupafoiomeutioJoãodaSilvaquecomprou:
mysql>UPDATEcomprasSETcomprador='JoãodaSilva'WHEREid=3;
QueryOK,1rowaffected(0,00sec)
6JUNTANDODADOSDEVÁRIASTABELAS
43
Rowsmatched:1Changed:1Warnings:0
Osmartphonefuieu:
mysql>UPDATEcomprasSETcomprador='AlexFelipe'WHEREid=4;
QueryOK,1rowaffected(0,01sec)
Rowsmatched:1Changed:1Warnings:0
EomaterialescolarfoiomeutioJoãodaSilva:
mysql>UPDATEcomprasSETcomprador='JoãodaSilva'WHEREid=5;
QueryOK,1rowaffected(0,01sec)
Rowsmatched:1Changed:1Warnings:0
Vejamosoresultado:
mysql>SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+----------------+
|id|valor|data|observacoes|recebida|comprador|
+----+----------+------------+------------------------------+----------+----------------+
|1|20.00|2016-01-05|Lanchonete|1|AlexFelipe|
|2|15.00|2016-01-06|Lanchonete|1|AlexVieira|
|3|915.50|2016-01-06|Guarda-roupa|0|JoãodaSilva|
|4|949.99|2016-01-10|Smartphone|0|AlexFelipe|
|5|200.00|2012-02-19|Materialescolar|1|JoãodaSilva|
|...|...|...|...|...|...|
+----+----------+------------+------------------------------+----------+----------------+
46rowsinset(0,01sec)
Pensandobem,nãofoiomeutioJoãodaSilvaquecomprouMaterialescolaresimomeutioJoão
Vieira,eudevoteranotadoerrado...Entãovamosalterar:
mysql>UPDATEcomprasSETcomprador='Joãovieira'WHEREcomprador='JoãodaSilva';
QueryOK,2rowsaffected(0,01sec)
Rowsmatched:2Changed:2Warnings:0
Vejamoscomoficouoresultado:
mysql>select*fromcompraslimit5;
+----+--------+------------+------------------+----------+--------------+
|id|valor|data|observacoes|recebida|comprador|
+----+--------+------------+------------------+----------+--------------+
|1|20.00|2016-01-05|Lanchonete|1|AlexFelipe|
|2|15.00|2016-01-06|Lanchonete|1|AlexVieira|
|3|915.50|2016-01-06|Guarda-roupa|0|Joãovieira|
|4|949.99|2016-01-10|Smartphone|0|AlexFelipe|
|5|200.00|2012-02-19|Materialescolar|1|Joãovieira|
+----+--------+------------+------------------+----------+--------------+
5rowsinset(0,00sec)
Opa! Espera aí! O meu tio João da Silva sumiu!? E o meu tio João Vieira foi inserido com o
sobrenomeminúsculo...Vamosalterarnovamente...PrimeiroonomedoJoãoVieira:
mysql>UPDATEcomprasSETcomprador='JoãoVieira'WHEREcomprador='Joãovieira';
QueryOK,2rowsaffected(0,01sec)
Rowsmatched:2Changed:2Warnings:0
AgoravamosreenseriromeutioJoãodaSilvaparaacompradoguarda-roupa:
44
6JUNTANDODADOSDEVÁRIASTABELAS
mysql>UPDATEcomprasSETcomprador='JoãodaSilva'WHEREid=3;
QueryOK,1rowaffected(0,01sec)
Rowsmatched:1Changed:1Warnings:0
Vamosverificarnovamenteanossatabela:
mysql>SELECT*FROMcomprasLIMIT5;
+----+--------+------------+------------------+----------+----------------+
|id|valor|data|observacoes|recebida|comprador|
+----+--------+------------+------------------+----------+----------------+
|1|20.00|2016-01-05|Lanchonete|1|AlexFelipe|
|2|15.00|2016-01-06|Lanchonete|1|AlexVieira|
|3|915.50|2016-01-06|Guarda-roupa|0|JoãodaSilva|
|4|949.99|2016-01-10|Smartphone|0|AlexFelipe|
|5|200.00|2012-02-19|Materialescolar|1|JoãoVieira|
|...|...|...|...|...|...|
+----+--------+------------+------------------+----------+----------------+
5rowsinset(0,00sec)
Nossa,quetrabalheira!Porcausadeumerrinhoeutivequealterartudonovamente!Seeunãoficar
atento, com certeza acontecerá uma caca gigante no meu banco. Além disso, eu preciso também
adicionarotelefonedocompradorparaumdia,sefornecessário,entraremcontato!Então,novamente,
iremosadicionarumcolunanovacomonometelefoneetipoVARCHAR(15):
mysql>ALTERTABLEcomprasADDCOLUMNtelefoneVARCHAR(30);
QueryOK,0rowsaffected(0,06sec)
Records:0Duplicates:0Warnings:0
Vamosadicionarotelefonedecadaum.Primeiroomeutelefone:
mysql>UPDATEcomprasSETtelefone='5571-2751'WHEREcomprador='AlexFelipe';
QueryOK,2rowsaffected(0,01sec)
Rowsmatched:2Changed:2Warnings:0
Agoraodomeuxará,AlexVieira:
mysql>UPDATEcomprasSETtelefone='5083-3884'WHEREcomprador='AlexVieira';
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
Porfim,ostelefonesdosmeustios:
mysql>UPDATEcomprasSETtelefone='2220-4156'WHEREcomprador='JoãodaSilva';
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
mysql>UPDATEcomprasSETtelefone='2297-0033'WHEREcomprador='JoãoVieira';
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
Vejamoscomoficouanossatabela:
SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+----------------+-----------+
|id|valor|data|observacoes|recebida|comprador|telefone|
+----+----------+------------+------------------------------+----------+----------------+-----------+
|1|20.00|2016-01-05|Lanchonete|1|AlexFelipe|5571-2751|
|2|15.00|2016-01-06|Lanchonete|1|AlexVieira|5083-3884|
|3|915.50|2016-01-06|Guarda-roupa|0|JoãodaSilva|2220-4156|
6JUNTANDODADOSDEVÁRIASTABELAS
45
|4|949.99|2016-01-10|Smartphone|0|AlexFelipe|5571-2751|
|5|200.00|2012-02-19|Materialescolar|1|JoãoVieira|2297-0033|
|...|...|...|...|...|...|...|
+----+----------+------------+------------------------------+----------+----------------+-----------+
46rowsinset(0,00sec)
Certo, agora nossos compradores possuem telefone também. Ainda faltam mais compras sem
comprador, então continuaremos adicionando os compradores. A próxima compra foi o meu primo
AlexVieiraquecomprou.Vamosinseri-lonovamente:
mysql>UPDATEcomprasSETcomprador='AlexVieira'WHEREid=6;
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
Consultandonossatabelaparaverificarcomoestáficando:
mysql>SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+----------------+-----------+
|id|valor|data|observacoes|recebida|comprador|telefone|
+----+----------+------------+------------------------------+----------+----------------+-----------+
|1|20.00|2016-01-05|Lanchonete|1|AlexFelipe|5571-2751|
|2|15.00|2016-01-06|Lanchonete|1|AlexVieira|5083-3884|
|3|915.50|2016-01-06|Guarda-roupa|0|JoãodaSilva|2220-4156|
|4|949.99|2016-01-10|Smartphone|0|AlexFelipe|5571-2751|
|5|200.00|2012-02-19|Materialescolar|1|JoãoVieira|2297-0033|
|6|3500.00|2012-05-21|Televisao|0|AlexVieira|NULL|
|...|...|...|...|...|...|...|
+----+----------+------------+------------------------------+----------+----------------+-----------+
46rowsinset(0,00sec)
Ahnão!Esquecidecolocarotelefone.Deixaeucolocar:
mysql>UPDATEcomprasSETtelefone='5571-2751'WHEREid=6;
QueryOK,1rowaffected(0,00sec)
Rowsmatched:1Changed:1Warnings:0
Vamosverseagoravaidarcerto:
mysql>SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+----------------+-----------+
|id|valor|data|observacoes|recebida|comprador|telefone|
+----+----------+------------+------------------------------+----------+----------------+-----------+
|1|20.00|2016-01-05|Lanchonete|1|AlexFelipe|5571-2751|
|2|15.00|2016-01-06|Lanchonete|1|AlexVieira|5083-3884|
|3|915.50|2016-01-06|Guarda-roupa|0|JoãodaSilva|2220-4156|
|4|949.99|2016-01-10|Smartphone|0|AlexFelipe|5571-2751|
|5|200.00|2012-02-19|Materialescolar|1|JoãoVieira|2297-0033|
|6|3500.00|2012-05-21|Televisao|0|AlexVieira|5571-2751|
|...|...|...|...|...|...|...|
+----+----------+------------+------------------------------+----------+----------------+-----------+
46rowsinset(0,00sec)
Poxa vida... Percebi que ao invés de colocar o telefone do meu primo acabei colocando o meu...
Quanto sofrimento por causa de 2 colunas novas! Além de ter que me preocupar se os nomes dos
compradores estão sendo exatamente iguais, agora eu preciso me preocupar se os telefones também
estãocorretosemais,todasasvezesqueeuprecisoinserirumcompradoreuprecisolembrardeinseriro
seu telefone... E se agora eu precisasse adicionar o endereço, e-mail ou quaisquer informações do
46
6JUNTANDODADOSDEVÁRIASTABELAS
comprador?Euteriaquelembrartodasessasinformaçõescadavezqueeuinserirapenasumacompra!
Quehorror!
Veja o quão problemático está sendo manter as informações de um comprador em uma tabela de
compras.Alémdetrabalhosa,ainserçãoébemproblemática,poisháumgranderiscodefalhasnomeu
banco de dados como por exemplo: informações redundantes (que se repetem) ou então informações
incoerentes(omesmocompradorcomalgumainformaçãodiferente).Comtodacertezanãoéumaboa
soluçãoparaanossanecessidade!Seráquenãoexisteumaformadiferentederesolverisso?
6.1NORMALIZANDONOSSOMODELO
Reparaquetemosdoiselementos,duasentidades,emumaunicatabela:acompraeocomprador.
Quandonosdeparamoscomessestiposdeproblemascriamosnovastabelas.Entãovamoscriaruma
tabelachamadacompradores.
Nonossocasoqueremosquecadacompradorsejarepresentadopeloseunome,endereçoetelefone.
Como já pensamos em modelagem antes, aqui fica o nosso modelo de tabela para representar os
compradores:
mysql>CREATETABLEcompradores(
idINTPRIMARYKEYAUTO_INCREMENT,
nomeVARCHAR(200),
enderecoVARCHAR(200),
telefoneVARCHAR(30)
);
QueryOK,0rowsaffected(0,01sec)
VamosverificaranossatabelapormeiodoDESC:
mysql>DESCcompradores;
+----------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+----------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|nome|varchar(200)|YES||NULL||
|endereco|varchar(200)|YES||NULL||
|telefone|varchar(30)|YES||NULL||
+----------+--------------+------+-----+---------+----------------+
4rowsinset(0,00sec)
Agora que criamos a nossa tabela, vamos inserir alguns compradores, no meu caso, as compras
foramfeitasapenaspormimepelomeutioJoãodaSilva,entãoadicionaremosapenas2compradores:
mysql>INSERTINTOcompradores(nome,endereco,telefone)VALUES
('AlexFelipe','RuaVergueiro,3185','5571-2751');
QueryOK,1rowaffected(0,01sec)
mysql>INSERTINTOcompradores(nome,endereco,telefone)VALUES
('JoãodaSilva','Av.Paulista,6544','2220-4156');
QueryOK,1rowaffected(0,01sec)
Vamosverificarosnossoscompradores:
6.1NORMALIZANDONOSSOMODELO
47
mysql>SELECT*FROMcompradores;
+----+----------------+---------------------+-----------+
|id|nome|endereco|telefone|
+----+----------------+---------------------+-----------+
|1|AlexFelipe|RuaVergueiro,3185|5571-2751|
|2|JoãodaSilva|Av.Paulista,6544|2220-4156|
+----+----------------+---------------------+-----------+
2rowsinset(0,00sec)
Criamos agora duas tabelas diferentes na nossa base de dados, a tabela compras e a tabela
compradores.Entãonãoprecisamosmaisdasinforçõesdoscompradoresnatabelacompras,ouseja,
vamos excluir as colunas comprador e telefone , mas como podemos excluir uma coluna?
Precisamosalteraraestruturadatabela,entãocomeçaremospeloALTERTABLE:
ALTERTABLEcompras
Separaadicionarumacolunautilizamosainstrução ADDCOLUMN, logo, para excluir uma tabela,
utilizaremosainstruçãoDROPCOLUMN:
mysql>ALTERTABLEcomprasDROPCOLUMNcomprador;
QueryOK,0rowsaffected(0,06sec)
Records:0Duplicates:0Warnings:0
Vamosverificaraestruturadanossatabela:
mysql>DESCcompras;
+-------------+---------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+---------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|valor|decimal(18,2)|YES||NULL||
|data|date|YES||NULL||
|observacoes|varchar(255)|NO||NULL||
|recebida|tinyint(1)|YES||0||
|telefone|varchar(30)|YES||NULL||
+-------------+---------------+------+-----+---------+----------------+
6rowsinset(0,00sec)
Acolunacompradorfoiexcluídacomsucesso!Porfim,excluiremosacolunatelefone:
mysql>ALTERTABLEcomprasDROPCOLUMNtelefone;
QueryOK,0rowsaffected(0,03sec)
Records:0Duplicates:0Warnings:0
Agora que excluímos todas as colunas relacionadas aos compradores da tabela compras ,
precisamos de alguma forma associar uma compra com um comprador. Então vamos criar uma nova
colunanatabelacompraseadicionaroiddocompradornessacoluna:
mysql>ALTERTABLEcomprasADDCOLUMNid_compradoresint;
QueryOK,0rowsaffected(0,03sec)
Records:0Duplicates:0Warnings:0
Porqueoiddoscompradoresprecisaficarnatabelacomprasenãooiddatabelacomprasna
tabela compradores ? Precisamos pensar seguinte forma: uma compra só pode ser de 1 único
compradoreocompradorpodeter1oumaiscompras,comopodemosgarantirque1compraperteça
48
6.1NORMALIZANDONOSSOMODELO
apenas a 1 comprador? Fazendo com que a compra saiba quem é o seu único comprador! Ou seja,
recebendoachavedoseudono!Porissoadicionamosoiddoscompradoresnatabeladecompras,se
fizéssemosocontrário, ou seja, adicionássemos o id da compra na tabela compradores, iríamos
permitirque1única comprafosserealizadapor1oumaiscompradores,oquenãofazsentidoalgum
sabendoqueasminhascomprassóforamrealizadaspormimapenaseasdomeutioporeleapenas.
Temos a nossa nova coluna para associar o comprador com a sua compra, então vamos fazer
UPDATE na tabela compras inserindo o comprador de id 1 para a primeira metade das compras e
inserindoasegundametadeparaocompradordeid2.Primeirovamosverificarquantascomprasnós
temosusandoafunçãoCOUNT();
mysql>SELECTcount(*)FROMcompras;
+----------+
|count(*)|
+----------+
|46|
+----------+
1rowinset(0,00sec)
Vamosatualizaraprimeirametade:
mysql>UPDATEcomprasSETid_compradores=1WHEREid<22;
QueryOK,20rowsaffected(0,01sec)
Rowsmatched:20Changed:20Warnings:0
Agoraasegundametade:
mysql>UPDATEcomprasSETid_compradores=2WHEREid>21;
QueryOK,26rowsaffected(0,01sec)
Rowsmatched:26Changed:26Warnings:0
Verificandoascomprasnonossobancodedados:
mysql>SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+----------------+
|id|valor|data|observacoes|recebida|id_compradores|
+----+----------+------------+------------------------------+----------+----------------+
|1|20.00|2016-01-05|Lanchonete|1|1|
|2|15.00|2016-01-06|Lanchonete|1|1|
|3|915.50|2016-01-06|Guarda-roupa|0|1|
|4|949.99|2016-01-10|Smartphone|0|1|
|5|200.00|2012-02-19|Materialescolar|1|1|
|6|3500.00|2012-05-21|Televisao|0|1|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|1|
|8|163.45|2012-12-15|Pizzaprafamilia|1|1|
|9|4780.00|2013-01-23|Saladeestar|1|1|
|10|392.15|2013-03-03|Quartos|1|1|
|12|402.90|2013-03-21|Copa|1|1|
|13|54.98|2013-04-12|Lanchonete|0|1|
|14|12.34|2013-05-23|Lanchonete|0|1|
|15|78.65|2013-12-04|Lanchonete|0|1|
|16|12.39|2013-01-06|Sorvetenoparque|0|1|
|17|98.12|2013-07-09|HopiHari|1|1|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|1|
|19|3212.40|2013-11-13|Comprasdomes|1|1|
|20|223.09|2013-12-17|Comprasdenatal|1|1|
|21|768.90|2013-01-16|Festa|1|1|
6.1NORMALIZANDONOSSOMODELO
49
|22|827.50|2014-01-09|Festa|1|2|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|2|
|24|678.43|2014-05-21|PassagempraBahia|1|2|
|25|10937.12|2014-04-30|CarnavalemCancun|1|2|
|26|1501.00|2014-06-22|Presentedasogra|0|2|
|27|1709.00|2014-08-25|Parceladacasa|0|2|
|28|567.09|2014-09-25|Parceladocarro|0|2|
|29|631.53|2014-10-12|IPTU|1|2|
|30|909.11|2014-02-11|IPVA|1|2|
|31|768.18|2014-04-10|GasolinaviagemPortoAlegre|1|2|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|2|
|33|115.90|2014-06-12|Diadosnamorados|0|2|
|34|98.00|2014-10-12|Diadascrianças|0|2|
|35|253.70|2014-12-20|Natal-presentes|0|2|
|36|370.15|2014-12-25|Comprasdenatal|0|2|
|37|32.09|2015-07-02|Lanchonete|1|2|
|38|954.12|2015-11-03|ShowdaIveteSangalo|1|2|
|39|98.70|2015-02-07|Lanchonete|1|2|
|40|213.50|2015-09-25|Roupas|0|2|
|41|1245.20|2015-10-17|Roupas|0|2|
|42|23.78|2015-12-18|LanchonetedoZé|1|2|
|43|576.12|2015-09-13|Sapatos|1|2|
|44|12.34|2015-07-19|Canetas|0|2|
|45|87.43|2015-05-10|Gravata|0|2|
|46|887.66|2015-02-02|Presenteparaofilhao|1|2|
|48|150.00|2016-01-05|Compradeteste|0|2|
+----+----------+------------+------------------------------+----------+----------------+
46rowsinset(0,00sec)
6.2ONETOMANY/MANYTOONE
Repare que o que fizemos foi deixar claro que um comprador pode ter muitas compras (um para
muitos),ouaindaquemuitascompraspodemvirdomesmocomprador(manytoone),sódependedo
pontodevista.ChamamosentãodeumarelaçãoOnetoMany(ouManytoOne).
6.3FOREIGNKEY
Conseguimosassociarumacompracomumcomprador,entãoagoravamosbuscarasinformações
dascompraseseusrespectivoscompradores:
mysql>SELECT*FROMcompras;
+----+----------+------------+------------------------------+----------+----------------+
|id|valor|data|observacoes|recebida|id_compradores|
+----+----------+------------+------------------------------+----------+----------------+
|1|20.00|2016-01-05|Lanchonete|1|1|
|2|15.00|2016-01-06|Lanchonete|1|1|
|3|915.50|2016-01-06|Guarda-roupa|0|1|
|4|949.99|2016-01-10|Smartphone|0|1|
|5|200.00|2012-02-19|Materialescolar|1|1|
|6|3500.00|2012-05-21|Televisao|0|1|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|1|
|8|163.45|2012-12-15|Pizzaprafamilia|1|1|
|9|4780.00|2013-01-23|Saladeestar|1|1|
|10|392.15|2013-03-03|Quartos|1|1|
|12|402.90|2013-03-21|Copa|1|1|
|13|54.98|2013-04-12|Lanchonete|0|1|
|14|12.34|2013-05-23|Lanchonete|0|1|
50
6.2ONETOMANY/MANYTOONE
|15|78.65|2013-12-04|Lanchonete|0|1|
|16|12.39|2013-01-06|Sorvetenoparque|0|1|
|17|98.12|2013-07-09|HopiHari|1|1|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|1|
|19|3212.40|2013-11-13|Comprasdomes|1|1|
|20|223.09|2013-12-17|Comprasdenatal|1|1|
|21|768.90|2013-01-16|Festa|1|1|
|22|827.50|2014-01-09|Festa|1|2|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|2|
|24|678.43|2014-05-21|PassagempraBahia|1|2|
|25|10937.12|2014-04-30|CarnavalemCancun|1|2|
|26|1501.00|2014-06-22|Presentedasogra|0|2|
|27|1709.00|2014-08-25|Parceladacasa|0|2|
|28|567.09|2014-09-25|Parceladocarro|0|2|
|29|631.53|2014-10-12|IPTU|1|2|
|30|909.11|2014-02-11|IPVA|1|2|
|31|768.18|2014-04-10|GasolinaviagemPortoAlegre|1|2|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|2|
|33|115.90|2014-06-12|Diadosnamorados|0|2|
|34|98.00|2014-10-12|Diadascrianças|0|2|
|35|253.70|2014-12-20|Natal-presentes|0|2|
|36|370.15|2014-12-25|Comprasdenatal|0|2|
|37|32.09|2015-07-02|Lanchonete|1|2|
|38|954.12|2015-11-03|ShowdaIveteSangalo|1|2|
|39|98.70|2015-02-07|Lanchonete|1|2|
|40|213.50|2015-09-25|Roupas|0|2|
|41|1245.20|2015-10-17|Roupas|0|2|
|42|23.78|2015-12-18|LanchonetedoZé|1|2|
|43|576.12|2015-09-13|Sapatos|1|2|
|44|12.34|2015-07-19|Canetas|0|2|
|45|87.43|2015-05-10|Gravata|0|2|
|46|887.66|2015-02-02|Presenteparaofilhao|1|2|
|48|150.00|2016-01-05|Compradeteste|0|2|
+----+----------+------------+------------------------------+----------+----------------+
46rowsinset(0,00sec)
mysql>SELECT*FROMcompradores;
+----+----------------+---------------------+-----------+
|id|nome|endereco|telefone|
+----+----------------+---------------------+-----------+
|1|AlexFelipe|RuaVergueiro,3185|5571-2751|
|2|JoãodaSilva|Av.Paulista,6544|2220-4156|
+----+----------------+---------------------+-----------+
2rowsinset(0,00sec)
Não... Não era isso que eu queria, eu quero que, em apenas uma query, nesse caso um SELECT,
retornetantoasinformaçõesdatabela comprasedatabela compradores. Será que podemos fazer
isso? Sim, podemos! Lembra da instrução FROM que indica qual é a tabela que estamos buscando as
informações?Alémdebuscarporumaúnicatabela,podemosindicarquequeremosbuscarpormaisde
1tabelaseparandoastabelaspor",":
SELECT*FROMcompras,compradores,tabela3,tabela4,...;
EntãovamosadicionaremumúnicoSELECTastabelas:comprasecompradores:
mysql>SELECT*FROMcompras,compradores;
+----+----------+------------+------------------------------+----------+----------------+----+---------------+---------------------+-----------+
|id|valor|data|observacoes|recebida|id_compradores|id|nome
|endereco|telefone|
6.3FOREIGNKEY
51
+----+----------+------------+------------------------------+----------+----------------+----+---------------+---------------------+-----------+
|1|20.00|2016-01-05|Lanchonete|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|1|20.00|2016-01-05|Lanchonete|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|2|15.00|2016-01-06|Lanchonete|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|2|15.00|2016-01-06|Lanchonete|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|3|915.50|2016-01-06|Guarda-roupa|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|3|915.50|2016-01-06|Guarda-roupa|0|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|4|949.99|2016-01-10|Smartphone|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|4|949.99|2016-01-10|Smartphone|0|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|5|200.00|2012-02-19|Materialescolar|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|5|200.00|2012-02-19|Materialescolar|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|6|3500.00|2012-05-21|Televisao|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|6|3500.00|2012-05-21|Televisao|0|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|8|163.45|2012-12-15|Pizzaprafamilia|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|8|163.45|2012-12-15|Pizzaprafamilia|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|9|4780.00|2013-01-23|Saladeestar|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|9|4780.00|2013-01-23|Saladeestar|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|10|392.15|2013-03-03|Quartos|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|10|392.15|2013-03-03|Quartos|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|12|402.90|2013-03-21|Copa|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|12|402.90|2013-03-21|Copa|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|13|54.98|2013-04-12|Lanchonete|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|13|54.98|2013-04-12|Lanchonete|0|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|14|12.34|2013-05-23|Lanchonete|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|14|12.34|2013-05-23|Lanchonete|0|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|15|78.65|2013-12-04|Lanchonete|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|15|78.65|2013-12-04|Lanchonete|0|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|16|12.39|2013-01-06|Sorvetenoparque|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|16|12.39|2013-01-06|Sorvetenoparque|0|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|17|98.12|2013-07-09|HopiHari|1|1|1|AlexF
52
6.3FOREIGNKEY
elipe|RuaVergueiro,3185|5571-2751|
|17|98.12|2013-07-09|HopiHari|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|19|3212.40|2013-11-13|Comprasdomes|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|19|3212.40|2013-11-13|Comprasdomes|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|20|223.09|2013-12-17|Comprasdenatal|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|20|223.09|2013-12-17|Comprasdenatal|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|21|768.90|2013-01-16|Festa|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|21|768.90|2013-01-16|Festa|1|1|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|22|827.50|2014-01-09|Festa|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|22|827.50|2014-01-09|Festa|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|24|678.43|2014-05-21|PassagempraBahia|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|24|678.43|2014-05-21|PassagempraBahia|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|25|10937.12|2014-04-30|CarnavalemCancun|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|25|10937.12|2014-04-30|CarnavalemCancun|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|26|1501.00|2014-06-22|Presentedasogra|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|26|1501.00|2014-06-22|Presentedasogra|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|27|1709.00|2014-08-25|Parceladacasa|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|27|1709.00|2014-08-25|Parceladacasa|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|28|567.09|2014-09-25|Parceladocarro|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|28|567.09|2014-09-25|Parceladocarro|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|29|631.53|2014-10-12|IPTU|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|29|631.53|2014-10-12|IPTU|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|30|909.11|2014-02-11|IPVA|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|30|909.11|2014-02-11|IPVA|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|31|768.18|2014-04-10|GasolinaviagemPortoAlegre|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|31|768.18|2014-04-10|GasolinaviagemPortoAlegre|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
6.3FOREIGNKEY
53
|33|115.90|2014-06-12|Diadosnamorados|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|33|115.90|2014-06-12|Diadosnamorados|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|34|98.00|2014-10-12|Diadascrianças|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|34|98.00|2014-10-12|Diadascrianças|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|35|253.70|2014-12-20|Natal-presentes|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|35|253.70|2014-12-20|Natal-presentes|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|36|370.15|2014-12-25|Comprasdenatal|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|36|370.15|2014-12-25|Comprasdenatal|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|37|32.09|2015-07-02|Lanchonete|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|37|32.09|2015-07-02|Lanchonete|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|38|954.12|2015-11-03|ShowdaIveteSangalo|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|38|954.12|2015-11-03|ShowdaIveteSangalo|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|39|98.70|2015-02-07|Lanchonete|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|39|98.70|2015-02-07|Lanchonete|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|40|213.50|2015-09-25|Roupas|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|40|213.50|2015-09-25|Roupas|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|41|1245.20|2015-10-17|Roupas|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|41|1245.20|2015-10-17|Roupas|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|42|23.78|2015-12-18|LanchonetedoZé|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|42|23.78|2015-12-18|LanchonetedoZé|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|43|576.12|2015-09-13|Sapatos|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|43|576.12|2015-09-13|Sapatos|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|44|12.34|2015-07-19|Canetas|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|44|12.34|2015-07-19|Canetas|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|45|87.43|2015-05-10|Gravata|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|45|87.43|2015-05-10|Gravata|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|46|887.66|2015-02-02|Presenteparaofilhao|1|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|46|887.66|2015-02-02|Presenteparaofilhao|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|48|150.00|2016-01-05|Compradeteste|0|2|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|48|150.00|2016-01-05|Compradeteste|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
+----+----------+------------+------------------------------+----------+----------------+----+---------------+---------------------+-----------+
92rowsinset(0,00sec)
54
6.3FOREIGNKEY
Opa!Observequeessaqueryestáumpoucoestranha,poiseladevolveu92linhas,sendoquetemos
apenas46comprasregistradas!Duplicouosnossosregistros...Esseproblemaaconteceu,poisoMySQL
nãosabecomoassociaratabelacompraseatabelacompradores.PrecisamosinformaraoMySQLpor
meiodequalcolunaelefaráessaassociação.Percebaqueacolunaquecontémachave(primarykey)do
comprador é justamente a id_compradores, chamamos esse tipo de coluna de FOREIGN KEY ou
chaveestrangeira.Parajuntarmosachaveestrangeiracomachaveprimáriadeumatabela,utilizamosa
instruçãoJOIN,informandoatabelaeachavequequeremosassociar:
mysql>SELECT*FROMcomprasJOINcompradoresoncompras.id_compradores=compradores.id;
+----+----------+------------+------------------------------+----------+----------------+----+---------------+---------------------+-----------+
|id|valor|data|observacoes|recebida|id_compradores|id|nome
|endereco|telefone|
+----+----------+------------+------------------------------+----------+----------------+----+---------------+---------------------+-----------+
|1|20.00|2016-01-05|Lanchonete|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|2|15.00|2016-01-06|Lanchonete|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|3|915.50|2016-01-06|Guarda-roupa|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|4|949.99|2016-01-10|Smartphone|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|5|200.00|2012-02-19|Materialescolar|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|6|3500.00|2012-05-21|Televisao|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|7|1576.40|2012-04-30|Materialdeconstrucao|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|8|163.45|2012-12-15|Pizzaprafamilia|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|9|4780.00|2013-01-23|Saladeestar|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|10|392.15|2013-03-03|Quartos|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|12|402.90|2013-03-21|Copa|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|13|54.98|2013-04-12|Lanchonete|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|14|12.34|2013-05-23|Lanchonete|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|15|78.65|2013-12-04|Lanchonete|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|16|12.39|2013-01-06|Sorvetenoparque|0|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|17|98.12|2013-07-09|HopiHari|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|18|2498.00|2013-01-12|Comprasdejaneiro|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|19|3212.40|2013-11-13|Comprasdomes|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|20|223.09|2013-12-17|Comprasdenatal|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|21|768.90|2013-01-16|Festa|1|1|1|AlexF
elipe|RuaVergueiro,3185|5571-2751|
|22|827.50|2014-01-09|Festa|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|23|12.00|2014-02-19|Salgadonoaeroporto|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
6.3FOREIGNKEY
55
|24|678.43|2014-05-21|PassagempraBahia|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|25|10937.12|2014-04-30|CarnavalemCancun|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|26|1501.00|2014-06-22|Presentedasogra|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|27|1709.00|2014-08-25|Parceladacasa|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|28|567.09|2014-09-25|Parceladocarro|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|29|631.53|2014-10-12|IPTU|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|30|909.11|2014-02-11|IPVA|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|31|768.18|2014-04-10|GasolinaviagemPortoAlegre|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|32|434.00|2014-04-01|RodeiointeriordeSaoPaulo|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|33|115.90|2014-06-12|Diadosnamorados|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|34|98.00|2014-10-12|Diadascrianças|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|35|253.70|2014-12-20|Natal-presentes|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|36|370.15|2014-12-25|Comprasdenatal|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|37|32.09|2015-07-02|Lanchonete|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|38|954.12|2015-11-03|ShowdaIveteSangalo|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|39|98.70|2015-02-07|Lanchonete|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|40|213.50|2015-09-25|Roupas|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|41|1245.20|2015-10-17|Roupas|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|42|23.78|2015-12-18|LanchonetedoZé|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|43|576.12|2015-09-13|Sapatos|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|44|12.34|2015-07-19|Canetas|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|45|87.43|2015-05-10|Gravata|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|46|887.66|2015-02-02|Presenteparaofilhao|1|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
|48|150.00|2016-01-05|Compradeteste|0|2|2|Joãod
aSilva|Av.Paulista,6544|2220-4156|
+----+----------+------------+------------------------------+----------+----------------+----+---------------+---------------------+-----------+
46rowsinset(0,01sec)
Ainstrução JOINesperaumatabelaqueprecisaserjuntada: FROMcomprasJOINcompradores.
Nessecasoestamosjuntandoatabelacomprascomatabelacompradores.Parapassarmosocritériode
junção, utilizamos a instrução ON : ON compras.id_compradores = compradores.id . Nesse
momentoestamosinformandoaFOREIGNKEYdatabelacompras(compras.id_compradores)equalé
achave(compradores.id)databelacompradoresquereferenciaessaFOREIGNKEY.
Agoraanossaqueryretornouosnossosregistroscorretamente.Porém,aindaexisteumproblemana
56
6.3FOREIGNKEY
nossatabelacompras.ObserveesseINSERT:
mysql>INSERTINTOcompras(valor,data,observacoes,id_compradores)
VALUES(1500,'2016-01-05','Playstation4',100);
QueryOK,1rowaffected(0,00sec)
Vamosverificaranossatabelacompras:
mysql>SELECT*FROMcomprasWHEREid_compradores=100;
+----+---------+------------+---------------+----------+----------------+
|id|valor|data|observacoes|recebida|id_compradores|
+----+---------+------------+---------------+----------+----------------+
|49|1500.00|2016-01-05|Playstation4|0|100|
+----+---------+------------+---------------+----------+----------------+
1rowinset(0,00sec)
Note que não existe o comprador com id 100, mas, mesmo assim, conseguimos adicioná-lo na
nossatabelade compras.Precisamosgarantiraintegridadedosnossosdados,informandoaoMySQL
queacolunaid_compradoresdatabelacompraséumaFOREIGNKEYdatabelacompradores,ouseja,
só poderemos adicionar um id apenas se estiver registrado na tabela compradores. Antes de
adicionarmosaFOREIGNKEY,precisamosexcluiroregistrocomoid_compradores=100,poisnãoé
possíveladicionarumaFOREIGNKEYcomdadosquenãoexistanatabelaqueiremosreferenciar.
mysql>DELETEFROMcomprasWHEREid_compradores=100;
QueryOK,1rowaffected(0,01sec)
Quando adicionamos uma FOREIGNKEY em uma tabela, estamos adicionando uma Constraints,
entãoprecisaremosalteraraestruturadatabelacomprasutilizandoainstruçãoALTERTABLE:
mysql>ALTERTABLEcomprasADDCONSTRAINTfk_compradoresFOREIGNKEY(id_compradores)
REFERENCEScompradores(id);
QueryOK,46rowsaffected(0,04sec)
Records:46Duplicates:0Warnings:0
Setentarmosadicionaracompraanteriornovamente:
mysql>INSERTINTOcompras(valor,data,observacoes,id_compradores)
VALUES(1500,'2016-01-05','Playstation4',100);
ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`ControleDeGast
os`.`compras`,CONSTRAINT`fk_compradores`FOREIGNKEY(`id_compradores`)REFERENCES`compradores`(`
id`))
Agoraonossobancodedadosnãopermiteainserçãoderegistroscomcompradoresinexistentes!Se
tentarmosadicionaressamesmacompracomumcompradorexistente:
INSERTINTOcompras(valor,data,observacoes,id_compradores)
VALUES(1500,'2016-01-05','Playstation4',1);
QueryOK,1rowaffected(0,00sec)
severificarmosessacompra:
mysql>SELECT*FROMcomprasWHEREobservacoes='Playstation4';
+----+---------+------------+---------------+----------+----------------+
|id|valor|data|observacoes|recebida|id_compradores|
+----+---------+------------+---------------+----------+----------------+
|51|1500.00|2016-01-05|Playstation4|0|1|
6.3FOREIGNKEY
57
+----+---------+------------+---------------+----------+----------------+
1rowinset(0,00sec)
6.4DETERMINANDOVALORESFIXOSNATABELA
Conseguimos deixar a nossa base de dados bem robusta, restringindo as nossas colunas para não
permitirvaloresnulosenãoaceitarainserçãodecompradoresinexistentes,masagorasurgiuumanova
necessidadequeprecisaserimplementada,precisamosinformartambémqualéaformadepagamento
que foi realizada na compra, por exemplo, existem 2 formas de pagamento, boleto e crédito. Como
poderíamosimplementaressanovainformaçãonanossatabelaatualmente?Criamosumanovacoluna
comotipoVARCHARecorresmosoriscodeinserirumaformadepagamentoinválida?Nãopareceuma
boa solução... Que tal criarmos uma nova tabela chamada id_forma_de_pagto e fazermos uma
FOREIGNKEY?Aparentementeéumaboasolução,porémiremoscriarumanovatabelapararesolver
umproblemabempequeno?Lembre-sequeacadaFOREIGNKEYmaisJOINsasnossasqueriesterão...
NoMySQL,existeotipodedadoENUMquepermitequeinformemosquaisserãoosdadosqueelepode
aceitar.VamosadicionaroENUMnanossatabeladecompras:
mysql>ALTERTABLEcomprasADDCOLUMNforma_pagtoENUM('BOLETO','CREDITO');
QueryOK,0rowsaffected(0,04sec)
Records:0Duplicates:0Warnings:0
Vamostentaradicionarumacompra:
mysql>INSERTINTOcompras(valor,data,observacoes,id_compradores,forma_pagto)
VALUES(400,'2016-01-06','SSD128GB',1,'BOLETO');
QueryOK,1rowaffected(0,00sec)
Retornandoessanovacompra:
mysql>SELECT*FROMcomprasWHEREobservacoes='SSD128GB';
+----+--------+------------+-------------+----------+----------------+-------------+
|id|valor|data|observacoes|recebida|id_compradores|forma_pagto|
+----+--------+------------+-------------+----------+----------------+-------------+
|52|400.00|2016-01-06|SSD128GB|0|1|BOLETO|
+----+--------+------------+-------------+----------+----------------+-------------+
1rowinset(0,00sec)
Masesetentarmosadicionarumanovacompra,porémcomaformadepagamentoemdinheiro?
mysql>INSERTINTOcompras(valor,data,observacoes,id_compradores,forma_pagto)
VALUES(80,'2016-01-07','Boladefutebol',2,'DINHEIRO');
QueryOK,1rowaffected,1warning(0,00sec)
Vamosverificarcomoficounanossatabeladecompras:
mysql>SELECT*FROMcomprasWHEREobservacoes='Boladefutebol';
+----+-------+------------+-----------------+----------+----------------+-------------+
|id|valor|data|observacoes|recebida|id_compradores|forma_pagto|
+----+-------+------------+-----------------+----------+----------------+-------------+
|53|80.00|2016-01-07|Boladefutebol|0|2||
+----+-------+------------+-----------------+----------+----------------+-------------+
1rowinset(0,00sec)
58
6.4DETERMINANDOVALORESFIXOSNATABELA
6.5SERVERSQLMODES
O MySQL impediu que fosse adicionado um valor diferente de "BOLETO" ou "CREDITO", mas o
querealmenteprecisamoséqueelesimplesmentenãodeixeadicionarumacompraquenãotenhapelo
menos uma dessas formas de pagamento. Além das configurações das tabelas, podemos também
configurar o próprio servidor do MySQL. O servidor do MySQL opera em diferentes SQL modes e
dentre esses modos, existe o strict mode que tem a finalidade de tratar valores inválidos que
configuramosemnossastabelasparainstruçõesde INSERTe UPDATE, como por exemplo, o nosso
ENUM.ParahabilitarostrictmodeprecisamosalteraroSQLmodedanossasessão.Nessecasousaremos
omodo"STRICT_ALL_TABLES":
mysql>SETSESSIONsql_mode='STRICT_ALL_TABLES';
QueryOK,0rowsaffected(0,00sec)
Sequisermosverificarseomodofoimodificadopodemosretornaressevalorpormeiodainstrução
SELECT:
mysql>SELECT@@SESSION.sql_mode;
+--------------------+
|@@SESSION.sql_mode|
+--------------------+
|STRICT_ALL_TABLES|
+--------------------+
1rowinset(0,00sec)
AgoraqueconfiguramosoSQLmodedoMySQLparaimpedirainserçãodevaloresinválidos,vamos
apagaroúltimoregistroquefoiinseridocomvalorinválidoetentaradicioná-lonovamente:
mysql>DELETEFROMcomprasWHEREobservacoes='BOLADEFUTEBOL';
QueryOK,1rowaffected(0,00sec)
Testandonovamenteamesmainserção:
mysql>INSERTINTOcompras(valor,data,observacoes,id_compradores,forma_pagto)
->VALUES(80,'2016-01-07','BOLADEFUTEBOL',2,'DINHEIRO');
ERROR1265(01000):Datatruncatedforcolumn'forma_pagto'atrow1
Perceba que agora o MySQL impediu que a linha fosse inserida, pois o valor não é válido para a
colunaforma_pagto.
OSQLmodeéumaconfiguraçãodoservidorenósalteramosapenasasessãoqueestávamoslogado,o
que aconteceria se caso saímos da sessão que configuramos e entrássemos em uma nova? O MySQL
adotariaoSQLmodepadrãojáconfigurado!Ouseja,teriámosquealterarnovamenteparaostrictmode.
Masalémdasessão,podemosfazeraconfiguraçãoglobaldoSQLmode.
mysql>SETGLOBALsql_mode='STRICT_ALL_TABLES';
QueryOK,0rowsaffected(0,00sec)
SeverificarmosaconfiguraçãoglobalparaoSQLmode:
mysql>SELECT@@GLOBAL.sql_mode;
+-------------------+
6.5SERVERSQLMODES
59
|@@GLOBAL.sql_mode|
+-------------------+
|STRICT_ALL_TABLES|
+-------------------+
1rowinset(0,00sec)
Agora,todasasvezesqueentrarmosnoMySQL,seráadotadoostrictmode.
O ENUMéumaboasoluçãoquandoqueremosrestringirvaloresespecíficosejáesperadosemum
coluna,porémnãofazpartedopadrãoANSIqueéopadrãoparaaescritadeinstruçõesSQL,ouseja,é
umrecursoexclusivodoMySQLecadabancodedadospossuiasuaprópriaimplementaçãoparaessa
mesmafuncionadalidade.
6.6RESUMINDO
Nesse capítulo aprendemos a fazer uma relação entre duas tabelas utilizando FOREIGN KEYS.
Vimostambémqueparafazermosqueriescomduastabelasdiferentesutilizamosaschavesestrangeiras
pormeiodainstrução JOIN que informa ao MySQL quais serão os critérios para associar as tabelas
distintas.Esempreprecisamoslembrarque,quandoestamoslidandocomFOREIGNKEY, precisamos
criarumaConstraintparagarantirquetodasaschavesestrangeirasprecisaexistirnatabelaquefazemos
referência.Alémdisso,vimostambémquequandoqueremosadicionarumacolunanovaparaqueaceite
apenas determinados valores já esperado por nós, como é o caso da forma de pagamento, podemos
utilizaro ENUMdoMySQLcomoumsolução,poréméimportantelembrarqueéumasoluçãoquenão
fazpartedopadrãoANSI,ouseja,cadabancodedadospossuisuaprópriaimplementação.Vamospara
osexercícios?
Vamosparaosexercícios?
EXERCÍCIOS
1. Crieatabelacompradorescomid,nome,enderecoetelefone.
2. Insiraoscompradores,GuilhermeeJoãodaSilva.
3. Adicioneacoluna id_compradoresnatabela compras e defina a chave estrangeira (FOREIGN
KEY)referenciandooiddatabelacompradores.
4. Atualizeatabelacompraseinsiraoiddoscompradoresnacolunaid_compradores.
5. ExibaoNOMEdocompradoreoVALORdetodasascomprasfeitasantesde09/08/2014.
6. ExibatodasascomprasdocompradorquepossuiIDiguala2.
7. Exibatodasascompras(massemosdadosdocomprador),cujocompradortenhanomequecomeça
com'GUILHERME'.
60
6.6RESUMINDO
8. Exibaonomedocompradoreasomadetodasassuascompras.
9. A tabela compras foi alterada para conter uma FOREIGN KEY referenciando a coluna id da
tabela compradores . O objetivo é deixar claro para o banco de dados que
compras.id_compradoresestádealgumaformarelacionadocomatabela compradoresatravés
dacolunacompradores.id.MesmosemcriaraFOREIGNKEYépossívelrelacionartabelasatravés
docomandoJOIN.
10. QualavantagememutilizaraFOREIGNKEY?
11. Crieumacolunachamada"forma_pagto"dotipoENUMedefinaosvalores:'BOLETO'e'CREDITO'.
12. Ativeostrictmodenasessãoqueestáutilizandoparaimpossibilitarvaloresinválidos.Utilizeomodo
"STRICT_ALL_TABLES".EverifiqueseoSQLmodefoialteradofazendoumSELECTnasessão.
13. Tente inserir uma compra com forma de pagamento diferente de 'BOLETO' ou 'CREDITO', por
exemplo,'DINHEIRO'everifiqueseoMySQLrecusaainserção.
14. AdicioneasformasdepagamentoparatodasascompraspormeiodainstruçãoUPDATE.
15. FaçaaconfiguraçãoglobaldoMySQLparaqueelesempreentrenostrictmode.
6.6RESUMINDO
61
CAPÍTULO7
ALUNOSSEMMATRÍCULAEOEXISTS
Paraasegundapartedenossocursoutilizaremosumconjuntodedadosdeumsistemadeensinoonline
comoexemplo.Nãosepreocupe,poisdisponilizaremosoarquivoparaquevocêbaixeeexecuteoscript
comtodasastabelas.
Continuaremos usando o terminal do MySQL durante o curso, porém, se você prefere uma outra
interface,comoporexemplooMySQLWorkbench,fiqueavontadeeusaroqueformelhorparavocê.
AbraoterminaldoMySQLcomocomandomysql-uroot-pecrieabasededadosescola.
createdatabaseescola
QueryOK,1rowaffected(0,01sec)
Agora que criamos a nossa base de dados, podemos importar o arquivo .sql já existente. Saia do
terminaleexecuteoarquivonabasededadosescola.
mysql-uroot-pescola<escola.sql
Com o arquivo importado, podemos abrir novamente o MySQL, porém selecione a base de dados
escoola.
mysql-uroot-pescola
ParaverificartodasastabelasdanossabasededadospodemosutilizaroinstruçãoSHOWTABLESdo
MySQL:
SHOWTABLES;
+------------------+
|Tables_in_escola|
+------------------+
|aluno|
|curso|
|exercicio|
|matricula|
|nota|
|resposta|
|secao|
+------------------+
7rowsinset(0,00sec)
Sabemos quais são as tabelas, porém precisamos saber mais sobre a estrutura dessas tabelas, então
vamosutilizarainstruçãoDESC.Vamosverificarprimeiroatabelaaluno:
62
7ALUNOSSEMMATRÍCULAEOEXISTS
DESCaluno;
+-------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|nome|varchar(255)|NO||||
|email|varchar(255)|NO||||
+-------+--------------+------+-----+---------+----------------+
Percebaqueéumatabelabemsimples,ondeserãoarmazenadasapenasasinformaçõesdosalunos.
Vamosverificaratabelacurso:
DESCcurso;
+-------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|nome|varchar(255)|NO||||
+-------+--------------+------+-----+---------+----------------+
Da mesma forma que a tabela aluno, a tabela curso armazenada apenas as informações dos
cursos.Agoravamosverificaratabelamatricula:
DESCmatricula;
+----------+-------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+----------+-------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|aluno_id|int(11)|NO||NULL||
|curso_id|int(11)|NO||NULL||
|data|datetime|NO||NULL||
|tipo|varchar(20)|NO||||
+----------+-------------+------+-----+---------+----------------+
Conseguimos achar a primeira associação das tabelas, ou seja, a coluna aluno_id referencia a
tabelaalunoeacoluna curso_idreferenciaatabela curso.Entãovamosverificarquaissãotodos
oscursosdeumaluno.Paraumamelhorcompreensãodecomoéoresultadoesperadodessaquery,veja
aplanilhaaseguir:
Figura7.1:Planilhaexemplo
Comopodemosver,nóstemosalistadealunoseseusrespectivoscursos.Entãovamoscomeçara
retornar todos os alunos que possuem uma matrícula, ou seja, vamos fazer um JOIN entre a tabela
alunoematricula:
7ALUNOSSEMMATRÍCULAEOEXISTS
63
SELECTa.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id;
+-----------------+
|nome|
+-----------------+
|JoãodaSilva|
|FredericoJosé|
|AlbertoSantos|
|RenataAlonso|
|PauloJosé|
|ManoelSantos|
|RenataFerreira|
|PaulaSoares|
|RenataAlonso|
|ManoelSantos|
|JoãodaSilva|
|FredericoJosé|
|AlbertoSantos|
|FredericoJosé|
+-----------------+
Percebaqueutilizamosa.nome,m.aluno_idea.id,afinal,oquesignifica?Quandoescrevemos
alunoa,significaqueestamos"apelidando"atabela aluno,ouseja,todasasvezesqueutilizarmos
a.alguma_coisa,estaremospegandoalgumacolunadatabela aluno!EmSQL,esses"apelidos"são
conhecidoscomoAlias.
Os alunos foram retornados. Então agora vamos juntar a tabela curso e aluno com a tabela
matriculaaomesmotempo,porém,dessavezvamosretornaronomedoalunoeonomedocurso:
SELECTa.nome,c.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id
JOINcursocONm.curso_id=c.id;
+-----------------+------------------------------------+
|nome|nome|
+-----------------+------------------------------------+
|JoãodaSilva|SQLebancodedados|
|FredericoJosé|SQLebancodedados|
|AlbertoSantos|Scrumemétodoságeis|
|RenataAlonso|C#eorientaçãoaobjetos|
|PauloJosé|SQLebancodedados|
|ManoelSantos|Scrumemétodoságeis|
|RenataFerreira|DesenvolvimentowebcomVRaptor|
|PaulaSoares|DesenvolvimentomobilecomAndroid|
|RenataAlonso|DesenvolvimentomobilecomAndroid|
|ManoelSantos|SQLebancodedados|
|JoãodaSilva|C#eorientaçãoaobjetos|
|FredericoJosé|C#eorientaçãoaobjetos|
|AlbertoSantos|C#eorientaçãoaobjetos|
|FredericoJosé|DesenvolvimentowebcomVRaptor|
+-----------------+-------------------|-----------------+
Vamosverificarquantosalunosnóstemosnanossabasededados:
SELECTCOUNT(*)FROMaluno;
+----------+
|COUNT(*)|
64
7ALUNOSSEMMATRÍCULAEOEXISTS
+----------+
|16|
+----------+
7.1SUBQUERIES
Observe que foram retornadas 14 linhas quando buscamos todos os alunos e seus cursos, ou seja,
existem alunos que não tem matrícula! Como podemos verificar quais são os alunos que não estão
matriculados?NoMySQL,podemosutilizarafunção EXISTS()paraverificarseexistealgumregistro
deacordocomumadeterminadaquery:
SELECTa.nomeFROMalunoa
WHEREEXISTS(SELECTm.idFROMmatriculamWHEREm.aluno_id=a.id);
+-----------------+
|nome|
+-----------------+
|JoãodaSilva|
|FredericoJosé|
|AlbertoSantos|
|RenataAlonso|
|PauloJosé|
|ManoelSantos|
|RenataFerreira|
|PaulaSoares|
+-----------------+
Repare que escrevemos uma query dentro de uma função, quando fazemos esse tipo de query
chamamosdesubquery.Masoqueaconteceuexatamentenessaquery?QuandoutilizamosoEXISTS()
indicamosquequeremosoretornodetodososalunosnomesdosalunos(a.nome)queestãonatabela
aluno,porém,queremosapenasseexistirumamatrículaparaessealuno EXISTS(SELECTm.idFROM
matriculamWHEREm.aluno_id=a.id).
Perceba que novamente estamos retornando os alunos matriculados sendo que precisamos dos
alunosquenãoestãomatriculados.Nessecaso,podemosutilizarainstrução NOTparafazeranegação,
ouseja,pararetornarosalunosquenãopossuemmatrícula:
SELECTa.nomeFROMalunoa
WHERENOTEXISTS(SELECTm.idFROMmatriculamWHEREm.aluno_id=a.id);
+------------------+
|nome|
+------------------+
|PaulodaSilva|
|CarlosCunha|
|JosedaSilva|
|DaniloCunha|
|ZilmiraJosé|
|CristaldoSantos|
|OsmirFerreira|
|ClaudioSoares|
+------------------+
Conseguimosachartodosalunosquefazempartedosistemaequenãopossuemumamatrícula,algo
7.1SUBQUERIES
65
quenãoéesperado...Provavelmenteessesalunoshaviamcanceladoamatrículamasaindasim,existiao
cadastro deles, sabendo dessas informações temos a capacidade de criar relatórios com informações
relevantespara,porexemplo,osetorcomercialdessainstituiçãoentraremcontatocomosalunosnão
matriculadosetentarefetuarumavenda.
Ainstituiçãosubiualgunsexercícioseprecisasaberquaisdessesexercíciosnãoforamrespondidos.
Vamosnovamenteobservaroresultadoqueseesperautilizandoumaplanilhacomoexemplo:
Figura7.2:Planilhaexemplo
Como havíamos visto anteriormente, existem as tabelas, exercicio e resposta, vamos uma
olhadanatabelaexercicio:
DESCexercicio;
+------------------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+------------------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|secao_id|int(11)|NO||NULL||
|pergunta|varchar(255)|NO||NULL||
|resposta_oficial|varchar(255)|NO||NULL||
+------------------+--------------+------+-----+---------+----------------+
Agoraatabelarespostas:
DESCresposta;
+---------------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+---------------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|exercicio_id|int(11)|YES||NULL||
|aluno_id|int(11)|YES||NULL||
|resposta_dada|varchar(255)|YES||NULL||
+---------------+--------------+------+-----+---------+----------------+
Novamente encontramos uma outra associação, porém agora é entre exercicio e resposta. Então
vamospegartodososexercíciosquenãoforamrespondidosutilizandonovamenteoNOTEXISTS:
SELECT*FROMexercicioe
WHERENOTEXISTS(SELECTr.idFROMrespostarWHEREr.exercicio_id=e.id);
+----+----------+------------------------------+-----------------------------------------------------+
|id|secao_id|pergunta|resposta_oficial
|
+----+----------+------------------------------+-----------------------------------------------------+
|8|4|comofunciona?|insertinto(coluna1,coluna2)values(v1,v2)
|
66
7.1SUBQUERIES
|9|5|Comofuncionaaweb?|requisicaoeresposta
|
|10|5|Quelinguagenspossoajudar?|varias,java,php,c#,etc
|
|11|6|OqueehMVC?|modelviewcontroller
|
|12|6|Frameworksqueusam?|vraptor,springmvc,struts,etc
|
|14|8|Oqueéuminterceptor?|ehcomosefosseumfiltroqueehexecutadoantes
|
|15|8|quandousar?|tratamentodeexcecoes,conexaocomobancodedados
|
+----+----------+------------------------------+-----------------------------------------------------+
Se quisermos retornar da mesma forma que fizemos no exemplo da planilha, basta informar os
camposdesejados:
SELECTe.id,e.perguntaFROMexercicioe
WHERENOTEXISTS(SELECTr.idFROMrespostarWHEREr.exercicio_id=e.id);
+----+------------------------------+
|id|pergunta|
+----+------------------------------+
|8|comofunciona?|
|9|Comofuncionaaweb?|
|10|Quelinguagenspossoajudar?|
|11|OqueehMVC?|
|12|Frameworksqueusam?|
|14|Oqueéuminterceptor?|
|15|quandousar?|
+----+------------------------------+
Opessoaldocomercialdainstituição,informouqueexistemalgunscursosquenãotemnenhuma
matrícula.Vamosverificarcomoéesperadodesseresultadopelanossaplanilha:
Figura7.3:Planilhaexemplo
Da mesma forma que retornamos todos os exercícios que não tinha respostas, podemos retornar
todososcursosquenãopossuemmatrícula:
SELECTc.nomeFROMcursoc
WHERENOTEXISTS(SELECTm.idFROMmatriculamWHEREm.curso_id=c.id);
+--------------------------------+
|nome|
+--------------------------------+
|Javaeorientaçãoaobjetos|
|DesenvolvimentomobilecomiOS|
|RubyonRails|
|PHPeMySql|
+--------------------------------+
7.1SUBQUERIES
67
Vejaquequeriesmuitoparecidaspodemresolverproblemasdiferentes!
A instituição informou que tiveram vários exercícios que não foram respondidos pelos alunos nos
cursosqueforamrealizadosrecentemente.Vamosverificarquemforamessesalunos,paraverificarmos
o motivo de não ter respondido, se foi um problema no sistema ou na base de dados... Novamente
vamosverificaroqueseesperadesseresultadonumaplanilha:
Figura7.4:Planilhaexemplo
Vamostentarfazeressaquery.Começaremosretornandooalunojuntandoatabela alunocoma
tabelamatricula.
SELECTa.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id
Agoravamosjuntartambématabelacursoeretornaroscursostambém:
SELECTa.nome,c.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id
JOINcursocONm.curso_id=c.id
Vamostestareverificarcomoestáanossaqueryatualmente:
+-----------------+------------------------------------+
|nome|nome|
+-----------------+------------------------------------+
|JoãodaSilva|SQLebancodedados|
|FredericoJosé|SQLebancodedados|
|AlbertoSantos|Scrumemétodoságeis|
|RenataAlonso|C#eorientaçãoaobjetos|
|PauloJosé|SQLebancodedados|
|ManoelSantos|Scrumemétodoságeis|
|RenataFerreira|DesenvolvimentowebcomVRaptor|
|PaulaSoares|DesenvolvimentomobilecomAndroid|
|RenataAlonso|DesenvolvimentomobilecomAndroid|
|ManoelSantos|SQLebancodedados|
|JoãodaSilva|C#eorientaçãoaobjetos|
|FredericoJosé|C#eorientaçãoaobjetos|
|AlbertoSantos|C#eorientaçãoaobjetos|
|FredericoJosé|DesenvolvimentowebcomVRaptor|
+-----------------+------------------------------------+
Aparentementeestátudocerto,porémaindaprecisamosinformarquequeremosapenasosalunos
que não responderam os exercícios desses de algum desses cursos. Então adicionaremos agora o NOT
EXISTS():
SELECTa.nome,c.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id
JOINcursocONm.curso_id=c.id
68
7.1SUBQUERIES
WHERENOTEXISTS(SELECTr.aluno_id
FROMrespostarWHEREr.aluno_id=a.id);
+-----------------+------------------------------------+
|nome|nome|
+-----------------+------------------------------------+
|PauloJosé|SQLebancodedados|
|ManoelSantos|Scrumemétodoságeis|
|RenataFerreira|DesenvolvimentowebcomVRaptor|
|PaulaSoares|DesenvolvimentomobilecomAndroid|
|ManoelSantos|SQLebancodedados|
+-----------------+------------------------------------+
Há uma regra no sistema em que não pode permitir que alunos que não estejam matriculados
resposdamosexercícios,ouseja,nãopodeexistirumarespostanatabela respostacomumiddeum
aluno(aluno_id)quenãoestejamatriculado.Vamosprimeiroverificartodososalunosmatriculados
queresponderamosexercícios:
SELECTr.id,a.nomeFROMalunoa
JOINrespostarONr.aluno_id=a.id
WHEREEXISTS(SELECTm.aluno_idFROMmatriculam
WHEREm.aluno_id=a.id);
+----+-----------------+
|id|nome|
+----+-----------------+
|1|JoãodaSilva|
|2|JoãodaSilva|
|3|JoãodaSilva|
|4|JoãodaSilva|
|5|JoãodaSilva|
|6|JoãodaSilva|
|7|JoãodaSilva|
|8|FredericoJosé|
|9|FredericoJosé|
|10|FredericoJosé|
|11|FredericoJosé|
|12|AlbertoSantos|
|13|AlbertoSantos|
|14|AlbertoSantos|
|15|AlbertoSantos|
|16|AlbertoSantos|
|17|AlbertoSantos|
|18|AlbertoSantos|
|19|AlbertoSantos|
|20|AlbertoSantos|
|21|RenataAlonso|
|22|RenataAlonso|
|23|RenataAlonso|
|24|RenataAlonso|
|25|RenataAlonso|
|26|RenataAlonso|
|27|RenataAlonso|
+----+-----------------+
Observequerepetiualgunsalunos,poisumalunorespondeumaisdeumaquestão.Vamosverificar
agoraosqueresponderam,poŕemnãoestãomatriculados:
SELECTr.id,a.nomeFROMalunoa
JOINrespostarONr.aluno_id=a.id
7.1SUBQUERIES
69
WHERENOTEXISTS(SELECTm.aluno_idFROMmatriculam
WHEREm.aluno_id=a.id);
Emptyset(0,00sec)
Oresultadosaiuconformeoesperado,issosignifica,quenãoexistemrespostasdealunosquenão
possuemmatrícula!
7.2RESUMINDO
Nesse capítulo aprendemos a criar queries capazes de retornar valores caso exista, ou não, uma
associação entre duas tabelas, como por exemplo, se um aluno está matriculado ou se um aluno
respondeualgumexercíciopormeiodafunção EXISTS().Aprendemostambémoquesãosubqueries
como no exemplo em que passamos uma query como parâmetro para a função EXISTS() resolver
diversosproblemas,alémdisso,analisamosqueváriasqueries"similadres"temacapacidadederesolver
problemasdistintosutilizandooEXISTS().Vamosparaosexercícios?
EXERCÍCIOS
1. Baixeoschemadobancodedadosaqui
Crieobancodedadosescola:
createdatabaseescola
Importe-onoseuMysqlcomoseguintecomando,diretonoterminal:
mysql-uroot-pescola<escola.sql
FaçaumSELECTqualquerparagarantirqueosdadosestãolá.
DICA:Salveoarquivo escola.sqlemumlugardefácilacessopeloterminal.Vocêdeverodaro
comando para importar o schema no mesmo lugar onde estar o aquivo escola.sql. Por exemplo,
salveoarquivoSQLnapasta\alura-mysql.Depoisabraumterminaleentrenessapasta:
cd\aula-mysql
mysql-uroot-pescola<escola.sql
NoWindowsvocêpodeusarocomando diréparalistarosarquivosdapastaatualnalinhade
comando. Ou seja, ao executar dir na pasta aula-mysql você deve encontrar o arquivo
escola.sql.
1. Busquetodososalunosquenãotenhamnenhumamatrículanoscursos.
2. Busquetodososalunosquenãotiveramnenhumamatrículanosúltimos45dias,usandoainstrução
EXISTS.
70
7.2RESUMINDO
3. ÉpossívelfazeramesmaconsultasemusarEXISTS?Quaissão?
7.2RESUMINDO
71
CAPÍTULO8
AGRUPANDODADOSCOMGROUPBY
Ainstuiçãosolicitouamédiadetodososcursosparafazerumacomparaçãodenotasparaverificarse
todososcursospossuemamesmamédia,quaiscursostemmenoresnotasequaispossuemasmaiores
notas.Vamosverificaraestruturadealgumastabelasdanossabasededados,começaremospelatabela
curso:
DESCcurso;
+-------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|nome|varchar(255)|NO||||
+-------+--------------+------+-----+---------+----------------+
Agoravamosverificaratabelasecao:
DESCsecao;
+------------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+------------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|curso_id|int(11)|NO||NULL||
|titulo|varchar(255)|NO||||
|explicacao|varchar(255)|NO||NULL||
|numero|int(11)|NO||NULL||
+------------+--------------+------+-----+---------+----------------+
Já podemos perceber que existe uma relação entre curso de secao. Vamos também dar uma
olhadanatabelaexercicio:
DESCexercicio;
+------------------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+------------------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|secao_id|int(11)|NO||NULL||
|pergunta|varchar(255)|NO||NULL||
|resposta_oficial|varchar(255)|NO||NULL||
+------------------+--------------+------+-----+---------+----------------+
Observe que na tabela exercicio temos uma associação com a tabela secao. Agora vamos
verificaratabelaresposta:
DESCresposta
72
8AGRUPANDODADOSCOMGROUPBY
+---------------+--------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+---------------+--------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|exercicio_id|int(11)|YES||NULL||
|aluno_id|int(11)|YES||NULL||
|resposta_dada|varchar(255)|YES||NULL||
+---------------+--------------+------+-----+---------+----------------+
Podemosverificarqueatabelarespostaestáassociadacomatabela exercício.Porfim,vamos
verificaratabelanota:
DESCnota;
+-------------+---------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+-------------+---------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|resposta_id|int(11)|YES||NULL||
|nota|decimal(18,2)|YES||NULL||
+-------------+---------------+------+-----+---------+----------------+
Notequetambématabelanotapossuiumaassociação,nessecasocomatabelaresposta.
Comovimos,existemmuitastabelasquepodemosselecionaremnossaquery,entãovamosmontara
nossaqueryporpartes.Começaremospelatabelanota:
SELECTn.notaFROMnotan;
+-------+
|nota|
+-------+
|8.00|
|0.00|
|7.00|
|6.00|
|9.00|
|10.00|
|4.00|
|4.00|
|7.00|
|8.00|
|6.00|
|7.00|
|4.00|
|9.00|
|3.00|
|5.00|
|5.00|
|5.00|
|6.00|
|8.00|
|8.00|
|9.00|
|10.00|
|2.00|
|0.00|
|1.00|
|4.00|
8AGRUPANDODADOSCOMGROUPBY
73
+-------+
Conseguimos pegar todas as notas, agora precisamos resolver a nossa primeira associação, nesse
caso,juntaratabelarespostacomatabelanota:
SELECTn.notaFROMnotan
JOINrespostarONn.resposta_id=r.id;
+-------+
|nota|
+-------+
|8.00|
|0.00|
|7.00|
|6.00|
|9.00|
|10.00|
|4.00|
|4.00|
|7.00|
|8.00|
|6.00|
|7.00|
|4.00|
|9.00|
|3.00|
|5.00|
|5.00|
|5.00|
|6.00|
|8.00|
|8.00|
|9.00|
|10.00|
|2.00|
|0.00|
|1.00|
|4.00|
+-------+
Devolvemostodasasnotasassociadascomatabelaresposta.AgoravamosparaopróximoJOIN
entreatabelarespostaeatabelaexercicio:
SELECTn.notaFROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id;
+-------+
|nota|
+-------+
|8.00|
|0.00|
|7.00|
|6.00|
|9.00|
|10.00|
|4.00|
|4.00|
|7.00|
|8.00|
|6.00|
74
8AGRUPANDODADOSCOMGROUPBY
|7.00|
|4.00|
|9.00|
|3.00|
|5.00|
|5.00|
|5.00|
|6.00|
|8.00|
|8.00|
|9.00|
|10.00|
|2.00|
|0.00|
|1.00|
|4.00|
+-------+
Agorafaremosaassociaçãoentreatabelaexercicioeatabelasecao:
SELECTn.notaFROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id;
+-------+
|nota|
+-------+
|8.00|
|0.00|
|7.00|
|6.00|
|9.00|
|10.00|
|4.00|
|4.00|
|7.00|
|8.00|
|6.00|
|7.00|
|4.00|
|9.00|
|3.00|
|5.00|
|5.00|
|5.00|
|6.00|
|8.00|
|8.00|
|9.00|
|10.00|
|2.00|
|0.00|
|1.00|
|4.00|
+-------+
Porfim,faremosaúltimaassociaçãoentreatabelasecaoeatabelacurso.
SELECTn.notaFROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
8AGRUPANDODADOSCOMGROUPBY
75
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id;
+-------+
|nota|
+-------+
|8.00|
|0.00|
|7.00|
|6.00|
|9.00|
|10.00|
|4.00|
|4.00|
|7.00|
|8.00|
|6.00|
|7.00|
|4.00|
|9.00|
|3.00|
|5.00|
|5.00|
|5.00|
|6.00|
|8.00|
|8.00|
|9.00|
|10.00|
|2.00|
|0.00|
|1.00|
|4.00|
+-------+
Fizemos todas associações que precisávamos, porém repare que ainda está retornando a nota de
todososalunosporcursoumaauma,porémnósprecisamosdamédiaporcurso!NoMySQL,podemos
utilizarafunçãoAVG()paratiraramédia:
SELECTAVG(n.nota)FROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id;
+-------------+
|AVG(n.nota)|
+-------------+
|5.740741|
+-------------+
Observequefoiretornadoapenasumvalor,seráqueessamédiaéigualparatodososcursos?Vamos
tentarretornaroscursoseverificarmos:
SELECTc.nome,AVG(n.nota)FROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id;
76
8AGRUPANDODADOSCOMGROUPBY
+----------------------+-------------+
|nome|AVG(n.nota)|
+----------------------+-------------+
|SQLebancodedados|5.740741|
+----------------------+-------------+
Apenas1curso?Nãoeraesseoresultadoqueesperávamos!QuandoutilizamosafunçãoAVG()ela
calculatodososvaloresexistentesdaqueryeretornaamédia,porémemapenasumalinha!Paraquea
funçãoAVG()calculeamédiadecadacurso,precisamosinformarquequeremosagruparamédiapara
umadeterminadacoluna,nessecaso,acolunac.nome,ouseja,paracadacursodiferentequeremosque
calculeamédia.Paraagruparmosumacolunautilizamosainstrução GROUPBY,informandoacoluna
quequeremosagrupar:
SELECTc.nome,AVG(n.nota)FROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id
GROUPBYc.nome;
+---------------------------------+-------------+
|nome|AVG(n.nota)|
+---------------------------------+-------------+
|C#eorientaçãoaobjetos|4.857143|
|DesenvolvimentowebcomVRaptor|8.000000|
|Scrumemétodoságeis|5.777778|
|SQLebancodedados|6.100000|
+---------------------------------+-------------+
Opessoaldocomercialdainstituiçãoinformouquealgunsalunosestãoreclamandopelaquantidade
de exercícios nos cursos. Então vamos verificar quantos exercícios existem para cada curso. Primeiro
vamosverificarquantosexercíciosexistemnobancousandoafunçãoCOUNT():
SELECTCOUNT(*)FROMexercicio;
+----------+
|COUNT(*)|
+----------+
|31|
+----------+
Retormosaquantidadedetodososexercícios,porémnósprecisamossaberototaldeexercíciospara
cadacurso,ouseja,precisamosjuntaratabelacurso.Porém,parajuntaratabelacurso,teremosque
juntaratabelasecao:
SELECTCOUNT(*)FROMexercicioe
JOINsecaosONe.secao_id=s.id
Agorapodemosjuntaratabelacursoeretornaronomedocursotambém:
SELECTc.nome,COUNT(*)FROMexercicioe
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id;
+----------------------+----------+
|nome|COUNT(*)|
8AGRUPANDODADOSCOMGROUPBY
77
+----------------------+----------+
|SQLebancodedados|31|
+----------------------+----------+
Percebaqueoresultadofoisimilaraoqueaconteceuquandotentamostiraramédiasemagrupar!
Então precisamos também informar que queremos agrupar a contagem pelo nome do curso. Então
vamosadicionaroGROUPBY:
SELECTc.nome,COUNT(*)FROMexercicioe
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id
GROUPBYc.nome;
+---------------------------------+----------+
|nome|COUNT(*)|
+---------------------------------+----------+
|C#eorientaçãoaobjetos|7|
|DesenvolvimentowebcomVRaptor|7|
|Scrumemétodoságeis|9|
|SQLebancodedados|8|
+---------------------------------+----------+
Notequeonomedacolunaquecontatodososexercíciosestáumpoucoestranha,vamosadicionar
umaliasparamelhoraroresultado:
SELECTc.nome,COUNT(*)AScontagemFROMexercicioe
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id
GROUPBYc.nome;
+---------------------------------+----------+
|nome|contagem|
+---------------------------------+----------+
|C#eorientaçãoaobjetos|7|
|DesenvolvimentowebcomVRaptor|7|
|Scrumemétodoságeis|9|
|SQLebancodedados|8|
+---------------------------------+----------+
Agoraorelatóriofazmuitomaissentido.
TodofinaldesemestrenósprecisamosenviarumrelatórioparaoMECinformandoquantosalunos
estãomatriculadosemcadacursodainstituição.Faremosnovamenteanossaqueryporpartes,vamos
retornarprimeirotodososcursos:
SELECTc.nomeFROMcursoc
Vamosjuntaratabelamatricula:
SELECTc.nomeFROMcursoc
JOINmatriculamONm.curso_id=c.id
Agoravamosjuntarosalunostambém:
SELECTc.nomeFROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id;
78
8AGRUPANDODADOSCOMGROUPBY
Precisamosagoracontaraquantidadedealunos:
SELECTc.nome,COUNT(a.id)ASquantidadeFROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id;
+----------------------+------------+
|nome|quantidade|
+----------------------+------------+
|SQLebancodedados|14|
+----------------------+------------+
Lembre-sequeprecisamosagruparacontagempelonomedocurso:
SELECTc.nome,COUNT(a.id)ASquantidadeFROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id
GROUPBYc.nome;
+------------------------------------+------------+
|nome|quantidade|
+------------------------------------+------------+
|C#eorientaçãoaobjetos|4|
|DesenvolvimentomobilecomAndroid|2|
|DesenvolvimentowebcomVRaptor|2|
|Scrumemétodoságeis|2|
|SQLebancodedados|4|
+------------------------------------+------------+
Agoraconseguimosrealizaronossorelatórioconformeoesperado.
8.1RESUMINDO
Vimosnessecapítulocomopodemosgerarrelatóriosutilizandofunçõescomo AVG()e COUNT().
Vimostambémque,seprecisamosretornarascolunasparaverificarqualéovalordecadalinha,como
porexemplo,amédiadecadacurso,precisamosagruparessascolunaspormeiodo GROUPBY.Vamos
paraosexercícios?
EXERCÍCIOS
1. Exibaamédiadasnotasporcurso.
2. Devolva o curso e as médias de notas, levando em conta somente alunos que tenham "Silva" ou
"Santos"nosobrenome.
3. Conteaquantidadederespostasporexercício.Exibaaperguntaeonúmeroderespostas.
4. VocêpodeordenarpeloCOUNTtambém.BastacolocarORDERBYCOUNT(coluna).
Peguearespostadoexercícioanterior,eordenepornúmeroderespostas,emordemdecrescente.
1. Podemos agrupar por mais de um campo de uma só vez. Por exemplo, se quisermos a média de
8.1RESUMINDO
79
notasporalunoporcurso,podemosfazerGROUPBYaluno.id,curso.id.
80
8.1RESUMINDO
CAPÍTULO9
FILTRANDOAGREGAÇÕESEOHAVING
Todo o fim de semestre, a instituição de ensino precisa montar os boletins dos alunos. Então vamos
montaraqueryqueretornarátodasasinformaçõesparamontaroboletim.Começaremosretornando
todasasnotasdosalunos:
SELECTn.notaFROMnotan
Agoravamosassociaracomasrespostascomasnotas:
SELECTn.notaFROMnotan
JOINrespostarONr.id=n.resposta_id
Associaremosagoracomosexercícioscomasrespostas:
SELECTn.notaFROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
Agoraassociaremosaseçãocomosexercícios:
SELECTn.notaFROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
Agoraocursocomaseção:
SELECTn.notaFROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
Porfim,arespostacomoaluno:
SELECTn.notaFROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
JOINalunoaONa.id=r.aluno_id;
Verificandooresultado:
+-------+
|nota|
+-------+
|8.00|
9FILTRANDOAGREGAÇÕESEOHAVING
81
|0.00|
|7.00|
|6.00|
|9.00|
|10.00|
|4.00|
|4.00|
|7.00|
|8.00|
|6.00|
|7.00|
|4.00|
|9.00|
|3.00|
|5.00|
|5.00|
|5.00|
|6.00|
|8.00|
|8.00|
|9.00|
|10.00|
|2.00|
|0.00|
|1.00|
|4.00|
+-------+
Observe que estamos fazendo queries grandes, aconselhamos que, no momento que precisar fazer
uma query complexa, faça queries menores e testes seus resultados, pois se existir alguma instrução
errada,émaisfácildeidentificaroproblema.
Agora que associamos todas as nossas tabelas necessárias, vamos tirar a média com a função de
agregaçãoAVG()queécapazdetirarmédias,conformevistoduranteocurso:
SELECTAVG(n.nota)FROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
JOINalunoaONa.id=r.aluno_id;
+-------------+
|AVG(n.nota)|
+-------------+
|5.740741|
+-------------+
Retornou a média, porém não queremos apenas a média! Precisamos também dos alunos e dos
cursos.Entãovamosadicioná-los:
SELECTa.nome,c.nome,AVG(n.nota)FROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
JOINalunoaONa.id=r.aluno_id;
+----------------+----------------------+-------------+
82
9FILTRANDOAGREGAÇÕESEOHAVING
|nome|nome|AVG(n.nota)|
+----------------+----------------------+-------------+
|JoãodaSilva|SQLebancodedados|5.740741|
+----------------+----------------------+-------------+
Lembre-sequeestamoslidandocomumafunçãodeagregação,ouseja,senãoinformarmosaforma
queelaprecisaagruparascolunas,elaretornaráapenasumalinha!Porém,precisamossemprepensar
em qual tipo de agrupamento é necessário, nesse caso queremos que mostre a média de acada aluno,
entãoagruparemospelosalunos,porémtambémquequeremosqueacadacursoqueoalunosfezmostre
asua:
SELECTa.nome,c.nome,AVG(n.nota)FROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
JOINalunoaONa.id=r.aluno_id
GROUPBYa.nome,c.nome;
+-----------------+---------------------------------+-------------+
|nome|nome|AVG(n.nota)|
+-----------------+---------------------------------+-------------+
|AlbertoSantos|Scrumemétodoságeis|5.777778|
|FredericoJosé|DesenvolvimentowebcomVRaptor|8.000000|
|FredericoJosé|SQLebancodedados|5.666667|
|JoãodaSilva|SQLebancodedados|6.285714|
|RenataAlonso|C#eorientaçãoaobjetos|4.857143|
+-----------------+---------------------------------+-------------+
9.1CONDIÇÕESCOMHAVING
Retornamostodasasmédiasdosalunos,porémainstituiçãoprecisadeumrelatórioseparadopara
todososalunosquereprovaram,ouseja,quetiraramnotabaixa,nessecasomédiasmenoresque5.De
acordocomoquevimosatéagorabastariaadicionarmosumWHERE:
SELECTa.nome,c.nome,AVG(n.nota)FROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
JOINalunoaONa.id=r.aluno_id
WHEREAVG(n.nota)<5
GROUPBYa.nome,c.nome;
ERROR1111(HY000):Invaliduseofgroupfunction
Nesse caso, estamos tentando adicionar condições para uma função de agregação, porém, quando
queremosadicionarcondiçõesparafunçõesdeagregaçãoprecisamosutilizaro HAVING ao invés de
WHERE:
SELECTa.nome,c.nome,AVG(n.nota)FROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
9.1CONDIÇÕESCOMHAVING
83
JOINalunoaONa.id=r.aluno_id
HAVINGAVG(n.nota)<5
GROUPBYa.nome,c.nome;
ERROR1064(42000):YouhaveanerrorinyourSQLsyntax;checkthemanualthatcorrespondstoyourM
ySQLserverversionfortherightsyntaxtousenear'GROUPBYa.nome,c.nome'atline8
Além de utilizarmos o HAVING existe um pequeno detalhe, precisamos sempre agrupar antes as
colunaspeloGROUPBYparadepoisutilizarmosoHAVING:
SELECTa.nome,c.nome,AVG(n.nota)FROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
JOINalunoaONa.id=r.aluno_id
GROUPBYa.nome,c.nome
HAVINGAVG(n.nota)<5;
+---------------+-----------------------------+-------------+
|nome|nome|AVG(n.nota)|
+---------------+-----------------------------+-------------+
|RenataAlonso|C#eorientaçãoaobjetos|4.857143|
+---------------+-----------------------------+-------------+
Agoraconseguimosretornaroalunoqueteveamédiaabaixode5.Esequiséssemospegartodosos
alunosqueaprovaram?Ésimples,bastariaalterarosinalpara>=:
SELECTa.nome,c.nome,AVG(n.nota)FROMnotan
JOINrespostarONr.id=n.resposta_id
JOINexercicioeONe.id=r.exercicio_id
JOINsecaosONs.id=e.secao_id
JOINcursocONc.id=s.curso_id
JOINalunoaONa.id=r.aluno_id
GROUPBYa.nome,c.nome
HAVINGAVG(n.nota)>=5;
+-----------------+---------------------------------+-------------+
|nome|nome|AVG(n.nota)|
+-----------------+---------------------------------+-------------+
|AlbertoSantos|Scrumemétodoságeis|5.777778|
|FredericoJosé|DesenvolvimentowebcomVRaptor|8.000000|
|FredericoJosé|SQLebancodedados|5.666667|
|JoãodaSilva|SQLebancodedados|6.285714|
+-----------------+---------------------------------+-------------+
A instuição enviou mais uma solicitação de um relatório informando quais cursos tem poucos
alunosparatomarumadecisãosevaimanteroscursosouseirácancelá-los.Entãovamosnovamente
fazeranossaqueryporpassos,primeirovamoscomeçarselecionandooscursos:
SELECTc.nomeFROMcursoc
Agoravamosjuntarocursocomamatrículaeamatrículacomoalunoeverificaroresultado:
SELECTc.nomeFROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id;
+------------------------------------+
84
9.1CONDIÇÕESCOMHAVING
|nome|
+------------------------------------+
|SQLebancodedados|
|SQLebancodedados|
|Scrumemétodoságeis|
|C#eorientaçãoaobjetos|
|SQLebancodedados|
|Scrumemétodoságeis|
|DesenvolvimentowebcomVRaptor|
|DesenvolvimentomobilecomAndroid|
|DesenvolvimentomobilecomAndroid|
|SQLebancodedados|
|C#eorientaçãoaobjetos|
|C#eorientaçãoaobjetos|
|C#eorientaçãoaobjetos|
|DesenvolvimentowebcomVRaptor|
+------------------------------------+
Nossaqueryestáfuncionando,entãovamoscontaraquantidadedealunoscomafunçãoCOUNT():
SELECTc.nome,COUNT(a.id)FROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id;
Háumdetalhenessaquery,poisqueremoscontartodososalunosdecadacurso,ouseja,precisamos
agruparoscursos!
SELECTc.nome,COUNT(a.id)FROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id
GROUPBYc.nome;
+------------------------------------+-------------+
|nome|COUNT(a.id)|
+------------------------------------+-------------+
|C#eorientaçãoaobjetos|4|
|DesenvolvimentomobilecomAndroid|2|
|DesenvolvimentowebcomVRaptor|2|
|Scrumemétodoságeis|2|
|SQLebancodedados|4|
+------------------------------------+-------------+
Aquery funcionou, porém precisamos saber apenas os cursos que tem poucos alunos, nesse caso,
cursosquetenhammenosde10alunos:
SELECTc.nome,COUNT(a.id)FROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id
GROUPBYc.nome
HAVINGCOUNT(a.id)<10;
+------------------------------------+-------------+
|nome|COUNT(a.id)|
+------------------------------------+-------------+
|C#eorientaçãoaobjetos|4|
|DesenvolvimentomobilecomAndroid|2|
|DesenvolvimentowebcomVRaptor|2|
|Scrumemétodoságeis|2|
|SQLebancodedados|4|
+------------------------------------+-------------+
9.1CONDIÇÕESCOMHAVING
85
Agorapodemosenviarorelatórioparaainstituição.
9.2RESUMINDO
Sabemos que para adicionarmos filtros apenas para colunas utilizamos a instrução WHERE e
indicamostodasaspeculiaridadesnecessárias,porémquandoprecisamosadicionarfiltrosparafunções
de agregação, como por exemplo o AVG(), precisamos utilizar a instrução HAVING. Além disso, é
sempre bom lembrar que, quando estamos desenvolvendo queries grandes, é recomendado que faça
passa-a-passoqueries menores, ou seja, resolva os menores problemas juntando cada tabela por vez e
teste para verificar se está funcionando, pois isso ajuda a verificar aonde está o problema da query.
Vamosparaosexercícios?
EXERCÍCIOS
1. Qualéaprincipaldiferençaentreasinstruçõeshavingewheredosql?
2. Devolva todos os alunos, cursos e a média de suas notas. Lembre-se de agrupar por aluno e por
curso.Filtretambémpelanota:sómostrealunoscomnotamédiamenordoque5.
3. Exibatodososcursoseasuaquantidadedematrículas.Mas,exibasomentecursosquetenhammais
de1matrícula.
4. Exiba o nome do curso e a quantidade de seções que existe nele. Mostre só cursos com mais de 3
seções.
86
9.2RESUMINDO
CAPÍTULO10
MÚLTIPLOSVALORESNACONDIÇÃOEO
IN
Osetordefinanceirodessainstituição,solicitouumrelatórioinformandotodasasformasdepagamento
cadastradas no banco de dados para verificar se está de acordo com o que eles trabalham. Na base de
dados,severificarmosatabelamatricula:
DESCmatricula;
+----------+-------------+------+-----+---------+----------------+
|Field|Type|Null|Key|Default|Extra|
+----------+-------------+------+-----+---------+----------------+
|id|int(11)|NO|PRI|NULL|auto_increment|
|aluno_id|int(11)|NO||NULL||
|curso_id|int(11)|NO||NULL||
|data|datetime|NO||NULL||
|tipo|varchar(20)|NO||||
+----------+-------------+------+-----+---------+----------------+
Observequeexisteacolunatipoquerepresentaqualéaformadepagamento.Eprecisamospegar
umrelatóriodaseguintemaneira:
Figura10.1:Planilhaexemplo
Vamosselecionarapenasacolunatipodatabelamatricula:
SELECTm.tipoFROMmatriculam;
+-------------+
|tipo|
+-------------+
|PAGA_PF|
|PAGA_PJ|
|PAGA_PF|
|PAGA_CHEQUE|
|PAGA_BOLETO|
|PAGA_PJ|
|PAGA_PF|
|PAGA_PJ|
|PAGA_PJ|
|PAGA_CHEQUE|
|PAGA_BOLETO|
|PAGA_PJ|
10MÚLTIPLOSVALORESNACONDIÇÃOEOIN
87
|PAGA_PF|
|PAGA_PJ|
+-------------+
Vejaqueforamretornadostiposdepagamentoiguais,porémprecisamosenviarumrelatórioapenas
com os tipos de pagamento distintos. Para retornamos os valores distintos de uma coluna podemos
utilizarainstruçãoDISTINCT:
SELECTDISTINCTm.tipoFROMmatriculam;
+-------------+
|tipo|
+-------------+
|PAGA_PF|
|PAGA_PJ|
|PAGA_CHEQUE|
|PAGA_BOLETO|
+-------------+
Conseguimosretornarorelatóriodasformasdepagamento,porémosetorfinanceiroaindaprecisa
saberdemaisinformações.Agorafoisolicitadoqueenviasseumrelatóriocomoscursoseaquantidade
dealunosquepossuemotipodepagamentoPJ.Vamosverificaroexemploemumaplanilha:
Figura10.2:Planilhaexemplo
SabemosqueorelatórioésobreaquantidadedematrículasqueforampagascomoPJ,precisamos
contar,ouseja,usaremosafunçãoCOUNT().Vamoscomeçaracontaraquantidadedematrículas:
SELECTCOUNT(m.id)FROMmatriculam;
+-------------+
|COUNT(m.id)|
+-------------+
|14|
+-------------+
Agoravamosjuntarcomatabelacursoeexibironomedocurso:
SELECTc.nome,COUNT(m.id)FROMmatriculam
JOINcursocONm.curso_id=c.id;
+----------------------+-------------+
|nome|COUNT(m.id)|
+----------------------+-------------+
|SQLebancodedados|14|
+----------------------+-------------+
Observequefoiretornadoapenasumalinha!Issosignificaqueafunção COUNT()tambéméuma
funçãodeagregação,ouseja,sequeremosadicionarmaiscolunasnanossaquery,precisamosagrupá-las.
Entãovamosagruparonomedocurso:
88
10MÚLTIPLOSVALORESNACONDIÇÃOEOIN
SELECTc.nome,COUNT(m.id)FROMmatriculam
JOINcursocONm.curso_id=c.id
GROUPBYc.nome;
+------------------------------------+-------------+
|nome|COUNT(m.id)|
+------------------------------------+-------------+
|C#eorientaçãoaobjetos|4|
|DesenvolvimentomobilecomAndroid|2|
|DesenvolvimentowebcomVRaptor|2|
|Scrumemétodoságeis|2|
|SQLebancodedados|4|
+------------------------------------+-------------+
Conseguimos retornar todas os cursos e a quantidade de matrículas, porém precisamos filtrar por
tipodepagamentoPJ.EntãovamosadicionarumWHERE:
SELECTc.nome,COUNT(m.id)FROMmatriculam
JOINcursocONm.curso_id=c.id
WHEREm.tipo='PAGA_PJ'
GROUPBYc.nome;
+------------------------------------+-------------+
|nome|COUNT(m.id)|
+------------------------------------+-------------+
|C#eorientaçãoaobjetos|1|
|DesenvolvimentomobilecomAndroid|2|
|DesenvolvimentowebcomVRaptor|1|
|Scrumemétodoságeis|1|
|SQLebancodedados|1|
+------------------------------------+-------------+
FILTROSUTILIZANDOOIN
Osetorfinanceirodainstituiçãoprecisademaisdetalhessobreostiposdepagamentodecadacurso,
elesprecisamdeumrelatóriosimilaraoquefizemos,porémparatodosquesejampagamentoPJePF.
Paradiferenciarotipodepagamento,precisaremosadicionaracolunadom.tipo:
SELECTc.nome,COUNT(m.id),m.tipo
FROMmatriculam
JOINcursocONm.curso_id=c.id
WHEREm.tipo='PAGA_PJ'
ORm.tipo='PAGA_PF'
GROUPBYc.nome,m.tipo;
+------------------------------------+-------------+---------+
|nome|COUNT(m.id)|tipo|
+------------------------------------+-------------+---------+
|C#eorientaçãoaobjetos|1|PAGA_PF|
|C#eorientaçãoaobjetos|1|PAGA_PJ|
|DesenvolvimentomobilecomAndroid|2|PAGA_PJ|
|DesenvolvimentowebcomVRaptor|1|PAGA_PF|
|DesenvolvimentowebcomVRaptor|1|PAGA_PJ|
|Scrumemétodoságeis|1|PAGA_PF|
|Scrumemétodoságeis|1|PAGA_PJ|
|SQLebancodedados|1|PAGA_PF|
|SQLebancodedados|1|PAGA_PJ|
+------------------------------------+-------------+---------+
10MÚLTIPLOSVALORESNACONDIÇÃOEOIN
89
Suponhamos que agora precisamos retornar também os que foram pagos em boleto ou cheque. O
quepoderíamosadicionarnaquery?MaisORs?
SELECTc.nome,COUNT(m.id),m.tipo
FROMmatriculam
JOINcursocONm.curso_id=c.id
WHEREm.tipo='PAGA_PJ'
ORm.tipo='PAGA_PF'
ORm.tipo='PAGA_BOLETO'
ORm.tipo='PAGA_CHEQUE'
ORm.tipo='...'
ORm.tipo='...'
GROUPBYc.nome,m.tipo;
Resolveria, mas perceba que a nossa query a cada novo tipo de pagamento a nossa query tende a
crescer,dificultandoaleitura...EmSQL,existeainstrução INquepermiteespecificarmosmaisdeum
valorqueprecisamosfiltraraomesmotempoparaumadeterminadacoluna:
SELECTc.nome,COUNT(m.id),m.tipo
FROMmatriculam
JOINcursocONm.curso_id=c.id
WHEREm.tipoIN('PAGA_PJ','PAGA_PF','PAGA_CHEQUE','PAGA_BOLETO')
GROUPBYc.nome,m.tipo;
+------------------------------------+-------------+-------------+
|nome|COUNT(m.id)|tipo|
+------------------------------------+-------------+-------------+
|C#eorientaçãoaobjetos|1|PAGA_BOLETO|
|C#eorientaçãoaobjetos|1|PAGA_CHEQUE|
|C#eorientaçãoaobjetos|1|PAGA_PF|
|C#eorientaçãoaobjetos|1|PAGA_PJ|
|DesenvolvimentomobilecomAndroid|2|PAGA_PJ|
|DesenvolvimentowebcomVRaptor|1|PAGA_PF|
|DesenvolvimentowebcomVRaptor|1|PAGA_PJ|
|Scrumemétodoságeis|1|PAGA_PF|
|Scrumemétodoságeis|1|PAGA_PJ|
|SQLebancodedados|1|PAGA_BOLETO|
|SQLebancodedados|1|PAGA_CHEQUE|
|SQLebancodedados|1|PAGA_PF|
|SQLebancodedados|1|PAGA_PJ|
+------------------------------------+-------------+-------------+
Seumnovotipodepagamentoforadicionado,bastaadicionarmosdentrodo INeanossaquery
funcionarácorretamente.
Ainstituiçãonomeou3alunoscomoosmaisdestacadosnosúltimoscursosrealizamosegostariade
saberquaisforamtodososcursosqueelesfizeram.Os3alunosquesedestacaramforam:JoãodaSilva,
AlbertoSantoseaRenataAlonso.Vamosverificarquaissãoosidsdessesalunos:
SELECT*FROMaluno;
+----+------------------+----------------------+
|id|nome|email|
+----+------------------+----------------------+
|1|JoãodaSilva|[email protected]|
|2|FredericoJosé|[email protected]|
|3|AlbertoSantos|[email protected]|
|4|RenataAlonso|[email protected]|
90
10MÚLTIPLOSVALORESNACONDIÇÃOEOIN
|5|PaulodaSilva|[email protected]|
|6|CarlosCunha|[email protected]|
|7|PauloJosé|[email protected]|
|8|ManoelSantos|[email protected]|
|9|RenataFerreira|[email protected]|
|10|PaulaSoares|[email protected]|
|11|JosedaSilva|[email protected]|
|12|DaniloCunha|[email protected]|
|13|ZilmiraJosé|[email protected]|
|14|CristaldoSantos|[email protected]|
|15|OsmirFerreira|[email protected]|
|16|ClaudioSoares|[email protected]|
+----+------------------+----------------------+
OalunoJoãodaSilvaé1,AlbertoSantos3eRenataAlonso4.Agoraquesabemososidspodemos
verificarosseuscursos.Entãovamoscomeçaranossaqueryretornandotodososcursos:
SELECTc.nomeFROMcursoc;
Agoravamosjuntarocursocomamatrícula:
SELECTc.nomeFROMcursoc
JOINmatriculamONm.curso_id=c.id;
Porfim,vamosjuntaramatriculacomoalunoeretornaronomedoaluno:
SELECTa.nome,c.nomeFROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id;
Fizemostodasasjunções,agorasóprecisamosdofiltro.Precisamosretornaroscursosdos3alunos
aomesmotempo,podemosutilizarainstruçãoIN:
SELECTa.nome,c.nomeFROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id
WHEREa.idIN(1,3,4);
+----------------+------------------------------------+
|nome|nome|
+----------------+------------------------------------+
|JoãodaSilva|SQLebancodedados|
|AlbertoSantos|Scrumemétodoságeis|
|RenataAlonso|C#eorientaçãoaobjetos|
|RenataAlonso|DesenvolvimentomobilecomAndroid|
|JoãodaSilva|C#eorientaçãoaobjetos|
|AlbertoSantos|C#eorientaçãoaobjetos|
+----------------+------------------------------------+
Retornamos todos os cursos dos 3 alunos, porém ainda tá um pouco desorganizado, então vamos
ordenarpelonomedosalunosutilizandooORDERBY:
SELECTa.nome,c.nomeFROMcursoc
JOINmatriculamONm.curso_id=c.id
JOINalunoaONm.aluno_id=a.id
WHEREa.idIN(1,3,4)
ORDERBYa.nome;
+----------------+------------------------------------+
10MÚLTIPLOSVALORESNACONDIÇÃOEOIN
91
|nome|nome|
+----------------+------------------------------------+
|AlbertoSantos|Scrumemétodoságeis|
|AlbertoSantos|C#eorientaçãoaobjetos|
|JoãodaSilva|SQLebancodedados|
|JoãodaSilva|C#eorientaçãoaobjetos|
|RenataAlonso|C#eorientaçãoaobjetos|
|RenataAlonso|DesenvolvimentomobilecomAndroid|
+----------------+------------------------------------+
Nainstituição,serãolançadosalgunscursosnovosde.NETeopessoaldocomercialprecisadivulgar
essescursosparaosex-alunos,porémapenasparaosex-alunosquejáfizeramoscursosdeC#edeSQL.
Inicialmentevamosverificarosidsdessescursos:
SELECT*FROMcurso;
+----+------------------------------------+
|id|nome|
+----+------------------------------------+
|1|SQLebancodedados|
|2|DesenvolvimentowebcomVRaptor|
|3|Scrumemétodoságeis|
|4|C#eorientaçãoaobjetos|
|5|Javaeorientaçãoaobjetos|
|6|DesenvolvimentomobilecomiOS|
|7|DesenvolvimentomobilecomAndroid|
|8|RubyonRails|
|9|PHPeMySql|
+----+------------------------------------+
CursodeSQLé1eocursodeC#é4.Construindoanossaquery,começaremosretornandooaluno:
SELECTa.nomeFROMalunoa;
Entãojuntamoscomamatriculaeocursoevamosretornarquaisforamoscursosrealizados:
SELECTa.nome,c.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id
JOINcursocONm.curso_id=c.id;
AgorautilizaremosofiltropararetornartantoocursodeSQL(1),quantoocursodeC#(4):
SELECTa.nome,c.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id
JOINcursocONm.curso_id=c.id
WHEREc.idIN(1,4);
+-----------------+-----------------------------+
|nome|nome|
+-----------------+-----------------------------+
|JoãodaSilva|SQLebancodedados|
|FredericoJosé|SQLebancodedados|
|RenataAlonso|C#eorientaçãoaobjetos|
|PauloJosé|SQLebancodedados|
|ManoelSantos|SQLebancodedados|
|JoãodaSilva|C#eorientaçãoaobjetos|
|FredericoJosé|C#eorientaçãoaobjetos|
|AlbertoSantos|C#eorientaçãoaobjetos|
+-----------------+-----------------------------+
92
10MÚLTIPLOSVALORESNACONDIÇÃOEOIN
Novamenteoresultadoestádesordenado,vamosordenarpelonomedoaluno:
SELECTa.nome,c.nomeFROMalunoa
JOINmatriculamONm.aluno_id=a.id
JOINcursocONm.curso_id=c.id
WHEREc.idIN(1,4)
ORDERBYa.nome;
+-----------------+-----------------------------+
|nome|nome|
+-----------------+-----------------------------+
|AlbertoSantos|C#eorientaçãoaobjetos|
|FredericoJosé|SQLebancodedados|
|FredericoJosé|C#eorientaçãoaobjetos|
|JoãodaSilva|SQLebancodedados|
|JoãodaSilva|C#eorientaçãoaobjetos|
|ManoelSantos|SQLebancodedados|
|PauloJosé|SQLebancodedados|
|RenataAlonso|C#eorientaçãoaobjetos|
+-----------------+-----------------------------+
Agora sabemos que apenas os alunos Frederico José e João da Silva, são os ex-alunos aptos para
realizarosnovoscursosde.NET.
10.1RESUMINDO
Nesse capítulo vimos que quando precisamos saber todos os valores de uma determinada coluna
podemosutilizarainstruçãoDISTINCTpararetornartodososvaloresdistintos,ouseja,semnenhuma
repetição. Vimos também que quando precisamos realizar vários filtros para uma mesma coluna,
podemos utilizar a instrução IN passando por parâmetro todos os valores que esperamos que seja
retornado,aoinvésdeficarpreenchendoanossaquerycomváriosORs.Vamosparaosexercícios?
EXERCÍCIOS
1. Exibatodosostiposdematrículaqueexistemnatabela.UseDISTINCTparaquenãohajarepetição.
2. Exibatodososcursoseasuaquantidadedematrículas.MasfiltrepormatrículasdostiposPFouPJ.
3. Traga todas as perguntas e a quantidade de respostas de cada uma. Mas dessa vez, somente dos
cursoscomID1e3.
10.1RESUMINDO
93
CAPÍTULO11
SUB-QUERIES
Ainstituiçãoprecisadeumrelatóriomaisrobusto,comasseguintesinformações:Precisadonomedo
alunoecurso,amédiadoalunoemrelaçãoaocursoeadiferençaentreamédiadoalunoeamédiageral
docurso.Demonstrandoemumaplanilha,oresultadoqueseesperaéoseguinte:
Figura11.1:Planilhaexemplo
ObservequeoalunoAlexfezocursodeJavatirou6demédiaeadiferençaentreamédiadeleea
médiageralparaocursodeJavafoi-1,issosignificaqueamédiageraldocursodeJavaé7,ouseja,6-7.
VamosanalisaroalunoGuilherme,vejaqueeletirou6demédiaeadiferençafoi0,poisamédiageral
do curso de SQL é 6, ou seja, 6 - 6. Por fim, a aluna Renata tirou média 7 no curso de C#, porém a
diferençafoi1,issosignificaqueamédiageralé6,ouseja,7-6.
Comovocêmontariaessaquery?Aparentementeéumpoucocomplexa...Entãovamoscomeçarpor
partesdamesmaformaquefizemosanteriormente.Começaremospelatabelanota:
SELECTn.notaFROMnotan;
Agoravamosjuntarastabelasrespostaeexercicioevamosverificaroresultado:
SELECTn.notaFROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id;
+-------+
|nota|
+-------+
|8.00|
|0.00|
|7.00|
|6.00|
|9.00|
|10.00|
|4.00|
|4.00|
|7.00|
|8.00|
|6.00|
|7.00|
94
11SUB-QUERIES
|4.00|
|9.00|
|3.00|
|5.00|
|5.00|
|5.00|
|6.00|
|8.00|
|8.00|
|9.00|
|10.00|
|2.00|
|0.00|
|1.00|
|4.00|
+-------+
Anossaqueryestáfuncionando.Vamosadicionarastabelasde secaoe curso,porém,dessavez
vamosadicionaronomedocurso:
SELECTc.nome,n.notaFROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id;
+---------------------------------+-------+
|nome|nota|
+---------------------------------+-------+
|SQLebancodedados|8.00|
|SQLebancodedados|0.00|
|SQLebancodedados|7.00|
|SQLebancodedados|6.00|
|SQLebancodedados|9.00|
|SQLebancodedados|10.00|
|SQLebancodedados|4.00|
|SQLebancodedados|4.00|
|SQLebancodedados|7.00|
|DesenvolvimentowebcomVRaptor|8.00|
|SQLebancodedados|6.00|
|Scrumemétodoságeis|7.00|
|Scrumemétodoságeis|4.00|
|Scrumemétodoságeis|9.00|
|Scrumemétodoságeis|3.00|
|Scrumemétodoságeis|5.00|
|Scrumemétodoságeis|5.00|
|Scrumemétodoságeis|5.00|
|Scrumemétodoságeis|6.00|
|Scrumemétodoságeis|8.00|
|C#eorientaçãoaobjetos|8.00|
|C#eorientaçãoaobjetos|9.00|
|C#eorientaçãoaobjetos|10.00|
|C#eorientaçãoaobjetos|2.00|
|C#eorientaçãoaobjetos|0.00|
|C#eorientaçãoaobjetos|1.00|
|C#eorientaçãoaobjetos|4.00|
+---------------------------------+-------+
Porfim,juntaremosatabelaalunocomatabelarespostaeretornaremosonomedoaluno:
SELECTa.nome,c.nome,n.notaFROMnotan
11SUB-QUERIES
95
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id
JOINalunoaONr.aluno_id=a.id;
+-----------------+---------------------------------+-------+
|nome|nome|nota|
+-----------------+---------------------------------+-------+
|JoãodaSilva|SQLebancodedados|8.00|
|JoãodaSilva|SQLebancodedados|0.00|
|JoãodaSilva|SQLebancodedados|7.00|
|JoãodaSilva|SQLebancodedados|6.00|
|JoãodaSilva|SQLebancodedados|9.00|
|JoãodaSilva|SQLebancodedados|10.00|
|JoãodaSilva|SQLebancodedados|4.00|
|FredericoJosé|SQLebancodedados|4.00|
|FredericoJosé|SQLebancodedados|7.00|
|FredericoJosé|DesenvolvimentowebcomVRaptor|8.00|
|FredericoJosé|SQLebancodedados|6.00|
|AlbertoSantos|Scrumemétodoságeis|7.00|
|AlbertoSantos|Scrumemétodoságeis|4.00|
|AlbertoSantos|Scrumemétodoságeis|9.00|
|AlbertoSantos|Scrumemétodoságeis|3.00|
|AlbertoSantos|Scrumemétodoságeis|5.00|
|AlbertoSantos|Scrumemétodoságeis|5.00|
|AlbertoSantos|Scrumemétodoságeis|5.00|
|AlbertoSantos|Scrumemétodoságeis|6.00|
|AlbertoSantos|Scrumemétodoságeis|8.00|
|RenataAlonso|C#eorientaçãoaobjetos|8.00|
|RenataAlonso|C#eorientaçãoaobjetos|9.00|
|RenataAlonso|C#eorientaçãoaobjetos|10.00|
|RenataAlonso|C#eorientaçãoaobjetos|2.00|
|RenataAlonso|C#eorientaçãoaobjetos|0.00|
|RenataAlonso|C#eorientaçãoaobjetos|1.00|
|RenataAlonso|C#eorientaçãoaobjetos|4.00|
+-----------------+---------------------------------+-------+
Conseguimosretornartodasasnotasdoalunoeoscursos,porémnósprecisamosdasmédiasenão
detodasasnotas.EntãovamosutilizarafunçãoAVG()pararetornaramédiadoaluno.Lembre-seque
afunçãoAVG()éumafunçãodeagregação,ouseja,precisamosagruparoalunoeocursotambém:
SELECTa.nome,c.nome,AVG(n.nota)FROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id
JOINalunoaONr.aluno_id=a.id
GROUPBYa.nome,c.nome;
+-----------------+---------------------------------+-------------+
|nome|nome|AVG(n.nota)|
+-----------------+---------------------------------+-------------+
|AlbertoSantos|Scrumemétodoságeis|5.777778|
|FredericoJosé|DesenvolvimentowebcomVRaptor|8.000000|
|FredericoJosé|SQLebancodedados|5.666667|
|JoãodaSilva|SQLebancodedados|6.285714|
|RenataAlonso|C#eorientaçãoaobjetos|4.857143|
+-----------------+---------------------------------+-------------+
Agoranóstemosamédiadoalunoeseurespectivocurso,masaindafaltaacolunadadiferençaque
96
11SUB-QUERIES
calculaadiferençaentreamédiadoalunoemumdeterminadocursoesubtraipelamédiageral.Porém
ainda não temos a média geral, então como podemos pegar a média geral? Vamos verificar a tabela
nota:
SELECT*FROMnota;
+----+-------------+-------+
|id|resposta_id|nota|
+----+-------------+-------+
|1|1|8.00|
|2|2|0.00|
|3|3|7.00|
|4|4|6.00|
|5|5|9.00|
|6|6|10.00|
|7|7|4.00|
|8|8|4.00|
|9|9|7.00|
|10|10|8.00|
|11|11|6.00|
|12|12|7.00|
|13|13|4.00|
|14|14|9.00|
|15|15|3.00|
|16|16|5.00|
|17|17|5.00|
|18|18|5.00|
|19|19|6.00|
|20|20|8.00|
|21|21|8.00|
|22|22|9.00|
|23|23|10.00|
|24|24|2.00|
|25|25|0.00|
|26|26|1.00|
|27|27|4.00|
+----+-------------+-------+
Percebaquetemostodasasnotas,teoricamenteasnotasdetodososcursos,ouseja,parapegarmosa
médiageralusaremosoAVG():
SELECTAVG(n.nota)FROMnotan;
+-------------+
|AVG(n.nota)|
+-------------+
|5.740741|
+-------------+
Conseguimosamédiageral,agoravamosadicionaracolunadiferença.Antesdecomeçarafazera
colunadiferençavamosnomearacolunademédiadoalunoparamelhoraravisualização:
SELECTa.nome,c.nome,AVG(n.nota)asmedia_alunoFROMnota
...
Acolunadiferençaprecisadainformaçãoda media_aluno - media geral, porém, nós não temos
nenhumacolunaparaamédiageral,eoresultadoqueprecisamosestáemumaquerydiferente...Como
podemos resolver isso? Adicionando essa outra query dentro da query principal, ou seja, fazer uma
11SUB-QUERIES
97
subquery:
SELECTa.nome,c.nome,AVG(n.nota)asmedia_aluno,
AVG(n.nota)-(SELECTAVG(n.nota)FROMnotan)asdiferenca
FROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id
JOINalunoaONr.aluno_id=a.id
GROUPBYa.nome,c.nome;
+-----------------+---------------------------------+-------------+-----------+
|nome|nome|media_aluno|diferenca|
+-----------------+---------------------------------+-------------+-----------+
|AlbertoSantos|Scrumemétodoságeis|5.777778|0.037037|
|FredericoJosé|DesenvolvimentowebcomVRaptor|8.000000|2.259259|
|FredericoJosé|SQLebancodedados|5.666667|-0.074074|
|JoãodaSilva|SQLebancodedados|6.285714|0.544974|
|RenataAlonso|C#eorientaçãoaobjetos|4.857143|-0.883598|
+-----------------+---------------------------------+-------------+-----------+
Observe que agora retornamos a diferença, mas será que essas informações batem? Que tal
retornamosamédiageraltambém?
SELECTa.nome,c.nome,AVG(n.nota)asmedia_aluno,
(SELECTAVG(n.nota)FROMnotan)asmedia_geral,
AVG(n.nota)-(SELECTAVG(n.nota)FROMnotan)asdiferenca
FROMnotan
JOINrespostarONn.resposta_id=r.id
JOINexercicioeONr.exercicio_id=e.id
JOINsecaosONe.secao_id=s.id
JOINcursocONs.curso_id=c.id
JOINalunoaONr.aluno_id=a.id
GROUPBYa.nome,c.nome;
+-----------------+---------------------------------+-------------+-------------+-----------+
|nome|nome|media_aluno|media_geral|diferenca|
+-----------------+---------------------------------+-------------+-------------+-----------+
|AlbertoSantos|Scrumemétodoságeis|5.777778|5.740741|0.037037|
|FredericoJosé|DesenvolvimentowebcomVRaptor|8.000000|5.740741|2.259259|
|FredericoJosé|SQLebancodedados|5.666667|5.740741|-0.074074|
|JoãodaSilva|SQLebancodedados|6.285714|5.740741|0.544974|
|RenataAlonso|C#eorientaçãoaobjetos|4.857143|5.740741|-0.883598|
+-----------------+---------------------------------+-------------+-------------+-----------+
Conseguimos exibir o relatório como esperado, porém existe um pequeno detalhe. Note que o
resultadodasubquery(SELECTAVG(n.nota)FROMnotan)foideapenasumalinhaeéjustamentepor
essemotivoqueconseguimosefetuaroperaçõesaritméticascomo,nessecaso,asubtração.Seoresultado
fossemaisdeumalinha,nãoseriapossívelrealizaroperações.
Ainstituiçãoprecisadeumrelatóriodoaproveitamentodosalunosnoscursos,ouseja,precisamos
saberseelesestãorespondendotodososexercícios,entãoiremosbuscaronúmeroderespostasquecada
respondeualunoindividualmente.Vamosverificaroqueéesperadodoresultadoemumaplanilha:
98
11SUB-QUERIES
Figura11.2:Planilhaexemplo
Entãoprimeirocomeçaremosretornandoosalunos:
SELECTa.nomeFROMalunoa;
Agoraprecisamosdaquantidadedetodasasrespostas,entãousaremosoCOUNT():
SELECTCOUNT(r.id)FROMrespostar;
+-------------+
|COUNT(r.id)|
+-------------+
|27|
+-------------+
Sabemos a query que conta as respostas e sabemos a query que retornam os alunos, então vamos
adicionar a query que conta as respostas dentro da que retorna os alunos, ou seja, vamos fazer
novamenteumasubquery!
SELECTa.nome,(SELECTCOUNT(r.id)FROMrespostar)ASquantidade_respostasFROMalunoa;
+------------------+----------------------+
|nome|quantidade_respostas|
+------------------+----------------------+
|JoãodaSilva|27|
|FredericoJosé|27|
|AlbertoSantos|27|
|RenataAlonso|27|
|PaulodaSilva|27|
|CarlosCunha|27|
|PauloJosé|27|
|ManoelSantos|27|
|RenataFerreira|27|
|PaulaSoares|27|
|JosedaSilva|27|
|DaniloCunha|27|
|ZilmiraJosé|27|
|CristaldoSantos|27|
|OsmirFerreira|27|
|ClaudioSoares|27|
+------------------+----------------------+
Observequeos resultadosda quantidadederespostas foramiguais paratodosos alunos,poisnão
adicionamos nenhum filtro na subquery. Para resolver o problema, basta adicionar um WHERE
indicandooqueprecisaserfiltrado,nessecaso,oiddosalunosretornadosnaqueryprincipal:
SELECTa.nome,(SELECTCOUNT(r.id)FROMrespostarWHEREr.aluno_id=a.id)ASquantidade_respostasF
ROMalunoa;
+------------------+----------------------+
11SUB-QUERIES
99
|nome|quantidade_respostas|
+------------------+----------------------+
|JoãodaSilva|7|
|FredericoJosé|4|
|AlbertoSantos|9|
|RenataAlonso|7|
|PaulodaSilva|0|
|CarlosCunha|0|
|PauloJosé|0|
|ManoelSantos|0|
|RenataFerreira|0|
|PaulaSoares|0|
|JosedaSilva|0|
|DaniloCunha|0|
|ZilmiraJosé|0|
|CristaldoSantos|0|
|OsmirFerreira|0|
|ClaudioSoares|0|
+------------------+----------------------+
Ainstituiçãoprecisadeumarelatóriomuitoparecidocomaqueryqueacabamosdefazer,elaprecisa
saberquantasmatrículasumalunotem,ouseja,aoínvesderesposta,informaremosasmatrículas.Então
vamosapenassubstituirasinformaçõesdasrespostaspelasinformaçõesdamatrícula:
SELECTa.nome,(SELECTCOUNT(m.id)FROMmatriculamWHEREm.aluno_id=a.id)ASquantidade_matricula
FROMalunoa;
+------------------+----------------------+
|nome|quantidade_matricula|
+------------------+----------------------+
|JoãodaSilva|2|
|FredericoJosé|3|
|AlbertoSantos|2|
|RenataAlonso|2|
|PaulodaSilva|0|
|CarlosCunha|0|
|PauloJosé|1|
|ManoelSantos|2|
|RenataFerreira|1|
|PaulaSoares|1|
|JosedaSilva|0|
|DaniloCunha|0|
|ZilmiraJosé|0|
|CristaldoSantos|0|
|OsmirFerreira|0|
|ClaudioSoares|0|
+------------------+----------------------+
Conseguimospegaraquantidadederespostaematriculadeumdeterminadoaluno,porémfizemos
isso separadamente, porém agora precisamos juntar essas informações para montar em um único
relatórioquemostre,onomedoaluno,aquantidadederespostaseaquantidadedematrículas.Então
vamospartirdoprincípio,ouseja,fazeraqueryqueretornatodososalunos:
SELECTa.nomeFROMalunoa;
Agoravamospegaraquantidadederespostas:
SELECTCOUNT(r.id)FROMrespostar;
100
11SUB-QUERIES
Eentãovamospegaraquantidadedematriculas:
SELECTCOUNT(m.id)FROMmatriculam;
TemostodososSELECTsqueresolvemumdeterminadoproblema,ouseja,agoraprecisamosjuntar
todos eles para resolver a nova necessidade. Então vamos adicionar as duas queries que contam as
matrículaseasrespostasdentrodaqueryprincipal,ouseja,aqueretornaosalunos:
SELECTa.nome,
(SELECTCOUNT(m.id)FROMmatriculamWHEREm.aluno_id=a.id)ASquantidade_matricula,
(SELECTCOUNT(r.id)FROMrespostarWHEREr.aluno_id=a.id)ASquantidade_respostas
FROMalunoa;
+------------------+----------------------+----------------------+
|nome|quantidade_matricula|quantidade_respostas|
+------------------+----------------------+----------------------+
|JoãodaSilva|2|7|
|FredericoJosé|3|4|
|AlbertoSantos|2|9|
|RenataAlonso|2|7|
|PaulodaSilva|0|0|
|CarlosCunha|0|0|
|PauloJosé|1|0|
|ManoelSantos|2|0|
|RenataFerreira|1|0|
|PaulaSoares|1|0|
|JosedaSilva|0|0|
|DaniloCunha|0|0|
|ZilmiraJosé|0|0|
|CristaldoSantos|0|0|
|OsmirFerreira|0|0|
|ClaudioSoares|0|0|
+------------------+----------------------+----------------------+
11.1RESUMINDO
Vimosquenessecapítuloaprendemosautilizarsubqueriespararesolverdiversosproblemas,como
por exemplo contar a quantidade de matriculas ou de respostas de um aluno. Também vimos que
podemosaplicaroperaçõesaritméticasutilizandosubqueries,porémésempreimportantelembrarque
sópodemosrealizaressetipodeoperaçõesdesdequeasubquerieretorneumaúnicalinha.Entãovamos
paraosexercícios?
EXERCÍCIOS
1. Exibaamédiadasnotasporaluno,alémdeumacolunacomadiferençaentreamédiadoalunoea
médiageral.Usesub-queriesparaisso.
2. Qualéoproblemadeseusarsub-queries?
3. Exiba a quantidade de matrículas por curso. Além disso, exiba a divisão entre matrículas naquele
cursoematrículastotais.
11.1RESUMINDO
101
102
11.1RESUMINDO
CAPÍTULO12
ENTENDENDOOLEFTJOIN
Osinstrutoresdainstituiçãopediramumrelatóriocomosalunosquesãomaisparticipativosnasalade
aula, ou seja, queremos retornar os alunos que responderam mais exercícios. Consequentemente
encontraremos também os alunos que não estão participando muito, então já aproveitamos e
conversamoscomelesparaentenderoqueestáacontecendo.Entãocomeçaremosretornandooaluno:
SELECTa.nomeFROMalunon;
Agora vamos contar a quantidade de respostas por meio da função COUNT() e agrupando pelo
nomedoaluno:
SELECTa.nome,COUNT(r.id)ASrespostas
FROMalunoa
JOINrespostarONr.aluno_id=a.id
GROUPBYa.nome;
+-----------------+-----------+
|nome|respostas|
+-----------------+-----------+
|AlbertoSantos|9|
|FredericoJosé|4|
|JoãodaSilva|7|
|RenataAlonso|7|
+-----------------+-----------+
Mas onde estão todos os meus alunos? Fugiram? Aparentemente essa query não está trazendo
exatamenteoqueagenteesperava...Vamoscontaraquantidadedealunosexistentes:
SELECTCOUNT(a.id)FROMalunoa;
+-------------+
|COUNT(a.id)|
+-------------+
|16|
+-------------+
Observe que existe 16 alunos no banco de dados, porém só foram retornados 4 alunos e suas
respostas. Provavelmente não está sendo retornando os alunos que não possuem respostas! Vamos
verificaroqueestáacontecendoexatamente.Vamospegarumalunoquenãofoiretornado,comoode
id5.Quantasrespostaseletem?
SELECTr.idFROMrespostarWHEREr.aluno_id=5;
+------------------+
|id|
+------------------+
12ENTENDENDOOLEFTJOIN
103
|0|
+------------------+
Tudo bem, ele não respondeu, mas como ele foi desaparecer daquela query nossa? Vamos pegar
outroalunoquedesapareceu,odeid6:
SELECTr.idFROMrespostarWHEREr.aluno_id=6;
+------------------+
|id|
+------------------+
|0|
+------------------+
Opa,parecequeencontramosumpadrão.
SELECTr.idFROMrespostarWHEREr.aluno_id=7;
+------------------+
|id|
+------------------+
|0|
+------------------+
Sim,encontramosumpadrão.Seráqueéverdadeiroessateoriaqueestásurgindonaminhacabeça?
Alunos sem resposta desapareceram? Vamos procurar todos os alunos que não possuem nenhuma
resposta.Istoéselecionarosalunosquenãoexiste,respostadestealuno:
SELECTa.nomeFROMalunoaWHERENOTEXISTS(SELECTr.idFROMrespostarWHEREr.aluno_id=a.id);
+------------------+
|nome|
+------------------+
|PaulodaSilva|
|CarlosCunha|
|PauloJosé|
|ManoelSantos|
|RenataFerreira|
|PaulaSoares|
|JosedaSilva|
|DaniloCunha|
|ZilmiraJosé|
|CristaldoSantos|
|OsmirFerreira|
|ClaudioSoares|
+------------------+
Se verificarmos os nomes, realmente, todos os alunos que não tem respostas não estão sendo
retornadosnaquelaprimeiraquery.Porémnósqueremostambémqueretorneosalunossemrespostas...
Vamostentardeumaoutramaneira,vamosretornaronomedoalunoearespostaqueelerespondeu:
SELECTa.nome,r.resposta_dadaFROMalunoa
JOINrespostarONr.aluno_id=a.id;
+-----------------+----------------------------------------------------------------------------------+
|nome|resposta_dada
|
104
12ENTENDENDOOLEFTJOIN
+-----------------+----------------------------------------------------------------------------------+
|JoãodaSilva|umaselecao
|
|JoãodaSilva|ixi,naosei
|
|JoãodaSilva|alterardados
|
|JoãodaSilva|eskecerowhereealterartudo
|
|JoãodaSilva|apagarcoisas
|
|JoãodaSilva|tbnaopodeeskecerowhere
|
|JoãodaSilva|inserirdados
|
|FredericoJosé|buscardados
|
|FredericoJosé|selectcamposfromtabela
|
|FredericoJosé|alterarcoisas
|
|FredericoJosé|ixi,naosei
|
|AlbertoSantos|tempoprafazeralgo
|
|AlbertoSantos|1a4semanas
|
|AlbertoSantos|melhoriadoprocesso
|
|AlbertoSantos|tododia
|
|AlbertoSantos|reuniaodestatus
|
|AlbertoSantos|tododia
|
|AlbertoSantos|oquadrobranco
|
|AlbertoSantos|ummetodoagil
|
|AlbertoSantos|temvariosoutros
|
|RenataAlonso|ehainternet
|
|RenataAlonso|browserfazrequisicao,servidormandaresposta
|
|RenataAlonso|ehoservidorquelidacomhttp
|
|RenataAlonso|naosei
|
|RenataAlonso|bancodedados!
|
|RenataAlonso|ehcolocaraappnainternet
|
|RenataAlonso|dependedatecnologia,masgeralmenteehlevarpraumservidorquetanainternet
|
+-----------------+----------------------------------------------------------------------------------+
Agoravamosadicionaracolunaiddatabelaalunoealuno_iddatabelaresposta:
SELECTa.id,a.nome,r.aluno_id,r.resposta_dadaFROMalunoa
12ENTENDENDOOLEFTJOIN
105
JOINrespostarONr.aluno_id=a.id;
+----+-----------------+----------+----------------------------------------------------------------------------------+
|id|nome|aluno_id|resposta_dada
|
+----+-----------------+----------+----------------------------------------------------------------------------------+
|1|JoãodaSilva|1|umaselecao
|
|1|JoãodaSilva|1|ixi,naosei
|
|1|JoãodaSilva|1|alterardados
|
|1|JoãodaSilva|1|eskecerowhereealterartudo
|
|1|JoãodaSilva|1|apagarcoisas
|
|1|JoãodaSilva|1|tbnaopodeeskecerowhere
|
|1|JoãodaSilva|1|inserirdados
|
|2|FredericoJosé|2|buscardados
|
|2|FredericoJosé|2|selectcamposfromtabela
|
|2|FredericoJosé|2|alterarcoisas
|
|2|FredericoJosé|2|ixi,naosei
|
|3|AlbertoSantos|3|tempoprafazeralgo
|
|3|AlbertoSantos|3|1a4semanas
|
|3|AlbertoSantos|3|melhoriadoprocesso
|
|3|AlbertoSantos|3|tododia
|
|3|AlbertoSantos|3|reuniaodestatus
|
|3|AlbertoSantos|3|tododia
|
|3|AlbertoSantos|3|oquadrobranco
|
|3|AlbertoSantos|3|ummetodoagil
|
|3|AlbertoSantos|3|temvariosoutros
|
|4|RenataAlonso|4|ehainternet
|
|4|RenataAlonso|4|browserfazrequisicao,servidormandaresposta
|
|4|RenataAlonso|4|ehoservidorquelidacomhttp
|
|4|RenataAlonso|4|naosei
|
|4|RenataAlonso|4|bancodedados!
|
|4|RenataAlonso|4|ehcolocaraappnainternet
|
|4|RenataAlonso|4|dependedatecnologia,masgeralmenteehlevarpraumservidorqu
etanainternet|
+----+-----------------+----------+------------------------------------------------------------------
106
12ENTENDENDOOLEFTJOIN
-----------------+
Perceba que separamos as informações dos alunos de um lado e a das respostas do outro lado.
Analisando esses dados podemos verificar que quando fizemos o JOIN entre a tabela aluno e
resposta estamos trazendo apenas todos os registros que possuem o id da tabela aluno e o
aluno_iddatabelaresposta.
OqueoSQLfaztambéméquetodososalunoscujoo idnão estejanacoluna aluno_id não
serãoretornados!Istoé,elesótraráparanósalguémqueo JOINtenhavalorigualnasduastabelas.Se
sóestápresenteemumadastabelas,eleignora.
EmSQL,existeum JOINdiferentequepermiteoretornodealunosquetambémnãopossuamo
id na tabela que está sendo associada. Queremos pegar todo mundo da tabela da esquerda,
independentementedeexistirounãoumvalornatabeladadireita.Éumtaldejoindeesquerda,oLEFT
JOIN,ouseja,eletrarátodososregistrosdatabeladaesquerdamesmoquenãoexistaumaassociação
natabeladadireita:
SELECTa.id,a.nome,r.aluno_id,r.resposta_dadaFROMalunoa
LEFTJOINrespostarONr.aluno_id=a.id;
+----+------------------+----------+----------------------------------------------------------------------------------+
|id|nome|aluno_id|resposta_dada
|
+----+------------------+----------+----------------------------------------------------------------------------------+
|1|JoãodaSilva|1|umaselecao
|
|1|JoãodaSilva|1|ixi,naosei
|
|1|JoãodaSilva|1|alterardados
|
|1|JoãodaSilva|1|eskecerowhereealterartudo
|
|1|JoãodaSilva|1|apagarcoisas
|
|1|JoãodaSilva|1|tbnaopodeeskecerowhere
|
|1|JoãodaSilva|1|inserirdados
|
|2|FredericoJosé|2|buscardados
|
|2|FredericoJosé|2|selectcamposfromtabela
|
|2|FredericoJosé|2|alterarcoisas
|
|2|FredericoJosé|2|ixi,naosei
|
|3|AlbertoSantos|3|tempoprafazeralgo
|
|3|AlbertoSantos|3|1a4semanas
|
|3|AlbertoSantos|3|melhoriadoprocesso
|
|3|AlbertoSantos|3|tododia
|
12ENTENDENDOOLEFTJOIN
107
|3|AlbertoSantos|3|reuniaodestatus
|
|3|AlbertoSantos|3|tododia
|
|3|AlbertoSantos|3|oquadrobranco
|
|3|AlbertoSantos|3|ummetodoagil
|
|3|AlbertoSantos|3|temvariosoutros
|
|4|RenataAlonso|4|ehainternet
|
|4|RenataAlonso|4|browserfazrequisicao,servidormandaresposta
|
|4|RenataAlonso|4|ehoservidorquelidacomhttp
|
|4|RenataAlonso|4|naosei
|
|4|RenataAlonso|4|bancodedados!
|
|4|RenataAlonso|4|ehcolocaraappnainternet
|
|4|RenataAlonso|4|dependedatecnologia,masgeralmenteehlevarpraumservidorq
uetanainternet|
|5|PaulodaSilva|NULL|NULL
|
|6|CarlosCunha|NULL|NULL
|
|7|PauloJosé|NULL|NULL
|
|8|ManoelSantos|NULL|NULL
|
|9|RenataFerreira|NULL|NULL
|
|10|PaulaSoares|NULL|NULL
|
|11|JosedaSilva|NULL|NULL
|
|12|DaniloCunha|NULL|NULL
|
|13|ZilmiraJosé|NULL|NULL
|
|14|CristaldoSantos|NULL|NULL
|
|15|OsmirFerreira|NULL|NULL
|
|16|ClaudioSoares|NULL|NULL
|
+----+------------------+----------+----------------------------------------------------------------------------------+
Conseguimosretornartodososregistros.Entãoagoravamostentarcontarnovamentesasrespostas,
agrupandopelonome:
SELECTa.nome,COUNT(r.id)ASrespostas
FROMalunoa
LEFTJOINrespostarONr.aluno_id=a.id
GROUPBYa.nome;
+------------------+-----------+
|nome|respostas|
+------------------+-----------+
108
12ENTENDENDOOLEFTJOIN
|AlbertoSantos|9|
|CarlosCunha|0|
|ClaudioSoares|0|
|CristaldoSantos|0|
|DaniloCunha|0|
|FredericoJosé|4|
|JoãodaSilva|7|
|JosedaSilva|0|
|ManoelSantos|0|
|OsmirFerreira|0|
|PaulaSoares|0|
|PaulodaSilva|0|
|PauloJosé|0|
|RenataAlonso|7|
|RenataFerreira|0|
|ZilmiraJosé|0|
+------------------+-----------+
Agoraconseguimosretornartodososalunoseaquantidadederespostas,mesmoqueoalunonão
tenharespondidopelomenosumaresposta.
12.1RIGHTJOIN
Vamos supor que ao invés de retornar todos os alunos e suas respostas, mesmo que o aluno não
tenha nenhuma resposta, queremos faer o contrário, ou seja, retornar todos as respostas que foram
respondidas e as que não foram respondidas. Vamos verificar se existe alguma resposta que não foi
respondidaporumaluno:
SELECTr.idFROMrespostar
WHEREr.aluno_idISNULL;
Emptyset(0,00sec)
Nãoexisteexercíciosemresposta,entãovamosinserirumarespostasemassociaraumaluno:
INSERTINTOresposta(resposta_dada)VALUES('xvale15.');
QueryOK,1rowaffected(0,01sec)
Severificarmosnovamenteseexisteumarespostaquenãofoirespondidaporumaluno:
SELECTr.idFROMrespostar
WHEREr.aluno_idISNULL;
+----+
|id|
+----+
|28|
+----+
Agoraexisteumarespostaquenãofoiassociadaaumaluno.Damesmaformaqueutilizamosum
JOIN diferente para pegar todos os dados da tabela da esquerda (LEFT) mesmo que não tenha
associaçãocomatabelaqueestásendojuntada,existetambémoJOINquefaráoprocedimento,porém
paraatabeladadireita(RIGHT),queéotaldoRIGHTJOIN:
12.1RIGHTJOIN
109
SELECTa.nome,r.resposta_dada
FROMalunoa
RIGHTJOINrespostarONr.aluno_id=a.id;
+-----------------+----------------------------------------------------------------------------------+
|nome|resposta_dada
|
+-----------------+----------------------------------------------------------------------------------+
|JoãodaSilva|umaselecao
|
|JoãodaSilva|ixi,naosei
|
|JoãodaSilva|alterardados
|
|JoãodaSilva|eskecerowhereealterartudo
|
|JoãodaSilva|apagarcoisas
|
|JoãodaSilva|tbnaopodeeskecerowhere
|
|JoãodaSilva|inserirdados
|
|FredericoJosé|buscardados
|
|FredericoJosé|selectcamposfromtabela
|
|FredericoJosé|alterarcoisas
|
|FredericoJosé|ixi,naosei
|
|AlbertoSantos|tempoprafazeralgo
|
|AlbertoSantos|1a4semanas
|
|AlbertoSantos|melhoriadoprocesso
|
|AlbertoSantos|tododia
|
|AlbertoSantos|reuniaodestatus
|
|AlbertoSantos|tododia
|
|AlbertoSantos|oquadrobranco
|
|AlbertoSantos|ummetodoagil
|
|AlbertoSantos|temvariosoutros
|
|RenataAlonso|ehainternet
|
|RenataAlonso|browserfazrequisicao,servidormandaresposta
|
|RenataAlonso|ehoservidorquelidacomhttp
|
|RenataAlonso|naosei
|
|RenataAlonso|bancodedados!
|
|RenataAlonso|ehcolocaraappnainternet
|
|RenataAlonso|dependedatecnologia,masgeralmenteehlevarpraumservidorquetanainternet
110
12.1RIGHTJOIN
|
|NULL|xvale15
|
+-----------------+----------------------------------------------------------------------------------+
Observequefoiretornadaarespostaemquenãofoirespondidaporumaluno.
Quandoutilizamosapenaso JOINsignificaquequeremosretornartodososregistrosquetenham
umaassociação,ouseja,queexistatantonatabeladaesquerdaquantonatabeladadireita,esse JOIN
tambéméconhecidocomoINNERJOIN.VamosverificaroresultadoutilizandooINNERJOIN:
SELECTa.nome,COUNT(r.id)ASrespostas
FROMalunoa
INNERJOINrespostarONr.aluno_id=a.id
GROUPBYa.nome;
+-----------------+-----------+
|nome|respostas|
+-----------------+-----------+
|AlbertoSantos|9|
|FredericoJosé|4|
|JoãodaSilva|7|
|RenataAlonso|7|
+-----------------+-----------+
Ele trouxe apenas os alunos que possuem ao menos uma resposta, ou seja, que exista a associação
entreatabeladaesquerda(aluno)eatabeladadireita(resposta).
12.2JOINOUSUBQUERY?
Nocapítuloanteriortivemosquefazerumaquerypararetornartodososalunoseaquantidadede
matrículas, porém utilizamos subqueries para resolver o nosso problema. Podemos também, construir
essaqueryapenascomJOINs.Vamostentar:
SELECTa.nome,COUNT(m.id)ASqtd_matriculaFROMalunoa
JOINmatriculamONm.aluno_id=a.id
GROUPBYa.nome;
+-----------------+---------------+
|nome|qtd_matricula|
+-----------------+---------------+
|AlbertoSantos|2|
|FredericoJosé|3|
|JoãodaSilva|2|
|ManoelSantos|2|
|PaulaSoares|1|
|PauloJosé|1|
|RenataAlonso|2|
|RenataFerreira|1|
+-----------------+---------------+
Aparentemente não retornou os alunos que não possuem matrícula, porém utilizamos apenas o
JOIN,ouseja,o INNERJOIN.Aoinvésdo INNERJOINqueretornaapenasseexistiraassociação
entre as tabelas da esquerda (aluno) e a da direita(matricula), nós queremos retornar todos os
12.2JOINOUSUBQUERY?
111
alunos,mesmoquenãopossuammatrículas,ouseja,tabeladaesquerda.Entãovamostentaragoracom
oLEFTJOIN:
SELECTa.nome,COUNT(m.id)ASqtd_matriculaFROMalunoa
LEFTJOINmatriculamONm.aluno_id=a.id
GROUPBYa.nome;
+------------------+---------------+
|nome|qtd_matricula|
+------------------+---------------+
|AlbertoSantos|2|
|CarlosCunha|0|
|ClaudioSoares|0|
|CristaldoSantos|0|
|DaniloCunha|0|
|FredericoJosé|3|
|JoãodaSilva|2|
|JosedaSilva|0|
|ManoelSantos|2|
|OsmirFerreira|0|
|PaulaSoares|1|
|PaulodaSilva|0|
|PauloJosé|1|
|RenataAlonso|2|
|RenataFerreira|1|
|ZilmiraJosé|0|
+------------------+---------------+
Damesmaformaqueconseguimospegartodososalunoseaquantidadederespostasmesmoqueo
alunonãotenharespondidonenhumarespostautilizandoo LEFTJOINpoderíamostambémresolver
utilizandoumasubqueryparecidacomqualretornavatodososalunoseaquantidadedematrículas:
SELECTa.nome,
(SELECTCOUNT(r.id)FROMrespostarWHEREr.aluno_id=a.id)ASrespostas
FROMalunoa;
+------------------+-----------+
|nome|respostas|
+------------------+-----------+
|JoãodaSilva|7|
|FredericoJosé|4|
|AlbertoSantos|9|
|RenataAlonso|7|
|PaulodaSilva|0|
|CarlosCunha|0|
|PauloJosé|0|
|ManoelSantos|0|
|RenataFerreira|0|
|PaulaSoares|0|
|JosedaSilva|0|
|DaniloCunha|0|
|ZilmiraJosé|0|
|CristaldoSantos|0|
|OsmirFerreira|0|
|ClaudioSoares|0|
+------------------+-----------+
Oresultadoéomesmo!Seoresultadoéomesmo,quandoeudevoutilizaroJOINouassubqueries?
Aparentementeasubqueryémaisenxutaemaisfácildeserescrita,porémosSGBDssempreterãoum
112
12.2JOINOUSUBQUERY?
desempenho melhor para JOIN em relação a subqueries, então prefira o uso de JOINs ao invés de
subquery.
Vamos tentar juntar as queries que fizemos agora pouco, porém em uma única query. Veja o
exemploemumaplanilha.
Figura12.1:Planilhaexemplo
Primeirovamosfazercomsubqueries.Entãocomeçaremosretornandoonomedoaluno:
SELECTa.nomeFROMalunoa;
Agoravamoscontartodasasrespostasetestaroresultado:
SELECTa.nome,
(SELECTCOUNT(r.id)FROMrespostarWHEREr.aluno_id=a.id)ASqtd_respostas
FROMalunoa;
+------------------+---------------+
|nome|qtd_respostas|
+------------------+---------------+
|JoãodaSilva|7|
|FredericoJosé|4|
|AlbertoSantos|9|
|RenataAlonso|7|
|PaulodaSilva|0|
|CarlosCunha|0|
|PauloJosé|0|
|ManoelSantos|0|
|RenataFerreira|0|
|PaulaSoares|0|
|JosedaSilva|0|
|DaniloCunha|0|
|ZilmiraJosé|0|
|CristaldoSantos|0|
|OsmirFerreira|0|
|ClaudioSoares|0|
+------------------+---------------+
Porfim,vamoscontarasmatrículaseretornaracontagem:
SELECTa.nome,
(SELECTCOUNT(r.id)FROMrespostarWHEREr.aluno_id=a.id)ASqtd_respostas,
(SELECTCOUNT(m.id)FROMmatriculamWHEREm.aluno_id=a.id)ASqtd_matriculas
FROMalunoa;
+------------------+---------------+----------------+
|nome|qtd_respostas|qtd_matriculas|
+------------------+---------------+----------------+
|JoãodaSilva|7|2|
|FredericoJosé|4|3|
|AlbertoSantos|9|2|
|RenataAlonso|7|2|
12.2JOINOUSUBQUERY?
113
|PaulodaSilva|0|0|
|CarlosCunha|0|0|
|PauloJosé|0|1|
|ManoelSantos|0|2|
|RenataFerreira|0|1|
|PaulaSoares|0|1|
|JosedaSilva|0|0|
|DaniloCunha|0|0|
|ZilmiraJosé|0|0|
|CristaldoSantos|0|0|
|OsmirFerreira|0|0|
|ClaudioSoares|0|0|
+------------------+---------------+----------------+
Conseguimosoresultadoesperadoutilizandoassubqueries,vamostentarcomo LEFTJOIN? Da
mesmaformaquefizemosanteriormente,começaremosretornandoosalunos:
SELECTa.nomeFROMalunoa;
Agoravamosjuntarastabelaalunocomastabelasrespostaematricula:
SELECTa.nome,r.idASqtd_respostas,
m.idASqtd_matriculas
FROMalunoa
LEFTJOINrespostarONr.aluno_id=a.id
LEFTJOINmatriculamONm.aluno_id=a.id;
+------------------+---------------+----------------+
|nome|qtd_respostas|qtd_matriculas|
+------------------+---------------+----------------+
|JoãodaSilva|1|1|
|JoãodaSilva|2|1|
|JoãodaSilva|3|1|
|JoãodaSilva|4|1|
|JoãodaSilva|5|1|
|JoãodaSilva|6|1|
|JoãodaSilva|7|1|
|FredericoJosé|8|2|
|FredericoJosé|9|2|
|FredericoJosé|10|2|
|FredericoJosé|11|2|
|AlbertoSantos|12|3|
|AlbertoSantos|13|3|
|AlbertoSantos|14|3|
|AlbertoSantos|15|3|
|AlbertoSantos|16|3|
|AlbertoSantos|17|3|
|AlbertoSantos|18|3|
|AlbertoSantos|19|3|
|AlbertoSantos|20|3|
|RenataAlonso|21|4|
|RenataAlonso|22|4|
|RenataAlonso|23|4|
|RenataAlonso|24|4|
|RenataAlonso|25|4|
|RenataAlonso|26|4|
|RenataAlonso|27|4|
|RenataAlonso|21|9|
|RenataAlonso|22|9|
|RenataAlonso|23|9|
|RenataAlonso|24|9|
114
12.2JOINOUSUBQUERY?
|RenataAlonso|25|9|
|RenataAlonso|26|9|
|RenataAlonso|27|9|
|JoãodaSilva|1|11|
|JoãodaSilva|2|11|
|JoãodaSilva|3|11|
|JoãodaSilva|4|11|
|JoãodaSilva|5|11|
|JoãodaSilva|6|11|
|JoãodaSilva|7|11|
|FredericoJosé|8|12|
|FredericoJosé|9|12|
|FredericoJosé|10|12|
|FredericoJosé|11|12|
|AlbertoSantos|12|13|
|AlbertoSantos|13|13|
|AlbertoSantos|14|13|
|AlbertoSantos|15|13|
|AlbertoSantos|16|13|
|AlbertoSantos|17|13|
|AlbertoSantos|18|13|
|AlbertoSantos|19|13|
|AlbertoSantos|20|13|
|FredericoJosé|8|14|
|FredericoJosé|9|14|
|FredericoJosé|10|14|
|FredericoJosé|11|14|
|PauloJosé|NULL|5|
|ManoelSantos|NULL|6|
|RenataFerreira|NULL|7|
|PaulaSoares|NULL|8|
|ManoelSantos|NULL|10|
|PaulodaSilva|NULL|NULL|
|CarlosCunha|NULL|NULL|
|JosedaSilva|NULL|NULL|
|DaniloCunha|NULL|NULL|
|ZilmiraJosé|NULL|NULL|
|CristaldoSantos|NULL|NULL|
|OsmirFerreira|NULL|NULL|
|ClaudioSoares|NULL|NULL|
+------------------+---------------+----------------+
Antes de contarmos as colunas de qtd_resposas e qtd_matricula, vamos analisar um pouco esse
resultado. Note que o aluno João da Silva retornou 14 vezes, parece que tem alguma coisa estranha.
VamospegaroiddoJoãodaSilvaevamosverificarosregistrosdelenatabela respostaenatabela
matricula:
SELECTa.idFROMalunoaWHEREa.nome='JoãodaSilva';
+----+
|id|
+----+
|1|
+----+
Agoravamosverificartodososregistrosdelenatabelaresposta:
SELECTr.idFROMrespostar
WHEREr.aluno_id=1;
12.2JOINOUSUBQUERY?
115
+----+
|id|
+----+
|1|
|2|
|3|
|4|
|5|
|6|
|7|
+----+
Foramretornados7registros,agoravamosverificarnatabelamatricula:
SELECTm.idFROMmatriculam
WHEREm.aluno_id=1;
+----+
|id|
+----+
|1|
|11|
+----+
Foramretornados2registros.Seanalisarmosumpoucoessestrêsresultadoschegamosaosseguintes
números:
aluno=1respostas=7matrículas=2
Vamos executar novamente a nossa query que retorna o aluno e a contagem de respostas e
matrículas:
SELECTa.nome,r.idASqtd_respostas,
m.idASqtd_matriculas
FROMalunoa
LEFTJOINrespostarONr.aluno_id=a.id
LEFTJOINmatriculamONm.aluno_id=a.id;
+------------------+---------------+----------------+
|nome|qtd_respostas|qtd_matriculas|
+------------------+---------------+----------------+
|JoãodaSilva|1|1|
|JoãodaSilva|2|1|
|JoãodaSilva|3|1|
|JoãodaSilva|4|1|
|JoãodaSilva|5|1|
|JoãodaSilva|6|1|
|JoãodaSilva|7|1|
|FredericoJosé|8|2|
|FredericoJosé|9|2|
|FredericoJosé|10|2|
|FredericoJosé|11|2|
|AlbertoSantos|12|3|
|AlbertoSantos|13|3|
|AlbertoSantos|14|3|
|AlbertoSantos|15|3|
|AlbertoSantos|16|3|
|AlbertoSantos|17|3|
|AlbertoSantos|18|3|
|AlbertoSantos|19|3|
116
12.2JOINOUSUBQUERY?
|AlbertoSantos|20|3|
|RenataAlonso|21|4|
|RenataAlonso|22|4|
|RenataAlonso|23|4|
|RenataAlonso|24|4|
|RenataAlonso|25|4|
|RenataAlonso|26|4|
|RenataAlonso|27|4|
|RenataAlonso|21|9|
|RenataAlonso|22|9|
|RenataAlonso|23|9|
|RenataAlonso|24|9|
|RenataAlonso|25|9|
|RenataAlonso|26|9|
|RenataAlonso|27|9|
|JoãodaSilva|1|11|
|JoãodaSilva|2|11|
|JoãodaSilva|3|11|
|JoãodaSilva|4|11|
|JoãodaSilva|5|11|
|JoãodaSilva|6|11|
|JoãodaSilva|7|11|
|FredericoJosé|8|12|
|FredericoJosé|9|12|
|FredericoJosé|10|12|
|FredericoJosé|11|12|
|AlbertoSantos|12|13|
|AlbertoSantos|13|13|
|AlbertoSantos|14|13|
|AlbertoSantos|15|13|
|AlbertoSantos|16|13|
|AlbertoSantos|17|13|
|AlbertoSantos|18|13|
|AlbertoSantos|19|13|
|AlbertoSantos|20|13|
|FredericoJosé|8|14|
|FredericoJosé|9|14|
|FredericoJosé|10|14|
|FredericoJosé|11|14|
|PauloJosé|NULL|5|
|ManoelSantos|NULL|6|
|RenataFerreira|NULL|7|
|PaulaSoares|NULL|8|
|ManoelSantos|NULL|10|
|PaulodaSilva|NULL|NULL|
|CarlosCunha|NULL|NULL|
|JosedaSilva|NULL|NULL|
|DaniloCunha|NULL|NULL|
|ZilmiraJosé|NULL|NULL|
|CristaldoSantos|NULL|NULL|
|OsmirFerreira|NULL|NULL|
|ClaudioSoares|NULL|NULL|
+------------------+---------------+----------------+
Reparequeanossaqueryassociandoumaluno1,comaresposta1ematrícula1,omesmoaluno1,
comresposta1ematrícula11eassimsucessivamente...Issosignificaqueessaqueryestámultiplicandoo
aluno(1) x respostas(7) x matrículas(2)... Com certeza essa contagem não funcionará! Precisamos de
resultados distintos, ou seja, iremos utilizar o DISTINCT para evitar esse problema. Agora podemos
contarasrespostaseasmatrículas:
12.2JOINOUSUBQUERY?
117
SELECTa.nome,COUNT(DISTINCTr.id)ASqtd_respostas,
COUNT(DISTINCTm.id)ASqtd_matriculas
FROMalunoa
LEFTJOINrespostarONr.aluno_id=a.id
LEFTJOINmatriculamONm.aluno_id=a.id
GROUPBYa.nome;
+------------------+---------------+----------------+
|nome|qtd_respostas|qtd_matriculas|
+------------------+---------------+----------------+
|AlbertoSantos|9|2|
|CarlosCunha|0|0|
|ClaudioSoares|0|0|
|CristaldoSantos|0|0|
|DaniloCunha|0|0|
|FredericoJosé|4|3|
|JoãodaSilva|7|2|
|JosedaSilva|0|0|
|ManoelSantos|0|2|
|OsmirFerreira|0|0|
|PaulaSoares|0|1|
|PaulodaSilva|0|0|
|PauloJosé|0|1|
|RenataAlonso|7|2|
|RenataFerreira|0|1|
|ZilmiraJosé|0|0|
+------------------+---------------+----------------+
EsenãoadicionássemosainstruçãoDISTINCT?Oqueaconteceria?Vamostestar:
SELECTa.nome,COUNT(r.id)ASqtd_respostas,
COUNT(m.id)ASqtd_matriculas
FROMalunoa
LEFTJOINrespostarONr.aluno_id=a.id
LEFTJOINmatriculamONm.aluno_id=a.id
GROUPBYa.nome;
+------------------+---------------+----------------+
|nome|qtd_respostas|qtd_matriculas|
+------------------+---------------+----------------+
|AlbertoSantos|18|18|
|CarlosCunha|0|0|
|ClaudioSoares|0|0|
|CristaldoSantos|0|0|
|DaniloCunha|0|0|
|FredericoJosé|12|12|
|JoãodaSilva|14|14|
|JosedaSilva|0|0|
|ManoelSantos|0|2|
|OsmirFerreira|0|0|
|PaulaSoares|0|1|
|PaulodaSilva|0|0|
|PauloJosé|0|1|
|RenataAlonso|14|14|
|RenataFerreira|0|1|
|ZilmiraJosé|0|0|
+------------------+---------------+----------------+
Oresultadoalémdeserbemmaiorqueoesperado,repetenasduascolunas,poisestáacontecendo
aqueleproblemadamultiplicaçãodaslinhas!Percebaquesóconseguimosverificardeumaformarápida
oproblemaqueaconteceu,poisfizemosaquerypasso-a-passo,verificandocadaresultadoe,aomesmo
118
12.2JOINOUSUBQUERY?
tempo,corringoosproblemasquesurgiam.
12.3RESUMINDO
Nestecapítuloaprendemoscomoutilizarosdiferentestiposde JOINs,comoporexemploo LEFT
JOINqueretornaosregistrosdatabelaaesquerdaeo RIGHTJOINqueretornaosdadireitamesmo
quenãotenhamassociações.VimostambémqueoJOINtambéméconhecidocomoINNERJOINque
retorna apenas os registros que estão associados. Além disso, vimos que algumas queries podem ser
resolvidas utilizando subqueries ou LEFT/RIGHT JOIN, porém é importante lembrar que os SGBDs
sempre terão melhor desempenho com o os JOINs, por isso é recomendado que utilize os JOINs.
Vamosparaosexercícios?
EXERCÍCIOS
1. Exibatodososalunosesuaspossíveisrespostas.Exibatodososalunos,mesmoqueelesnãotenham
respondidonenhumapergunta.
2. ExibaagoratodososalunosesuaspossíveisrespostasparaoexercíciocomID=1.Exibatodosos
alunosmesmoqueelenãotenharespondidooexercício.
Lembre-sedeusaracondiçãonoJOIN.
1. QualadiferençaentreoJOINconvencional(muitasvezeschamadotambémdeINNERJOIN)para
oLEFTJOIN?
12.3RESUMINDO
119
CAPÍTULO13
MUITOSALUNOSEOLIMIT
Precisamosdeumrelatórioqueretornetodososalunos,algocomoselecionaronomedetodoseles,com
umSELECTsimples:
SELECTa.nomeFROMalunoa;
+------------------+
|nome|
+------------------+
|JoãodaSilva|
|FredericoJosé|
|AlbertoSantos|
|RenataAlonso|
|PaulodaSilva|
|CarlosCunha|
|PauloJosé|
|ManoelSantos|
|RenataFerreira|
|PaulaSoares|
|JosedaSilva|
|DaniloCunha|
|ZilmiraJosé|
|CristaldoSantos|
|OsmirFerreira|
|ClaudioSoares|
+------------------+
Paramelhoraroresultadopodemosordernaraqueryporordemalfabéticadonome:
SELECTa.nomeFROMalunoaORDERBYa.nome;
+------------------+
|nome|
+------------------+
|AlbertoSantos|
|CarlosCunha|
|ClaudioSoares|
|CristaldoSantos|
|DaniloCunha|
|FredericoJosé|
|JoãodaSilva|
|JosedaSilva|
|ManoelSantos|
|OsmirFerreira|
|PaulaSoares|
|PaulodaSilva|
|PauloJosé|
|RenataAlonso|
|RenataFerreira|
|ZilmiraJosé|
120
13MUITOSALUNOSEOLIMIT
+------------------+
Vamosverificaragoraquantosalunosestãocadastrados:
SELECTcount(*)FROMaluno;
+----------+
|count(*)|
+----------+
|16|
+----------+
Como podemos ver, é uma quantidade relativamente baixa, pois quando estamos trabalhando em
umaaplicaçãoreal,geralmenteovolumedeinformaçõesémuitomaior.
Nofacebook,porexemplo,quantosamigosvocêtem?Quandovocêentranofacebook,aparecetodas
as atualizações dos seus amigos de uma vez? Todas as milhares de atualizações de uma única vez?
Imagine a loucura que é trazer milhares de dados de uma única vez para você ver apenas 5, 10
notificações.Provavelmentevaiaparecendoaospoucos,certo?Entãoquetalmostrarmososalunosaos
poucostambém?Ouseja,fazermosumapaginaçãononossorelatório,algocomo5alunos"porpágina".
Mas como podemos fazer isso? No MySQL, podemos limitar em 5 a quantidade de registros que
desejamosretornar:
SELECTa.nomeFROMalunoa
ORDERBYa.nome
LIMIT5;
+------------------+
|nome|
+------------------+
|AlbertoSantos|
|CarlosCunha|
|ClaudioSoares|
|CristaldoSantos|
|DaniloCunha|
+------------------+
Nessecasoretornamososprimeiros5alunosemordemalfabética.Oatodelimitaréextremamente
importanteamedidaqueosdadoscrescem.Sevocêtemmilmensagensantigas,nãovaiquererveras
mildeumavezsó,tragasomenteas10primeirase,setiverinteresse,mais10,mais10etc.
13.1 LIMITANDO E BUSCANDO A PARTIR DE UMA QUANTIDADE
ESPECÍFICA
Agora vamos pegar os próximos 5 alunos, isto é, senhor MySQL, ignore os 5 primeiros, e depois
pegueparamimospróximos5:
SELECTa.nomeFROMalunoa
ORDERBYa.nome
LIMIT5,5;
+-----------------+
|nome|
13.1LIMITANDOEBUSCANDOAPARTIRDEUMAQUANTIDADEESPECÍFICA
121
+-----------------+
|FredericoJosé|
|JoãodaSilva|
|JosedaSilva|
|ManoelSantos|
|OsmirFerreira|
+-----------------+
LIMIT 5 ? LIMIT 5,5 ?
utilizamos
o
Parece um pouco estranho, o que será que isso significa? Quando
funciona
da
seguinte
maneira:
LIMIT
linha_inicial,qtd_de_linhas_para_avançar, ou seja, quando fizemos LIMIT 5, informamos ao
MySQLqueavançe5linhasapenas,poisporpadrãoeleiniciarápelaprimeiralinha,chamadadelinha
LIMIT
0.SefizéssemosLIMIT0,5,porexemplo:
SELECTa.nomeFROMalunoa
ORDERBYa.nome
LIMIT0,5;
+------------------+
|nome|
+------------------+
|AlbertoSantos|
|CarlosCunha|
|ClaudioSoares|
|CristaldoSantos|
|DaniloCunha|
+------------------+
PercebaqueoresultadoéomesmoqueLIMIT5!SepedimosLIMIT5,10oqueelenostrás?
SELECTa.nomeFROMalunoa
ORDERBYa.nome
LIMIT5,10;
+-----------------+
|nome|
+-----------------+
|FredericoJosé|
|JoãodaSilva|
|JosedaSilva|
|ManoelSantos|
|OsmirFerreira|
|PaulaSoares|
|PaulodaSilva|
|PauloJosé|
|RenataAlonso|
|RenataFerreira|
+-----------------+
O resultado iniciará após a linha 5, ou seja, linha 6 e avançará 10 linhas. Vamos demonstrar os
exemplostodosdeumaúnicavez:
Todososalunos:
SELECTa.nomeFROMalunoa;
+------------------+
|nome|
122
13.1LIMITANDOEBUSCANDOAPARTIRDEUMAQUANTIDADEESPECÍFICA
+------------------+
|AlbertoSantos|
|CarlosCunha|
|ClaudioSoares|
|CristaldoSantos|
|DaniloCunha|
|FredericoJosé|
|JoãodaSilva|
|JosedaSilva|
|ManoelSantos|
|OsmirFerreira|
|PaulaSoares|
|PaulodaSilva|
|PauloJosé|
|RenataAlonso|
|RenataFerreira|
|ZilmiraJosé|
+------------------+
Pegandoos5primeiros:
SELECTa.nomeFROMalunoa
ORDERBYa.nome
LIMIT5;
+------------------+
|nome|
+------------------+
|AlbertoSantos|
|CarlosCunha|
|ClaudioSoares|
|CristaldoSantos|
|DaniloCunha|
+------------------+
Ignorandoos5primeiros,pegandoospróximos5alunos:
SELECTa.nomeFROMalunoa
ORDERBYa.nome
LIMIT5,5;
+-----------------+
|nome|
+-----------------+
|FredericoJosé|
|JoãodaSilva|
|JosedaSilva|
|ManoelSantos|
|OsmirFerreira|
+-----------------+
13.2RESUMINDO
Nesse capítulo vimos como nem sempre retornar todos os registros das tabelas são necessários,
algumasvezes,precisamosfiltraraquantidadedelinhas,poisemumaaplicaçãoreal,podemoslidarcom
uma quantidade bem grande de dados. Justamente por esse caso, podemos limitar as nossas queries
utilizandoainstruçãoLIMIT.Vamosparaosexercícios?
13.2RESUMINDO
123
EXERCÍCIOS
1. Escrevaumaqueryquetragaapenasosdoisprimeirosalunosdatabela.
2. EscrevaumaSQLquedevolvaos3primeirosalunosqueoe-mailterminecomodomínio".com".
3. Devolvaos2primeirosalunosqueoe-mailterminecom".com",ordenandopornome.
4. DevolvatodososalunosquetenhamSilvaemalgumlugarnoseunome.
124
13.2RESUMINDO