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

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

• Үй тапсырмасын тексеру
  • Негізгі кілтті қосу
  • Молдованы қосу
  • Қалалар кестесін құру
  • Қалаларға шетелдік кілт қосыңыз
  • Бонус бөлігі. Тестілеу
• SELECT мәлімдемесі
  • WHERE параметрі
  • ORDER BY параметрі
  • GROUP BY параметрі
• Үй жұмысы
• Қорытынды

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

Үй тапсырмасын тексеру

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

1. Ел кестесінің схемасына ID өрісінен бастапқы кілтті (PRIMARY KEY) қосу керек.
2. Елдер кестесіне тағы бір елді қосыңыз – Молдова.
3. Алдыңғы мақаланың схемасына сәйкес, сипатталған барлық өрістерді қамтитын кесте қаласын жасаңыз. Өріс атаулары келесідей болады: идентификатор, атау, ел_идентификаторы, халық.
4. Қалалық кестеге бастапқы кілтті қосыңыз.
5. Қалалық кестеге шетелдік кілт қосыңыз.

Бастау үшін алдыңғы мақаланың бірінші бөлігін қолданып, мәліметтер базасының терминалына өтейік.

Негізгі кілтті қосу

Бастапқы кілтті (PRIMARY KEY) екі жолмен қосуға болады: бірден кестені құру кезінде немесе жасалғаннан кейін ALTER TABLE көмегімен.

Кестені құру кезіндегі негізгі кілт

Біз кестені жасап қойғандықтан және оны жоймай-ақ осы дерекқорда бұл тәсілді көрсете алмайтындықтан, біз жай ғана уақытша сынақ деректер базасын жасаймыз, онда біз бәрін жасаймыз. Келесі командаларды енгізейік:

• жаңа мәліметтер базасын құру:

  $CREATE DATABASE тесті;

• бастапқы кілтті қосу арқылы кесте жасаңыз:

  $ CREATE TABLE елі (id INT, аты VARCHAR(30), PRIMARY KEY (id));

Жалпы, күрделі ештеңе жоқ. Айнымалы мәндерді жариялағаннан кейін келесі PRIMARY KEY (id) бөлігі қосылады , мұнда бастапқы кілт болатын өрістің атауы жақшаға беріледі. Ал кесте схемасы қалай өзгергенін көрейік: $ DESC елі; Көріп отырғаныңыздай, PRI мәні идентификатор жазбасының кілт өрісінде пайда болды .

Кестені құрудан кейінгі негізгі кілт

Жоғарыда айтқанымдай, кестені жасағаннан кейінгі бірінші кілтті ALTER TABLE көмегімен тағайындауға болады . Біз бұл мысалды қалаларымыздың дерекқорында іске қосамыз :

• сынақтан біздің дерекқорымызға барайық:

  $USE қалалары;

• Біздің дерекқорымызда бар екенімізді тексерейік (ол жерде басқа өріс болуы керек - халық саны). Ол үшін жазамыз:

  $ DESC популяциясы;

бәрі дұрыс, үстел біздікі. Келесіні жазайық:

$ ALTER TABLE елі ADD PRIMARY KEY (id);

• және оны пәрменмен дереу тексеріңіз:

  $DESC елі;

Суреттен көріп отырғаныңыздай, бәрі дұрыс, PRI мәні дәл болуы керек жерде. Айтпақшы, біз сынақ базасымен жұмыс істедік. Енді біз оны жоюымыз керек: неліктен serverді шатастыруымыз керек, солай емес пе? Ол үшін біз өте танымал команданы қолданамыз: $ DROP DATABASE тесті;

Молдованы қосу

Алдымен біз не жазатынымызды шешуіміз керек. Келесі идентификаторымыз 4 болады. Аты Молдова болады, ал оның халқы 3550900. Сондықтан біз бұрыннан білетін INSERT INTO командасын орындаймыз: $ INSERT INTO country VALUES (4, 'Молдава', 3550900); Және бұл мәннің нақты дерекқорда бар-жоғын тексереміз: $ SELECT * FROM country WHERE id = 4; Деректер сұрауында мен қай өрісті іздейтінін бірден анықтадым, сондықтан бізде бір ғана жазба бар, ол бізге қажет.

