ما پایگاه داده ها و زبان SQL را تجزیه و تحلیل می کنیم. (قسمت 3) - "پروژه جاوا از A تا Z"

مقاله ای از مجموعه ای در مورد ایجاد یک پروژه جاوا (لینک به سایر مواد در پایان است). هدف آن تجزیه و تحلیل فناوری های کلیدی است که نتیجه آن نوشتن یک ربات تلگرام است.

• بررسی تکالیف
  • افزودن کلید اصلی
  • اضافه شدن مولداوی
  • جدول شهرها را ایجاد کنید
  • یک کلید خارجی به شهرها اضافه کنید
  • بخش پاداش آزمایش کردن
• عبارت SELECT
  • پارامتر WHERE
  • ترتیب بر اساس پارامتر
  • GROUP BY پارامتر
• مشق شب
• نتیجه

سلام، خانم ها و آقایان، اجازه دهید در مورد پایگاه داده، SQL و موارد دیگر صحبت کنیم. مطالب امروز شامل بخش تئوری و بخشی عمل خواهد بود. اجازه دهید یادآوری کنم که آخرین بار در مورد نحوه تنظیم همه چیز، نحوه ایجاد یک پایگاه داده، یک جدول و دریافت داده از آن صحبت کردیم. وقت آن است که ببینیم آیا چیزی با سنجش از راه دور درست شده است یا خیر. به نظر من نیمی از آن را فقط بر اساس مقاله قبلی می شد انجام داد. معلوم شد که برای مونتاژ صحیح یک برنامه و زیبایی کم و بیش همه چیز، باید در مورد پایگاه داده ها صحبت کنید و برای صحبت در مورد آنها باید زمان زیادی را صرف کنید.

بررسی تکالیف

احترام بزرگ برای همه کسانی که وظایف را با موفقیت انجام دادند. این بدان معنی است که شما می دانید که فقط شما به این نیاز دارید و فقط به شما کمک می کند. برای کسانی که از تکلیف من غافل شده اند، این شرط را به شما یادآوری می کنم:

1. شما باید یک کلید اصلی (PRIMARY KEY) را از قسمت ID به طرح جدول کشور اضافه کنید.
2. کشور دیگری را به جدول کشور اضافه کنید - مولداوی.
3. با توجه به طرح مقاله قبلی، یک شهر جدول ایجاد کنید که شامل تمام فیلدهایی است که توضیح داده شده است. نام فیلدها به صورت زیر خواهد بود: id، name، country_id، جمعیت.
4. یک کلید اصلی به جدول شهر اضافه کنید.
5. یک کلید خارجی به جدول شهر اضافه کنید.

برای شروع، از قسمت اول مقاله قبلی استفاده می کنیم و به ترمینال پایگاه داده می رویم.

افزودن کلید اصلی

شما می توانید یک کلید اصلی (کلید اولیه) را به دو روش اضافه کنید: بلافاصله هنگام ایجاد جدول، یا پس از ایجاد، با استفاده از ALTER TABLE.

کلید اصلی در هنگام ایجاد جدول

از آنجایی که ما قبلا یک جدول ایجاد کرده‌ایم و بدون حذف آن نمی‌توانیم این رویکرد را در این پایگاه داده نشان دهیم، به سادگی یک پایگاه داده آزمایشی موقت ایجاد می‌کنیم که در آن همه کارها را انجام خواهیم داد. بیایید دستورات زیر را وارد کنیم:

• ایجاد یک پایگاه داده جدید:

  تست $CREATE DATABASE.

• یک جدول با اضافه کردن یک کلید اصلی ایجاد کنید:

  $ ایجاد جدول کشور(id INT، نام VARCHAR(30)، PRIMARY KEY (id));

به طور کلی، هیچ چیز پیچیده نیست. پس از اعلان متغیرها، قسمت زیر PRIMARY KEY (id) اضافه می شود که نام فیلدی که کلید اصلی خواهد بود در پرانتز قرار می گیرد. و بیایید ببینیم که طرح جدول چگونه تغییر کرده است: $ DESC country; همانطور که می بینید، مقدار PRI در قسمت Key برای ورودی شناسه ظاهر شده است .

کلید اصلی پس از ایجاد جدول

همانطور که قبلاً گفتم، اولین کلید بعد از ایجاد جدول را می توان با استفاده از ALTER TABLE اختصاص داد . ما این مثال را در پایگاه داده شهرهای خود اجرا خواهیم کرد :

• بیایید از آزمایشی به پایگاه داده خود برویم:

  $USE شهرها؛

• بیایید بررسی کنیم که قطعاً در پایگاه داده خود هستیم (باید فیلد دیگری در آنجا وجود داشته باشد - جمعیت). برای این کار می نویسیم:

  $ جمعیت DESC;

همه چیز درست است، جدول مال ماست. بیایید موارد زیر را بنویسیم:

$ تغییر کشور جدول اضافه کردن کلید اصلی (id);

• و بلافاصله با دستور زیر آن را بررسی کنید:

  کشور $DESC؛

همانطور که از تصویر می بینید، همه چیز درست است، مقدار PRI دقیقا همان جایی است که باید باشد. به هر حال، ما با پایگاه داده آزمایشی کار کردیم. اکنون باید آن را حذف کنیم: چرا باید سرور را شلوغ کنیم، درست است؟ برای انجام این کار، از یک دستور نسبتاً معروف استفاده می کنیم: $ DROP DATABASE test;

اضافه شدن مولداوی

ابتدا باید تصمیم بگیریم که چه چیزی را ضبط کنیم. شناسه بعدی ما 4 خواهد بود. نام Moldova خواهد بود و جمعیت آن 3550900 است. بنابراین، ما دستور INSERT INTO را که قبلاً می دانیم اجرا می کنیم: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); و بررسی می کنیم که آیا این مقدار دقیقاً در پایگاه داده است: $ SELECT * FROM country WHERE id = 4; در درخواست داده، من فوراً تعیین کردم که کدام فیلد جستجو شود، بنابراین فقط یک رکورد به دست آوردیم که همان چیزی است که نیاز داشتیم.

جدول شهرها را ایجاد کنید

با استفاده از نمودار مقاله اول در مورد پایگاه داده، اطلاعات لازم را در مورد جدول به دست می آوریم. شامل فیلدهای زیر خواهد بود:

• شناسه - شناسه منحصر به فرد.
• نام - نام شهر؛
• country_id - کلید خارجی کشور؛
• جمعیت - جمعیت شهر.

فکر نمی کنید هر بار نوشتن یک شناسه منحصر به فرد کمی استرس زا است؟ من می خواهم این را به مقامات MySQL بسپارم . و چنین راهی وجود دارد - افزایش خودکار . باید این را به فیلد دیجیتال اضافه کنیم و اگر به صراحت مقادیر را پاس نکنیم، خود MySQL شناسه را نسبت به قبلی یک عدد افزایش می دهد. بنابراین، ایجاد یک جدول به این صورت خواهد بود: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, جمعیت INT, PRIMARY KEY (id)); بیایید به نمودار جدول نگاه کنیم تا ببینیم آیا همه چیز به درستی انجام شده است: $ DESC city; همانطور که از نمودار جدول می بینید، ما یک توضیح جدید برای فیلد id داریم - auto_increment. بنابراین ما همه چیز را درست انجام دادیم. بیایید داده ها را روی یک جدول کاملاً پیکربندی شده بررسی کنیم. برای انجام این کار، آخرین قسمت کار - کلید خارجی - را انجام خواهیم داد.

یک کلید خارجی به شهرها اضافه کنید

برای یک کلید خارجی این دستور وجود خواهد داشت: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); و بیایید بلافاصله بررسی کنیم که چه چیزی در طرح جدول مشکل دارد: آیا در طول یک ساعت تغییر کرده است؟ شهر $DESC؛

بخش پاداش آزمایش کردن

فراموش کردم آن را به کار اضافه کنم - داده هایی را که در تصویر قسمت اول بود پر کنید. فراموش کردم، پس حالا خودم انجامش می دهم. و برای کسانی که علاقه مند هستند، می توانید خودتان بدون من این کار را انجام دهید و سپس ما بررسی می کنیم ؛) خارکف، کیف، مینسک، اودسا، ورونژ وجود داشت و ما نیز کیشینوف را اضافه خواهیم کرد. اما این بار ما شناسه‌ها را ارسال نمی‌کنیم، از آنها صرفنظر می‌کنیم: $ INSERT INTO city (نام، شناسه_کشور، جمعیت) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk') , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); همانطور که می بینید، می توانید چندین ورودی را به طور همزمان با استفاده از یک دستور INSERT INTO ایجاد کنید. یک چیز مفید، به یاد داشته باشید) و بلافاصله بیایید ببینیم در جدول چه چیزی وجود دارد: $ SELECT * FROM city; AUTO_INCREMENT - دقیقاً همانطور که می خواستیم کار کرد. فایل های شناسه همه پر شده اند، حتی اگر ما آنها را ارسال نکرده باشیم. کلید خارجی یک چیز وابسته است. برای بررسی اینکه آیا درست کار می کند، می توانید سعی کنید یک کلید خارجی بنویسید که در جدول خارجی وجود ندارد. فرض کنید تصمیم گرفتیم که id = 5 قزاقستان باشد. اما در واقع در جدول کشورها نیست. و برای بررسی اینکه پایگاه داده قسم می خورد، شهر را اضافه کنید - Astana: $ INSERT INTO city (name, country_id, جمعیت) VALUES ('Astana', 5, 1136156); و طبیعتاً این خطا را دریافت می کنیم: اکنون کلید خارجی اطمینان حاصل می کند که ما سعی نمی کنیم کشوری را به شهری که در پایگاه داده ما نیست اختصاص دهیم. این قسمت از تکالیف را می توان تکمیل شده در نظر گرفت - به سمت جدید بروید :)

عبارت SELECT

خوب، همه چیز دیگر آنقدر ترسناک به نظر نمی رسد، درست است؟ من می خواهم یک بار دیگر یادآوری کنم که برای توسعه دهندگان جاوا، دانش پایگاه داده یک امر ضروری است. بدون پایگاه داده نمی توانید به جایی بروید. بله، من از قبل می خواهم شروع به نوشتن یک برنامه کنم، موافقم. اما لازم است. پس این راه را ادامه خواهیم داد. با استفاده از دستور SELECT، داده ها را از پایگاه داده بازیابی می کنیم. یعنی این یک عملیات DML معمولی است (آیا قبلاً فراموش کرده اید چیست؟...))) مقالات را دوباره بخوانید. مزایای پایگاه داده های رابطه ای چیست؟ آنها عملکرد بسیار خوبی برای جمع آوری و بازیابی داده ها دارند. این همان چیزی است که دستور SELECT برای آن استفاده می شود. به نظر می رسد که هیچ چیز پیچیده ای در مورد آن وجود ندارد، درست است؟ اما معلوم می‌شود که هنوز چیزهای زیادی برای درک وجود دارد) برای ما مهم است که اصولی را که می‌توانیم از آن‌ها استفاده کنیم، درک کنیم. ساده ترین پرس و جو با دستور 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 * از شهر؛ اما به وضوح از بین بردن همه داده ها برای ما سرگرم کننده نیست. اگر بخواهیم میخ ها را با میکروسکوپ چکش کنیم، دقیقاً مشابه است [1] , [2] . از آنجایی که پایگاه داده عملیات فیلترینگ، مرتب سازی و تجمیع را بسیار سریعتر از کد جاوا انجام می دهد، بهتر است این موضوع را به پایگاه داده بسپارید. بنابراین، با پیچیده کردن وظایف، عملکرد جدیدی را باز خواهیم کرد.

پارامتر WHERE

برای فیلتر کردن یک انتخاب، از کلمه WHERE استفاده می شود . این باید به صورت زیر تفسیر شود: SELECT * FROM tablename (همه فیلدها را از نام جدول انتخاب کنید) WHERE talbe_row = 1 (که در رکوردها قسمت table_row برابر با 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); و ما در حال حاضر شهرهایی از دو کشور در پاسخ داریم. اگر چندین شرایط برای فیلتر وجود داشته باشد چه؟ فرض کنید شهرهایی با جمعیت بیش از دو میلیون نفر می خواهیم؟ برای این کار از کلمات OR و AND استفاده کنید : $ SELECT * FROM city WHERE country_id IN (1, 3) AND جمعیت > 2000000; عالی است، اما اگر لازم باشد یک شرط دیگر اضافه کنیم - جستجوی نام ها از طریق یک عبارت منظم (من عبارات منظم را در اینجا شرح نمی دهم: اینجا شخصی است که این کار را به طور خلاصه در 4 قسمت انجام داده است )؟ به عنوان مثال، نحوه نوشتن یک شهر را به یاد می آوریم، اما نه به طور کامل... برای این کار، می توانید کلمه کلیدی LIKE را به عبارت فیلتر اضافه کنید : $ SELECT * FROM city WHERE country_id IN (1, 3) AND جمعیت > 2000000 OR نام مانند "%hark%"؛ و به این ترتیب خارکف را نیز به دست آوردیم. در نتیجه می توان گفت که جستجوی ما بسیار خوب بود. اما من می خواهم نه بر اساس شناسه، بلکه بر اساس جمعیت مرتب کنم، اما چگونه؟ بله خیلی ساده...

