Dasturlash kitobi/6-QISM — Database19 daqiqa

6.4-bob: SQL asoslari — CREATE, INSERT, SELECT, UPDATE, DELETE, WHERE

6-QISM — Ma'lumotlar bazasi (Database) · 4-mavzu

1. Kirish va motivatsiya

MongoDB (NoSQL) dunyosini chuqur ko'rdik (6.2, 6.3). Endi ikkinchi katta dunyoga — relatsion ma'lumotlar bazasi va SQL tiliga — o'tamiz. Bu — dasturlashning eng qadimiy, eng barqaror, eng keng tarqalgan texnologiyalaridan biri (1970-yillardan). Bank, e-commerce, ERP, davlat tizimlari — deyarli hammasi SQL'da. Sizning stack'ingda PostgreSQL va MySQL bor; ORM'lar (Sequelize, Prisma, TypeORM — 6.14) ham SQL ustida ishlaydi. SQL'ni bilmasdan, ularni chuqur tushunib bo'lmaydi.

SQL (Structured Query Language) — relatsion DB bilan ishlash tili (6.1: 2.6). U deklarativ — siz nima kerakligini aytasiz ("18 dan katta foydalanuvchilarni ber"), DB esa qanday topishni o'zi hal qiladi (optimizatsiya). Bu — imperativ kod (JS — har qadamni yozasiz) dan farqli. SQL — standart: bir marta o'rgansangiz, PostgreSQL, MySQL, SQLite, SQL Server — hammasida deyarli bir xil ishlaydi.

Bu bobda SQL'ning poydevorini quramiz: jadval yaratish (CREATE), ma'lumot qo'shish (INSERT), o'qish (SELECT — eng muhim, eng katta), yangilash (UPDATE), o'chirish (DELETE), shartlar (WHERE), turlar va cheklovlar (constraints). Keyingi boblar (MySQL/PostgreSQL, JOIN, normalizatsiya — 6.5-6.13) shu asosga quriladi.

O'xshatish: SQL — kutubxonachiga buyruq berish. MongoDB'da o'zingiz javonlarga borib ma'lumot olasiz (kod bilan); SQL'da kutubxonachiga gapirib aytasiz: "18 yoshdan katta, Toshkentdan, ism bo'yicha tartiblangan o'quvchilar ro'yxatini ber". U qanday topishni o'zi biladi (qaysi javon, qaysi indeks). Siz — natijani so'raysiz, u — topib beradi. Deklarativ til.

Nega muhim?

Eng keng tarqalgan — bank, e-commerce, ERP; ko'p ish o'rni SQL talab qiladi.
Standart — bir marta o'rgansangiz, barcha SQL DB'da ishlaydi.
ORM asosi — Sequelize/Prisma/TypeORM 6.14-bob SQL ustida; SQL'ni bilish shart.
Barqaror — 50 yildan beri; o'rganish — uzoq muddatli sarmoya.

2. Nazariya — chuqur tushuntirish

2.1. SQL nima va deklarativlik

SQL — relatsion DB so'rovlari tili. Asosiy xususiyat — deklarativ (6.1: 2.6):

text

  Imperativ (JS — qanday):              Deklarativ (SQL — nima):
  const natija = [];                    SELECT * FROM users WHERE yosh > 18;
  for (const u of users) {               "18 dan katta foydalanuvchilar"
    if (u.yosh > 18) natija.push(u);     (qanday topishni DB hal qiladi)
  }

Deklarativlik kuchi: siz maqsadni aytasiz, DB optimizatsiya qiladi (qaysi indeks, qaysi tartib — 6.13). Bu — SQL'ning soddaligi va kuchi. Murakkab so'rov ham — bir necha qator.

2.2. SQL buyruqlar turlari (DDL, DML, ...)

SQL buyruqlari guruhlarga bo'linadi (drivedatascience):

