Analizziamo i database e il linguaggio SQL. (Parte 6 - Verifica dell'incarico finale) - "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. Questa parte contiene un'analisi dell'attività finale sul database. Saluti, cari lettori. Oggi analizzeremo il compito dell'ultimo articolo sul database. È interessante perché è destinato alle interviste di livello medio. Cioè, dopo questo compito puoi già sostenere un colloquio e supererai con successo almeno una parte di ciò che riguarda i database relazionali. So quanto questo articolo possa essere necessario e quindi metterò tutta la mia esperienza per renderlo utile e interessante. E se non ti addormenti a metà articolo vorrà dire che ho raggiunto il mio obiettivo. Non ripeterò l'intero compito: lo citerò prima di completare ogni compito, sottolineandolo in corsivo. Mi aspetto che tutti coloro che leggono questo articolo eseguano tutte le query nel proprio database e ottengano la stessa cosa. Ciò porterà il massimo beneficio all’azienda. E sarò un po 'più felice al pensiero di aver aiutato qualcuno nel nostro difficile compito)

Esercizio 1

Scrivi uno script SQL per creare la tabella 'Studente' con i seguenti campi: id (chiave primaria), nome, cognome, e_mail (univoco). Lo abbiamo già fatto quindi non dovrebbero esserci problemi. Nello script è necessario specificare la chiave primaria e un campo univoco diverso da quello primario. Per prima cosa creiamo un nuovo database per questa attività: $ CREATE DATABASE final_task; E usiamo questo database: $ USE final_task; Una volta che l'ambiente è configurato e pronto per eseguire il lavoro, possiamo scrivere il seguente script: $ CREATE TABLE student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), UNICO (e-mail)); Finora non c’è nulla di nuovo rispetto a quanto abbiamo già vissuto. Ogni commento è superfluo, andiamo avanti.

Compito 2-3

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". Uniamo due attività in una per renderla più veloce e conveniente. Ho già discusso come aggiungere una chiave esterna separata negli articoli precedenti. Inoltre, dobbiamo ricordare come creiamo connessioni e attraverso cosa. L'articolo precedente ti aiuterà, quindi ecco lo script: $ CREATE TABLE book ( id INT, titolo VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, titolo), FOREIGN KEY (student_id) REFERENCES student (id ) ); In questo modo semplice, abbiamo aggiunto una chiave composta per la nostra tabella PRIMARY KEY (id, title) , ora la chiave sarà esattamente la coppia. Ciò significa che nella tabella possono essere presenti più valori di campo ID identici. E esattamente lo stesso per il titolo.

Compito 4

Scrivi uno script SQL per creare la tabella 'Insegnante' con i seguenti campi: id (chiave primaria), nome, cognome, e_mail (univoco), oggetto. Continuiamo a preparare il nostro database per le query, creiamo una tabella insegnante: $ CREATE TABLE insegnante( id INT AUTO_INCREMENT, nome VARCHAR(30), cognome VARCHAR(30), email VARCHAR(100), soggetto VARCHAR(40), PRIMARY KEY ( id), UNICO (email) ); Finora non è difficile, vero? Tre compiti sono già finiti!

Compito 5

Collegare "Studente" e "Insegnante" con una relazione "Studente" molti-a-molti Insegnante. Ora questo è più interessante! Ne abbiamo appena parlato l'ultima volta. Permettetemi di ricordarvi cosa è necessario per raggiungere questo obiettivo: è necessario creare una tabella intermedia che memorizzi le coppie studente-docente. Con il suo aiuto sarà possibile creare una relazione molti-a-molti. Creiamo quindi una tabella students_x_techers . L'approccio di denominazione è aperto e potrebbe anche essere: studente_insegnante . $ CREATE TABLE students_x_teachers ( student_id INT NOT NULL, Teacher_id INT NOT NULL, CHIAVE PRIMARIA (id_studente, id_insegnante), CHIAVE ESTERA (id_studente) RIFERIMENTI studente(id), CHIAVE ESTERA (id_insegnante) RIFERIMENTI insegnante(id) ); Come puoi vedere, tutto è fatto in modo chiaro e coerente. Abbiamo una chiave composta per due chiavi esterne: student_id e Teacher_id. Perché anche una chiave esterna? Per poter essere sicuri che esistano record nelle tabelle studenti e insegnanti per le coppie da registrare.

Compito 6

Seleziona "Studente" che ha "oro" nel cognome, ad esempio "Sidorov", "Voronovsky". Per renderlo interessante e visivo per noi, suggerisco di aggiungere prima diversi studenti, in modo che alcuni di loro siano adatti a questa richiesta e altri no. Scriviamo quindi quelli che dovranno essere inclusi a seguito della richiesta: $ INSERT INTO studente (nome, cognome, email) VALORI ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO studente (nome, cognome, email) VALORI ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); E quelli che non dovrebbero entrare: $ INSERT INTO student (name, last_name, email) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO studente (nome, cognome, email) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Controlliamo il risultato, guardiamo l'elenco dei dati nella tabella degli studenti: $ SELECT * FROM student; e otteniamo: ci sono quattro record in totale, due di essi dovrebbero adattarsi e due no. Dopo aver preparato tutti i dati per la richiesta, possiamo effettuare una richiesta per l'attività stessa: $ SELECT * FROM student WHERE last_name LIKE '%oro%'; Di conseguenza, Ivan e Nikolai hanno esaminato l'elenco.

Compito 7

Leggiamo il compito successivo: Selezionare 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

Per chiarezza, è necessario aggiungere più dati. Senza ulteriori indugi, aggiungiamo i Petrov, Ivanov e Sidorov, che non conoscono la loro parentela ;) Non inventerò un indirizzo email, lo escluderò semplicemente dalle nuove voci. Eseguiamo il seguente comando 12 volte: $ INSERT INTO studente (nome, cognome) VALUES ('Ivan', 'Ivanov'); Aggiungiamo 15 Petrov: $ INSERT INTO studente (nome, cognome) VALUES ('Petr', 'Petrov'); E due Sidorov (ne abbiamo già uno))): $ INSERT INTO studente (nome, cognome) VALUES ('Sidor', 'Sidorov'); I dati sono ora pronti. Per ottenere tali dati, è necessario eseguire un raggruppamento; per eseguire un raggruppamento, è necessario utilizzare l'operatore Raggruppa per e è necessario farlo tramite il campo cognome. Puoi anche notare che il numero di ripetizioni è designato come quantità , e qui devi anche ricordare come creare alias in SQL: $ SELECT last_name, COUNT(*) as amount FROM student BY last_name ORDER BY COUNT(*) DESC ; Quindi ho esagerato con i Petrov: si è scoperto che erano 16))

Compito 8

Condizione: 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

Oh, a questo scopo abbiamo già Ivan, Peters e Sidors. Pertanto non è necessario aggiungerli. Sappiamo già come ordinare. L'unica cosa di cui non abbiamo parlato oggi è come selezionare un certo numero di record. Ciò è già apparso in soluzioni precedenti ai problemi del database. Per chi non l'ha letto, lo legga. Per il resto andiamo dritti al sodo: $ SELECT nome, COUNT(*) come quantità FROM studente GRUPPO PER nome ORDER BY COUNT(*) DESC LIMIT 3; Come si può vedere dalla query, se si conosce l'ordine degli operatori in una query SELECT, non ci saranno problemi con l'esecuzione di tale query. E questo compito spetta ancora a noi. E la conoscenza presentata in precedenza è abbastanza sufficiente per risolvere questo problema.

Compito 9

Condizione del compito: Selezionare lo 'Studente' che ha il maggior numero di 'Libri' e di 'Insegnanti' 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

Quindi questo compito è chiaramente più difficile del precedente, giusto? Non c'è da stupirsi: profuma di join... e più di uno) Per prima cosa bisogna capire cosa fare... È chiaro che la quantità di Book richiede un raggruppamento. Ma cosa? E perché dovremmo raggrupparli? La query coinvolge tre tabelle, raggruppamento e ordinamento. A giudicare dal fatto che i record in cui non sono presenti libri non vengono visualizzati, significa che è necessario utilizzare INNER JOIN. Faremo anche una richiesta per LEFT JOIN in modo che non ci siano problemi con questo. E ci sono diverse opzioni. La prima cosa che facciamo è unire tre tabelle in un unico record. Successivamente, raggruppiamo per studente e aggiungiamo il nome dell'insegnante. Cosa sceglieremo? Il nome dell'insegnante, dello studente e il numero di libri. Aggiungiamo i dati per la richiesta:

