MySQL可拿来练手的测试数据（表结构说明及测试数据创建）

一、表结构

1、学生表

Student(Sid,Sname,Sage,Ssex) –> 学生编号，学生姓名，出生年月，学生性别

2、课程表

Course(Cid,Cname,Tid) –> 课程编号，课程名称，教师编号

3、教师表

Teacher(Tid,Tname) –> 教师编号，教师姓名

4、成绩表

SC(Sid,Cid,Score) –> 学生编号，课程编号，分数

二、测试数据创建

#1、学生表
— 建表语句
CREATE TABLE Student (
  SID VARCHAR (10),
  Same varchar (10),
  Sage datetime,
  Ssex varchar (10)
)
— 插入测试数据
INSERT INTO Student VALUES(’01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
INSERT INTO Student VALUES(’02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
INSERT INTO Student VALUES(’03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
INSERT INTO Student VALUES(’04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
INSERT INTO Student VALUES(’05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
INSERT INTO Student VALUES(’06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
INSERT INTO Student VALUES(’07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
INSERT INTO Student VALUES(’08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);

#2、课程表
— 建表语句
CREATE TABLE Course (
  CID VARCHAR (10),
  Cname nvarchar (10),
  TID VARCHAR (10)
)
— 插入测试数据
INSERT INTO Course VALUES(’01’ , ‘语文’ , ’02’);
INSERT INTO Course VALUES(’02’ , ‘数学’ , ’01’);
INSERT INTO Course VALUES(’03’ , ‘英语’ , ’03’);

#3、教师表
— 建表语句
CREATE TABLE Teacher (
  TID VARCHAR (10),
  Tname nvarchar (10)
)
— 插入测试数据
INSERT INTO Teacher VALUES(’01’ , ‘张三’);
INSERT INTO Teacher VALUES(’02’ , ‘李四’);
INSERT INTO Teacher VALUES(’03’ , ‘王五’);

# 4、成绩表
— 建表语句
CREATE TABLE SC (
  SID VARCHAR (10),
  CID VARCHAR (10),
  score DECIMAL (18, 1)
)
— 插入测试数据
INSERT INTO SC VALUES(’01’ , ’01’ , 80);
INSERT INTO SC VALUES(’01’ , ’02’ , 90);
INSERT INTO SC VALUES(’01’ , ’03’ , 99);
INSERT INTO SC VALUES(’02’ , ’01’ , 70);
INSERT INTO SC VALUES(’02’ , ’02’ , 60);
INSERT INTO SC VALUES(’02’ , ’03’ , 80);
INSERT INTO SC VALUES(’03’ , ’01’ , 80);
INSERT INTO SC VALUES(’03’ , ’02’ , 80);
INSERT INTO SC VALUES(’03’ , ’03’ , 80);
INSERT INTO SC VALUES(’04’ , ’01’ , 50);
INSERT INTO SC VALUES(’04’ , ’02’ , 30);
INSERT INTO SC VALUES(’04’ , ’03’ , 20);
INSERT INTO SC VALUES(’05’ , ’01’ , 76);
INSERT INTO SC VALUES(’05’ , ’02’ , 87);
INSERT INTO SC VALUES(’06’ , ’01’ , 31);
INSERT INTO SC VALUES(’06’ , ’03’ , 34);
INSERT INTO SC VALUES(’07’ , ’02’ , 89);
INSERT INTO SC VALUES(’07’ , ’03’ , 98);