提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  数据库

当前栏目

mysql多表查询 2.建立多表数据库案例

mysql案例数据库 查询 建立 多表
2023-09-11 14:17:08 时间

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","15529144217@163.com");

insert into infor values(0,”小明”,”15129144319”,”15129144319@163.com”);

insert into infor values(0,”叶良辰”,”13929144291”,”13929144291@qq.com”);

insert into infor values(0,”叶小辰”,”13929144298”,”13929144298@163.com”);

insert into infor values(0,”东方不败”,”16629144412”,”16629144412@qq.com”);

insert into infor values(0,”令狐冲”,”16629144218”,”16629144218@163.com”);

insert into infor values(0,”赵一天”,”13729147491”,”13729147491@sina.com”);

insert into infor values(0,”小智”,”13629146688”,”13629146688@163.com”);

部门表插入数据：

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);

结果如图所示：

相关文章