أعجوبة

البرمجيات الحُرة والمفتوحة المصدر

أدوات المستخدم

أدوات الموقع

أثر:
docs:sql_basics

اختلافات

عرض الاختلافات بين النسخة المختارة و النسخة الحالية من الصفحة.

رابط إلى هذه المقارنة

جانبي المراجعة السابقةالمراجعة السابقة
docs:sql_basics [2012/01/18 16:43] – [أنواع الحقول] alsadidocs:sql_basics [2015/04/23 03:20] (حالي) – تحرير خارجي 127.0.0.1
سطر 1: سطر 1:
 +{{tag>مقالات مسودات بدهيات برمجة ويب خوادم قواعد_بيانات sql sqlite}}
 +====== بدهيات استعلامات SQL مع قواعد بيانات Sqlite ======
 +<note warning>
 +هذه الصفحة لا تزال قيد الإنشاء
 +</note>
 +
 +
 +===== مقدمة =====
 +إن 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**
 +
 +<note>
 +تسمح لك SQlite بعدم الالتزام بنوع الحقل المعرف في المخطط Schema كأن تضع نص في حقل عددي وهو ما يعرف بميزة dynamic typing.
 +</note>
 +من مرونة SQlite فإنها توفر الأنواع الإضافية التالية:
 +  * حقل نصي عالمي حر الطول **TEXT** وهو يدعم unicode دون الحاجة لتحديد أي طول. (هذا النوع متوفر في MySQL و PostgreSQL)
 +  * حقل ثنائي  **BLOB** لحفظ سلسلة من البيانات (ليس بالضرورة أن تكون نصا كحفظ محتويات ملف .jpg مثلا)
 +  * حقل عددي صحيح **INTEGER**
 +  * وحقل عددي نسبي **REAL**
 +
 +لا توفر SQLite دعما حقيقيا لحفظ التاريخ والوقت لكن يمكنك محاكاة ذلك عبر استعمال INTEGER وحفظ عدد الثواني بعد ما يعرف ببزوغ فجر يونكس Unix epoch.
 +
 +يمكنك استعمال أنواع SQL القياسية لكن اعلم أن SQLite ستدير الحجم ولن تحترم النوع أو الحد الذي تطلبه.
 +
 +<note tip>
 +يفضل البعض استخدام حقول من قوى 2 مثل VARCHAR(128) إلا أن هذا في SQLite ليس منه فائدة لأنها كلها من نوع TEXT
 +</note>
 +
 +
 +==== إنشاء قاعدة بيانات ====
 +تختلف الطريقة من قاعدة بيانات إلى أخرى.
 +=== في SQLite ===
 +في sqlite يكفي تنفيذ sqlite3 متبوعا باسم الملف المطلوب إنشاؤه ليكون قاعدة بيانات
 +<code bash>
 +sqlite3 my_db_file.db
 +</code>
 +
 +ولأغراض الاختبار يمكنك استعمال جدول في الذاكرة المتطايرة RAM وذلك بتمرير :memory: عوضا عن اسم الملف هكذا
 +
 +<code bash>
 +sqlite3 :memory:
 +</code>
 +
 +يتوفر واجهة رسومية تتعامل مع ملفات sqlite وهي متوفرة بصيغتين أسهلهما إضافة لمتصفح الإنترنت firefox (داخل قائمة أدوات tools) أو برنامج مستقل (يتم تنفيذه عبر xulrunner)
 +
 +  * https://addons.mozilla.org/en-US/firefox/addon/sqlite-manager/
 +  * http://code.google.com/p/sqlite-manager/
 +
 +{{ :docs:sqlite-manager.png?480 |واجهة أداة إدارة sqlite من firefox}}
 +
 +=== في MySQL ===
 +في mysql نتصل مع خادم قاعدة البيانات مثلا بالأمر
 +<code bash>
 +mysql --default-character-set=utf8 -u root
 +</code>
 +
 +أضف الخيار p حتى يسألك عن كلمة السر. يمكنك تحديد مستخدم غير root.
 +
 +ثم ننشئ قاعدة البيانات بالأمر
 +<code sql>
 +CREATE DATABASE testdb;
 +</code>
 +
 +<note tip>
 +جرت العادة أن تكتب الكلمات المحجوزة في SQL بأحرف كبيرة.
 +</note>
 +<note important>
 +تتبع كل عبارات SQL بفاصلة منقوطة ;
 +</note>
 +
 +
 +ولعرض بقواعد البيانات المتوفرة نستعمل **SHOW DATABASES** حيث جدول بها هكذا
 +
 +<code>
 +mysql> show databases;
 ++--------------------+
 +| Database           |
 ++--------------------+
 +| information_schema |
 +| mysql              |
 +| testdb             |
 ++--------------------+
 +3 rows in set (0.00 sec)
 +
 +mysql>
 +</code>
 +
 +لاختيار قاعدة بيانات نستعمل **USE** متبوعا باسم قاعدة البيانات ويمكن عمل هذا الاختيار عند الاتصال بخادم mysql بكتابة اسم قاعدة البيانات في نهاية الأمر
 +
 +<code bash>
 +mysql --default-character-set=utf8 -u root -p testdb
 +</code>
 +
 +==== إنشاء الجداول ====
 +
 +لإنشاء جدول نستخدم CREATE TABLE  ويمكن إنشاء جدول مؤقت متطاير المحتوى عبر CREATE TEMPORARY TABLE وهو جدول حذف تلقائيا عند إنهاء الاتصال (إغلاق البرنامج).
 +
 +ويمكن أن نضيف أيضا IF NOT EXISTS حتى لا يعطي خطأ إن كان الجدول موجودا مسبقا. بعدها نذكر اسم الجدول وبين قوسين نكتب تعريف للأعمدة الموجودة فيه
 +
 +<code sql>
 + CREATE TABLE addressbook (
 + name VARCHAR(30), 
 + email VARCHAR(30), 
 + phone VARCHAR(30)
 + );
 +</code> 
 +
 +<note tip>
 +تعتبر لغة SQL من اللغات حرة التنسيق Free form لهذا يجوز أن تكتب العبارة الواحدة على عدة أسطر.
 +</note>
 +
 +يمكنك وصف عمود ما بأنه معرّف أساسي PRIMARY KEY أو بأنه فريد UNIQUE أو مفهرس KEY أو أنه لا ينبغي أن يكون خاليا NOT NULL كما يمكنك أن تعطيه قيمة أولية كذلك يمكن جعله ذاتي الزيادة AUTOINCREMENT (أو كما في mysql يكتب AUTO_INCREMENT بعلامة تحتية _) وسيأتي تفصيل ذلك.
 +
 +<code sql>
 + CREATE TABLE addressbook (
 + id INTEGER PRIMARY KEY, 
 + name VARCHAR(30) UNIQUE, 
 + email VARCHAR(30) NOT NULL, 
 + phone VARCHAR(30) DEFAULT "NA"
 + );
 +</code> 
 +
 +<note tip>
 +يسمى الأمر السابق باسم Data Definition Language أو DDL اختصارا.
 +</note>
 +==== إضافة الصفوف ====
 +يمكننا إضافة الصفوف إلى الجدول عبر INSERT INTO متبوعة باسم الجدول ثم VALUES ثم القيم بين قوسين
 +
 +<code sql>
 +INSERT INTO addressbook VALUES (1, 'omar','omar@gmail.com', '+962-6-54321000');
 +</code>
 +
 +لكن إن كنت تريد تحديد قيم لأعمدة بعينها وترك الباقية للقيم التلقائية (مثل المعرف ذاتي الزيادة) يمكنك تحديد الأعمدة التي تريدها بين قوسين بعد اسم الجدول هكذا
 +
 +<code sql>
 +INSERT INTO addressbook (name, email, phone) VALUES ('ahmad','ahmad@gmail.com', '+962-6-54321000');
 +INSERT INTO addressbook (name, email) VALUES ('ali','ali@gmail.com');
 +</code>
 +
 +==== عرض كل الصفوف ====
 +لعرض كل الصفوف من جدول ما (مثل جدول addressbook من المثال السابق) يمكننا كتابة SELECT متبوعة بأسماء الأعمدة أو * لاختيار كل الأعمدة متبوعة بكلمة FROM متبوعة باسم الجدول هكذا
 +<code sql>
 +SELECT * FROM addressbook;
 +</code>
 +
 +والتي تعطي النتيجة التالية
 +
 +^ 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
 +<code sql>
 +SELECT name, phone FROM addressbook;
 +</code>
 +
 +والتي تعطي النتيجة التالية
 +
 +^ name ^ phone ^
 +| omar | +962-6-54321000 |
 +| ahmad | +962-6-54321000 |
 +| ali| NA |
 +
 +==== استخراج dump من قاعدة البيانات ====
 +لأغراض النسخ الاحتياطي أو التصدير يمكنك استخراج أوامر SQL التي تلزم لإعادة بناء قاعدة البيانات بكل جداولها بكل صفوفها ويسمى هذا dump.
 +
 +في SQLite يكون ذلك عبر الأمر الخاص .dump (أي نقطة متبوعة بكلمة dump) اكتبه في محث sqlite3 ثم اضغط ENTER دون وضع فاصلة منقوطة.
 +
 +ويعمل أيضا من سطر الأوامر مثلا في سطر أوامر نظام التشغيل (وليس SQLite) اكتب
 +
 +<code bash>
 +sqlite3 my_db_file.db .dump
 +</code>
 +==== عرض بنية قاعدة البيانات ====
 +إن كنت تحتاج عرض مخطط قاعدة البيانات بكل جداولها لكن دون الصفوف اكتب في محث sqlite3 الأمر الخاص .schema (أي نقطة متبوعة بكلمة schema متبوعة بالضغط على ENTER)
 +
 +==== حذف جدول ====
 +لحذف جدول (الصفوف والمخطط) نستخدم الأمر DROP TABLE هكذا
 +<code sql>
 +DROP TABLE addressbook;
 +</code>
 +===== إنشاء الجداول وفهرستها =====
 +==== الفهارس ====
 +لتسريع الوصول لصف يحتوي على قيمة معينة لحقل ما في جدول ما يجب أن يكون ذلك الحقل مفهرسا
 +مثلا للحصول على الصف الذي يحتوي القيمة omar لعمود name في جدول addressbook فإن قاعدة البيانات ستسير على كل الصفوف بحثا عن تلك القيمة في الجدول لكن إن كان هناك فهرس على حقل الاسم فإنها ستجده مباشرة دون المرور على كل الصفوف.
 +ليس المساواة فقط بل المقارنة مثلا للحصول على كل الصفوف في جدول العلامات التي تزيد تزيد العلامة عن 80 فإن كانت العلامة مفهرسة لن يتم مقارنة كل الصفوف بل يتم استخدام الفهرس لمعرفة تلك الصفوف مباشرة. أيضا تفيد الفهرسة تسريع في الترتيب التصاعدي أو التنازي للحقل المفهرس.
 +
 +يكون إنشاء الفهرس عبر استخدام CREATE INDEX أو CREATE UNIQUE INDEX ثم اسم الفهرس (اسم فريد ضمن نفس الجدول يجوز أن يكون اسم الحقل) ثم كلمة ON ثم بين قوسين الحقول التي تعمل الفهرسة عليها مثلا:
 +
 +<code sql>
 +CREATE TABLE tags (
 + name VARCHAR(30) UNIQUE, 
 + n VARCHAR(30) NOT NULL,
 +);
 +CREATE INDEX n ON tags (n);
 +</code>
 +هنا عملنا فهرسا فريدا ضمنيا على حقل الاسم لأننا استعملنا كلمة UNIQUE عند انشاء الجدول.
 +وعملنا فهرسا غير فريد على حقل التكرار n عبر CREATE INDEX بعد إنشاء الجدول.
 +
 +<note warning>
 +من الأخطاء الشائعة عند تعلم إنشاء الفهارس في SQL هو أن الأقواس بعد ON تسمح لك بعمل أكثر من فهرس على أكثر من حقل وهذا خطأ بل هي تعمل فهرس واحد مركب من تجميع الحقول.
 +</note>
 +لاحظ هذا المثال
 +
 +لكن لو أردنا أن نعمل فهرسا فريدا للاسم الأول وآخر للاسم الثاني
 +<code sql>
 +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);
 +</code>
 +والذي يكافئ:
 +<code sql>
 +CREATE TABLE users (
 + first_name VARCHAR(30),
 + last_name VARCHAR(30),
 + UNIQUE (first_name),
 + UNIQUE (last_name)
 +);
 +</code>
 +لكن هذا ليس ما نريده فهو يمنع تكرار الاسم الأول حتى لو اختلف الاسم الثاني. لكن المثال التالي:
 +<code sql>
 +CREATE TABLE users (
 + first_name VARCHAR(30),
 + last_name VARCHAR(30)
 +);
 +CREATE UNIQUE INDEX first_last_name ON users (first_name, last_name);
 +</code>
 +هنا الفريد ليس الحقل الأول أو الثاني بل تركيبهما معا أي يجوز أن يكون الاسم الأول مكررا إن اختلف الثاني.
 +الجدول السابق يمكن إنشاؤه بأمر واحد هكذا
 +<code sql>
 +CREATE TABLE users (
 + first_name VARCHAR(30),
 + last_name VARCHAR(30),
 + UNIQUE (first_name, last_name)
 +);
 +</code>
 +هنا الحصول على الصف الذي فيه الاسم الأول omar والثاني ahmad يكون سريعا
 +كذلك الوصول لكل الصفوف التي فيها الاسم الأول ali مهما كان الاسم الثاني أيضا يكون سريعا
 +
 +<note>
 +ترتيب الحقول مهم في الفهرس فإن كان عندك فهرس واحد على ثلاث حقول هي a ثم b ثم c
 +فإنك تستطيع البحث أو الترتيب بسرعة على السوابق أي قيم a دون تحديد b ولا c بسرعة لكن ليس b دون تحديد a.
 +</note>
 +
 +==== المفتاح الرئيسي PRIMARY KEY ====
 +المفتاح الرئيسي ما هو إلا فهرس فريد يستخدم لجلب الحقول سريعا.
 +في الغالب يكون هذا الحقل حقل رقمي متزايد تلقائيا.
 +ويجوز أن يكون على حقل عددي أو نصي أو حتى تركيب عدة الحقول (متجانسة النوع أو غير متجانسة النوع).
 +
 +هناك صيغتين لتعريفه بعد الحقل مباشرة هكذا
 +<code sql>
 +CREATE TABLE addressbook (
 + uid INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
 + name VARCHAR(30), 
 + email VARCHAR(30), 
 + phone VARCHAR(30)
 +);
 +</code>
 +
 +أو بعد الحقول بطريقة تشبه تعريف الفهارس ويمكن أن تكون مركبة بذكر أكثر من حقل
 +
 +<code sql>
 +CREATE TABLE addressbook (
 + uid INTEGER NOT NULL AUTOINCREMENT, 
 + name VARCHAR(30), 
 + email VARCHAR(30), 
 + phone VARCHAR(30),
 + PRIMARY KEY (uid)
 +);
 +</code>
 +
 +<note>نذكر بأن الحقل ذاتي الزيادة في MYSQL يكتب AUTO_INCREMENT بعلامة تحتية _</note>
 +===== الاستعلام =====
 +الاستعلام يكون عبر SELECT 
 +
 +==== تطبيق بعض الحسابات ====
 +
 +يمكننا استخدام الاستعلام بالأمر SELECT من أجل القيام بحسابات هكذا
 +
 +<code sql>
 +SELECT 1+1;
 +SELECT MAX(1,2,3,-1);
 +SELECT LENGTH("ojuba");
 +</code>
 +
 +==== عرض حقول مخصوصة ====
 +
 +يمكن عرض كل الحقول عبر * ويمكن تحديد الحقول المطلوبة عبر سردها مفصولة بعلامة ,
 +
 +<code sql>
 +SELECT first_name, last_name FROM users;
 +</code>
 +
 +يمكن عمل كنية لكل حقل عبر AS هكذا
 +
 +<code sql>
 +SELECT first_name AS n1, last_name AS n2 FROM users;
 +</code>
 +
 +==== الترتيب ====
 +يمكنك ترتيب الصفوف عبر ORDER BY متبوعة باسم الحقل ثم ASC (وهو التلقائي) أو DESC للتصاعدي أو التنازلي على الترتيب.
 +<code sql>
 +SELECT * FROM users ORDER BY last_name ASC;
 +</code>
 +يجوز الترتيب على أكثر من حقل 
 +<code sql>
 +SELECT * FROM users ORDER BY first_name ASC, last_name ASC;
 +</code>
 +في هذا المثال نرتب على الاسم الأول فإن تساوى على الأخير.
 +
 +==== الحد وتقليب الصفحات ====
 +يمكن وضع حد أعلى لعدد الصفوف المجلوبة عبر عبارة LIMIT مثلا لاستخراج صف واحد يمكنك كتابة
 +
 +<code sql>
 +SELECT * FROM addressbook LIMIT 1
 +</code>
 +
 +لاستخراج أول 5 صفوف عند التريب حسب الاسم الأخير أبجديا
 +
 +<code sql>
 +SELECT * FROM addressbook ORDER BY last_name LIMIT 5
 +</code>
 +
 +يمكن تخطي عدد من الصفوف بواسطة عبارة الإزاحة OFFSET متبوعة بعدد الصفوف المتخطاة. مثلا العبارة السابقة تكافئ
 +
 +<code sql>
 +SELECT * FROM addressbook ORDER BY last_name LIMIT 5 OFFSET 0
 +</code>
 +
 +أما الصفحة الثالثة فتكون بتخطي 10 صفوف (5 في كل صفحة) لعرض الصفوف حتى الخامس عشر.
 +<code sql>
 +SELECT * FROM addressbook ORDER BY last_name LIMIT 5 OFFSET 10
 +</code>
 +
 +ويمكن وضع فاصلة , مكان عبارة OFFSET لكن ترتيب التعبيرين مقلوب (الإزاحة أولا ثم الحد)
 +
 +<code sql>
 +SELECT * FROM addressbook ORDER BY last_name LIMIT 10, 5
 +</code>
 +
 +لعرض رقم آخر صف يمكن استخدام الترتيب العكسي مع وضع حد أعلى صف واحد
 +
 +<code sql>
 +SELECT rowid FROM addressbook ORDER BY rowid DESC LIMIT 1
 +</code>
 +
 +==== الشرط WHERE ====
 +يمكن عرض الصفوف التي تطابق شرط معين عبر عبارة WHERE متبوعة بالشرط أو الشروط المركبة عبر AND أو OR ويمكن استخدام الأقواس.
 +
 +مثلا لعرض الصفوف التي يكون الاسم الأخير فيها omar نكتب الاستعلام
 +
 +<code sql>
 +SELECT * FROM addressbook WHERE last_name = 'omar';
 +</code>
 +
 +هناك الكثير من العبارات المرنة يمكن أن نستعملها بعد WHERE مثل:
 +  * فحص المساواة =
 +  * المقارنة < أو >
 +  * IS NULL
 +  * IS NOT NULL
 +    * مطابقة الأنماط عبر الشبه LIKE حيث نستعمل % بمعنى أي شيء من أي طول
 +
 +مثلا لعرض الأسماء التي تبدأ ب A
 +
 +<code sql>
 +SELECT * FROM addressbook WHERE last_name LIKE 'A%' ORDER BY last_name;
 +</code>
 +
 +<note tip>
 +مطابقة نمط السوابق (كما في المثال السابق) يستفيد من الفهارس أما اللواحق أو التوسط فلا يستفيد.
 +</note>
 +
 +<note tip>
 +قبل أن تتندر على كود NULL=NULL تعطي FALSE عليك تخيل أن NULL تمثل قيمة مجهولة خالية. فإن القيمة المجهولة لا تساوي القيمة المجهولة الأخرى. لهذا نستعمل أداة IS NULL
 +</note>
 +
 +
 +===== ربط الجداول JOIN =====
 +يمكن مقابلة جدولين أو أكثر كل صف في الأول مع كل صفوف الآخر كما في الضرب الديكارتي.
 +لنأخذ المثال التالي:
 +<code sql>
 +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);
 +</code>
 +^ جدول الأصناف items ^^^
 +^ name ^ price ^ in_stock ^
 +| milk | 2.5 | 1000 |
 +| chicken | 5.1 | 3000 |
 +
 +
 +^ جدول عربة التسوق cart ^^
 +^ item_name ^ number ^
 +| milk | 3 |
 +| milk | 1 |
 +| chicken | 2 |
 +
 +مقابلة الجدولين تكون هكذا
 +<code sql>
 +SELECT *, price*number AS total_price FROM items JOIN cart;
 +</code>
 +
 +والتي تعطي
 +
 +^ 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 لمطابقة اسم الصنف
 +
 +<code sql>
 +SELECT *, price*number AS total_price FROM items JOIN cart WHERE name=item_name;
 +</code>
 +حيث سنحصل على 
 +^ 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 كما في الأمر التالي والذي يكافئ السابق:
 +<code sql>
 +SELECT *, price*number AS total_price FROM items JOIN cart ON name=item_name;
 +</code>
 +
 +إن كان هناك تشابه في الأسماء فإننا بحاجة لإزالة الغموض وذلك بذكر اسم الجدول أو كنيته ثم نقطة ثم اسم الحقل مثلا items.name عوضا عن name
 +
 +يمكنك أن تعطي الجدول أو الحقل كنية وذلك بذكر الكنية بعد عبارة AS كما فعلنا في حاصل ضرب العدد في سعر price*number وسميناه السعر الإجمالي total_price
 +
 +===== أنواع الربط =====
 +==== الربط الداخلي INNER JOIN (النوع التلقائي) ====
 +لنضف حقول غير متقابلة في الجدولين
 +<code sql>
 +INSERT INTO items VALUES ('beans', 0.5, 2000);
 +INSERT INTO cart VALUES ('cheese', 1);
 +</code>
 +الآن لننفذ أمر الربط السابق والذي يكافئ تحديد نوع الربط على أنه INNER JOIN لأنه التلقائي
 +<code sql>
 +SELECT *, price*number AS total_price FROM items INNER JOIN cart ON name=item_name;
 +</code>
 +نلاحظ أن النتيجة هي نفسها كما كانت قبل إضافة الحقول غير المتقابلة ولم نجد صنف beans لأنه لم يظهر في عربة التسوق ولا وجدنا الجبن الذي ظهر في العربة ولم يظهر في الأضناف
 +
 +لنقم الآن بإضافة صفوف متقابلة لكن بقيمة خالية NULL في حقول التقابل
 +<code sql>
 +INSERT INTO items VALUES (NULL, 0.75, 8500);
 +INSERT INTO cart VALUES (NULL, 1);
 +</code>
 +
 +وسنجد أن المقابلة الداخلية لم تشمل الصفوف الجديدة لأنها خالية
 +
 +<note tip>تذكر أن الخالي لا يفحص بالتساوي NULL=NULL  لأنه يعطي FALSE بل يفحص ب IS NULL </note>
 +
 +==== الربط الخارجي OUTER JOIN ====
 +وهو على 3 أنواع هي
 +  * التام FULL OUTER JOIN
 +  * الأيسر LEFT OUTER JOIN أو LEFT JOIN اختصارا وهو الوحيد المدعوم في SQLite
 +  * الأيمن RIGHT OUTER JOIN أو RIGHT JOIN اختصارا
 +
 +الربط الخارجي يربط الصفوف إن كان وإن كان أحد طرفي المقابلة خاليا NULL. أما الربط الأيسر فيمر على كل صفر في طرف المقابلة الأيسر حتى وإن كان الطرف الأيمن خال. والمقابلة اليمنى بالعكس.
 +
 +<code sql>
 +SELECT *, price*number AS total_price FROM items LEFT JOIN cart ON name=item_name;
 +</code>
 +
 +^ 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
 +
 +<note tip>يمكن محاكة الربط الأيمن بقلب الترتيب واستخادم الأيسر.</note>
 +
 +<note tip>لاحظ أن الحليب موجود مرتين ذلك أن اسم الصنف في جدول items مفتاح أساسي فريد أما في جدول عربة التسوق فهو حقل عادي غير فريد وهذا يقودنا لاحقا لعمل علاقات واحد إلى أكثر One to Many أما إن كان فريدا فتكون العلاقة واحد لواحد One to One ويسمى اسم الصنف item_name في جدول العربة المفتاح الخارجي Foreign Key</note>
 +
 +===== تجميع الصفوف GROUP BY =====
 +لاحظ أن الحليب موجود مرتين في العربة يمكننا جمهما حيث تتيح لنا عبارة GROUP BY تحديد عدد من الحقول إن تطابقت في أكثر من صف جمعا معا في صف واحد وذلك باستخدام الحقل المشترك وأي عدد من دوال التجميع aggregation functions مثل المجموع SUM والمتوسط AVG وغيرها.
 +
 +<code sql>
 +SELECT item_name, SUM(number) AS total_number FROM cart GROUP BY item_name;
 +</code>
 +
 +والتي تعطي (حيث جمع مدخلتا الحليب في مدخلة واحدة عوضا عن 1 و 3 أصبحت مدخلة واحدة عددها 4)
 +
 +^ item_name ^ total_number ^
 +| NULL | 1 |
 +| cheese | 1 |
 +| chicken | 2 |
 +| milk | 4 |
 +
 +<note important>
 +الحقول غير المذكورة في التجميع يجب أن تجلب عبر دالة تجميع وإلا فإنها ستكون واحد عشوائية من بين الصفوف المجمعة.
 +</note>
 +
 +إن استخدام أي دالة تجميع مثل SUM دون ذكر التجميع يؤدي إلى عملية تجميع لكل الصفوف. لذا يمكننا طباعة السعر الإجمالي للفاتورة كاملة بالاستعلام التالي:
 +<code sql>
 +SELECT SUM(price*number) as total FROM items JOIN cart ON name=item_name;
 +</code>
 +
 +===== الاشتراط بعد التجميع عبر HAVING =====
 +لا يجوز استخدام أي من دوال التجميع مثل SUM في عبارة WHERE لأنها تنفذ قبل التجميع GROUP BY
 +وهنا جاءت الحاجة لعبارة HAVING والتي تذكر بعد GROUP BY
 +
 +<note tip>
 +ترتيب الشروط هو ON التي تستخدم مقرونة مع JOIN ثم WHERE والتي تحدث قبل GROUP BY وأخيرا تنفذ HAVING
 +</note>
 +
 +===== الإضافة من استعلام =====
 +يمكنك أخذ نواتج عملية الاستعلام (بكل مزاياها) وتمريرها إلى INSERT INTO وذلك بوضع عبارة SELECT مكان VALUES وذلك جائز سواء من نفس الجدول أو من جدول آخر أو من عملية دمج JOIN ...إلخ
 +
 +<code sql>
 +INSERT INTO t1 (col1, col2) SELECT t2.c1, t2.c2 FROM t2 WHERE t2.c1>5;
 +</code>
 +
 +
 +===== ترحيل الحركات دفعة واحدة transactions =====
 +
 +لنتخيل أن لدينا مجموعة من الاستعلامات نريد تنفيذها دفعة واحدة (نسميها حركة Transaction)
 +وذلك بوضع BEGIN في قبل أول عملية و COMMIT بعد آخر واحدة هذا يجعلها أسرع بشكل كبير جدا
 +
 +<code sql>
 +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;
 +</code>
 +
 +لكن السرعة ليست هي الفائدة الوحيدة. مثلا فلتكن إضافة 5 صفوف لكن إضافة الصف الرابع فشلت لسبب أو لآخر (كأن يكون حقل فريد مكرر)
 +إن كنا نريد ضمان دخول الخمسة بحركة واحدة إما كلها وإما لا شيء فإننا نستعمل ROLLBACK كي نعود إلى الحالة التي كان عليها قبل الحركة.
 +
 +لاحظ أن الاستعلام في آخر المثال التالي لم يعد أي نتائج.
 +<code sql>
 +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;
 +</code>
 +
 +هناك 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
 +
 +<code SQL>
 +SELECT fun, profit FROM real_world WHERE relational=FALSE;
 +</code>
 +
 +أحد أصدقائي محبي SQL رد عليهم
 +
 +<code SQL>
 +Query OK, 0 rows selected (0.00 sec)
 +</code>
 +
  
docs/sql_basics.txt · آخر تعديل: 2015/04/23 03:20 بواسطة 127.0.0.1
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki