Біз мәліметтер қорын және SQL тілін талдаймыз. (5-бөлім – қосылыстар мен қосылыстар) – «А-дан Я-ға дейінгі Java жобасы»

Java жобасын жасау туралы сериядан мақала (басқа материалдарға сілтемелер соңында). Оның мақсаты – негізгі технологияларды талдау, нәтижесі – телеграмма ботын жазу.

• Мәліметтер қорындағы қатынас түрлері
  • Бірден көпке
  • Бірден-бірге (бірден-бірге)
  • Көптен көпке
• Қосылымдар (қосылулар)
  • ІШКІ ҚОСЫЛУ
  • СОЛ ҚОСЫЛУ
  • ОҢ ҚОСЫЛУ
  • Біріктірулерді қорғау
• Үй жұмысы

Барлығына сәлем, бағдарламалық жасақтаманың болашақ ағалары мен аға буындары. Өткен бөлімде айтқанымдай ( үй тапсырмасын тексеру ), бүгін жаңа материал болады. Әсіресе құлшыныс танытатындар үшін мен бәрін білетіндер де, білмейтіндер де, бірақ google-де іздегісі келетіндер тәжірибе жасап, дағдыларын сынауы үшін қызықты үй тапсырмасын қазып алдым. Бүгін біз жалғаулар мен жалғаулардың түрлері туралы айтатын боламыз.

Мәліметтер қорындағы қатынас түрлері

Қарым-қатынастардың не екенін түсіну үшін сыртқы кілттің не екенін есте сақтау керек. Ұмытып кеткендер үшін серияның басына қош келдіңіз .

Бірден көпке

Елдер мен қалалар туралы мысалды еске түсірейік. Қаланың ел болуы керек екені анық. Елді қаламен қалай байланыстыруға болады? Әрбір қалаға ол тиесілі елдің бірегей идентификаторын (ID) тіркеу керек: біз мұны жасап қойдық. Бұл қосылыс түрлерінің бірі деп аталады – біреуден көпке (ағылшынша нұсқасын – бір-көпке де білу жақсы болар еді). Басқаша айтқанда, біз айта аламыз: бірнеше қала бір елге тиесілі болуы мүмкін. Міне, сіз мұны есте сақтауыңыз керек: бір-көп арасындағы қарым-қатынас. Әзірге түсінікті, солай ма? Егер жоқ болса, онда Интернеттен алынған бірінші сурет: Мұнда тұтынушылар мен олардың тапсырыстары бар екенін көрсетеді. Бір тұтынушының бірнеше тапсырысы болуы мүмкін екендігі мағынасы бар. Бірден көпке бар :) Немесе басқа мысал: Үш кесте бар: баспагер, автор және кітап. Банкротқа ұшырағысы келмейтін, табысты болғысы келетін әрбір баспаның авторы біреу емес, келісесіз бе? Өз кезегінде әрбір автордың бірнеше кітабы болуы мүмкін – бұған да күмән келтіруге болмайды. Бұл тағы да бір автордың көп кітаппен, бір баспаның көп автормен байланысын білдіреді . Бұдан да көп мысал келтіруге болады. Алғашында қабылдаудың қиындығы абстрактілі ойлауды үйренуде ғана болуы мүмкін: үстелдерге және олардың өзара әрекеттестігіне сырттан қарау.

Бірден-бірге (бірден-бірге)

