Create a database
Steps to Create Mysql Database
Create a database in two ways
1) Executing a simple SQL query
2) Using forward engineering in MySQL Workbench
CREATE DATABASE is an SQL command to create a database.
Imagine that you need to create a database named "movies". You can do this by running the following SQL command.
CREATE DATABASE movies;
Note: You can also use the CREATE SCHEMA command instead of CREATE DATABASE
Now let's improve our SQL query by adding more parameters and specifications.
IF NOT EXISTS
One MySQL server can have multiple databases. If you're not the only one accessing the same MySQL server, or if you have to deal with multiple databases, there's a chance you might be trying to create a new database with the same name as an existing database. IF NOT EXISTS, you can instruct the MySQL server to check for the existence of a database with a similar name before creating the database.
When IF NOT EXISTS is used, the database is only created if the name does not conflict with the name of an existing database. Without using IF NOT EXISTS MySQL throws an error.
CREATE DATABASE IF NOT EXISTS movies;
Collation and character set
A match is a set of rules used in a comparison. Many people use MySQL to store non-English data. Data is stored in MySQL using a specific character set. The character set can be defined at different levels, namely: server, database, table, and columns.
You need to select collation rules, which in turn depend on the selected character set.
For example, the Latin1 character set uses a
latin1_swedish_ci collation that is Swedish case insensitive.
CREATE DATABASE IF NOT EXISTS movies CHARACTER SET latin1 COLLATE latin1_swedish_ci
When using local languages like Arabic, Chinese, etc., it's a good idea to choose the Unicode (utf-8) character set, which has multiple collations, or just stick with the default utf8-general-ci collation.
You can find a list of all collations and character sets here
You can see the list of existing databases by running the following SQL command.
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;
- "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 define the nature of the data that can be stored in a particular table column.
MySQL has 3 main categories of data types, namely
- 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.
|TINYINT()||-128 to 127, normal
0 to 255, UNSIGNED
|SMALLINT()||-32768 to 32767, typically
0 to 65535 UNSIGNED.
|AVERAGE ()||-8388608 to 8388607, typically
0 to 16777215 UNSIGNED.
|int()||-2147483648 to 2147483647 normal
0 to 4294967295 UNSIGNED.
|BIGINT()||-9223372036854775808 to 9223372036854775807 OK
0 to 18446744073709551615 UNSIGNED.
|FLOAT||A small approximate floating point number.|
|DOUBLE (,)||Large floating point number.|
|DECIMAL( , )||DOUBLE is stored as a string that accepts a fixed decimal point. Choice for storing currency values.|
Text data types
As the name of the data type category suggests, they are used to store text values. Always make sure that your text data does not exceed the maximum length.
|char()||Fixed section 0 to 255 characters long.|
|varchar()||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.|
|MEDIUM TEXT||A string with a maximum length of 16777215 characters.|
|MEDIUMBLOB||A string with a maximum length of 16777215 characters.|
|LONG TEXT||A string with a maximum length of 4294967295 characters.|
|LONGBLOB||A string with a maximum length of 4294967295 characters.|
Apart from the above, there are some other data types in MySQL.
|ENUM||To store a text value selected from a list of predefined text values|
|KIT||This is also used to store text values selected from a list of predefined text values. May have multiple meanings.|
|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||Like VARCHAR, the difference is that the texts are stored in binary format.|
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 ;
- 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 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
5. The next screen shows a summary of the objects in our EER diagram. Our MyFlix database has 5 tables. Leave the default selection and click Next.
6.. The window shown below will appear. This window allows you to preview the SQL script for creating our database. We can save the scripts to a *.sql file" or copy the scripts to the clipboard. Click next button
7. The window shown below appears after the database has been successfully created on the selected MySQL server instance.
- Creating a database involves translating a logical database design model into a physical database.
- MySQL supports several data types for numeric values, dates, and string values.
- The CREATE DATABASE command is used to create a database
- The CREATE TABLE command is used to create tables in a database.
- MySQL Workbench supports direct engineering, which involves automatically generating SQL scripts from a logical database model that can be executed to create a physical database.
The database along with dummy data is attached. We will use this database for all our further lessons. Simple DB import into MySQL Workbench to get started