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

In a relational database, you can use data grouping to obtain summary information about table data. Data grouping is realized through grouping functions, Group by and having clauses. 1. Grouping function SQL function includes single-line function and multi-line function. Grouping functions belong to multiline functions. Acts on a set of data and returns a value for a set of data. Grouping function: SELECT [column,] group function (column). FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column];. Common grouping functions: AVG, SUM, MIN, MAX, COUNT, WM_CONCAT. A, AVG () to find the average. For example: SELECT avg (sal) FROM emp;SQL > SELECT avg (sal) FROM emp; AVG (SAL) 2135b, SUM () summation. For example: SELECT SUM (sal) FROM emp;SQL > SELECT MIN (sal) FROM emp; MIN (SAL) 800c, MIN to find the minimum. For example: SELECT MIN (sal) FROM emp;SQL > SELECT MIN (sal) FROM emp;MIN (SAL) 800d, COUNT () count (statistics). For example: for the total number of employees, SELECT COUNT (*) FROM emp;SQL > SELECT COUNT (*) FROM emp;COUNT (*)-15 you can use the DISTINCT keyword to remove duplicates when using grouping. SQL > select count (distinct deptno) from emp;COUNT (DISTINCTDEPTNO) 3e, WM_CONCAT row-to-column (let query result row-to-column) this function separates column values with a', 'sign and displays them in a row. SQL > select wm_concat (ename) from emp;WM_CONCAT (ENAME) EASON,G_EASON,ALLEN,WARD,JONES,MARTIN,BLAKE,CLARK,SCOTT,KING,TURNER,ADAMS,JAMES,FORD,MILLER2, Group by clause function: you can divide the data in the table into several groups. Syntax of Group by clause: SELECT [column,] group function (column),... FROM table [WHERE condition] [GROUP BY group_by_expression] [HAVING group_condition] [ORDER BY column]; for example: find out the average salary of each part, and ask to show: the department number, the average salary of the part. SELECT deptno, avg (sal) from emp group by deptno;SQL > SELECT deptno, avg (sal) from emp group by deptno;DEPTNO AVG (SAL) 30 1566.66667 20 2312.5 10 2916.66667 all columns in the SELECT list that are not included in the group function should be included in the GROUP BY clause. For example, grouped by department and position: SQL > SELECT deptno, job, avg (sal) from emp group by deptno, job DEPTNO JOB AVG (SAL) 20 CLERK 950 30 SALESMAN 1400 20 MANAGER 2975 30 CLERK 950 10 PRESIDENT 5000 30 MANAGER 2850 10 CLERK 1300 20 ANALYST 3000 10 MANAGER 2450 has selected 9 rows. The columns contained in the group by clause do not have to be included in the SELECT list: SQL > select avg (sal) from emp group by deptno; AVG (SAL) 1566.66667 2312.52916.66667 again as an example: find out the employee name of each part and display: partial label, employee name. SQL > select deptno, WM_concat (ename) from emp group by deptno;DEPTNO WM_CONCAT (ENAME) 10 CLARK,MILLER,KING 20 EASON,FORD,ADAMS,SCOTT,JONES,G_EASON 30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD3, Having clause function: used to filter the grouped results. Example: if the average salary is greater than 2500, please show: Department number, average salary] you can no longer use the group function in the WHERE clause, you can use the group function in the having clause. SQL > select deptno,avg (sal) from emp group by deptno having avg (sal) > 2500 position DEPTNO AVG (SAL) 10 2916.66667having clause and group by clause are usually used together. If there is no group function in the condition, you can use the where statement normally. For example, select deptno, avg (sal) from emp where deptno=10 group by deptno;SQL > select deptno, avg (sal) from emp where deptno=10 group by deptno;DEPTNO AVG (SAL) 10 2916.66667where greatly reduces the number of packet records, thus improving efficiency. From the perspective of SQL optimization, use where clauses as much as possible. First filter and then group. Nesting of grouping functions: finding the maximum of partial average wages. Select max (avg (sal)) from emp group by deptno;SQL > select max (avg (sal)) from emp group by deptno;MAX (AVG (SAL)) 2916.66667

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.