MySQL must know-11th chapter-Using Data processing functions

-The 11th chapter uses data processing Functions "> 11th use data processing functions

This chapter describes what functions are, what functions MySQL supports, and how to use these functions.

-11.1 Function ">11.1 function"

Like most other computer languages, SQL supports the use of functions to process data. Functions are generally performed on data, which facilitates the conversion and processing of data. The RTrim (), which is used to remove trailing spaces in the previous chapter, is an example of a function.
A function that does not have the portability of SQL can be called Portable (portable) code that runs on multiple systems. In contrast, most SQL statements are portable, and when there are differences between SQL implementations, these differences are often less difficult to handle. But the portability of the function is not strong. Almost every major DBMS implementation supports functions that are not supported by other implementations, and sometimes the differences are large. For code portability, many SQL programmers do not approve of the use of specially implemented features. While this is beneficial, it does not always benefit the performance of your application. If you do not use these functions, it can be difficult to write some application code. Other methods must be used to achieve the work that the DBMS has done very effectively. If you decide to use a function, you should be sure to comment on the code so that you (or someone else) will know exactly what the SQL code is written in the future.

-11.2 using function ">11.2"

Most SQL implementations support the following types of functions.

• A text function that handles text strings (such as deleting or populating values, converting values to uppercase or lowercase).
• A numeric function that is used to perform arithmetic operations on numeric data, such as returning absolute values and doing algebraic operations.
• A date and time function that handles date and time values and extracts specific components from these values (for example, returns the difference of two dates, checks for date validity, and so on).
• Returns system functions for special information that the DBMS is using, such as returning user logon information and checking version details.

-11.2.1 Text Processing function ">11.2.1 Text processing function

In the previous chapter we have seen an example of a text processing function that uses the RTrim () function to remove the space to the right of the column value. Here is another example, this time using the upper () function:

As you can see, Upper () converts text to uppercase, so each vendor in this example is listed two times, the first is the value stored in the Vendors table, and the second time is converted to uppercase as a column vend_name_upcase.
Table 11-1 lists some of the commonly used text processing functions.

The Soundex in table 11-1 need further explanation. Soundex is an algorithm that converts any text string into an alphanumeric pattern that describes its voice representation. Soundex considered similar pronounced characters and syllables, making it possible to compare strings in pronunciation rather than letters. Although SOUNDEX is not a SQL concept, MySQL (like most DBMS) provides support for Soundex. An example of using the SOUNDEX () function is given below. The Customers table has a customer Coyote Inc., whose contact name is Y.lee. But if this is an input error, this contact name should actually be y.lie, what should I do? Obviously, searching by the correct contact name does not return data, as follows:

Now try the search using the Soundex () function, which matches all contact names that sound similar to Y.lie:

In this example, the WHERE clause uses the SOUNDEX () function to convert cust_contact column values and search strings for their soundex values. Because Y.lee and Y.lie are pronounced similar, their soundex values match, so the WHERE clause correctly filters out the required data.

-11.2.2 date and time processing functions >11.2.2 date and time handlers

Dates and times are stored in the appropriate data type and in a special format so that they can be sorted or filtered quickly and efficiently, and save physical storage space. In general, applications do not use the format used to store dates and times, so date and time functions are always used to read, count, and process these values. For this reason, date and time functions play an important role in the MySQL language.
Table 11-2 lists some of the commonly used date and time processing functions.

So far, we have filtered the data using the WHERE clause of the comparison value and the text, but the data often needs to be filtered by date. Filtering with dates requires attention to some other problems and the use of special MySQL functions. The first thing to note is the date format used by MySQL. Whenever you specify a date, whether it is inserting or updating table values or filtering with a WHERE clause, the date must be in format YYYY-MM-DD. Therefore, September 1, 2005, given for 2005-09-01. Although other date formats may also be OK, this is the preferred date format because it excludes ambiguities (for example, 04/05/06 is May 4, 2006 or April 5, 2006 or May 6, 2004 or ...). ）。
The 4-digit year should always be used to support 2-digit years, with MySQL processing 00-69 as 2000-2069 and processing 70-99 to 1970-1999. Although they may be the year that is intended, it is more reliable to use the full 4-digit year because MySQL does not have to make any assumptions. Therefore, the basic date comparison should be simple:
! [pic] (MySQL must know-11th chapter-Using Data processing functions/snipaste_2018-04-13_18-40-50

This SELECT statement runs correctly. It retrieves an order record with the order_date of the order record for 2005-09-01. But is it reliable to use where order_date = ' 2005-09-01 '? The Order_date data type is DateTime. This type stores the date and time values. The values in the sample table all have a time value of 00:00:00, but in practice it is probably not always the case. What if the order date is stored with the current date and time (so that you know not only the order date but also the time of the next order)? For example, the stored order_date value is 2005-09-01 11:30:05, where order_date = ' 2005-09-01 ' fails. Even if a row with that date is given, it is not retrieved because the where match failed. The workaround is to instruct MySQL to compare the given date only with the date part in the column, rather than comparing the given date with the entire column value. To do this, you must use the date () function. Date (order_date) indicates that MySQL extracts only the date portion of the column, and a more reliable SELECT statement is:

If you want a date, use Date () If you want only the date, then using date () is a good habit, even if you know that the corresponding column contains only the date. This way, if you have a date and time value later in the table for some reason, your SQL code does not have to change. Of course, there is also a time () function, which you should use only when you want to. Date () and time () are first introduced in MySQL 4.1.1. The use of other operators (described in the 6th chapter) is clear after you know how to use dates for equality testing. However, there is a date comparison that needs to be explained. What if you want to retrieve all orders from September 2005? A simple equality test is not possible, because it also matches the number of days in the month. There are several workarounds, one of which is shown below:

Where the between operator is used to define 2005-09-01 and 2005-09-30 as a date range to match. There is another way (one that does not need to remember how many days each month is or does not need to worry about leap year February):

Year () is a function that returns years from a date (or datetime). Similarly, month () returns months from the date. Therefore, where year (order_date) = 2005 and Month (order_date) = 9 Retrieves all rows of the Order_date for September 2005.
MySQL version difference MySQL 4.1.1 adds many date and time functions. If you are using an earlier version of MySQL, you should consult the specific documentation to determine which functions you can use.

-11.2.3 Numeric processing function ">11.2.3 Numeric processing function

Numeric processing functions work with numeric data only. These functions are generally used primarily for algebraic, triangular, or geometric operations, so there are no strings or date-time processing functions that are used so frequently. Ironically, in the function of the primary DBMS, the numeric function is the most consistent and uniform function.
Table 11-3 lists some of the commonly used numeric processing functions.

-11.3 Summary ">11.3 summary

This chapter describes how to use SQL data processing functions, and focuses on date processing functions.

MySQL must know-11th chapter-Using Data processing functions