Analizziamo i database e il linguaggio SQL. (Parte 5 - connessioni e join) - "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.

• Tipi di relazioni nel database
  • Uno a molti
  • Uno a uno (uno a uno)
  • Molti-a-molti
• Connessioni (join)
  • UNIONE INTERNA
  • SINISTRA UNISCITI
  • ISCRIVITI GIUSTO
  • Protezione dei join
• Compiti a casa

Ciao a tutti, futuri Senior e Senioritas del software. Come ho detto nella parte precedente ( controllo dei compiti ), oggi ci sarà del nuovo materiale. Per coloro che sono particolarmente desiderosi, ho scovato un interessante compito a casa in modo che coloro che sanno già tutto e coloro che non sanno ma vogliono cercare su Google possano esercitarsi e mettere alla prova le proprie capacità. Oggi parleremo dei tipi di connessioni e join.

Tipi di relazioni nel database

Per capire quali sono le relazioni, è necessario ricordare cos'è una chiave esterna. Per chi se lo fosse dimenticato, benvenuto all'inizio della serie .

Uno a molti

Ricordiamo il nostro esempio con paesi e città. È chiaro che una città deve avere un paese. Come collegare un paese a una città? È necessario associare a ciascuna città un identificativo univoco (ID) del Paese di appartenenza: noi lo abbiamo già fatto. Questo è chiamato uno dei tipi di connessione: uno a molti (sarebbe utile conoscere anche la versione inglese: uno a molti). Per parafrasare, possiamo dire: più città possono appartenere ad un paese. È così che dovresti ricordarlo: una relazione uno-a-molti. Fin qui è chiaro, vero? In caso contrario, ecco la prima immagine da Internet: mostra che ci sono clienti e i loro ordini. È logico che un cliente possa avere più di un ordine. C'è uno a molti :) O un altro esempio: ci sono tre tabelle: editore, autore e libro. Ogni editore che non vuole fallire e vuole avere successo ha più di un autore, non sei d'accordo? A sua volta, ogni autore può avere più di un libro: anche su questo non ci possono essere dubbi. E questo significa, ancora una volta, il collegamento di un autore a molti libri, di un editore a molti autori . Ci sono moltissimi altri esempi che si possono fornire. La difficoltà percettiva all'inizio potrebbe risiedere solo nell'imparare a pensare in modo astratto: guardare dall'esterno i tavoli e la loro interazione.

Uno a uno (uno a uno)

Si può dire che questo sia un caso speciale di comunicazione uno-a-molti. Una situazione in cui un record in una tabella è correlato a un solo record in un'altra tabella. Quali esempi possono esserci dalla vita? Se escludiamo la poligamia, allora possiamo dire che esiste una relazione uno a uno tra marito e moglie. Anche se diciamo che la poligamia è consentita, ogni moglie può comunque avere un solo marito. Lo stesso si può dire dei genitori. Ogni persona può avere un solo padre biologico e una sola madre biologica. Rapporto uno a uno esplicito. Mentre scrivevo questo, mi è venuto in mente un pensiero: perché allora dividere una relazione uno a uno in due record in tabelle diverse, se hanno già una relazione uno a uno? Ho trovato la risposta da solo. Questi record possono anche essere collegati ad altri record in altri modi. Di cosa sto parlando? Un altro esempio di connessione uno a uno è tra il Paese e il presidente. È possibile annotare tutti i dati sul presidente nella tabella “paese”? Sì, puoi, SQL non dirà una parola. Ma se si pensa che anche il presidente è una persona... E può anche avere una moglie (altra relazione uno a uno) e dei figli (altra relazione uno a molti) e allora si scopre che sarà necessario per collegare il paese con la moglie e i figli del presidente…. Sembra pazzesco, vero? :D Ci possono essere molti altri esempi per questa connessione. Inoltre, in una situazione del genere, è possibile aggiungere una chiave esterna a entrambe le tabelle, a differenza della relazione uno-a-molti.

Molti-a-molti

