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

Java жобасын жасау туралы сериядан мақала (басқа материалдарға сілтемелер соңында). Оның мақсаты – негізгі технологияларды талдау, нәтижесі – телеграмма ботын жазу. Бұл бөлімде дерекқордағы соңғы тапсырманың талдауы бар. Сәлеметсіздер ме, құрметті оқырмандар. Бүгін біз дерекқордағы соңғы мақаладағы тапсырманы талдаймыз. Бұл қызықты, себебі ол орта деңгейдегі сұхбаттарға арналған. Яғни, осы тапсырмадан кейін сіз сұхбатқа бара аласыз және сіз реляциялық дерекқорларға қатысты нәрселердің кем дегенде бір бөлігін сәтті өтесіз. Мен бұл мақаланың қаншалықты қажет екенін білемін, сондықтан оны пайдалы және қызықты ету үшін барлық тәжірибемді саламын. Егер сіз мақаланың ортасында ұйықтамасаңыз, бұл менің мақсатыма жеттім дегенді білдіреді. Тапсырманы толығымен қайталамаймын: әр тапсырманы орындамас бұрын курсивпен астын сызып, дәйексөз келтіремін. Мен осы мақаланы оқығандардың барлығы өздерінің дерекқорындағы барлық сұрауларды орындап, бірдей нәрсені алады деп күтемін. Бұл бизнеске максималды пайда әкеледі. Ал қиын жұмысымызда біреуге көмектестім деген ойдан біраз қуанатын боламын)

1-жаттығу

Келесі өрістермен «Студент» кестесін жасау үшін SQL сценарийін жазыңыз: id (негізгі кілт), аты, тегі, электрондық поштасы (бірегей). Біз мұны жасадық, сондықтан ешқандай проблемалар болмауы керек. Сценарийде негізгі кілтті және негізгіден ерекшеленетін бірегей өрісті көрсету керек. Алдымен, осы тапсырма үшін жаңа деректер қорын жасайық: $ CREATE DATABASE final_task; Ал мына дерекқорды қолданайық: $ USE final_task; Орта орнатылып, тапсырманы орындауға дайын болған соң, біз келесі сценарийді жаза аламыз: $ CREATE TABLE студенті ( id INT AUTO_INCREMENT, аты VARCHAR(40), тегі VARCHAR(50), электрондық пошта VARCHAR(100), PRIMARY KEY ( id), UNIQUE (электрондық пошта ) ); Әзірге біздің басынан өткергенімізбен салыстырғанда жаңалық жоқ. Кез келген түсініктеме қажет емес, әрі қарай жүрейік.

2-3-тапсырма

Келесі өрістермен «Кітап» кестесін жасау үшін SQL сценарийін жазыңыз: идентификатор, тақырып (id + тақырып = негізгі кілт). «Студент» пен «Кітапты» «Студент» бір-көп «Кітап» қатынасымен байланыстырыңыз. Оны тезірек және ыңғайлы ету үшін екі тапсырманы біріктірейік. Мен алдыңғы мақалаларда сыртқы кілтті бөлек қалай қосу керектігін талқыладым. Қосу үшін біз байланыстарды қалай және не арқылы жасайтынымызды есте сақтауымыз керек. Алдыңғы мақала сізге көмектеседі, содан кейін мына сценарий: $ CREATE TABLE book ( id INT, тақырып VARCHAR(100), student_id INT DEFAULT NULL, PRIMARY KEY (id, тақырып), FOREIGN KEY (student_id) ӘДЕБИЕТТЕР студент (id) )); Осы қарапайым жолмен біз PRIMARY KEY (id, title) кестемізге құрама кілт қостық , енді кілт дәл жұп болады. Бұл кестеде бірнеше бірдей идентификатор өрісінің мәні болуы мүмкін дегенді білдіреді. Атау үшін де дәл солай.

4-тапсырма

Келесі өрістермен «Мұғалім» кестесін жасау үшін SQL сценарийін жазыңыз: id (негізгі кілт), аты, тегі, электрондық поштасы (бірегей), тақырып. Дерекқорымызды сұраулар үшін дайындауды жалғастырамыз, мұғалім кестесін жасаймыз: $ CREATE TABLE мұғалімі( id INT AUTO_INCREMENT, аты VARCHAR(30), тегі VARCHAR(30), электрондық пошта VARCHAR(100), тақырып VARCHAR(40), PRIMARY KEY ( id), UNIQUE (электрондық пошта) ); Әзірге қиын емес, солай ма? Үш тапсырма аяқталды!

