MySQL funksiyalari

SELECT
    CONCAT(ism, ' ', familiya)          AS toliq_ism,
    CONCAT_WS(', ', shahar, 'O''zbekiston') AS manzil,
    UPPER(ism)                          AS katta,
    LOWER(email)                        AS kichik,
    LENGTH(ism)                         AS baytlar,
    CHAR_LENGTH(ism)                    AS belgilar,
    SUBSTRING(ism, 1, 3)                AS qisqartma,
    LEFT(familiya, 1)                   AS bosh_harf,
    RIGHT(telefon, 4)                   AS oxirgi_4,
    TRIM(ism)                           AS tozalangan,
    REPLACE(telefon, '+998', '')        AS raqamsiz,
    REVERSE(ism)                        AS teskari,
    LPAD(id, 6, '0')                    AS kod,
    LOCATE('@', email)                  AS at_joyi
FROM talabalar
LIMIT 1;

-- Email dan domenni ajratish
SELECT
    email,
    SUBSTRING_INDEX(email, '@', -1)  AS domen,
    SUBSTRING_INDEX(email, '@', 1)   AS foydalanuvchi
FROM talabalar;

-- Bosh harflar (initsiallar)
SELECT
    CONCAT(LEFT(ism, 1), '.', LEFT(familiya, 1), '.') AS initsiallar
FROM talabalar;

-- Telefonni formatlash
SELECT
    CONCAT('+998 ', SUBSTRING(telefon, 5, 2), ' ',
           SUBSTRING(telefon, 7, 3), '-',
           SUBSTRING(telefon, 10, 2), '-',
           SUBSTRING(telefon, 12, 2)) AS formatli
FROM talabalar
WHERE telefon IS NOT NULL;

-- Matnni qisqartirish
SELECT
    CASE
        WHEN CHAR_LENGTH(tavsif) > 50
        THEN CONCAT(SUBSTRING(tavsif, 1, 50), '...')
        ELSE tavsif
    END AS qisqa_tavsif
FROM mahsulotlar;

SELECT
    ROUND(3.14159, 2)      AS yaxlitlangan,   -- 3.14
    ROUND(2.5)             AS yaxlit,         -- 3
    FLOOR(3.9)             AS pastga,         -- 3
    CEIL(3.1)              AS yuqoriga,       -- 4
    TRUNCATE(3.999, 2)     AS kesilgan,       -- 3.99
    ABS(-42)               AS modul,          -- 42
    MOD(17, 5)             AS qoldiq,         -- 2
    POWER(2, 10)           AS daraja,         -- 1024
    SQRT(144)              AS ildiz,          -- 12
    GREATEST(3, 7, 2)      AS eng_katta,      -- 7
    LEAST(3, 7, 2)         AS eng_kichik,     -- 2
    RAND()                 AS tasodifiy,
    FORMAT(1234567.891, 2) AS formatli;       -- 1,234,567.89

-- Foizni hisoblash
SELECT
    nomi,
    narx,
    ROUND(narx * 0.12)                AS qqs,
    ROUND(narx * 1.12)                AS qqs_bilan,
    ROUND(narx * 0.85)                AS chegirmali,
    FORMAT(narx, 0)                   AS chiroyli_narx
FROM mahsulotlar;

-- Ulushni hisoblash
SELECT
    shahar,
    COUNT(*)                                                     AS soni,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1)          AS foiz
FROM talabalar
GROUP BY shahar;

SELECT
    NOW()                    AS hozir,           -- 2026-09-09 15:30:00
    CURDATE()                AS bugun,           -- 2026-09-09
    CURTIME()                AS vaqt,            -- 15:30:00
    YEAR(NOW())              AS yil,
    MONTH(NOW())             AS oy,
    DAY(NOW())               AS kun,
    HOUR(NOW())              AS soat,
    DAYOFWEEK(NOW())         AS hafta_kuni,      -- 1 = yakshanba
    DAYNAME(NOW())           AS kun_nomi,
    MONTHNAME(NOW())         AS oy_nomi,
    WEEK(NOW())              AS hafta,
    QUARTER(NOW())           AS chorak,
    LAST_DAY(NOW())          AS oy_oxiri;

SELECT
    DATE_ADD(NOW(), INTERVAL 7 DAY)      AS bir_hafta,
    DATE_SUB(NOW(), INTERVAL 1 MONTH)    AS oy_oldin,
    DATE_ADD(NOW(), INTERVAL 2 YEAR)     AS ikki_yil,
    DATEDIFF('2026-12-31', CURDATE())    AS qolgan_kunlar,
    TIMESTAMPDIFF(YEAR, '2004-05-15', CURDATE()) AS yosh,
    TIMESTAMPDIFF(HOUR, boshlanish, tugash)      AS davomiylik;

SELECT
    DATE_FORMAT(NOW(), '%d.%m.%Y')            AS uz_format,   -- 09.09.2026
    DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i')      AS toliq,
    DATE_FORMAT(NOW(), '%d %M %Y')            AS matnli,
    DATE_FORMAT(NOW(), '%W')                  AS hafta_kuni,
    STR_TO_DATE('09.09.2026', '%d.%m.%Y')     AS sanaga;

