windows,pycharm,python，PyMySQL( 五 ) _索引

< (select count(1) from course)
30查询所有学生都选修了的课程的课程号和课程名
selectcourse_id,course.cnamefromscoreleft join course on score.course_id=course.cidgroup bycourse_idhavingcount(1) = ( select count(1) from student )
31查询选修了生物和体育课程的所有学生的学号、姓名
selectstudent_id,student.snamefromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidwherecourse.cname in ("生物","体育")group bystudent_idhavingcount(1)=2
32查询至少有一门与学号为“1”的学生所选修的课程相同的其他学生学号和姓名
selectstudent_id,student.snamefromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidwherescore.student_id!=1 andscore.course_id in ( select course_id from score where student_id=1 )group bystudent_idhavingcount(1)>=1
33查询与学号为2的同学选修的课程完全相同的其他学生学号和姓名
selectstudent_id,student.snamefromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidwherescore.course_id in ( select course_id from score where student_id=1 )and --第二个条件为找到选修课程数量与学号为2的学生一样的idscore.student_id in(selectstudent_idfromscorewherestudent_id!=2group by student_idhavingcount(1) = (select count(1) from score where student_id=2))group bystudent_idhavingcount(1)=( select count(1) from score where student_id=2 )
34查询生物课程比物理课程高的所有学生的学号和姓名
selectstudent_id,student.sname,max(case course.cname when '生物' num else -1 end) as sw,max(case course.cname when '物理' num else -1 end) as wlformscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidwherecourse.cname in ('生物','物理')group bystudent_idhavingsw > wl
35查询每门课程成绩最好的前3名（不考虑成绩并列）
selectcid,cname,--第一名(selectstudent.snamefromscoreleft join student on score.student_id=student.sidwherecourse_id = course.idorder bynumber desclimit1offset0) as '第一名',--第二名(selectstudent.snamefromscoreleft join student on score.student_id=student.sidwherecourse_id = course.idorder bynumber desclimit1offset1) as '第二名',--第三名(selectstudent.snamefromscoreleft join student on score.student_id=student.sidwherecourse_id = course.idorder bynumber desclimit1offset2) as '第三名'fromcourse
36创建一张有外键的表sc，把score的所有数据都插入到sc中
create table 'sc'{'sid' int not null auto_increment primary key,'student_id' int not null,'course_id' int not null,'num' int not null,constraint 'fk_sc_course' foreign key ('course_id') reference 'course'('cid'),constraint 'fk_sc_student' foreign key ('student_id') reference 'student'('sid')} default charset=utf8;insert into sc select * from score;
37 向sc中插入一些记录，1）没有上过课程id为2的课程的学生id，2）课程id为2，3）成绩为80
insert into sc (student_id, course_id, number)selectsid,2,80fromstudentwheresid not in (selectstudent_idfromscorewherecourse_id=2)
38 向sc中插入一些记录，1）没上过id为2的课程的学生id，2）课程id为2，3）成绩为课程id为3的最高分