MySQL Create field + data processing function + Summary data (aggregation function) + grouped data

"0" README 0.1) This part of the text is described in the "MySQL must Know", designed to review "MySQL create field + data processing functions + summary data (aggregation function) + packet data" basic knowledge;
"1" Create calculated fields 1) problem+solution

1.1) Problem: the data stored in the table is not required by the application. We need to retrieve the converted, computed or formatted data directly from the database; 1.2) Solution: This is where the computed field works, and the calculated field is created within the SELECT statement at run time;

2) field definition: basically the same as the meaning of the column, often used interchangeably, but the database column is generally referred to as a column, and the term field is commonly used in the calculation field of the connection; Attention) It is recommended that data conversion and formatting be done on the database server, because the database server does this much faster than the client;
3) Stitching fields

3.1) Splicing: The values are joined together to form a single value (using the concat () function to splice two columns);
3.2) Delete the data to the right of the extra empty Glyd data: This can be done through the RTrim () function (not case-sensitive, that is, the RTrim () function), as well as the LTrim () function and the trim () function;

4) Ability to perform arithmetic
Attention)the Now () function in MySQL, which returns the current date and time;
"2" Using data processing functions 1) RTrim () + ltrim () + trim () in the previous chapter is a litchi using the function; Attention) code that can run on multiple systems is called portability, whereas most SQL statements are portable, but the portability of the functions is not strong, and almost every DBMS implementation supports other functions that are not supported by the implementation, and for code portability, Many SQL programmers disapprove of the ability to use special implementations, and if you decide to use a function, you should make sure that the code is commented out; (dry--do not recommend using a function in MySQL because its function is not very portable); 2) Text processing function

3) Date and time processing function
Attention) The first thing to note is the date format used by MySQL. Whenever you specify a date, whether it be inserting or updating table values or filtering with a WHERE clause, the date must be format YYYY-MM-DD, which is the preferred date format because it excludes ambiguities such as 04/05/06 is May 4, 2006 or April 5, 2006 or May 6, 2004? )problem+solution)

problem) as shown, for datetime type Deal_time, where deal_time= ' 2016-05-10 ' retrieval failed; solution) indicates MySQL Compare the given date with the date part in the column only, not the given date and the entire column value. To do this, you must use the date () function;

Attention)the date () function and the time () function are all introduced in MySQL4.1.1;
4) There is another date that needs to be compared. What if you want to retrieve all orders from September 2005? (Between ... Statement)

5) Numeric processing function
"3" Summary data 1) aggregation function: such as determining the number of rows in the table, and obtaining the rows of the table group, and find the maximum value of the table column, minimum value and average;

1.1) Aggregation function definition: A function that runs on a row group, computes and returns a single value;
1.2) AVG () function

Attention) null value:the AVG () function ignores rows where the column value is null;

< strong>1.3) The two usages of the count () function

func1) counts the number of rows in a table using count (*), regardless of whether the table column contains a null value (NULL) or a non-null value;

Blockquote style= "margin:0 0 0 40px; Border:none; padding:0px "> Func2) using the Count (column) Count the values in a particular column, ignoring null values,

1.4) max () function and min () function + sum () function

2) Aggregation of different values: the above 5 aggregation functions can be used as follows:

2.1) performs a calculation on all rows, either specifying the all parameter or not giving the parameter (because all is the default behavior), 2.2) contains only a different value, specifying the distinct parameter;

See a lychee) consider the different price (distinct== different)
3) Combined aggregation function

"4" grouping data (group BY and HAVING clause) 1) Data grouping: statistics on the number of production provided by each supplier; 2) Create a group: The grouping is created by the GROUP BY clause in the SELECT statement;
3) before using the GROUP BY clause, you need to know some important provisions (rules)

R1)The GROUP BY clause can contain any number of columns, which allows grouping to be nested, providing finer control over the grouping of data (also known as GROUP by Column1, column2== grouped by Column1 first, Then grouped according to Column2); R2) If grouping is nested within the GROUP BY clause, the data is summarized on the last set of groupings. In other words, when a grouping is established, all the specified columns are calculated together; R3)each column listed in the GROUP BY clause must be a retrieval column or a valid expression. If you use an expression in select, you must specify the same expression in the GROUP BY clause, and you cannot use aliases; R4) The first open aggregation calculation statement, each column in the SELECT statement must be given in the GROUP BY clause; (Dry goods Regulations) R5) If there is a null value in the grouping column, the null value is returned as a grouping. If there are multiple rows of null values in the column, they are grouped into one set; R6)The GROUP BY clause must appear after the WHERE clause before the ORDER BY clause; (dry--group by clause in the location of the WHERE clause and the ORDER BY clause)

Attention) using rollup: With the WITH rollup keyword, you can get the values for each grouping and each grouping summary level (for each grouping);
4) Filter Grouping

4.1) The filter group is processed using the HAVING clause, 4.2) The difference between the WHERE clause and the HAVING clause (difference):

D1)where filter rows, and having filter columns

,

D2)where the data is grouped before filtering, and having the data is grouped after filtering; (Dry Goods-- This is where and has an important difference)

Attention) The location of the HAVING clause:the HAVING clause filter is based on group by, which is behind the GROUP BY clause; See a lychee) prove where to filter the data before grouping, and having the data is filtered after grouping; (Dry Goods-this is where and have an important difference)
5) Grouping and sorting (group by and order by)

5.1) Group BY and order by are different
5.2) Do not forget GROUP by: The ORDER BY clause should also be given when using the GROUP BY clause. This is the only way to ensure that the data is sorted correctly;

6) Order of the SELECT clause

MySQL Create field + data processing function + Summary data (aggregation function) + grouped data