The UNIQUE restriction guarantees that almost all elements in a field are distinct from each other. For a field or group of fields, the UNIQUE and PRIMARY KEY limits mutually have an assurance of individuality. We may, nevertheless, have several UNIQUE restraints in each table, though one PRIMARY KEY limit for each table instead. Let’s learn it by trying some examples.

Unique Constraint via Workbench:

First of all, we have to learn about how to add Unique constraints to the table while using MySQL Workbench 8.0. Open your newly installed MySQL Workbench 8.0 and connect it to the database.

In the query area, you have to write the below command to create a table ‘Person’. This table has 4 columns with one primary key. We have to specify one unique column. As you can see, we have cleared the column ‘Id’ as the ‘UNIQUE’ column:

Now the table “Person” has been created with its “UNIQUE” column “ID”. You can find the table under the “Navigator” and “Schemas” while listed in the “Tables” option.

While inserting the records, when you tap on the “Apply” button, it will review the inserted records as shown below. You can see we have one duplicated record at lines 3, and 4 that has the same “ID”. Tap the “Apply” button to apply changes.

In the below image, it’s generating an error that the column “ID” has duplicated, which is the value “13”.

After correcting the record, when you apply the changes, it will work correctly.

Unique Constraint via Command-Line Shell:

In the MySQL command-line shell, we will be adding UNIQUE keys to one or several columns. Open your command-line first to have a glimpse of each in the below examples. Type your password to use the command shell.

Example 01: On Single Column

Within this syntax, as in column specification, we use the UNIQUE term that you’d like to apply the uniqueness law. Once we insert or alter a value that creates duplicates in the particular column, the modification will be refused by MySQL and an exception will be given as well. There will be a column restriction in this Particular limit. And you could use that to implement one field’s unique rule too. Here is a syntax for a single-column UNIQUE key:

Let’s create a table “supplier” in the database “data” with three columns in it. The column “ID” is defined as “UNIQUE”.

On checking, you can see the table has no records yet.

Let’s insert the records into the table. The first record will be inserted into the table smoothly, as shown below.

The second record will be inserted smoothly again since it has no duplicate values at the column “ID”. On the other hand, it is taking the same value as it uses in the first query at column “Area”.

On inserting the third record, we have provided the duplicate value “1” as we have provided in the first insert statement. It will generate an error that the “ID” column is getting a duplicate value, as shown in the image below.

While checking again, you can see that the table has only the record of the first two insert statements. While there is no record from the third insert statement.

Example 02: On Multiple Columns

With this format, after the UNIQUE term, we apply a comma-separated set of columns in brackets. The composition of values in field col1 and col2 is used by MySQL to determine the uniqueness.

We have been creating a table “minister” in the database “data” with five columns. The column “ID” is defined as “UNIQUE” and “PRIMARY”. The keyword “CONSTRAINT” is used to name a unique key constraint as “uc_add_sal”. The “UNIQUE” keyword is used to define a UNIQUE constraint on the columns specified in the brackets, e.g., Address and “Salary”. Now we have a total of three columns having “UNIQUE” constraint on them.

On checking the table, you can see the table is empty right now.

Let’s insert some records into it. The first record will be added to the table successfully because it’s the first line and there is no row to be matched with.

Enter another unique record without any duplicate values at any column, as shown below.

It doesn’t affect when we enter the duplicate values for the columns that have no “UNIQUE” constraint on them. Have a look at the below query. It has a duplicate value at the column “Name” and “Job”. It works properly because these two columns have no “UNIQUE” constraint defined on them.

On the other hand, when we insert the duplicate value, e.g., “13” and “Rawalpindi”, it will generate an error, as shown below. This is because “13” and “Rawalpindi” have been specified earlier.

On checking, we have only three records in the table, inserted by the first three queries.

Conclusion:

We have gracefully done all the examples of defining UNIQUE constraints on the single and multiple columns while using the MySQL Workbench 8.0 and MySQL command-line client shell. Hopefully, you will get no problems while solving problems relating to UNIQUE keys.

Syntax:

In the following syntax:

• IF: It’s the keyword clause for the condition to be started.
• Condition: After the ‘IF’ clause, It’s the constraint to be fulfilled.
• Statements: Can be any piece of code, e.g., select, retrieve, update, delete. If a condition is evaluated as TRUE, then the statements after the ‘THEN’ clause will be executed.
• ENDIF: It’s the end of the ‘IF’ clause. After it, the power is moved to the next clause.

Let’s start understanding If-then by launching the MySQL command shell. By typing the password, we are ready to go.

Example 01: MySQL IF() Function:

To gain knowledge about the If statement, we have first to test the IF() function. In the below example, we have defined the IF() function in the SELECT query and give it a condition to check whether 2 is greater than 9 or not. If the condition is valid, it will return the first value after the condition; otherwise, second. As our condition is not valid, that’s why it returns ‘false’.

Let’s assume a table ‘order’ as shown in the appended image.

Let’s have a look at the IF() function performed on this table. We have been selecting the three columns. If the column ‘Status’ has the value ‘Paid’ then the IF() method will return ‘Excellent’ otherwise ‘Bad’. The IF() function’s returned value will be saved into a newly run-time-created column ‘Remarks’. Now we can see the output as appended below.

Example 02: MySQL IF-THEN Statement

We have tried an IF() function on the MySQL command line. Let’s try a new example of the IF-Then statement in a MySQL GUI while using procedures. Open the MySQL Workbench 8.0 and connect to the Database first.

We have been working on the database ‘data’; then, you have to state the ‘use data’ command to use it in Workbench. Press the flash icon to execute it. You have to know that the If-then statements work with store procedures, as shown below. We have declared a keyword delimiter to start the store procedure. The procedure ‘myResult’ has been taking two arguments. After the BEGIN statement, we have an IF statement that is checking the condition. If the condition satisfies, then the ‘THEN’ command and its following statement will be executed. If the condition gets false, then the statements after ‘END IF’ will be implemented.

As the procedure ‘myResult’ has been taking two arguments, we have to pass two values into it.

After passing values to the store procedure, we have to Call the procedure to see the results of the If-then statement.

And the result is given below. It has calculated the discount_rate via the If-then statement.

If you want to use the same stored procedure again, you have to drop this procedure first using the DROP command below and then execute it again.

Example 03: MySQL IF-THEN-ELSE Statement

Let’s go to some broad level. We will be looking at the IF-Then-Else statement this time by using the stored procedure in our example. Have a look at the below table, ‘student’ with some fields in it.

First of all, you have to use the database ‘data’ to use the table ‘student’ in our store procedure. For that, type the below command in your MySQL command-line terminal.

Now declare a delimiter, and then start writing the store procedure. The ‘CREATE’ command will be used to declare or create a procedure as always. The procedure ‘details’ has been taking two arguments. After that, the store procedure starts with the ‘BEGIN’ keyword. The word ‘DECLARE’ has been used to define a variable ‘Sub’ for subjects. The ‘SELECT’ query has been used to select the values of column ‘Subject’ from the table ‘student’ and save it into the newly declared variable ‘Sub’. The user-provided value ‘S_Subject’ will be compared to the column ‘Subject’ value. In the ‘IF’ statement, If the user-provided value ‘S_Subject’ matches with the column ‘Subject’ value, then the relative ‘THEN’ statement will be executed along with its statements within. This procedure will be processed from the first ‘IF’ statement to the second, then the third ‘ELSEIF’ statement. If the last ‘ELSEIF’ part doesn’t match with the value provided by a user, then control will be given to the ‘END IF’ statement.

Let’s end the delimiter with the below command.

We have to call the store procedure by executing it with a ‘CALL’ query and provided arguments in the parenthesis. As we have given ‘Maths’ as its value, then a new column will be generated to output the ‘THEN’ and ‘SET’ statement.

Let’s check the newly created column ‘S_Cource’ by using the below command. You can see we have a relevant result according to the course ‘Maths’.

Call the procedure again by a change at the first argument, i.e., subject. We have provided the subject ‘Computer’ this time.

While selecting the value of the ‘S_Cource’ column, you can see we have a value relating to the subject ‘Computer’, which is ‘IT’.

Once more, we have been calling the procedure as below by a modification at the first argument. We have delivered the subject ‘History’ this time.

By calling the column ‘S_Cource’ again, you can see how we have a result ‘Masters in History’ regarding the recently provided subject, e.g., History.

Conclusion:

We have done will all the examples required for If-then statements elaboration, e.g., IF() function, If-then simple statement, If-then-else statement.

Transaction Properties

Transactions, frequently known by the term ACID, have four main general properties.

• Atomicity: This guarantees that all tasks inside the work unit become completed successfully; else, just at the failure point, the process is terminated, and prior processes are restored to their old state.
• Consistency: This means that upon a sufficiently committed process, the database updates state appropriately.
• Isolation: It helps transactions to work with one another and individually and transparently.
• Durability: This makes sure that in the event of a system breakdown, the outcome or consequence of a committed transaction retains.

MySQL Transactions Working:

Within MySQL, the two terms, Commit and Rollback are primarily used only for MySQL transactions. The transactions start only with BEGIN WORK declaration and finish either by a COMMIT declaration or a ROLLBACK declaration. The SQL instructions comprise the majority of the transaction amongst both the starting and stopping statements. Such event series is irrespective of the specific programming language utilized. You will make a suitable path in whatever language you are using to build the application. The below SQL statements can be implemented using the mysql query() feature.

• BEGIN: Start your process or transaction by providing the BEGIN WORK SQL instruction.
• Add SQL COMMAND: one or even more SQL statements such as SELECT, INSERT, UPDATE, and DELETE, respectively. Confirm even if there’s no mistake and everything is in compliance with your expectations.
• COMMIT: The COMMIT instruction must be imposed after a successful transaction is done, such that the modifications to all of the associated tables can take full effect.
• ROLLBACK: If a malfunction occurs, it’s indeed appropriate to send a ROLLBACK instruction to revert each table specified in the transaction towards its former condition.
• AUTOCOMMIT: By default, MySQL applies the modifications permanently to a database. If AUTOCOMMIT is set to 1 (standard), then each SQL query (whether or not inside a transaction) is assumed to be a completed transaction and committed until it is completed by default. To avoid automatic commit, set AUTOCOMMIT to 0.

Example 01: AutoCommit Mode On:

MySQL operates with the Autocommit phase allowed through default. It ensures that MySQL saves the changes on the disk to create it perpetually as long as we run a query that adjusts (alters) a table. It is not necessary to turn back the move. Let’s try with AUTOCOMMIT on mode. Open your MySQL command-line shell and type your password to begin.

Take an example of a table ‘book’ that has been created in a database ‘data’. Right now, we haven’t performed any query on it yet.

Step 2: This process is to update the table ‘book’. Let’s update the value of column ‘Author’ where the ‘Name’ of a book is ‘Home’. You can see changes have been made successfully.

By having a glimpse of the updated table, we have a modified value of the author where ‘name’ is ‘Home’.

Let’s use the ROLLBACK command to revert the changes by simply adding the below query. You can see that the ROLLBACK query doesn’t work here as it shows that ‘0 rows affected’.

You can even see the table as well. The table has got no changes after the ROLLBACK statement execution so far. This means that the ROLLBACK doesn’t work when we have AUTOCOMMIT on by default.

Example 02: AutoCommit Mode Off:

To revert the changes made, let’s try with AUTOCOMMIT off mode. Using the same example of table ‘book’, we will perform some changes on it. We will be using the START TRANSACTION declaration to deactivate its auto-commit phase or simply type the below command to set AUTOCOMMIT off.

Let’s say we have the same table ‘book’ in our database, and we have to make changes to it. Then revert those changes to the old ones again.

If you haven’t turned off the AUTOCOMMIT mode, then make a start with a START TRANSACTION query in the command line shell as below.

We will be updating the same table using the UPDATE command by setting the ‘Author’ as ‘Aliana’ where the ‘Name’ of a book is ‘Dream’. Do it by using the below command. You will see that the changes will be made successfully and effectively.

Let’s check whether the above query has worked perfectly and made changes to the table or not. You can check the updated table by using the below SELECT command as always.

You can see that the query has worked great, as shown below.

Now, it’s a turn of the ROLLBACK command to perform its function. Try the ROLLBACK command in your command line to roll back the recent update to the table.

Let’s check whether the ROLLBACK query has been worked as it should work or not. For this, you have to check the table ‘book’ again by using the ‘SELECT’ command as always.

You can see from the below output that ROLLBACK has finally worked. It has reverted the changes made by the UPDATE query on this table.

Conclusion:

That’s all for MySQL transactions. I hope this guide will help you to perform MySQL transactions conveniently.

To get started, you must have MySQL installed on your system with its utilities: MySQL workbench and command-line client shell. After that, you should have some data or values in your database tables as duplicates. Let’s explore this with some examples. First of all, open your command-line client shell from your desktop taskbar and type your MySQL password upon asked.

We have found different methods to find duplicated in a table. Have a look at them one by one.

Search Duplicates in a Single Column

First, you must know about the syntax of the query used to check and count duplicates for a single column.

Here is the explanation of the above query:

• Column: Name of the column to be checked.
• COUNT(): the function used to count many duplicate values.
• GROUP BY: the clause used to group all rows according to that particular column.

We have created a new table called ‘animals’ in our MySQL database ‘data’ having duplicate values. It has six columns with different values in it, e.g., id, Name, Species, Gender, Age, and Price providing information regarding different pets. Upon calling this table using the SELECT query, we get the below output on our MySQL command-line client shell.

Now, we will try to find the redundant and repeated values from the above table by using the COUNT and GROUP BY clause in the SELECT query. This query will count the Names of pets which are located less than 3 times in the table. After that, it will display those Names as below.

Using the same query to get different results while changing the COUNT number for Names of pets as shown below.

To get results for a total of 3 duplicate values for Names of pets as shown below.

Search Duplicates in Multiple Columns

The syntax of the query to check or count duplicates for multiple columns is as follows:

Here is the explanation of the above query:

• col1, col2: name of the columns to be checked.
• COUNT(): the function used to count several duplicate values.
• GROUP BY: the clause used to group all rows according to that specific column.

We have been using the same table called ‘animals’ having duplicate values. We got the below output while utilizing the above query for checking the duplicate values in multiple columns. We have been checking and counting the duplicate values for columns Gender and Price while grouped by the column Price. It will show the pet genders and their prices which are residing in the table as duplicates not more than 5.

Search Duplicates in Single Table Using INNER JOIN

Here is the basic syntax for finding duplicates in a single table:

Here is the narrative of the overhead query:

• Col: the name of the column to be checked and selected for duplicates.
• Temp: keyword to apply inner join on a column.
• Table: name of the table to be checked.

We have a new table, ‘order2’ with duplicate values in the column OrderNo as shown below.

We are selecting three columns: Item, Sales, OrderNo to be shown in the output. While the column OrderNo is used to check duplicates. The inner join will select the values or rows having the values of Items more than one in a table. Upon executing, we will get the results below.

Search Duplicates in Multiple Tables Using INNER JOIN

Here is the simplified syntax for finding duplicates in multiple tables:

Here is the description of the overhead query:

• col: name of the columns to be checked and selected.
• INNER JOIN: the function used to Join two tables.
• ON: used to join two tables according to provided columns.

We have two tables, ‘ order1’ and ‘order2’, in our database having the ‘OrderNo’ column in both as displayed below.

We will be using the INNER join to combine the duplicates of two tables according to a specified column. The INNER JOIN clause will get all the data from both the tables by joining them, and the ON clause will relate the same name columns from both tables, e.g., OrderNo.

To get the particular columns in an output, try the below command:

Conclusion

We could now search for multiple copies in one or several tables of MySQL information and recognize the GROUP BY, COUNT, and INNER JOIN function. Make sure that you have built the tables properly and also that the right columns are chosen.

Syntax

Let’s examine the above syntax for this query:

• Table_name: is the title of an existing table you want to modify.
• existing_column_name: is the name of a column to be deleted.

Note: You can have more than one columns to be deleted. For that, you have to use more than one DROP COlUMN clause in your query.

Drop Column via MySQL Workbench

Make sure you have MySQL installed on your windows system. You have to open the newly installed MySQL workbench from the start button of your desktop. We have to make sure to connect our  MySQL workbench with the database from the main menu of the workbench under the ‘Database’ tab.

Under the workbench’s Navigation bar, we have a list of different databases that we have created already. Within the database ‘data’, we have added a table ‘student’. The table ‘student’ has the following records in it as below.

If you want to drop a column from an existing table ‘student’, you have to sail across in the direction of the Schemas beneath the Navigator. Inside the database ‘data’, we have a list of tables, e.g., student and teacher. We will expand the table ‘student’. While hovering over it, you will discover a representation of the setting icon, as shown below. Hit it off to carry on.

A new window will be opened in the workbench as below. We might see a list of columns and their definitions. To drop a column from the table, you have to select that column, right-click on it and press the ‘Delete Selected’ option.

A new window will be popped up, having a query written on it to drop a column. Hit on Apply button to proceed with the update.

Another below window will be opened. Tap on a Finish button to reflect changes at the table ‘student’.

You can see a column ‘age’ has been removed from the table ‘student’ as we couldn’t find it here.

Try the below query in a workbench query place below the navigator to drop a column from a table. Tap on the flash icon under the navigator bar as highlighted in the image below to reflect the query’s changes.

The new altered table without a column ‘age’ is shown below.

Drop a Column via Command-Line Shell

Make sure you have a command-line client shell utility of MySQL has been installed on your current system. To remove a column from a table while using the command-line, open the MySQL command-line client from the taskbar. Type your MySQL password while asked in the shell to continue working.

Suppose we have a table ‘student’ with some record in it residing in the schema ‘data’. While checking, we have found a given-below record in the table ‘student’. Right now, this table has probably 9 columns in it.

Example 01: Drop a Single Column

If you are looking for an example to delete a single column from an existing table, then this example is indeed for you. Considering the same above table, let’s delete the column named ‘lastname’ from it. After that, we must have 8 columns left. Try the below query in the MySQL command-line client shell. If the query works properly, it will display a message that the query is ‘OK’.

The above image shows that the query works properly, and the column ‘lastname’ has been removed from the table ‘student’. Let us check it and use the same SELECT query to call the table ‘student’.

The output below shows that we have left with only 8 columns, and the column ‘lastname’ and its values have been deleted from the table ‘student’ successfully.

You can delete columns from the start, last, middle, and from any position of the table.

Example 02: Drop More than One Columns

You’re also able to drop more than one column from any table in MySQL using the ALTER query. You just need to add more than one DROP clause in the ALTER query. Let’s take the same above updated table ‘student’ having 8 columns. We have to delete the two columns, e.g., gender and reg_date, from it. For that, we have to use two DROP Column clauses in our query. Let us execute the below ALTER query followed by the DROP clauses in the MySQL command-line client shell.

As you can see from the above query message that the query worked perfectly. Upon checking the table ‘student’, we have got an updated table having 5 columns left in it. The column named ‘gender’ and ‘reg_date’ has been removed from it.

Point to be noted that we have deleted the columns reg_date and gender from two different locations of a table. This means you can delete any column from any location of a table. It is not necessary to delete columns from the last place of the table.

Conclusion

You have proficiently tried all the inquiries to delete, remove or drop a single column or more than one column from an already defined table in a database while working in MySQL workbench and Command-line client shell. We hope you have got no issues while trying all the above methods.

First of all, you must have some data in your database schema to perform queries on it. Let’s make a table named ‘student’ in the database ‘data’ using a CREATE query in MYSQL Workbench or Command-Line Client. The table ‘student’ has six columns: ‘id’, ‘firstname’, ’lastname’, ‘email’, ‘reg_date’, and ‘class’. We will be adding values to its columns using its grid view as below and click on the ‘Apply’ button to save changes. Now you can perform any update on these records.

Delete via Workbench Interface

A very simple method to delete row/rows from the MySQL table is via the workbench grid view as we have a table ‘student’ with ten records in it. To delete a single row from a table, you have to select the particular row and press the delete-row icon from the grid window as we have selected the 10^th row and pressed the highlighted icon below.

After tapping on the delete icon, you can see that the 10^th row and its record have been deleted from the table ‘student’. If you want to delete more than one row, you have to select more than one row consecutively.

Delete Single Row via Command-Line

Another simple method to delete a row from the MySQL schema is through the command-line client. Open the MySQL command-line client under the newly installed ‘MySql’ via the ‘window’ button. First of all, check and display all the records of table ‘student’ using the ‘SELECT’ command as below.

Example 01: Using One Condition in WHERE Clause
Let’s delete a single row using the ‘WHERE’ clause in the ‘DELETE’ query. We are deleting the row where the ‘lastname = Waleed’, which is row number 10 as above. Let’s try it as:

It has been deleted successfully as it displays that ‘Query OK, 1 row affected’.

On display all the rows of table ‘student’, we can see that the record of the 10^th row has been deleted from the table.

Use the same ‘DELETE’ query in the navigator of the workbench to delete a record as shown.

Example 02: Using More than One Condition in WHERE Clause
You can also delete the single row from the table, using more than one condition in the ‘DELETE’ query of MySQL. We are using two conditions in the ‘WHERE’ clause, e.g., ‘lastname = khursheed’ and ‘id > 7’. This query will only delete the row which has an id greater than ‘7’, and its lastname is ‘khursheed’. In our case, it is the 9^th row.

The 9^th row has been deleted successfully as it says that ‘Query OK, 1 row affected.’

On checking, we have only 8 rows left within the table. The 9^th row has been wiped away from the table, as shown below.

Example 03: Using LIMIT Condition in WHERE Clause
We can also delete a single row via the ‘LIMIT’ clause in the ‘DELETE’ query. In this query, we have to define a limit as ‘1’ for a single row to be deleted. We have defined a limit value as ‘1’ in the ‘WHERE’ clause of the ‘DELETE’ query. It will only delete the first row from all the records having ‘lastname = Awan’, which is row number 2.

Use the ‘SELECT’ query to check the updated table.  You can see that the 2^nd row is nowhere in the table as displayed below, and we have only 7 rows left.

Delete Multiple Rows via Command-Line

Let us update the table ‘student’ first by adding some records to it so we can delete multiple rows. Let’s display the records of a table where the lastname is ‘Awan’, using the ‘SELECT’ query with the only WHERE clause. This query will display only 4 rows, as we have only 4 records for the column ‘lastname = Awan’.

Example 01: Using LIMIT Condition in WHERE Clause
To delete multiple rows from a table, we can be using the ‘LIMIT’ condition in the ‘WHERE’ clause of the ‘DELETE’ query. We just have to define the ’LIMIT’ other than 1 or any negative number. So, we have been defining ‘LIMIT’ as ‘3’, to delete 3 rows from the table. It will delete the first three rows of the record having the ‘lastname’ as ‘Awan’.

Display the remaining records of the table using the ‘SELECT’ query. You will see, there is only 1 record left for ‘lastname’ having the value ‘Awan’, and three rows have been deleted.

Example 02: Using More than One Conditions in WHERE Clause
We are using the same above the table and defined two conditions in the ‘WHERE’ clause to delete rows having ‘id’ greater than 2 and less than 9 as follows:

We have only 2 rows left in the table while checking the records.

Example 03: Delete All Rows
You can delete all the rows from the table ‘student’ using the below simple query in the command line as:

While trying to display the records, you will get an empty set of tables.

Conclusion

We have taken a glimpse of different ways to delete single and multiple rows from a table while working in MySQL via the workbench and command-line client interface.

Subquery within Single Table Records:

Create a table named ‘animals’ in the database ‘data.’ Add the below following record of different animals with different properties as displayed. Fetch this record using the SELECT query as follows:

Example 01:

Let’s retrieve the limited records of this table using the subqueries. Using the below query, we know that subquery will be executed first, and its output will be used in the main query as input. A subquery is simply fetching the age where the animal price is 2500. The age of an animal whose price is 2500 is 4 in the table. The main query will select all the table records where the age is greater than 4, and the output is given below.

Example 02:

Let’s use the same table in different situations. In this example, we will be using some Function instead of WHERE clause in the subquery. We have been taking the average of all the prices given for animals. The average price will be 3189. The main query will select all the records of animals having a price of more than 3189. You will get the below output.

Example 03:

Let’s use the IN clause in the main SELECT query. First of all, the subquery will fetch prices greater than 2500. After that, the main query will select all the records of table ‘animals’ where the price lies in the subquery result.

Example 04:

We have been using the subquery to fetch the name of the animal where the price is 7000. As that animal is a cow, that’s why the name ‘cow’ will be returned to the main query. In the main query, all the records will be retrieved from the table where the animal name is ‘cow.’ As we have only two records for animal ‘cow,’ that’s why we have the below output.

Subquery within Multiple Table Records:

Assume the below two tables, ‘ student’ and ‘teacher,’ in your database. Let’s try some examples of subqueries using these two tables.

Example 01:

We will fetch data from one table using the subquery and use it as an input for the main query. This means that these two tables can relate in some manner. In the below example, we have been using the subquery to fetch the student’s name from the table ‘student’ where the teacher name is ‘Samina.’ This query will return ‘Samina’ to the main query table ‘teacher.’ The main query will then select all the records related to the teacher name ‘Samina.’ As we have two records for this name, therefore we have got this result.

Example 02:

To elaborate the subquery in the case of different tables, try this example. We have a subquery that is fetching the teacher’s name from the table student. The name should have ‘i’ at any position in its value. This means, all the names in the column TeachName having ‘i’ in their value will be selected and returned to the main query. The main query will select all the records from the ‘teacher’ table where the teacher name is in the output returned by the subquery. As subquery returned 4 names of teachers, that’s why we will be having a record of all these names residing in the table ‘teacher.’

Example 03:

Consider the below two tables, ‘order’ and ‘order1’.

Let’s try an ANY clause in this example to elaborate subquery. The subquery will select the ‘id’ from the table ‘order1’, where the column ‘Status’ has a value of ‘Unpaid.’ The ‘id’ can be more than 1. This means that more than 1 value would be returned to the main query to get the table ‘order’ results. In this case, any ‘id’ could be used. We have got the below output for this query.

Example 04:

Assume you have the below data in the table ‘order1’ before applying any query.

Let’s apply the query within a query to delete some records from the table ‘order1’. Firstly, the subquery will select the ‘Status’ value from the table ‘order’ where the Item is ‘Book.’ The subquery returns ‘Paid’ as the value. Now the main query will delete the rows from the table ‘order1’ where the ‘Status’ column value is ‘Paid.’

Upon checking, we have now the below records remained in the table ‘order1’ after the execution of the query.

Conclusion:

You have efficiently worked with a lot of subqueries in all the above examples. We hope everything is clear and clean now.

Syntax:

Let’s have a glimpse at the explanation of a query.

• Table_name: Name of a table to get data from
• Expression: Name of a column to be retrieved or name of a column used to arrange data.
• ASC: Used to categorize data in ascending order. It is optional.
• DESC: Used to arrange data in descending order. It is optional
• WHERE condition: It is an optional constraint to be used.

Get started with opening the MySQL command-line client shell to start working on sorting. It may ask for your MySQL password. Type your password and tap Enter to continue.

Example: Sort without ORDER BY (ASC or DESC) clause:

To elaborate sorting with the ORDER BY clause, we have been starting our first example without using the ORDER BY clause. We have a table ‘teacher’ in the schema ‘data’ of MySQL with some records in it. When you want to fetch the data from this table, you will get it as it is, as it was inserted in the table without performing extra sorting, as presented below.

Example: Sort with ORDER BY Column Name without ASC|DESC:

Taking the same table with a little change in the SELECT query. We have specified the name of a column according to which the whole table will get sorted. We have been using the column ‘id’ to sort the table. As we haven’t defined the sort type, e.g., Ascending or descending, that’s why it will be automatically sorted in ascending order of ‘id’.

Let’s sort the same table without using ASC or DESC expression in the SELECT statement while using another column. We will be sorting this table ORDER BY the column ‘subject’. All the data in the column ‘subject’ will get sorted alphabetically first; then, the whole table will be sorted according to it.

Now. We will be sorting the table ‘teacher’, according to the column ‘qualification’. This query will sort the column ‘qualification’ alphabetically first. After that, all the records get sorted by this column as below.

You may also fetch the specified column data from the table with the ORDER BY clause. Let’s display the three-column data from the table ‘teacher’ and sort this data according to the column ‘firstname’. We will be getting three columns sorted record as shown.

Example: Sort with ORDER BY Single Column Name with ASC|DESC:

Now, we will be performing the same query with a little change in its syntax. We will specify the sorting type while defining the column name in the query. Let us fetch the record of four columns: firstname, lastname, subject, and qualification from a table ‘teacher’ while sorting this record according to the column ‘firstname’ in ascending order. This means that the column ‘firstname’ will be sorted in ascending order first then all the data regarding it will get sorted.

According to the descending order of column ‘firstname’, Sorting the same record of four columns’ is as follows.

Fetching the fully-fledge table ‘teacher’ while using the column ‘firstname’ in ascending order is as follows.

Let’s retrieve the whole table by the descending order of column ‘id’ as below.

Example: Sort with ORDER BY Multiple Column Name with ASC|DESC:

Yes! You can easily sort your table with multiple columns. You just need to specify the ORDER BY clause while a comma separates each column name with its sorting type. Let’ take a glimpse of a simple example. We have been selecting four-column data from a table. Firstly, this data will be sorted according to the column ‘id’ in descending order then into descending order by the column ‘firstname’.

Example: Sort with ORDER BY with WHERE clause:

As we know that the WHERE clause is being used for performing some conditions on data. We can easily sort our data while using the WHERE clause and fetch it according to it. We have performed a simple query in which we have fetched all records from the table ‘teacher’ where the ‘id’ is greater than 4 and the ‘qualification’ of a teacher is ‘Mphil’. This query will fetch the records of teachers whom qualification is equaled to MPhil, and their ‘id’ is not less than 5. After that, this data will b sorted in descending order of the ‘ids’ of teachers as shown in the image.

If you want to fetch the specified columns from a table, you can also do that. Let’s do that as below.

Conclusion:

We have done almost all the examples to learn the ORDER BY clause for sorting the records. I hope this article will help you to sort results in MySQL.

LIMIT Syntax:

Here is the explanation of the LIMIT query:

• Expression: Can be a column name or steric ‘*’.
• Offset: The offset determines the offset to be returned from the first row. If you use the 0 as offset, it will return the row 1 and vice versa.
• Rows: The total rows to be returned.

Open the command-line shell of MySQL and type the password. Press Enter to continue.

Assume you have a table called ‘teacher’ in your MySQL database, as shown below. If you want to fetch all the records or rows of this table without any limit define, you will do it using the simple SELECT query as follows:

Example 01: LIMIT with Row Number Only:

If a user wants to fetch some records while limiting the number of rows, he/she can do it by using the simple LIMIT clause in the SELECT statement. Let’s try an example while utilizing the above table. Suppose you want to display only 6 rows from the above table while displaying the rows following the descending order of a column TeachName. Try the following query:

Example 02: LIMIT With OFFSET and Row Number:

Let’s try the same table to define Offset alongside the row number. Suppose you need to fetch only 6 records from the table while the record returned must be starting from the 6^th row of a table. Try the succeeding query:

Example 03: LIMIT with WHERE Clause:

Assume the table ‘same’ is located in the MySQL database. Fetch the whole table while sorting it in ascending order of column ‘id’ using the SELECT command along with ORDER BY as follows:

Trying the stated query, we will have random three records of the age between 12 and 34 from any random location of the table. While in the above table, we have more than 6 records of age group between 12 and 34.

When we use the ORDER BY clause in the query without stating the order type, it will automatically fetch the record in ascending order as below.

To fetch a limited record in another sort order, you have to define the sort order. As we are fetching 8-row data using the descending order of column ‘age’.

Let’s fetch only 2 rows where the pet name is ‘dog’. On execution, we have only 1 result because the ‘=’ sign searched for the exact pattern, and we have only 1 record of its pattern.

Example 04: LIMIT with LIKE Clause and It’s Wildcards:

We searched for the pattern ‘dog’ and got only 1 result. While we have more than 1 record of ‘dog’ in the table. Now we will fetch those records using the LIKE clause along with the LIMIT clause. To display the only 5 records of the table where the pet name starts from ‘d’, try the below query. As we have only 2 records for pattern ‘dog’, that’s why we have got only 2.

Let’s retrieve only 10 records from the table, where the pet must have ‘r’ at any middle location of its name. As we have horse, parrot, and rabbit in the pets having ‘r’ in their name, that’s why we have got only 4 records from this query.

To get 6 records of the table, where the pet name must have ‘t’ at the end, run the stated query in the SQL command-line shell. Here we have 4 records from this query.

To get 8 records of the table, where a person’s job must have ‘er’ at the end, run the below query in the shell. Here we have got 6 records from this query.

Let’s change the WHERE clause along with the LIKE statement in the SELECT query. Suppose you want to fetch a 6-row record from the table ‘same’. You have tried a condition to fetch the only records where the ‘fname’ must have ‘a’ at the last of its value, and ‘lname’ must have ‘a’ at any middle location of its value. On the other hand, the data must be sorted following the column ‘age’ descending order. To get these 6 records, run the below-stated query in the SQL command-line shell. We have got 5 records for this condition, and the result is shown below.

Conclusion:

I hope you are fully prepared about the LIMT topic after trying almost all the examples for the LIMIT clause along with its members, e.g., Offset and row number.

MySQL provides these two wildcards for constructing patterns.

• The percentage ‘%’
• The underscore ‘_’

Open your newly installed command-line client shell of MySQL and type your MySQL password to work on it.

We have created a new table called ‘teacher’ in our database having different records in it, as shown below.

MySQL LIKE with Percentage % Wildcard:

Percentage sign works differently while using at different locations with alphabets. In the first example, the percentage sign has been used at the last location of the pattern to fetch a record of two columns, ‘TeachName’ and ‘subject’, where the subject name starts with ‘C’. Upon trying the below LIKE query, we have got the below result.

Use of the percentage sign before the pattern means that the pattern will match the last location of a value. So we have been looking for the records of columns ‘TeachName’ and ‘subject’ where the teacher name containing the alphabet ‘a’ at the last location. We have found the below output.

If you want to search the string pattern at the middle of the value, you have to place the percentage sign at both the start and end of the pattern. We have searched for the ‘am’ pattern lies between teachers’ names using the below-stated query.

Use the percentage sign in the middle of the pattern to search for a matching value without knowing what comes in the middle of it. We have displayed all the data related to the teacher name starting with ‘S’ and ends with ‘a’.

MySQL LIKE with Underscore ‘_’ Wildcard:

We will be using a new table called ‘record’ for understanding the underscore wildcard operator. The wildcard underscore ‘_’ works as one character when placed at some location; that’s why it couldn’t work for more than two characters as a percentage operator does.

Let’s fetch matching values while placing the underscore at the last of the pattern location. You have to define the exact number of characters in that particular name. Otherwise, your query won’t work. We want to display the records of the ‘Name’ starts with ‘Za’, where the three underscores mean that the last three characters of this name can be anything, and the name should consist of only 5 characters.

Place the underscore operator at the start of the pattern to search for the value. This implies that the starting characters of a string value can be anything. After the specified character has been used, the percentage sign means that the string value can be of any length. So, when we execute this query, it will return the values with different lengths.

In the below query, we have been using the wildcard underscore in the middle of the pattern. This means that the character before the last alphabet can be anything but the last alphabet must be ‘a’. The percentage sign shows that the string can be of any length.

We will be using the underscore at the start and any point of the pattern while searching in the column ‘Country’. This shows that the second character of a pattern must be ‘u’.

MySQL LIKE with NOT Operator:

MySQL helps you merge the NOT operator with the LIKE operator to identify a string that doesn’t even match a particular sequence. We have been searching for the records of columns: ‘Name’, ‘City’ and ‘Country’, where the Country name must have the ‘i’ alphabet in its string at any of the middle locations. We have got three results for this particular query.

MySQL LIKE with Escape Characters:

The sequence you would like to match often includes wildcard characters, e.g., %10, 20, etc. In this scenario, we could use the ESCAPE clause to define an escape symbol such that the wildcard symbol is treated as a literal character by MySQL. When you do not specifically mention an escape character, the standard escape operator is backslash ‘\’. Let’s search for the values in the table having ‘_20’ at the end of city names. You have to add ‘\’ as a wildcard escape character before the ‘_20’ because the underscore is itself a wildcard. It shows the data of cities having ‘_20’ at the end of their names. The percentage sign means the start of the name can be of any length and can have any character.

In the below example, the ‘%’ sign at start and end is used as a wildcard operator as used before. The second last ‘%’ sign is a pattern to be searched, and ‘\’ is an escape character here.

The ‘%’ sign is the wildcard operator, ‘\’ is the escape character and the last ‘/’ sign is a pattern to be searched at the last position of names here.

Conclusion:

We have done with Like Clause & Wildcards, which are important instruments that help hunt for information that matches complicated patterns. I hope this guide has helped you reach your actual goal of learning LIKE operators and wildcard operators.

A CSV or comma-separated value document is a delineated text document that distinguishes values from a comma. Every line is its information record. Each data, parted by commas, comprises one or extra fields. The origin of the title for this document layout is the usage of the comma as a field divider. For sharing information between various programs, such documents are used. For instance, Database and contact administrators also endorse CSV files. The theory is that from one program to a CSV document, you may transfer complex information and afterward import the information in that CSV document to some other program. In this tutorial, we will learn how to import data from a CSV file into MySQL workbench. Let’s get started.

Step 01: Create a CSV File