SELECT
    ism,
    -- IF: uch argumentli shart
    IF(ortacha_baho >= 4.0, 'yaxshi', 'oddiy')          AS daraja,

    -- IFNULL: NULL ni almashtirish
    IFNULL(telefon, 'kiritilmagan')                     AS telefon,

    -- NULLIF: teng bo'lsa NULL qaytaradi
    NULLIF(ortacha_baho, 0)                             AS baho,

    -- COALESCE: birinchi NULL bo'lmagan
    COALESCE(telefon, email, 'aloqa yo''q')             AS aloqa,

    -- CASE: to'liq shart
    CASE
        WHEN ortacha_baho >= 4.5 THEN 'A''lo'
        WHEN ortacha_baho >= 3.5 THEN 'Yaxshi'
        WHEN ortacha_baho >= 3.0 THEN 'Qoniqarli'
        ELSE 'Qoniqarsiz'
    END                                                  AS harfli_baho
FROM talabalar;

SELECT
    COUNT(*)                            AS jami,
    COUNT(telefon)                      AS telefoni_borlar,
    COUNT(DISTINCT shahar)              AS shaharlar,
    SUM(ortacha_baho)                   AS yigindi,
    AVG(ortacha_baho)                   AS ortacha,
    MIN(ortacha_baho)                   AS eng_past,
    MAX(ortacha_baho)                   AS eng_yuqori,
    STDDEV(ortacha_baho)                AS ogish,
    GROUP_CONCAT(ism ORDER BY ism SEPARATOR ', ') AS ismlar
FROM talabalar;

SELECT
    ism,
    shahar,
    ortacha_baho,

    -- Umumiy reyting
    ROW_NUMBER() OVER (ORDER BY ortacha_baho DESC)          AS umumiy_orin,

    -- Shahar ichidagi reyting
    ROW_NUMBER() OVER (PARTITION BY shahar
                       ORDER BY ortacha_baho DESC)          AS shahar_orin,

    -- Teng qiymatlarni bir xil raqamlash
    RANK()       OVER (ORDER BY ortacha_baho DESC)          AS rank_,
    DENSE_RANK() OVER (ORDER BY ortacha_baho DESC)          AS zich_rank,

    -- Shahar bo'yicha o'rtacha (guruhlamasdan!)
    ROUND(AVG(ortacha_baho) OVER (PARTITION BY shahar), 2)  AS shahar_ortacha,

    -- Umumiy yig'indi
    SUM(ortacha_baho) OVER ()                               AS umumiy_yigindi,

    -- Oldingi va keyingi qator
    LAG(ortacha_baho)  OVER (ORDER BY ortacha_baho DESC)    AS oldingi,
    LEAD(ortacha_baho) OVER (ORDER BY ortacha_baho DESC)    AS keyingi
FROM talabalar
ORDER BY ortacha_baho DESC;

-- To'plangan yig'indi (running total)
SELECT
    sana,
    summa,
    SUM(summa) OVER (ORDER BY sana) AS toplangan
FROM kunlik_savdo;

-- Oldingi kun bilan solishtirish
SELECT
    sana,
    summa,
    LAG(summa) OVER (ORDER BY sana)                         AS kecha,
    summa - LAG(summa) OVER (ORDER BY sana)                 AS farq,
    ROUND((summa - LAG(summa) OVER (ORDER BY sana)) * 100.0
          / LAG(summa) OVER (ORDER BY sana), 1)             AS foiz_ozgarish
FROM kunlik_savdo
ORDER BY sana;

-- JSON bilan ishlash
SELECT
    JSON_EXTRACT(sozlamalar, '$.til')          AS til,
    sozlamalar ->> '$.mavzu'                   AS mavzu
FROM foydalanuvchilar;

-- Xesh va shifrlash
SELECT
    MD5('parol'),
    SHA2('parol', 256),
    UUID()                                     AS noyob_id;

-- Tur o'zgartirish
SELECT
    CAST('123' AS UNSIGNED)                    AS songa,
    CAST(123 AS CHAR)                          AS matnga,
    CAST('2026-09-09' AS DATE)                 AS sanaga;

-- Tizim ma'lumotlari
SELECT DATABASE(), USER(), VERSION(), CONNECTION_ID();

SELECT
    DATE_FORMAT(b.yaratilgan, '%Y-%m')                    AS oy,
    DATE_FORMAT(b.yaratilgan, '%M %Y')                    AS oy_nomi,
    COUNT(DISTINCT b.id)                                  AS buyurtmalar,
    COUNT(DISTINCT b.mijoz_id)                            AS mijozlar,
    FORMAT(SUM(be.soni * be.narx), 0)                     AS tushum,
    FORMAT(AVG(be.soni * be.narx), 0)                     AS ortacha_chek,
    CONCAT(ROUND(
        SUM(be.soni * be.narx) * 100.0 /
        SUM(SUM(be.soni * be.narx)) OVER (), 1), '%')     AS ulush
FROM buyurtmalar b
JOIN buyurtma_elementlari be ON be.buyurtma_id = b.id
WHERE b.holat <> 'bekor'
  AND b.yaratilgan >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY oy, oy_nomi
ORDER BY oy;