😟 🚩 💏 المهام والحلول لمقاتل PostgreSQL 👨🏻‍🌾 🚹 🍣

تحية لجميع محبي SQL!

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

الإجابات المقدمة مناسبة لـ PostgreSQL ( ستكون معظم المهام مناسبة لأنظمة DBMS الأخرى ، لكن النتائج والحلول قد تكون مختلفة. بل إنها مثيرة للاهتمام حيث تنشأ الاختلافات)

حاول الإجابة على نفسك قبل فتح المفسد.

دعنا نذهب!

سأحاول وضع علامة النجمة على شيء بحت لـ PostgreSQL * (ليس هناك الكثير من هذه اللحظات)

1. قليلا عن العمليات العددية

1.1 هل سيتم تلبية هذه الطلبات؟ ما النتائج التي سيعودون إليها؟

-- )     SELECT 3/2; -- ) SELECT min('- '::TEXT), avg('- '::TEXT); -- )*      FALSE,     ? SELECT 7.2 = (3.8::FLOAT + 3.4) -- ) SELECT (20/25)*25.0;

إجابات على 1.1

ج) الجواب: 1
فقط سيتم عرض الجزء بأكمله ، لأنه تستخدم العملية أعداد صحيحة. غالبًا ما يتم العثور عليه بلغات أخرى.

ب) الجواب: لن يتم تنفيذ الطلب .

متوسط يعطي خطأ منذ ذلك الحين يقبل فقط الأرقام والفترات الزمنية *

ومع ذلك ، يمكن تنفيذ دالة min / max على البيانات النصية (وفقًا للترتيب الأبجدي في قاعدة البيانات).
في بعض الأحيان قد يكون هذا مفيدًا عندما تحتاج إلى النظر على الأقل إلى عمود غير مدرج في GROUP BY
أو عندما تحتاج إلى تطبيق الفرز الأبجدي على الأرقام ، حيث "10" <"2"

ب) الجواب: خطأ

قد يبدو الأمر غريبًا ، لكن هذا مقبول ، لأنه هذه ميزة لجهاز كمبيوتر يمثل بعض أرقام الفاصلة العائمة ؛ يمكن أن يتخذ الرقم الشكل 7.1 (9)
أتذكر كيف تعاملت مع أحد الطلبات لفترة طويلة دون أن أعرف ذلك.

د) الجواب: 0 . المصيد هو أن التعبير بين قوسين سيكون = 0

SELECT (20 / 25.0) * 25 يعمل بشكل صحيح

1.2 بالنظر إلى الجدول " table_2 " (مع عمود " قيمة " (INTEGER)) الذي يتكون من الصفوف الخمسة التالية:

القيمة
5
5
لا شيء
5
5

ما هي النتيجة التي ستعرض الاستعلام:

 SELECT (avg(value)*count(*)) - sum(value) FROM table_2;

خيارات الإجابة

-4
0
لا شيء
5
سوف يسبب خطأ بسبب غير محدد GROUP BY
لا شيء من المذكورة

الإجابة 1.2

الجواب: 5

تتجاهل الدالات التجميعية المطبقة على عمود معين القيمة الفارغة (NULL) ، ولكن العدد (*) سيحسب كل الصفوف
5 * 5 - 20

2. قضايا عامة

2.1 في أي الحالات يمكن أن يعيد الاستعلام جميع محتويات الجدول؟ ( mother_id INTEGER ، الجدول مليء بمجموعة متنوعة من البيانات)

  SELECT * FROM any_table WHERE parent_id = parent_id;

كيف سيتصرف الطلب أدناه؟ ما البيانات التي ستنتجها؟ * PostgreSQL

  SELECT * FROM any_table WHERE parent_id IS NOT DISTINCT FROM parent_id;

أجوبة على 2.1

سيُظهر الاستعلام الأول جميع الإدخالات باستثناء تلك التي يكون الوالدين بها فارغًا

سيظهر الاستعلام الثاني جميع إدخالات الجدول. IS DISTINCT FROM يشبه منطقيا عامل التشغيل ! = حيث NULL مطابق لـ NULL
ليس مميزًا من الناحية المنطقية يحول عدم المساواة إلى المساواة

2.2. ماذا ستكون نتيجة الطلب؟

 -- ) SELECT * FROM ( SELECT 1 UNION ALL SELECT 1 ) x(y) UNION ( SELECT 2 UNION ALL SELECT 2 );

