编辑代码

CREATE DATABASE test;
use test;

-- 学生表
CREATE TABLE Student(
	s_id VARCHAR(20),
	s_name VARCHAR(20) NOT NULL DEFAULT '',
	s_birth VARCHAR(200) NOT NULL DEFAULT '',
	s_sex VARCHAR(10) NOT NULL DEFAULT '',
	PRIMARY KEY(s_id)
);

-- 课程表
CREATE TABLE Course(
    c_id VARCHAR(20),
    c_name VARCHAR(20) NOT NULL DEFAULT '',
    t_id VARCHAR(20) NOT NULL,
    PRIMARY KEY(c_id)
);

-- 教师表
CREATE TABLE Teacher(
    t_id varchar(20),
    t_name varchar(20) NOT NULL DEFAULT ''
);

-- 成绩表
CREATE TABLE Score(
    s_id varchar(20),
    c_id varchar(20),
    s_score varchar(20)
);

-- 插入学生表数据
INSERT INTO Student VALUES('001','甲','1995-05-02','男');
INSERT INTO Student VALUES('002','乙','1995-05-02','女');
INSERT INTO Student VALUES('003','丙','1995-05-02','男');
INSERT INTO Student VALUES('004','丁','1995-05-02','女');
INSERT INTO Student VALUES('005','戊','1995-05-02','女');
INSERT INTO Student VALUES('006','戌','1995-05-02','女');
INSERT INTO Student VALUES('007','庚','1995-05-02','男');
INSERT INTO Student VALUES('008','辛','1995-05-02','男');
INSERT INTO Student VALUES('009','亥','1995-05-02','男');

-- 插入课程表数据
INSERT INTO Course VALUES('c01','软件工程','t01');
INSERT INTO Course VALUES('c02','数据库','t02');
INSERT INTO Course VALUES('c03','计算机网络','t03');
INSERT INTO Course VALUES('c04','计算机组成原理','t04');
-- 插入教师表数据
INSERT INTO Teacher VALUES('t01','张三');
INSERT INTO Teacher VALUES('t02','李四');
INSERT INTO Teacher VALUES('t03','王五');
INSERT INTO Teacher VALUES('t04','赵六');

-- 插入成绩表数据
insert into Score values('s01' , 'c01' , 80);
insert into Score values('s01' , 'c02' , 90);
insert into Score values('s01' , 'c03' , 99);
insert into Score values('s02' , 'c01' , 70);
insert into Score values('s02' , 'c02' , 60);
insert into Score values('s02' , 'c03' , 80);
insert into Score values('s03' , 'c01' , 80);
insert into Score values('s03' , 'c02' , 80);
insert into Score values('s03' , 'c03' , 80);
insert into Score values('s04' , 'c01' , 50);
insert into Score values('s04' , 'c02' , 30);
insert into Score values('s04' , 'c03' , 20);
insert into Score values('s05' , 'c01' , 76);
insert into Score values('s05' , 'c02' , 87);
insert into Score values('s06' , 'c01' , 31);
insert into Score values('s06' , 'c03' , 34);
insert into Score values('s07' , 'c02' , 89);
insert into Score values('s07' , 'c03' , 98);




SELECT * FROM Student;
SELECT * FROM Course;
SELECT * FROM Teacher;
Select * FROM Score;