drop table if exists student;
drop table if exists class;
drop table if exists score;
create table student(
sno int(10) primary key auto_increment,
sname varchar(32)
);
create table class(
cno int(10) primary key auto_increment,
cname varchar(32),
teacher varchar(32)
);
# sc表中学生与课程是多对多关系，主键设成谁（复合主键(sno,cno)）
# sno和cno也必须来自学生表和课程表，因此他们是主键的同时也是外键
# 一个表只能有一个主键但是可以有多个外键
create table score(
sno int(10),
cno int(10),
grade double(3,1),
constraint sc_sno_cno_pk primary key(sno,cno),
constraint sc_sno_fk foreign key(sno) references student(sno),
constraint sc_cno_fk foreign key(cno) references class(cno)
);
insert into student(sname) values('张三');
insert into student(sname) values('李四');
insert into student(sname) values('王五');
insert into student(sname) values('赵六');
insert into class(cname,cteacher) values('java','王老师');
insert into class(cname,cteacher) values('C++','张老师');
insert into class(cname,cteacher) values('C#','李老师');
insert into class(cname,cteacher) values('mysql','周老师');
insert into class(cname,cteacher) values('oracle','黎明');
insert into score(sno,cno,scgrade) values(1,1,30);
insert into score(sno,cno,scgrade) values(1,2,50);
insert into score(sno,cno,scgrade) values(1,3,80);
insert into score(sno,cno,scgrade) values(1,4,80);
insert into score(sno,cno,scgrade) values(1,5,70);
insert into score(sno,cno,scgrade) values(2,2,80);
insert into score(sno,cno,scgrade) values(2,3,50);
insert into score(sno,cno,scgrade) values(2,4,70);
insert into score(sno,cno,scgrade) values(2,5,80);
insert into score(sno,cno,scgrade) values(3,1,60);
insert into score(sno,cno,scgrade) values(3,2,70);
insert into score(sno,cno,scgrade) values(3,3,60);
insert into score(sno,cno,scgrade) values(4,3,50);
insert into score(sno,cno,scgrade) values(4,5,40);
select * from student;
select * from class;
select * from score;
学生表：student
sno sname 学号 姓名
课程表：class
cno cname teacher
课程编号 课程名称 老师名称
选课表：score
sno cno grade
学号 课号 成绩
1，找出没选过“黎明”老师的所有学生姓名。
1、先找出 黎明 教授课程的编号 编号等于5
select cno from class where teacher = '黎明';
+-----+
| cno |
+-----+
| 5 |
+-----+
2，查找成绩表 通过课程号5 找出学号
select sno from score where cno = (select cno from class where teacher = '黎明');
+-----+
| sno |
+-----+
| 1 |
| 2 |
| 4 |
+-----+
3 通过学号找出学生姓名
select sname from student where sno not in (select sno from score where cno = (select cno from class where teacher = '黎明'));
+--------+
| sname |
+--------+
| 王五 |
+--------+
2，列出 2 门以上（含2 门）不及格学生姓名及平均成绩。
1 找出两门不及格的学生
select
sno,count(*)as number
from score
where grade<60
group by sno
having number>=2;
+-----+--------+
| sno | number |
+-----+--------+
| 1 | 2 |
| 4 | 2 |
+-----+--------+ 1 和 4 两门不及格
2 找出姓名
select
s.sno,count(*)as number,t.sname
from score s
join student t
on s.sno = t.sno
where grade<60
group by s.sno,t.sname
having number>=2;
+-----+--------+--------+
| sno | number | sname |
+-----+--------+--------+
| 1 | 2 | 张三 |
| 4 | 2 | 赵六 |
+-----+--------+--------+
3 找出平均成绩 按照课号分组 求平均成绩
select sno,avg(grade) as avggrade from score group by sno;
+-----+----------+
| sno | avggrade |
+-----+----------+
| 1 | 62.00000 |
| 2 | 70.00000 |
| 3 | 63.33333 |
| 4 | 45.00000 |
+-----+----------+
4 做表连接
select
s.sno,count(*)as number,t.sname,f.avggrade
from score s
join student t
on s.sno = t.sno
join (select sno,avg(grade) as avggrade from score group by sno) as f
on s.sno = f.sno
where grade<60
group by s.sno,t.sname
having number>=2;
+-----+--------+--------+----------+
| sno | number | sname | avggrade |
+-----+--------+--------+----------+
| 1 | 2 | 张三 | 62.00000 |
| 4 | 2 | 赵六 | 45.00000 |
+-----+--------+--------+----------+
3，即学过 1 号课程又学过 2 号课所有学生的姓名。
1 学过1号课程的同学
select sno from score where cno=1;
+-----+
| sno |
+-----+
| 1 |
| 3 |
+-----+
2 学过2号课程的同学
select sno from score where cno=2;
+-----+
| sno |
+-----+
| 1 |
| 2 |
| 3 |
+-----+
3 where条件：学过课程 1 并且 学过课程 2
select sno,sname from score where cno=1 and cno in (select sno from score where cno=2);
学过课程1的 学过课程2个
+-----+
| sno |
+-----+
| 1 |
| 3 |
+-----+
4 表连接 条件sno相等
select
s.sno,c.sname
from score s
join student c
on s.sno = c.sno
where cno=1 and cno in (select sno from score where cno=2);
+-----+--------+
| sno | sname |
+-----+--------+
| 1 | 张三 |
| 3 | 王五 |
+-----+--------+