الرد على 2.2

ستكون النتيجة صفين بقيم 1 و 2 ، ستزيل UNION جميع التكرارات في التحديد الناتج ، وليس فقط بين الجدولين المرتبطين. لقد لاحظت أن هذا ليس واضحًا للجميع.

2.3 كتابة استعلام يوضح تاريخ الغد.

الرد على 2.3

 SELECT CAST((now()+ INTERVAL '1 DAY') AS DATE)

لا يعمل الجميع في الغالب مع التواريخ ، ولكن الأمر يستحق إتقان الحد الأدنى
* حل Postgres ، ولكن أعتقد أن DBMSs الأخرى لا تختلف كثيرًا

إذا كان العمل في التواريخ جديدًا بالنسبة لك ، أنصحك بتجربة الطلب
على سبيل المثال:
- استبدال DAY بـ (أسبوع ، شهر ، سنة ، إلخ.)
- استبدل +1 بـ -9000
- استبدل DATE بـ TIME
- إزالة CAST
- اترك الآن فقط ()
الخ.

مستوحاة من بعض النتائج ، اقرأ دليل ، كل الموضوعات هناك موصوفة بالتفصيل

2.4 تم تصميم عبارات UPDATE و DELETE و INSERT و MERGE لمعالجة البيانات في الجداول. هل SELECT .. الإعدام "آمن"؟ هل يمكن لأي استفسار أن يؤثر على البيانات في الجدول؟

الرد على 2.4

قد يبدو السؤال بدائيًا ، ولكن ...

في بداية تعلم SQL ، رأيت أن هذا البيان يمكن أن يعرض البيانات فقط ، ولكن:

إلى جانب حقيقة أن SELECT قادرة على قفل طاولة التغيير (BEGIN ؛ SELECT ... FOR UPDATE) *
SELECT قادر على استدعاء الوظائف التي يمكنها أداء أي معالجة تقريبًا.

يحتاج المبتدئون إلى فهم هذا على الفور ، وليس بعد إكمال طلب "المعلومات الصغيرة" على خادم ~~الإنتاج~~

3. PostgreSQL فقط

3.1 وصف ما يحدث عند تنفيذ هذا الاستعلام في مربع حوار SQL:

 SELECT * INTO wtf FROM pg_stat_activity;

الرد على 3.1

بشكل عام ، يتم استخدام SELECT INTO في دالات plpgsql لكتابة قيمة إلى متغير.

خارج plpgsql ، سيكون تأثير الأمر مشابهًا للاستعلام أدناه:

 CREATE TABLE wtf AS SELECT * FROM pg_stat_activity;

3.2 ما سيظهره هذا الطلب "البسيط"

 SELECT wtf_ FROM pg_stat_activity AS wtf_ ;

الرد على 3.2

عرض نظام pg_stat_activity (VIEW) للعمليات النشطة في قاعدة البيانات.

تكمن خصوصية الاستعلام في أنه سيتم عرض عمود واحد يحتوي على صفوف (ROW) لها TYPE pg_stat_activity (أو جدول آخر). تحتاج إلى معرفة ذلك في وقت أقرب لأولئك الذين يكتبون وظائف. يمكنك قراءة المزيد في الدليل.
تمت إضافة السؤال لأنه يمكن للمبتدئ بسهولة الحصول على هذه النتيجة عن طريق الخطأ ، ولا يفهم ما هو الأمر

4. العمل مع النص. التعبيرات العادية

أعتقد أنك بحاجة إلى أن تكون قادرًا ليس فقط على بناء الاستفسارات ، ولكن أيضًا تقديم النتائج بالطريقة الصحيحة.
تعد التعبيرات العادية موضوعًا ضخمًا منفصلاً ، مع العديد من المقالات عالية الجودة. لذلك ، سوف أعرض أمثلة فقط ، بدون توضيحات تفصيلية.

4.1. افترض أن هناك جدول " table_5 " مع عمود نص " X " والعديد من الصفوف المختلفة. ما الاستعلام الذي يمكن أن يحصل على آخر 10 أحرف في كل سطر؟

الرد على 4.1

