1.学生表
CREATE TABLE `Student` (
`sid` int(32) NOT NULL AUTO_INCREMENT,
`sname` varchar(32) DEFAULT NULL,
`ssox` varchar(32) DEFAULT NULL,
`sbirth` date DEFAULT NULL,
PRIMARY KEY (`sid`)
)
2.选课表
CREATE TABLE `selectCourse` (
`sid` int(32) DEFAULT NULL,
`cid` int(32) DEFAULT NULL
)
3.课程表
CREATE TABLE `Course` (
`cid` int(32) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) DEFAULT NULL,
`cteacher` varchar(32) DEFAULT NULL,
PRIMARY KEY (`cid`)
)
alter table selectCourse add foreign key(sid) references Student(sid);
alter table selectCourse add foreign key(cid) references Course(cid);
插入一些数据:
INSERT INTO `Student` (`sid`, `sname`, `ssox`, `sbirth`) VALUES ('1', '神奇的我', '男', '1999-1-1');
INSERT INTO `Student` (`sid`, `sname`, `ssox`, `sbirth`) VALUES ('2', '神奇的他', '女', '1999-9-9');
INSERT INTO `Student` (`sid`, `sname`, `ssox`, `sbirth`) VALUES ('3', '老王', '男', '2000-12-12');
INSERT INTO `Student` (`sid`, `sname`, `ssox`, `sbirth`) VALUES ('4', '老张', '男', '2000-11-11');
INSERT INTO `Student` (`sid`, `sname`, `ssox`, `sbirth`) VALUES ('5', '小美', '女', '2001-1-1');
INSERT INTO `Student` (`sid`, `sname`, `ssox`, `sbirth`) VALUES ('6', '小明', '男', '2010-3-2');
INSERT INTO `Student` (`sid`, `sname`, `ssox`, `sbirth`) VALUES ('7', '小张', '男', '2012-12-12');
INSERT INTO `Course` (`cid`, `cname`, `cteacher`) VALUES ('1', '数学', '隔壁老王');
INSERT INTO `Course` (`cid`, `cname`, `cteacher`) VALUES ('2', '英语', '隔壁老张');
INSERT INTO `Course` (`cid`, `cname`, `cteacher`) VALUES ('3', '体育', '隔壁老王');
INSERT INTO `Course` (`cid`, `cname`, `cteacher`) VALUES ('4', '语文', '隔壁老李');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('1','1');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('1','2');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('1','3');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('2','1');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('2','2');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('2','3');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('3','2');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('4','2');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('5','2');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('6','2');
INSERT INTO `selectCourse` (`sid`, `cid`) VALUES ('7','2');
5.进行多表查询
例如查询学生学号为1学生的有 哪些课程 分别什么老师
select cname,cteacher from Course inner join selectCourse on selectCourse.cid=Course.cid and sid=1;
意思是 选择课程名称和课程老师 来自 教师表 和 选课表 中 当 选课表的课程id=教师表课程id 并且 学生id为1
例如查询老师叫 隔壁老王的老师 体育课程的学生
意思是 选择学生姓名 来自 学生表 条件 (来自教师表 和 选课表 中 当选课表课程id=3 并且 教师表课程id=3 并且老师为 隔壁老王)
select sname from Student where sid in(select sid from Course inner join selectCourse on selectCourse.cid=3 and Course.cid=3 and cteacher='隔壁老王');