SQL Database Basic

Topic :
1. SQL - Create Database
2. SQL - Show Databases
3. SQL - Drop Database
4. SQL - Select Database
5. SQL - Rename Database
6. SQL - Backup Database

CREATE Database Statement

Syntax

Following is the syntax to create a database in SQL −

CREATE DATABASE DatabaseName;

Here, the “DatabaseName” is the name of the database that we want to create. The database name can contain any valid identifiers, such as number, letters, or underscores. But a DatabaseName cannot be a keyword available in SQL.

Example

Following is an example to create a database testDB using SQL CREATE DATABASE statement −

CREATE DATABASE p4n;

List Databases using SQL

Once the database testDB is created, you can check it in the list of databases using SQL command SHOW DATABASES; as follows: −

SHOW DATABASES;

DROP Database Statement

Syntax

Following is the syntax to delete a database in SQL −

DROP DATABASE DatabaseName;

Here, the DatabaseName is the name of the database that you want to delete. A database name is always unique within the RDBMS.

DROP DATABASE IF EXISTS Statement

The DROP DATABASE IF EXISTS statement is used to drop (delete) a database, but with the additional condition of checking if the database exists before attempting to drop it. This can help prevent errors if you're attempting to drop a database that might not exist. Here's the syntax:

DROP DATABASE IF EXISTS database_name;

Here’s an example of using the DROP DATABASE IF EXISTS statement:

DROP DATABASE IF EXISTS p4n;

In this example, if the database named “p4n” exists, it will be dropped. If it doesn’t exist, no error will be generated, and the statement will simply do nothing.

The USE DATABASE Statement

The SQL USE DATABASE statement is used to select a database from a list of databases available in the system. Once a datbase is selected, we can perform various operations on it such as creating tables, inserting data, updating data, and deleting data.

Syntax

Following is the syntax of the USE DATABASE statement in SQL −

USE DatabaseName;

Rename Database

RENAME DATABASE…TO (Obsolete)

The SQL RANME DATABASE…TO statement is used to rename an existing user-created database.

Syntax

Following is the syntax of the RENAME DATABASE…TO statement −

RENAME DATABASE OldDatabaseName TO NewDatabaseName;

The ALTER DATABASE…MODIFY Statement

Syntax

Following is the syntax of the ALTER DATABASE…MODIFY command −

ALTER DATABASE OldDatabaseName MODIFY NAME = NewDatabaseName;

Example

Following is the SQL command in SQL Server to rename the database testDB to tutorialsDB:

ALTER DATABASE p4n MODIFY NAME = codeswithpankaj ;

Backup MySQL Database

Assuming you have the MySQL command line tools installed and you’re running this command in a terminal:

mysqldump -u your_username -p your_database_name > backup.sql

Replace your_username with your MySQL username and your_database_name with the name of the database you want to back up. You will be prompted to enter your MySQL password after running this command.

For example, if your username is “root” and the database you want to back up is called “MyDatabase”, you would run:

mysqldump -u root -p MyDatabase > backup.sql

After running this command, a file named “backup.sql” will be created in the same directory. This file will contain SQL statements that represent the structure and data of your database.