mysql多表查询

多表查询,SQL JOINS

1. inner join内连接

select s.name as student_name,t.name as teacher_name from students as s inner join teachers as t on s.teacherid=t.tid; /*2张表取交集,内连接*/

2. 交叉连接

select * from students cross join teachers; /*笛卡尔乘积,students表内容和teacher表内容各自组合一遍*/

 

3. 左外连接

select s.name as student_name,t.name as teacher_name from students as s left outer join teachers as t on s.teacherid=t.tid;

 

1.)左内连接

select s.name as student_name,t.name as teacher_name from students as s left outer join teachers as t on s.teacherid=t.tid and t.name is null;

 

4.右外连接

select s.name as student_name,t.name as teacher_name from teachers as t right outer join students as s on s.teacherid=t.tid;

 

5.完全外连接

union

 

 

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

-> union

-> select s.name as student_name,t.name as teacher_name from students as s right outer join teachers as t on s.teacherid=t.tid;

 

5.自连接

select s1.name as emp, s2.name as leader from students as s1 inner join students as s2 on s1.teacherid=s2.stuid; /*使用内连接取2张表的交集*/

 

select s1.name as emp, s2.name as leader from students as s1 left outer join students as s2 on s1.teacherid=s2.stuid; /*使用左外连接取2张表的交集*/

 

6.子查询

#查看分数大于平均分的分数

select stuid,score from scores where score > (select avg(score) from scores);

 

#查询分数大于平均分数,学生的姓名和分数

select st.name,sc.score from students as st inner join scores as sc on st.stuid=sc.stuid and score > (select avg(score) from scores);