MySQL Tables and Data Insertion for Beginners

MySQL is a widely used relational database management system (RDBMS) found in web apps, online shops, and many backend projects. This guide walks beginners through creating tables and adding data to them with MySQL.

Prerequisites

Before you start, make sure you have the following:

• MySQL installed and configured on your system.
• Basic understanding of SQL syntax.
• Access to the MySQL command line or MySQL Workbench.

MySQL Table Syntax

Create Table Syntax

The syntax for creating a table in MySQL with a primary key is as follows:

CREATE TABLE table_name (
    column1_name data_type PRIMARY KEY,
    column2_name data_type,
    ...
);

Role of a Primary Key

A primary key is a column (or combination of columns) that uniquely marks each row in a table. It makes sure no two rows share the same primary key value(s), supporting data integrity and avoiding duplicate entries. In the example below, the id column functions as the primary key, giving every user a distinct identifier.

Example with Primary Key

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);

MySQL Table Syntax without Primary Key

The syntax for creating a table in MySQL without a primary key is as follows:

CREATE TABLE table_name (
    column1_name data_type,
    column2_name data_type,
    ...
);

Most Common MySQL Commands Table

Here’s a table summarizing the MySQL commands used in this tutorial, including their syntax, usage, and examples:

Step 1 – Create a Database

First, create a database that will hold your table. Use the CREATE DATABASE statement followed by the database name. In this example, the database is named mydatabase.

CREATE DATABASE mydatabase;

After creating the database, switch into it using the USE statement. This makes sure the next commands run inside the newly created database.

USE mydatabase;

By running these two statements, you have created a database and made it the active database for your current session.

Step 2 – Create a Table

To create a table in MySQL, use the CREATE TABLE statement and provide the table name. In this example, the table is called users. The definition sits inside parentheses and includes four columns: id, name, email, and registration_date.

Here’s a breakdown of each column:

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(255) UNIQUE,
    registration_date DATE
);

id: This column is set as an integer (INT) and is assigned as the table’s primary key using PRIMARY KEY. With AUTO_INCREMENT, every new insert increases the id value by 1, beginning at 1. This guarantees each record has its own unique identifier.

name and email: These columns use VARCHAR to store variable-length strings. The number in parentheses is the maximum character length allowed. For name, it’s 100 characters, while email allows up to 255. The UNIQUE rule on email ensures every email address is distinct and cannot be repeated.

registration_date: This column uses the DATE type to store dates. It records the date each user signed up.

By running this CREATE TABLE statement, you have created the users table with the defined columns and their attributes.

Step 3 – Insert Data into the Table

To add data into the users table, use the INSERT INTO statement. After the table name (users), specify the columns you want to fill: name, email, and registration_date. Then use VALUES to provide the data that should be inserted.

In this example, the inserted values are:

• name: ‘John Doe’
• email: ‘john@example.com’
• registration_date: ‘2025-01-10’

Here’s the SQL statement to implement this:

INSERT INTO users (name, email, registration_date)
VALUES ('John Doe', 'john@example.com', '2025-01-10');

By running this statement, a new record is added to the users table with the provided values.

Inserting Multiple Rows

When you want to add multiple rows, using one INSERT INTO statement with several VALUES groups is often more efficient than running separate INSERT statements for each row. This reduces the number of database round trips, which can improve performance and lower the load on the database server.

Here’s an example of how to insert multiple rows into the users table in a single statement:

INSERT INTO users (name, email, registration_date)
VALUES
('Jane Smith', 'jane@example.com', '2025-01-11'),
('Emily Johnson', 'emily@example.com', '2025-01-12');

In this example, two rows are added to the users table using one INSERT INTO statement. Each VALUES set represents one row, and the rows are separated by commas. This inserts multiple rows in a single operation, which is more efficient than running separate statements per row.

Step 4 – Verify the Data

After inserting data into the users table, it’s important to confirm the insert worked and the data is correct. This helps ensure the result matches what you expect and can reveal issues early.

To verify the inserted data, use the SELECT statement to fetch all rows from the users table. The syntax SELECT * returns every column (*) from the chosen table (users). This lets you view the complete dataset and confirm the expected entries are present.

Here’s the SQL statement to verify the data:

SELECT * FROM users;

When you run this statement, you should see the following output:

Output

