MySQL Lecture 8·How to perform mathematical calculations, string processing and conditional judgment?

Hello, I am safe and sound.

Article directory

• How to perform mathematical calculations, string processing and conditional judgment?
• • Math functions
  • String functions
  • conditional judgment function
  • Summarize

How to perform mathematical calculations, string processing and conditional judgment?

MySQL provides many functions that are powerful and very convenient to use, including mathematical functions, string processing functions, and conditional judgment functions.

In many scenarios, we will use these functions. For example, in the actual development process of supermarket projects, there will be such requirements:

• The rule of member points is that one point is accumulated for one dollar, and no points are earned if the amount is less than one dollar. This requires the use of the mathematical function floor() that rounds down;
• When printing receipts, the width of the cash register paper is fixed. How can we make the printing result clear and neat? At this time, you need to use string processing functions such as concat();
• Different data are processed in different ways. How to choose the correct processing method? This will use conditional judgment functions such as IF(expression, V1, V2);…

These functions are of great help to us in managing the database and improving the efficiency of data processing.

Mathematical functions

Let’s first learn mathematical functions, which are mainly used to process numerical data. There are three commonly used functions, namely the rounding function round(), ceil(), floor(), the absolute value function abs() and the remainder function mod( ).

Knowing these functions, let’s look at the specific needs of supermarket operators. They proposed that in order to increase sales, member marketing should be carried out, mainly to give members points, and to give members certain discounts based on the number of points.

The rules of points are also very simple, that is, you will get one point for every dollar spent. If you spend less than one dollar, you will not get points. Then we need to round up the sales amount.

Four tables are mainly used here, namely sales order details table, sales order header table, product information table and member information table. To facilitate understanding, the table structure and data are simplified.

Sales order details:

Sales order table:

Product information table:

Member information form:

In this scenario, you can use the rounding functions of mathematical functions in MySQL. There are three main types:

• Round up ceil(x) and ceiling(x): return the smallest int type integer greater than or equal to x;
• Round down floor(x): Returns the largest int integer less than or equal to x;
• Rounding function round(x, d): x represents the number to be processed, d represents the number of decimal places retained, and the processing method is rounding. round(x) means retaining 0 decimal places.

The current integration rule is one dollar for one integral. If the value is less than one dollar, there will be no integration. Obviously, it is rounded down, so you can use the floor() function.

First, we need to obtain relevant information about member consumption through related queries:

mysql> select
    -> c.membername as 'member', -- Get the member name from the membership table
    -> b.transactionno as 'order number',-- Get the order number from the sales order header table
    -> b.transdate as 'Transaction Time', -- Get the transaction time from the sales order table
    -> d.goodsname as 'product name', -- Get the product name from the product information table
    -> a.salesvalue as 'transaction amount'
    -> from
    -> demo.transactiondetails a
    ->join
    -> demo.transactionhead b on (a.transactionid = b.transactionid)
    ->join
    -> demo.membermaster c on (b.memberid = c.memberid)
    ->join
    -> demo.goodsmaster d on (a.itemnumber = d.itemnumber);
 + ------ + ------------------ + -------------------------- + - --------- + ---------- +
| Member | Order number | Transaction time | Product name | Transaction amount |
 + ------ + ------------------ + -------------------------- + - --------- + ---------- +
| Zhang San | 0120201201000001 | 2020-12-01 14:25:56 | Book | 176.22 |
| Zhang San | 0120201201000001 | 2020-12-01 14:25:56 | Pen | 24.75 |
| Li Si | 0120201202000001 | 2020-12-02 10:50:50 | Book | 234.96 |
| Li Si | 0120201202000001 | 2020-12-02 10:50:50 | Pen | 26.40 |
 + ------ + ------------------ + -------------------------- + - --------- + ---------- +
4 rows in set (0.01 sec)

Next, we use floor(a.salesvalue) to round down the sales amount to obtain the member points value. The code is as follows:

mysql> select
    -> c.membername as 'member',
    -> b.transactionno as 'odd number',
    -> b.transdate as 'Transaction Time',
    -> d.goodsname as 'goods name',
    -> a.salesvalue as 'transaction amount',
    -> FLOOR(a.salesvalue) as 'Points' -- Use the FLOOR function to round down
    -> from
    -> demo.transactiondetails a
    ->join
    -> demo.transactionhead b on (a.transactionid = b.transactionid)
    ->join
    -> demo.membermaster c on (b.memberid = c.memberid)
    ->join
    -> demo.goodsmaster d on (a.itemnumber = d.itemnumber);
 + ------ + ------------------ + -------------------------- + - --------- + ---------- + ------ +
| Member | Order number | Transaction time | Product name | Transaction amount | Points |
 + ------ + ------------------ + -------------------------- + - --------- + ---------- + ------ +
| Zhang San | 0120201201000001 | 2020-12-01 14:25:56 | Book | 176.22 | 176 |
| Zhang San | 0120201201000001 | 2020-12-01 14:25:56 | Pen | 24.75 | 24 |
| Li Si | 0120201202000001 | 2020-12-02 10:50:50 | Book | 234.96 | 234 |
| Li Si | 0120201202000001 | 2020-12-02 10:50:50 | Pen | 26.40 | 26 |
 + ------ + ------------------ + -------------------------- + - --------- + ---------- + ------ +
4 rows in set (0.01 sec)

Similarly, if the user’s points rule is changed to “one point for less than one yuan”, which actually means rounding up the amount, in this case, we can use the CEIL() function. The operation method is the same as before, so I won’t explain it in detail here.

Next, let’s talk about the usage of rounding function round():

Supermarket operators proposed that when checking out, the amount receivable can be set to which digit the amount should be rounded. For example, you can round to dollars, dimes, or cents.

Round the decimal according to the specified number of digits. In such a scenario, round(x, d) is used. Its function is to retain d decimal places for the value x by rounding.

According to the requirements of the supermarket operator, we set the number of decimal places d in the function round(x, d) to 0, 1, 2

If we want to be accurate to minutes, we can set it to two decimal places:

mysql> select round(salesvalue,2) -- D is set to 2, indicating that 2 decimal places are retained, which is accurate to the minute
-> from demo.transactiondetails
-> where transactionid=1 and itemnumber=1;
 +---------------------+
| ROUND(salesvalue,2) |
 +---------------------+
| 176.22 |
 +---------------------+
1rows in set (0.00 sec)

In addition to the functions we just learned, MySQL also supports the absolute value function ABS() and the remainder function MOD(). ABS(X) means to get the absolute value of X; MOD(X, Y) means to get the value of X divided by Y. remainder.

These functions are relatively simple to use. Just focus on understanding their meaning. Let’s learn about string functions.

String functions

In addition to mathematical calculations, we often encounter scenarios where we need to process strings. For example, if we want to add the “￥” symbol in front of the amount, we will use the string splicing function; for another example, we need to When a set of numbers is transmitted over the Internet in the form of a string, a type conversion function is used.

There are 4 more commonly used string functions:

• concat(s1, s2, …): means concatenating the strings s1, s2… to form a string
• cast(expression as char): means converting the value of the expression into a string
• char_length (string): means getting the length of the string
• space(n): means getting a string consisting of n spaces

Next, I will use the actual application scenarios in the supermarket project to explain how to use these string functions.

After the customer pays the money and completes the transaction, the system must print a receipt. When printing receipts, there are many format requirements. For example, a small ticket paper, 57 mm wide, can probably type 32 characters, which is 16 Chinese characters. The user requires that a transaction should have two lines. The first line is product information, and the second line should include four types of information: quantity, price, discount and amount. So, how can we print this information clearly on the receipt and print it neatly and beautifully? This involves the processing of strings.

First, let’s take a look at how to print the first line of product information. Product information includes: product name and product specifications, and product specifications must be included in parentheses. In this way, the product name and product specifications must be concatenated into a string.

We can use the concatenating string function concat() as follows:

mysql> select
-> concat(goodsname, '(', specification, ')') as product information -- here the product name, brackets and specifications are spliced together
-> from
-> demo.goodsmaster
-> where itemnumber = 1;
 + ---------- +
| Product Information |
 + ---------- +
| Book (16 pages) |
 + ---------- +
1 row in set (0.00 sec)

In this way, we get the product with product number 1, and its product information is: “Book (16 karat)”.

In the second step, let’s take a look at how to print the second line. The second line includes quantity, price, discount and amount, a total of 4 types of information.

Because a line has a maximum of 32 characters, we allocate 7 characters to the quantity, 7 characters to the price, 6 characters to the discount, and 9 characters to the amount, plus the middle 3 characters, which is exactly 32 characters.

Explain why it is allocated this way?

• The quantity is 7 characters, that is, 3 digits are given before the decimal point, 3 digits are given after the decimal point, plus 1 decimal place, the maximum is 999.999, which basically meets the needs of retail
• In the same way, the price is given to 7 digits, which means 4 digits before the decimal point, 2 digits after the decimal point, plus the decimal point, so that the maximum value can be expressed as 9999.99
• The discount is 6 digits, 2 digits after the decimal point, 2 digits before the decimal point, plus the decimal point and “%”, this is enough
• The amount has 9 digits and can be displayed up to 999999.99, which is enough.

