MySQL Database Introduction

MySQL - Database Introduction: The database plays an essential role in MySQL for storing and modifying the data. The database creates and keeps multiple tables. The database organizes and manipulates data files using a table and its index, row, and column.

MySQL management system handles databases using two methods. The first method is a Command-line client of MySQL. The second method is MySQL workbench. In this chapter, you will learn databases using command line client queries and workbench interface.

MySQL workbench creates a database to save schemas and tables as per requirement. The Schemas saves various tables for storing data. After, the table can create, save, delete, and update the information of the application. This interface does not use query due to the automation of software.

MySQL command-line client requires a database. We cannot access data files without a database. MySQL has several queries to access and handle data.

MySQL - Database Queries

The important command-line client queries are given below.

Database Queries	Description
Create database	Create a new database in the MySQL interfaces.
Show database	Display available database in the MySQL interfaces.
Use database	Use database in the MySQL interfaces.
Delete database	Remove unwanted databases in the MySQL interfaces.
Copy database	Create a duplicate database in the MySQL interfaces.

Prerequisites

The command-line client interfaces use passwords and start to operate the data. If a database is available, then the interface must use the available database. If the database does not exist in the system; then, the interface creates a new database.

Create a database in MySQL command-line client

MySQL requires a database to access old data and store new data. If a database is not present, then the command-line client creates a new database. The database can save various tables and manage table data.

The database helps to operate the information of the table. If you cannot use a database, then the MySQL interface does not work. Creating a new database is the primary step of operating data using the MySQL system.

Syntax

The below syntax is to create a new database. The database name must be unique or different from another database.

CREATE DATABASE database_name;

If you are confused about the database name, then use the "if not exists" command. If the given name available in the system, then the output shows an error. If the given name does not exist in the system, then you get the new database. The "if not exists" command is optional.

CREATE DATABASE IF NOT EXISTS database_name;

If you want to show the format of the newly created database, then use the following syntax.

SHOW CREATE DATABASE database_name;

Examples of creating a database.

1) Example: create a new database.

Execute the below query to get the new database.

 mysql> create database tutorials;
 Query OK, 1 row affected (1.11 sec) 

As shown in the above image, the command-line client has created a new thread for MySQL query. It would be best if you start using the available database. You are ready to use other queries.

2) Example: create a new database using the "IF NOT EXISTS" command.

If you want to get the database's exact name, then use the below statement. Execute the below query in the command-line client.

 mysql> create a database if not exists tutorials;
 Query OK, 1 row affected, 1 warning (0.23 sec) 

As shown in the above image, the command-line client has created a new thread for MySQL query.

3) Example: display created database.

If you want to show created database, then use the below query. The following syntax helps to analyze the created database. Execute the below query in the command-line client.

mysql> show create database tutorial;

As shown in the above image, the command-line client has created a new thread for MySQL query. You are ready to use other queries.

Show database in MySQL command-line client

MySQL helps in creating a database by using the queries. If you want to display the available database, then use the following query to show the entire database.

Syntax

The following syntax shows databases in the MySQL data management system. Execute the below syntax.

Show databases;

The database applies filters or conditions to display the required database. It operates the "WHERE" clause with the condition and "LIKE" operator with a pattern.

The syntax uses the LIKE operator with the required pattern.

Show databases LIKE pattern;

The syntax uses the "WHERE" clause with the required expression.

Show databases WHERE expression;

Examples of the show database.

1) Example: show available database.

Execute the below query to get the database.

mysql> show databases;

OUTPUT

As shown in the above image, the available database has been displayed. The MySQL interface has displayed the entire list of the database.

2) Example: show database using LIKE operator.

Execute the below query to get the database. The following query uses the LIKE operator with the required pattern.

mysql> show databases like '%tut%';

OUTPUT

As shown in the above image, the available database has been displayed. The MySQL interface has displayed the required list of the database.

3) Example: show database using WHERE clause.

Execute the below query to get the database. The following query uses the WHERE clause with the necessary condition.

mysql> show databases like '%tut%';

OUTPUT

As shown in the above image, the available database has been displayed. The MySQL interface has displayed the required list of the database.

Use database in MySQL command-line client

MySQL is a database management system that can contain several databases. If the user wants some specific data from the database, then the MySQL command-line client will use the database. If the interface does not use a database, then you do not execute data operations. The database helps to operate tables and their data.

The command-line client executes a Create database, show database, and delete database queries before executing the "use database" query.

Syntax

The following syntax helps to utilize a required database.

Use database_name;

Examples of Use Database

1) Example: Use the existence database.

Execute the below query to get the new database.

mysql> use tutorial;

OUTPUT

