Mysql_06_ using aggregate functions to query

#使用数据库
Use db_student;

#创建表
CREATE TABLE ' T_grade ' (
' id ' int PRIMARY KEY auto_increment not NULL,
' Stuname ' varchar (60),
' Course ' varchar (60),
' Score ' int
);

#插入数据
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 1 ', ' Zhang San ', ' language ', ' the ') ';
Insert INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 2 ', ' Zhang San ', ' math ', ' 90 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 3 ', ' Zhang San ', ' English ', ' 87 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 4 ', ' John Doe ', ' language ', ' 79 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 5 ', ' John Doe ', ' math ', ' 95 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 6 ', ' John Doe ', ' English ', ' 80 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 7 ', ' Harry ', ' language ', ' 77 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 8 ', ' Harry ', ' Math ', ' 81 ');
INSERT INTO ' t_grade ' (' id ', ' stuname ', ' Course ', ' score ') VALUES (' 9 ', ' Harry ', ' English ', ' "");

#第五章: Single-table operation
#第二节: Querying with aggregate functions
#5.1:count () function
SELECT COUNT (*) from T_grade; #统计记录的条数
SELECT COUNT (*) as total from T_grade; #添加别名
Select Stuname,count (Course) as "number of accounts" from T_grade GROUP by Stuname,course;
Select Stuname,group_concat (Course) as "account" from the T_grade GROUP by Stuname;

#5.2:sum () function
Select Stuname,group_concat (Course) as "account", SUM (score) as "total Score" from T_grade WHERE stuname= "Zhang San";
Select Stuname,group_concat (Course) as "account", SUM (score) as "total Score" from the T_grade GROUP by Stuname;

#5.3:avg () function
Select Stuname,group_concat (Course) as "account", AVG (score) as "average score" from T_grade WHERE stuname= "John Doe";
Select Stuname,group_concat (Course) as "account", AVG (score) as "average score" from T_grade GROUP by Stuname;

#5.4:max () function
Select Stuname,max (Score) as "fraction" from T_grade WHERE stuname= "Harry";
Select Stuname,max (Score) as "highest score" from T_grade GROUP by Stuname;

#5.5:min () function
Select Stuname,min (Score) as "lowest score" from T_grade WHERE stuname= "John Doe";
Select Stuname,min (Score) as "minimum score" from T_grade GROUP by Stuname;

Mysql_06_ using aggregate functions to query