Biz verilənlər bazası və SQL dilini təhlil edirik. (6-cı hissə - Yekun tapşırığın yoxlanılması) - "A-dan Z-yə Java layihəsi"

Java layihəsinin yaradılması haqqında silsilədən məqalə (digər materiallara keçidlər sonundadır). Onun məqsədi əsas texnologiyaları təhlil etməkdir, nəticə teleqram botunu yazmaqdır. Bu hissə verilənlər bazasında son tapşırığın təhlilini ehtiva edir. Salam, əziz oxucular. Bu gün verilənlər bazasındakı son məqalədən tapşırığı təhlil edəcəyik. Maraqlıdır, çünki orta səviyyəli müsahibələr üçün nəzərdə tutulub. Yəni bu tapşırığı yerinə yetirdikdən sonra siz artıq müsahibəyə gedə bilərsiniz və siz relational verilənlər bazası ilə bağlı olanların heç olmasa bir hissəsini uğurla keçəcəksiniz. Mən bu məqalənin nə qədər zəruri ola biləcəyini bilirəm və buna görə də bütün təcrübəmi onun faydalı və maraqlı olması üçün sərf edəcəyəm. Məqalənin ortasında yuxuya getməsəniz, bu, məqsədimə çatdığım anlamına gələcək. Bütün tapşırığı təkrarlamayacağam: hər tapşırığı yerinə yetirməzdən əvvəl onu kursivlə vurğulayaraq sitat gətirəcəyəm. Gözləyirəm ki, bu yazını oxuyan hər kəs öz verilənlər bazasındakı bütün sorğuları işlədəcək və eyni şeyi alacaq. Bu, biznesə maksimum fayda gətirəcəkdir. Və çətin işimizdə kiməsə kömək etdiyim düşüncəsindən bir az daha xoşbəxt olacağam)

Məşq 1

Aşağıdakı sahələrlə 'Tələbə' cədvəlini yaratmaq üçün SQL skripti yazın: id (əsas açar), ad, soyad, e_mail (unikal). Biz bunu artıq etdik, ona görə də heç bir problem olmamalıdır. Skriptdə əsas açarı və əsasdan fərqlənən unikal sahəni göstərməlisiniz. Əvvəlcə bu tapşırıq üçün yeni verilənlər bazası yaradaq: $ CREATE DATABASE final_task; Və gəlin bu verilənlər bazasından istifadə edək: $ USE final_task; Ətraf mühit qurulduqdan və işi yerinə yetirməyə hazır olduqdan sonra biz aşağıdakı skripti yaza bilərik: $ CREATE TABLE tələbə ( id INT AUTO_INCREMENT, ad VARCHAR(40), soyad VARCHAR(50), e-poçt VARCHAR(100), PRIMARY KEY ( id), UNİKAL (e-poçt) ); İndiyə qədər keçdiklərimizlə müqayisədə yeni heç nə yoxdur. İstənilən şərh lazımsızdır, davam edək.

Tapşırıq 2-3

Aşağıdakı sahələrlə "Kitab" cədvəlini yaratmaq üçün SQL skripti yazın: id, başlıq (id + başlıq = əsas açar). "Tələbə" və "Kitab"ı "Tələbə" bir-çox "Kitab" əlaqəsi ilə əlaqələndirin. Daha sürətli və daha rahat etmək üçün iki tapşırığı birləşdirək. Ayrı bir xarici açarın necə əlavə olunacağını əvvəlki məqalələrdə artıq müzakirə etmişəm. Əlavə etmək üçün əlaqələri necə və nə ilə qurduğumuzu xatırlamalıyıq. Əvvəlki məqalə sizə kömək edəcək, sonra isə skript budur: $ CREATE TABLE kitab ( id INT, başlıq VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, title), XARİCİ AÇAR (tələbə_id) REFERANSLAR tələbə (id) ) ); Bu sadə şəkildə, cədvəlimizə PRIMARY KEY (id, başlıq) üçün kompozit açar əlavə etdik , indi açar tam olaraq cüt olacaq. Bu o deməkdir ki, cədvəldə birdən çox eyni id sahəsi dəyəri ola bilər. Və başlıq üçün tamamilə eyni.

Tapşırıq 4

Aşağıdakı sahələrlə 'Müəllim' cədvəlini yaratmaq üçün SQL skripti yazın: id (əsas açar), ad, soyad, e_mail (unikal), mövzu. Verilənlər bazamızı sorğular üçün hazırlamağa, müəllim cədvəli yaratmağa davam edirik: $ CREATE TABLE müəllim( id INT AUTO_INCREMENT, ad VARCHAR(30), soyad VARCHAR(30), email VARCHAR(100), mövzu VARCHAR(40), PRIMARY KEY ( id), UNİKAL (e-poçt) ); İndiyə qədər çətin deyil, elə deyilmi? Üç tapşırıq artıq bitdi!

Tapşırıq 5

'Tələbə' və 'Müəllim'i 'Tələbə' çoxdan çox Müəllim' əlaqəsi ilə əlaqələndirin. İndi bu daha maraqlıdır! Keçən dəfə bu haqda danışmışdıq. Buna nail olmaq üçün nəyin lazım olduğunu xatırlatmaq istərdim: tələbə-müəllim cütlərini saxlayacaq aralıq cədvəl yaratmalısınız. Onun köməyi ilə çoxlu-çoxlu əlaqə yaratmaq mümkün olacaq. Buna görə də, cədvəl yaradaq student_x_techers . Adlandırma yanaşması açıqdır və bu da ola bilər: student_teacher . $ CREATE TABLE student_x_teachers (tələbə_id INT NOT NULL, müəllim_id INT NOT NULL, PRIMARY KEY (tələbə_id, müəllim_id), XARİCİ AÇAR (tələbə_id) REFERANSLAR tələbə(id), XARİCİ AÇAR (müəllim_id) REFERANSLAR müəllim(id)); Gördüyünüz kimi, hər şey aydın və ardıcıl şəkildə edilir. İki xarici açar üçün kompozit açarımız var: student_id və teacher_id. Niyə həm də xarici açar? Tələbə və müəllim cədvəllərində qeydə alınan cütlər üçün qeydlərin mövcud olduğuna əmin olaq.

Tapşırıq 6

Soyadında "oro" olan "Tələbə" seçin, məsələn, "Sidorov", "Voronovski". Bizim üçün maraqlı və əyani olması üçün əvvəlcə bir neçə tələbə əlavə etməyi təklif edirəm ki, onlardan bəziləri bu istəyə uyğun olsun, bəziləri isə uyğun gəlmir. Ona görə də sorğu nəticəsində daxil edilməli olanları yazaq : $ INSERT INTO tələbə (ad, soyad, email) DƏYƏRLƏR ('İvan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO tələbə (ad, soyad, email) DƏYƏRLƏR ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Daxil olmamalı olanlar: $ TƏLƏBƏNƏ (ad, soyad, e-poçt) DAXİL EDİN DƏYƏRLƏR ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO tələbə (ad, soyad, email) DƏYƏRLƏR('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Nəticəni yoxlayaq, tələbələr cədvəlindəki məlumatların siyahısına baxaq: $ SELECT * FROM student; və alırıq: Cəmi dörd qeyd var, onlardan ikisi uyğun olmalıdır, ikisi isə uyğun deyil. Sorğu üçün bütün məlumatları hazırladıqdan sonra tapşırığın özü üçün sorğu verə bilərik: $ SEÇİN * FROM student WHERE soyadı LIKE '%oro%'; Nəticədə İvan və Nikolay siyahıdan keçdilər.

Tapşırıq 7

Növbəti tapşırıq, oxuyuruq: 'Tələbə' cədvəlindən bütün soyadları ('soyad_ad') və onların təkrarlarının sayını seçin. Nəzərə alın ki, verilənlər bazasında adlar var. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

Soyad	kəmiyyət
Petrov	15
İvanov	12
Sidorov	3

Aydınlıq üçün daha çox məlumat əlavə etməlisiniz. Sözü uzatmadan qohumluqlarını bilməyən Petrovlar, İvanovlar və Sidorovları da əlavə edək ;) E-poçt ünvanı icad etməyəcəm, sadəcə yeni entrylərdən xaric edəcəm. Aşağıdakı əmri 12 dəfə yerinə yetirək: $ INSERT INTO tələbə (ad, soyad) VALUES ('İvan', 'İvanov'); 15 Petrov əlavə edək: $ INSERT INTO tələbə (ad, soyad) DƏYƏRLƏR ('Petr', 'Petrov'); Və iki Sidorov (bizdə artıq bir var))): $ INSERT INTO tələbə (ad, soyad) DƏYƏRLƏRİ ('Sidor', 'Sidorov'); Məlumat artıq hazırdır. Bu cür məlumatları əldə etmək üçün qruplaşdırma, qruplaşdırmaq üçün isə Group By operatorundan istifadə etməli və bunu soyad_ad sahəsi ilə etməlisiniz. Siz həmçinin qeyd edə bilərsiniz ki, təkrarların sayı kəmiyyət olaraq təyin olunur və burada siz həmçinin SQL-də ləqəblərin necə qurulacağını xatırlamalısınız: $ SELECT soyadı, COUNT(*) kəmiyyət kimi tələbə QRUPUNDAN soyadına görə SİFARİŞ COUNT(*) DESC ; Beləliklə, Petrovlarla həddindən artıq çox oldum - 16 oldu))

Tapşırıq 8

Şərt: "Tələbə"dən ən çox təkrarlanan 3 ad seçin. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

ad	kəmiyyət
İskəndər	27
Sergey	10
Peter	7

Oh, bunun üçün bizdə artıq İvanlar, Peterlər və Sidorlar var. Buna görə də onları əlavə etməyə ehtiyac yoxdur. Biz artıq necə çeşidlənəcəyimizi bilirik. Bu gün danışmadığımız yeganə şey, müəyyən sayda qeydləri necə seçməkdir. Bu, verilənlər bazası problemlərinin əvvəlki həllərində artıq ortaya çıxdı. Oxumayanlar oxusun. Qalanları üçün gəlin birbaşa mətləbə keçək: $ SEÇİN ad, kəmiyyət olaraq COUNT(*) TƏLƏBƏLƏR QRUPUNDAN Ada görə SİFARİŞ COUNT(*) DESC LIMIT 3; Sorğudan göründüyü kimi, SELECT sorğusunda operatorların sırasını bilsəniz, belə sorğunun icrasında heç bir problem olmayacaq. Və bu vəzifə hələ də bizim üzərimizdədir. Və əvvəllər təqdim olunan biliklər bu problemi həll etmək üçün kifayətdir.

Tapşırıq 9

Tapşırıq şərti: Ən çox “Kitab” və əlaqəli “Müəllim” sayına malik olan “Tələbəni” seçin. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

Müəllimin soyadı	tələbənin soyadı	Kitabın miqdarı
Petrov	Sidorov	7
İvanov	Smith	5
Petrov	Kankava	2

Beləliklə, bu vəzifə əvvəlkindən daha çətindir, elə deyilmi? Təəccüblü deyil: qoşulmaq kimi iyi gəlir... və birdən çox) Birincisi, nə edəcəyimizi başa düşməliyik... Aydındır ki, Kitabın kəmiyyəti qruplaşdırma tələb edir. Amma nə? Və niyə onları qruplaşdırmalıyıq? Sorğu üç cədvəli, qruplaşdırmanı və çeşidləməni əhatə edir. Heç bir kitab olmayan qeydlərin göstərilmədiyinə görə, bu, INNER JOIN götürməyiniz lazım olduğunu göstərir. Bununla bağlı heç bir problem olmaması üçün SOL QOŞULUŞ üçün sorğu da edəcəyik. Və bir neçə variant var. Etdiyimiz ilk şey üç cədvəli bir qeyddə birləşdirməkdir. Sonra, biz tələbələri qruplaşdırırıq və ona müəllimin adını əlavə edirik. Nə seçəcəyik? Müəllimin, tələbənin adı və kitabların sayı. Sorğu üçün məlumatları əlavə edək:

• üç müəllim;
• on kitab;
• iki tələbəni üç müəllimlə birləşdirin.

Üç müəllim

$ INSERT INTO müəllim(soyad) DƏYƏRLƏRİ ('Matvienko'); $ INSERT INTO müəllim(soyad) DƏYƏRLƏRİ ('Şevçenko'); $ INSERT INTO müəllim(soyad) DƏYƏRLƏRİ ('Vasilenko');

10 kitab

1-ci və 2-ci tələbələrin şəxsiyyət vəsiqələrini götürəcəm. Onlara kitablar əlavə edəcəm. AUTO_INCREMENT təyin edilmədiyi üçün hər dəfə yeni ID yazmamaq üçün aşağıdakıları etməlisiniz: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Sonra, birinci tələbə üçün kitablar əlavə edin: $ INSERT INTO book (ad, student_id) VALUES('book1', 1); $ INSERT INTO kitab (başlıq, tələbə_id) VALUES('book2', 1); $ INSERT INTO kitaba (başlıq, tələbə_id) VALUES('book3', 1); $ INSERT INTO kitaba (başlıq, tələbə_id) VALUES('book4', 1); $ INSERT INTO kitaba (başlıq, tələbə_id) VALUES('book5', 1); $ INSERT INTO kitaba (başlıq, tələbə_id) VALUES('book6', 1); İkinci tələbə üçün kitablar: $ INSERT INTO book (ad, student_id) VALUES('book7', 2); $ INSERT INTO kitaba (başlıq, tələbə_id) VALUES('book8', 2); $ INSERT INTO kitaba (başlıq, tələbə_id) VALUES('book9', 2); $ INSERT INTO kitaba (başlıq, tələbə_id) VALUES('book10', 2);

Müəllim-şagird əlaqələri

Bunun üçün cədvələ student_x_teachers əlavə edin: $ INSERT INTO student_x_teachers DƏYƏRLƏRİ (1,1); $INSERT INTO student_x_teachers VALUES(1,2); $INSERT INTO student_x_teachers VALUES(2,3);

Müraciəti həyata keçirək

Birinci mərhələni həyata keçiririk - üç cədvəli bir qeyddə birləşdiririk: $ SEÇİN * FROM müəllim tch İNNER JOIN student_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 b kitabçası .id = b.student_id; Əlbəttə ki, hələ heç bir qeydimiz yoxdur, lakin üç cədvəli uğurla birləşdirdiyimizi görə bilərik. İndi biz kitabların qruplaşdırılması, çeşidlənməsi və bizə lazım olan sahələri əlavə edirik: $ 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 QOŞULUN student_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN müəllim tch ON tch.id = st_x_tch.teacher_id QRUP st.id ÜZRƏ SİFARİŞ KİTABLAR AÇIQLA; Lakin biz SQL-də xəta və aşağıdakı cavabı alırıq: Xəta Kodu: 1055. SEÇİM siyahısının №1 ifadəsi GROUP BY bəndində deyil və GROUP BY-dəki sütunlardan funksional olaraq asılı olmayan 'final_task.tch.last_name' ümumiləşdirilməmiş sütunundan ibarətdir. bənd Bu elementləri götürmək işləmir, çünki müəllim və tələbə arasında çoxlu-çoxlu əlaqə var. Və bu doğrudur: hər tələbəyə yalnız bir müəllim tuta bilmərik. Beləliklə, başqa yolla gedək. Gəlin View SQL adlı bir şeydən istifadə edək. İdeya nədir: biz artıq bizə lazım olan qruplaşdırma ilə yeni cədvəl olan ayrıca görünüş yaradırıq. Və bu cədvələ lazım olan müəllim adlarını əlavə edəcəyik. Amma nəzərə alırıq ki, birdən çox müəllim ola bilər, ona görə də yazılar təkrarlanacaq. Görünüş yaradın: $ CREATE VIEW studentBooks as SECH st.last_name,st.id,COUNT(*) as books FROM student st INNER JOIN book b ON st.id=b.student_id QRUP st.id=b.student_id BY QRUP SİFARİŞ KİTABLAR AÇIQLAMA; Sonra, bu görünüşlə üç sahəyə malik sadə cədvəl kimi işləyirik: tələbə soyadı, tələbə_id və kitabların sayı. Tələbənin şəxsiyyət vəsiqəsinə uyğun olaraq biz müəllimi iki birləşmə vasitəsilə də əlavə edə bilərik: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from studentbook sbw INNER JOIN student_x_teachers stch ON sbw. id = stch.student_id INNER JOIN müəllim tch ON tch.id = stch.teacher_id; İndi nəticə belə olacaq: Huh! Bu bir xahişdir, eləmi?) Gözlənildiyi kimi çıxdı: id=1 olan şagirdin altı kitabı və iki müəllimi, id=2 olan şagirdin isə dörd kitabı və bir müəllimi var.

Tapşırıq 10

Şərt: Bütün “Tələbələri” arasında ən çox “Kitab”a malik olan “Müəllim”i seçin. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

Müəllimin soyadı	Kitabın miqdarı
Petrov	9
İvanov	5

Burada əvvəlki tapşırıqdan hazır sorğudan istifadə edə bilərik. Bununla bağlı nəyi dəyişdirməliyik? Bizdə artıq bu məlumat var, sadəcə başqa qruplaşdırma əlavə etmək və tələbənin adını çıxış məlumatlarından silmək lazımdır. Ancaq əvvəlcə nəticəni daha maraqlı etmək üçün müəllimə daha bir şagird əlavə edək. Bunun üçün yazırıq: $ INSERT INTO student_x_teachers DƏYƏRLƏRİ (2, 1); Sorğunun özü də: $ CH.last_name 'Müəllim' olaraq SEÇİN, SUM(sbw.books) 'Kitablar' kimi tələbə kitabından sbw İÇİNƏ QOŞULUN student_x_teachers stch ON sbw.id = stch.student_id INNER JOIN müəllim tch ON tch.id =s müəllim_id QRUP tch.id; Nəticədə əldə edirik: həmin müəllim Vasilenkonun 10 kitabı, Şevçenkonun isə 6 kitabı var...)

