SQL Create database Example – Learn how to create database in MySQL with example and important points. Also, know how to select database in MySQL and show the list of databases as well.
SQL Syntax to create database
Create Database [IF NOT EXISTS] [Database_name];
SQL Query example to create the data base BOOKSTORE:
Query: create database BOOKSTORE;
LIST DATABASES
To show the list of databases use query “SHOW DATABASES;”
example: SHOW DATABASES;
Here you can see the list of all database in which the BOOKSTORE data base is included.
Output:
information_schema
BOOKSTORE
mysql
performance_schema
sys
test
SELECTING DATABASE
To select the database from the list of databases use query: USE DATABASE_NAME
Example: use BOOKSTORE;
KNOW THE CURRENTLY WORKING DATABASE
To know the currently working database name in which you are working, use the following query:
Query Example: SELECT DATABASE();
mysql> SELECT DATABASE();
Output:
| DATABASE() |
|---|
| bookstore |
Notes on Creating Database in SQL
- Spaces are not allowed for creating a database.
Example: Books store.
- Underscore can be allowed for creating a database name.
Example: Books_Store:
- If trying to create a database with the existing database name, database server throws an error “ERROR 1007 (HY000) at line (line number): Can’t create database ‘BOOKSTORE’; database exists”
in this case use the following query
CREATE DATABASE IF NOT EXISTS BOOKSTORE
IF NOT EXISTS is an optional clause of statement. It is used to prevent in getting an error of creating a new database that is already exists in the database server. Database server not allowed to have 2 databases with same name.