How to Create Database in Mysql

Database creation is a basic need for a web application. In this tutorial, we will discuss how to create database in MySQL. We will also discuss the steps to create Database in MySQL Workbench. There are many database management systems such as Microsoft SQL Server, Oracle 10g, PostgreSQL and MySQL. MySQL is an open source database management system which is used for managing databases in websites and applications.

This post is about how to create a database and table in Mysql and SQL server and related video tutorials. I created this blog mainly to share the knowledge about the different types of databases, but I find that people really don’t know about Database. So I decided to create this blog where you can learn basics like how to create a Database, Table and many more stuff related to Databases.

Table of Contents

Introduction to the MySQL CREATE DATABASE statement

To create a new database in MySQL, you use the CREATE DATABASE statement with the following syntax:

CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name]Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, specify name of the database after the the CREATE DATABASE keywords. The database name must be unique within a MySQL server instance. If you attempt to create a database with a name that already exists, MySQL will issue an error.
Second, use the IF NOT EXISTS option to conditionally create a database if it doesn’t exist.
Third, specify the character set and collation for the new database. If you skip the CHARACTER SET and COLLATE clauses, MySQL will the default character set and collation for the new database.

Creating a new database using the mysql client tool

To create a new database via the mysql client tool, you follow these steps:

First, log in to the MySQL Server using a user account that has the CREATE DATABASE privilege:

mysql -u root -pCode language: SQL (Structured Query Language) (sql)

It’ll prompt you for entering a password. To authenticate, you need to type the password for the root user account and press the Enter key.

Next, display the current databases available on the server using the SHOW DATABASES statement. This step is optional.

SHOW DATABASES;

Output:

+--------------------+
| Database           |
+--------------------+
| classicmodels      |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)Code language: JavaScript (javascript)

Then, issue the CREATE DATABASE command with a database name e.g., testdb and press Enter:

CREATE DATABASE testdb;

It’ll return the following:

Query OK, 1 row affected (0.02 sec)Code language: CSS (css)

After that, use the SHOW CREATE DATABASE command to review the created database:

SHOW CREATE DATABASE testdb;Code language: SQL (Structured Query Language) (sql)

MySQL returns the database name and the character set and collation of the database:

+----------+----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                                                  |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
| testdb   | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)Code language: JavaScript (javascript)

Finally, select the newly created database to work with by using the USE statement:

USE testdb;Code language: SQL (Structured Query Language) (sql)

Output:

Database changed

Now, you can start creating tables and other databases objects within the testdb database.

To quit the mysql program, type exit command:

exitCode language: PHP (php)

Output:

Bye

Creating a new database using MySQL Workbench

To create a new database using the MySQL Workbench, you follow these steps:

First, launch the MySQL Workbench and click the setup new connection button as shown in the following screenshot:

Second, type the name for the connection and click the Test Connection button.

MySQL Workbench displays a dialog asking for the password of the root user:

You need to (1) type the password for the root user, (2) check the Save password in vault, and (3) click OK button.

Third, double-click the connection name Local to connect to the MySQL Server.

MySQL Workbench opens the following window which consists of four parts: Navigator, Query, Information, and Output.

Fourth, click the create a new schema in the connected server button from the toolbar:

In MySQL, the schema is the synonym for the database. Creating a new schema also means creating a new database.

Fifth, the following window is open. You need to (1) enter the schema name, (2) change the character set and collation if necessary, and click the Apply button:

Sixth, MySQL Workbench opens the following window that displays the SQL script which will be executed. Note that the CREATE SCHEMA statement command has the same effect as the CREATE DATABASE statement.

If everything is fine, you will see the new database created and showed in the schemas tab of the Navigator section.

Seventh, to select the testdb2 database, (1) right-click the database name and (2) choose Set as Default Schema menu item:

The testdb2 node is open as shown in the following screenshot.

Now, you can work with testdb2 from the MySQL Workbench.

How to Create Table in MySQL

CREATE TABLE command is used to create tables in a database

MySQL Create Database, Tables, Data Types

Tables can be created using CREATE TABLE statement and it actually has the following syntax.

CREATE  TABLE [IF NOT EXISTS] `TableName` (`fieldname` dataType [optional parameters]) ENGINE = storage Engine;

HERE

“CREATE TABLE” is the one responsible for the creation of the table in the database.
“[IF NOT EXISTS]” is optional and only create the table if no matching table name is found.
“`fieldName`” is the name of the field and “data Type” defines the nature of the data to be stored in the field.
“[optional parameters]” additional information about a field such as ” AUTO_INCREMENT” , NOT NULL etc.

MySQL Create Table Example

Below is a MySQL example to create a table in database:

CREATE  TABLE IF NOT EXISTS `MyFlixDB`.`Members` (
  `membership_number` INT  AUTOINCREMENT ,
  `full_names` VARCHAR(150) NOT NULL ,
  `gender` VARCHAR(6) ,
  `date_of_birth` DATE ,
  `physical_address` VARCHAR(255) ,
  `postal_address` VARCHAR(255) ,
  `contact_number` VARCHAR(75) ,
  `email` VARCHAR(255) ,
  PRIMARY KEY (`membership_number`) )
ENGINE = InnoDB;

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.

DATA TYPES

Data types define the nature of the data that can be stored in a particular column of a table

MySQL has 3 main categories of data types namely

Numeric,
Text
Date/time.

Numeric Data types

Numeric data types are used to store numeric values. It is very important to make sure range of your data is between lower and upper boundaries of numeric data types.

TINYINT( )	-128 to 127 normal 0 to 255 UNSIGNED.
SMALLINT( )	-32768 to 32767 normal 0 to 65535 UNSIGNED.
MEDIUMINT( )	-8388608 to 8388607 normal 0 to 16777215 UNSIGNED.
INT( )	-2147483648 to 2147483647 normal 0 to 4294967295 UNSIGNED.
BIGINT( )	-9223372036854775808 to 9223372036854775807 normal 0 to 18446744073709551615 UNSIGNED.
FLOAT	A small approximate number with a floating decimal point.
DOUBLE( , )	A large number with a floating decimal point.
DECIMAL( , )	A DOUBLE stored as a string , allowing for a fixed decimal point. Choice for storing currency values.

Text Data Types

As data type category name implies these are used to store text values. Always make sure you length of your textual data do not exceed maximum lengths.

CHAR( )	A fixed section from 0 to 255 characters long.
VARCHAR( )	A variable section from 0 to 255 characters long.
TINYTEXT	A string with a maximum length of 255 characters.
TEXT	A string with a maximum length of 65535 characters.
BLOB	A string with a maximum length of 65535 characters.
MEDIUMTEXT	A string with a maximum length of 16777215 characters.
MEDIUMBLOB	A string with a maximum length of 16777215 characters.
LONGTEXT	A string with a maximum length of 4294967295 characters.
LONGBLOB	A string with a maximum length of 4294967295 characters.

Date / Time

DATE	YYYY-MM-DD
DATETIME	YYYY-MM-DD HH:MM:SS
TIMESTAMP	YYYYMMDDHHMMSS
TIME	HH:MM:SS

Apart from above there are some other data types in MySQL.

ENUM	To store text value chosen from a list of predefined text values
SET	This is also used for storing text values chosen from a list of predefined text values. It can have multiple values.
BOOL	Synonym for TINYINT(1), used to store Boolean values
BINARY	Similar to CHAR, difference is texts are stored in binary format.
VARBINARY	Similar to VARCHAR, difference is texts are stored in binary format.

Now let’s see a query for creating a table which has data of all data types. Study it and identify how each data type is defined in the below create table MySQL example.

CREATE TABLE`all_data_types` (
    `varchar` VARCHAR( 20 )  ,
    `tinyint` TINYINT  ,
    `text` TEXT  ,
    `date` DATE  ,
    `smallint` SMALLINT  ,
    `mediumint` MEDIUMINT  ,
    `int` INT  ,
    `bigint` BIGINT  ,
    `float` FLOAT( 10, 2 )  ,
    `double` DOUBLE  ,
    `decimal` DECIMAL( 10, 2 )  ,
    `datetime` DATETIME  ,
    `timestamp` TIMESTAMP  ,
    `time` TIME  ,
    `year` YEAR  ,
    `char` CHAR( 10 )  ,
    `tinyblob` TINYBLOB  ,
    `tinytext` TINYTEXT  ,
    `blob` BLOB  ,
    `mediumblob` MEDIUMBLOB  ,
    `mediumtext` MEDIUMTEXT  ,
    `longblob` LONGBLOB  ,
    `longtext` LONGTEXT  ,
    `enum` ENUM( '1', '2', '3' )  ,
    `set` SET( '1', '2', '3' )  ,
    `bool` BOOL  ,
    `binary` BINARY( 20 )  ,
    `varbinary` VARBINARY( 20 )
) ENGINE= MYISAM ;

Best practices

Use upper case letters for SQL keywords i.e. “DROP SCHEMA IF EXISTS `MyFlixDB`;”
End all your SQL commands using semi colons.
Avoid using spaces in schema, table and field names. Use underscores instead to separate schema, table or field names.

How to create MySQL workbench ER diagram forward engineering

MySQL workbench has utilities that support forward engineering. Forward engineering is a technical term is to describe the process of translating a logical model into a physical implement automatically.

We created an ER diagram on our ER modeling tutorial. We will now use that ER model to generate the SQL scripts that will create our database.

Creating the MyFlix database from the MyFlix ER modelhttps://e26ae130252dd4a5260217ca13d6a5a9.safeframe.googlesyndication.com/safeframe/1-0-38/html/container.html

Step 1) Open ER model of MyFlix database

Open the ER model of MyFlix database that you created in earlier tutorial.

Step 2) Select forward engineer

Click on the database menu. Select forward engineer

Step 3) Connection options

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

Step 4) Select the options shown below

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

Step 5) Keep the selections default and click Next

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.

Step 6) Review the SQL script

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

Step 7) Commit Progress

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

Conclusion

Creating a database in MySQL is quite simple, especially if you know how to do it. There are three ways to create databases in MySQL. They include: creating the database and the tables by using SQL and workbench and thirdly by using command line tool.

0 0 votes

Article Rating