How do i create a new database in mysql database?

3.3.1 Creating and Selecting a Database

If the administrator creates your database for you when setting up your permissions, you can begin using it. Otherwise, you need to create it yourself:

mysql> CREATE DATABASE menagerie;

Under Unix, database names are case-sensitive (unlike SQL keywords), so you must always refer to your database as menagerie, not as Menagerie, MENAGERIE, or some other variant. This is also true for table names. (Under Windows, this restriction does not apply, although you must refer to databases and tables using the same lettercase throughout a given query. However, for a variety of reasons, the recommended best practice is always to use the same lettercase that was used when the database was created.)

Note

If you get an error such as ERROR 1044 (42000): Access denied for user 'micah'@'localhost' to database 'menagerie' when attempting to create a database, this means that your user account does not have the necessary privileges to do so. Discuss this with the administrator or see Section 6.2, “Access Control and Account Management”.

Creating a database does not select it for use; you must do that explicitly. To make menagerie the current database, use this statement:

mysql> USE menagerie
Database changed

Your database needs to be created only once, but you must select it for use each time you begin a mysql session. You can do this by issuing a USE statement as shown in the example. Alternatively, you can select the database on the command line when you invoke mysql. Just specify its name after any connection parameters that you might need to provide. For example:

$> mysql -h host -u user -p menagerie
Enter password: ********

Important

menagerie in the command just shown is not your password. If you want to supply your password on the command line after the -p option, you must do so with no intervening space (for example, as -ppassword, not as -p password). However, putting your password on the command line is not recommended, because doing so exposes it to snooping by other users logged in on your machine.

Note

You can see at any time which database is currently selected using SELECT DATABASE().

The article provides a detailed overview of how to create a database in MySQL using different methods and tools (including the Command Line, Workbench, and dbForge Studio for MySQL).

MySQL is a relational database management system based on SQL. It is developed, distributed, and supported by the Oracle Corporation. MySQL is free and open-source software and it is gaining more and more popularity due to its reliability, compatibility, cost-effectiveness, and comprehensive support.

MySQL has a fork – MariaDB, which is made by the original developers of MySQL. MariaDB has the same database structure and indexes which allows it to be a drop-in replacement for MySQL.

MySQL and MariaDB both support a number of popular operating systems including but not limited to the following: Linux, Ubuntu, Debian, Microsoft Windows, macOS.

Before you can start creating a new MySQL database, you need to download MySQL Server.

In this article, we study the different ways to create a MySQL database.

Contents

1. CREATE DATABASE: MySQL syntax example
2. Create a database from the Command Line Client
3. Create a database using MySQL Workbench
4. Create a database using dbForge Studio for MySQL

CREATE DATABASE: MySQL syntax example

The very first way to create a database in MySQL that should be mentioned is by using the CREATE DATABASE statement. This statement creates a database with the specified name. Please remember, that to use it, you need the CREATE privilege for the database.

CREATE DATABASE mydatabase;

Note:
You will get an error if you run the CREATE DATABASE statement without specifying IF NOT EXISTS and the database already exists. So it’s better to use the IF NOT EXISTS clause to prevent errors.

CREATE DATABASE IF NOT EXISTS mydatabase;

After you execute the CREATE DATABASE statement, MySQL will return a message to notify whether the database has been created successfully or not.

Create a database from the Command Line Client

MySQL Command Line Client usually comes with MySQL Server installation pack. It is installed in two versions – with the support for UTF-8 and without it. You can run the console client right from the Start menu.

To create a new MySQL database via MySQL Command Line Client:
1. Run the client.
2. Enter your password.
3. Execute the create database command.

You can learn more about working with MySQL Command-Line Client in our How to Connect to MySQL Server article.

How to create a database in MySQL Workbench

MySQL Workbench is a popular visual database tool for designing, developing, and administering MySQL databases.

How to use MySQL Workbench to create a database:

1. Launch MySQL Workbench and click the + button to open the Setup New Connection wizard.

2. Enter the name for the connection and username, then click Test Connection. Enter the password in the dialog asking for the password.
We enter localhost and root.

3. Click the required connection in the MySQL Connections section of the Workbench start page.

4. In the MySQL Workbench window that opens, click the Create a new schema in the connected server button on the main toolbar. Then enter the schema name, change the character set and collation if necessary, and click Apply.

5. In the Apply SQL Script to Database window that opens, click Apply. Then click Finish.

6. Check that the database has appeared in the Navigator.

Workbench today is one of the most popular professional tools for MySQL database development. However, dbForge Studio for MySQL is its worthy rival boasting, in many aspects, superior functionality. Let’s look at how you can create a new MySQL database with dbForge Studio for MySQL.

How to create a database using dbForge Studio for MySQL

dbForge Studio for MySQL offers a quite simple and intuitive way to create a new database in MySQL. You don’t need to be a professional developer or DBA to get started with dbForge Studio for MySQL.

1. First, you need to create a required connection. Click the New Connection button on the Database Explorer toolbar. Alternatively, go to the Database menu in the main toolbar and click New Connection.

2. In the Database Connection Properties window that opens, provide all the necessary credentials for your connection.

3. The new connection will appear in the Database Explorer. Right-click the connection name and select New Database. Alternatively, go to the Database menu in the main toolbar and click New Database.

4. In the New Database tab that will open, enter the name for your new database, select charset and collation. You can check the script for the database in the lower part of the window. Click Apply Changes, once you’ve configured everything as required.

5. Check that your newly created database has appeared on your MySQL server. To do this, right-click the connection name in the Database Explorer and click Refresh.

After Database Creation

When you have multiple databases in your MySQL server, then to start working with the database you’ve created, use the following statement:

USE database_name;

To create a new table in the database, use the following syntax:

CREATE TABLE [IF NOT EXISTS] table_name(
   column_1_definition,
   column_2_definition,
   …,
   table_constraints
) ENGINE=storage_engine;

In the following picture, you can see how you can create a MySQL table in dbForge Studio for MySQL. The Studio has an automatic code completion feature, so won’t have to type all the code.

To learn more about creating tables in MySQL, please refer to our How to Create Tables using MySQL CREATE TABLE Statement and via GUI Tool for MySQL article.

To delete a table, use the following statement:

DROP DATABASE databasename;

dbForge Studio for MySQL allows deleting a database visually without having to write code. Just right-click the required database in the Database Explorer, then click Delete.

Conclusion

In the article, we have walked through the ways to create a MySQL database and proved that dbForge Studio for MySQL is the best alternative to MySQL Workbench.

As you can see, dbForge Studio for MySQL allows creating a new database quickly and easily in a convenient and modern interface. And dbForge Support Team is always ready to help you in case there are any difficulties. Moreover, we provide a fully-functional 30-day free trial of our products. Download dbForge Studio for MySQL and test all its features to their full capacity. Also, you can watch this video tutorial:

• Author
• Recent Posts

create database in MySQL syntax, create database MySQL, create database MySQL Workbench

See also

How do you create a new database?

How do I create a MySQL database for beginners?

How do I create a new database and database user in MySQL?

How do I create a new database in SQL?