首页 > > 网络编程 > Mysql >

mysql_06_使用聚合函数查询

2018-06-17 23:57:25来源：未知阅读 ()

 1 #使用数据库
 2 USE db_student;
 3 
 4 #创建表
 5 create table `t_grade` (
 6 `id` int PRIMARY KEY auto_increment NOT NULL,
 7 `stuName` varchar (60),
 8 `course` varchar (60),
 9 `score` int 
10 );
11 
12 #插入数据
13 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('1','张三','语文','91');
14 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('2','张三','数学','90');
15 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('3','张三','英语','87');
16 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('4','李四','语文','79');
17 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('5','李四','数学','95');
18 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('6','李四','英语','80');
19 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('7','王五','语文','77');
20 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('8','王五','数学','81');
21 insert into `t_grade` (`id`, `stuName`, `course`, `score`) values('9','王五','英语','89');
22 
23 
24 #第五章:单表操作
25 #第二节:使用聚合函数查询
26 #5.1:COUNT()函数
27 SELECT COUNT(*) FROM t_grade;#统计记录的条数
28 SELECT COUNT(*) AS total FROM t_grade;#添加别名
29 SELECT stuName,COUNT(course) AS "科目数" FROM t_grade GROUP BY stuName,course;
30 SELECT stuName,GROUP_CONCAT(course) AS "科目" FROM t_grade GROUP BY stuName;
31 
32 #5.2:sum()函数
33 SELECT stuName,GROUP_CONCAT(course) AS "科目",SUM(score) AS "总分" FROM t_grade WHERE stuName="张三";
34 SELECT stuName,GROUP_CONCAT(course) AS "科目",SUM(score) AS "总分" FROM t_grade GROUP BY stuName;
35 
36 #5.3:avg()函数
37 SELECT stuName,GROUP_CONCAT(course) AS "科目",AVG(score) AS "平均分" FROM t_grade WHERE stuName="李四";
38 SELECT stuName,GROUP_CONCAT(course) AS "科目",AVG(score) AS "平均分" FROM t_grade GROUP BY stuName;
39 
40 #5.4:max()函数
41 SELECT stuName,MAX(score) AS "分数" FROM t_grade WHERE stuName="王五";
42 SELECT stuName,MAX(score) AS "最高分" FROM t_grade GROUP BY stuName;
43 
44 #5.5:min()函数
45 SELECT stuName,MIN(score) AS "最低分" FROM t_grade WHERE stuName="李四";
46 SELECT stuName,MIN(score) AS "最低分" FROM t_grade GROUP BY stuName;