الوثيقة بهيئات أخرىHTMLODT

بدهيات استعلامات SQL مع قواعد بيانات Sqlite

هذه الصفحة لا تزال قيد الإنشاء

مقدمة

إن SQL وتعني لغة الاستعلامات الهيكلية Structured Query Language وهي من وضع IBM (في نظام لهم اسمه النظام R يعود للسبعينات من القرن الماضي) وهي لغة عيارية منذ 1986 م حيث تبنتها ANSI ثم ISO في معايير متلاحقة.

يجدر بنا الإشارة إلى أن SQL ليست لغة برمجة وإنما لغة للتحدث مع قواعد البيانات العلائقية RDBMS.

ويوجد العديد من قواعد البيانات التي يمكنك مخاطبتها بهذه اللغة سواء الحرة مثل MySql و PostgreSQL أو المملوكة مثل الشهيرة Oracle وهناك قواعد بيانات تدعم SQL كميزة إضافية غير أساسية.

بعكس ما قد يخطر ببالك ليس بالضرورة أن تكون قواعد البيانات برمجيات عبر شبكة فهناك منها ما هي هيئة ملفات تستخدمها تماما كما تكتب قيم بينها فواصل CSV في ملف لكنها تكون أسرع وأكثر كفاءة وفعالية. منها قواعد بيانات SQLite التي أطلقت عام 2001 م بعد قيام مؤلفها بأبحاث لصالح البحرية للتحكم بالمدمرات الصاروخية.

اخترنا SQLite لعدة أسباب

  • لا تحتاج التعقيدات المرتبطة بنموذج الخادم والعميل client/server فهي في النهاية مجرد ملف.
  • مع أنها صممت كي تكون منضدة إلا أنه يمكنها التعامل مع كم كبير من البيانات
  • تدعم معايير SQL-92 بشكل كبير.

كل ما يلزمنا في هذه الوثيقة برنامج صغير اسمه sqlite3 (في فيدورا/أعجوبة تجده في حزمة sqlite) وهناك إضافة متصفح الإنترنت لثعلب النار firefox تسمح لك بالتعامل رسوميا مع ملفات SQLite.

الأساسيات

تتألف قاعدة البيانات من عدد من الجداول كل منها له مخطط ثابت schema يحدد ما يحتويه كل جدول من حقول (أعمدة).

أنواع الحقول

في SQL القياسية يجب تعريف أنواع الحقول بشكل مسبق والالتزام بها.

  • الحقول النصية
    • حرة الطول ذات حد أعلى VARCHAR ونتبعها بعدد صحيح بين قوسين هو الحد الأعلى لطولها.
    • ثابت الطول CHAR متبوع هو الآخر بعدد بين قوسين توسّد محتوياته بمسافات إن قل طولها عنه.
    • ومثلهما حقلي NVARCHAR و NCHAR لكنهما مع إضافة دعم المحارف العالمية.
  • الحقول العددية
    • الصحيحة وهي INTEGER و SMALLINT
    • النسبية ذات الفاصلة العائمة FLOAT و REAL و DOUBLE PRECISION
    • عدد من البتات BIT و VARBIT
  • حقول منطقية BOOL و BOOLEAN
  • حقول زمنية DATETIME و TIME و DATE و TIMESTAMP

تسمح لك SQlite بعدم الالتزام بنوع الحقل المعرف في المخطط Schema كأن تضع نص في حقل عددي وهو ما يعرف بميزة dynamic typing.

من مرونة SQlite فإنها توفر الأنواع الإضافية التالية:

  • حقل نصي عالمي حر الطول TEXT وهو يدعم unicode دون الحاجة لتحديد أي طول. (هذا النوع متوفر في MySQL و PostgreSQL)
  • حقل ثنائي BLOB لحفظ سلسلة من البيانات (ليس بالضرورة أن تكون نصا كحفظ محتويات ملف .jpg مثلا)
  • حقل عددي صحيح INTEGER
  • وحقل عددي نسبي REAL

لا توفر SQLite دعما حقيقيا لحفظ التاريخ والوقت لكن يمكنك محاكاة ذلك عبر استعمال INTEGER وحفظ عدد الثواني بعد ما يعرف ببزوغ فجر يونكس Unix epoch.

يمكنك استعمال أنواع SQL القياسية لكن اعلم أن SQLite ستدير الحجم ولن تحترم النوع أو الحد الذي تطلبه.

يفضل البعض استخدام حقول من قوى 2 مثل VARCHAR(128) إلا أن هذا في SQLite ليس منه فائدة لأنها كلها من نوع TEXT

إنشاء قاعدة بيانات

تختلف الطريقة من قاعدة بيانات إلى أخرى.

في SQLite

في sqlite يكفي تنفيذ sqlite3 متبوعا باسم الملف المطلوب إنشاؤه ليكون قاعدة بيانات

sqlite3 my_db_file.db

ولأغراض الاختبار يمكنك استعمال جدول في الذاكرة المتطايرة RAM وذلك بتمرير :memory: عوضا عن اسم الملف هكذا

sqlite3 :memory:

يتوفر واجهة رسومية تتعامل مع ملفات sqlite وهي متوفرة بصيغتين أسهلهما إضافة لمتصفح الإنترنت firefox (داخل قائمة أدوات tools) أو برنامج مستقل (يتم تنفيذه عبر xulrunner)

واجهة أداة إدارة sqlite من firefox

في MySQL

في mysql نتصل مع خادم قاعدة البيانات مثلا بالأمر

mysql --default-character-set=utf8 -u root

أضف الخيار p حتى يسألك عن كلمة السر. يمكنك تحديد مستخدم غير root.

ثم ننشئ قاعدة البيانات بالأمر

CREATE DATABASE testdb;

جرت العادة أن تكتب الكلمات المحجوزة في SQL بأحرف كبيرة.

تتبع كل عبارات SQL بفاصلة منقوطة ;

ولعرض بقواعد البيانات المتوفرة نستعمل SHOW DATABASES حيث جدول بها هكذا

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| testdb             |
+--------------------+
3 rows in set (0.00 sec)

mysql>

لاختيار قاعدة بيانات نستعمل USE متبوعا باسم قاعدة البيانات ويمكن عمل هذا الاختيار عند الاتصال بخادم mysql بكتابة اسم قاعدة البيانات في نهاية الأمر

mysql --default-character-set=utf8 -u root -p testdb

إنشاء الجداول

لإنشاء جدول نستخدم CREATE TABLE ويمكن إنشاء جدول مؤقت متطاير المحتوى عبر CREATE TEMPORARY TABLE وهو جدول حذف تلقائيا عند إنهاء الاتصال (إغلاق البرنامج).

ويمكن أن نضيف أيضا IF NOT EXISTS حتى لا يعطي خطأ إن كان الجدول موجودا مسبقا. بعدها نذكر اسم الجدول وبين قوسين نكتب تعريف للأعمدة الموجودة فيه

 CREATE TABLE addressbook (
	name VARCHAR(30), 
	email VARCHAR(30), 
	phone VARCHAR(30)
 );

تعتبر لغة SQL من اللغات حرة التنسيق Free form لهذا يجوز أن تكتب العبارة الواحدة على عدة أسطر.

يمكنك وصف عمود ما بأنه معرّف أساسي PRIMARY KEY أو بأنه فريد UNIQUE أو مفهرس KEY أو أنه لا ينبغي أن يكون خاليا NOT NULL كما يمكنك أن تعطيه قيمة أولية كذلك يمكن جعله ذاتي الزيادة AUTOINCREMENT (أو كما في mysql يكتب AUTO_INCREMENT بعلامة تحتية _) وسيأتي تفصيل ذلك.

 CREATE TABLE addressbook (
	id INTEGER PRIMARY KEY, 
	name VARCHAR(30) UNIQUE, 
	email VARCHAR(30) NOT NULL, 
	phone VARCHAR(30) DEFAULT "NA"
 );

يسمى الأمر السابق باسم Data Definition Language أو DDL اختصارا.

إضافة الصفوف

يمكننا إضافة الصفوف إلى الجدول عبر INSERT INTO متبوعة باسم الجدول ثم VALUES ثم القيم بين قوسين

INSERT INTO addressbook VALUES (1, 'omar','omar@gmail.com', '+962-6-54321000');

لكن إن كنت تريد تحديد قيم لأعمدة بعينها وترك الباقية للقيم التلقائية (مثل المعرف ذاتي الزيادة) يمكنك تحديد الأعمدة التي تريدها بين قوسين بعد اسم الجدول هكذا

INSERT INTO addressbook (name, email, phone) VALUES ('ahmad','ahmad@gmail.com', '+962-6-54321000');
INSERT INTO addressbook (name, email) VALUES ('ali','ali@gmail.com');

عرض كل الصفوف

لعرض كل الصفوف من جدول ما (مثل جدول addressbook من المثال السابق) يمكننا كتابة SELECT متبوعة بأسماء الأعمدة أو * لاختيار كل الأعمدة متبوعة بكلمة FROM متبوعة باسم الجدول هكذا

SELECT * FROM addressbook;

والتي تعطي النتيجة التالية

id name email phone
1 omar omar@gmail.com +962-6-54321000
2 ahmad ahmad@gmail.com +962-6-54321000
3 ali ali@gmail.com NA

فإن حددنا الأعمدة مثلا عمودي name و phone

SELECT name, phone FROM addressbook;

والتي تعطي النتيجة التالية

name phone
omar +962-6-54321000
ahmad +962-6-54321000
ali NA

استخراج dump من قاعدة البيانات

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

في SQLite يكون ذلك عبر الأمر الخاص .dump (أي نقطة متبوعة بكلمة dump) اكتبه في محث sqlite3 ثم اضغط ENTER دون وضع فاصلة منقوطة.

ويعمل أيضا من سطر الأوامر مثلا في سطر أوامر نظام التشغيل (وليس SQLite) اكتب

sqlite3 my_db_file.db .dump

عرض بنية قاعدة البيانات

إن كنت تحتاج عرض مخطط قاعدة البيانات بكل جداولها لكن دون الصفوف اكتب في محث sqlite3 الأمر الخاص .schema (أي نقطة متبوعة بكلمة schema متبوعة بالضغط على ENTER)

حذف جدول

لحذف جدول (الصفوف والمخطط) نستخدم الأمر DROP TABLE هكذا

DROP TABLE addressbook;

إنشاء الجداول وفهرستها

الفهارس

لتسريع الوصول لصف يحتوي على قيمة معينة لحقل ما في جدول ما يجب أن يكون ذلك الحقل مفهرسا مثلا للحصول على الصف الذي يحتوي القيمة omar لعمود name في جدول addressbook فإن قاعدة البيانات ستسير على كل الصفوف بحثا عن تلك القيمة في الجدول لكن إن كان هناك فهرس على حقل الاسم فإنها ستجده مباشرة دون المرور على كل الصفوف. ليس المساواة فقط بل المقارنة مثلا للحصول على كل الصفوف في جدول العلامات التي تزيد تزيد العلامة عن 80 فإن كانت العلامة مفهرسة لن يتم مقارنة كل الصفوف بل يتم استخدام الفهرس لمعرفة تلك الصفوف مباشرة. أيضا تفيد الفهرسة تسريع في الترتيب التصاعدي أو التنازي للحقل المفهرس.

يكون إنشاء الفهرس عبر استخدام CREATE INDEX أو CREATE UNIQUE INDEX ثم اسم الفهرس (اسم فريد ضمن نفس الجدول يجوز أن يكون اسم الحقل) ثم كلمة ON ثم بين قوسين الحقول التي تعمل الفهرسة عليها مثلا:

CREATE TABLE tags (
	name VARCHAR(30) UNIQUE, 
	n VARCHAR(30) NOT NULL,
);
CREATE INDEX n ON tags (n);

هنا عملنا فهرسا فريدا ضمنيا على حقل الاسم لأننا استعملنا كلمة UNIQUE عند انشاء الجدول. وعملنا فهرسا غير فريد على حقل التكرار n عبر CREATE INDEX بعد إنشاء الجدول.

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

