What happens if you delete mysql database?

What happens if you delete mysql database?
If you haven’t read the other installments in our MySQL blog series, we recommend you check them out:

  • MySQL Basics: How to Create a MySQL User
  • MySQL Basics: How to Create a MySQL Database

Since the previous blog posts have already walked you through creating a MySQL user, you’re now prepared for the next step: deleting a MySQL user. Deleting a MySQL user is very simple. Unlike the process of creating a user, where you have to add permissions, deleting a user only requires you to enter the command and the username. Deleting a user from your MySQL database will remove the user and all of the user’s privileges. The user will be unable to log back into the database or make changes.

How to Delete a MySQL User

When deleting a MySQL user, you must have the global create user or delete privilege for the database. If you do not have this privilege, you will not be able to delete another user. If this is the case, you can either switch to another user that has the privileges (if you have more than one user designated to you) or ask your admin for privileges. For more information about privileges, see our How to Create a MySQL User blog

The Drop User Statement

You can remove a user by using the DROP USER command. This command removes a user by deleting their information from the grant tables within the database. A user’s information needs to be stored in the grant tables so that the database can grant the user access. Without any information in the grant table, users will not be able to gain access. he DROP USER statement looks something like this:

DROP USER IF EXISTS user

Replace user with the username that you want to delete. The IF EXISTS portion of the command keeps MySQL from throwing an error if the user doesn’t exist.

You can also run the command as:

DROP USER user

However, if the user does not exist, you will receive an error.

Specifying User Hostname

The user can also be specified by hostname, if you want to limit where the user can connect from. This would look something like this:

DROP USER IF EXISTS ‘user’@’localhost’;

If you don’t specify a hostname and just list the user, all users with that username would be deleted, regardless of hostname. For example, if you executed the following, all “joe” usernames would be deleted:

DROP USER joe;

When just a user is listed, the command will default to using a wildcard in the place of a hostname, thus signalling that all hostnames should be affected by the command:

DROP USER ‘joe’@’%’

Removing More than One User at a Time

You can also remove more than one user at a time. To do this, simply list the users at the end of the DROP USER statement and separate them with commas:

DROP USER IF EXISTS user, user;

You can specify hostnames or only list the username so that all instances of the username are deleted.

Things to Keep in Mind

Deleting a user does not delete the databases or objects the user created or worked on.  Also, if the user is currently using an instance of MySQL while you are in the process of deleting the user, the instance will not close until the user closes it. After the instance is closed, the user will be deleted from the grant tables.The changes the user makes in that last instance would still be valid. However, the next time they attempt to log in, they will be unable to.

Deleting a User: In Review

By learning how to control your MySQL database users, you learn how to better manage your databases. Better management helps you get the most out of your databases, and consequently, your site. To boost your MySQL knowledge even more, stay tuned for more installments in the MySQL Basics blog series. You can also check out our posts on  How to Create a MySQL User and How to Create a MySQL Database for more information about managing your MySQL database.

databasesdeletedrop userSQLuser


If you want to delete a record from any MySQL table, then you can use the SQL command DELETE FROM. You can use this command at the mysql> prompt as well as in any script like PHP.

Syntax

The following code block has a generic SQL syntax of the DELETE command to delete data from a MySQL table.

DELETE FROM table_name [WHERE Clause]
  • If the WHERE clause is not specified, then all the records will be deleted from the given MySQL table.

  • You can specify any condition using the WHERE clause.

  • You can delete records in a single table at a time.

The WHERE clause is very useful when you want to delete selected rows in a table.

Deleting Data from the Command Prompt

This will use the SQL DELETE command with the WHERE clause to delete selected data into the MySQL table – tutorials_tbl.

Example

The following example will delete a record from the tutorial_tbl whose tutorial_id is 3.

root@host# mysql -u root -p password;
Enter password:*******

mysql> use TUTORIALS;
Database changed

mysql> DELETE FROM tutorials_tbl WHERE tutorial_id=3;
Query OK, 1 row affected (0.23 sec)

mysql>

Deleting Data Using a PHP Script

PHP uses mysqli query() or mysql_query() function to delete records in a MySQL table. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)
Sr.No.Parameter & Description
1

$sql

Required - SQL query to delete records in a MySQL table.

2

$resultmode

Optional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to delete a record in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Deleting MySQL Table record</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'TUTORIALS';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
		 
         if ($mysqli->query('DELETE FROM tutorials_tbl where tutorial_id = 4')) {
            printf("Table tutorials_tbl record deleted successfully.<br />");
         }
         if ($mysqli->errno) {
            printf("Could not delete record from table: %s<br />", $mysqli->error);
         }
         $sql = "SELECT tutorial_id, tutorial_title, tutorial_author, submission_date 
            FROM tutorials_tbl";
		 
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Access the mysql_example.php deployed on apache web server and verify the output. Here we've entered multiple records in the table before running the select script.

Connected successfully.
Table tutorials_tbl record deleted successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
Id: 5, Title: Apache Tutorial, Author: Suresh, Date: 2021

Does uninstalling MySQL delete database?

No, reinstalling mysql-server will not delete you database files, only delete the package files of mysql-server . You will be able to access your files(database) after you re-install the server.

How do I clear a MySQL database?

How to empty a MySQL database.
Go to cPanel >> Databases section >> phpMyAdmin menu. ... .
Select the database you wish to empty. ... .
Tick Check All to select all tables and choose the Drop option from the With selected drop-down list:.
This will execute the DROP TABLE SQL query to empty all the tables at once..

Can we drop database in MySQL?

We can drop/delete/remove a MySQL database quickly with the MySQL DROP DATABASE command. It will delete the database along with all the tables, indexes, and constraints permanently.

Can I uninstall MySQL?

To uninstall MySQL on Windows, make sure that your first stop the running server. Once the server is stopped, you can uninstall MySQL via the Windows "Control Panel". Go to "Programs and Features" and select "MySQL" => "Uninstall".