Қалалар кестесін құру

Мәліметтер қоры туралы бірінші мақаладағы диаграмманы пайдалана отырып , кесте туралы қажетті ақпаратты аламыз. Ол келесі өрістерді қамтиды:

• id – бірегей идентификатор;
• аты — қала атауы;
• country_id — елдің сыртқы кілті;
• популяция — қала халқы.

Әр жолы бірегей идентификатор жазу аздап стресс болады, солай емес пе? Мен мұны MySQL органдарына қалдырғым келеді . Және мұндай жол бар - AUTO INCREMENT . Біз мұны цифрлық өріске қосуымыз керек, егер біз мәндерді нақты бермесек, MySQL өзі идентификаторды алдыңғымен салыстырғанда бір есе арттырады. Сондықтан кестені құру келесідей болады: $ CREATE TABLE city ( id INT AUTO_INCREMENT, аты VARCHAR(30), елдің_идентификаторы INT, халық INT, PRIMARY KEY (id)); Барлығы дұрыс орындалғанын білу үшін кесте диаграммасын қарастырайық: $ DESC city; Кесте диаграммасынан көріп отырғаныңыздай, бізде id өрісі үшін жаңа сипаттама бар - auto_increment. Сондықтан біз бәрін дұрыс жасадық. Толық конфигурацияланған кестедегі деректерді тексерейік. Ол үшін тапсырманың соңғы бөлігін - сыртқы кілтті орындаймыз.

Қалаларға шетелдік кілт қосыңыз

Шетелдік кілт үшін мына пәрмен болады: $ ALTER TABLE city ADD FOREIGN KEY (country_id) РЕФЕРЕНЦИЯЛАР елі(id); Кесте схемасында не дұрыс емес екенін бірден тексерейік: ол бір сағат ішінде өзгерді ме? $DESC қаласы;

Бонус бөлігі. Тестілеу

Мен оны тапсырмаға қосуды ұмытып кеттім - бірінші бөлімнің скриншотындағы деректерді толтырыңыз. Мен ұмытып қалдым, енді оны өзім жасаймын. Қызығушылық танытқандар үшін менсіз өзіңіз жасай аласыз, сосын тексереміз;) Харьков, Киев, Минск, Одесса, Воронеж болды, Кишиневті де қосамыз. Бірақ бұл жолы біз идентификаторларды жібермейміз, оларды өткізіп жібереміз: $ INSERT INTO қала (аты, елдің идентификаторы, халық саны) VALUES ('Харьков', 1, 1443000), ('Киев', 1, 3703100), ('Минск') , 3, 2545500), («Одесса», 1, 1017699), («Воронеж», 2, 1058261), («Кишинев», 4, 695400); Көріп отырғаныңыздай, бір INSERT INTO пәрменін пайдаланып бір уақытта бірнеше жазбалар жасауға болады. Ыңғайлы нәрсе, есіңізде болсын) Енді кестеде не бар екенін бірден көрейік: $ SELECT * FROM city; AUTO_INCREMENT - дәл біз қалағандай жұмыс істеді. Идентификаторлық файлдар біз жібермегенімізге қарамастан, барлығы толтырылған. Сыртқы кілт - бұл тәуелді нәрсе. Оның дұрыс жұмыс істейтінін тексеру үшін сыртқы кестеде жоқ сыртқы кілтті жазуға болады. id = 5 Қазақстан деп шештік делік. Бірақ шын мәнінде ол елдер кестесінде жоқ. Ал деректер базасының ант беретінін тексеру үшін Астана қаласын қосыңыз: $ INSERT INTO қала (аты, елдің идентификаторы, халқы) VALUES («Астана», 5, 1136156); Және біз, әрине, қатені аламыз: Енді шетелдік кілт біздің дерекқорымызда жоқ қалаға елді тағайындауға тырыспауымызды қамтамасыз етеді. Үй тапсырмасының бұл бөлігін аяқталды деп санауға болады - жаңасына көшіңіз :)

SELECT мәлімдемесі