لاحظ هذا المثال

لكن لو أردنا أن نعمل فهرسا فريدا للاسم الأول وآخر للاسم الثاني

CREATE TABLE users (
	first_name VARCHAR(30),
	last_name VARCHAR(30),
	UNIQUE (first_name),
	UNIQUE (last_name)
);
CREATE UNIQUE INDEX first_name ON users (first_name)
CREATE UNIQUE INDEX last_name ON users (last_name);

والذي يكافئ:

CREATE TABLE users (
	first_name VARCHAR(30),
	last_name VARCHAR(30),
	UNIQUE (first_name),
	UNIQUE (last_name)
);

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

CREATE TABLE users (
	first_name VARCHAR(30),
	last_name VARCHAR(30)
);
CREATE UNIQUE INDEX first_last_name ON users (first_name, last_name);

هنا الفريد ليس الحقل الأول أو الثاني بل تركيبهما معا أي يجوز أن يكون الاسم الأول مكررا إن اختلف الثاني. الجدول السابق يمكن إنشاؤه بأمر واحد هكذا

CREATE TABLE users (
	first_name VARCHAR(30),
	last_name VARCHAR(30),
	UNIQUE (first_name, last_name)
);

هنا الحصول على الصف الذي فيه الاسم الأول omar والثاني ahmad يكون سريعا كذلك الوصول لكل الصفوف التي فيها الاسم الأول ali مهما كان الاسم الثاني أيضا يكون سريعا

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

المفتاح الرئيسي PRIMARY KEY

المفتاح الرئيسي ما هو إلا فهرس فريد يستخدم لجلب الحقول سريعا. في الغالب يكون هذا الحقل حقل رقمي متزايد تلقائيا. ويجوز أن يكون على حقل عددي أو نصي أو حتى تركيب عدة الحقول (متجانسة النوع أو غير متجانسة النوع).

هناك صيغتين لتعريفه بعد الحقل مباشرة هكذا

CREATE TABLE addressbook (
	uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
	name VARCHAR(30), 
	email VARCHAR(30), 
	phone VARCHAR(30)
);

أو بعد الحقول بطريقة تشبه تعريف الفهارس ويمكن أن تكون مركبة بذكر أكثر من حقل

CREATE TABLE addressbook (
	uid INTEGER NOT NULL AUTOINCREMENT, 
	name VARCHAR(30), 
	email VARCHAR(30), 
	phone VARCHAR(30),
	PRIMARY KEY (uid)
);

نذكر بأن الحقل ذاتي الزيادة في MYSQL يكتب AUTO_INCREMENT بعلامة تحتية _

الاستعلام

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

تطبيق بعض الحسابات

يمكننا استخدام الاستعلام بالأمر SELECT من أجل القيام بحسابات هكذا

SELECT 1+1;
SELECT MAX(1,2,3,-1);
SELECT LENGTH("ojuba");

عرض حقول مخصوصة

يمكن عرض كل الحقول عبر * ويمكن تحديد الحقول المطلوبة عبر سردها مفصولة بعلامة ,

SELECT first_name, last_name FROM users;

يمكن عمل كنية لكل حقل عبر AS هكذا

SELECT first_name AS n1, last_name AS n2 FROM users;

الترتيب

يمكنك ترتيب الصفوف عبر ORDER BY متبوعة باسم الحقل ثم ASC (وهو التلقائي) أو DESC للتصاعدي أو التنازلي على الترتيب.

SELECT * FROM users ORDER BY last_name ASC;

يجوز الترتيب على أكثر من حقل

SELECT * FROM users ORDER BY first_name ASC, last_name ASC;

في هذا المثال نرتب على الاسم الأول فإن تساوى على الأخير.

الحد وتقليب الصفحات

يمكن وضع حد أعلى لعدد الصفوف المجلوبة عبر عبارة LIMIT مثلا لاستخراج صف واحد يمكنك كتابة

SELECT * FROM addressbook LIMIT 1

