MySQL-学生表以及课程、老师、成绩表的创建

学生表以及课程、老师、成绩表的创建

-- 删除表
drop table if exists score;
drop table if exists student;
drop table if exists course;
drop table if exists teacher;

学生表

create table student(
	stuNo	char(5) primary key, -- 学生学号
	stuName varchar(32) not null, -- 学生姓名
	gender char(1) not null	default '男', -- 学生性别
	age int not null -- 学生年龄
)charset='utf8';

-- 添加学生数据
insert into student(stuNo,stuName,gender,age) values('001','李志','男',14);
insert into student(stuNo,stuName,gender,age) values('002','宋东野','男',23);
insert into student(stuNo,stuName,gender,age) values('003','赵雷','男',34);
insert into student(stuNo,stuName,gender,age) values('004','马頔','男',32);
insert into student(stuNo,stuName,gender,age) values('005','陈粒','女',18);
insert into student(stuNo,stuName,gender,age) values('006','筠子','女',4);
insert into student(stuNo,stuName,gender,age) values('007','寸铁','男',56);
insert into student(stuNo,stuName,gender,age) values('008','狗毛','男',47);
insert into student(stuNo,stuName,gender,age) values('009','崔健','男',39);
insert into student(stuNo,stuName,gender,age) values('010','草东','男',40);
insert into student(stuNo,stuName,gender,age) values('011','张悬','女',36);
insert into student(stuNo,stuName,gender,age) values('012','撒娇','女',25);
insert into student(stuNo,stuName,gender,age) values('013','宋东野','男',35);

教师表

create table teacher(
	tNo char(5) primary key, -- 教师号
	tName varchar(32) not null -- 教师名字
)charset='utf8';

-- 添加老师数据
insert into teacher(tNo,tName) values('t001','张三');
insert into teacher(tNo,tName) values('t002','李四');
insert into teacher(tNo,tName) values('t003','王二');
insert into teacher(tNo,tName) values('t004','王五');

课程表

create table course(
	cNo char(5) primary key, -- 课程号
	cName varchar(32) not null unique, -- 课程名
	tNo char(5), -- 教师号
	foreign key(tNo) references teacher(tNo)
)charset='utf8';

-- 添加课程数据
insert into course(cNo,cName,tNo) values('c001','数据结构','t001');
insert into course(cNo,cName,tNo) values('c002','操作系统','t002');
insert into course(cNo,cName,tNo) values('c003','计算机网络','t001');
insert into course(cNo,cName,tNo) values('c004','软件工程','t003');
insert into course(cNo,cName,tNo) values('c005','计算机组成原理','t002');
insert into course(cNo,cName,tNo) values('c006','数据库系统','t002');
insert into course(cNo,cName,tNo) values('c007','C语言','t003');
insert into course(cNo,cName,tNo) values('c008','高等数学','t004');

成绩表

create table score(
	sNo char(5) not null, -- 教师号
	cNo char(5) not null, -- 课程号
	score double not null, -- 成绩
	primary key(sNo,cNo)
);

-- 添加成绩数据
insert into score(sNo,cNo,score) values('001','c001',56);
insert into score(sNo,cNo,score) values('001','c002',87);
insert into score(sNo,cNo,score) values('001','c003',69);
insert into score(sNo,cNo,score) values('001','c004',92);
insert into score(sNo,cNo,score) values('002','c001',76);
insert into score(sNo,cNo,score) values('002','c003',69);
insert into score(sNo,cNo,score) values('002','c005',90);
insert into score(sNo,cNo,score) values('003','c002',53);
insert into score(sNo,cNo,score) values('003','c003',96);
insert into score(sNo,cNo,score) values('004','c004',67);
insert into score(sNo,cNo,score) values('005','c008',69);
insert into score(sNo,cNo,score) values('005','c007',78);
insert into score(sNo,cNo,score) values('005','c002',70);
insert into score(sNo,cNo,score) values('005','c003',81);
insert into score(sNo,cNo,score) values('006','c004',93);
insert into score(sNo,cNo,score) values('006','c008',62);
insert into score(sNo,cNo,score) values('007','c001',58);
insert into score(sNo,cNo,score) values('007','c002',76);
insert into score(sNo,cNo,score) values('008','c003',65);
insert into score(sNo,cNo,score) values('008','c004',89);
insert into score(sNo,cNo,score) values('009','c005',92);
insert into score(sNo,cNo,score) values('009','c006',90);
insert into score(sNo,cNo,score) values('009','c007',42);
insert into score(sNo,cNo,score) values('009','c008',80);
insert into score(sNo,cNo,score) values('010','c002',75);
insert into score(sNo,cNo,score) values('010','c004',69);
insert into score(sNo,cNo,score) values('011','c004',92);
insert into score(sNo,cNo,score) values('011','c005',99);
insert into score(sNo,cNo,score) values('011','c002',100);
insert into score(sNo,cNo,score) values('011','c006',76);
insert into score(sNo,cNo,score) values('012','c003',71);
insert into score(sNo,cNo,score) values('012','c002',83);
insert into score(sNo,cNo,score) values('013','c004',80);
insert into score(sNo,cNo,score) values('013','c007',96);