+----+------------+-------------------+----------------+
| id | name       | email             | registration_date |
+----+------------+-------------------+----------------+
|  1 | John Doe   | john@example.com | 2025-01-10      |
|  2 | Jane Smith | jane@example.com | 2025-01-11      |
|  3 | Emily Johnson | emily@example.com | 2025-01-12      |
+----+------------+-------------------+----------------+

Step 5 – Update Data

Updating records that already exist in a database is an essential task that lets you adjust stored information after it has been inserted. It supports long-term accuracy and consistency. In this step, we show how to update a specific row in the users table using the UPDATE statement.

To update existing rows, use UPDATE followed by the table name, then the SET clause to define what changes, and the WHERE clause to decide which row(s) should be updated. Here’s an example that changes the email address for the user whose id is 1:

UPDATE users SET email = 'john.doe@example.com' WHERE id = 1;

After running the UPDATE statement, it’s important to confirm the change was applied successfully. To do that, use SELECT to retrieve the updated record(s). The SELECT * syntax returns all columns (*) from the users table, allowing you to view the full dataset and confirm the updated value appears as expected.

Here’s the SQL statement to verify the update:

SELECT * FROM users;

When you run this statement, you should see the following output, showing that the email address for the user with id equal to 1 has been updated successfully:

Output

+----+------------+-------------------+----------------+
| id | name       | email             | registration_date |
+----+------------+-------------------+----------------+
|  1 | John Doe   | john.doe@example.com | 2025-01-10      |
|  2 | Jane Smith | jane@example.com | 2025-01-11      |
|  3 | Emily Johnson | emily@example.com | 2025-01-12      |
+----+------------+-------------------+----------------+

Practical Usage

Inserting data for a blog, CRM, or e-commerce site

In many real-world systems such as blogs, CRM (Customer Relationship Management) platforms, and e-commerce websites, adding data into a database is a core operation. For example, when someone signs up on a blog or an online store, their details must be saved in the database so they can be used later. In a CRM environment, customer information is written into the database so interactions and relationships can be managed over time. This step is fundamental for creating a backend that is stable and able to scale.

Here’s an example of how to insert user registration data into a database using PHP and MySQL:

<?php
// Assuming $conn is a valid MySQL connection
if (isset($_POST['register'])) {
    $name = $_POST['name'];
    $email = $_POST['email'];
    $password = $_POST['password']; // Assuming password is hashed for security

    $query = "INSERT INTO users (name, email, password) VALUES (?, ?, ?)";
    $stmt = $conn->prepare($query);
    $stmt->bind_param("sss", $name, $email, $password);
    $stmt->execute();
    $stmt->close();
}
?>

Integration with backend workflows

The ability to add data to a database connects directly with many backend processes. In web applications, for instance, registration details are commonly written to the database using server-side technologies such as PHP, Python, or Node.js. That stored data is then used for login and authentication, profile handling, and delivering more personalized experiences. In CRM solutions, inserting data is key to logging customer interactions, organizing sales pipelines, and producing insights that support business growth.

Here’s an example of how to insert customer interaction data into a database using Node.js and MySQL:

const mysql = require('mysql');

// Assuming db is a valid MySQL connection
const insertCustomerInteraction = (customerID, interactionType, interactionDate) => {
    const query = "INSERT INTO customer_interactions (customer_id, interaction_type, interaction_date) VALUES (?, ?, ?)";
    db.query(query, [customerID, interactionType, interactionDate], (error, results, fields) => {
        if (error) throw error;
        console.log('Customer interaction inserted successfully');
    });
};

Common errors

Table already exists

If you try to create a table that is already present in the database, MySQL returns an error. To prevent this, you can include the IF NOT EXISTS clause in your CREATE TABLE command. Here’s an example:

CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL
);

Incorrect data types

Choosing the wrong data types for columns can cause errors or lead to unexpected results. For example, inserting a text value into an integer column will trigger an error. Make sure each column’s data type matches the kind of data you plan to store.

Example of incorrect data type usage:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age VARCHAR(3) NOT NULL // Incorrect data type for age, should be INT
);

INSERT INTO users (name, email, age) VALUES ('John Doe', 'john.doe@example.com', 'twenty-five'); // This will result in an error due to incorrect data type for age

Corrected example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    age INT NOT NULL // Correct data type for age
);

INSERT INTO users (name, email, age) VALUES ('John Doe', 'john.doe@example.com', 25); // Correct insertion with the right data type for age

Syntax errors

Syntax issues can happen when SQL statements are written incorrectly—for example, missing parentheses, mismatched brackets, improper keyword usage, or wrong column names. To reduce syntax errors, keep statements properly formatted and follow MySQL syntax rules closely.