لاستخراج أول 5 صفوف عند التريب حسب الاسم الأخير أبجديا

SELECT * FROM addressbook ORDER BY last_name LIMIT 5

يمكن تخطي عدد من الصفوف بواسطة عبارة الإزاحة OFFSET متبوعة بعدد الصفوف المتخطاة. مثلا العبارة السابقة تكافئ

SELECT * FROM addressbook ORDER BY last_name LIMIT 5 OFFSET 0

أما الصفحة الثالثة فتكون بتخطي 10 صفوف (5 في كل صفحة) لعرض الصفوف حتى الخامس عشر.

SELECT * FROM addressbook ORDER BY last_name LIMIT 5 OFFSET 10

ويمكن وضع فاصلة , مكان عبارة OFFSET لكن ترتيب التعبيرين مقلوب (الإزاحة أولا ثم الحد)

SELECT * FROM addressbook ORDER BY last_name LIMIT 10, 5

لعرض رقم آخر صف يمكن استخدام الترتيب العكسي مع وضع حد أعلى صف واحد

SELECT rowid FROM addressbook ORDER BY rowid DESC LIMIT 1

الشرط WHERE

يمكن عرض الصفوف التي تطابق شرط معين عبر عبارة WHERE متبوعة بالشرط أو الشروط المركبة عبر AND أو OR ويمكن استخدام الأقواس.

مثلا لعرض الصفوف التي يكون الاسم الأخير فيها omar نكتب الاستعلام

SELECT * FROM addressbook WHERE last_name = 'omar';

هناك الكثير من العبارات المرنة يمكن أن نستعملها بعد WHERE مثل:

  • فحص المساواة =
  • المقارنة < أو >
  • IS NULL
  • IS NOT NULL
    • مطابقة الأنماط عبر الشبه LIKE حيث نستعمل % بمعنى أي شيء من أي طول

مثلا لعرض الأسماء التي تبدأ ب A

SELECT * FROM addressbook WHERE last_name LIKE 'A%' ORDER BY last_name;

مطابقة نمط السوابق (كما في المثال السابق) يستفيد من الفهارس أما اللواحق أو التوسط فلا يستفيد.

قبل أن تتندر على كود NULL=NULL تعطي FALSE عليك تخيل أن NULL تمثل قيمة مجهولة خالية. فإن القيمة المجهولة لا تساوي القيمة المجهولة الأخرى. لهذا نستعمل أداة IS NULL

ربط الجداول JOIN

يمكن مقابلة جدولين أو أكثر كل صف في الأول مع كل صفوف الآخر كما في الضرب الديكارتي. لنأخذ المثال التالي:

CREATE TABLE items (name VARCHAR(20) PRIMARY KEY, price DOUBLE, in_stock INTEGER);
CREATE TABLE cart (item_name VARCHAR(20), NUMBER INTEGER);
INSERT INTO items VALUES ('milk', 2.5, 1000);
INSERT INTO items VALUES ('chicken', 5.1, 3000);
INSERT INTO cart VALUES ('milk', 3);
INSERT INTO cart VALUES ('milk', 1);
INSERT INTO cart VALUES ('chicken', 2);
جدول الأصناف items
name price in_stock
milk 2.5 1000
chicken 5.1 3000
جدول عربة التسوق cart
item_name number
milk 3
milk 1
chicken 2

مقابلة الجدولين تكون هكذا

SELECT *, price*NUMBER AS total_price FROM items JOIN cart;

والتي تعطي

name price in_stock item_name number total_price
milk 2.5 1000 milk 3 7.5
milk 2.5 1000 milk 1 2.5
milk 2.5 1000 chicken 2 5.0
chicken 5.1 3000 milk 3 15.3
chicken 5.1 3000 milk 1 5.1
chicken 5.1 3000 chicken 2 10.2

لكن هناك صفوف لا نريدها فلماذا نضرب عدد الدجاجات في سعر الحليب!

أول ما يخطر بالبال هو استعمال WHERE لمطابقة اسم الصنف

SELECT *, price*NUMBER AS total_price FROM items JOIN cart WHERE name=item_name;

حيث سنحصل على

name price in_stock item_name number total_price
milk 2.5 1000 milk 3 7.5
milk 2.5 1000 milk 1 2.5
chicken 5.1 3000 chicken 2 10.2

لكن لاداع لذلك فكل عبارة JOIN تأخذ بعدها مباشرة عبارة ON متبوعا بشرط مطابقة الصفوف بين الجدولين يمين ويسار عبارة JOIN كما في الأمر التالي والذي يكافئ السابق:

SELECT *, price*NUMBER AS total_price FROM items JOIN cart ON name=item_name;

إن كان هناك تشابه في الأسماء فإننا بحاجة لإزالة الغموض وذلك بذكر اسم الجدول أو كنيته ثم نقطة ثم اسم الحقل مثلا items.name عوضا عن name

يمكنك أن تعطي الجدول أو الحقل كنية وذلك بذكر الكنية بعد عبارة AS كما فعلنا في حاصل ضرب العدد في سعر price*number وسميناه السعر الإجمالي total_price

أنواع الربط

الربط الداخلي INNER JOIN (النوع التلقائي)

لنضف حقول غير متقابلة في الجدولين

INSERT INTO items VALUES ('beans', 0.5, 2000);
INSERT INTO cart VALUES ('cheese', 1);

الآن لننفذ أمر الربط السابق والذي يكافئ تحديد نوع الربط على أنه INNER JOIN لأنه التلقائي

SELECT *, price*NUMBER AS total_price FROM items INNER JOIN cart ON name=item_name;

نلاحظ أن النتيجة هي نفسها كما كانت قبل إضافة الحقول غير المتقابلة ولم نجد صنف beans لأنه لم يظهر في عربة التسوق ولا وجدنا الجبن الذي ظهر في العربة ولم يظهر في الأضناف

لنقم الآن بإضافة صفوف متقابلة لكن بقيمة خالية NULL في حقول التقابل

INSERT INTO items VALUES (NULL, 0.75, 8500);
INSERT INTO cart VALUES (NULL, 1);

وسنجد أن المقابلة الداخلية لم تشمل الصفوف الجديدة لأنها خالية

تذكر أن الخالي لا يفحص بالتساوي NULL=NULL لأنه يعطي FALSE بل يفحص ب IS NULL

الربط الخارجي OUTER JOIN

وهو على 3 أنواع هي

  • التام FULL OUTER JOIN
  • الأيسر LEFT OUTER JOIN أو LEFT JOIN اختصارا وهو الوحيد المدعوم في SQLite
  • الأيمن RIGHT OUTER JOIN أو RIGHT JOIN اختصارا

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

SELECT *, price*NUMBER AS total_price FROM items LEFT JOIN cart ON name=item_name;
name price in_stock item_name number total_price
milk 2.5 1000 milk 1 2.5
milk 2.5 1000 milk 3 7.5
chicken 5.1 3000 chicken 2 10.2
beans 0.5 2000 NULL NULL NULL
NULL 0.75 8500 NULL NULL NULL

وهنا علينا ملاحظة ما يلي:

  • لاحظ أن الجبن لم يكن بين النتائج لأنه في الطرف الأيمن ولا مقابل له في الطرف الأيسر.
  • الصف الأيسر الذي حقل المقابلة في خال NULL موجود لكنه لم يتم مقابلته مع الأيمن الذي حقل المقابلة فيه NULL بل كان ما يقابله حقول كلها خالية ذلك أن NULL لا تساوي NULL بل تفحصل ب IS NULL

يمكن محاكة الربط الأيمن بقلب الترتيب واستخادم الأيسر.

لاحظ أن الحليب موجود مرتين ذلك أن اسم الصنف في جدول items مفتاح أساسي فريد أما في جدول عربة التسوق فهو حقل عادي غير فريد وهذا يقودنا لاحقا لعمل علاقات واحد إلى أكثر One to Many أما إن كان فريدا فتكون العلاقة واحد لواحد One to One ويسمى اسم الصنف item_name في جدول العربة المفتاح الخارجي Foreign Key

تجميع الصفوف GROUP BY

لاحظ أن الحليب موجود مرتين في العربة يمكننا جمهما حيث تتيح لنا عبارة GROUP BY تحديد عدد من الحقول إن تطابقت في أكثر من صف جمعا معا في صف واحد وذلك باستخدام الحقل المشترك وأي عدد من دوال التجميع aggregation functions مثل المجموع SUM والمتوسط AVG وغيرها.

SELECT item_name, SUM(NUMBER) AS total_number FROM cart GROUP BY item_name;

والتي تعطي (حيث جمع مدخلتا الحليب في مدخلة واحدة عوضا عن 1 و 3 أصبحت مدخلة واحدة عددها 4)

item_name total_number
NULL 1
cheese 1
chicken 2
milk 4

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

إن استخدام أي دالة تجميع مثل SUM دون ذكر التجميع يؤدي إلى عملية تجميع لكل الصفوف. لذا يمكننا طباعة السعر الإجمالي للفاتورة كاملة بالاستعلام التالي:

SELECT SUM(price*NUMBER) AS total FROM items JOIN cart ON name=item_name;

الاشتراط بعد التجميع عبر HAVING

لا يجوز استخدام أي من دوال التجميع مثل SUM في عبارة WHERE لأنها تنفذ قبل التجميع GROUP BY وهنا جاءت الحاجة لعبارة HAVING والتي تذكر بعد GROUP BY

ترتيب الشروط هو ON التي تستخدم مقرونة مع JOIN ثم WHERE والتي تحدث قبل GROUP BY وأخيرا تنفذ HAVING

الإضافة من استعلام

يمكنك أخذ نواتج عملية الاستعلام (بكل مزاياها) وتمريرها إلى INSERT INTO وذلك بوضع عبارة SELECT مكان VALUES وذلك جائز سواء من نفس الجدول أو من جدول آخر أو من عملية دمج JOIN …إلخ

INSERT INTO t1 (col1, col2) SELECT t2.c1, t2.c2 FROM t2 WHERE t2.c1>5;

ترحيل الحركات دفعة واحدة transactions

لنتخيل أن لدينا مجموعة من الاستعلامات نريد تنفيذها دفعة واحدة (نسميها حركة Transaction) وذلك بوضع BEGIN في قبل أول عملية و COMMIT بعد آخر واحدة هذا يجعلها أسرع بشكل كبير جدا

CREATE TABLE t1 (col1 TEXT);
BEGIN;
INSERT INTO t1 VALUES ('cell1');
INSERT INTO t1 VALUES ('cell2');
INSERT INTO t1 VALUES ('cell3');
INSERT INTO t1 VALUES ('cell1');
INSERT INTO t1 VALUES ('cell5');
COMMIT;

لكن السرعة ليست هي الفائدة الوحيدة. مثلا فلتكن إضافة 5 صفوف لكن إضافة الصف الرابع فشلت لسبب أو لآخر (كأن يكون حقل فريد مكرر) إن كنا نريد ضمان دخول الخمسة بحركة واحدة إما كلها وإما لا شيء فإننا نستعمل ROLLBACK كي نعود إلى الحالة التي كان عليها قبل الحركة.

لاحظ أن الاستعلام في آخر المثال التالي لم يعد أي نتائج.

sqlite> DROP TABLE t1;CREATE TABLE t1 (col1 TEXT UNIQUE);
sqlite> BEGIN;
sqlite> INSERT INTO t1 VALUES ('cell1');
sqlite> INSERT INTO t1 VALUES ('cell2');
sqlite> INSERT INTO t1 VALUES ('cell3');
sqlite> INSERT INTO t1 VALUES ('cell1');
Error: COLUMN col1 IS NOT UNIQUE
sqlite> ROLLBACK;
sqlite> SELECT * FROM t1;

هناك 3 أنواع من الحركات

  • المؤجل deferred وهو النوع التلقائي وهنا لا تففل قاعدة البيانات.
  • الفوري immediate هذا النوع يقفل عمليات الكتابة ولا يقفل القراءة
  • الحصري exclusive هذا النوع يقفل كل شيء

العلاقات

  • علاقة واحد إلى واحد One to One
  • علاقة واحد إلى العديد One to Many
  • علاقة العديد إلى واحد Many to One
  • علاقة العديد إلى العديد Many to Many

دعاة اسقاطها NoSQL

ظهرت الكثير من الدعوات باسم NoSQL تدعو إلى التخلي عن SQL وهذا من أسوأ المصطلحات في عالم الحاسوب فهي في الواقع لا تنتقد اللغة في حد ذاتها بل تنتقد الصفة العلائقية فيها relational ويقترح البعض تسميتها حركات NoREL.

قواعد البيانات العلاقية RDBMS والتي نتخاطب معها بلغة SQL مبنية على مبدأ ACID أي

  • atomicity
  • consistency
  • isolation
  • durability

أما NoSQL فهي على مبدأ BASE أي:

  • Basically Available
  • Soft state
  • Eventual consistency

من أشهر العبارات التي تستخدم للدعوة إلى NoSQL

SELECT fun, profit FROM real_world WHERE relational=FALSE;

أحد أصدقائي محبي SQL رد عليهم

Query OK, 0 ROWS selected (0.00 sec)

نقاش

password, 2010/07/17 01:16

ما الوقت المحدد للانتهاء من كتابة المقال/الدرس؟

مؤيد السعدي, 2010/07/17 13:50

لا يوجد وقت. هي بحسب فراغي. من يريد المساهمة فأهلا وسلها.

عمر, 2010/12/10 15:52

يارب مساعدة

أبو حفصة, 2010/12/13 15:07

ملاحظة : عنوان المقال حول SQLITE

بينما الأمثلة حول MySQL

مؤيد السعدي, 2010/12/16 11:24

سأشرح SQL وليس SQLITE ولا MySQL لكنني سأطبق على SQLITE في الغالب وسأتطرق للفروق إن لزم الأمر

بن عبد الله يوسف, 2011/03/03 16:00

شكرا و بارك الله فيك على هذا الطرح أود أن تطلعنا عن مميزات و سلبيات sqlite ان أمكن و مدى فعاليته ان قارناها ب MySql

مؤيد السعدي, 2011/03/03 17:32

هما أمران مختلفان لكن يتحدثان لغة واحدة هي SQL فقواعد sqlite هي ملفات أما MySQL فهي خادم Server تمتاز الأولى بالسرعة لكنها تعمل قفل lock على مستوى قاعدة البيانات كاملة وليس على مستوى الجدول أو حتى الصف عند الكتابة مما يسبب البطئ عند وضعها في بيئة تحتاج كتابة متوازية

بن عبد الله يوسف, 2011/03/03 19:04

اذن أخي أفهم بأن SqLite لا يمكن استعمالها كقاعدة بيانات لتطبيق مشترك في الادخال على سبيل المثال لو استخدمناها في قاعدة لموقع و كان عدد المتعاملين مع القاعدة كثير سيسبب بطأ لأن القاعدة تشغل من طرف شخص اخر حتى يتم تحريرها

Danyon, 2011/10/31 12:40

Free knowledge like this doesn't just help, it promote deomcracy. Thank you.

Asim, 2012/02/09 11:21

You've hit the ball out the park! Inrdceible!

Eduardo, 2012/08/01 07:07

If your articles are awlyas this helpful, “I'll be back.”

Alex, 2012/08/30 05:47

That's a celevr answer to a tricky question

Mande, 2012/10/18 14:11

Always the best content from these prodigious wrtires.

mr diet, 2013/05/18 18:52

بارك الله فيك

fisher man, 2013/06/04 15:29

بارك الله فيك وجزاك الله كل خيرررررررررر

Enter your comment
إن لم تسطع قراءة الرموز، فبإمكانك تنزيل هذا المف الصويت والذي سقرأها لك.
 
docs/sql_basics.txt · آخر تعديل: 2013/02/13 16:01 (تحرير خارجي)
Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0

جميع حقوق النشر والتوزيع ممنوحة ضمن شروط رخصة وقف العامة.