3 - عملگرهای پیشرفته

• عملگر LIKE
• عملگر AND
• عملگر BETWEEN
• عملگر OR
• عملگر IN
• عملگر IS NULL
• تقدم و نقیض‌سازی

عملگر LIKE

عملگر LIKE برای یافتن مقادیری استفاده می‌شود که با یک الگو مطابقت دارند. الگوها همیشه داخل کوتیشن وارد می‌شوند. علامت درصد (%) نماینده صفر یا چند نویسه ناشناخته و زیرخط (_) نماینده دقیقاً یک نویسه ناشناخته است.

SELECT NAME, COUNTRY
FROM PERSONS
WHERE NAME LIKE 'EINST__N';

SELECT NAME, COUNTRY
FROM PERSONS
WHERE NAME LIKE '%ST__N%';

• LIKE مقادیری را پیدا می‌کند که با الگو تطبیق دارند.
• % نماینده صفر یا چند نویسه است.
• _ نماینده یک نویسه است.

عملگر LIKE - ادامه

در نحو فعلی SELECT، عملگر LIKE در Clause WHERE افزوده شده و نمونه از نویسه‌های % و _ استفاده می‌کند.

SELECT [ DISTINCT ] collist
FROM table
WHERE col LIKE pattern
ORDER BY col [ DESC ] list

-- Example
WHERE NAME LIKE '%ST__N%'

تمرین‌ها

1. همه کشورهایی را انتخاب کنید که واژه guinea در نامشان وجود دارد. باید 4 سطر پیدا کنید.
2. همه ستون‌های افرادی را نمایش دهید که حرف z نویسه دوم نامشان است. 2 نفر وجود دارند.
3. همه ستون‌های اشخاصی را نشان دهید که در 15 ژوئیه متولد شده‌اند. 6 نفر هستند.

امتیاز اضافه

4. همه افرادی را پیدا کنید که در نامشان آپاستروف وجود دارد. 3 نفر هستند.
5. فهرستی از ادیانی بسازید که واژه orthodox را در خود دارند. فهرست را مرتب و تکراری‌ها را حذف کنید. نتیجه 10 سطر دارد.

عملگر AND

عملگر AND دو مقایسه را ترکیب می‌کند و یک مقایسه مرکب می‌سازد. کلیدواژه AND بین دو مقایسه قرار می‌گیرد. برای درست‌بودن نتیجه مقایسه مرکب، هر دو مقایسه باید درست باشند.

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME LIKE 'A%'
  AND BDATE >= '1900-01-01';

SELECT COUNTRY, GNP
FROM COUNTRIES
WHERE GNP >= 1000
  AND GNP < 2000;

• AND دو مقایسه را ترکیب می‌کند.
• AND بین دو مقایسه قرار می‌گیرد.
• هر دو مقایسه باید درست باشند.

عملگر AND - ادامه

نحو فعلی SELECT با افزوده‌شدن AND به Clause WHERE:

SELECT [ DISTINCT ] collist
FROM table
WHERE cmpr AND cmpr

-- Example
WHERE NAME LIKE '%ST__N%' AND JOB = 'S'

تمرین‌ها

1. همه ستون‌های دانشمندان اهل آلمان را فهرست کنید. باید 7 دانشمند پیدا شود.
2. همه ستون‌های کشورهایی را نمایش دهید که GNP آن‌ها کمتر از سه میلیارد و نرخ سوادشان کمتر از 40 درصد است. به یاد داشته باشید GNP بر حسب میلیون دلار ذخیره شده است. نتیجه را بر اساس GNP مرتب کنید. 7 سطر وجود دارد.
3. نام کشور و نرخ سواد کشورهایی را نشان دهید که نرخ سوادشان بین 55 و 60 درصد است. 7 سطر وجود دارد.

امتیاز اضافه

4. همه ستون‌های انگلیسی‌هایی را نشان دهید که در قرن شانزدهم متولد شده‌اند؛ مرتب‌شده بر اساس نام. نتیجه 4 سطر دارد.
5. همه ستون‌های هشت ارتشی را نشان دهید که بیش از 100,000 نیرو، کمتر از 1,000 تانک، کمتر از 1,000 کشتی و کمتر از 1,000 هواپیما دارند.

عملگر BETWEEN

برخی مقایسه‌های مرکب مبتنی بر AND را می‌توان با BETWEEN ساده‌تر بیان کرد. BETWEEN هر مقدار ستون را با یک بازه مقایسه می‌کند و دو انتهای بازه نیز جزو بازه هستند.

