此场景多用于题目所示的需求;也常用于降低某些历史表中的数据行数,或类似场景。以下以归档学生成绩为例。
Studentscores 为归档成绩表,scores为详细成绩表,两表依靠xh(学号)进行连接。
##mysql5.6及以后,mysql这款互联网数据库,为啥是互联网数据库,因为既免费又好上线。函数啥啥的真的好用,更新语句的写法也是各种各样,几乎是只要你能想的通,它就能办到。
#考生成绩表
create table studentscores (
xh varchar(10) primary key not null,
xm varchar(10) not null,
scores varchar(1000)
);
#成绩表
create table scores(
xh varchar(10) not null,
kmdm varchar(10) not null,
kmmc varchar(20) not null,
score float(6,2) not null,
primary key(xh,kmdm)
);
#插入成绩
insert into studentscores(xh,xm,scores) values ('1001','张3','');
insert into studentscores(xh,xm,scores) values ('1002','李3','');
select * from studentscores order by xh;
insert into scores (xh,kmdm,kmmc,score) values('1001','01','语文',60.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','02','数学',70.5);
insert into scores (xh,kmdm,kmmc,score) values('1001','03','英语',99);
insert into scores (xh,kmdm,kmmc,score) values('1002','01','语文',98);
insert into sco