MySQL索引类型： 1.普通索引 2.唯一索引：只存在唯一的值，重复插入会报错 3.主键索引：一个表只能有一个 4.全文索引：方便查找大容量字段 5.外键索引 6.组合索引 mysql8 group by的问题：https://www.cnblogs.com/wang615/p/12376491.html 【MySQL训练题】 Student(SID,Sname,Sage,Ssex)  - SID 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 Course(CID,Cname,TID)  - CID --课程编号,Cname 课程名称,TID 教师编号 Teacher(TID,Tname)  - TID 教师编号,Tname 教师姓名 SC(SID,CID,score)  - SID 学生编号,CID 课程编号,score 分数 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 Select a.sname, b.score  FROM Student as a LEFT JOIN SC ON a.SID = b.SID wHERE 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数 SELECT stu.\*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc WHERE cid=01) c1,(SELECT score,Scid from sc WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc WHERE Cid=02) c3 WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and c1.score>c2.score 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数 SELECT stu.\*,c1.score '01课程',c2.score '02课程',c3.score '03课程' from (SELECT score,Scid from sc WHERE cid=01) c1,(SELECT score,Scid from sc WHERE Cid=02) c2,student stu,(SELECT score,Scid from sc WHERE Cid=02) c3 WHERE c1.Scid=c2.scid and stu.Sid=c1.scid and c3.scid=c2.scid and c1.score<c2.score 3、查询平均成绩大于等于70分的同学的学生编号和学生姓名和平均成绩 SELECT student.sid,student.sname,AVG(sc.score) FROM student,sc WHERE student.sid=sc.scid GROUP BY student.sid,student.sname HAVING AVG(sc.score)>=70 4、查询平均成绩小于70分的同学的学生编号和学生姓名和平均成绩 SELECT student.sid,student.sname,AVG(sc.score) FROM student,sc WHERE student.sid=sc.scid GROUP BY student.sid,student.sname HAVING AVG(sc.score)<70 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩 SELECT student.sid,student.sname,COUNT(sc.cid),sum(sc.score) FROM student,sc WHERE student.sid=sc.scid GROUP BY student.sid,student.sname 6、查询"李"姓老师的数量 SELECT count(tname) FROM teacher where tname like "李%" 7、查询学过"张三"老师授课的同学的信息 SELECT student.* from student,teacher,sc WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三" group BY student.sid,student.sname,student.sage,student.ssex 8、查询没学过"张三"老师授课的同学的信息 SELECT student.* from student,teacher,sc WHERE teacher.tid=sc.cid and student.sname not in (SELECT student.sname from student,teacher,sc WHERE student.sid=sc.scid and teacher.tid=sc.cid and teacher.tname ="张三") group BY student.sid,student.sname,student.sage,student.ssex 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息 SELECT \* FROM student WHERE sid in (SELECT s1.scid FROM (SELECT scid FROM sc WHERE cid='01') s1,(SELECT scid FROM sc WHERE cid='02') s2 WHERE s1.scid=s2.scid) 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息 SELECT student.\* FROM student where sid =(SELECT scid from sc where scid not in (SELECT scid FROM sc WHERE cid='02') GROUP BY scid ) 参考： https://blog.csdn.net/qq_43154385/article/details/87967878