Енді бәрі соншалықты қорқынышты емес сияқты, солай емес пе? Java әзірлеушілері үшін дерекқорды білу міндетті болып табылатынын тағы бір рет атап өткім келеді. Дерекқорсыз ешқайда бара алмайсыз. Иә, мен қазірдің өзінде өтініш жазуды бастағым келеді, келісемін. Бірақ бұл қажет. Ендеше, біз бұл жолды жалғастырамыз. SELECT операторының көмегімен біз мәліметтер қорынан деректерді аламыз. Яғни, бұл әдеттегі DML операциясы (бұл не екенін ұмытып қалдыңыз ба?...))) БҰРЫН мақалаларды қайта оқыңыз). Реляциялық деректер қорының артықшылықтары қандай? Олардың деректерді жинақтау және алу үшін тамаша функционалдығы бар. SELECT операторы осы үшін пайдаланылады. Бұл туралы күрделі ештеңе болмауы мүмкін сияқты, солай емес пе? Бірақ түсіну керек нәрсе әлі көп екені белгілі болды) Бізге негізделе алатын негіздерді түсіну маңызды. SELECT операторы бар ең қарапайым сұраныс бір кестеден барлық деректерді таңдау болып табылады. Маған викидегі операторлар ТАҢДАУ сұрауында нақты қандай ретпен жүруі керек екендігі туралы сипаттама қатты ұнады, сондықтан мен оны осында көшіремін:

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Мұнда алдымен GROUP BY операторын, содан кейін WHERE операторын қоюға болмайтынын көруге болады. Қайдан шыққаны белгісіз қателіктер үшін кейінірек реніш болмас үшін мұны есте сақтау керек. $SELECT * FROM қала; Бірақ барлық деректерді жою біз үшін қызық емес. Егер біз микроскоппен шеге соғуды қаласақ [1] , [2] . Деректер базасы сүзу, сұрыптау және біріктіру операцияларын Java codeына қарағанда әлдеқайда жылдам орындайтындықтан, бұл мәселені дерекқорға қалдырған дұрыс. Сондықтан тапсырмаларды қиындата отырып, біз жаңа функцияларды ашамыз.

WHERE параметрі

Таңдауды сүзу үшін WHERE сөзі пайдаланылады . Мұны келесідей түсіндіру керек: SELECT * FROM кесте атауы (кесте кесте атауынан барлық өрістерді таңдаңыз) WHERE talbe_row = 1 (мұнда жазбалардағы кесте_жолы өрісі 1-ге тең). Сұраудағы кілт сөздердің реті маңызды екенін ескеру маңызды. WHERE a =1 FROM table_name SELECT * деп жаза алмайсыз. Орыс тілі үшін бұл жақсы, ал кейбіреулер үшін бұл соншалықты жаман көрінбеуі мүмкін, бірақ SQL үшін бұл мүмкін емес. Біз келесі сұранысты жазамыз: $ SELECT * FROM city WHERE country_id = 1; Ал біз Украина қалаларын таңдадық. Жаман емес, иә? Біз тек украиндықты ғана емес, белорустарды да қаласақ ше? Осы мақсатта өріс қабылдай алатын мәндер жинағын тізімдей аламыз: $SELECT * FROM city WHERE country_id IN(1, 3); Ал бізде жауап ретінде екі елдің қалалары бар. Сүзу үшін бірнеше шарттар болса ше? Екі миллионнан астам халқы бар қалаларды қалаймыз делік? Ол үшін НЕМЕСЕ және ЖӘНЕ сөздерін пайдаланыңыз : $ SELECT * FROM city WHERE country_id IN (1, 3) ЖӘНЕ халық > 2000000; Тамаша, бірақ егер бізге тағы бір шарт қосу керек болса ше - тұрақты тіркес арқылы атауларды іздеу (бұл жерде тұрақты тіркестерді сипаттамаймын: міне, мұны 4 бөлікте «қысқаша» жасаған адам )? Мысалы, біз қаланы қалай жазу керектігін есте сақтаймыз, бірақ толық емес... Ол үшін сүзгілеу өрнегіне LIKE кілт сөзін қосуға болады : $ SELECT * FROM city WHERE country_id IN (1, 3) ЖӘНЕ халық > 2000000 НЕМЕСЕ «%hark%» LIKE атауы; Осылайша біз Харьковты да алдық. Нәтижесінде ізденісіміз өте жақсы шықты деп айта аламыз. Бірақ мен ID бойынша емес, халық саны бойынша сұрыптағым келеді, бірақ қалай? Иә өте қарапайым...

