سلسلة sql للمحترفين مواضيع متقدمة في SQL

ستعرض هذه المقالة عددًا من المواضيع المتقدمة في SQL، مثل إدارة الصلاحيات، واستخدام ملفات XML في الاستعلامات، والمفاتيح الرئيسية والفهارس وأرقام الصفوف.

GRANT و REVOKE

تُستخدم العبارة GRANT لمنح الإذن لمستخدم ما بإجراء عملية على قاعدة البيانات، فيما تُستخدم العبارة REVOKE لسحب الإذن أو الصلاحية منه.

يمنح المثال التالي الإذن للمستخدمَين ‎User1‎ و ‎User2‎ بإجراء العمليتين ‎SELECT‎ و ‎UPDATE‎ على الجدول ‎Employees‎.

GRANT SELECT, UPDATE
ON Employees
TO User1, User2;

يسحب المثال التالي من المستخدمَين ‎User1‎ و ‎User2‎ صلاحية تنفيذ العمليتين ‎SELECT‎ و ‎UPDATE‎ على جدول الموظفين.

REVOKE SELECT, UPDATE
ON Employees
FROM User1, User2;

استخدام ملفات XML في SQL

يمكن تعريف جدول بيانات من ملف XML واستخدامه في استعلامات SQL مثل أيّ جدول عادي:

DECLARE @xmlIN XML = '<TableData>
<aaa Main="First">
    <row name="a" value="1" />
    <row name="b" value="2" />
    <row name="c" value="3" />
</aaa>
<aaa Main="Second">
    <row name="a" value="3" />
    <row name="b" value="4" />
    <row name="c" value="5" />
</aaa>
<aaa Main="Third">
    <row name="a" value="10" />
    <row name="b" value="20" />
    <row name="c" value="30" />
</aaa>
</TableData>'
SELECT t.col.value('../@Main', 'varchar(10)') [Header],
t.col.value('@name', 'VARCHAR(25)') [name], 
t.col.value('@value', 'VARCHAR(25)') [Value]
FROM @xmlIn.nodes('//TableData/aaa/row') AS t (col)

الخرج الناتج:

Header        name      Value
First          a          1
First          b          2
First          c          3
Second         a          3
Second         b          4
Second         c          5
Third          a          10
Third          b          20
Third          c          30

المفاتيح الرئيسية Primary Keys

تُستخدَم المفاتيح الرئيسية لتمييز صفوف جدول من قاعدة بيانات. ولا يُسمح إلا بمفتاح رئيسي واحد في كلّ جدول.

تنشئ الشيفرة التالية جدولًا للموظفين، مع جعل المُعرّف Id مفتاحه الرئيسي:

CREATE TABLE Employees (
    Id int NOT NULL,
    PRIMARY KEY (Id),
    ...
);

يمكن أيضًا تركيب مفتاح من حقل واحد أو أكثر، هذا النوع من المفاتيح يُسمّى المفاتيح المُركّبة، وتُصاغ على النحو التالي:

CREATE TABLE EMPLOYEE (
    e1_id INT,
    e2_id INT,
   PRIMARY KEY (e1_id, e2_id)
)

استخدام الزيادة التلقائية Auto Increment

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

إليك الأمثلة التوضيحية التالية:

• MySQL

CREATE TABLE Employees (
    Id int NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (Id)
);

• PostgreSQL

CREATE TABLE Employees (
    Id SERIAL PRIMARY KEY
);

• SQL Server

CREATE TABLE Employees (
    Id int NOT NULL IDENTITY,
    PRIMARY KEY (Id)
);

• SQLite

CREATE TABLE Employees (
    Id INTEGER PRIMARY KEY 
);

الفهارس Indexes

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

توجد عدّة أنواع من الفهارس، ويمكنك إنشاؤها على أيّ جدول. يحسّن استخدَام فهارس الأعمدة في عبارات WHERE أو JOIN أو ORDER BY أداء الاستعلامات تحسينًا.

الفهارس المُرتّبة Sorted Index

