创建表student:
CREATE TABLE `student` (
`sid` INT(11) NOT NULL AUTO_INCREMENT,
`sname` VARCHAR(20) NOT NULL DEFAULT '0',
`sage` INT(11) NOT NULL DEFAULT '0',
`sex` VARCHAR(10) NOT NULL DEFAULT '0',
`sdept` VARCHAR(20) NULL DEFAULT NULL,
PRIMARY KEY (`sid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
创建表course:
CREATE TABLE `course` (
`cid` INT(11) NOT NULL AUTO_INCREMENT,
`cname` VARCHAR(20) NOT NULL DEFAULT '0',
`ccredit` INT(11) NOT NULL DEFAULT '0',
`semester` INT(11) NOT NULL DEFAULT '0',
`period` INT(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`cid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=3;
创建表sc:
CREATE TABLE `sc` (
`sid` INT(11) NOT NULL DEFAULT '0',
`cid` INT(11) NOT NULL DEFAULT '0',
`score` INT(11) NULL DEFAULT NULL,
PRIMARY KEY (`sid`, `cid`),
CONSTRAINT `FK_sc_course` FOREIGN KEY (`cid`) REFERENCES `course` (`cid`),
CONSTRAINT `FK_sc_student` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;
查询:
1. 计算机系人名字和年龄:
select sname, sage from student where sdept="computer"
2. 数学成绩在80到100之间的名字和学科和分数:
select sname,cname,score from student s, course c, sc where s.sid=sc.sid and c.cid=sc.cid and sc.score between 80 and 100 and cname="数学"
3. 每个系多少学生:
select count(sdept),sdept from student group by sdept
4. 课程1比课程2分数高的学号:
select a.sid from(select sid, score from sc where cid=1)a, (select sid,score from sc where cid=2)b
where a.score>b.score and a.sid=b.sid
5. 平均分大于80分的学号和平均分:
select sid, avg(score) from sc group by sid having avg(score)>70
6. 所有学生的学号, 姓名, 选课数, 总成绩:
select student.sid, sname, count(sc.cid),sum(sc.score) from student left outer join sc on student.sid=sc.sid group by student.sid;
7.