Analisamos bancos de dados e a linguagem SQL. (Parte 5 - conexões e junções) - "Projeto Java de A a Z"

Um artigo de uma série sobre a criação de um projeto Java (links para outros materiais estão no final). Seu objetivo é analisar tecnologias-chave, o resultado é escrever um bot de telegrama.

• Tipos de relacionamentos no banco de dados
  • Um para muitos
  • Um para um (um para um)
  • Muitos para muitos
• Conexões (junções)
  • JUNÇÃO INTERNA
  • ASSOCIAÇÃO À ESQUERDA
  • JUNTE-SE À DIREITA
  • Protegendo junções
• Trabalho de casa

Olá a todos, futuros Seniores e Senioritas de software. Como falei na parte anterior ( conferindo o dever de casa ), hoje terá material novo. Para aqueles que estão especialmente ansiosos, desenterrei um trabalho de casa interessante para que aqueles que já sabem tudo e aqueles que não sabem, mas querem pesquisar no Google, possam praticar e testar suas habilidades. Hoje falaremos sobre tipos de conexões e junções.

Tipos de relacionamentos no banco de dados

Para entender o que são relacionamentos, você precisa lembrar o que é uma chave estrangeira. Para quem esqueceu, seja bem vindo ao início da série .

Um para muitos

Vamos relembrar nosso exemplo com países e cidades. É claro que uma cidade deve ter um país. Como vincular um país a uma cidade? É necessário anexar a cada cidade um identificador único (ID) do país a que pertence: já fizemos isso. Isso é chamado de um dos tipos de conexões - um para muitos (também seria bom conhecer a versão em inglês - um para muitos). Parafraseando, podemos dizer: várias cidades podem pertencer a um país. É assim que você deve se lembrar: um relacionamento um-para-muitos. Até agora está claro, certo? Caso contrário, aqui está a primeira foto da Internet: mostra que existem clientes e seus pedidos. Faz sentido que um cliente possa ter mais de um pedido. Existe um para muitos :) Ou outro exemplo: Existem três tabelas: editora, autor e livro. Toda editora que não quer falir e quer ter sucesso tem mais de um autor, não concorda? Por sua vez, cada autor pode ter mais de um livro – disso também não há dúvida. E isto significa, mais uma vez, a ligação de um autor a muitos livros, de uma editora a muitos autores . Existem muitos outros exemplos que podem ser dados. A dificuldade de percepção a princípio pode residir apenas em aprender a pensar abstratamente: a olhar de fora para as mesas e sua interação.

Um para um (um para um)

Pode-se dizer que este é um caso especial de comunicação um-para-muitos. Situação em que um registro de uma tabela está relacionado a apenas um registro de outra tabela. Que exemplos pode haver da vida? Se excluirmos a poligamia, podemos dizer que existe um relacionamento individual entre marido e mulher. Embora mesmo que digamos que a poligamia é permitida, cada esposa ainda pode ter apenas um marido. O mesmo pode ser dito sobre os pais. Cada pessoa pode ter apenas um pai biológico e apenas uma mãe biológica. Relacionamento um-para-um explícito. Enquanto eu escrevia isso, um pensamento me ocorreu: por que então dividir um relacionamento um-para-um em dois registros em tabelas diferentes, se eles já possuem um relacionamento um-para-um? Eu mesmo descobri a resposta. Esses registros também podem estar vinculados a outros registros de outras maneiras. Do que estou falando? Outro exemplo de conexões um-a-um é entre o país e o presidente. É possível anotar todos os dados do presidente na tabela “país”? Sim, você pode, o SQL não dirá uma palavra. Mas se você pensar que o presidente também é uma pessoa... E ele também pode ter uma esposa (outro relacionamento um-para-um) e filhos (outro relacionamento um-para-muitos) e então acontece que será necessário conectar o país com a esposa e os filhos do presidente…. Parece loucura, certo? :D Pode haver muitos outros exemplos para esta conexão. Além disso, em tal situação, você pode adicionar uma chave estrangeira a ambas as tabelas, ao contrário de um relacionamento um-para-muitos.

