How do i create a schema in mysql?

Now that we have MySQL installed and we've configured MySQL workbench. We can start creating what we need to do in order to go through the course. So if you open up MySQL workbench and double click on the SQL connection that you made. I'm going to use the new connection that I created in the last guide. double click on the connection.

On the left-hand side, we already walked through the server status. We're going to walk through how we can create a database schema. A schema is a workspace for our database that allows us to group all of our data together. A database is going to be made up of all kinds of tables that are in different places. We are able to store data that relates to other tables in our schema. It's a way where we can define where our tables exist and how they relate to one another. You can see the schema’s on the left-hand side. There's some sample data here; this is from a few test databases that I have on my system. We're going to go through what we need to do in order to create one from scratch.

So if you come up here to the top left-hand corner of MySQL workbench, you can see that if I hover over the button it says “create a new schema in the connected server.”

Click on the button and it will pop open this dialog box in a new tab. Now fill in the schema name. I am going to name this is the devcamp_sql_course_schema. You can name yours anything you like. You don't have to make any other changes besides giving the schema a name.

Now if I click on “apply” this is going to pop up a box that says “Review the SQL script to be applied to the database”. This is one of the really nice features of using a tool like MySQL workbench. if you're not that familiar SQL, MySQL workbench will actually create a decent amount the SQL code for you. It will then show you exactly what's been created. Without this, we'd have to write all of this from scratch in the terminal. If that's not something that you want to do then this is a great option.

So right here it's a single command on a single line and it's going to say ’CREATE SCHEMA devcamp_sql_course_schema’ ;. So what this is going to do is give us our names pacing and our grouping that can store all of our tables inside. Press “apply” and it says “SQL statement was successfully applied to the database.”

Doing it this way would be the exact same as if we went in the terminal, opened up a connection to MySQL, and ran then that schema creator. So that's I want you to kind of keep in mind that all of these things whether you see someone do it in the command line or you see it in MySQL workbench, they're pretty much all the same.

MySQL workbench is simply giving commands to the system so that it can understand what we're wanting to do. In this case, we want it to create a database schema for us. So now when I click close, you can see the schema here on the left-hand side. We can now see the "devcamp_sql_course_schema" schema. If your schema name is too long you can grab the bar next to it and drag it out.

Now if I click on the ‘devcamp_sql_course_schema’ it will show tables, views, stored procedures, and functions. If I click on ‘table’ there won't be any tables because we haven't created those yet. We're going to do that in the next few guides! If I click on one of these other schemas I have on my computer. For instance, If I click on “sample_two” and then click on the “tables” it will drop down all the tables for that schema. Such as addresses, friends, guides, and users. They all have data inside of them so we could see the columns, indexes, foreign keys, and anything like that.

So this is a really helpful tool for being able to navigate inside of a database and see the structure. We're going to build all of this from scratch as we go through the course. So that is how you can build a database schema and in the next guide, we're going to talk about how we can actually go through and create our first table.

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, and 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 I create a new schema?

What is a schema in MySQL?

How do I get MySQL schema?

What is the difference between create schema and create database?