🐘 PostgreSQL Kirish

🎯 Darsning Maqsadi

PostgreSQL ma'lumotlar bazasi tizimi bilan tanishish va asosiy operatsiyalarni o'rganish.

📋 Dars Rejasi

PostgreSQL nima?
O'rnatish va sozlash
Asosiy SQL so'rovlar
Python bilan ulanish
Amaliy mashqlar

🐘 PostgreSQL Nima?

📖 Ta'rif

PostgreSQL - bu ochiq kodli, kuchli va ishonchli relational ma'lumotlar bazasi tizimi.

✅ PostgreSQL Afzalliklari

Ochiq kodli - Bepul va ochiq
ACID qoidalari - Ma'lumotlar ishonchliligi
Kengaytirilishi - Ko'plab funksiyalar
Standart SQL - SQL standartlariga mos

🎯 Xususiyatlari

Relational - Jadvalar o'rtasida bog'lanish
Scalable - Katta loyihalar uchun
Cross-platform - Barcha OS da ishlaydi
JSON qo'llab-quvvatlash - NoSQL xususiyatlari

⚙️ PostgreSQL O'rnatish

💻 O'rnatish Qadamlari

PostgreSQL'ni yuklab oling
O'rnatish jarayonini boshlang
Parol o'rnating
Port raqamini tanlang (default: 5432)

# Windows uchun # https://www.postgresql.org/download/windows/ # macOS uchun brew install postgresql # Ubuntu/Debian uchun sudo apt-get install postgresql postgresql-contrib

🔧 Dasturlash Muhiti

pgAdmin - Grafik interfeys
psql - Terminal orqali
DBeaver - Universal ma'lumotlar bazasi klienti

📝 Asosiy SQL So'rovlar

🗄️ Ma'lumotlar Bazasi Yaratish

-- Ma'lumotlar bazasi yaratish
CREATE DATABASE my_database;

-- Ma'lumotlar bazasiga ulanish
\c my_database;

-- Jadval yaratish
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
            

📊 Ma'lumotlar bilan Ishlash

-- Ma'lumot qo'shish INSERT INTO users (name, email, age) VALUES ('John Doe', '[email protected]', 25); -- Ma'lumotlarni ko'rish SELECT * FROM users; -- Ma'lumot yangilash UPDATE users SET age = 26 WHERE name = 'John Doe'; -- Ma'lumot o'chirish DELETE FROM users WHERE age < 18;

🔍 Murakkab So'rovlar

📊 JOIN Operatsiyalari

-- Ikki jadvalni birlashtirish
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    product_name VARCHAR(100),
    price DECIMAL(10,2)
);

-- INNER JOIN
SELECT u.name, o.product_name, o.price
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;
            

📈 Agregat Funksiyalar

-- Statistikalar
SELECT 
    COUNT(*) as total_users,
    AVG(age) as average_age,
    MAX(age) as oldest_user,
    MIN(age) as youngest_user
FROM users;

-- Guruhlash
SELECT age, COUNT(*) as count
FROM users
GROUP BY age
HAVING COUNT(*) > 1;
            

🐍 Python bilan Ulanish

📦 Kerakli Kutubxonalar

# psycopg2 kutubxonasini o'rnatish pip install psycopg2-binary # Yoki pip install psycopg2

🔌 Ulanish Kodi

import psycopg2 from psycopg2 import Error try: # Ma'lumotlar bazasiga ulanish connection = psycopg2.connect( host="localhost", database="my_database", user="postgres", password="your_password" ) # Cursor yaratish cursor = connection.cursor() # So'rov bajarish cursor.execute("SELECT * FROM users;") records = cursor.fetchall() print("Ma'lumotlar:") for row in records: print(f"ID: {row[0]}, Ism: {row[1]}, Email: {row[2]}") except Error as e: print(f"Xatolik: {e}") finally: if connection: cursor.close() connection.close() print("Ulanish yopildi")

💼 Amaliy Misol: Blog Tizimi

📝 Loyiha: Blog Ma'lumotlar Bazasi

-- Blog tizimi uchun jadvallar
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INTEGER REFERENCES users(id),
    category_id INTEGER REFERENCES categories(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id),
    user_id INTEGER REFERENCES users(id),
    comment TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
            

🔍 Blog So'rovlari

-- Eng ko'p kommentariyalar bilan postlar
SELECT p.title, COUNT(c.id) as comment_count
FROM posts p
LEFT JOIN comments c ON p.id = c.post_id
GROUP BY p.id, p.title
ORDER BY comment_count DESC;

-- Muallif bo'yicha postlar soni
SELECT u.name, COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.author_id
GROUP BY u.id, u.name;
            

📝 Xulosa

🎯 O'rganilgan Mavzular

PostgreSQL o'rnatish va sozlash
Asosiy SQL so'rovlar (CREATE, INSERT, SELECT, UPDATE, DELETE)
Murakkab so'rovlar (JOIN, GROUP BY, HAVING)
Python bilan ulanish
Amaliy loyiha: Blog tizimi

✅ Keyingi Qadamlar

MongoDB o'rganish
ORM (SQLAlchemy)
Database optimization
Backup va restore

💡 Maslahatlar

Muntazam backup oling
Indexlardan foydalaning
So'rovlarni optimizatsiya qiling
Xavfsizlikni unutmang

🏆 Amaliy Mashq

O'zingizning blog tizimingizni yarating va unda ma'lumotlar bilan ishlang!