ترتیب بر اساس پارامتر

با استفاده از ORDER BY، می توانیم رکوردهایی را که دریافت کرده ایم بر اساس یک فیلد خاص مرتب کنیم. هم اعداد و هم رشته ها را مرتب می کند. بیایید پرس و جو قبلی را گسترش دهیم، بر اساس جمعیت مرتب کنیم و ترتیب بر اساس جمعیت را اضافه کنیم: $ SELECT * FROM city WHERE country_id IN (1, 3) و جمعیت > 2000000 یا نامی مانند "%hark%" ORDER BY جمعیت. همانطور که می بینیم، مرتب سازی به ترتیب طبیعی، یعنی به ترتیب صعودی انجام شده است. اگر برعکس بخواهیم چه؟ برای انجام این کار، باید کلمه DESC را اضافه کنید: $ SELECT * FROM city WHERE country_id IN (1، 3) و جمعیت > 2000000 یا نامی مانند “%hark%” ORDER BY DESC جمعیت; در حال حاضر مرتب سازی بر اساس کاهش جمعیت است. و پایگاه داده این کار را خیلی سریع انجام می دهد: هیچ Collections.sort در آنجا قابل مقایسه نیست. حالا بیایید بر اساس ردیف، بر اساس نام به ترتیب معکوس مرتب کنیم: $ SELECT * FROM city WHERE country_id در (1، 3) و جمعیت > 2000000 یا نام مانند "%hark%" ترتیب بر اساس نام DESC;

GROUP BY پارامتر

برای گروه بندی رکوردها بر اساس فیلدهای خاص استفاده می شود. این معمولاً برای استفاده از توابع جمع مورد نیاز است... توابع جمع چیست؟)) اگر برای رکوردهای مختلف یکسان باشند، گروه بندی بر اساس برخی از فیلدها منطقی است. بیایید با استفاده از مثال خود ببینیم این به چه معناست. بیایید بگوییم که شهرها دارای کلید خارجی هستند - شناسه کشور. بنابراین، شناسه برای شهرهایی از همان کشور یکسان است. بنابراین، می توانید رکوردها را بر اساس آنها گرفته و گروه بندی کنید: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; اما باید اعتراف کنید که بدون توابع تجمع کمی ضعیف به نظر می رسد. بنابراین، اجازه دهید به چند مورد از رایج ترین توابع نگاه کنیم:

• COUNT — تعداد رکوردها، می توانند بدون گروه بندی استفاده شوند، به عنوان COUNT(*) استفاده می شود . در مورد گروه بندی بر اساس برخی فیلدها - COUNT(groupped_field);
• MAX - حداکثر مقدار را برای یک فیلد خاص پیدا می کند.
• MIN - حداقل مقدار را برای یک فیلد خاص پیدا می کند.
• SUM - مجموع یک فیلد خاص را پیدا می کند.
• AVG - مقدار متوسط ​​را پیدا می کند.

به طور کلی، این توابع را می توان بدون گروه بندی استفاده کرد، تنها در این صورت تنها یک فیلد نمایش داده می شود. بیایید آنها را برای جمعیت شهرمان امتحان کنیم: $ SELECT COUNT(*) FROM city; آنچه آنها خواستند چیزی بود که به دست آوردند. فقط تعداد رکوردها گاهی اوقات این مفید است. به عنوان مثال، اگر باید تعداد مقالات یک نویسنده خاص را دریابیم. نیازی به حذف آنها از پایگاه داده و شمارش آنها نیست. شما به سادگی می توانید از COUNT() استفاده کنید. $ SELECT AVG(جمعیت) FROM city; $ SELECT MIN(جمعیت) FROM شهر; و اینجاست که گروه بندی لازم الاجرا می شود. به عنوان مثال، وظیفه به دست آوردن کوچکترین شهر در کشور است. از قبل می دانید چگونه این کار را انجام دهید؟ خودتان آن را امتحان کنید، سپس تماشا کنید: $ SELECT country_id به عنوان کشور، MIN(جمعیت) FROM city WHERE GROUP BY country_id; تا اینجا ما فقط شناسه کشور را می بینیم، اما مهم نیست - دفعه بعد همه چیز را انجام خواهیم داد. و بنابراین در حال حاضر یک نتیجه وجود دارد، و ما به آنچه می خواستیم رسیدیم - کوچکترین شهر کشور با ID = 1. بقیه توابع یکسان خواهند بود. توجه به این نکته مهم است که حذف همه فیلدها از طریق * هنگام استفاده از گروه بندی و تجمیع کارساز نخواهد بود! بهش فکر کن ;)

