directory

1.MySQL provides a description of functions

  • concept: Similar to the method in Java and Python, encapsulates a group of logical statements in a method body and exposes the method name.
  • benefits: ⅰ Hidden implementation details; ⅱ improve code reuse;
  • call: select function name (argument list) [from table];
  • The characteristics of: ⅰ what to call (function name); ⅱ what to do (function function);
  • classification: ⅰ single-line function; ⅱ Grouping function;
  • What is a single line function: applies to each row of records in the table, a record out of a result;
  • What is an aggregation function: applies to one or more rows and returns a result;

2. Classification of single-line functions

  • Character function;
  • Mathematical function;
  • Date function;
  • Other functions;
  • Flow control function;

Character functions

1) length(STR) : the number of bytes of the parameter value;

  • forutf8For character sets, an English character is 1 byte; A Chinese character is 3 bytes;
  • forgbkFor character sets, an English character is 1 byte; A Chinese character is 2 bytes;

The operation is as follows:

2) the concat (str1 str2,…). : concatenated string;

The operation is as follows:

3) upper(STR) : uppercase all letters in a character;

The operation is as follows:

4) lower(STR) : Lower all letters in a character to lowercase;

The operation is as follows:

5) substr(STR,start,len) : Truncate the string from the start position,len indicates the length to be truncated;

  • Len length not specified: truncates from start to the end of the string.
  • Len length specified: indicates that len length is truncated from start.

The operation is as follows:

6) instr(STR, the substring to look for) : returns the index of the first occurrence of the substring, or 0 if not found;

  • Returns the index of the first occurrence of the substring in the string when the substring is found in the string.
  • When the substring is not in the string: returns 0.

The operation is as follows:

7) Trim (STR) : Trim the space before and after the string;

  • This function can only remove Spaces before and after a string, not in the middle of the string.

The operation is as follows:

8) lpad(STR,len, fill character) : with the specified character, to achieve the specified length of the string left padding;

The operation is as follows:

9) rpad(STR,len, fill character) : with the specified character, to realize the string right fill specified length;

The operation is as follows:

10) replace(STR, substring, another string) : Replace the string in STR with another string;

The operation is as follows:

4. Mathematical functions

1) Round (x,[reserved digits]) : round;

  • When theA positive numberforrounded: According to the normal calculation method, can be rounded.
  • When theA negative numberforrounded: First put the sign aside, to remove the negative sign after the positive number to round, after completion, and then put the negative sign, fill can.

The operation is as follows:

2) ceil(x) : round up, return >= the minimum integer of this parameter. (Ceiling function)

  • Ceiling functionThis function exists in Excel and Python. You just think about the ceiling of your house, throw this number on the ceiling, and find outGreater than or equal toOf this numberThe smallest integer.

The operation is as follows:

3) floor(x) : round down, return <= the maximum integer for this parameter. (Floor function)

  • The floor functionThis function exists in Excel and Python. You just think about the floor of your house, throw this number on the floor, and find outLess than or equal toOf this numberThe largest integer.

The operation is as follows:

4) TRUNCate (x,D) : truncate;

  • Truncate function“, also exist in Excel, Python, basically the same meaning. But this functionIt's a little hard to understandI’m here to take you to learn.

The understanding is as follows:

"Refer to the illustration below to experience the following text."
1(D is positive and operates on the decimal part to the right of the decimal point. D=1, straight from no1In one place, cut off the back part. D=2, straight from no2In one place, cut off the back part. .2D) is0, just get rid of the decimal part.3D is a negative number and operates on the integer to the left of the decimal point. D=- 1Directly from the- 1At that point, I cut off the decimal part and"From the current position (including the current position), after the integer part replaced by 0". D=2 -Directly from the2 -At that point, I cut off the decimal part and"From the current position (including the current position), after the integer part replaced by 0".Copy the code

Figure:

The operation is as follows:

5) mod(dividend, divisor) : mod;

  • When the dividend is positive, the result is positive.
  • When the dividend is negative, the result is negative.

The operation is as follows:

5. Date and time functions

  • Meaning of date: Year, month, day.
  • The Meaning of time: Refers to what we often call hours, minutes and seconds.

Before we talk about the following functions, let’s add one more thing: what do the different time formatters mean?

1) now() : returns the current date and time of the system;

The operation is as follows:

2) curdate() : returns only the current date of the system.

The operation is as follows:

3) curtime() : returns only the current system time, excluding the date;

The operation is as follows:

4) Get date and time middle, month, day, hour, minute, second;

  • Get year: year();
  • Get month: month();
  • Get date: day();
  • Get hour: hour();
  • Get the minute: minute();
  • Get seconds: second();

5) weekofyear() : get the week number of the current moment;

The operation is as follows:

6) quarter() : Get the quarter of the current moment;

The operation is as follows:

7) str_to_date() : converts a string in the date format to a date in the specified format;

The operation is as follows:

8) date_format() : converts a date to a date string;

  • %Y-%m-%dThe month returned is 01,02… That’s the format.
  • %Y-%c-%dThe month returned is 1,2… That’s the format.

The operation is as follows:

9) date_add() + interval: offset date and time forward and backward;

The operation is as follows:

10) last_day() : select last_day() from last_day;

The operation is as follows:

11) datediff(end_date,start_date) : calculate the number of days between two time difference;

The operation is as follows:

12) timestampdiff(unit,start_date,end_date) : calculate the year/month/day returned by two times;

The unit argument is the unit in which to determine the result of (start_date,end_date) and is expressed as an integer. The following are valid units:

  • Year: the year
  • In the month:
  • Day: the day
  • Hour, hour
  • Minute minute
  • Second: the second
  • Microsecond: microseconds
  • Week: the number of cycles
  • Quarter: quarter
  • YEAR: the YEAR

