Create a Database in MySQL

Categories: Database // Tags: .
Email this to someonePrint this pageShare on Google+0Share on Facebook0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0Pin on Pinterest5Share on StumbleUpon1

There are two ways to create a database in MySQL

1) By executing a simple SQL query

2) By using forward engineering in MySQL Workbench

In this tutorial we will learn both of the ways to create the database in MySQL.

  • Create a Database by query method:

Lets imagine we need to create a database with name “Thatsjavainfo”. You can do it by executing following SQL command.

we can also use the command CREATE SCHEMA instead of CREATE DATABASE.

A single MySQL server can have multiple databases.There is a probability of attempting to create a new database with name of an existing database .  IF NOT EXISTS let you to instruct MySQL server to check the existence of a database with a similar name prior to creating database.

When IF NOT EXISTS is used database is created only if given name does not conflict with an existing database’s name. Without the use of IF NOT EXISTS MySQL throws an error.

Collation is set of rules used in comparison. Many people use MySQL to store data other than English. Data is stored in MySQL using a specific character set. The character set can be defined at different levels viz, server , database , table and columns.

You need to select the rules of collation which in turn depend on the character set chosen.

For instance, the Latine1 character set uses the latin1_swedish_ci collation which is the Swedish case insensitive order.

The best practice while using local languages like Arabic , Chinese etc is to select Unicode (utf-8) character set which has several collations or just stick to default collation utf8-general-ci.

You can see list of existing databases by running following SQL command.

Create Table Example

Now let’s see what the MySQL’s data types are. You can use any of them depending on your need. You should always try to not to underestimate or overestimate potential range of data when creating a database.

  • Create a Database by workbench ER diagram:

1.       Open the ER model of MyFlix database that you created in earlier tutorial. Click Here To Download MyFlixDB

2.       Click on the database menu. Select forward engineer

Create Database in MySQL
Create Database in MySQL

 

3.       The next window,  allows you to connect to an instance of MySQL server. Click on the stored connection drop down list and select local host. Click Execute

Create a Database in MySQL
Create a Database in MySQL

4.       Select the options shown below in the wizard that appears. Click next

Create a Database in MySQL
Create a Database in MySQL

5.       The next screen shows the summary of objects in our EER diagram. Our MyFlix DB has 5 tables.  Keep the selections default and click Next.

6..       The window shown below appears. This window allows you to preview the SQL script to create our database. We can save the scripts to a *.sql” file or copy the scripts to the clipboard. Click on next button

  1. The window shown below appears after successfully creating the database on the selected MySQL server instance.

The Database along with Dummy Data is attached. We will be using this DB for all our further tutorials. Simple import the DB in MySQL Workbench to get started

 

Email this to someonePrint this pageShare on Google+0Share on Facebook0Tweet about this on TwitterShare on LinkedIn0Share on Reddit0Pin on Pinterest5Share on StumbleUpon1

About Team

Browse Archived Articles by Team