To create a CSV file, you have to open Microsoft Excel in your system and add some data as we have created a file and added the below record into it. Make sure to create field names easy to make it convenient for yourself. Now, save this file with the ‘.csv’ extension. You can change its file format while saving it to any folder. Go to ‘File,’ and hit on ‘Save As.’ Select the location, name the document and below the name field, select the file format. We have named our file as ‘book.’

Step 02: Connect Database

To import your CSV file data into MySQL, you must first connect your Workbench with the Database. For this purpose, open the newly install MySQL Workbench 8.0 and go to the ‘Database’ option. Hit on the ‘Connect to Database’ option from the listed options.

The below new window will be opened. Select your Database, e.g., ‘Stored Connection.’ After that, set the ‘Parameters’ and then hit the ‘OK’ button to link to the Database. You will be heading to the MySQL Workbench Schema’s Graphical User Interface window.

Step 03: Create Schema

In the Local instance or database window, you will see a list of schemas already created by MySQL. You have to generate a new database or use the already created Database as we have a database ‘data’ as our default database. We will utilize it to import data.

Step 04: Create Table

In the query area of schema’ data,’ create a table ‘book’ using the schema by ‘use’ command. Make sure to use the same names for the file and its fields as used in the CSV file. After that, select the create query to be executed and tap on the flash button. The new table ‘book’ will be created in the database’ data.’

You can see that the table ‘book’ has been created, and you can change its constraints.

The table is currently empty as below.

Step 05: Import CSV File Data into Table

Let’s start importing data from the CSV file into the newly created table. Navigate the Schemas and click on the ‘data’ database. There will be a newly created list of tables under the table option of database’ data.’ Hit on the just created table ‘book’ and right-click on it. Now click on the option, i.e. ‘Table Data Import Wizard’ option.

You will see, the window will be opened as shown. Select the file ‘book’ by adding its location. Tap the ‘Browse’ button to pick the file path.

As you can have a glimpse that we have saved a file ‘book’ into some directory. Click on it, and tap on the ‘Open’ button to continue.

We can view that the file path has been selected. Hit on the ‘Next’ button to proceed.

We have the following window now. If you want to add your CSV file data into an already created table, you have to opt for, ‘Use the existing table’ radio button. On the other hand, if you want to import your CSV file data into the new table, you have to create a new table by choosing the’ Create new table’ by choosing the radio button. In this option, you have to select the database name that is already located in your server and give a new table. If you have selected the ‘Use existing table’ option, you have to checkmark the checkbox ‘Truncate table before import.’ Then, click on the ‘Next’ button to go on to the next step.

Now we are on the ‘Configure Import Settings’ window. In this window, you have to select Encoding, e.g., we have selected ‘utf-8’, which is standard. Make sure the Source and Destination columns have similar names. If not, you can select the destination column names by tapping on the column’s name and checking the list of column names. If there are any extra columns generated, you can unselect them here to avoid the creation of extra fields. If everything is set, then you are good to go. Hit on the ‘Next’ button to get closer to import data from the CSV file.

Now, it has two steps listed to import the file data. Firstly, it will prepare the import, and after that, it will import the data file. We have to just tap on the ‘Next’ button to initiate the import process.

After tapping the ‘Next’ button, it will start importing. It will hardly take 2 minutes to import all the data from the file ‘book.’ If you have too much data in the file, it may take a little longer.

After the import has been completed, hit on the ‘Next’ button again.

Finally, the import process has been completed. Tap on the ‘Finish’ button to end it efficiently.

Step 06: Check the Table

We can now refresh the table ‘book to see if it has been updated or not. See! The CSV file data has been magnificently imported into a table ‘book’ without any error or change.

Conclusion

Finally, we have done all the necessary steps to import data from a CSV file to a MySQL Server Workbench 8.0.

Data redundancy occurs for a lot of reasons. Several of the complicated duties you should cope with while working with database systems is trying to discover duplicate values. For this purpose, We will be using the COUNT() aggregate method. The COUNT() method returns the sum of rows residing in a specific table. The COUNT() function permits you to sum all rows or only rows matching the condition defined. In this guide, You’ll get to know how to identify duplicate values for one or maybe more MySQL columns using COUNT(). The COUNT() method has the following three types:

• COUNT(*)
• COUNT(expression)
• COUNT(DISTINCT expression)

Make definite that you have MySQL installed on your system. Open the MySQL command-line client shell and enter your password to continue. We will be looking at some examples for counting the matching values using the COUNT() method.

We have a table ‘social’ in our schema ‘data’. Let’s check its record via the following query.

MySQL COUNT(*)

The COUNT(*) method is used to count the number of rows residing in the table or count the number of rows according to the given condition. To check the total number of rows in a table, ‘social’ try the below query. We have a total of 15 rows in the table as per the result.

Take a glimpse of the COUNT(*) method while defining some conditions. We have to fetch the number of rows where the username is the same as ‘Mustafa’. You can see we have only 4 records for this particular name.

To fetch the total sum of rows where users’ website is ‘Instagram’, try the below-stated query. The table ‘social’ has only 4 records for the website ‘Instagram’.

To retrieve the total number of rows where the ‘Age’ is greater than 18 is as follows:

Let’s fetch the data of columns ‘User’ and ‘Website’ from a table, where the User name starts with the alphabet ‘M’. Try the below instruction on the shell.

MySQL COUNT(expression)

In MySQL, the COUNT(expression) method is only used when you want to count non-Null values of the column ‘expression’. The ‘expression’ would be the name of any column. Let us take a simple example of it. We have been only counting the non-null values of a column ‘Website’, which is related to the column ‘Age’ having a value that equals ‘25’. See! We have only 4 non-null records for the users having age ‘25’, who are using websites.

MySQL COUNT(DISTNCT expression)

In MySQL, the COUNT(DISTINCT expression) method is used to sum non-Null values and distinct values of the column ‘expression’. To count a distinct number of non-null values in the column ‘Age’ we have been using the below query. You will find 6 non-null and distinct records of column ‘Age’ from the table ‘social’. This means we have a total of 6 people having different ages.

MySQL COUNT(IF(expression))

For large emphasis, you should merge COUNT() with flow control functions. For starters, for a portion of the expression being used in the COUNT() method, you might use the IF() function. It may be very useful to do this to provide a fast breakdown of the information inside a database. We will be counting the number of rows with different age conditions and dividing them into three different columns, which can be said as categories. First, COUNT(IF) will be counting the rows having age less than 20 and save this count into a new column named ‘Teenage’. Second COUNT(IF) is counting the rows having ages between 20 and 30 while saving it to a column ‘Young’. Third, the last counts the rows having ages greater than 30 and saved into a column ‘Mature’. We have 5 teenagers, 9 young and only 1 mature person in our record.

MySQL COUNT(*) with GROUP BY Clause

The GROUP BY statement is a SQL instruction using for group rows with the same values. It returns the total number of values residing in each group. For instance, if you want to check each user’s number separately, you have to define the column ‘User’ with the GROUP BY clause while counting records for each user with COUNT(*).

You can either select more than two columns while performing the counting of rows along with the GROUP BY clause, as follows.

If we want to count rows while using the WHERE clause having some conditions in it alongside the GROUP BY and COUNT(*), you can also do that. The below query will fetch and count the records of columns: ‘User’, ‘Website’, and ‘Age’ where the website value is ‘Instagram’ and ‘Snapchat’ only. You can see we have only 1 record for both websites for different users.

MySQL COUNT(*) with GROUP BY and ORDER BY Clause

Let’s try the GROUP BY and ORDER BY clauses jointly with COUNT() method. Let’s fetch and count the rows of table ‘social’ while arranging the data in descending order using this query:

The below-stated query will first count the rows, then display the only records having COUNT greater than 2 in ascending order.

Conclusion

We have gone through all the possible methods to count the matching or duplicate records using the COUNT() method with different other clauses.

MySQL Database System is a highly scalable database service for creating cloud-native applications. Therefore we have to perform different operations while working on it. The ALTER TABLE declaration is being cast-off to add, remove, or alter columns while working on an already existing table in any schema of MySQL. We’ll teach you exactly how to declare a column to an existing table utilizing the MySQL ADD COLUMN expression in this guide.

Syntax:

Here is the detail of this query:

• Table_name: is the existing table you wish to amend or add a new column.
• New_column_name: is the title for a new column to be added.
• Column_definition: It is the data type of a new column and its definition, e.g., Null, Not Null.
• FIRST | AFTER column_name: This clause specifies the location of a new column in the table. It is optional; that’s why if not used, the column will be implanted at the last of a table.

Add Column via MySQL Workbench

Open your newly installed MySQL workbench from the start button of your desktop. Make sure to connect your workbench with the database.

In the Navigation bar of the workbench under the schema, you can find the already created databases. We have created a database ‘data’ and added a table ‘student’ into it. The table ‘student’ has the following columns and records in it.

To add a new column in the existing table ‘student’, you have to navigate towards the Schemas under the Navigator. Within the database ‘data’, we have a list of tables, e.g., student and teacher. You have to expand the table, student. While hovering over it, you will find an icon of the setting, as highlighted below. Click on it to continue.

The below window will be opened in MySQL workbench. You can view a list of columns and their definitions. You can add a new column at the last of all the columns by double tapping at the last space and, after that, writing a column name on it.

It is clear from the below image that we have added the new column ‘age’ at the last of all columns, with its definition defined.

You will find a new window having a query listed to add a new column as below.  Click on Apply button to proceed.

The window will be opened. Hit on Finish to see changes.

Now, the restructured table is appended below.

After adding values, it will look like as underneath. You can also add the Alter query in the space above this table and under the navigation bar to add a column in a table.

Add Column via Command-Line Shell

To add a new column in an existing table while using the command-line, you have to open the MySQL command-line client from the taskbar. Enter your MySQL password when asked and press Enter.

Upon checking, we have found the below-given record in the table ‘student’. Let’s add a new column, ‘age’, at the end of the table.

Example 01: Add Single Column

If you want to add a single column to an existing table, then this example is meant for you. Now, we will add a new column named ‘age’ at the last place of the table, ‘student’. To add a new column ‘age’ after the column ‘class’, try the below query in the MySQL command-line shell.

On checking the table, you will see that the table has created a new empty column ‘age’ at the last place, as shown in the image.

We will be updating a table while adding values to a newly created column ‘age’. We have tried the below three UPDATE queries to add values into a column ‘age’.

Let’s check the updated table ‘student’ using the below SELECT query in the shell as:

Now we have a full-fledge newly updated table as given below.

Example 02: Add More than One Columns

You can also add more than one column at different places of an existing table using ALTER query. Let’s try the below query to add two new columns, e.g., gender and city, at the last of all the table ‘student’ columns. We have used two ADD clauses in this query to add two columns.

You will find the below-updated table upon checking it with the SELECT query in the shell. You will find that the table has created two new columns with no values in them at all.

To avoid the emptiness of newly created columns, we will be adding values to new columns, e.g., gender and city. We have tried the below three UPDATE queries to add values into the columns’ gender’ and ‘city’. First of all, we have updated the column ‘gender’ using the below query as:

After that, we have updated the column ‘city’ by using the below two UPDATE commands:

Let’s check the updated table ‘student’ using the beneath SELECT query in the command line shell as:

Finally, we have got a newly restructured table as given below.

Conclusion

Perfect! We have efficiently tried all the queries to add a single column or more than one column in an existing table while working in MySQL workbench and Command-line client shell.

In this article, we will learn to configure database-level encryption for MariaDB.

Getting Started

The data at rest encryption requires an encryption plugin along with the key management. The encryption plugin is responsible for managing the encryption key as well as encrypting/decrypting the data.

MariaDB provides three encryption key management solutions, so how you databases manage encryption key depends on the solution you are using. This tutorial will demonstrate database-level encryption using the MariaDB File Key Management solution. However, this plugin does not provide a key rotation feature.

If you are using a LAMP server, the files to add this plugin are located in the “/opt/lamp” directory. If not, then the changes are made in the “/etc/mysql/conf.d” folder.

Creating Encryption Keys

Before encrypting the database using the File key management plugin, we need to create the files containing encryption keys. We will create a file with two pieces of information. That’s an encryption key in a hex-encoded format along with a 32-bit key identifier.

We will create a new folder “keys” in the “/etc/mysql/” directory and use the OpenSSL utility to randomly generate 3 Hex strings and redirect the output to a new file in the keys folder. Type in the following commands:

Where 1,2,3 are the key identifiers; we include them to create a reference to the encryption keys using variable innodb_default_encryption_key_id in MariaDB. The output file will look like this:

Key File Encryption

We can easily set the system variable file_key_management_filename with the appropriate path inside the File Key Management plugin. But it’s not secure to leave the keys in plain text. We can reduce the risk to some extent by assigning file permissions but, that isn’t sufficient.

Now we will encrypt previously created keys using a randomly generated password. In contrast, the key-size can vary from 128/192/256-bits.

Hence we will use the openssl enc command in the terminal to encrypt the enc_key.txt file to enc_key.enc, using the encryption key created above. Besides, MariaDB only supports the CBC mode of AES to encrypt its encryption keys.

We also delete our enc_keys.txt file as it is no longer required. Besides, we can always decrypt our data in MariaDB as long as our password file is secure.

Configuring File Key Management Plugin

We will now configure MariaDB with the File Key Management plugin by adding the following variables in the configuration file. The configuration files are usually located in ‘/etc/mysql’ and read all the .cnf files by default. Or you can create a new configuration file “mariadb_enc.cnf” under ‘/etc/mysql/conf.d/ directory.

Now your configuration file can look entirely different from this. However, add these encryption variables under [sqld]. If the key is encrypted, the plugin requires two system variables to configure, i.e., file_key_management_filename and file_key_management_filekey.

You can find details for each system variable from the official MariaDB website.

Securing The Password File

We will change our MySQL directory permissions to secure the password and other sensitive files. The ownership of the MariaDB will be changed to the current user, which on Ubuntu is mysql.

Now we will change the password and encrypted file permissions to

Now restart the database service.

Conclusion

This article has learned how database-level encryption is the need of the hour and how we can configure encryption-at-rest in MariaDB. The only drawback of the File Key Management plugin is that it does not support key rotation. However, apart from this plugin, many other key management encryption solutions, i.e., AWS Key Management Plugin and Eperi Key Management Plugin. You can find more details on these plugins from MariaDB’s official website. ]]> https://linuxhint.com/mysql-workbench-tutorial-for-ubuntu/ Sun, 03 Jan 2021 18:41:25 +0000 https://linuxhint.com/?p=84069 MySQL Workbench is a cross-platform GUI client for MySQL database users and administrators. Workbench makes the task easier for database admins by providing important tools to manage databases and users, creating databases, running SQL queries, setting up and configuring servers, and many more.

It is a powerful tool that enables us to visualize modules for creating, executing, and optimizing several queries. So, in this article, I am going to give a tour of MySQL Workbench and show you how to use it.

After installation, when you launch MySQL workbench for the first time, it somewhat looks like the following screenshot. It is the home window of the workbench.

Here you will be able to perform database admin tasks, right from creating a database to configuring and executing database servers.

Key MySQL Workbench Features

• SQL Development
• Data Modeling
• Server Administration
• Data Migration
• MySQL Enterprise Support

So, there are three modules in MySQL Workbench, SQL Development, Data Modeling, and Migration. For each of these, there is a separate tab present on the MySQL Workbench home screen.

1. SQL Development

This is the first module in MySQL workbench that enables database administrators to create and manage connections to database servers.

For example, let me show you how you can connect to localhost. Click on Databases and then Connect to Database, a new window will appear similar to the below screenshot, here you need to click Ok, and then, it will ask for a MySQL server password.

Enter your password and click Ok. It will connect to the database named, localhost. You can also check Save the password in keychain to remember it for future use.

The following window will appear with a tab named Query 1 on successful connection to the database. Here you can start creating and managing databases.

The output pane here gives you output to any query you run or execute. It means you can see the result immediately.

For example, let’s create a table named LINUXHINT1 and try to execute it. Don’t forget to select the default schema before executing any query.

You can see in the above screenshot that table named, LINUXHINT1, is created in the output pane.

2. Data Modeling

As the name suggests, it will help you create models of your database graphically and allow you to perform reverse and forward engineering between schema and live databases.

You can also add various fields to your database using its comprehensive table editor, which is very easy to use and offers tools to edit tables, columns, indexes, and various more.

The data modeling window is something that looks like what is shown in the following screen shot. Here you can see various buttons like Add Diagram, Add Table, Add View, Add Routine, and Add Groups.

You can use the schema privileges drop-down to add users and various user roles. Also, you can add scripts under the SQL Scripts drop-down.

3. Data Migration

It is a great feature to migrate the data from other databases like Microsoft SQL server, Microsoft Access, Sybase ASE, SQLite, and other relational database management systems (RDBMS).

On top of that, you can also migrate from earlier versions of MySQL to the latest releases.

So, these are the basic things you can do with MySQL workbench. Apart from that, you can be a server admin and create/manage server instances, manage security, create and manage different users, and give them permissions to work on MySQL objects and perform import/export.

Administration

Server Status
Under this tab database admins, can keep track of the performance of the currently connected database. Here, they can monitor the connection status, number of connections, and traffic.

Users and Privileges
Here, administrator can add a specific user, and give them access to edit and work on databases and schemas. In the future, they can reassess the permissions and make changes to them according to requirements.

Under the administrative roles tab, you can choose the roles for which you want to give permissions. Similarly, under schema privileges, you can select permissions you want to give like to select, alter, create, etc.

Data Import/Restore
Here you can import schemas and restore them to previous ones.

So, this is the basic MySQL workbench tutorial for Ubuntu, which should be enough to make you familiar with  MySQL Workbench and start your journey in database management. Feel free to share your views with us at @linuxhint and @SwapTirthakar. ]]> https://linuxhint.com/mysql-primary-and-foreign-keys/ Mon, 28 Dec 2020 08:11:55 +0000 https://linuxhint.com/?p=83392

The primary key can be any field or column of a table, which should be a unique and non-null value for each record or a row.

The Foreign key is a field that contains the primary key of some other table to establish a connection between each other.

Let’s have a look at the syntax and different examples to create primary and foreign keys in MySQL.

Primary Keys

We can make a primary key on a segment of the table by utilizing the ALTER TABLE.

Primary key while creating a table

Suppose that we want to create a table of books in MySQL that contains the ID, name, and category of a book, in which the ID column will be the primary key.

The query for creating such a table and creating the ID column a primary key column will be like this:

In this syntax, we can define constraints at the end of the query.

If we describe the table,

We can see in the column of the key that the book_id is set as the Primary Key of the table.

Drop a Primary key

Instead of adding, if we want to delete or drop a primary key, the ALTER command is used.

So, this is all about how we can create and delete a primary key in a table.

Primary key creation via ALTER TABLE

To define a primary key, we can put the ALTER TABLE into use.

The primary key is added successfully. Now, let’s learn about the foreign keys a well.

Foreign Keys

Just like primary keys, foreign keys can be defined while defining the table using the ALTER TABLE command.