Muitos para muitos

Já pelo nome você pode adivinhar do que falaremos. Muitas vezes na vida, e nós programamos nossas vidas, há situações em que os tipos de conexões acima não são suficientes para descrever as coisas que precisamos. Já falamos sobre editoras, livros e autores. Há tantas conexões aqui... Cada publicação pode ter vários autores – uma conexão um-para-muitos. Ao mesmo tempo, cada autor pode ter várias editoras (por que não, o escritor publicou em um lugar, brigou por dinheiro, foi para outra editora, por exemplo). E este é novamente um relacionamento um-para-muitos. Ou isto: cada autor pode ter vários livros, mas cada livro também pode ter vários autores. Novamente, uma relação um-para-muitos entre autor e livro, livro e autor. Deste exemplo podemos tirar uma conclusão mais formalizada: Ficou claro como definir os tipos de conexão anteriores no SQL: basta passar o ID daquela para aqueles registros, que são muitos, certo? Um país fornece seu ID como chave estrangeira para muitas cidades. O que fazer com relacionamentos muitos-para-muitos ? Este método não é adequado. Precisamos adicionar outra tabela que conecte as duas tabelas. Por exemplo, vamos ao MySQL, criar um novo banco de dados muitos para muitos, criar duas tabelas, autor e livro, que conterão apenas nomes e seus IDs: CREATE DATABASE muitos para muitos; USE muitos para muitos; CREATE TABLE autor (id INT AUTO_INCREMENT, nome VARCHAR (100), CHAVE PRIMÁRIA (id)); CREATE TABLE livro (id INT AUTO_INCREMENT, nome VARCHAR (100), CHAVE PRIMÁRIA (id)); Agora vamos criar uma terceira tabela que terá duas chaves estrangeiras das nossas tabelas autor e livro, e esse link será único. Ou seja, não será possível adicionar duas vezes um registro com as mesmas chaves: CREATE TABLE autores_x_books ( book_id INT NOT NULL, author_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) REFERENCES autor (id), UNIQUE (livro_id, autor_id)); Aqui usamos vários novos recursos que precisam ser comentados separadamente:

• NOT NULL significa que o campo deve ser sempre preenchido e, se não o fizermos, o SQL nos informará;
• UNIQUE diz que um campo ou vários campos devem ser únicos na tabela. Muitas vezes acontece que além do identificador exclusivo, mais um campo deve ser exclusivo para cada registro. E a UNIQUE é responsável exatamente por esse assunto.

Da minha prática: ao passar de um sistema antigo para um novo, nós, como desenvolvedores, devemos armazenar os IDs do sistema antigo para trabalhar com ele e criar o nosso próprio. Por que criar o seu próprio e não usar os antigos? Eles podem não ser suficientemente exclusivos ou esta abordagem para a criação de IDs pode não ser mais relevante e limitada. Para isso, tornamos o antigo nome de ID também único na tabela. Para verificar isso, você precisa adicionar dados. Adicione um livro e autor: NSERT INTO livro (nome) VALUES ("livro1"); INSERT INTO autor (nome) VALUES ("autor1"); Já sabemos por artigos anteriores que eles terão os IDs 1 e 1. Portanto, podemos adicionar imediatamente um registro à terceira tabela: INSERT INTOauthors_x_books VALUES (1,1); E tudo ficará bem até que queiramos repetir o último comando novamente: ou seja, anotar novamente os mesmos IDs: O resultado será natural - um erro. Haverá uma duplicata. A entrada não será gravada. É assim que uma conexão muitos-para-muitos será criada... Tudo isso é muito legal e interessante, mas surge uma questão lógica: como conseguir essa informação? Como combinar dados de tabelas diferentes e obter uma resposta? É sobre isso que falaremos na próxima parte))

Conexões (junções)

