Биз маалымат базаларын жана SQL тилин талдайбыз. (6-бөлүк - жыйынтыктоочу тапшырманы текшерүү) - "Java долбоору Адан Яга чейин"

Java долбоорун түзүү жөнүндө бир катар макала (башка материалдарга шилтемелер аягында). Анын максаты негизги технологияларды талдоо, натыйжасы телеграмма бот жазуу болуп саналат. Бул бөлүк маалымат базасындагы акыркы тапшырманын анализин камтыйт. Саламатсыздарбы, урматтуу окурмандар. Бүгүн биз маалымат базасындагы акыркы макаладагы тапшырманы талдайбыз. Бул кызыктуу, анткени ал Орто деңгээлдеги интервьюларга арналган. Башкача айтканда, бул тапшырмадан кийин сиз маектешүүгө бара аласыз жана реляциялык маалымат базаларына тиешелүү нерселердин жок дегенде бир бөлүгүн ийгorктүү өткөрүп аласыз. Мен бул макаланын канчалык зарыл экенин билем, ошондуктан аны пайдалуу жана кызыктуу кылуу үчүн бардык тажрыйбамды жумшайм. Ал эми макаланын ортосунда уктап калбасаңыз, бул менин максатыма жеттим дегенди билдирет. Мен тапшырманы толугу менен кайталабайм: ар бир тапшырманы аткаруунун алдында анын астын курсив менен сызып, цитата кылам. Бул макаланы окуган ар бир адам өз маалымат базасындагы бардык суроолорду иштетип, ошол эле нерсени алат деп күтөм. Бул бизнеске максималдуу пайда алып келет. Ал эми биздин кыйын ишибизде бирөөгө жардам бердим деген ойдон бир аз кубанып калам)

Exercise 1

Төмөнкү талаалар менен "Студент" tableсын түзүү үчүн SQL скриптин жазыңыз: id (негизги ачкыч), аты, фамorясы, e_mail (уникалдуу). Биз муну эчак эле жасаганбыз, андыктан эч кандай маселе жаралбашы керек. Скриптте сиз негизги ачкычты жана негизгиден айырмаланган уникалдуу талааны көрсөтүшүңүз керек. Биринчиден, бул тапшырма үчүн жаңы маалымат базасын түзөлү: $ CREATE DATABASE final_task; Жана бул маалымат базасын колдонолу: $ USE final_task; Чөйрө орнотулуп, ишти аткарууга даяр болгондон кийин, биз төмөнкү скриптти жаза алабыз: $ CREATE TABLE студенти ( id INT AUTO_INCREMENT, аты VARCHAR(40), фамorясы VARCHAR(50), электрондук почта VARCHAR(100), PRIMARY KEY ( id), UNIQUE (электрондук почта) ); Азырынча биз башынан өткөргөн нерселерге салыштырмалуу жаңы эч нерсе жок. Ар кандай комментарийлер керексиз, келгиле уланталы.

Тапшырма 2-3

Төмөнкү талаалар менен "Китеп" tableсын түзүү үчүн SQL скриптин жазыңыз: id, аталыш (id + аталыш = негизги ачкыч). "Студент" менен "Китепти" "Студент" бирден көпкө "Китеп" байланышы менен байланыштырыңыз. Тезирээк жана ыңгайлуураак кылуу үчүн эки тапшырманы бириктирели. Мен мурунку макалаларда өзүнчө чет элдик ачкычты кантип кошууну талкууладым. Кошумчалай турган болсок, биз байланыштарды кантип жана эмне аркылуу түзгөнүбүздү эстешибиз керек. Мурунку макала сизге жардам берет, анан бул жерде скрипт: $ CREATE TABLE китеби ( id INT, аталыш VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, аталышы), FOREIGN KEY (student_id) РЕФЕРЕНЦИЯЛАР студент (id) )); Ушундай жөнөкөй жол менен, биз tableбызга PRIMARY KEY (id, title) үчүн курама ачкыч коштук , эми ачкыч так түгөй болот. Бул tableда бирден ашык окшош ID талаасынын мааниси болушу мүмкүн дегенди билдирет. Ал эми наам үчүн дал ушундай.