Бұл бір-көп байланысының ерекше жағдайы деп айтуға болады. Бір кестедегі бір жазба басқа кестедегі бір ғана жазбамен байланысты болатын жағдай. Өмірден қандай мысал келтіруге болады? Егер көп әйел алуды алып тастасақ, онда ерлі-зайыптылардың арасында бір-бірлік қатынас бар деп айтуға болады. Көп әйел алуға рұқсат деп айтсақ та, әр әйелде бір ғана күйеу болуы мүмкін. Ата-аналар туралы да солай деуге болады. Әр адамның тек бір биологиялық әкесі және бір биологиялық анасы болуы мүмкін. Айқын бір-біріне қатынасы. Осыны жазып жатқанда маған бір ой келді: егер оларда бір-бір қатынас болса, онда неге бір-бір қатынасты әртүрлі кестелердегі екі жазбаға бөлу керек? Жауабын өзім ойлап таптым. Бұл жазбалар басқа жолдармен басқа жазбалармен де байланыстырылуы мүмкін. Мен не туралы айтып отырмын? Ел мен президент арасындағы жеке байланыстың тағы бір мысалы. «Ел» кестесіне президент туралы барлық деректерді жазуға болады ма? Иә, мүмкін, SQL бір сөз айтпайды. Бірақ егер сіз президентті де адам деп ойласаңыз... Оның да әйелі (басқа бір-біріне қарым-қатынасы) және балалары (тағы бір-көп қарым-қатынасы) болуы мүмкін, содан кейін бұл болады екен. елді президенттің әйелі мен балаларымен байланыстыру қажет... Ақылсыз естіледі, солай ма? :D Бұл байланысқа көптеген басқа мысалдар келтіруге болады. Оның үстіне, мұндай жағдайда «бірден көпке» қатынасынан айырмашылығы, екі кестеге де сыртқы кілт қосуға болады.

Көптен көпке

Қазірдің өзінде атауға негізделген, сіз не туралы сөйлесетінімізді болжай аласыз. Көбінесе өмірде және біз өз өмірімізді бағдарламалаймыз, жоғарыда аталған байланыс түрлері бізге қажет нәрселерді сипаттау үшін жеткіліксіз болатын жағдайлар болады. Баспагерлер, кітаптар мен авторлар туралы жоғарыда айттық. Мұнда көптеген байланыстар бар... Әрбір басылымда бірнеше авторлар болуы мүмкін - бір-көп байланысы. Бұл ретте әр автордың бірнеше баспасы болуы мүмкін (неге болмасқа, жазушы бір жерде шықты, ақшаға таласып, басқа баспаға кетті, мысалы). Және бұл тағы да бір-көп қатынасы. Немесе бұл: әр автордың бірнеше кітабы болуы мүмкін, бірақ әр кітапта бірнеше авторлар болуы мүмкін. Тағы да автор мен кітап, кітап пен автор арасындағы бір-көп қатынасы. Бұл мысалдан біз неғұрлым ресми қорытынды жасай аламыз: SQL-де алдыңғы қосылым түрлерін қалай орнату керектігі түсінікті болды: біз оның идентификаторын сол жазбаларға береміз, олардың саны көп, солай емес пе? Бір ел өзінің жеке куәлігін көптеген қалаларға шетелдік кілт ретінде береді. «Көптен көпке» қатынасымен не істеу керек ? Бұл әдіс қолайлы емес. Біз екі кестені байланыстыратын басқа кестені қосуымыз керек. Мысалы, MySQL-ке барайық, жаңа көптеген мәліметтер қорын құрайық, екі кестені, автор мен кітапты құрайық, онда тек атаулар мен олардың идентификаторлары болады: CREATE DATABASE manytomany; көптеген пайдалану; CREATE TABLE авторы (id INT AUTO_INCREMENT, аты VARCHAR(100), PRIMARY KEY (id) ); CREATE TABLE book( id INT AUTO_INCREMENT, аты VARCHAR(100), PRIMARY KEY (id) ); Енді біздің авторлық және кітап кестелерімізден екі сыртқы кілті болатын үшінші кестені құрайық және бұл сілтеме бірегей болады. Яғни, бірдей кілттермен жазбаны екі рет қосу мүмкін болмайды: CREATE TABLE авторлары_x_кітаптар ( book_id INT NOT NULL, автор_id INT NOT NULL, FOREIGN KEY (book_id) REFERENCES book(id), FOREIGN KEY (author_id) СІЛТЕМЕЛЕР авторы (id ), UNIQUE (кітап_идентификаторы, автор_идентификаторы) ); Мұнда біз бөлек түсініктеме беруді қажет ететін бірнеше жаңа мүмкіндіктерді қолдандық:

