数据表之间的关联
六、数据表之间的关联
在此只讲简单的内容,遇到的情况不多
建表语句:
1、create table class(id int auto_increment primary key, name varchar(20) not null, stunum int not null);
2、create table student(id int auto_increment primary key, name varchar(20) not null, gender bit default 1, classid int not null, foreign key(classid) references class(id));
插入一些数据:
Insert into class values(0,”python01”, 55), (0,”python02”, 50),(0,”python03”, 60),(0,”python04”, 80);
Insert into student values(0,“tom”, 1 ,1);
Insert into student values(0, "lilei", 1 10);(报错,关联外键值没有10)
Insert into student values(0, "jack", 1 2);
insert into student values(0,"jack1",1,2);
insert into student values(0,"jack2",1,2);
insert into student values(0,"jack3",1,2);
insert into student values(0,"jack4",1,2);
insert into student values(0,"jack5",1,3);
insert into student values(0,"jack6",1,3);
关联查询:
分类:
1、表A inner join表B表A与表B匹配的行会出现在结果集中
Selectstudent.name, class.namefromclass inner join studenton class.id=student.classid;
说明:不想显示id,显示id对应的名字
2、表A left join表B:
表A与表B匹配的行会出现在结果集中,外加表A中独有的数据,未对应的数据会使用null填充
Selectstudent.name, class.namefromclass left join studenton class.id=student.classid;
3、表A right join表B:
表A与表B匹配的行会出现在结果集中,外加表B中独有的数据,未对应的数据会使用null填充
Selectstudent.name, class.namefromclass right join studenton class.id=student.classid;
长按下方二维码关注:
领取专属 10元无门槛券
私享最新 技术干货