1、问题背景 (1)学生表(学号,姓名,年龄,性别) student(S#,Sname,Sage,Ssex) (2)课程表(课程编号,课程名称,教师编号) course(C#,Cname,T#) (3)成绩表(学号,课程编号,分数) student_score(S#,C#,score) (4)教师表(教师编号,教师名称) teacher(T#,tname) 2、创建表 (1)学生表 1 2 3 4 5 6 7 8 9 | Create Table
CREATE TABLE `student` (
`S#` bigint (12) NOT NULL COMMENT '学号' ,
`Sname` varchar (20) DEFAULT NULL COMMENT '姓名' ,
`Sage` int (3) DEFAULT NULL COMMENT '年龄' ,
`Ssex` varchar (4) DEFAULT NULL COMMENT '性别' ,
PRIMARY KEY (`S#`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
(2)课程表1 2 3 4 5 6 7 8 | Create Table
CREATE TABLE `cource` (
`C#` bigint (12) NOT NULL COMMENT '课程编号' ,
`Cname` varchar (20) DEFAULT NULL COMMENT '课程名称' ,
`T#` bigint (12) DEFAULT NULL COMMENT '教师编号' ,
PRIMARY KEY (`C#`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
(3)成绩表1 2 3 4 5 6 7 8 | Create Table
CREATE TABLE `student_score` (
`S#` bigint (12) NOT NULL COMMENT '学号' ,
`C#` bigint (12) NOT NULL COMMENT '课程编号' ,
`score` double DEFAULT NULL COMMENT '分数' ,
PRIMARY KEY (`S#`,`C#`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
(4)教师表1 2 3 4 5 6 7 | Create Table
CREATE TABLE `teacher` (
`T#` bigint (12) NOT NULL COMMENT '教师编号' ,
`tname` varchar (20) DEFAULT NULL COMMENT '教师名称' ,
PRIMARY KEY (`www.a6qp.com`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
|
3、问题(1)查询“1”课程比“2”课程成绩高的所有学生的学号; 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT
a.`S#`
FROM
( SELECT
sc.`S#`,
sc.`score`
FROM
student_score sc
WHERE sc.`C#` = 1) a,
( SELECT
sc.`S#`,
sc.`score`
FROM
student_score sc `student_score`
WHERE sc.`C#` = 2) b
WHERE a.score > b.score
AND a.`S#` = b.`S#` ;
|
(2)查询平均成绩大于60分的同学的学号和平均成绩 1 2 3 4 5 6 7 | SELECT
sc.`S#`,
AVG (sc.`score`)
FROM
student_score sc
GROUP BY sc.`S#`
HAVING AVG (sc.`score`) > 60 ;
|
|