4-тапшырма

Төмөнкү талаалар менен "Мугалим" tableсын түзүү үчүн SQL скриптин жазыңыз: id (негизги ачкыч), аты, фамorясы, e_mail (уникалдуу), тема. Биз маалымат базабызды суроолорго даярдоону улантып жатабыз, мугалимдердин tableсын түзөбүз: $ CREATE TABLE мугалими( id INT AUTO_INCREMENT, аты VARCHAR(30), фамorясы VARCHAR(30), электрондук почта VARCHAR(100), тема VARCHAR(40), PRIMARY KEY ( id), UNIQUE (электрондук почта) ); Азырынча бул кыйын эмес, туурабы? Үч тапшырма бүттү!

5-тапшырма

"Студент" менен "Мугалимди" "Студенттин" көптөн көпкө Мугалим" байланышы менен байланыштырыңыз. Эми бул кызыктуураак! Бул тууралуу акыркы жолу сүйлөшкөнбүз. Буга жетишүү үчүн эмне керек экенин эскертип кетейин: мугалим-студенттин жуптарын сактай турган аралык tableны түзүү керек. Анын жардамы менен көп-көп мамилелерди түзүүгө болот. Ошондуктан, стол түзөлү student_x_techers . Ат коюу ыкмасы ачык жана ошондой эле болушу мүмкүн: student_teacher . $ CREATE TABLE student_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (student_id, teacher_id), FOREIGN KEY (student_id) REREIGN KEY (студенттин_id) REREIGN KEY (студент_id) REREIGN KEY (id), FIREIGN KEY (мугалим_ид) РЕФЕРЕНЦИЯЛАР мугалими(id) ); Көрүнүп тургандай, бардыгы так жана ырааттуу аткарылат. Бизде эки чет өлкөлүк ачкыч үчүн курама ачкыч бар: student_id жана teacher_id. Эмне үчүн ошондой эле чет элдик ачкыч? Жазылып жаткан жуптар үчүн студент жана мугалим tableларындагы жазуулар бар экенине ынануу үчүн.

6-тапшырма

Surnameсында "оро" бар "Студентти" тандаңыз, мисалы "Сидоров", "Вороновский". Бул биз үчүн кызыктуу жана визуалдуу болушу үчүн, мен адегенде бир нече студентти кошууну сунуштайм, алардын айрымдары бул суроого ылайыктуу болсо, кээ бири ылайыктуу эмес. Ошондуктан, келгиле, суранычтын жыйынтыгында кимдер киргизorши керек экенин жазып көрөлү: $ INSERT INTO студент (аты-жөнү, фамorясы, электрондук почтасы) БААЛУУЛАР ('Ivan', 'Сидоров', 'ivan.sidorov@gmail.com'); $ INSERT INTO студент (аты-жөнү, фамorясы, электрондук почтасы) БААЛУУЛАР ('Николай', 'Вороновский', 'nikolay.voronovsky@gmail.com'); Ал эми кирбеши керек болгондор: $ INSERT INTO студенттин (аты-жөнү, фамorясы, электрондук почтасы) БААЛУУСУ ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO студент (аты-жөнү, фамorясы, электрондук почтасы) VALUES('Костя', 'Peterов', 'kostya.petrov@gmail.com'); Жыйынтыгын текшерип көрөлү, студенттердин tableсындагы маалыматтардын тизмесин карап көрөлү: $ SELECT * FROM student; жана биз алабыз: Бардыгы болуп төрт жазуу бар, алардын экөө туура келиши керек жана экөө туура келбейт. Сурам үчүн бардык маалыматтарды даярдап, биз тапшырманын өзүнө сурам жасай алабыз: $ SELECT * FROM student WHERE фамorя LIKE '%oro%'; Натыйжада Ivan менен Николай тизмеден өтүштү.

7-тапшырма

Кийинки тапшырма, биз окуйбуз: 'Студент' tableсынан бардык фамorяларды ('фамorя_аты') жана алардын кайталануу санын тандаңыз. Маалыматтар базасында ысымдар бар экенин карап көрөлү. Саны боюнча азаюу тартибинде иреттөө. Бул төмөнкүдөй болушу керек:

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

Түшүнүктүү болушу үчүн, көбүрөөк маалымат кошуу керек. Туугандыгын билбеген Peterовдорду, Ivanовдорду, Сидоровдорду дагы кеп кылбай эле коелу ;) Электрондук addressти ойлоп таппайм, жаны жазуулардан чыгарып салам. Төмөнкү буйрукту 12 жолу аткаралы: $ INSERT INTO студент (аты-жөнү, фамorясы) VALUES ('Ivan', 'Ivanов'); 15 Peterовду кошолу: $ INSERT INTO студент (аты-жөнү, фамorясы) БААЛУУСУ («Peter», «Peterов»); Ал эми эки Сидоров (бизде бирөө бар))): $ INSERT INTO студент (аты-жөнү, фамorясы) VALUES ('Сидор', 'Сидоров'); Маалымат азыр даяр. Мындай маалыматтарды алуу үчүн сиз топко бөлүү керек, топтоо үчүн сиз Group By операторун колдонушуңуз керек жана муну фамorя талаасы боюнча аткарышыңыз керек. Сиз ошондой эле кайталоолордун саны сан катары көрсөтүлгөнүн байкасаңыз болот , жана бул жерде сиз SQLде лакап аттарды кантип жасоону унутпашыңыз керек: $ SELECT фамorя, COUNT(*) саны катары Студенттердин ТОБУНАН фамorясы боюнча ТАРТИП COUNT(*) DESC ; Ошентип, мен аны Peterовдор менен ашыкча кылдым - 16 болуп чыкты))

Тапшырма 8

Шарт: "Студент" ичинен эң көп кайталанган 3 ысымды тандаңыз. Саны боюнча азаюу тартибинде иреттөө. Бул төмөнкүдөй болушу керек:

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

Оо, бул үчүн бизде Ivanдар, Питерлер жана Сидорлор бар. Ошондуктан аларды кошуунун кереги жок. Кантип сорттош керек экенин билебиз. Биз бүгүн сүйлөшпөгөн бир гана нерсе - бул белгилүү бир сандагы жазууларды кантип тандоо керек. Бул мурунтан эле маалымат базасынын көйгөйлөрүн чечүү үчүн пайда болгон. Окубагандар үчүн окугула. Калгандары үчүн түз сөзгө өтөбүз: $ SELECT Name, COUNT(*) саны катары Студенттик ТОПТОН АТ БЕРҮҮ ТАРТИП БY COUNT(*) DESC LIMIT 3; Суроодон көрүнүп тургандай, эгерде сиз SELECT сурамындагы операторлордун тартибин билсеңиз, мындай суроону аткарууда эч кандай көйгөйлөр болбойт. Ал эми бул милдет дагы биздин мойнубузда. Ал эми буга чейин берилген бorм бул маселени чечүү үчүн жетиштүү болуп саналат.

9-тапшырма

Тапшырма шарты: Эң көп "Китеп" жана ага байланыштуу "Мугалим" бар "Окуучуну" тандаңыз. Саны боюнча азаюу тартибинде иреттөө. Бул төмөнкүдөй болушу керек:

Мугалимдин фамorясы	студенттин фамorясы	Китептин саны
Peterов	Сидоров	7
Ivanов	Смит	5
Peterов	Kankava	2

