mysql_06_使用聚合函數查詢

#使用資料庫
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','張三','語文','91');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('2','張三','數學','90');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('3','張三','英語','87');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('4','李四','語文','79');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('5','李四','數學','95');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('6','李四','英語','80');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('7','王五','語文','77');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('8','王五','數學','81');
insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('9','王五','英語','89');

#第五章:單表操作
#第二節:使用聚合函數查詢
#5.1:COUNT()函數
SELECT COUNT(*) FROM t_grade;#統計記錄的條數
SELECT COUNT(*) AS total FROM t_grade;#添加別名
SELECT stuName,COUNT(course) AS "科目數" FROM t_grade GROUP BY stuName,course;
SELECT stuName,GROUP_CONCAT(course) AS "科目" FROM t_grade GROUP BY stuName;

#5.2:sum()函數
SELECT stuName,GROUP_CONCAT(course) AS "科目",SUM(score) AS "總分" FROM t_grade WHERE stuName="張三";
SELECT stuName,GROUP_CONCAT(course) AS "科目",SUM(score) AS "總分" FROM t_grade GROUP BY stuName;

#5.3:avg()函數
SELECT stuName,GROUP_CONCAT(course) AS "科目",AVG(score) AS "平均分" FROM t_grade WHERE stuName="李四";
SELECT stuName,GROUP_CONCAT(course) AS "科目",AVG(score) AS "平均分" FROM t_grade GROUP BY stuName;

#5.4:max()函數
SELECT stuName,MAX(score) AS "分數" FROM t_grade WHERE stuName="王五";
SELECT stuName,MAX(score) AS "最高分" FROM t_grade GROUP BY stuName;

#5.5:min()函數
SELECT stuName,MIN(score) AS "最低分" FROM t_grade WHERE stuName="李四";
SELECT stuName,MIN(score) AS "最低分" FROM t_grade GROUP BY stuName;