MySQL訓練題 · Laravel開發

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