|
1-ma’ruza. Ma’lumotlar bazasining maqsadi, vazifalari va asosiy tushunchalari. Rеja
|
| bet | 71/101 | | Sana | 20.12.2023 | | Hajmi | 1,17 Mb. | | #124746 |
Bog'liq Sanjar2-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
|
| |
