Ma'lumot turlari va jadval yaratish

CREATE TABLE mahsulotlar (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    soni     SMALLINT UNSIGNED,        -- faqat musbat, 0-65535
    narx     DECIMAL(12,2),            -- 12 ta raqam, 2 tasi kasrda
    reyting  DECIMAL(2,1)              -- 0.0 dan 9.9 gacha
);

CREATE TABLE maqolalar (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    til_kodi CHAR(2),                  -- har doim 2 ta belgi: 'uz', 'ru'
    sarlavha VARCHAR(200) NOT NULL,
    qisqacha VARCHAR(500),
    matn     MEDIUMTEXT
);

CREATE TABLE hodisalar (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nomi          VARCHAR(160) NOT NULL,
    tugilgan_sana DATE,
    boshlanish    DATETIME,
    yaratilgan    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    yangilangan   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE misol (
    -- Cheklangan variantlar
    holat   ENUM('yangi', 'jarayonda', 'tugallandi') DEFAULT 'yangi',

    -- Bir nechta variantni birga
    teglar  SET('muhim', 'shoshilinch', 'arxiv'),

    -- Mantiqiy (aslida TINYINT(1))
    faolmi  BOOLEAN DEFAULT TRUE,

    -- JSON ma'lumot (MySQL 5.7+)
    sozlama JSON,

    -- Ikkilik ma'lumot
    rasm    BLOB
);

CREATE TABLE foydalanuvchilar (
    id       INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email    VARCHAR(190) NOT NULL UNIQUE,
    ism      VARCHAR(120) NOT NULL,
    yosh     TINYINT UNSIGNED CHECK (yosh >= 14 AND yosh <= 120),
    balans   DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    rol      ENUM('foydalanuvchi', 'muharrir', 'admin')
                 NOT NULL DEFAULT 'foydalanuvchi',
    faolmi   BOOLEAN NOT NULL DEFAULT TRUE,
    yaratilgan TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 1-usul: avtomatik oshadigan son (eng keng tarqalgan)
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

-- 2-usul: tabiiy kalit
CREATE TABLE viloyatlar (
    kodi CHAR(2) PRIMARY KEY,        -- 'TS', 'SA', 'BU'
    nomi VARCHAR(60) NOT NULL
);

-- 3-usul: tarkibiy kalit (ikki ustundan)
CREATE TABLE talaba_fan (
    talaba_id INT UNSIGNED,
    fan_id    INT UNSIGNED,
    baho      TINYINT,
    PRIMARY KEY (talaba_id, fan_id)
);

CREATE TABLE IF NOT EXISTS talabalar (
    id            INT UNSIGNED AUTO_INCREMENT,
    ism           VARCHAR(120)  NOT NULL,
    familiya      VARCHAR(120)  NOT NULL,
    email         VARCHAR(190)  NOT NULL,
    tugilgan_sana DATE,
    shahar        VARCHAR(60),
    kurs          TINYINT UNSIGNED CHECK (kurs BETWEEN 1 AND 6),
    ortacha_baho  DECIMAL(3,2)  DEFAULT 0.00,
    faolmi        BOOLEAN       NOT NULL DEFAULT TRUE,
    yaratilgan    TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    yangilangan   TIMESTAMP     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    PRIMARY KEY (id),
    UNIQUE KEY uq_email (email),
    KEY idx_shahar (shahar),
    KEY idx_kurs (kurs)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

-- Ustun qo'shish
ALTER TABLE talabalar ADD COLUMN telefon VARCHAR(20);
ALTER TABLE talabalar ADD COLUMN izoh TEXT AFTER shahar;

-- Ustun turini o'zgartirish
ALTER TABLE talabalar MODIFY COLUMN ism VARCHAR(150) NOT NULL;

-- Ustun nomini o'zgartirish
ALTER TABLE talabalar CHANGE COLUMN shahar viloyat VARCHAR(60);

-- Ustunni o'chirish
ALTER TABLE talabalar DROP COLUMN izoh;

-- Indeks qo'shish
ALTER TABLE talabalar ADD INDEX idx_familiya (familiya);

-- Jadval nomini o'zgartirish
RENAME TABLE talabalar TO oquvchilar;

-- Jadvalni tozalash (tuzilma qoladi)
TRUNCATE TABLE talabalar;

-- Jadvalni butunlay o'chirish
DROP TABLE IF EXISTS talabalar;

CREATE DATABASE IF NOT EXISTS maktab
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE maktab;

CREATE TABLE guruhlar (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nomi       VARCHAR(20) NOT NULL UNIQUE,
    yil        YEAR NOT NULL,
    yaratilgan TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE talabalar (
    id            INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    guruh_id      INT UNSIGNED,
    ism           VARCHAR(120) NOT NULL,
    familiya      VARCHAR(120) NOT NULL,
    email         VARCHAR(190) NOT NULL UNIQUE,
    telefon       VARCHAR(20),
    tugilgan_sana DATE,
    shahar        VARCHAR(60),
    ortacha_baho  DECIMAL(3,2) DEFAULT 0.00,
    faolmi        BOOLEAN NOT NULL DEFAULT TRUE,
    yaratilgan    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    yangilangan   TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    KEY idx_guruh (guruh_id),
    KEY idx_shahar (shahar)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO guruhlar (nomi, yil) VALUES
    ('IT-101', 2026),
    ('IT-102', 2026),
    ('DIZ-201', 2025);

INSERT INTO talabalar (guruh_id, ism, familiya, email, shahar, ortacha_baho) VALUES
    (1, 'Husanboy', 'Qodirov',  '[email protected]', 'Namangan',  4.60),
    (1, 'Sardor',  'Aliyev',    '[email protected]',  'Toshkent',  3.80),
    (2, 'Malika',  'Yusupova',  '[email protected]',  'Buxoro',    4.90),
    (2, 'Bekzod',  'Rasulov',   '[email protected]',  'Toshkent',  3.20),
    (3, 'Nodira',  'Sobirova',  '[email protected]',  'Samarqand', 4.30);

SELECT * FROM talabalar;