• NOT NULL өріс әрқашан толтырылуы керек дегенді білдіреді, ал егер толтырмасақ, SQL бізге бұл туралы айтады;
• UNIQUE өріс немесе өрістер жиыны кестеде бірегей болуы керек екенін айтады. Бірегей идентификатордан басқа, әрбір жазба үшін тағы бір өріс бірегей болуы керек болатын жағдайлар жиі кездеседі. Ал UNIQUE дәл осы мәселеге жауапты.

Менің тәжірибемнен: ескі жүйеден жаңасына өткенде, біз әзірлеушілер ретінде онымен жұмыс істеу және өзімізді құру үшін ескі жүйенің идентификаторларын сақтауымыз керек. Неліктен өзіңізді жасап, ескісін қолданбайсыз? Олар жеткілікті бірегей болмауы мүмкін немесе идентификаторларды жасаудың бұл тәсілі енді өзекті және шектеулі болмауы мүмкін. Осы мақсатта біз ескі ID-атын кестеде бірегей етіп жасадық. Мұны тексеру үшін деректерді қосу керек. Кітап пен авторды қосу: NSERT INTO book (аты) VALUES («кітап1»); INSERT INTO автор (аты) VALUES («автор1»); Олардың 1 және 1 идентификаторлары болатынын біз алдыңғы мақалалардан бұрыннан білеміз. Сондықтан үшінші кестеге бірден жазба қосуға болады: INSERT INTO authors_x_books VALUES (1,1); Және соңғы пәрменді қайтадан қайталағымыз келгенше бәрі жақсы болады: яғни сол идентификаторларды қайтадан жазып алыңыз: Нәтиже табиғи болады - қате. Көшірме болады. Жазба жазылмайды. «Көптен көпке» байланысы осылай құрылады... Мұның бәрі өте керемет және қызықты, бірақ логикалық сұрақ туындайды: бұл ақпаратты қалай алуға болады? Әртүрлі кестелердегі деректерді қалай біріктіріп, бір жауап алуға болады? Бұл туралы келесі бөлімде айтатын боламыз))

Қосылымдар (қосылулар)

Алдыңғы бөлімде мен сізді біріктірулердің не екенін және оларды қайда қолдану керектігін бірден түсінуге дайындадым. Өйткені түсіністік пайда болғаннан кейін бәрі бірден қарапайым болып, қосылыстар туралы барлық мақалалар нәрестенің көзі сияқты анық болатынына терең сенімдімін :D Жалпы және жалпы алғанда, біріктірулер бірнеше кестелерден нәтиже алуда. JOIN (ағылшын тілінен қосылу). Және бұл бәрі...) Ал қосылу үшін кестелер біріктірілетін өрісті көрсету керек. Шайтан боялғандай қорқынышты емес, солай емес пе?) Әрі қарай, біз қосылыстың қандай түрлері бар және оларды қалай пайдалану керектігі туралы сөйлесетін боламыз. Біріктірудің көптеген түрлері бар, біз олардың барлығын қарастырмаймыз. Тек бізге шынымен қажет нәрселер. Сондықтан бізді Cross және Natural сияқты экзотикалық қосылыстар қызықтырмайды. Мен мүлдем ұмытып кеттім, біз тағы бір нюансты есте сақтауымыз керек: кестелер мен өрістерде бүркеншік аттар - бүркеншік аттар болуы мүмкін. Олар біріктіру үшін ыңғайлы қолданылады. Мысалы, мұны істеуге болады: SELECT * FROM table1; егер сұрау жиі table1 қолданатын болса, онда оған бүркеншік ат беруге болады: SELECT* FROM table1 t1 ретінде; немесе жазу оңайырақ: SELECT * FROM table1 t1; содан кейін сұрауда t1-ді осы кесте үшін бүркеншік ат ретінде пайдалануға болады .

ІШКІ ҚОСЫЛУ

