CREATE, INSERT, UPDATE, DELETE

10-bob 15 kod bloki 3556 so'z

Hozirgacha hamma narsa faqat o'qish edi. Bu bob data ni o'zgartiradi — ya'ni xato haqiqatan qimmatga tushadigan bob. Xavfsizlik odatlarini sintaksis bilan birga o'rgating, keyinroq emas.

Analyst lar ko'pincha bu hech qachon kerak bo'lmaydi deb o'ylaydi. Kerak bo'ladi: tahlil uchun vaqtinchalik table qurish, CSV yuklash, data sifati muammosini tuzatish, dashboard uchun view yaratish.

CREATE TABLE

CREATE TABLE IF NOT EXISTS assignments (
    assignment_id  INTEGER PRIMARY KEY,
    course_id      INTEGER NOT NULL,
    title          TEXT    NOT NULL,
    max_points     INTEGER NOT NULL DEFAULT 100,
    due_date       DATE,
    created_at     TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE
);

Natija jadvali yo'q (1 gap bajarildi, 0 ms)

Uni qatorma-qator o'qish — asosiy saboq: type nima saqlanishi mumkinligini, constraint lar esa nimaga ruxsat berilishini aytadi.

Table ni to'g'ridan-to'g'ri query dan ham yaratish mumkin — tahlilni "surat" qilib saqlash uchun juda foydali:

CREATE TABLE student_summary AS
SELECT
    s.student_id,
    s.first_name || ' ' || s.last_name AS full_name,
    COUNT(e.enrollment_id)             AS courses,
    ROUND(AVG(e.grade), 1)             AS avg_grade
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id;

Natija jadvali yo'q (1 gap bajarildi, 1 ms)

:::gotcha ⚠️ CREATE TABLE AS data ni ko'chiradi, qoidalarni emas Yangi table row larni va column type larini oladi, lekin primary key ham, foreign key ham, constraint ham, index ham yo'q. Bu — surat, haqiqiy table emas. Tahlil uchun yaxshi; production table ning asosi sifatida hech qachon emas. :::

Constraint lar

Constraint lar — database siz uchun majburlaydigan qoidalar. Sxemaga surgan har bir qoida — tunda soat 3 da xato script tomonidan hech qachon buzilmaydigan qoida.

CREATE TABLE students_v2 (
    student_id  INTEGER PRIMARY KEY,
    email       TEXT    UNIQUE NOT NULL,
    first_name  TEXT    NOT NULL,
    gender      TEXT    CHECK (gender IN ('M','F')),
    birth_date  DATE    CHECK (birth_date < DATE('now')),
    status      TEXT    NOT NULL DEFAULT 'active',
    CHECK (LENGTH(first_name) >= 2)          -- table darajasidagi check
);

Natija jadvali yo'q (1 gap bajarildi, 0 ms)

:::info 📌 Nega constraint lar dasturda emas, database da bo'lishi kerak Dastur kodini chetlab o'tish mumkin — ikkinchi ilova, admin script, qo'lda tuzatish, SQL client li stajyor orqali. Database esa oxirgi himoya chizig'i va u hech qachon unutmaydi. CHECK (grade BETWEEN 0 AND 100) 5000 ballni imkonsiz qiladi, shunchaki kam ehtimolli emas. :::

ON DELETE — farzandlarga nima bo'ladi

FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE RESTRICT -- standart: rad etadi FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE CASCADE -- farzandlarni ham o'chiradi FOREIGN KEY (course_id) REFERENCES courses(course_id) ON DELETE SET NULL -- ularni yetim qoldiradi :::gotcha ⚠️ CASCADE ko'ringanidan xavfliroq ON DELETE CASCADE bilan DELETE FROM courses WHERE course_id = 2; indamay beshta enrollment ni ham o'chiradi — va ularning ham cascade li farzandlari bo'lsa, ular ham ketadi. Bitta ifoda, noma'lum ta'sir doirasi. Standart RESTRICT ni afzal ko'ring va tartib bilan, ongli o'chiring. :::

INSERT (va upsert)

