多表合并

1.多表纵向合并union

MariaDB [hellodb]> select from teachers union select from students;

2.交叉连接

select from students cross join select from teachers;

3.内连接inner join

MariaDB [hellodb]> select * from students inner join teachers on students.teacherid=teachers.tid;

MariaDB [hellodb]> select stuid,s.name,tid,t.name from students s inner join teachers t on s.teacherid=t.tid;

MariaDB [hellodb]> select stuid,s.name,tid,t.name from students s,teachers t where s.teacherid=t.tid;

1）内连接后过滤数据

select stuid,s.name,tid,t.name from students s inner join teachers t on s.teacherid=t.tid and stuid >3;

4.左外连接

MariaDB [hellodb]> select stuid,s.name,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid;

5.右外连接

MariaDB [hellodb]> select stuid,s.name,tid,t.name from students as s right join teachers as t on s.teacherid=t.tid;

6.左外连接扩展用法

MariaDB [hellodb]> select stuid,s.name,teacherid,tid,t.name from students as s left outer join teachers as t on s.teacherid=t.tid where t.tid is null;

7.完全外连接 full outer joion

select from students left join teachers on students.teacherid=teachers.tid
union
select from students right join teachers on students.teacherid=teachers.tid;

子查询：select 的执行结果，被其它SQL调用
select * from students where age < ( select avg(age) from students );
MariaDB [hellodb]> select stuid,name,age from students where age > (select avg(age) from students);

8.完全外连接的扩展示例

MariaDB [hellodb]> select * from (select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s left join teachers t on s.teacherid=t.tid union select s.stuid,s.name s_name,s.teacherid,t.tid,t.name t_name from students s right join teachers t on s.teacherid=t.tid) as a where a.teacherid is null or a.tid is null;

自连接
MariaDB [hellodb]> select emp.name emp_name , leader.name leader_name from employee emp inner join employee as leader on emp.leaderid=leader.id;

三张表连接示例
MariaDB [hellodb]> select st.stuid,st.name,sc.score,co.course from students as st inner join scores sc on st.stuid=sc.stuid inner join courses co on sc.courseid=co.courseid;