SELECT COUNTRY, LITERACY
FROM COUNTRIES
WHERE LITERACY >= 55
  AND LITERACY <= 60
ORDER BY LITERACY;

SELECT COUNTRY, LITERACY
FROM COUNTRIES
WHERE LITERACY BETWEEN 55 AND 60
ORDER BY LITERACY;

• BETWEEN مقدار ستون را با یک بازه مقایسه می‌کند.
• هر دو نقطه انتهایی در بازه قرار دارند.
• مقدار دوم باید از مقدار اول بزرگ‌تر باشد.

عملگر BETWEEN - ادامه

در نحو فعلی، BETWEEN به Clause WHERE افزوده شده است.

WHERE col BETWEEN i AND j
-- Example
WHERE LITERACY BETWEEN 55 AND 60

تمرین‌ها

1. با استفاده از BETWEEN همه ستون‌های کشورهایی را فهرست کنید که مساحتشان حداقل 5 و حداکثر 75 مایل مربع است. 5 سطر وجود دارد.
2. همه ستون‌های کشورهایی را انتخاب کنید که جمعیتشان بین 100,000 و 200,000 نفر است. 6 سطر وجود دارد.
3. همه ستون‌های ارتش‌هایی را انتخاب کنید که تجهیزاتشان بیش از 300 و کمتر از 400 هواپیما دارد. 9 ارتش وجود دارد.

امتیاز اضافه

4. همه ستون‌های ارتش‌هایی را انتخاب کنید که دست‌کم 300,000 نیرو دارند و بودجه‌شان بین 10 و 100 میلیارد دلار است. به یاد داشته باشید BUDGET بر حسب میلیون دلار ذخیره می‌شود. 4 ارتش وجود دارد.

عملگر OR

عملگر OR دو مقایسه را ترکیب و یک مقایسه مرکب ایجاد می‌کند. کلیدواژه OR بین دو مقایسه قرار می‌گیرد. اگر یکی یا هر دو مقایسه درست باشند، نتیجه مرکب درست است.

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME = 'LUTHER'
   OR NAME = 'CALVIN';

SELECT COUNTRY, LANGUAGE
FROM COUNTRIES
WHERE COUNTRY = 'GHANA'
   OR COUNTRY = 'USA'
   OR COUNTRY = 'FIJI';

• OR دو مقایسه را ترکیب می‌کند.
• OR بین دو مقایسه قرار می‌گیرد.
• درست‌بودن یکی یا هر دو مقایسه کافی است.

عملگر OR - ادامه

در نحو فعلی SELECT، OR به Clause WHERE افزوده شده است.

WHERE cmpr OR cmpr
-- Example
WHERE NAME = 'LUTHER' OR NAME = 'CALVIN'

تمرین‌ها

1. همه ستون‌های جدول ARMIES را برای اسرائیل و عراق نمایش دهید.
2. همه ستون‌های افرادی را نشان دهید که در یکی از دو تاریخ 1835-10-19 یا 1917-02-06 متولد شده‌اند. 4 نفر هستند... یا واقعاً 4 نفرند؟
3. نام و تاریخ تولد Poe، Hugo و Dahl را فهرست کنید.

امتیاز اضافه

4. چند نفر در پایگاه داده در قرن سیزدهم یا پانزدهم متولد شده‌اند؟

عملگر IN

برخی مقایسه‌های مرکب مبتنی بر OR را می‌توان با IN ساده‌تر بیان کرد. IN مقدار هر ستون را با فهرستی از مقادیر مقایسه می‌کند. فهرست داخل پرانتز قرار دارد و مقادیر با ویرگول جدا می‌شوند.

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME = 'POE'
   OR NAME = 'HUGO'
   OR NAME = 'DAHL';

SELECT NAME, BDATE
FROM PERSONS
WHERE NAME IN ('POE', 'HUGO', 'DAHL');

• IN مقدار ستون را با یک فهرست مقایسه می‌کند.
• فهرست داخل پرانتز نوشته می‌شود.
• مقادیر فهرست با ویرگول جدا می‌شوند.

عملگر IN - ادامه

در نحو فعلی SELECT، IN به Clause WHERE افزوده شده است.

WHERE col IN ( value list )
-- Example
WHERE NAME IN ('POE', 'HUGO', 'DAHL')

تمرین‌ها