5-тапсырма

«Студент» және «Мұғалім» сөздерін «Оқушы» көп-көп мұғалім» қатынасымен байланыстырыңыз. Енді бұл қызықтырақ! Біз бұл туралы өткен жолы ғана айттық. Бұған қол жеткізу үшін не қажет екенін еске салайын: мұғалім мен студент жұптарын сақтайтын аралық кесте жасау керек. Оның көмегімен көп-көп қатынасын құруға болады. Сондықтан, student_x_techers кестесін құрайық . Атау тәсілі ашық және келесідей болуы мүмкін: student_teacher . $ CREATE TABLE student_x_teachers ( student_id INT NOT NULL, teacher_id INT NOT NULL, PRIMARY KEY (студент_идентификаторы, мұғалім_идентификаторы), СЫРТҚЫ КІЛТІ (студент_идентификаторы) РЕФЕРЕНЦИЯЛАР студенті(идентификаторы), СЫРТҚЫ КІЛТІ (мұғалім_идентификаторы) ӘДЕБИЕТТЕР мұғалімі(id) ); Көріп отырғаныңыздай, бәрі анық және жүйелі түрде орындалады. Бізде екі шетелдік кілт үшін құрама кілт бар: student_id және teacher_id. Неліктен шетелдік кілт? Жазылып жатқан жұптар үшін студенттер мен мұғалім кестелеріндегі жазбалар бар екеніне көз жеткізу үшін.

6-тапсырма

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

7-тапсырма

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

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

Түсінікті болу үшін қосымша деректерді қосу керек. Көп ұзамай-ақ туыстық қатынасын білмейтін Peterовтар, Ivanовтар, Сидоровтар отбасын қосайық ;) Электрондық address ойлап таппаймын, жаңа жазбалардан алып тастаймын. Келесі пәрменді 12 рет орындаймыз: $ INSERT INTO студент (аты-жөні, тегі_аты) VALUES («Ivan», «Ivanов»); 15 Peterов қосайық: $ INSERT INTO студент (аты-жөні, тегі_аты) VALUES («Peter», «Peterов»); Ал екі Сидоров (бізде біреу бар))): $ INSERT INTO студент (аты-жөні, тегі) VALUES («Сидор», «Сидоров»); Деректер қазір дайын. Мұндай деректерді алу үшін топтастыруды орындау керек, топтау үшін Group By операторын пайдалану керек және мұны фамorя өрісі бойынша орындау керек. Сондай-ақ, қайталану саны сан ретінде белгіленетінін байқай аласыз және бұл жерде SQL-де бүркеншік аттарды қалай жасау керектігін де есте сақтау қажет: $ ТАҢДАУ фамorя, COUNT(*) саны ретінде студенттер тобынан фамorя бойынша ТАПСЫРУ COUNT(*) DESC ; Сондықтан мен оны Peterовтармен асыра орындадым - 16 болды))

8-тапсырма

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

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

О, бұл үшін бізде Ivanдар, Питерлер және Сидорлар бар. Сондықтан оларды қосудың қажеті жоқ. Біз қалай сұрыптау керектігін білеміз. Бүгін біз айтпаған жалғыз нәрсе - жазбалардың белгілі бір санын қалай таңдау керек. Бұл дерекқор проблемаларының алдыңғы шешімдерінде пайда болды. Оқымағандар оқысын. Қалғаны үшін тікелей мәселеге көшейік: $ SELECT name, COUNT(*) саны ретінде FROM Student GROUP BY BY COUNT ROUP BY COUNT(*) DESC LIMIT 3; Сұраудан көрініп тұрғандай, ТАҢДАУ сұранысындағы операторлардың ретін білсеңіз, мұндай сұранысты орындауда қиындықтар болмайды. Ал бұл міндет әлі де өзімізге байланысты. Ал бұрын берілген білім бұл мәселені шешуге жеткілікті.

9-тапсырма

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

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

Демек, бұл тапсырма бұрынғыға қарағанда қиынырақ екені анық, солай емес пе? Таңқаларлық емес: қосылу сияқты иіс шығады... және біреуден көп) Біріншіден, не істеу керектігін түсінуіміз керек... Кітаптың саны топтастыруды қажет ететіні анық. Бірақ не? Неліктен біз оларды топтастыруымыз керек? Сұрау үш кестені, топтауды және сұрыптауды қамтиды. Кітаптар жоқ жазбалар көрсетілмейтініне қарағанда, бұл сізге INNER JOIN алу керек дегенді білдіреді. Сондай-ақ, біз LEFT JOIN сұрауын жасаймыз, сонда ешқандай проблемалар туындамайды. Және бірнеше нұсқалар бар. Біз жасайтын бірінші нәрсе - үш кестені бір жазбаға біріктіру. Содан кейін біз оқушы бойынша топтасамыз және оған мұғалімнің атын қосамыз. Біз нені таңдаймыз? Мұғалімнің аты-жөні, оқушы және кітаптар саны. Сұраныс үшін деректерді қосамыз:

• үш мұғалім;
• он кітап;
• екі оқушыны үш мұғаліммен байланыстыру.

Үш мұғалім

$ INSERT INTO мұғалім(фамorясы) VALUES («Матвиенко»); $ INSERT INTO оқытушы(фамorясы) VALUES («Шевченко»); $ INSERT INTO мұғалім(фамorясы) VALUES («Василенко»);

10 кітап

Мен 1-ші және 2-ші студенттердің жеке куәліктерін аламын. Мен оларға кітаптар қоса беремін. AUTO_INCREMENT орнатылмағандықтан, әр уақытта жаңа идентификатор жазбау үшін келесі әрекеттерді орындау керек: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Содан кейін бірінші студентке кітаптарды қосыңыз: $ INSERT INTO book (атауы, студент_идентификаторы) 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 (атауы, студент_идентификаторы) VALUES('book7', 2); $ INSERT INTO book (атауы, студент_идентификаторы) VALUES('book8', 2); $ INSERT INTO book (атауы, студент_идентификаторы) VALUES('book9', 2); $ INSERT INTO book (атауы, студент_идентификаторы) VALUES('book10', 2);

Мұғалім мен оқушы арасындағы байланыс

Ол үшін кестеге student_x_teachers қосыңыз: $ INSERT INTO student_x_teachers VALUES (1,1); $INSERT INTO student_x_teachers VALUES(1,2); $INSERT INTO student_x_teachers VALUES(2,3);

Өтінішті орындайық

Бірінші кезеңді орындаймыз – үш кестені бір жазбаға байланыстырамыз: $ SELECT * FROM teacher 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кітап .id = b.student_id; Әрине, бізде әлі ешқандай жазба жоқ, бірақ біз үш кестені сәтті байланыстырғанымызды көреміз. Енді біз кітапты топтауды, сұрыптауды және бізге қажет өрістерді қосамыз: $ 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 GROUP BY st.id ТАПСЫРЫС БОЙЫНША кітаптар DESC; Бірақ біз SQL жүйесінде қатені және келесі жауапты аламыз: Қате codeы: 1055. SELECT тізімінің №1 өрнегі GROUP BY тармағында жоқ және GROUP BY бағандарына функционалдық тәуелді емес "final_task.tch.last_name" жиынтықталмаған бағанынан тұрады. тармағы Бұл элементтерді қабылдаңыз, бұл жұмыс істемейді, өйткені мұғалім мен оқушы арасында көптен көпке қатынас бар. Бұл рас: біз бір оқушыға бір ғана мұғалім ала алмаймыз. Ендеше басқа жолмен жүрейік. View SQL деп аталатын нәрсені қолданайық. Идеясы неде: біз жаңа кесте болып табылатын жеке көріністі жасаймыз, қазірдің өзінде бізге қажет топтау. Ал бұл кестеге мұғалімдердің қажетті есімдерін қосамыз. Бірақ біз бірнеше мұғалім болуы мүмкін екенін ескереміз, сондықтан жазбалар қайталанады. Көрініс жасаңыз: $ 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 GROUP BY BY st.id=b.student_id GROUP BY BY st.id ORDER BY books DESC; Содан кейін біз бұл көрініспен үш өрісі бар қарапайым кесте ретінде жұмыс істейміз: студенттің тегі, студенттің идентификаторы және кітаптар саны. Студенттің идентификаторына сәйкес мұғалімді екі біріктіру арқылы да қосуға болады: $ SELECT tch.last_name 'Teacher', sbw.last_name 'Student', sbw.books ретінде 'Кітаптар' студенттік кітапша sbw INNER JOIN student_x_teachers stch ON sbw. id = stch.student_id INNER JOIN мұғалімі tch ON tch.id = stch.teacher_id; Ал енді нәтиже: Huh! Бұл сұраныс, солай емес пе?) Күткендей болды: id=1 бар оқушыда алты кітап және екі мұғалім, ал id=2 оқушыда төрт кітап және бір мұғалім бар.