إذا كانت الفهارس مُرتّبة بنفس الترتيب الذي ستُسترجع به، فلن تجري التعليمة ‎SELECT‎ أيّ ترتيب إضافي أثناء الاسترجاع.

CREATE INDEX ix_scoreboard_score ON scoreboard (score DESC);

عند تنفيذ الاستعلام التالي:

SELECT * FROM scoreboard ORDER BY score DESC;

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

الفهرس الجزئي أو المُصفّى Partial or Filtered Index

تتيح SQL Server و SQLite إنشاء فهارس تحتوي جزءًا من الأعمدة، وكذلك جزءًا من الصفوف.

لنعتبر كمّية متزايدة من الطلبات يساوي الحقل ‎order_state_id‎ الخاص بها القيمة 2 (والتي تمثّل الحالة "مكتملة")، وكمّية أخرى ثابتة من الطلبات يساوي الحقل ‎order_state_id الخاص بها القيمة 1 (والتي تمثّل الحالة "غير مكتملة").

إليك الاستعلام التالي:

SELECT id, comment
     FROM orders
  WHERE order_state_id =  1
     AND product_id = @some_value;

يتيح لك استخدام الفهرسة الجزئية تقييد الفهرس (limit the index)، بحيث لا تُضمَّن إلا الطلبات التي لم تكتمل بعدُ:

CREATE INDEX Started_Orders
 ON orders(product_id)
 WHERE order_state_id = 1;

سيؤدي هذا إلى تقليل كمّية المؤشّرات، ويوفّر مساحة التخزين، ويقلّل من تكلفة تحديث الفهارس.

إنشاء فهرس

تنشئ الشيفرة التالية فهرسًا للعمود EmployeeId في الجدول Cars.

CREATE INDEX ix_cars_employee_id ON Cars (EmployeeId);

يحسّن استخدام الفهرس سرعة الاستعلامات التي تحاول أن ترتّب أو تختار الصفوف وفقًا لقيم EmployeeId، كما في المثال التالي:

SELECT * FROM Cars WHERE EmployeeId = 1

يمكن أن يحتوي الفهرس أكثر من عمود واحد كما في المثال التالي:

CREATE INDEX ix_cars_e_c_o_ids ON Cars (EmployeeId, CarId, OwnerId);

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

يستخدم المثال التالي الفهرس الثاني:

SELECT * FROM Cars WHERE EmployeeId = 1 Order by CarId DESC

يفقد الفهرس هذه المزايا في حال كان الترتيب مختلفًا كما يبيّن المثال التالي:

SELECT * FROM Cars WHERE OwnerId = 17 Order by CarId DESC

لم يعد الفهرس مفيدًا الآن، لأنّه يتوجّب على قاعدة البيانات أن تسترجع الفهرس بالكامل، وعبر جميع قيم EmployeeId و CarID بُغية إيجاد العناصر التي تحقق الشرط ‎OwnerId = 17‎.

رغم كل ما قلناه، إلا أنّه من الممكن أن يُستخدم الفهرس رغم كل شيء؛ فقد يخمّن محسّن الاستعلامات (query optimizer) أنّ استرداد الفهرس، والتصفية بحسب قيمة ‎OwnerId‎، ثم استرداد الصفوف المطلوبة حصرًا، سيكون أسرع من استرداد الجدول بالكامل ، خاصةً إن كان الجدول كبيرًا.

محو فهرس أو تعطيله وإعادة إنشائه

تُستخدم التعليمة ‎DROP‎ لحذف الفهرس. في هذا المثال، تمحو ‎DROP‎ فهرسًا يُسمّى ix_cars_employee_id في الجدول Cars:

DROP INDEX ix_cars_employee_id ON Cars; 

تحذف ‎DROP‎ الفهرس نهائيًا، وفي حال كان الفهرس مُجمّعًا (clustered)، فسيُزال التجميع، ولن يكون بالإمكان إعادة بنائه دون إعادة إنشاء الفهرس، وهي عمليّة يمكن أن تكون بطيئة ومكلفة من الناحية الحسابية.

