Analisamos bancos de dados e a linguagem SQL. (Parte 3) - "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.

• Verificando o dever de casa
  • Adicionando uma chave primária
  • Adicionando Moldávia
  • Crie a tabela de cidades
  • Adicione uma chave estrangeira às cidades
  • Parte bônus. Teste
• Instrução SELECT
  • Parâmetro ONDE
  • Parâmetro ORDER BY
  • Parâmetro GRUPO POR
• Trabalho de casa
• Conclusão

Olá senhoras e senhores, vamos continuar falando sobre bancos de dados, SQL e outras coisas. O material de hoje conterá parte teoria e parte prática. Deixe-me lembrar que da última vez falamos sobre como configurar tudo, como criar um banco de dados, uma tabela e obter dados dela. É hora de ver se deu certo com o sensoriamento remoto. Na minha opinião, metade disso poderia ter sido feito apenas com base no artigo anterior. Acontece que para montar bem uma aplicação e deixar tudo mais ou menos bonito é preciso falar sobre bancos de dados, e para falar sobre eles é preciso gastar muito tempo.

Verificando o dever de casa

Enorme respeito a todos que concluíram as tarefas com sucesso. Isso significa que você entende que só você precisa disso e isso só te ajuda. Para aqueles que negligenciaram minha tarefa, deixe-me lembrá-los da condição:

1. Você precisa adicionar uma chave primária (PRIMARY KEY) do campo ID ao esquema da tabela de países.
2. Adicione outro país à tabela de países - Moldávia.
3. Seguindo o esquema do artigo anterior, crie uma tabela cidade, que conterá todos os campos que estão descritos. Os nomes dos campos serão os seguintes: id, nome, country_id, população.
4. Adicione uma chave primária à tabela de cidades.
5. Adicione uma chave estrangeira à tabela de cidades.

Para começar, vamos usar a primeira parte do artigo anterior e ir ao terminal do banco de dados.

Adicionando uma chave primária

Você pode adicionar uma chave primária (PRIMARY KEY) de duas maneiras: imediatamente ao criar uma tabela, ou após a criação, usando ALTER TABLE.

Chave primária durante a criação da tabela

Como já criamos uma tabela, e sem excluí-la não poderemos mostrar esta abordagem dentro deste banco de dados, simplesmente criaremos um banco de dados de teste temporário no qual faremos tudo. Vamos inserir os seguintes comandos:

• crie um novo banco de dados:

  Teste $CREATE DATABASE;

• crie uma tabela adicionando uma chave primária:

  $ CREATE TABLE país(id INT, nome VARCHAR(30), CHAVE PRIMÁRIA (id));

Em geral, nada complicado. Após declarar as variáveis, é adicionada a seguinte parte PRIMARY KEY (id) , onde o nome do campo que será a chave primária é passado entre parênteses. E vamos ver como mudou o esquema da tabela: $DESC country; Como você pode ver, o valor PRI apareceu no campo Chave da entrada id .

Chave primária após a criação da tabela

Como eu disse anteriormente, a primeira chave após a criação de uma tabela pode ser atribuída usando ALTER TABLE . Executaremos este exemplo em nosso banco de dados de cidades :

• vamos ao nosso banco de dados a partir do teste:

  $USE cidades;

• Vamos verificar se estamos definitivamente em nosso banco de dados (deve haver outro campo lá - população). Para fazer isso escrevemos:

  $ População DESC;

está tudo correto, a mesa é nossa. Vamos escrever o seguinte:

$ ALTER TABLE país ADICIONAR CHAVE PRIMÁRIA (id);

• e verifique imediatamente com o comando:

  $DESC país;

Como vocês podem ver na foto está tudo correto, o valor do PRI está exatamente onde deveria estar. Aliás, trabalhamos com um banco de dados de teste. Agora precisamos excluí-lo: por que precisamos desorganizar o servidor, certo? Para fazer isso, usamos um comando bastante conhecido: $ DROP DATABASE test;

Adicionando Moldávia

