اخبارويندوز

PostgreSQL شرح

في هذه المقالة سنلقي نظرة على ماهية استعلام SQL وكيفية استخدامه مع قواعد بيانات PostgreSQL في المواقف المختلفة. نقدم 50 نموذجًا لأنواع SQL والاستعلامات مع وصف وظائفها وكيفية استخدامها في PostgreSQL.

ما هو SQL؟

SQL هي اختصار لعبارة “لغة الاستعلام الهيكلية”. في الأصل ، كان يطلق عليه اسم SEQUEL (لغة الاستعلام الإنجليزية المهيكلة) وكان يستخدم لتخزين ومعالجة البيانات في قواعد البيانات. تُستخدم SQL اليوم لأداء جميع أنواع عمليات البيانات في أنظمة إدارة قواعد البيانات العلائقية (RDBMS).

SQL هي لغة قوية حيث يمكننا إجراء مجموعة واسعة من العمليات:

  • تنفيذ الاستفسارات
  • ابحث عن المعلومة
  • إدراج وتحديث وحذف السجلات في قاعدة البيانات (عمليات DML)
  • إنشاء كائنات جديدة في قاعدة بيانات (عمليات DDL)
  • تعيين الأذونات على الجداول والإجراءات والوظائف وطرق العرض
  • والكثير الكثير…

❤️ قد يهمك: تحميل tftp2

ما هو معيار ANSI SQL؟

أنشأ المعهد الوطني الأمريكي للمعايير (ANSI) معيارًا لـ SQL في عام 1986 ، وتم اعتماده من قبل المنظمة الدولية للتوحيد القياسي (ISO) في عام 1987. لكي تتوافق كل RDBMS مع معيار ANSI ، يتعين عليهم جميعًا دعم التخصص الرئيسي الأوامر ، مثل DML ، بطريقة مماثلة قدر الإمكان. يمكن العثور على مزيد من المعلومات حول معيار ANSI على صفحة SQL Wikipedia .

يتبع SQL معايير ANSI / ISO ، ولكن هناك إصدارات مختلفة من لغة SQL المستخدمة من قبل أنظمة قواعد البيانات المختلفة. على سبيل المثال ، في PostgreSQL يمكننا إجراء عملية INSERT باستخدام RETURNING clauses ، والتي لا تستطيع جميع قواعد البيانات الأخرى القيام بها.


    [centos@tushar-ldap-docker bin]$ ./psql postgres
    psql.bin (11.9.17)
    Type "help" for help.
    postgres=# create table tyu(n int);
    CREATE TABLE
    postgres=# insert into tyu values(1),(2) returning *;
    n 
    ---
    1
    2
   (2 rows)
   INSERT 0 2 
  

ولكن لكي تتوافق مع معيار ANSI ، فإن جميع قواعد البيانات تدعم الأوامر (مثل DELETE ، UPDATE ، SELECT ، INSERT) بنفس الطريقة – أي أن بناء الجملة يجب أن يعمل في أي مكان.

إذا تحققنا من توثيق PostgreSQL لعبارة INSERT ، فستتم مناقشة مطابقتها لمعيار SQL في قسم التوافق بالصفحة:

التوافق

يتوافق INSERT مع معيار SQL ، باستثناء أن عبارة RETURNING هي امتداد PostgreSQL ، وكذلك القدرة على استخدام WITH مع INSERT ، والقدرة على تحديد إجراء بديل باستخدام ON CONFLICT. أيضًا ، لا يسمح المعيار بالحالة التي يتم فيها حذف قائمة أسماء الأعمدة ، ولكن لا يتم ملء جميع الأعمدة من عبارة أو استعلام VALUES.

يحدد معيار SQL أنه لا يمكن تحديد قيمة تجاوز النظام إلا إذا كان عمود الهوية الذي تم إنشاؤه موجودًا دائمًا. تسمح PostgreSQL بالشرط في أي حال وتتجاهله إذا لم يكن قابلاً للتطبيق.

تتضمن وثائق PostgreSQL وصفًا واضحًا لأي اختلافات عن معيار SQL لأي أمر ، بما في ذلك:

  • DML (إدراج ، تحديد ، حذف)
  • DDL (إنشاء ، تبديل ، اقتطاع ، إسقاط ، إعادة تسمية)
  • TCL (COMMIT ، ROLLBACK ، SAVEPOINT)
  • DCL (منح وإلغاء)

🙂 اقرأ ايصا: ادخل تطبيق الراجحي يطلعني

ضمن قسم التوافق لكل أمر.

أمثلة استعلام SQL

دعنا الآن نستكشف بعض الأمثلة على استعلامات PostgreSQL الشائعة والمفيدة التي يمكن استخدامها في مواقف مختلفة.

 

1. إنشاء استعلام جدول في PostgreSQL

CREATE TABLE هي كلمة أساسية ستنشئ جدولًا جديدًا فارغًا في قاعدة البيانات. سيكون الجدول مملوكًا للمستخدم الذي أصدر هذا الأمر.


      postgres=# create table dummy_table(name varchar(20),address text,age int);
      CREATE TABLE
    

2. أدخل الاستعلام في PostgreSQL

يتم استخدام الأمر INSERT لإدراج البيانات في جدول:


        postgres=# insert into dummy_table values('XYZ','location-A',25);
        INSERT 0 1
        postgres=# insert into dummy_table values('ABC','location-B',35);
        INSERT 0 1
        postgres=# insert into dummy_table values('DEF','location-C',40);
        INSERT 0 1
        postgres=# insert into dummy_table values('PQR','location-D',54);
        INSERT 0 1
    

3. حدد الاستعلام بدون شرط WHERE في PostgreSQL

يتم استخدام الأمر SELECT (عند استخدامه بدون شرط WHERE الاختياري) لجلب جميع البيانات من جدول قاعدة البيانات:


        postgres=# select * from dummy_table;
        name |  address   | age 
        ---------+--------------+ -----
        XYZ   | location-A |  25
        ABC   | location-B |  35
        DEF   | location-C |  40
        PQR   | location-D |  54
        (4 rows)
      

4. تحديث الاستعلام في PostgreSQL

يتم استخدام UPDATE لإجراء تحديثات على البيانات أو الصف (الصفوف) في جدول قاعدة البيانات. في المثال أدناه نستخدم UPDATE لتغيير عمر شخص اسمه “PQR”:


        postgres=# update dummy_table set age=50 where name='PQR';
        UPDATE 1
        postgres=# select * from dummy_table;
        name |  address   | age 
        --------+--------------+-------
        XYZ  | location-A |  25
        ABC  | location-B |  35
        DEF  | location-C |  40
        PQR  | location-D |  50
        (4 rows)
      

بعد ذلك ، سنستخدم الأمر UPDATE لتغيير اسم وعمر شخص عنوانه “location-D”:


postgres=# update dummy_table set name='GHI',age=54 where address='location-D';
UPDATE 1
postgres=# select * from dummy_table;
 name |  address   | age 
------+------------+-----
 XYZ  | location-A |  25
 ABC  | location-B |  35
 DEF  | location-C |  40
 GHI  | location-D |  54
(4 rows)

postgres=# 

إذا أردنا تعديل جميع القيم في أعمدة العنوان والعمر في dummy_table ، فلن نحتاج إلى استخدام جملة WHERE. سيبدو استعلام التحديث كما يلي:


postgres=# update dummy_table set age=54,address='location-X';
UPDATE 4


postgres=# select * from dummy_table ;
 name |  address   | age 
------+------------+--------
 XYZ  | location-X |  54
 ABC  | location-X |  54
 DEF  | location-X |  54
 GHI  | location-X |  54
(4 rows)

postgres=# 

تقوم عبارة RETURNING بإرجاع الصفوف المحدثة. هذا اختياري في UPDATE:


postgres=# update dummy_table set age=30 where name='XYZ' returning age as age_no;
 age_no 
---------
      30
(1 row)

UPDATE 1

يوصى دائمًا بإجراء مثل هذه العمليات ضمن كتل المعاملات (على سبيل المثال ، BEGIN … COMMIT / ROLLBACK ؛) ، لذلك لدينا خيار التراجع عن العملية.

🌚 شاهد : شراء موقع الكتروني جديد

5. حذف الاستعلام في PostgreSQL

يتم استخدام الأمر DELETE لحذف الصف (الصفوف). يمكن استخدامه مع أو بدون شرط WHERE الاختياري ، ولكن لاحظ: إذا كان الشرط WHERE مفقودًا ، فسيحذف الأمر جميع الصفوف ، ويترك لك جدولًا فارغًا.

في هذا المثال ، نحذف صفًا واحدًا له قيمة عمود العمر 65:


postgres=# delete from dummy_table where age=65;
DELETE 1
postgres=# 

6. عوامل المقارنة في استعلامات PostgreSQL

في PostgreSQL ، بمساعدة عوامل المقارنة ، يمكننا العثور على نتائج حيث لا تكون القيمة الموجودة في العمود مساوية للشرط أو القيمة المحددة.

أقل من أو يساوي الاستعلام:


postgres=# select * from dummy_table where age <=50;
 name |  address   | age 
------+------------+-----
 XYZ  | location-A |  25
 ABC  | location-B |  35
 DEF  | location-C |  40
 PQR  | location-D |  50
(4 rows)

أكبر من أو يساوي الاستعلام:


postgres=# select * from dummy_table where age>=50;
 name |  address   | age 
------+------------+-----
 PQR  | location-D |  50
(1 row)

لا يساوي الاستعلام:


postgres=# select * from dummy_table where age<>50;
 name |  address   | age 
------+------------+-----
 XYZ  | location-A |  25
 ABC  | location-B |  35
 DEF  | location-C |  40
(3 rows)

يساوي الاستعلام:


postgres=# select * from dummy_table where age=50;
 name |  address   | age 
------+------------+-----
 PQR  | location-D |  50
(1 row)

7. حدد استعلامًا مميزًا في PostgreSQL

يتم استخدام عبارة SELECT DISTINCT لإرجاع القيم المميزة فقط من الجدول. يزيل أي قيم مكررة.

حدد الاستعلام بدون عبارة DISTINCT


postgres=# select age from dummy_table order by 1;
 age 
-----
   1
   1
   2
   2
   3
(5 rows)

حدد الاستعلام مع جملة DISTINCT


postgres=# select distinct age from dummy_table order by 1;
 age 
-----
   1
   2
   3
(3 rows)

8. اقتطاع الاستعلام في PostgreSQL

يتم استخدام الأمر TRUNCATE لإفراغ جدول:


postgres=# truncate table dummy_table;
TRUNCATE TABLE

9. استعلام DROP TABLE في PostgreSQL

يُستخدم أمر DROP TABLE هذا لإسقاط جدول من قاعدة البيانات:


postgresql=# drop table if exists dummy;
NOTICE:  table "dummy" does not exist, skipping
DROP TABLE

أزال هذا الأمر الجدول بالكامل ، بما في ذلك أي بيانات وفهارس وقواعد ومشغلات وقيود مرتبطة بهذا الجدول.

💙هل شاهدت: أسعار سيرفرات أمازون

10. إنشاء استعلام عرض في PostgreSQL

يتم استخدام الأمر CREATE VIEW لإنشاء طرق عرض. طرق العرض عبارة عن جداول زائفة تُستخدم لتقديم جدول كامل أو مجموعة فرعية أو تحديد أعمدة من الجدول الأساسي:


postgres=# create or replace view vi as select * from dummy_table where age is NULL;
CREATE VIEW

11. قم بإنشاء جدول في Postgresql باستخدام عبارة SELECT

باستخدام بناء الجملة في المثال أدناه ، يمكننا إنشاء جدول باستخدام عبارة SELECT:


postgres=# select 'My name  is X' as col1 , 10 as col2, 'Address is -XYZ location' as col3  into new_table;
SELECT 1
postgres=# select * from new_table ;
     col1      | col2 |           col3           
---------------+------+--------------------------
 My name  is X |   10 | Address is -XYZ location
(1 row)
       

12. مهلة الاستعلام في PostgreSQL

يمكننا إصدار أمر استعلام لانتهاء المهلة بعد فترة معينة بمساعدة معلمات GUC (اختصارًا للتكوين الموحد الكبير) مثل statement_timeout ، والذي يحبط أي عبارة تستغرق أكثر من العدد المحدد من المللي ثانية:


postgresql=# set statement_timeout=10;
SET
postgresql=# select pg_sleep(20);
ERROR:  canceling statement due to statement timeout
       

13. استخدام إنشاء تسلسل مع استعلام INSERT في PostgreSQL

الأمر CREATE SEQUENCE هو مولد رقم متسلسل. بمجرد إنشاء التسلسل ، يمكننا استخدام الدالتين التاليتين والتابعين للتسلسل لإدراج القيم في جدول:


postgres=# create sequence seq;
CREATE SEQUENCE
postgres=# create table tab(n int);
CREATE TABLE
postgres=# insert into tab values (nextval('seq'));
INSERT 0 1
postgres=# insert into tab values (currval('seq'));
INSERT 0 1
postgres=# insert into tab values (nextval('seq'));
INSERT 0 1
postgres=# select * from tab;
 n 
---
 1
 1
 2
(3 rows)

14. استيراد أنواع بيانات BLOB إلى PostgreSQL

لا تدعم PostgreSQL بشكل مباشر BLOBs (كائنات ثنائية كبيرة) ، ولكن يمكننا العمل معها باستخدام الطرق التالية:

لنفترض أن لديك صورة (بتنسيق png) تم تنزيلها في / home / edb / المجلد:


[edb@localhost]$ ls /home/edb/mypic.png 
/home/edb/mypic.png

نريد تخزين هذه الصورة في قاعدة بيانات PostgreSQL.

انتقل إلى مجلد bin الخاص بتثبيت PostgreSQL واتصل بمحطة psql:


postgres=# Create table testing(n int,n1 oid);
CREATE TABLE
postgres=# insert into testing values (1,lo_import('/home/edb/mypic.png'));
INSERT 0 1

تقوم الدالة lo_import () بتحميل الملف المسمى إلى pg_largeobject وإرجاع قيمة OID (معرف الكائن) التي ستشير إلى الكائن الكبير. سيؤدي تحديد جدول الاختبار إلى إظهار معرف الكائن فقط وليس وحدات البت التي تتكون منها هذه الصورة.

 

15. استعلام مشابه في PostgreSQL

عامل التشغيل ILIKE هو وظيفة مطابقة مشابهة لعامل LIKE ، ولكن مع ميزة أنه يطابق Valus بشكل غير حساس لحالة الأحرف.


postgres=# select * from ted;
  n  
-----
 TAR
 TaR
 Tar
 tar
(4 rows)

استخدام ILIKE في حالة WHERE


postgres=# select * from ted where n ilike 'TAR%';
  n  
-----
 TAR
 TaR
 Tar
 tar
(4 rows)

16. الاستعلامات الهرمية في PostgreSQL

الاستعلامات الهرمية هي تلك التي يكون للنتائج فيها علاقة منظمة أو علاقة أصل وطفل ويتم عرضها في بنية شجرة. لمعرفة كيفية عمل الاستعلامات الهرمية ، قم بإنشاء جدول وهمي:


create table test_table(  
  emp_no                int,  
  ename                 char(5),  
  job                       char(9),  
  manager_no        int
);

أدخل البيانات في “test_table”:


insert into test_table values(10,'A1','CEO',null);
insert into test_table values(11, 'B1', 'VP', 10);
insert into test_table values(12, 'B2', 'VP', 10);
insert into test_table values(13, 'B3', 'VP', 10);
insert into test_table values(14, 'C1', 'DIRECTOR', 13);
insert into test_table values(15, ‘C2’, ‘DIRECTOR’, 13);
insert into test_table values(16, 'D1', 'MANAGER', 15);
insert into test_table values(17 ,'E1', 'ENGINEER', 11);
insert into test_table values(18, 'E2', 'ENGINEER', 11);

يمكننا إجراء استعلامات هرمية في هذا الجدول باستخدام الطرق أدناه.

 

17. وظيفة الطول في PostgreSQL

ترجع دالة الطول عدد الأحرف أو عدد البايت في متغير سلسلة محدد.

استعلام SELECT الأساسي


postgres=# select name,age from dummy_table;
 name | age 
------+-----
 XYZ  |  25
 ABC  |  35
 DEF  |  40
 PQR  |  54
 PQR  |    
(5 rows)

استعلام مع دالة الطول لاسم العمود والعمر


postgres=# select length(name),length(age) from dummy_table;
 length | length 
--------+--------
      3 |      2
      3 |      2
      3 |      2
      3 |      2
      3 |       
(5 rows)

18. عندما لا يكون للاستعلام وجهة لبيانات النتائج في PostgreSQL

لنفترض أنه أثناء تحديد وظيفة معينة ، نتلقى رسالة الخطأ أدناه:


postgresql=# create or replace function f(n int) 
returns int 
as 
$$ 
begin 
select now();
return 1;
 end; 
$$ language 'plpgsql';
CREATE FUNCTION
postgres=# select f(9);
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.

لتجنب مثل هذه الأخطاء ، يمكننا إما استخدام PERFORM أو الإعلان عن متغير واستخدامه في عبارة SELECT INTO :

😍قد يهمك ايضا: كيفية شراء دومين مدى الحياة

باستخدام الأداء


postgres=# create or replace function f(n int) 
returns int 
as 
$$ 
begin 
perform
now(); 
 return 1; 
 end; 
$$ language 'plpgsql';
CREATE FUNCTION
postgresql=# select f(9);
 f 
---
 1
(1 row)

تعريف متغير واستخدامه في عبارة SELECT INTO


postgres=# create or replace function f(n int) 
returns int 
as 
$$ 
declare 
a date;
begin 
select now() into a;
raise notice ‘%s’,a; 
return 1; 
end; 
$$ language 'plpgsql';
CREATE FUNCTION
 
 
postgresql=# select f(9);
          NOTICE: 24-SEP-20 13:15:46.23388s
             f
            ---
            1
        (1 row)

19. تصدير نتيجة الاستعلام إلى ملف نصي في PostgreSQL

بمساعدة الأمر COPY ، يمكننا تصدير البيانات من جدول إلى ملف نصي خارجي وكذلك استيراد البيانات من ملف نصي إلى جدول.

تصدير البيانات من جدول إلى ملف نصي


postgres=#  copy dummy_table to '/tmp/abc.txt';
COPY 5
  
postgres=# \! cat /tmp/abc.txt
XYZ	location-A	25
ABC	location-B	35
DEF	location-C	40
PQR	location-D	50
CXC	1	50

استيراد البيانات من ملف نصي إلى جدول


postgres=# copy dummy_table from '/tmp/abc.txt';
COPY 5
  1. بمساعدة تعبيرات الجدول الشائعة (CTE):

    
    postgres=#WITH RECURSIVE cte AS (                                                                                                                                                          SELECT emp_no, ename, manager_no, 1 AS level                                                                                                                                             FROM   test_table                                                                                                                                                                      where manager_no is null                                                                                                                                                            UNION  ALL                                                                                                                                                                               SELECT e.emp_no, e.ename, e.manager_no, c.level + 1                                                                                                                                      FROM   cte c                                                                                                                                                                             JOIN   test_table e ON e.manager_no = c.emp_no                                                                                  )                                                                                                                                                                                     SELECT *                                                                                                                                                                                 FROM   cte;
    
    emp_no | ename | manager_no | level 
    -----------+----------+------------------+-------
         10    | A1        |                        |     1
         11    | B1        |          10          |     2
         12    | B2        |          10          |     2
         13    | B3        |          10          |     2
         14    | C1        |         13           |     3
         17    | E1        |          11          |      3
         18    | E2        |          11           |     3
         15    | C2        |          13           |     3
         16    | D1        |          15           |     4
    (9 rows)
    
    postgres=# 
    
  2. باستخدام ملحق tablefunc:

    الامتداد tablefunc هو وحدة نمطية توجد في المجلد والمساهمات في مصادر PostgreSQL.

    أولاً ، قم بإنشاء ملحق tablefunc:

    
    postgres=# CREATE EXTENSION tablefunc;
    
    CREATE EXTENSION
    
    postgres=# 
    

    بعد ذلك ، استخدم وظيفة connectby في tablefunc لعرض النتائج بشكل هرمي:

    
    postgres=# SELECT * FROM connectby('dummy_table', 'emp_no', 'manager_no', '10', 0, '->') AS t(emp_no int, manager_no int, level int, ord text) order by emp_no;
    
     emp_no | manager_no | level |      ord       
    
    --------+------------+-------+----------------
    
         10 |                       |      0        | 10
    
         11 |              10     |      1        | 10->11
    
         12 |              10     |      1        | 10->12
    
         13 |              10     |      1        | 10->13
    
         14 |              13     |      2        | 10->13->14
    
         15 |              13     |      2        | 10->13->15
    
         16 |              15     |      3        | 10->13->15->16
    
         17 |              11     |      2        | 10->11->17
    
         18 |              11     |      2        | 10->11->18
    
    (9 rows)
    
    
    postgres=# 
    

😅شاهد ايضا: ماهو dns وما هي فوائده واستخداماته؟

20. سرد الاستعلام عن قواعد البيانات في PostgreSQL

يمكن استخدام الاستعلام التالي لإظهار جميع قواعد البيانات التي تم إنشاؤها:


postgres=# select oid,datname from pg_database;
  oid     |  datname  
-----------+-----------
 13743 | postgres
     1     | template1
 13742 | template0
(3 rows) 

يمكننا أيضًا سرد جميع أسماء قواعد البيانات باستخدام الأمر \ l في موجه psql.

 

21. التحقق من وقت تنفيذ الاستعلام في PostgreSQL

يمكننا التحقق من الوقت اللازم لتنفيذ الاستعلام عن طريق تمكين \ time في موجه psql:


postgres=# \timing
Timing is on.

سيُظهر استعلام SELECT الآن وقت التنفيذ:


postgres=# select * from dummy_table;
 name |  address   | age 
------+------------+--------
 XYZ  | location-A | 25
 ABC  | location-B | 35
 DEF  | location-C | 40
 PQR  | location-D | 50
 CXC  | 1               | 50
(5 rows)
 
Time: 0.440 ms
postgres=# 

22. استعلام SQL الديناميكي في PostgreSQL

يستخدم Dynamic SQL لتقليل المهام المتكررة عندما يتعلق الأمر بالاستعلام.
لا يتم تخزين استعلامات SQL الديناميكية مؤقتًا في الذاكرة.


postgres=# do
postgres-# $$
postgres$# begin
postgres$# execute 'select * from dummy_table';
postgres$# end;
postgres$# $$;
DO

23. COUNT استعلام في PostgreSQL

يقوم الاستعلام COUNT بإرجاع عدد الصفوف في جدول. إذا استخدمنا (*) فسيشمل هذا قيمًا خالية ؛ وإلا سيتم استبعاد القيم الفارغة.


postgres=# select count(*) from dummy_table;
 count 
-------
     5
(1 row)
 
postgres=# select count(avg) from dummy_table;
 count 
-------
     4
(1 row)

24. استعلام LIMIT و OFFSET في PostgreSQL

يتم استخدام عبارة LIMIT للحد من كمية البيانات التي يتم إرجاعها بواسطة عبارة SELECT. سيعرض الاستعلام أدناه صفًا واحدًا فقط:


postgres=# select * from dummy_table  limit 1;
 name |  address   | age 
------+------------+-----
 XYZ  | location-A |  25
(1 row)

يتم استخدام OFFSET عندما نريد تخطي عدد معين من الصفوف:


postgres=# select * from dummy_table  offset 4;
 name | address | age 
------+---------+-----
 cxc  | 1       |  50
(1 row)

🤗نحن نحبك لذلك شاهد: انشاء ايميل وهمي gmail

25. إذا… تعبير آخر في PostgreSQL

يمكننا استخدام الجمل الشرطية مثل IF … ELSE في كتلة مجهولة. يتحقق المثال أدناه مما إذا كانت قيم المتغيرين abc و xyz متطابقتين ويطبع النتيجة – أي 150:


postgres=# Do 
             $$
             Declare
              abc int;
              xyz int;
              begin
              abc:=100;
              xyz:=abc;
              if abc=xyz then 
               xyz=150;  
              raise notice '%',xyz;
             else
             end if;               
             end;
               $$
;
NOTICE:  150
DO

26. التحديث باستخدام استعلام JOIN في PostgreSQL

يمكننا استخدام UPDATE مع جملة JOIN و WHERE عندما نريد تحديث القيم من جدول واحد (الجدول X) بناءً على قيم من جدول آخر (الجدول Y):


postgres=# create table X(n int, n1 char(10));
CREATE TABLE
postgres=# insert into X values (1,'abc');
INSERT 0 1
postgres=# insert into X values (2,'xyz');
INSERT 0 1
postgres=# insert into X values (3,'pqr');
INSERT 0 1

postgres=# create table Y(n int, n1 char(10));
CREATE TABLE

postgres=# insert into Y values (1,'');
INSERT 0 1
postgres=# insert into Y values (2,'');
INSERT 0 1

postgres=# insert into Y values (5,'axyz');
INSERT 0 1

postgres=# update Y set n1=X.n1 from X  where X.n=Y.n;
UPDATE 2
postgres=# select * from Y;
 n |     n1     
---+------------
 5 | axyz      
 1 | abc       
 2 | xyz       
(3 rows)

postgres=# 

27. استعلام INNER JOIN في PostgreSQL

سيجد أمر INNER JOIN صفوفًا من جدولين (أو أكثر) حيث تتطابق بيانات الأعمدة المحددة في الجداول :



postgres=# select *  from x inner join  y on  x.n1 = y.n1;
 n |     n1     | n |     n1     
---+------------+---+------------
 1 | abc        | 1 | abc       
 2 | xyz        | 2 | xyz       
(2 rows)

postgres=# 
 

28. تعبير CASE في PostgreSQL

تعبير CASE هو تعبير شرطي عام ، مشابه لعبارة IF… ELSE.


postgres=# SELECT age,
       CASE age WHEN 25 THEN 'one'
              WHEN 50 THEN 'two'
              ELSE 'other'
       END
    FROM  dummy_table;
 age | case  
-----+-------
  25 | one
  35 | other
  40 | other
  50 | two
  50 | two
(5 rows)

29. استعلام تكراري PostgreSQL

تُستخدم الاستعلامات التكرارية للتعامل مع الاستعلامات الهرمية أو البيانات الهيكلية الشجرية. دائمًا ما تكون بنية استعلام WITH RECURSIVE: أ) مصطلح غير متكرر
ب) UNION (أو UNION ALL) ، ثم مصطلح تكراري

حيث يتضمن المصطلح العودي إشارة إلى إخراج الاستعلام.


CREATE TABLE emp_test (
  id int,
  ename varchar(255),
  emanager int
);

INSERT INTO emp_test VALUES (1, 'abc', null);
INSERT INTO emp_test VALUES (2, 'xyz', 1);
INSERT INTO emp_test VALUES (3, 'def', 2);
INSERT INTO emp_test VALUES (4, 'cde', 1);
INSERT INTO emp_test VALUES (5, 'qrs', 2);
INSERT INTO emp_test VALUES (9, 'iop', 3);
INSERT INTO emp_test VALUES (10, 'klm', 4);

سيعطي الاستعلام العودي أدناه جميع التقارير بترتيب معين:


postgres=#WITH RECURSIVE emp_testnew  AS (
  SELECT id, ename, emanager
  FROM emp_test
  WHERE id = 2
  UNION ALL
  SELECT e.id, e.ename, e.emanager
  FROM emp_test e
  INNER JOIN emp_testnew e1 ON e1.id = e.emanager
)
SELECT *
FROM emp_testnew;

 id | ename | emanager 
----+-------+----------
  2 | xyz   |        1
  3 | def   |        2
  5 | qrs   |        2
  9 | iop   |        3
(4 rows)

postgres=# 

30. استعلامات سجل PostgreSQL

باستخدام الوحدة النمطية pg_stat_statements ، يمكننا تتبع إحصائيات التنفيذ لجميع جمل SQL. للقيام بذلك ، نحتاج إلى إنشاء امتداد وإضافته في shared_preload_libraries داخل ملف postgresql.conf:

postgres=# create extension pg_stat_statements;
CREATE EXTENSION


postgres=# show shared_preload_libraries ;
                           shared_preload_libraries                           
-------------------------------------------------
 $libdir/pg_stat_statements
(1 row)