-- Nomlangan column lar — saqlanadigan kodda faqat shu ko'rinishni ishlating
INSERT INTO students (student_id, first_name, last_name, gender, city, birth_date, enrolled_date)
VALUES (11, 'Nodira', 'Yusupova', 'F', 'Tashkent', '2005-04-01', '2026-09-01');

-- O'rin bo'yicha — qisqaroq, lekin column qo'shilgan kuni buziladi
INSERT INTO students VALUES (11, 'Nodira', 'Yusupova', 'F', 'Tashkent', '2005-04-01', '2026-09-01');

-- Bitta ifodada ko'p row — ko'p ifodadan ancha tez
INSERT INTO courses (course_id, title, category, credits, price) VALUES
    (7, 'Power BI',      'Data', 3, 1600000),
    (8, 'Excel for Analysts', 'Data', 2,  800000);

-- DEFAULT i bor yoki NULL ga ruxsat beruvchi column larni tashlab ketish mumkin
INSERT INTO courses (course_id, title) VALUES (9, 'Statistics II');
-- credits o'zining DEFAULT i 3 ni oladi; category va price NULL bo'ladi

INSERT ... SELECT Query natijasini insert qilish — data ni ommaviy ko'chirish, arxivlash yoki o'zgartirish usuli:

CREATE TABLE top_students (student_id INTEGER, full_name TEXT, avg_grade REAL);

INSERT INTO top_students (student_id, full_name, avg_grade)
SELECT s.student_id,
       s.first_name || ' ' || s.last_name,
       AVG(e.grade)
FROM students s JOIN enrollments e USING (student_id)
WHERE e.grade IS NOT NULL
GROUP BY s.student_id
HAVING AVG(e.grade) >= 85;
-- Aziza 90.0, Dilnoza 88.0, Malika 89.0, Otabek 95.5 qo'shiladi

Natija jadvali yo'q (2 gap bajarildi, 1 ms)

Upsert — insert yoki update

-- SQLite / PostgreSQL
INSERT INTO courses (course_id, title, price)
VALUES (1, 'Python Basics', 1300000)
ON CONFLICT (course_id) DO UPDATE SET price = excluded.price;

-- Xato berish o'rniga row ni e'tiborsiz qoldirish
INSERT OR IGNORE INTO courses (course_id, title) VALUES (1, 'Python Basics');

-- MySQL
INSERT INTO courses (course_id, title, price) VALUES (1, 'Python Basics', 1300000)
ON DUPLICATE KEY UPDATE price = VALUES(price);

Upsert — har bir data yuklash script iga kerak bo'lgan narsa: uni ikki marta ishga tushirsangiz, natija bir marta ishga tushirgandagidek bo'lishi kerak. Bu xususiyat idempotentlik deyiladi va aynan shu pipeline ni nosozlikdan keyin qayta ishga tushirishni xavfsiz qiladi.

UPDATE

-- Bitta row ni o'zgartirish
UPDATE students SET city = 'Tashkent' WHERE student_id = 7;

-- Bir vaqtda bir nechta column
UPDATE enrollments
SET grade = 85, status = 'completed'
WHERE enrollment_id = 3;

-- Yangi qiymatni eskisidan hisoblash
UPDATE courses SET price = price * 1.10 WHERE category = 'Data';

-- CASE bilan shartli update
UPDATE enrollments
SET status = CASE WHEN grade >= 60 THEN 'completed' ELSE 'failed' END
WHERE grade IS NOT NULL AND status = 'active';

Natija jadvali yo'q (4 gap bajarildi, 0 ms)

:::gotcha ⚠️ Xavfsizlik marosimi — buni maslahat emas, qoida sifatida o'rgating Har bir UPDATE yoki DELETE dan oldin:

-- 1-QADAM: qaysi row larni o'zgartirmoqchi ekaningizni aniq ko'ring
SELECT * FROM students WHERE student_id = 7;

-- 2-QADAM: faqat endi, o'sha WHERE ni saqlab, SELECT * ni UPDATE ga almashtiring
UPDATE students SET city = 'Tashkent' WHERE student_id = 7;

1 qator

1 ms