• tre insegnanti;
• dieci libri;
• collegare due studenti con tre insegnanti.

Tre insegnanti

$ INSERT INTO insegnante(cognome) VALUES ('Matvienko'); $ INSERT INTO insegnante(cognome) VALUES ('Shevchenko'); $ INSERT INTO insegnante(cognome) VALUES ('Vasilenko');

10 libri

Prenderò i documenti d'identità del 1° e del 2° studente. Allegherò loro dei libri. Poiché AUTO_INCREMENT non è stato impostato, per non scrivere ogni volta un nuovo ID è necessario eseguire le seguenti operazioni: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Successivamente, aggiungi i libri per il primo studente: $ INSERT INTO book (title, student_id) VALUES('book1', 1); $ INSERT INTO libro (titolo, id_studente) VALUES('libro2', 1); $ INSERT INTO libro (titolo, id_studente) VALUES('libro3', 1); $ INSERT INTO libro (titolo, id_studente) VALUES('libro4', 1); $ INSERT INTO libro (titolo, id_studente) VALUES('libro5', 1); $ INSERT INTO libro (titolo, id_studente) VALUES('libro6', 1); E libri per il secondo studente: $ INSERT INTO book (title, student_id) VALUES('book7', 2); $ INSERT INTO libro (titolo, id_studente) VALUES('libro8', 2); $ INSERT INTO libro (titolo, id_studente) VALUES('libro9', 2); $ INSERT INTO libro (titolo, id_studente) VALUES('libro10', 2);

Collegamenti insegnante-studente

Per fare ciò, aggiungi students_x_teachers alla tabella: $ INSERT INTO students_x_teachers VALUES (1,1); $INSERT INTO studenti_x_insegnanti VALUES(1,2); $INSERT INTO studenti_x_insegnanti VALUES(2,3);

Implementiamo la richiesta

Eseguiamo la prima fase: colleghiamo tre tabelle in un record: $ SELECT * FROM insegnante tch INNER JOIN students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.id_studente; Naturalmente non abbiamo ancora nessun record, tuttavia possiamo vedere che abbiamo collegato con successo tre tabelle. Ora aggiungiamo il raggruppamento dei libri, l'ordinamento e i campi di cui abbiamo bisogno: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM student st INNER JOIN book b ON st.id = b.student_id INNER ISCRIVITI students_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN insegnante tch ON tch.id = st_x_tch.teacher_id GRUPPO PER st.id ORDINA PER libri DESC; Ma otteniamo un errore in SQL e la seguente risposta: Codice errore: 1055. L'espressione n. 1 dell'elenco SELECT non è nella clausola GROUP BY e contiene la colonna non aggregata "final_task.tch.last_name" che non dipende funzionalmente dalle colonne in GROUP BY clausola Non funziona prendere questi elementi perché esiste una relazione molti-a-molti tra insegnante e studente. Ed è vero: non possiamo avere un solo insegnante per studente. Quindi andiamo dall'altra parte. Usiamo qualcosa chiamato View SQL. Qual è l'idea: creiamo una vista separata, che è una nuova tabella, già con il raggruppamento di cui abbiamo bisogno. E a questa tabella aggiungeremo i nomi necessari degli insegnanti. Ma teniamo conto del fatto che potrebbero esserci più insegnanti, quindi le voci verranno ripetute. Crea una vista: $ CREATE VIEW studentBooks as SELECT st.last_name,st.id,COUNT(*) as books FROM student st INNER JOIN book b ON st.id=b.student_id GRUPPO PER st.id ORDER BY libri DESC; Successivamente, lavoreremo con questa visualizzazione come una semplice tabella con tre campi: cognome dello studente, ID_studente e conteggio dei libri. In base all'ID dello studente, possiamo anche aggiungere un insegnante attraverso due join: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from studentbook sbw INNER JOIN students_x_teachers stch ON sbw. id = stch.student_id INNER JOIN insegnante tch ON tch.id = stch.teacher_id; E ora il risultato sarà: Huh! Questa è una richiesta, giusto?) È andata come previsto: uno studente con id=1 ha sei libri e due insegnanti, e uno studente con id=2 ha quattro libri e un insegnante.

