有三个表s(学生表), C(课程表) , SC(学生选课表)

S(SNO, SNAME)代表 (学号，姓名)C(CNO, CNAME, CTEACHER) 代表(课号， 课名， 教师)SC(SNO, CNO, SCGRADE) 代表(学号， 课号， 成绩) create table s( sno int(4) primary key auto_increment, sname varchar(32) ); crate table c( con int(4) primary key auto_increment, cname varchar(32), cteacher varchar(32) ); create table sc( sno int (4), cno int(4), scgrade double(3, 1), constraint sc_sno_cno_pk primary key(sno, cno), constraint sc_sno_fk foreign key(sno) references s(sno), //需要使用其他表的字段，所以在此处增加一个外键的约束 constraint sc_cno_fk foreign key(cno) references c(cno) );

 1.  找出“黎明”老师教过的课程编号

select cno from c where cteacher = "黎明"

2.  找出所有选过”黎明”老师的学生编号

select distinct sno from sc where cno in (select cno from c where cteacher = "黎明")；

3. 列出2门以上(含2门)不及格学生姓名及平均成绩

第一步:先找出不及格门数大于2的学生编号和姓名

select sc.sno, s.name, count（*） as studentNum from sc join s on sc.sno = s.sno where sc.scgrade < 60 group by sc.sno, s.name having studentNum >= 2;

作为临时表t1

第二步:找平均值，作为临时表t2

select sc.sno, avg(sc.scgrade) as avgscgradge from sc group by sc.sno

第三步;将t1表和t2表进行连接

select t1.sname, t2.avgscgradge from t1 join t2 on t1.sno = t2.sno

找出即学过1号课程又学过2号课程所有学生的姓名

select sno, s.sname from sc join s on sc.sno = s.sno where cno = 1 and sc.sno in (select sno from sc where cno = 2);