Database Basics›Core· 35 min read

Creating Databases & Tables

Define where data lives — databases, tables, columns and their data types.

What you will learn

Create a database and table
Choose column data types
Set a primary key

CREATE DATABASE & TABLE

Before you can store anything, you need two things: a database (a named container) and at least one table (the grid of rows and columns) inside it. SQL gives you a command for each. Here we build a database called codingclave and put a users table in it.

Building a place for data is a three-step process. Read the numbered steps first, then look at the code — each line maps to a step:

Create the database with CREATE DATABASE codingclave; — this makes the empty container.
Switch into it with USE codingclave; so the next commands know which database to work in.
Create a table with CREATE TABLE users (...), listing each column and the kind of value it holds.

Create a database and a users table

CREATE DATABASE codingclave;
USE codingclave;

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) NOT NULL,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Walking through the table line by line: id INT AUTO_INCREMENT PRIMARY KEY is a whole-number id that MySQL fills in and counts up for you (1, 2, 3…) and marks as the unique key for each row. name VARCHAR(100) NOT NULL is text up to 100 characters that must have a value (NOT NULL means it cannot be left empty). email VARCHAR(150) NOT NULL is the same idea for the email. age INT is an optional whole number (no NOT NULL, so it may be empty). created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP is a date-and-time that automatically fills in with the moment the row was added.

Note: Output: Query OK, 0 rows affected. These commands do not return data — they build structure. MySQL replies with a short success message like "Query OK". After running them you have an empty users table, ready for rows. In phpMyAdmin you would now see codingclave in the left sidebar with users underneath it.

Common data types

Type	For
`INT`	Whole numbers
`VARCHAR(n)`	Text up to n characters
`TEXT`	Long text
`DECIMAL(8,2)`	Money / precise decimals
`DATE` / `TIMESTAMP`	Dates and times
`BOOLEAN`	true / false

Tip: Every table should have a primary key — a unique id for each row. id INT AUTO_INCREMENT PRIMARY KEY makes MySQL number rows automatically.

Q. What does AUTO_INCREMENT do on an id column?

Answer: AUTO_INCREMENT automatically assigns the next unique number to each new row — ideal for a primary key.

✍️ Practice

Create a products table with id, name, price and stock.
Choose appropriate data types for each column.

🏠 Homework

Create the tables for your library app (books, members) with primary keys.