MySQL knowledge learning-eleventh day: use data processing functions

Preface

Have a nice weekend~ It's too hot lately

function

Basically all languages ​​have functions, so I won't introduce them in detail here. We have already touched some functions before, such as concat, Rtrim, here are some things that need attention.

Insert picture description here

Use function

Most SQL implementations support the following types of functions:

1. Text functions used to process text strings (such as deleting or filling values, and converting values ​​to uppercase or lowercase)
2. Numerical functions used to perform arithmetic operations on numeric data (such as returning absolute values, performing algebraic operations)
3. Date and time functions used to process date and time values ​​and extract specific components from these values ​​(for example, return the difference between two dates, check date validity, etc.).
4. System functions that return special information that the DBMS is using (such as returning user login information, checking version details)

Text function

Earlier we came into contact with the RTEIM function, today we introduce another function- Upperfunction, which is used to convert text to uppercase, for the following table name

Insert picture description here


Insert picture description here

UpperThe usage of the function is Upper(str), that is to say, the data type of the column must be a character type.

Insert picture description here


Insert picture description here

Corresponding function usage is different, some need one parameter, some need multiple, and those who are interested can go to find the information by themselves.

Period and date processing functions

The following function is good to understand. When you need to find it, you can

Insert picture description here


look at the following table name. The data type of column birthday is datetime.

Insert picture description here


Whether you insert or update the table value or use the WHERE clause to filter, the date must be in the format yyyy-mm -dd.

Let's recall the usage of where

Insert picture description here

Is the above result correct? But is it reliable? We note that the birthday column stores the time in addition to the date. Is this correct? Let's modify the form a bit and

Insert picture description here


give him a time (if not, the default is 00:00:00.000000), and then test the code just now.

Insert picture description here


The result returned at this time is empty, why? We know that the above match is a complete match, because there is time later, at this time the system thinks that the two are different, so it will not return the result. So what to do? At this time, we need to use the date function date.

Insert picture description here


Insert picture description here


Now there is a question. What should we do if we need to find out all the records whose birthday is in July 1998? One of the methods is to
target the following table name

Insert picture description here
Insert picture description here


Or use the regular expressions we learned before,

Insert picture description here


or it can be like this

Insert picture description here

Numerical processing function

Numerical processing functions only process numerical data. These functions are generally mainly used for algebra, trigonometry or geometric operations, so they are not as frequently used as string or date-time processing functions

Insert picture description here

Today’s content is relatively small, remember to review the previous content~