Ең кең таралған және қарапайым қосылу. Онда бізде екі кесте және оны біріктіруге болатын өріс болған кезде, екі кестеде қатынасы бар барлық жазбалар таңдалатынын айтады. Әйтеуір айту қиын болды. Мысалға қарайық: Қалаларымыздың дерекқорына бір жазбаны қосамыз. Бір жазба қалалар үшін, екіншісі елдер үшін: $ INSERT INTO country VALUES(5, "Өзбекстан", 34036800); және $ INSERT INTO қала (аты, халқы) VALUES("Тбorси", 1171100); Біз кестемізде қала жоқ елді және кестемізде елмен байланысы жоқ қаланы қостық. Сонымен, INNER JOIN екі кестедегі қосылымдар үшін барлық жазбаларды шығарумен айналысады. Екі кестені кесте1 мен кестені біріктіргіміз келгенде жалпы синтаксис осылай көрінеді: SELECT * FROM table1 t1 INNER JOIN table2 ON t1.id = t2.t1_id; содан кейін екі кестеде қатынасы бар барлық жазбалар қайтарылады. Біздің жағдайда, біз қалалармен қатар елдер туралы ақпаратты алғымыз келгенде, ол келесідей болады: $ SELECT * FROM city ci INNER JOIN country co ON ci.country_id = co.id; Мұнда атаулары бірдей болғанымен, алдымен қалалардың өрісі, одан кейін елдердің өрісі келетінін анық байқауға болады. Бірақ жоғарыда біз қосқан екі жазба жоқ. Өйткені INNER JOIN дәл осылай жұмыс істейді.

СОЛ ҚОСЫЛУ

Көрші кестеде ол үшін жазба болмағандықтан, негізгі кестенің өрістерін жоғалту бізді қанағаттандырмайтын жағдайлар бар және жиі кездеседі. LEFT JOIN - бұл үшін. Алдыңғы сұрауымызда ІШКІ емес, СОЛ дегенді көрсетсек, жауапта басқа қаланы қосамыз - Тбorси: $ SELECT * FROM city ci LEFT JOIN country co ON ci.country_id = co.id; Тбorси туралы жаңа жазба пайда болды және елге қатысты барлық нәрсе жоқ . Бұл көбінесе осылай қолданылады.

ОҢ ҚОСЫЛУ

Мұнда LEFT JOIN-тен айырмашылығы болады, өйткені барлық өрістер қосылымда сол жақта емес, оң жақта таңдалады. Яғни, қалалар емес, барлық елдер қабылданады: $ SELECT * FROM city ci RIGHT JOIN country co ON ci.country_id = co.id; Енді бұл жағдайда Тбorси болмайтыны анық, бірақ бізде Өзбекстан болады. Міне осылай…))

Біріктірулерді қорғау

Енді мен сіздерге жасөспірімдердің қосылыстың мәнін түсінетіндігіне сендіру үшін сұхбат алдында сығалайтын әдеттегі суретті көрсеткім келеді: Мұнда барлығы жиынтық түрінде көрсетілген, әрбір шеңбер кесте. Оның боялған жерлері SELECT ішінде көрсетілетін бөліктер. Қарап көрейік:

• INNER JOIN - тек жиындардың қиылысы, яғни екі кестеге - A және B байланыстары бар жазбалар;
• LEFT JOIN — А кестесіндегі барлық жазбалар, соның ішінде А кестесінің қиылысы (байланысы) бар В кестесіндегі барлық жазбалар;
• RIGHT JOIN – LEFT JOIN-ге дәл қарама-қарсы – В кестесіндегі барлық жазбалар және байланысы бар А жазбалары.

Осының бәрінен кейін бұл сурет анық болуы керек))

Үй жұмысы

Бұл жолы тапсырмалар өте қызықты болады және оларды сәтті шешкендердің барлығы SQL жағында жұмыс істеуге дайын екеніне сенімді бола алады! Тапсырмалар шайналмаған және орта буын оқушылары үшін жазылған, сондықтан бұл сізге оңай және жалықтырмайды :) Тапсырмаларды өзіңіз орындауға бір апта уақыт беремін, содан кейін егжей-тегжейлі талдаумен жеке мақала жариялаймын. мен берген тапсырмалардың шешімі туралы.

Нақты тапсырма:

