How to Create Database in MySQL a Complete Guide | MySQL Tutorial

As you know MySQL is the most popular and open-source relational database management system. If you don’t have MySQL installed on your Linux machine then read another article “How to install MySQL in Ubuntu 19.04”.

Remember one more thing, this is important MySQL must be installed in your system. If you don’t have, then where will you create a database?. Many People in the world start running the command, without knowing the basic requirement. And last they will blam to writer or trainer.

If you are an administrative user (the minimum privilege required to create a new database is CREATE) or with a root user account then you can run all commands. You can read the article on “How to create a user in MySQL”.

In this tutorial, I will cover How to create database in MySQL, create the database if not exists, show all database, select database for starting work, and use of mysqladmin and more. This article is good for beginners.

Article Content

How to create a database in MySQL

Before, creating database in MySQL, you must access the database by running following command.

$mysql -u root -p

vijay@Ubuntu-19 : ~ $mysql -u root -p 
Enter password: 
Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.16 MySQL Community Server - GPL Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 
mysql>

Creating database is very simple you can do that by using CREATE DATABASE followed by database name. It can be done, by executing single command as follows:

mysql>CREATE DATABASE database_name;

mysql> CREATE DATABASE webdata; 
Query OK, 1 row affected (0.08 sec) 
mysql>

Some time, If you create a database in MySQL exists already, you will see the following error message. And In this situatioin, you will not able to create a database with the same name.

mysql> CREATE DATABASE webdata; 
ERROR 1007 (HY000): Can't create database 'webdata'; 
database exists

MySQL create a database if not exists

When I see such errors, I become frustrated. Maybe you feel the same, and gone to be mad after seeing this error.

If you don’t want this error message on the screen then you can add ‘IF NOT EXISTS’ text to command CREATE DATABASE and command will be like following

mysql>CREATE DATABASE IF NOT EXISTS database_name;

mysql> CREATE DATABASE IF NOT EXISTS webdata; 
Query OK, 1 row affected, 1 warning (0.00 sec) 
mysql>

In the above output, you can see 1 row affected with, 1 warning. Which means that the query was successful, and 1 warning which tells us that the database already exists and no new database was created.

Do you want to see the warnings? Run following command.

mysql>SHOW WARNINGS;

mysql> SHOW WARNINGS;
 +-------+------+--------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------+ | Note | 1007 | Can't create database 'webdata'; database exists | +-------+------+--------------------------------------------------+ 1 row in set (0.00 sec) 
mysql>

Show MySQL databases

Do you know “How many databases exist in your system?”? Literally, I don’t know. But this is really curious to know about it. One command can be used to check the result of the available database.

mysql>SHOW DATABASES;

This command will show databases exist on the server.

mysql> SHOW DATABASES; 
+--------------------+ | Database | +--------------------+ | information_schema | | mydata | | mysql | | performance_schema | | sys | | webdata | +--------------------+ 6 rows in set (0.09 sec) mysql>

How to select a database in MySQL

You can’t work with a newly created database If you don’t select a database for use. You must select a database before go for working with it. You can use “USE” command followed by database_name.

The command looks like the following:

mysql>USE dabase_name;

mysql> USE webdata; 
Database changed 
mysql>

Create a database in MySQL with mysqladmin

mysqladmin is themost powerful command for administrator to manage MySQL. It can be used to create a database without access to MySQL shell.

You can create following command to create database

$mysqladmin -u root -p create optdata

In the above command

• mysqladmin : It is a command.
• -u root : -u option followed by username for MySQL
• -p : For password, It will ask you later.
• create: query name
• optdata: database name.

Command will be similar like below

To show database run following command:

vijay@Ubuntu-19 : ~ $mysql -u root -p -e "show databases;
" Enter password: 
+--------------------+ | Database | +--------------------+ | information_schema | | mydata | | mysql | | optdata | | performance_schema | | sys | | webdata | +--------------------+ :vijay@Ubuntu-19 $~

Conclusion

Know you have a basic understanding of how to create a database in MySQL and working with it.

Please leave in the comment box if you have any type of query or suggestion.

Thanks for Reading!
Cheers!

Originally published at https://www.cyberpratibha.com on June 24, 2019.