Già dal nome si intuisce di cosa parleremo. Spesso nella vita, e programmiamo la nostra vita, ci sono situazioni in cui i tipi di connessioni sopra indicati non sono sufficienti per descrivere le cose di cui abbiamo bisogno. Abbiamo già parlato di editori, libri e autori. Ci sono così tante connessioni qui... Ogni pubblicazione può avere diversi autori: una connessione uno-a-molti. Allo stesso tempo, ogni autore può avere più editori (perché no, lo scrittore è stato pubblicato in un posto, ha litigato per i soldi, è andato in un'altra casa editrice, per esempio). E anche questa è una relazione uno-a-molti. Oppure questo: ogni autore può avere più libri, ma ogni libro può anche avere più autori. Ancora una volta, una relazione uno-a-molti tra autore e libro, libro e autore. Da questo esempio possiamo trarre una conclusione più formalizzata: Era chiaro come impostare i tipi di connessione precedenti in SQL: ne passiamo semplicemente l'ID a quei record, che ce ne sono molti, giusto? Un paese fornisce il proprio ID come chiave straniera a molte città. Cosa fare con le relazioni molti-a-molti ? Questo metodo non è adatto. Dobbiamo aggiungere un'altra tabella che colleghi le due tabelle. Ad esempio, andiamo su MySQL, creiamo un nuovo database manytomany, creiamo due tabelle, autore e libro, che conterranno solo nomi e relativi ID: CREATE DATABASE manytomany; USA molti a molti; CREATE TABLE autore (id INT AUTO_INCREMENT, nome VARCHAR(100), PRIMARY KEY (id)); CREATE TABLE book( id INT AUTO_INCREMENT, nome VARCHAR(100), PRIMARY KEY (id) ); Ora creiamo una terza tabella che avrà due chiavi esterne dalle nostre tabelle autore e libro e questo collegamento sarà univoco. Cioè non sarà possibile aggiungere due volte un record con le stesse chiavi: CREATE TABLE autori_x_libri ( book_id INT NOT NULL, autore_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES libro(id), FOREIGN KEY (author_id) REFERENCES autore (id), UNICO (id_libro, id_autore)); Qui abbiamo utilizzato diverse nuove funzionalità che devono essere commentate separatamente:

• NOT NULL significa che il campo deve essere sempre compilato e se non lo facciamo SQL ce lo dirà;
• UNIQUE dice che un campo o un gruppo di campi deve essere univoco nella tabella. Accade spesso che oltre all'identificatore univoco, un altro campo debba essere univoco per ciascun record. E UNIQUE è responsabile proprio di questa questione.

Dalla mia pratica: quando passiamo da un vecchio sistema a uno nuovo, noi, come sviluppatori, dobbiamo memorizzare gli ID del vecchio sistema per lavorarci e crearne uno nostro. Perché crearne uno tuo e non utilizzare quelli vecchi? Potrebbero non essere sufficientemente univoci oppure questo approccio alla creazione degli ID potrebbe non essere più rilevante e limitato. A questo scopo abbiamo reso univoco anche il vecchio nome ID nella tabella. Per verificarlo, è necessario aggiungere dati. Aggiungi un libro e un autore: NSERT INTO libro (nome) VALUES ("libro1"); INSERT INTO autore (nome) VALORI ("autore1"); Sappiamo già dagli articoli precedenti che avranno ID 1 e 1. Possiamo quindi aggiungere subito un record alla terza tabella: INSERT INTO autori_x_libri VALUES (1,1); E tutto andrà bene finché non vorremo ripetere di nuovo l'ultimo comando: cioè scrivere di nuovo gli stessi ID: il risultato sarà naturale: un errore. Ci sarà un duplicato. La voce non verrà registrata. Ecco come verrà creata una connessione molti-a-molti... Tutto questo è molto interessante e interessante, ma sorge una domanda logica: come ottenere queste informazioni? Come combinare insieme i dati di tabelle diverse e ottenere una risposta? Questo è ciò di cui parleremo nella parte successiva))

Connessioni (join)

Nella parte precedente ti ho preparato a capire subito cosa sono i join e dove utilizzarli. Perché sono profondamente convinto che non appena arriverà la comprensione, tutto diventerà immediatamente molto semplice e tutti gli articoli sui join saranno chiari come gli occhi di un bambino :D In generale, i join ottengono risultati da più tabelle attraverso di un JOIN (join dall'inglese join). E questo è tutto...) E per aderire è necessario specificare il campo in cui verranno unite le tabelle. Il diavolo non è così spaventoso come viene dipinto, giusto?) Successivamente, parleremo solo di quali tipi di join esistono e di come usarli. Esistono molti tipi di join e non li prenderemo in considerazione tutti. Solo quelli di cui abbiamo veramente bisogno. Ecco perché non siamo interessati a unioni esotiche come Cross e Natural. Mi ero completamente dimenticato, dobbiamo ricordare un'altra sfumatura: tabelle e campi possono avere alias : pseudonimi. Sono convenientemente utilizzati per i join. Ad esempio, puoi fare questo: SELECT * FROM table1; se la query utilizzerà spesso table1, puoi assegnarle un alias: SELECT* FROM table1 as t1; o ancora più semplice da scrivere: SELECT * FROM table1 t1; e successivamente nella query sarà possibile utilizzare t1 come alias per questa tabella.

UNIONE INTERNA

L'unione più comune e semplice. Dice che quando abbiamo due tabelle e un campo tramite il quale possono essere unite, verranno selezionati tutti i record le cui relazioni esistono nelle due tabelle. Era difficile dirlo in qualche modo. Diamo un'occhiata ad un esempio: aggiungiamo un record al database delle nostre città. Una voce per le città e una per i paesi: $ INSERT INTO country VALUES(5, "Uzbekistan", 34036800); e $ INSERT INTO città (nome, popolazione) VALUES("Tbilisi", 1171100); Abbiamo aggiunto un paese che non ha una città nella nostra tabella e una città che non è associata ad un paese nella nostra tabella. Pertanto, INNER JOIN è impegnato a emettere tutti i record per quelle connessioni che si trovano in due tabelle. Ecco come appare la sintassi generale quando vogliamo unire due tabelle table1 e table2: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; e quindi verranno restituiti tutti i record che hanno una relazione nelle due tabelle. Nel nostro caso, quando vogliamo ricevere informazioni sui paesi insieme alle città, il risultato sarà questo: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Qui, anche se i nomi sono gli stessi, si vede chiaramente che vengono prima i campi delle città, poi quelli dei paesi. Ma le due voci che abbiamo aggiunto sopra non ci sono. Perché è esattamente così che funziona INNER JOIN.

SINISTRA UNISCITI

Ci sono casi, e molto spesso, in cui non siamo soddisfatti della perdita di campi della tabella principale perché non esiste alcun record nella tabella adiacente. Ecco a cosa serve il LEFT JOIN. Se nella nostra richiesta precedente specifichiamo LEFT invece di INNER, aggiungeremo un'altra città nella risposta - Tbilisi: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; C'è una new entry su Tbilisi e tutto ciò che riguarda il Paese è in null . Questo è spesso il modo in cui viene utilizzato.

ISCRIVITI GIUSTO

Qui ci sarà una differenza rispetto a LEFT JOIN in quanto tutti i campi verranno selezionati non a sinistra, ma a destra nella connessione. Cioè, non le città, ma tutti i paesi verranno presi: $ SELECT * FROM città ci RIGHT JOIN paese co ON ci.country_id = co.id; Ora è chiaro che in questo caso non ci sarà Tbilisi, ma avremo l’Uzbekistan. Qualcosa del genere…))