1. Келесі өрістермен «Студент» кестесін жасау үшін SQL сценарийін жазыңыз: id (негізгі кілт), аты, тегі, электрондық поштасы (бірегей).
2. Келесі өрістермен «Кітап» кестесін жасау үшін SQL сценарийін жазыңыз: идентификатор, тақырып (id + тақырып = негізгі кілт). «Студент» пен «Кітапты» «Студент» бір-көп «Кітап» қатынасымен байланыстырыңыз.
3. Келесі өрістермен «Мұғалім» кестесін жасау үшін SQL сценарийін жазыңыз: id (негізгі кілт), аты, тегі, электрондық поштасы (бірегей), тақырып.
4. «Студент» және «Мұғалім» сөздерін «Оқушы» көп-көп мұғалім» қатынасымен байланыстырыңыз.
5. Tagінде «oro» бар «Студентті» таңдаңыз, мысалы, «Sid oro v», «V oro novsky».
6. «Студент» кестесінен барлық фамorяларды («фамorя_аты») және олардың қайталану санын таңдаңыз. Мәліметтер базасында аттар бар екенін ескеріңіз. Саны бойынша кему ретімен сұрыптаңыз. Ол келесідей болуы керек:

   тек	саны
   Peterов	15
   Ivanов	12
   Сидоров	3

7. «Студент» ішінен ең көп қайталанатын 3 есімді таңдаңыз. Саны бойынша кему ретімен сұрыптаңыз. Ол келесідей болуы керек:

   аты	саны
   Александр	27
   Сергей	10
   Peter	7

8. Ең көп «Кітап» және байланысты «Мұғалім» саны бар «Оқушыларды» таңдаңыз. Саны бойынша кему ретімен сұрыптаңыз. Ол келесідей болуы керек:

   Мұғалімнің тегі	студенттің тегі_аты	Кітаптың саны
   Peterов	Сидоров	7
   Ivanов	Смит	5
   Peterов	Канкава	2>

9. Барлық «Оқушы» ішінен ең көп «Кітап» саны бар «Мұғалімді» таңдаңыз. Саны бойынша кему ретімен сұрыптаңыз. Ол келесідей болуы керек:

   Мұғалімнің тегі	Кітаптың саны
   Peterов	9
   Ivanов	5

10. Барлық «Оқушы» үшін «Кітап» саны 7 мен 11 арасында болатын «Мұғалім» таңдаңыз. Саны бойынша кему ретімен сұрыптаңыз. Ол келесідей болуы керек:

    Мұғалімнің тегі	Кітаптың саны
    Peterов	он бір
    Сидоров	9
    Ivanов	7

11. «Түрі» (студент немесе мұғалім) өрісімен «Мұғалім» және «Студент» барлық «фамorясы» мен «атын» басып шығарыңыз. "Tagі_аты" бойынша алфавит бойынша сұрыптаңыз. Ол келесідей болуы керек:

    тек	түрі
    Ivanов	студент
    Канкава	мұғалім
    Смит	студент
    Сидоров	мұғалім
    Peterов	мұғалім

12. Студент ағымдағы курсты сақтайтын бар «Студент» кестесіне «баға» бағанын қосыңыз (1-ден 6-ға дейінгі сандық мән).
13. Бұл элемент міндетті емес, бірақ плюс болады. Барлық «Кітаптар» арқылы өтетін және үтірмен бөлінген барлық «тақырыптарды» шығаратын функцияны жазыңыз.

Қорытынды

Деректер базасы туралы серия біраз созылды. Келісемін. Дегенмен, біз ұзақ жолдан өттік және нәтижесінде біз бұл мәселені білуге ​​​​болады! Оқығандарыңыз үшін рахмет, жобаны әрі қарай жалғастырғысы келетіндердің барлығы GitHub сайтында тіркелгі жасап, менің аккаунтыма жазылу керек екенін еске саламын :) Алда тағы да - Maven және Docker туралы сөйлесейік. Оқығаныңыз үшін барлығына рахмет. Тағы да қайталаймын: жүрген адам жолды меңгереді;)

Сериядағы барлық материалдардың тізімі осы мақаланың басында.