python练习题12：数据库基础

练习题1

创建如下表：

练习题一

建表语句：
class表

create table class (
cid int primary key auto_increment,
caption varchar(4) not null default ''
)engine=Innodb, charset=utf8;

## 插入值
insert into class (caption) values ("三年二班"), ("一年三班"), ("三年一班");

student表

create table student (
sid int primary key auto_increment,
sname varchar(32) not null default '',
gender char(1) not null default '',
class_id int,
constraint fk_student_class foreign key(class_id) references class(cid)
)engine=Innodb, charset=utf8;

## 插入值
insert into student (sname, gender, class_id) values ("钢蛋", "女", 1), ("铁锤", "女", 1), ("山炮", "男", 2);

teacher表

create table teacher (
tid int primary key auto_increment,
tname varchar(32) not null default ''
)engine=Innodb, charset=utf8;

## 插入值
insert into teacher (tname) values ("波多"), ("苍空"), ("饭岛");

course表

create table course (
cid int primary key auto_increment,
cname varchar(10) not null default '',
teacher_id int,
constraint fk_course_teacher foreign key(teacher_id) references teacher(tid)
)engine=Innodb, charset=utf8;

## 插入值
insert into course (cname, teacher_id) values ("生物", 1), ("体育", 1), ("物理", 2);

score表

create table score (
sid int primary key auto_increment,
student_id int,
course_id int,
number tinyint unsigned,
constraint fk_score_student foreign key(student_id) references student(sid),
constraint fk_score_course foreign key(course_id) references course(cid)
)engine=Innodb, charset=utf8;

## 插入值
insert into score (student_id,