10-тапсырма

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

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

Мұнда біз алдыңғы тапсырмадағы дайын сұранысты пайдалана аламыз. Ол үшін нені өзгертуіміз керек? Бізде бұл деректер бар, тек басқа топтауды қосып, шығыс деректерінен студенттің атын алып тастау керек. Бірақ алдымен нәтиже қызықтырақ болу үшін мұғалімге тағы бір оқушыны қосамыз. Ол үшін мынаны жазамыз: $ INSERT INTO student_x_teachers VALUES (2, 1); Ал сұраудың өзі: $ SELECT 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; Нәтижесінде мынаны аламыз: әлгі мұғалім Василенcodeа 10 кітап, ал Шевченcodeа 6...)

11-тапсырма

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

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

Бұл жерде біз HAVING қолданбасын қолданамыз. Біз ол туралы сөйлестік. Сұраныс бұрынғыдай болады, тек кітаптар саны белгілі бір диапазонда болатын шартты қосу керек. Алдыңғы мақалаларда айтқанымдай, топтау және/немесе жинақтау функциялары бойынша сүзгілеуді жасау қажет болғанда, біз HAVING : $ SELECT 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 = stch.teacher_id CH.id БОЙЫНША ТОПТАУ СУМ(sbw.books) > 6 ЖӘНЕ СУММ(sbw.books) <sbw1; Мен қосқан бөлікті бөлектеп қойдым. Ал, шын мәнінде, күтілетін нәтиже: Бұл айналымнан тек Василенко өтті))

12-тапсырма

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

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

Яғни, біз екі нәтижені біріктіруіміз керек, және дәл осы UNION үшін қажет. Басқаша айтқанда, біз студенттерден және мұғалімдерден жазбаларды алып, бірге басып шығарамыз: $ ТАҢДАУ фамorясын, мұғалімнің түрі ретінде 'мұғалім' UNION БАРЛЫҚ фамorясын, студенттің түрі ретінде 'студентті' ТАПЫРЫС БY фамorя; Және мұғалімдер де, студенттер де болады. Барлығы қарапайым болып көрінеді, бірақ біз нәтижеге қарап отырмыз. Сондықтан сіз екі нәрсені болжауыңыз керек.

13-тапсырма

Шарты: Студент ағымдағы курсты сақтайтын бар «Студент» кестесіне «баға» бағанын қосыңыз (сандық мән 1-ден 6-ға дейін). ALTER TABLE student ADD CONSTRAINT check_rate CHECK (бағалау > 0 ЖӘНЕ баға < 7); Мұнда біз ALTER TABLE және CHECK арқылы осы өріске 1-ден 6-ға дейін шектеу қою үшін өрісті қосамыз.

14-тапсырма

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

қорытындылар

Уххх... Бұл оңай болған жоқ. Бұл қызықты болды. Тапсырмалар лайықты болды, мен сенімдімін. Осы тапсырмаларды орындау барысында біз бұрын белгісіз көптеген нәрселерді қарастырдық:

• SQL КӨРІНІСІ
• GROUP_CONCAT
• ОДА

тағыда басқа. Менің істегенімді оқып, қайталауға күш-қуаты бар барлық адамдарға рахмет. Сұраныстарды қалай жақсырақ жасау керектігін кім біледі - түсініктемелерде жазыңыз, мен оларды міндетті түрде оқимын)

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