Python kursi/Bazalar va ORM1/12-dars17 daqiqa

Mundarija (22)

23.1-dars: SQL asoslari

23-QISM — BAZALAR VA ORM · 1-dars

1. Kirish va motivatsiya

Veb-freymvorklarda ORM (Django ORM, SQLAlchemy) ma'lumot bilan ishladi — lekin ular SQL (Structured Query Language) ustiga qurilgan. ORM SQL yozadi, va murakkab holatda SQL'ni o'zingiz yozasiz. SQL — ma'lumotlar bazasi bilan gaplashishning universal tili. Uni bilish — har backend dasturchining asosiy ko'nikmasi.

SQL — 1970-yillardan beri standart (PostgreSQL, MySQL, SQLite — hammasi SQL). U ma'lumotni jadval (satrlar va ustunlar) ko'rinishida saqlaydi va so'rovlar bilan boshqaradi: yaratish (CREATE), qo'shish (INSERT), o'qish (SELECT), yangilash (UPDATE), o'chirish (DELETE). Bu darsda SQL asoslarini sqlite3 (Python bilan keladigan baza) orqali o'rganamiz.

Real vaziyat. Bir dasturchi faqat ORM'ni bilardi — murakkab hisobot (bir necha jadval, guruhlash, agregat) kerak bo'lganda ORM'da qiynaldi (sekin, chalkash so'rov). U SQL'ni o'rgandi: bitta SELECT ... JOIN ... GROUP BY so'rovi bilan hisobotni tez yasadi. SQL'ni bilish ORM'dan chuqurroq nazorat beradi — murakkab holatda SQL to'g'ridan yoziladi.

Bu darsda SQL asoslarini — jadval, SELECT, WHERE, INSERT/UPDATE/DELETEni o'rganamiz.

Bu darsda:

