单表

数据库地址： [rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com](<http://rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com/>)
用户名： fanmao85
密码： 85@fanmao
数据库: fanmao85  fanmao65_hm

where 条件练习

group by 分组练习

order by 排序 limit 限制显示数据

单表查询 综合练习

多表查询

主机地址：rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com
端口号：3306
用户名：fanmao65
密码：abc@fanmao65
数据库：fanmao65_hm

1. 查询学生 学号，姓名，科目，分数。

   SELECT s.sno, s.sname,cname, sc.degree  from student as s
   INNER JOIN score as sc
   ON sc.sno = s.sno
   INNER JOIN course as c
   ON c.cno = sc.cno;
   
   

2. 查询学生 学号，姓名，科目，分数。 根据科目-分数进行排序

   SELECT s.sno, s.sname,cname, sc.degree  from student as s
   INNER JOIN score as sc
   ON sc.sno = s.sno
   INNER JOIN course as c
   ON c.cno = sc.cno
   ORDER BY cname,sc.degree
   

3. 查询科目分数一样的学生名单。

   - - 3. 查询科目分数一样的学生名单。
   -- 1 成绩表中 根据 课程号 成绩 进行分组 统计 count 值 count > 1 说明有重复
   select cno,degree, COUNT(degree) FROM score
   GROUP BY cno,degree HAVING COUNT(degree) > 1;
   - - 2 获取到重复值大于1的 学号，成绩
   select cno,degree FROM score
   GROUP BY cno,degree HAVING COUNT(degree) > 1;
   - - 多表联查
   SELECT s.sno,s.sname,sc.cno,sc.degree from student as s
   INNER JOIN score as sc
   ON s.sno = sc.sno
   INNER JOIN (
   select cno,degree FROM score
   GROUP BY cno,degree HAVING COUNT(degree) > 1
   ) as tmp
   ON tmp.cno = sc.cno AND tmp.degree = sc.degree
   ORDER BY sc.cno,sc.degree;
   

4. 平均分不及格的学生名单，平均分。

   select s.sno, s.sname, avg(sc.degree)  from student as s
   LEFT JOIN score as  sc
   ON s.sno = sc.sno
   GROUP BY s.sno,s.sname HAVING  avg(sc.degree) is NULL or  avg(sc.degree) < 60;
   

5. 查询每个同学的总成绩，没有成绩也要显示出来，显示结果为0。

   select s.sno, s.sname, ifnull(sum(sc.degree),0) from student as s 
   LEFT JOIN score as  sc 
   ON s.sno = sc.sno
   GROUP BY s.sno,s.sname
   

6. 计算机导论成绩的最高的同学。

   -- `计算机导论`成绩的最高的同学。
   -- 1. 先找到计算机导论的最高分数
   SELECT course.cname, max(score.degree)  from score 
   INNER JOIN course
   ON course.cno = score.cno
   WHERE course.cname = "计算机导论"
   
   -- 2. 根据分数找到对应的人员
   
   SELECT s.sno, s.sname,cname, sc.degree  from student as s
   INNER JOIN score as sc 
   ON sc.sno = s.sno
   INNER JOIN course as c 
   ON c.cno = sc.cno 
   WHERE c.cname = "计算机导论" and sc.degree = (
   
   	SELECT  max(score.degree)  from score 
   	INNER JOIN course
   	ON course.cno = score.cno
   	WHERE course.cname = "计算机导论"
   )