MYSQL Database

Introduction to MYSQL Database

MySQL Database is a Relational Database Management System which is available freely as an open-source and based on SQL (Structured Query Language) language for data access and retrieval processes.MySQL Database software is provided under the terms of the GNU General Public License and also under other proprietary licenses. Also, a Database is defined as a Data Structure that is used to store any data and information on the server in an organized manner. The Database contains tables, and records are inserted or saved in those tables in the form of columns and rows so that the manipulation of data can be done in an easy way.

How to Create a Database in MySQL?

We can create MySQL Database using different methods but as it is based on SQL then, let us consider the following.

Syntax:

CREATE Database Database_name;

You can also create MySQL Database using the command line interface on your desktop and pass the SQL queries to view the result.

1. You can also follow the SQL commands if you are using the WAMP server. You can download WAMP or XAMPP, which is open source and install it on your system; you have to run localhost to open the homepage of WAMP.

2. You will find the PHPmyadmin option, which is the MySQL server, use root as username and password (blank if not set) and helps to access the database and perform any SQL query commands.

3. You can access the database and tables to manipulate any field’s data.

4. You can either execute the SQL query through the query editor area and manage the databases on the MySQL server or even write PHP code to connect the database and make changes to it.

5. For example, a database named ‘PersonDb’ in the database option is created as follow using the WAMP server:

Code:

CREATE Database PersonDb;

Output:

6. Using SQL command:

Code:

CREATE DATABASE PERSONDB;

Output:

7. Once the database is created, you can view it in the lists of databases on the server using the following query:

Code:

SHOW DATABASES;

Output:

8. You can view the database by clicking on a database on the left menu bar, where you can add tables:

9. Further, you can use PHP scripts to integrate the SQL query and establish a connection of any webpage to the database for insertions and other manipulations to store data.

Code:

<?php
$dbhost = “localhost”;            // Host/Server name
$dbuser = “root”;                     // Host user name
$dbpassword = “ ”;                 //use password if exists
$con = mysql_connect ($dbhost, $dbuser, $dbpassword);
if (! $con)
{
die (‘No Connection Established: ’ . mysql_error());
}
echo “Connection Established successfully”;
$sql = ‘CREATE DATABASE PersonDb’;
$query = mysql_query ($sql, $con);
if (! $query){
die (‘Could not create Database: ’ . mysql_error());
}
echo “Database created successfully\n”;
mysql_close ($con);
?>

Here, mysql_connectparameter is used to open a connection with the MySQL server, and CREATEquery created a database where as mysql_queryfunction checks if the database is created with the connection or not and returns TRUE on success and FALSE on failure.

How to Select a Database from Multiple Databases?

Generally, we use the SQL SELECT statement to select a particular database from multiple databases and display its tables of records. This is required when many databases exist on MySQL servers, and we need to work only with one from them.

We use the USE SQL command for this with the following:

Syntax:

USE Database_name;

Example

Code:

USE PersonDb;

Output:

How to Drop a Database?

1. We need admin access for deleting any database from the server. The following syntax is used to delete any database from the MySQL server:

Syntax:

DROP DATABASE Database_name;

Example

Code:

DROP DATABASE PersonDb;

Output:

2. Dropping a database may not be simple; if by you delete any database, you have to lose all the tables with records. So be careful about this; the DROP SQL statement destroys all the data available in that database.

3. When you proceed or execute this query, then a warning or message is displayed asking to confirm that we are sure we want to delete this database or not.

4. While deleting the database in the PHP script, it does not prompt to ask whether you want to delete the database or not, so be careful before executing the code:

Code:

<?php
$dbhost = “localhost”;// Host/Server name
$dbuser = “root”;// Host user name
$dbpassword = “ ”; //  Use password if exists
$con = mysql_connect ($dbhost, $dbuser, $dbpassword);
if (! $con)
{
die (‘No Connection Established: ’ . mysql_error());
}
echo “Connection Established successfully”;
$sql = ‘DROP DATABASE PersonDb’;
$query = mysql_query ($sql, $con);
if (! $query){
die (‘Could not drop the Database: ’ . mysql_error());
}
echo “Database deleted successfully\n”;
mysql_close ($con);
?>

Conclusion

• MySQL Database application is used for various purposes such as E-Commerce, Data Warehousing, and logging applications to maintain and store records. But the most important use of MySQL Database is as Web Database.
• Some major benefits of MySQL Database are Data safety, On-Demand Scalability, High Presentation, Round-the-Clock Uptime, Wide-ranging Transactional Provision, Complete Workflow Mechanism, Concentrated Total Cost of Proprietorship, etc.
• For example, if we have a database for a company, then it holds the tables for different areas like employee’s info, services records or financial transactions, and so on.
• Thus, MySQL Database is a fast, reliable, and stable RDBMS which saves our money and time, too, where SQL queries are used for manipulation of the records.

Recommended Articles

This is a guide to MYSQL Database. Here we discuss the Introduction to MYSQL Database and the practical examples and different subquery expressions. You can also go through our suggested articles to learn more –

1. 1. MySQL Operators
   2. Jira vs Github
   3. MySQL String functions
   4. MySQL vs SQLite