How to use SQL Query In MySQL

You can use the information_schema database to query the size of a MySQL database:

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

• The query “SELECT table_schema AS ‘Database Name‘” retrieves the information from the column named table_schema in the information_schema.tables. This renaming of this column to “Database Name” is carried out specifically for generating a result set containing each database’s name present within it.
• The “SUM(data_length + index_length) / (1024 * 1024) AS “Size (MB)” code calculates the size of databases by adding their data_length and index_length columns from information_schema.tables. The resultant sum is divided by (1024 * 1024) to convert bytes into megabytes, which is then labeled as “Size (MB)” in the output set.
• The information_schema.tables can be utilized to obtain data on the specified table name. This specific case refers to the information_schema.tables table.
• GROUP BY table_schema: by using the GROUP BY clause on the table_schema column (which holds database names), our query calculates and groups together all sizes of unique databases. The resulting output displays each MySQL database’s name alongside its size in megabytes – obtained by combining both data and index lengths from tables within mentioned database.

In summary, the query retrieves the size of each database in megabytes, combining the data and index lengths of its tables. The result set will display the “Database Name” and the corresponding “Size (MB)” for each database on your MySQL.

Output:

This query sums up the data length and index length for each table in the specified database, providing the total size in MB.

SQL stands for Structured Query Language which is a computer language for storing, manipulating, and retrieving data stored in relational database management systems (RDBMS). SQL was developed at IBM by Donald Chamberlin, Donald C. Messerli, and Raymond F. Boyce in the year 1970s. MySQL is an open-source Relational Database Management System that stores data in a structured format using rows and columns. MYSQL language is easy to use as compared to other programming languages like C, C++, Java, etc. By learning some basic commands we can work, create, and interact with the Database.

A table is used to organize data in the form of rows and columns and is used for both storing and displaying records in the structure format. It is similar to worksheets in the spreadsheet application. A table creation command requires three things:

• Name of the table
• Names of fields
• Definitions for each field

MySQL allows us to create a table in the database mainly in two ways:

• MySQL Command Line Client
• MySQL Workbench

To get the size of a MySQL database, you can use various methods, including SQL queries or checking the file system. Here are a few ways to accomplish this: