SQL ma'lumot kiritish, yangilash va o'chirish: DML

DML — Data Manipulation Language — ma'lumotlarni boshqarish buyruqlari.

INSERT — Qo'shish

-- Bitta qator
INSERT INTO talabalar (ism, email, ball)
VALUES ('Sherali Rahimov', 'sherali@mail.uz', 92);

-- Bir necha qator
INSERT INTO talabalar (ism, email, ball) VALUES
    ('Aziza Karimova', 'aziza@mail.uz', 88),
    ('Bobur Toshmatov', 'bobur@mail.uz', 76),
    ('Nilufar Yusupova', 'nilufar@mail.uz', 95);

-- Boshqa jadvaldan ko'chirish
INSERT INTO talabalar_arxiv (ism, email, ball)
SELECT ism, email, ball FROM talabalar
WHERE yaratildi < '2025-01-01';

UPDATE — Yangilash

-- Bitta ustun
UPDATE talabalar SET ball = 95 WHERE id = 1;

-- Bir necha ustun
UPDATE talabalar
SET ball = 90, shahar = 'Samarqand', yangilandi = NOW()
WHERE email = 'sherali@mail.uz';

-- Hisob-kitob bilan
UPDATE xodimlar SET maosh = maosh * 1.15
WHERE lavozim = 'Dasturchi';

-- JOIN bilan yangilash
UPDATE talabalar t
SET kurs_id = k.id
FROM kurslar k
WHERE k.nom = 'Python' AND t.email LIKE '%@example.com';

DELETE — O'chirish

-- Shart bilan o'chirish
DELETE FROM talabalar WHERE ball < 50;

-- Barcha qatorlarni o'chirish (sekin)
DELETE FROM talabalar;

-- TRUNCATE — tez tozalash
TRUNCATE TABLE talabalar;
TRUNCATE TABLE talabalar RESTART IDENTITY; -- ID ni 1 dan boshlaydi

UPSERT — Qo'shish yoki yangilash

-- PostgreSQL
INSERT INTO talabalar (email, ism, ball)
VALUES ('sherali@mail.uz', 'Sherali', 90)
ON CONFLICT (email) DO UPDATE
SET ism = EXCLUDED.ism,
    ball = EXCLUDED.ball,
    yangilandi = NOW();

-- Agar mavjud bo'lsa hech narsa qilma
INSERT INTO talabalar (email, ism)
VALUES ('sherali@mail.uz', 'Sherali')
ON CONFLICT (email) DO NOTHING;

RETURNING — Natijani qaytarish

-- Kiritilgan qatorni qaytarish
INSERT INTO talabalar (ism, email)
VALUES ('Yangi Talaba', 'yangi@mail.uz')
RETURNING id, ism, yaratildi;

-- O'chirilgan qatorlarni ko'rish
DELETE FROM talabalar WHERE ball < 50
RETURNING id, ism, ball;