**建立student表**
属性有:编号：id (主键，自动增长),姓名:sname,出生年月:sage,性别:ssex(枚举)
create table student(sid int primary key auto_increment,
sname varchar(20),
sage date,
ssex enum(‘男’,‘女’));
**建立教师表**
create table teacher(tid int primary key auto_increment,
tname varchar(20));
**建立课程表**
create table course(cid int primary key auto_increment,
cname varchar(20),
tid int,
foreign key(tid) references teacher(tid));
**建立成绩表**
create table sc(sid int,
cid int,
score int);
**插入数据**
1，先给student表插入数据
insert into student values(1,'赵雷','1990-01-01','男'),
(2,'钱电','1990-12-21','男'),
(3,'孙风','1990-05-20','男'),
(4,'李云','1990-08-06','男'),
(5,'周梅','1991-12-01','女'),
(6,'吴兰','1992-03-01','女'),
(7,'郑竹','1989-07-01','女'),
(8,'王菊','1990-01-20','女');
2, 给teacher表插入数据,这里不可以先给course表插入数据,因为course表外链接到teacher的主键
insert into teacher values(1,'张三'),
(2,'李四'),
(3,'王五');
3, 给course表插入数据
insert into course values(1,'语文',2),
(2,'语文',1),
(3,'语文',3);
4, 最后给sc表插入数据(题目图片少了第1个学生成绩，在这加上 1,1,90; 1,2,80; 1,3,90)
insert into sc values(1,1,90),
(1,2,80),
(1,3,90),
(2,1,70),
(2,2,60),
(2,3,80),
(3,1,80),
(3,2,80),
(3,3,80),
(4,1,50),
(4,2,30),
(4,3,20),
(5,1,76),
(5,2,87),
(6,1,31),
(6,3,34),
(7,2,89),
(7,3,98);
**1.查询出只有两门课程的全部学生的学号和姓名**
SELECT * FROM student s
WHERE s.sid in
(SELECT sc.sid FROM sc
GROUP BY sc.sid
HAVING COUNT(*)=2);
**2.查询没有学全所有课程的同学的信息**
SELECT s.sname,cname,score FROM student s,course,sc
WHERE sc.sid=s.sid
AND sc.cid=course.cid;
**3.查询选修了全部课程的学生信息**
SELECT * FROM student s
WHERE s.sid in
(SELECT sc.sid from sc
GROUP BY sc.sid
HAVING COUNT(*)=(SELECT COUNT(*) from course));
------------------------------------
SELECT * FROM student
WHERE NOT EXISTS
(SELECT * FROM course
WHERE NOT EXISTS
(SELECT *
FROM sc
WHERE sc.sid=student.sid AND sc.cid=course.cid));
**4.查询学过"张三"老师授课的同学的信息**
SELECT * FROM student s,sc,teacher t, course
WHERE sc.sid=s.sid
AND course.cid=sc.cid
AND course.tid=t.tid
AND t.tname='张三';
**5.查询没学过"张三"老师讲授的任一门课程的学生姓名**
SELECT * FROM student s
WHERE s.sid not in
(SELECT sc.sid FROM sc ,course,teacher
WHERE sc.cid=course.cid
AND teacher.tid=course.tid AND teacher.tname='张三');
**6.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩**
SELECT s.sid,s.sname,AVG(score) from sc ,student s
WHERE s.sid=sc.sid
GROUP BY sid
HAVING avg(score)>80;
**7.查询任何一门课程成绩在70分以上的姓名、课程名称和分数**
SELECT s.sid,s.sname,c.cname,score
FROM student s,sc,course c
WHERE sc.sid=s.sid
AND sc.cid=c.cid
AND score>70;
**8.查询不及格的课程**
SELECT s.sname,c.cname,score FROM student s,course c,sc
WHERE sc.sid=s.sid
AND sc.cid=c.cid
AND sc.score<60;
**9.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；**
SELECT s.sid,s.sname FROM student s ,sc
WHERE s.sid=sc.sid
AND cid=1
AND score>80;
**10.求每门课程的查询选修了全部课程的学生信息学生人数**
select cid,count(sid) from sc
group by sc.cid;
**11.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息**
SELECT s.* FROM student s,sc
WHERE sc.sid=s.sid
AND sc.cid=01
AND EXISTS
(SELECT 1 FROM sc sc1
WHERE sc1.sid=sc.sid
AND sc1.cid=02);
//EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
EXISTS 指定一个子查询，检测 行 的存在。
//select 1 from table;与select anycol(目的表集合中的任意一行） from table; 表里如果有记录，就显示 1 简单理解就是不查询具体列，只要有值就显示1
**12.查询"01"课程比"02"课程成绩高的学生的信息及课程分数**
SELECT s.*,a.score '课程01的分数',b.score '课程02的分数'
FROM student s,sc a,sc b
WHERE a.sid=s.sid
AND b.sid=s.sid
AND a.cid=01
AND b.cid=02
AND a.score>b.score;
网友评论