Mysql database notes-Basic queries (grouping and Aggregate functions)

Mysql database notes-Basic queries (grouping and Aggregate functions)

Select count (*) FROM T_Employee WHERE FAge = 23; // view the number of students alter table T_Employee ADD FSubCompany VARCHAR (20); alter table T_Employee ADD FDepartment VARCHAR (20 ); // Add two columns for the T_Employee table: UPDATE T_Employee SET FSubCompany = 'beijing', FDepartment = 'development' WHERE FNumber = 'dev001 '; // after adding the two fields, you must update the values of these two fields in the original data row of the table.

SQL provides an aggregate function to perform data statistics such as the number of statistical result sets, the maximum value of a field, the minimum value of a field, the average value of a field, and the total value of a field.
Data groups are used to divide data into multiple logical groups, so that each group can be aggregated. In SQL statements, the GROUP BY clause is used as the GROUP BY field ". The grouping statement must be used with aggregate function 1.
For use, the group by clause is responsible for dividing data into logical groups, while the aggregate function performs statistics on each GROUP.

SELECT FAge FROM T_Employee group by FAge; // SELECT FAge FROM T_Employee for the company's employees; // SELECT FAge, AVG (FSalary) for the company's employees of all ages) FROM T_Employee group by FSubCompany, FDepartment; SELECT FAge, COUNT (*) AS CountOfThisAge FROM T_Employee group by FAge; // view the number of employees in each age group select FSubCompany, avg (FSalary) AS FSalarySUM FROM T_Employee group by FSubCompany; SELECT FAge, COUNT (*) AS CountOfThisAge FROM T_Employee group by FAge having count (*)> 1; // only retrieve the age GROUP with 1 excess