join查询

1.  join查询

1.1.  建表语句

drop table if exists student_course;

drop table if exists course;

drop table if exists student;

create table course(

       cid int not NULL,

       cname varchar(20) not null,

       constraint pk_cid primary key(cid)

);

create table student(

    sid int not null,

    sname varchar(20),

    constraint pk_sid primary key(sid)

);

create table student_course(

    m_sid bigint not null,

    m_cid bigint not null,

    constraint pk_sid_cid primary key(m_sid,m_cid),

    constraint fk_sid foreign key(m_sid) references student(sid) ,

    constraint fk_cid foreign key(m_cid) references course(cid)

);

insert into student values(1,'张三'),(2,'李四'),(3,'王五'),(4,'麻子');

insert into course values(1,'高等数学'),(2,'大学英语'),(3,'大学物理'),(4,'电影欣赏');

insert into student_course values (1,2),(1,4),(2,4),(3,4);

select * from student;

select * from course;

select * from student_course;

1.2.  笛卡尔集，结果有64条 4*4*4

select * from course, student, student_course;

1.3.  内连接,求两个表的交集。

可以不指定inner，默认就是inner join。

select * from student_course inner join course on (m_cid = cid);

1.4.  左外连接

用法： a left join b on(a.id=b.id) 或者 a left join b using(id)。a的所有列均出现在结果中，b的数据被连接进来，无连接项的则显示为空，多连接项的在新行展示

select * from student left join student_course on(m_sid=sid);

select * from student left join student_course on(m_sid=sid) left join course on(m_cid=cid);

1.5.  右外连接

select * from student right join student_course on(m_sid=sid);

select * from student right join student_course on(m_sid=sid) right join course on(m_cid=cid);

1.6.  全外连接

用法： a full outer join b on(a.id=b.id) 或者 a full outer join b using(id). 其中outer可以省略。实际是一个内连接加上左表和右表独有的数据行，左表独有的行右表字段填充NULL

select * from student full join student_course on(m_sid=sid);

select * from student full join student_course on(m_sid=sid) full join course on(m_cid=cid);

UNION，INTERSECT和EXCEPT（并集，交集，差集）

参考文章： https://www.cnblogs.com/alianbog/p/5621562.html