How to create a database in SQL?
How to create a database in SQL
- It is very necessary to create a database in order to store the data into the database.
- Database name must always be unique.
- SQL does not allow to create a database with the same name which already exists in the server.
- To ensure the uniqueness while creating a new database, the database administer must be aware of the already existing databases into the server.
- The SHOW DATABASES command is used in SQL to list all the databases which are present in server.
Example:
We will check all the available databases.
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | demo | | mysql | | performance_schema | | test | +--------------------+ 5 rows in set (0.05 sec)
In the above example, all the databases which are present in server including the system databases(information_schema, performance_schema, mysql) and user created databases(demo, test) are displayed.
- Once the database administrator is familiar with the names of already existing databases which are present in SQL server, now the database administrator can create a database with any name considering the output of SHOW DATABASES command.
Syntax for creating a database in SQL:
CREATE DATABASE DATABASENAME;
Example: We will create a new database with the name “Exampledb”.
mysql> CREATE DATABASE Exampledb;
A new database with the name “exampledb” is successfully created in the server.
- To ensure that the database is successfully created, we will again use the SHOW DATABASES command.
Example:
We will display all the available databases to check if the “exampledb” is created or not.
mysql> SHOW DATABASES;
Output:
+--------------------+ | Database | +--------------------+ | information_schema | | demo | | exampledb | | mysql | | performance_schema | | test | +--------------------+
Now, we can see the newly created database with the name “exampledb” is also listed.
- Alternative to CREATE DATABASE command is CREATE SCHEMA COMMAND. Both queries performs the same task of database creation.
Syntax:
CREATE SCHEMA DATABASENAME;
Example: We will use an alternate query to create a database with the name “testDB”.
mysql> CREATE SCHEMA testDB;
Example: We will display all the available databases to check if the “testdb” is created or not.
mysql> SHOW DATABASES;
Output
+--------------------+ | Database | +--------------------+ | information_schema | | demo | | exampledb | | mysql | | performance_schema | | test | | testdb | +--------------------+ 7 rows in set (0.05 sec)
- One can also review the already created database using the below command:
SHOW CREATE DATABASE DATABASE_NAME;
Example:
We will review the already created database named as “exampledb”. mysql> SHOW CREATE DATABASE exampledb;
Output:
+------------+-----------------------------------------------------------------------+ | Database | Create Database | +------------+-----------------------------------------------------------------------+ | exampledb | CREATE DATABASE ` exampledb ` /*!40100 DEFAULT CHARACTER SET latin1 */ | +------------+-----------------------------------------------------------------------+ 1 row in set (0.00 sec)
Here, the command which was used to create the database named “exampledb” is displayed along with the character set. Since, the character set was not specified during that database creation, the database is created with the default character set i.e., latin1.
- To store the data into a particular database, one needs to tell the server which specific database the administrator wants to use.
Syntax:
USE DATABASE_NAME;
Example:
To operate on a specific database, the user needs to tell the server that he wants to perform further queries on “exampledb”.
mysql> USE exampledb;
Output:
Database changed
Since, we have used the database named as “exampledb”, all the further queries will now be operated on this particular database.
Parameters of CREATE statement
One can also improvise the CREATE DATABASE query by adding parameters and specifications to it.
- IF NOT EXISTS
There can be multiple databases in a single MySQL server. One may try to create a database which is already present within the MySQL server. So, in that case using “IF NOT EXISTS” parameter with the CREATE DATABASE query serves the purpose. Prior to the creation of a new database, it instructs the server to check if the database already exists with the specified name.
Syntax:
CREATE DATABASE IF NOT EXISTS DATABASENAME;
Example:
In order to create a new database “demodb”, we will first display all the available databases and then create “demodb” if a database with that name does not already exist.
mysql> SHOW DATABASES;
Output:
+--------------------+ | Database | +--------------------+ | information_schema | | demo | | exampledb | | mysql | | performance_schema | | test | | testdb | +--------------------+ 7 rows in set (0.05 sec)
mysql> CREATE DATABASE IF NOT EXISTS demodb; Query OK, 1 row affected (0.00 sec)
Earlier there was no database with the name “demodb”. So, now the database with name “demodb” will be created. If we try to create a database with the existing name without the use of IF NOT EXISTS, then in that case SQL will throw an error.
Example:
Now, we will display all the available databases to ensure that the database “demodb” is created.
mysql> SHOW DATABASES;
Output:
+--------------------+ | Database | +--------------------+ | information_schema | | demo | | demodb | | exampledb | | mysql | | performance_schema | | test | | testdb | +--------------------+ 8 rows in set (0.00 sec)
Here, database with the name “demodb” is created.
- Collation and Character Set
Collation is a set of rules which are useful for comparison. One can store the SQL data in different language other than English. To store the data in some other language, you need to select the character set for that particular language. Different levels of character set includes server, database, table, and column. Once the character set is chosen, then only the rules of collation can be selected.
Example:
We will create a new database with name “sample” whose character set is “latin1” and collation rule is “latin1_swedish_ci”.
mysql> CREATE DATABASE IF NOT EXISTS sample CHARACTER SET latin1 COLLATE latin1_swedish_ci;
Here, “sample” database is created with latin1 as its character set and latin1_swedish_ci as its collation rule.