MySQL_表关系、连接查询
备注:本文是自我学习使用,若有错误,欢迎指正。
一、表关系
1.一对一
创建‘man’和‘woman’表,一个丈夫通过主键‘mid’对应一个妻子的主键‘wid’。每一个‘妻子’都对应唯一一个‘丈夫’。
2.一对多
创建父亲表:
create table father(
-> fid int primary key,
-> fname varchar(10)
->);
创建儿子表:
create table son(
-> sid int primary key,
-> sname varchar(10),
-> fid int ,
-> constraint fk_fid foreign key(fid) references father(fid));
插入数据:
insert into father values(01,'父亲');
insert into son values(001,'儿子',01);
select *from father ,son where son.fid = father.fid;
- 与一对一区别:'妻子’表和‘丈夫’表是一一对应的,所以外键可以设在任一表,但一对多,外键只能设在‘儿子’表(多的表),不然设在‘父亲’表的话一个‘fid’是对应多个‘sid’的这在表格是无法表示的。
3.多对多
创建课程表
mysql> create table course(
-> cid int,
-> cname varchar(10),
-> primary key(cid));
创建学生表
mysql> create table student(
-> sid int primary key,
-> sname varchar(10));
创建成绩表
mysql> create table result(
-> cid int,
-> sid int,
-> primary key(cid,sid),
-> result int,
-> constraint fk_cid foreign key (cid) references course(cid),
-> constraint fk_sid foreign key (sid) references student(sid));
插入数据
insert into course values(1,'c++');
insert into course values(2,'java');
insert into student values(101,'学生1');
insert into student values(102,'学生2');
insert into result values(1,101,99),(1,102,66),(2,101,88),(2,102,77);
select s.*,c.*,r.result from student s,course c,result r where r.cid=c.cid and r.sid =s.sid;
- 学生和课程是多对多关系(每个学生可以选修多门课程,每门课程可以被多个学生选修),所以会产生一个中间表:成绩表(每个学生的每一个课程都应该有一个成绩)
二、连接查询
先准备好两个表。
1.外连接
1.1左外连接
格式:
select *from 表1 left outer join 表2 on 连接条件;
select *from one left outer join two on one.aid =two.bid;
- 效果:以表1为主表,表1有的主键数据,表2没的,则表2列显示为NULL,如‘04’列;表1没有的主键数据,表2有的,则不显示。如‘05’列。
1.2右外连接
格式:
select *from 表1 right outer join 表2 on 连接条件;
select *from one right outer join two on one.aid =two.bid;
- 效果同上,将表1与表2相换。
1.3全外连接
MySQL没有全外连接,一般用union来代替
mysql> select *from one left outer join two on one.aid =two.bid
-> union
-> select *from one right outer join two on one.aid =two.bid;
2.内连接
select *from one inner join two on one.aid =two.bid;
- 效果:把2个表中都有的主键显示出来,缺一不可。
3.自然连接
select*from one natural join two;
- 一般不用,产生了数据冗余。