SQL练习题 （学生信息表 教师信息 课程信息等）

– 创建学生信息表

CREATE TABLE Student(
Sid VARCHAR(10),
Sname VARCHAR(10),
Sage datetime,
Ssex VARCHAR(10)
);

– 由于建表没有定义字符编码 utf8

alter table student default character set utf8;
alter table student change ssex ssex varchar(10) character SET utf8;
SHOW create TABLE Student;
SELECT * FROM student;

– 添加数据

> INSERT into Student VALUES('01',"赵雷",'1993-11-01','男'); INSERT into
> Student VALUES('02','钱电','1993-01-01','男'); INSERT into Student
> VALUES('03','孙风','1994-11-21','男'); INSERT into Student
> VALUES('04','李云','1990-03-01','男'); INSERT into Student
> VALUES('05','周梅','1990-10-01','女'); INSERT into Student
> VALUES('06','吴兰','1994-01-31','女'); INSERT into Student
> VALUES('07','郑竹','1990-12-01','女'); INSERT into Student
> VALUES('08','张三','1996-05-01','女'); INSERT into Student
> VALUES('09','李四','1990-01-11','女'); INSERT into Student
> VALUES('10','李四','1990-11-23','女'); INSERT into Student
> VALUES('11','赵六','1994-12-04','女'); INSERT into Student
> VALUES('12','孙七','1991-01-30','男'); INSERT into Student
> VALUES('13','尧舜','1995-01-01','男');

– 科目表

CREATE TABLE Course(
CId VARCHAR(10),
Cname VARCHAR(10),
TId VARCHAR(10)
);

alter table course default character set utf8;
alter table course change cname cname varchar(10) character SET utf8;
INSERT INTO Course VALUES('01','语文','02');
INSERT INTO Course VALUES('02','数学','01');
INSERT INTO Course VALUES('03','英文','03');

– 教师表

CREATE TABLE Teacher(
Tid VARCHAR(10),
Tname VARCHAR(10)
);

alter table teacher change tname tname varchar(10) character SET utf8;
INSERT INTO teacher VALUES('01','张三');
INSERT INTO teacher VALUES('02','李四');
INSERT INTO teacher VALUES('03','王五');

– 成绩表

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