Create a database

Creating MySQL tables

Tables can be created with the 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 who is responsible for creating 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 that will be stored in the field.
• "[optional parameters]" additional information about the field, such as "AUTO_INCREMENT", NOT NULL, etc.
  

  Table creation example:-

  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 are the MySQL data types. You can use any of them depending on your needs. You should always try not to underestimate or overestimate the 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 table column.

MySQL has 3 main categories of data types, namely

1.   Numerical,
2.   Text
3.   Date Time.

Numeric Data Types

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

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

Now let's look at an example SQL query to create a table that has data of all data types. Study it and determine how each data type is defined.

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 practics

• Use capital letters for SQL keywords, such as "DROP SCHEMA IF EXISTS` MyFlixDB`; "
• End all your SQL commands using semicolons.
• Avoid spaces in schema, table, and field names. Instead, use an underscore to separate schema, table, or field names.

MySQL workbench ER diagram forward engineering

MySQL Workbench has utilities that support forward engineering. Forward engineering is a technical term for the process of automatically translating a logical model into a physical tool .

We have created an ER diagram in our ER modeling guide. We will now use this ER model to generate the SQL scripts that will create our database.

Creating a MyFlix Database from a MyFlix ER Model

1. Open the MyFlix database ER model you created in the previous tutorial.

2. Click on the database menu. Choose a forward engineer

3. The next window allows you to connect to the MySQL server instance. Click on the saved netlist and select localhost. Click Run

4. Select the options shown below in the wizard that opens. Click "Next