ORDER BY параметрі

ORDER BY көмегімен біз алынған жазбаларды белгілі бір өріс бойынша сұрыптай аламыз. Ол сандарды да, жолдарды да сұрыптайды. Алдыңғы сұрауды кеңейтіп, халық саны бойынша сұрыптайық, ТАПСЫРЫС БОЙЫНША халық санына қарай сұрыптайық: $ ТАҢДАҢЫЗ * қаладан ҚАЙДА ЕЛ_ID IN IN (1, 3) ЖӘНЕ халық > 2000000 НЕМЕСЕ «%hark%» LIKE ТАҢДАУ. Көріп отырғанымыздай, сұрыптау табиғи тәртіпте, яғни өсу ретімен өтті. Егер біз керісінше болғымыз келсе ше? Ол үшін DESC сөзін қосу керек: $ SELECT * FROM city FROM WHERE country_id IN IN (1, 3) ЖӘНЕ халық > 2000000 НЕМЕСЕ «%hark%» LIKE ТАҢДАУ ТАПСЫРЫС БОЙЫНША халық DESC; Енді сұрыптау популяцияны азайтуға негізделген. Дерекқор мұны өте жылдам жасайды: онда Collections.sort ешбір салыстыруға болмайды. Енді жолдар бойынша, кері ретпен атау бойынша сұрыптайық: $ ТАҢДАУ * ҚАЛАДАН ҚАЙДА ЕЛ_ID IN (1, 3) ЖӘНЕ халық > 2000000 НЕМЕСЕ «%hark%» СИЯҚТЫ атау Аты бойынша ТАПСЫРУ DESC;

GROUP BY параметрі

Жазбаларды белгілі бір өрістер бойынша топтау үшін қолданылады. Бұл әдетте жиынтық функцияларды пайдалану үшін қажет... Жиынтық функциялар дегеніміз не?)) Әр түрлі жазбалар үшін бірдей болса, кейбір өрістер бойынша топтастыру мағынасы бар. Біздің мысалды пайдалана отырып, бұл нені білдіретінін қарастырайық. Айталық, қалаларда шетелдік кілттер – ел идентификаторлары бар. Сонымен, ID бір елдің қалалары үшін бірдей. Сондықтан, олар бойынша жазбаларды алып, топтастыруға болады: $ SELECT country_id, COUNT(*) FROM FROM CROUP BY country_id; Бірақ біріктіру функцияларынсыз бұл аздап әлсіз көрінеді, мойындау керек. Сондықтан ең көп таралған функциялардың бірнешеуін қарастырайық:

• COUNT - жазбалар саны, топтастырусыз пайдалануға болады, COUNT(*) ретінде пайдаланылады . Кейбір өріс бойынша топтастыру жағдайында - COUNT(топталған_өріс);
• MAX – нақты өріс үшін максималды мәнді табады;
• MIN – нақты өріс үшін ең аз мәнді табады;
• SUM – белгілі бір өрістің қосындысын табады;
• AVG – орташа мәнді табады.

Жалпы, бұл функцияларды топтастырусыз пайдалануға болады, сонда ғана бір өріс көрсетіледі. Оларды біздің қала тұрғындары үшін қолданып көрейік: $ SELECT COUNT(*) FROM FROM; Олардың сұрағандары - алғандары болды. Тек жазбалар саны. Кейде бұл пайдалы. Мысалы, белгілі бір автордың мақалаларының санын білу керек болса. Оларды дерекқордан шығарып, санаудың қажеті жоқ. Сіз жай ғана COUNT() пайдалана аласыз. $ ТАҢДАУ AVG(халық) ҚАЛАДАН; $ ТАҢДАУ MIN (халық) қаладан; Міне, топтастыру күшіне енеді. Мысалы, елдегі ең кішкентай қаланы алу міндеті тұр. Мұны қалай жасау керектігін білесіз бе? Өзіңіз көріңіз, содан кейін мынаны қараңыз: $ SELECT country_id as Country, MIN(халық) FROM FROM FROUP GROUP BY BY country_id; Әзірге біз тек елдің жеке куәлігін көреміз, бірақ бұл маңызды емес - келесі жолы біз бәрін жасаймыз. Нәтиже қазірдің өзінде бар және біз қалаған нәрсеге қол жеткіздік - ID = 1 болатын елдегі ең кішкентай қала. Қалған функциялар бірдей болады. Топтау мен біріктіруді пайдаланған кезде барлық өрістерді * арқылы сызу жұмыс істемейтінін ескеру маңызды! Ойланыңыз;)

Үй жұмысы

Алдыңғы мақалалардың нәтижесі бойынша, үй тапсырмасы орындалып жатқаны анық, сондықтан жалғастырайық)) Иә, үй тапсырмасын орындағандардың бәрі түсініктемелерде «+» қоя береді. Мен үшін үй тапсырмасының тақырыбының сізге қызықты болуы маңызды, сондықтан мен оны болашақта жалғастырамын. Иә, мен сіздің пікірлеріңізді үнемі оқып отырамын. Әрине, мен аз жауап беремін. Мен олардан қиынырақ SQL есептерін беруді сұрағанын көрдім. Біз қосылуды үйренбейінше, қызықты мәселелер болмайды, сондықтан маған қосымша материал қажет болатындар болады.

Тапсырмалар:

HAVING операторын түсініп , біздің мысалдағы кестелерге мысал сұрауын жазыңыз. Түсінікті болу үшін кейбір өрістерді немесе бірнеше мәндерді қосу қажет болса, оларды қосыңыз. Егер біреу қаласа, түсініктемелерде мысал шешіміңізді жазыңыз: осылайша мен оны уақытым болса, тексере аламын.
1. UI арқылы дерекқормен жұмыс істеу үшін MySQL Workbench орнатыңыз. Менің ойымша, бізде консольден жұмыс істеу тәжірибесі жеткілікті. Дерекқорға қосылыңыз. Дерекқормен жұмыс істеу үшін басқа нәрсені пайдалансаңыз, бұл тапсырманы өткізіп жіберіңіз. Мұнда және одан әрі мен тек MySQL Workbench-ті қолданамын.
2. Деректерімізді пайдаланып алу үшін сұрауларды жазыңыз:
   1. ең шағын/халқы ең көп ел;
   2. елдегі тұрғындардың орташа саны;
   3. атаулары «а» әрпімен аяқталатын елдердегі тұрғындардың орташа саны;
   4. төрт миллионнан астам халқы бар елдер саны;
   5. елдерді тұрғындарының азаюы бойынша сұрыптау;
   6. елдерді табиғи ретімен атаулары бойынша сұрыптаңыз.

Қорытынды

Бүгін біз өткен сабақтағы үй тапсырмасын егжей-тегжейлі талқыладық. Оның үстіне, мен мұны істемегендер үшін де, істегендер үшін де маңызды деп санаймын. Біріншісі үшін бұл жауапты білуге, ал екіншісі үшін оны нәтижемен салыстыруға мүмкіндік береді. Жобадағы өзгерістер туралы хабардар болу үшін менің GitHub тіркелгісіне жазылыңыз . Мен бүкіл codeтық базаны сонда сақтаймын. Барлығы осы ұйымда болады . Содан кейін біз SELECT мәлімдемесін талқыладық. Ол біз үшін ең маңыздысы. Дәл осы арқылы деректерге барлық сұраныстар өтеді және біз оны түсінуіміз керек. Ең бастысы - параметрлерді қосу ретін есте сақтау (ҚАЙДА, ТАПСЫРУ БОЙЫНША, GROUP BY және т.б.). Иә, мен мүмкін болатынның бәрін айтқан жоқпын, бірақ мен өзіме ондай мақсат қоймадым. Иә, сіз қазірдің өзінде өтініш жазуға асық екеніңізді білемін. Сабырлы болыңыз, бұл сізге қажет. Жоба үшін де, кәсіби өсу үшін де. Күту кезінде Git сізге бұрыннан таныс екеніне көз жеткізіңіз. Мен оны әдепкі бойынша, танымал құрал ретінде қолданамын. Оқығаныңыз үшін барлығына рахмет. Келесі мақалада дерекқор қосылымдары мен қосылулар туралы айтатын боламыз. Керемет тапсырмалар сонда болады))

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