Data Sorting and grouping statements

Data Sorting and grouping statements when SQL statements are used to perform query operations, we may find that the sorting of the queried data results is unordered. To better observe the query results in a data table, developers or users often need to sort the queried data, which requires the order by clause. In the actual application of the database, you sometimes need to perform statistics and grouping operations on the queried data. Therefore, you need to understand the Aggregate functions of SQL statements and the use of GROUP BY clauses. In some cases, developers or users also want to make further statistics on the results After grouping. in SQL statements, a keyword such as ROLLUP is provided for statistics on data. Finally, we will introduce how to limit the number of rows in the result set in the primary database. 1. the order by clause is used to sort data records. A column in a specified table is ordered. The order by clause can be used to sort the specified column in the query results in ascending or descending ORDER, this depends on the keyword after the order by clause. If the keyword behind the order by clause is ASC, the query result is executed in ascending ORDER. If the keyword behind the order by clause is DESC, then, the query results are executed in descending order. The syntax rules are as follows: order by column name 1 [ASC | DESC] Where column name 1 indicates that the column needs to be sorted. The keywords ASC and DESC are optional. If the order by clause is not followed by asc or DESC, the ascending operation is performed BY default. SELECT teaID, teaName, dept, partition Sion, salaryFROM T_teacher WHERE dept = 'computer system 'order by salary ASC specifies the position sequence number of the column in the table for sorting BY using the order by clause, in addition to column names, you can sort a specified column by its sequence number in the selected list. SELECT teaID, teaName, dept, Sion, salaryFROM T_teacher order by 5 ASC sorts the non-selected columns in the SELECT statement in the order by clause. You can also sort the selected columns that do not appear in the SELECT statement. SELECT teaID, teaName, dept, partition sionfrom T_teacher WHERE dept = 'computer system 'order BY salary: specify multiple columns in the table for sorting BY clauses. In addition to specifying a single column for sorting, you can also specify multiple columns in the data table for sorting. If you want to specify multiple columns in the data table for sorting, you must use commas to separate the specified columns. The syntax rules are as follows: order by column name 1 [ASC | DESC], column name 2 [ASC | DESC], column name 1 and column name 2 indicate that you need to sort the specified data column. Use commas (,) to separate column names 1 and 2. The keywords ASC and DESC are optional. If the order by clause is not followed by asc or DESC, the ascending operation is performed BY default. First, sort BY the first column specified in order by. Then, sort BY the ascending or descending ORDER of the second column specified in the order by clause. SELECT teaID, teaName, dept, partition Sion, salaryFROM T_teacher order by salary DESC, dept ASC 2. commonly used Aggregate functions are also called grouping functions or statistical functions. They are mainly used for statistical calculation of a set of data, such as sum and average, common Aggregate functions include COUNT, MAX, MIN, SUM, and AVG. You can use the DISTINCT keyword in the COUNT, SUM, and AVG functions to remove repeated items in the specified column. After the DISTINCT keyword is used, only the values of different rows are counted. The columns or expressions in the MAX and MIN functions can be numeric, numeric, or date values. If the columns or expressions in the MAX and MIN functions are sorted in the sequence from A to Z. If the first letter is the same, the size of the second letter in the string is compared, and so on. Chinese characters are sorted by the full spelling of Chinese pinyin. Select max (salary), MIN (salary) FROM T_teacher SUM and AVG functions can only be numeric values. Except COUNT (*), several other functions ignore NULL values (NULL rows) in the Expression During computation ). The COUNT function is used to calculate the total number of rows in a data table. The SUM function is used to calculate the SUM of the attribute values of a column in the data table. Select sum (salary), COUNT (salary), AVG (salary) FROM T_teacher Aggregate functions can only appear in SELECT statements, group by clauses, and HAVING clauses, aggregate functions cannot appear in the WHERE clause. 3. when you use the group by clause to GROUP data in a single column and use the group by clause to group a column in a data table, A statistical result is calculated for different values in the column of the specified group. The syntax format is as follows: group by column name 1 where column name 1 indicates that you need to GROUP the column. SELECT dept, COUNT (distinct sion) FROM T_teacherGROUP BY dept Select must use Group By when both data columns and Aggregate functions are included. When grouping multiple columns in a data table using the group by clause, a statistical result is calculated for different values in multiple columns of the specified GROUP. The syntax format is as follows: group by column name 1, column name 2... Column names 1 and 2 indicate that you need to group the specified columns. Use commas (,) to separate column names 1 and 2. Use HAVING subsentences to limit the query results after grouping. If you want to limit the query conditions for grouping results, you need to use the HAVING clause. The HAVING clause is used to limit the query results after grouping. Therefore, this clause must be placed behind the group by clause. The syntax format is as follows: group by column name 1 HAVING condition expression where column name 1 indicates that the column needs to be grouped. The conditional expression after the HAVING clause is used to filter the grouped results. Aggregate functions are often used in HAVING clauses to filter grouped results. SELECT dept, partition Sion, MAX (salary) FROM T_teacherGROUP BY dept, partition sionhaving MAX (salary)> 3000 Note: HAVING exists only under group by, and HAVING is used for GROUP; WHERE is for SELECT (for tables or views), WHERE is used before distribution. SELECT sequence Sion, MAX (salary) FROM T_teacherWHERE age> 30 group by sequence sionhaving MAX (salary)> 3000 After grouping data in a data table, you also want to sort the group results. If you want to sort the grouping results using the group by clause, you need to use the order by clause. SELECT dept, partition Sion, MAX (salary) FROM T_teacherGROUP BY deptORDER by max (salary) DESC sort the highest salary of each GROUP after group; the following statement sorts the salaries of the first row of each group. SELECT dept, partition Sion, MAX (salary) FROM T_teacherGROUP BY deptORDER BY salary desc group by clause processes NULL values. When the group by clause is used to group a specified column, sometimes the specified column may contain NULL values. The group by clause groups all NULL values in the column. If you want to obtain the row corresponding to the maximum wage of each group (instead of the first row of the group), you can use subqueries and other methods. Select teaName, salaryfrom (select * from t_teacher order by salary desc) tempgroup by deptorder by salary 4. in practice, statistical data using the ROLLUP keyword sometimes requires not only grouping statistical results, but also further calculation of grouping statistical results, for example, through the instructor information table (T_teacher) the colleges and teachers' titles in China are grouped to get the teachers' salaries After grouping, and we also hope to make a periodic statistics on the salaries of teachers in each department, we hope to get the sum (equivalent to subtotal) of the salaries of teachers with different titles in each school and the sum (equivalent to the total) of the salaries of teachers with different titles in all schools ). In this case, you cannot use the group by clause only. In this case, you need to use the ROLLUP keyword. The ROLLUP keyword must be placed behind the group by keyword. The ROLLUP keyword is used slightly differently in different databases. A. Use with rollup in MySQL and Microsoft SQL Server databases. The syntax format is as follows: group by column name 1 with rollup where column name 1 indicates that the column is to be grouped, And the with rollup keyword indicates that the grouping results are to be counted. Of course, you can also group multiple columns and count the grouping results. The syntax format is as follows: group by column name 1, column name 2 with rollup B. in Oracle Database, the ROLLUP keyword needs to be followed BY the GROUP BY keyword, and then write the fields to be grouped. The syntax format is as follows: group by rollup (column name 1, column name 2 ...) 5. limit the number of rows in the result set. Sometimes, developers or users do not want to display all the data in the data column of the query result, but only want to display several rows, especially in paging operations. For example, a data table finally queries 100 records, and developers or users only care about the values of the first 10 records, therefore, you need to limit the number of data records in the query results. The methods for limiting the number of results set rows in different databases are also different. A. You can use the LIMIT keyword to LIMIT the number of rows in the result set in the MySQL database. It can be used to LIMIT the number of queried data results. By using the LIMIT keyword, developers or users can get the desired result. If you want to use LIMIT to LIMIT the number of rows in the result set, you can use the following syntax format. In LIMIT n, LIMIT is the keyword, and number n indicates that the number of rows in the result set must be limited. SELECT teaID, teaName, dept, analysionfrom T_teacherORDER BY teaIDLIMIT 3 -- LIMIT 3, 3, 4th to 6th records after ascending order B. the Oracle database does not support the LIMIT keyword similar to the LIMIT keyword in MySQL to LIMIT the number of rows in the result set. However, the ROWNUM keyword can be used in the Oracle database to LIMIT the number of rows in the result set. The syntax format is as follows: where rownum <n WHERE the ROWNUM keyword indicates the serial number of the matching result, and its starting value is always from 1. Number n indicates the number of rows in the result set to be restricted. Of course, in addition to using (<) less than, the comparison operator can also use (<=) less than or equal. C. Use the LIMIT keyword and ROWNUM In the MySQL database and Oracle database to LIMIT the number of result set rows. Use the TOP keyword in the Microsoft SQL Server database. The syntax format is as follows: select top n [PRECENT] column name 1, column name 2... FROM table name... TOP is the keyword that limits the number of rows in the result set. number n indicates the number of rows in the result set. the PRECENT keyword indicates the number n % before the returned result set. It is optional.