MySQL Database Operation

Introduction

MySQL is a relational database management system based on SQL, the most extensively used programming language for accessing and managing database records. MySQL is open-source software under the GNU license.

Create Database

A database is a tool for organising and storing information. It allows us to organise data into tables, rows, columns, and indexes so that we can find what we need quickly. The database allows us to rapidly access and manage the records.

In MySQL, a database is represented as a directory containing all files in the form of a table. It enables us to create a database using a variety of approaches, the most prevalent of which are:

1.MySQL Workbench 

It’s a database architect, developer, and administrator’s graphical user interface (GUI) tool. This visual tool supports SQL programming, data modelling, data migration, and comprehensive administrative capabilities for server configuration, user management, backup, and more. We can create new physical data models, E-R diagrams, and SQL queries with it (run queries, etc.).

To create a new database using this tool, start MySQL Workbench and log in with the specified username and password. The following screen will appear:

The new Schema window appears on the screen. When creating a new database, use the default character set and collation (for example, employeedb). Now, as shown on the screen below, click the Apply button:

There is a new popup window that appears. Then press the Apply button.

Click the Finish button to complete the database creation.

2.MySQL Command Line Client

We can build a new database in MySQL by using the CREATE DATABASE statement with the following syntax:

Syntax

CREATE DATABASE database_name;

Example:

CREATE DATABASE test_database_001;

Select Database

In MySQL, the SELECT Database command is used to choose a database to work with. When MySQL Server supports multiple databases, this query is used.

The SQL command USE can be used to pick a certain database.

Syntax:

USE database_name;

Example:

USE test_database_001;

Drop Database

A database can be removed.

We can quickly drop/delete/remove a MySQL database using the MySQL DROP DATABASE command. The database, including all tables, indexes, and constraints, will be permanently deleted. As a result, when removing a database with MySQL, we must proceed with caution because we will lose all the database’s contents. If the database is not available on the MySQL server, the DROP DATABASE query fails.

Syntax:

DROP DATABASE database_name;

 

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.