What is a Database?
A database is an organized or structured collection of essential data that has been held to make it easy to access, recover, manage, and update. In a database, data can be arranged in table format as well as in rows and columns. Data indexing makes it simple to obtain any data anytime it’s needed.
Different databases include SQL Server, Oracle Database, PostgreSQL, Informix, Sybase, MongoDB and MySQL. DBMS is used to manage these contemporary databases. To manipulate the data in a database, Structured Query Language, or SQL is used.
MySQL Database
MySQL is a fast and easy-to-use database used by many small and large businesses. MySQL is developed, distributed, and operated by the Swedish company MySQL AB. MySQL has become very popular for several reasons −
- MySQL supports large databases with more than 50 million rows per table. The size of the table file is 4 GB but can be expanded to a maximum limit of 8 million terabytes (TB).
- MySQL runs on many operating systems and in many languages, including PHP, PERL, C, C++, and JAVA.
- MySQL is released under an open-source license.
- MySQL uses the standard version of the popular SQL database language.
- MySQL is a very powerful program. It supports most of the features of expensive and powerful database packages.
- MySQL is well compatible with PHP, the most popular web development language.
Want to get certified in PHP & MySQL? Enroll in our PHP & MySQL Course.
MySQL CREATE DATABASE Statement
The Create New Database Section will be the first item you see when the MySQL Databases interface opens.
Simply type the database name that you want to create, then click the Create Database button.
Now the database has been added, you will receive a success message.
The CREATE DATABASE statement is used to create a new SQL database.
Syntax:
CREATE DATABASE database_name;
CREATE DATABASE Example:
Below is an example of an SQL statement that creates a database named “MyDatabase”:
Example
CREATE DATABASE MyDatabase;
Add a Database User
You must create a user and give it complete access to the database to use your newly created database in your application.
Scroll to the bottom of the MySQL databases interface to create a database user. MySQL users will appear here, and Add New User will appear under it.
Step 1 − Choose a strong password and enter your desired username. To add a new user, click Create User. “You have successfully created a MySQL user,” will appear as a success message.
After the creation of the MySQL user,, now you just need to add the User to the database.
Step 2 − Find “Add User to Database” by scrolling down.
Step 3 − Choose the user and database that you want to connect. When you click the Add button, the following screen will appear.
To give all the privileges to the user, now you have to select the ALL PRIVILEGES checkbox and all the checkboxes will automatically get selected.
Next, click on the make changes option to save the changes.
Modifying a Database
With time, the data kept in MySQL could become corrupt. There are several possible reasons for this. If you face any database-related errors while accessing the database in a web application, you must fix the database to restore the corrupt data. But before repairing the database, you need to find the table in which the data is corrupted by checking the database for errors.
You have two options for troubleshooting database-related issues. You must navigate to Modify Database Options in the MySQL Database Interface to carry out these actions.
You can check the below image for Modifying Database and take reference.
Check a Database
You can determine which table in the database is corrupted using the functionality of the cPanel Database.
The steps listed below must be completed to check a database.
Step 1 − From the Check Database drop-down menu, choose the database you want to check.
Step 2 − Click the Check Database button.
Step 3 − At this point, cPanel will execute an automatic script to locate the corrupt table in the Database. If a corrupt table is identified, the name of the table will be displayed; otherwise, a success message will be shown.
MySQL SELECT DATABASE Statement
In MySQL, you can choose any specific database you want to work with by using the SELECT Database command.
Syntax:
USE MyDatabase;
SELECT DATABASE Example:
Below is an example of an SQL statement to select a database named “MyDatabase”.
Example
USE MyDatabase;
MySQL SHOW DATABASE Statement
You can use the SHOW DATABASES command to list every database on a MySQL server host as shown below:
SHOW DATABASES;
You must first log in to the database server as shown below to list all databases on the local MySQL database server.
>mysql -u root -p
Enter password: **********
mysql>
After that, use the SHOW DATABASES command.
mysql> SHOW DATABASES;
+-----------------------------------+
| Database |
+-----------------------------------+
| Constraints |
| information_schema |
| testing |
| performance_schema |
| queries |
| system |
+-----------------------------------+
6 rows in set (0.00 sec)
As the command SHOW SCHEMAS is the same as the command SHOW DATABASES, the output of the following command is identical to that of the command given.
SHOW SCHEMAS;
Using the LIKE clause, you can query a database for data that fits a particular pattern.
SHOW DATABASES LIKE pattern;
For example, the following statement returns a database that ends with the string ‘schema’;
SHOW DATABASES LIKE '%schema';
+-----------------------------------+
| Database (%schema) |
+-----------------------------------+
| information_schema |
| performance_schema |
+-----------------------------------+
2 rows in set (0.00 sec)
You must have the SHOW DATABASES access to run the SHOW DATABASES statement if the MySQL database server was started with the —skip-show-database option.
Querying database data from information_schema
You can directly query the database information from the schemata table in the information schema database if the LIKE clause’s condition is insufficient.
The SHOW DATABASES command’s output is similar to the one from the following query.
SELECT schema_name
FROM information_schema.schemata;
Databases whose names end with “schema” or “s” are returned by the following SELECT command.
SELECT schema_name
FROM information_schema.schemata
WHERE schema_name LIKE '%schema' OR
schema_name LIKE '%s';
It returns the following result.
+------------------------------------+
| SCHEMA_NAME |
+------------------------------------+
| information_schema |
| performance_schema |
| Constraints |
| Queries |
+------------------------------------+
4 rows in set (0.00 sec)
Revise your constraints through MySQL Constraints Blog.
MySQL DROP DATABASE Statement
To drop an existing SQL database the DROP DATABASE statement is used.
Syntax:
DROP DATABASE database_name;
DROP DATABASE Example:
Below is an example of an SQL statement that drops the existing database named “MyDatabase”:
Example
DROP DATABASE MyDatabase;
Visit our SQL Community to get answers to all your queries!