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

This article mainly explains "what are the very practical MySQL functions". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the very practical MySQL functions"!

Catalogue

Description of functions in 1.MySQL

two。 One-line function classification

3. Character function

4. Mathematical function

5. Date-time function

6. Other commonly used system functions

7. Flow control function

8. Aggregate function

1) the function and classification of aggregate function

2) simple use of aggregate function

3) which data types are supported for the parameters passed in the five aggregate functions?

4) the use of aggregate functions and group by is "the most important"

Description of functions in 1.MySQL

"concept": similar to the method in java and python, it encapsulates a set of logical statements in the method body and exposes the method name.

"benefits": Ⅰ hides implementation details; Ⅱ improves code reusability

"call": select function name (argument list) [from table]

"Features": what is the name of Ⅰ (function name); what does Ⅱ do (function function)

"Classification": Ⅰ one-line function; Ⅱ grouping function

"what is a single-line function:": acts on each row of records in the table, and each record gives a result.

"what is an aggregate function:": acts on one or more rows and finally returns a result

two。 One-line function classification

Character function

Mathematical function

Date function

Other functions

Flow control function

3. Character function

1) length (str): gets the number of bytes of the parameter value

For the utf8 character set, one English occupies 1 byte and one Chinese occupies 3 bytes

For the gbk character set, one English occupies 1 byte and one Chinese occupies 2 bytes

Do the following:

2) concat (str1,str2, …) Stitching strings

Do the following:

3) upper (str): changes all letters in characters to uppercase

Do the following:

4) lower (str): lowercase all letters in a character

Do the following:

5) substr (str,start,len): the string is intercepted from the start position, and len represents the length to be intercepted; no len length is specified: it means that the string is intercepted to the end from start. Specified len length: indicates that len lengths are intercepted from the beginning of start.

Do the following:

6) instr (str, the substring to be found): returns the index of the first occurrence of the substring, and returns 0 if it cannot be found; when the searched substring exists in the string: returns the index of the first occurrence of the substring in the string. When the substring found is not in the string: 0 is returned.

Do the following:

7) trim (str): remove the spaces before and after the string; this function can only remove the spaces before and after the string, not the spaces in the middle of the string.

Do the following:

8) lpad (str,len, fill character): use the specified character to fill the string with the specified length on the left

Do the following:

9) rpad (str,len, fill character): use the specified character to fill the string with the specified length

Do the following:

10) replace (str, substring, another string): replace the string in the string str with another string

Do the following:

4. Mathematical function

1) round (x, [reserved digits]): rounded; when rounding positive numbers: according to the normal calculation, it can be rounded. When rounding a negative number: first put the symbol aside, round the positive number after the minus sign is removed, and then fill in the minus sign when it is finished.

Do the following:

2) ceil (x): round up and return the smallest integer > = this parameter. (ceiling function) ceiling function, which exists in excel,python. Just imagine the ceiling of your house, throw this number on the ceiling and find the smallest integer greater than or equal to this number.

Do the following:

3) floor (x): rounded down, return = 60, medium: 70-80, excellent: 80-90, excellent: > = 90

Do the following:

Highest score of select sc.c,cname,max (score), lowest score of min (score), average score of avg (score), passing rate of sum (case when score > 60 then 1 else 0 end) / count (*), excellent rate of sum (case when score > = 70 and score=80 and score=90 then 1 else 0 end) / count (*) from sc left join course on sc.c = course.cgroup by sc.c

The results are as follows:

8. Aggregate function 1) function and classification of aggregate function

The function of ① aggregate function

Used as statistics, also known as aggregate functions or statistical functions or group functions.

Classification of ② aggregate functions; sum summation of avg average value, max maximum value, min minimum value count calculation number

2) simple use of aggregate function

3) which data types are supported for the parameters passed in the five aggregate functions?

Mysql is not a strongly typed programming language. In other words, the execution results of some statements may not report errors, but the execution results have no practical significance, so we also think that they are incorrect.

① test data

"Table creation statement" create table test (id int primary key auto_increment, name varchar (20) not null, sal int, birth date) charset=utf8 "insert data" insert into test (name,sal,birth) values ("Zoo", 6500 last year 1993.3.20'), ("Hobby", 4000 last month 1997.6.10'), ("Aline", 5500 last month 2000.5.1'), ("Bob", 10000 last month 2008.10.1')

② sum () function and avg () function: passing in integer / decimal types makes sense

The conclusions are as follows:

The sum () function and the avg () function do not make much sense for the calculation of string types and date / time types. Therefore, the sum () function and the avg () function are only used to sum decimal types and integers.

③ max () function and min () function: it is significant to introduce integer / decimal type and date / time type.

The conclusions are as follows:

Passed in max () and min () is the "integer / decimal type", which calculates the maximum and minimum values of the value. Max () and min () pass in the "date type". The maximum value calculated by max () is the date closest to us, and the minimum value calculated by min () is the date farthest away from us. The string type is passed in max () and min (). The maximum value calculated by max () is displayed in English alphabetical order, and the minimum value calculated by min () is also displayed in English alphabetical order, which is of little significance.

④ count () function: you can pass in any data type, but be careful when you encounter null

The conclusions are as follows:

The count () function can pass in any data type to represent the row count.

"but the following knowledge points require special attention."

First of all, let's take a look at the meaning of count (sal) and count (birth). These two sentences respectively represent

Count the number of rows in sal column fields and birth column fields. Because one of the records is a null value, use count ()

The null line is ignored when the function is counted.

Second, for count (*), it means to count how many rows there are in [the whole table], which must be correct for the number of rows in the original data.

Statistically, as long as the value of a column field in a row of the entire table is not null,count (*), the row is considered to be a row. Of course, if one

The whole row is null, and you don't have to insert this record.

Summary:

When there is no null value in a field column, "count (column field) = count (*)."

When there is a null value in a field column, the count (column field)

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.