CREATE TABLE talabalar (
id SERIAL PRIMARY KEY,
ism VARCHAR(50) NOT NULL,
familiya VARCHAR(50) NOT NULL,
yosh INTEGER CHECK (yosh > 0),
email VARCHAR(100) UNIQUE,
ro'yxat_sanasi DATE DEFAULT CURRENT_DATE
);
Cheklovlar (Constraints):
PRIMARY KEY - asosiy kalit
NOT NULL - bo'sh bo'lmasligi kerak
UNIQUE - noyob qiymat
CHECK - shart tekshirish
DEFAULT - standart qiymat
Ma'lumot Qo'shish (INSERT)
Asosiy sintaksis:
INSERT INTO jadval_nomi (ustun1, ustun2, ...)
VALUES (qiymat1, qiymat2, ...);
Bitta yozuv qo'shish:
INSERT INTO talabalar (ism, familiya, yosh, email)
VALUES ('Ali', 'Valiyev', 20, '[email protected]');
UPDATE talabalar
SET ism = 'Ali Akbar',
yosh = 21
WHERE id = 1;
Shartli yangilash:
UPDATE talabalar
SET yosh = yosh + 1
WHERE ro'yxat_sanasi < '2024-01-01';
⚠️ Diqqat: WHERE sharti bo'lmasa, barcha yozuvlar yangilanadi!
Ma'lumotlarni O'chirish (DELETE)
Asosiy sintaksis:
DELETE FROM jadval_nomi
WHERE shart;
Aniq yozuvni o'chirish:
DELETE FROM talabalar
WHERE id = 5;
Shartli o'chirish:
-- 18 yoshdan kichiklar
DELETE FROM talabalar
WHERE yosh < 18;
-- Email bo'sh bo'lganlar
DELETE FROM talabalar
WHERE email IS NULL;
Barcha ma'lumotlarni o'chirish:
-- Barcha yozuvlar (jadval qoladi)
DELETE FROM talabalar;
-- Tezroq variant
TRUNCATE TABLE talabalar;
⚠️ Diqqat: WHERE sharti bo'lmasa, barcha yozuvlar o'chiriladi!
Filtrlash va Izlash
WHERE operatorlari:
-- Tenglik
SELECT * FROM talabalar WHERE yosh = 20;
-- Tengsizlik
SELECT * FROM talabalar WHERE yosh != 20;
SELECT * FROM talabalar WHERE yosh <> 20;
-- Taqqoslash
SELECT * FROM talabalar WHERE yosh > 18;
SELECT * FROM talabalar WHERE yosh <= 25;
LIKE operatori (matn izlash):
-- 'A' harfi bilan boshlanadigan
SELECT * FROM talabalar WHERE ism LIKE 'A%';
-- 'ov' bilan tugaydigan
SELECT * FROM talabalar WHERE familiya LIKE '%ov';
-- O'rtasida 'li' bo'lgan
SELECT * FROM talabalar WHERE ism LIKE '%li%';
IN operatori:
SELECT * FROM talabalar
WHERE yosh IN (19, 20, 21);
BETWEEN operatori:
SELECT * FROM talabalar
WHERE yosh BETWEEN 18 AND 25;
Mantiqiy Operatorlar
AND operatori:
SELECT * FROM talabalar
WHERE yosh > 18 AND yosh < 25;
OR operatori:
SELECT * FROM talabalar
WHERE ism = 'Ali' OR familiya = 'Karimov';
NOT operatori:
SELECT * FROM talabalar
WHERE NOT yosh = 20;
-- Yoki
SELECT * FROM talabalar
WHERE yosh NOT IN (18, 19, 20);
Murakkab shartlar:
SELECT * FROM talabalar
WHERE (yosh > 18 AND yosh < 25)
AND (ism LIKE 'A%' OR familiya LIKE '%ov');
NULL qiymatlar:
-- Email bo'sh bo'lgan
SELECT * FROM talabalar WHERE email IS NULL;
-- Email bo'sh bo'lmagan
SELECT * FROM talabalar WHERE email IS NOT NULL;
Amaliy Misol - Universitet Tizimi
-- 1. Kafedra jadvali
CREATE TABLE kafedralar (
id SERIAL PRIMARY KEY,
nomi VARCHAR(100) NOT NULL,
mudiri VARCHAR(100)
);
-- 2. Ma'lumot qo'shish
INSERT INTO kafedralar (nomi, mudiri)
VALUES
('Informatika', 'Prof. Karimov'),
('Matematika', 'Prof. Umarova'),
('Fizika', 'Prof. Toshev');
-- 3. Talabalar jadvali (kafedra bilan bog'langan)
CREATE TABLE talabalar (
id SERIAL PRIMARY KEY,
ism VARCHAR(50) NOT NULL,
familiya VARCHAR(50) NOT NULL,
yosh INTEGER CHECK (yosh > 0),
kafedra_id INTEGER REFERENCES kafedralar(id),
stipendiya DECIMAL(10,2) DEFAULT 0,
ro'yxat_sanasi DATE DEFAULT CURRENT_DATE
);
-- 4. Talabalar ma'lumotini qo'shish
INSERT INTO talabalar (ism, familiya, yosh, kafedra_id, stipendiya)
VALUES
('Ali', 'Valiyev', 20, 1, 500000),
('Dilnoza', 'Karimova', 19, 2, 450000),
('Sardor', 'Umarov', 21, 1, 550000);
-- 5. Ma'lumotlarni o'qish
SELECT t.ism, t.familiya, k.nomi as kafedra
FROM talabalar t
JOIN kafedralar k ON t.kafedra_id = k.id;
-- 6. Statistika
SELECT k.nomi, COUNT(t.id) as talabalar_soni
FROM kafedralar k
LEFT JOIN talabalar t ON k.id = t.kafedra_id
GROUP BY k.id, k.nomi;