MySQL - CREATE & INSERT | Docs | Akkal Dhami

MySQL - CREATE & INSERT

CREATE TABLE creates a new table in MySQL. Think of a table as a spreadsheet — CREATE TABLE is you typing a new sheet at the bottom.

CREATE DATABASE creates a new database in MySQL.

INSERT adds new rows into a table. Think of a table as a spreadsheet — INSERT is you typing a new row at the bottom.

1. MySQL CREATE DATABASE

Syntax

syntax.sql

CREATE DATABASE database_name;

database_name — the name of the database you're creating

Example

example.sql

CREATE DATABASE my_db;

Use the Database

use.sql

USE my_db;

Drop the Database

drop.sql

DROP DATABASE database_name;

database_name — the name of the database you're dropping

2. MySQL CREATE TABLE

Basic syntax

syntax.sql

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

table_name — the name of the table you're creating
column1, column2, column3 — the names of the columns you're creating
datatype — the data type of the column
constraints — any constraints or rules for the column, such as NOT NULL or DEFAULT

Example

example.sql

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255),
    price DECIMAL(6,2),
    in_stock BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Let's break down each line:

Column	Meaning
`id INT AUTO_INCREMENT PRIMARY KEY`	A whole number that MySQL generates automatically (1, 2, 3, ...) and uses as the unique identifier for each row.
`title VARCHAR(255) NOT NULL`	Text with a maximum length of 255 characters. This field is required and cannot be empty.
`author VARCHAR(255)`	Text field up to 255 characters. Optional, so it can contain `NULL`.
`price DECIMAL(6,2)`	A fixed-point number with up to 6 digits in total and 2 digits after the decimal point. Ideal for storing money.
`in_stock BOOLEAN DEFAULT TRUE`	Stores a `TRUE` or `FALSE` value. Defaults to `TRUE` if no value is provided.
`created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP`	Automatically stores the date and time when the row is created.

Common MySQL data types

Type	Use
`INT`	Whole numbers.
`VARCHAR(n)`	Short text with a maximum length of `n` characters.
`TEXT`	Long text content such as articles, descriptions, or comments.
`DECIMAL(p,s)`	Exact numbers used for money and measurements.
`BOOLEAN`	Stores a true or false value.
`DATE`	Stores only a date, for example `2026-07-14`.
`TIMESTAMP` / `DATETIME`	Stores both date and time together.

Key building blocks (constraints)

PRIMARY KEY — uniquely identifies each row; every table should have one
AUTO_INCREMENT — MySQL fills this in for you, counting up automatically
NOT NULL — this column can't be left blank
DEFAULT value — if you don't provide a value, use this one instead
UNIQUE value — no two rows can have the same value in this column (e.g. email VARCHAR(255) UNIQUE)

Common MySQL constraints

Constraint	Purpose	Example
`PRIMARY KEY`	Uniquely identifies each row; can't be NULL, only one per table	`id INT PRIMARY KEY`
`AUTO_INCREMENT`	Automatically generates the next number for a column	`id INT AUTO_INCREMENT`
`NOT NULL`	Column must always have a value, can't be left empty	`title VARCHAR(255) NOT NULL`
`UNIQUE`	No two rows can share the same value in this column	`email VARCHAR(255) UNIQUE`
`DEFAULT`	Sets a fallback value used when none is provided	`in_stock BOOLEAN DEFAULT TRUE`
`CHECK`	Validates that a value meets a condition before it's saved	`price DECIMAL(6,2) CHECK (price > 0)`
`FOREIGN KEY`	Links a column to a primary key in another table, enforcing valid references	`author_id INT, FOREIGN KEY (author_id) REFERENCES authors(id)`
`INDEX / KEY`	Speeds up lookups on a column, without enforcing uniqueness	`INDEX idx_title (title)`

See the Table Structure

describe.sql

DESC books;
 
# or
 
SHOW COLUMNS FROM books;

Output

