SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL OPERATORS SQL COMMANDS SQL Queries

SQL Table

SQL TABLE SQL CREATE TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL UPDATE TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT INTO Statement

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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) 
create a database in SQL

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;
create a database in SQL

       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               |
 +--------------------+ 
create a database in SQL

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;
create a database in SQL

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) 
create a database in SQL
  • 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) 
create a database in SQL

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
create a database in SQL

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.

  1. 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) 
create a database in SQL
 mysql> CREATE DATABASE IF NOT EXISTS demodb;
 Query OK, 1 row affected (0.00 sec) 
create a database in SQL

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) 
create a database in SQL

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;
create a database in SQL

Here, “sample” database is created with latin1 as its character set and latin1_swedish_ci as its collation rule.



ADVERTISEMENT
ADVERTISEMENT