多表查詢

**交叉連接 cross join 內(nèi)連接 inner join 外連接  左連接 left join 右連接 right join 聯(lián)合查詢 UNION 全連接 **

1、多表縱向合并

縱向合并需要注意的是，兩張合并的表查詢結(jié)果的字段數(shù)必須一致，

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

查詢結(jié)果 我們嘗試將第二張表中的name,tid查詢的順序反過來試一下

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

查詢結(jié)果

總結(jié)：

    我們發(fā)現(xiàn)縱向合并對字段的類型并不嚴格，只要與第一張表的字段數(shù)是相同的就可以，當然，第二個查詢的結(jié)果顯示然是沒有意義的。     在別的數(shù)據(jù)庫中，例如orcal或serverSQL會報錯，因為類型不符。

2、union的去重功能

查看teachers表 重新構(gòu)建一個與teachers表相似的表 添加數(shù)據(jù)

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

將兩張表連接起來再次查看

總結(jié)：

    union本身亦可以去重，當然這里只是示范一下，還有個命令可以直接去重     select distinct * from teacher2 可以在自己表中去掉重復(fù)的行 

CROSS JOINS

首先，我們之前利用union 進行了縱向連接，那么，我們可不可以橫向連接呢？當然是可以的，縱向連接由字段數(shù)量的限制，而橫向連接是沒有字段的限制的，比如：創(chuàng)建兩個表接下來我們直接最者兩張表進行cross join 連接，

首先，第一張表的第一條記錄和第二張表的每條記錄進行整合，這就有了15條記錄。在數(shù)據(jù)庫中百萬級別的表才算有點規(guī)模，假如真的這樣做了，無疑是災(zāi)難性的。其次，這樣將兩張表連接起來是沒有意義。所以，我們使用內(nèi)連接來進行連接，找出對應(yīng)兩張表的關(guān)聯(lián)性，設(shè)定條件進行連接查找。

1、內(nèi)連接

關(guān)鍵字： inner join

MariaDB [hellodb]> select * from      -> students inner join teachers     -> on  條件 等價于 where      -> students.teacherid=teachers.tid;     由于是跨表查詢，所以，必須指明哪個表下的字段，否則系統(tǒng)無法識別來源

查詢結(jié)果

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;    

查詢結(jié)果

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;

查詢結(jié)果

3、右外連接

說明：

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

查詢結(jié)果

4、左外連接 擴展

說明：

查詢結(jié)果

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    對有關(guān)聯(lián)的查詢結(jié)果再次進行過濾     -> 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) ;

查詢結(jié)果 現(xiàn)在我們接著上圖中的問題：

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;

查詢結(jié)果

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，我們發(fā)現(xiàn)，A表的TID和第二張表的ID是關(guān)聯(lián)的，     當我們查詢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;

    說明：     假設(shè)我們有兩張表，學(xué)生表和課程表     學(xué)生表存放的是：     stu_id,stu_name,stu_cassid     課程表中存放的是：     cours_id, cours_name     在數(shù)據(jù)庫中，有很多邏輯結(jié)構(gòu)，一對一，一對多，多對多。結(jié)合實際情況，我們一個學(xué)生可能同時學(xué)習(xí)多個課程，每個課程可能有好多學(xué)生學(xué)，所以，由此可以看出是多對多的關(guān)系，     要實現(xiàn)多對多，在數(shù)據(jù)庫中我們可以創(chuàng)建第三個表來實現(xiàn)，     第三張表中存放的是     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;

    說明;     我們暫時不考慮學(xué)生表中的信息，只查詢成績表和課程表，     只取兩個表的交集部分，依舊還是使用inner join

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

    說明：     我們要實現(xiàn)的是某個學(xué)生的某個課程對應(yīng)的成績，     我們對比兩張表，發(fā)現(xiàn)，我只要把第一步的查詢結(jié)果與第二張表的查詢結(jié)果聯(lián)合在一次就達到了我們的最終要求。   

select * from  students inner join scores on students.stuid=scores.stuid; 查詢結(jié)果

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;