Analizziamo i database e il linguaggio SQL. (Parte 4 - Controllo dei compiti) - "Progetto Java dalla A alla Z"

Un articolo tratto da una serie sulla creazione di un progetto Java (i collegamenti ad altri materiali sono alla fine). Il suo obiettivo è analizzare le tecnologie chiave, il risultato è scrivere un bot di Telegram. Articoli precedenti e analisi dei compiti su database: 1 , 2 , 3 . A tutti coloro che hanno avuto la pazienza e la perseveranza, a tutti coloro che hanno seguito con me questo quarto articolo, ben fatto. Come si suol dire, chi cammina padroneggia la strada. Questa settimana uscirà l'articolo finale sui Database, in cui parleremo di tipologie di relazioni e join . Ma prima di affrontare le nuove informazioni, controlliamo i compiti... Mi sono sentita davvero una maestra. Non arrabbiarti con me: non ho una formazione pedagogica, questo è certo. Dato che la scorsa settimana un controllo dettagliato del telecomando ha fatto la parte del leone nel materiale, ho deciso di dividere l'analisi dei compiti e il ripasso del nuovo materiale in due parti.

In realtà, analisi dei compiti

Sono decisamente contento che ci siano persone che fanno PD e ne parlano. È fantastico! Sono il più sicuro possibile che leggere semplicemente senza consolidare la conoscenza sia una strada che non porta da nessuna parte. Pertanto, tutti coloro che hanno fatto o cercato di fare rispettano. Permettetemi di ricordarvi le condizioni dei compiti:

1. Comprendi l' operatore HAVING e scrivi una query di esempio per le tabelle del nostro esempio. Se hai bisogno di aggiungere alcuni campi o più valori per renderlo più chiaro, aggiungili. Se qualcuno vuole, scrivi la tua soluzione di esempio nei commenti, così posso anche controllarla se ho tempo.
2. Installa MySQL Workbench per lavorare con il database tramite l'interfaccia utente. Penso che abbiamo già fatto abbastanza pratica lavorando dalla console. Connettersi al database. Se usi qualcos'altro per lavorare con il database, sentiti libero di saltare questa attività. Qui e oltre utilizzerò solo MySQL Workbench.
3. Scrivi richieste di ricezione utilizzando i nostri dati:
   1. il paese più piccolo/più popoloso;
   2. numero medio di abitanti del paese;
   3. numero medio di abitanti nei paesi i cui nomi terminano con “a”;
   4. il numero di paesi con una popolazione superiore a quattro milioni;
   5. ordinare i paesi per numero decrescente di abitanti;
   6. ordinare i paesi per nome in ordine naturale.

Parliamo di AVERE

Conoscere l'operatore Avendo può aiutarti a superare più di un colloquio in cui ci saranno problemi SQL. Pertanto, comprenderlo è estremamente importante. Si dà il caso che non sia possibile utilizzare le condizioni per l'aggregazione delle funzioni (SUM, MIN, MAX, AVG). Inoltre, HAVING viene utilizzato per i campi raggruppati. Cosa significa? Se ad esempio vogliamo arrivare ai Paesi in cui il numero medio di abitanti nelle città è superiore a 50.000 abitanti, non possiamo fare a meno di utilizzare HAVING. A quanto ho capito, questo viene fatto perché l'aggregazione avviene dopo che l'istruzione WHERE è stata eseguita ed è impossibile aggiungervi valori di aggregazione che verranno calcolati successivamente. Anche se finora le mie opinioni non aggiungono molto alla comprensione, puoi semplicemente accettarlo come un dato di fatto e seguirlo. Nella programmazione, accade spesso che se qualcosa non è chiaro in un momento, potrebbe significare che il cervello semplicemente non l'ha ancora digerito. Dormi con questo pensiero, e il giorno dopo tutto ti sarà più chiaro.

Installazione di MySQL Workbench