Protezione dei join

Ora voglio mostrarvi un'immagine tipica che i ragazzi stipano prima di un colloquio per convincerli di aver compreso l'essenza dei join: qui tutto è mostrato sotto forma di set, ogni cerchio è un tavolo. E i punti in cui è dipinto sono quelle parti che verranno mostrate in SELECT. Guardiamo:

• INNER JOIN è solo l'intersezione di insiemi, ovvero quei record che hanno connessioni a due tabelle: A e B;
• LEFT JOIN indica tutti i record della tabella A, inclusi tutti i record della tabella B che hanno un'intersezione (connessione) con A;
• RIGHT JOIN è esattamente l'opposto di LEFT JOIN: tutti i record nella tabella B e i record di A che hanno una relazione.

Dopo tutto questo, questa immagine dovrebbe essere chiara))

Compiti a casa

Questa volta i compiti saranno molto interessanti e tutti coloro che li risolveranno con successo potranno stare certi di essere pronti per iniziare a lavorare sul lato SQL! I compiti non sono masticati e sono stati scritti per gli studenti delle medie, quindi non sarà facile e noioso per te :) Ti darò una settimana per svolgere i compiti da solo, e poi pubblicherò un articolo separato con un'analisi dettagliata della soluzione ai compiti che ti ho dato.

