MySQL Create Table

Anatomy of CREATE TABLE

A CREATE TABLE statement names the table, then lists a comma-separated set of column definitions inside parentheses. Each column definition supplies a name and a data type, and may optionally add constraints such as NOT NULL or DEFAULT. The last lines of the definition can also declare table-level constraints like a PRIMARY KEY spanning multiple columns.

CREATE TABLE employees (
  id INT PRIMARY KEY AUTO_INCREMENT,
  first_name VARCHAR(50) NOT NULL,
  last_name VARCHAR(50) NOT NULL,
  hire_date DATE,
  salary DECIMAL(10,2)
);

Common Data Types

Choosing the right data type keeps storage compact and comparisons fast. MySQL groups types into numeric, string, and date/time families, and picking a type that is too large or too generic (VARCHAR(255) for everything) wastes space and hides bugs that a stricter type would have caught at insert time.

Nullability and Defaults

By default, any column may hold NULL, meaning 'no value'. Marking a column NOT NULL forces every row to supply a real value at insert time, which is appropriate for columns like email or last_name that should never be missing. A DEFAULT clause supplies an automatic value when an INSERT statement omits the column entirely.

CREATE TABLE products (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(8,2) NOT NULL DEFAULT 0.00,
  in_stock BOOLEAN NOT NULL DEFAULT TRUE,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Creating from an Existing Table

You can also derive a new table's structure (and optionally its rows) from an existing one, which is handy for building temporary copies or archive tables without retyping every column.

CREATE TABLE employees_backup AS
SELECT * FROM employees;

• CREATE TABLE ... AS SELECT copies data but not indexes, constraints, or AUTO_INCREMENT settings.
• CREATE TABLE IF NOT EXISTS avoids an error if the table is already present.
• SHOW CREATE TABLE employees; prints the exact statement MySQL would use to recreate the table.