Qui e oltre utilizzerò Workbench per le query. Ti mostrerò cosa ti serve per installare e creare una connessione al database. Questo è un prodotto Oracle, quindi devi solo andare sul loro sito Web e selezionare la versione e il sistema operativo desiderati. Per fare ciò, segui questo collegamento : Qui puoi selezionare esattamente il sistema operativo di cui hai bisogno. Fare clic su Download , ma al posto di scaricare vedremo questa finestra: Non perderti, basta cercare il pulsante chiamato No grazie, avvia il mio download e il download inizierà. Perché lo stanno facendo? Probabilmente, affinché più persone si registrino con loro, questo non è importante per noi. Dopo aver scaricato con successo, esegui il file di installazione. Su MacOS appare così: Basta trascinare l'icona e il gioco è fatto, l'installazione è completa. Non è così difficile come installare MySQL stesso, giusto? Oppure ci siamo semplicemente abituati e siamo diventati più esperti ;) La seconda parte di questo compito è stabilire una connessione al nostro database. Cosa è necessario per questo? Clicca sul segno più accanto a Connessioni MySQL: Nella finestra che appare inserisci i dati necessari:

• Nome connessione : il nome della nostra connessione. Scrivi nomi che siano il più chiari possibile in modo che non ci siano problemi di identificazione in seguito. Do a questa connessione il nome JRTB_DB ;
• Nome host : sarà già impostato come locale 127.0.0.1 (noto anche come localhost). Nel nostro caso non è necessario modificare nulla, poiché il database è installato sul computer, ma se il database è altrove, è necessario modificare di conseguenza l'host (ip della macchina su cui è in esecuzione il database);
• Nome utente : puoi anche specificare il tuo utente, se necessario. Se non hai aggiunto nulla in questa chiave, lasciala invariata;
• Password : fai clic su Memorizza nel portachiavi e imposta esattamente la password che hai impostato per te stesso. Ho lasciato tutto semplice: root .

Per verificare se la connessione funzionerà, fare clic su Test connessione : Bene, se tutto è stato fatto correttamente, il risultato non tarderà ad arrivare: Ora abbiamo una connessione salvata nel database e non avremo bisogno di creare una connessione e riempire nel nome e nella password ogni volta. E questa felicità sarà simile a questa: entriamo nella connessione appena creata e vediamo la finestra di richiesta. Per essere sicuri che tutto sia corretto, controlliamo l’elenco dei database, andiamo nel nostro e prendiamo tutti i dati sulle città: e qui abbiamo carta bianca per ciò che ci serve. La prima sezione è responsabile dello script che inseriamo. Successivamente, nella griglia dei risultati , vediamo il risultato dell'ultima operazione nello script. E l'output dell'azione mostra un elenco di operazioni e il relativo risultato. Una cosa molto utile, voglio dirtelo: con il suo aiuto puoi monitorare la velocità di esecuzione di alcuni script. Perché è importante? Uno dei problemi più comuni relativi alla velocità di esecuzione delle attività in un'applicazione è la velocità di esecuzione delle query nel database. Qui puoi controllarli manualmente in modo rapido e comodo.

Scriviamo le richieste necessarie

Abbiamo solo 7 richieste da completare, andiamo!

1. Ottieni il paese più popoloso. Qui puoi andare d'astuzia e prendere diversi percorsi:

• Secondo la tabella dei paesi

Quindi dobbiamo solo ordinare la nostra query per popolazione e prendere un solo record. Per fare ciò, è necessario aggiungere un operatore LIMIT alla fine dello script e specificare la quantità richiesta: $ SELECT * FROM paese ORDER BY popolazione DESC LIMIT 1;

• Secondo la tabella della città

Qui tutto è più interessante, perché la richiesta sarà più complessa, ma anche più interessante. Poiché non abbiamo ancora idea delle unioni, possiamo solo ottenere l'ID del paese: $ SELECT country_id, SUM(popolazione) FROM città GRUPPO BY country_id ORDER BY SUM(popolazione) DESC LIMIT 1; Qui abbiamo fatto una cosa interessante: abbiamo raccolto la somma della popolazione di tutte le città conosciute in ogni paese, l'abbiamo ordinata in base a questa somma e abbiamo preso il primo elemento. Beh, è ​​fantastico? Sono felice :D Dopo questo ti senti subito un guru delle query... (non per molto, ovviamente))