Демек, бул милдет мурункусуна караганда алда канча татаал, туурабы? Таң калыштуу эмес: кошулуу сыяктуу жыттанып жатат... жана бирден көп) Биринчиден, эмне кылуу керек экенин түшүнүшүбүз керек... Китептин саны топтоого муктаж экени анык. Бирок эмне? Анан эмне үчүн аларды топтошубуз керек? Суроо үч tableны, топтоштурууну жана сорттоону камтыйт. Китептери жок жазуулар көрсөтүлбөгөнүнө караганда, бул сиз INNER JOIN алуу керек дегенди билдирет. Биз ошондой эле СОЛ ЖОЮНУ өтүнүчүн жасайбыз, андыктан бул маселеде эч кандай көйгөй болбойт. Жана бир нече варианттар бар. Эң биринчи биз үч tableны бир жазууга бириктиребиз. Андан кийин, биз окуучу боюнча топтоп, ага мугалимдин атын кошобуз. Биз эмнени тандайбыз? Мугалимдин, окуучунун аты-жөнү жана китептердин саны. Сурам үчүн маалыматтарды кошолу:

• үч мугалим;
• он китеп;
• эки окуучуну үч мугалим менен байланыштыруу.

Үч мугалим

$ INSERT INTO мугалим(фамorясы) БААЛУУСУ ('Матвиенко'); $ INSERT INTO мугалим(фамorясы) БААЛУУСУ ('Шевченко'); $ INSERT INTO мугалим(фамorясы) БААЛУУСУ ('Василенко');

10 китеп

1- жана 2-студенттердин күбөлүктөрүн алам. Мен аларга китептерди тиркейм. AUTO_INCREMENT орнотулбагандыктан, ар бир жолу жаңы идентификатор жазбаш үчүн, сиз төмөнкүлөрдү кылышыңыз керек: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Андан кийин, биринчи студент үчүн китептерди кошуңуз: $ INSERT INTO book (аталышы, student_id) VALUES('book1', 1); $ INSERT INTO book (аталышы, студенттин_идентификатору) VALUES('book2', 1); $ INSERT INTO book (аталышы, студенттин идентификатору) VALUES('book3', 1); $ INSERT INTO book (аталышы, студенттин_идентификатору) VALUES('book4', 1); $ INSERT INTO book (аталышы, студенттин идентификатору) VALUES('book5', 1); $ INSERT INTO book (аталышы, студенттин_идентификатору) VALUES('book6', 1); Ал эми экинчи студент үчүн китептер: $ INSERT INTO book (аталышы, student_id) VALUES('book7', 2); $ INSERT INTO book (аталышы, студенттин идентификатору) VALUES('book8', 2); $ INSERT INTO book (аталышы, студенттин идентификатору) VALUES('book9', 2); $ INSERT INTO book (аталышы, студенттин идентификатору) VALUES('book10', 2);

Мугалим-окуучунун байланыштары

Бул үчүн, tableга student_x_teachers кошуңуз: $ INSERT INTO student_x_teachers БААЛУУЛАР (1,1); $INSERT INTO student_x_teachers VALUES(1,2); $INSERT INTO student_x_teachers VALUES(2,3);

Суранычты ишке ашыралы

