Exploring Existing Databases in MySQL

Once you’ve connected to your MySQL server, one of the first tasks you might want to do is explore the existing databases. This helps you understand what databases are available on your server and gives you a starting point for further operations. Below, we'll go through the steps to explore existing databases using both MySQL Workbench and the Command Line.

1. Exploring Databases Using MySQL Workbench

Step 1: Connect to the MySQL Server

Open MySQL Workbench and connect to your MySQL server by selecting the appropriate connection and entering your password if prompted.

Step 2: View Existing Databases

Once connected, you will see a panel on the left labeled Navigator. This panel lists all the schemas (databases) on your server.
Under the SCHEMAS tab, you will see a list of all the databases that currently exist on the MySQL server.
You can expand each database to view its tables, views, stored procedures, and other objects.

Step 3: Explore a Specific Database

To explore the contents of a specific database, click the arrow next to the database name in the SCHEMAS tab. This will expand the database, showing all the tables, views, and other objects within it.
You can further explore by expanding individual tables to see the columns, indexes, and foreign keys.

Step 4: Run SQL Commands to List Databases

You can also list all databases using an SQL query. In the SQL editor, type the following command:
```
SHOW DATABASES;
```
Click the Execute button (lightning bolt icon) to run the query. The result grid will display a list of all databases on the server.

2. Exploring Databases Using the Command Line

Step 1: Connect to MySQL

Open your Command Prompt (Windows) or Terminal (macOS/Linux).
Connect to MySQL by typing the following command:
```
mysql -u root -p
```
Replace root with your MySQL username if you're using a different account. Enter your password when prompted.

Step 2: List All Databases

Once you are at the MySQL prompt (mysql>), you can list all the databases on your server by typing:
```
SHOW DATABASES;
```
Press Enter to execute the command. You will see a list of all the databases available on the MySQL server.

Step 3: Explore a Specific Database

To start working with a specific database, you need to select it using the USE command. For example, to select the test_db database, you would type:
```
USE test_db;
```
After selecting the database, you can explore its contents by listing the tables. To do this, type:
```
SHOW TABLES;
```
This will display all the tables within the selected database. You can further explore each table using SQL queries like DESCRIBE table_name; to view the structure of a specific table.

3. Additional SQL Commands for Exploring Databases

Viewing Table Structure:
- To see the structure of a table, use the DESCRIBE command followed by the table name:
  DESCRIBE table_name;
- This will display the columns in the table, along with their data types, constraints, and other details.

Viewing Database Size:

To check the size of a database or its tables, you can run the following query:

SELECT table_schema AS "Database", 
       ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.tables 
GROUP BY table_schema;

This query will return the size of each database in megabytes.

Conclusion

Exploring existing databases in MySQL is a fundamental step in understanding your data environment. Whether you use MySQL Workbench or the Command Line, these methods allow you to view and navigate the databases on your server, making it easier to manage and interact with your data. Once you're familiar with the structure of the databases, you can begin running queries and performing operations on the data.

This tutorial is brought to you by codes with pankaj.

PreviousIntroduction to Databases NextUnderstanding Tables, Rows, and Columns.

Last updated 2 months ago