The operation is as follows:

6. Other common system functions

7. Process control function

1) If function: implement if-else effect;

2) ifnull function: check whether the value is null, ifnull is filled with the specified value;

3) the case… Three uses of the when function;

case … There are three ways to use “when”, and I’m sure my summary will be quite comprehensive. I hope you must study these several usages, they are very useful.

  • Equivalence judgment: similar to switch Case effect in Java;
  • Interval judgment: similar to if-elif-else in Python;
  • case … When and aggregate function;
(1) case… When is used as a grammatical format for equivalence judgment;
caseThe field or expression constant to judge1Then The value to display1Or statements1When a constant2Then The value to display2Or statements2.elseThe value n or statement n end to displayCopy the code

The operation is as follows:

(2) the case… When is used as a syntactic format for interval judgment;
caseThe when condition1Then The value to display1Or statements1The when condition2Then The value to display2Or statements2.elseThe value n or statement n end to displayCopy the code

The operation is as follows:

(3) case… When in conjunction with aggregate functions

Using the original table above, complete the following questions:

-- 18-- Course ID, course name, maximum score, minimum score, average score, pass rate, medium rate, excellent rate, excellent rate -- pass rate: >=60, the medium is:70- 80., excellent for:80- 90., excellent is: >=90
Copy the code

The operation is as follows:

selectSc. C, CNAME, Max (Score),min(score), avG (score), sum(case when score>60 then 1 else 0End)/count(*), sum(case when score>=70 and score<80 then 1 else 0End)/count(*)case when score>=80 and score<90 then 1 else 0End)/count(*), sum(case when score>=90 then 1 else 0End)/count(*) from sc left join course on sc.c = course.c group by sc.c;Copy the code

The results are as follows:

8. Aggregate functions

1) Function and classification of aggregation function;

① Function of aggregation function;

Used for statistics, also called aggregate function or statistical function or group function.

② Classification of aggregation functions;
  • The sum sum
  • Avg average
  • Max maximum
  • Min min
  • Count Count

2) Simple use of aggregate functions

3) What data types are supported by the parameters passed in the five aggregate functions?

Mysql is not a strongly typed programming language. That is, some statements may be executed without error, but the execution result has no practical meaning and therefore is considered incorrect.

① Test data;
"Constructional sentence"
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.'1993.3.20'),
 ("Hobby".4000.'1997.6.10'),
 ("Aline".5500.'2000.5.1'),
 ("Bob".10000.'2008.10.1');
Copy the code
② The sum() function and avg() function: it makes sense to pass in the integer/decimal type;

The conclusions are as follows:

  • The sum() and avg() functions don’t make much sense for string or date/time calculations. So the sum() function and the avg() function, we only use the sum() functionDecimal type and integer typeSum.
③ Max () function and min() function: the input of integer/decimal type and date/time type is significant;

The conclusions are as follows:

  • Max () and min() pass in “integer/decimal type” and evaluate numericallyThe maximumandThe minimum value.
  • The “date type” is passed in Max () and min(), and Max () computes the maximum value away from usThe most recent dateThe minimum value of min() calculated is away from usThe furthest dateI can write that down.
  • Max () and min() are passed inString typeThe maximum value calculated by Max () is displayed in alphabetical order, and the minimum value calculated by min() is also displayed in alphabetical order.
The count() function can pass in any data type, but beware of null;

The conclusions are as follows:

The count() function can pass in any data type to count rows."But here are some things that need special attention."How does count(sal),count(birth) mean? How many rows does sal count? How many rows does birth count? Because one of the records is null, the count() function ignores null rows when counting. Second, if count(*) is a count of how many rows there are in the table, this must be a correct count of the number of rows in the original table. Count (*) considers this behavior as long as one of the columns in a row of the table is not null1Line. Of course, if the entire row is null, you don't need to insert this record. Summary: When there is no null value in a column, then"Count (column field)=count(*)."When there is a null value in a field column"Count (column field)So, if you want to count the number of rows in the entire table, use count(*).Copy the code

In fact, all grouping functions ignore null values, but the count() function above should be careful when it encounters null values.

⑤ The count() function encounters null values.

The conclusions are as follows:

For avg(Sal) averaging, (6500+4000+5500+10000) /4=6500. For the average of sum()/count(*),6500+4000+5500+10000) /5=5200. Take a good look at the example above. Sometimes someone's score is null, but you still have it5Personal existence, so you have to think about how to use the appropriate function to achieve the result you want.Copy the code
⑥ Count (1),count(0).
Whether the sum (1),sum(0),count(1),count(0),avg(1),avg(0), which is equivalent to adding a new column to the original table. Second, we know that where is followed by a logical value when using where1And the where0The principle is the same as adding a column to the original table. We just need to remember that in mysql:"Non-0 is true, 0 is false.". In other words, everything down here is1Where you can replace any non0Numbers, it's all ok.Copy the code

The schematic diagram is as follows:

Test it out:

⑦ Count (*) counting efficiency problem;
  • In MYISAM storage engine, count(*) is efficient.
  • INNODB storage engine, count(*) is about as efficient as count(1), but more efficient than count(field).
  • To sum up: count(*) is preferred.

4) The combination of aggregation function and group BY is the most important;

This knowledge will be covered in a later section. When group by is used in a SQL statement, the group by column must be the same as the group by column.

Phase to recommend

Complete PyPy mastery in 5 minutes

5 minutes to master common Configuration files in Python

OpenCV artificial intelligence image recognition technology practical case

Click below to read the article and join the community

Give it a thumbs up