Example of syntax error:

INSERT INTO users (name, email, age VALUES ('John Doe', 'john.doe@example.com', 25); // Missing closing parenthesis

Corrected example:

INSERT INTO users (name, email, age) VALUES ('John Doe', 'john.doe@example.com', 25); // Correctly formatted SQL statement

Difference between INSERT, INSERT IGNORE, and REPLACE

When working with MySQL, it’s important to understand how INSERT, INSERT IGNORE, and REPLACE differ. Each statement is designed for a specific behavior when adding rows to a table. Below is a detailed explanation of each option, with examples and a comparison table.

INSERT

The standard INSERT command adds a new row to a table. If the row already exists, the statement triggers an error. This is the most typical approach for inserting data.

INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');

INSERT IGNORE

INSERT IGNORE behaves like INSERT, but if the row already exists, it skips the error. This is helpful when you only want to insert a row if it is not already there and you don’t need to handle the error manually. If the row exists, the insertion attempt is ignored quietly.

INSERT IGNORE INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');

REPLACE

REPLACE works similarly to INSERT, but when the row already exists, it swaps out the existing row and writes the new data instead. This can be useful when you want to ensure a row is inserted or refreshed so duplicates are not kept.

REPLACE INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');

Comparison Table

Here’s a comparison table to help you understand the key differences between INSERT, INSERT IGNORE, and REPLACE:

When choosing the right statement, consider the intended behavior:

• Use INSERT when you want to add a row and explicitly handle an error if the row already exists.
• Use INSERT IGNORE when you want to skip duplicate rows silently without handling errors directly.
• Use REPLACE when you want to insert a row or overwrite the existing one if a matching record already exists.

Understanding these differences helps you select the most suitable insertion method for your use case while maintaining data integrity in your MySQL database.

How to use prepared statements

Prepared statements provide a safer way to run SQL with dynamic values. They help prevent SQL injection by separating the SQL code from the data being inserted. In PHP, you can use mysqli or PDO to prepare and execute statements. Here’s a concise example using mysqli:

<?php
// Prepare an SQL statement to insert a new user into the 'users' table
$stmt = $conn->prepare("INSERT INTO users (name, email) VALUES (?, ?)");
// Bind the parameters to the SQL statement, specifying the types of the variables
$stmt->bind_param("ss", $name, $email);
// Assign values to the variables
$name = 'Jane Doe';
$email = 'jane.doe@example.com';
// Execute the prepared statement
$stmt->execute();
// Close the prepared statement
$stmt->close();
?>

FAQs

1. Can I create a table without defining a primary key?

Yes, you can create a table without defining a primary key. However, it’s strongly recommended to set a primary key for each table to protect data integrity and support efficient data retrieval. Here’s an example of creating a table without a primary key:

CREATE TABLE users (
  name VARCHAR(255),
  email VARCHAR(255)
);

2. How do I insert multiple rows in one query?

You can insert multiple rows in one query using the following syntax:

INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com'), ('Jane Doe', 'jane.doe@example.com');

3. What’s the difference between CHAR and VARCHAR in MySQL?

CHAR and VARCHAR are both string data types in MySQL, but they differ significantly in how they store data and manage storage space.

CHAR is a fixed-length data type, meaning it always reserves the exact amount of space defined for the column. If the stored value is shorter than the specified length, MySQL pads the remaining space with trailing spaces. For example, a column defined as `CHAR(10)` will always consume space for 10 characters, regardless of the actual string length.

VARCHAR, in contrast, is a variable-length data type that stores only the actual number of characters entered, along with a small amount of additional metadata for length tracking. This makes VARCHAR far more storage-efficient for values that vary in size.

Here’s an example of using both CHAR and VARCHAR:

CREATE TABLE users (
  name CHAR(10),
  email VARCHAR(255)
);

4. How do I copy and create a new table in MySQL?

You can copy and create a new table in MySQL using the following syntax:

CREATE TABLE new_users SELECT * FROM users;

This will create a new table new_users with the same structure and data as the users table.

5. How to create a database in MySQL?

To create a database in MySQL, use the following command:

CREATE DATABASE mydatabase;

This will create a new database named mydatabase.

Conclusion

In this tutorial, you have learned how to create and insert a table in MySQL using simple SQL commands. You have also covered some common errors and how to avoid them. This is just the beginning of your MySQL journey.