mysql-7 数据检索(5)

2018-06-17 23:14:47来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

联结(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`;

  

 

 

组合查询

多数SQL查询都只包含从一个货多个表中返回数据的单条SELECT语句。MYSQL也允许执行多个查询(多个select语句),并将结果作为单个查询结果集返回。这些组合查询通常成为并(union)或者复合查询(compound query)。

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <=5 ;

SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <=5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <=5 OR vend_id IN (1001,1002);

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <=5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002) ORDER BY vend_id,prod_price;

 

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`;

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql权限与安全

下一篇:mysql中类似indexOf的方法LOCATE()