postgres=# select query from pg_stat_statements where query like 'create table%';
-[ RECORD 1 ]-------------------
query | create table test(n int)

يمكننا أيضًا تكوين PostgreSQL لإنشاء مخرجات السجل من خلال تمكين هذه المعلمات في ملف postgresql.conf:


logging_collector = on
log_directory = 'log'
log_filename = ‘postgresql-%Y-%m-%d_%H%M%S.log'
log_destination = ‘stderr’
Log file will be created under the pg_log directory which resides under the data folder. 

[centos@tushar-ldap-docker bin]$ ls  data/log
postgresql-2020-09-17_150932.log  postgresql-2020-09-19_000000.log  
[centos@tushar-ldap-docker bin]$ 

سيتم تسجيل الاستعلامات في هذه الملفات.

🙂 اقرأ ايصا: كيف اطلع بطاقة باي بال

31. استخدام متغير في استعلام PostgreSQL

يمكننا الإعلان عن متغير في PostgreSQL في موجه psql:


postgres=# \set cond 50

استخدام متغير في شرط WHERE


postgres=# select * from dummy_table where age=:cond;
 name |  address   | age 
------+------------+-----
 PQR  | location-D |  50
(1 row)

أو


postgres=# \set cond 50
postgres=# select :cond+100 ;
 ?column? 
----------
      150
(1 row)

32. الاستعلام عن التاريخ في PostgreSQL

تقدم PostgreSQL وظائف للتاريخ والوقت يمكن استخدامها في الاستعلامات.


postgres=# select now();
               now                
----------------------------------
 22-SEP-20 03:08:42.636385 +05:30
(1 row)

postgres=# select current_date;
 current_date 
--------------
 22-SEP-20
(1 row)

postgres=# select current_time;
     current_time      
-----------------------
 03:08:53.648466+05:30
(1 row)

يمكننا أيضًا إجراء استعلام عن نطاق التاريخ للعثور على صفوف ذات قيم بين طابعين زمنيين:


postgres=# create table datetable(n int,n1 date);
CREATE TABLE

postgres=# insert into datetable values (1,'12-01-1980');
INSERT 0 1
postgres=# insert into datetable values (2,'12-01-2020');
INSERT 0 1
postgres=# insert into datetable values (3,'12-01-2000');
INSERT 0 1
postgres=# select * from datetable where n1 between '12-01-1980' and '12-01-2000';
 n |         n1         
---+--------------------
 1 | 12-JAN-80 00:00:00
 3 | 12-JAN-00 00:00:00
(2 rows)

33. وظيفة PostgreSQL RETURN QUERY نتيجة

عندما يتم الإعلان عن دالة PL / pgSQL لإرجاع SETOF بعض أنواع البيانات ، يتم تحديد الإرجاع بواسطة أمر RETURN QUERY :


postgres=# CREATE FUNCTION get(int) RETURNS SETOF integer AS
$BODY$
BEGIN
    RETURN QUERY SELECT age
                   FROM dummy_table
                  WHERE age >= $1  ;
    RETURN;
 END
$BODY$
LANGUAGE plpgsql;


postgres=# select * from get(9);
 get 
-----
  25
  35
  40
  50
(4 rows)

34. أداء الاستعلام المتوازي PostgreSQL

تتيح لك الاستعلامات الموازية في PostgreSQL إنهاء الاستعلامات بشكل أسرع من خلال استخدام العديد من وحدات المعالجة المركزية. تم تعيين معلمات GUCs هذه في ملف postgresql.conf:


#max_parallel_maintenance_workers = 2   # taken from max_parallel_workers
#max_parallel_workers_per_gather = 2    # taken from max_parallel_workers
#parallel_leader_participation = on
#max_parallel_workers = 8               # maximum number of max_worker_processes that
                                                           # can be used in parallel operations


postgres=# create table ty(n int);
CREATE TABLE
postgres=# insert into ty values (generate_series(1,300000));
INSERT 0 300000

postgres=# analyze ty;
ANALYZE
postgres=# explain  select * from ty where n<=1;
                             QUERY PLAN                              
---------------------------------------------------------------------
 Gather  (cost=1000.00..4536.88 rows=30 width=4)
   Workers Planned: 1
   ->  Parallel Seq Scan on ty  (cost=0.00..3533.88 rows=18 width=4)
         Filter: (n <= 1)
(4 rows)

postgres=# 

35. العوامل المنطقية في PostgreSQL

هناك ثلاثة عوامل تشغيل منطقية أساسية متوفرة في PostgreSQL: AND و OR و NOT.

تُستخدم عوامل التشغيل هذه لمطابقة الشروط في عبارات SQL – على سبيل المثال ، في جمل WHERE و HAVING.


AND = when both boolean expressions are true then it will return TRUE
OR   = when any boolean expression is true then it will return TRUE
NOT = reverses the value of Boolean operator 

بعض أمثلة العمليات المنطقية


  • If both expressions are true, then the result is TRUE. postgresql=# select 1=1/1 and 200=2+198 as result_and_operator; result_and_operator --------------------- t (1 row) postgresql=#
  • If one expression is true and another expression is NULL, then the result is NULL. postgresql=# select 4=4 and null; ?column? ---------- (1 row)
  • If one expression is true and another expression is false, then the result is TRUE. postgres=# select 1=100 OR 2=2; ?column? ---------- t (1 row)

 

36. اصطياد الصفوف المكررة في جدول PostgreSQL

في استعلام SQL التالي ، يوجد سجلين بالقيمة 50:


 postgres=# select age from dummy_table;
 age 
-----
  25
  35
  40
  50
  50
(5 rows)

يمكننا استخدام استعلام SELECT… HAVING التالي للعثور على الصفوف المكررة:


postgres=#  select age, count(age) from dummy_table group by age having count(age)>1;
 age | count 
-----+-------
  50 |     2
