2-ma’ruza. Ma’lumotlar bazasiga qo’yiladigan talablar. Rеja: Axborot tizimlari va ma'lumotlar bazasi

2-jadval. Satrli funksiyalar

Sintaksis	Qaytuvchi qiymat
LEFT(s,n)	s satrdan birinchi n ta belgini olish
RIGHT(s.n)	s satrdan oxirgi n ta belgini olish
SUBSTRING(s, m, n)	s satrining n belgisini s satrining m-belgilaridan boshlab nusxalash natijasida olingan satr
LCASE(s)	s satrini barcha harflarini kichik harfga o'tkazish
UCASE(s)	s satrini barcha harflarini katta harfga o'tkazish
CONCAT(s1, s2, ...)	s1, s2, ... qatorlarini bog'lovchi (birlashtiruvchi) satr.
LENGTH(s)	s satr uzunligi

3-jadval. Mantiqiy qiymatni qaytaradigan operatorlar va funktsiyalar (1 - Rost, 0 –yolg’on)

Sintaksis	Qaytuvchi qiymat
x = y  x > y  x < y  x >= y  x <= y  x != y	Taqqoslash operatsiyasining natijasiga qarab 1 (Rost) yoki 0 (Yolg’on),
NOT l	1, agar l=0  0, agar l=1
l1 AND l2	l1 va l2 uchun mantiqiy va amalining natijasi
l1 OR l2	l1 va l2 uchun mantiqiy yoki amalining natijasi
BETWEEN (x, y z)	mantiqiy ifoda natijasi (x = y VA x = z)
ISNULL (v)	1, v ning qiymati bo’sh bo’lsa (NULL)  0, aks holda
IFNULL (v1, v2)	v1, agar v1 bo'sh bo'lmasa  v2, aks holda
s LIKE namuna	1, namuna s satrida mavjud bo’lsa   0, aks holda
s NOT LIKE namuna	0, namuna s satrida mavjud bo’lmasa  1, aks holda

Misol
Aytaylik, materiallar jadvalidagi materiallarning zichligi to'g'risida ma'lumotni namoyish qilishda ularning tarkibida alyuminiy bo'lgan materiallarni aniqlash kerak (aniqroq, ularning nomiga alyuminiy haqida eslatib o'tilgan materiallar). Ushbu vazifani quyidagi operator yordamida bajarish mumkin.

SELECT name, name LIKE '%alu%', density FROM materials;
+--------------+-------------------+---------+
| name | name LIKE '%alu%' | density |
+--------------+-------------------+---------+
| steel | 0 | 7.80 |
| aluminium | 1 | 2.70 |
| concrete | 0 | 5.60 |
| duraluminium | 1 | 2.80 |
| titanium | 0 | 4.50 |
| brass | 0 | 8.50 |
+--------------+-------------------+---------+
Misol
Ushbu cheklangan elementning uchlari bo'lgan tugunlarning identifikatorlari orasidagi farqning eng katta qiymatini aniqlash uchun har bir cheklangan element uchun zarurat bo'lsin. Ushbu vazifani quyidagi operator hal qilishi mumkin.
SELECT id, n1, n2, n3, MAX(ABS(n1-n2),ABS(n1-n3),ABS(n2-n3))
FROM elements;
+----+----+----+----+---------------------------------------+
| id | n1 | n2 | n3 | MAX(ABS(n1-n2),ABS(n1-n3),ABS(n2-n3)) |
+----+----+----+----+---------------------------------------+
| 29 | 24 | 26 | 25 | 2 |
| 30 | 24 | 25 | 23 | 2 |
| 31 | 22 | 26 | 24 | 4 |
| 1 | 2 | 3 | 5 | &nbs
p; 3 |
| 2 | 1 | 2 | 4 | &nbs
p; 3 |
| 3 | 2 | 5 | 4 | &nbs
p; 3 |
| 4 | 4 | 5 | 6 | &nbs
p; 2 |
| 25 | 24 | 23 | 21 | 3 |
| 20 | 20 | 19 | 17 | 3 |
| 21 | 21 | 19 | 20 | 2 |
| 22 | 21 | 23 | 19 | 4 |
| 12 | 12 | 14 | 13 | 2 |
| 13 | 12 | 15 | 14 | 3 |
| 14 | 13 | 14 | 18 | 5 |
| 26 | 28 | 27 | 22 | 6 |
| 7 | 7 | 8 | 9 | &nbs
p; 2 |
| 8 | 8 | 10 | 9 | 2
|
| 9 | 9 | 10 | 11 | 2 |
| 10 | 10 | 12 | 11 | 2 |
| 11 | 11 | 12 | 13 | 2 |
| 16 | 16 | 17 | 14 | 3 |
| 17 | 18 | 17 | 14 | 4 |
| 18 | 16 | 20 | 17 | 4 |
| 19 | 19 | 18 | 17 | 2 |
| 15 | 15 | 16 | 14 | 2 |
| 27 | 27 | 29 | 26 | 3 |
| 28 | 22 | 27 | 26 | 5 |
| 5 | 5 | 7 | 6 | &nbs
p; 2 |
| 6 | 5 | 8 | 7 | &nbs