Il compito vero e proprio:

1. Scrivi uno script SQL per creare la tabella 'Studente' con i seguenti campi: id (chiave primaria), nome, cognome, e_mail (univoco).
2. Scrivi uno script SQL per creare la tabella 'Libro' con i seguenti campi: id, titolo (id + titolo = chiave primaria). Collega "Studente" e "Libro" con una relazione "Studente" uno-a-molti "Libro".
3. Scrivi uno script SQL per creare la tabella 'Insegnante' con i seguenti campi: id (chiave primaria), nome, cognome, e_mail (univoco), oggetto.
4. Collegare "Studente" e "Insegnante" con una relazione "Studente" molti-a-molti Insegnante.
5. Seleziona 'Studente' che ha 'oro' nel cognome, ad esempio 'Sid oro v', 'V oro novsky'.
6. Seleziona dalla tabella 'Studente' tutti i cognomi ('cognome') e il numero delle loro ripetizioni. Considera che ci sono omonimi nel database. Ordina per quantità in ordine decrescente. Dovrebbe sembrare come questo:

   cognome	quantità
   Petrov	15
   Ivanov	12
   Sidorov	3

7. Seleziona i primi 3 nomi più ripetuti da "Studente". Ordina per quantità in ordine decrescente. Dovrebbe sembrare come questo:

   nome	quantità
   Alessandro	27
   Sergey	10
   Peter	7

8. Seleziona 'Studenti' che hanno il maggior numero di 'Libri' e 'Docenti' associati Ordina per quantità in ordine decrescente. Dovrebbe sembrare come questo:

   Cognome dell'insegnante	Cognome dello studente	Quantità del libro
   Petrov	Sidorov	7
   Ivanov	fabbro	5
   Petrov	Kankava	2>

9. Seleziona l'"Insegnante" che ha il maggior numero di "Libri" tra tutti i suoi "Studenti". Ordina per quantità in ordine decrescente. Dovrebbe sembrare come questo:

   Cognome dell'insegnante	Quantità del libro
   Petrov	9
   Ivanov	5

10. Selezionare 'Insegnante' il cui numero di 'Libro' per tutti i suoi 'Studenti' sia compreso tra 7 e 11. Ordina per quantità in ordine decrescente. Dovrebbe sembrare come questo:

    Cognome dell'insegnante	Quantità del libro
    Petrov	undici
    Sidorov	9
    Ivanov	7

11. Stampa tutto il 'cognome' e il 'nome' di tutti i 'Docente' e 'Studente' con il campo 'tipo' (studente o docente). Ordina in ordine alfabetico per "cognome". Dovrebbe sembrare come questo:

    cognome	tipo
    Ivanov	alunno
    Kankava	insegnante
    fabbro	alunno
    Sidorov	insegnante
    Petrov	insegnante

12. Aggiungi una colonna "tariffa" alla tabella "Studente" esistente, che memorizzerà il corso in cui si trova attualmente lo studente (valore numerico da 1 a 6).
13. Questo articolo non è obbligatorio, ma sarà un vantaggio. Scrivi una funzione che esaminerà tutti i "Libri" e restituirà tutti i "titoli" separati da virgole.

Conclusione

La serie sul database si è trascinata un po'. Essere d'accordo. Tuttavia abbiamo fatto molta strada e di conseguenza ne emergiamo con cognizione di causa! Grazie a tutti per aver letto, vi ricordo che chiunque voglia andare avanti e seguire il progetto deve creare un account su GitHub e iscriversi al mio account :) Altro in arrivo: parliamo di Maven e Docker. Grazie a tutti per aver letto. Lo ripeto ancora una volta: chi cammina padroneggia la strada ;)

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