How to Create a Data Base

By | May 16, 2020
How to Create a Database

The database used to hold or collect data in a well-organized manner so the operations like insertion, update, deletion and retrieval of data become easy. These days we often use a Relational database to store data because it uses a table-like structure to store data in rows and columns and to create a relational database we use Structured Query Language(SQL). There are many SQL programs we have such as Oracle, MySQL, SQLite, etc. which can create a database.

Here in this article, we have provided a piece of brief information on how can you use any Structured Query Language to Create a database. Here specifically we have used MySQL as a tool to create the database.

Vamware

How to create a DataBase

1. Create Command

To create a Database in MySQL we use the CREATE DATABASE command followed by the database name.

Suppose if you want to create a database for music the command you will write would be

CREATE DATABASE music;

2. If NOT EXISTS:

Before creating a new database it is a good practice to use IF NOT EXISTS command along with CREATE DATABASE command. If we try to create a database that has the same name as an existing database present in the Database, then MySQL could through an error so we use the IF NOT EXISTS command so we do not get any error. By using the IF NOT EXISTS CREATE DATABASE command we create a new database if the same name does not exist in the database or else it uses the existing one and does not throw an error.

Example:

CREATE DATABASE IF NOT EXISTS music;

3. USE command:

Once we created a database then we need to specify that specific database and to do this we use the USE command. By using the USE command, we tell MySQL that we want to access that specific database.

Example:

USE music;

4. CREATE TABLE command:

MySQL is a relational database management system that why it stores data in tabular form using rows and columns, so to create a table we use the CREATE TABLE command.

Syntax to create a table in a database:

CREATE TABLE [IF NOT EXISTS] TABLE_NAME (FIELDS DATATYPES);
  • Here CREATE TABLE create a table
  • [IF NOT EXISTS] command do nothing it’s just there because if the similar table name already exists, so the MySQL does not throw any error.
  • TABLE_NAME could be arbitrary
  • FIELD represents the variable name or heading for each column.
  • Each FIELD should be followed by a data type.

Example:

CREATE TABLE IF NOT EXISTS music_info(song VARCHAR(50), length INT, release_date DATE, singer VARCHAR(20));

Data Types

There are 3 main Datatypes we use in SQL:

  • String or Text
  • Numeric
  • Date and Time

1. String or Text Data Types

String or text data types used to represent the alphanumeric character in SQL, there are various text datatypes in MySQL:

Text Datatype Description
CHAR() Fixed number of character
VARCHAR() Variable number of character
TINYTEXT String with max length of 255 characters
TEXT String with max length of 65553 characters
BLOB String with max length of of 65535 characters.
MEDIUMTEXT String with max length of 16777215 characters.
MIDIUMLOB String with max length of 16777215 characters.
LONGTEXT String with max length of 4294967295 characters.
LONGBLOB String with max length of 4294967295 characters.

2. Numeric Datatype

Numeric data types use to represent the digits.

Numeric Datatype Description
TINYINT Range -128 to 128
SMALLINT Range -32768 to 32767
MEDIUMINT Range -8388608 to 8388607
INT Range -2147483648 to 2147483647
BIGINT Range -9223372036854775808 to 9223372036854775807
FLOAT Contain decimal numbers
DOUBLE It includes a large number of floating decimal points
DECIMAL Similar to float

3. Date/Time Data Types

With SQL we can use the specific data types for date and time.

Date Time Datatype Description
DATE YYYY-MM-DD
TIME HH:MM:SS
TIMESTAMP YYYYMMDDHHMMSS
DATESTAMP YYYY-MM-DD HH:MM:SS

4. Insert Data into the table:

To insert data in the table we use the insert command and pass the data in the same order we defined the data types.

Example:

INSERT INTO music_info VALUES("PERFECT", 180, "2018/12/12", "ED SHREEEN")

5. Show Command:

The show command is used to display all the database and table.

Example:

SHOW DATABASES;

+--------------------+
| Database           |
+--------------------+
| contacts           |
| information_schema |
| music              |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| tcontacts          |
| world              |
+--------------------+
USE music;
SHOW TABLES:
+-----------------+
| Tables_in_music |
+-----------------+
| music_info      |
+-----------------+

Conclusion

To Create a Data base in any SQL program we follow these steps:

  • Use the CREATE DATABASE command to create a Database
  • Use, USE command to access that specific database
  • Use CREATE TABLE command to create a new table inside the database
  • Use INSERT INTO VALUES command to insert values or data into the table.

A database can contain more than one table and with the help of some SQL command, we can link all the tables with each other on the behalf of primary keys.

You may be also interested in:

Leave a Reply

Your email address will not be published. Required fields are marked *