1.表结构:
2.黏贴如下sql,直接建表
# 1、创建表
# 创建班级表
create table class(
cid int primary key auto_increment,
caption varchar(32) not null
);
# 创建学生表
create table student(
sid int primary key auto_increment,
gender char(1) not null,
class_id int not null,
sname varchar(32) not null,
foreign key(class_id) references class(cid) on delete cascade on update cascade
);
# 创建老师表
create table teacher(
tid int primary key auto_increment,
tname varchar(32) not null
);
# 创建课程表
create table course(
cid int primary key auto_increment,
cname varchar(32) not null,
teacher_id int not null,
foreign key(teacher_id) references teacher(tid) on delete cascade on update cascade
);
# 创建成绩表
create table score(
sid int primary key auto_increment,
student_id int not null,
course_id int not null,
num int not null,
foreign key(student_id) references student(sid) on delete cascade on update cascade,
foreign key(course_id) references course(cid) on delete cascade on update cascade
);
# 2、插入记录
# 班级表插入记录
insert into class values
('1', '三年二班'),
('2', '三年三班'),
('3', '一年二班'),
('4', '二年一班');
# 学生表插入记录
insert into student values
('1', '男', '1', '理解'),
('2', '女', '1', '钢蛋'),
('3', '男', '1', '张三'),
('4', '男', '1', '张一'),
('5', '女', '1', '张二'),
('6', '男', '1', '张四'),
('7', '女', '2', '铁锤'),
('8', '男', '2', '李三'),
('9', '男', '2', '李一'),
('10', '女', '2', '李二'),
('11', '男', '2', '李四'),
('12', '女', '3', '如花'),
('13', '男', '3', '刘三'),
('14', '男', '3', '刘一'),
('15', '女', '3', '刘二'),
('16', '男', '3', '刘四');
# 老师表插入记录
insert into teacher values
('1', '张磊'),
('2', '李平'),
('3', '刘海燕'),
('4', '朱云海'),
('5', '李春秋');
# 课程表插入记录
insert into course values
('1', '生物', '1'),
('2', '物理', '2'),
('3', '体育', '3'),
('4', '美术', '2');
# 成绩表插入记录
insert into score values
('1', '1', '1', '10'),
('2', '1', '2', '9'),
('3', '1', '3', '76'),
('5', '1', '4', '66'),
('6', '2', '1', '8'),
('8', '2', '3', '68'),
('9', '2', '4', '99'),
('10', '3', '1', '77'),
('11', '3', '2', '66'),
('12', '3', '3', '87'),
('13', '3', '4', '99'),
('14', '4', '1', '79'),
('15', '4', '2', '11'),
('16', '4', '3', '67'),
('17', '4', '4', '100'),
('18', '5', '1', '79'),
('19', '5', '2', '11'),
('20', '5', '3', '67'),
('21', '5', '4', '100'),
('22', '6', '1', '9'),
('23', '6', '2', '100'),
('24', '6', '3', '67'),
('25', '6', '4', '100'),
('26', '7', '1', '9'),
('27', '7', '2', '100'),
('28', '7', '3', '67'),
('29', '7', '4', '88'),
('30', '8', '1', '9'),
('31', '8', '2', '100'),
('32', '8', '3', '67'),
('33', '8', '4', '88'),
('34', '9', '1', '91'),
('35', '9', '2', '88'),
('36', '9', '3', '67'),
('37', '9', '4', '22'),
('38', '10', '1', '90'),
('39', '10', '2', '77'),
('40', '10', '3', '43'),
('41', '10', '4', '87'),
('42', '11', '1', '90'),
('43', '11', '2', '77'),
('44', '11', '3', '43'),
('45', '11', '4', '87'),
('46', '12', '1', '90'),
('47', '12', '2', '77'),
('48', '12', '3', '43'),
('49', '12', '4', '87'),
('52', '13', '3', '87');
# 1、创建表
# 创建班级表
create table