Биз биринчи этапты жасайбыз - үч tableны бир жазууга байланыштырабыз: $ SELECT * FROM мугалим tch INNER 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 book .id = b.student_id; Албетте, бизде азырынча эч кандай жазуу жок, бирок биз үч үстөлдү ийгorктүү туташтырганыбызды көрөбүз. Эми биз китептерди топтоо, сорттоо жана бизге керектүү талааларды кошобуз: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) FROM student st INNER JOIN book b ON st.id = b.student_id INNER JOIN student_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN Мугалимге tch ON tch.id = st_x_tch.teacher_id ГРУППА СТ.ИД БОЮНЧА ТАРТИП боюнча китептер DESC; Бирок биз SQLде ката жана төмөнкү жоопту алабыз: Ката codeу: 1055. SELECT тизмесинин №1 туюнтмасы GROUP BY пунктунда эмес жана GROUP BY тилкесинде функционалдык жактан көз каранды болбогон "final_task.tch.last_name" бириктирилбеген тилкени камтыйт. Бул элементтерди алып иштебейт , анткени мугалим менен окуучунун ортосунда көптөн көпкө байланыш бар. Бул чындык: биз бир окуучуга бир эле мугалимди ала албайбыз. Андыктан башка жол менен кетели. Келгиле, View SQL деп аталган нерсени колдонолу. Идея эмнеде: биз өзүнчө көрүнүштү түзөбүз, ал жаңы table, буга чейин эле бизге керек болгон топтор менен. Ал эми бул tableга мугалимдердин керектүү ысымдарын кошобуз. Бирок биз бирден көп мугалим болушу мүмкүн экенин эске алабыз, андыктан жазуулар кайталанат. Көрүнүш түзүү: $ CREATE VIEW studentBooks as SELECT as st.last_name,st.id,COUNT(*) as books FROM student st INNER JOIN book b ON st.id=b.student_id ГРУППА СТ.Ид ТАРТИБИ боюнча китептер DESC; Андан кийин, биз бул көрүнүш менен үч талаадан турган жөнөкөй table катары иштейбиз: студенттин фамorясы, студенттин идентификатору жана китептердин саны. Студенттин идентификаторуна ылайык, биз мугалимди эки бириктирүү аркылуу да кошо алабыз: $ SELECT tch.last_name 'Мугалим', sbw.last_name 'Student', sbw.books 'Китептер' катары студенттик китептен sbw INNER JOIN student_x_teachers sch ON sbw. id = stch.student_id INNER JOIN мугалими tch ON tch.id = stch.teacher_id; Эми натыйжа болот: Huh! Бул сураныч, туурабы?) Күтүлгөндөй болду: id=1 болгон окуучуда алты китеп жана эки мугалим, ал эми id=2 болгон окуучуда төрт китеп жана бир мугалим бар.

10-тапшырма

Шарт: Бардык "Окуучуларынын" ичинен эң көп "Китептерге" ээ болгон "Мугалимди" тандаңыз. Саны боюнча азаюу тартибинде иреттөө. Бул төмөнкүдөй болушу керек:

Мугалимдин фамorясы	Китептин саны
Peterов	9
Ivanов	5

Бул жерде биз мурунку тапшырмадан даяр суроону колдоно алабыз. Бул тууралуу эмнени өзгөртүүбүз керек? Бизде бул маалыматтар мурунтан эле бар, биз жөн гана башка группировканы кошуп, студенттин атын чыгаруу маалыматтарынан алып салышыбыз керек. Бирок адегенде мугалимге дагы бир окуучуну кошуп алалы, натыйжа кызыктуураак болсун. Бул үчүн, биз жазабыз: $ INSERT INTO student_x_teachers БААЛУУЛАР (2, 1); Ал эми суроонун өзү: $ tch.last_name 'Мугалим' катары, SUM(sbw.books) 'Китептер' катары студенттик китептен sbw INNER JOIN student_x_teachers stch ON sbw.id = stch.student_id INNER JOIN мугалими tch ON tch.id =s . teacher_id GROUP BY tch.id; Натыйжада: тиги мугалим Василенконун 10 китеби, ал эми Шевченконун 6 китеби бар...)

11-тапшырма

Шарт: Бардык "Окуучуларынын" "Китептеринин" саны 7ден 11ге чейин болгон "Мугалимди" тандаңыз. Саны боюнча азаюу тартибинде иреттөө. Бул төмөнкүдөй болушу керек:

Мугалимдин фамorясы	Китептин саны
Peterов	он бир
Сидоров	9
Ivanов	7

