工具:notepad++
数据表示例
学生表 student{学号,姓名,性别,出生年月日,所在班级}
课程表course{客场号,课程名称,教师编号}
成绩表score{学号,课程号,成绩}
教师表teacher{教师编号,教师名字,教师性别,出生年月日,职称,所在部门}
CREATE TABLE student(
sno VARCHAR(20) PRIMARY key,
sname VARCHAR(20) not NULL,
ssex VARCHAR(10) not NULL,
sbirthday datetime,
class varchar(20)
);
CREATE TABLE teacher(
tno VARCHAR(20) PRIMARY key,
tname VARCHAR(20) not NULL,
tsex VARCHAR(10) not NULL,
tbirthday datetime,
prof VARCHAR(20),
depart VARCHAR(20) NOT NULL
);
#将课程的教师号设置为外键与教师表关联
CREATE TABLE course(
cno VARCHAR(20) PRIMARY key,
cname VARCHAR(20) NOT NULL,
tno VARCHAR(20) NOT NULL,
FOREIGN key(tno) REFERENCES teacher(tno)
);
#score表设置外键sno和cno,学号和课程号与学生表,课程表关联,再设置将课程号和学号设置为联合主键,防止数据重复
CREATE TABLE score(
sno VARCHAR(20) not NULL,
cno VARCHAR(20) not NULL,
dgree DOUBLE,
FOREIGN key(sno) REFERENCES student(sno),
FOREIGN key (cno) REFERENCES course(cno),
PRIMARY key(sno,cno)
);
#添加学生表
INSERT INTO student VALUES('101','张一','男','1999-10-1','95033');
INSERT INTO student VALUES('102','张二','男','1999-10-2','95033');
INSERT INTO student VALUES('103','张三','女','1999-10-3','95031');
INSERT INTO student VALUES('104','张四','女','1999-10-4','95031');
INSERT INTO student VALUES('105','张五','男','1999-10-5','95032');
INSERT INTO student VALUES('106','张六','女','1999-10-7','95032');
INSERT INTO student VALUES('107','张七','男','1999-10-7','95034');
INSERT INTO student VALUES('108','张八','男','1999-10-8','95034');
INSERT INTO teacher VALUES('801','李一','男','1960-3-1','副教授','计算机系');
INSERT INTO teacher VALUES('802','李二','女','1960-3-2','讲师','计算机系');
INSERT INTO teacher VALUES('803','李三','女','1960-3-3','教授','电子信息');
INSERT INTO teacher VALUES('804','李四','女','1960-3-4','助教','电子信息');
INSERT INTO course VALUES('3-101','C++','801');
INSERT INTO course VALUES('3-102','JAVA','802');
INSERT INTO course VALUES('3-103','PYTHON','803');
INSERT INTO course VALUES('3-104','PHP','804');
INSERT INTO score VALUES('101','3-101','86');
INSERT INTO score VALUES('101','3-102','75');
INSERT INTO score VALUES('101','3-103','88');
INSERT INTO score VALUES('102','3-104','90');
INSERT INTO score VALUES('102','3-102','65');
INSERT INTO score VALUES('102','3-103','76');
INSERT INTO score VALUES('103','3-101','66');
INSERT INTO score VALUES('103','3-104','88');
INSERT INTO score VALUES('103','3-103','99');
INSERT INTO score VALUES('104','3-104','63');
INSERT INTO score VALUES('104','3-103','87');
INSERT INTO score VALUES('104','3-101','79');
INSERT INTO score VALUES('105','3-101','79');
INSERT INTO score VALUES('105','3-102','76');
INSERT INTO score VALUES('105','3-103','99');
INSERT INTO score VALUES('106','3-104','90');
INSERT INTO score VALUES('107','3-104','76');
INSERT INTO score VALUES('108','3-103','88');