程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> MySQL綜合教程 >> mysql-7 數據檢索(5),mysql-7數據檢索

mysql-7 數據檢索(5),mysql-7數據檢索

編輯:MySQL綜合教程

mysql-7 數據檢索(5),mysql-7數據檢索


聯結(join)

SQL最強大的功能之一就是能在數據檢索查詢的執行中聯結(join)表。聯結就是利用SQL的SELECT能執行的最重要的操作。

例子:此例子包含兩個表,一個表是vendors , vendors表包含所有供應商信息,每個供應商占一行,每個供應商具有唯一的標識,此標識稱為主鍵,可以是供應商ID或任何其他唯一值。

products表只存儲產品信息,它除了存儲供應商ID(vendors表的主鍵)外不存儲其他供應商信息,vendors表的主鍵又叫做products的外鍵,它將vendors表與products表關聯,利用供應商ID能從vnedors表中找出相應供應商詳細信息。

SELECT * FROM products;

SELECT * FROM vendors;

SELECT * FROM orderitems;

 兩個表聯結

SELECT vend_name , prod_name , prod_price FROM vendors , products WHERE vendors.`vend_id` = products.`vend_id` ORDER BY vend_name,prod_name;


SELECT vend_name , prod_name , prod_price FROM vendors INNER JOIN products ON vendors.`vend_id` = products.`vend_id`;

 

多個表聯結

SELECT prod_name , vend_name ,prod_price , quantity FROM orderitems , products , vendors WHERE products.`vend_id` = vendors.`vend_id` AND orderitems.`prod_id` = products.`prod_id` AND order_num = 20005;

 

 使用表別名

SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;

 

創建高級聯結,使用表別名

SELECT cust_name , cust_contact FROM customers AS c , orders AS o ,orderitems AS oi WHERE c.`cust_id` = o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'TNT2';

自聯結

SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');   自查詢
SELECT p1.prod_id,p1.prod_name FROM products AS p1, products AS p2 WHERE p1.`vend_id`=p2.`vend_id` AND p2.`prod_id`= 'DTNTR'; 自聯結

自然聯結

SELECT c.* , o.order_num ,o.order_date, oi.prod_id, oi.quantity, OI.item_price FROM customers AS c ,orders AS o, orderitems AS oi WHERE c.`cust_id` =o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'FB';

 

外部聯結

SELECT customers.`cust_id`,orders.`order_num` FROM customers LEFT OUTER JOIN orders ON customers.`cust_id` = orders.`cust_id`;

  

 

 

SELECT vend_name , prod_name , prod_price FROM vendors , products WHERE vendors.`vend_id` = products.`vend_id` ORDER BY vend_name,prod_name;
SELECT vend_name , prod_name , prod_price FROM vendors INNER JOIN products ON vendors.`vend_id` = products.`vend_id`;
SELECT prod_name , vend_name ,prod_price , quantity FROM orderitems , products , vendors WHERE products.`vend_id` = vendors.`vend_id` AND orderitems.`prod_id` = products.`prod_id` AND order_num = 20005;
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title FROM vendors ORDER BY vend_name;
SELECT cust_name , cust_contact FROM customers AS c , orders AS o ,orderitems AS oi WHERE c.`cust_id` = o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'TNT2';
SELECT prod_id,prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
SELECT p1.prod_id,p1.prod_name FROM products AS p1, products AS p2 WHERE p1.`vend_id`=p2.`vend_id` AND p2.`prod_id`= 'DTNTR';
SELECT c.* , o.order_num ,o.order_date, oi.prod_id, oi.quantity, OI.item_price FROM customers AS c ,orders AS o, orderitems AS oi WHERE c.`cust_id` =o.`cust_id` AND oi.`order_num` = o.`order_num` AND prod_id = 'FB';
SELECT customers.`cust_id`,orders.`order_num` FROM customers LEFT OUTER JOIN orders ON customers.`cust_id` = orders.`cust_id`;

 

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved