Shulou(Shulou.com)06/01 Report--

Navicat Premium is a management tool that can connect to multiple databases. It allows you to connect to MySQL, Oracle and PostgreSQL databases simultaneously with a single program, making it more convenient to manage different types of databases. If you like it, you might as well download the trial version and feel it.

CASE is a flow control statement that works very similar to the IF-THEN-ELSE statement. The CASE statement can select values based on the data to pass through the condition and return a value when the first condition is met. Therefore, once the condition is established, it shorts, ignoring the following clause and returning the result. As we have seen in today's article, it can be used to test conditions and discrete values.

Basic grammar

The CASE statement takes two forms: the first evaluates one or more conditions and returns the result where the first condition is true. If none of the conditions are true, the result after ELSE is returned, and if there is no ELSE section, NULL is returned:

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE resultEND

The second CASE syntax returns the first value = compare_value to compare the result to true. If no comparison is true, the result after ELSE is returned. If there is no ELSE section, NULL is returned:

CASE compare_value WHEN condition1 THEN result1 WHEN condition2 THEN result2 WHEN conditionN THEN resultN ELSE resultEND

Some examples

To try the case statement, we will use Navicat Premium to write some queries against the Sakila sample database. It is a powerful database development and management tool that can connect to most popular databases simultaneously, including MySQL, MariaDB, MongoDB, SQL Server, Oracle, PostgreSQL and SQLite databases. It is also compatible with many cloud databases, such as Amazon RDS, Amazon Aurora, Amazon Redshift, Microsoft Azure, Oracle Cloud, Google Cloud and MongoDB Atlas.

Grammar 1

This is a query that selects a list of movie titles, as well as their year of release and rental price:

We will add a column to divide the rental price into three categories: "discount", "regular" and "premium". The price range is:

Less than $2.99

Between $2.99 and $4.99

Over $4.99

To help with the case statement, Navicat provides a code snippet that you can simply drag and drop into the SQL editor. Although you can create your own, Navicat comes with a number of standard SQL statements, including DDL and flow control statements. In fact, you will find the CASE statement at the top of the flow control list:

After you put the code snippets into the editor, the editable parts are color-coded to help identify them. You can use the Tab key to move from one to the next.

Because these statements are generic in nature, you may need to modify them slightly to suit your specific database type. This is the complete CASE statement and query for MySQL:

Grammar 2

The second CASE syntax is ideal for testing discrete values against two or more conditions. For example, we can use it to add target audience lists based on movie ratings:

Conclusion

In today's article, we learned how to use SQL CASE statements to select values based on underlying data. The sample SQL statement is written in Navicat Premium. It helps you quickly write code-complete and customizable code snippets by getting suggestions for keywords and removing duplicates from the coding.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.