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条 444

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