Nesse artigo você vai aprender como criar tabelas e realizar consultas em um banco de dados em SQL.
Nesse tutorial trabalharemos o seguinte:
- Script Criar Banco de Dados com SQL
- Alter Table – Alterar tabelas
- Drop Table – Excluir tabelas
- Insert Into – Inserir dados em uma tabela em SQL
- Select – Realizar consultas básicas em SQL
- Como trabalhar com datas em SQL
- Descomplicando SQL JOINS
Primeiramente vamos criar um banco de dados em MySQL e em seguida realizar consultas simples com JOIN SQL. Logo após trabalharemos com consultas um pouquinho mais complexas…
Para você que está começando, não deixe de fazer o todos as queries e praticar. Você também pode usar o script de banco de dados SQL pronto, e praticar apenas as consultas.
Contudo, como tudo em TI, com a prática que aprendemos de verdade ????
Vamos lá!
Criar banco de dados com SQL + Scripts
Aqui trabalharemos com um banco de dados sobre uma plantação. Dessa forma sua estrutura está representada no diagrama abaixo:
Então, com o diagrama fica fácil criar o banco de dados, não é mesmo?
Sugiro que você trabalhe somente em linha de comando, essa é a melhor maneira de realmente entender o que está fazendo. Embora ferramentas gráficas sejam ótimas, é fundamental que você tenha domínio do tema.
Enfim, os scripts de criação das tabelas que utilizei seguem 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!
Afinal como estamos trabalhando com chaves estrangeiras, é importante que as tabelas onde estão localizadas tenham sido criadas para que possa existir o relacionamento.
DROP TABLE – Excluir Tabelas SQL
Caso tenha criado uma tabela incorretamente, você pode exclui-la com o comando:
DROP TABLE nome_da_tabela
ALTER TABLE – Exemplos de Alteração de Tabela
Além disso, você pode ainda alterar as colunas das suas tabelas já criadas. Para adicionar novas colunas em uma tabela existente, considere o exemplo abaixo.
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, rg
Inserir dados em tabelas SQL
Para 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);
Contudo fique atento às chaves primárias das tabelas: Caso opte por inseri-las manualmente, elas devem ser ÚNICAS!
Consultas SQL – SELECT Exemplos
Primeiramente iniciaremos com uma query simples, excluindo funcionários 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 funcionarios WHERE idade < 18
Selecione todos os funcionários do banco.
SELECT * FROM funcionarios
Lembre-se que o * retorna todas as colunas da tabela em questão. Para retornar somente o nome dos funcionários, tente:
SELECT nome FROM funcionarios
Já que trabalharemos com mais de uma tabela e também com subqueries, aconselho a criar alias.
Alias, por sua vez, são como instâncias das tabelas: a tabela receberá “outro nome” em nossa query para que possamos chamá-la mais de uma vez, sem problemas. Para isso personalize sua query retornando todos os nomes que contenham ‘Silva‘.
SELECT tb_func.nome FROM funcionarios tb_func WHERE 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. Dessa maneira, para que possamos excluir algo aconselho sempre a trabalhar com chaves primárias, a fim de não excluir registros indesejados.
Então como devemos primeiramente identificar quais são os plantios de banana, você deverá criar 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 parênteses de IN estará o comando que criei anteriormente, onde estarão selecionados todos os ids de banana.
DELETE FROM plantio WHERE plantio.`plantioID` IN (SELECT planta.`ID` FROM planta WHERE planta.`nome` LIKE '%banana%' )
Consultas complexas com SQL
Em seguida, trabalharemos com uma query um pouco maior: devemos retornar todos os funcionários que trabalharam com milho, seja na colheita ou plantio, em 2010.
Assim sendo, 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: – SQL Consultas com Join.
Bonus: Descomplicando SQL Joins
Como trabalhar com datas em SQL
Trabalhar com datas em SQL requer que as mesmas estejam dentro de aspas (“”) e no formato “YYYY-MM-DD”.
Desse modo 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 t1 INNER 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')) )
Em seguida, utilizando como base o comando acima, retorne todos os funcionários e suas idades, que trabalharam com mamão em 2008.
O Comando NOT IN
Depois disso, selecione agora todos os funcionários que não trabalharam com mandioca em 2008. Logo você deve utilizar o comando NOT IN.
SELECT tbfunc.nome FROM funcionario tbfunc WHERE tbfunc.`funcid` NOT IN( SELECT t1.`funcid` FROM funcionario t1 INNER 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')) ) ) )
O Comando SUM
Em seguida informe o peso total de tomate que foi colhido em 2010. Para isso você deve utilizar 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 colhido WHERE ((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'))
Comando AS SQL – Exemplos
Por fim, escreva 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.
Desse modo utilizando AS podemos renomear os nomes das colunas do resultado da query.
Lembre-se: essa alteração é visível somente no resultado da query, e não influencia no resultado ou estrutura das tabelas.
Finalmente, 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 planta
LEFT JOIN colhido ON ID
LEFT JOIN plantio ON ID
GROUP 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! ????????????