text

  DDL (Data Definition Language) — TUZILMA: CREATE, ALTER, DROP (jadval yaratish/o'zgartirish)
  DML (Data Manipulation Language) — MA'LUMOT: INSERT, UPDATE, DELETE, SELECT
  DCL (Data Control Language) — RUXSAT: GRANT, REVOKE (kim nima qila oladi)
  TCL (Transaction Control) — TRANZAKSIYA: BEGIN, COMMIT, ROLLBACK (6.11)

Bu bobda asosan DDL (CREATE) va DML (INSERT/SELECT/UPDATE/DELETE). DCL/TCL — keyingi boblar (6.11: tranzaksiya).

2.3. Ma'lumot turlari (data types)

SQL'da har ustun aniq turga ega bo'lishi shart (6.1: 2.9 qat'iy schema):

text

  Sonlar:    INTEGER (butun), BIGINT (katta), DECIMAL/NUMERIC (aniq kasr — pul!),
             SERIAL (avto-o'suvchi — PostgreSQL), REAL/FLOAT (suzuvchi)
  Matn:      VARCHAR(n) (cheklangan), TEXT (uzun), CHAR(n) (qat'iy uzunlik)
  Sana/vaqt: DATE, TIME, TIMESTAMP, TIMESTAMPTZ (vaqt zonasi bilan)
  Boolean:   BOOLEAN (true/false)
  Boshqa:    JSON/JSONB (PostgreSQL — NoSQL ichida!), UUID, ENUM

Pul uchun DECIMAL (FLOAT emas — 14): FLOAT/REAL — taqribiy (0.1 + 0.2 ≠ 0.3 — 2.1-JS). Pul, narx — DECIMAL/NUMERIC (aniq). Bu — moliyaviy ma'lumotda muhim. Matn uzunligini biling (VARCHAR(50) vs TEXT).

2.4. CREATE TABLE — jadval yaratish (DDL)

CREATE TABLE — jadval (6.1: 2.4) tuzilmasini belgilaydi:

sql

CREATE TABLE users (
  id SERIAL PRIMARY KEY,              -- avto-o'suvchi, asosiy kalit
  ism VARCHAR(50) NOT NULL,          -- matn, majburiy
  email VARCHAR(100) UNIQUE NOT NULL, -- noyob, majburiy
  yosh INTEGER CHECK (yosh >= 18),   -- son, shart (18+)
  faol BOOLEAN DEFAULT true,         -- standart qiymat
  yaratilgan TIMESTAMP DEFAULT NOW() -- avtomatik vaqt
);

Har ustun: nom + tur 2.3-bob + cheklovlar (constraints — 2.5). Schema oldindan belgilanadi (qat'iy — 6.1: 2.9). Keyin o'zgartirish — ALTER TABLE (migration — 6.16).

2.5. Cheklovlar (constraints) — ma'lumot yaxlitligi

Constraint — ustunga qoida (noto'g'ri ma'lumot kirmasligi — w3schools):

text

  PRIMARY KEY  — noyob identifikator (har qator uchun bitta — 6.1: 2.4)
  FOREIGN KEY  — boshqa jadvalga ishora (bog'lanish — 2.6, 6.1: 2.5)
  UNIQUE       — takrorlanmas qiymat (email)
  NOT NULL     — bo'sh bo'lmaydi (majburiy)
  CHECK        — shart (yosh >= 18, narx > 0)
  DEFAULT      — standart qiymat (berilmasa)

Constraint — DB darajasidagi himoya (5.9: API validatsiya birinchi; bu — oxirgi, ishonchli himoya). Masalan UNIQUE email — ikki bir xil email hech qachon kira olmaydi (kodda xato bo'lsa ham). Bu — relatsion DB'ning yaxlitlik kuchi.

2.6. FOREIGN KEY — bog'lanish (relations)

FOREIGN KEY — jadvallarni bog'laydi (6.1: 2.5):

sql

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),   -- users.id ga ishora (FK)
  summa DECIMAL(10,2),
  -- yoki to'liq:
  -- FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

FK kafolati (dbschema): user_idga faqat mavjud users.id kirishi mumkin (yo'q foydalanuvchiga buyurtma — imkonsiz). ON DELETE CASCADE — foydalanuvchi o'chsa, uning buyurtmalari ham o'chadi (yoki SET NULL, RESTRICT). Bu — ma'lumot yaxlitligining asosi (MongoDB'da bu qo'lda — 6.3).

2.7. INSERT — ma'lumot qo'shish (DML)

sql

-- Bitta qator
INSERT INTO users (ism, email, yosh) VALUES ('Ali', 'ali@a.uz', 25);

-- Bir necha qator
INSERT INTO users (ism, email, yosh) VALUES
  ('Vali', 'vali@a.uz', 30),
  ('Hasan', 'hasan@a.uz', 22);

-- Qo'shib, natijani qaytarish (PostgreSQL)
INSERT INTO users (ism, email) VALUES ('Olim', 'olim@a.uz') RETURNING id, ism;

id va yaratilgan — berilmaydi (SERIAL/DEFAULT avtomatik — 2.4). RETURNING (PostgreSQL) — qo'shilgan qatorni qaytaradi (yangi id'ni olish — qulay).

2.8. SELECT — o'qish (eng muhim buyruq)

SELECT — ma'lumot o'qish (eng ko'p ishlatiladigan, eng katta imkoniyatli):

sql

SELECT * FROM users;                        -- barcha ustun, barcha qator
SELECT ism, email FROM users;               -- faqat ism, email
SELECT * FROM users WHERE yosh > 18;        -- shartli (WHERE — 2.9)
SELECT DISTINCT shahar FROM users;          -- takrorlanmas qiymatlar
SELECT COUNT(*) FROM users;                 -- nechta qator (agregat — 6.7)

SELECT — SQL'ning yuragi. * — barcha ustun (lekin kerakli ustunlarni tanlash — yaxshiroq, tez). Keyingi qismlar (WHERE, ORDER BY, JOIN, GROUP BY) SELECTni kuchaytiradi.

2.9. WHERE — shartlash (filtrlash)

WHERE — qatorlarni filtrlaydi (MongoDB'dagi find sharti — 6.2: 2.8):

sql

SELECT * FROM users WHERE yosh > 18;                    -- taqqoslash
SELECT * FROM users WHERE yosh BETWEEN 18 AND 60;       -- oraliq
SELECT * FROM users WHERE shahar IN ('Toshkent', 'Samarqand');  -- ro'yxat
SELECT * FROM users WHERE ism LIKE 'Al%';              -- matn ('Al' bilan boshlanadi)
SELECT * FROM users WHERE email IS NULL;               -- bo'sh
SELECT * FROM users WHERE yosh > 18 AND faol = true;   -- mantiq (AND/OR/NOT)

WHERE operatorlari: =, <>, <, >, <=, >= (taqqoslash), BETWEEN (oraliq), IN (ro'yxat), LIKE (matn — % har qancha belgi, _ bitta), IS NULL, AND/OR/NOT (mantiq). Bular — MongoDB query operatorlarining (6.2: 2.8) SQL ekvivalenti.

2.10. ORDER BY, LIMIT, OFFSET (tartib, sahifalash)

sql

SELECT * FROM users ORDER BY yosh DESC;            -- yosh bo'yicha kamayish (ASC — o'sish)
SELECT * FROM users ORDER BY shahar ASC, yosh DESC; -- ko'p ustun bo'yicha
SELECT * FROM users LIMIT 10;                       -- 10 ta
SELECT * FROM users LIMIT 10 OFFSET 20;             -- 20 tasini o'tkazib, 10 ta (page 3)

ORDER BY (tartib), LIMIT/OFFSET (sahifalash — 5.7) — MongoDB'dagi sort/limit/skip (6.2: 2.9) ekvivalenti. Sahifalash: LIMIT 10 OFFSET (page-1)*10.

2.11. UPDATE — yangilash (DML)

sql

UPDATE users SET yosh = 26 WHERE id = 1;             -- bitta maydon
UPDATE users SET yosh = 26, faol = false WHERE id = 1;  -- bir necha
UPDATE products SET narx = narx * 1.1 WHERE kategoriya = 'telefon';  -- hisoblash (10% oshirish)

WHERE'ni UNUTMANG (eng xavfli xato — 14): UPDATE users SET faol = false; — WHERE'siz BARCHA foydalanuvchini o'zgartiradi (falokat!). Har doim WHERE bilan (qaysi qator). Bu — eng ko'p uchraydigan, eng qimmat xato.

2.12. DELETE — o'chirish (DML)

sql

DELETE FROM users WHERE id = 1;                      -- bitta qator
DELETE FROM users WHERE faol = false;                -- shartli
DELETE FROM users;                                   --  HAMMASI (WHERE'siz — xavfli!)

DELETE'da ham WHERE majburiy (2.11 kabi): DELETE FROM users; — butun jadvalni bo'shatadi. Production'da soft delete (6.2: 2.11 — UPDATE ... SET ochirilgan = true) ko'pincha afzal (tiklash mumkin, audit).

2.13. ALTER TABLE — tuzilmani o'zgartirish (DDL)

sql

ALTER TABLE users ADD COLUMN telefon VARCHAR(20);    -- ustun qo'shish
ALTER TABLE users DROP COLUMN telefon;               -- ustun o'chirish
ALTER TABLE users ALTER COLUMN yosh SET NOT NULL;    -- o'zgartirish
ALTER TABLE users RENAME COLUMN ism TO toliq_ism;    -- nom o'zgartirish

Schema qat'iy (6.1: 2.9), shuning uchun o'zgartirish — ALTER. Production'da bu — migration 6.16-bob orqali boshqariladi (nazorat ostida, qaytariladigan).

2.14. NULL — "qiymat yo'q" (maxsus)

NULL — "qiymat yo'q/noma'lum" (bo'sh string yoki 0 emas):

sql

SELECT * FROM users WHERE email IS NULL;       -- bo'sh (= NULL ishlamaydi!)
SELECT * FROM users WHERE email IS NOT NULL;   -- bor
-- NULL bilan hisob: NULL + 5 = NULL (har amal NULL beradi)
SELECT COALESCE(telefon, 'kiritilmagan') FROM users;  -- NULL bo'lsa, default

NULL — maxsus: email = NULL ishlamaydi (NULL hech narsaga teng emas, hatto NULL'ga ham). IS NULL/IS NOT NULL ishlating. COALESCE — NULL bo'lsa, muqobil qiymat. NULL — yangi boshlovchilar uchun chalkash, lekin muhim.

2.15. SQL injection (eng muhim xavfsizlik — 14)

Eng xavfli SQL hujumi — SQL injection (foydalanuvchi kiritmasi orqali zararli SQL — 14):

text

   XAVFLI (string birlashtirish):
  query = "SELECT * FROM users WHERE ism = '" + input + "'";
  Agar input = "'; DROP TABLE users; --"    jadval o'chiriladi! (falokat)

   XAVFSIZ (parametrli so'rov / prepared statement):
  query = "SELECT * FROM users WHERE ism = $1";   // $1 — parametr
  db.query(query, [input]);                        // input alohida (SQL emas)

HECH QACHON foydalanuvchi kiritmasini SQL'ga string bilan birlashtirmang (14): har doim parametrli so'rov (prepared statement — $1, ?). Driver/ORM input'ni ma'lumot sifatida ishlaydi (SQL kod emas). Bu — OWASP Top 10 (14: 6.5'da pg, ORM'lar buni avtomatik qiladi).

2.16. SQL'ni qayerda ishlatish (DBMS)

text

  PostgreSQL — eng kuchli, ochiq kodli (tavsiya — 6.6); JSON, kengaytmalar
  MySQL      — mashhur, keng tarqalgan 6.5-bob
  SQLite     — fayl-DB (kichik, mobil, test)
  SQL Server — Microsoft; Oracle — korporativ

  Hammasida SQL deyarli BIR XIL (standart) — kichik farqlar bor

Bu bobda standart SQL (hammasiga mos). Keyingi boblar: MySQL 6.5-bob, PostgreSQL 6.6-bob — Node.js bilan. SQL'ni o'rganib, har birida ishlatasiz.

2.17. Xavfsizlik va best practices (14)

text

   SQL injection — parametrli so'rov (15-bo'lim, 14) — ENG MUHIM
   UPDATE/DELETE doim WHERE bilan (2.11, 2.12)
   Pul — DECIMAL (FLOAT emas — 2.3)
   Constraint'lar (NOT NULL, UNIQUE, CHECK, FK) — yaxlitlik 2.5-bob
   Kerakli ustunlarni tanlash (SELECT * o'rniga — tez)
   DB foydalanuvchisiga eng kam imtiyoz (14)
   Migration bilan schema o'zgartirish (6.16)

2.18. Agregat funksiyalar va GROUP BY (jamlash)

Agregat funksiyalar — ko'p qatorni bitta qiymatga jamlaydi (sanash, yig'ish, o'rtacha):

sql

SELECT COUNT(*) FROM users;                    -- nechta qator
SELECT COUNT(email) FROM users;                -- NULL bo'lmagan email soni (NULL sanalmaydi — 2.14)
SELECT SUM(balans) FROM users;                 -- yig'indi
SELECT AVG(yosh), MIN(yosh), MAX(yosh) FROM users;  -- o'rtacha, eng kichik, eng katta

GROUP BY — qatorlarni guruhlarga bo'lib, har guruh uchun agregat hisoblaydi:

sql

-- Har shahar bo'yicha foydalanuvchi soni
SELECT shahar, COUNT(*) FROM users GROUP BY shahar;

-- Faqat 5 tadan ko'p bo'lgan shaharlar (HAVING — guruh sharti)
SELECT shahar, COUNT(*) FROM users
GROUP BY shahar
HAVING COUNT(*) > 5;

WHERE vs HAVING: WHERE — qatorlarni guruhlashdan oldin filtrlaydi 2.9-bob; HAVING — guruhlarni agregatdan keyin filtrlaydi (agregatga shart qo'yish uchun). Agregat asoslari shu yerda; ko'p jadval bo'yicha jamlash — JOIN 6.7-bob bilan chuqurroq.

3. Sintaksis — tez ma'lumotnoma

sql

-- DDL (tuzilma — 2.4)
CREATE TABLE users (id SERIAL PRIMARY KEY, ism VARCHAR(50) NOT NULL, email VARCHAR(100) UNIQUE);
ALTER TABLE users ADD COLUMN telefon VARCHAR(20);   -- (2.13)
DROP TABLE users;

-- DML (ma'lumot)
INSERT INTO users (ism, email) VALUES ('Ali', 'ali@a.uz');           -- C (2.7)
SELECT * FROM users WHERE yosh > 18 ORDER BY ism LIMIT 10;           -- R (2.8-2.10)
UPDATE users SET yosh = 26 WHERE id = 1;                             -- U (2.11)
DELETE FROM users WHERE id = 1;                                       -- D (2.12)

-- WHERE 2.9-bob: =, >, BETWEEN, IN, LIKE, IS NULL, AND/OR
--  Parametrli (injection himoyasi — 2.15): WHERE ism = $1

4. Batafsil kod namunalari

Misol 1 — Jadval yaratish (constraints bilan — 2.4, 2.5)

sql

-- Foydalanuvchilar jadvali (to'liq cheklovlar — 2.5)
CREATE TABLE users (
  id SERIAL PRIMARY KEY,                          -- avto-o'suvchi PK (2.4)
  ism VARCHAR(50) NOT NULL,                       -- majburiy
  email VARCHAR(100) UNIQUE NOT NULL,             -- noyob + majburiy
  parol VARCHAR(255) NOT NULL,                    -- hash (5.15)
  yosh INTEGER CHECK (yosh >= 18),                -- shart (2.5)
  rol VARCHAR(20) DEFAULT 'user'                  -- standart
    CHECK (rol IN ('user', 'admin', 'moderator')), -- enum kabi (5.17)
  balans DECIMAL(12,2) DEFAULT 0,                 -- pul — DECIMAL! (2.3)
  faol BOOLEAN DEFAULT true,
  created_at TIMESTAMP DEFAULT NOW(),             -- avtomatik vaqt
  updated_at TIMESTAMP DEFAULT NOW()
);

Misol 2 — Bog'langan jadval (FK — 2.6)

sql

-- Buyurtmalar — users'ga bog'langan (2.6, 6.1: 2.5)
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,   -- FK (2.6)
  summa DECIMAL(10,2) NOT NULL CHECK (summa > 0),                    -- pul, musbat
  holat VARCHAR(20) DEFAULT 'yangi'
    CHECK (holat IN ('yangi', 'tasdiqlandi', 'yetkazildi', 'bekor')),
  created_at TIMESTAMP DEFAULT NOW()
);
-- ON DELETE CASCADE: user o'chsa, buyurtmalari ham o'chadi (2.6)

Misol 3 — INSERT (turli usul — 2.7)

sql

-- Bitta
INSERT INTO users (ism, email, parol, yosh)
VALUES ('Ali', 'ali@a.uz', '$2b$12$...', 25);

-- Ko'p qator
INSERT INTO users (ism, email, parol) VALUES
  ('Vali', 'vali@a.uz', '$2b$...'),
  ('Hasan', 'hasan@a.uz', '$2b$...');

-- Qo'shib, id'ni qaytarish (PostgreSQL — 2.7)
INSERT INTO orders (user_id, summa) VALUES (1, 50000.00) RETURNING id, created_at;

Misol 4 — SELECT (filtr, tartib, sahifalash — 2.8-2.10)

sql

-- Asosiy o'qish (kerakli ustunlar — 2.8)
SELECT id, ism, email FROM users WHERE faol = true;

-- Murakkab WHERE (2.9)
SELECT * FROM users
WHERE yosh BETWEEN 18 AND 35
  AND rol IN ('user', 'moderator')
  AND ism LIKE 'A%'                              -- 'A' bilan boshlanadi
ORDER BY yosh DESC, ism ASC                      -- tartib (2.10)
LIMIT 20 OFFSET 0;                               -- sahifalash (2.10)

-- Sanaga oid
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND holat = 'tugallandi'
ORDER BY created_at DESC;

Misol 5 — UPDATE (xavfsiz — 2.11)

sql

-- Bitta qator (WHERE bilan — 2.11)
UPDATE users SET yosh = 26, updated_at = NOW() WHERE id = 1;

-- Hisoblash bilan (narxni 10% oshirish)
UPDATE products SET narx = narx * 1.10 WHERE kategoriya = 'telefon';

-- Shartli ko'p qator
UPDATE orders SET holat = 'bekor'
WHERE holat = 'yangi' AND created_at < NOW() - INTERVAL '30 minutes';   -- 30 daqiqa eski

--  HAR DOIM WHERE (aks holda hammasi o'zgaradi — 2.11)

Misol 6 — DELETE va soft delete (2.12)

sql

-- Hard delete (butunlay — ehtiyot bilan, WHERE bilan — 2.12)
DELETE FROM orders WHERE id = 10;

-- Soft delete (tavsiya — belgilash, 6.2: 2.11)
ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP;   -- ustun qo'shing
UPDATE users SET deleted_at = NOW() WHERE id = 1;     -- "o'chirish"
SELECT * FROM users WHERE deleted_at IS NULL;         -- o'chirilmaganlar (2.14)

Misol 7 — Node.js'da SQL (pg — parametrli — 2.15)

// PostgreSQL driver (pg) — 6.6'da chuqur
import pg from "pg";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });   // (5.8)

//  XAVFLI — string birlashtirish (SQL injection — 2.15, 14)
// const r = await pool.query(`SELECT * FROM users WHERE email = '${email}'`);

//  XAVFSIZ — parametrli so'rov ($1 — 2.15)
const r = await pool.query(
  "SELECT id, ism, email FROM users WHERE email = $1",   // $1 — joy
  [email]                                                 // qiymat alohida (SQL emas!)
);
console.log(r.rows);   // natija qatorlari

Misol 8 — To'liq CRUD (Node.js + pg — 2.7-2.12)

// CREATE
const yangi = await pool.query(
  "INSERT INTO users (ism, email, parol) VALUES ($1, $2, $3) RETURNING id, ism, email",
  [ism, email, parolHash]                                 // parametrli (2.15)
);

// READ (sahifalash — 2.10)
const royxat = await pool.query(
  "SELECT id, ism, email FROM users WHERE faol = $1 ORDER BY created_at DESC LIMIT $2 OFFSET $3",
  [true, limit, (page - 1) * limit]
);

// UPDATE (2.11)
const yangilangan = await pool.query(
  "UPDATE users SET ism = $1, updated_at = NOW() WHERE id = $2 RETURNING *",
  [yangiIsm, id]
);

// DELETE (2.12)
await pool.query("DELETE FROM users WHERE id = $1", [id]);

5. To'g'ri va noto'g'ri holatlar

1) SQL injection (string birlashtirish)

//  FALOKAT — injection (14, 2.15)
pool.query(`SELECT * FROM users WHERE email = '${email}'`);

//  parametrli
pool.query("SELECT * FROM users WHERE email = $1", [email]);

2) WHERE'siz UPDATE/DELETE

sql

--  HAMMASI o'zgaradi/o'chadi (falokat — 2.11, 2.12)
UPDATE users SET faol = false;
DELETE FROM users;

--  WHERE bilan
UPDATE users SET faol = false WHERE id = 1;

3) Pul uchun FLOAT

sql

--  taqribiy (yaxlitlik xatosi — 2.3)
balans REAL

--  aniq
balans DECIMAL(12,2)

4) NULL'ni = bilan tekshirish

sql

--  ishlamaydi (2.14)
WHERE email = NULL

--  IS NULL
WHERE email IS NULL

5) SELECT * (kerakmas ustunlar)

sql

--  hamma ustun (parol ham! — tez emas, 14)
SELECT * FROM users;

--  kerakli ustunlar
SELECT id, ism, email FROM users;

6. Keng tarqalgan xatolar va yechimlari

Xato 1 — syntax error at or near ...

Sababi: SQL sintaksis xatosi (vergul, qavs, kalit so'z). Yechimi: so'rovni diqqat bilan tekshiring; SQL formatlovchi; kichik-katta harf.

Xato 2 — duplicate key value violates unique constraint

Sababi: UNIQUE buzildi (email takror — 2.5). Yechimi: oldindan tekshiring; 409 qaytaring 5.10-bob; ON CONFLICT (PostgreSQL upsert).

Xato 3 — violates foreign key constraint

Sababi: mavjud bo'lmagan FK (yo'q user_id — 2.6), yoki bog'liq qator o'chirishga urinish. Yechimi: FK mavjudligini tekshiring; ON DELETE CASCADE/SET NULL.

Xato 4 — null value violates not-null constraint

Sababi: NOT NULL ustun bo'sh qoldirildi 2.5-bob. Yechimi: majburiy maydonni bering; DEFAULT qo'shing.

Xato 5 — SQL injection ehtimoli

Sababi: string birlashtirish 2.15-bob. Yechimi: parametrli so'rov ($1, ?); ORM 6.14-bob.

Xato 6 — Noto'g'ri natija (WHERE mantiq)

Sababi: AND/OR tartibi, qavs yo'q. Yechimi: murakkab shartda qavs: WHERE (a OR b) AND c.

7. Integratsiya — bu mavzu stack'ning qayerida uchraydi

DB asoslari 6.1-bob: relatsion model, jadval, FK.
MongoDB 6.2-bob: taqqoslash (find SELECT, query operatorlar).
MySQL/PostgreSQL (6.5, 6.6): SQL'ni Node.js bilan ishlatish.
JOIN 6.7-bob: ko'p jadval — keyingi daraja.
Tranzaksiya 6.11-bob: ACID, BEGIN/COMMIT.
ORM 6.14-bob: Sequelize/Prisma/TypeORM — SQL'ni avtomatlashtiradi.
Xavfsizlik (14): SQL injection — eng muhim.
Validatsiya 5.9-bob: API + DB constraint (ikki qatlam).
NestJS 8.3-bob: TypeORM/Prisma — SQL ustida.

8. Eng yaxshi amaliyotlar (best practices)

Parametrli so'rov (SQL injection — ENG MUHIM — 2.15, 14).
UPDATE/DELETE doim WHERE bilan (2.11, 2.12).
Pul — DECIMAL (FLOAT emas — 2.3).
Constraint'lardan foydalaning (NOT NULL, UNIQUE, CHECK, FK — yaxlitlik — 2.5).
Kerakli ustunlarni tanlang (SELECT * o'rniga — tez, maxfiy yashiring — 2.8).
NULL'ni IS NULL bilan (= emas — 2.14).
Soft delete (muhim ma'lumot — 2.12).
Schema o'zgarishi migration bilan (ALTER nazorat ostida — 6.16).
Sahifalash (LIMIT/OFFSET — katta natija — 2.10).
DB foydalanuvchisiga eng kam imtiyoz (14, 2.17).

9. Amaliy loyiha: "SQL Schema va CRUD (PostgreSQL)"

SQL asoslarini mustahkamlash.

Maqsad

Relatsion schema loyihalash va to'liq CRUD'ni SQL bilan amalga oshirish (e-commerce yoki blog).

Talablar (requirements)

Schema: kamida 3 bog'langan jadval (users, products, orders) — PK, FK, constraints (Misol 1, 2, 2.4-2.6).
To'g'ri turlar: pul DECIMAL, sana TIMESTAMP, enum CHECK (2.3, 2.5).
INSERT: turli usul; RETURNING (Misol 3, 2.7).
SELECT: filtr (WHERE — BETWEEN/IN/LIKE), tartib, sahifalash (Misol 4, 2.8-2.10).
UPDATE: WHERE bilan; hisoblash (narx oshirish — Misol 5, 2.11).
DELETE + soft delete: ikkalasi (Misol 6, 2.12).
FK xatti-harakati: ON DELETE CASCADE/SET NULL 2.6-bob.
Node.js integratsiya: pg, parametrli so'rov (injection himoyasi — Misol 7, 8, 2.15).
Constraint test: UNIQUE/CHECK/NOT NULL buzilishini ko'rsating 2.5-bob.

Maslahatlar (hint)

Pul: DECIMAL(12,2) (2.3, 3-xato).
FK: REFERENCES users(id) ON DELETE CASCADE 2.6-bob.
Parametrli: query("... $1", [val]) (2.15, 1-xato).
UPDATE/DELETE: WHERE majburiy (2.11, 2-xato).
Sahifalash: LIMIT $1 OFFSET $2 2.10-bob.
NULL: IS NULL (2.14, 4-xato).

"Tayyor" mezonlari (acceptance criteria)

3 bog'langan jadval (PK/FK/constraints).
To'g'ri turlar (DECIMAL pul, CHECK enum).
INSERT (RETURNING bilan).
SELECT (filtr/tartib/sahifalash).
UPDATE (WHERE, hisoblash).
DELETE + soft delete.
FK xatti-harakati (CASCADE).
Node.js parametrli so'rov (injection himoyasi).
Constraint'lar ishlaydi (noto'g'ri ma'lumot rad).

Yechim kodi ataylab berilmagan — bu loyihani o'zingiz yozib ko'ring.

10. Xulosa va keyingi bobga ko'prik

Bu bobda relatsion DB tilini — SQL asoslari — o'rgandik:

SQL — deklarativ til (nima kerakligini aytasiz — 2.1); buyruq turlari (DDL/DML — 2.2); turlar (DECIMAL pul! — 2.3).
CREATE TABLE 2.4-bob + constraints (PK/FK/UNIQUE/CHECK/NOT NULL — yaxlitlik — 2.5, 2.6).
CRUD: INSERT 2.7-bob, SELECT (WHERE/ORDER/LIMIT — 2.8-2.10), UPDATE 2.11-bob, DELETE 2.12-bob — WHERE majburiy!
NULL (maxsus — IS NULL — 2.14); SQL injection (parametrli so'rov — ENG MUHIM — 2.15, 14); ALTER 2.13-bob.
Agregat (COUNT/SUM/AVG) va GROUP BY/HAVING — jamlash asoslari (2.18; chuqurroq — 6.7).

Keyingi bob — 6.5-bob: MySQL (Express bilan). SQL tilini bildik; endi uni amalda — MySQL DB'sini Node.js/Express bilan ulashni o'rganamiz: o'rnatish, ulanish (mysql2), so'rovlar, connection pool, va Express API bilan to'liq integratsiya.

Foydalanilgan rasmiy/ishonchli manbalar

W3Schools — SQL (CREATE, INSERT, SELECT, UPDATE, DELETE, constraints, FK, PK)
DriveDataScience — SQL DDL/DML; DbSchema — Foreign Key (2026); Codecademy — SQL cheatsheet
OWASP — SQL Injection (parametrli so'rov)

Izohlar (0)

Izoh yozish uchun kiring.

Hozircha izoh yo'q. Birinchi bo'ling!