Mysql-多表查询

1.多表关联查询

//语法
select * from 表1,B表2;

mysql> select * from student,sc;#有很多冗余数据

2.内连接查询

select 字段1,字段2... from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件;

mysql> select * from student inner join sc on student.sno=sc.sno;
# 带有条件
mysql> select * from student inner join sc on student.sno=sc.sno where ssex="女"
order by score desc;
5 rows in set (0.00 sec)

3.外连接查询

select 字段 from 表1 left outer join 表2 on 表1.字段=表2.字段 where 条件;

mysql> select * from student left outer join sc on student.sno=sc.sno;

select 字段 from 表1 right outer join 表2 on 表1.字段=表2.字段 where 条件;

//联合查询
select * from student left outer join sc on student.sno=sc.sno UNION
select * from student right outer join sc on student.sno=sc.sno;


mysql> select * from student left outer join sc on student.sno=sc.sno UNION
ALL select* from student right outer join sc on student.sno=sc.sno; # 不去重

4.自连接查询

select 字段 from 表1 a , 表1 b where 条件;
或者
select 字段 from 表1 a [left] join 表1 b on 条件;