--- 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/how-to-create-database-in-mysql.png --- [Skip to content](#main) **⚡ Smart Summary** Creating a database in MySQL follows two proven paths: executing a CREATE DATABASE statement, or generating physical schemas from an ER model through MySQL Workbench forward engineering. Both approaches produce identical, production-ready tables. * 🗄️ **Database Creation:** Run CREATE DATABASE movies; or CREATE SCHEMA, which MySQL treats as a synonym. * 🛡️ **Safe Re-Runs:** Add IF NOT EXISTS so a repeated script skips creation instead of raising a duplicate-name error. * 🌐 **Character Set:** Choose utf8mb4 with utf8mb4\_0900\_ai\_ci for multilingual data; latin1 with latin1\_swedish\_ci suits legacy English-only schemas. * 🧱 **Table Creation:** CREATE TABLE defines each field name, data type, PRIMARY KEY, and the storage engine, normally InnoDB. * 🔢 **Data Types:** Column types fall into numeric, text, and date or time families, plus ENUM, SET, BOOL, and binary variants. * ⚙️ **Forward Engineering:** MySQL Workbench converts an approved ER model into executable SQL scripts and commits them to a live server. [ Read More ](javascript:void%280%29;) ![](https://www.guru99.com/images/how-to-create-database-in-mysql.png) ## Steps to Create Database in MySQL A database in MySQL is a named container for tables, views, and related objects. You can create one 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 the name “movies”. You can create a database in [MySQL](https://www.guru99.com/mysql-tutorial.html) by executing the following SQL command. CREATE DATABASE movies; **Note: You can also use the command CREATE SCHEMA instead of CREATE DATABASE.** The statement works only once. Running it again throws an error, so let’s improve the query with more parameters. ## IF NOT EXISTS A single MySQL server can hold many databases. When several people share that server, you may try to create a database with the name of an existing one. **IF NOT EXISTS** instructs the MySQL server to check for a database with the same name before creating it. The database is created only if the name is free; without this clause, MySQL throws an error. CREATE DATABASE IF NOT EXISTS movies; Once the database name is safe, the next decision is how MySQL should store and compare the text inside it. ## Collation and Character Set A **character set** decides which characters a column can store, while a **collation** is the set of **rules used in comparison** and sorting. Both can be defined at four levels: server, database, table, and column. The collation you choose depends on the character set. 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; For local languages such as Arabic or Chinese, select the Unicode `utf8mb4` character set, which stores every Unicode character, including emoji. In MySQL 8.0, `utf8mb4` is the default character set and `utf8mb4_0900_ai_ci` the default collation. CREATE DATABASE IF NOT EXISTS movies CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_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 the list of existing databases by running the following SQL command. SHOW DATABASES; With the database in place, you can now add the tables that will actually hold the records. ## How to Create Table in MySQL The CREATE TABLE command is used to create tables in a database. [](https://www.guru99.com/images/CreateTable%282%29.jpg) As the diagram shows, every table belongs to one database. Tables are created with the **CREATE TABLE** statement, which has the following syntax. CREATE TABLE [IF NOT EXISTS] `TableName` (`fieldname` dataType [optional parameters]) ENGINE = storage Engine; **HERE** * “CREATE TABLE” is responsible for the creation of the table in the database. * “\[IF NOT EXISTS\]” is optional and only creates 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 stored in it. * “\[optional parameters\]” is extra information about a field, such as “AUTO\_INCREMENT” or NOT NULL. * “storage Engine” manages the table, normally InnoDB, which supports transactions and foreign keys. ### RELATED ARTICLES * [MySQL LIMIT & OFFSET with Examples ](https://www.guru99.com/limit.html "MySQL LIMIT & OFFSET with Examples") * [MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example ](https://www.guru99.com/where-clause.html "MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example") * [SQL vs MySQL – Difference Between Them ](https://www.guru99.com/sql-vs-mysql.html "SQL vs MySQL – Difference Between Them") * [What is SQL? Full Form & Basics Tutorial ](https://www.guru99.com/what-is-sql.html "What is SQL? Full Form & Basics Tutorial") ## MySQL Create Table Example Below is a MySQL example to create a table in a database: CREATE TABLE IF NOT EXISTS `MyFlixDB`.`Members` ( `membership_number` INT AUTO_INCREMENT , `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; **Note:** the correct MySQL keyword is `AUTO_INCREMENT` with an underscore. `AUTOINCREMENT` belongs to SQLite and raises a syntax error in MySQL. Every column carries a data type, so choose carefully: neither underestimate nor overestimate the range of data you expect. ## MySQL 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 the range of your data is between the lower and upper boundaries of the numeric data type you pick. | 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 the data type category name implies, these are used to store text values. Always make sure the length of your textual data does not exceed the maximum length of the column. | 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 and Time Data Types Date and time data types store calendar and clock values in a fixed format, which keeps sorting and comparison reliable. | DATE | YYYY-MM-DD | | --------- | ------------------- | | DATETIME | YYYY-MM-DD HH:MM:SS | | TIMESTAMP | YYYYMMDDHHMMSS | | TIME | HH:MM:SS | ### Other Data Types Apart from the above, there are some other data types in MySQL. | ENUM | To store a 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; the difference is that texts are stored in binary format. | | VARBINARY | Similar to VARCHAR; the difference is that texts are stored in binary format. | Now let’s see a query for creating a table which uses all the 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 for Creating a MySQL Database A few conventions keep your scripts readable: * Use upper case letters for SQL keywords, i.e. “DROP SCHEMA IF EXISTS \`MyFlixDB\`;” * End all your SQL commands using semicolons. * Avoid using spaces in schema, table, and field names. Use underscores instead to separate schema, table, or field names. * Prefer InnoDB over MyISAM for new tables, because InnoDB supports transactions, row-level locking, and foreign keys. The query method is complete. The second route reaches the same database from a visual model instead of hand-written SQL. ## 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 the technical term that describes the process of **translating a logical model into a physical implementation automatically**. We created an [ER diagram](https://www.guru99.com/er-diagram-tutorial-dbms.html) in our [ER modeling](https://www.guru99.com/er-modeling.html) lesson. 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 the ER model of the MyFlix database** that you created earlier. **Step 2) Select forward engineer** Click on the Database menu and 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 below previews the SQL script that creates our database. Save the script to a \*.sql file or copy it to the clipboard, then click Next. [](https://www.guru99.com/images/Wizard5.png) **Step 7) Commit progress** The window below appears once the database is created on the selected MySQL server instance. The tables from the ER model now exist physically on the server. [](https://www.guru99.com/images/Wizard4%282%29.png) The database along with dummy data is attached. We will use this DB in the lessons that follow. Simply import it in MySQL Workbench to get started. [Click Here To Download MyFlixDB](https://drive.google.com/uc?export=download&id=0B%5FvqvT0ovzHccjhtdGlrZ0MtZ0k) ## FAQs 🔁 What is the difference between CREATE DATABASE and CREATE SCHEMA? There is no difference in MySQL. CREATE SCHEMA is an alias for CREATE DATABASE, and both build the same object. In other systems, such as Oracle, a schema and a database remain separate concepts. 🗑️ How do you delete a MySQL database that was created by mistake? Run DROP DATABASE IF EXISTS movies; from the client or from [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html). The command permanently deletes the database and every table inside it, so take a backup first. 🧭 Which privileges are required to create a database in MySQL? The account needs the CREATE privilege at the global level. Administrators grant it with GRANT CREATE ON \*.\* TO ‘user’@’localhost’;. Without that privilege, the server returns an access denied error. 🤖 Can AI tools write CREATE TABLE statements for you? Yes. AI assistants turn a plain description of your entities into CREATE TABLE scripts with keys and data types. Always review the generated types, lengths, and constraints before running the script on a production server. 🧠 How does AI help with database schema design? AI features in modern database clients read your [ER diagram](https://www.guru99.com/er-diagram-tutorial-dbms.html) or query history and suggest normalization fixes, index candidates, and data type corrections. The suggestions speed up design reviews, but a human still approves every change. #### 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/how-to-create-database-in-mysql.png","url":"https://www.guru99.com/images/how-to-create-database-in-mysql.png","width":"700","height":"250","caption":"How to Create Database in MySQL","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":"2026-07-14T12:36:04+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/how-to-create-database-in-mysql.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"}},{"articleSection":"SQL","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","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen"},"dateModified":"2026-07-14T12:36:04+05:30","image":{"@id":"https://www.guru99.com/images/how-to-create-database-in-mysql.png"},"copyrightYear":"2026","name":"How to Create Database in MySQL (Create MySQL Tables)","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"}]},{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between CREATE DATABASE and CREATE SCHEMA?","acceptedAnswer":{"@type":"Answer","text":"There is no difference in MySQL. CREATE SCHEMA is an alias for CREATE DATABASE, and both build the same object. In other systems, such as Oracle, a schema and a database remain separate concepts."}},{"@type":"Question","name":"How do you delete a MySQL database that was created by mistake?","acceptedAnswer":{"@type":"Answer","text":"Run DROP DATABASE IF EXISTS movies; from the client or from MySQL Workbench. The command permanently deletes the database and every table inside it, so take a backup first."}},{"@type":"Question","name":"Which privileges are required to create a database in MySQL?","acceptedAnswer":{"@type":"Answer","text":"The account needs the CREATE privilege at the global level. Administrators grant it with GRANT CREATE ON *.* TO 'user'@'localhost';. Without that privilege, the server returns an access denied error."}},{"@type":"Question","name":"Can AI tools write CREATE TABLE statements for you?","acceptedAnswer":{"@type":"Answer","text":"Yes. AI assistants turn a plain description of your entities into CREATE TABLE scripts with keys and data types. Always review the generated types, lengths, and constraints before running the script on a production server."}},{"@type":"Question","name":"How does AI help with database schema design?","acceptedAnswer":{"@type":"Answer","text":"AI features in modern database clients read your ER diagram or query history and suggest normalization fixes, index candidates, and data type corrections. The suggestions speed up design reviews, but a human still approves every change."}}]}],"@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"}}]} ```