Resumo rápido – Consulta e criação de tabelas em SQL
Esse post irá abranger praticamente toda a parte prática de um curso de Banco de Dados I ministrado em um curso de Sistemas de Informação. Trabalharemos criando um banco de dados em MySQL e logo após trabalhando com consultas simples e outras um pouquinho mais complexas. Para você que está começando, não deixe de fazer todas as queries e praticar. Como tudo em TI, com a prática que aprendemos de verdade 
Mão a obra!
Trabalharemos com um banco de dados sobre uma plantação. Sua estrutura está representada nao diagrama abaixo:
Com o digrama fica fácil criar o BD, não é mesmo? Sugiro que você trabalhe somente em linha de comando, essa é a melhor maneira de realmente entender o que está fazendo. Ferramentas gráficas são ótimas, mas é fundamental que você tenha domínio do tema. Os scripts de criação das tabelas que utilizei estão abaixo.
CREATE TABLE `canteiro` (`canteiroid` INTEGER(11) NOT NULL AUTO_INCREMENT,`nome` CHAR(20) COLLATE DEFAULT NULL,`luzdiaria` INTEGER(2) DEFAULT NULL,`agua` INTEGER(3) DEFAULT NULL,PRIMARY KEY (`canteiroid`),UNIQUE KEY `canteiroid` (`canteiroid`))CREATE TABLE `funcionario` (`funcid` INTEGER(11) NOT NULL AUTO_INCREMENT,`nome` CHAR(80) COLLATE NOT NULL DEFAULT '',`idade` INTEGER(2) UNSIGNED DEFAULT NULL,PRIMARY KEY (`funcid`),UNIQUE KEY `funcid` (`funcid`))CREATE TABLE `planta` (`ID` INTEGER(11) NOT NULL AUTO_INCREMENT,`nome` CHAR(20) COLLATE NOT NULL DEFAULT '',`luzdiaria` INTEGER(2) DEFAULT NULL,`agura` INTEGER(2) DEFAULT NULL,`peso` INTEGER(2) DEFAULT NULL,PRIMARY KEY (`ID`),UNIQUE KEY `ID` (`ID`))CREATE TABLE `plantio` (`plantioID` INTEGER(11) NOT NULL AUTO_INCREMENT,`plantaID` INTEGER(11) NOT NULL,`funcID` INTEGER(11) NOT NULL ,`canteiroID` INTEGER(11) NOT NULL,`Data` DATE DEFAULT NULL,`sementes` INTEGER(4) DEFAULT 0 NOT NULL,PRIMARY KEY (`plantioID`),FOREIGN KEY(`plantaID`) REFERENCES planta(ID),FOREIGN KEY(`funcID`) REFERENCES funcionario(funcID),FOREIGN KEY(`plantaID`) REFERENCES canteiro(canteiroID),UNIQUE KEY `ID` (`plantioID`))CREATE TABLE `colhido` (`colhidoID` INTEGER(11) NOT NULL AUTO_INCREMENT,`plantaID` INTEGER(11) NOT NULL,`funcID` INTEGER(11) NOT NULL ,`canteiroID` INTEGER(11) NOT NULL,`Data` DATE DEFAULT NULL,`quantidade` INTEGER(4) DEFAULT 0 NOT NULL,`peso` DOUBLE(4,3) DEFAULT 0 NOT NULL,PRIMARY KEY (`colhidoID`),FOREIGN KEY(`plantaID`) REFERENCES planta(ID),FOREIGN KEY(`funcID`) REFERENCES funcionario(funcID),FOREIGN KEY(`plantaID`) REFERENCES canteiro(canteiroID),UNIQUE KEY `ID` (`colhidoID`))Lembre-se que ao utilizar esse script a ordem é muito importante! Como estamos trabalhando com chaves estrangeiras, é importante que as tabelas aonde estão localizadas tenham sido criadas para que possa existir o relacionamento.
Caso tenha criado uma tabela incorretamente, você pode exclui-la com o comando:
DROP TABLE nome_da_tabelaVocê pode ainda alterar as colunas das suas tabelas já criadas. Para adicionar novas colunas em uma tabela existente
ALTER TABLE funcionario ADD cpf varchar(20),rg varchar(15)Alterando um campo já existente na tabela
ALTER TABLE funcionario ALTER COLUMN cpf varchar(14)Removendo colunas em uma tabela:
ALTER TABLE funcionario DROP COLUMN cpf, rgPara que possamos testar os comandos que serão executados, vamos inserir alguns dados em nossas tabelas. Como exemplos, vamos inserir uma planta na tabela planta:
INSERT INTO planta (nome, luzdiaria, agua,peso )VALUES ('abacaxi', 3.0, 5.25, 1.5)Adicione outros campos para que possamos testar as queries que executaremos a seguir. Seguem algumas sugestões:
INSERT INTO `planta` (`nome`,`luzdiaria`,`agua`,`peso`)VALUES ('tomate',0.03,0.03,0.03);INSERT INTO `funcionario`(`funcid`,`nome`,`idade`)VALUES (25,'Gabriella Fonseca Ribeiro',20);INSERT INTO`plantio`(`plantioID`,`plantaID`,`funcID`,`canteiroID`,`Data`,`sementes`)VALUES (15,1,25,4,2011-06-07,8);Fique atento às chaves primárias das tabelas – Caso opte por inseri-las manualmente, elas devem ser ÚNICAS!
Primeiramente iniciaremos com uma query simples, excluindo funcionarios com menos de 18 anos. Para isso informe o nome da tabela e na cláusula WHERE personalize o campo a ser observado na exclusão:
DELETE FROM funcionariosWHERE idade< 18Selecione todos os funcionarios do banco.
SELECT * FROM funcionariosLembre-se que o * retorna todas as colunas da tabela em questão. Para retornar somente o nome dos funcionários, tente:
SELECT nome FROM funcionariosComo trabalharemos com mais de uma tabela e também com subqueries, aconselho a criar alias, que são como instâncias das tabelas – a tabela receberá “outro nome” em nossa query para que possamos chama-la mais de uma vez, sem problemas. Personalize sua query retornando todos os nomes que contenham ‘Silva’.
SELECT tb_func.nomeFROM funcionarios tb_funcWHERE tb_func.nome LIKE '%Silva%'Começaremos agora a trabalhar com subqueries. Devemos identificar todos os plantios de banana e exclui-los de nossa base de dados. Para que possamos excluir algo aconselho sempre a trabalhar com chaves primárias, a fim de não excluir registros indesejados. Como devemos primeiramente identificar quais são os plantios de banana, crie uma query para isso. Na query a seguir retornarei todos os ids em que existam plantas com o nome ‘banana’.
SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%banana%'Após identificar quais são os ids que eu quero excluir, utilizando o comando IN para que eu selecione “todos de uma vez”, eu monto minha query de exclusão. Dentro dos parenteses de IN estará o comando que criei anteriormente, aonde estarão selecionados todos os ids de banana.
DELETE FROM plantio WHEREplantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%banana%' )Agora trabalheremos com uma query um pouco maior: devemos retornar todos os funcionários que trabalharam com milho, seja na colheita ou plantio, em 2010. Utilizando o INNER JOIN retornarei todos os relacionamentos entre funcionários e plantios/colhido caso existem funcionários SOMENTE. Se ainda tem dúvidas sobre comandos com Join, consulte esse post.
Trabalhar com datas em SQL requer que as mesmas estejam dentro de “” e no formato “YYYY-MM-DD”. Caso você esteja precisando de dados de um ano qualquer não se esqueça que terá de selecionar, na cláusula WHERE, as datas entre o último dia do ano anterior e o último dia do ano que está trabalhando.
SELECT t1.`nome`FROM funcionario t1INNER JOIN plantio ON plantio.`funcID` = t1.`funcid`INNER JOIN colhido ON colhido.`funcID` = t1.`funcid`WHERE((plantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%milho%' ))OR(colhido.`colhidoID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%milho%' )))AND(((plantio.`Data` > '2009-12-31') AND (plantio.`Data` < '2011-01-01'))OR((colhido.`Data` > '2009-12-31') AND (colhido.`Data` < '2011-01-01')))Utilizando como base o comando acima, retorne todos os funcionários e suas idades, que trabalharam com mamão em 2008.
Selecione agora todos os funcionários que não trabalharam com mandioca em 2008. Utilize o comando NOT IN.
SELECT tbfunc.nomeFROM funcionario tbfuncWHERE tbfunc.`funcid` NOT IN(SELECT t1.`funcid`FROM funcionario t1INNER JOIN plantio ON plantio.`funcID` = t1.`funcid`INNER JOIN colhido ON colhido.`funcID` = t1.`funcid`WHERE((plantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%mandioca%' ))OR(colhido.`colhidoID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%mandioca%' )))AND(((plantio.`Data` > '2007-12-31') AND (plantio.`Data` < '2009-01-01'))OR((colhido.`Data` > '2007-12-31') AND (colhido.`Data` < '2009-01-01')))))Informe o peso total de tomate que foi colhido em 2010. Utilize o comando SUM para somar os valores de uma coluna – lembre-se que esse comando aplica-se somente à campos numéricos!
SELECT SUM(colhido.`peso`) AS 'Peso Total'FROM colhidoWHERE((colhido.`colhidoID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%tomate%' )))AND((colhido.`Data` > '2009-12-31') AND (colhido.`Data` < '2011-01-01'))Por fim, para encerrar, screva uma instrução SQL que informe para cada tipo de hortaliças/legumes quantos quilos de produto se pode colher a partir de cada quilograma de semente. Utilizando AS nos podemos renomear os nomes das colunas do resultado da query – essa alteração é visível somente no resultado da query, e não influencia no resultado ou estrutura das tabelas. Em relação à conta, estou aplicando uma média comum, dividindo a soma total de Kgs colhidos ao longo dos anos, pelas sementes plantadas.
SELECT planta.`nome` as 'Planta', (SUM(colhido.`peso`)) as 'Total colhido em KG',(SUM(plantio.`sementes`)) AS 'Total de Sementes Plantadas',('Total colhido em KG'/'Total de Sementes Plantadas') AS 'Estimativa de KG/semente'FROM plantaLEFT JOIN colhido ON IDLEFT JOIN plantio ON IDGROUP BY planta.`ID`ORDER BY `planta`.`nome`Bom, é isso! Caso tenha alguma dúvida, deixem comentários nesse post. Responderei o mais rápido possível. Espero que tenha gostado!
Com informações de Eu Faço Programas.
Gabriella Fonseca é desenvolvedora web com boa experiência em empresas como CPM Braxis Capgemini e VELP Tecnologia. É ainda universitária, cursando Sistemas de Informação na PUC-MG. Desenvolvimento é sua paixão e teve certeza disso depois do convívio com os colegas de estágio enquanto fazia o curso de Matemática Computacional na UFMG. Apesar de relativamente curta, sua carreira é marcada pelo reconhecimento do que tenta fazer de melhor. Em 2010 recebeu a premiação de Melhor Projeto Asp.Net pelo programa Microsoft Students to Bussiness. Como sempre foi apaixonada por Matemática, durante o ensino médio recebi Menções Honrosas na OBMEP 2005, OBM 2005, OBMEP 2006, OBMEP 2008 e medalha de bronze na OBMEP 2007. Tem um bom conhecimento de desenvolvimento Web - especialmente .Net, mas é capaz de desenvolver aplicações simples para Android ou fazer correções e adicionar funcionalidades em um sistema PHP. Foca em desenvolvimento web, pesquisa e otimização de sites, marketing digital, redes sociais e comunicação interativa. Bloga em www.eufacoprogramas.wordpress.com e dá pitacos em www.techlivre.com.br
0 comments:
Post a Comment