(1 row)

37. تعداد الاستعلام في PostgreSQL

الأنواع المعدودة (التعداد) هي أنواع بيانات تشتمل على مجموعة قيم ثابتة ومرتبة.


postgres=# CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TYPE
postgres=# create table testi(n int, n1 mood);
CREATE TABLE
postgres=# insert into testi values (1,'happy');
INSERT 0 1
postgres=# insert into testi values (1,'sad');
INSERT 0 1
postgres=# insert into testi values (1,'ok');
INSERT 0 1

إذا لم يتم تحديد التعداد ، فسيظهر خطأ:


postgres=# insert into testi values (1,'happyo');
ERROR:  invalid input value for enum mood: "happyo"

38. استعلام محوري في PostgreSQL

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


CREATE TABLE newtb(id SERIAL, rowid varchar(10), attri varchar(10), val varchar(10));
INSERT INTO newtb(rowid, attri, val) values('t1','a1','v1');
INSERT INTO newtb(rowid, attri, val) values('t1','a2','v2');
INSERT INTO newtb(rowid, attri, val) values('t1','a3','v3');
INSERT INTO newtb(rowid, attri, val) values('t1','a4','v4');
INSERT INTO newtb(rowid, attri, val) values('t2','a1','v5');
INSERT INTO newtb(rowid, attri, val) values('t2','a2','v6');
INSERT INTO newtb(rowid, attri, val) values('t2','a3','v7');
INSERT INTO newtb(rowid, attri, val) values('t2','a4','v8');

لإنشاء جدول محوري ، تحتاج إلى تثبيت ملحق tablefunc :


postgres=# create extension tablefunc;
CREATE EXTENSION

Select *
FROM crosstab(
  'select rowid, attri, val
   from newtb
   where attri = ''a2'' or attri = ''a3''
   order by 1,2')
AS newtb(row_name varchar(10), category_1 varchar(10), category_2 varchar(10), category_3 varchar(10));
 row_name | category_1 | category_2 | category_3 
----------+------------+------------+--------------------------
   t1       |              v2        |            v3    | 
   t2       |             v6         |             v7   | 
(2 rows)

39. استعلام SELF JOIN في PostgreSQL

عندما ننضم إلى جدول ضد نفسه ، فإن هذا يسمى SELF JOIN. يمكن القيام بذلك باستخدام INNER JOIN أو LEFT JOIN. تعد SELF JOINs مفيدة عند مقارنة أعمدة الصفوف داخل نفس الجدول:


postgres=# create table emp1(emp_id int, firstname char(10), lastname char(10) , manager_id int);
CREATE TABLE
postgres=# 
postgres=# 
postgres=# insert into emp1 values(1,'ABC','XYZ',NULL);
INSERT 0 1
postgres=# insert into emp1 values(2,'TYU','BGV',1);
INSERT 0 1
postgres=# insert into emp1 values(3,'TEU','ZZV',1);
INSERT 0 1
postgres=# insert into emp1 values(4,'REU','AZV',2);
INSERT 0 1
postgres=# insert into emp1 values(5,'QREU','WZV',2);
INSERT 0 1
postgres=# insert into emp1 values(6,'DREU','QZV',3);
INSERT 0 1


postgres=# select a.firstname,b.lastname from emp1 a inner join emp1 b on a.emp_id=b.manager_id order by 1 ;
 firstname  |  lastname  
------------+------------
 ABC        | ZZV       
 ABC        | BGV       
 TEU        | QZV       
 TYU        | WZV       
 TYU        | AZV       
(5 rows)

postgres=# 

40. استعلام متكرر بين الوالدين والطفل في PostgreSQL

بمساعدة تعبيرات الجدول الشائعة (CTE) ، يمكننا إجراء استعلامات عودية بين الوالدين والطفل:


postgres=# CREATE TABLE recu_pc (
 id SERIAL PRIMARY KEY,
 name varchar(10) NOT NULL,
 parent_id integer );
CREATE TABLE
 
postgres=# insert into recu_pc values (1, 'Grandmother', NULL);
INSERT 0 1
postgres=# insert into recu_pc values (2, 'mother', 1);
INSERT 0 1
postgres=# insert into recu_pc values (3, 'daughter', 2);
INSERT 0 1

 
postgres=# WITH RECURSIVE rec_q (id) as
(          
  SELECT recu_pc.id, recu_pc.name from recu_pc where name='mother'
  UNION ALL
  SELECT recu_pc.id, recu_pc.name from rec_q, recu_pc where recu_pc.parent_id = rec_q.id
  )
SELECT *
FROM rec_q;
 id |   name   
----+----------
  2 | mother
  3 | daughter
(2 rows)

41. تحديد متغير في استعلام في PostgreSQL

باستخدام كتلة مجهولة ، يمكننا تحديد متغير يمكن تمريره في استعلام:


postgres=# do 
$$
declare 
a int;
begin 
select age into a from dummy_table  
where name ='XYZ';
raise notice '%',a; 
end;
$$;
NOTICE:  25
DO

42. إعداد بيان في PostgreSQL

يتم استخدام بيان معدة لتحسين الأداء. عندما يتم تنفيذ جملة PREPARE ، لا يتم تحليلها فحسب ، بل يتم تحليلها أيضًا ، وعندما نقوم بإطلاق أمر التنفيذ ، يتم تخطيط وتنفيذ البيان المُعد.

يمكن أن تقبل البيانات المعدة المعلمات.


postgres=# prepare test(int) as 
select * from dummy_table where age=$1;
PREPARE
 
postgres=# execute test(50);
 name |  address   | age 
------+------------+---------
 PQR  | location-D |  50
 CXC | 1                |  50
(2 rows)

43. فحص القيم الفارغة في PostgreSQL

لتعريف أو تحديد الصفوف التي تحتوي على قيم NULL ، يمكن استخدام شرط IS NULL في جملة WHERE.