mysql> DESC books;
+------------+--------------+------+-----+-------------------+-------------------+
| Field      | Type         | Null | Key | Default           | Extra             |
+------------+--------------+------+-----+-------------------+-------------------+
| id         | int          | NO   | PRI | NULL              | auto_increment    |
| title      | varchar(255) | NO   |     | NULL              |                   |
| author     | varchar(255) | YES  |     | NULL              |                   |
| price      | decimal(6,2) | YES  |     | NULL              |                   |
| in_stock   | tinyint(1)   | YES  |     | 1                 |                   |
| created_at | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
+------------+--------------+------+-----+-------------------+-------------------+
6 rows in set (0.03 sec)

Drop the Table

drop.sql

DROP TABLE table_name;

table_name — the name of the table you're dropping

3. MySQL INSERT

Basic syntax

syntax.sql

INSERT INTO table_name (column1, column2, column3)
VALUES (value1, value2, value3);

table_name — which table you're adding to
The column list tells MySQL which columns you're filling in
VALUES supplies the actual data, in the same order as the columns

Examples

Inserting one row

example.sql

INSERT INTO books (title, author, price)
VALUES ('The Hobbit', 'J.R.R. Tolkien', 12.99);

Notice id isn't mentioned — because it's AUTO_INCREMENT, MySQL assigns it automatically (1, 2, 3...). Same with in_stock if it has a DEFAULT value — you can skip columns that have defaults or are optional.

Confirming the insertion

mysql> SELECT * FROM books;
+----+------------+----------------+-------+----------+---------------------+
| id | title      | author         | price | in_stock | created_at          |
+----+------------+----------------+-------+----------+---------------------+
|  1 | The Hobbit | J.R.R. Tolkien | 12.99 |        1 | 2026-07-14 18:41:39 |
+----+------------+----------------+-------+----------+---------------------+
1 row in set (0.00 sec)

Bulk insert — multiple rows at once

example.sql

INSERT INTO books (title, author, price) 
VALUES
('Dune', 'Frank Herbert', 15.50),
('1984', 'George Orwell', 9.99),
('Brave New World', 'Aldous Huxley', 10.25);

Always name your columns

You can skip the column list, but then you must supply a value for every column in exact table order:

example.sql

INSERT INTO books VALUES
(DEFAULT, 'Fahrenheit 451', 'Ray Bradbury', 8.50, TRUE, DEFAULT);

Avoid this pattern because it depends on the current table order. If columns are added, removed, or reordered later, your query may fail or insert data into unexpected columns. Always mention the column names explicitly.

Handling duplicates

If title had a UNIQUE constraint and you tried inserting a duplicate, MySQL would throw an error. Two ways to handle it:

example.sql

-- Skip silently instead of erroring
INSERT IGNORE INTO books (title, author, price)
VALUES ('The Hobbit', 'J.R.R. Tolkien', 12.99);
 
-- Or update the existing row instead
INSERT INTO books (title, author, price)
VALUES ('The Hobbit', 'J.R.R. Tolkien', 13.99)
ON DUPLICATE KEY UPDATE price = 13.99;

Common mistake

Mixing up column order:

test.sql

-- WRONG — price and author swapped
INSERT INTO books (title, author, price)
VALUES ('Dune', 15.50, 'Frank Herbert');

This either errors out or silently inserts garbage — always double-check the order matches.

4. CREATE commands — quick reference

example.sql

-- Create database if not exists
CREATE DATABASE IF NOT EXISTS my_database;
 
-- Use the database
USE my_database;
 
-- Basic table creation
CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255),
    price DECIMAL(6,2),
    in_stock BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- List all tables
SHOW TABLES;
 
-- View table structure
DESCRIBE books;
-- or
SHOW COLUMNS FROM books;

5. INSERT commands — quick reference

example.sql

-- Single row
INSERT INTO books (title, author, price)
VALUES ('The Hobbit', 'J.R.R. Tolkien', 12.99);
 
-- Multiple rows (bulk insert)
INSERT INTO books (title, author, price) VALUES
    ('Dune', 'Frank Herbert', 15.50),
    ('1984', 'George Orwell', 9.99),
    ('Brave New World', 'Aldous Huxley', 10.25);
 
-- All columns, no column list (order-dependent, use with caution)
INSERT INTO books VALUES 
(DEFAULT, 'Fahrenheit 451', 'Ray Bradbury', 8.50, TRUE, DEFAULT);
 