Compito 10

Condizione: Selezionare 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

Qui possiamo utilizzare una richiesta già pronta dell'attività precedente. Cosa dobbiamo cambiare a riguardo? Abbiamo già questi dati, dobbiamo solo aggiungere un altro raggruppamento e rimuovere il nome dello studente dai dati di output. Ma prima aggiungiamo un altro studente all'insegnante per rendere il risultato più interessante. Per fare questo scriviamo: $ INSERT INTO students_x_teachers VALUES (2, 1); E la query stessa: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN Teacher tch ON tch.id = stch .insegnante_id GRUPPO PER tch.id; Di conseguenza, otteniamo: quell'insegnante Vasilenko ha 10 libri e Shevchenko ne ha 6...)

Compito 11

Condizione: selezionare un 'Insegnante' il cui numero di 'Libri' 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

Qui è dove useremo HAVING. Abbiamo parlato di lui. La richiesta sarà esattamente la stessa di prima, solo che dovrai aggiungere la condizione che il numero di libri rientri in un determinato intervallo. E come ho detto negli articoli precedenti, quando dobbiamo eseguire filtri durante il raggruppamento e/o le funzioni di aggregazione, dobbiamo utilizzare HAVING : $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from studentbook sbw INNER JOIN students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN insegnante tch ON tch.id = stch.teacher_id GRUPPO PER tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; Ho evidenziato la parte che ho aggiunto. E, in effetti, il risultato atteso: solo Vasilenko ha superato questo turno))

Compito 12

Condizione: stampare tutti i 'cognome' e '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

Cioè, dobbiamo combinare due output, e questo è esattamente lo scopo di UNION. In altre parole, prenderemo i record degli studenti e dei docenti e stamperemo insieme: $ SELECT cognome, 'docente' come tipo da insegnante UNION ALL select cognome, 'studente' come tipo da studente ORDER BY cognome; E ci saranno sia insegnanti che studenti. Sembrerebbe che tutto sia semplice, ma è allora che guardiamo già il risultato. E quindi devi indovinare due cose.

Compito 13

Condizione: aggiungere una colonna "tariffa" alla tabella esistente "Studente", che memorizzerà il corso in cui si trova attualmente lo studente (valore numerico da 1 a 6). ALTER TABLE studente ADD CONSTRAINT check_rate CHECK (tasso > 0 AND tasso < 7); Qui stiamo aggiungendo un campo tramite ALTER TABLE e CHECK per impostare il limite su questo campo da 1 a 6.

Compito 14

Condizione: questo articolo non è obbligatorio, ma sarà un vantaggio. Scrivi una funzione che esaminerà tutti i "Libri" e restituirà tutti i "titoli" separati da virgole. Qui basterà restituire a seguito della richiesta una riga, che conterrà tutti i titoli dei libri. Anche in questo caso ho dovuto cercare su Google. Esiste una funzione del genere - GROUP_CONCAT , con la quale ciò viene fatto in modo molto semplice: $ SELECT GROUP_CONCAT(titolo) dal libro; E questo è tutto...)) Tutti i 14 compiti sono pronti.

conclusioni

Uuhhh... Non è stato facile. Era interessante. Ne è valsa la pena, ne sono più che sicuro. Mentre svolgevamo queste attività, abbiamo esaminato molte cose che prima non erano note:

• VISTA SQL
• GRUPPO_CONCAT
• UNIONE

e così via. Grazie a tutti coloro che hanno avuto la forza di leggere e ripetere quello che ho fatto. Chi sa come perfezionare le richieste - scrivi nei commenti, li leggerò sicuramente)

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