Primeiro precisamos decidir o que iremos gravar. Nosso próximo ID será 4. O nome será Moldávia e sua população é 3550900. Portanto, executamos o comando INSERT INTO que já conhecemos: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); E verificamos se esse valor está exatamente no banco de dados: $ SELECT * FROM country WHERE id = 4; Na solicitação de dados determinei imediatamente qual campo seria pesquisado, então obtivemos apenas um registro, que era o que precisávamos.

Crie a tabela de cidades

Usando o diagrama do primeiro artigo sobre banco de dados, obtemos as informações necessárias sobre a tabela. Ele conterá os seguintes campos:

• id — identificador exclusivo;
• nome – nome da cidade;
• country_id — chave estrangeira do país;
• população – população da cidade.

É um pouco estressante escrever sempre uma identificação única, não acha? Eu gostaria de deixar isso para as autoridades do MySQL . E existe uma maneira - AUTO INCREMENTO . Precisamos adicionar isso ao campo digital, e se não passarmos explicitamente os valores, o próprio MySQL aumentará o ID em um em relação ao anterior. Portanto, a criação de uma tabela ficará assim: $ CREATE TABLE cidade ( id INT AUTO_INCREMENT, nome VARCHAR(30), country_id INT, população INT, PRIMARY KEY (id)); Vejamos o diagrama da tabela para ver se tudo foi feito corretamente: $DESC cidade; Como você pode ver no diagrama da tabela, temos uma nova descrição para o campo id - auto_increment. Então fizemos tudo certo. Vamos verificar os dados de uma tabela totalmente configurada. Para fazer isso, faremos a última parte da tarefa – a chave estrangeira.

Adicione uma chave estrangeira às cidades

Para uma chave estrangeira haverá este comando: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); E vamos verificar imediatamente o que há de errado com o esquema da tabela: ele mudou ao longo de uma hora? $DESC cidade;

Parte bônus. Teste

Esqueci de adicioná-lo à tarefa - preencha os dados que estavam na captura de tela da primeira parte. Eu esqueci, então agora vou fazer isso sozinho. E para quem estiver interessado, você pode fazer isso sozinho sem mim e depois verificaremos ;) Havia Kharkov, Kiev, Minsk, Odessa, Voronezh, e também adicionaremos Chisinau. Mas desta vez não transmitiremos IDs, iremos ignorá-los: $ INSERT INTO city (nome, country_id, população) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Como você pode ver, você pode fazer várias entradas simultaneamente usando um comando INSERT INTO. Uma coisa útil, lembre-se) E imediatamente vamos ver o que está na tabela: $ SELECT * FROM city; AUTO_INCREMENT - funcionou exatamente como queríamos. Os arquivos de identificação estão todos preenchidos, embora não os tenhamos enviado. Uma chave estrangeira é algo dependente. Para verificar se funciona corretamente, você pode tentar escrever uma chave estrangeira que não existe na tabela estrangeira. Digamos que decidimos que id = 5 é o Cazaquistão. Mas na realidade não está na tabela dos países. E para verificar se o banco de dados vai jurar, adicione a cidade - Astana: $ INSERT INTO cidade (nome, country_id, população) VALUES ('Astana', 5, 1136156); E naturalmente obtemos o erro: Agora a chave estrangeira garante que não tentamos atribuir um país à cidade que não está em nosso banco de dados. Esta parte da lição de casa pode ser considerada concluída - avance para a nova :)

Instrução SELECT