مشق شب

بر اساس نتایج مقالات قبلی، مشخص است که تکالیف در حال انجام است، بنابراین ادامه می دهیم)) بله، هرکسی که تکالیف را انجام می دهد، همچنان "+" را در نظرات قرار می دهد. برای من مهم است که مبحث تکالیف برای شما جالب باشد تا در آینده به انجام آن ادامه دهم. بله، من نظرات شما را مرتب می خوانم. البته کمتر جواب میدم. دیدم که خواستند مشکلات SQL سخت تری بدهند. تا زمانی که پیوستن ها را یاد نگیریم، هیچ مشکل جالبی وجود نخواهد داشت، بنابراین مواردی وجود خواهد داشت که برای مطالب بیشتر به آنها نیاز دارم.

وظایف:

عملگر HAVING را درک کنید و یک پرس و جوی مثال برای جداول مثال ما بنویسید. اگر نیاز به اضافه کردن چند فیلد یا مقادیر بیشتری برای واضح تر شدن آن دارید، آنها را اضافه کنید. اگر کسی می خواهد، راه حل نمونه خود را در نظرات بنویسید: به این ترتیب اگر وقت داشتم، می توانم آن را نیز بررسی کنم.
1. MySQL Workbench را برای کار با پایگاه داده از طریق UI نصب کنید. من فکر می کنم ما قبلاً تمرین کافی را با کار از روی کنسول داشته ایم. به پایگاه داده متصل شوید. اگر از چیز دیگری برای کار با پایگاه داده استفاده می کنید، از این کار صرف نظر کنید. در اینجا و بیشتر من فقط از MySQL Workbench استفاده خواهم کرد.
2. درخواست هایی برای دریافت با استفاده از داده های ما بنویسید:
   1. کوچکترین/پرجمعیت ترین کشور؛
   2. میانگین تعداد ساکنان کشور؛
   3. میانگین تعداد ساکنان کشورهایی که نام آنها با "الف" ختم می شود.
   4. تعداد کشورهایی که بیش از چهار میلیون نفر جمعیت دارند؛
   5. دسته بندی کشورها با کاهش تعداد ساکنان
   6. کشورها را بر اساس نام به ترتیب طبیعی مرتب کنید.

نتیجه

امروز به طور مفصل در مورد تکالیف درس گذشته بحث کردیم. علاوه بر این، من این را هم برای کسانی که این کار را نکرده اند و هم برای کسانی که انجام داده اند مهم می دانم. برای اولی، این فرصتی است برای یافتن پاسخ، و برای دومی، برای مقایسه آن با نتیجه خود. در حساب GitHub من مشترک شوید تا از تغییرات پروژه مطلع شوید. من کل پایه کد را در آنجا حفظ خواهم کرد. همه چیز در این سازمان اتفاق خواهد افتاد . بعد، ما دستور SELECT را مورد بحث قرار دادیم. او برای ما مهم ترین است. از طریق آن است که تمام درخواست های داده از طریق آن انجام می شود و ما باید آن را درک کنیم. مهمترین چیز این است که ترتیب اضافه شدن پارامترها را به خاطر بسپارید (WHERE، ORDER BY، GROUP BY و غیره). بله، من هر چیزی را که ممکن بود نگفتم، اما چنین هدفی را برای خودم تعیین نکردم. بله، می دانم که شما در حال حاضر مشتاق نوشتن یک برنامه هستید. صبور باشید، این تنها چیزی است که نیاز دارید. هم برای پروژه و هم برای رشد حرفه ای شما. در حالی که منتظر هستید، مطمئن شوید که Git از قبل برای شما آشنا است. من به طور پیش فرض از آن به عنوان یک ابزار شناخته شده استفاده خواهم کرد. با تشکر از همه برای خواندن. در مقاله بعدی در مورد اتصالات پایگاه داده و اتصالات صحبت خواهیم کرد. این جایی است که وظایف جالب خواهد بود))

فهرستی از تمام مواد این مجموعه در ابتدای این مقاله است.