تعلم لغة SQL مع هذه الوصفات الخمس السهلة

SQL (لغة الاستعلام الهيكلية) هي لغة قوية ومعبرة للتعامل مع البيانات من قواعد البيانات العلائقية. لكن قد يبدو الأمر شاقًا للمبتدئين.

"الوصفات" التي سأشاركها معك اليوم هي بعض الأمثلة الأساسية من قاعدة بيانات بسيطة. لكن الأنماط التي ستتعلمها هنا يمكن أن تساعدك في كتابة استفسارات دقيقة. ستجعلك هذه تشعر وكأنك معادلة بيانات MasterChef في أي وقت من الأوقات

ملاحظة حول بناء الجملة: تتم كتابة معظم الاستعلامات أدناه بالنمط المستخدم في PostgreSQL من سطر أوامر psql. يمكن أن تستخدم محركات SQL المختلفة أوامر مختلفة قليلاً.

يجب أن تعمل معظم الاستعلامات أدناه في معظم المحركات بدون تعديل ، على الرغم من أن بعض المحركات أو أدوات واجهة المستخدم الرسومية قد تتطلب حذف علامات الاقتباس حول أسماء الجداول والأعمدة.

الطبق 1: إرجاع جميع المستخدمين الذين تم إنشاؤهم في نطاق تاريخ معين

مكونات

  • تحديد
  • من عند
  • أين
  • و

طريقة

SELECT * FROM "Users" WHERE "created_at" > "2020-01-01" AND "created_at" < "2020-02-01";

هذا الطبق البسيط هو عنصر أساسي متعدد الاستخدامات. نحن هنا نعيد المستخدمين الذين يستوفون شرطين معينين من خلال تسلسل WHEREالشروط ANDببيان. يمكننا توسيع هذا مع المزيد من ANDالبيانات.

في حين أن المثال هنا يتعلق بنطاق زمني محدد ، فإن معظم طلبات البحث تتطلب نوعًا من الشروط لتصفية البيانات بشكل مفيد.

الطبق 2: البحث عن جميع التعليقات الخاصة بكتاب ، بما في ذلك المستخدم الذي قام بالتعليق

(جديد) المكونات

  • انضم

طريقة

SELECT "Comments"."comment", "Users"."username" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" WHERE "Comments"."bookId" = 1;

يفترض هذا الاستعلام بنية الجدول التالية:

أحد الأشياء التي يمكن أن تبدأ في الخلط بين المبتدئين و SQL هو استخدام JOINs للعثور على البيانات من الجداول المرتبطة.

يعرض ERD (مخطط علاقة الكيان) أعلاه ثلاثة جداول ، المستخدمون ، والكتب ، والتعليقات ، وارتباطاتهم.

كل جدول لديه idوهو جريئة في الرسم البياني لإظهار أنه هو المفتاح الأساسي للجدول. يكون هذا المفتاح الأساسي دائمًا قيمة فريدة ويستخدم لتمييز السجلات في الجداول عن بعضها.

و مائل أسماء الأعمدة userIdو bookIdفي الجدول التعليقات هي المفاتيح الخارجية، وهو ما يعني أنها هي المفتاح الأساسي في الجداول الأخرى، وتستخدم هنا للمرجع تلك الجداول.

تُظهر الموصلات في ERD أعلاه أيضًا طبيعة العلاقات بين الجداول الثلاثة.

تعني نهاية النقطة المفردة في الموصل "واحد" والنهاية المنقسمة على الموصل تعني "العديد" ، وبالتالي فإن جدول المستخدم له علاقة "رأس بأطراف" مع جدول التعليقات.

يمكن أن يكون للمستخدم العديد من التعليقات ، على سبيل المثال ، ولكن التعليق يمكن أن ينتمي فقط إلى مستخدم واحد. الكتب والتعليقات لها نفس العلاقة في الرسم التخطيطي أعلاه.

يجب أن يكون استعلام SQL منطقيًا بناءً على ما نعرفه الآن. نعيد فقط الأعمدة المسماة ، أي عمود التعليق من جدول التعليقات واسم المستخدم من جدول المستخدمين المرتبط (بناءً على المفتاح الخارجي المشار إليه). في المثال أعلاه ، نحصر البحث في كتاب واحد ، مرة أخرى بناءً على المفتاح الخارجي في جدول التعليقات.

الطبق 3: عد عدد التعليقات التي أضافها كل مستخدم

(جديد) المكونات

  • العد
  • مثل
  • مجموعة من

طريقة

SELECT "Users"."username", COUNT("Comments"."id") AS "CommentCount" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id" GROUP BY "Users"."id";

يقوم هذا الاستعلام الصغير ببعض الأشياء المثيرة للاهتمام. أسهل ما يمكن فهمه هو ASالبيان. هذا يسمح لنا بشكل تعسفي ومؤقتا بإعادة تسمية الأعمدة في البيانات التي يتم إرجاعها. نقوم هنا بإعادة تسمية العمود المشتق ، ولكنه مفيد أيضًا عندما يكون لديك idأعمدة متعددة ، حيث يمكنك إعادة تسميتها بأشياء مثل userIdأو commentIdوما إلى ذلك.

و COUNTالبيان هو وظيفة SQL ذلك، كما كنت تتوقع، تعول الأشياء. نحسب هنا عدد التعليقات المرتبطة بالمستخدم. كيف يعمل؟ حسنًا ، هذا GROUP BYهو المكون النهائي المهم.

لنتخيل باختصار طلب بحث مختلف قليلاً:

SELECT "Users"."username", "Comments"."comment" FROM "Comments" JOIN "Users" ON "Comments"."userId" = "Users"."id";

لاحظ ، لا عد أو تجميع. نريد فقط كل تعليق ومن قام به.

قد يبدو الإخراج مثل هذا:

|----------|-----------------------------| | username | comment | |----------|-----------------------------| | jackson | it's good, I liked it | | jackson | this was ok, not the best | | quincy | excellent read, recommended | | quincy | not worth reading | | quincy | I haven't read this yet | ------------------------------------------

تخيل الآن أننا أردنا حساب تعليقات جاكسون وكوينسي - من السهل رؤيتها بلمحة هنا ، ولكن أصعب مع مجموعة بيانات أكبر كما يمكنك أن تتخيل.

في GROUP BYبيان يقول أساسا الاستعلام لعلاج جميع jacksonالسجلات كمجموعة واحدة، وجميع quincyالسجلات وآخر. و COUNTظيفة ثم بحساب السجلات في هذه المجموعة وعوائد تلك القيمة:

|----------|--------------| | username | CommentCount | |----------|--------------| | jackson | 2 | | quincy | 3 | ---------------------------

الطبق 4: ابحث عن المستخدمين الذين لم يعلقوا

(جديد) المكونات

  • الانضمام إلى اليسار
  • باطل

طريقة

SELECT "Users"."username" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId" WHERE "Comments"."id" IS NULL;

يمكن أن تصبح الصلات المختلفة مربكة للغاية ، لذلك لن أقوم بفك حزمها هنا. يوجد هنا تفصيل ممتاز لها: التمثيلات المرئية لـ SQL Joins ، والتي تمثل أيضًا بعض الاختلافات النحوية بين النكهات المختلفة أو SQL.

دعنا نتخيل نسخة بديلة من هذا الاستعلام بسرعة:

SELECT "Users"."username", "Comments"."id" AS "commentId" FROM "Users" LEFT JOIN "Comments" ON "Users"."id" = "Comments"."userId";

We still have the LEFT JOIN but we've added a column and removed the WHERE clause.

The return data might look something like this:

|----------|-----------| | username | commentId | |----------|-----------| | jackson | 1 | | jackson | 2 | | quincy | NULL | | abbey | 3 | ------------------------

So Jackson is responsible for comments 1 and 2, Abbey for 3, and Quincy has not commented.

The difference between a LEFT JOIN and an INNER JOIN (what we've been calling just a JOIN until now, which is valid) is that the inner join only shows records where there are values for both tables. A left join, on the other hand, returns everything from the first, or left, table (the FROM one) even if there is nothing in the right table. An inner join would therefore only show the records for Jackson and Abbey.

Now that we can visualize what the LEFT JOIN returns, it's easier to reason about what the WHERE...IS NULL part does. We return only those users where the commentId is a null value, and we don't actually need the null value column included in the output, hence its original omission.

Dish 5: List all comments added by each user in a single field, pipe separated

(New) Ingredients

  • GROUP_CONCAT or STRING_AGG

Method (MySQL)

SELECT "Users"."username", GROUP_CONCAT("Comments"."comment" SEPARATOR " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

Method (Postgresql)

SELECT "Users"."username", STRING_AGG("Comments"."comment", " | ") AS "comments" FROM "Users" JOIN "Comments" ON "Users"."id" = "Comments"."userId" GROUP BY "Users"."id";

This final recipe shows a difference in syntax for a similar function in two of the most popular SQL engines.

Here is a sample output we might expect:

|----------|---------------------------------------------------| | username | comments | |----------|---------------------------------------------------| | jackson | it's good, I liked it | this was ok, not the best | | quincy | excellent read, recommended | not worth reading | ----------------------------------------------------------------

We can see here that the comments have been grouped and concatenated / aggregated, that is joined together in a single record field.

BonAppetit

الآن بعد أن أصبح لديك بعض وصفات SQL للرجوع إليها ، كن مبدعًا وقم بتقديم أطباق البيانات الخاصة بك!

أحب أن أفكر في WHERE، JOIN، COUNT، GROUP_CONCATمثل الملح والدهون، حمض حرارة الطهي قاعدة البيانات. بمجرد أن تعرف ما تفعله بهذه العناصر الأساسية ، فأنت في طريقك إلى الإتقان.

إذا كانت هذه مجموعة مفيدة ، أو كان لديك وصفات أخرى مفضلة لمشاركتها ، أرسل لي تعليقًا أو تابعنا على Twitter:JacksonBates.