多表查詢(xún)

使用單個(gè)select 語(yǔ)句從多個(gè)表格中取出相關(guān)的查詢(xún)結(jié)果,多表連接通常是建立在有相互關(guān)系的父子表上;

1交叉連接

第一個(gè)表格的所有行 乘以 第二個(gè)表格中的所有行,也就是笛卡爾積
創(chuàng)建一個(gè)消費(fèi)者與顧客的表格：
代碼如下：

-- create table customers(
-- id int primary key auto_increment,
-- name VARCHAR(20)not null,
-- address VARCHAR(20)not NULL
-- );
-- CREATE table orders(
-- order_namre VARCHAR(20) primary key,
-- num char(20) not NULL,
-- price int not null,
-- customers_id int,
-- constraint cus_ord_fk FOREIGN key(customers_id) REFERENCES customers(id)
-- )
自己插入數(shù)據(jù)即可。
語(yǔ)法:

隱式語(yǔ)法(不使用關(guān)鍵字): select * from customers,orders;

運(yùn)行結(jié)果如下：

顯式語(yǔ)法(使用關(guān)鍵字):select * from customers c INNER JOIN orders o ON c.id=o.customer_id;

兩個(gè)運(yùn)行結(jié)果一樣，但是笛卡爾積有錯(cuò)誤，下面的方法進(jìn)行修正

2內(nèi)連接

因?yàn)榻徊孢B接獲得的結(jié)果集是錯(cuò)誤的。因此內(nèi)連接是在交叉連接的基礎(chǔ)上
只列出連接表中與連接條件相匹配的數(shù)據(jù)行，匹配不上的記錄不會(huì)被列出。

語(yǔ)法:

隱式語(yǔ)法:

select * from customers,orders where customers.id=orders.customers_id;

顯式語(yǔ)法:

select * from customers c INNER JOIN orders o ON c.id=o.customer_id;

運(yùn)行結(jié)果如下

我們還可以給程序起別名：
代碼如下：

select * from customers as c,orders o where c.id=o.customers_id;
SELECT * from customers as c inner join orders o on c.id=o.customers_id;

3外連接

內(nèi)連接只列出所有購(gòu)買(mǎi)過(guò)商品的用戶(hù)的信息,不會(huì)列出沒(méi)有購(gòu)買(mǎi)商品用戶(hù)。
而外鏈接是以一張表為基表，其他表信息進(jìn)行拼接,如果有就拼接上,如果沒(méi)有顯示null; 外鏈接分為左外連接和右下連接。
左外連接: 以關(guān)鍵字左邊的表格為基表進(jìn)行拼接

語(yǔ)法:

select * from customers c LEFT JOIN orders o ON c.id=o.customer_id;

右外連接: 以關(guān)鍵字右邊的表格為基表

語(yǔ)法:

select * from orders o RIGHT JOIN customers c ON c.id=o.customer_id;

4子查詢(xún)

某些情況下,當(dāng)進(jìn)行查詢(xún)的時(shí)候,需要的條件是另外一個(gè)select語(yǔ)句的結(jié)果,這個(gè)時(shí)候就會(huì)用到子查詢(xún),為了給主查詢(xún)(外部查詢(xún)) 提供數(shù)據(jù)而首先執(zhí)行的查詢(xún)(內(nèi)部查詢(xún))被叫做子查詢(xún); 子查詢(xún)分為嵌套子查詢(xún)和相關(guān)子查詢(xún)。

嵌套子查詢(xún):

內(nèi)部查詢(xún)的執(zhí)行獨(dú)立于外部查詢(xún)，內(nèi)部查詢(xún)僅執(zhí)行一次，執(zhí)行完畢后將結(jié)果作為外部查詢(xún)的條件使用（嵌套子查詢(xún)中的子查詢(xún)語(yǔ)句可以拿出來(lái)單獨(dú)運(yùn)行。）

語(yǔ)法及練習(xí): 查詢(xún)出id為1的老師教過(guò)的所有學(xué)生。

select * from students where id in(select s_id from teacher_student where t_id=1);

相關(guān)子查詢(xún)：

內(nèi)部查詢(xún)的執(zhí)行依賴(lài)于外部查詢(xún)的數(shù)據(jù)，外部查詢(xún)每執(zhí)行一次，內(nèi)部查詢(xún)也會(huì)執(zhí)行一次。每一次都是外部查詢(xún)先執(zhí)行，取出外部查詢(xún)表中的一個(gè)元組，將當(dāng)前元組中的數(shù)據(jù)傳遞給內(nèi)部查詢(xún)，然后執(zhí)行內(nèi)部查詢(xún)。根據(jù)內(nèi)部查詢(xún)執(zhí)行的結(jié)果，判斷當(dāng)前元組是否滿(mǎn)足外部查詢(xún)中的where條件，若滿(mǎn)足則當(dāng)前元組是符合要求的記錄，否則不符合要求。然后，外部查詢(xún)繼續(xù)取出下一個(gè)元組數(shù)據(jù)，執(zhí)行上述的操作，直到全部元組均被處理完畢。
創(chuàng)建三個(gè)表格
練習(xí)1. 查詢(xún)出id為1的老師教過(guò)的所有學(xué)生。
– 創(chuàng)建老師表

create table teacher1(
id int primary key auto_increment,
name char(20) not NULL,
subject char(20) not null
);

– 創(chuàng)建學(xué)生表

create table student1(
id int primary key auto_increment,
name char(20) unique not null,
age int null
);

– 創(chuàng)建第三個(gè)表格

create table tea_stu(
id int PRIMARY KEY,
name char(20),
t_id int,
s_id int,
score int not null,
constraint teacher1_id_fk foreign key(t_id) references teacher1(id),
constraint student_id_fk foreign key(s_id) references student1(id)
);

練習(xí)1. 查詢(xún)出id為1的老師教過(guò)的所有學(xué)生。

做法1 用分開(kāi)的方法寫(xiě)出來(lái)：

select s_id from tea_stu where t_id=1;
select * from student1 where id in(2,3)；

做法2：

select * from student1 where id in(select s_id from tea_stu where t_id=1);

相關(guān)子查詢(xún)：

內(nèi)部查詢(xún)的執(zhí)行依賴(lài)于外部查詢(xún)的數(shù)據(jù)，外部查詢(xún)每執(zhí)行一次，內(nèi)部查詢(xún)也會(huì)執(zhí)行一次。每一次都是外部查詢(xún)先執(zhí)行，取出外部查詢(xún)表中的一個(gè)元組，將當(dāng)前元組中的數(shù)據(jù)傳遞給內(nèi)部查詢(xún)，然后執(zhí)行內(nèi)部查詢(xún)。根據(jù)內(nèi)部查詢(xún)執(zhí)行的結(jié)果，判斷當(dāng)前元組是否滿(mǎn)足外部查詢(xún)中的where條件，若滿(mǎn)足則當(dāng)前元組是符合要求的記錄，否則不符合要求。然后，外部查詢(xún)繼續(xù)取出下一個(gè)元組數(shù)據(jù)，執(zhí)行上述的操作，直到全部元組均被處理完畢。
求：每一科考試成績(jī)大于平均分的學(xué)生的分?jǐn)?shù)。

select * from tea_stu as a where a.score>(select avg(b.score) from tea_stu as b where a.s_id=b.s_id);

以上所述是小編給大家介紹的MySQL多表查詢(xún)?cè)斀庹?，希望?duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！