Foreign key while creating a table

In the primary key section, we have created a table for the books. Now, let’s suppose, we have another table of authors in our database that includes the author’s ID as a primary key, author’s first name, and last name,

And we want to create a foreign key to the author’s ID in the books table. So, to create a foreign key on author_id while creating the books table, we run this query:

Drop a Foreign key

Dropping a foreign is not the same as dropping a primary key. We first have to get the name of the constraints by running the “SHOW CREATE TABLE books” command.

Then provide the constraint name to the ALTER TABLE command like this:

This is how we can create and delete a foreign key in a table.

Primary key using the ALTER TABLE command

For creating a foreign key on an existing table using the ALTER TABLE command,

Let’s DESC the books table:

We can see that the author_id is set as the foreign key successfully.

Summary

We have learned about the profound and concepts of Primary keys and Foreign keys. As well as the creation, addition, and deletion of a primary or foreign key in a table.

There is no such statement as FULL OUTER JOIN in SQL, but we can use a simple JOIN to get the same results or by simply using a SELECT statement over two different tables.

Otherwise, MySQL provides LEFT JOIN and RIGHT JOIN to get the records or rows from the right or left table, respectively. Let’s try a couple of different examples to get the desired results using appropriate joins.

Examples

Before we start learning the usage of LEFT and RIGHT JOIN. We will learn how to get all of the data from both of the tables (either common or uncommon) using the simple SELECT statement and using the CROSS JOIN with the SELECT statement. First, let’s try to get all of the data from both of the tables using the SELECT statement.

For instance, there are 2 tables that we got by the name of the author and books.

If we want to get all the columns from both of the tables. The SELECT query will be used like this:

As you can see, we have all the columns from both of the tables without even providing a condition.

If we use the JOIN or CROSS JOIN clause, both will bring us the same results. For example:

Now, let’s try to apply the CROSS JOIN:

As you can witness, all of these queries are bringing us the same results.

However, it is not good to have all the columns in such an abstract form. So, to get a few specific columns from the left or right table, there are two ways in which you could proceed; one way is that you use column names using SELECT statements or using joins that fit your requirement.

Alright, now let’s move forward to understand the LEFT JOIN and RIGHT JOIN.

LEFT JOIN

Suppose that we want to get some specific columns that are either from the books table or common between the books and authors table, based on some condition, the condition is actually provided by comparing two different tables. For example, we want to join two tables, books, and authors where the book’s ID is equal to the author’s ID. We can expect such a result by using LEFT Join with SELECT statement; SELECT query with the column names that you want to get from either the books table or authors. The SELECT query with the LEFT JOIN and condition would be like this:

Since we have mentioned the books table on the left side, the join will get the ID of one row from the books table and look for the same ID number in the author’s table. If it finds the same ID number, it will also show the given columns from the author’s table. Otherwise, it will show NULL in the columns of the author’s table. Let’s execute this query and witness the results.

As you can see, we have the rows from both of the tables where the books table ID is equal to the author’s table ID. In the last row, we can also see that there is no ID number 4 in the author’s table, so it has returned NULL against it.

RIGHT JOIN

Similarly, if we want to get some data, either from the author’s table or common between the books and the author’s table, based on some conditions, this kind of results can be expected by using the RIGHT join and SELECT clause. The SELECT query with the RIGHT JOIN and condition would be like this:

This time, we know that the authors’ table is on the right side, so the join will get the ID of one row from the author’s table and look for the same ID number in the book’s table. If it finds the same ID number, it will show the given columns from the books table. Executing the query would result in this:

As you can see, we have the rows from both of the tables where the author’s ID is equal to the book’s ID. We know that there was a fourth book in the books table, although we didn’t get it, this is because of the RIGHT JOIN.

So, this is how the LEFT JOIN and RIGHT JOIN truly works.

Conclusion

We have learned and understood the CROSS, LEFT, and RIGHT JOIN, as well as learned to use them to get the desired results in MySQL. We also have tried a couple of different examples of JOINS to understand the concepts in a better and profound way. So keep visiting our website linuxhint.com for more useful content like this.

INSERT statement is used to insert data in rows of a table.

Syntax

The syntax for inserting data into a table is:

In this syntax:

First, mention the table_name (in which you want to insert data) along with the column names in parentheses (column_name_1, column_name_2, …) (columns of the table), followed by the INSERT INTO clause.

After mentioning the table name and column names in parentheses, you need to provide the values after the VALUES clause like this:

(value_1, value_2, …); these are values or data you want to insert corresponding to the columns.

You can also provide or add multiple rows in a single query by separating them with a comma.

Let’s try some examples of data insertion into a table in MySQL and have a better understanding of the INSERT command.

Examples

Before getting started learning the data insertion. Let’s first create a table and set some different data types of the columns so that we can insert different types of data. The query for creating a table would be like this:

In this query, we have created a table with the name of cars, which includes the following columns:

An integer type car_id column with the constraint of AUTO_INCREMENT (which means that during data insertion, even if we don’t provide any value, it will automatically increment the value and add that value in this column).

A name column with the data type of VARCHAR, which includes the name of the car, and set the constraint so it can’t be NULL.

A manufacturing_date column will have the date of when the car was manufactured.

An engine column will have the engine type. For example, Gasoline, Diesel, or Hybrid. We have set the constraints that forbid this value to be null, and if it is not provided while inserting a new row, then it sets the default value to  ‘Gasoline’.

A description column that includes the description of the car.

And in the end, we have created a primary key on the car_id column.

After creating a table successfully, let’s move towards the data Insertion.

INSERT Command

In the INSERT command, it is not necessary to insert data into all the columns. We can just insert data into some specific columns until we are fulfilling the requirements that we have during the creation of the table. So, let’s first try to enter the car name and its engine type only. The query for inserting the data would be like this:

After successfully adding one row to the table. The table should be updated.

As you can see, we have not added any car_id, but because of the AUTO INCREMENT constraint, the car_id is automatically inserted, along with the other two fields

Well, we can also provide the DEFAULT keyword while inserting data. When we provide the DEFAULT keyword while inserting data, the DEFAULT value that will be assigned is what we have set during the creation of the table. For example:

Now, let’s take a look at the table again.

The default value ‘Gasoline’ is assigned. That’s great!

Alright, now, let’s learn about the format of inserting the date into MySQL’s Table.

Insert Date into a table

To insert a date into MySQL, we need to follow the following syntax:

‘YYYY-MM-DD’

Year, Month, and Date are separated by dashes. For Example:

Or if you want to insert the current date. We can use MySQL’s built-in functions like CURRENT_DATE() or NOW(). You can get today’s date by using any of these functions. For example:

Similarly, the NOW() function would do the same for us:

Now, let’s see the current status of the table.

It can be observed that today’s date is inserted successfully by both of the functions.

Alright, now, let’s try to insert more than one row in a single INSERT statement.

Inserting Multiple Values

To insert multiple values, we can provide them in the parentheses separated by a comma followed by the VALUES clause. For example:

In this single query, we have added three different car models of AUDI in three different rows of the ‘cars’ table. The table should contain three added rows.

As you can see, all of the three rows are inserted as we desired.

So, these are some of the different syntaxes and ways to insert data into a table.

Wrapping Up

In this article, we have learned different syntaxes to insert different types of data into the table. We have also learned to use the CURRENT_DATE() function, NOW() function, and DEFAULT keyword to understand the different syntaxes for adding or inserting data into a table.

MySQL is a well-known database that can be used with ease and no hassle and is used in a lot of big firms. Data integrity and Data administrators mean a lot to such huge companies. But when it comes to data integrity and taking care of the users, their privileges, and the creation and deletion of them, the database administrator takes responsibility for such tasks. So, in this article, we are going to learn about different methods to delete or drop a user in MySQL.

Before we get started learning about the user’s deletion in MySQL, it is assumed that you know how to create and list the users and have already installed MySQL on your system. So, figure out the version of MySQL using the command below:

If you could see the version, it means that it’s already installed. Moving forward, we will figure out the status of the system’s mysql.service. Then, we would be able to sign in to the MySQL server.

In case the service hasn’t started, you can initiate it with the following command:

Once the service begins, you can connect yourself to the MySQL shell as a root user so, you can pretty much access everything inside.

After logging into MySQL, list the user names and hostnames from mysql.user by running the following command:

After having a look at the list of users, select the user you want to drop/delete.

There are two ways to delete a user with a subtle difference. If you want to delete an already existing user and you know its name, you can run the simple “DROP USER” command, along with the user name and its hostname. Like this:

But if you do not know or remember the name of the user and have a hunch of the user’s name, then MySQL provides the IF EXISTS clause to help in such scenarios. If the user’s name exists in the MySQL against the provided name in the query, it will get deleted for sure. Otherwise, it won’t get deleted. However, if we do not utilize the IF EXISTS clause, MySQL won’t work, and you would see an error. So, it is recommended to use the IF EXISTS clause if you are not sure about the existence of the user’s name in MySQL. The general syntax if you want to use the IF EXISTS clause is shared below:

You can delete or drop multiple users in a single query as well by running the following command in MySQL’s shell:

When you have deleted the user, you can check the list of users again, whether the user still existed in the list or not.

You can see in the list that the deleted user or users are not there anymore.

So, this is how we can delete or drop a user in MySQL using the DROP command.

Conclusion

In this article, we have learned two different syntaxes for deleting the user in MySQL. We have also learned to delete multiple users in a single query as well.

MySQL is a well-known database derived from standard SQL. It is one of the most popular databases out there. MySQL allows you to perform CRUD operations and all other major commands needed when managing a database. When you need data from different tables based on specific conditions, MySQL provides joins to handle these types of tasks. This article covers the MySQL inner join in detail.