يسمح لك SQL بالخروج بالعديد من الحلول لنفس المشكلة ، على سبيل المثال:
أبسط ما يتبادر إلى الذهن هو الصحيح (X ، 10)
يمكن استخدام regex: سلسلة فرعية (X، '. {0،10} $')
يمكنك حتى ~~nakostylyat~~ "مراوغة" (من جميع ~~النواحي~~ ) مثل هذا: عكس (سلسلة فرعية (عكس (X) لمدة 10))

4.2 يوجد جدول "table_6" مع عمود نص "X". يحتوي الجدول على صف واحد (كل النص بالإنجليزية والروسية فقط):

 'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777'

أ) اكتب استعلامًا سيُرجع الأحرف 42 إلى 68 من هذه السلسلة
ب) كيف يتم استخراج الأحرف الرأسمالية (الروسية أو الإنجليزية) فقط في سلسلة باستخدام SQL؟
ج) كيفية حساب مجموع الأرقام ( وليس الأرقام ) في سلسلة باستخدام SQL

رسم SQL

 WITH table_6(X) AS( SELECT 'Lorem 3 Ipsum 23 standard 7 dummy 17 text Ultimate Answer of Life ?? 777'::TEXT ) SELECT X FROM table_6

إجابات على 4.2

  --    WITH  "SQL " -- ) SELECT SUBSTRING(LEFT(X,68) FROM 42 ) FROM table_6 -- 1  SELECT SUBSTRING(X, 42, (68-42)+1) FROM table_6 -- 2  -- 3    -- )  ,        SELECT regexp_replace(X,'[^A-Z-]', '','g') FROM table_6 --  ''      - --   'g'    1  -- )        --   regexp_matches   **     ,      SELECT sum(x[1]::INT) FROM ( SELECT regexp_matches(X,'[0-9]+','g') FROM table_6 ) AS y(x) -- *        -- **   +,     (   1 ,     )

4.3. كيف يمكن استبدال كل الفراغات المزدوجة (الثلاثية أو أكثر) بمسافة واحدة في النص (خلية الجدول)؟ (حسب التقليد: الجدول " table_7 " مع العمود " X ") (ملاحظة: سيكون كافياً لكتابة SELECT التي تُرجع النتيجة المطلوبة ، وليس UPDATE table_7 ... )

الرد على 4.3

 WITH table_7(X) AS (SELECT 'Lorem 3 Ipsum 23 standard 7 dummy 11 text'::TEXT) -- 1 .    (2   ) SELECT regexp_replace(X, '( ){2,}', ' ', 'g') FROM table_7 -- 2 .     (,  ,    ..)   ,      SELECT regexp_replace(X, '\s+', ' ', 'g') FROM table_7 --  !  ,  -    ,    "" .    .. --   ,    , ,        --    ,      ,    SELECT replace(replace(replace(X, ' ', '<>'), '><', ''), '<>', ' ') FROM table_7

4.4 هناك سلسلة " X " يسمح فيها بالأخطاء المطبعية. بدلاً من الحروف الروسية (e ، o ، s ، C) ، تم استخدام أحرف مماثلة ظاهريًا للأبجدية الإنجليزية. استبدل هذه الأحرف بـ SQL.

ملاحظة: يجب أن يحتوي الخط على أحرف روسية فقط ، ولا داعي للقلق بشأن تغيير محتمل في الكلمات الإنجليزية.

(إذا كنت تواجه صعوبة في استبدال جميع الأحرف ، فاستبدل حرفًا واحدًا على الأقل)

سطر المثال:

X = 'Coeo eoc oe'

الرد على 4.4

 -- , Replace(Replace(Replace(..  ,  --        (1   1 ) SELECT TRANSLATE('Coeo  eoc oe', 'Cceo', '')

4.5 كتابة استعلام يحول سلسلة:
" إيفان إيفانوف وإيفانوفيتش" إلى الأنواع "إيفان إيفانوف"

الرد على 4.5

 --  ,     SELECT initcap('  ') *

السعي المكافأة لأولئك الذين تعاملوا

~~عظيم إذا كان هناك وظيفة جاهزة~~
هل يمكنك تحويل العكس؟ (يفضل بدون فقدان الحشو).
ربما لم تكن المهمة نموذجية ، لكنها ستكون مفيدة للتنمية.

'إيفانوف إيفان إيفانوفيتش' يعتنق 'إيفانوف إيفان إيفانوفيتش'
~~وعكس الحالة؟~~

استجابة تحدي المكافأة

 SELECT string_agg(LOWER(LEFT(x,1)) || UPPER(SUBSTRING(x from 2)), '' ORDER BY rn) FROM (SELECT * FROM regexp_split_to_table('    4 TesT', '\y') WITH ORDINALITY y(x, rn) ) AS z -- *  PostgreSQL,      --    ,     --      ,      --      . -- WITH ORDINALITY      (   9.4) --      --          -- ..

5. قليلا عن المعاملات

تعتبر المعاملات أمرًا مهمًا جدًا في نظام إدارة قواعد البيانات (DBMS) ؛ فمن المهم فهم النقاط الرئيسية.

سأحاول محاكاة مثال:

افترض أن هناك جدول "بضائع" يعمل فيه مستخدمان.
يحتوي على عمود خصم صحيح يساوي 10 لجميع الصفوف.
إعدادات قاعدة البيانات قياسية (READ COMMITTED - قراءة البيانات الملتزمة).

يفتح المستخدم User_1 معاملة ، وينفذ الطلب التالي:

 BEGIN; UPDATE goods SET discount = discount + 5;

ثانية ثانية ، مستخدم آخر ( User_2 )
ينفذ نفس الطلب تقريبًا دون فتح معاملة:

 UPDATE goods SET discount = discount + 10;

ما رأيك سيحدث في المواقف التالية:

ج) ما هي النتيجة التي سيحصل عليها المستخدم_2 إذا ترك المستخدم_1 المعاملة مفتوحة (أي لم يؤكد المعاملة / لا يتراجع عن التغييرات)؟
ما سيراه المستخدم_1 عند الطلب:

 SELECT discount FROM goods LIMIT 1;

ب) ماذا يحدث إذا قام المستخدم_1 بـ ROLLBACK؟ ما هي النتائج التي سيحصل عليها User_2؟

س) ماذا يحدث إذا لم يلتزم User_1؟ ما هي النتائج التي سيحصل عليها User_2؟

الإجابات

على حد علمي ، READ UN CommitTED غير مدعوم في PostgreSQL ، ولا يمكن قراءة البيانات القذرة (غير المؤكدة)

ستكون الإجابات كما يلي:

أ) سينتظر طلب User_2 إلى COMMIT أو ROLLBACK من User_1. (يبدو أن الطلب يتجمد)
سيرى User_1 في معاملته نسخته من لقطة قاعدة البيانات ، حيث يساوي الخصم بالفعل 15

ب) إذا قام المستخدم_1 بعمل ROLLBACK ، فستظل قيمة الخصم كما هي ، ثم سيتم تنفيذ User_2 ، مما سيضيف 10 إلى الخصم وسيكون الخصم 20

ج) إذا قام المستخدم_1 بالالتزام ، فستزداد قيمة الخصم بمقدار 5 ، ثم سيتم تنفيذ User_2 ، مما سيضيف 10 إلى الخصم وسيكون الخصم 25

نسخة أخرى من هذه المهمة

نسخة مختلفة قليلاً من المهمة 13 من المستخدم kirill_petrov على READ COMMITTED

 --      CREATE TABLE goods (discount) AS (SELECT 10::INT UNION ALL SELECT 15); -- 1. User_1   (  ): BEGIN; UPDATE goods SET discount = discount + 5; --2. User_2  : UPDATE goods SET discount = discount + 100 WHERE discount = 15 --3. User_1  COMMIT;

ما البيانات التي ستكون في الجدول؟

الخلاصة

أعتقد أن تطرق إلى نقاط مثيرة للاهتمام للغاية.

آمل أن تساعد المهام في تحفيز المبتدئين ، لأنه أمر ممل أن تتعلم شيئًا بدون أهداف / أهداف / اتجاهات محددة.

سأكون سعيدًا لأولئك الذين كان من السهل الإجابة على جميع الأسئلة. وآمل أن أولئك الذين واجهوا صعوبات حصلوا على ~~ركلة في~~ اتجاه التنمية. أولئك الذين لا يفهمون الكثير ، لكنهم يريدون تعلم SQL ، أدعو دورة المقاتلين الشباب PostgreSQL إلى مقالتي الأخيرة.

أتطلع إلى أي إضافات أو حلول لمشاكل مثيرة للاهتمام بشكل خاص (يمكنك التعدين) وتعليقات أخرى!

شكرا لكم على اهتمامكم! أتمنى لك النجاح في تعلم SQL!