Na parte anterior, preparei você para entender imediatamente o que são joins e onde utilizá-los. Porque estou profundamente convencido de que assim que a compreensão chegar, tudo se tornará imediatamente muito simples, e todos os artigos sobre junções serão claros como os olhos de um bebê :D Grosso modo e em geral, as junções são obtidas como resultado de várias tabelas por meio de um JOIN (join do inglês join). E isso é tudo...) E para ingressar, você precisa especificar o campo pelo qual as tabelas serão unidas. O diabo não é tão assustador quanto é pintado, certo?) A seguir, falaremos apenas sobre quais tipos de junções existem e como usá-las. Existem muitos tipos de junções e não consideraremos todos eles. Somente aqueles que realmente precisamos. É por isso que não estamos interessados ​​em junções exóticas como Cross e Natural. Esqueci completamente, precisamos lembrar mais uma nuance: tabelas e campos podem ter aliases - pseudônimos. Eles são convenientemente usados ​​para junções. Por exemplo, você pode fazer isto: SELECT * FROM table1; se a consulta usar frequentemente a tabela1, você poderá fornecer um alias: SELECT* FROM table1 as t1; ou ainda mais fácil de escrever: SELECT * FROM table1 t1; e posteriormente na consulta será possível usar t1 como alias para esta tabela.

JUNÇÃO INTERNA

A junção mais comum e simples. Diz que quando tivermos duas tabelas e um campo pelo qual possa ser unido, serão selecionados todos os registros cujos relacionamentos existam nas duas tabelas. Foi difícil dizer de alguma forma. Vejamos um exemplo: Vamos adicionar um registro ao nosso banco de dados de cidades. Uma entrada para cidades e outra para países: $ INSERT INTO country VALUES(5, "Uzbequistão", 34036800); e $ INSERT INTO cidade (nome, população) VALUES("Tbilisi", 1171100); Adicionamos um país que não possui uma cidade em nossa tabela e uma cidade que não está associada a um país em nossa tabela. Portanto, INNER JOIN está empenhado em emitir todos os registros para as conexões que estão em duas tabelas. Esta é a aparência da sintaxe geral quando queremos unir duas tabelas tabela1 e tabela2: SELECT * FROM tabela1 t1 INNER JOIN tabela2 ON t1.id = t2.t1_id; e então todos os registros que possuem relacionamento nas duas tabelas serão retornados. Para o nosso caso, quando quisermos receber informações de países junto com cidades, ficará assim: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Aqui, embora os nomes sejam iguais, você pode ver claramente que os campos das cidades vêm primeiro, depois os campos dos países. Mas as duas entradas que adicionamos acima não estão lá. Porque é exatamente assim que o INNER JOIN funciona.

ASSOCIAÇÃO À ESQUERDA

Há casos, e muitas vezes, em que não ficamos satisfeitos com a perda de campos da tabela principal pelo fato de não haver registro para isso na tabela adjacente. É para isso que serve um LEFT JOIN. Se em nossa solicitação anterior especificarmos LEFT em vez de INNER, adicionaremos outra cidade na resposta - Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Há uma nova entrada sobre Tbilisi e tudo o que se relaciona com o país está em null . Muitas vezes é assim que é usado.

JUNTE-SE À DIREITA

Aqui haverá uma diferença de LEFT JOIN porque todos os campos serão selecionados não à esquerda, mas à direita na conexão. Ou seja, não serão consideradas cidades, mas todos os países: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Agora está claro que neste caso não haverá Tbilisi, mas teremos o Uzbequistão. Algo parecido…))

Protegendo junções

Agora quero mostrar uma imagem típica que os juniores empinam antes de uma entrevista para convencê-los de que entendem a essência das junções: Aqui tudo é mostrado na forma de conjuntos, cada círculo é uma mesa. E os locais onde está pintado são as partes que serão mostradas no SELECT. Vamos olhar:

• INNER JOIN é apenas a interseção de conjuntos, ou seja, aqueles registros que possuem conexões com duas tabelas – A e B;
• LEFT JOIN são todos os registros da tabela A, incluindo todos os registros da tabela B que possuem uma interseção (conexão) com A;
• RIGHT JOIN é exatamente o oposto de LEFT JOIN - todos os registros da tabela B e registros de A que possuem um relacionamento.

