MySQL: دليل تفصيلي لإنشاء جداول وقيود في MySQL

-- Customer Table
CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    phone VARCHAR(20) UNIQUE
);

-- Product List
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10,2) NOT NULL
);

-- Orders Table
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    FOREIGN KEY (customer_id) REFERENCES customers(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4];

CREATE TABLE IF NOT EXISTS users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 0 AND age <= 150),
    status ENUM('active', 'inactive', 'banned') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='User Table';

Query OK, 0 rows affected (0.02 sec)

-- Single-column primary key
CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL
);

-- Composite Primary Key
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- When deleting a customer，All orders for this customer have been automatically deleted.
DELETE FROM customers WHERE id = 1;
-- orders In the table customer_id=1 Records are automatically deleted

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE,        -- Single-row, unique
    username VARCHAR(50) UNIQUE,      -- Single-row, unique
    phone VARCHAR(20),
    UNIQUE KEY (phone)                -- Another way to write it
);

-- Composite Unique Constraint
CREATE TABLE user_roles (
    user_id INT,
    role_id INT,
    UNIQUE KEY (user_id, role_id)    -- The same role cannot be assigned to the same user more than once.
);

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,       -- Product Name (Required)
    price DECIMAL(10,2) NOT NULL,     -- Price is required
    description TEXT,                  -- The description may be left blank
    category_id INT NOT NULL
);

-- Insertion Test
INSERT INTO products (name, price) VALUES ('iPhone', 999);
-- Error：category_id Cannot be NULL
-- ERROR 1048 (23000): Column 'category_id' cannot be null

CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    status ENUM('draft', 'published', 'archived') DEFAULT 'draft',
    view_count INT DEFAULT 0,
    is_featured BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Omit fields with default values during insertion
INSERT INTO articles (title) VALUES ('My First Article');

-- Search
SELECT * FROM articles;

+----+-------------------+--------+------------+-------------+---------------------+
| id | title             | status | view_count | is_featured | created_at          |
+----+-------------------+--------+------------+-------------+---------------------+
|  1 | My First Article  | draft  |          0 |           0 | 2026-07-03 10:00:00 |
+----+-------------------+--------+------------+-------------+---------------------+

CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 65),
    salary DECIMAL(10,2) CHECK (salary > 0),
    email VARCHAR(100) CHECK (email LIKE '%@%.%')
);

-- Test
INSERT INTO employees (name, age, salary, email) VALUES ('Alice', 25, 5000, 'alice@company.com');
-- Error：age Not satisfied CHECK Conditions
INSERT INTO employees (name, age, salary, email) VALUES ('Bob', 15, 3000, 'bob@company.com');
-- ERROR 3819: Check constraint 'employees_chk_1' is violated.

-- View all tables in the current database
SHOW TABLES;

-- View Table Structure
DESCRIBE users;
-- Or
DESC users;

-- View the statement used to create the table
SHOW CREATE TABLE users\G

-- View the table's column information
SHOW COLUMNS FROM users;

+----------------+--------------+------+-----+-------------------+----------------+
| Field          | Type         | Null | Key | Default           | Extra          |
+----------------+--------------+------+-----+-------------------+----------------+
| id             | int          | NO   | PRI | NULL              | auto_increment |
| username       | varchar(50)  | NO   | UNI | NULL              |                |
| email          | varchar(100) | NO   |     | NULL              |                |
| password_hash  | varchar(255) | NO   |     | NULL              |                |
| age            | int          | YES  |     | NULL              |                |
| status         | enum(...)    | YES  |     | active            |                |
| created_at     | timestamp    | YES  |     | CURRENT_TIMESTAMP |                |
| updated_at     | timestamp    | YES  |     | CURRENT_TIMESTAMP |                |
+----------------+--------------+------+-----+-------------------+----------------+