Shunchaki ko’chirib qo’ymasdan miyyalarizni ishlatib o’zilarni topshiriqlariz bilan ishlanglar

LOWER – satrning harflarini kichik registrga o’tkazib beradi, kata harflarga o’tkazish uchun UPPER funksiyasi mavjud. LOWER funksiyasi sintaksisi quyidagicha:
LOWER(str)
Misol:
SELECT
LOWER('MySQL')
LTRIM – satrning chap tarafidagi probel belgilarini olib tashlaydi. LTRIM funksiyasi sintaksisi quyidagicha:
LTRIM(str)
Misol:
SELECT
LTRIM(' MySQL Tutorial') result;
Matematik funksiyalar

Nomi	Ta’rifi
ABS()	Sonning absolyut qiymati
CEIL()	Sonni o’ziga teng yoki kata bo’lgan butun songa yaxlitlaydi
FLOOR()	Sonni o’zidan katta bo’lmagan eng katt butun qiymatga yaxlitlaydi
ROUND(n,[d])	Sonni ko’rsatilgan xonaga yaxlitlaydi.

Sana uchun funksiyalar

Nomi	Ta’rifi
CURDATE()	Bugungi sana
DATEDIFF(date_1,date_2)	Ikki sana orasidagi kunlar soni
DAYNAME(date)	Sananing xafta kuni nomi
NOW()	Bugungi sana va vaqt
WEEKDAY(date)	Sananing xafta kuni raqami(0 dan 6 gacha)

Boshqaruv funksiyalari

CASE – taqqoslashni amalga oshiradi yoki shartni tekshiradi va shu orqali natijaviy qiymatni beradi. Ikki xil ko’rinishi mavjud:
CASE value WHEN [compare_value] THEN result [WHEN [compare_value] THEN result ...] [ELSE result] END
Value ni compare_value bilan taqqoslaydi teng bo’lsa natijani qaytaradi.
CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
Condition shartini tekshiradi rost qiymat uchun ko’rsatilgan natijani beradi.
Misol:
SELECT CompanyName,
CASE Country WHEN 'USA' THEN 'North America'
WHEN 'Canada' THEN 'North America'
WHEN 'Brazil' THEN 'South America'
WHEN 'Japan' THEN 'Asia'
WHEN 'Singapore' THEN 'Asia'
WHEN 'Australia' THEN 'Australia'
ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY CompanyName;

SELECT CompanyName,

CASE WHEN Country = 'USA' THEN 'North America'
WHEN Country = 'Canada' THEN 'North America'
WHEN Country = 'Brazil' THEN 'South America'
WHEN Country = 'Japan' THEN 'Asia'
WHEN Country = 'Singapore' THEN 'Asia'
WHEN Country = 'Australia' THEN 'Australia'
ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY CompanyName;

IF(expr1,expr2,expr3) – uchta argument qabul qiladi, expr1 TRUE qiymat qaytarsa expr2 ni, aks holda expr3 ni qaytaradi.

SELECT ProductName,

UnitPrice,
IF(UnitPrice>=20, 'High Price', 'Low Price') AS "Price Level"
FROM Products;

IFNULL(expr1,expr2) – expr1 NULL bo’lmasa expr1 ni, aks holda expr2 ni beradi.

SELECT FirstName, LastName, IFNULL(ReportsTo, 'Is CEO')
FROM Employees;


9-lobaratoriya ishi
Agregat funksiyalardan foydalanib so‘rovlar yaratish
MAX() – maksimal qiymat;
MIN() – minimal qiymat;
SUM() – yig’indini hisoblash;
COUNT() – sanash;
AVG() – o’rtacha qiymat;