After allocating the string size of each part of the information, let’s explain the format processing, because the value of the data will be different every time, and if printed directly, it will be uneven.

Here is the quantity as an example:

The first step is to convert the number into a string. Here we need to use the cast() function to convert the number into a string, as shown below:

mysql> select
-> CAST(quantity as CHAR) -- Convert decimal type to string
-> from
-> demo.transactiondetails
-> where
-> transactionid = 1 and itemnumber =1;
 +---------------------+
| CAST(price as CHAR) |
 +---------------------+
| 2.000 |
 +---------------------+
1 rows in set (0.00 sec)

The second step is to calculate the length of the string. Here we need to use the char_length() function.

It should be noted that although each Chinese character occupies 2 characters in length when printed, this function obtains the number of Chinese characters. Therefore, if there are Chinese characters in the string, the length of the string obtained by the function is different from the actual length and needs to be filled with spaces.

We can use the following query to get the string length after converting the quantity field into a string.

mysql> select
-> char_length(CAST(quantity as char)) as length
-> from
-> demo.transactiondetails
-> where
-> transactionid = 1 and itemnumber =1;
 +---------------------+
| Length |
 +---------------------+
| 5 |
 +---------------------+
1 rows in set (0.00 sec)

The third step is to fill in the 7-digit length with spaces. At this time, we need to use the space() function.

Because we use left-aligned printing (left-aligned means that the string starts from the left, and the empty positions on the right are filled with spaces), we need to concatenate the strings first, and then fill in the spaces after the strings:

mysql> select
-> concat(CAST(quantity as char),
-> space(7 - char_length(cast(quantity as char)))) as quantity
-> from
-> demo.transactiondetails
-> where
-> transactionid = 1 and itemnumber = 1;
 + ---------- +
| Quantity |
 + ---------- +
| 2.000 |
 + ---------- +
1 row in set (0.00 sec)

In addition, MySQL also supports SUBSTR(), MID(), TRIM(), LTRIM(), RTRIM().

Conditional judgment function

When we were splicing product information strings just now, there was an exception, that is, when the specifications were empty, the product information would become “NULL”. This result is obviously not what we want, because the name becomes NULL, customers will feel strange and will not know what product they bought. We hope that if the specification is a null value, there will be no need to add a specification. How to achieve it? This requires the use of conditional judgment functions.

The function of conditional judgment function is to return different values according to specific conditions. There are two commonly used ones:

• ifnull(v1, v2): means if the value of v1 is not null, return v1, otherwise return v2;
• if(expression, v1, v2): If the expression is true (TRUE), return v1, otherwise return v2

We hope that for products with empty specifications, when splicing product information strings, the specifications should not be empty. This problem can be solved through the ifnull(specification, ’) function. Specifically, it is judged whether the field ‘specification’ is empty. If it is empty, an empty string is returned. Otherwise, the value of the product specification is returned. The code is as follows:

mysql> select
    -> goodsname,
    -> specification,
    -> concat(goodsname,'(', ifnull(specification, ''),')') as concatenation -- Use conditional judgment function, if the specification is empty, the brackets are empty strings
    -> from
    -> demo.goodsmaster;
 + ---------- + --------------- + ---------- +
| goodsname | specification | splicing |
 + ---------- + --------------- + ---------- +
| Book | 16 karat | Book (16 karat) |
| pen | NULL | pen() |
 + ---------- + --------------- + ---------- +
2 rows in set (0.00 sec)

The result is that if the specification is empty, the product information becomes “Product Information ()”, which seems to be not bad. But there is also a problem: the empty bracket “()” after the product name will make customers feel strange. Can it be removed?

If you use the IFNULL (V1, V2) function, it is not easy to do, but it doesn’t matter. We can try to use another conditional judgment function IF (expression, V1, V2) to solve it. The expression here is ISNULL(specification). This function is used to determine whether the field “specificaiton” is empty. V1 returns the product name, and V2 returns the splicing specification of the product name. The code looks like this:

mysql> select
    -> goodsname,
    -> specification,
    -> -- Make a judgment here. If it is a null value, return the product name, otherwise, splice the specifications.
    -> if(isnull(specification),
    -> goodsname,
    -> concat(goodsname, '(', specification, ')')) as concatenation
    -> from
    -> demo.goodsmaster;
 + ---------- + --------------- + ---------- +
| goodsname | specification | splicing |
 + ---------- + --------------- + ---------- +
| Book | 16 karat | Book (16 karat) |
| pen | NULL | pen |
 + ---------- + --------------- + ---------- +
2 rows in set (0.02 sec)

Summary

The above only introduces some commonly used functions, but there are many more functions in MySQL.

When I meet An Ran and meet you, I live up to my code and live up to you.

Thank you for your time, see you in the next article~