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
- MySQL create a database if not exists
- Show MySQL databases
- How to select a database in MySQL
- Create a database in MySQL with mysqladmin
- Conclusion
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
[email protected]:~$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> [email protected]:~$
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 the most 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
[email protected]:~$mysqladmin -u root -p create optdata Enter password: [email protected]:~$
To show database run following command:
[email protected]:~$mysql -u root -p -e "show databases;" Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | mydata | | mysql | | optdata | | performance_schema | | sys | | webdata | +--------------------+ [email protected]:~$
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!