Aprendendo a programar em SQL Descrição da aula De uma forma simples e fácil, você vai aprender o que são tabelas, linguagem de definição de dados, linguagem de manipulação de dados e os comandos mais básicos de SQL.
Introdução Nunca entendi entendi o porque das pessoas pessoas complicarem complicarem algo tão simples simples como a SQL, SQL, pois desde nossa infância nós aprendemos a criar, ler, atualizar e deletar informações de todo tipo, com várias regras e formatos. Porque não pegamos um exemplo de nossas brincadeiras mais divertidas de fazer fazer com os nossas nossas primas e primos sozinhos em um dia de chuva sem internet? Estou falando do STOP. Se você não sabe de qual brincadeira se trata, entre neste link: http://www.alienado.net/brincadeiras-de-stop-no-papel/ http://www.alienado.net/brincadeiras-de-stop-no-papel/.. De todas as brincadeiras que já joguei no papel, STOP é uma das minhas preferidas. Mas o que isto tem de haver com banco de dados e SQL? Calma que eu já chego lá.
Só vale nome de cidade do Brasil hein!? Como todo mundo sabe, antes de iniciar a brincadeira, nós escolhemos quais “colunas” nós vamos escrever durante o jogo (Nome, CEP, Fruta, Cor, Carro e a minha favorita MSÉ). Cada “coluna” tem suas regras como por exemplo a coluna “fruta” tinha que ser um nome de uma FRUTA, portanto, morango não é fruta e não podia ser escrito. Quando todas as colunas são colocadas no papel e as regras de cada uma é acertada entre todos nós iniciamos a brincadeira. Agora repare na Figura 1 e me diga: isto é ou não é uma tabela?
Figura 1 – Adedanha ou STOP – Fonte: http://minhananne.blogspot.com.br/2010/10/brincadeira-adedanha.html Então você pode ver que desde nossa infância nós criamos “colunas” de coisas e elaboramos regras para elas. A linguagem de modelagem de dados (DDL) faz exatamente isto. Cria e elabora as “colunas” onde serão estabelecidas regras para cada uma delas. Simples não? É lógico que as colunas em um banco de dados tem muito mais propriedades que nossa brincadeira de infância, porém não foge da mesma linha de pensamento: O que vamos escrever (Coluna “Fruta”) e o que será escrito (“Somente frutas”).
O que podemos aprender com isto? Agora que você já sabe o que é a DDL e para o que ela serve, vamos criar alguns diagramas para exemplificar. Se você ainda não sabe criar diagramas em banco de dados eu sugiro que leia o #5 Eu Respondo: Criando diagramas de banco de dados . Modelei a tabela da figura 1 utilizando o brModelo. Veja abaixo como ficou a geração dos esquemas:
Figura 2 – Esquemas conceitual, lógico e físico do jogo STOP No brModelo nós só precisamos nos preocupar com o esquema conceitual, pois os outros dois são gerados automaticamente. Agora vamos ver o que significa cada linha do comando SQL abaixo: CREATE TABLE stop
(
codigo_stop INTEGER letra CHAR , nome VARCHAR (25), carro VARCHAR (25), lugar VARCHAR (25),
PRIMARY KEY,
objeto VARCHAR (25), animal VARCHAR (25), fruta VARCHAR (25), cor VARCHAR (25), pts DOUBLE PRECISION )
1. A primeira linha “CREATE TABLE stop (” se refere à criação de uma tabela com o nome de “stop”. Note que nesta linha é aberto um parênteses que só é fechado no final do comando. 2. A segunda linha se refere à chave primária da tabela. É um identificador de cada linha da tabela, como se fosse um “C.P.F” de cada linha. Para entender o que é chave primária sugiro que leia o #5 Eu Respondo: Criando diagramas de banco de dados. 3. A terceira linha se refere à coluna (que em banco de dados nós chamamos também de campo) “letra”. A palavra “char” logo em seguida define o tipo do campo, ou seja, somente um carácter (uma letra). 4. A quarta linha se refere ao campo “carro” e seu tipo é varchar. O tipo varchar abria vários caracteres, que são delimitados por parênteses, ou seja, varchar(25) significa que este campo pode ter no máximo 25 caracteres; 5. Da quinta até a décima linha nós temos outras colunas com outros tipos de “categorias” do stop, todas com o mesmo tipo de dado “varchar(25)”. 6. Na décima primeira linha nós temos o campo “pts” que tem seu tipo “double precision”. Este tipo de dado abrange números decimais com ou sem vírgula (por exemplo 5 ou 5.1234). 7. Na última linha temos o parênteses fechando o comando da criação da tabela, em outras palavras, delimitando o comando CREATE TABLE. Quando se tem mais comando abaixo deste, usa-se também um ponto e vírgula após o parênteses. Se você entendeu os comando acima citados parabéns! Você já sabe o que é DDL e como utilizá-la. Agora vamos voltar à nossa brincadeira e ver se aprendemos mais alguma coisa dela.
Jogando o stop Depois de escolher a letra no dedo é claro, começa a brincadeira. Se a letra for C é tranquilo mas se for H fica bem difícil. Eu sempre escrevia primeiro o nome, depois a filme, CEP, carro, Objeto, MSÉ e o resto. Porém quando via que alguém escrevia o mesmo nome que o meu eu logo trocava para ganhar mais pontos. Se você escrevesse besteira perdia pontos então era bom deletar antes que alguém gritasse STOP. Depois era só rir das coisas que escreviam para ganhar mais pontos, porque na contagem final valia mais os nomes diferentes. Todo este negócio de escrever, ler, mudar e apagar as coisas têm um nome em banco de dados e este nome é CRUD que em inglês significa respectivamente:
1. Create (Criar). Em SQL significa “escreva” alguma coisa em alguma tabela. Mais especificadamente “insira dentro” da tabela “fruta” na coluna “nome” o valor “Mamão”, que traduzindo para a SQL é INSERT INTO fruta
(nome)
VALUES
("Mamão")
2. Uma linha criada é chamada de “novo registro” ou “nova tupla”. 3. Read (Ler). Em SQL significa “leia” alguma coisa em alguma tabela. Mais especificadamente “Selecione” a coluna “nome” da tabela “fruta”, que traduzindo para a SQL é SELECT fruta.nome FROM fruta
4. É a função mais utilizada do CRUD, que pode também ter a propriedade WHERE para especificar aonde nós queremos procurar na tabela. Por exemplo se tivéssemos uma tabela de nome “pessoa” com os campos “nome” e “sexo”. Para procurar os nomes das pessoas do sexo feminino nós usaríamos desta forma: SELECT pessoa.nome
FROM pessoa WHERE pessoa.sexo
= "feminino"
5. Update (Atualizar). Em SQL significa “mude” o valor da alguma coluna em alguma tabela. Mais especificadamente “Atualize” da tabela “fruta” na coluna “nome” com o valor “melancia” onde “nome” é igual a “Mamão”, que traduzindo para SQL é UPDATE fruta
SET nome="melancia" WHERE nome="Mamão"
6. Delete (Deletar). Em SQL significa “apague” da tabela “fruta” onde os valores “nome” são iguais a “Mamão”, que traduzindo para SQL é DELETE FROM fruta WHERE nome="Mamão"
Veja que não fazemos nada além do que “brincarmos” com as tabelas, criando, lendo, mudando e apagando linhas, assim como fazemos no STOP. Desta forma o “CRUD” nada mais é que princípios básicos de armazenamento de dados através da linguagem SQL. E adivinha o que o CRUD utiliza? A linguagem de modelagem de dados (DML), que nada mais é uma linguagem para modelar os dados, isto é, a DML é utilizada para criar, ler, atualizar e deletar dados. Observa-se desta maneira que a DML e DDL são conceitos tão simples que nós utilizamos até em uma brincadeira de adolescente. E a causa do seu sucesso é exatamente a sua simplicidade e facilidade no gerenciamento de bancos de dados. É claro que podemos fazer consultas mirabolantes, inserções de valores virtuosas, proezas inimagináveis na SQL mas em resumo ela é tão fáceis e simples como uma brincadeira.
Vamos ao exercício Um dos leitores do blog mandou um exercício que utiliza todos os conceitos acima. É um exemplo de banco de dados de uma escola, onde tem tabelas de alunos, cursos, disciplinas, professores etc. Na figura 3 nós temos o modelo conceitual do exercício.
Figura 3 – Modelo conceitual de banco de dados de uma escola Parece difícil de entender no início mas vamos dar uma olhada entidade por entidade (uma tabela no banco de dados é chamada de entidade no modelo entidaderelacionamento), relação por relação (relacionamentos das tabelas no banco são feitos através de chaves primárias e chaves estrangeiras) para vermos o que que há. 1. Por primeiro a entidade alunos. Esta entidade contém os dados básicos de um aluno. Se você comparar com a entidade professores você vai perceber alguns campos em comum (nome, email e senha). Não que repetir campos está errado, porém poderíamos fazer uma melhora neste diagrama. Porém como o exercício foi dado para ser feito desta maneira nós não vamos mudar. 2. Por segundo a relação faz uma. Repare na seta logo acima da relação e veja o que ela está dizendo que a entidade alunos faz uma matricula. É muito mais fácil entender o papel de cada entidade e relações se existem nomes simples e adequados para cada tipo de elemento, contando também com a direção da seta. 3. Por terceiro a entidade matriculas. Ela serve especialmente para cadastrar uma matrícula feita pelo aluno, por isto também temos nela o campo ativa e data_cadastro que nos ajudam a identificar quando o aluno fez a matrícula e se o aluno ainda está em uma situação regular. 4. Por quarto a relação inscreve em um. Note que pela direção da seta nós podemos entender que a entidade matricula inscreve em um curso, em outras palavras, que ao aluno fazer uma matrícula ele é inscrito em um curso.
5. Por quinto a entidade cursos. Note que nesta entidade somente há dois campos: o idcurso que nada mais é que uma identificação da linha da tabela e o campo nome que vai conter o nome do curso. Na vida real uma entidade pode variar o número de campos que em média são de 5 a 50 campos. Porém como este exemplo é didático nós temos poucos campos, somente o essencial para haver um mínimo de consistência da base da dados. Para entender mais sobre o conceito de consistência em banco de dados sugiro que leia a aula Banco de Dados – Conhecimento Básico. 6. Por sexto a entidade associativa disciplinas_cursos. Uma entidade associativa acontece quando duas entidades possuem uma relação de muitos para muitos, neste exemplo os cursos podem ter muitas disciplinas e uma disciplina pode estar em muitos cursos. Para entender melhor a entidade associativa sugiro que leia o #5 Eu Respondo: Criando diagramas de banco de dados . 7. Por sétimo a entidade disciplinas. Ela serve para registrar as disciplinas oferecidas na escola, porém esta escola só oferece um conjunto de disciplinas em um determinado curso. 8. Por oitavo a relação ministra. Observe que pelo sentido da seta a entidade professor ministra uma disciplina. O que parece óbvio pelo diagrama pode ficar muito complicado se for somente por linha de código, por isto é tão importante “desenhar” e planejar todo o banco de dados antes de efetivamente construí -lo. Após descritos todos os elementos do diagrama acima a figura 4 ilustra o que acontece quando é gerado o modelo lógico. Eu alterei um pouco o modelo para ficar mais compreensível, porém foram poucas alterações que não afetam um estudo dos arquivos da aula.
Figura 4 – Modelo lógico do banco de dados da escola Note que as relações sumiram dando lugar às chaves primárias (chaves em amarelo) e chaves estrangeiras (chaves em cinza). Neste modelo também podemos ver o tipo de dado que cada campo possui e as cardinalidades entre as tabelas. Este diagrama é o mais próximo da estrutura de uma tabela de um banco de dados relacional, porém ainda não é o código em si. No brModelo existe a opção de gerar o código físico, que nada mais é que a própria DDL entrando em ação para criar a tabela e definir suas colunas.
Depois de entender as linguagens e os diagramas que são exigidos no exercício no próximo tópico estão as questões dos exercícios e a resolução de cada uma delas.
Questões e resolução As questões do exercício estão divididas por tópicos sendo que o primeiro parágrafo é a resolução do exercício em seguida a explicação da resposta.
A Envie O Comando SQL Que Cria O Seguinte Modelo Dentro Do Banco De Dados, Junto Com Os Comandos Para Inserir, Atualizar, Excluir E Selecionar Usuários -- Primeira parte: criacao do modelo dentro do banco de dados CREATE TABLE alunos
( nome VARCHAR (75), email VARCHAR (75), senha VARCHAR (10), cpf VARCHAR (11), idpessoa INTEGER PRIMARY ) CREATE TABLE matriculas
KEY
(
idmatricula INTEGER PRIMARY KEY, ativa INTEGER , data_cadastro DATE, idpessoa INTEGER , idcurso INTEGER , FOREIGN KEY(idpessoa) REFERENCES alunos (idpessoa) ) CREATE TABLE cursos
( idcurso INTEGER PRIMARY nome VARCHAR (25) ) CREATE TABLE disciplinas
iddisciplina INTEGER nome VARCHAR (75), idprofessor INTEGER )
(
PRIMARY KEY,
CREATE TABLE professores
idprofessor INTEGER nome VARCHAR (75), email VARCHAR (75), senha VARCHAR (10) )
KEY,
(
PRIMARY KEY,
disciplinas_cursos ( iddisciplina INTEGER , idcurso INTEGER , iddisciplina_cursos INTEGER PRIMARY CREATE TABLE
KEY,
FOREIGN KEY(iddisciplina ) REFERENCES disciplinas FOREIGN KEY(idcurso) REFERENCES cursos
(iddisciplina ),
(idcurso)
) ALTER TABLE matriculas ADD FOREIGN KEY(idcurso) REFERENCES cursos
(idcurso) ALTER TABLE disciplinas ADD FOREIGN KEY(idprofessor ) REFERENCES
professores (idprofessor) -- Segunda parte: CRUD da tabela usuarios -- Inserir (Create) INSERT INTO alunos
(nome,email,senha,cpf) VALUES ("Jose","
[email protected]" ,"1234","93472312251" ) -- Atualizar (Update) UPDATE alunos
SET nome="Jose
Medeiros",email="
[email protected]" ,senha="4312",cpf="12578569845" WHERE nome="Jose"
-- Excluir (Delete) DELETE FROM alunos WHERE nome="Jose
Medeiros"
-- Selecionar usuários (Read) SELECT
*
FROM alunos
Explicação da questão A
A primeira parte da questão trata-se exclusivamente da linguagem de definição de dados (DDL). Através dela nós criamos e modelamos os dados dentro do banco de dados, transformando o código em uma tabela. A segunda parte abrange o CRUD que utiliza a linguagem de modelagem de dados (DML) para fazer as operações com os dados já citadas nesta aula. As próximas questões tratam somente da leitura dos dados utilizando consultas SQL, portanto estão incluídas na linguagem de manipulação de dados.
B. Enviar O Comando De Seleção Que Permita Visualizar As Disciplinas De Um Determinado Aluno. SELECT
d.nome FROM
disciplinas d INNER JOIN disciplinas_cursos dc ON (d.iddisciplina = dc.iddisciplina ) INNER JOIN cursos c ON (dc.idcurso = c.idcurso) INNER JOIN matriculas m ON (c.idcurso = m.idcurso) INNER JOIN alunos a ON (m.idpessoa = a.idpessoa) WHERE
a.nome = 'Jose'
Explicação da questão B
Observa-se que a tabela aluno teve que passar por várias outras tabelas até chegar na tabela disciplina. O tipo de “ligamento” utilizado foi o INNER JOIN pela sua
simplicidade. Para encontrar novas formas de “ligar” tabelas sugiro que leia a aula Banco de Dados – Conhecimento Básico.
C. Enviar O Comando De Seleção Que Permita Encontrar Os Professores De Um Determinado Aluno. SELECT
p.nome FROM
professores p INNER JOIN disciplinas d ON (p.idprofessor = d.iddisciplina ) INNER JOIN disciplinas_cursos dc ON (d.iddisciplina = dc.iddisciplina ) INNER JOIN cursos c ON (dc.idcurso = c.idcurso) INNER JOIN matriculas m ON (c.idcurso = m.idcurso) INNER JOIN alunos a ON (m.idpessoa = a.idpessoa) WHERE
a.nome = 'Jose'
Explicação da questão C
Observa-se que é praticamente a mesma coisa que a questão anterior porém com mais uma ligação da tabela professor. O INNER JOIN é ótimo pois liga as tabelas e as indexa direitinho, porém este método prejudica muito na performance, sendo recomendável utilizar outras formas de obter os dados. Porém como se trata de um exercício que visa uma maior clareza sacrificando a performance, não tem problema algum em usá-lo.
D. Enviar O Comando De Seleção Que Permita Selecionar Os Alunos De Um Determinado Professor. SELECT
a.nome FROM
alunos a INNER JOIN matriculas INNER INNER INNER INNER
m
(a.idpessoa = m.idpessoa) JOIN cursos c ON (m.idcurso = c.idcurso) JOIN disciplinas_cursos dc ON (c.idcurso = dc.idcurso) JOIN disciplinas d ON (dc.iddisciplina = d.iddisciplina ) JOIN professores p ON (d.idprofessor = p.idprofessor) ON
WHERE
p.nome = 'Jose'
Explicação da questão D
A resolução desta questão é praticamente a mesma coisa da resolução anterior, mudando apenas as posições das tabelas. Outra propriedade utilizada para a
simplicidade do código é os aliases (apelido) das tabelas. Isto significa que toda vez que eu colocar alguma letra em frente à algum nome de uma tabela, esta letra vai referenciar esta tabela. Por exemplo: “professores PR”. O aliase PR está referenciando a tabela professores.
