--- description: You can create database in two ways, by executing a simple SQL query or by using forward engineering in MySQL workbench. Creating Tables MySQL, Data types title: How to Create Database in MySQL (Create MySQL Tables) image: https://www.guru99.com/images/create-a-database.png --- [Skip to content](#main) ## Steps to Create Database in MySQL **Create Database in two ways** 1) By executing a simple SQL query 2) By using forward engineering in MySQL Workbench As [SQL beginner](https://www.guru99.com/sql.html), let’s look into the query method first. ## How to Create Database in MySQL Here is how to create a database in MySQL: CREATE DATABASE is the SQL command used for creating a database in MySQL. Imagine you need to create a database with name “movies”. You can create a database in [MySQL](https://www.guru99.com/mysql-tutorial.html) by executing following SQL command. CREATE DATABASE movies; **Note: you can also use the command CREATE SCHEMA instead of CREATE DATABASE** Now let’s improve our SQL query adding more parameters and specifications. ## IF NOT EXISTS A single MySQL server could have multiple databases. If you are not the only one accessing the same MySQL server or if you have to deal with 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. CREATE DATABASE IF NOT EXISTS movies; ## Collation and Character Set **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 Latin1 character set uses the `latin1_swedish_ci` collation which is the Swedish case insensitive order. CREATE DATABASE IF NOT EXISTS movies CHARACTER SET latin1 COLLATE latin1_swedish_ci 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 find the list of all collations and character sets [here](https://dev.mysql.com/doc/refman/8.0/en/charset-charsets.html) You can see list of existing databases by running following SQL command. SHOW DATABASES ## How to Create Table in MySQL CREATE TABLE command is used to create tables in a database [](https://www.guru99.com/images/CreateTable%282%29.jpg) 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. ### RELATED ARTICLES * [ MySQL UNION – Complete Tutorial ](https://www.guru99.com/unions.html "MySQL UNION – Complete Tutorial") * [ MySQL SubQuery Tutorial with Examples ](https://www.guru99.com/sub-queries.html "MySQL SubQuery Tutorial with Examples") * [ ORDER BY in MySQL: DESC & ASC Query with EXAMPLE ](https://www.guru99.com/order-by-desc-and-asc.html "ORDER BY in MySQL: DESC & ASC Query with EXAMPLE") * [ 9 BEST Online SQL Compiler and Editors (2026) ](https://www.guru99.com/best-online-sql-compiler-editors.html "9 BEST Online SQL Compiler and Editors (2026)") ## 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 1. Numeric, 2. Text 3. 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 normal0 to 255 UNSIGNED. | | ------------ | ----------------------------------------------------------------------------------------------------- | | SMALLINT( ) | \-32768 to 32767 normal0 to 65535 UNSIGNED. | | MEDIUMINT( ) | \-8388608 to 8388607 normal0 to 16777215 UNSIGNED. | | INT( ) | \-2147483648 to 2147483647 normal0 to 4294967295 UNSIGNED. | | BIGINT( ) | \-9223372036854775808 to 9223372036854775807 normal0 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](https://www.guru99.com/introduction-to-mysql-workbench.html) 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](https://www.guru99.com/er-diagram-tutorial-dbms.html) on our [ER modeling tutorial](https://www.guru99.com/er-modeling.html). 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 model** **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 [](https://www.guru99.com/images/ForwardEngineering.png) **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 [](https://www.guru99.com/images/Wizard6.png) **Step 4)** Select the options shown below Select the options shown below in the wizard that appears. Click next [](https://www.guru99.com/images/Wizard3.png) **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. [](https://www.guru99.com/images/Wizard4.png) **Step 6)** Review the SQL script The window shown below appears. This window allows you to preview the [SQL script](https://www.guru99.com/sap-hana-sql-script.html) to create our database. We can save the scripts to a \*.sql” file or copy the scripts to the clipboard. Click on next button [](https://www.guru99.com/images/Wizard5.png) **Step 7)** Commit Progress The window shown below appears after successfully creating the database on the selected MySQL server instance. [](https://www.guru99.com/images/Wizard4%282%29.png) * Creating a database involves translating the logical database design model into the physical database. * MySQL supports a number of data types for numeric, dates and strings values. * CREATE DATABASE command is used to create a database * CREATE TABLE command is used to create tables in a database * MySQL workbench supports forward engineering which involves automatically generating SQL scripts from the logical database model that can be executed to create the physical database 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 [Click Here To Download MyFlixDB](https://drive.google.com/uc?export=download&id=0B%5FvqvT0ovzHccjhtdGlrZ0MtZ0k) #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/create-a-database.png","url":"https://www.guru99.com/images/create-a-database.png","width":"416","height":"250","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/how-to-create-a-database.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql","name":"SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/how-to-create-a-database.html","name":"How to Create Database in MySQL (Create MySQL Tables)"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/how-to-create-a-database.html#webpage","url":"https://www.guru99.com/how-to-create-a-database.html","name":"How to Create Database in MySQL (Create MySQL Tables)","dateModified":"2024-07-17T18:46:21+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/create-a-database.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/how-to-create-a-database.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen","description":"I'm Marcus Allen, an SQL and Data Warehousing Consultant with over a decade of experience in designing and optimizing large-scale data solutions.","url":"https://www.guru99.com/author/marcus","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/marcus-allen-author.png","url":"https://www.guru99.com/images/marcus-allen-author.png","caption":"Marcus Allen","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"image":{"@id":"https://www.guru99.com/images/create-a-database.png"},"headline":"How to Create Database in MySQL (Create MySQL Tables)","description":"You can create database in two ways, by executing a simple SQL query or by using forward engineering in MySQL workbench. Creating Tables MySQL, Data types","keywords":"sql","@type":"Article","author":{"@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen"},"dateModified":"2024-07-17T18:46:21+05:30","name":"How to Create Database in MySQL (Create MySQL Tables)","articleSection":"SQL","subjectOf":[{"@type":"HowTo","name":"How to create MySQL workbench ER diagram forward engineering","description":"MySQL workbench has utilities that support forward engineering. Forward engineering is a technical term to describe the process of translating a logical model into a physical implement automatically.","step":[{"@type":"HowToStep","name":"Step 1) Open ER model of MyFlix database","text":"In first step Open the ER model of MyFlix database that you created in earlier tutorial.","url":"https://www.guru99.com/how-to-create-a-database.html#step1"},{"@type":"HowToStep","name":"Step 2) Select forward engineer","text":"Now Click on the database menu. Select forward engineer","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/ForwardEngineering.png"},"url":"https://www.guru99.com/how-to-create-a-database.html#step2"},{"@type":"HowToStep","name":"Step 3) Connection options","text":" In The next window, allows you to connect to an instance of MySQL server. Click on the stored connection drop down list and select the local host. Click Execute","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/Wizard6.png"},"url":"https://www.guru99.com/how-to-create-a-database.html#step3"},{"@type":"HowToStep","name":"Step 4) Select the options shown below","text":"Now Select the options shown below in the wizard that appears. Click next","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/Wizard3.png"},"url":"https://www.guru99.com/how-to-create-a-database.html#step4"},{"@type":"HowToStep","name":"Step 5) Keep the selections default and click Next","text":"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.","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/Wizard4.png"},"url":"https://www.guru99.com/how-to-create-a-database.html#step5"},{"@type":"HowToStep","name":"Step 6) Review the SQL script","text":"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","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/Wizard5.png"},"url":"https://www.guru99.com/how-to-create-a-database.html#step6"},{"@type":"HowToStep","name":"Step 7) Commit Progress","text":"The window shown below appears after successfully creating the database on the selected MySQL server instance.","image":{"@type":"ImageObject","url":"https://cdn.guru99.com/images/Wizard4(2).png"},"url":"https://www.guru99.com/how-to-create-a-database.html#step7"}]}],"@id":"https://www.guru99.com/how-to-create-a-database.html#schema-24115","isPartOf":{"@id":"https://www.guru99.com/how-to-create-a-database.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/how-to-create-a-database.html#webpage"}}]} ```