多表查詢

**交叉連接 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;