🏗️ CREATE TABLE - Jadval yaratish

CREATE TABLE sintaksisi

CREATE TABLE table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    column3 datatype constraints,
    ...
);

Asosiy ma'lumot turlari

1. TEXT - Matn ma'lumotlari

CREATE TABLE users (
    name TEXT,
    email TEXT,
    description TEXT
);

2. INTEGER - Butun sonlar

CREATE TABLE products (
    id INTEGER,
    price INTEGER,
    quantity INTEGER
);

3. REAL - O'nlik sonlar

CREATE TABLE sales (
    amount REAL,
    tax_rate REAL,
    discount REAL
);

4. BLOB - Binary ma'lumotlar

CREATE TABLE files (
    id INTEGER,
    file_data BLOB,
    file_name TEXT
);

5. NULL - Bo'sh qiymat

CREATE TABLE contacts (
    phone TEXT,
    address TEXT  -- NULL bo'lishi mumkin
);

Constraint-lar (Cheklovlar)

1. PRIMARY KEY - Asosiy kalit

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

-- Yoki AUTOINCREMENT bilan
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT
);

2. NOT NULL - Bo'sh bo'lmasligi

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

3. UNIQUE - Takrorlanmasligi

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    username TEXT UNIQUE
);

4. DEFAULT - Standart qiymat

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL DEFAULT 0.0,
    is_active BOOLEAN DEFAULT 1,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

5. CHECK - Shart tekshirish

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER CHECK (age >= 0 AND age <= 120),
    grade TEXT CHECK (grade IN ('A', 'B', 'C', 'D', 'F'))
);

Amaliy misollar

1. Oddiy jadval

CREATE TABLE books (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    author TEXT NOT NULL,
    pages INTEGER,
    price REAL
);

2. Murakkab jadval

CREATE TABLE employees (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    phone TEXT,
    salary REAL CHECK (salary > 0),
    department TEXT DEFAULT 'General',
    hire_date DATE DEFAULT CURRENT_DATE,
    is_active BOOLEAN DEFAULT 1
);

3. Bog'langan jadvallar

-- Kategoriyalar jadvali
CREATE TABLE categories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT
);

-- Mahsulotlar jadvali
CREATE TABLE products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT,
    price REAL NOT NULL CHECK (price > 0),
    category_id INTEGER,
    stock_quantity INTEGER DEFAULT 0 CHECK (stock_quantity >= 0),
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

Jadvalni o'zgartirish

Ustun qo'shish

-- Yangi ustun qo'shish
ALTER TABLE users ADD COLUMN phone TEXT;

-- Ustun qo'shish DEFAULT qiymat bilan
ALTER TABLE users ADD COLUMN status TEXT DEFAULT 'active';

Jadval nomini o'zgartirish

-- Jadval nomini o'zgartirish
ALTER TABLE users RENAME TO customers;

Ustun nomini o'zgartirish

-- Ustun nomini o'zgartirish
ALTER TABLE users RENAME COLUMN email TO email_address;

Jadvalni o'chirish

-- Jadvalni o'chirish
DROP TABLE users;

-- Agar mavjud bo'lsa o'chirish
DROP TABLE IF EXISTS users;

Jadval haqida ma'lumot

Jadval strukturasi

-- Jadval strukturasi
.schema users

-- Barcha jadvallar strukturasi
.schema

Jadvallar ro'yxati

-- Barcha jadvallar
.tables

-- Belgili pattern bo'yicha
.tables user*
.tables *log*

Eng yaxshi amaliyotlar

Xatolar va ularni hal qilish

Keng tarqalgan xatolar

-- Xato: Jadval allaqachon mavjud
CREATE TABLE users (id INTEGER);
-- Xato: table users already exists

-- To'g'ri: IF NOT EXISTS ishlatish
CREATE TABLE IF NOT EXISTS users (id INTEGER);

-- Xato: Noto'g'ri ma'lumot turi
CREATE TABLE test (id TEXT PRIMARY KEY);
-- Bu ishlaydi, lekin INTEGER yaxshiroq

-- Xato: Constraint xatosi
CREATE TABLE test (age INTEGER CHECK (age > 0));
INSERT INTO test VALUES (-5);  -- Xato: CHECK constraint failed
Keyingi dars: INSERT INTO - Ma'lumot qo'shish →