[MySQL Tutorial] Creating Databases and Tables in MySQL
![[MySQL Tutorial] Creating Databases and Tables in MySQL](/uploads/editor/Mysql-강좌-3강-데이터베이스-Database-와-테이블-Table-만들기-20260703-3e4eb8.webp)
![[Mysql 강좌] MySQL 8.x 데이터베이스 입문 프롤로그 - 왜 데이터베이스를 배워야 할까요?](/uploads/editor/Mysql-%EA%B0%95%EC%A2%8C-MySQL-8-x-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EC%9E%85%EB%AC%B8-%ED%94%84%EB%A1%A4%EB%A1%9C%EA%B7%B8-%EC%99%9C-%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4%EB%A5%BC-%EB%B0%B0%EC%9B%8C%EC%95%BC-%ED%95%A0%EA%B9%8C%EC%9A%94-20260703-3f39d4.webp)
You Need Space Before Storing Data
Just as you need land to build a house, you need a database (Database) to store data.
Within a database, tables are created to organize data by type.
To simplify:
MySQL
└── Database
├── Member Table
├── Board Table
├── Comment Table
└── Category Table
In other words, you can create multiple databases within MySQL, and each database can contain multiple tables.
What is a Database?
A database is a storage space that manages multiple tables.
For example, an e-commerce site might have the following structure:
E-commerce Database
Members
Products
Orders
Shopping Cart
Reviews
Announcements
Similarly, a forum site could be structured as follows:
Forum Database
Members
Posts
Comments
Attachments
Categories
You can think of a database as the main storage space for an entire project.
What is a Table?
A table is where actual data is stored.
If you've used Excel, this will be familiar.
For example, a table storing member information might look like this:
ID | Name | Username | |
|---|---|---|---|
1 | Hong Gil-dong | hong | |
2 | Kim Cheolsu | kim | |
3 | Lee Young-hee | lee |
Each row represents one member's information,
and each column stores a specific type of data, like name or email.
Creating a Database
The SQL command to create a database is simple:
CREATE DATABASE blogdb;
Running this creates a database named blogdb.
In real projects, it's best to name your database after the project for clarity.
For example:
blogdb
shoppingmall
board
school
These names make it easy to identify the purpose of each database.
Selecting a Database
After creating a database, you can't use it immediately—you must select which one to work with first.
USE blogdb;
From now on, all SQL commands will operate within the blogdb database.
Creating Your First Table
Let's create a simple table to store member information:
CREATE TABLE member (
id INT,
name VARCHAR(50),
email VARCHAR(100)
);
Running this SQL creates a table named member.
While simple, we'll add more features as we progress.
What are Columns?
Columns represent the types of data you want to store.
In our example:
Column Name | Description |
|---|---|
id | Member ID Number |
name | Member Name |
Email Address |
Each column stores one type of information.
Designing columns well is the first step to building a good database.
Why Data Types Are Important
Storing all data in the same way would be inefficient.
You should store numbers as numbers,
and text as text.
For example:
id INT
This creates a column for storing numbers.
Conversely:
name VARCHAR(50)
This creates a text column that can hold up to 50 characters.
We'll cover data types in more detail in the next lesson.
Checking Table Structure
To verify your table was created correctly, use this command:
DESC member;
Or:
DESCRIBE member;
The results will show information like:
Column names
Data types
Whether NULL is allowed
Default values
It's a good habit to check your table structure after creation.
Checking the Current Database
To see which database you're currently using, run:
SELECT DATABASE();
This will display the name of your selected database.
Checking this helps avoid accidentally working in the wrong database during practice sessions.
Viewing Existing Tables
To see all tables in your current database, use:
SHOW TABLES;
If the member table appears in the list, it was created successfully.
This command becomes essential as you create more tables.
Viewing All Databases
To see all databases on your MySQL server, use:
SHOW DATABASES;
After installation, you'll typically see default databases like:
mysql
performance_schema
information_schema
sys
Your blogdb database will also appear in this list.
Common Beginner Mistakes
Here are common mistakes beginners make when practicing:
1. Forgetting to Use 'USE'
If you don't select a database, table creation may fail or occur in the wrong database.
2. Missing Semicolons (;)
SQL statements usually end with semicolons.
CREATE DATABASE blogdb;
Get into the habit of checking for a semicolon at the end of each command.
3. Creating Duplicate Table Names
Trying to create a table with an existing name will cause errors.
Before creating, check if the table already exists.
4. Mixing Case Sensitivity
SQL commands are case-insensitive,
but table names and file systems may be case-sensitive depending on your operating system.
It's best to use lowercase for consistency.
Key Takeaways
Remember these key points from this lesson:
Databases manage multiple tables.
Tables store actual data.
Use
CREATE DATABASEto create a database.Use
USEto select a database.Use
CREATE TABLEto create a table.Check creation status with
SHOW DATABASESandSHOW TABLES.Check table structure using
DESC.
Understanding these concepts will make future SQL practice much easier.
Conclusion
In this lesson, you learned the basics of databases and tables in MySQL, including how to create them using simple SQL commands.
You're now ready to store data.
In the next lesson, we'll dive deeper into columns (Column) and data types (Data Type), learning how to properly structure your database for efficient storage.
This foundational knowledge is crucial—be sure to practice these concepts hands-on.
FAQ
What is the difference between a database and a table?
A database is a container that holds multiple tables, while a table stores specific types of data (like members or products). Think of a database as a project folder and tables as individual spreadsheets within it.
Why are data types important in MySQL?
Data types ensure efficient storage. For example, using INT for numbers instead of text saves space and allows proper mathematical operations on numerical data.
Korean Source
This article is an English localization of a Korean-language report from BlogScore. Original: [Mysql 강좌] 3강 데이터베이스(Database)와 테이블(Table) 만들기