Skip to content

Your First Table

Create a table, add data, and read it back.

A database without tables is an empty room. You need shelves to put things on. In database terms, those shelves are tables. This stage walks you through creating your first table, inserting data, and reading it back. By the end, you will have a working students table with real data.

Definition

CREATE TABLE is a SQL statement that defines a new table with columns, data types, and constraints. Each column has a name and a data type (such as INT, VARCHAR, or DATE) that determines what kind of data it can store.

Required step: Make sure you have the school database from Stage 2. If not, run CREATE DATABASE school; USE school; first.

Step 1: Create the Table

Copy this into the playground. It creates a students table with an id, name, and email column.

Create a students table

CREATE DATABASE IF NOT EXISTS school;
USE school;

CREATE TABLE students (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100)
);

IF NOT EXISTS prevents an error if the database already exists. AUTO_INCREMENT assigns 1, 2, 3 automatically. PRIMARY KEY makes id unique. NOT NULL means name cannot be NULL.

Step 2: Insert Data

INSERT inserts new rows into an existing table. You specify the columns and the values.

Add some students

INSERT INTO students (name, email)
VALUES
  ('Alice', '[email protected]'),
  ('Bob', '[email protected]'),
  ('Charlie', '[email protected]');

Notice we skip id. AUTO_INCREMENT handles it. Each row of values is one record.

See the data

SELECT * FROM students;

SELECT * means 'return all columns'. FROM students specifies the table. The result shows all three rows.

Step 3: Query with WHERE

WHERE indicates the conditions that rows must satisfy to be selected. A clause is a part of a SQL statement (like WHERE, FROM, or ORDER BY). Only matching rows are returned.

Find a specific student

SELECT * FROM students
WHERE name = 'Alice';

The WHERE clause compares name to 'Alice'. Only rows that match are returned.

Check for missing values

SELECT * FROM students
WHERE email IS NULL;

NULL means absence of data. You cannot use = NULL. Always use IS NULL or IS NOT NULL. More filtering patterns come in Stage 5.

1.Reading Your First Query Results

When you run SELECT * FROM students, MySQL returns a result set, which is a table of rows and columns. The column headers appear at the top, followed by each matching row. Here is what you might see:

+----+---------+-------------------+
| id | name    | email             |
+----+---------+-------------------+
|  1 | Alice   | [email protected] |
|  2 | Bob     | [email protected]   |
|  3 | Charlie | [email protected] |
+----+---------+-------------------+

The + and - characters form the table borders. The | characters separate columns. Each row of data appears between the horizontal lines. The column headers tell you what each value represents.

2.Common INSERT Mistakes

  • Mismatched column count. If you specify columns, the number of values must match. INSERT INTO students (name, email) VALUES ('Alice') fails because you provided one value for two columns.
  • Wrong data types. INSERT INTO students (name, email) VALUES (123, '[email protected]') works because MySQL converts 123 to a string, but it is better to match types explicitly. Numbers go without quotes, strings need quotes.
  • Forgetting quotes around strings. INSERT INTO students (name) VALUES (Alice) fails because MySQL thinks Alice is a column name. Always wrap string values in single quotes: VALUES ('Alice').
  • Using double quotes for strings. MySQL accepts double quotes for strings by default, but the SQL standard uses single quotes. Use single quotes to avoid confusion with identifiers.

3.What Comes Next

You now have a working table with data. In the next stage, you will learn how to modify existing rows with UPDATE and remove rows with DELETE. These are the three core data operations: read (SELECT), write (INSERT), and modify (UPDATE/DELETE). Together, they form the foundation of every database application.

What does AUTO_INCREMENT do?

How do you check for NULL values?

Key Takeaways

  • CREATE TABLE defines columns with data types and constraints.
  • INSERT INTO adds rows. AUTO_INCREMENT handles the primary key.
  • SELECT * FROM table returns all rows and columns.
  • WHERE filters rows. Use IS NULL for absence of data.

Ready to test your knowledge?

Take a Quiz

Related Tutorials