练习:多表合并

多表合并

练习:多表合并

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;
练习:多表合并

上一篇:mybatis-03 多对一,一对多的处理


下一篇:排除GC引起的CPU飙高