熟妇综合久久久久久,自拍视频在线播放,国产精品白拍三级

主頁(yè) > 知識(shí)庫(kù) > MySQL連接查詢(xún)實(shí)例詳解

MySQL連接查詢(xún)實(shí)例詳解

本文實(shí)例講述了MySQL連接查詢(xún)。分享給大家供大家參考，具體如下：

創(chuàng)建表suppliers：

CREATE TABLE suppliers
(
 s_id   int   NOT NULL AUTO_INCREMENT,
 s_name  char(50) NOT NULL,
 s_city  char(50) NULL,
 s_zip   char(10) NULL,
 s_call  CHAR(50) NOT NULL,
 PRIMARY KEY (s_id)
) ;
INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)
VALUES(101,'FastFruit Inc.','Tianjin','300000','48075'),
(102,'LT Supplies','Chongqing','400000','44333'),
(103,'ACME','Shanghai','200000','90046'),
(104,'FNK Inc.','Zhongshan','528437','11111'),
(105,'Good Set','Taiyuang','030000', '22222'),
(106,'Just Eat Ours','Beijing','010', '45678'),
(107,'DK Inc.','Zhengzhou','450000', '33332');

內(nèi)連接

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits ,suppliers
WHERE fruits.s_id = suppliers.s_id;

使用 inner join 語(yǔ)法進(jìn)行內(nèi)連接查詢(xún)

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits
INNER JOIN suppliers ON fruits.s_id = suppliers.s_id;

創(chuàng)建表orders：

CREATE TABLE orders
(
 o_num int   NOT NULL AUTO_INCREMENT,
 o_date datetime NOT NULL,
 c_id  int   NOT NULL,
 PRIMARY KEY (o_num)
) ;
INSERT INTO orders(o_num, o_date, c_id)
VALUES(30001, '2008-09-01', 10001),
(30002, '2008-09-12', 10003),
(30003, '2008-09-30', 10004),
(30004, '2008-10-03', 10005),
(30005, '2008-10-08', 10001);

左連接

SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;

右連接

SELECT customers.c_id, orders.o_num
from customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;

復(fù)合條件連接查詢(xún)

SELECT customers.c_id, orders.o_num
FROM customers INNER JOIN orders
ON customers.c_id = orders.c_id AND customers.c_id = 10001;
SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id
ORDER BY fruits.s_id;

【例.46】在fruits表和suppliers表之間使用內(nèi)連接查詢(xún)，查詢(xún)之前，查看兩個(gè)表的結(jié)構(gòu)

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits ,suppliers
WHERE fruits.s_id = suppliers.s_id;

【例.47】在fruits表和suppliers表之間使用INNER JOIN語(yǔ)法進(jìn)行內(nèi)連接查詢(xún)

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id;

【例.48】查詢(xún)供應(yīng)f_id='a1'的水果供應(yīng)商提供的其他水果種類(lèi)

SELECT f1.f_id, f1.f_name
FROM fruits AS f1, fruits AS f2
WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

【例.49】在customers表和orders表中，查詢(xún)所有客戶(hù)，包括沒(méi)有訂單的客戶(hù)，SQL語(yǔ)法如下

SELECT customers.c_id, orders.o_num
FROM customers LEFT OUTER JOIN orders
ON customers.c_id = orders.c_id;

【例.50】在customers表和orders表中，查詢(xún)所有訂單，包括沒(méi)有客戶(hù)的訂單

SELECT customers.c_id, orders.o_num
from customers RIGHT OUTER JOIN orders
ON customers.c_id = orders.c_id;

【例.51】在customers表和orders表中，使用INNER JOIN語(yǔ)法查詢(xún)customers表中ID為10001、的客戶(hù)的訂單信息

SELECT customers.c_id, orders.o_num
FROM customers INNER JOIN orders
ON customers.c_id = orders.c_id AND customers.c_id = 10001;

【例.52】在fruits表和suppliers表之間使用INNER JOIN語(yǔ)法進(jìn)行內(nèi)連接查詢(xún)，并對(duì)查詢(xún)結(jié)果排序

SELECT suppliers.s_id, s_name,f_name, f_price
FROM fruits INNER JOIN suppliers
ON fruits.s_id = suppliers.s_id
ORDER BY fruits.s_id;

更多關(guān)于MySQL相關(guān)內(nèi)容感興趣的讀者可查看本站專(zhuān)題：《MySQL常用函數(shù)大匯總》、《MySQL日志操作技巧大全》、《MySQL事務(wù)操作技巧匯總》、《MySQL存儲(chǔ)過(guò)程技巧大全》及《MySQL數(shù)據(jù)庫(kù)鎖相關(guān)技巧匯總》

希望本文所述對(duì)大家MySQL數(shù)據(jù)庫(kù)計(jì)有所幫助。

您可能感興趣的文章:

MySQL中基本的多表連接查詢(xún)教程
詳解MySQL中的分組查詢(xún)與連接查詢(xún)語(yǔ)句
mysql連接查詢(xún)（左連接，右連接，內(nèi)連接）
MySQL查詢(xún)優(yōu)化:連接查詢(xún)排序淺談
MySQL筆記之連接查詢(xún)?cè)斀?/li>
MySQL優(yōu)化之使用連接(join)代替子查詢(xún)
MySQL查詢(xún)優(yōu)化:用子查詢(xún)代替非主鍵連接查詢(xún)實(shí)例介紹
經(jīng)典mysql連接查詢(xún)例題
MySQL中對(duì)表連接查詢(xún)的簡(jiǎn)單優(yōu)化教程
mysql多表連接查詢(xún)實(shí)例講解
mysql多個(gè)left join連接查詢(xún)用法分析
Mysql自連接查詢(xún)實(shí)例詳解

標(biāo)簽：商丘貴州南陽(yáng) 三門(mén)峽揚(yáng)州巴中新余贛州

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL連接查詢(xún)實(shí)例詳解》，本文關(guān)鍵詞 MySQL,連接,查詢(xún),實(shí)例,詳解,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無(wú)關(guān)。