WHERE siz UPDATE table dagi har bir row ni o'zgartiradi va transaction dan tashqarida orqaga qaytarish yo'q. Odamlar production database ni buzishning eng ko'p uchraydigan usuli aynan shu. :::

Boshqa table dan update qilish (bu yerda dialect lar sezilarli farq qiladi):

-- SQLite / PostgreSQL: correlated subquery
UPDATE students
SET city = 'Tashkent'
WHERE student_id IN (
    SELECT student_id FROM enrollments WHERE course_id = 5
);

-- PostgreSQL da UPDATE ... FROM ham bor
UPDATE students s
SET city = t.city
FROM temp_city_fixes t
WHERE s.student_id = t.student_id;

DELETE, TRUNCATE, DROP

DELETE``TRUNCATE``DROP

Nimani o'chiradiTanlangan row larniBarcha row larniButun table ni WHERE bormiHaYo'qYo'q Table qoladimiHaHaYo'q Rollback bo'ladimiHa (transaction da)Odatda yo'qOdatda yo'q Katta table da tezligiSekin (har row ni log qiladi)Juda tezJuda tez Trigger larni ishga tushiradimiHaYo'q—

DELETE FROM enrollments WHERE status = 'dropped';       -- 1 ta row o'chadi
DELETE FROM enrollments;                                 -- HAMMA 20 ta row o'chadi
DROP TABLE IF EXISTS top_students;                       -- table butunlay yo'qoladi

Natija jadvali yo'q (3 gap bajarildi, 0 ms)

:::gotcha ⚠️ SQLite da TRUNCATE yo'q DELETE FROM table; ishlating — SQLite aynan shu holatni ichkarida optimallashtiradi. Auto-increment hisoblagichlarini ham nolga qaytarish uchun sqlite_sequence dan o'sha table ning row ini o'chiring. :::

ALTER TABLE

ALTER TABLE table ning tuzilmasini u allaqachon data saqlayotgan paytda o'zgartiradi.

-- column qo'shish (mavjud row lar NULL yoki DEFAULT oladi)
ALTER TABLE students ADD COLUMN phone TEXT;
ALTER TABLE students ADD COLUMN is_active INTEGER NOT NULL DEFAULT 1;

-- column nomini o'zgartirish (SQLite 3.25+)
ALTER TABLE students RENAME COLUMN phone TO phone_number;

-- table nomini o'zgartirish
ALTER TABLE students RENAME TO learners;

-- column ni o'chirish (SQLite 3.35+)
ALTER TABLE students DROP COLUMN phone_number;

:::gotcha ⚠️ SQLite ning ALTER TABLE i ataylab cheklangan SQLite faqat column qo'shish, column nomini o'zgartirish, table nomini o'zgartirish va column o'chirish ni qo'llab-quvvatlaydi. Siz mavjud table da column ning type ini o'zgartira olmaysiz, constraint qo'sha olmaysiz yoki foreign key qo'sha olmaysiz. PostgreSQL va MySQL bularning hammasini qo'llab-quvvatlaydi.

Standart chetlab o'tish usuli — to'rt qadamli qayta qurish; buni bir marta ko'rsatishga arziydi, chunki talabalar unga duch keladi:

