多表查询

**交叉连接 cross join 内连接 inner join 外连接  左连接 left join 右连接 right join 联合查询 UNION 全连接 **

1、多表纵向合并

纵向合并需要注意的是，两张合并的表查询结果的字段数必须一致，

MariaDB [hellodb]> select stuid,name from students     -> union      -> select tid,name from teachers;

查询结果 我们尝试将第二张表中的name,tid查询的顺序反过来试一下

MariaDB [hellodb]> select stuid,name from students     -> union      -> select name,tid from teachers;

查询结果

总结：

    我们发现纵向合并对字段的类型并不严格，只要与第一张表的字段数是相同的就可以，当然，第二个查询的结果显示然是没有意义的。     在别的数据库中，例如orcal或serverSQL会报错，因为类型不符。

2、union的去重功能

查看teachers表 重新构建一个与teachers表相似的表 添加数据

insert teachers2 (tid,name,age,gender)values(5,'linux',22,'m'); insert teachers2 (tid,name,age,gender)values(6,'Python',22,'m');

将两张表连接起来再次查看

总结：

    union本身亦可以去重，当然这里只是示范一下，还有个命令可以直接去重     select distinct * from teacher2 可以在自己表中去掉重复的行 

CROSS JOINS

首先，我们之前利用union 进行了纵向连接，那么，我们可不可以横向连接呢？当然是可以的，纵向连接由字段数量的限制，而横向连接是没有字段的限制的，比如：创建两个表接下来我们直接最者两张表进行cross join 连接，

首先，第一张表的第一条记录和第二张表的每条记录进行整合，这就有了15条记录。在数据库中百万级别的表才算有点规模，假如真的这样做了，无疑是灾难性的。其次，这样将两张表连接起来是没有意义。所以，我们使用内连接来进行连接，找出对应两张表的关联性，设定条件进行连接查找。

1、内连接

关键字： inner join

MariaDB [hellodb]> select * from      -> students inner join teachers     -> on  条件 等价于 where      -> students.teacherid=teachers.tid;     由于是跨表查询，所以，必须指明哪个表下的字段，否则系统无法识别来源

查询结果

select  对字段定义别名     stuid,s.name as studentname ,s.age as studentage tid     t.name as teachername ,t.age as teacherage     from      students as s   对表定义别名     inner join      连接     teachers as t   定义别名     on              条件       s.teacherid=t.tid;    

查询结果

2、左外连接

说明：

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

查询结果

3、右外连接

说明：

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

查询结果

4、左外连接 扩展

说明：

查询结果

5、右外连接 扩展

说明：

MariaDB [hellodb]> select      -> stuid,s.name,teacherid,     -> tid,t.name      -> from     -> students as s     -> left join     -> teachers as t     -> on      -> s.teacherid=t.tid    对有关联的查询结果再次进行过滤     -> where     -> stuid is null;

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

select * from students where age < (select avg(age) from students) ;

查询结果 现在我们接着上图中的问题：

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

查询结果

8、自连接

说明：

create table employee ( id int, name char(10), leader_id int     ); 插入信息 insert employee values(1,'A',null); insert employee values(2,'B',1); insert emplyee values(3,'C',2); insert emplyee values(4,'D',3);

    我们要查询的是第一张表的NAME和第二张表的上级的NAME，我们发现，A表的TID和第二张表的ID是关联的，     当我们查询1号员工的TID的时候，由于1号员工的TID是null，所以，我们要显示的上级NAME是NULL，     当我们查询2号员工的上级ID时，当A表的TID等于B表的ID的时候，条件达成，显示B表的姓名。以此类推 

select A.name as employee_name,B.name as leader_name from employee as A left join employee as B on A.leaderid=B.id;

    说明：     假设我们有两张表，学生表和课程表     学生表存放的是：     stu_id,stu_name,stu_cassid     课程表中存放的是：     cours_id, cours_name     在数据库中，有很多逻辑结构，一对一，一对多，多对多。结合实际情况，我们一个学生可能同时学习多个课程，每个课程可能有好多学生学，所以，由此可以看出是多对多的关系，     要实现多对多，在数据库中我们可以创建第三个表来实现，     第三张表中存放的是     id,stu_id,cours_id，score     但是这个两个字段显然都不合适做主键，所以，就可以添加一个字段ID做主键。再添加一个score字段，存放课程成绩

MariaDB [hellodb]> select stu.name,sc.score      -> from     -> students as stu      -> inner join     -> scores as sc     -> on     -> stu.stuid=sc.stuid;

    说明;     我们暂时不考虑学生表中的信息，只查询成绩表和课程表，     只取两个表的交集部分，依旧还是使用inner join

MariaDB [hellodb]> select course.course,sc.score     -> from     -> scores as sc     -> inner join     -> courses as course     -> on     -> course.courseid=sc.courseid;

    说明：     我们要实现的是某个学生的某个课程对应的成绩，     我们对比两张表，发现，我只要把第一步的查询结果与第二张表的查询结果联合在一次就达到了我们的最终要求。   

select * from  students inner join scores on students.stuid=scores.stuid; 查询结果

MariaDB [hellodb]> select     -> stu.name as student_name,     -> co.course as course_name,     -> sc.score      -> from     -> students as stu     -> inner join     -> scorses as sc     -> on     -> stu.stuid=sc.stuid     -> inner join     -> courses as co     -> on     -> sc.courseid=co.courseid;