Analisamos bancos de dados e a linguagem SQL. (Parte 4 - verificação do dever de casa) - "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. Artigos anteriores e análise de trabalhos de casa nas bases de dados: 1 , 2 , 3 . A todos que tiveram paciência e resistência, a todos que me acompanharam no quarto artigo - muito bem. Como se costuma dizer, quem caminha domina o caminho. Essa semana será lançado o artigo final sobre Bancos de Dados, no qual falaremos sobre tipos de relacionamentos e joins . Mas antes de tratarmos das novas informações, vamos conferir nosso dever de casa... Me senti realmente uma professora. Não fique bravo comigo: não tenho formação pedagógica, isso é certo. Como na semana passada uma verificação detalhada do controle remoto ocupou a maior parte do material, decidi dividir a análise do meu dever de casa e a revisão do novo material em duas partes.

Na verdade, análise do dever de casa

Estou definitivamente feliz que existam pessoas que fazem DP e falam sobre isso. Isso é legal! Tenho a maior certeza possível de que simplesmente ler sem consolidar conhecimentos é um caminho para lugar nenhum. Portanto, respeite todos que fizeram ou tentaram fazer. Deixe-me lembrá-lo das condições das tarefas:

1. Entenda o operador HAVING e escreva um exemplo de consulta para as tabelas do nosso exemplo. Se precisar adicionar alguns campos ou mais valores para deixar mais claro, adicione-os. Se alguém quiser, escreva seu exemplo de solução nos comentários - para que eu também possa verificar se tiver tempo.
2. Instale o MySQL Workbench para trabalhar com o banco de dados por meio da UI. Acho que já praticamos bastante trabalhando no console. Conecte-se ao banco de dados. Se você usar outra coisa para trabalhar com o banco de dados, fique à vontade para pular esta tarefa. Aqui e mais adiante usarei apenas MySQL Workbench.
3. Escreva pedidos de recebimento usando nossos dados:
   1. o país menor/mais populoso;
   2. número médio de habitantes do país;
   3. número médio de habitantes em países cujos nomes terminam em “a”;
   4. o número de países com uma população superior a quatro milhões;
   5. classificar os países por número decrescente de habitantes;
   6. classificar os países por nome em ordem natural.

Vamos falar sobre TER

Conhecer o operador Tendo pode ajudá-lo a passar em mais de uma entrevista onde haverá problemas de SQL. Portanto, entendê-lo é extremamente importante. Acontece que você não pode usar condições para agregar funções (SUM, MIN, MAX, AVG). Além disso, HAVING é usado para campos agrupados. O que isso significa? Por exemplo, se quisermos obter países onde o número médio de habitantes nas cidades seja superior a 50.000 habitantes, não podemos prescindir de HAVING. Pelo que entendi, isso é feito porque a agregação ocorre após a execução da instrução WHERE e é impossível adicionar valores de agregação a ela que serão calculados posteriormente. Mesmo que até agora minhas opiniões não acrescentem muito ao entendimento, você pode simplesmente aceitar isso como um fato e seguir em frente. Na programação, muitas vezes acontece que, se algo não estiver claro em um determinado momento, pode muito bem significar que o cérebro simplesmente ainda não o digeriu. Durma com esse pensamento e no dia seguinte tudo ficará mais claro.

Instalando o MySQL Workbench

Aqui e mais adiante usarei o Workbench para consultas. Vou mostrar o que você precisa para instalar e criar uma conexão com o banco de dados. Este é um produto da Oracle, então basta acessar o site deles e selecionar a versão e o sistema operacional desejado. Para fazer isso, siga este link : Aqui você pode selecionar exatamente o sistema operacional que precisa. Clique em Download , mas em vez de baixar veremos esta janela: Não se perca, basta procurar o botão chamado Não, obrigado, basta iniciar meu download , e o download começará. Porque eles estão fazendo aquilo? Provavelmente, para que mais pessoas se cadastrem neles, isso não é importante para nós. Após o download bem-sucedido, execute o arquivo de instalação. No MacOS fica assim: Basta arrastar o ícone e pronto, a instalação está completa. Não é tão difícil quanto instalar o próprio MySQL, certo? Ou apenas nos acostumamos e ficamos mais experientes ;) A segunda parte desta tarefa é estabelecer uma conexão com nosso banco de dados. O que é necessário para isso? Clique no sinal de mais ao lado de Conexões MySQL: Na janela que aparece, insira os dados necessários:

• Nome da conexão - o nome da nossa conexão. Escreva nomes o mais claros possível para que não haja problemas de identificação posteriormente. Dou a essa conexão o nome JRTB_DB ;
• Hostname - já estará definido como local 127.0.0.1 (também conhecido como localhost). No nosso caso, nada precisa ser alterado, pois o banco de dados está instalado no computador, mas se o banco de dados estiver em outro lugar, então o host (ip da máquina em que o banco de dados está rodando) precisa ser alterado de acordo;
• Nome de usuário - você também pode especificar seu usuário, se necessário. Se você não adicionou nada nesta chave, deixe-a inalterada;
• Senha - clique em Armazenar nas Chaves e defina exatamente a senha que você definiu para si mesmo. Deixei tudo simples - root .

