本文實(shí)例講述了MySQL連接查詢。分享給大家供大家參考，具體如下：
創(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_priceFROM fruits ,suppliersWHERE fruits.s_id = suppliers.s_id;
使用 inner join 語法進(jìn)行內(nèi)連接查詢
SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruitsINNER 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_numFROM customers LEFT OUTER JOIN ordersON customers.c_id = orders.c_id;
右連接
SELECT customers.c_id, orders.o_numfrom customers RIGHT OUTER JOIN ordersON customers.c_id = orders.c_id;
復(fù)合條件連接查詢
SELECT customers.c_id, orders.o_numFROM customers INNER JOIN ordersON customers.c_id = orders.c_id AND customers.c_id = 10001;SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id = suppliers.s_idORDER BY fruits.s_id;
【例.46】在fruits表和suppliers表之間使用內(nèi)連接查詢，查詢之前，查看兩個(gè)表的結(jié)構(gòu)
SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits ,suppliersWHERE fruits.s_id = suppliers.s_id;
【例.47】在fruits表和suppliers表之間使用INNER JOIN語法進(jìn)行內(nèi)連接查詢
SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id = suppliers.s_id;
【例.48】查詢供應(yīng)f_id='a1'的水果供應(yīng)商提供的其他水果種類
SELECT f1.f_id, f1.f_nameFROM fruits AS f1, fruits AS f2WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';
【例.49】在customers表和orders表中，查詢所有客戶，包括沒有訂單的客戶，SQL語法如下
SELECT customers.c_id, orders.o_numFROM customers LEFT OUTER JOIN ordersON customers.c_id = orders.c_id;
【例.50】在customers表和orders表中，查詢所有訂單，包括沒有客戶的訂單
SELECT customers.c_id, orders.o_numfrom customers RIGHT OUTER JOIN ordersON customers.c_id = orders.c_id;
【例.51】在customers表和orders表中，使用INNER JOIN語法查詢customers表中ID為10001、的客戶的訂單信息
SELECT customers.c_id, orders.o_numFROM customers INNER JOIN ordersON customers.c_id = orders.c_id AND customers.c_id = 10001;
【例.52】在fruits表和suppliers表之間使用INNER JOIN語法進(jìn)行內(nèi)連接查詢，并對(duì)查詢結(jié)果排序
SELECT suppliers.s_id, s_name,f_name, f_priceFROM fruits INNER JOIN suppliersON fruits.s_id = suppliers.s_idORDER BY fruits.s_id;
希望本文所述對(duì)大家MySQL數(shù)據(jù)庫計(jì)有所幫助。
新聞熱點(diǎn)
疑難解答
圖片精選