postgres=# select * from dummy_table;
 name |  address   | age 
------+------------+-----
 XYZ  | location-A |  25
 ABC  | location-B |  35
 DEF  | location-C |  40
 PQR  | location-D |  54
 PQR  | location-D |    
(5 rows)

تحديد القيم الخالية في عمود “العمر”:


postgres=# select name from dummy_table where age is null;
 name 
------
 PQR
(1 row)

44. التحقق مما إذا كان الاستعلام فارغًا في PostgreSQL

يمكننا استخدام EXISTS للتحقق مما إذا كان الاستعلام فارغًا. EXISTS هو عامل تشغيل منطقي يختبر وجود صفوف في طلب بحث فرعي.


postgres=# select exists(select * from (select 'true' where 1=3));
 exists 
--------
 f
(1 row)

postgres=# select exists(select * from (select 'true' where 1=1));
 exists 
--------
 t
(1 row)

45. سجل وقت تنفيذ الاستعلام في PostgreSQL

للاطلاع على سجل وقت تنفيذ الاستعلام ، تحتاج إلى تمكين معلمات GUC ذات الصلة :


postgresql=# set log_min_duration_statement=0;
SET
postgresql=# set log_statement='all';
SET

الآن ، إذا تحققنا من ملف السجل ، الذي تم إنشاؤه في مجلد البيانات / السجل ، فيجب أن نتلقى رسائل وقت التنفيذ:


2020-09-23 02:47:12.887 +0530 [30737] LOG:  statement: create table gg1(n int);
2020-09-23 02:47:12.888 +0530 [30737] LOG:  duration: 1.165 ms
2020-09-23 02:47:28.092 +0530 [30737] LOG:  statement: insert into gg1 values (generate_series(1,100000));
2020-09-23 02:47:28.182 +0530 [30737] LOG:  duration: 94.858 ms 

46. ​​تشغيل استعلام في برنامج نصي صدفة في PostgreSQL

يمكننا استخدام سكربت bash shell لتنفيذ أوامر psql. المثال أدناه هو ملف print.sh نقوم فيه بإضافة رقمين:


[edb@tushar-ldap-docker misc]$ cat print.sh 
#!/bin/bash
# This script is used to print addition of 2 numbers
# 1. Get path of bin directory.
BIN_DIRECTORY='/usr/psql-11/bin/'
$BIN_DIRECTORY/psql -U  postgres -p 5432 -d postgres -c 'select  5+5;'

قم بتنفيذ البرنامج النصي shell.


edb@tushar-ldap-docker misc]$ print.sh 
 ?column? 
----------
       10
(1 row)

47. مشغل الاتحاد في PostgreSQL

يتم استخدام UNION لدمج نتائج عبارتين أو أكثر من عبارات SQL مع التخلص من الصفوف المكررة.


postgres=# create table tab1(n int); 
CREATE TABLE

postgres=# insert into tab1 values (1),(2);
INSERT 0 2
postgres=# create table tab2(n int); 
CREATE TABLE

postgres=# insert into tab2 values (3),(2);
INSERT 0 2

postgres=# select * from tab1 union select * from tab2;
 n 
---
 2
 1
 3
(3 rows)

48. تحسين الاستعلام في PostgreSQL

فيما يلي بعض الأدوات المتاحة لتحسين أداء استعلام PostgreSQL:

لسوء الحظ ، لا توجد صيغة ثابتة واحدة لتحسين استعلامات PostgreSQL ، حيث يمكن أن يتأثر أداء الاستعلام بالعديد من الأشياء (على سبيل المثال ، قد يتأثر الأداء بمشكلة في الأجهزة).

  1. يشرح
  2. مكنسة
  3. تحليل
  4. فهارس قاعدة البيانات
  5. معلمات تكوين PostgreSQL
  6. يشرحيوضح الأمر EXPLAIN خطة تنفيذ البيان. ستؤدي إضافة الكلمة الأساسية ANALYZE في خطة التفسير إلى تنفيذ الاستعلام وعرض عدد الصفوف الحقيقي ووقت التشغيل الحقيقي.
  7. مكنسةيزيل الأمر VACUUM بشكل أساسي جميع المجموعات التي تم حذفها أو جعلها قديمة بواسطة التحديث ولكن لم تتم إزالتها فعليًا من الجدول.
  8. تحليلتقوم ANALYZE بجمع إحصائيات حول محتويات الجداول في قاعدة البيانات
  9. فهارس قاعدة البياناتيتيح لك إنشاء فهارس قاعدة البيانات تجنب فحص الجدول بالكامل ، مما يجعل تنفيذ الاستعلام بطيئًا للغاية.
  10. معلمات تكوين PostgreSQLقد نستخدم بعض معلمات تهيئة PostgreSQL للحصول على أداء أفضل – على سبيل المثال ، max_connections و checkpoint_segments و work_mem و random_page_cost.

 

49. استعلام ALTER TABLE في PostgreSQL

يُستخدم الأمر ALTER TABLE لإضافة أعمدة أو تعديلها في الجدول:


postgres=# alter table dummy_table add col1 int;
ALTER TABLE
postgres=# alter table dummy_table  rename col1 to col2;
ALTER TABLE
postgres=#  alter table dummy_table alter column col2 type char;
ALTER TABLE
postgres=# alter table dummy_table drop column col2;
ALTER TABLE

50. التعليق في PostgreSQL

تعطينا PostgreSQL ميزة يمكننا من خلالها تقديم تعليق حول كائن قاعدة البيانات باستخدام جملة COMMENT .


postgres=# Comment on table dummy_table  is 'This was a testing table';
COMMENT

postgres=# Comment on function f is 'Don''t update the function please' ;
COMMENT

مقالات ذات صلة

اترك تعليقاً

زر الذهاب إلى الأعلى