What is an inner join? An inner join is the same as a simple join. An inner join returns common records or rows from the provided condition(s) and tables. We can use any of these clauses and we will still have the same results. Let us look at some examples to show you how to correctly use inner joins in MySQL.

Examples

Before learning the usage of inner joins, We can get the result from two different tables based on the condition(s) by using the SELECT statement and the WHERE clause. In the following example, ‘books’ and ‘authors’ are two different tables in a database.

In the ‘books’ table, we have the foreign author_id key from the ‘authors’ table.

To get all the columns from both tables, we set books.author_id = authors.author_id. The SELECT query would be as follows:

As you can see in the image above, we have obtained all columns from both tables. Often, it does not look good to have all the columns, even if they are not needed. So, if you want to obtain only certain columns from both tables, you will need to mention the column names in the SELECT statement, as follows:

As you can see, we have a clean and clear-cut output of the four provided columns from both tables.

Now, we will perform the same task using the INNER JOIN clause.

To join two tables using the INNER JOIN clause, the SELECT query would be as follows:

As you can see in the screenshot above, we have obtained the same output, but using the INNER JOIN clause this time.

As stated earlier, the INNER JOIN clause is the same as a simple JOIN clause. This means that we can use the JOIN clause instead of the INNER JOIN clause and still obtain the same results. The SELECT query with the simple JOIN clause would be as follows:

As you can see in the image above, we have obtained the same results. This shows you that the simple JOIN and INNER JOIN clauses are the same. You can get the same results using any of these join clauses.

The concept of the inner join does not stop here. In previous examples, we applied the join on two tables on the basis of the author_id key. Since we know that the author_id key is already the foreign key in the ‘books’ table, we may shorten the syntax by using the USING clause with the join. The syntax for using the USING clause with the JOIN clause is as follows:

It can be observed that this query has generated the same results with the USING clause.

Similarly, we can apply a condition along with applying the join between two tables using the WHERE clause. For example, to get the same four columns from both tables  in which the author’s last name is equal to ‘Hill,’ the query for getting such output will be:

As you can see in the image above, we only obtained two rows in which the last name of the author is ‘Hill.’

So, now, you have seen some examples of the different ways to use an inner join to get the results you want in MySQL.

Conclusion

In this article, we tried a couple of different examples of using the inner join to provide a more complete understanding of the concept. You also learned how to use the USING and WHERE clauses with the inner join, as well as how to obtain the required results in MySQL. For more useful content like this, keep visiting our website, linuxhint.com.

MySQL is an open-source Database Management system, which can be used for both small and large projects. Developed by The Oracle Corporation, it uses standard SQL behind it. In database management, CRUD operations are a kind of basic requirement to know.

In this article, we will learn to update the data in MySQL tables using MySQL’s provided UPDATE statement. An UPDATE statement is basically a DML (Data Manipulation Language) statement because it modifies or updates the data.

Syntax

The syntax for updating a column or columns in a table in MySQL is:

In this syntax, table_name is the table in which you are willing to update any column.

By using the SET clause, we can assign new values to multiple columns by using the equal sign “=”.

column_name is that column where you want to make an update.

In the end, we can also provide the WHERE clause to apply some condition or filter the updating process.

Let’s make it clear by showing an example in which we would update the value of a column inside a table.

Example

First, open up your terminal and log in to the MySQL shell and choose the database in which you want to update a table. In order to see all the tables inside a database, run this command:

We have one table in our selected database. Let’s see whether some data exist in it or not. To see the data in a table, run the SELECT command as follows:

Suppose we want to update the car’s name and manufacturing date of the third row from “BMW I8” to “BMW M4” where car_id is 3, Then the query for updating the name and date would be like this:

After running the update command and having an output of “1 row(s) affected”, now, let’s view the table:

As you can see in the screenshot given below, the data of the third row is updated successfully.

So, this is how you can update the data of any table in MySQL using the UPDATE statement.

Conclusion

We have come to know the utilization of UPDATE statement and how we can update data in any MySQL database. Then, we have discussed how to update a single column, as well as multiple columns at once. Lastly, we have also seen the use of the WHERE clause.

MySQL is one of the most famous open-source and freely available DBMS (Database Management Software System). It has an easy-to-use interface and is famous for its speed. If you are doing a job as a database administrator in some big organization, you often need to go through a large number of databases and their tables. So, in this article, we are going to learn how we can list or show tables in the MySQL shell.

In order to get started with listing and showing the tables in a database, login to MySQL shell as a root user by running the following command in the terminal:

Then, select the database by running the USE statement of MySQL:

If you don’t know what databases you have, you can list the databases by running the SHOW DATABASES command of MySQL:

After selecting a database, the simplest and easiest way to list tables is to run the SHOW TABLES statement of MySQL in the shell:

You can see the list of tables in the selected database in the screenshot below.

However, this list only contains the name of the tables. MySQL provides another statement to show the type of the table. Either it is a view or a base table. We can see the table type as well by adding the FULL clause in the SHOW TABLES statement:

As you can see in the screenshot below, we have got the table type as well in the second column, along with the table names.

In MySQL, we can actually list or show tables without selecting a database first. As we did earlier, we do not need to select a database first before listing the tables. We can get the list of tables of any database by running the following command:

Or if you have a long list of tables and you want to filter through them. You can also use the LIKE clause to do so:

To understand the pattern. Suppose we want to list all the tables whose name starts from the ‘tes’. The command for showing the tables will be like this:

The percentage ‘%’ sign denotes that there can be any or no character after that.

Just like how we listed tables without selecting the database first. We can list the tables from a specific database without logging into the MySQL shell as well. In order to accomplish this, run the following command in the terminal:

The ‘-e’ is for executing the MySQL statement.

As you can see in the screenshot, we got the same output or list of tables in the terminal without logging into the MySQL shell and selecting the database.

So, these are some of the ways to show the tables and filter them.

Conclusion

In this article, we have learned how to show the tables in a database in MySQL using different methods. We have also learned how to filter the list of tables using the LIKE clause.

MySQL is the most widely used, free open-source DBMS (Database Management System). It is used by some of the famous organizations like NASA, US NAVY, Tesla, Twitter, Spotify, and a lot more. If your work is related to database administration tasks for huge organizations and corporations, responsible for data integrity, and care for the privileges that users have over too many databases, then this article is for you. It will guide you in listing the users, look at the privileges they have, and list the users on behalf of a database. This is because we can list the users using MySQL’s own built mysql.user table.

To list down the users in MySQL, we first need to login to the MySQL server.

We can log in to the MySQL server as a root user by running the following command:

If you are unable to login, there is a possibility that your system’s mysql.service is not active and running. So, to start the service, run the following command:

To check the status of the service, use the following command:

If it is active and running, try to log in now.

After logging in to the MySQL server, we can list all the users regardless of the access they have over the different databases by using the SELECT statement and MySQL’s build mysql.user table.

As you can see in the screenshot, we got a lot of information. MySQL has a table for the list of users. So, if we want to trim down the columns to have a few columns instead of using an asterisk, MySQL provides the DESC statement to get all the column names and fields of the user’s table.

To get the user’s table run the following command:

After having a look at the table, we can list a limited amount of information and have a few columns about the user. Instead of using an asterisk sign in the SELECT statement, we can provide the column names and list the users by running the  command in the example below:

As you can see in the screenshot attached, we have a list of only two columns now.

Conclusion

This article contains a very basic and easy to follow concept to list the users in MySQL using different techniques. We have learned how we can use the column names of myqsl.user to trim the list and have a better insight.

So, for more useful content and a better understanding of database concepts, keep visiting linuxhint.com.

MySQL is a relational database management system that provides quick and reliable solutions. It is well-known for its ability to execute quickly and for its unique and straightforward user experience. Performing CRUD operations are the core operations and basic concepts when working with databases. In this article, you will learn how to delete a table in a database.

Before learning more about the deletion of tables using MySQL, be sure that you have the latest version of MySQL installed on your computer. Also, ensure that you have a database and a table in it that you want to delete. In this article, we assume that you understand the basic concepts of MySQL statements and that you have a database and table in MySQL that you would like to delete.

You can figure out the version of MySQL running on your system by running the ‘mysql -V’ command:

You can now move forward knowing that you have the latest version installed.

To figure out whether MySQL is working properly, run the following command:

If the service is not running, then you can activate the service using the command below:

After starting it, connect to the MySQL server as a root user with superuser privileges using sudo. Otherwise, you can enter a custom username instead of the root username.

The following steps show the table deletion process for MySQL servers in the command-line terminal.

After entering the MySQL shell, list the databases and choose the database from which you want to delete a table.

Select the correct database by running the USE statement with the database name.

After choosing the database from the list, choose the table, as well. To see a list of the tables in the database, run the SHOW TABLES command:

Now, choose the table that you would like to delete. To delete the table, run the “DROP TABLE” command and provide a table name, for example:

If you are unable to delete or drop a table, make sure that you have the correct privileges for that table. If you do not have the privileges issue but are still getting an error when attempting to delete a table, then you may be trying to delete a nonexistent table, or there could be a spelling mistake. To avoid this error, MySQL provides the “IF EXISTS” clause. If you use this clause, MySQL will not throw any errors if no table exists of the given name in the query in the database. The “IF EXISTS” clause has a specific syntax that needs to be followed, shown below:

Conclusion

This article includes two different methods of deleting an existing table in a MySQL database, both with and without using the “IF EXISTS” clause. The article also described the difference between these two methods for your convenience.