How to Create a new Database: A Beginners Guide to MySQL

mysql if statementMySQL is an open source relational database management system (RDBMS) that provides strong data protection and high performance. MySQL is an easy to use free database solution that is often used for maintaining massive amounts of data in web applications. Due to its low storage requirements and high performance engine, MySQL is preferred by most experienced programmers. You can easily create databases, edit them and manage the records by entering simple commands.

MySQL offers a command line interface for work which means you need to enter individual commands through the command prompt. The following syntax is used to create a new database in MySQL:

CREATE {DATABASE} [IF NOT EXISTS] db_name
   [create_specification] ...

create_specification:
   [DEFAULT] CHARACTER SET [=] charset_name
 | [DEFAULT] COLLATE [=] collation_name

Before we move on to an example to make things more clear, it is better to understand a few concepts of relational database management system that are used in MySQL.

Relational Database- A Database Management System that stores data in the form of related tables is known as a relational database.

Table– A database object comprising of related data entries is known as a table. Data present in a table is displayed in the form of rows and columns.

Data Type- A data type specifies the type of data that a field can hold. It is a form of storage which allows entry of previously defined values that each field can store.

MySQL uses the following common data types:

Text Data Types

CHAR( )      fixed from 0 to 255 characters long

VARCHAR( )   variable from 0 to 255 characters long

Numeric Data Types

INT( )         -2147483648 to 2147483647 SIGNED or 0 to 4294967295 UNSIGNED

FLOAT          small number with a floating decimal point

DOUBLE( , )    large number with a floating decimal point

DECIMAL( , )   DOUBLE stored as a string , allowing for a fixed decimal point

Date Data Types

DATE      YYYY-MM-DD

TIME      HH:MM:SS

Field- Each of the entity present in a table is known as a field. The field in a table is a column that holds information related to every record present in a table.

Row- Each individual record present in a table is identified by a row. A single row of data that shows students name, their roll number, their address and their grade is shown in the below table. Row can also be known by the name of a record.

Column- A column in a table represents a vertical entity that contains all the information related to a specific field. The address column stores addresses of every student listed in the table.

Now that you have understood the basic concepts of a relational database management system, let’s move to create a new database in MySQL.

The following syntax is used to create a new database.

CREATE DATABASE <DATABASENAME>

Here, databasename will be the name of the database which you want to create.

As an example, enter the following command from the command line prompt:

CREATE DATABASE <student>;

This will create a new database named “student”.

Please note the use of semicolon here. Every MySQL command must end with a semi colon.

Now, the database named “student” is currently empty. In order to fill in some data, we will create a table named “marks” in the database “student”. But before we do that, we need to select the created database by entering the following command:

USE student;

MySQL will display a message “Database changed” which means you have entered into the new database student. Now, we can proceed by creating a table.

Enter the following command to create a table named “marks”.

CREATE TABLE marks (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, name CHAR(100), english INT(100), sciene INT(100), maths INT(100);

Here:

id creates a key field which is an integer (as specified by the data type)

NOT NULL indicates this field cannot be left blank

AUTO_INCREMENT will automatically increment the id field by 1 in case a NULL value is entered

PRIMARY KEY indicates this field is the main field of the table that can fetch unique records

name creates a field that can hold text value upto 100 characters long

english creates a field that can hold  values upto 100 characters long as specified by the data type and its value 100

Similarly science and maths creates 2 more fields that can hold values upto 100 characters long as specified by the data type and its value 100

Let us now enter some records in our created table “marks”.

In order to enter students marks, use the following command:

INSERT INTO marks (id, name, english, science, maths) VALUES (NULL, ‘Peter’, ‘80’, ‘92’, ‘87’);

This command will create a record in the marks table inserting data into the respective fields as given in the command.

NULL in the command allows the AUTO_INCREMENT to automatically increase the id field by 1.

Now, we have a table with 5 different fields and 1 row of record.

Let us now insert some more records in our table.

We will enter 3 more commands to insert data of 3 more students namely “Mike”, “Maria” and “Sandra”.

INSERT INTO marks (id, name, english, science, maths) VALUES (NULL, ‘Mike’, ‘76’, ‘62’, ‘85’);
INSERT INTO marks (id, name, english, science, maths) VALUES (NULL, ‘Maria’, ‘52’, ‘88’, ‘77’);
INSERT INTO marks (id, name, english, science, maths) VALUES (NULL, ‘Sandra’, ‘89’, ‘90’, ‘83’);

Now, we have a table with 4 records.

In order to display the records present in your table, run the following command:

SELECT * FROM student;

This command will fetch all the records contained in the table student.

The asterisk sign indicates all the rows. You can fetch specific fields from your created table by replacing * with the field name.

You can use this to create multiple tables for yourself using the CREATE command. Make sure to define the data types properly in order to allow correct data insertion into the table.

Hope you enjoyed this tutorial and are now ready to create your own database using MySQL.