-
Notifications
You must be signed in to change notification settings - Fork 0
DATABASE
A database is a structured collection of data that is stored in a computer system.
The CREATE DATABASE statement is a DDL (Data Definition Language) statement used to create a new database in SQL. If you are creating your database on Linux or Unix, then database names are case-sensitive, even though SQL keywords are case-insensitive. If you are working on Windows then this restriction does not apply.
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.
The SQL SHOW DATABASES statement is used to list down all the available databases in MySQL database. You can use SHOW SCHEMAS as an alternate command for SHOW DATABASES. Syntax Following is the syntax of SQL SHOW DATABASES to list down all the available databases in MySQL − SHOW DATABASES [LIKE 'pattern' | WHERE expr];
The SHOW SCHEMAS Statement You can use the SHOW SCHEMAS statement as an alternate for the SHOW DATABASES statement.
Syntax Following is the syntax of the SQL SHOW SCHEMAS statement to list down all the available databases in MySQL − SHOW SCHEMAS [LIKE 'pattern' | WHERE expr] ; We can use LIKE or WHERE clause along with SHOW SCHEMAS to filter out a list of databases.
The SQL DROP DATABASE statement is used to delete an existing database along with all the data such as tables, views, indexes, stored procedures, and constraints.
Following are the important points to remember before you delete an existing database −
Make sure you have taken proper backup of the database before you delete it. Make sure no other application is connected and using this database. Make sure you have the necessary privilege to delete the database. Usually, an admin can delete the database.
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.
MySQL mysqldump command can be used to take complete backup of a given database. This operation will be performed from command line and will require database user name and password, preferably admin privilege. mysqldump -u username -p"password" -R databasename > databasename.sql
-- Restore MySQL Database -- If we have a database dump then we can use the following two step process to restore our database. First step is to create our new database using mysqladmin prompt command as follows: mysqladmin -u username -p"password" create tutorialsDB; --The next step is to import old database into new database shown below : mysql -u username -p"password" tutorialsDB < testDB.sql;
-- If you want to keep your database name same as the old one then you will have to drop old database and then re-create it before importing old data into this database, but make sure you don't have any data in this database which you do not want to loose.
-- If you are working with MS SQL Server then to create a backup for an existing database, SQL provides us with a simple SQL BACKUP DATABASE command.
-- Syntax -- Following is the syntax of the BACKUP DATABASE command in SQL − BACKUP DATABASE database_name TO DISK = 'filepath' GO -- Following is an example to create a backup file for the database testDB on D drive BACKUP DATABASE testDB TO DISK = 'D:\testDB.bak' GO
The SQL Backup with a DIFFERENTIAL Statement is used to create a differential backup of the database. The differential backup contains only the changes made to the database since the last full backup. This type of backup is usually smaller in size compared to a full backup. Thus, it reduces the time to perform the backup.
Syntax Following is the syntax for the backup database using DIFFERENTIAL Statement − BACKUP DATABASE database_name TO DISK = 'filepath' WITH DIFFERENTIAL GO
Example Let us look at an example using the DIFFERENTIAL Statement below − SQL> BACKUP DATABASE testDB TO DISK = 'D:\testDB.bak' WITH DIFFERENTIAL GO
If you have a proper backup of an MS SQL database then youc an easily restore it when needed.
Syntax Following is the syntax of the RESTORE DATABASE command in SQL − RESTORE DATABASE database_name FROM DISK = 'filepath' [WITH REPLACE] GO Here WITH REPLACE option can be given if you want to overwrite the existing database.
Example Following is an example to restore a database from a backup file testDB.bak available on D drive. SQL> RESTORE DATABASE testDB FROM DISK = 'D:\testDB.bak' WITH REPLACE GO