1. با استفاده از IN همه ستون‌های Einstein، Galilei و Newton را نشان دهید.
2. همه ستون‌های کشورهایی را نمایش دهید که نرخ سوادشان 20، 40 یا 60 درصد است. 5 کشور وجود دارد.
3. نام و کشور همه دانشمندان پایگاه داده را که اهل آلمان، اتریش یا ایتالیا هستند فهرست کنید. نتیجه 11 سطر دارد.

امتیاز اضافه

4. همه افراد اهل آلمان، اتریش یا ایتالیا را پیدا کنید که نویسنده، سرگرمی‌ساز یا رهبر کسب‌وکارند. 11 نفر هستند. شناسه‌های رسمی شغل در جدول JOBS قرار دارند.

عملگر IS NULL

مقدار Null یعنی ورودی یک ستون وجود ندارد. Null به معنی «ناشناخته» یا «قابل اعمال نیست» است. Null نه Blank است و نه صفر؛ دو Null لزوماً برابر نیستند و روی Null نمی‌توان عملیات حسابی انجام داد. عملگر IS NULL سطرهایی را پیدا می‌کند که مقدار Null دارند.

SELECT COUNTRY, POP
FROM COUNTRIES
WHERE POP IS NULL;

SELECT NAME, BDATE
FROM PERSONS
WHERE COUNTRY = 'IRAN'
  AND BDATE IS NULL;

• Nullها مقدارهای مفقود هستند.
• Null با Blank یکسان نیست.
• Null با صفر یکسان نیست.
• IS NULL مقادیر Null را پیدا می‌کند.

عملگر IS NULL - ادامه

در نحو فعلی SELECT، IS NULL به Clause WHERE افزوده شده است.

WHERE col IS NULL
-- Example
WHERE POP IS NULL

تمرین‌ها

1. همه ستون‌های کشورهایی را نشان دهید که GNP آن‌ها ناشناخته است. 1 کشور وجود دارد.
2. همه ستون‌های افراد حوزه سرگرمی را که جنسیتشان Null است نمایش دهید. 2 نفر وجود دارند.
3. همه ستون‌های اسرائیلی‌هایی را که تاریخ تولدشان ناشناخته است انتخاب کنید. 9 نفر هستند.

امتیاز اضافه

4. چند کشور مساحتی کمتر از 10 مایل مربع و GNP بیشتر از 250 میلیون دارند؟ چند کشور با همان شرط مساحت، GNP کمتر یا مساوی 250 میلیون دارند؟ بر اساس این نتایج حدس می‌زنید در مجموع چند کشور مساحت کمتر از 10 مایل مربع داشته باشند؟

تقدم و نقیض‌سازی

پرانتز برای تعیین تقدم، یعنی ترتیب ارزیابی مقایسه‌ها، استفاده می‌شود. SQL ابتدا مقایسه‌های داخل پرانتز را ارزیابی می‌کند. کلیدواژه NOT برای نقیض‌کردن یا معکوس‌کردن نتیجه یک مقایسه به کار می‌رود.

SELECT JOB, NAME
FROM PERSONS
WHERE COUNTRY = 'ITALY'
  AND (JOB = 'S' OR JOB = 'W')
ORDER BY JOB, NAME;

SELECT JOB, NAME
FROM PERSONS
WHERE COUNTRY = 'ITALY'
  AND NOT (JOB = 'S' OR JOB = 'W')
ORDER BY JOB, NAME;

• مقایسه‌های داخل پرانتز ابتدا ارزیابی می‌شوند.
• NOT پیش از یک مقایسه قرار می‌گیرد.
• NOT نتیجه مقایسه را معکوس می‌کند.

تقدم و نقیض‌سازی - ادامه

در نحو فعلی SELECT، پرانتز و کلیدواژه NOT به Clause WHERE افزوده شده‌اند.

WHERE [ NOT ] comparison
WHERE ( compound comparison )

-- Examples
WHERE NOT NAME LIKE '%ST__N%'
WHERE (JOB = 'S' OR JOB = 'W')

تمرین‌ها

1. همه ستون‌های آلمانی‌هایی را نمایش دهید که یا الهی‌دان هستند یا رهبر کسب‌وکار. نتیجه را بر اساس نام مرتب کنید. 3 سطر وجود دارد.
2. پرس‌وجوی قبلی را تغییر دهید تا همه آلمانی‌هایی را پیدا کند که رهبر کسب‌وکار یا الهی‌دان نیستند. نتیجه 11 سطر دارد.

امتیاز اضافه

3. چند ارتش، بدون احتساب آمریکا و روسیه، بودجه نظامی بیش از 30 میلیارد دلار دارند؟