The above image displays the output after executing the "use database" query. The command-line client uses the database to manage the information.

2) Example: Use a non-existence database.

Execute the below query to get the new database.

mysql> use tutorials;

OUTPUT

As shown in the above image, the command-line client resulted in an error.

Delete database in MySQL command-line client

MySQL interface helps in creating a new database as per the new project.  Sometimes, an unwanted database gets stored in MySQL. This database is never removed until the user deletes it.

You must remove unnecessary databases to maintain data and memory. The delete MySQL database syntax is below.

Syntax

The given syntax helps to remove known databases.

Drop database database_name;

The given syntax helps to remove unknown databases.

Drop database IF EXISTS database_name;

Examples of delete database

1) Example: Delete unwanted database.

Execute the below query to delete a database.

 mysql> drop database tutorials;
 Query OK, 0 rows affected (1.31 sec) 

OUTPUT

Execute the following MySQL query to get output.

mysql> show databases;

As shown in the above image, the command-line client interface has removed the database to manage the memory size.

2) Example: Delete unwanted database with a statement.

Execute the below query to delete an existing database.

 mysql> drop database IF EXISTS tutorials;
 Query OK, 0 rows affected (0.18 sec) 

OUTPUT

Execute the following MySQL query to get output.

mysql> show databases;

As shown in the above image, the command-line client interface has removed the database to manage the memory size.

3) Example: Delete a non-existence database with a statement.

Execute the below query to delete a non-existence database.

mysql> drop database tutorials;

You can refer to the below image.

As shown in the above image, the command-line client interface cannot remove the non-existence database.

After performing the query, MySQL shows an ERROR or gives a warning. If you use the "IF EXISTS" statement, then the MySQL interface shows a warning. If you use a basic query, then the MySQL interface gives an error.

Copy database in MySQL command-line client

MySQL manages data of the database in table format. Every time you cannot work with the original database. The database must be safe and easy to operate. It stores multiple tables and creates interconnection with each other.

The database MySQL provides a feature to create a duplicate database. You can work on the clone database and then apply it to the original database. The cloning of a database helps to access a similar database for a different operation.

Syntax

The copy MySQL database syntax is given below.

CREATE DATABASE old-database-name_copy;

Examples of the copy database

1) Example: create a duplicate database.

Execute the following query.

 mysql> create database tutorial_copy;
 Query OK, 1 row affected (0.17 sec) 

Output

Execute the following query to display the same database.

mysql> show databases;

As shown in the above image, the "tutorial" database is an original database, and "tutorial_copy" is a clone of the actual database.

MySQL Workbench Procedure

MySQL workbench interface works for automation. The workbench does not use a query or command. The database creates a framework of the schemas and tables.

If you want to create a database in MySQL, then you should create schemas. The workbench schemas can create, store, operate the table. MySQL workbench said "schemas," and MySQL command-line client said "database." Both works in a similar way, like storing multiple tables.

Show Schemas

MySQL workbench shows a database list on the front page. MySQL schemas display the left side column of the workbench. This column is called Navigator.

You can see the above output image. The database list has been displayed in the navigator columns. You can search database name or schema. Click on the "tutorial" database.

The workbench handles tables, views, stored Procedures, and Functions of the available schema.

Create Schemas

 To create new schemas in MySQL workbench, go to navigator columns of the home page and

click on the right button of the mouse. Next, click on the "create schema..." option.

 You can refer to the above image as it is the first step that creates the new schema. After this, you are required to give a name to the schema.

You see the "Name:  mysql_data," you can place the required name of the schema. The mysql_data is the name of the new schema. Next, proceed by clicking on the Apply button. However, make sure that the schema name is written in the lowercase.

MySQL workbench interface executes the SQL statement such as "create database schema_name;" as per requirement.

As you can see in the above image, a new schema is now available in the MySQL workbench interface.

Alter Schemas

You can change the schema name easily using the workbench interface. Click on the required schema name and press the right button of the mouse.

As shown in the above image, you will get many options to operate and manage schemas. After you change the charset/collations of the schemas, proceed by clicking on the "Finish" button.

Delete Schemas

The workbench interface creates and stores multiple schemas in the system. Some schemas are essential, but few schemas cannot be used. The multiple schemas store numerous data. This data affects the memory size of the MySQL management system.

Removing unwanted schemas is necessary to manage MySQL memory size. You can remove the entire schema using the below procedure. You can see the following image for reference.

Click on the respective schemas to delete. Right-click on the database or schema

Press the drop schema row of the palette.

Next, click on either Review SQL to verify the query or click on "Drop Now" to delete the unwanted data.

The above procedure helps to delete schema in the workbench interface. MySQL workbench works automatically as well as supports SQL queries.