mysql多表查询 2.建立多表数据库案例
mysql多表查询 2.建立多表数据库案例
1)建立数据库
create database student2017 charset=utf8;
2)建立3张表
信息表: create table infor( id int auto_increment primary key not null, name varchar(10) not null, phone varchar(15) not null, email varchar(20) ); |
部门表: create table branch( id int(4) primary key not null, class varchar(6) ); |
成绩表: create table scores( id int auto_increment primary key not null, nameid int, classid int, python int(4), java int(4), c int(4), constraint scores_infor_fk1 foreign key(nameid) references infor(id) on update cascade on delete cascade, constraint scores_branch_fk foreign key(classid) references branch(id) on update cascade on delete cascade ); |
3)插入数据
信息表插入数据:
insert into infor values(1,"小青","15529144217","[email protected]");
insert into infor values(0,”小明”,”15129144319”,”[email protected]”);
insert into infor values(0,”叶良辰”,”13929144291”,”[email protected]”);
insert into infor values(0,”叶小辰”,”13929144298”,”[email protected]”);
insert into infor values(0,”东方不败”,”16629144412”,”[email protected]”);
insert into infor values(0,”令狐冲”,”16629144218”,”[email protected]”);
insert into infor values(0,”赵一天”,”13729147491”,”[email protected]”);
insert into infor values(0,”小智”,”13629146688”,”[email protected]”);
部门表插入数据:
insert into branch values(6003,”华山派”);
insert into branch values(6004,”武当派”);
insert into branch values(6005,”逍遥派”);
insert into branch values(6006,”日月神教”);
成绩表插入数据:
insert into scores values(1,1,6006,82.5,84.8,88.8);
insert into scores values(0,2,6003,65,74.8,65.5);
insert into scores values(0,3,6004,89.5,74.5,90);
insert into scores values(0,4,6005,76,54,83.5);
insert into scores values(0,5,6005,77,66,62);
insert into scores values(0,6,6006,99,94,96);
insert into scores values(0,7,6003,66.5,73.9,49.8);
insert into scores values(0,8,6004,90.5,84.2,89);
结果如图所示: