表名和字段

学生表

Student(s_id,s_name,s_birth,s_sex)：学生编号、学生姓名、出生年月、学生性别。

课程表

Course(c_id,c_name,t_id)：课程编号、课程名称、教师编号。

教师表

Teacher(t_id,t_name)：教师编号、教师姓名。

成绩表

Score(s_id,c_id,s_score)：学生编号、课程编号、分数。

建表语句

# 建表语句
# 学生表
CREATE TABLE `student`(
    `s_id` VARCHAR(20),
    `s_name` VARCHAR(20) NOT NULL DEFAULT ‘‘,
    `s_birth` VARCHAR(20) 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 ‘‘,
    PRIMARY KEY(`t_id`)
);

# 成绩表
CREATE TABLE `score`(
    `s_id` VARCHAR(20),
    `c_id` VARCHAR(20),
    `s_score` INT(3),
    PRIMARY KEY(`s_id`,`c_id`)
);

# 插入学生表测试数据
insert into Student values(‘1‘, ‘赵雷‘, ‘1990-01-01‘, ‘男‘);
insert into Student values(‘2‘, ‘钱电‘, ‘1990-12-21‘, ‘男‘);
insert into Student values(‘3‘, ‘孙风‘, ‘1990-05-20‘, ‘男‘);
insert into Student values(‘4‘, ‘李云‘, ‘1990-08-06‘, ‘男‘);
insert into Student values(‘5‘, ‘周梅‘, ‘1991-12-01‘, ‘女‘);
insert into Student values(‘6‘, ‘吴兰‘, ‘1992-03-01‘, ‘女‘);
insert into Student values(‘7‘, ‘郑竹‘, ‘1989-07-01‘, ‘女‘);
insert into Student values(‘8‘, ‘王菊‘, ‘1990-01-20‘, ‘女‘);

# 插入课程表测试数据
insert into Course values(‘1‘, ‘语文‘, ‘2‘);
insert into Course values(‘2‘, ‘数学‘, ‘1‘);
insert into Course values(‘3‘, ‘英语‘, ‘3‘);

# 插入教师表测试数据
insert into Teacher values(‘1‘, ‘张三‘);
insert into Teacher values(‘2‘, ‘李四‘);
insert into Teacher values(‘3‘, ‘王五‘);

# 插入成绩表测试数据
insert into Score values(‘1‘, ‘1‘, 80);
insert into Score values(‘1‘, ‘2‘, 90);
insert into Score values(‘1‘, ‘3‘, 99);
insert into Score values(‘2‘, ‘1‘, 70);
insert into Score values(‘2‘, ‘2‘, 60);
insert into Score values(‘2‘, ‘3‘, 80);
insert into Score values(‘3‘, ‘1‘, 80);
insert into Score values(‘3‘, ‘2‘, 80);
insert into Score values(‘3‘, ‘3‘, 80);
insert into Score values(‘4‘, ‘1‘, 50);
insert into Score values(‘4‘, ‘2‘, 30);
insert into Score values(‘4‘, ‘3‘, 20);
insert into Score values(‘5‘, ‘1‘, 76);
insert into Score values(‘5‘, ‘2‘, 87);
insert into Score values(‘6‘, ‘1‘, 31);
insert into Score values(‘6‘, ‘3‘, 34);
insert into Score values(‘7‘, ‘2‘, 89);
insert into Score values(‘7‘, ‘3‘, 98);

MySQL最经典50道练习题