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.
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.
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.
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.
CREATE TABLE IF NOT EXISTS music_info(song VARCHAR(50), length INT, release_date DATE, singer VARCHAR(20));
There are 3 main Datatypes we use in SQL:
- String or Text
- 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:
|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.
|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|
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.
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.
SHOW DATABASES; +--------------------+ | Database | +--------------------+ | contacts | | information_schema | | music | | mysql | | performance_schema | | sakila | | sys | | tcontacts | | world | +--------------------+ USE music; SHOW TABLES: +-----------------+ | Tables_in_music | +-----------------+ | music_info | +-----------------+
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: