ستعرض هذه المقالة عددًا من المواضيع المتقدمة في 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
تسمح العديد من قواعد البيانات بزيادة قيمة المفتاح الرئيسي تلقائيًا عند إضافة مفتاح جديد. يضمن هذا السلوك أن تكون كلّ المفاتيح مختلفة عن بعضها.
إليك الأمثلة التوضيحية التالية:
CREATE TABLE Employees ( Id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (Id) );
CREATE TABLE Employees ( Id SERIAL PRIMARY KEY );
CREATE TABLE Employees ( Id int NOT NULL IDENTITY, PRIMARY KEY (Id) );
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).
إليك الجدول التالي:
orderId | userId | storeName | orderValue | orderDate |
---|---|---|---|---|
1 | 43 | Store A | 25 | 20-03-2016 |
2 | 57 | Store B | 50 | 22-03-2016 |
3 | 43 | Store A | 30 | 25-03-2016 |
4 | 82 | Store C | 10 | 26-03-2016 |
5 | 21 | Store A | 45 | 29-03-2016 |
يستخدم الاستعلام أدناه 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;
يُعاد الخرج التالي:
storeName | total_nr_orders | nr_unique_customers | average_order_value first_order | lastOrder | |
---|---|---|---|---|---|
Store A | 3 | 2 | 33.3 | 20-03-2016 | 29-03-2016 |
Store B | 1 | 1 | 50 | 22-03-2016 | 22-03-2016 |
Store C | 1 | 1 | 10 | 26-03-2016 | 26-03-2016 |
بالمقابل، تُستخدم DISTINCT
لسرد توليفة فريدة (unique combination) من القيم المختلفة للأعمدة المحدّدة.
SELECT DISTINCT storeName, userId FROM orders;
سنحصل على الخرج:
storeName | userId |
---|---|
Store A | 43 |
Store B | 57 |
Store C | 82 |
Store A | 21 |
البحث عن التكرارات في جزء من الأعمدة
يستخدم المثال التالي دالة نافذة - 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
أفضل التعليقات
لا توجد أية تعليقات بعد
انضم إلى النقاش
يمكنك أن تنشر الآن وتسجل لاحقًا. إذا كان لديك حساب، فسجل الدخول الآن لتنشر باسم حسابك.