MySQL Database

Create Database


A database is used to store the collection of records in an organized form. It allows us to hold the data into tables, rows, columns, and indexes to find the relevant information frequently. We can access and manage the records through the database very easily.


MySQL implements a database as a directory that stores all files in the form of a table. It allows us to create a database mainly in two ways:


1. MySQL Command Line Client

2. MySQL Workbench


MySQL Command Line Client



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


CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

Example


mysql> CREATE DATABASE employeesdb;



mysql> CREATE DATABASE employeesdb;


MySQL SELECT Database


SELECT Database is used in MySQL to select a particular database to work with. This query is used when multiple databases are available with MySQL Server.


You can use SQL command USE to select a particular database.


USE database_name;

Example


USE customers;





MySQL Show/List Tables


DESCRIBE means to show the information in detail. Since we have tables in MySQL, so we will use the DESCRIBE command to show the structure of our table, such as column names, constraints on column names, etc. The DESC command is a short form of the DESCRIBE command.



Syntax


MySQL Show/List Databases


When we work with the MySQL server, it is a common task to show or list the databases, displaying the table from a particular database, and information of user accounts and their privileges that reside on the server. In this article, we are going to focus on how to list databases in the MySQL server.



USE database_name;

Example


mysql> SHOW DATABASES;





MySQL DROP Database


Dropping a database deletes the database from an instance of SQL Server and deletes the physical disk files used by the database. If the database or any one of its files is offline when it is dropped, the disk files are not deleted.


MySQL allows us to drop/delete/remove a database mainly in two ways:

1. MySQL Command Line Client

2. MySQL Workbench



DROP DATABASE [IF EXISTS] database_name;



Example


DROP DATABASE mytestdb_copy;





MySQL COPY Database


MySQL copy database is a process of creating an exact duplicate of a MySQL database. This can be done for a variety of reasons, including creating a backup or transferring data to another server.2


We need to follow these steps to copy a database to another database:

1. First, use the CREATE DATABASE statement to create a new database.

2. Second, store the data to an SQL file. We can give any name to this file, but it must end with a .sql extension.


Third, export all the database objects along with its data to copy using the mysqldump tool and then import this file into the new database.



Example


mysqldump -u root -p testdb > D:\Database_backup\testdb.sql



mysql> SHOW TABLES;