MySQL: الدليل الشامل لتعديل هياكل جداول MySQL

-- Add a mobile phone number field
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Change the username length
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;

-- Add a Single Field
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add a field and specify its position
ALTER TABLE users ADD COLUMN avatar VARCHAR(255) AFTER username;

-- Add to the beginning of the table
ALTER TABLE users ADD COLUMN uuid CHAR(36) FIRST;

-- Add Multiple Fields
ALTER TABLE users 
ADD COLUMN address VARCHAR(200),
ADD COLUMN city VARCHAR(50),
ADD COLUMN country VARCHAR(50) DEFAULT 'China';

Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

-- Delete a Single Field
ALTER TABLE users DROP COLUMN phone;

-- Delete Multiple Fields
ALTER TABLE users DROP COLUMN address, DROP COLUMN city;

-- Modify Field Type
ALTER TABLE users MODIFY COLUMN username VARCHAR(100) NOT NULL;

-- Change the field type and move it to the specified location
ALTER TABLE users MODIFY COLUMN email VARCHAR(150) FIRST;

-- Modify Field Default Values
ALTER TABLE users MODIFY COLUMN status VARCHAR(20) DEFAULT 'active';

-- Rename Field
ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(100) NOT NULL;

-- Rename and Change Type
ALTER TABLE users CHANGE COLUMN phone mobile VARCHAR(30);

-- Method 1: RENAME TO
ALTER TABLE users RENAME TO customers;

-- Method 2: RENAME TABLE
RENAME TABLE users TO customers;

-- Rename Multiple Tables at Once
RENAME TABLE 
    old_users TO users,
    old_orders TO orders;

-- Create a table with an auto-increment column
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100)
);

-- Insert Data (id auto-generated)
INSERT INTO products (name) VALUES ('iPhone');
INSERT INTO products (name) VALUES ('MacBook');

-- View Current Accumulated Value
SHOW CREATE TABLE products\G

-- Modify the Auto-Increment Start Value
ALTER TABLE products AUTO_INCREMENT = 1000;

-- Insert New Data (id starts from 1000)
INSERT INTO products (name) VALUES ('iPad');

+----+---------+
| id | name    |
+----+---------+
|  1 | iPhone  |
|  2 | MacBook |
| 1000| iPad   |
+----+---------+

-- Add a default value
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- Delete the default value
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- Edit the default value for created_at
ALTER TABLE orders ALTER COLUMN order_date SET DEFAULT (CURRENT_DATE);

-- Add a Primary Key
ALTER TABLE users ADD PRIMARY KEY (id);

-- Delete Primary Key
ALTER TABLE users DROP PRIMARY KEY;

-- Add a unique constraint
ALTER TABLE users ADD UNIQUE (email);

-- Delete the unique constraint
ALTER TABLE users DROP INDEX email;

-- Add a Foreign Key
ALTER TABLE orders 
ADD CONSTRAINT fk_customer 
FOREIGN KEY (customer_id) REFERENCES customers(id);

-- Delete Foreign Key
ALTER TABLE orders DROP FOREIGN KEY fk_customer;

-- Add CHECK Constraints
ALTER TABLE employees ADD CONSTRAINT chk_age CHECK (age >= 18);

-- Delete CHECK Constraints
ALTER TABLE employees DROP CHECK chk_age;

ALTER TABLE users
ADD COLUMN phone VARCHAR(20) AFTER email,
ADD COLUMN avatar VARCHAR(255),
MODIFY COLUMN username VARCHAR(150) NOT NULL,
DROP COLUMN temp_field,
ADD UNIQUE (phone);