The short answer: CREATE TABLE table_name (column_name data_type constraints, ...); run inside a selected database. The longer answer is about the choices you make on each line, because a table is hard to change once real data and application code depend on it.
CREATE TABLE table_name (
column1 datatype [NOT NULL] [DEFAULT value],
column2 datatype ...,
PRIMARY KEY (column1),
[UNIQUE KEY name (column)],
[CONSTRAINT name FOREIGN KEY (col) REFERENCES other_table(col)]
) [ENGINE=InnoDB] [DEFAULT CHARSET=utf8mb4];
If you have not created a database yet, start with creating a database in MySQL and run USE your_database; first.
A complete example: a products table
CREATE TABLE IF NOT EXISTS products (
product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
sku VARCHAR(20) NOT NULL,
name VARCHAR(120) NOT NULL,
category VARCHAR(40) NOT NULL DEFAULT 'General',
price DECIMAL(10,2) NOT NULL,
in_stock BOOLEAN NOT NULL DEFAULT TRUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (product_id),
UNIQUE KEY uq_products_sku (sku)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Line by line, here is why each choice was made:
INT UNSIGNED AUTO_INCREMENTwithPRIMARY KEY: MySQL numbers rows for you, and the key never needs to be negative.sku VARCHAR(20) NOT NULLplusUNIQUE KEY: the business identifier must exist and must not repeat. The surrogateproduct_idstays the join key.DECIMAL(10,2)for price: exact arithmetic for money.FLOATstores approximations and will give you rupee totals that are off by fractions of a paisa.DEFAULTvalues let inserts skip columns that usually have the same value.utf8mb4: full Unicode, including Hindi text and emoji. The oldutf8alias (utf8mb3) cannot store every character.IF NOT EXISTS: a setup script can run twice without an error.
Insert rows and check the result
INSERT INTO products (sku, name, price) VALUES ('KB-101','USB keyboard',649.00);
INSERT INTO products (sku, name, category, price, in_stock)
VALUES ('WC-220','HD webcam','Video',1899.00,FALSE);
SELECT product_id, sku, name, category, price, in_stock FROM products;
| product_id | sku | name | category | price | in_stock |
|---|---|---|---|---|---|
| 1 | KB-101 | USB keyboard | General | 649.00 | 1 |
| 2 | WC-220 | HD webcam | Video | 1899.00 | 0 |
The first row picked up category = 'General' and in_stock = 1 from the defaults. BOOLEAN is stored as TINYINT(1), so it shows 1 and 0. DESCRIBE confirms what MySQL actually built:
DESCRIBE products;
| Field | Type | Null | Key | Default | Extra |
|---|---|---|---|---|---|
| product_id | int unsigned | NO | PRI | NULL | auto_increment |
| sku | varchar(20) | NO | UNI | NULL | |
| name | varchar(120) | NO | NULL | ||
| category | varchar(40) | NO | General | ||
| price | decimal(10,2) | NO | NULL | ||
| in_stock | tinyint(1) | NO | 1 | ||
| created_at | datetime | NO | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
Adding rules: CHECK and FOREIGN KEY
A second table can reference the first and validate its own values:
CREATE TABLE product_reviews (
review_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
product_id INT UNSIGNED NOT NULL,
rating TINYINT NOT NULL,
CONSTRAINT chk_rating CHECK (rating BETWEEN 1 AND 5),
CONSTRAINT fk_reviews_product FOREIGN KEY (product_id)
REFERENCES products(product_id)
) ENGINE=InnoDB;
INSERT INTO product_reviews (product_id, rating) VALUES (1, 7);
ERROR 3819 (HY000): Check constraint 'chk_rating' is violated.
The foreign key column must have the same type as the column it references, including UNSIGNED. A mismatch is a common reason the CREATE TABLE itself fails. CHECK constraints are enforced from MySQL 8.0.16; older versions parsed them and silently ignored them. Every rule is covered with examples in MySQL constraints explained.
Copying a table: LIKE vs AS SELECT
CREATE TABLE products_copy LIKE products; -- structure only, keys included
CREATE TABLE products_snapshot AS SELECT * FROM products; -- data, but no keys
SHOW CREATE TABLE products_snapshot\G
CREATE TABLE `products_snapshot` (
`product_id` int unsigned NOT NULL DEFAULT '0',
`sku` varchar(20) NOT NULL,
`name` varchar(120) NOT NULL,
`category` varchar(40) NOT NULL DEFAULT 'General',
`price` decimal(10,2) NOT NULL,
`in_stock` tinyint(1) NOT NULL DEFAULT '1',
`created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
Look closely: the snapshot has no PRIMARY KEY, no UNIQUE KEY and no AUTO_INCREMENT. CREATE TABLE ... AS SELECT copies columns and data only. Use LIKE followed by INSERT ... SELECT when you need a true copy.
Mistakes that cause errors or regret later
- Reserved words as names.
CREATE TABLE order (id INT);fails withERROR 1064near 'order'. Useorders, or quote it as`order`(renaming is better). - No primary key. InnoDB then creates a hidden one. Replication, updates and tools all work better with an explicit key.
- Huge VARCHARs everywhere.
VARCHAR(255)on every column is legal, but it hides what the data is meant to be. Size columns to the real data; the MySQL data types guide helps. - Phone numbers and PIN codes as integers. They lose leading zeros and a
+91prefix. Store them asVARCHAR. - Mixed naming. Choose one style (lowercase with underscores is the usual MySQL choice) and keep it. On Linux servers, table names are case-sensitive by default.
To change a table after creating it, use ALTER TABLE (add a column, add an index, change a type). To remove it, use DROP TABLE. The differences between deleting data and dropping objects are explained in DELETE vs TRUNCATE vs DROP.