Create a Table

What is a table

A table is where you store your data. Think of it like a spreadsheet where:

• Each row represents one item (like one user)
• Each column represents one piece of information about that item (like username, email)

What is a field

A field is one piece of information you want to store. For example, a user might have a name, email, and age - each of these is a field.

What are data types

Data types tell the database what kind of information each field can store. For example, you wouldn’t store someone’s age as text - you’d store it as a number. Data types help the database understand and validate your data.

For our first table, we’ll use two common data types:

• INT = whole numbers (like user IDs, ages, counts)
• VARCHAR = text (like names, emails, descriptions)

Let’s create a users table to store user information. Run this:

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  username VARCHAR(100),
  email VARCHAR(255)
);

Verify Your Table

After creating your table, you can check it was created:

SHOW TABLES;

This shows all tables in your current database. You should see users listed.

You can also see the structure of your table:

DESCRIBE users;

This shows each column name, its data type, and other properties like whether it’s a primary key or auto-increment.

Breaking down each field:

Note: You may see the data types listed in either uppercase or lowercase letters, e.g. INT or int.

id int PRI auto_increment

• id = the field name. You have used id fields before from the frontend to fetch single results using a GET request.
• int = stores whole numbers
• PRI = stands for PRIMARYT KEY- this field uniquely identifies each user (no two users can have the same ID).
• auto_increment = MySQL automatically assigns the next available number (1, 2, 3, etc.)

username varchar(100)

• username = the field name
• varchar stands for “variable-length character string.” This means it stores text, like names or words, and only uses as much space as needed for each entry (up to the limit you set).
• (100) = the maximum number of characters this field can store. For example, if you set it to 100, you can store any text from 0 to 100 characters long in this field.

email varchar(255)

• email = the field name
• varchar(255) = stores text up to 255 characters (which is the standard maximum length for email addresses in most databases)

Common SQL Data Types