Para verificar se a conexão funcionará, clique em Testar Conexão : Pois bem, se tudo foi feito corretamente, o resultado não demorará a chegar: Agora temos uma conexão salva no banco de dados, e não precisaremos criar uma conexão e preencher no nome e senha todas as vezes. E essa felicidade ficará assim: Entramos na conexão recém-criada e vemos a janela de solicitação. Para ter certeza de que está tudo correto, vamos verificar a lista de bancos de dados, ir ao nosso e pegar todos os dados das cidades: E aqui temos carta branca para o que precisamos. A primeira seção é responsável pelo script que inserimos. A seguir, na Grade de Resultados , vemos o resultado da última operação do script. E Action Output mostra uma lista de operações e seu resultado. Uma coisa muito útil, quero te contar: com sua ajuda você pode monitorar a velocidade de execução de determinados scripts. Por que isso é importante? Um dos problemas mais comuns na velocidade de execução de tarefas em uma aplicação é a velocidade de execução de consultas no banco de dados. Aqui eles podem ser verificados manualmente de forma rápida e conveniente.

Escrevemos as solicitações necessárias

Temos apenas 7 pedidos para completar, vamos lá!

1. Obtenha o país mais populoso. Aqui você pode ir com astúcia e seguir vários caminhos:

• De acordo com a tabela do país

Então só precisamos classificar nossa consulta por população e obter apenas um registro. Para fazer isso, você precisa adicionar um operador LIMIT no final do script e especificar a quantidade necessária: $ SELECT * FROM país ORDER BY população DESC LIMIT 1;

• De acordo com a tabela da cidade

Tudo é mais interessante aqui, porque o pedido será mais complexo, mas também mais interessante. Como ainda não temos ideia sobre joins, só podemos obter o ID do país: $ SELECT country_id, SUM(population) FROM city GROUP BY country_id ORDER BY SUM(population) DESC LIMIT 1; Aqui fizemos uma coisa legal - coletamos a soma da população de todas as cidades conhecidas em cada país, classificamos por essa soma e pegamos o primeiro elemento. Bem, é ótimo? Estou encantado :D Depois disso você imediatamente se sente como um guru de consultas... (não por muito tempo, é claro))

2. Obtenha o menor país. Aqui você pode ir com astúcia e fazer vários caminhos

Nesse caso, tudo será exatamente igual. A única diferença é que a classificação será invertida - isso é tudo. Portanto, simplesmente escrevo solicitações:

• De acordo com a tabela da cidade

$ SELECT country_id, SUM(população) FROM cidade GROUP BY country_id ORDER BY SUM(população) LIMIT 1;

• De acordo com a tabela do país

$ SELECT * FROM país ORDER BY população LIMITE 1; E veja o resultado você mesmo!

3. Número médio de habitantes no país

Aqui, novamente, a especificação técnica não é de alguma forma precisa, como se o gerente a tivesse escrito... Por que decidi isso? Porque não está claro em qual tabela trabalhar. Mas isso é normal: simplesmente não existem tarefas em que tudo fique imediatamente claro e compreensível. Portanto, você precisa ler atentamente as tarefas e, se tiver dúvidas, pergunte imediatamente! Isso mesmo, uma observação. Tendo em conta os dados que temos na base de dados, faremos a pesquisa utilizando dados das cidades. Para fazer isso, escreva a seguinte consulta: $ SELECT country_id, AVG(population) FROM city GROUP BY country_id; Aqui é simples: usamos a função AVG e agrupamos nossos registros de cidades por país.

4. Número médio de habitantes em países cujos nomes terminam com “a”

Haverá uma pequena alteração aqui mediante solicitação. Precisamos adicionar filtragem por nomes antes de agrupar. Faço minha lição de casa, como todos os alunos, antes de publicar este artigo, e entendo que esse problema não pode ser resolvido sem junções. Por que? Porque além do ID do país, também precisamos saber o nome dele. E isso não pode ser feito sem juntar duas tabelas em um registro. Portanto, farei esta tarefa, claro, mas este é o meu batente...))) Queria criar uma tarefa usando LIKE na solicitação...) $ SELECT ci.country_id, AVG(ci.population ) FROM cidade ci INNER JOIN país co ON ci.country_id = co.id WHERE co.name LIKE "%a" GROUP BY country_id; O que aconteceu aqui? Primeiro, juntamos os registros das tabelas de cidades e países usando a chave estrangeira country_id, filtrados por nomes de países para que terminassem em “a” e só então agrupados por country_id.

5. Número de países com população superior a quatro milhões

Aqui só precisamos usar a função COUNT e adicionar filtragem por população: $ SELECT COUNT(*) from country WHERE população > 4000000; Como resultado, descobrimos que existem 3 desses países. Isso está correto? Sim, apenas a Moldávia não ultrapassa este marco.

6. Classifique os países diminuindo o número de habitantes

Para fazer isso, precisamos usar o operador ORDER BY que já conhecemos. Mas lembre-se de que, por padrão, a classificação ocorre em ordem natural. Para números, isso significa que eles são classificados em ordem crescente; para strings, significa que são classificados a partir dos primeiros caracteres. Se precisarmos de uma classificação decrescente, precisamos do inverso da natural: $ SELECT * FROM country ORDER BY população DESC;

7. Classifique os países por nome em ordem natural

É aqui que o conhecimento do que é a ordem natural será útil. Como é o padrão, é muito fácil para nós: $ SELECT * FROM country ORDER BY nome;

Em vez de saída

Acontece que o tamanho da solução do dever de casa será muito grande, então abriremos uma exceção: estou publicando este artigo com cheque, e na sexta publicarei novo material com conexões e junções. Obrigado a todos pela leitura. Até sexta!

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