SQL: إنشاء وإدارة قواعد البيانات

CREATE DATABASE database_name;

CREATE DATABASE my_shop;

CREATE DATABASE IF NOT EXISTS my_shop;

CREATE DATABASE my_shop
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE my_shop;

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| my_shop            |
| mysql              |
| performance_schema |
+--------------------+

SELECT DATABASE();

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

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    category VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL
);

SHOW TABLES;

+-------------------+
| Tables_in_my_shop |
+-------------------+
| orders            |
| products          |
| users             |
+-------------------+

DESCRIBE users;
-- أو
DESC users;
-- أو
SHOW COLUMNS FROM users;

+------------+--------------+------+-----+-------------------+
| Field      | Type         | Null | Key | Default           |
+------------+--------------+------+-----+-------------------+
| id         | int(11)      | NO   | PRI | NULL              |
| username   | varchar(50)  | NO   | UNI | NULL              |
| email      | varchar(100) | NO   | UNI | NULL              |
| password   | varchar(255) | NO   |     | NULL              |
| created_at | timestamp    | NO   |     | CURRENT_TIMESTAMP |
+------------+--------------+------+-----+-------------------+

SHOW CREATE TABLE users;

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

ALTER TABLE users
ADD COLUMN address TEXT,
ADD COLUMN city VARCHAR(50),
ADD COLUMN country VARCHAR(50);

-- إضافة بعد عمود محدد
ALTER TABLE users
ADD COLUMN middle_name VARCHAR(50) AFTER username;

-- إضافة كأول عمود
ALTER TABLE users
ADD COLUMN user_id INT FIRST;

-- تغيير نوع بيانات العمود
ALTER TABLE users
MODIFY COLUMN phone VARCHAR(30);

-- تغيير اسم ونوع العمود
ALTER TABLE users
CHANGE COLUMN phone phone_number VARCHAR(30);

ALTER TABLE users
DROP COLUMN middle_name;

-- إضافة قيد UNIQUE
ALTER TABLE users
ADD UNIQUE (email);

-- إضافة INDEX لعمليات بحث أسرع
ALTER TABLE products
ADD INDEX idx_category (category);

-- إضافة PRIMARY KEY (إذا لم يكن موجوداً)
ALTER TABLE users
ADD PRIMARY KEY (id);

RENAME TABLE old_name TO new_name;

-- أو
ALTER TABLE old_name RENAME TO new_name;

DROP TABLE table_name;

-- نسخة آمنة (بدون خطأ إذا لم يكن موجوداً)
DROP TABLE IF EXISTS table_name;

TRUNCATE TABLE users;

DROP DATABASE database_name;

-- نسخة آمنة
DROP DATABASE IF EXISTS database_name;

-- فهرس عمود واحد
CREATE INDEX idx_username ON users(username);

-- فهرس أعمدة متعددة (مركب)
CREATE INDEX idx_name_category ON products(name, category);

-- فهرس فريد
CREATE UNIQUE INDEX idx_email ON users(email);

SHOW INDEX FROM users;

DROP INDEX idx_username ON users;

-- أو
ALTER TABLE users DROP INDEX idx_username;

-- إنشاء قاعدة البيانات
CREATE DATABASE IF NOT EXISTS ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

USE ecommerce;

-- جدول المستخدمين
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
);

-- جدول الفئات
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- جدول المنتجات
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    category_id INT,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock INT DEFAULT 0,
    image_url VARCHAR(255),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
    INDEX idx_category (category_id),
    INDEX idx_price (price)
);

-- جدول الطلبات
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
    shipping_address TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id),
    INDEX idx_status (status)
);

-- جدول عناصر الطلب (علاقة متعدد لمتعدد)
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE
);