Bem, tudo não parece mais tão assustador, certo? Gostaria de ressaltar mais uma vez que para desenvolvedores Java, o conhecimento do banco de dados é obrigatório. Sem um banco de dados você não pode ir a lugar nenhum. Sim, já quero começar a escrever uma candidatura, concordo. Mas é necessário. Então continuaremos assim. Usando a instrução SELECT, recuperamos dados do banco de dados. Ou seja, esta é uma operação típica de DML (já esqueceu o que é?...))) Releia os artigos ANTES). Quais são os benefícios dos bancos de dados relacionais? Eles possuem ótima funcionalidade para agregar e recuperar dados. É para isso que a instrução SELECT é usada. Parece que não poderia haver nada de complicado nisso, certo? Mas acontece que ainda há muito para entender. É importante que entendamos os princípios básicos a partir dos quais podemos construir. A consulta mais simples com uma instrução SELECT é selecionar todos os dados de uma tabela. Eu realmente gostei da descrição do wiki sobre exatamente qual ordem os operadores devem seguir em uma consulta SELECT, então vou copiá-la descaradamente aqui:

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Aqui você pode ver que não pode colocar primeiro o operador GROUP BY e depois o operador WHERE. Isso precisa ser lembrado para que mais tarde não haja ressentimento por erros que não estão claros de onde vêm. $SELECT *FROM cidade; Mas extrair todos os dados claramente não é divertido para nós. É exatamente o mesmo se quiséssemos martelar pregos com um microscópio [1] , [2] . Como o banco de dados executa operações de filtragem, classificação e agregação muito mais rápido que o código Java, é melhor deixar esse assunto para o banco de dados. Portanto, ao complicar as tarefas abriremos novas funcionalidades.

Parâmetro ONDE

Para filtrar uma seleção, utiliza-se a palavra WHERE . Isso deve ser interpretado da seguinte forma: SELECT * FROM tablename (seleciona todos os campos da tabela tablename) WHERE talbe_row = 1 (onde nos registros o campo table_row é igual a 1). É importante observar que a ordem das palavras-chave na consulta é importante. Você não pode escrever WHERE a =1 FROM table_name SELECT *. Para o idioma russo está tudo bem, e para alguns pode não parecer uma bagunça, mas para SQL isso é inaceitável. Escrevemos a seguinte consulta: $ SELECT * FROM city WHERE country_id = 1; E escolhemos cidades ucranianas. Nada mal, certo? E se quisermos não só o ucraniano, mas também o bielorrusso? Para tanto, podemos listar a coleção de valores que o campo pode assumir: $SELECT * FROM cidade WHERE country_id IN(1, 3); E já temos cidades de dois países respondendo. E se houver várias condições para filtrar? Digamos que queremos cidades com uma população de mais de dois milhões? Para fazer isso, use as palavras OR e AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND população > 2000000; Ótimo, mas e se precisarmos adicionar mais uma condição - pesquisar nomes por meio de uma expressão regular (não vou descrever expressões regulares aqui: aqui está uma pessoa que fez isso “brevemente” em 4 partes )? Por exemplo, lembramos como se escreve uma cidade, mas não completamente... Para fazer isso, você pode adicionar a palavra-chave LIKE à expressão de filtragem : $ SELECT * FROM cidade WHERE country_id IN (1, 3) AND população > 2000000 OR nome LIKE “%hark%”; E assim também obtivemos Kharkov. Como resultado, podemos dizer que nossa busca acabou sendo muito boa. Mas eu gostaria de classificar não por ID, mas por população, mas como? Sim, muito simples...

Parâmetro ORDER BY

Usando ORDER BY, podemos classificar os registros que recebemos por um campo específico. Ele classifica números e strings. Vamos expandir a consulta anterior, ordenar por população, adicionando ORDER BY população: $ SELECT * FROM cidade WHERE country_id IN (1, 3) AND população > 2000000 OR nome LIKE “%hark%” ORDER BY população; Como podemos observar, a ordenação ocorreu em ordem natural, ou seja, em ordem crescente. E se quisermos o oposto? Para fazer isso, você precisa adicionar a palavra DESC: $ SELECT * FROM cidade WHERE country_id IN (1, 3) AND população > 2000000 OU nome LIKE “%hark%” ORDER BY população DESC; Agora a classificação é baseada na redução da população. E o banco de dados faz isso muito rapidamente: nenhum Collections.sort pode ser comparado. Agora vamos ordenar por linha, por nome na ordem inversa: $ SELECT * FROM cidade WHERE country_id IN (1, 3) AND população > 2000000 OR nome LIKE “%hark%” ORDER BY nome DESC;

Parâmetro GRUPO POR

Usado para agrupar registros por campos específicos. Isso geralmente é necessário para usar funções agregadas... O que são funções agregadas?)) Faz sentido agrupar por alguns campos se eles forem iguais para registros diferentes. Vejamos o que isso significa usando nosso exemplo. Digamos que as cidades tenham chaves estrangeiras – IDs de países. Portanto, o ID é o mesmo para cidades do mesmo país. Portanto, você pode pegar e agrupar registros por eles: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; Mas sem funções de agregação parece um pouco sem graça, você deve admitir. Portanto, vejamos algumas das funções mais comuns:

• COUNT - número de registros, pode ser usado sem agrupamento, usado como COUNT(*) . No caso de agrupamento por algum campo - COUNT(groupped_field);
• MAX - encontra o valor máximo para um campo específico;
• MIN - encontra o valor mínimo para um campo específico;
• SUM - encontra a soma de um campo específico;
• AVG - encontra o valor médio.

Em geral, essas funções podem ser utilizadas sem agrupamento, só assim será exibido apenas um campo. Vamos testá-los para a população da nossa cidade: $ SELECT COUNT(*) FROM city; O que eles pediram foi o que conseguiram. Apenas o número de registros. Às vezes isso é útil. Por exemplo, se precisarmos saber a quantidade de artigos de um determinado autor. Não há necessidade de retirá-los do banco de dados e contá-los. Você pode simplesmente usar COUNT(). $ SELECT AVG(população) FROM cidade; $ SELECT MIN(população) FROM cidade; E é aqui que o agrupamento entra em vigor. Por exemplo, a tarefa é conseguir a menor cidade do país. Já sabe como fazer isso? Experimente você mesmo e observe: $ SELECT country_id as Country, MIN(population) FROM city WHERE GROUP BY country_id; Até agora vemos apenas o ID do país, mas não importa - da próxima vez faremos tudo. E então já existe um resultado, e conseguimos o que queríamos - a menor cidade do país com ID = 1. O resto das funções serão as mesmas. É importante observar que classificar todos os campos com * ao usar agrupamento e agregação não funcionará! Pense nisso ;)

Trabalho de casa

Com base nos resultados dos artigos anteriores, fica claro que o dever de casa está sendo feito, então vamos continuar)) Sim, todos que fizerem o dever de casa continuarão colocando “+” nos comentários. É importante para mim que o tema do dever de casa seja do seu interesse, para que eu continue a fazê-lo no futuro. Sim, leio seus comentários regularmente. Claro, respondo com menos frequência. Vi que pediram para dar problemas de SQL mais difíceis. Até aprendermos as junções, não haverá problemas interessantes, então haverá aqueles que eu preciso para mais material.

Tarefas:

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: assim também posso verificar se tiver tempo.
1. 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.
2. Escreva solicitações para receber 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.

Conclusão

Hoje discutimos em detalhes o dever de casa da última lição. Além disso, considero isto importante tanto para quem não o fez como para quem o fez. Para o primeiro, esta é uma oportunidade de saber a resposta e, para o segundo, de compará-la com o seu resultado. Assine minha conta GitHub para se manter atualizado sobre as mudanças no projeto. Manterei toda a base de código lá. Tudo acontecerá nesta organização . A seguir, discutimos a instrução SELECT. Ele é o mais importante para nós. É por ele que passarão todas as solicitações de dados, e devemos entendê-lo. O mais importante é lembrar a ordem em que os parâmetros são adicionados (WHERE, ORDER BY, GROUP BY e assim por diante). Sim, não contei tudo o que era possível, mas não estabeleci essa meta para mim. Sim, eu sei que você já está ansioso para escrever um requerimento. Seja paciente, isso é tudo que você precisa. Tanto para o projeto quanto para o seu crescimento profissional. Enquanto você espera, certifique-se de que o Git já seja familiar para você. Vou usá-lo por padrão, como uma ferramenta bem conhecida. Obrigado a todos pela leitura. No próximo artigo falaremos sobre conexões e junções de banco de dados. É aí que estarão as tarefas legais))

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