Бул жерде биз HAVING колдонобуз. Биз ал жөнүндө сүйлөштүк. Сурам мурункудай эле болот, болгону китептердин саны белгилүү бир диапазондо болот деген шартты кошуу керек. Мурунку макалаларда айткандай, топтоо жана/же бириктирүү функцияларын фильтрлөө керек болгондо, биз HAVING : $ SELECT tch.last_name 'Мугалим' катары, SUM(sbw.books) 'Китептер' катары колдонушубуз керек. studentbook sbw INNER JOIN student_x_teachers stch ON sbw.id = stch.student_id INNER JOIN мугалими tch ON tch.id = stch.teacher_id SUMM(sbw.books) БАР tch.id БОЮНЧА ГРУППА > 6 ЖАНА SUM(sbw1) <sbw1; Мен кошкон бөлүгүн бөлүп койдум. Жана чындыгында күтүлгөн жыйынтык: Бул раунддан Василенко гана өттү))

12-тапшырма

Шарт: "Тип" талаасы (студент же мугалим) менен "Мугалимдин" жана "Студенттин" бардык "фамorясы" менен "атын" басып чыгарыңыз. "фамorя_аты" боюнча алфавит боюнча иреттөө. Бул төмөнкүдөй болушу керек:

акыркы аты	түрү
Ivanов	студент
Kankava	мугалим
Смит	студент
Сидоров	мугалим
Peterов	мугалим

Башкача айтканда, биз эки чыгашаны айкалыштырууга тийишпиз, СОЮЗ дал ушул нерсе учун. Башкача айтканда, биз окуучулардан жана мугалимдерден жазууларды алып, чогуу басып чыгарабыз: $ SELECT фамorя, 'мугалим' тип катары мугалим СОЮЗ БАРДЫК фамorяны, 'студентти' фамorяны тандаңыз, окуучунун фамorясы боюнча ORDER БY фамorясы; Анан мугалимдер да, студенттер да болот. Бул баары жөнөкөй көрүнөт, бирок бул биз буга чейин эле натыйжасын карап жатканда. Ошентип, эки нерсени болжолдоо керек.

13-тапшырма

Шарт: Учурдагы "Студент" tableсына "чен" тилкесин кошуңуз, анда студент учурда окуп жаткан курс сакталат (сандык маани 1ден 6га чейин). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (чен > 0 ЖАНА баа < 7); Бул жерде биз ALTER TABLE жана CHECK аркылуу бул талаага 1ден 6га чейин чек коюу үчүн талаа кошуп жатабыз.

Тапшырма 14

Шарт: Бул нерсе талап кылынbyte, бирок плюс болот. Бардык "Китептер" аркылуу өтүп, үтүр менен бөлүнгөн бардык "аталдар" чыгара турган функцияны жазыңыз. Бул жерде сиз жөн гана китептердин бардык аталыштарын камтыган суроо-талаптын натыйжасында сапты кайтарышыңыз керек. Бул жерде дагы мен Google издөөгө туура келди. Мындай функция бар - GROUP_CONCAT , мунун жардамы менен бул абдан жөнөкөй: $ SELECT GROUP_CONCAT(аталышы) китептен; Болду...)) 14 тапшырманын баары даяр.

корутундулар

Ууххх... Бул оңой болгон жок. Бул кызыктуу болду. Тапшырмалар татыктуу болду, мен ишенем. Бул тапшырмаларды аткарып жатып, биз мурда белгисиз болгон көптөгөн нерселерди карап чыктык:

• SQL VIEW
• GROUP_CONCAT
• СОЮЗ

жана башка. Окуп, кылганымды кайталаганга күчү жеткендердин баарына рахмат. Сурамдарды кантип жакшыраак жасоону ким билет - комментарийге жазыңыз, мен аларды сөзсүз окуйм)

Сериядагы бардык материалдардын тизмеси ушул макаланын башында.