MySQL Database Structure
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;
A MySQL database is a collection of database objects like tables, views, stored procedures, etc. The most important object is the database table.
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;
Create a new MySQL database: Create MySQL Database
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
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;
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.
MySQL Tutorials on this website:
For more information on MySQL Database: