create table scores( id int primary key auto_increment, stuid int, subid int, score decimal(5,2) );
alter table scores add constraint stu_sco foreign key(stuid) references students(id);
create table scores( id int primary key auto_increment, stuid int, subid int, score decimal(5,2), foreign key(stuid) references students(id), foreign key(subid) references subjects(id) );
alter table scores add constraint stu_sco foreign key(stuid) references students(id) on delete cascade;
级联操作的类型包括:
select students.sname,subjects.stitle,scores.score from scores inner join students on scores.stuid=students.id inner join subjects on scores.subid=subjects.id;
select students.sname,avg(scores.score) from scores inner join students on scores.stuid=students.id group by students.sname;
select students.sname,avg(scores.score) from scores inner join students on scores.stuid=students.id where students.gender=1 group by students.sname;
select subjects.stitle,avg(scores.score) from scores inner join subjects on scores.subid=subjects.id group by subjects.stitle;
select subjects.stitle,avg(scores.score),max(scores.score) from scores inner join subjects on scores.subid=subjects.id where subjects.isdelete=0 group by subjects.stitle;
如对本文有疑问, 点击进行留言回复!!
数据库优化-索引的创建-MySQL-index-SQL优化-避免全表扫描
网友评论