MySQL Database Structure

Overview

In this tutorial, we will learn MySQL Database Structure concepts. MySQL is a relational database. Database information and data is stored in the form of tables. A MySQL server can manage many databases at once. We can also call a database a schema.

View Database Structure

In this section we will learn the steps and syntax to view a database structure.

SHOW DATABASES
SHOW TABLES
DESCRIBE <table_name>

We can use the command SHOW DATABASES to list the databases.

mysql> SHOW DATABASES;

MySQL Databases Win11

MySQL Database

A MySQL database is a collection of database objects like tables, views, stored procedures, etc. The most important object is the database table.

MySQL Database Structure

A MySQL database can contain multiple tables and other database objects. We can create many tables in a single database.

For example, the world database contains three tables. We can use the command SHOW TABLES to list the database tables.

mysql> SHOW TABLES;

MySQL Database Tables Win11

Create a new MySQL database: Create MySQL Database

MySQL Table

A table consists of columns and rows. The rows of a database table consist of a collection of column values that describe a real-world entity. For example, an employee working in an Organization. An organization is also an entity. We can store the employee data in a database table called Employee.

Create a Table in MySQL database: Create New Table

Column

A column is also referred to as a field. A database table can contain multiple columns. Each column describes the data that each row in the table. The type of data stored is the datatype of the column. The rows may contain different data but of the same datatype.

For example, the city database table has five columns. The data stored in the ID column is an integer. We can use the command DESC <table_name> to list the columns and the datatypes of the table.

mysql> DESC city;

MySQL Table Columns

Row

A row is also referred to as a tuple or as a record. A database table can contain multiple rows. Each row in the table consists of a value for each column in the database table.

Retrieve Information from the table: Retrieve data from Table

That’s it. A basic understanding of database structure would help in database design.

—