笔试题:
有学生表Student, 课程表Course, 学生选课表StudentCourse
- 请设计上面三个表,并画出实体关系图。
- 请写出建表语句,创建上面三个表。
- 请写出插入语句,插入某学生选的一门课。
- 有个学生的名字写错了,请写出更新语句.
- 请写出查询语句,查询出一门课都没选的学生.
- 请写出查询语句,查询出选了5门课的学生.
首先来分析,一个学生可以选择多门课程,一门课程可以被多个学生选择,确定关系是多对多关系。
多对多关系 建表原则:
需要创建第三张表,中间表中至少两个字段,这两个字段分别作为外键指向各自一方的主键。
1、表之间的实体关系图如下:
2、建表语句:
学生表
CREATE TABLE `student` (
`sid` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
`class` VARCHAR(20) NOT NULL,
PRIMARY KEY (`sid`)
);
INSERT INTO student (`name`,class) VALUES
('张三','一年级'),('李四','二年级'),('王五','三年级'),('赵六','四年级');
SELECT * FROM student;
课程表
CREATE TABLE `course` (
`cid` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
PRIMARY KEY (`cid`)
);
INSERT INTO course (`name`) VALUES
('语文'),('数学'),('英语'),('物理'),('地理'),('历史');
SELECT * FROM course;
学生选课表
CREATE TABLE `studentcourse` (
`sid` INT(11) NOT NULL,
`cid` INT(11) NOT NULL,
PRIMARY KEY (`sid`,`cid`),
FOREIGN KEY(sid) REFERENCES student(sid),
FOREIGN KEY(cid) REFERENCES course(cid)
);
INSERT INTO studentcourse VALUES
(1,1),(1,2),(1,3),(2,1),(2,3),(3,1),(3,2),(3,3),(3,4),(3,5);
SELECT * FROM studentcourse;
3、 更新学生名字写错了。
UPDATE student SET `name`='八八' WHERE sid=4;
SELECT * FROM student;
4、查询出一门课都没选的学生
SELECT s.`name` FROM student s WHERE s.`sid` NOT IN
(SELECT sc.`sid` FROM studentcourse sc WHERE sc.`sid`);
5、查询出选了5门课的学生
SELECT s.`name` FROM student s JOIN studentcourse sc ON
s.`sid`=sc.`sid` GROUP BY s.`sid` HAVING COUNT(sc.`sid`)=5;