2. Ottieni il paese più piccolo. Qui puoi andare astutamente e prendere diversi percorsi

In questo caso, tutto sarà esattamente lo stesso. L'unica differenza è che l'ordinamento verrà invertito: tutto qui. Pertanto, scrivo semplicemente richieste:

• Secondo la tabella della città

$ SELECT id_paese, SUM(popolazione) DA città GRUPPO PER id_paese ORDER BY SUM(popolazione) LIMITE 1;

• Secondo la tabella dei paesi

$ SELECT * FROM paese ORDINA PER LIMITE di popolazione 1; E guarda tu stesso il risultato!

3. Numero medio di abitanti del paese

Anche in questo caso le specifiche tecniche non sono precise, come se le avesse scritte il manager... Perché ho deciso questa cosa? Perché non è chiaro su quale tavolo lavorare. Ma questo è normale: semplicemente non esistono compiti in cui tutto sarà immediatamente chiaro e comprensibile. Pertanto, devi leggere attentamente le attività e, se hai domande, chiedile subito! Esatto, un'osservazione. Tenendo conto dei dati che abbiamo nel database, effettueremo la ricerca utilizzando i dati delle città. Per fare ciò, scrivi la seguente query: $ SELECT country_id, AVG(popolazione) FROM città GRUPPO BY country_id; Qui è semplice: utilizziamo la funzione AVG e raggruppiamo i record delle nostre città per paese.

4. Numero medio di abitanti nei paesi i cui nomi finiscono con “a”

Ci sarà un leggero cambiamento qui su richiesta. Dobbiamo aggiungere il filtraggio per nome prima di effettuare il raggruppamento. Faccio i compiti, come tutti gli studenti, prima di pubblicare questo articolo e capisco che questo problema non può essere risolto senza join. Perché? Perché oltre all'ID del Paese dobbiamo conoscere anche il suo nome. E questo non può essere fatto senza unire due tabelle in un unico record. Quindi farò questo compito, ovviamente, ma questo è il mio stipite...))) Volevo inventare un compito usando LIKE nella richiesta...) $ SELECT ci.country_id, AVG(ci.population ) DA città ci INNER JOIN paese co ON ci.id_paese = co.id WHERE co.nome LIKE "%a" GRUPPO PER id_paese; Cos'è successo qua? Innanzitutto, abbiamo unito i record delle tabelle città e paese utilizzando la chiave esterna country_id, filtrata per nome paese in modo che terminassero con "a" e solo successivamente raggruppata per country_id.

5. Numero di paesi con una popolazione superiore a quattro milioni

Qui dobbiamo solo utilizzare la funzione COUNT e aggiungere filtri sulla popolazione: $ SELECT COUNT(*) dal paese WHERE popolazione > 4000000; Di conseguenza, scopriamo che i paesi di questo tipo sono 3. È corretto? Sì, solo la Moldavia non supera questo traguardo.

6. Ordina i paesi per numero decrescente di abitanti

Per fare ciò dobbiamo utilizzare l'operatore ORDER BY che già conosciamo. Ma tieni presente che per impostazione predefinita l'ordinamento è in ordine naturale. Per i numeri significa che è ordinato in modo crescente, per le stringhe significa che è ordinato a partire dai primi caratteri. Se abbiamo bisogno di un ordinamento discendente, abbiamo bisogno dell'inverso di quello naturale: $ SELECT * FROM paese ORDER BY popolazione DESC;

7. Ordina i paesi per nome in ordine naturale

È qui che tornerà utile la conoscenza di quale sia l'ordine naturale. Dato che è l'impostazione predefinita, per noi è un gioco da ragazzi: $ SELECT * FROM country ORDER BY nome;

Invece di uscita

Si dà il caso che la dimensione della soluzione dei compiti sarà molto ampia, quindi faremo un'eccezione: pubblico questo articolo con un assegno e venerdì pubblicherò nuovo materiale con collegamenti e unioni. Grazie a tutti per aver letto. Fino a venerdì!

Un elenco di tutti i materiali della serie si trova all'inizio di questo articolo.