BEGIN TRANSACTION;
CREATE TABLE students_new ( ... yangi ta'rif ... );
INSERT INTO students_new SELECT ... FROM students;
DROP TABLE students;
ALTER TABLE students_new RENAME TO students;
COMMIT;

:::

Transaction lar — COMMIT va ROLLBACK

Transaction bir nechta ifodani bitta "yo hammasi, yo hech narsa" birligiga birlashtiradi. Yo har bir ifoda kuchga kiradi, yo hech biri.

`BEGIN TRANSACTION;

UPDATE students SET city = 'Samarkand' WHERE student_id = 1; DELETE FROM enrollments WHERE enrollment_id = 20;

-- Doimiy qilishdan oldin natijani tekshiring SELECT * FROM students WHERE student_id = 1;

COMMIT; -- doimiy qilish -- ROLLBACK; — yoki BEGIN dan keyingi hamma narsani bekor qilish:::tip 💡 Talabalar doim ishlatishi kerak bo'lgan xavfsizlik to'ri Har qanday xavfliUPDATEyokiDELETEdan oldin:BEGIN TRANSACTION;→ bajaring → tekshirish uchunSELECT→ to'g'ri bo'lsaCOMMIT, bo'lmasaROLLBACK`. Bu qaytarib bo'lmaydigan xatoni besh soniyalik tiklanishga aylantiradi. Bu bobdagi eng qimmatli odat. :::

Transaction lar mavjudligining klassik sababi — bank o'tkazmasi:

BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT; Ikkita ifoda orasida database yiqilsa, transaction pul shunchaki yo'q bo'lib ketmasligini kafolatlaydi. Usiz 100 birinchi hisobdan chiqadi va hech qayerga yetib bormaydi.

Normalization (1NF, 2NF, 3NF)

Normalization — table larni har bir fakt aynan bir marta saqlanadigan qilib tashkil qilish jarayoni. Yomon loyihalangan table dan boshlab uni qadam-baqadam tuzating — bu mavzu faqat shunday tushunarli bo'ladi.

-- Yomon table: hamma narsa bitta joyda
enrollments_bad
+----+-------------+---------------------+------------------+-------+---------------+
| id | student     | courses             | teacher          | grade | teacher_phone |
+----+-------------+---------------------+------------------+-------+---------------+
| 1  | Aziza K.    | Python, SQL, ML     | Sardor Yoqubov   | 92    | +998901112233 |
| 2  | Bekzod T.   | Python, Statistics  | Sardor Yoqubov   | 75    | +998901112233 |
+----+-------------+---------------------+------------------+-------+---------------+

Buzilish: courses vergul bilan ajratilgan ro'yxatni saqlaydi. Uni matn bilan jarrohlik qilmasdan filtrlab, join qilib yoki sanab bo'lmaydi, va WHERE courses = 'SQL' hech narsa topmaydi.

Yechim: har bir student-course juftligi uchun bitta row.

| id | student | course | teacher | grade | teacher_phone | | 1 | Aziza K. | Python | Sardor Yoqubov | 92 | +998901112233 | | 2 | Aziza K. | SQL | Rustam Halilov | 88 | +998903334455 | | 3 | Aziza K. | ML | Nodira Alimova | NULL | +998905556677 |

2NF — kalitning bir qismiga qisman bog'liqlik yo'q Buzilish: (student, course) dan iborat kompozit kalitda student ning o'z atributlari — shahri, tug'ilgan sanasi — juftlikka emas, faqat student ga bog'liq. Shuning uchun ular o'sha student chiqadigan har bir row da takrorlanadi.

Yechim: student atributlarini students table iga ko'chiring va unga student_id orqali murojaat qiling.

3NF — kalit bo'lmagan column ga bog'liqlik yo'q

Buzilish: teacher_phone enrollment ga umuman bog'liq emas; u kalit bo'lmagan teacher ga bog'liq. Sardor ning telefon raqami u o'qitadigan har bir dars uchun bir martadan saqlanadi — va u raqamini o'zgartirganda o'sha row larning hammasini yangilash kerak, aks holda database o'zi bilan zid bo'lib qoladi.

Yechim: telefonni bir marta saqlaydigan teachers table, unga teacher_id orqali murojaat qilinadi.

Uchalasini qo'llasangiz, aynan 1-bobdagi sxema chiqadi. Mashqning maqsadi shu — o'sha to'rtta table tasodifiy tanlanmagan.

View lar

View — table kabi ishlaydigan saqlangan query. U hech qanday data saqlamaydi — har safar ishlatilganda query ni qaytadan bajaradi.

CREATE VIEW enrollment_details AS
SELECT
    e.enrollment_id,
    s.first_name || ' ' || s.last_name AS student,
    c.title                            AS course,
    COALESCE(t.full_name, '(yo''q)')   AS teacher,
    e.grade,
    e.status,
    e.enrolled_at
FROM enrollments e
JOIN      students s ON e.student_id = s.student_id
JOIN      courses  c ON e.course_id  = c.course_id
LEFT JOIN teachers t ON e.teacher_id = t.teacher_id;

-- endi uni oddiy table kabi so'roq qiling
SELECT student, course, grade FROM enrollment_details WHERE grade >= 90;

DROP VIEW IF EXISTS enrollment_details;

:::tip 💡 Nega view lar o'qituvchi uchun muhim - Murakkablikni yashiradi. Talabalar to'rt table li join ni o'zlari yoza olishidan oldin enrollment_details ustida real savollarga javob bera oladi. - Bitta ta'rif, ko'p foydalanuvchi. "Faol student" bir marta ta'riflanadi; barcha hisobotlar bir xil javob beradi. - Xavfsizlik. Asosiy table o'rniga oylik yoki telefonni chiqarib tashlagan view ga ruxsat bering. - Barqarorlik. Ostidagi table lar qayta qurilayotganda ham view ning shakli o'zgarmay qolishi mumkin. :::

Mashqlar to'plami

-- 1. Mantiqiy constraint lar bilan assignments table ini yarating
CREATE TABLE IF NOT EXISTS assignments (
    assignment_id INTEGER PRIMARY KEY,
    course_id     INTEGER NOT NULL REFERENCES courses(course_id),
    title         TEXT NOT NULL,
    max_points    INTEGER NOT NULL DEFAULT 100 CHECK (max_points > 0),
    due_date      DATE
);

-- 2. Bitta ifodada uchta assignment qo'shing
INSERT INTO assignments (assignment_id, course_id, title, due_date) VALUES
    (1, 2, 'JOIN mashqlari',      '2026-03-01'),
    (2, 2, 'Window functions',    '2026-03-15'),
    (3, 1, 'Python loops',        '2026-03-10');

-- 3. Query dan xulosa table ini surat qilib saqlang
CREATE TABLE course_stats AS
SELECT c.course_id, c.title, COUNT(e.enrollment_id) AS enrollments,
       ROUND(AVG(e.grade),1) AS avg_grade
FROM courses c LEFT JOIN enrollments e USING (course_id)
GROUP BY c.course_id, c.title;

-- 4. Xavfsiz update: avval qarang, keyin o'zgartiring
SELECT * FROM enrollments WHERE enrollment_id = 3;
BEGIN TRANSACTION;
UPDATE enrollments SET grade = 85, status = 'completed' WHERE enrollment_id = 3;
SELECT * FROM enrollments WHERE enrollment_id = 3;
ROLLBACK;   -- yoki to'g'ri ko'rinsa COMMIT

-- 5. Data kategoriyasidagi narxlarni 10% ga oshiring
BEGIN TRANSACTION;
UPDATE courses SET price = ROUND(price * 1.10) WHERE category = 'Data';
SELECT title, price FROM courses WHERE category = 'Data';
ROLLBACK;

-- 6. CASE yordamida status ni bahodan aniqlang
UPDATE enrollments
SET status = CASE WHEN grade >= 60 THEN 'completed' ELSE 'failed' END
WHERE grade IS NOT NULL;

-- 7. Hisobot view ini yarating
CREATE VIEW IF NOT EXISTS enrollment_details AS
SELECT e.enrollment_id, s.first_name || ' ' || s.last_name AS student,
       c.title AS course, e.grade, e.status
FROM enrollments e
JOIN students s USING (student_id)
JOIN courses  c USING (course_id);

-- 8. Bu mashq yaratgan hamma narsani tozalang
DROP VIEW  IF EXISTS enrollment_details;
DROP TABLE IF EXISTS course_stats;
DROP TABLE IF EXISTS assignments;

Intervyu savollari

S: DELETE, TRUNCATE va DROP?

DELETE tanlangan row larni o'chiradi, WHERE bo'lishi mumkin, transaction ga bo'ysunadi va trigger larni ishga tushiradi. TRUNCATE table ni WHERE siz tez bo'shatadi va odatda rollback qilinmaydi. DROP table ning o'zini o'chiradi.

S: Transaction nima va ACID nimani anglatadi?

Bitta "yo hammasi, yo hech narsa" birligi sifatida qaraladigan ifodalar guruhi. Atomicity, Consistency, Isolation, Durability.

S: Nega constraint larni dasturda emas, database da qo'yish kerak?

Dastur kodini boshqa ilovalar, script lar yoki qo'lda kirish chetlab o'tishi mumkin. Database qoidani har bir client uchun, doimiy majburlaydi.

S: PRIMARY KEY va UNIQUE farqi nima?

Primary key unique va NULL emas, va table da aynan bittasi bo'ladi. Unique constraint NULL ga (odatda bittasiga) ruxsat beradi va table da bir nechtasi bo'lishi mumkin.

S: ON DELETE CASCADE nima qiladi va qachon xavfli?

Ota row ni o'chirish uning farzandlarini avtomatik o'chiradi. Xavfli, chunki bitta kichik o'chirish hech qanday ogohlantirishsiz bir nechta table bo'ylab tarqalishi mumkin; RESTRICT xavfsizroq standart.

S: 1NF, 2NF va 3NF ni bittadan jumlada tushuntiring.

1NF: katakda bitta qiymat. 2NF: 1NF va kompozit kalitning faqat bir qismiga bog'liq column yo'q. 3NF: 2NF va kalit bo'lmagan column boshqa kalit bo'lmagan column ga bog'liq emas.

S: Qachon ataylab denormalize qilasiz?

Analytics/OLAP tizimlarida, o'qish tezligi yozish anomaliyalaridan muhimroq bo'lganda — keng table lar va star schema hisobotga kamroq join degani.

S: View nima va u data saqlaydimi?

Nomlangan saqlangan query. U hech qanday data saqlamaydi va har safar qaytadan bajariladi. Materialized view esa natijalarni saqlaydi va yangilanib turishi kerak.

S: Qayta ishga tushirish xavfsiz bo'lgan insert ni qanday yozasiz?

Upsert ishlating — INSERT ... ON CONFLICT DO UPDATE (SQLite/PostgreSQL) yoki ON DUPLICATE KEY UPDATE (MySQL) — shunda amal idempotent bo'ladi.

Xulosa

• CREATE TABLE type larni va qoidalarni e'lon qiladi. CREATE TABLE AS SELECT query ni surat qiladi, lekin hech qanday constraint yoki index ko'chirmaydi.
• Constraint lar — PRIMARY KEY, NOT NULL, UNIQUE, CHECK, DEFAULT, FOREIGN KEY — database ning oxirgi himoya chizig'i va ularni chetlab o'tib bo'lmaydi.
• SQLite har bir sessiyada PRAGMA foreign_keys = ON; talab qiladi, aks holda foreign key lar umuman majburlanmaydi.
• INSERT: column larni nomlab yozing; bitta ifodada ko'p row qo'shing; ommaviy ish uchun INSERT ... SELECT va idempotent yuklash uchun upsert ishlating.
• Doim avval o'sha WHERE bilan SELECT qiling, keyin UPDATE/DELETE ga almashtiring. WHERE yo'qligi butun table ni o'zgartiradi.
• BEGIN TRANSACTION → o'zgartiring → tekshiring → COMMIT yoki ROLLBACK. Bu — falokatni besh soniyalik tuzatishga aylantiradigan odat. ACID = Atomicity, Consistency, Isolation, Durability.
• Normalization: 1NF katakda bitta qiymat · 2NF qisman bog'liqlik yo'q · 3NF kalit bo'lmagan bog'liqlik yo'q. Uchalasini iflos table ga qo'llash aynan 1-bobdagi sxemani beradi.
• Analytics uchun denormalization to'g'ri — star schema yozish xavfsizligini o'qish tezligiga almashtiradi.
• View lar — hech narsa saqlamaydigan saqlangan query lar: ular murakkablikni yashiradi, ta'riflarni markazlashtiradi va kirishni cheklaydi — lekin sekin query ni tez qilmaydi.

Keyingi: 11-bob — index lar, EXPLAIN va nega bitta query 3 millisekund, boshqasi esa 3 daqiqa ishlaydi.