Ma'lumotlar bazasi va jadval
CREATE TABLE (jadval yaratish)
INSERT (ma'lumot qo'shish)
SELECT va WHERE (o'qish, filtr)
ORDER BY, LIMIT (saralash, cheklash)
Agregatlar (COUNT, AVG, GROUP BY)
UPDATE, DELETE
Amaliy: to'liq SQL

ℹ Misollarda sqlite3 (Python stdlib) bilan sinaladi.

2. Nazariya — chuqur tushuntirish

2.1. Ma'lumotlar bazasi va jadval

Relyatsion baza — jadvallar to'plami:

kurslar jadvali:
┌────┬─────────┬──────┬─────┐
│ id │ nom     │ narx │ til │   ← ustunlar
├────┼─────────┼──────┼─────┤
│ 1  │ Python  │ 100  │ uz  │   ← satr (yozuv)
│ 2  │ Go      │ 120  │ en  │
└────┴─────────┴──────┴─────┘

Jadval — satrlar (yozuvlar) va ustunlar (maydonlar). Har satr — bir yozuv (kurs), har ustun — bir xususiyat (nom, narx). id — birlamchi kalit (primary key, har satrni noyob aniqlaydi). Bu 21.3 dagi Django modelining SQL asosi.

2.2. `CREATE TABLE`

CREATE TABLE — jadval yaratadi (sxema):

sql

CREATE TABLE kurslar (
    id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    narx INTEGER,
    til TEXT
);

Element	Ma'no
`id INTEGER PRIMARY KEY`	Birlamchi kalit
`nom TEXT NOT NULL`	Matn, bo'sh bo'lmasin
`narx INTEGER`	Butun son

CREATE TABLE — jadval nomi va ustunlar (nom + tur + cheklov). Turlar: INTEGER (son), TEXT (matn), REAL (o'nlik), BLOB (binar). PRIMARY KEY, NOT NULL — cheklovlar. Bu 21.3 dagi model maydonlarining SQL versiyasi.

2.3. `INSERT`

INSERT — jadvalga yangi satr qo'shadi:

sql

INSERT INTO kurslar (nom, narx, til) VALUES ('Python', 100, 'uz');

Python'da parametr bilan (16.7, xavfsizlik):

python

cur.execute("INSERT INTO kurslar (nom, narx, til) VALUES (?, ?, ?)", ("Python", 100, "uz"))
cur.executemany("INSERT INTO kurslar (nom, narx) VALUES (?, ?)", [("Go", 120), ("Rust", 150)])

INSERT INTO jadval (ustunlar) VALUES (qiymatlar) — yangi satr. Python'da ? parametr (SQL injection'dan himoya, 20.8). executemany — ko'p satr birdan. commit() bilan saqlanadi.

2.4. `SELECT` va `WHERE`

SELECT — ma'lumot o'qiydi; WHERE — filtr:

sql

SELECT * FROM kurslar;                        -- barcha ustun, satr
SELECT nom, narx FROM kurslar;                -- tanlangan ustun
SELECT * FROM kurslar WHERE narx >= 120;      -- filtr
SELECT * FROM kurslar WHERE til = 'uz' AND narx < 100;  -- VA

Element	Ma'no
`SELECT *`	Barcha ustun
`SELECT nom, narx`	Tanlangan ustun
`WHERE shart`	Filtr (satrlar)
`AND`/`OR`/`NOT`	Mantiq

SELECT ustunlar FROM jadval WHERE shart — o'qish so'rovi. WHERE — 21.9 dagi ORM filterning SQL asosi. AND/OR — murakkab shart. SELECT * — barchasi (ehtiyot, kerak ustunni tanlang).

2.5. `ORDER BY`, `LIMIT`

Saralash va cheklash:

sql

SELECT * FROM kurslar ORDER BY narx DESC;         -- narx kamayuvchi
SELECT * FROM kurslar ORDER BY til, narx;         -- til, keyin narx
SELECT * FROM kurslar ORDER BY narx DESC LIMIT 3; -- eng qimmat 3

ORDER BY ustun — saralash (ASC o'suvchi — standart, DESC kamayuvchi). LIMIT n — faqat n satr (sahifalash, 20.4). Bir necha ustun bo'yicha (ORDER BY til, narx). Bu 21.9 dagi order_byning SQL asosi.

2.6. Agregatlar va `GROUP BY`

Agregatlar — butun to'plam bo'yicha hisob:

sql

SELECT COUNT(*) FROM kurslar;                     -- soni
SELECT AVG(narx), MAX(narx), MIN(narx) FROM kurslar;  -- o'rtacha, max, min
SELECT SUM(narx) FROM kurslar;                    -- yig'indi

-- GROUP BY (guruhlash):
SELECT til, COUNT(*) FROM kurslar GROUP BY til;   -- har til uchun soni

Agregatlar: COUNT (soni), AVG (o'rtacha), SUM (yig'indi), MAX/MIN. GROUP BY ustun — satrlarni guruhlab, har guruhga agregat (21.9 dagi ORM annotate). Bu hisobotlar uchun (har til/kategoriya bo'yicha).

2.7. `UPDATE`, `DELETE`

O'zgartirish va o'chirish:

sql

UPDATE kurslar SET narx = narx + 10 WHERE til = 'uz';   -- o'zgartirish
DELETE FROM kurslar WHERE narx > 200;                    -- o'chirish

UPDATE jadval SET ustun = qiymat WHERE shart — satrlarni o'zgartiradi; DELETE FROM jadval WHERE shart — o'chiradi. WHERE muhim — usiz barcha satr o'zgaradi/o'chiladi (xavfli!). Bu 20.8 dagi UPDATE/DELETEning asosi.

2.8. SQL — ma'lumot tili

SQL turi	Buyruqlar
DDL (tuzilma)	`CREATE`, `ALTER`, `DROP`
DML (ma'lumot)	`INSERT`, `UPDATE`, `DELETE`, `SELECT`
Filtr/saralash	`WHERE`, `ORDER BY`, `LIMIT`
Agregat	`COUNT`, `GROUP BY`, `HAVING`

SQL — deklarativ til: "nima kerak" ni aytasiz (SELECT ... WHERE), "qanday olish" ni baza hal qiladi (20.4 deklarativ). SQL universal (PostgreSQL, MySQL, SQLite) — bir marta o'rganib, har bazada ishlatasiz. Bu ORM'ning asosi va murakkab holatda to'g'ridan ishlatiladi.

3. Tez ma'lumotnoma

sql

-- jadval:
CREATE TABLE kurslar (id INTEGER PRIMARY KEY, nom TEXT NOT NULL, narx INTEGER);

-- qo'shish:
INSERT INTO kurslar (nom, narx) VALUES ('Python', 100);

-- o'qish:
SELECT * FROM kurslar WHERE narx >= 100 ORDER BY narx DESC LIMIT 5;

-- agregat:
SELECT til, COUNT(*), AVG(narx) FROM kurslar GROUP BY til;

-- o'zgartirish:
UPDATE kurslar SET narx = 150 WHERE id = 1;
DELETE FROM kurslar WHERE narx > 200;

python

# Python'da (sqlite3):
cur.execute("SELECT * FROM kurslar WHERE narx > ?", (100,))
cur.executemany("INSERT INTO kurslar (nom) VALUES (?)", [(...),])
conn.commit()

4. Batafsil misollar

Misollarda sqlite3 (Python stdlib) bilan sinaladi.

Misol 1 — Jadval, `INSERT`, `SELECT`

python

"""CREATE TABLE; INSERT (parametr bilan); SELECT * va tanlangan ustun; row_factory."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()

    print("=== 1. Jadval yaratish (CREATE TABLE) ===")
    cur.execute("CREATE TABLE kurslar (id INTEGER PRIMARY KEY, nom TEXT NOT NULL, narx INTEGER, til TEXT)")
    print("  kurslar jadvali yaratildi")

    print("\n=== 2. Ma'lumot qo'shish (INSERT) ===")
    cur.executemany(
        "INSERT INTO kurslar (nom, narx, til) VALUES (?, ?, ?)",
        [("Python", 100, "uz"), ("Go", 120, "en"), ("Rust", 150, "en"), ("Java", 90, "uz")],
    )
    conn.commit()
    print(f"  {cur.rowcount} ta so'nggi so'rov, jami: {cur.execute('SELECT COUNT(*) c FROM kurslar').fetchone()['c']}")

    print("\n=== 3. Barcha satr (SELECT *) ===")
    for r in cur.execute("SELECT * FROM kurslar ORDER BY id"):
        print(f"    id={r['id']}, {r['nom']}, {r['narx']} so'm, {r['til']}")

    print("\n=== 4. Tanlangan ustunlar ===")
    natija = cur.execute("SELECT nom, narx FROM kurslar ORDER BY id").fetchall()
    print(f"  nomlar: {[r['nom'] for r in natija]}")
    print("  ⭐ CREATE (jadval), INSERT (qo'shish), SELECT (o'qish)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. Jadval yaratish (CREATE TABLE) ===
  kurslar jadvali yaratildi

=== 2. Ma'lumot qo'shish (INSERT) ===
  4 ta so'nggi so'rov, jami: 4

=== 3. Barcha satr (SELECT *) ===
    id=1, Python, 100 so'm, uz
    id=2, Go, 120 so'm, en
    id=3, Rust, 150 so'm, en
    id=4, Java, 90 so'm, uz

=== 4. Tanlangan ustunlar ===
  nomlar: ['Python', 'Go', 'Rust', 'Java']
  ⭐ CREATE (jadval), INSERT (qo'shish), SELECT (o'qish)

Nima ko'rsatdi: 2.1, 2.2, 2.3, 2.4-bo'limlar.

Misol 2 — `WHERE`, `ORDER BY`, `LIMIT`

python

"""WHERE (filtr, AND/OR); ORDER BY (saralash ASC/DESC); LIMIT (cheklash); murakkab shart."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute("CREATE TABLE kurslar (id INTEGER PRIMARY KEY, nom TEXT, narx INTEGER, til TEXT)")
    cur.executemany(
        "INSERT INTO kurslar (nom, narx, til) VALUES (?, ?, ?)",
        [("Python", 100, "uz"), ("JavaScript", 80, "uz"), ("Go", 120, "en"), ("Rust", 150, "en"), ("Java", 90, "uz")],
    )
    conn.commit()

    print("=== 1. WHERE (filtr) ===")
    natija = cur.execute("SELECT nom, narx FROM kurslar WHERE narx >= 120").fetchall()
    print(f"  narx>=120: {[(r['nom'], r['narx']) for r in natija]}")

    print("\n=== 2. WHERE AND (murakkab shart) ===")
    natija = cur.execute("SELECT nom FROM kurslar WHERE til = 'uz' AND narx < 100").fetchall()
    print(f"  uz va narx<100: {[r['nom'] for r in natija]}")

    print("\n=== 3. ORDER BY DESC ===")
    natija = cur.execute("SELECT nom, narx FROM kurslar ORDER BY narx DESC").fetchall()
    print(f"  narx kamayuvchi: {[(r['nom'], r['narx']) for r in natija]}")

    print("\n=== 4. ORDER BY + LIMIT (eng qimmat 2) ===")
    natija = cur.execute("SELECT nom FROM kurslar ORDER BY narx DESC LIMIT 2").fetchall()
    print(f"  eng qimmat 2: {[r['nom'] for r in natija]}")
    print("  ⭐ WHERE (filtr), ORDER BY (saralash), LIMIT (cheklash)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. WHERE (filtr) ===
  narx>=120: [('Go', 120), ('Rust', 150)]

=== 2. WHERE AND (murakkab shart) ===
  uz va narx<100: ['JavaScript', 'Java']

=== 3. ORDER BY DESC ===
  narx kamayuvchi: [('Rust', 150), ('Go', 120), ('Python', 100), ('Java', 90), ('JavaScript', 80)]

=== 4. ORDER BY + LIMIT (eng qimmat 2) ===
  eng qimmat 2: ['Rust', 'Go']
  ⭐ WHERE (filtr), ORDER BY (saralash), LIMIT (cheklash)

Nima ko'rsatdi: 2.4, 2.5-bo'limlar.

Misol 3 — Agregatlar va `GROUP BY`

python

"""agregatlar (COUNT, AVG, SUM, MAX, MIN); GROUP BY (guruhlash); HAVING (guruh filtri)."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute("CREATE TABLE kurslar (id INTEGER PRIMARY KEY, nom TEXT, narx INTEGER, til TEXT)")
    cur.executemany(
        "INSERT INTO kurslar (nom, narx, til) VALUES (?, ?, ?)",
        [("Python", 100, "uz"), ("JavaScript", 80, "uz"), ("Go", 120, "en"), ("Rust", 150, "en"), ("Java", 90, "uz")],
    )
    conn.commit()

    print("=== 1. Agregatlar (butun to'plam) ===")
    r = cur.execute("SELECT COUNT(*) c, AVG(narx) a, MAX(narx) mx, MIN(narx) mn FROM kurslar").fetchone()
    print(f"  soni: {r['c']}, o'rtacha: {r['a']}, max: {r['mx']}, min: {r['mn']}")

    print("\n=== 2. SUM (yig'indi) ===")
    r = cur.execute("SELECT SUM(narx) s FROM kurslar").fetchone()
    print(f"  jami narx: {r['s']}")

    print("\n=== 3. GROUP BY (til bo'yicha) ===")
    natija = cur.execute("SELECT til, COUNT(*) c, AVG(narx) a FROM kurslar GROUP BY til ORDER BY til").fetchall()
    for r in natija:
        print(f"    {r['til']}: {r['c']} kurs, o'rtacha {r['a']}")

    print("\n=== 4. HAVING (guruh filtri) ===")
    natija = cur.execute("SELECT til, COUNT(*) c FROM kurslar GROUP BY til HAVING COUNT(*) >= 3").fetchall()
    print(f"  3+ kursli til: {[(r['til'], r['c']) for r in natija]}")
    print("  ⭐ COUNT/AVG/SUM/MAX/MIN; GROUP BY (guruh); HAVING (guruh filtri)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. Agregatlar (butun to'plam) ===
  soni: 5, o'rtacha: 108.0, max: 150, min: 80

=== 2. SUM (yig'indi) ===
  jami narx: 540

=== 3. GROUP BY (til bo'yicha) ===
    en: 2 kurs, o'rtacha 135.0
    uz: 3 kurs, o'rtacha 90.0

=== 4. HAVING (guruh filtri) ===
  3+ kursli til: [('uz', 3)]
  ⭐ COUNT/AVG/SUM/MAX/MIN; GROUP BY (guruh); HAVING (guruh filtri)

Nima ko'rsatdi: 2.6-bo'lim.

Misol 4 — Amaliy: to'liq SQL (CRUD)

To'liq SQL amallarini — yaratish, o'qish, o'zgartirish, o'chirish — bir misolda ko'rsatami. Bu — SQL bilan ma'lumot boshqaruvining namunasi.

python

"""to'liq SQL: CREATE, INSERT, SELECT (filtr, agregat), UPDATE, DELETE; WHERE muhimligi."""

import warnings
warnings.filterwarnings("ignore")

import sqlite3


def main() -> None:
    conn = sqlite3.connect(":memory:")
    conn.row_factory = sqlite3.Row
    cur = conn.cursor()
    cur.execute("CREATE TABLE kurslar (id INTEGER PRIMARY KEY, nom TEXT, narx INTEGER, til TEXT)")
    cur.executemany(
        "INSERT INTO kurslar (nom, narx, til) VALUES (?, ?, ?)",
        [("Python", 100, "uz"), ("Go", 120, "en"), ("Rust", 150, "en"), ("Java", 90, "uz")],
    )
    conn.commit()

    print("=== 1. Boshlang'ich holat ===")
    print(f"  kurslar: {[r['nom'] for r in cur.execute('SELECT nom FROM kurslar ORDER BY id')]}")

    print("\n=== 2. UPDATE (uz kurslarga +10) ===")
    cur.execute("UPDATE kurslar SET narx = narx + 10 WHERE til = 'uz'")
    conn.commit()
    natija = cur.execute("SELECT nom, narx FROM kurslar WHERE til = 'uz' ORDER BY nom").fetchall()
    print(f"  yangilangan: {[(r['nom'], r['narx']) for r in natija]}")

    print("\n=== 3. DELETE (qimmat kurslar) ===")
    cur.execute("DELETE FROM kurslar WHERE narx >= 150")
    conn.commit()
    print(f"  qolgan: {[r['nom'] for r in cur.execute('SELECT nom FROM kurslar ORDER BY id')]}")

    print("\n=== 4. Yakuniy hisobot ===")
    r = cur.execute("SELECT COUNT(*) c, AVG(narx) a FROM kurslar").fetchone()
    print(f"  jami: {r['c']} kurs, o'rtacha narx: {r['a']}")
    print("  ⭐ CREATE→INSERT→SELECT→UPDATE→DELETE (WHERE bilan xavfsiz)")

    conn.close()


if __name__ == "__main__":
    main()

Natijaning muhim qismi:

text

=== 1. Boshlang'ich holat ===
  kurslar: ['Python', 'Go', 'Rust', 'Java']

=== 2. UPDATE (uz kurslarga +10) ===
  yangilangan: [('Java', 100), ('Python', 110)]

=== 3. DELETE (qimmat kurslar) ===
  qolgan: ['Python', 'Go', 'Java']

=== 4. Yakuniy hisobot ===
  jami: 3 kurs, o'rtacha narx: 110.0
  ⭐ CREATE→INSERT→SELECT→UPDATE→DELETE (WHERE bilan xavfsiz)

Nima ko'rsatdi: 2.1–2.8-bo'limlar.

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

Noto'g'ri fikr	To'g'risi
"SQL — faqat ORM ostida"	Mustaqil til (to'g'ridan)
"`SELECT *` doim yaxshi"	Kerak ustunni tanlang
"`WHERE`siz `DELETE`"	Barcha satr o'chiladi (xavfli)
"SQL har bazada boshqacha"	Asoslar universal
"Agregatni Python'da"	SQL'da (baza hisoblaydi)
"Parametrsiz SQL"	`?` (injection himoya)
"`GROUP BY`siz agregat guruh"	GROUP BY kerak
"`commit`siz saqlanadi"	`commit()` kerak

6. Keng tarqalgan xatolar va yechimlari

1. WHEREsiz UPDATE/DELETE

sql

DELETE FROM kurslar;                          -- ⚠️ HAMMASI o'chadi
DELETE FROM kurslar WHERE narx > 200;         -- ✅

2. Parametrsiz SQL (injection)

python

cur.execute(f"SELECT * WHERE nom = '{nom}'")   -- ⚠️ injection
cur.execute("SELECT * WHERE nom = ?", (nom,))  -- ✅

3. SELECT * (kerak ustun)

sql

SELECT * FROM kurslar;                        -- ⚠️ barcha ustun
SELECT nom, narx FROM kurslar;                -- ✅ kerakli

4. commitni unutish

python

cur.execute("INSERT ...")                     -- ⚠️ saqlanmaydi
cur.execute("INSERT ..."); conn.commit()      -- ✅

5. Agregatni Python'da

python

sum(r["narx"] for r in cur.execute("SELECT narx..."))   -- ⚠️
cur.execute("SELECT SUM(narx) FROM ...")      -- ✅ baza

6. GROUP BYsiz guruh agregat

sql

SELECT til, COUNT(*) FROM kurslar;            -- ⚠️ noaniq
SELECT til, COUNT(*) FROM kurslar GROUP BY til;  -- ✅

7. HAVING o'rniga WHERE (agregat filtr)

sql

SELECT til, COUNT(*) FROM ... WHERE COUNT(*) > 2 GROUP BY til   -- ❌
SELECT til, COUNT(*) FROM ... GROUP BY til HAVING COUNT(*) > 2  -- ✅

7. Integratsiya — bu bilim qayerda kerak bo'ladi

20.8-dars (o'tilgan): sqlite3 + FastAPI
21.9-dars (o'tilgan): Django ORM — SQL asosi
23.2-dars: JOIN — bir necha jadval
23.6/23.7-dars: SQLAlchemy — SQL Python'da
16.7-dars (o'tilgan): sqlite3 — parametrlar

8. Eng yaxshi amaliyotlar

SQL asoslarini biling (ORM ostida ham SQL).
Kerak ustunni tanlang (SELECT * emas).
UPDATE/DELETEda WHERE bering (majburiy).
Parametr (?) ishlating (injection himoya).
Agregat/guruhlashni SQL'da (baza hisoblaydi).
GROUP BY bilan guruh, HAVING bilan guruh filtri.
commit() bilan saqlang.
Murakkab holatda SQL to'g'ridan yozing (ORM emas).

9. Amaliy topshiriq

Vazifa 1: Bashorat qiling

sql

1.  -- jadval nima?
2.  -- PRIMARY KEY nima?
3.  -- INSERT nima qiladi?
4.  -- SELECT * nima?
5.  -- WHERE nima?
6.  -- ORDER BY DESC nima?
7.  -- LIMIT nima?
8.  -- COUNT(*) nima?
9.  -- GROUP BY nima?
10. -- HAVING nima?
11. -- WHERE'siz DELETE?
12. -- ? parametr nega?

Javoblar

Satrlar va ustunlar (yozuvlar)
Birlamchi kalit (noyob aniqlaydi)
Yangi satr qo'shadi
Barcha ustun
Satrlarni filtrlaydi
Kamayuvchi saralash
Faqat n satr
Satrlar soni
Satrlarni guruhlaydi
Guruh filtri (agregat)
Barcha satr o'chiladi (xavfli)
SQL injection'dan himoya

Vazifa 2: Xatolarni tuzating

sql

1.  DELETE FROM kurslar;                    -- WHERE

2.  cur.execute(f"WHERE nom='{nom}'")        -- parametr

3.  SELECT * FROM kurslar;                   -- ustun

4.  cur.execute("INSERT ...")                -- commit

5.  SELECT til, COUNT(*) FROM kurslar;       -- GROUP BY

Javoblar

sql

1.  DELETE FROM kurslar WHERE narx > 200;

2.  cur.execute("WHERE nom = ?", (nom,))

3.  SELECT nom, narx FROM kurslar;

4.  cur.execute("INSERT ..."); conn.commit()

5.  SELECT til, COUNT(*) FROM kurslar GROUP BY til;

Vazifa 3: Mahsulot jadvali

To'liq CRUD:

CREATE TABLE mahsulotlar (id, nom, narx, kategoriya)
INSERT (bir necha)
SELECT, WHERE, ORDER BY
UPDATE, DELETE

Vazifa 4: Filtr va saralash

Turli so'rovlar:

WHERE (bir necha shart)
AND/OR/NOT
ORDER BY (bir necha ustun)
LIMIT (sahifalash)

Vazifa 5: Hisobotlar

Agregatlar:

COUNT, AVG, SUM, MAX, MIN
GROUP BY (kategoriya bo'yicha)
HAVING
Hisobot yasash

Vazifa 6: Kutubxona bazasi

Kutubxona tizimi:

kitoblar jadvali
To'liq CRUD
Qidiruv (LIKE)
Hisobotlar

Vazifa 7: O'ylash

SQL — deklarativ til: siz "nima kerak" ni aytasiz (SELECT nom FROM kurslar WHERE narx > 100), baza "qanday olish" ni (indeks, tartib, optimizatsiya) o'zi hal qiladi. Bu 20.4 dagi FastAPI validatsiyasi, 21.9 dagi ORM kabi deklarativ. "Nima vs qanday" ajratishning afzalligi nima, va nima uchun deklarativ SQL imperativ (qo'lda sikl bilan qidiruv) dan ustun?

Javob

Qisqa javob: SQL deklarativ — siz natijani (nima kerak) tasvirlaysiz, baza jarayonni (qanday olish — qaysi indeks, qaysi tartib) o'zi tanlaydi. Imperativ yondashuv (Python'da barcha satrni olib, sikl bilan qidiruv) — siz har qadamni yozasiz. Deklarativ ustun, chunki: optimizatsiya bazaga qoladi (baza indeks, statistika bilan eng tez yo'lni tanlaydi — siz bilmagan), qisqa (bitta so'rov vs ko'p qator kod), o'qiladigan (niyat aniq). "Nima vs qanday" ajratish — muhandislik kuchi: mexanizmni (qanday) tizimga topshirib, niyatga (nima) e'tibor.

1. Deklarativ vs imperativ

Deklarativ (SQL)	Imperativ (sikl)
Natijani tasvirla	Har qadamni yoz
`SELECT ... WHERE`	`for` + `if`
Baza optimallashtiradi	Siz optimallashtirasiz
Qisqa	Uzun

2. Nega deklarativ ustun

Afzallik	Sabab
Optimizatsiya	Baza eng tez yo'lni tanlaydi
Qisqa	Bitta so'rov
O'qiladigan	Niyat aniq
Mustaqil	Baza "qanday"ni hal qiladi

3. Baza optimizatsiyasi

Baza indeks, statistika, so'rov rejasi bilan eng tez yo'lni tanlaydi 23.3-bob. Python'da qo'lda bu qiyin — baza mutaxassis.

4. "Nima vs qanday" boshqa joyda

Deklarativ	Imperativ
SQL	Sikl bilan qidiruv
Regex	Tahlil kodi
ORM	Qo'lda SQL
HTML/CSS	Chizish kodi

5. Muhandislik saboqlari

Deklarativ — natijani tasvirla (jarayon emas)
Optimizatsiya tizimga qoladi (baza)
Qisqa, o'qiladigan, mustaqil
"Nima vs qanday" — mexanizmni yashir

6. Xulosa

SQL deklarativ — nima kerak
Baza qanday olishni hal qiladi (optimizatsiya)
Deklarativ — qisqa, tez, o'qiladigan
"Nima vs qanday" — muhandislik tamoyili

Nimani mustahkamlaydi: 2.1–2.8-bo'limlar.

Xulosa

Bu darsda SQL asoslarini o'rgandik.

Eng muhim uch fikr:

Jadval va DDL. Relyatsion baza — jadvallar (satrlar va ustunlar): har satr — yozuv (kurs), har ustun — xususiyat (nom, narx), id — birlamchi kalit (noyob aniqlaydi). CREATE TABLE jadval sxemasini yaratadi (ustun nomi + tur: INTEGER, TEXT, REAL + cheklov: PRIMARY KEY, NOT NULL). Bu 21.3 dagi Django modelining SQL asosi.
CRUD va filtr. INSERT INTO jadval (ustunlar) VALUES (qiymatlar) — qo'shish (Python'da ? parametr, injection himoya); SELECT ustunlar FROM jadval WHERE shart — o'qish (filtr, AND/OR); ORDER BY (saralash ASC/DESC), LIMIT (cheklash); UPDATE ... SET ... WHERE va DELETE ... WHERE — o'zgartirish/o'chirish (WHERE majburiy — usiz barcha satr!). Bu 21.9 ORM va 20.8 dagi so'rovlarning SQL asosi.
Agregatlar va deklarativlik. Agregatlar (COUNT, AVG, SUM, MAX, MIN) — butun to'plam bo'yicha hisob (baza hisoblaydi, Python emas); GROUP BY ustun — satrlarni guruhlab, har guruhga agregat; HAVING — guruh filtri. SQL — deklarativ til: "nima kerak" ni aytasiz, baza "qanday olish" ni (indeks, optimizatsiya) hal qiladi. SQL universal (PostgreSQL, MySQL, SQLite) — ORM'ning asosi va murakkab holatda to'g'ridan yoziladi.

Keyingi darsda JOIN va bog'lanishlar ni o'rganamiz: bir necha jadvalni birlashtirib so'rov — INNER JOIN, LEFT JOIN va tashqi kalitlar orqali bog'langan ma'lumot.

Ulashish:Telegram'da

Izohlar (0)

Izoh yozish uchun kiring.

Hozircha izoh yo'q. Birinchi bo'ling!

23.1-dars: SQL asoslari

1. Kirish va motivatsiya

2. Nazariya — chuqur tushuntirish

2.1. Ma'lumotlar bazasi va jadval

2.2. CREATE TABLE

2.3. INSERT

2.4. SELECT va WHERE

2.5. ORDER BY, LIMIT

2.6. Agregatlar va GROUP BY

2.7. UPDATE, DELETE

2.8. SQL — ma'lumot tili

3. Tez ma'lumotnoma

4. Batafsil misollar

Misol 1 — Jadval, INSERT, SELECT

Misol 2 — WHERE, ORDER BY, LIMIT

Misol 3 — Agregatlar va GROUP BY

Misol 4 — Amaliy: to'liq SQL (CRUD)

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

6. Keng tarqalgan xatolar va yechimlari

7. Integratsiya — bu bilim qayerda kerak bo'ladi

8. Eng yaxshi amaliyotlar

9. Amaliy topshiriq

Xulosa

Izohlar (0)

2.2. `CREATE TABLE`

2.3. `INSERT`

2.4. `SELECT` va `WHERE`

2.5. `ORDER BY`, `LIMIT`

2.6. Agregatlar va `GROUP BY`

2.7. `UPDATE`, `DELETE`

Misol 1 — Jadval, `INSERT`, `SELECT`

Misol 2 — `WHERE`, `ORDER BY`, `LIMIT`

Misol 3 — Agregatlar va `GROUP BY`