هناك حلّ آخر، وهو تعطيل (disable) الفهرس بدل حذفه:

ALTER INDEX ix_cars_employee_id ON Cars DISABLE;

يسمح هذا للجدول بالاحتفاظ ببنية الفهرس وبياناته الوصفية (metadata). إضافة إلى إحصائيات الفهرس، وهكذا سيسهُل تقييم التغيير الحاصل. وإذا لزم الأمر، سيكون من الممكن إعادة بناء الفهرس لاحقًا دون الاضطرار إلى إعادة إنشائه من الصفر:

ALTER INDEX ix_cars_employee_id ON Cars REBUILD;

الفهارس المجمّعة Clustered أو الفريدة Unique أو المُرتّبة Sorted

لكل فهرس عدد من الخصائص، هذه الخصائص يمكن أن تُحدّد ساعة إنشاء الفهرس، أو يمكن أن تعيَّن لاحقًا.

CREATE CLUSTERED INDEX ix_clust_employee_id ON Employees(EmployeeId, Email); 

تنشئ عبارة SQL أعلاه فهرسًا مُجمّعا (clustered index) جديدًا لجَدول الموظفين Employees. الفهارس المُجمّعة هي فهارس تتحكّم في البنية الفعلية للجدول؛ إذ يُرتَّب الجدول بحيث يُطابق بنية الفهرس. نتيجة لهذا، لا يمكن أن يكون للجدول أكثر من فهرس مُجمّع واحد. ما يعني أنّ الشيفرة أعلاه ستفشل في حال كان الجدول يتوفّر سلفًا على فهرس مُجمّع (تسمّى الجداول التي لا تحتوي على فهارس مُجمّعة "كوْمَات" heaps).

ينشئ المثال التالي فهرسًا فريدًا (unique index) للعمود Email في جدول العملاء Customers. علاوة على تسريع الاستعلام، يفرض الفهرس أن تكون عناوين البريد الإلكتروني فريدة (غير مكرّرة) في العمود. وإن حاولت إدراج صفّ يحتوي بريدًا إلكترونيًا موجودًا سلفا، فستفشل عملية الإدراج أو التحديث (افتراضيًا).

CREATE UNIQUE INDEX uq_customers_email ON Customers(Email);

ينشئ المثال التالي فهرسًا لجدول العملاء Customers، هذا الفهرس يضع على الجدول قيودًا تنصّ على أنّ الحقل EmployeeID ينبغي أن يكون فريدًا. (ستفشل هذه العملية إن لم يكن العمود فريدًا - أي، إن كان هناك موظف آخر يحمل نفس القيمة.)

CREATE UNIQUE INDEX ix_eid_desc ON Customers(EmployeeID);

تنشئ الشيفرة التالية فهرسًا مُرتّبا ترتيبًا تنازليًا. افتراضيا، تُرتّب الفهارس (على الأقل في MSSQL server) تصاعديًا، لكن يمكن تغيير هذا السلوك كما يوضّح المثال التالي:

CREATE INDEX ix_eid_desc ON Customers(EmployeeID Desc);

إعادة بناء الفهرس

مع مرور الوقت، قد تصبح الفهارس المتشعّبة من النمط ب B-Tree مُجزّأة (fragmented) نتيجة عمليات التحديث والحذف والإدراج. في نظام SQLServer، هناك نوعان من الفهارس، الفهارس الداخلية، والتي تكون فيها صفحة الفهرس نصف فارغة (half empty)، والفهارس الخارجية، والتي لا يتطابق فيها ترتيب الصفحة المنطقي مع الترتيب الفعلي). إعادة بناء الفهارس تشبه إلى حدّ بعيد حذفها ثمّ إعادة إنشائها.

يمكن إعادة بناء الفهرس باستخدام الصياغة التالية:

ALTER INDEX index_name REBUILD;

