🗄️ PostgreSQL Create Database & Table

🎯 Darsning Maqsadi

PostgreSQL'da ma'lumotlar bazasi va jadval yaratish, Python bilan ulanish va amaliy mashqlar.

📋 Dars Rejasi

Database yaratish
Table yaratish
Data types va constraints
Python bilan ulanish
Amaliy loyiha

🗄️ Ma'lumotlar Bazasi Yaratish

📖 Database Yaratish Qadamlari

💻 Terminal orqali

psql'ga ulaning
CREATE DATABASE buyrug'ini ishlating
Bazaga ulaning
Jadvallar yarating

-- PostgreSQL'ga ulanish
psql -U postgres

-- Ma'lumotlar bazasi yaratish
CREATE DATABASE my_blog;

-- Bazaga ulanish
\c my_blog;

-- Mavjud bazalarni ko'rish
\l

-- Bazani o'chirish (ehtiyot bo'ling!)
DROP DATABASE my_blog;
            

🔧 PgAdmin orqali

PgAdmin'ni oching
Servers → PostgreSQL → Databases
O'ng bosish → "Create" → "Database..."
Database nomini kiriting
"Save" tugmasini bosing

📊 Jadval Yaratish

📝 Asosiy Jadval Yaratish

-- Users jadvali yaratish
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    age INTEGER CHECK (age >= 0 AND age <= 150),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
            

🔗 Bog'lanishli Jadval

-- Posts jadvali (users bilan bog'langan)
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    content TEXT,
    author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Comments jadvali
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
            

📋 Ma'lumot Turlari

🔢 Raqamli Turlar

INTEGER - Butun sonlar
BIGINT - Katta butun sonlar
DECIMAL - O'nlik sonlar
FLOAT - Suzuvchi nuqta
SERIAL - Avtomatik o'suvchi

📝 Matn Turlari

VARCHAR(n) - O'zgaruvchan uzunlik
CHAR(n) - Belgili uzunlik
TEXT - Uzun matn
JSON - JSON ma'lumotlar
UUID - Universaldan identifikator

📅 Vaqt Turlari

DATE - Sana
TIME - Vaqt
TIMESTAMP - Sana va vaqt
INTERVAL - Vaqt oralig'i

🔘 Boshqa Turlar

BOOLEAN - True/False
ARRAY - Massiv
BYTEA - Binary ma'lumot
INET - IP manzil

🔒 Cheklovlar (Constraints)

🛡️ Asosiy Cheklovlar

-- Cheklovlar bilan jadval yaratish
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) CHECK (price > 0),
    category_id INTEGER REFERENCES categories(id),
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    sku VARCHAR(50) UNIQUE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Cheklov qo'shish
ALTER TABLE products 
ADD CONSTRAINT check_price_positive 
CHECK (price > 0);

-- Cheklov o'chirish
ALTER TABLE products 
DROP CONSTRAINT check_price_positive;
            

✅ Cheklov Turlari

PRIMARY KEY - Asosiy kalit
FOREIGN KEY - Tashqi kalit
UNIQUE - Takrorlanmas
NOT NULL - Bo'sh bo'lmasin
CHECK - Shart tekshirish

🎯 Maqsad

Ma'lumotlar to'g'riligini ta'minlash
Integritetni saqlash
Xatoliklarni oldini olish
Ma'lumotlar ishonchliligini oshirish

🐍 Python bilan Ulanish

📦 Kerakli Kutubxonalar

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

🔌 Ulanish va Jadval Yaratish

import psycopg2 from psycopg2 import Error def create_database_and_tables(): try: # PostgreSQL'ga ulanish connection = psycopg2.connect( host="localhost", database="postgres", # default database user="postgres", password="your_password" ) connection.autocommit = True cursor = connection.cursor() # Ma'lumotlar bazasi yaratish cursor.execute("CREATE DATABASE my_blog;") print("Ma'lumotlar bazasi yaratildi!") # Yangi bazaga ulanish connection.close() connection = psycopg2.connect( host="localhost", database="my_blog", user="postgres", password="your_password" ) cursor = connection.cursor() # Users jadvali yaratish cursor.execute(""" CREATE TABLE users ( id SERIAL PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); """) print("Users jadvali yaratildi!") except Error as e: print(f"Xatolik: {e}") finally: if connection: cursor.close() connection.close() print("Ulanish yopildi") # Funksiyani chaqirish create_database_and_tables()

💼 Amaliy Misol: Blog Tizimi

📝 To'liq Blog Tizimi

-- Blog tizimi uchun to'liq schema
CREATE DATABASE blog_system;

\c blog_system;

-- Users jadvali
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    bio TEXT,
    avatar_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Categories jadvali
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Posts jadvali
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    author_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    featured_image VARCHAR(255),
    view_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Comments jadvali
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
    parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    is_approved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
            

🔍 Foydali So'rovlar

Eng ko'p ko'rilgan postlar
Muallif bo'yicha postlar
Kategoriya bo'yicha postlar
Eng ko'p kommentariyalar

📝 Xulosa

🎯 O'rganilgan Mavzular

Ma'lumotlar bazasi yaratish
Jadval yaratish va sozlash
Ma'lumot turlari va cheklovlar
Python bilan ulanish
Amaliy blog tizimi loyihasi

✅ Keyingi Qadamlar

CRUD operatsiyalari
Murakkab so'rovlar
Indexlar va optimizatsiya
Backup va restore

💡 Maslahatlar

Nomlanish qoidalariga rioya qiling
Cheklovlardan to'g'ri foydalaning
Indexlarni optimizatsiya qiling
Muntazam backup oling

🏆 Amaliy Mashq

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