多表查詢

**交叉連接 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查詢的順序反過(guò)來(lái)試一下

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

查詢結(jié)果

總結(jié)：

    我們發(fā)現(xiàn)縱向合并對(duì)字段的類型并不嚴(yán)格，只要與第一張表的字段數(shù)是相同的就可以，當(dāng)然，第二個(gè)查詢的結(jié)果顯示然是沒(méi)有意義的。     在別的數(shù)據(jù)庫(kù)中，例如orcal或serverSQL會(huì)報(bào)錯(cuò)，因?yàn)轭愋筒环?/pre>
2、union的去重功能
查看teachers表   重新構(gòu)建一個(gè)與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');
將兩張表連接起來(lái)再次查看  
總結(jié)：
    union本身亦可以去重，當(dāng)然這里只是示范一下，還有個(gè)命令可以直接去重     select distinct * from teacher2 可以在自己表中去掉重復(fù)的行 
CROSS JOINS
首先，我們之前利用union 進(jìn)行了縱向連接，那么，我們可不可以橫向連接呢？當(dāng)然是可以的，縱向連接由字段數(shù)量的限制，而橫向連接是沒(méi)有字段的限制的，比如：創(chuàng)建兩個(gè)表接下來(lái)我們直接最者兩張表進(jìn)行cross join 連接，

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

1、內(nèi)連接

關(guān)鍵字： inner join
MariaDB [hellodb]> select * from      -> students inner join teachers     -> on  條件 等價(jià)于 where      -> students.teacherid=teachers.tid;     由于是跨表查詢，所以，必須指明哪個(gè)表下的字段，否則系統(tǒng)無(wú)法識(shí)別來(lái)源
查詢結(jié)果
select  對(duì)字段定義別名     stuid,s.name as studentname ,s.age as studentage tid     t.name as teachername ,t.age as teacherage     from      students as s   對(duì)表定義別名     inner join      連接     teachers as t   定義別名     on              條件       s.teacherid=t.tid;    
查詢結(jié)果
2、左外連接
說(shuō)明：
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、右外連接
  說(shuō)明：
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、左外連接 擴(kuò)展
說(shuō)明：
  查詢結(jié)果
5、右外連接 擴(kuò)展

說(shuō)明：  

MariaDB [hellodb]> select      -> stuid,s.name,teacherid,     -> tid,t.name      -> from     -> students as s     -> left join     -> teachers as t     -> on      -> s.teacherid=t.tid    對(duì)有關(guān)聯(lián)的查詢結(jié)果再次進(jìn)行過(guò)濾     -> where     -> stuid is null;
查詢結(jié)果
6、完全外連接

說(shuō)明：
如圖   
select * from students left join teachers  on  students.teacherid=teachers.tid  union  select * from students right join teachers  on  students.teacherid=teachers.tid;
查詢結(jié)果  
7、子查詢

說(shuō)明： 現(xiàn)在我們要查詢所有小于平均年齡的學(xué)生  
select * from students where age < (select avg(age) from students) ;
查詢結(jié)果  現(xiàn)在我們接著上圖中的問(wè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、自連接
說(shuō)明：   

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);
結(jié)果如下  假設(shè)，我們要查詢每個(gè)員工的上級(jí)領(lǐng)導(dǎo)ID，該怎么查。  
    我們要查詢的是第一張表的NAME和第二張表的上級(jí)的NAME，我們發(fā)現(xiàn)，A表的TID和第二張表的ID是關(guān)聯(lián)的，     當(dāng)我們查詢1號(hào)員工的TID的時(shí)候，由于1號(hào)員工的TID是null，所以，我們要顯示的上級(jí)NAME是NULL，     當(dāng)我們查詢2號(hào)員工的上級(jí)ID時(shí)，當(dāng)A表的TID等于B表的ID的時(shí)候，條件達(dá)成，顯示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;
查詢結(jié)果  
9、三表查詢
    說(shuō)明：     假設(shè)我們有兩張表，學(xué)生表和課程表     學(xué)生表存放的是：     stu_id,stu_name,stu_cassid     課程表中存放的是：     cours_id, cours_name     在數(shù)據(jù)庫(kù)中，有很多邏輯結(jié)構(gòu)，一對(duì)一，一對(duì)多，多對(duì)多。結(jié)合實(shí)際情況，我們一個(gè)學(xué)生可能同時(shí)學(xué)習(xí)多個(gè)課程，每個(gè)課程可能有好多學(xué)生學(xué)，所以，由此可以看出是多對(duì)多的關(guān)系，     要實(shí)現(xiàn)多對(duì)多，在數(shù)據(jù)庫(kù)中我們可以創(chuàng)建第三個(gè)表來(lái)實(shí)現(xiàn)，     第三張表中存放的是     id,stu_id,cours_id，score     但是這個(gè)兩個(gè)字段顯然都不合適做主鍵，所以，就可以添加一個(gè)字段ID做主鍵。再添加一個(gè)score字段，存放課程成績(jī)
我們最終要實(shí)現(xiàn)的是某個(gè)學(xué)生在某個(gè)課程上考試成績(jī)是多少  
第一步：首先我們實(shí)現(xiàn)兩張表來(lái)進(jìn)行查詢，這樣條理會(huì)清晰很多
實(shí)現(xiàn)：
說(shuō)明：
MariaDB [hellodb]> select stu.name,sc.score      -> from     -> students as stu      -> inner join     -> scores as sc     -> on     -> stu.stuid=sc.stuid;

查詢結(jié)果  
第二步：這次我們實(shí)現(xiàn)的是某個(gè)課程的對(duì)應(yīng)成績(jī)
    說(shuō)明;     我們暫時(shí)不考慮學(xué)生表中的信息，只查詢成績(jī)表和課程表，     只取兩個(gè)表的交集部分，依舊還是使用inner join
MariaDB [hellodb]> select course.course,sc.score     -> from     -> scores as sc     -> inner join     -> courses as course     -> on     -> course.courseid=sc.courseid;
查詢結(jié)果  
第三步：將以上兩個(gè)步驟連接起來(lái)，就達(dá)到了我們的要求
    說(shuō)明：     我們要實(shí)現(xiàn)的是某個(gè)學(xué)生的某個(gè)課程對(duì)應(yīng)的成績(jī)，     我們對(duì)比兩張表，發(fā)現(xiàn)，我只要把第一步的查詢結(jié)果與第二張表的查詢結(jié)果聯(lián)合在一次就達(dá)到了我們的最終要求。   

當(dāng)然，我們指定對(duì)應(yīng)的字段就可以了  

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

我們可以將查詢出來(lái)的結(jié)果想象成一張獨(dú)立的表，然后，我們將這張表中的courseid與課程表中的courseid相等作為條件，將課程名稱取出來(lái)。  

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;
查詢結(jié)果