Depois de tudo isso, esta imagem deve ficar clara))

Trabalho de casa

Desta vez as tarefas serão muito interessantes e todos aqueles que as resolverem com sucesso podem ter a certeza de que estão prontos para começar a trabalhar na parte SQL! As tarefas não são complicadas e foram escritas para alunos do ensino médio, então não será fácil e chato para você :) Darei a você uma semana para fazer as tarefas sozinho e depois publicarei um artigo separado com uma análise detalhada da solução para as tarefas que lhe dei.

A tarefa real:

1. Escreva um script SQL para criar a tabela 'Aluno' com os seguintes campos: id (chave primária), nome, sobrenome, e_mail (único).
2. Escreva um script SQL para criar a tabela 'Livro' com os seguintes campos: id, título (id + título = chave primária). Vincule 'Aluno' e 'Livro' com um relacionamento 'Livro' um-para-muitos de 'Aluno'.
3. Escreva um script SQL para criar a tabela 'Professor' com os seguintes campos: id (chave primária), nome, sobrenome, e-mail (único), assunto.
4. Vincule 'Aluno' e 'Professor' com um relacionamento 'Aluno' muitos para muitos Professor'.
5. Selecione 'Aluno' que tenha 'oro' no sobrenome, por exemplo 'Sid oro v', 'V oro novsky'.
6. Selecione na tabela 'Aluno' todos os sobrenomes ('sobrenome') e o número de suas repetições. Considere que existem homônimos no banco de dados. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

   sobrenome	quantidade
   Petrov	15
   Ivanov	12
   Sidorov	3

7. Selecione os 3 nomes mais repetidos em 'Aluno'. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

   nome	quantidade
   Alexandre	27
   Sergei	10
   Peter	7

8. Selecione 'Alunos' que possuem o maior número de 'Livro' e 'Professor' associado. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

   Sobrenome do professor	Sobrenome do aluno	Quantidade do livro
   Petrov	Sidorov	7
   Ivanov	Smith	5
   Petrov	Kankava	2>

9. Selecione o 'Professor' que possui o maior número de 'Livros' de todos os seus 'Alunos'. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

   Sobrenome do professor	Quantidade do livro
   Petrov	9
   Ivanov	5

10. Selecione 'Professor' cujo número de 'Livro' para todos os seus 'Alunos' esteja entre 7 e 11. Classifique por quantidade em ordem decrescente. Deveria ficar assim:

    Sobrenome do professor	Quantidade do livro
    Petrov	onze
    Sidorov	9
    Ivanov	7

11. Imprima todos os 'sobrenome' e 'nome' de todos os 'Professor' e 'Aluno' com o campo 'tipo' (aluno ou professor). Classifique em ordem alfabética por 'sobrenome'. Deveria ficar assim:

    sobrenome	tipo
    Ivanov	estudante
    Kankava	professor
    Smith	estudante
    Sidorov	professor
    Petrov	professor

12. Adicione uma coluna 'taxa' à tabela 'Aluno' existente, que armazenará o curso em que o aluno está atualmente (valor numérico de 1 a 6).
13. Este item não é obrigatório, mas será um plus. Escreva uma função que percorrerá todos os 'Livros' e produzirá todos os 'títulos' separados por vírgulas.

Conclusão

A série sobre banco de dados se arrastou um pouco. Concordar. Porém, percorremos um longo caminho e como resultado saímos com conhecimento do assunto! Obrigado a todos pela leitura, lembro que todos que quiserem seguir em frente e acompanhar o projeto precisam criar uma conta no GitHub e se inscrever na minha conta :) Mais por vir - vamos falar sobre Maven e Docker. Obrigado a todos pela leitura. Repito mais uma vez: quem caminha dominará a estrada ;)

Uma lista de todos os materiais da série está no início deste artigo.