Tapşırıq 11

Şərt: Bütün "Tələbələri" üçün "Kitabların" sayı 7 ilə 11 arasında olan "Müəllim" seçin. Kəmiyyətə görə azalan qaydada çeşidləyin. Bu belə görünməlidir:

Müəllimin soyadı	Kitabın miqdarı
Petrov	on bir
Sidorov	9
İvanov	7

HAVING-dən istifadə edəcəyimiz yer budur. Onun haqqında danışdıq. Sorğu əvvəlki kimi tam eyni olacaq, yalnız kitabların sayının müəyyən diapazonda olması şərtini əlavə etməlisiniz. Əvvəlki məqalələrdə dediyim kimi, qruplaşdırma və/yaxud toplama funksiyaları zamanı filtrləmə aparmalı olduğumuz zaman HAVING : $ SELECT tch.last_name 'Müəllim', SUM(sbw.books) kimi 'Kitablar' kimi istifadə etməliyik. tələbə dəftəri sbw DAXİLİ QOŞULUN student_x_teachers stch ON sbw.id = stch.student_id DAXİLİ QOŞULUN müəllim tch ON tch.id = stch.teacher_id tch.id SUM(sbw.books) OLAN QRUP > 6 VƏ CƏMİ(sbw.book) <sbw1; Mən əlavə etdiyim hissəni vurğuladım. Və əslində gözlənilən nəticə: Bu raunddan yalnız Vasilenko keçdi))

Tapşırıq 12

Şərt: "Növ" (tələbə və ya müəllim) sahəsi ilə "Müəllim" və "Tələbə"nin bütün "soyad_adı" və "ad"ını çap edin. 'soyad_adına' görə əlifba sırası ilə çeşidləyin. Bu belə görünməlidir:

Soyad	növü
İvanov	tələbə
Kankava	müəllim
Smith	tələbə
Sidorov	müəllim
Petrov	müəllim

Yəni biz iki çıxışı birləşdirməliyik və BİRLİK məhz bunun üçündür. Başqa sözlə, biz tələbələrdən və müəllimlərdən qeydlər götürüb birlikdə çap edəcəyik: $ SELECT soyad, 'müəllim' növü olaraq müəllim UNİON ALL seçin soyadı, 'tələbə' tipi olaraq tələbədən SİPARİŞ BY soyad; Həm müəllimlər, həm də tələbələr olacaq. Hər şeyin sadə olduğu görünür, amma nəticəyə baxdığımız zaman budur. Və buna görə də iki şeyi təxmin etməlisiniz.

Tapşırıq 13

Şərt: Mövcud "Tələbə" cədvəlinə tələbənin hazırda keçdiyi kursu saxlayacaq "dərəcə" sütununu əlavə edin (1-dən 6-a qədər rəqəmsal dəyər). DEĞİŞTİRİN CƏDVƏL tələbəsi ƏLAVƏ MƏHDUDİYYƏTİ yoxlama_rate CHECK (dərəcə > 0 VƏ dərəcə < 7); Burada biz ALTER TABLE və CHECK vasitəsilə bu sahəyə limiti 1-dən 6-ya qədər təyin etmək üçün sahə əlavə edirik.

Tapşırıq 14

Vəziyyət: Bu element tələb olunmur, lakin bir artı olacaq. Bütün "Kitablar"dan keçəcək və vergüllə ayrılmış bütün "başlıqları" çıxaracaq bir funksiya yazın. Burada sadəcə kitabların bütün adlarını ehtiva edən sorğu nəticəsində bir sətir qaytarmaq lazımdır. Burada yenə google-a müraciət etməli oldum. Belə bir funksiya var - GROUP_CONCAT , bu, çox sadə şəkildə edilir: $ SELECT GROUP_CONCAT(title) from book; Və budur...)) Bütün 14 tapşırıq hazırdır.

nəticələr

Uuhhh... Asan deyildi. Maraqlı idi. Tapşırıqlar buna dəyərdi, mən əminəm. Bu tapşırıqları yerinə yetirərkən əvvəllər məlum olmayan bir çox şeyi nəzərdən keçirdik:

• SQL VIEW
• GROUP_CONCAT
• BİRLİK

və s. Oxumaq və etdiklərimi təkrarlamaq gücü olan hər kəsə təşəkkür edirəm. İstəkləri necə daha yaxşı edəcəyini kim bilir - şərhlərdə yazın, mən onları mütləq oxuyacağam)

Serialdakı bütün materialların siyahısı bu məqalənin əvvəlindədir.