إعادة بناء الفهارس هي عملية منقطعة (offline) افتراضيًا، أيّ أنّها تقفِل الجدول أثناء عملها وتمنع التعديل عليه، لكنّ العديد من أنظمة معالجة قواعد البيانات (RDBMS) تسمح بإعادة البناء عبر الشبكة (online). كما توفّر بعض أنظمة قواعد البيانات بدائل أخرى لإعادة بناء الفهارس، مثل ‎REORGANIZE‎ (في SQLServer) أو ‎COALESCE‎ / ‎SHRINK SPACE‎ (في Oracle).

الإدراج باستخدام فهرس فريد

ستفشل الشيفرة التالية في حال تم تعيين فهرس فريد للعمود Email في جدول العملاء:

UPDATE Customers SET Email = "richard0123@example.com" WHERE id = 1;

تقترح هذه الشيفرة بديلًا ممكنًا في مثل هذه الحالة:

UPDATE Customers SET Email = "richard0123@example.com" WHERE id = 1 ON DUPLICATE KEY;

رقم الصف Row number

يمكن استخدام أرقام الصفوف في استعلامات SQL عبر الدالة ROW_NUMBER.

يحذف المثال التالي جميع السجلات خلا السجلّ الأخير (جدول من نوع "واحد إلى كثير" - 1‎‎ to Many)

WITH cte AS (
   SELECT ProjectID,
     ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY InsertDate DESC) AS rn
   FROM ProjectNotes
)
DELETE FROM cte WHERE rn > 1;

تضمّن الشيفرة التالية رقم الصف وفقًا لترتيب الطلبية.

SELECT
   ROW_NUMBER() OVER(ORDER BY Fname ASC) AS RowNumber,
   Fname,
   LName
FROM Employees

يقسّم المثال التالي أرقام الصفوف إلى مجموعات وفقًا لمعيار محدّد.

SELECT
  ROW_NUMBER() OVER(PARTITION BY DepartmentId ORDER BY DepartmentId ASC) AS RowNumber,
  DepartmentId, Fname, LName
FROM Employees

الفرق بين Group By و Distinct في SQL

تُستخدم العبارة ‎GROUP ‎BY‎ في SQL مع دوال التجميع (aggregation functions).

إليك الجدول التالي:

يستخدم الاستعلام أدناه ‎GROUP BY‎ لإجراء عمليات حسابية تجميعية (aggregated calculations).

SELECT
    storeName,
    COUNT(*) AS total_nr_orders,
    COUNT(DISTINCT userId) AS nr_unique_customers,
    AVG(orderValue) AS average_order_value,
    MIN(orderDate) AS first_order,
    MAX(orderDate) AS lastOrder
FROM
    orders
GROUP BY
    storeName;

يُعاد الخرج التالي:

بالمقابل، تُستخدم ‎DISTINCT‎ لسرد توليفة فريدة (unique combination) من القيم المختلفة للأعمدة المحدّدة.

SELECT DISTINCT
    storeName,
    userId
FROM
    orders;

سنحصل على الخرج:

البحث عن التكرارات في جزء من الأعمدة

يستخدم المثال التالي دالة نافذة - Window Function - (أي دالة تجري حسابات على مجموعة من الصفوف، كما تتيح الوصول إلى بيانات السجلات التي تسبق السجل الحالي أو التي تلحقه) لعرض جميع الصفوف المُكرّرة (في جزء من الأعمدة).

WITH CTE (StudentId, Fname, LName, DOB, RowCnt)
as (
SELECT StudentId, FirstName, LastName, DateOfBirth as DOB, SUM(1) OVER (Partition By FirstName,
LastName, DateOfBirth) as RowCnt
FROM tblStudent
)
SELECT * from CTE where RowCnt > 1
ORDER BY DOB, LName

ترجمة -وبتصرّف- للفصول من 34 إلى 40 من الكتاب SQL Notes for Professionals

اقرأ أيضًا:

• المقال التالي: الدوال النصية String Functions في SQL
• المقال السابق: حذف الجداول وقواعد البيانات في SQL
• النسخة العربية الكاملة لكتاب ملاحظات للعاملين بلغة SQL 1.0.0