-- Skip row silently if duplicate
INSERT IGNORE INTO books (title, author, price)
VALUES ('The Hobbit', 'J.R.R. Tolkien', 12.99);
 
-- Update existing row if duplicate found
INSERT INTO books (title, author, price)
VALUES ('The Hobbit', 'J.R.R. Tolkien', 13.99)
ON DUPLICATE KEY UPDATE price = 13.99;
 
-- Verify
SELECT * FROM books;

CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(255), price DECIMAL(6,2), in_stock BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );

Column

Meaning

id INT AUTO_INCREMENT PRIMARY KEY

A whole number that MySQL generates automatically (1, 2, 3, ...) and uses as the unique identifier for each row.

title VARCHAR(255) NOT NULL

Text with a maximum length of 255 characters. This field is required and cannot be empty.

author VARCHAR(255)

Text field up to 255 characters. Optional, so it can contain NULL.

price DECIMAL(6,2)

A fixed-point number with up to 6 digits in total and 2 digits after the decimal point. Ideal for storing money.

in_stock BOOLEAN DEFAULT TRUE

Stores a TRUE or FALSE value. Defaults to TRUE if no value is provided.

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Automatically stores the date and time when the row is created.

Type

Use

INT

Whole numbers.

VARCHAR(n)

Short text with a maximum length of n characters.

TEXT

Long text content such as articles, descriptions, or comments.

DECIMAL(p,s)

Exact numbers used for money and measurements.

BOOLEAN

Stores a true or false value.

DATE

Stores only a date, for example 2026-07-14.

TIMESTAMP / DATETIME

Stores both date and time together.

Constraint

Purpose

Example

PRIMARY KEY

Uniquely identifies each row; can't be NULL, only one per table

id INT PRIMARY KEY

AUTO_INCREMENT

Automatically generates the next number for a column

id INT AUTO_INCREMENT

NOT NULL

Column must always have a value, can't be left empty

title VARCHAR(255) NOT NULL

UNIQUE

No two rows can share the same value in this column

email VARCHAR(255) UNIQUE

DEFAULT

Sets a fallback value used when none is provided

in_stock BOOLEAN DEFAULT TRUE

CHECK

Validates that a value meets a condition before it's saved

price DECIMAL(6,2) CHECK (price > 0)

FOREIGN KEY

Links a column to a primary key in another table, enforcing valid references

author_id INT, FOREIGN KEY (author_id) REFERENCES authors(id)

INDEX / KEY

Speeds up lookups on a column, without enforcing uniqueness

INDEX idx_title (title)

-- Skip silently instead of erroring INSERT IGNORE INTO books (title, author, price) VALUES ('The Hobbit', 'J.R.R. Tolkien', 12.99); -- Or update the existing row instead INSERT INTO books (title, author, price) VALUES ('The Hobbit', 'J.R.R. Tolkien', 13.99) ON DUPLICATE KEY UPDATE price = 13.99;

-- Create database if not exists CREATE DATABASE IF NOT EXISTS my_database; -- Use the database USE my_database; -- Basic table creation CREATE TABLE books ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, author VARCHAR(255), price DECIMAL(6,2), in_stock BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- List all tables SHOW TABLES; -- View table structure DESCRIBE books; -- or SHOW COLUMNS FROM books;

-- Single row INSERT INTO books (title, author, price) VALUES ('The Hobbit', 'J.R.R. Tolkien', 12.99); -- Multiple rows (bulk insert) INSERT INTO books (title, author, price) VALUES ('Dune', 'Frank Herbert', 15.50), ('1984', 'George Orwell', 9.99), ('Brave New World', 'Aldous Huxley', 10.25); -- All columns, no column list (order-dependent, use with caution) INSERT INTO books VALUES (DEFAULT, 'Fahrenheit 451', 'Ray Bradbury', 8.50, TRUE, DEFAULT); -- Skip row silently if duplicate INSERT IGNORE INTO books (title, author, price) VALUES ('The Hobbit', 'J.R.R. Tolkien', 12.99); -- Update existing row if duplicate found INSERT INTO books (title, author, price) VALUES ('The Hobbit', 'J.R.R. Tolkien', 13.99) ON DUPLICATE KEY UPDATE price = 13.99; -- Verify SELECT * FROM books;