Note 1: This does not execute the DROP statements, it just gives you a list of them. SELECT concat('DROP TABLE IF EXISTS `', table_name, '` ') Limit the generated drops to your database like this:.Here we also discuss the introduction and syntax, along with different examples and its code implementation.I found the generated set of drop statements useful, and recommend these tweaks: To avoid raising an error when the DROP query specifies a database name that does not exist, we can use the IF EXISTS clause in both DROP DATABASE and DROP SCHEMA commands. We treat schemas and databases similarly, considering them as synonyms. DROP SCHEMA is an alternative command to delete a database existing on the database server. Hence, we should be careful while using this command, as it is impossible to restore the dropped database if you don’t have any backup. We can drop the database using the SQL command DROP DATABASE, which entirely and permanently deletes the database and all the contents of that database. You can also confirm the presence and dropping of databases at each stage while performing this task by using the SHOW DATABASES command that gives the list of all the databases present on your database server at that particular moment when this command is executed. This is due to the absence of any tables in the mysqlDropDemo database. We can observe the successful dropping of the database as no rows are returned or affected. That now results in the following output: We will be executing the following query statement – DROP SCHEMA IF EXISTS mysqlDropDemo Now if you execute the same query of DROP SCHEMA, then the output will be different. Let us create the database named mysqlDropDemo using the following query – CREATE DATABASE mysqlDropDemo It can be seen that one warning is raised. Now, let us execute the same query with the IF EXISTS clause in it – It throws an error saying no database named mysqlDropDemo exists in my database server. The output of the above query execution is as follows: Let us try to delete the database named mysqlDropDemo using the following query statement – DROP SCHEMA mysqlDropDemo This functions in the same manner as that of the DROP DATABASE command. Hence, we can make the use of DROP SCHEMA name_of_database In MySQL, the database and schema are used interchangeably, meaning both are the same thing and synonyms. The DROP DATABASE command gives the return value as the number of the tables that were present in that database and got deleted when dropping the database. It can be concluded that the query executed successfully without any errors or warnings and returned two affected rows. Now, let us drop the educba database using the drop database command – DROP DATABASE IF EXISTS educba Now let us create two tables named to test and development CREATE TABLE test(Īnd one more create table query for development that is as follows – CREATE TABLE development(Įxecution of the above query gives the following output: Now, let us create one database named educba using CREATE DATABASE command – CREATE DATABASE educba Additionally, the output shows the presence of one warning. This indicates that the query did not have any impact on the rows. The output indicates that the query was executed successfully and without any errors, even though the “educba” database does not exist in our database. The output of the execution of the above query is as shown below: Now, let us study the output after adding the IF EXISTS clause in our query statement – DROP DATABASE IF EXISTS educba We observe that the system throws an error with error code 1008, indicating that the ‘educba’ database cannot be dropped because it does not exist. Now, let us try to delete the database that is not present on our database server and see the outputs – DROP DATABASE educba Įxecuting the above command gives the following output: That gives the following output in my case: Then you must execute the following command to list all the databases in your MySQL database server. Executing the above command will give you the following output:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |